# Database proposal

All schema changes are Laravel migrations. Use unsigned big integer keys, `utf8mb4`, foreign keys, explicit nullable choices, timestamps, and soft deletes only for recoverable business records. Money is `decimal(12,2)` plus an ISO currency code.

| Area | Tables | Key rules/indexes |
|---|---|---|
| Identity | users, workspaces, workspace_user, team_invitations, client_invitations, password_reset_tokens, sessions | unique email; unique workspace/user; token hashes unique; invitations expiry/revocation indexes |
| Clients | clients, client_contacts, onboarding_submissions, client_brand_profiles, client_assets | workspace/client indexes; original onboarding immutable; profile is separate working record |
| Work | projects, thumbnail_briefs, project_assignments, project_tasks, project_status_histories | client/status/deadline index; workspace/status/deadline index; unique assignment role per project/user |
| Review | files, thumbnail_versions, image_annotations, comments, revision_rounds, approvals, deliveries | version unique per project/version number; annotation version/visibility index; files use random storage keys |
| Finance | invoices, invoice_items | unique workspace/invoice number; client/status/due-date index; never cascade-delete invoices |
| Operations | notifications, notification_preferences, activity_events, jobs, failed_jobs | activity record type/id index and actor/date index; event records are append-only |

## Relationships

One workspace owns team memberships and clients. One client owns contacts, profile, onboarding records, assets, projects, and invoices. A project owns a brief, assignments, tasks, versions, comments, revisions, approval, delivery, and activity records. Files are polymorphically associated but retain `workspace_id`, `client_id`, `project_id`, owner, category, random path, and original name to authorize without guessing URLs.