# 04 — Database Entity & Relationship Specification

Specification only. No migrations or SQL exist yet (Phase 1+). Engine: MariaDB 11.4/11.8 LTS, InnoDB, `utf8mb4` / `utf8mb4_unicode_520_ci` (or the server default of the chosen version; recorded in ADR-0003).

## 1. Conventions (all tables unless stated)
- `id BIGINT UNSIGNED` internal PK; `public_id CHAR(26)` ULID, unique per tenant: `UNIQUE(tenant_id, public_id)`.
- `tenant_id BIGINT UNSIGNED NOT NULL` on every business table; `UNIQUE(tenant_id, id)` on every parent; child FKs are composite `(tenant_id, parent_id)`.
- Timestamps `created_at`, `updated_at` as `DATETIME(6)` in UTC. Display conversion uses tenant timezone.
- Soft retention: `archived_at` where business records may be archived; hard delete only where legally and logically permitted (see retention, 06 §8).
- `row_version INT` for optimistic locking on contended rows (stock, bookings, invoices).
- Money: `BIGINT` minor units + `CHAR(3)` currency. Quantities: `DECIMAL(14,3)`. Rates: `DECIMAL(9,6)`. No FLOAT/DOUBLE.
- Encrypted fields: `VARBINARY`/`TEXT` holding the envelope (06 §4) in column `<name>_enc`; blind index in `<name>_bidx CHAR(64)`.
- Enumerations: `VARCHAR` + CHECK, not MySQL ENUM, so values can evolve through migrations.
- JSON columns only for flexible, non-queried payloads (MariaDB `JSON` is an alias of `LONGTEXT` with a validity check, so no JSON indexing is relied upon; portable to MySQL).
- Indexes: every FK, every `(tenant_id, …)` access path used by a list/filter, plus unique business keys per tenant.
- Naming: snake_case plural tables, `module_` prefix only where ambiguous.

## 2. Entities by module

### Core / platform (no tenant_id unless noted)
`tenants`(id, public_id, name, slug, status[trial|active|suspended|closed], preset_key, locale, timezone, currency, created_at) · `tenant_domains`(tenant_id, host, verified_at) · `platform_users`(id, email, password_hash, mfa required) · `support_grants`(platform_user_id, tenant_id, reason, scope, expires_at, revoked_at) · `presets`(key, name) · `preset_versions`(preset_key, version, definition JSON) · `tenant_keys`(tenant_id, key_version, kind[dek|index], wrapped_key, created_at, retired_at; see 06 §3) · `approval_requests`(tenant_id, type[refund|adjustment|freeze|po|…], subject_type/id, requested_by, approver, status, amount_minor, decided_at — backs the "request/approve" actions in 07) · `schema_migrations` · `jobs`(tenant_id nullable only for platform jobs) · `job_failures` · `cron_heartbeats`.

### M03 IAM
`users`(global identity: id, public_id, email unique, email_verified_at, password_hash, status) — a person may belong to several tenants.
`memberships`(tenant_id, user_id, status, default_branch_id, last_active_at; UNIQUE(tenant_id,user_id)) — the only authority for tenant access.
`roles`(tenant_id, key, name, is_system, template_version) · `role_permissions`(tenant_id, role_id, permission, scope JSON) · `membership_roles`(tenant_id, membership_id, role_id) · `membership_branch_scopes`(tenant_id, membership_id, branch_id) · `client_assignments` lives in M07.
`sessions`(id hash, user_id, tenant_id nullable, ip_hash, ua_hash, created_at, last_seen, rotated_at, revoked_at) · `login_attempts`(key_hash, ip_hash, outcome, at) · `mfa_factors`(user_id, type, secret_enc, confirmed_at) · `mfa_recovery_codes`(hashes) · `email_tokens`(user_id, purpose, token_hash, expires_at, used_at) · `invitations`(tenant_id, email, role_ids, token_hash, expires_at, accepted_at).

### M20 Audit
`audit_log`(id, tenant_id nullable [platform events], seq per tenant, occurred_at, actor_type, actor_id, actor_membership_id, action, resource_type, resource_id, outcome, correlation_id, ip_hash, ua_hash, change_redacted JSON, prev_hash, row_hash). Append-only: grants INSERT/SELECT only. `audit_chain_heads`(tenant_id, last_seq, last_hash; row-locked to serialise chain appends per tenant) · `audit_checkpoints`(tenant_id, seq_from, seq_to, root_hash, signed_by_key, exported_offsite_at) — retention purges are only allowed up to a checkpoint, so the chain stays verifiable after old rows are deleted · `audit_exports`(who, filter, at). `security_events` view/table for auth anomalies.

