# 0001 rationale: Staff attendance tables

## Context

> Premise note: The original scope line said two staff sharing a number can be recorded without guessing. This spec records the clash on an attempt row and refuses a second current enrollment. Two enrolled staff cannot share a phone. If you wanted both to remain enrolled, the unique key on `current_phone` would have to go.

> Premise note: You will run the SQL on every school. A missed school will not get tables at app boot. That is a known operational failure until the SQL is applied.

> Premise note: Face photos and templates are treated as biometric personal data with NDPR style consent and a 7 day window for fail photos. That is a product bar, not a completed legal review.

> Premise note: `is_late` is a stored flag computed at write time. Stored derived values usually go stale; here that staleness is the point, so yesterday does not move when settings change.

Staff attendance is new. The school app already has a `staff` list with `phone_number`, class attendance for learners, and an event clock for visitors. None of those hold a daily staff check in, a face enrollment, a geofence, or a fail photo with a retention window.

Each school already has its own MySQL database selected from the session. New rows must live there. Changing `staff` would collide with payroll, login, and the staff list. Mixing this into student attendance would mix two jobs on one screen family.

The public check in page has no staff login. Identity starts from a typed phone, then GPS, then a live face. Two gates at one school can POST at the same moment. The schema has to make “one success per staff per Lagos date” true even when PHP retries. It also has to store enough of a failed try for an admin to review, without keeping fail photos forever.

If this is not decided, every later slice invents its own columns. Phone uniqueness, late flags, and location history would disagree across screens.

## Options considered

### Option 1: Dedicated `staff_attendance_*` tables in the school database

New InnoDB tables next to `staff`, with unique keys for current phone, current staff, and one day row per date. Settings are one typed row. Templates sit in their own table. You apply `CREATE TABLE` yourself.

**Pros**:
- Leaves `staff` and student attendance alone.
- Unique keys hold under two busy gates.
- Tenant boundary is the database you already select.

**Cons**:
- Every school must receive the SQL; missed schools fail later slices.
- Seven tables is more than a few extra columns on `staff`.

### Option 2: Extend student attendance tables

Reuse class or subject attendance rows and add staff as another subject or a flag.

**Pros**:
- Fewer new names; some report habits already exist.

**Cons**:
- Those tables are about learners, class, subject, session, and term. Staff gate check in is none of those.
- A shared unique key story for phone and face would still need new tables.

### Option 3: Columns or JSON on `staff`

Add enrollment, last check in, and settings JSON onto `staff`.

**Pros**:
- One row per person; lookups by `staff_id` stay simple.

**Cons**:
- The scope forbids changing `staff`.
- No natural place for attempts, tokens, many locations, or replaced faces.
- Two gates updating one staff row would overwrite times without a day unique key.

### Option 4: PHP rules only, no unique keys

Create tables but leave uniqueness to application checks.

**Pros**:
- Simpler SQL; no shadow columns for partial uniques.

**Cons**:
- Two gates can both read “not yet in” and both insert.
- Shared phone handling becomes a guess under load.

## Rationale

Option 1 matches how this app already isolates schools: pick the database, then query. It does not fight `staff` or student attendance. Unique keys are the cheapest way to make one current phone, one current face per staff, and one success per Lagos date true when two kiosks POST together. Option 4 fails that race. Option 3 is blocked by the leave `staff` unchanged rule. Option 2 would tangle learner attendance with a gate loop that has GPS, liveness, and fail photos.

Shadow nullable columns exist because MySQL has no partial unique index. That is ugly, and it is still better than a seventh “current map” table after templates already used the extra table budget for the biometric blob.

Settings as typed columns plus a seed row means check in can read knobs before anyone opens a settings screen. The face threshold seed is a guess until the matcher spec names the scale; the column type still holds a precise number.

Deactivate via `is_active` is an explicit live or inactive status, not a hidden deleted row. Combined with a name snapshot on the day row, reports stay readable after a rename or a close.

Enrollment phone is the attendance identity because `staff.phone_number` can still be shared or edited. Blocking a second current enrollment is the rule you chose: the attempt log is where a shared staff phone is recorded without picking a person.

A generated unique column would stop shadow drift. This spec keeps the chosen shadow columns until a MySQL version is pinned. PHP must write clocks; MySQL `NOW()` is unsafe because the session zone is not guaranteed to be Lagos. Day rows exist only as the result of token consume so a public POST cannot send its own `check_in_at` or `is_late`.