Claude Codeで論理削除と履歴テーブルを設計する手順 — 部分一意インデックスとトリガー

論理削除でまず壊れるのはユニーク制約

論理削除は、行をDELETEせずにdeleted_atへ時刻を入れて「消えたことにする」設計です。誤操作から戻せて、関連データも残せます。代わりに、ふつうのユニーク制約と相性が悪くなります。

users.emailにUNIQUEを付けたまま、ユーザーを論理削除したとします。行は残っているので、同じメールアドレスで再登録しようとすると重複エラーになります。逆にUNIQUEを外すと、生きている行の重複を止められません。

PostgreSQLでの解は、生きている行だけに一意性をかける部分一意インデックスです。マニュアルは、インデックスの一部の行だけを対象にした一意インデックスは「条件を満たす行の中で一意性を守り、満たさない行は縛らない」と説明しています。

CREATE UNIQUE INDEX users_email_active_key
  ON users (email)
  WHERE deleted_at IS NULL;

Claude Codeに任せるときは、この一文だけでは足りません。履歴の保存、既存クエリの条件漏れ、復元時の衝突まで含めて設計し、動くかどうかを自分で確かめさせる必要があります。この記事は、その手順をPostgreSQLを前提に並べます。MySQLなど部分インデックスを持たない製品では、設計が変わります。

頼む前に決めておく3つのこと

Claudeに「論理削除を入れて」とだけ頼むと、deleted_at列を足して終わりになりがちです。先に次の3点を決めて、プロンプトかCLAUDE.mdに書いておきます。

• 論理削除の対象テーブル。参照データなど、物理削除でよいテーブルは外す
• 一意にしたい列と、その範囲(生きている行の中だけで一意か)
• 履歴に残す操作。登録・更新・削除のすべてか、削除だけか

履歴テーブルは論理削除の代わりではありません。論理削除は「今の画面から消す」ための状態で、履歴は「いつ誰が何をしたか」を残すための記録です。両方を入れる場合は役割を分けておくと、後で混ざりません。

論理削除の列と部分一意インデックス

最小のスキーマは次のとおりです。削除フラグの真偽値ではなく、時刻の列にすると、いつ消えたかも同時に残ります。

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  name       text NOT NULL,
  deleted_at timestamptz
);
 
CREATE UNIQUE INDEX users_email_active_key
  ON users (email)
  WHERE deleted_at IS NULL;

通常のUNIQUEはemailに付けません。付けると、論理削除済みの行が同じメールアドレスを占有し続けます。

削除はDELETEではなくUPDATEで行います。すでに消えた行を二重に更新しないよう、条件にdeleted_at IS NULLを入れておきます。

UPDATE users
   SET deleted_at = now()
 WHERE id = $1
   AND deleted_at IS NULL;

NULLの扱いに注意する

deleted_atがNULLの行を「生きている」と見なす設計では、NULLの比較が罠になります。ユニーク制約は、既定では2つのNULLを等しいと見なしません。ここで一意にしたいのはemailであってdeleted_atではないので、WHERE deleted_at IS NULLの述語で絞る形なら問題になりません。

一方、UNIQUE (email, deleted_at)のようにdeleted_atを制約の列に含める設計は避けます。生きている行はdeleted_atがNULLなので、NULL同士が等しくならず、同じメールアドレスが何件でも入ってしまいます。NULLS NOT DISTINCTを付ければこの挙動を変えられ、生きている行の重複も止まります。ただ、deleted_atを一意性の列に入れると「生きている行の中で一意」という意図が制約の形から読めなくなります。同じトランザクションで削除と再登録を繰り返すとnow()の値が揃って衝突する場合もあるため、述語で絞る部分インデックスのほうが意図をそのまま表せます。

部分インデックスは条件が合わないと使われない