### M01/M02 Tenant setup
`branches`(tenant_id, name, address, timezone, status) · `tenant_settings`(tenant_id, key, value JSON, version) · `tenant_setting_versions` · `themes`(tenant_id, status[draft|published], tokens JSON, version, published_at) · `theme_versions` · `brand_assets`(tenant_id, kind[logo|icon|…], file_ref, checksum) · `onboarding_state`.

### M04 CRM
`clients`(tenant_id, branch_id, status[prospect|active|lapsed|archived], given/family name, dob_enc, email, phone, id_number_enc + bidx, marketing consent summary) · `client_addresses` · `emergency_contacts`(phone/name encrypted as C3 optional) · `client_custom_fields`/`custom_field_defs`(tenant_id, key, type, visibility, retention, required, staff_roles) · `client_tags` · `client_notes`(visibility, author) · `consents`(tenant_id, client_id, purpose, text_version_id, granted_at, withdrawn_at, evidence) · `agreement_templates`/`agreement_versions` (immutable) · `signed_agreements`(client_id, version_id, signed_at, signature_data, document_ref) · `documents`(client_id, class, file_ref enc, checksum) · `health_disclosures`(client_id, data_enc, collected_at, purpose) · `communication_preferences` · `data_requests`(type[export|correct|delete], status, due_at) · `client_keys`(tenant_id, client_id, wrapped_key, destroyed_at — per-client key used to encrypt PII copied into immutable snapshots; destroying it crypto-shreds those copies, see 06 §3b) · `leads`/`prospect_activities`.

### M05 Memberships
`membership_plans`(tenant_id, name, price_minor, billing_period, access_rules JSON, terms_version) · `plan_versions` (immutable once sold) · `contracts`(tenant_id, client_id, plan_version_id, start/end, status, snapshot JSON, signed_agreement_id) · `membership_events`(contract_id, type[start|freeze|unfreeze|cancel|renew|upgrade], effective_at, reason, actor) — append-only history · `freezes`.

### M06 Attendance
`access_policies`(tenant_id, rule set) · `checkins`(tenant_id, client_id, branch_id, at, method, decision, decision_reason, staff_membership_id, idempotency_key) · `access_devices` · `door_events` (later).

### M07/M08 Trainers & programmes
`trainer_profiles`(tenant_id, membership_id, bio, rates) · `client_assignments`(tenant_id, client_id, trainer_membership_id, from, to) · `session_packages`/`package_balances`/`package_ledger` (append-only) · `exercises` · `programmes`(client_id, version) · `programme_items` · `assessments`(client_id, type, measured_at, values_enc where health-related) · `goals`.

### M09 Bookings
`rooms` (links M16) · `class_types` · `class_sessions`(tenant_id, type, start/end, capacity, instructor, room, status, row_version) · `bookings`(session_id, client_id, status[booked|waitlisted|attended|no_show|cancelled], source, cancelled_reason; UNIQUE(session,client)) · `waitlist_entries`(position) · `appointments`(trainer, client, start/end, status) · `availability_rules` · `class_series`(tenant_id, recurrence rule, local start time, tenant time zone; recurrence expands in local wall-clock time so daylight-saving or zone rule changes never shift a class, and materialised `class_sessions` are stored in UTC) · `cancellation_policies`.

### M10–M12 Finance
`tenant_tax_profile`(tenant_id, legal name, trading name, registered address, VAT registration number, tax-invoice wording, effective dates; copied into every issued invoice snapshot) · `tax_rates`(tenant_id, rate, inclusive, effective) · `invoices`(tenant_id, number [gap-free per tenant series], client_id, currency, status[draft|issued|part_paid|paid|void|credited], issued_at, due_at, totals, **snapshot JSON** immutable after issue) · `invoice_lines`(invoice_id, description, qty, unit_minor, tax_rate snapshot, line_total) · `invoice_number_series` · `payments`(tenant_id, client_id, method, amount_minor, currency, status[pending|succeeded|failed|reversed|unknown], provider, provider_ref, received_at) · `payment_allocations`(payment_id, invoice_id, amount_minor) append-only · `credit_notes`(+lines, linked invoice, snapshot) · `refunds`(payment_id, amount, reason, status, requested_by, approved_by, provider_ref) · `refund_approvals` · `recurring_schedules`(contract_id, next_run, amount, status) · `provider_customers`/`payment_methods`(provider token only, last4, brand, expiry — never PAN/CVV) · `provider_events`(provider, event_id UNIQUE, payload_redacted, processed_at, outcome) · `ledger_entries` (double-entry-style journal for reconciliation) · `expenses`(category, amount, receipt ref) · `reconciliation_batches`/`reconciliation_items` · `financial_adjustments`(who, reason, approver).

