# Daycare Portal — Database Design

Living blueprint. We design domain-by-domain (ERD + table spec), review, **then** write migrations.
Nothing here is a migration yet.

## Conventions (apply to every table)

- **Naming:** `students` (not children), `guardians`, `payers`. snake_case. Pivots: `student_guardian`, `student_payer`.
- **Money:** `decimal(10,2)` columns — exact fixed-point (never `float`/`double`). Round split/percentage math to the cent.
- **Enums/status:** stored as `string`, validated in the app (no DB enums).
- **Multi-school seam:** every domain table carries `school_id` → one `schools` row for now; multi-school is future, not built.
- **Soft deletes** on record tables (students, classrooms, guardians, families, payers, invoices…).
- **Timestamps** everywhere. IDs are bigint auto-increment.

---

# Accounts & Auth

Three **separate Laravel guards**, each its own table — kept apart so a family login can never touch staff data, and vice-versa.

| guard | table | who | how they get in |
|---|---|---|---|
| `staff` | `users` | admin, teachers, finance | **admin-provisioned** — login + forgot/reset only, no self-registration |
| `guardian` | `guardians` | parents / families | **self-service** — register → verify email → login, full forgot/reset |
| `student` | `students` | children | no login today; guard reserved so we can switch it on later without a rewrite |

**One login screen, a `Parent | Staff` toggle.** The toggle just picks which guard the credentials authenticate against (two Sanctum login endpoints under the hood). It doesn't weaken separation — staff creds fail against the guardian table and vice-versa. It also gates the extras, because the flows are asymmetric:

- **Parent** → login **+ "Create an account"** (register) **+ Forgot password**. Register + verify-email exist only here.
- **Staff** → login **+ Forgot password** only. Staff are created from the Admin/Team screen (Phase 5), never self-registered.

Forgot/reset carries the toggle selection so the reset targets the correct guard's broker (covers a shared email — a teacher who's also a parent). Toggle defaults to **Parent** (the larger audience).

`students` stays a plain record table for now; adding `email` / `password` / `remember_token` later turns it into the third authenticatable without touching the rest of the schema.

---

# Phase 1 — Backbone

The people + the place-in-the-ladder. Everything else (attendance, billing, care) hangs off these.

```mermaid
erDiagram
    schools ||--o{ grades : "defines"
    schools ||--o{ classrooms : "has"
    schools ||--o{ students : "enrols"
    schools ||--o{ payers : "bills"

    grades ||--o{ classrooms : "level of"
    classrooms ||--o{ enrollments : "placements"
    students ||--o{ enrollments : "trajectory"
    classrooms ||--o{ classroom_teacher : ""
    users ||--o{ classroom_teacher : "teaches"

    schools ||--o{ families : "family units"
    schools ||--o{ guardians : "guardian accounts"
    families ||--o{ students : "siblings"

    students ||--o{ student_guardian : ""
    guardians ||--o{ student_guardian : "guardian of"

    students ||--o{ student_payer : ""
    payers ||--o{ student_payer : ""
    guardians |o--o{ payers : "may be a"

    grades {
        bigint id PK
        bigint school_id FK
        string name "Infant, Year 1, Year 2…"
        int position "promotion ladder order"
        int child_per_educator "ratio, e.g. 8 = 1:8"
        decimal default_tuition "nullable"
    }
    classrooms {
        bigint id PK
        bigint school_id FK
        bigint grade_id FK
        string name "SweetPea, Year 1 – Room A"
        string year "2026/2027"
        int capacity "nullable"
        bool is_archived
    }
    students {
        bigint id PK
        bigint school_id FK
        bigint family_id FK "nullable — the family unit"
        string first_name
        string last_name
        date dob "nullable"
        string invite_code "unique, guardian self-link"
        string status "active/waitlisted/graduated/withdrawn"
    }
    families {
        bigint id PK
        bigint school_id FK
        string name "The Okoye Family"
        string address "nullable"
    }
    enrollments {
        bigint id PK
        bigint student_id FK
        bigint classroom_id FK
        date started_on
        date ended_on "null = current"
        string end_reason "promoted/graduated/withdrawn/transferred"
    }
    guardians {
        bigint id PK
        bigint school_id FK
        string first_name
        string last_name
        string email "unique, login (own guard)"
        datetime email_verified_at "nullable"
        string password
    }
    student_guardian {
        bigint id PK
        bigint student_id FK
        bigint guardian_id FK "the guardian"
        string relationship "mum/dad/grandparent…"
        bool is_primary
    }
    payers {
        bigint id PK
        bigint school_id FK
        string type "guardian/employer/agency/other"
        bigint guardian_id FK "nullable — set when payer is a guardian"
        string name
    }
    student_payer {
        bigint id PK
        bigint student_id FK
        bigint payer_id FK
        decimal share_percentage "sum per student = 100"
        bool is_primary
    }
    classroom_teacher {
        bigint id PK
        bigint classroom_id FK
        bigint user_id FK
    }
```

## Tables

### `schools` — tenant root (the multi-school seam)
One row today; every domain table FKs to it so multi-school is a later switch, not a rewrite.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| name | string | "WODI Community Child Care Centre" |
| slug | string, unique | |
| timezone | string | e.g. `America/Toronto` (matters for attendance/billing dates) |
| license_no | string, null | ministry licence |
| address / phone / email | string, null | |
| timestamps | | |

### `grades` — the level ladder (age-group + progression + tuition anchor)
Admin-named and **ordered**. Doubles as age-group (ratios) and the promotion ladder.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| name | string | "Infant", "Year 1", "Year 2" |
| position | int | ladder order; promotion walks this up |
| child_per_educator | int, null | ratio (8 = 1:8; from CWELCC) |
| min_age_months / max_age_months | int, null | age band (placement hints) |
| default_tuition | decimal(10,2), null | anchor when creating tuition plans |
| description | text, null | |
| timestamps | | |

Unique `(school_id, name)` · index `(school_id, position)`.

