Observations sync for never-created user_ids → permanent FK-reject retry loop (invisible learners)

[Rick-Wilson]

Summary

Some devices sync observations under a user_id that has no users row on the server. Every such insert is rejected by the observations.user_id → users(id) foreign key (code 787: FOREIGN KEY constraint failed), but the client keeps retrying the same observation indefinitely. The learner is invisible server-side (no roster entry, no name, no stored progress) and their practice data is silently lost.

Surfaced while investigating the assignment-results panel (#110); found in the API error logs.

Evidence

Log volume — this is by far the dominant error class:

• Failed to store observation <UUID>: FOREIGN KEY constraint failed — ~8,100 occurrences (96% of all ERROR lines in ~/Library/Logs/bridge-classroom-api.log).
  • Distribution: 56 in 2026-02, ~8,055 in 2026-07, last one 2026-07-01T23:12:55 (burst, not currently ongoing).
  • The same observation UUID repeats many times → the client retries the same un-storable row forever, inflating the count.
• The log line carries only the observation UUID + browser user-agent — not the user_id — so the offending identity is not directly recoverable from logs.
• A representative batch logged Stored 6/7 observations alongside the failure: a single /observations POST contained a mix of storable and un-storable user_ids — consistent with a device holding queued observations from a prior localStorage identity plus a current one (user-switch / localStorage-reset signature).

The only foreign key on observations:

FOREIGN KEY (user_id) REFERENCES users(id)

Orphaned identities still visible in the DB (board_status, 3 ids):

13cecd51-314e-4d79-be35-ab903964515a   (18 board rows, baker-bridge / DONT)
98b6855f-68c9-4b0f-a5fc-6c5ae987929e   (18 board rows, baker-bridge / Major)
f33dc7b9-bf26-48b3-af1f-acba456f58ca   (20 board rows, baker-bridge / Major)

• None appear in users, classroom_members, assignments, student_summary, assignment_board_status, or grants.
• Absent from the users table in all 14 retained daily backups (back to 2026-06-29) — so no name is recoverable anywhere.
• Their board_status rows are all stamped 2026-05-15T02:01:4x, matching the correctness_v2_backfill completion time exactly. That backfill derives board_status from the observations table — proving these user_ids had observations in May.
• By the 2026-06-29 backup they have 0 observations and 0 users rows.

Interpretation

There is no delete-user function (users can only clear their own localStorage), so these are not deleted accounts. They are localStorage-only identities that were never successfully created server-side. Reconstructed timeline:

1. Device generates a user_id in localStorage and begins practicing.
2. Its observations were inserted into the DB before the user_id → users FK was enforced (fossilized into board_status by the 2026-05-15 backfill).
3. A later observations table rebuild added/enforced the FK and dropped the FK-violating rows; board_status was not rebuilt, leaving the orphan rollup rows.
4. Post-enforcement, those devices keep syncing → FOREIGN KEY constraint failed, retried forever.

Note: these 3 board_status orphans are only the identities that still leave a trace. The 2026-07 FK-storm device left zero recoverable server footprint (its observations never stored; if it started after the 2026-05-15 backfill it has no board_status fossil either), so it cannot be identified.

Impact

• Silent data loss: affected learners' practice is never stored.
• Invisible learners: no roster/name/progress server-side; a teacher cannot see them.
• Log/DB noise: thousands of ERROR lines; a permanent client retry loop wasting requests.

Open question / root cause to confirm

How does a user_id start syncing observations before its users row exists? Candidates:

• Create-user request failing silently (there are also 4 stale CreateUserRequest: missing field \email`` rejects from 2026-02-20 — an old client payload shape that yields exactly this: a never-created user).
• Observation sync firing before, or independently of, a successful create-user call.

Suggested directions (not prescribing)

• Client: on a 409/FK/"user not found" response, stop infinitely retrying; trigger (or re-trigger) server-side user creation, then re-sync. Don't let sync run before create succeeds.
• Server: return a distinct, non-retryable status for "observation references unknown user" instead of a generic 500, so the client can react (self-heal by creating the user) rather than loop.
• Cleanup: prune the 3 orphaned board_status rows (cosmetic).
• Observability: log the user_id on store failure so future occurrences are diagnosable.

Investigation context

• Error log: ~/Library/Logs/bridge-classroom-api.log
• Live DB: bridge-classroom-api/data/bridge_classroom.db; backups in bridge-classroom-api/data/bridge_classroom_backup_YYYYMMDD.db (14-day retention).
• Related but separate stale errors seen in the same log (already fixed or benign): no column found for name: prerelease (fixed in fix(assignments): correct completed count, accuracy, and drill-in (#110) #111), exercises: no such column: u.name (fixed in 5059710, errors from 2026-05-23), and a one-off Resend mail.bridge-craftwork.com not verified on 2026-06-12.