ADR-0036: Authorized Pickup List — Closed Guardian List Data Model (issue #589)

Status: accepted Date: 2026-08-09 Deciders: @barateza Tags: [checkin, pickup, guardian, lgpd, v1, d1, offline]

Context

Pickup is currently a config enum only: pickupModeSchema = z.enum(["GUARDIAN_NAME", "PHONE_SUFFIX", "AUTHORIZED_LIST", "PIN"]) (packages/schemas/src/enums.ts), default PHONE_SUFFIX, stored per-church in church_config. The enum value AUTHORIZED_LIST exists but has no backing data model. Checkout validates only the HMAC-derived daily code (ADR-0026); pickupPerson/pickupPhoneSuffix are audit-only columns that the UI hardcodes to "" (app/src/app/pages/CheckOutPage.tsx). No pickup-mode field has ever been exercised.

Issue #589 (milestone v1.0, addition #1 per docs/research/licensing-business-model-2026.md §7.2) requires the authorized-pickup data model (closed guardian list) — a per-child closed list of adults authorized for pickup, checkout validation against the list, and CRUD UI. docs/research/authorized-pickup-2026.md provides the regulatory/market evidence (CFOC 9.2.4.8, LGPD art. 14, COPPA §312.5, NIST SP 800-63, Brightwheel/Procare/KidPass/MyKids/Prover). The design was settled by grilling (2026-08-09).

Decision

1. Data model — junction table authorized_pickups, not a JSON column

Add a D1 table authorized_pickups (child ↔ adult, closed list per child) instead of a JSON array on students:

CREATE TABLE IF NOT EXISTS authorized_pickups (
  id                  TEXT PRIMARY KEY,
  student_id          TEXT NOT NULL REFERENCES students(student_id),
  user_id             TEXT NOT NULL,            -- account-holding adult (RESPONSAVEL)
  guardian_id         TEXT,                     -- optional link to `guardians` when the adult is the child's responsável
  relationship        TEXT NOT NULL DEFAULT 'OUTRO'
    CHECK(relationship IN ('PAI','MAE','AVO','TIO','TIA','RESPONSAVEL_LEGAL','OUTRO')),
  status              TEXT NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE | INACTIVE (soft delete)
  created_by          TEXT NOT NULL,
  created_at          TEXT NOT NULL,
  updated_at          TEXT NOT NULL,
  deactivated_at      TEXT,
  deactivated_by      TEXT,
  deactivation_reason TEXT
);

Rationale: referential integrity, per-child queryability at checkout, per-row audit, per-row LGPD erasure (delete one row, not a blob), and the repo's existing junction precedent (guardians, class_assignments). Next migration file: 0027_authorized_pickups.sql.

2. Accounts are required — every list entry has a user_id

Every adult on the list has a user account (user_id NOT NULL). Adding an adult creates a basic account (minimal users row: name + phone, role RESPONSAVEL, no password at creation) and verifies the phone via OTP at creation — reusing the onboarding OTP machinery (onboardingService.ts: SHA-256 otp_hash, 6-digit code, 10-min expiry, 3-attempt cap). The adult can later claim the account through the parent portal.

• The guardians table (onboarding semantics: who registered the child, user_id NOT NULL, accented relationship CHECK) stays untouched in v1. Pickup is a permission, distinct from being the responsável.
• Rationale: the phone is the checkout identifier (last-4 match) and the future PIN-reset anchor; an admin-typed number that doesn't belong to the adult breaks legitimate pickup and weakens the gate. OTP is the market norm (Brightwheel email/SMS invite + verification code), defensible under LGPD art. 14 §5 "todos os esforços razoáveis… consideradas as tecnologias disponíveis", and proportionate at NIST IAL1 (self-asserted attributes; the physical pickup step — CFOC photo-ID culture — carries final identity verification).

3. The child's responsável is implicitly authorized

Onboarding approval already creates a users + guardians row for the responsável; it now also creates an ACTIVE authorized_pickups entry automatically. Otherwise a parent who was never manually added to the list would be blocked by the closed-list gate — a footgun. CFOC 9.2.4.8 treats legal guardians as documented at enrollment (explicit authorization is for other adults and noncustodial parents); 100% of surveyed market products auto-authorize parents. The admin can deactivate the entry.

4. Verification semantics in AUTHORIZED_LIST mode

The list is an additional gate layered on top of the daily code — the code (ADR-0026 HMAC, offline-capable) stays mandatory in every mode; AUTHORIZED_LIST adds list membership as a second condition.

• Checkout identifier: phone suffix (last 4 digits) matched against the entry's phone. Derived at checkout, never stored as a separate secret; the existing check_in_events.pickup_phone_suffix column remains the audit record of what was verified. The audit gains the matched list entry (pickupAdultId, pickupListVerified) so a release is traceable to a specific authorized person.
• Blocking: non-authorized attempts are hard-blocked with a distinct NOT_AUTHORIZED result + alert; EMERGENCY_CHECKOUT (admin-only) remains the override path. Failed attempts respect the existing checkout.max_attempts lockout.
• PII at the reception screen: the CHAMADOR sees name + relationship only — never the phone (Brightwheel "Approved Pickup sees no child info" principle).

5. Offline — best-effort, server re-validates on sync

An offline pickup against a stale local list (adult revoked since last sync) cannot be fully validated on-device. The list is treated as best-effort offline: the daily code remains the primary offline gate; membership is re-validated server-side on sync. Not chosen: blocking AUTHORIZED_LIST checkout when the list is stale (would break offline-first for the common case).

6. LGPD — reuse the existing parental consent

The list is treated as part of the already-consented scope: the existing parental-consent checkbox text is extended to cover the pickup list; no per-entry consent. Guardian name/phone/relationship are ordinary personal data (LGPD art. 5-I), not sensitive — no art. 11 regime. Deletions are soft (justification required, min 10 chars); LGPD erasure is a single-row + event-redaction operation.

7. UI scope (v1) and deferred items

• v1: ADMIN-only CRUD section in the child profile (StudentForm/StudentProfilePage); checkout prompt (phone suffix) + list display in CheckOutPage when mode = AUTHORIZED_LIST.
• Deferred to post-mvp: PIN mode (no pin_hash column in v1), explicit deny-list/isDenied (court order / custodial request), onboarding self-registration capture of the list, guardian photos (LGPD art. 5-II biometric regime if ever added).

Consequences

Positive

• The closed list becomes a real, queryable, auditable data model — the v1.0 scope line's addition #1 — with per-row LGPD erasure and soft-delete-with-justification matching the repo's rules.
• Checkout security improves in AUTHORIZED_LIST mode (list gate + code + lockout), and the audit becomes traceable to a specific authorized adult.
• guardians semantics are preserved; pickup permission is independently revocable without touching who-registered-what.

Negative

• Account creation (basic user + OTP) at list-add adds friction to admin CRUD; the OTP step verifies the phone, not the person — final identity verification remains the physical checkout step.
• Offline staleness is accepted: a revoked adult can still release a child offline until the next sync re-validates.
• New moving parts: migration 0027_, Zod authorizedPickupSchema + pickupRelationshipSchema, Dexie table + DEXIE_SCHEMA_VERSION bump, sync registry entity type (AUTHORIZED_PICKUP_EVENT), new Worker endpoints (BOLA/object-level authz required), student/check-in event schema extensions, Zod snapshot regeneration.

Neutral

• D1 foreign-key semantics (PRAGMA foreign_keys/cascade) must be verified at implementation time before relying on REFERENCES students(student_id) (flag in authorized-pickup-2026.md §6).
• The existing guardians.relationship CHECK (accented values) and the new ASCII pickupRelationshipSchema coexist in v1; normalization is a possible future cleanup, not a v1 task.

References

• Issue: #589 (v1.0, closed guardian list); research doc §7.1/§7.2 (docs/research/licensing-business-model-2026.md)
• Research: docs/research/authorized-pickup-2026.md (regulatory/market evidence, data-model sketch)
• ADR-0026 (deterministic daily codes), ADR-0016 (guardians/scope system), ADR-0022 (multi-role)
• Migrations: 0014_guardians.sql, 0022_check_in.sql, 0021_role_soft_delete.sql, 0010_events.sql
• Code: packages/schemas/src/enums.ts, packages/schemas/src/entities.ts, workers/src/routes/checkin.ts, workers/src/services/checkinService.ts, workers/src/services/onboardingService.ts, app/src/storage/dexieSchema.ts, app/src/app/pages/CheckOutPage.tsx