A real-time game-scoring app with multi-user simultaneous editing

This product's design principle was consistently "don't trust the client (place the trust boundary in the DB)."

Concurrent-editing integrity — optimistic but unbreakable: Broadcasting high-frequency input over WebSocket brings delivery cost, fragility under failure, and ordering problems. The solution was deterministic idempotency keys. A unique key is computed from an at-bat/pitch "slot," and the DB absorbs duplicates with the (recording_team_id, idempotency_key) unique constraint. Writes use upsert(ignoreDuplicates), resolving one's own team's row via a permission-scoped RPC only on conflict. The device is offline-first: a pitch is first reflected optimistically locally + queued persistently, and a single drain worker sends safely with exponential backoff. Even if the app restarts or the scorer advances the operation position, re-sends never collide thanks to the keys and converge. Reflection between clients uses mutation-driven cache invalidation and short-interval refetch (near-real-time), with a game_states.version column enabling optimistic locking too.

Authorization — enforced in PostgreSQL, not the app: Authorization doesn't rely on app-side gating but is enforced via Row-Level Security in the DB. RLS is enabled on all 69 public tables, with 280 policies expressing role, team, time-limited permissions, and per-field disclosure. RPCs are written with the canonical SECURITY DEFINER + fixed search_path, verifying auth.uid(), role, and require_mfa() at the function entry. Disclosure of player info to scouts is three-layered — "request → grant → audit" — disclosing only the intersection of player and team-manager approval, with append-only auditing (mindful of personal-information-protection law). RLS changes must be accompanied by pgTAP tests, switching role via set local request.jwt.claims to verify both allow and deny and prevent regressions.

Automation to run in production alone: 11 GitHub Actions handle types, lint, type coverage, pgTAP, RLS coverage, migration safety (squawk), a11y policy, schema-drift detection, and OTA delivery. Schema-drift monitoring in particular is a safety net added after an actual incident of "merged but not deployed," checking the sync of migrations and Edge Functions daily.