# Verify: Attendance data model · spec 0001 · updated 2026-10-26
_Steps derived from spec 0001 acceptance criteria. `/check verify` runs these; `/test` locks the durable ones._

This slice is schema only. There is no HTTP surface. Confirm the live school database (the one selected by `$_SESSION['db_name']` for this host, default `zeonglob_zap_demo_sec`) and that every other school database in `zeonglob_schools.schools.database_name` has the same tables. Proof inserts must run in a transaction and roll back.

## UI / manual
None. This slice has no screen.

## Commands

- [x] On the current school database, `SHOW TABLES LIKE 'staff_attendance_%'` lists `staff_attendance_settings`, `staff_attendance_locations`, `staff_attendance_enrollments`, `staff_attendance_templates`, `staff_attendance_tokens`, `staff_attendance_days`, `staff_attendance_attempts` → AC-1
- [x] `SHOW TABLE STATUS` for those seven tables: `ENGINE = InnoDB` and `Collation = utf8mb4_unicode_ci` → AC-17
- [x] `SELECT id, face_match_threshold, max_gps_accuracy_m, allow_checkout, min_checkout_minutes, late_after FROM staff_attendance_settings WHERE id = 1` returns one row: `0.5000`, `50`, `1`, `30`, `08:00:00` → AC-14
- [x] `SELECT COUNT(*) FROM staff_attendance_settings` is `1`. A second `INSERT` with `id = 1` does not add a row (`ON DUPLICATE KEY UPDATE id = id`) → AC-14
- [x] `SHOW COLUMNS` on each of the seven tables has no `school_id` column. A query issued while mysqli is on a different school database cannot see this school’s attendance rows → AC-2
- [x] `SHOW COLUMNS FROM staff` matches the pre existing staff columns (`staff_id`, `school_id`, name fields, `phone_number`, `activation`, and the rest). No attendance column was added → AC-3
- [x] `enrollments.phone_normalized` is `char(13) NOT NULL`. `attempts.phone_normalized` is `char(13) NULL`. `attempts.phone_raw` is `varchar(32) NOT NULL` → AC-4
- [x] Insert a current enrollment with `current_phone` set. A second current insert with the same `current_phone` fails with MySQL `1062`. Spec later writers log that clash as `outcome = fail`, `reason_code = duplicate_phone`, `staff_id` NULL → AC-5
- [x] Insert a current enrollment with `current_staff_id` set. A second current insert with the same `current_staff_id` fails with MySQL `1062` → AC-6
- [x] A current enrollment requires `consent_at` (`NOT NULL`). `status` accepts `current` or `replaced`. Setting `status = replaced` and nulling `current_staff_id` and `current_phone` lets a different staff take that phone as current → AC-7
- [x] `attempts.photo_object_key` is `varchar(255) NULL`. No column on attempts is a public URL. A fail row with a key and `created_at` older than PHP `Africa/Lagos` now minus 7 days is selectable for later delete → AC-8
- [x] `UNIQUE (staff_id, attend_date)` on days. `check_in_at` is `datetime NOT NULL`. A second insert for the same pair fails with `1062`. `attend_date` is a `date` column (PHP writes the Lagos calendar date at insert; this slice does not write days) → AC-9
- [x] `check_out_at` is `datetime NULL`. Schema allows an update of an existing day and has no insert trigger for check out → AC-10
- [x] Locations have `is_active tinyint(1) NOT NULL`. Days have `location_id` and `location_name varchar(100) NOT NULL` → AC-11
- [x] Days have `is_late tinyint(1) NOT NULL`. Settings have `late_after time NOT NULL`. There is no DB trigger that rewrites old `is_late` when settings change → AC-12
- [x] `tokens.token_hash` is `char(64) NOT NULL` with `UNIQUE uq_token_hash`. `expires_at` is `datetime NOT NULL`. `consumed_at` is `datetime NULL`. No column stores a raw token → AC-13
- [x] `staff_attendance_templates.template` is `mediumblob NOT NULL`. Primary key is `enrollment_id`. A missing row is allowed (no enrollment row requires a template row) → AC-15
- [x] `enrollments.tested_at` is `datetime NULL` → AC-16
- [x] Run `docs/specs/0001-attendance-data-model/schema.sql` on every remaining school in `zeonglob_schools.schools.database_name`. `CREATE TABLE IF NOT EXISTS` is safe. After that, every school has the seven tables, InnoDB, `utf8mb4_unicode_ci`, and settings `id = 1` → AC-1, AC-14, AC-17

## Value sourcing

Schema this slice owns (confirm the column exists and the named source is the only one):

