DB DETAILS - LARAVEL TICKETING SYSTEM ==================================== Database Name ------------- ticketing Purpose ------- This database powers a Laravel-based ticketing and support management system. It is designed to manage users, support agents, support categories, tickets, ticket comments, file attachments, internal team messages, client API access, queues, sessions, password resets, and audit history. This is a clean starter database. It contains safe demo data only and does not include live API secrets, customer ticket history, real sessions, real access tokens, or production credentials. Recommended Environment ----------------------- - Laravel 10 or newer - PHP 8.1 or newer - MySQL 8.0 or compatible MariaDB version - InnoDB storage engine - utf8mb4 character set - utf8mb4_unicode_ci collation Important Compatibility Note ---------------------------- The clean SQL version uses CHECK constraints and JSON columns. MySQL 8+ is recommended. If your hosting uses an older MySQL or MariaDB version, CHECK constraints may need to be removed or enforced only in Laravel validation. Main Features Supported ----------------------- 1. User accounts 2. Admin, agent, and normal user roles 3. Client API key ownership 4. Support categories 5. Ticket creation and assignment 6. Ticket priority and status tracking 7. SLA tracking 8. First response tracking 9. Resolution and closure tracking 10. Ticket comments 11. Internal comments 12. File attachments 13. Agent-to-ticket assignment 14. Team/internal messages 15. Ticket audit/history events 16. Laravel cache support 17. Laravel queue/job support 18. Laravel session support 19. Laravel Sanctum personal access tokens 20. Password reset tokens Core Tables ----------- 1. client_api_keys Stores external client/API access records. Important fields: - id - client_name - api_key - api_secret_hash - allowed_origins - is_active - feature_cyber_security - feature_cloud_storage - feature_full_email_setup - feature_full_management_system - email_accounts_used - email_accounts_limit - last_used_at Purpose: Use this table when a client or external frontend needs API access to the ticketing system. Secrets should be hashed, not stored in plain text. Recommended usage: - Show the raw secret only once when generated. - Store only the hashed secret. - Rotate secrets when needed. - Restrict allowed origins in production. 2. support_categories Stores ticket/support categories. Important fields: - id - name - slug - is_active - sort_order Example categories: - General support - Technical support - Sales Purpose: This avoids storing category names as random text and keeps categories consistent across tickets and agents. 3. users Stores app users, admins, and support agents. Important fields: - id - client_api_key_id - support_category_id - name - email - password - role - is_super_admin - department - category Roles: - user - agent - admin Purpose: This table handles login users and support staff. Agents can optionally be linked to a support category. Starter users: - Admin User: admin@example.com - Support Agent: agent@example.com - Demo User: user@example.com Starter password: - password Security note: The starter password is for local/demo use only. Change or remove all demo accounts before production. 4. tickets Stores support tickets. Important fields: - id - ticket_number - support_category_id - title - description - priority - status - requester_id - requester_name - requester_email - department - assigned_to_id - sla_due - first_response_sla - resolution_sla - first_response_at - first_response_met - resolved_at - closed_at - reopened_at - last_customer_reply_at - last_agent_reply_at - deleted_at Ticket priority values: - low - medium - high - urgent Ticket status values: - open - pending - resolved - closed Purpose: This is the main table of the system. Each support request should become a ticket. Recommended ticket flow: 1. User creates a ticket. 2. Ticket starts as open. 3. Agent is assigned. 4. Agent replies. 5. First response time is recorded. 6. Ticket may move to pending if waiting for user/customer. 7. Ticket is resolved when work is complete. 8. Ticket is closed after confirmation or timeout. 9. Ticket can be reopened if the issue returns. 5. ticket_agent Links tickets to assigned agents. Important fields: - ticket_id - agent_id Purpose: Allows one ticket to have multiple agents and one agent to handle many tickets. Recommended usage: Use tickets.assigned_to_id for the primary assigned agent, and ticket_agent for additional collaborators. 6. comments Stores ticket comments and replies. Important fields: - id - ticket_id - author_id - content - is_internal - is_first_response - deleted_at Purpose: Each ticket conversation should be stored here. Comment types: - Public customer/agent reply: is_internal = 0 - Internal team note: is_internal = 1 - First agent response: is_first_response = 1 Recommended usage: When an agent posts the first public response, update: - comments.is_first_response = 1 - tickets.first_response_at - tickets.first_response_met - tickets.last_agent_reply_at 7. attachments Stores uploaded file metadata. Important fields: - id - ticket_id - comment_id - uploaded_by_id - filename - original_filename - stored_filename - path - disk - mime_type - size - checksum Purpose: Files can be attached to tickets or comments. Recommended usage: - Store files on a Laravel disk such as local, public, or s3. - Store only metadata in this table. - Validate file size and MIME type before upload. - Use checksum if you need duplicate detection or file integrity checks. 8. messages Stores internal/team chat messages. Important fields: - id - user_id - message - room_type - room_id Purpose: Supports internal team messaging or ticket-related chat. Room examples: - general - ticket - department 9. ticket_events Stores ticket audit history. Important fields: - id - ticket_id - actor_id - event_type - old_value - new_value - metadata - created_at Purpose: This table makes the database feel alive because it tracks what happened to each ticket over time. Example event types: - ticket_created - ticket_assigned - status_changed - priority_changed - category_changed - comment_added - attachment_uploaded - first_response_recorded - ticket_resolved - ticket_closed - ticket_reopened Recommended usage: Every major ticket action should insert a row into ticket_events. This creates a timeline for the ticket detail page. Laravel/System Tables --------------------- cache Stores Laravel cache records when using database cache driver. cache_locks Stores cache locks used for atomic operations. jobs Stores queued jobs when using database queue driver. failed_jobs Stores jobs that failed permanently. job_batches Stores Laravel batched queue jobs. migrations Tracks which Laravel migrations have run. password_reset_tokens Stores password reset tokens. personal_access_tokens Stores Laravel Sanctum API tokens. sessions Stores user sessions when using database session driver. Main Relationships ------------------ client_api_keys -> users One API client can own many users. support_categories -> users One support category can be linked to many agents/users. support_categories -> tickets One support category can have many tickets. users -> tickets as requester One user can create many tickets. users -> tickets as assigned agent One user/agent can be assigned many tickets. tickets -> comments One ticket can have many comments. users -> comments One user can write many comments. tickets -> attachments One ticket can have many attachments. comments -> attachments One comment can have many attachments. users -> attachments One user can upload many attachments. tickets -> ticket_events One ticket can have many history events. users -> ticket_events One user can perform many ticket actions. users <-> tickets through ticket_agent Many agents can be assigned to many tickets. Starter Demo Data ----------------- Demo client: - Client name: Demo Client - API key: ck_demo_change_me - API secret: demo-secret-change-me Note: The clean schema stores api_secret_hash, not api_secret. In real production, generate a secure random secret and hash it before saving. Demo users: 1. Admin User Email: admin@example.com Role: admin Super admin: yes 2. Support Agent Email: agent@example.com Role: agent Category: Technical support 3. Demo User Email: user@example.com Role: user Demo login password for all seed users: password Demo ticket: - Ticket number: TKT-DEMO-000001 - Title: Welcome ticket - Category: Technical support - Priority: medium - Status: open - Requester: Demo User - Assigned agent: Support Agent Demo comments: - Sample customer comment - Sample agent response Demo message: - Team chat is ready. How To Bring The Database To Life --------------------------------- 1. Import the SQL file Use phpMyAdmin, MySQL CLI, TablePlus, DBeaver, or your hosting database panel. MySQL CLI example: mysql -u root -p < ticketing_clean_database.sql 2. Configure Laravel .env Set your Laravel database connection: DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=ticketing DB_USERNAME=root DB_PASSWORD= Update DB_USERNAME and DB_PASSWORD to match your server. 3. Clear Laravel cache Run: php artisan config:clear php artisan cache:clear php artisan route:clear php artisan view:clear 4. Confirm the app can connect Run: php artisan migrate:status If Laravel can read the database, it should show migration status. 5. Login with demo account Use: Admin: admin@example.com password Agent: agent@example.com password User: user@example.com password 6. Create ticket workflows in Laravel Recommended routes/features: User side: - Register/login - Create ticket - View own tickets - Reply to ticket - Upload attachment - Reopen ticket Agent side: - View assigned tickets - View all open tickets by category - Reply to tickets - Add internal notes - Change ticket status - Change ticket priority - Assign ticket to another agent - Resolve ticket Admin side: - Manage users - Manage agents - Manage support categories - Manage client API keys - View reports - View audit logs 7. Use ticket_events for timeline display On the ticket detail page, show ticket_events as the activity timeline. Example timeline: - Ticket created - Assigned to Support Agent - Agent replied - First response SLA met - Status changed from open to pending - Attachment uploaded - Ticket resolved 8. Use SLA fields for dashboards Recommended dashboard cards: - Open tickets - Pending tickets - Resolved tickets - Closed tickets - Overdue tickets - First response SLA breached - Resolution SLA breached - Tickets by priority - Tickets by category - Tickets assigned to each agent 9. Secure production before launch Before using this database in production: - Delete demo users or change their emails/passwords. - Replace demo API key and demo secret. - Hash all API secrets. - Use HTTPS. - Restrict allowed_origins. - Enable file upload validation. - Add rate limiting to API routes. - Use Laravel policies for ticket access. - Use queues for notifications. - Backup the database daily. - Never expose .env files publicly. Recommended Laravel Models -------------------------- Create or update these models: - User - Ticket - Comment - Attachment - SupportCategory - ClientApiKey - TicketEvent - Message Recommended Eloquent Relationships ---------------------------------- User model: - belongsTo ClientApiKey - belongsTo SupportCategory - hasMany requestedTickets - hasMany assignedTickets - hasMany comments - hasMany attachments - hasMany ticketEvents Ticket model: - belongsTo requester User - belongsTo assignedAgent User - belongsTo SupportCategory - belongsToMany agents through ticket_agent - hasMany comments - hasMany attachments - hasMany events Comment model: - belongsTo Ticket - belongsTo author User - hasMany attachments Attachment model: - belongsTo Ticket - belongsTo Comment - belongsTo uploadedBy User SupportCategory model: - hasMany tickets - hasMany users ClientApiKey model: - hasMany users TicketEvent model: - belongsTo Ticket - belongsTo actor User Message model: - belongsTo User Recommended Status Logic ------------------------ open: Ticket has been created and needs attention. pending: Ticket is waiting for customer reply, external team, or extra information. resolved: Agent has completed the work. closed: Ticket is finalized and should no longer be edited except by admin. Recommended Priority Logic -------------------------- low: Minor issue. No major business impact. medium: Normal support request. high: Important issue affecting work. urgent: Critical issue requiring fast response. Recommended SLA Logic --------------------- First response SLA: The deadline for the first public agent reply. Resolution SLA: The deadline for resolving the ticket. sla_due: General SLA deadline used for quick filtering and dashboard alerts. Suggested SLA examples: - low: first response in 24 hours, resolution in 5 days - medium: first response in 8 hours, resolution in 3 days - high: first response in 2 hours, resolution in 1 day - urgent: first response in 30 minutes, resolution in 4 hours Recommended Index Usage ----------------------- The schema includes indexes for common queries such as: - tickets by status - tickets by priority - tickets by category - tickets by requester - tickets by assigned agent - tickets by created date - tickets by updated date - tickets by status and priority - tickets by requester and status - messages by room - comments by ticket - attachments by ticket/comment These indexes help dashboards, filters, and ticket listing pages stay fast. Production Checklist -------------------- Before launch, confirm the following: [ ] Demo users removed or changed [ ] Demo API key removed or changed [ ] APP_KEY generated [ ] Database credentials are secure [ ] API secrets are hashed [ ] HTTPS enabled [ ] CORS allowed origins configured [ ] File upload size limit configured [ ] File MIME validation enabled [ ] Queue worker configured [ ] Scheduler configured [ ] Backups configured [ ] Error logging configured [ ] Email sending configured [ ] Ticket notifications configured [ ] Admin permissions tested [ ] User permissions tested [ ] Agent permissions tested [ ] Ticket audit events working [ ] SLA calculations tested Clean Design Summary -------------------- This database is structured to support a real Laravel ticketing system, not just static storage. The tickets table handles the main support requests. The comments table creates conversations. The attachments table adds file support. The ticket_agent table supports collaborative assignments. The ticket_events table brings the system to life by recording every important action as a timeline. With the correct Laravel models, controllers, policies, notifications, and queue jobs, this database can power a working support desk where users submit issues, agents respond, admins manage categories and clients, and every ticket has a visible history.