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

For amateur baseball | an Expo SDK 55 + Next.js 16 + Supabase (Postgres 17) monorepo | solo-built a zero-trust design pushing authorization into the DB (RLS on all 72 tables, 233 policies) and offline-resilient, idempotent concurrent input

Problem: Multiple people scoring the same game simultaneously, even going offline at a poor-reception ballpark, must not break. Further, what players, teams, scouts, and operators can see and do had to be strictly separated, with authorization guaranteed on the premise of not trusting the client.
What was built: Idempotency-key-driven offline-first sync ensures concurrent-input integrity, and a zero-trust design enforces authorization in the DB layer with PostgreSQL RLS.
Role: Solo
Technical architect and full-stack developer.
Result: RLS on all 72 tables, 233-policy zero-trust authorization | solo-built offline-resilient, idempotent concurrent input
Assurance: 233 +RLS policies
1,400 files+Total tests
96.7 %+Type coverage
11 workflowsCI/CD workflows

Client

A real-time game-scoring platform for amateur baseball (iOS / Android mobile app + a Web admin console for operators) | Users: players, team managers, scorers, scouts, operators — multiple stakeholders | Setup: solo (full-stack), from domain design through mobile, admin console, DB/authorization, CI/CD, observability, and release operations

My role

Technical architect and full-stack developer. Solo from design through production operations — domain modeling, DB / RLS design, the mobile app (React Native / Expo), the operator Web admin console (Next.js), auth/authorization (MFA / RLS / audit logs), CI/CD, observability, and release operations (EAS / OTA / Supabase).

Challenge (Situation & Task)

"Collaborative scoring" — where multiple scorers record the same game simultaneously without breaking from duplication, conflict, or out-of-order writes — had to work even at ballparks with poor reception (frequent offline). At the same time, multi-layered authorization was required to strictly separate what each role — player, team manager, scout, operator — can see and do. Starting from the premise that the mobile app can be tampered with, placing the trust boundary on the server/DB side was the fundamental requirement.

This app concentrated the difficulties of collaborative editing, mobile, and multi-layered authorization.

Concurrent editing × offline: multiple people make high-frequency, pitch-by-pitch entries, and reception is unstable at ballparks, so offline is frequent. Data integrity had to hold across send retries, double sends, and device restarts.
Zero-trust multi-layered authorization: permissions differ across player, team manager, scorer, scout, and operator, and authorization was multi-layered — "team-level viewing," "time-limited scorer permission," "per-field disclosure to scouts (mindful of personal-information-protection law)." Because client-side gating can be bypassed, it had to be enforced on the DB side.
Domain correctness: baseball scoring has complex state transitions (inning, top/bottom, outs, runners, batting order, pitch sequence), and invalid states had to be structurally excluded with types and DB constraints.
Sustainability of solo operation: developing and operating alone made advanced automation that mechanically prevents regressions (types, tests, schema consistency, a11y, deploys) indispensable.

Why these technologies (Rationale)

A pnpm + Turborepo monorepo (Expo SDK 55 / React Native 0.83 / React 19 / Next.js 16): because mobile, admin, and DB needed to share the same domain vocabulary, Zod schemas are consolidated in packages/domain as the single source of truth for the whole stack. pnpm catalog centrally pins major dependencies (Zod / React, etc.) to eliminate silent drift.
Supabase (PostgreSQL 17 + RLS / Realtime / Edge Functions): to enforce authorization logic in the DB layer with PostgreSQL Row-Level Security rather than scattering it across the app. A zero-trust design that doesn't trust the client, realized at low cost without an extra backend server. Server-side processing — invitations, email-OTP MFA, IAP-receipt verification, push-notification delivery — is split into 14 Edge Functions (Deno).
Idempotency-key-driven offline-first sync: rather than broadcasting high-frequency input over WebSocket, write safely with deterministic idempotency keys and reflect to each client via TanStack Query cache invalidation + short-interval refetch. Prioritizing "never breaking even with poor reception" (device-local is Zustand + a persistent queue).
The operator admin console on Next.js 16 (App Router / RSC): the admin console uses server components + @supabase/ssr for RLS-effective server fetching, avoiding excessive client state.
TypeScript strict + CI-enforced type coverage: enabling even noUncheckedIndexedAccess and exactOptionalPropertyTypes, with per-package type-coverage thresholds (96.7%–100%) enforced in CI.

What I did (Action)

