# 0001. Staff attendance tables in the school database

**Date**: 2026-10-26
**Updated**: 2026-10-30
**Status**: Accepted
**Amended**: 2026-10-30 (spec 0006: retake edits the same current row; Remove deletes it; `storage_type` added)

## Summary

This decision puts staff attendance in seven new MySQL tables inside the current school database. You run the SQL on each school; the app does not create tables at boot. Later screens read and write these columns only. The existing `staff` table stays as it is.

## Requirements

**User stories**:
- As the person who applies schema, I want one copyable SQL script so every school database can hold staff attendance rows.
- As a later check in builder, I want unique keys so two gates cannot double clock the same staff on the same Lagos date, and so a shared phone cannot map to two current enrollments.
- As a later admin, I want location names and late flags frozen on the day row so a later settings edit or location rename does not rewrite history.

**Acceptance criteria** (the contract, each criterion is IDed and independently checkable):
- **AC-1**: After the published SQL is run on a school database, these tables exist: `staff_attendance_settings`, `staff_attendance_locations`, `staff_attendance_enrollments`, `staff_attendance_templates`, `staff_attendance_tokens`, `staff_attendance_days`, `staff_attendance_attempts`.
- **AC-2**: None of those tables include a `school_id` column. Tenant isolation is the selected school database.
- **AC-3**: The `staff` table is unchanged: no new columns and no altered columns.
- **AC-4**: When a phone is valid, `phone_normalized` is `CHAR(13)` digits. Attempts also store `phone_raw`. Invalid phones leave `phone_normalized` NULL on the attempt.
- **AC-5**: `UNIQUE (current_phone)` allows at most one current enrollment per normalized phone. A second current enroll with that phone fails that unique key. The clash is inserted into attempts with `outcome = fail`, `reason_code = duplicate_phone`, and `staff_id` NULL.
- **AC-6**: `UNIQUE (current_staff_id)` allows at most one current enrollment per staff member.
- **AC-7**: An enrollment row requires `consent_at` (PHP Lagos now at enroll and at retake). Live rows use `status = current`. Retake UPDATEs that same current row (new photo key, phone copied from `staff.phone_number`, `tested_at` NULL, `replaced_at` and replaced actor set, `storage_type` set). It does not INSERT a second row. Remove DELETEs the row. `storage_type` is `VARCHAR(32) NOT NULL DEFAULT 'backblaze'`. At most one live row per `staff_id`.
- **AC-8**: Failed attempt photos are referenced only by `attempts.photo_object_key` (`VARCHAR(255)`, nullable). Cleanup eligibility is `outcome = fail` AND `photo_object_key` IS NOT NULL AND `created_at` is before PHP `Africa/Lagos` now minus 7 days. No column stores a public URL.
- **AC-9**: `UNIQUE (staff_id, attend_date)` on days. `check_in_at` is `DATETIME`. `attend_date` is the `Africa/Lagos` calendar date written at insert. A second successful insert for that pair fails; that attempt is logged as `outcome = fail`, `reason_code = already_in`. A retry of a token that already produced a day row returns that success, not `already_in`.
- **AC-10**: `check_out_at` is nullable. Check out never inserts a day row. It updates the latest open day for that `staff_id` (`check_out_at` IS NULL), which may be a previous Lagos date. No open row logs `fail` / `no_checkin`. An already closed row logs `fail` / `already_out`.
- **AC-11**: Locations use `is_active`. Daily rows store `location_id` and a `location_name` snapshot taken at check in.
- **AC-12**: `is_late` is stored at insert by comparing `check_in_at` to the then current `settings.late_after`. Later settings edits do not rewrite old days.
- **AC-13**: Tokens store `token_hash` `CHAR(64)` (SHA-256 hex of a 64 character hex raw token). The raw token is never stored. `expires_at` and `consumed_at` exist.
- **AC-14**: Settings is one row with `id = 1`, inserted by the published SQL with the seed defaults below.
- **AC-15**: The biometric blob lives only in `staff_attendance_templates`, at most one row per `enrollment_id`, `MEDIUMBLOB NOT NULL` when the row exists. A missing row means no template yet.
- **AC-16**: `enrollments.tested_at` is a nullable `DATETIME`.
- **AC-17**: Every new table uses InnoDB, `utf8mb4`, `utf8mb4_unicode_ci`.

## Decision

**Chosen option**: Option 1: Dedicated `staff_attendance_*` tables in the current school database.