### `classrooms` — the room/group (operational unit)
Where teachers, feed, chat, roster, and daily reports live. Belongs to a **grade** + a **year**.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| grade_id | bigint FK | the level |
| name | string | "SweetPea", "Year 1 – Room A" |
| year | string | "2026/2027" |
| color / banner | string, null | UI |
| capacity | int, null | max students → occupancy |
| is_archived | bool, default false | past years kept, not deleted |
| timestamps · soft deletes | | |

Index `(school_id, year, is_archived)`, `(grade_id)`.

### `classroom_teacher` — pivot (classroom ↔ staff user)
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| classroom_id | bigint FK | |
| user_id | bigint FK | staff (teacher). Roles handled in the Admin phase |
| timestamps | | |

Unique `(classroom_id, user_id)`. A room may have several teachers.

### `families` — the family unit (billing + grouping)
The **authoritative family boundary.** An admin assigns each student to one family; siblings share it. Deliberately *not* derived from the guardian graph — a guardian shared between two children would wrongly merge unrelated families (connected-components over-groups). Guardianship (`student_guardian`) stays independent and crosses families freely.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| name | string | "The Okoye Family" |
| address | string, null | shared family address |
| phone / email | string, null | primary family contact |
| notes | text, null | |
| timestamps · soft deletes | | |

A student's family = its `family_id`; the family's adults = the union of its students' guardians. Billing can target the family (one family invoice for all siblings) — see Phase 3.

### `students` — the child record
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| family_id | bigint FK, null | the family unit (siblings share it); **assigned, not derived** |
| first_name / last_name | string | |
| preferred_name | string, null | |
| dob | date, null | |
| gender | string, null | |
| photo_path | string, null | |
| invite_code | string, null, unique | guardian self-links via this |
| status | string | `active` / `waitlisted` / `graduated` / `withdrawn` / `archived` |
| notes | text, null | |
| timestamps · soft deletes | | |

**Current classroom is derived** from the open enrollment (`ended_on IS NULL`) — no denormalized column. We can add a cached `current_classroom_id` later if query load demands it.

### `guardians` — the family account (own auth guard)
A parent self-registers here (own table + guard, kept apart from staff). They verify email, then redeem a student's `invite_code` to link to the child via `student_guardian`.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| first_name / last_name | string | |
| email | string, unique | login |
| email_verified_at | datetime, null | self-service verify flow |
| phone | string, null | |
| password | string | |
| remember_token | string, null | |
| timestamps · soft deletes | | |

### `student_guardian` — pivot (student ↔ guardian)
Flow: a parent self-registers → a `guardians` row; they redeem the student's `invite_code` → a row here links them to the child. Two parents redeem the same code → two rows. Siblings → the same guardian appears on multiple students.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| student_id | bigint FK | |
| guardian_id | bigint FK | the linked `guardians` row |
| relationship | string | `mum` / `dad` / `grandparent` / `guardian` … |
| is_primary | bool | |
| timestamps | | |

Unique `(student_id, guardian_id)`. Admin-entered people with **no login** (e.g. a grandparent only for pickup) are **emergency contacts** (Phase 5), not guardians.

### `payers` — a paying party (distinct from guardian)
Billing responsibility ≠ family. A payer may be a guardian, or an external employer / subsidy agency.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| type | string | `guardian` / `employer` / `agency` / `other` |
| guardian_id | bigint FK, null | set when the payer *is* a guardian (their `guardians` row) |
| name | string | display (person or org) |
| email / phone | string, null | |
| timestamps · soft deletes | | |

### `student_payer` — pivot with split
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| student_id / payer_id | bigint FK | |
| share_percentage | decimal(5,2) | **sum per student must = 100** |
| is_primary | bool | |
| timestamps | | |

Unique `(student_id, payer_id)`. Rule enforced in the app: allocations total 100%.

### `enrollments` — student ↔ classroom over time (the trajectory)
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| student_id | bigint FK | |
| classroom_id | bigint FK | room carries grade + year |
| started_on | date | |
| ended_on | date, null | `null` = current placement |
| end_reason | string, null | `promoted` / `graduated` / `withdrawn` / `transferred` |
| timestamps | | |

Index `(student_id, ended_on)`, `(classroom_id)`.
**Promotion / graduation** = close the open enrollment (`end_reason = promoted`) and open a new one in the next grade's classroom for the new year. Top of the ladder → `graduated`, student exits.

---

## Decisions made here (flagged for your review)

1. **Guardians are their own table + auth guard** (`guardians`), separate from staff (`users`) and students. Parents self-register + verify + reset; staff are admin-provisioned (login only); `students` is a record table now with a guard reserved for later. One login screen with a `Parent | Staff` toggle routes to the right guard — see **Accounts & Auth**. Admin-entered, no-login people are **emergency contacts** (Phase 5).
2. **Payer is separate from guardian.** A guardian who pays gets a `payers` row (`type=guardian`, `guardian_id` set); agencies/employers are payers with no guardian. Keeps *family* and *who-owes-money* independent.
3. **No `current_classroom_id` on students** — derived from the open enrollment. Cache later only if needed.
4. **`grade` and `classroom` are two tables** (per your call) — ratios/tuition on the grade, teachers/feed/roster on the classroom.
5. **Ratio** as a single `child_per_educator` int on the grade.
6. **Family = an explicit `families` record, not a derived graph.** Each student carries `family_id` (siblings share it); it's admin-assigned because deriving family from the guardian graph over-groups — a guardian linked to two children would merge two otherwise-separate families, and an aunt linked to one child would leak onto her sibling. `student_guardian` stays the messy per-child link and crosses families freely. The family is also the natural target for a family invoice (Phase 3).

