# 03 — Data Model

Master ERD and shared conventions. Column-level detail per table lives in each
module spec; this is the map that keeps migrations from colliding or duplicating.

## 1. Shared conventions (every table)

```php
$table->id();                                   // internal PK
$table->ulid('ulid')->unique();                 // public identifier
$table->foreignId('firm_id')->constrained()->restrictOnDelete();
...domain columns...
$table->timestamps();
$table->softDeletes();                          // default; explicit exception if not used
```

- Money: `decimal(18,2)`, always paired with an implicit NGN currency (single
  currency v1). Never `float`.
- Status/type columns: backed string enum values, e.g. `'draft'`, `'sent'` — not
  integers — so DB rows are human-readable and enum drift is visible in queries.
- Dates vs datetimes: use `date` for calendar-only concepts (hearing date, due
  date), `datetime`/`timestamp` when time-of-day matters.
- Every FK: `constrained()->{cascade|restrict|nullOnDelete}` chosen deliberately
  per relationship (financial/audit rows: `restrict`; pure child rows: `cascade`;
  optional links: `nullOnDelete`).
- Every FK column indexed; composite indexes added where a module spec says so.
- Polymorphic use is limited to two well-justified cases (activity subject via
  activitylog, and media via medialibrary) — everywhere else, explicit FK tables,
  for query clarity and referential integrity.

## 2. High-level ERD

```
firms ──< users ──< user_firm (roles) 
   │        │
   │        └──< personal_access_tokens (if API needed)
   │
   ├──< offices
   ├──< courts, practice_areas, matter_stages     (reference data)
   ├──< contacts ──< clients
   │                  │
   │                  └──< portal_users
   │
   ├──< matters >── clients
   │      │
   │      ├──< matter_team (user_id, role)
   │      ├──< matter_parties (contact_id, role)
   │      ├──< matter_events (timeline)
   │      ├──< matter_deadlines
   │      ├──< hearings ──< hearing_attendances
   │      │        └──< appearance_fee_claims
   │      ├──< witnesses ──< testimonies
   │      ├──< tasks ──< task_comments
   │      ├──< time_entries
   │      ├──< fee_arrangements
   │      ├──< media (documents, via medialibrary) ──< media custom props (version, legal_type)
   │      ├──< evidence_items
   │      ├──< invoices ──< invoice_items
   │      ├──< receipts
   │      ├──< expenses
   │      ├──< retainer_ledger_entries
   │      └──< matter_comments
   │
   ├──< accounts (COA) ──< journals ──< journal_lines
   ├──< bank_accounts ──< bank_transactions
   ├──< announcements ──< announcement_reads
   ├──< message_threads ──< messages
   ├──< staff (extends users) ──< leave_requests, payroll (optional)
   └──< activity_log (audit — spatie)
```

## 3. Reference data tables (seed-managed, firm-editable)

`courts`, `practice_areas`, `matter_stages`, `contact_categories`,
`appearance_fee_scales`, `document_types`, `task_templates`. All follow the same
shape: `id, firm_id, name, slug, sort_order, is_active, timestamps`.

## 4. Numbering sequences

`number_sequences`: `firm_id, key (matter/invoice/receipt/claim), year,
last_number` — locked row per key/year, incremented inside a transaction by
`NumberingService`. Prevents duplicate human-facing numbers under concurrency.

## 5. Settings & feature flags

`settings`: `firm_id, key, value(json)`. Typed accessors via a `Settings`
facade/service (`Settings::get('vat_rate')`), cached per firm, invalidated on
write. Avoids scattering config columns across the `firms` table over time.

## 6. Indexing strategy (representative — full list per module)

| Table | Composite index | Why |
|---|---|---|
| matters | (firm_id, status), (firm_id, client_id) | list filters, client matter tab |
| hearings | (firm_id, hearing_date), (matter_id, hearing_date) | daily roster, matter timeline |
| matter_deadlines | (firm_id, due_date, status) | deadline dashboard scan |
| time_entries | (matter_id, billable), (user_id, worked_on) | unbilled-time queries, timesheets |
| invoices | (firm_id, status, due_date) | aging report |
| journal_lines | (account_id, created_at) | ledger/account statements |
| activity_log | (subject_type, subject_id) | audit trail per record (spatie default) |

## 7. Data retention & deletion

- Soft deletes everywhere by default; **hard delete only** via a scheduled,
  logged purge job for genuinely disposable data (e.g. read notification rows
  older than 1 year) — never for financial or case records.
- Financial rows (`journal_lines`, `invoices`, `receipts`) are never deletable
  through the UI once posted — only reversible via a counter-entry, preserving
  the audit trail.