Chapter 2 - Database Technical Reference

Engine and scope

• Migrations (database/migrations): 93 files.
• Schema::create statements: 89 explicit table names + 5 Spatie mapping tables through $tableNames config = effectively ~94 tables.
• Models (app/Models): 85 files.
• Last migration dates 2026-03-12 (monthly_reports title/description), indicating active schema evolution.

ER Diagram and Key Groups

• See Appendix A - Diagrams.
• Tenant root: workspaces; users.current_workspace_id stores active context.
• Each sub-entity can be bound via direct workspace_id (many, e.g. invoices, payments, timesheets, project_budgets, email_accounts) or via parent (tasks via project_id, project_expenses via project_id).

Detailed Table Group Table Names (authoritative list)

Core / framework

users, password_reset_tokens, sessions, cache, cache_locks, jobs, job_batches, failed_jobs, notifications (Laravel DB notifications).

Authorization

roles, permissions, model_has_permissions, model_has_roles, role_has_permissions (Spatie; custom models at app/Models/Role.php, app/Models/Permission.php).

Tenancy, users & workspace admins

workspaces, workspace_members, workspace_invitations, workspace_ai_agents, login_histories, audit_logs, performance_metrics, monthly_reports.

Projects / cases and work

projects, project_clients, project_members, project_milestones, project_notes, project_activities, project_attachments,

task_stages, tasks, task_comments, task_checklists, task_attachments, task_members, task_events, task_templates, task_template_langs, communications,

creator? users with created_by optional.

Timesheets

timesheets, timesheet_entries, timesheet_approvals.

Bugs

bug_statuses, bugs, bug_comments, bug_attachments.

Budgets

project_budgets, budget_categories, project_expenses, expense_approvals, expense_attachments, budget_revisions, budget_revision_approvals, expense_recurring, expense_workflows.

Invoicing & commerce

invoices, invoice_items, payments, currencies, payment_settings, coupons, plan_requests, plan_orders.

Referrals

referral_settings, referrals, payout_requests.

Plans

plans.

Email

email_accounts, email_messages, whatsapp_messages, webhooks.

Slack/Telegram/Zoom

slack_settings, telegram_settings, zoom_meetings, zoom_meeting_members.

Media & libraries

media, media_items.

Integrations AI

ai_batch_logs, ai_batch_log_entries, ai_chat_conversations, ai_chat_messages, ai_conversation_files, ai_knowledge_entries, ai_token_usage.

Communications, marketing

notification_templates, notification_template_langs, email_templates, email_template_langs, user_email_templates, newsletters, contacts, landing_page_settings, landing_page_custom_pages.

System/saas

settings.

Others

activities only referenced in seeders? categories, not referenced migrations (factories exist but no migration) remained after cleanup.

Model Relationships (intentional trace)