[Offline-resilient, idempotent concurrent input] Clients generate deterministic idempotency keys — at-bat game:{id}:inn:{n}:{top/bottom}:seq:{n}, pitch ab:{id}:pitch:{n}. The DB's (recording_team_id, idempotency_key) unique constraint + upsert(ignoreDuplicates) + a permission-scoped RPC resolve_own_at_bat_id on conflict make retries, double sends, and concurrent users always converge to the same logical row.
[A fault-tolerant write queue] A pitch tap is first optimistically reflected to Zustand and added to an AsyncStorage persistent queue. A single "drain worker" alone writes to the pitches table, suppressing re-send storms with single-flight control and exponential backoff (up to 8 attempts, a ~17-minute envelope) while re-sending safely across offline, app restarts, and out-of-sync operation positions. When the oldest pending op sits stalled past 30 seconds, a stall event is reported to Sentry (with a 60-second throttle to also curb report storms), so sync stalls never go unnoticed.
[Consolidating zero-trust authorization in the DB] RLS is enabled on all 72 public tables, with 233 policies expressing per-role access. Disclosure to scouts is controlled by per-field grants on the intersection of "player approval ∩ team-manager approval" + an append-only audit log (mindful of personal-information-protection law). Sensitive operations are gated by email-OTP MFA; custom_access_token_hook injects mfa_verified into the JWT, and require_mfa() enforces it at the RPC entry.
[Structural correctness of the baseball domain] At-bat results, pitch types, on-base reasons, etc. are expressed with Zod enums, modeling fielder's choice correctly by making the on-base reason orthogonal to the at-bat result. Outs 0–3 and innings 1–99 are doubly guaranteed by TypeScript and DB CHECK constraints, and rules like put-out conditions are consolidated into pure functions shared by TS / SQL (DRY).
[Automation that supports solo operation] 11 GitHub Actions automate types, lint (Biome), type coverage, dead-code detection (knip), i18n consistency, RN a11y-policy checks, migration-safety checks (squawk), RLS coverage, pgTAP, schema-drift detection, and EAS / OTA delivery. A custom guard forces every migration to ship with a paired pgTAP test, so no authorization or schema change lands on mainline untested.
[Observability and privacy] Sentry (mobile / admin) runs through a 7-layer PII scrubber, masking tokens, email, phone, IP, and user IDs before sending. The structured Transport in packages/observability automatically scrubs log context too.

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 72 public tables, with 233 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). Further, a custom CI guard mechanically forces every migration to ship with a paired pgTAP test, 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. Underpinning them, the root package.json carries 58 quality-gate scripts of the lint / check / test family (including the guard that forces migration↔pgTAP pairing). 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.

Responsibilities

Domain design / DB / RLS design
Mobile app development (React Native / Expo)
Operator Web admin console (Next.js 16 / RSC)
Auth/authorization design (MFA / RLS / audit logs)
CI/CD, observability, release operations (EAS / OTA, Sentry)

Technologies

React Native

Expo

Next.js

TypeScript

Supabase

PostgreSQL

Row Level Security

Zod

TanStack Query

Zustand

React Server Components

NativeWind

react-hook-form

PL/pgSQL

pgTAP

Vitest

Jest

fast-check

Sentry

Turborepo

pnpm

Biome

GitHub Actions

EAS / OTA

Edge Functions (Deno)

Results in numbers

RLS policies: 233 +RLS enabled on all 72 public tables (100% coverage, zero-trust).
Total tests: 1,400 files+App-layer (1,138 Jest / Vitest) + DB-layer (282 pgTAP) test files.
Type coverage: 96.7 %+CI-enforced across all packages (up to 100%, TypeScript strict).
CHECK constraints: 220 +Structurally excludes invalid states such as outs 0–3.
CI/CD workflows: 11 workflowsAutomates types, lint, schema-drift detection, a11y, and OTA delivery.
DB migrations: 286 migrationsEvolved through squawk static checks and the guard forcing paired pgTAP tests.
Edge Functions: 14 functionsInvitations, email-OTP MFA, IAP-receipt verification, push-notification delivery, and more split into Deno Edge Functions.
Quality-gate scripts: 58 scriptsThe root package.json's lint / check / test family (excluding fixers), including the guard forcing migration↔pgTAP pairing.

Results

Realized collaborative scoring where multiple scorers record the same game simultaneously, without breaking even at poor-reception ballparks (frequent offline).
Consolidated authorization into PostgreSQL RLS, enabling RLS on all 72 public tables (100% coverage). Built a zero-trust foundation that strictly separates per-role access with 233 policies.
Controlled disclosure of player info to scouts via per-field grants on "player approval ∩ team-manager approval" + an append-only audit log — a design mindful of personal-information-protection law.
Constantly verifies RLS, integrity, and state transitions with 282 pgTAP DB tests, with a CI guard forcing every migration to ship with a paired pgTAP test. With the app layer, ensured quality across ~1,400 test files, with type coverage CI-enforced across all packages (96.7%–100%).
Automated with 11 CI workflows — schema-drift detection ("merged but not deployed"), migration-safety checks (squawk), RN a11y-policy checks — for safe continuous delivery even solo.
Established observability that detects production anomalies while masking tokens, email, phone, IP, and user IDs via Sentry + a 7-layer PII scrubber.
Designed, developed, and operated the mobile app (iOS / Android) and operator Web admin console solo, under Zod domain types as the single source of truth.

Facing a similar challenge?

Your business challenge can likely be solved with today's technology too. Start with a free 30-minute technical consultation and tell me where things stand.

Ask whether your challenge could become a SaaS

Fixed-scope projects and technical advisory are both available.

View all case studies

At a glance