## Open questions before Phase 2
- `students.status` vs deriving status from enrollment — keep an explicit status column? (I've kept it; it simplifies waitlist/graduated queries.)
- Timezone: single school timezone on `schools` is enough, or per-classroom? (Assuming per-school.)

---

# Phase 2 — Attendance

One row per student per day: presence, the in/out clock, and a **billable flag** that Phase 3 (Billing) reads. Attendance never computes money — it decides *which days count*.

```mermaid
erDiagram
    schools ||--o{ holidays : "closure calendar"
    schools ||--o{ attendances : ""
    students ||--o{ attendances : "daily record"
    classrooms ||--o{ attendances : "sat in"
    users ||--o{ attendances : "recorded by"

    attendances {
        bigint id PK
        bigint school_id FK
        bigint student_id FK
        bigint classroom_id FK "room that day"
        date date
        string status "present/absent/half_day/full_day/civic_holiday"
        datetime check_in_at "nullable"
        datetime check_out_at "nullable"
        bool is_billable "billing hook reads this"
    }
    holidays {
        bigint id PK
        bigint school_id FK
        date date
        string name "Civic Holiday, Christmas…"
        bool is_billable "usually false"
    }
```

## Tables

### `attendances` — the daily record
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| student_id | bigint FK | |
| classroom_id | bigint FK | room the student sat in that day — **stored, not derived**, so history survives promotion |
| date | date | |
| status | string | `present` / `absent` / `half_day` / `full_day` / `civic_holiday` |
| reason | string, null | absence reason — `illness` / `vacation` / `appointment` / … (illness tracking) |
| check_in_at | datetime, null | |
| check_out_at | datetime, null | |
| check_in_photo_path | string, null | **drop-off snapshot** — proof the child was physically brought in (missing-child evidence) |
| check_out_photo_path | string, null | pickup snapshot — corroborates who left with the child |
| checked_in_by | bigint FK users, null | staff who marked it (staff-only) |
| checked_out_by | bigint FK users, null | staff who marked pickup |
| released_to | string, null | who collected the child (authorized pickup) |
| is_billable | bool, default true | set from status + holiday policy when the row is written; the billing hook consumes it verbatim |
| notes | text, null | |
| timestamps | | |

Unique `(student_id, date)` — one record per child per day. Index `(school_id, date, status)` and `(classroom_id, date)` for the daily room roster.

### `holidays` — school closure calendar
Days the school is closed. When attendance is generated for a holiday, students get `civic_holiday` and `is_billable = false` — unless the closure is a contracted billed day.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| date | date | |
| name | string | "Civic Holiday", "Christmas Day" |
| is_billable | bool, default false | some contracted closures are still billed |
| timestamps | | |

Unique `(school_id, date)`.

## How it feeds billing (the hook, not the math)
- Each `attendances` row carries `is_billable` + `status`. Phase 3 rolls these into **billable days**: `full_day` / `present` = 1, `half_day` = ½, `absent` / `civic_holiday` = 0 (policy-adjustable).
- **CWELCC doesn't live here.** The $22/day parent-fee cap is applied at **invoice time** (Phase 3) against the billable days this phase produces — attendance stays money-free.
- `holidays` + `is_billable` are the only closure inputs billing needs; everything else is a sum over attendance.

## Decisions made here (flagged for your review)
1. **One row per student per day** (`unique(student_id, date)`), not an event log. A single check-in/out pair covers daycare reality; a multi-event table is a later addition only if kiosks need re-entry.
2. **`classroom_id` stored on each row**, not derived — so a promoted student's past attendance still points at the room they were actually in.
3. **`is_billable` is a stored flag**, set when the row is written. Billing reads it as-is; the money math (rates, CWELCC cap) stays in Phase 3.
4. **Half-day is a `status`**, not an AM/PM session table. Add a `session` (am/pm) column later only if split-day billing needs it.
5. **Staff-only marking, with a drop-off photo.** Attendance is recorded by staff (`checked_in_by` → `users`), not a guardian self-check-in. Each check-in can carry a `check_in_photo_path` — a picture taken at drop-off proving the child was actually brought in. In a missing-child scenario the row + `check_in_at` + photo answer "was the kid dropped off at all, and did someone leave with them?" (Photo is a stored file path for now; can graduate to a media table later.)

## Open questions before Phase 3
- **Absent-but-billed:** do contracted spots bill on an absence? (Modeled as `is_billable` per row so policy can go either way — need the default.)
- **Multiple in/out events** in one day (child leaves for an appointment and returns) — assuming no for now, single pair.

---

# Phase 3 — Billing

The big one. Four moving parts: a **price book** (what things cost), **subsidies** (incl. the CWELCC daily cap), **invoices + lines** (the resolved bill), and **payments**. Money math lives here; attendance (Phase 2) and split payers (Phase 1) feed in.

```mermaid
erDiagram
    grades ||--o{ fee_schedules : "tuition for"
    fee_schedules ||--o{ student_fees : "assigned"
    students ||--o{ student_fees : "on plan"
    students ||--o{ student_subsidies : "receives"
    subsidies ||--o{ student_subsidies : ""
    payers ||--o{ invoices : "billed to"
    invoices ||--o{ invoice_lines : ""
    students ||--o{ invoice_lines : "charge for"
    payers ||--o{ payments : "pays"
    payments ||--o{ payment_invoice : ""
    invoices ||--o{ payment_invoice : "settled by"

    fee_schedules {
        bigint id PK
        bigint school_id FK
        bigint grade_id FK "nullable"
        string name "Full-day Infant"
        string billing_cycle "monthly/weekly/daily"
        string day_type "full_day/half_day"
        decimal amount
        bool cwelcc_eligible
    }
    subsidies {
        bigint id PK
        bigint school_id FK
        string name "CWELCC, City subsidy"
        string kind "fixed/percentage/daily_cap"
        decimal amount "nullable"
        decimal percentage "nullable"
        decimal daily_cap "nullable — CWELCC = 22.00"
    }
    invoices {
        bigint id PK
        bigint school_id FK
        bigint payer_id FK
        string number
        date period_start
        date period_end
        date due_on
        decimal subtotal
        decimal subsidy
        decimal total
        decimal amount_paid
        string status "draft/issued/partial/paid/overdue/void"
    }
    invoice_lines {
        bigint id PK
        bigint invoice_id FK
        bigint student_id FK "nullable"
        string type "tuition/one_off/subsidy/discount/late_fee/tax"
        string description
        decimal quantity "e.g. billable days"
        decimal unit_amount
        decimal amount "signed — subsidies negative"
    }
    payments {
        bigint id PK
        bigint school_id FK
        bigint payer_id FK
        decimal amount
        string method "cash/card/bank/cheque/eft/subsidy"
        date paid_on
    }
```

## Tables

### `fee_schedules` — recurring tuition price book
Anchored on a grade; a student picks one up via `student_fees`.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| grade_id | bigint FK, null | default rate for the grade |
| name | string | "Full-day Infant" |
| billing_cycle | string | `monthly` / `weekly` / `daily` |
| day_type | string | `full_day` / `half_day` |
| amount | decimal(10,2) | rate per cycle |
| cwelcc_eligible | bool | whether the CWELCC cap applies |
| is_active | bool | |
| timestamps · soft deletes | | |

### `fee_items` — one-off charge catalog
Registration, materials, late-pickup, meals. Placed onto an invoice as a `one_off` line.

| column | type | notes |
|---|---|---|
| id · school_id | | |
| name | string | "Registration fee" |
| default_amount | decimal(10,2) | |
| taxable | bool | |
| timestamps | | |

### `student_fees` — student ↔ fee_schedule over time
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| student_id / fee_schedule_id | bigint FK | |
| started_on / ended_on | date · date, null | `null` = current |
| override_amount | decimal(10,2), null | per-student rate override |
| timestamps | | |

Index `(student_id, ended_on)`.

### `subsidies` — funding that reduces what a payer owes
CWELCC is one row: `kind=daily_cap`, `daily_cap=22.00` (the $22/day parent-fee cap).

| column | type | notes |
|---|---|---|
| id · school_id | | |
| name | string | "CWELCC", "City of Toronto subsidy" |
| kind | string | `fixed` / `percentage` / `daily_cap` |
| amount | decimal(10,2), null | for `fixed` |
| percentage | decimal(5,2), null | for `percentage` |
| daily_cap | decimal(10,2), null | for `daily_cap` (CWELCC = 22.00) |
| timestamps | | |

### `student_subsidies` — subsidy assignment
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| student_id / subsidy_id | bigint FK | |
| payer_id | bigint FK, null | whose portion it reduces (usually the guardian payer) |
| started_on / ended_on | date · date, null | |
| timestamps | | |

### `invoices` — the resolved bill (addressed to a payer)
One invoice per **payer** per period. A "family invoice" is just the case where the family is the sole payer.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| payer_id | bigint FK | who owes |
| number | string, unique | human ref |
| period_start / period_end | date | billing window |
| issued_on / due_on | date, null | |
| subtotal | decimal(10,2) | sum of charge lines |
| subsidy | decimal(10,2) | sum of subsidy reductions |
| discount / tax | decimal(10,2), default 0 | |
| total | decimal(10,2) | what the payer owes |
| amount_paid | decimal(10,2), default 0 | maintained from payments |
| status | string | `draft` / `issued` / `partial` / `paid` / `overdue` / `void` |
| notes | text, null | |
| timestamps · soft deletes | | |

Index `(payer_id, status)`, `(school_id, period_start)`.

### `invoice_lines` — immutable line items
Resolved numbers frozen at issue time — later plan/price changes never rewrite history. Subsidies (incl. CWELCC) are **negative lines**.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| invoice_id | bigint FK | |
| student_id | bigint FK, null | which child the charge is for |
| type | string | `tuition` / `one_off` / `subsidy` / `discount` / `late_fee` / `tax` |
| description | string | |
| quantity | decimal(6,2) | e.g. billable days (from Phase 2) |
| unit_amount | decimal(10,2) | |
| amount | decimal(10,2) | signed — subsidy/discount negative |
| timestamps | | |

### `payments` — money received
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| payer_id | bigint FK | |
| amount | decimal(10,2) | |
| method | string | `cash` / `card` / `bank` / `cheque` / `eft` / `subsidy` |
| reference | string, null | txn / cheque no. |
| paid_on | date | |
| notes | text, null | |
| timestamps | | |

### `payment_invoice` — allocation pivot
One payment can settle several invoices (and partials).

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| payment_id / invoice_id | bigint FK | |
| amount | decimal(10,2) | portion applied to this invoice |

Unique `(payment_id, invoice_id)`. `invoices.amount_paid` = sum of its allocations → drives `partial` / `paid`.

## How it all connects
- **Attendance → billable days.** Daily-cycle tuition = `amount × billable_days` (from Phase 2's `is_billable`). Monthly-cycle = flat, but per-day subsidies/CWELCC still read billable days.
- **Split payers.** For each student, each charge is split across the student's payers by `student_payer.share_percentage`; each payer's invoice gets their share as its own line. Sole payer → one clean family bill.
- **CWELCC cap.** For a `cwelcc_eligible` plan, the parent payer's tuition is capped at `daily_cap × billable_days`; the reduction is a negative `subsidy` line, and the funded remainder lands in `subsidy`.
- **Immutability.** Everything resolves to `invoice_lines` at issue time; changing a `fee_schedule` next month never alters a past invoice.

## Decisions made here (flagged for your review)
1. **Invoices are addressed to a `payer`, not the family** — split payers make per-payer the only consistent unit; a "family invoice" is the sole-payer case.
2. **Subsidies (incl. CWELCC) are negative invoice lines**, computed at issue time and frozen — not live joins. History stays truthful.
3. **CWELCC = a `daily_cap` subsidy** (`daily_cap=22.00`), applied against billable days, not a special column.
4. **Recurring tuition via `fee_schedules`** anchored on grade, with a per-student `override_amount`.
5. **Payments allocate via `payment_invoice`** — one payment can cover multiple/partial invoices; `amount_paid` is derived.

## Open questions before Phase 4
- **Tax:** Ontario childcare is largely GST/HST-exempt — keep the `tax` seam but default 0? (Assuming yes.)
- **Proration** for mid-period enrolment / withdrawal — line-level quantity, or a separate proration rule?
- **Late fees / overdue** — auto-generated on `due_on` pass, or manual?
- **Invoice generation** — monthly batch job vs. on-demand; confirm the cycle.

---

# Phase 4 — Care

The child's day as parents see it: the activity log (meals, naps, diapers, activities, mood), incidents with sign-off, photos, and the classroom feed. **The "daily report" is derived** — a student + a date over these tables — not a stored row.

```mermaid
erDiagram
    students ||--o{ care_logs : "daily events"
    users ||--o{ care_logs : "recorded by"
    care_logs ||--o{ care_media : "attached"
    students ||--o{ care_media : "photos"
    students ||--o{ incidents : ""
    guardians ||--o{ incidents : "acknowledges"
    classrooms ||--o{ posts : "feed"
    users ||--o{ posts : "author"
    posts ||--o{ post_media : ""

    care_logs {
        bigint id PK
        bigint school_id FK
        bigint student_id FK
        bigint classroom_id FK "room that day"
        string type "meal/nap/diaper/activity/mood/note/health"
        datetime logged_at
        datetime ended_at "nullable — naps/activities"
        string note "nullable"
        json details "type-specific"
        bigint recorded_by FK
    }
    incidents {
        bigint id PK
        bigint school_id FK
        bigint student_id FK
        datetime occurred_at
        string type "injury/illness/behaviour/other"
        string severity "minor/moderate/serious"
        string description
        bigint reported_by FK
        bigint acknowledged_by FK "guardian, nullable"
        datetime acknowledged_at "nullable"
    }
    care_media {
        bigint id PK
        bigint student_id FK "nullable — class-wide"
        bigint classroom_id FK
        bigint care_log_id FK "nullable"
        string type "image/video"
        string path
        datetime taken_at "nullable"
    }
    posts {
        bigint id PK
        bigint school_id FK
        bigint classroom_id FK "nullable — school-wide"
        bigint author_id FK
        string body
        datetime published_at "nullable — draft"
    }
```

## Tables

### `care_logs` — the unified daily event log
One table for every routine event; `type` + `details` (JSON) carry the specifics. Keeps the day's feed a single ordered query.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| classroom_id | bigint FK | room that day — stamped, survives promotion |
| type | string | `meal` / `nap` / `diaper` / `bathroom` / `activity` / `mood` / `note` / `health` |
| logged_at | datetime | when it happened (start, for durations) |
| ended_at | datetime, null | naps / activities with a duration |
| note | string, null | free text |
| details | json, null | meal `{food, amount}`, diaper `{kind}`, mood `{label}` … |
| recorded_by | bigint FK users | staff |
| timestamps | | |

Index `(student_id, logged_at)`, `(classroom_id, logged_at)`.

### `incidents` — injury / illness / behaviour, with guardian sign-off
Separate from `care_logs` because they carry severity and a **guardian acknowledgement** and never auto-clear.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| classroom_id | bigint FK, null | |
| occurred_at | datetime | |
| type | string | `injury` / `illness` / `behaviour` / `other` |
| severity | string | `minor` / `moderate` / `serious` |
| location / body_part | string, null | where on premises / for injuries |
| description | text | |
| action_taken | text, null | |
| reported_by | bigint FK users | |
| acknowledged_by | bigint FK guardians, null | which guardian signed off |
| acknowledged_at | datetime, null | portal nags until set |
| timestamps · soft deletes | | |

### `care_media` — photos / videos (the child's stream)
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| student_id | bigint FK, null | `null` = class-wide |
| classroom_id | bigint FK | |
| care_log_id | bigint FK, null | optionally attached to an event |
| type | string | `image` / `video` |
| path | string | file path (media table later) |
| caption | string, null | |
| taken_at | datetime, null | |
| uploaded_by | bigint FK users | |
| timestamps | | |

### `posts` — classroom / school feed
Broadcast updates to a room's families (or school-wide). The social feed, distinct from the per-child log.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| classroom_id | bigint FK, null | `null` = school-wide |
| author_id | bigint FK users | |
| body | text | |
| published_at | datetime, null | draft until set |
| timestamps · soft deletes | | |

### `post_media` — feed attachments
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| post_id | bigint FK | |
| type | string | `image` / `video` |
| path | string | |
| position | int | ordering |

## Decisions made here (flagged for your review)
1. **Unified `care_logs`** (typed + JSON `details`) over a table-per-event — the parent-facing feed is one ordered query; type-specific fields live in `details`.
2. **Daily report is derived** (student + date over logs / media / incidents), not a stored table. An explicit publish/acknowledge header is an open question below.
3. **Incidents are their own table**, not a `care_logs` type — they need severity + guardian acknowledgement and must never silently clear.
4. **`classroom_id` stamped on care rows** (like attendance) so history survives promotion.
5. **Media as file paths** for now, consistent with the attendance photos; graduate to a media table later.

## Open questions before Phase 5
- **Explicit daily-report header** with publish + acknowledge ("parent received today's report"), or leave the report derived?
- **Class photo tagging** multiple children — per-student rows now, a tag pivot later?
- **Messaging / chat** (staff ↔ guardians, carried from preschool) — its own **Phase 6 — Communication**, confirm it's out of Care scope.

---

# Phase 5 — Admin, Staff & Records

The staff guard fleshed out: accounts, roles/permissions, plus the login-less records (emergency contacts) and documents that hang off students and staff.

```mermaid
erDiagram
    schools ||--o{ users : "staff"
    users ||--o{ role_user : ""
    roles ||--o{ role_user : ""
    roles ||--o{ permission_role : ""
    permissions ||--o{ permission_role : ""
    users ||--o{ staff_profiles : ""
    students ||--o{ emergency_contacts : "pickup/emergency"

    users {
        bigint id PK
        bigint school_id FK
        string name
        string email "unique, staff guard"
        string password
        bool is_super "owner-only, seeded"
        bool is_active
    }
    roles {
        bigint id PK
        bigint school_id FK
        string name "Admin/Teacher/Finance"
        bool is_system "seeded, locked"
    }
    permissions {
        bigint id PK
        string name "students.manage, billing.view"
        string group "UI grouping"
    }
    emergency_contacts {
        bigint id PK
        bigint student_id FK
        string name
        string relationship
        string phone
        bool is_authorized_pickup
        int priority "call order"
    }
    documents {
        bigint id PK
        string documentable_type "student/guardian/user"
        bigint documentable_id
        string type "immunization/consent/licence/certification"
        string path
        date expires_on "nullable"
    }
```

## Tables

### `users` — staff accounts (the `staff` guard)
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| name | string | |
| email | string, unique | login |
| email_verified_at | datetime, null | |
| password | string | |
| is_super | bool, default false | **owner-only, seeded** — never grantable in the app (carried from preschool) |
| is_active | bool, default true | deactivate rather than delete |
| remember_token | string, null | |
| timestamps · soft deletes | | |

### `roles` · `permissions` · `permission_role` · `role_user` — RBAC
Permission **catalog is seeded** (global list); roles are per-school and assign a subset.

| table | columns | notes |
|---|---|---|
| `roles` | id, school_id, name, is_system, timestamps | `is_system` roles are locked (not editable/deletable) |
| `permissions` | id, name (unique), group, timestamps | e.g. `students.manage`, `billing.view` |
| `permission_role` | role_id, permission_id | unique pair |
| `role_user` | role_id, user_id | unique pair |

### `staff_profiles` — HR fields on a staff user
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| user_id | bigint FK, unique | |
| position | string, null | "Lead Teacher", "Director" |
| hired_on | date, null | |
| qualifications | text, null | |
| certifications | json, null | first-aid / ECE with expiry dates |
| timestamps | | |

### `emergency_contacts` — login-less pickup / emergency people
The **admin-entered, no-login** people from Phase 1 (a grandparent only for pickup). Distinct from `guardians`, which authenticate.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| name | string | |
| relationship | string | grandparent / neighbour … |
| phone | string | |
| email | string, null | |
| is_authorized_pickup | bool | may collect the child |
| priority | int | call order |
| notes | text, null | |
| timestamps | | |

### `documents` — polymorphic file records with expiry
One table for immunization records, signed consent, school licences, and staff certifications.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| documentable_type / documentable_id | | polymorphic → student / guardian / user |
| name | string | |
| type | string | `immunization` / `consent` / `licence` / `certification` / `other` |
| path | string | file path (media table later) |
| expires_on | date, null | portal flags expiring docs |
| uploaded_by | bigint FK users | |
| timestamps · soft deletes | | |

## Decisions made here (flagged for your review)
1. **`is_super` is owner-only + seeded**, never grantable in-app (exactly as preschool).
2. **RBAC = roles + permissions**; the permission catalog is a seeded global list, roles are per-school, and `is_system` roles are locked.
3. **Emergency contacts are login-less records**, distinct from `guardians` — resolves the Phase 1 "admin-entered pickup person" seam.
4. **`documents` is polymorphic** (student / guardian / staff) with `expires_on` — one table covers immunization, consent, and staff certs.
5. **Deactivate (`is_active`), don't delete, staff** — preserves the `recorded_by` audit trail on attendance / care logs.

## Open questions before Phase 6
- **Audit log** (who changed what, when) — a dedicated table now, or later?
- **Document storage** — keep file paths, or introduce a real media table (the same question raised for attendance photos + care media; worth deciding once, globally).
- **Permission catalog scope** — confirm global seeded catalog + per-school roles (assumed).

---

# Phase 6 — Communication

Two-way messaging (staff ↔ guardians) and one-way announcements. Both audiences live in **separate guarded tables** (`users`, `guardians`), so senders and participants are **polymorphic**.

```mermaid
erDiagram
    conversations ||--o{ conversation_participants : ""
    conversations ||--o{ messages : ""
    messages ||--o{ message_attachments : ""
    classrooms ||--o{ announcements : "nullable"
    announcements ||--o{ announcement_reads : ""

    conversations {
        bigint id PK
        bigint school_id FK
        bigint classroom_id FK "nullable"
        string type "direct/group"
        string subject "nullable"
        bigint created_by
    }
    conversation_participants {
        bigint id PK
        bigint conversation_id FK
        string participant_type "user/guardian"
        bigint participant_id
        datetime last_read_at "nullable — unread counts"
    }
    messages {
        bigint id PK
        bigint conversation_id FK
        string sender_type "user/guardian"
        bigint sender_id
        string body
    }
    announcements {
        bigint id PK
        bigint school_id FK
        bigint classroom_id FK "nullable — school-wide"
        bigint author_id FK
        string title
        string body
        datetime published_at "nullable"
    }
```

## Tables

### `conversations` — a thread
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| classroom_id | bigint FK, null | context, if room-scoped |
| type | string | `direct` / `group` |
| subject | string, null | |
| created_by | bigint FK users | |
| timestamps · soft deletes | | |

### `conversation_participants` — polymorphic membership
Both staff and guardians join; `last_read_at` drives unread counts (the preschool refactor).

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| conversation_id | bigint FK | |
| participant_type / participant_id | | polymorphic → `user` / `guardian` |
| last_read_at | datetime, null | |
| timestamps | | |

Unique `(conversation_id, participant_type, participant_id)`.

### `messages` — polymorphic sender
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| conversation_id | bigint FK | |
| sender_type / sender_id | | polymorphic → `user` / `guardian` |
| body | text | |
| timestamps · soft deletes | | |

Index `(conversation_id, created_at)`.

### `message_attachments` — files on a message
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| message_id | bigint FK | |
| type | string | `image` / `video` / `file` |
| path | string | |

### `announcements` — one-way broadcast
Distinct from conversations: no replies, with read receipts.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| classroom_id | bigint FK, null | `null` = school-wide |
| author_id | bigint FK users | |
| title / body | string · text | |
| published_at | datetime, null | draft until set |
| timestamps · soft deletes | | |

### `announcement_reads` — receipts
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| announcement_id | bigint FK | |
| guardian_id | bigint FK | |
| read_at | datetime | |

Unique `(announcement_id, guardian_id)`.

## Decisions made here (flagged for your review)
1. **Senders + participants are polymorphic** (`user` / `guardian`) — the only way to thread two separately-guarded audiences into one conversation.
2. **`conversation_participants.last_read_at`** drives unread counts (carried from the preschool refactor), rather than per-message read rows.
3. **Announcements are separate from conversations** — one-way broadcast + read receipts vs. two-way threads; different shapes, different tables.
4. **Attachments as file paths** — pending the global media-table decision (Phase 5 open question).

## Open questions
- **Realtime** (Laravel Reverb / websockets) vs. polling — infra, not schema; doesn't change these tables.
- **Notifications** (email / push digests of unread messages + announcements) — Laravel's `notifications` table, added when we wire delivery.

---

# Phase 7 — Staff Ops / HR

The staff working life: who's **scheduled**, who **actually worked** (timekeeping → payroll), **time-off**, and **training/certs** with expiry. Extends the `users` (staff) + `staff_profiles` from Phase 5.

```mermaid
erDiagram
    users ||--o{ shifts : "scheduled"
    users ||--o{ time_entries : "worked"
    shifts |o--o{ time_entries : "fulfilled by"
    users ||--o{ time_off_requests : ""
    users ||--o{ training_records : ""

    shifts {
        bigint id PK
        bigint school_id FK
        bigint user_id FK
        bigint classroom_id FK "nullable"
        datetime starts_at
        datetime ends_at
        string status "scheduled/published/cancelled"
    }
    time_entries {
        bigint id PK
        bigint school_id FK
        bigint user_id FK
        bigint shift_id FK "nullable"
        datetime clock_in_at
        datetime clock_out_at "nullable"
        decimal hours "computed at clock-out"
        bigint approved_by FK "nullable"
    }
    time_off_requests {
        bigint id PK
        bigint user_id FK
        string type "vacation/sick/personal/unpaid"
        date starts_on
        date ends_on
        string status "pending/approved/denied"
    }
    training_records {
        bigint id PK
        bigint user_id FK
        string name "First Aid, CPR…"
        date completed_on "nullable"
        date expires_on "nullable — renewal reminder"
    }
```

## Tables

### `shifts` — scheduled work
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / user_id | bigint FK | the staff member |
| classroom_id | bigint FK, null | room assignment |
| starts_at / ends_at | datetime | |
| break_minutes | int, default 0 | |
| status | string | `scheduled` / `published` / `cancelled` |
| notes | text, null | |
| timestamps | | |

Index `(school_id, starts_at)`, `(user_id, starts_at)`.

### `time_entries` — timekeeping (feeds payroll)
Actual clock in/out. **Payroll-ready reporting is a sum over approved entries** per staff per period — a report, not a table (no pay runs stored yet).

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / user_id | bigint FK | |
| shift_id | bigint FK, null | the scheduled shift it fulfils, if any |
| clock_in_at | datetime | |
| clock_out_at | datetime, null | `null` = still clocked in |
| break_minutes | int, default 0 | |
| hours | decimal(5,2), null | worked hours, stored at clock-out |
| source | string | `manual` / `kiosk` |
| approved_by | bigint FK users, null | payroll sign-off |
| approved_at | datetime, null | |
| notes | text, null | |
| timestamps | | |

Index `(user_id, clock_in_at)`.

### `time_off_requests`
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / user_id | bigint FK | |
| type | string | `vacation` / `sick` / `personal` / `unpaid` |
| starts_on / ends_on | date | |
| status | string | `pending` / `approved` / `denied` |
| reason | text, null | |
| reviewed_by | bigint FK users, null | |
| reviewed_at | datetime, null | |
| timestamps | | |

### `training_records` — training + certifications with expiry
The structured cert store — `expires_on` drives renewal reminders. Supersedes the loose `staff_profiles.certifications` JSON for anything needing tracking/reminders.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / user_id | bigint FK | |
| name | string | "First Aid & CPR", "Food Handler" |
| provider | string, null | |
| completed_on | date, null | |
| expires_on | date, null | renewal reminder |
| certificate_path | string, null | |
| notes | text, null | |
| timestamps | | |

## Decisions
1. **Scheduling (`shifts`) and timekeeping (`time_entries`) are separate** — planned vs. actual. `time_entries` (approved) feeds payroll.
2. **Payroll is a report**, not stored pay runs — sum of approved hours per staff per pay period.
3. **`training_records` is the authoritative cert store** (expiry → reminders); `staff_profiles.certifications` JSON stays for misc/unstructured.

---

# Phase 8 — Finance

The money-**out** side and year-end. Income already lives in Billing (`invoices`/`payments`); this adds **expenses** and the **reports/receipts** on top.

```mermaid
erDiagram
    expense_categories ||--o{ expenses : "categorises"
    students ||--o{ tax_receipts : "receipt names child"
    payers ||--o{ tax_receipts : "claimant"

    expenses {
        bigint id PK
        bigint school_id FK
        bigint expense_category_id FK "nullable"
        string description
        decimal amount
        date spent_on
        bigint recorded_by FK
    }
    tax_receipts {
        bigint id PK
        bigint school_id FK
        bigint student_id FK
        bigint payer_id FK "nullable — claimant"
        int year
        decimal amount "total paid that year"
        string number "unique"
    }
```

## Tables

### `expense_categories`
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| name | string | Rent, Salaries, Supplies, Food, Utilities |
| timestamps | | |

### `expenses`
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| expense_category_id | bigint FK, null | |
| vendor | string, null | |
| description | string | |
| amount | decimal(10,2) | |
| spent_on | date | |
| method | string, null | `cash` / `card` / `bank` / `cheque` |
| receipt_path | string, null | |
| recorded_by | bigint FK users | |
| notes | text, null | |
| timestamps | | |

Index `(school_id, spent_on)`.

### `tax_receipts` — annual childcare receipts
A receipt **names the child** (CRA needs child + amount + provider). Computed from a year's `payments` per child; stored as a record so it can be reissued.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| student_id | bigint FK | the child |
| payer_id | bigint FK, null | who paid / claims |
| year | int | tax year |
| amount | decimal(10,2) | total paid that year |
| number | string, unique | receipt number |
| issued_on | date, null | |
| path | string, null | generated PDF |
| notes | text, null | |
| timestamps | | |

Index `(student_id, year)`.

## Decisions
1. **Income vs. expenses split by source** — income is Billing (`payments`), expenses live here. A **financial report (P&L) is derived**: payments − expenses over a period.
2. **Tax receipts are stored records** (number + amount + PDF), computed from the year's payments per child — so they reissue identically.

---

# Phase 9 — Health & Medical

The structured medical subset staff need at a glance — allergies, meds, immunizations — plus immunization **due dates that drive reminders**. General consent/health *forms* stay as `documents`; this is the queryable part.

```mermaid
erDiagram
    students ||--|| health_profiles : ""
    students ||--o{ allergies : ""
    students ||--o{ medications : ""
    students ||--o{ immunizations : ""

    health_profiles {
        bigint id PK
        bigint student_id FK "unique"
        string doctor_name "nullable"
        string health_card_no "nullable"
        string blood_type "nullable"
        text dietary_restrictions "nullable"
    }
    allergies {
        bigint id PK
        bigint student_id FK
        string allergen
        string severity "mild/moderate/severe/anaphylaxis"
    }
    medications {
        bigint id PK
        bigint student_id FK
        string name
        string dosage "nullable"
        bool requires_admin "staff gives on-site"
    }
    immunizations {
        bigint id PK
        bigint student_id FK
        string vaccine
        date administered_on "nullable"
        date due_on "nullable — reminders"
        string status "up_to_date/due/overdue/exempt"
    }
```

## Tables

### `health_profiles` — one per student
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id | bigint FK | |
| student_id | bigint FK, unique | |
| doctor_name / doctor_phone | string, null | |
| health_card_no | string, null | |
| blood_type | string, null | |
| dietary_restrictions | text, null | |
| notes | text, null | |
| timestamps | | |

### `allergies`
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| allergen | string | peanuts, dairy… |
| severity | string | `mild` / `moderate` / `severe` / `anaphylaxis` |
| reaction | string, null | |
| notes | text, null | |
| timestamps | | |

### `medications`
Each **dose given** is logged via `care_logs` (`type=health`) — or a dedicated `medication_administrations` (MAR) table later if a full log is needed.

| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| name | string | |
| dosage | string, null | |
| schedule | string, null | "twice daily", "as needed" |
| route | string, null | oral / topical … |
| starts_on / ends_on | date, null | |
| requires_admin | bool, default false | staff administers on-site |
| prescriber | string, null | |
| notes | text, null | |
| timestamps | | |

### `immunizations` — with due-date tracking
| column | type | notes |
|---|---|---|
| id | bigint PK | |
| school_id / student_id | bigint FK | |
| vaccine | string | MMR, DTaP… |
| dose | string, null | "1st", "booster" |
| administered_on | date, null | |
| due_on | date, null | **drives due/overdue reminders** |
| status | string | `up_to_date` / `due` / `overdue` / `exempt` |
| notes | text, null | |
| timestamps | | |

Index `(school_id, due_on)`, `(student_id)`.

## Decisions
1. **Allergies / medications / immunizations are structured tables** (queryable, reportable, reminder-able), not free-text on the student.
2. **General health forms/consents stay as `documents`**; only the actionable medical data is structured here.
3. **Med administration reuses `care_logs`** (`type=health`) for now — a full MAR table is a later add.

---

# Reminders & notifications (cross-cutting)

No new schema — reminders ride on **existing due-date columns** + scheduled jobs + Laravel's `notifications` table:

| reminder | driven by |
|---|---|
| Invoice due / overdue (#6) | `invoices.due_on`, `status` |
| Immunization due / overdue (#12) | `immunizations.due_on`, `status` |
| Training/cert renewal | `training_records.expires_on` |
| Document expiry | `documents.expires_on` |

A daily scheduled job scans these dates and dispatches notifications (email/in-app) to the right guardian or staff.

---

# Design complete — nine phases

| phase | domain | core tables |
|---|---|---|
| **1** | Backbone | schools, grades, classrooms, students, families, guardians, enrollments, payers + pivots |
| **2** | Attendance | attendances, holidays |
| **3** | Billing | fee_schedules, fee_items, student_fees, subsidies, invoices, invoice_lines, payments (+ pivots) |
| **4** | Care | care_logs, incidents, care_media, posts, post_media |
| **5** | Admin | users, roles, permissions, staff_profiles, emergency_contacts, documents |
| **6** | Communication | conversations, conversation_participants, messages, announcements (+ reads/attachments) |
| **7** | Staff Ops / HR | shifts, time_entries, time_off_requests, training_records |
| **8** | Finance | expense_categories, expenses, tax_receipts |
| **9** | Health & Medical | health_profiles, allergies, medications, immunizations |

**Cross-cutting decision — settled:** files (photos, documents, attachments) are stored as **plain path columns** on their owning tables — **no shared `media` table**.

**Deliberately dropped (by request):** a digital **form builder** and **electronic signatures**. Structured intake data lives in real tables (health/medical, emergency contacts); free-form documents live in `documents`.