# Schema notes

52 application tables. The full definitions live in `database/migrations/`;
this file records the decisions behind them.

## Table groups

| Group | Tables |
| --- | --- |
| Platform | `tenants`, `tenant_invitations`, `platform_admins`, `settings`, `audit_logs`, `impersonation_logs`, `webhook_events`, `email_log`, `login_attempts` |
| Billing | `subscription_plans`, `plan_prices`, `plan_entitlements`, `coupons`, `subscriptions`, `invoices`, `invoice_lines`, `payments`, `payment_methods` |
| Identity | `users`, `permissions`, `roles`, `role_permissions`, `user_roles`, `teams`, `team_members` |
| CRM | `companies`, `contacts`, `pipelines`, `pipeline_stages`, `leads`, `lead_scoring_rules`, `opportunities`, `products`, `opportunity_items`, `activities` |
| Productivity | `tasks`, `appointments`, `appointment_attendees`, `notes`, `files`, `tags`, `taggables`, `notifications`, `notification_preferences` |
| Support & meta | `support_tickets`, `ticket_replies`, `custom_fields`, `custom_field_values`, `imports`, `import_rows`, `tenant_counters` |

## Conventions

- InnoDB, `utf8mb4`, `utf8mb4_unicode_ci`.
- `BIGINT UNSIGNED` surrogate key plus a ULID `public_id` on anything addressed
  from outside the database.
- All timestamps stored in UTC; converted at the presentation boundary using the
  tenant's timezone, then the user's if they set one.
- Money is `DECIMAL(15,2)` beside its own `CHAR(3)` currency. Never a float.
- Enum-like columns are `VARCHAR` with an application constant, not MySQL
  `ENUM`. Changing an `ENUM` means `ALTER TABLE` on a live customer table, which
  is exactly what the upgrade path must avoid.
- Soft deletes on everything a user can delete. Hard deletes exist only in
  erasure workflows.

## Decisions worth knowing

**Composite foreign keys.** Every tenant-owned table carries
`UNIQUE (tenant_id, id)`, and references between tenant tables are composite:
`FOREIGN KEY (tenant_id, contact_id) REFERENCES contacts (tenant_id, id)`.
MySQL then refuses a cross-tenant reference outright.

The cost: `ON DELETE SET NULL` is illegal on a composite key containing a
`NOT NULL` column, so these keys are `RESTRICT` or `CASCADE`. Audit attribution
columns (`created_by`, `updated_by`, `uploaded_by`) therefore use simple keys
with `nullOnDelete` — they never drive an authorisation decision, so they do not
need the composite guarantee.

**`settings.tenant_id` defaults to 0 for platform scope.** A nullable column
would be the obvious choice, but MySQL treats NULLs as distinct in a unique
index, which would silently permit duplicate platform settings. The sentinel is
ugly and correct.

**`tenant_counters` exists to avoid `COUNT(*)`.** Checking "is this tenant under
its contact limit" must not scan a table holding every tenant's contacts.
Counters are updated in the same transaction as the record they count, with a
nightly reconciliation job to correct drift.

**`import_rows` persists every row of a CSV.** That is what makes a 50,000-row
import resumable across many short worker runs, and what produces a downloadable
error report naming the exact rows that failed and why.

**Audit tables carry no foreign keys.** `audit_logs`, `impersonation_logs`,
`email_log` and `login_attempts` must outlive the tenant they describe. A
cascade delete on a tenant would otherwise erase the record of what happened to
it.

**Leads and opportunities reference each other.** `leads.converted_opportunity_id`
and `opportunities.lead_id` form a deliberate cycle, added after both tables
exist. Both are `RESTRICT`, so a hard delete requires clearing one side first —
which the delete service does inside a transaction.