もう一つの注意は、検索側です。部分インデックスが使えるのは、クエリのWHERE条件がインデックスの述語を含意するとシステムが判断できるときだけです。単純な不等式の含意は認識されますが、そうでなければ述語はクエリの条件の一部と完全に一致している必要があります。マッチングは実行計画の作成時に行われるため、パラメータ化した条件では使われない場合があります。

つまり、検索のたびにWHERE email = $1 AND deleted_at IS NULLと書いているクエリは、一意インデックスの述語と同じ形なので適合します。WHERE email = $1だけのクエリでは、論理削除済みの行も混ざる上に、このインデックスも使えません。条件の書き忘れは、性能の問題と正しさの問題を同時に起こします。

履歴テーブルとトリガー

履歴は、元のテーブルと同じ列に、操作の種類・時刻・実行ユーザーを足した別テーブルに残します。PostgreSQLのマニュアルには、挿入・更新・削除のたびに別テーブルへ1行書く監査トリガーの例があり、特殊変数TG_OPで操作を判別しています。ここではその形を、列を明示する書き方にしてみます。

CREATE TABLE users_history (
  history_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  op         char(1)     NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  changed_by text        NOT NULL DEFAULT current_user,
  user_id    bigint      NOT NULL,
  email      text        NOT NULL,
  name       text        NOT NULL,
  deleted_at timestamptz
);
 
CREATE FUNCTION users_history_fn() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO users_history (op, user_id, email, name, deleted_at)
    VALUES ('D', OLD.id, OLD.email, OLD.name, OLD.deleted_at);
  ELSE
    INSERT INTO users_history (op, user_id, email, name, deleted_at)
    VALUES (left(TG_OP, 1), NEW.id, NEW.email, NEW.name, NEW.deleted_at);
  END IF;
  RETURN NULL;
END;
$$;
 
CREATE TRIGGER users_history_trg
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION users_history_fn();

AFTERの行トリガーなので、戻り値は無視されます。NULLを返しているのはそのためです。

履歴には、論理削除のUPDATEも1行として残ります。opがUでdeleted_atに値が入った行が、削除の記録です。物理的なDELETEは、運用上の例外(個人情報の消去依頼など)に使うことになり、そのときはDの行が残ります。

履歴に何を持たせるかはClaudeに決めさせない

列を明示すると、元のテーブルに列を足したとき、履歴側とトリガー関数の更新を忘れやすくなります。OLD.*やNEW.*をそのまま挿入する書き方は列追加に強い反面、列の並びが両テーブルでずれると壊れます。どちらを採るかはチームの運用で決めることなので、プロンプトに「列追加時は履歴テーブルと関数も同じマイグレーションで更新する」と一行入れておきます。

Claude Codeに設計と実装を頼む

ここからがClaude Code側の作業です。Claudeには「合否が返ってくる確認手段」を渡します。完了の判定材料がないと、Claudeは「できたように見える」ところで止まるためです。論理削除と履歴は、挙動で検証できる題材です。

CLAUDE.mdに規約を書く

プロジェクトのCLAUDE.mdに、論理削除の規約と確認コマンドを書いておきます。例えば次の形です。

# DB conventions
- 論理削除する表は deleted_at timestamptz を持つ。NULL が「生きている」
- 一意にしたい列は通常の UNIQUE でなく、WHERE deleted_at IS NULL の部分一意インデックス
- 論理削除する表のSELECT/UPDATEは deleted_at IS NULL を必ず付ける(ビューは active_<表名>)
- 履歴は <表名>_history にトリガーで書く。列追加時は履歴表と関数も同じマイグレーションで更新
- 確認: psql "$SCRATCH_DB_URL" -v ON_ERROR_STOP=1 -f db/checks/soft_delete_check.sql

リポジトリにテストなどの確認手段があるなら、それを実行するコマンドをCLAUDE.mdに書くのが最も有用な一行です。ここでは、次に作る検証スクリプトがそれにあたります。

検証用のSQLを先に用意する