Models reference models as normal Eloquent relations listed in docs/models-columns.json (repo data file). Read app/Models/*.php for exact method names; keys from Populate model relationships:

• User: plan (plans), workspaces (belongsToMany workspace_members), ownedWorkspaces, currentWorkspace, projects (project_members), tasks (task_members), timesheets, projectActivities, timerProject, timerTask, timerEntry, referrals, payoutRequests.
• Workspace: owner, members, activeMembers, invitations, pendingInvitations, users, various domain collections (taskStages, bugStatuses, projects, timesheets, workspaces planning).
• Project: workspace, members/clients, milestones, notes, activities, budget, expenses, tasks, timesheetEntries, attachments, invoices, emailMessages (hasOne EmailMessage: hasOne).
• Task: project, taskStage, milestone, assignedTo, members pivot, comments, checklist, attachments, events, emailMessage (hasOne? Actually EmailMessage belongs to Task; to check), auditLogs as morph? audit logs use entity_type/id; not typed relation.
• Invoice: project, workspace, client, creator, items, payments.
• EmailMessage: account, task, project, bug.
• AiChatConversation: workspace,user,messages,files; context layered context.
• MonthlyReport: employee, workspace, reviewer.

Column details

Column definitions (key only; rely on migrations for full list). Invocation Schema::create() is accurate source. For contract updates, confirm every column via database/migrations or run SHOW COLUMNS (not possible in this audit no DB).

Useful columns that stand out:

users (key columns)

name, email (unique), password (nullable), lang, current_business, avatar, type (default 'company'), created_by, mode, is_enable_login, google2fa_secret, google2fa_enable, status, active_module, plan_id, plan_expire_date, requested_plan, plan_is_active, storage_limit, is_trial, trial_day, trial_expire_date, referral_code, used_referral_code, commission_amount, current_workspace_id, timer_* fields, profile fields (position, job_title, country, branch, phone, specialties, skills, gender, birthday, id_number, languages JSON, address, emergency_contact_*, employee_id, department, date_of_joining, contract_type, probation_end_date, hourly_rate, bank_name, bank_account_number, bank_routing_number, bank_iban, bank_swift), social block, created_at, updated_at.

Careful: all SaaS economics fields use nullable/default when not SaaS.

workspaces (key columns)

name, slug (unique), description, owner_id, settings (json), is_active, timesheet_enabled (bool), timesheet_approval_required (enum none/manager/admin), timesheet_auto_submit (bool), timesheet_reminder_days, default_work_start, default_work_end, budget_settings (json), is_suspended (bool).

projects (key columns)

workspace_id, title, description, status (planning,active,on_hold,completed,cancelled), priority (low,medium,high,urgent), start_date, deadline, estimated_hours, actual_hours, budget (decimal), progress, is_public, governance_settings (json), created_by, updated_by, deleted_at.

tasks (key columns)

project_id, task_stage_id, milestone_id, title, description, priority (low/medium/high/critical), start_date, due_date, status? (not listed in migration! Important: tasks has columns in migrations enumerated — no top-level status column itself except task_stage). completion etc., assigned_to, allow_multi_assign (bool), created_by, order, progress, created_at etc.

Note: allow_multi_assign 0/1; multi-assignees stored in task_members pivot.

invoices (key columns)

invoice_number (unique), project_id, workspace_id, client_id, created_by, title, invoice_date, due_date, subtotal, tax_rate, tax_amount, discount_amount, total_amount, paid_amount, status (draft/sent/viewed/paid/partial_paid/overdue/cancelled), sent_at, viewed_at, paid_at, payment_method, payment_reference, payment_token, payment_details (json), client_details (json), notes, terms, deleted_at.

payments (key columns)

created_by, invoice_id, workspace_id (nullable), payment_method, amount, payment_date, transaction_id, status, gateway_response (json), notes.

email_accounts (key columns)

user_id, workspace_id, host, port, encryption, username, password (text; encrypted in model) , is_active, filter_type (none/whitelist/blocklist), filter_list (json), sync_limit, sync_unseen, automation_settings (json), use_idle (named last in later migrations).

email_messages (key columns)

account_id, message_id (unique), uid, from_email, from_name, to_email, subject, body_html, body_text, date, reply_to, received_path, is_read, project_id, task_id, bug_id, is_automated, ai_decision (json), reassignment_count.

tasks event types

task_id, event_type (assigned/submitted/returned/approved), triggered_by (email/manager/system), execution_minutes, return_count, return_reason, performance_status (on_time/delayed/critical).

Seeders Summary

• DatabaseSeeder orchestrates core and demo categories.
• Always: PermissionSeeder (286 permission), RoleSeeder, PlanSeeder, UserSeeder, CurrencySeeder, LandingPageSettingSeeder, LandingPageCustomPageSeeder, EmailTemplateSeeder, NotificationTemplateSeeder, TaskTemplateSeeder.
• Demo only: WorkspaceSeeder, CouponSeeder, PlanOrderSeeder, PlanRequestSeeder, NewsletterSeeder, ContactSeeder, TaskStageSeeder, BugStatusSeeder, ProjectSeeder, BudgetCategorySeeder, BudgetSeeder, TaskSeeder, BugSeeder, SubtaskSeeder, ActivitySeeder, TimesheetSeeder, ExpenseSeeder, InvoiceSeeder, LoginHistorySeeder, WebhookSeeder, ZoomMeetingSeeder.
• Standalone seeders (not in main list in DatabaseSeeder): SulkanCompanySeeder, MockEmailMessageSeeder, MediaSeeder, MediaItemSeeder, ExpenseWorkflowSeeder, BudgetAlertSeeder, ReferralSettingSeeder, BrandingRefreshSeeder, UpdateThemeColorSeeder.
• UserSeeder creates superadmin, company, and employee roles with default passwords 000 (documented in SEEDER_CREDENTIALS.md). Warning: placeholder credential set must be overwritten for any production environment.
• PermissionSeeder defines all permission baselines.
• RoleSeeder defines grant matrix.
• PlanSeeder defines Free (non-SaaS), Starter, Pro. Only Free initially when SaaS mode.
• Factories: UserFactory, ProductFactory, CategoryFactory (last 2 may be surplus from starter).

Migration conventions & history tracking

• Schema::create() directives.
• Schema::table typically extend columns (making extensive versions).
• Schema::create missing? no in-place alternative.
• Two migration snapshots with update only:
• 2026_01_08_update_existing_data_to_tkamol.php rebrand data fix.
• 2026_02_22_000001_update_ai_agent_defaults.php renames/cleans default agent info.
• 2026_01_29_160703_create_email_accounts... tables no-op re-add.
• Erasing data destructive: content will not tracked, migrations do not pre-load business data (only maintenance safe).
• Database migrations include softDeletes only workspace membership project? see migration files marked ->softDeletes.
• migrations table itself stores applied records (normal Laravel).

Migration highlights (selected)

• 2026_02_14_000001_add_ai_enhancement_fields.php adds practice_areas json, seniority_level columns to users + availability_status, max_cases to workspace_members.
• 2026_02_22_000002_... adds ai_monthly_token_limit, later renamed ai_monthly_price_limit 2026-03-04.
• 2026_01_29... adds automation_settings to email_accounts.
• 2026_02_16_000001_create_ai_agent_tables.php creates AI agent tables (and deletes AI agent permissions).
• 2026_03_04_000001 ups/permission tables; ensures user presence.
• 2026_01_30_... adds is_suspended to workspaces.
• 2026_01_09_... creates email_accounts, email_messages.
• 2026_01_30_... adds use_idle.
• 2026_02_13_... adds reassignment_count? Actually AI decision + bug id.
• 2026_02_14_... (second) employee monthly report etc.

Key integrity notes (identified)

1. No explicit global tenant scope; relies on queries.
2. WorkspaceObserver runs in every workspace creation (automation guard).
3. UserObserver decides default plan under SaaS.
4. TaskObserver injects TaskEvent each transition, including name substring-based logic.
5. Keys: each users.email unique; workspaces.slug unique.
6. Certain foreign keys (email_messages -> tasks/projects/bugs) use nullable with set null to retain data.
7. Expense (budget) parent relationship manually established via project_id columns instead of dedicated FKs; quality risk under data integrity (coders).
8. Unique (workspace_id, user_id) on workspace_members.
9. Some pivots (project_clients, task_members, workspace_members) include primary keys and timestamps.

Data retention deletion and GDPR-style

• Soft delete only observed in projects and maybe timesheets/invoices? dependent on each table's service (not mapped). Rely on watchers when production purges.
• Requirements likely need retention, but no framework exists.>