スプレッドシートの「行削除」を DB へ安全に同期する（行UUID方式）

Google スプレッドシートを人間の入力源、DB をアプリの正本として運用していると、 「シートから行が消えた ＝ そのレコードを無効化したい」 という同期要求が必ず出てくる。 しかし素朴に実装すると 実在するレコードを大量に誤削除する。この指示書はその安全な作り方と、 実際に踏んだ失敗パターンを含む。

前提

• 入力源: Google スプレッドシート（人間が行を追加・削除・並べ替えする）
• 正本: RDB（例: Postgres）。夜間バッチでシート → DB へ全件取り込み
• 目的: シートから行が消えたら、対応する DB レコードを archived_at 等で無効化する
• 削除は物理削除しない。必ず論理削除（archived_at / archived_by / archived_reason）にする

絶対にやってはいけない: 内容マッチによる削除判定

「会社名 + 件名でシートを引いて、無ければ削除」は必ず失敗する。理由:

• シートが全レコードを含む保証がない（フィルタ済み・年度別タブ・進行中のみ、等）
• 表記ゆれ（全角半角・空白・法人格の有無）で一致しない
• 1レコードが複数行に分割される（分割請求・前金/残金・明細行）

実測では、内容マッチで進行中レコードの相当数が偽陽性になった。行の同一性は内容で決めてはいけない。

正しい方式: 行に UUID を打つ

1. シートの右端に非表示列 row_uid を追加（列番号はハードコードせず必ずヘッダー名で解決する。人間が列を挿入する）
2. 取り込みバッチが、row_uid が空の行に UUID を採番して書き戻す
3. DB 側に対応表テーブルを持つ

create table row_links (
  row_uid text primary key,
  record_id uuid not null references records(id) on delete cascade,
  last_seen_at timestamptz not null default now(),
  missing_since timestamptz,          -- 欠落を初めて観測した時刻
  created_at timestamptz not null default now()
);
create index on row_links (record_id);

4. 毎回の取り込みで、シートに存在した row_uid の集合 alive を作る
5. row_links にあって alive に無いものが「消えた候補」

二段確認（1回の欠落では消さない）

1回目に欠落を観測 → missing_since = now() を立てるだけ。archive しない
2回目も欠落      → はじめて archive 対象
途中で復活       → missing_since = null に戻す（二段確認をやり直す）

一時的な読み取り失敗・シート編集中のスナップショット・行の並べ替え途中を吸収するために必須。

さらに 「1レコードに紐づく row_uid が全部消えた時だけ archive する」。 1レコードが複数行に分割されている場合、1行でも生存していればそのレコードは生きている。

多重ガード（これが本体。1つでも欠けると事故る）

archive を実行する前に、次を全部チェックして 1つでも該当したら archive も missing_since 更新も行わず中止し、通知する。

ガード	条件	防ぐ事故
列未解決	row_uid 列がヘッダーで見つからない	列削除・タブ間違いで全件消失判定
行数下限	シートの行数 < 想定の下限（例 500）	読み取り失敗・空レスポンスで全件 archive
生存数下限	alive の件数 < 下限（例 300）	同上
対応表の取得失敗	対応表の取得で例外	空集合を「全部消えた」と誤認
初回ベースライン	対応表が 0 件	採番前の初回実行で全件 archive
UUID重複	シート内に同じ row_uid が 2 行以上（後述）	書き戻しによる UUID 複製・上書き
大量消失	欠落数 > max(10, 総数 × 2%)	大規模な列ズレ・タブ差し替え
適用上限	archive 候補 > 20 件	想定外の一括削除

重要: すべて「止めて通知する」fail-safe にする。「怪しいので一部だけ実行」は絶対に作らない。

踏んだ最大の失敗: 書き戻しが UUID を複製し、隣の UUID を上書きする

シート側に自動処理（Apps Script 等）があり、シート全体を setValues で書き戻す設計だと、 分割行の展開処理（data[i].concat(...) で 1 レコードを複数行に展開する類）が 同じ row_uid を複数行にコピーする。しかもコピー先の行が元々持っていた別の UUID を上書きして消す。

実測（データ行 1370 の例）:

• 22 個の UUID が連続行ブロックに複製され、余剰 67 行
• 対応表 1368 件のうち 67 件の UUID がシートから完全消失
• しかし そのうち 56 件は同じ内容の行がシートに現存（＝削除されていない）
• 大量消失ガードが max(20, 総数×5%) = 68.4 だったため、欠落 67 は 1.4 差ですり抜けた

