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

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.

Key technical decisions

Supabase RLS: authorization enforced at the DB row level on all 72 tables (zero-trust)
Deterministic idempotency keys + unique constraint + resolution RPC: converging to the same logical row even offline / concurrent
Zod domain types as the single source of truth: shared across mobile, admin, DB types, and Edge Functions
Forced pgTAP pairing on every migration + type coverage + schema-drift detection: mechanically blocking regressions in CI

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,400files+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: 11workflowsAutomates types, lint, schema-drift detection, a11y, and OTA delivery.
DB migrations: 286migrationsEvolved through squawk static checks and the guard forcing paired pgTAP tests.
Edge Functions: 14functionsInvitations, email-OTP MFA, IAP-receipt verification, push-notification delivery, and more split into Deno Edge Functions.
Quality-gate scripts: 58scriptsThe 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.

同様の課題、抱えていませんか？

あなたのビジネス課題も、最新の技術で解決できます。まずは30分の無料技術相談から、状況をお聞かせください。

自社の課題もSaaS化できるか相談する

プロジェクト単位（請負）・技術顧問、どちらにも対応可能です

View all case studies