クライアントを信じない設計：オフライン同時編集の試合記録アプリで、整合性と認可をPostgreSQLに寄せる

// 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}`;

-- 記録チームごとに idempotency_key を一意化（チーム内の重複を吸収）
create unique index at_bats_idem_team_key
  on public.at_bats (recording_team_id, idempotency_key);

// 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,
});

/**
 * Durable-pitch drain worker — the SOLE writer of the `pitches` table.
 *
 *   - Single-flight gating（同じopを二重発火させない。サーバーの
 *     idempotency_key が第二の防衛線）
 *   - Self-healing at-bat open（スコアラーがスロットを進めても、
 *     アプリ再起動後でも、保留中の投球を独立してドレインできる）
 *   - Exponential backoff（成功で即ドレイン、失敗で次回をスケジュール）
 */

// 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) });
},

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,  -- 楽観ロック用
  ...
);

-- 自チームの 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())  -- 期限切れは除外
    )
  );

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')
);

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
$$;

-- 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));

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
$$;

// 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',
]);

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),
  ...
);

// 刺殺成立条件: 捕球者が居て、かつ捕球者本人が捕球失策をしていない
export function playEarnsPutout(play: DefensivePlay): boolean {
  return play.catcherPosition !== null && !catcherCommittedFieldingError(play);
}

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();

// 例: apps/admin/package.json
"typeCoverage": { "atLeast": 99.5, "strict": true, "detail": false }

# supabase-drift-watch.yml — 「マージ済みなのに未デプロイ」を毎日検知する安全網
# （デプロイワークフロー自体が "実行されない" 故障モードを捕まえる）
- run: node scripts/check-supabase-drift.mjs --strict
       --project-ref ${{ secrets.SUPABASE_PROJECT_REF }}

// 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}`;
}

// 管理画面側 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;
  };
}