### M13/M14 POS & inventory
`products`(sku UNIQUE per tenant, price_minor, tax_rate_id, track_stock) · `product_variants` · `suppliers` · `purchase_orders`(+lines, status, received) · `stock_locations` · `stock_movements`(product, location, qty_delta, reason[sale|receipt|adjust|transfer|return], ref_type, ref_id) append-only; on-hand = SUM, cached in `stock_levels` with `row_version` · `pos_sessions`(register, opened/closed, float, variance) · `pos_sales`(→ invoice) · `receipts`(snapshot).

### M15/M16 Staff & facilities
`staff_profiles` · `shifts` · `time_entries` · `commission_rules` · `commission_records` (immutable, adjusted by new rows) · `equipment`(branch, status) · `maintenance_tickets` · `maintenance_schedules` · `facility_areas`.

### M17 Communications
`email_transports`(tenant_id nullable for platform defaults, slot[A|B], host, port, encryption, username_enc, password_enc, from) · `email_templates`(tenant_id, key, locale, subject, html, text, version, placeholder schema) · `email_outbox`(tenant_id, idempotency_key UNIQUE, to_address (C2: required to send; body and address purged per retention), to_hash (lookup), template, payload JSON, state, attempts, last_error_redacted, transport_used, provider_msg_id) · `email_events`(outbox_id, type, at, detail) · `communication_log`(client_id, channel, subject, outbox_id) · `announcements`.

### M19/M21/M22
`export_jobs`(tenant_id, requester, type, filter, file_ref, expires_at, status) · `saved_reports` · `backup_runs`(type, method[mysqldump|native], started, finished, size, checksum, destination, status, verified_at) · `restore_tests` · `backup_destinations` (credentials encrypted) · `system_settings` (platform) · `diagnostic_results`.

## 3. Relationship highlights (ERD summary)
```
tenants 1─* branches, memberships, roles, clients, invoices ...
users *─* tenants via memberships (role set per membership)
clients 1─* contracts ─> plan_versions ; contracts 1─* membership_events
clients 1─* invoices 1─* invoice_lines ; payments *─* invoices via payment_allocations
payments 1─* refunds ; invoices 1─* credit_notes
class_sessions 1─* bookings *─1 clients ; class_sessions 1─* waitlist_entries
products 1─* stock_movements ; purchase_orders 1─* lines → stock_movements(receipt)
email_outbox 1─* email_events ; jobs → any tenant object by (tenant_id,type,id)
```
Full ERD diagram (Mermaid) will be generated into `DB-MAP.md` as tables are designed in detail per phase; this spec fixes entities and rules now.

## 4. Integrity rules
- Gap-free invoice numbers per tenant per series via a locked counter row inside the issuing transaction.
- Issued invoices, credit notes, signed agreements, plan versions, audit rows, stock movements, ledger entries, package ledger and membership events are **append-only**; enforced by service layer, and by DB grants/triggers where practical (audit: no UPDATE/DELETE grant).
- Booking capacity: row-lock the session (`SELECT … FOR UPDATE`) while counting and inserting; waitlist promotion in the same transaction.
- Stock: movement insert + level update in one transaction; negative stock policy per tenant.
- Payment idempotency: unique `(provider, event_id)` and unique payment `idempotency_key`.
- Check-in idempotency: unique `(tenant_id, idempotency_key)`.
- Tenant consistency: composite FKs described above; the Isolation suite verifies by attempting cross-tenant inserts directly at SQL level.

## 5. Retention hooks
Each table carries a retention class (see 06 §8) in `DB-MAP.md`. Deletion/anonymisation procedures operate per client and never remove accounting records that must be retained; those are de-identified instead where the law allows.