ADR-0034: Class–Session Relationship in Check-in/Capacity Queries

Status: accepted
Date: 2026-08-05
Deciders: @barateza
Tags: [check-in, capacity, data-model, d1, sql]

Context

Issue #613: four check-in/capacity queries in workers/src referenced columns that do not exist in the D1 schema (class_sessions.id/class_sessions.class_id, classes.id, class_sessions.class_id via sess.class_id), verified against the production D1 (c965d6fc…) and reproducible locally against real SQLite. check_in_events and the rest of migrations 0015–0025 are not yet deployed, so this is broken-on-next-deploy, not an active incident.

While fixing the column typos we had to answer a real modeling question the broken SQL had smuggled in: how does a session relate to a class?

The actual data model is:

• classes (turma) = age group (class_id PK, age_min/age_max, status). A child's class is a membership fact stored on the student: students.class_id.
• class_slots = recurring meeting time (aula recorrente) with capacity.
• class_sessions = a dated instance of a slot: {session_id, slot_id, session_date} — a pure mirror of the IndexedDB table (spec .specs/archive/features/2026-07-28_d1-mirror*), generated per slot × date (generateSessions, ensureSessionsForRange).
• check_in_events = {student_id, session_id, …}. The check-in identity is (student, session) — already codified in ADR-0026: daily_code = HMAC(secret, student_id + session_id + date).

The broken queries assumed a 1:1 session → class link (cs.class_id, sess.class_id). That link does not exist anywhere in the product — not in the Dexie schema, the seed data, the event projections, or the migrations. A Sunday-09:00 session hosts Berçário and Maternal and Juniores simultaneously; each child attends in their own turma.

Options considered

(a) Add class_id to class_sessions — a migration + seed + Dexie schema + projection change that encodes a forced 1:1 where the domain is effectively many classes per session. It contradicts how sessions are created (per slot + date, serving all classes) and would require inventing a per-(class, slot, date) session model that no consumer needs. Classic data-modeling smell: attaching an attribute whose grain (child membership) does not match the table's grain (slot + date).

(b) Resolve the class through the student's enrollment (students.class_id) — the class of a checked-in child is their own turma, independent of the session. For "which class fits this age" lookups, match classes.age_min/age_max directly. No schema change anywhere.

(c) Snapshot class_id on check_in_events at check-in time — the event-sourcing-consistent long-term model (an immutable event carries its own context; a later turma change never rewrites attendance history). This is what market-leading church check-in software does (the class is attached to the check-in record, not to the dated service). It is the correct future hardening but requires a migration + write-path change and has no consumer today.

Decision

Adopt (b) now — resolve the class through students.class_id in read queries; match classes by age range for the alternatives feature. No migration, seed, or projection changes. Document (c) as the recommended evolution when check-in gains class attribution.

Concretely:

• getActiveCheckIn — join classes via students.class_id (LEFT JOIN classes cl ON cl.class_id = s.class_id), drop the class_sessions join entirely. The session contributes nothing to "which class is this child in".
• findAlternativeClasses — alternatives are other slots with room; the class shown is the one whose age range covers the child (JOIN classes cl ON cl.status = 'ACTIVE' AND cl.age_min <= ?age AND cl.age_max >= ?age). The phantom class_sessions → classes join is removed. One row per (slot, class); capacity aggregated with MAX(occupied) and filtered via HAVING.
• validateSessionToday — selects session_id, session_date only; the vestigial class_id select is dropped (callers never used it).
• Capacity semantics — buildCapacityAdjustmentStatement and getSlotCapacity are class-agnostic: capacity is per-slot (class_slots.capacity), occupancy per dated session (session_capacity). getSlotCapacity now returns one row per slot via GROUP BY slot_id with COALESCE(MAX(occupied), 0) — the fullest session, conservative for the "Cheia"/90%-pre-capacity badges.

Consequences

• Positive: no schema/seed/projection churn; the queries now match the real domain; the check-in/capacity paths run on real SQLite (locked by workers/src/__integration__/checkin-capacity.test.ts).
• Cost: a child who changes turma is reported under their current class for historical check-ins. Acceptable for the label/display use case; eliminated by (c) when implemented.
• Waiting list (#567): notifyNextInQueue runs inside the CHECK_OUT sequence; its students.id references were fixed to student_id as part of this work. Its migration (0025) still declares FOREIGN KEY (child_id) REFERENCES students(id) — a non-existent column that will fail at INSERT time on D1; fix in #567's work.
• Same latent family outside this area: credentialService.ts (students.id) and the waiting-list route (routes/waitingList.ts:169) carry the same students.id reference and must be fixed before their features deploy.

Future (out of scope today)

Implement (c): add class_id to check_in_events, populate it from students.class_id at check-in time. Then getActiveCheckIn reads the snapshot (no join), and historical reports are stable under turma changes. Revisit when the check-in flow gains class-specific reporting or label attribution.