# スプレッドシートの「行削除」を 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 側に対応表テーブルを持つ ```sql 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」は成立しなかった。 **複製は上書きを伴う。** ### 対策 ```ts // 取り込みの行ループで出現回数を数える（Set では重複が潰れて検出できない） const occurrences = new Map(); 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`: 取り込み本体をスキップし、削除判定だけ回す ``` $ 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 系統に実着弾することを実物で確認 - [ ] 論理削除のみ（物理削除しない） ---