- [x] Seven tables exist because the published SQL in `schema.sql` was run, not because PHP created them at boot → AC-1
- [x] Settings seed row comes from the `INSERT ... VALUES (1, 0.5000, 50, 1, 30, '08:00:00', '2026-10-26 00:00:00')` in that SQL → AC-14
- [x] Which school is the selected mysqli database (`$_SESSION['db_name']`). No `school_id` on the new tables → AC-2
- [x] `phone_normalized` is stored as `CHAR(13)`. Digit rules come from `zap_normalize_ng_phone` in `app/admin/staff_list.php` (later writers). Invalid phones leave attempts.`phone_normalized` NULL → AC-4
- [x] `phone_raw` is `VARCHAR(32)` for the typed string (kiosk or enroll form) → AC-4
- [x] `current_phone` is a nullable `CHAR(13)` copy of `phone_normalized` only while `status = current` → AC-5, AC-7
- [x] `current_staff_id` is a nullable `INT UNSIGNED` copy of `staff_id` only while `status = current` → AC-6, AC-7
- [x] `consent_at`, `enrolled_at`, `replaced_at`, `tested_at` are `DATETIME` (PHP `Africa/Lagos` later). None are `TIMESTAMP` → AC-7, AC-16
- [x] `enrolled_actor_type` / `replaced_actor_type` are `VARCHAR(16)`; actor ids are nullable `INT UNSIGNED` → AC-7
- [x] Enrollment `photo_object_key` is `VARCHAR(255) NOT NULL` (opaque key from the later photos spec, never a public URL) → AC-8
- [x] `templates.template` is `MEDIUMBLOB NOT NULL`; absence of the row means no template yet → AC-15
- [x] `attend_date` is `DATE`; `check_in_at` and `check_out_at` are `DATETIME`; PHP will write Lagos clock, never the client, never MySQL `NOW()` → AC-9, AC-10
- [x] `is_late` is stored `TINYINT(1)` at insert from `TIME(check_in_at)` vs settings.`late_after` for `id = 1` → AC-12
- [x] `location_name` snapshot is `VARCHAR(100) NOT NULL` beside `location_id` → AC-11
- [x] Day `face_score` is `DECIMAL(8,4) NOT NULL` (later face matching spec) → AC-12
- [x] Raw token is not stored. `token_hash` is `CHAR(64)` (`hash('sha256', $rawHex)` later) → AC-13
- [x] Token `created_at`, `expires_at` (`created_at` plus 10 minutes), `consumed_at` nullable → AC-13
- [x] Failed photo pointer is attempts.`photo_object_key` nullable; cleanup rule is `outcome = fail` AND key set AND `created_at` before Lagos now minus 7 days → AC-8
- [x] Token GPS columns (`latitude`, `longitude`, `accuracy_m`) are `NOT NULL`. Attempt GPS columns are nullable copies from the token later → AC-11
- [x] `tokens.staff_id` and `tokens.location_id` are `NOT NULL` (mint computes them on the server later) → AC-5, AC-11
- [x] Attempts.`staff_id` is nullable (NULL on `duplicate_phone` and when no single current enrollment matches) → AC-5
- [x] Attempts.`ip` is `VARCHAR(45)` nullable; `user_agent` is `VARCHAR(512)` nullable → AC-8
- [x] Attempts.`outcome` is `VARCHAR(32) NOT NULL`; `reason_code` is `VARCHAR(64) NOT NULL` → AC-5, AC-9
- [x] Settings knobs are always the `id = 1` row (`late_after`, `max_gps_accuracy_m`, `allow_checkout`, `min_checkout_minutes`, `face_match_threshold`) → AC-14

Behavioral sources owned by later slices (do not fail this verify if no PHP writer exists yet; `/check verify` on those features must exercise them):

- [ ] Phone normalize empty or invalid → attempts.`phone_normalized` NULL (staff check in / enrollment)
- [ ] Admin duplicate clash inserts an attempt `fail` / `duplicate_phone` with `staff_id` NULL (enrollment)
- [ ] Mint GPS vs `max_gps_accuracy_m`: greater than fails `bad_gps`; equal is allowed (staff check in)
- [ ] Mint location is server geofence of `is_active = 1` locations; client does not send `location_id` (staff check in)
- [ ] Consume copies GPS from the token, not a new client point (staff check in)
- [ ] Check out updates the latest open day (`check_out_at IS NULL`), which may be a previous Lagos date, and never inserts (staff check in)
- [ ] `allow_checkout = 0` → `checkout_off`; minutes from `check_in_at` ≤ `min_checkout_minutes` → `too_soon`; no open day → `already_out` (staff check in)
- [ ] Consume direction: open day plus `allow_checkout = 1` means check out, else check in (staff check in)
- [ ] `staff.activation` not `activated` with a current enrollment → `inactive_staff` (staff check in)
- [ ] Consume with no current enrollment for `token.staff_id` → `no_match` and `consumed_at` set (staff check in)
- [ ] Location deactivated after mint → `outside_location` (staff check in)
- [ ] Token with `consumed_at` set: if a day row exists for that success, return that success, not `already_in` (staff check in)
- [ ] Rate limits: 5 per phone per 10 minutes (skip phone bucket when normalized is NULL) and 30 per IP per 10 minutes, counting all attempt outcomes (staff check in)
- [ ] 7 day fail photo delete job (private photos)

## Acceptance-criteria coverage
- AC-1 covered by SHOW TABLES plus remaining school apply
- AC-2 covered by no `school_id` column plus wrong database isolation
- AC-3 covered by `SHOW COLUMNS FROM staff`
- AC-4 covered by phone column types
- AC-5 covered by `uq_current_phone` duplicate insert
- AC-6 covered by `uq_current_staff` duplicate insert
- AC-7 covered by `consent_at` plus replace then reenroll
- AC-8 covered by attempts photo key type, no URL column, 7 day select rule
- AC-9 covered by `uq_staff_date` duplicate insert
- AC-10 covered by nullable `check_out_at`
- AC-11 covered by `is_active` plus `location_name` snapshot columns
- AC-12 covered by stored `is_late` and settings `late_after`
- AC-13 covered by `token_hash char(64)`, `expires_at`, `consumed_at`
- AC-14 covered by settings seed `id = 1`
- AC-15 covered by templates `MEDIUMBLOB` primary key
- AC-16 covered by nullable `tested_at`
- AC-17 covered by InnoDB and `utf8mb4_unicode_ci`