Seven InnoDB tables, logical ids with no `FOREIGN KEY` constraints, unique keys for current phone, current staff, token hash, and one day row per staff per Lagos date. You apply the SQL yourself on each school. `/develop` may later copy that SQL into a repo `.sql` file; this spec does not add PHP that creates tables.

## Feature design

**Data model sketch**:

All tables live in the school database already selected by `$_SESSION['db_name']`. Ids that point at `staff.staff_id` or `staff_attendance_locations.location_id` are logical (no MySQL foreign keys). `staff_id` columns use `INT UNSIGNED`, the same shape as `staff.staff_id`. Writers supply `DATETIME` values already in `Africa/Lagos` from PHP. Do not use the `TIMESTAMP` type. Do not call MySQL `NOW()` (session `time_zone` on this host is not guaranteed to be Lagos).

Shadow unique pattern: when `status = 'current'`, writers set `current_staff_id = staff_id` and `current_phone = phone_normalized`. When `status = 'replaced'`, writers set both shadow columns to NULL. MySQL unique keys allow many NULLs. A current row with a NULL shadow, or a shadow that does not equal `staff_id` / `phone_normalized`, is invalid and must not be used for check in.

Published SQL (copy and run per school):

```sql
CREATE TABLE IF NOT EXISTS `staff_attendance_settings` (
  `id` TINYINT UNSIGNED NOT NULL,
  `face_match_threshold` DECIMAL(8,4) NOT NULL DEFAULT 0.5000,
  `max_gps_accuracy_m` INT NOT NULL DEFAULT 50,
  `allow_checkout` TINYINT(1) NOT NULL DEFAULT 1,
  `min_checkout_minutes` INT NOT NULL DEFAULT 30,
  `late_after` TIME NOT NULL DEFAULT '08:00:00',
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `staff_attendance_settings` (
  `id`, `face_match_threshold`, `max_gps_accuracy_m`, `allow_checkout`,
  `min_checkout_minutes`, `late_after`, `updated_at`
) VALUES (1, 0.5000, 50, 1, 30, '08:00:00', '2026-10-26 00:00:00')
ON DUPLICATE KEY UPDATE `id` = `id`;

CREATE TABLE IF NOT EXISTS `staff_attendance_locations` (
  `location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `radius_m` INT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`location_id`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_enrollments` (
  `enrollment_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` INT UNSIGNED NOT NULL,
  `phone_normalized` CHAR(13) NOT NULL,
  `status` VARCHAR(16) NOT NULL,
  `current_staff_id` INT UNSIGNED DEFAULT NULL,
  `current_phone` CHAR(13) DEFAULT NULL,
  `consent_at` DATETIME NOT NULL,
  `photo_object_key` VARCHAR(255) NOT NULL,
  `storage_type` VARCHAR(32) NOT NULL DEFAULT 'backblaze',
  `tested_at` DATETIME DEFAULT NULL,
  `enrolled_at` DATETIME NOT NULL,
  `enrolled_actor_type` VARCHAR(16) NOT NULL,
  `enrolled_actor_id` INT UNSIGNED DEFAULT NULL,
  `replaced_at` DATETIME DEFAULT NULL,
  `replaced_actor_type` VARCHAR(16) DEFAULT NULL,
  `replaced_actor_id` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`enrollment_id`),
  UNIQUE KEY `uq_current_staff` (`current_staff_id`),
  UNIQUE KEY `uq_current_phone` (`current_phone`),
  KEY `idx_staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_templates` (
  `enrollment_id` INT UNSIGNED NOT NULL,
  `template` MEDIUMBLOB NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`enrollment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_tokens` (
  `token_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `token_hash` CHAR(64) NOT NULL,
  `phone_normalized` CHAR(13) NOT NULL,
  `staff_id` INT UNSIGNED NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `accuracy_m` INT NOT NULL,
  `location_id` INT UNSIGNED NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `consumed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`token_id`),
  UNIQUE KEY `uq_token_hash` (`token_hash`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_days` (
  `attendance_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` INT UNSIGNED NOT NULL,
  `attend_date` DATE NOT NULL,
  `check_in_at` DATETIME NOT NULL,
  `check_out_at` DATETIME DEFAULT NULL,
  `location_id` INT UNSIGNED NOT NULL,
  `location_name` VARCHAR(100) NOT NULL,
  `face_score` DECIMAL(8,4) NOT NULL,
  `is_late` TINYINT(1) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`attendance_id`),
  UNIQUE KEY `uq_staff_date` (`staff_id`, `attend_date`),
  KEY `idx_attend_date` (`attend_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_attempts` (
  `attempt_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone_raw` VARCHAR(32) NOT NULL,
  `phone_normalized` CHAR(13) DEFAULT NULL,
  `staff_id` INT UNSIGNED DEFAULT NULL,
  `outcome` VARCHAR(32) NOT NULL,
  `reason_code` VARCHAR(64) NOT NULL,
  `latitude` DECIMAL(10,8) DEFAULT NULL,
  `longitude` DECIMAL(11,8) DEFAULT NULL,
  `accuracy_m` INT DEFAULT NULL,
  `location_id` INT UNSIGNED DEFAULT NULL,
  `face_score` DECIMAL(8,4) DEFAULT NULL,
  `photo_object_key` VARCHAR(255) DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(512) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`attempt_id`),
  KEY `idx_phone_created` (`phone_normalized`, `created_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_photo_object_key` (`photo_object_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

Seed defaults (settings `id = 1`): `face_match_threshold` 0.5000, `max_gps_accuracy_m` 50, `allow_checkout` 1, `min_checkout_minutes` 30, `late_after` 08:00:00.

Allowed values:
- `status`: `current`, `replaced`
- `enrolled_actor_type` / `replaced_actor_type`: `staff`, `admin`, `backend`, `director`
- `outcome`: `success_in`, `success_out`, `fail`
- Starting `reason_code`: `ok_in`, `ok_out`, `no_match`, `low_score`, `no_face`, `many_faces`, `bad_gps`, `outside_location`, `duplicate_phone`, `not_enrolled`, `already_in`, `already_out`, `checkout_off`, `too_soon`, `token_bad`, `token_expired`, `token_used`, `rate_limited`, `invalid_phone`, `inactive_staff`, `no_checkin`

Relationships (logical, one to many unless noted):
- One settings row per school database (`id = 1`).
- One location to many tokens, days, and attempts.
- One staff member to at most one enrollment row (`status = current` while live). Remove deletes it. Retake edits it.
- One enrollment to zero or one template row (primary key `enrollment_id`).
- One staff member to many day rows; at most one day row per `attend_date`.
- Attempts optionally point at one staff member and one location.

**State transitions**:
- Enrollment: (none) → INSERT `current` (photo key plus staff phone) → UPDATE the same row on retake (`replaced_at` set, `tested_at` cleared) → DELETE on Remove. `tested_at` may be set while `current`.
- Location: `is_active = 1` → `is_active = 0` (deactivate). No hard delete in this model.
- Token: created (unexpired, `consumed_at` NULL) → consumed (`consumed_at` set on the final face POST, success or fail) or expired (`expires_at` passed). About 10 minutes from `created_at` to `expires_at`. Compare expiry to PHP `Africa/Lagos` now.
- Day: inserted only by a successful check in consume (`check_out_at` NULL) → check out consume sets `check_out_at`. Never created by check out. Never inserted from a client supplied clock or late flag.
- Attempt: insert only. Every mint or consume try, success or fail, inserts one attempt. No updates.

**API surface**:

This spec has no HTTP. Later slices must treat the SQL and these actions as the contract.

| Action | Method | Key inputs | Key outputs | Auth | Key errors |
|---|---|---|---|---|---|
| Apply published SQL | DBA run | school database | 7 tables plus settings seed | you, on each school | table already exists (`IF NOT EXISTS` is safe) |
| Insert location | later admin write | name, latitude, longitude, radius_m | location_id | admin, director, backend | none at schema layer (radius 20 to 2000 is a later spec) |
| Insert current enrollment | later admin write | staff_id (from `staff.staff_id`), phone from `staff.phone_number`, photo_object_key, actor, storage_type | enrollment_id | admin, director, backend | duplicate `current_phone` or `current_staff_id` (also log attempt) |
| Replace enrollment | later admin write | same enrollment_id, new photo key, phone from `staff.phone_number`, actor | UPDATE the current row (`replaced_at` set) | admin, director, backend | unique clash on the new current phone |
| Remove enrollment | later admin write | staff_id | DELETE the current row | admin, director, backend | no current row |
| Update settings | later admin write | columns on `id = 1` only | same row | admin, director, backend | never INSERT a second settings row |
| Mint token | later public POST | phone, GPS (lat, lon, accuracy) | raw token to client; hash stored | public check in | invalid phone; duplicate_phone; not_enrolled; inactive_staff; bad_gps; outside_location; rate_limited |
| Consume token | later public POST | raw token, live face frame | check in: new day row; check out: updated day row; always an attempt row | public check in | token_bad; token_expired; token_used; no_match; already_in; already_out; checkout_off; too_soon; outside_location; inactive_staff |

**Value sourcing**:

| Action | Value produced / displayed | Source |
|---|---|---|
| Apply SQL | seven tables exist | published SQL in this spec |
| Apply SQL | settings seed row | INSERT in this spec |
| Any read or write | which school | `$_SESSION['db_name']` already selects the mysqli database; no column |
| Normalize phone | `phone_normalized` | same digit rules as `zap_normalize_ng_phone` in `app/admin/staff_list.php` (234 plus 10 digits); empty or invalid → NULL on attempts |
| Attempt typed input | `phone_raw` | public POST: the kiosk string. Admin enroll clash: the enroll form string. Trimmed, max 32 chars |
| Current unique phone | `current_phone` | copy of `phone_normalized` only while `status = current` |
| Current unique staff | `current_staff_id` | copy of `staff_id` only while `status = current` |
| Consent time | `consent_at` | server clock in `Africa/Lagos` at enroll and at retake (no checkbox) |
| Enrolled at | `enrolled_at` | server clock in `Africa/Lagos` when that enrollment row is first inserted (may equal `consent_at`) |
| Replaced at | `replaced_at` | server clock in `Africa/Lagos` when that same current row is retaken |
| Who enrolled | `enrolled_actor_type`, `enrolled_actor_id` | session of the admin, director, or backend user. `actor_type` is their role (`staff` only if that login is a staff row). For Universal admin (`table_name` `admin`) and other executive roles, `actor_id` is `executives.executive_id`. For staff it is `staff.staff_id`. NULL if none |
| Who replaced | `replaced_actor_type`, `replaced_actor_id` | same session rule as who enrolled, at replace time |
| Enrollment photo pointer | `photo_object_key` | opaque `VARCHAR(255)` from the later private photos spec; never a public URL |
| Face template | `templates.template` | later face matching spec writes the blob; absence of row means none yet |
| Tested fact | `tested_at` | server clock when an admin test match succeeds (enrollment spec) |
| Calendar day | `attend_date` | `DATE` of `check_in_at` in `Africa/Lagos`, written by PHP at insert, never from the client |
| Check in moment | `check_in_at` | PHP server clock in `Africa/Lagos` at successful consume, never from the client |
| Check out moment | `check_out_at` | PHP server clock in `Africa/Lagos` at successful check out consume |
| Late flag | `is_late` | 1 if TIME(`check_in_at`) is after `settings.late_after` (`id = 1`) at insert time, else 0. PHP computes this; the client cannot send it |
| Location snapshot | `location_name` | `locations.name` for `token.location_id` at consume; `location_id` copied from the token |
| Face score on a day | `face_score` | later face matching spec on the consume frame |
| Raw token | 64 hex chars to the client | `bin2hex(random_bytes(32))` |
| Stored token | `token_hash` | `hash('sha256', $rawHex)` in PHP, 64 hex chars |
| Token created / consumed | `created_at`, `consumed_at` | PHP `Africa/Lagos` now at mint and at consume |
| Token expiry | `expires_at` | `created_at` plus 10 minutes. Expired when PHP `Africa/Lagos` now is after `expires_at` |
| Failed photo pointer | `attempts.photo_object_key` | later photos spec on a fail consume. Delete when `outcome = fail` AND key set AND `created_at` is before PHP Lagos now minus 7 days |
| Mint GPS | token latitude, longitude, accuracy_m | client GPS on the mint POST |
| Consume GPS | attempt GPS | copy from the token row, not a new client point |
| GPS accuracy check | `bad_gps` | mint: `accuracy_m` missing or `accuracy_m > max_gps_accuracy_m` (settings `id = 1`). Check this before the geofence. Greater than means fail; equal to max is allowed |
| Matched place | `location_id` on mint | server: token GPS point inside an `is_active = 1` location radius. Client does not send `location_id` or `staff_id` |
| Mint staff | `tokens.staff_id` | the `staff_id` of the single current enrollment whose `phone_normalized` matches |
| Attempt staff | attempts.`staff_id` | set only when exactly one current enrollment matches `phone_normalized`; else NULL (including duplicate_phone) |
| Attempt IP | `ip` | `$_SERVER['REMOTE_ADDR']` (or the first forwarded hop the later check in spec names), on that mint, consume, or admin enroll clash |
| Attempt user agent | `user_agent` | `$_SERVER['HTTP_USER_AGENT']`, truncated to 512, on that same request |
| Attempt outcome | `outcome` | `success_in` with a new day row; `success_out` with a check out update; `fail` otherwise. Required on every attempt |
| Success reasons | `ok_in`, `ok_out` | paired with `success_in` / `success_out` |
| Rate limit counts | 5 per phone per 10 minutes, 30 per IP per 10 minutes | COUNT all attempt outcomes. Phone bucket uses `phone_normalized` when set, else skip that bucket. Invalid phones (NULL normalized) count on IP only |
| Check out target row | which day to update | latest row for `token.staff_id` with `check_out_at` IS NULL, any `attend_date`. Not “today” |
| Check out allowed | `checkout_off` | `settings.allow_checkout = 0` (`id = 1`) |
| Check out wait | `too_soon` | minutes from `check_in_at` to PHP now are less than or equal to `settings.min_checkout_minutes`. 30 means the 31st minute is the first allowed |
| Second check out | `already_out` | that staff has no open day (`check_out_at` IS NULL) because the latest day is already closed |
| Consume direction | check in vs check out | if `allow_checkout = 0`, always check in. If `allow_checkout = 1` and an open day exists for `token.staff_id`, this consume is check out; else check in |
| Inactive staff | fail `inactive_staff` | `staff.activation` is not `activated` while a current enrollment still exists |
| In flight replace | fail `no_match` | consume loads the current enrollment for `token.staff_id`; if none, set `consumed_at` and fail |
| Deactivated place after mint | fail `outside_location` | consume requires `locations.is_active = 1` for `token.location_id` |
| Token already used | `token_used` | `consumed_at` IS NOT NULL. If a day row was already written for that success, return that success instead of `already_in` |
| Admin duplicate clash | attempt row | `fail` / `duplicate_phone`, `staff_id` NULL, `phone_raw` from the enroll form, IP and user agent from that admin request |
| Settings knobs | late, GPS max, checkout | always `SELECT` `staff_attendance_settings` WHERE `id = 1`. Never a second row |

**Key invariants**:
- Writers keep shadow uniques in sync with `status`. A current row with NULL `current_phone` or a mismatched copy must not be used for lookup or check in.
- Check in identity is `enrollments.phone_normalized`. That value is copied from `staff.phone_number` at enroll and retake. Editing staff does not move attendance identity until the next enroll or retake.
- Mint computes `staff_id` and `location_id` on the server. The client sends phone and GPS only.
- A token is minted only after exactly one current enrollment matches the phone, GPS accuracy is allowed, and GPS is inside an active location. `tokens.staff_id` and `tokens.location_id` are therefore NOT NULL.
- Consume uses the token’s stored GPS and `location_id`. It does not accept a new client point. It rechecks that location is still active and that a current enrollment still exists for that `staff_id`. It uses the current template (or photo) of that current enrollment, never a replaced one.
- Day rows are written only inside consume, by PHP. Unique `(staff_id, attend_date)` is the double clock lock. The loser INSERT fails; log `already_in`. A retry of an already successful token returns the existing success.
- `attend_date` must equal the Lagos date of `check_in_at`. PHP writes both; the schema cannot see timezone.
- Check out updates the latest open day for that staff, which may be yesterday. It never inserts.
- Settings writers only `UPDATE` `id = 1`.
- Radius 20 to 2000 meters is enforced by the later locations spec, not a MySQL CHECK (cPanel MySQL versions differ).
- Public check in must never `SELECT` `staff_attendance_templates.template`. The matching engine reads it server side only.
- There is no void or correction column. A wrong successful match owns that staff date until a later spec adds a correction path.

**Security model**:
- Personal data in scope: phone, GPS, IP, user agent, face photo keys, face template blob. Treat as biometric plus contact data. Product bar is NDPR style consent and short retention of fail photos. This is not a completed legal review.
- Tenant: selected school database only. No copies in the owner session database.
- Writers: admin, director, or backend for settings, locations, enrollments, templates. Public check in (no staff login) for tokens, days, attempts. Reports are read only.
- Readers of template, fail photo keys, GPS, IP, user agent: admin, director, backend only, through later signed photo views and reports. Public success may show a masked name, time, and location name only.
- Tokens: store the hash only. Raw token lives in the client for up to 10 minutes.
- Audit: every mint, consume, and failed enroll clash inserts into `staff_attendance_attempts`, including successes (`success_in`, `success_out`). Do not skip the log.

**Configuration required**:
None for this spec. Photo storage credentials and face matching endpoints belong to later specs. Token hashing uses SHA-256 with no pepper.

**Critical test scenarios** (each maps to an acceptance criterion in ## Requirements):
- Happy path: run the SQL on one school database; `SHOW TABLES` lists all seven; settings `id = 1` is present; `staff` column list is unchanged, verifies **AC-1**, **AC-3**, **AC-14**, **AC-17**
- Failure case: insert two current enrollments with the same `current_phone`; the second INSERT fails. Insert two day rows with the same `(staff_id, attend_date)`; the second INSERT fails, verifies **AC-5**, **AC-9**
- Auth/permission: confirm no `school_id` column and that a query with the wrong mysqli database cannot see another school’s rows, verifies **AC-2**
- Retention: an attempt with `outcome = fail` and `photo_object_key` set is selectable for delete when `created_at` is before now minus 7 days; no public URL column exists, verifies **AC-8**
- Replace: retake UPDATEs the same current row; Remove DELETEs it so a later enroll can use that staff id and phone again, verifies **AC-6**, **AC-7**

## Build plan

Tracer Bullet for a schema only feature: prove the SQL on one real school database, then apply it everywhere. No PHP table creator.

1. [x] Run the published SQL on one school database and confirm seven tables, InnoDB, `utf8mb4`, and the settings seed row, satisfies **AC-1**, **AC-14**, **AC-17**
2. [x] Prove unique keys with failing second inserts (current phone, current staff, staff plus date) and prove a replaced enrollment frees those uniques, satisfies **AC-5**, **AC-6**, **AC-7**, **AC-9**
3. [x] Confirm `staff` is unchanged and that no new table has `school_id`, satisfies **AC-2**, **AC-3**
4. [x] Confirm column types for phone, token hash, template table, `tested_at`, `check_out_at`, `is_late`, and `photo_object_key`, satisfies **AC-4**, **AC-8**, **AC-10**, **AC-11**, **AC-12**, **AC-13**, **AC-15**, **AC-16**
5. [x] Run the same SQL on the remaining school databases, satisfies **AC-1**, **AC-14**, **AC-17**

## Consequences

**Positive**:
- Later slices share one frozen column contract.
- Unique keys, not PHP alone, stop double clock and double current enroll under two busy gates.
- History stays readable after a location deactivate or a settings edit.
- Biometric blobs are not on the enrollment row, so a casual `SELECT *` there cannot leak them.

**Negative / tradeoffs**:
- A school that never runs the SQL will break every later slice until you run it. The app will not self heal.
- Blocking a second current enrollment on the same phone means two people who share a number cannot both clock in. The clash is logged, not enrolled.
- Shadow unique columns can drift if a writer updates `status` without updating them.
- `is_late` is stored, so it can disagree with current settings on purpose.
- No `FOREIGN KEY` means orphan `location_id` or `staff_id` values are possible if a row is hard deleted outside this model.
- Attempts have no `(ip, created_at)` index. The 30 per IP rate limit may scan until a later slice adds one.

**Neutral**:
- Student attendance tables are untouched.
- `/develop` may later add a `.sql` file that copies this script.
- Face threshold 0.5000 is a placeholder scale until the face matching spec names the real range.

## Follow-up

- [ ] Locations, settings, private photos, face matching, enrollment, staff check in, attendance report, and live board each still need their own spec.
- [ ] Private photos spec owns object key layout, signed view, and the 7 day fail photo delete job (`outcome = fail`, key set, `created_at` before now minus 7 days). Token GPS, IP, and user agent are kept; only fail photos expire.
- [ ] Face matching spec owns writing `staff_attendance_templates` and the real meaning of `face_match_threshold`.
- [ ] Enrollment spec owns consent wording (no version column), setting `tested_at`, and logging `duplicate_phone` attempts on a unique clash.
- [ ] Staff check in spec owns minting and consuming tokens, inactive staff, in flight replace, token retry success, and rate limits. Add `attempts(ip, created_at)` there if the IP limit is slow.
- [ ] No correction or void column in this build. A later spec would be needed to undo a wrong match on a staff date.
- [ ] `/develop` may copy the published SQL into a repo `.sql` file when a later slice wants it in tree.
- [ ] NDPR or other legal review of biometric storage is not done here.

## Rationale

Reasoning and options: see [rationale.md](rationale.md).