データモデル設計ガイド

履歴・監査ログ設計 — 別テーブル vs システムバージョニング

変更履歴の残し方を比較 — シャドウテーブル / システムバージョニング / イベントソーシング。

なぜ履歴が必要か

「誰が・いつ・何を・どう変更したか」は、監査対応、不具合調査、データ復旧、利用者への説明責任、機械学習の時点スナップショットなど、あらゆる場面で求められます。現行行だけを持つ CRUD 設計では、過去の値は上書きで失われるため、別の仕組みで残す必要があります。

主な選択肢は次の 3 つです。

別テーブル方式 (shadow table): orders に対して orders_history を別途持ち、更新のたびに旧値を INSERT する
システムバージョニング (temporal table): DB 機能で自動的に履歴を記録（SQL Server、MariaDB、Oracle）
イベントソーシング: 現在状態を「ある時点のスナップショット + イベントの積分」で表現

別テーブル方式 (shadow history table)

最もポータブルで広く使われる方式です。本体テーブルに加えて、同じ列構成 + valid_from/valid_to/operation/changed_by を持った履歴テーブルを用意し、BEFORE UPDATE / BEFORE DELETE トリガで旧値を INSERT します。

利点: どの RDBMS でも使える、クエリで自由に履歴を参照できる。欠点: トリガのメンテが必要、スキーマ変更時に履歴側も同期する必要あり。

sql

CREATE TABLE orders_history (
  history_id  BIGSERIAL    PRIMARY KEY,
  id          BIGINT       NOT NULL,   -- orders.id と同じ
  status      VARCHAR(20),
  total       NUMERIC(12,2),
  operation   CHAR(1)      NOT NULL,   -- I / U / D
  changed_by  BIGINT,
  changed_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_orders_history() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO orders_history (id, status, total, operation, changed_by)
  VALUES (OLD.id, OLD.status, OLD.total,
          CASE TG_OP WHEN 'UPDATE' THEN 'U' WHEN 'DELETE' THEN 'D' END,
          current_setting('app.user_id', true)::BIGINT);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

システムバージョニング (temporal table)

ANSI SQL:2011 で標準化された機能で、DB が自動的に履歴を管理してくれます。実装状況:

SQL Server: WITH (SYSTEM_VERSIONING = ON) で履歴テーブルを自動連携。FOR SYSTEM_TIME AS OF '2026-01-01' で過去時点のクエリが可能
MariaDB: WITH SYSTEM VERSIONING 句で同等の機能
Oracle: SQL:2011 準拠ではないが、類似機能として Flashback Query（AS OF TIMESTAMP ...）と Flashback Data Archive（長期履歴保持）を提供
PostgreSQL / MySQL / SQLite: 標準機能なし（拡張やトリガで代用）

利点: アプリコード不要で履歴が残る、過去時点クエリ (AS OF) が 1 行で書ける。欠点: RDBMS ロックイン、履歴の見え方をカスタマイズしにくい。

sql

-- SQL Server: システムバージョニング
CREATE TABLE orders (
  id          BIGINT       PRIMARY KEY,
  status      VARCHAR(20)  NOT NULL,
  total       DECIMAL(12,2) NOT NULL,
  valid_from  DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  valid_to    DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON);

-- 過去時点クエリ
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2026-01-01 00:00:00';

イベントソーシングとは何が違うか

イベントソーシングは、現在状態を一切保存せず、状態遷移を引き起こした「イベント」だけを追記していく方式です。現在状態はイベントを再生して計算します。

履歴テーブル方式が 「現在状態 + 過去の写真アルバム」 なのに対し、
イベントソーシングは 「最初からの全イベントのログ」 を真実とする

違い:

イベントソーシングはビジネスの意図（なぜ変わったか）を記録する。履歴テーブルは値の before/after だけ
読み取りのためにマテリアライズドビュー / リードモデルを別途持つ必要があり、構成が複雑
既存 CRUD システムからの移行コストが高く、採用は慎重に判断する

容量とパフォーマンスの問題

履歴は指数的に増えます。1 行 1 KB のテーブルが毎日 1 万更新なら、年間 3.6 GB の履歴が溜まります。何も対策しないと、2〜3 年で履歴テーブルが現行テーブルの 100 倍以上のサイズになることも珍しくありません。

対策:

パーティショニング: changed_at で月次 / 年次パーティション。古いデータの DROP PARTITION が秒で終わる
変更列の差分のみ記録: 全列ダンプではなく、変わった列だけを JSON で記録
保存期間ポリシー: 法定保存期間 (例: 7 年) + α で自動削除
コールドストレージへの退避: S3 / Parquet に移し、クエリ可能な状態で安価に保管

誰が変更したかを記録する

履歴で最も役立つのは changed_by (誰が変更したか)ですが、DB 側はアプリのログインユーザーを知りません。解決策:

セッション変数に仕込む: PostgreSQL なら SET LOCAL app.user_id = 42; → トリガで current_setting('app.user_id') を読む
トランザクション開始時に必ず設定する、を ORM 共通フックに仕込む
MySQL なら @user_id セッション変数、SQL Server なら SESSION_CONTEXT()

これを忘れると「変更の事実は残るが誰がやったか不明」になり、監査で致命的になります。