# Designing for an Untrusted Client: Pushing Consistency and Authorization into PostgreSQL for an Offline, Concurrently-Edited Game-Scoring App > Multiple people scoring the same game at once from a stadium with bad reception — I pushed that consistency and its multi-layered authorization down into the DB with deterministic idempotency keys and PostgreSQL row-level security (RLS), instead of relying on WebSocket broadcasts or client-side gating. Explained with real code from a Supabase / Expo / Next.js monorepo. - Published: 2026-06-24 - Author: 友田 陽大 - Tags: アーキテクチャ設計, TypeScript, PostgreSQL, Supabase, React Native, オフラインファースト, RLS, リアルタイム - URL: https://tomodahinata.com/en/blog/untrusted-client-postgres-rls-offline-first - Category: Databases & RLS - Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions ## Key points - The backbone of the design is consistently "don't trust the client" — the trust boundary sits on the server and DB side - Concurrent-edit consistency is protected not by WebSocket broadcasts but by deterministic idempotency keys + DB unique constraints + an offline durable queue - Authorization is enforced not with app-level if statements but with PostgreSQL RLS, expressing role-based access across all 69 tables and ~280 policies - Operations that involve state transitions are confined to SECURITY DEFINER RPCs, and the MFA verification result is baked into the JWT and enforced in the DB - Invalid states are doubly guarded by types and DB CHECK constraints / generated columns, and regressions are stopped mechanically with pgTAP, type coverage, and schema-drift detection --- I built and operate a "real-time game-scoring app" for amateur baseball entirely on my own — from domain design through mobile, the operations admin panel, the DB, CI/CD, and operations. It's an Expo + Next.js + Supabase monorepo consisting of a mobile app (iOS / Android), a web admin panel for operators, and the PostgreSQL database that underpins both. This product packed in a "mundane but fatal" set of hard problems, separate from any front-end flashiness: **multiple people must be able to record the same game simultaneously without it falling apart** — and **even while going offline at a stadium with poor reception**. On top of that, it demanded **multi-layered authorization** that strictly separates what information is visible and what features are operable per role: player, team admin, scorer, scout, operator. This article is a design record of how I solved those two hard problems — **concurrent-edit consistency** and **zero-trust authorization**. In one sentence, the backbone of the design was consistently "**don't trust the client.**" ## The backbone of the design: "Don't trust the client" The mobile app runs in the user's hands, and it can be tampered with. The network drops, requests are duplicated, ordering gets scrambled. So I made it my first principle to **place the trust boundary outside the app — on the server and database side**. This principle reduces to two concrete design decisions. 1. **Consistency is protected by "an optimistic client" × "strict DB constraints."** The client may move fast. But the truth is decided by the DB's unique and CHECK constraints. 2. **Authorization is enforced not with app-level if statements but with PostgreSQL row-level security (RLS).** Even if the UI forgets a permission check, the DB rejects it. Below, I walk through each with real code. ## 1. Concurrent-edit consistency — protected with idempotency keys, not WebSockets ### Why I didn't go with broadcasting When you hear "real-time collaborative editing," the first design that comes to mind is broadcasting every event over WebSockets. Indeed, Supabase Realtime lets you subscribe to Postgres changes. But a design that broadcasts the high-frequency, pitch-by-pitch input as-is didn't fit this product. - **Cost**: The load and cost of delivering every event to every client is not worth it relative to the scale of usage. - **Fragility under failure**: Events at the instant a connection drops are lost. How to reconcile after reconnection remains as a separate problem. - **The ordering problem**: Guaranteeing the order of events arriving from multiple writers is fundamentally hard at the delivery layer. So I demoted real-time delivery itself from the "leading role." **The correctness of a write is guaranteed by idempotency keys and DB constraints, and reflection to other clients is delegated to short-interval re-fetching (near-real-time)** — this trade-off was the configuration most resilient to the reality of frequent offline use. ### Deterministic idempotency keys (slot keys) The key is a **deterministic idempotency key**. An at-bat or a pitch is uniquely determined once you decide "which slot (position) of the game" it is. Rather than a random UUID, I adopted a key computed mechanically from the slot. ```ts // apps/mobile … scoringIdempotencyKeys.ts export const atBatIdempotencyKey = (input: AtBatSlotKeyInput): string => `game:${input.gameId}:inn:${input.inning}:${input.half}:seq:${input.sequenceInHalf}`; export const pitchIdempotencyKey = (input: PitchKeyInput): string => `ab:${input.atBatId}:pitch:${input.sequence}`; ``` There are two points. - **Zero randomness.** From the same slot, no matter how many times you compute it, the same key comes out. So even on retry, or computed from a different device, it points to the **same logical row** without collisions. - **Team-independent.** The key itself does not include the team. The scope of the recording team is expressed by a unique constraint on the DB side. The DB-side unique constraint is "recording team × idempotency key." With this, even if multiple scorers on the same team input the same slot simultaneously, the rows converge to one. ```sql -- 記録チームごとに idempotency_key を一意化（チーム内の重複を吸収） create unique index at_bats_idem_team_key on public.at_bats (recording_team_id, idempotency_key); ``` ### upsert and an RPC fallback that "returns your own team's row" I want the write to have the semantics of "INSERT, or if it already exists, take that row." A naive "SELECT, and if missing, INSERT" is weak against contention, so I use **`upsert` + `ignoreDuplicates`** and fall back, only on conflict, to **an RPC that resolves your own team's row**. ```ts // apps/mobile … useStartAtBat.ts（抜粋・簡略化） const { data: inserted } = await supabase .from('at_bats') .upsert( { game_id: input.gameId, inning: input.inning, half: input.half, batter_id: input.batterId, pitcher_id: input.pitcherId, batting_order: input.battingOrder, sequence_in_half: input.sequenceInHalf, idempotency_key: idempotencyKey, }, { onConflict: 'recording_team_id,idempotency_key', ignoreDuplicates: true }, ) .select('id') .maybeSingle(); if (inserted) { return { id: inserted.id }; } // 競合パス: すでに同じスロットの行がある → 自チームの行をRPCで解決 const { data: existingId } = await supabase.rpc('resolve_own_at_bat_id', { p_game_id: input.gameId, p_idempotency_key: idempotencyKey, }); ``` Why an **RPC** rather than "just SELECT"? `resolve_own_at_bat_id` is **permission-scoped**, designed to return only rows belonging to the team the caller is part of. By not letting the client pull arbitrary rows, even conflict resolution can be placed inside the trust boundary (the DB). ### An offline-first durable queue and drain worker At a stadium, going offline is an everyday occurrence. So writes are **persisted locally on the device first**, then sent safely in the background. The design is this. A pitch tap is first **optimistically reflected into the Zustand store**, and at the same time the operation is pushed onto **a queue persisted in AsyncStorage**. And **the only thing that writes to the `pitches` table is "a single drain worker."** ```ts /** * Durable-pitch drain worker — the SOLE writer of the `pitches` table. * * - Single-flight gating（同じopを二重発火させない。サーバーの * idempotency_key が第二の防衛線） * - Self-healing at-bat open（スコアラーがスロットを進めても、 * アプリ再起動後でも、保留中の投球を独立してドレインできる） * - Exponential backoff（成功で即ドレイン、失敗で次回をスケジュール） */ ``` These three points pay off. - **Single-flight control**: Don't let rapid taps or re-renders double-fire locally. Even if one slips through, the server's idempotency key is the second line of defense. - **Self-healing**: Even if the scorer advances the operation position, or the app restarts, pending pitches can be flushed "independently of the current live state." - **Exponential backoff**: Don't punish failures; space out the interval and resend. Thanks to the deterministic key, resending is **safe no matter how many times it repeats**. It's a structure where the idempotency key underpins reconciling "an optimistically fast UI" with "durable persistence that doesn't break" — the standard recipe for offline-first. ### Reflecting between clients: mutation-driven cache invalidation So how does it propagate to other scorers' screens? Here it's realized with TanStack Query's **cache invalidation + short-interval re-fetching**. When a write succeeds, only the dependent caches are precisely invalidated. ```ts // useRecordPitchEvent.ts（抜粋） onSuccess: (_data, variables) => { // pitch_events に依存する2つのキャッシュだけを無効化する queryClient.invalidateQueries({ queryKey: gameStateQueryKey(variables.gameId) }); queryClient.invalidateQueries({ queryKey: gameEarnedRunsQueryKey(variables.gameId) }); queryClient.invalidateQueries({ queryKey: gamePitchEventCountsQueryKey(variables.gameId) }); }, ``` Query keys are centralized into a hierarchical factory (`['games', gameId, sub-resource]`), preventing both missed and over-broad invalidations. Reads are given a short staleTime, so a re-fetch runs promptly after a change. Furthermore, the DB's `game_states` carries a **`version` column**, so optimistic locking (compare-and-swap) can be applied when needed. ```sql create table public.game_states ( ... current_inning smallint not null default 1 check (current_inning between 1 and 99), outs smallint not null default 0 check (outs between 0 and 3), version integer not null default 0, -- 楽観ロック用 ... ); ``` This is why I honestly call it "near-real-time." Rather than a millisecond-level bidirectional stream, I chose **"fast to write," "doesn't break," "converges in a few seconds."** Under the constraint of frequent offline use, this was the most robust. ## 2. Pushing authorization into PostgreSQL — zero-trust RLS ### Why not rely on app-side gating If you implement authorization with if statements in the UI or API layer, the probability of a missed check rises as the number of screens and roles grows. One missed spot becomes an information leak. So I enforced authorization at the DB layer with **PostgreSQL row-level security (RLS)**. Even if the app inadvertently forgets a check, the DB won't return the row or let it be written. **RLS is enabled on all 69 tables in the public schema, and role-based access is expressed with ~280 policies** (100% coverage). ### RLS and helper functions A policy declaratively states "only a user of this role, under this condition, may see / write this row." Team-scope and expiry checks are factored out into reusable helper functions (such as `is_team_admin`). ```sql -- 自チームの game_requests だけを SELECT 可能にする create policy game_requests_select_requester on public.game_requests for select to authenticated using ( exists ( select 1 from public.team_members tm where tm.team_id = requester_team_id and tm.user_id = auth.uid() and tm.left_at is null and (tm.expires_at is null or tm.expires_at > now()) -- 期限切れは除外 ) ); ``` For sensitive tables like audit logs, even SELECT is **fully denied to `authenticated` with a restrictive policy**, so that only `service_role` can touch them. I thoroughly apply "what must not be seen is simply never returned." ### Time-bound permissions This domain has many "temporarily granted permissions." Scorer permission, MFA sessions, match requests, invitations — all have an expiry. ```sql create table public.team_members ( ... role public.team_role not null, left_at timestamptz, expires_at timestamptz, -- expires_at を持てるのは scorer ロールだけ、という不変条件 constraint team_members_expires_at_scorer_only check (expires_at is null or role = 'scorer') ); ``` The "active member" check is woven consistently into each policy with the predicate `left_at is null and (expires_at is null or expires_at > now())`. Treating **expiry not as a business rule but as a data invariant** — this is the crux of zero trust. ### The scout-disclosure kernel: request → grant → audit What took the most care was **disclosing player information to scouts**. Because it handles personal information, I needed to strictly manage "who can see which fields, with whose approval," and it had to **withstand auditing**. The design is a coordination of three tables. | Table | Role | | --- | --- | | `scout_disclosure_requests` | A scout requests "I want to see these fields" (state machine: pending → accepted / declined / withdrawn) | | `scout_disclosure_grants` | Holds the fields actually disclosed. Only the **intersection of the player's approval ∩ the team admin's approval** is valid | | `scout_disclosure_audit` | An append-only audit log (mindful of personal-information protection law). Fully hidden from `authenticated` | The disclosure targets are five fields: `name` / `position` / `physical` / `handedness` / `grade`. The important thing is that **disclosure holds only at the intersection of "the fields the player approved" and "the fields the team admin approved."** Even if one side approves, it isn't disclosed unless the other side approves too. All DML is restricted to going through SECURITY DEFINER RPCs, and direct INSERT/UPDATE by `authenticated` is rejected. When a player transfers teams, a trigger automatically resets the disclosure policies, and that fact is also left in the audit log. ### SECURITY DEFINER and a fixed search_path RLS protects "whether a row is visible," but operations involving state transitions (approval, acceptance, idempotent creation) are expressed as **RPCs**, with authorization verified at the function's entry. The standard recipe in Supabase/PostgreSQL is `SECURITY DEFINER` + a fixed `search_path`. ```sql create or replace function public.accept_game_request(p_request_id uuid) returns public.games language plpgsql security definer set search_path = public -- search_path インジェクション対策 as $$ declare v_caller uuid := auth.uid(); v_request public.game_requests; begin if v_caller is null then raise exception '認証が必要です' using errcode = '28000'; end if; select * into v_request from public.game_requests where id = p_request_id for update; -- 行ロックで競合を直列化 -- 認可: 相手チームの admin のみが承認できる if not public.is_team_admin(v_request.requested_team_id) then raise exception '相手チームの admin のみ承認できます' using errcode = '42501'; end if; -- 冪等: すでに accepted なら同じ game を返す（再実行に強い） if v_request.status = 'accepted' and v_request.accepted_game_id is not null then return (select g from public.games g where g.id = v_request.accepted_game_id and g.deleted_at is null); end if; -- 期限切れ・不正な状態遷移を拒否し、ここから atomically に games を作る … end $$; ``` The `for update` row lock serializes contention, and the state-machine verification and idempotent re-execution (return the same result if already accepted) are confined inside the function. RLS handles "data visibility" and the RPC handles "operation correctness" — a division of roles. ### Enforcing MFA in the DB Sensitive operations are gated by MFA (email OTP). The important thing here is to **bake the MFA verification result into a JWT claim and enforce it on the DB side**. This is so as not to trust the client's "MFA-done flag." A custom access-token hook looks at the session's verification state and injects `app_metadata.mfa_verified` into the JWT. ```sql -- custom_access_token_hook（抜粋）: セッションが MFA 検証済みかを JWT に入れる v_session_id := nullif(v_claims->>'session_id', '')::uuid; if v_session_id is not null then select exists ( select 1 from public.mfa_verified_sessions s where s.session_id = v_session_id and s.expires_at > now() -- 検証には有効期限がある ) into v_mfa; end if; v_app_metadata := jsonb_set(v_app_metadata, '{mfa_verified}', to_jsonb(v_mfa)); ``` And sensitive RPCs call `require_mfa()` at the entry. ```sql create or replace function public.require_mfa() returns void language plpgsql stable as $$ begin if not public.session_is_mfa_verified() then raise exception 'MFA verification required' using errcode = '42501', hint = 'MFA_REQUIRED'; end if; end $$; ``` On the client side, immediately after OTP verification succeeds, it calls `supabase.auth.refreshSession()` to have a JWT carrying `mfa_verified=true` reissued. **Rather than the app asserting "I think I did MFA," the DB confirms it as fact** — this is zero trust taken to its conclusion. ## 3. Doubling up domain validity with types and the DB Even after hardening consistency and authorization, it's meaningless if **the domain model is wrong**. Baseball scoring has complex state transitions. Invalid states are made "impossible to create in the first place" with types and DB constraints. ### Zod enums and the orthogonality of "reason on base" At-bat results, pitch types, reasons for reaching base, and so on are defined centrally in `packages/domain` Zod schemas, shared by mobile, the admin panel, and Edge Functions. A design refinement is that I **made "at-bat result" and "reason on base" orthogonal**. ```ts // packages/domain … scoring.ts / enums.ts（抜粋） export const AtBatResult = z.enum([ 'single', 'double', 'triple', 'home_run', 'strikeout_swinging', 'strikeout_called', 'groundout', 'flyout', 'lineout', 'popout', 'walk', 'intentional_walk', 'hit_by_pitch', 'sacrifice_bunt', 'sacrifice_fly', 'fielders_choice', 'double_play', 'triple_play', 'dropped_third_strike', 'catcher_interference', // … ]); export const OnBaseReason = z.enum([ 'out', 'hit', 'walk', 'intentional_walk', 'hit_by_pitch', 'fielding_error', 'dropped_third_strike', 'sacrifice', 'fielders_choice', 'catcher_interference', 'interference', ]); ``` For example, a situation like "a grounder, but the batter reached on a fielder's choice" can be naturally expressed with the combination `result: 'groundout'` × `on_base_reason: 'fielders_choice'`. Forcing it all into a single enum produces impossible or ambiguous values. **By making them orthogonal, you gain expressiveness and correctness at the same time.** ### CHECK constraints and generated columns Types are a compile-time bulwark, but the DB is the last line of defense. The same invariants are **doubled up with DB CHECK constraints**. Outs are 0–3, innings 1–99, batting order 1–9, pitch location in normalized coordinates −1.0 to 1.0 — the DB rejects all of these. ```sql create table public.at_bats ( ... batting_order smallint not null check (batting_order between 1 and 9), idempotency_key text not null, -- 派生値はアプリで計算させず、DBの生成列に閉じ込める（単一真実源） is_hit boolean generated always as (public.at_bat_is_hit(result)) stored, is_official_at_bat boolean generated always as (public.at_bat_is_official(result)) stored, ... ); create table public.pitches ( ... location_x real not null check (location_x between -1.0 and 1.0), location_y real not null check (location_y between -1.0 and 1.0), ... ); ``` Derived values like "is it a hit" or "is it an official at-bat" are not computed in the app but made into **generated columns**. This structurally prevents the accident of the formulas diverging between aggregation and UI. Across the whole project there are **about 349** such CHECK constraints, making invalid states "unable to exist as data." ### Pure functions shared between TS and SQL (DRY) Domain rules appear in both TS and SQL. For example, "does this defensive play produce a putout?" If you implement this separately in the app and in aggregation, they will inevitably diverge. So I **consolidated the rule into a pure function** and shared the same definition on the TS side and the SQL side. ```ts // 刺殺成立条件: 捕球者が居て、かつ捕球者本人が捕球失策をしていない export function playEarnsPutout(play: DefensivePlay): boolean { return play.catcherPosition !== null && !catcherCommittedFieldingError(play); } ``` This function is the **single source of truth** shared by the fielding-record editor's "out row / error row" classification, the wizard's "rows = number of outs" consistency, and the DB-side aggregation. I consider DRY to be not a "trick for making code shorter" but **"the discipline of making the truth singular."** ## 4. Stopping regressions mechanically — pgTAP, type coverage, schema drift Precisely because I develop and operate alone, **not relying on human attentiveness to catch regressions** was a matter of life and death. I've embedded a large amount of "stop when broken" machinery into CI. ### Testing RLS with pgTAP RLS is powerful, but **merely writing it does not guarantee correctness**. So I made it a rule that authorization policies must always be accompanied by pgTAP tests. Using `set local request.jwt.claims` to switch roles, I verify **both the allowed cases and the denied cases**. ```sql select plan(4); -- 1. requester admin は自分の game_requests を SELECT できる select is( (select count(*)::int from public.game_requests where requester_team_id = '…' and requested_team_id = '…'), 1, 'requester admin can SELECT their own game_requests' ); -- 2. 無関係なチームの admin は見えない set local request.jwt.claims = '{"sub":"…","role":"authenticated"}'; select is( (select count(*)::int from public.game_requests where …), 0, 'unrelated team admin cannot see game_requests' ); -- 3. authenticated の直接 INSERT は拒否される（42501 / GRANT なし） select throws_ok( $$ insert into public.game_requests (…) values (…) $$, '42501', null, 'authenticated cannot directly INSERT game_requests' ); select finish(); ``` In the DB layer alone, **243 pgTAP test files** constantly verify behaviors that are "hard to notice when broken" — RLS, consistency, state transitions, resets on transfer. Combined with the app layer (Jest / Vitest / fast-check property tests), the test files reach **about 1,200** in scale. ### Enforcing type coverage in CI On top of TypeScript `strict`, I share a base config across all packages that also enables `noUncheckedIndexedAccess` and `exactOptionalPropertyTypes` (with relaxations being explicit and minimal, e.g. loosening only part of it for Next.js reasons). Furthermore, I have CI guard against the encroachment of `any` with a **type-coverage threshold**. ```jsonc // 例: apps/admin/package.json "typeCoverage": { "atLeast": 99.5, "strict": true, "detail": false } ``` The thresholds are **96.7%–100%** per package. Shared domain and UI are 100%, and the app side too is kept almost entirely typed — mechanically maintained. ### Catching "merged but not deployed" One thing that once gave me a scare in operations was the state of **"merged into main, but not reflected in the production DB."** The migration stays old while only the app moves forward — hard to notice, and dangerous. So I added **schema-drift monitoring** to CI. Every day, it inspects the sync between local migrations / Edge Functions and the deployed remote. ```yaml # supabase-drift-watch.yml — 「マージ済みなのに未デプロイ」を毎日検知する安全網 # （デプロイワークフロー自体が "実行されない" 故障モードを捕まえる） - run: node scripts/check-supabase-drift.mjs --strict --project-ref ${{ secrets.SUPABASE_PROJECT_REF }} ``` This script reconciles the ordering of migrations against the deployment state of Edge Functions, detects local-only / remote-only differences, and signals them via the exit code (0 = in sync / 1 = drift / 2 = environment fault). Destructive migration changes are separately statically inspected with **squawk**, and it also verifies that every table has RLS enabled and at least one policy. There are **11 CI workflows** in total — covering types, lint, type coverage, pgTAP, RLS coverage, a11y policy, drift detection, and OTA delivery. ## 5. Observability and privacy — a PII scrubber I want to detect production anomalies. But I don't want personal information flowing into logs. These two can coexist. Before sending to Sentry (mobile / admin panel), everything must pass through a **7-layer PII scrubber**. ```ts // packages/observability … scrubPii.ts（抜粋） // メールはローカル部を完全マスク、ドメインだけ残す（運用時の切り分け用） export function obfuscateEmail(value: string): string { const at = value.indexOf('@'); if (at <= 0 || at === value.length - 1) return value; const domain = value.slice(at + 1); return `[REDACTED]@${domain}`; } ``` The scrubber detects and masks tokens (`Authorization` / `*_token` / Bearer- or JWT-looking values), IPs, phone numbers, emails, and user IDs (first 8 chars + `…`) from **both the key name and the value**. It's applied to all events in Sentry's `beforeSend`, so it never gets forgotten. ```ts // 管理画面側 beforeSend: 全イベントの extra / tags / user.id をスクラブ export function buildBeforeSend(): (event: SentryEvent) => SentryEvent { return (event) => { if (event.extra) event.extra = scrubPii(event.extra); if (event.tags) event.tags = scrubPii(event.tags); if (event.user && typeof event.user.id === 'string') { event.user = { ...event.user, id: obfuscateUserId(event.user.id) }; } return event; }; } ``` A judgment like "don't keep the email's local part (keep only the domain)" is based on realizing in a past audit that **a "short local part + corporate domain" can identify an individual**. The trade-off between observability and privacy is left in the code as design intent. ## Summary: why this design worked, and the trade-offs What I consistently held to in this product was a single point: **"don't trust the client."** That paid off in concrete forms for each of the two hard problems — consistency and authorization. - **Consistency**: Not WebSocket broadcasting, but **deterministic idempotency keys + DB unique constraints + an offline durable queue**. It reconciled a fast UI and durable persistence in a form that's resilient to resending. - **Authorization**: Not app-level if statements, but **PostgreSQL RLS + SECURITY DEFINER RPCs + MFA enforced in the DB**. With 69 tables and 280 policies, it declaratively expressed everything down to role-, team-, expiry-, and field-level disclosure. - **Validity**: Doubling up types and DB constraints, generated columns, and TS/SQL-shared pure functions made invalid states "uncreatable." - **Sustainability**: pgTAP, type coverage, and schema-drift detection mechanically stop regressions even when working alone. Of course there are trade-offs. Pushing authorization into RLS incurs an up-front design cost in designing and testing policies and RPCs. Near-real-time isn't suited for uses that require millisecond-level bidirectional sync. But under the constraints of **"frequent offline use, multi-layered authorization, solo operation,"** I'm convinced this design is the most robust, the cheapest, and the least likely to break. The real difficulty of "real-time collaborative editing" lies not in the UI but in **where you draw the trust boundary**. Just by deciding not to trust the client, the design becomes surprisingly straightforward. --- If you'd like to discuss the design, implementation, and operation of similar requirements — "multi-person concurrent editing," "zero-trust authorization," "offline-first," "an end-to-end mobile + admin panel" — please feel free to get in touch. From requirements definition through infrastructure and operations, I handle it all one-on-one, end to end.