実装を頼む前に、期待する挙動をSQLで書いておきます。トランザクションの中で動かし、最後にROLLBACKすればデータは残りません。

BEGIN;
 
INSERT INTO users (email, name) VALUES ('a@example.com', 'A');
UPDATE users SET deleted_at = now() WHERE email = 'a@example.com';
 
-- 削除済みと同じメールで再登録できる
INSERT INTO users (email, name) VALUES ('a@example.com', 'A2');
 
-- 生きている行と重複する登録は拒否される
DO $$
BEGIN
  BEGIN
    INSERT INTO users (email, name) VALUES ('a@example.com', 'A3');
    RAISE EXCEPTION 'duplicate active email was accepted';
  EXCEPTION WHEN unique_violation THEN
    NULL;
  END;
END $$;
 
-- 履歴は 登録・論理削除・再登録 の3行
DO $$
BEGIN
  IF (SELECT count(*) FROM users_history WHERE email = 'a@example.com') <> 3 THEN
    RAISE EXCEPTION 'history rows mismatch';
  END IF;
END $$;
 
ROLLBACK;

-v ON_ERROR_STOP=1を付けたpsqlは、最初のエラーで終了コードを非ゼロにします。Claudeがこのコマンドを実行すれば、通ったか落ちたかが出力で分かります。手元の環境で一度走らせて、期待どおりに動くことを確かめてから渡します。

この確認用のDBは、本番や共有の開発DBにしないでください。使い捨てのSCRATCH_DB_URLだけをClaudeに見せ、本番の接続情報は権限の設定で読ませない構成が前提です。

Stopフックで「通るまで終わらせない」

Stopフックで確認コマンドを強制できます。スクリプトが終了コード2で終わると、Claudeは止まれずに作業へ戻され、標準エラーの内容が理由として渡されます。型チェックの例を、このスクリプトに置き換えます。

{
  "hooks": {
    "Stop": [
      {
        "hooks": [
          {
            "type": "command",
            "command": "psql \"$SCRATCH_DB_URL\" -v ON_ERROR_STOP=1 -f db/checks/soft_delete_check.sql 1>&2 || exit 2"
          }
        ]
      }
    ]
  }
}

フックが8回連続でブロックし、その間にツール呼び出しが無ければ、Claude Codeは次のブロックを無視してターンを終わらせます。上限はCLAUDE_CODE_STOP_HOOK_BLOCK_CAPで変えられます。この仕組みの詳細はStopフックのブロック上限に、フック全般の設定はHooks完全ガイドにあります。

Stopフックはこの作業と関係のない停止でも毎回走るため、プロジェクト設定に置くと、別の用件で使うときも検証が走ります。作業の間だけ有効にするか、SCRATCH_DB_URLが未設定なら何もせず終えるようにします。また、確認スクリプトが書いた範囲しか見ません。今回のスクリプトはusersだけを検査しているので、ほかの表に論理削除を広げるときは、表ごとに検査を足すことになります。

既存クエリの条件漏れを洗うレビュー

新規の表なら規約どおりに書かせれば済みます。難しいのは、すでに動いているアプリに論理削除を後から入れるときです。SELECTやUPDATEのどこかにdeleted_at IS NULLが抜けていると、消したはずの行が画面やAPIに出てきます。

最初に、機械的に拾える候補を洗います。

grep -rn --include='*.ts' --include='*.sql' -E 'FROM +users|JOIN +users|UPDATE +users' src db \
  | grep -v 'deleted_at'

これは同じ行にdeleted_atが無い箇所を出すだけの粗い絞り込みです。複数行にまたがるクエリや、ORMが組み立てるクエリは漏れます。後者はORMの設定でグローバルな絞り込みを入れる手もありますが、その場合も生SQLは別に確認が要ります。