設計時の想定「複製されても元の行の UUID は残るから、1 行でも生存すれば alive」は成立しなかった。 複製は上書きを伴う。

対策

// 取り込みの行ループで出現回数を数える（Set では重複が潰れて検出できない）
const occurrences = new Map<string, number>();
let duplicateUidCount = 0;   // 重複した UUID の種類数
let duplicateRowCount = 0;   // 余剰行数

for (const row of rows) {
  const uid = clean(row[uidColIndex]);
  if (!uid) continue;
  const n = (occurrences.get(uid) ?? 0) + 1;
  occurrences.set(uid, n);
  if (n === 2) duplicateUidCount++;   // 2 回目でその UUID を「重複」と数える
  if (n > 1) duplicateRowCount++;     // 2 回目以降を余剰行として数える
  alive.add(uid);
}

// リコンサイル冒頭で中止
if (duplicateUidCount > 0) {
  await notify(`🚨 同期を中止: uid が重複(重複uid=${duplicateUidCount}件/余剰行=${duplicateRowCount}行)。`
             + `書き戻しで uid が複製・上書きされた疑い。archive も missing_since 更新も行っていません。`);
  return;
}

そのうえで 大量消失の上限を max(10, 総数 × 2%) まで下げる。5% は実データでは緩すぎる。

根治は「シート側の自動処理が row_uid 列を複製しないようにする」こと。 ガードは事故を止めるだけで、同期機能自体は重複が解消されるまで動かない。

逆方向（DB → シート）の除外フラグ

アプリ側で無効化したレコードをシートにも反映したい場合、row_uid の隣に 除外 列を持ち、 取り込み時にその行をスキップする。ここにも罠がある。

「無効化された全レコード」を対象にしてはいけない。 自動メンテ処理（重複統合・自社分の除外・ 金額同期など）が付けた無効化まで対象になり、大量の行が恒久スキップされて取り込みが壊れる。 実測では自動メンテ由来が 561 件あり、安全上限がなければ取り込みが止まっていた。

対象は「人間がアプリ上で明示的に無効化したもの」だけに絞る（archived_by に実在ユーザーの 識別子が入っているもの、など）。

通知

中止・実行のどちらも通知する。通知先は 2 系統以上（担当チャンネル + 責任者への個別 DM）にする。 通知関数は絶対に throw しないこと。通知障害で同期本体を止めない。

よくある落とし穴: 通知先の環境変数が未設定でも continue-on-error のステップだと CI は緑のまま、通知は完全な no-op になる。「設定した」ではなく「実物が届いた」で確認する。

検証手順（本番データに書き込まずに確認する）

取り込みスクリプトに --dry-run と --reconcile-only を必ず用意する。

• --dry-run: DB もシートも一切書かない。読み取り専用の認証スコープを使う
• --reconcile-only: 取り込み本体をスキップし、削除判定だけ回す

$ <runner> scripts/import.ts --reconcile-only --dry-run
総行数: 1371
[uid] 新規採番=0 alive=1298 excluded=3
[reconcile] DRY-RUN missing=67 初回欠損=67 archive候補=0

さらに 独立した監査スクリプトでシートを直接読み、 「データ行数 / 空 UUID / ユニーク UUID / 重複グループ数 / 余剰行数」を出して リコンサイルの出力と数字が一致するか突き合わせる。実装のログだけを信じない。

注意: 中止時に通知を送る実装だと、dry-run でも通知が実送信される。検証で連打しないこと。

導入順序

1. row_uid / 除外 列を追加（冪等に。既存列を壊さない）
2. 採番だけ回す（archive は無効）。対応表のベースラインを作る
3. --dry-run --reconcile-only で欠落の中身を目視する。この時点で偽陽性が出れば設計が間違っている
4. 独立した監査スクリプトで UUID の重複・欠番を確認する
5. ガードを全部入れてから、はじめて archive を有効化する
6. 初回の本番実行は missing_since が立つだけ。実 archive は最短で 2 回目の実行

チェックリスト

• 列はヘッダー名で解決している（列番号のハードコードなし）
• 行番号をキャッシュしていない（並べ替えで動くので毎回引き直す）
• 二段確認（1回目は missing_since のみ）
• 1レコードの全 UUID が消えた時だけ archive
• UUID 重複の検出（Set ではなく出現回数）
• 大量消失・適用上限・行数下限・初回ベースラインの各ガード
• すべてのガードが「止めて通知」
• --dry-run が完全に読み取り専用
• 通知が 2 系統に実着弾することを実物で確認
• 論理削除のみ（物理削除しない）