次に、レビューを別のサブエージェントに任せます。公式のベストプラクティスには、仕様と差分を突き合わせるレビューのプロンプト例があり、「スタイルは見ない」と明示するよう勧めています。レビュー役は探すよう頼まれると必ず何か見つけるため、それをすべて直すと不要な抽象化が増えるからです。この形に倣うと、次のように頼めます。

サブエージェントで、このブランチのdiffを CLAUDE.md の論理削除の規約に照らしてレビューして。
users を参照する全クエリで deleted_at IS NULL が付いているか、JOINの相手側にも必要か、
履歴トリガーの対象列が最新か、を確認。正しさに影響する漏れだけ報告し、スタイルは対象外。

サブエージェントの使い方はサブエージェント完全ガイドにまとめています。レビューの結果は、見つかった箇所をそのまま鵜呑みにせず、各クエリを自分で開いて確かめます。

JOINの扱いは見落としやすい点です。orders JOIN usersでusers側が論理削除済みのとき、内部結合はusers.deleted_atを見なければその行を拾います。注文は残したいが、削除済みユーザーの名前は表示したくない、といった要件はクエリごとに違うので、Claudeに推測させず仕様として渡します。

条件漏れを構造で減らす

レビューに頼り切らず、書き方を減らす方向もあります。生きている行だけを返すビューを作り、アプリはそのビューを読む形にすると、条件の書き忘れが起きる場所が減ります。

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

ビューはdeleted_at IS NULLを展開するので、active_usersにWHERE email = $1で問い合わせれば部分インデックスの述語に合います。実際に使われるかはEXPLAINで確かめます。インデックスの選ばれ方はMySQLのスロークエリとインデックス案でも扱っていますが、製品ごとにEXPLAINの出力は違います。

復元とその落とし穴

論理削除を入れると、「戻す」機能も求められます。deleted_atをNULLに戻すだけのUPDATEは単純ですが、ここで部分一意インデックスが働きます。

削除後に同じメールアドレスで別の人が登録していると、復元のUPDATEは一意制約違反で失敗します。これはインデックスが意図どおりに動いた結果で、バグではありません。復元の画面やAPIで、このエラーをどう伝えるかを決めておきます。Claudeに頼むときは、「復元時に衝突したらエラーを返し、勝手に別のメールアドレスへ書き換えない」と明示します。

履歴テーブル側では、復元のUPDATEもUとして1行増えます。deleted_atがNULLに戻った行が復元の記録で、履歴を見れば「削除→復元」の往復が追えます。

履歴テーブルの運用で後から困ること

トリガーは全行の変更に反応するため、更新が多い表では履歴が急速に増えます。保持期間や、古い履歴をどこへ逃がすかは、設計の段階で決めておきます。

もう一つは、changed_byに入るcurrent_userがアプリのDBユーザーであることです。アプリが全員で同じDBユーザーを使っていると、誰が変更したかは履歴から分かりません。アプリ側の利用者IDを残したいなら、セッション変数経由で渡す、履歴に列を足すといった設計が必要になります。これはClaudeの出力に任せず、要件として先に決めます。

最後に、履歴のスキーマはマイグレーションで管理します。Claudeが生成したDDLを、直接本番に流す運用は避けます。マイグレーションファイルとして生成させ、スクラッチDBで検証スクリプトを通してから適用する流れにすると、先に作ったStopフックがそのまま関門になります。

まとめ

論理削除の落とし穴は、一意性が「消した行」まで縛ることです。生きている行だけの部分一意インデックスで直し、検索側のWHEREを述語に合わせれば、制約も性能も保てます。履歴は別表にトリガーで書き、復元時の衝突は仕様として決めておきます。

Claude Codeに任せるなら、期待する挙動をSQLの検証スクリプトとして先に用意し、Stopフックで通るまで終わらせない形が確実です。既存のアプリでは、条件漏れの洗い出しをgrepとサブエージェントのレビューで分けて行います。自動の確認が通っても、復元の仕様や履歴の保持期間のような要件は、人が決める部分として残ります。