MySQL | SQLite経験者向け、30日で習得するMySQL：実務応用 － Day26 ログ設計

SQL MySQL
スポンサーリンク

Day26 後半のゴール

「“証拠としてのログ”と“業務で使う履歴”をテーブル設計まで落とせるようになる」

前半で、「監査ログ」と「履歴管理」の目的は整理できました。
後半では、それを実際のテーブル設計・実装パターンに落としていきます。

ここでのゴールは次のイメージです。

監査ログ用のテーブルを、自分で設計できる
履歴テーブル（有効期間・バージョン管理）の典型パターンを理解する
「何でもログる」の危険性と、個人情報をログに書くときの注意点を意識できる

コードを書く前に、「どう設計するか」をしっかり固めます。

監査ログテーブルの設計例

「“誰が・いつ・何をしたか”を一行で表現する」

まずは監査ログ。
目的は「誰が・いつ・どの対象に・何をしたか」を後から追えるようにすることでした。

シンプルな監査ログテーブルの例を考えてみます。

CREATE TABLE audit_logs (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  occurred_at   DATETIME      NOT NULL,
  actor_type    VARCHAR(20)   NOT NULL,  -- 'user' / 'system' / 'admin' など
  actor_id      BIGINT        NULL,      -- ユーザーIDなど（匿名操作ならNULL）
  ip_address    VARCHAR(45)   NULL,
  action        VARCHAR(50)   NOT NULL,  -- 'login_success' / 'update_user' など
  target_type   VARCHAR(50)   NULL,      -- 'user' / 'order' など
  target_id     BIGINT        NULL,
  detail        JSON          NULL       -- 追加情報（変更前後など）をJSONで
) ENGINE=InnoDB;
SQL

ここでのポイントを一つずつかみ砕きます。

occurred_at は「いつ」を表すので必須です。
actor_type と actor_id で「誰が」を表します。
IPアドレスも、特にログイン系では重要な手がかりになります。
action は「何をしたか」を表すラベルです。
target_type と target_id で「どの対象に対して」を表します。
detail は、必要に応じて JSON で追加情報を入れる“拡張枠”です。

この設計にしておくと、例えば次のような行が入ります。

2025-05-10 10:00、actor_type=user, actor_id=123, action=login_success
2025-05-10 10:05、actor_type=admin, actor_id=1, action=update_user, target_type=user, target_id=123

detail に「変更前後の値」を入れるかどうかは、監査ポリシー次第です。
個人情報をそのまま detail に書きすぎると、ログ自体が“危険なデータの塊”になるので、ここは慎重に決めます。

監査ログをいつ・どう書くか

「“DBトリガー任せ”ではなく“アプリの意図として書く”」

監査ログをどこで書くか、という設計も重要です。

大きく分けると、二つのパターンがあります。

アプリケーション側で INSERT する
DBのトリガーで自動的に INSERT する

初心者が「楽そう」と感じるのはトリガーですが、
実務的には「アプリ側で書く」方がコントロールしやすいことが多いです。

理由はこうです。

誰が（ログインユーザーID）はアプリ側が一番よく知っている
どの画面・どのAPIからの操作かもアプリ側で分かる
ビジネスルールに応じて「これは監査対象」「これは対象外」を分けやすい

例えば、ユーザー情報更新APIの中で、
更新が成功したタイミングで監査ログを1行書く、というイメージです。

INSERT INTO audit_logs (
  occurred_at, actor_type, actor_id, ip_address,
  action, target_type, target_id, detail
) VALUES (
  NOW(), 'admin', 1, '203.0.113.10',
  'update_user', 'user', 123,
  JSON_OBJECT('changed_fields', JSON_ARRAY('email', 'name'))
);
SQL

ここでは、detail に「どの項目が変わったか」だけを入れています。
実際の値（メールアドレスの具体的な文字列など）を入れるかどうかは、
セキュリティポリシーと相談です。

履歴テーブルの典型パターン（有効期間方式）

「“いつからいつまでこの値だったか”をテーブルで表す」

次に、履歴管理の代表的なパターンを見ます。
ここでは「有効期間方式」を扱います。

例として、「ユーザーのメールアドレスの履歴」を管理したいとします。

CREATE TABLE user_email_history (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT      NOT NULL,
  email       VARCHAR(255) NOT NULL,
  valid_from  DATETIME    NOT NULL,
  valid_to    DATETIME    NULL,
  CONSTRAINT fk_user_email_history_user
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
SQL

このテーブルの意味はこうです。

1行が「ある期間に有効だったメールアドレス」を表す
valid_from 〜 valid_to の間、その email が有効だった
valid_to が NULL の行が「現在有効なメールアドレス」

例えば、ユーザーID=123のメールアドレスがこう変わったとします。

2024-01-01〜2025-05-10: old@example.com
2025-05-10〜現在: new@example.com

このとき、テーブルには次のような2行が入ります。

user_id=123, email=old@example.com, valid_from=2024-01-01, valid_to=2025-05-10
user_id=123, email=new@example.com, valid_from=2025-05-10, valid_to=NULL

これで、

「2024年12月31日時点のメールアドレスは？」
→ WHERE user_id=123 AND '2024-12-31' BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31')

のようなクエリで取れるようになります。

履歴テーブルをどう更新するか

「“古い行を閉じて、新しい行を開く”という操作をセットで行う」

有効期間方式の履歴テーブルでは、
更新処理が少しだけ手順を踏みます。

ユーザーのメールアドレスを変更するときの流れを考えます。

まず、「今有効な行」を特定します。

SELECT *
FROM user_email_history
WHERE user_id = 123 AND valid_to IS NULL;
SQL

その行の valid_to を「今の時刻」に更新します。

UPDATE user_email_history
SET valid_to = NOW()
WHERE user_id = 123 AND valid_to IS NULL;
SQL

次に、新しいメールアドレスの行を INSERT します。

INSERT INTO user_email_history (user_id, email, valid_from, valid_to)
VALUES (123, 'new@example.com', NOW(), NULL);
SQL

この二つの操作は、必ずトランザクションでまとめます。

START TRANSACTION;

UPDATE user_email_history
SET valid_to = NOW()
WHERE user_id = 123 AND valid_to IS NULL;

INSERT INTO user_email_history (user_id, email, valid_from, valid_to)
VALUES (123, 'new@example.com', NOW(), NULL);

COMMIT;
SQL

これにより、

「有効期間が重なる行が存在しない」
「常に1つだけ“現在有効な行”がある」

という状態を保てます。

アプリ側から見ると、

「メールアドレスを更新する」という1つの操作の裏で、
履歴テーブルでは2つのSQLが動いている

というイメージです。

バージョン番号方式という考え方

「“何番目のバージョンか”で履歴を追う」

有効期間方式の代わりに、
「バージョン番号」で履歴を管理するパターンもあります。

例えば、ユーザーのプロフィール全体をバージョン管理したい場合。

CREATE TABLE user_profile_history (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT      NOT NULL,
  version     INT         NOT NULL,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  created_at  DATETIME    NOT NULL,
  CONSTRAINT fk_user_profile_history_user
    FOREIGN KEY (user_id) REFERENCES users(id)
);
SQL

ここでは、

version=1 が最初の状態
version=2 が2回目の変更後
version=3 が3回目の変更後

というように、「何番目の状態か」を表します。

最新の状態を取りたいときは、

SELECT *
FROM user_profile_history
WHERE user_id = 123
ORDER BY version DESC
LIMIT 1;
SQL

のように書きます。

有効期間方式との違いは、

「いつ有効だったか」よりも「何回目の変更か」を重視する

という点です。

どちらを採用するかは、
業務要件（時間軸で見たいか、バージョンとして見たいか）で決めます。

個人情報をログ・履歴に書くときの注意点

「“残すこと自体がリスク”になることを忘れない」

監査ログ・履歴管理は、「残す」ことが目的ですが、
個人情報を残しすぎると、それ自体がリスクになります。

意識しておきたいポイントは次のようなものです。

パスワードの平文を絶対にログ・履歴に書かない
クレジットカード番号などは、原則としてDBにもログにも持たない（トークン化などを使う）
メールアドレス・住所などを監査ログの detail に丸ごと書かない

例えば、監査ログの detail に、

変更前メールアドレス: old@example.com
変更後メールアドレス: new@example.com

といった情報をそのまま書くと、
audit_logs テーブルが「個人情報の塊」になります。

代わりに、

「どの項目が変わったか」だけを記録する
「マスクした値」（例：a***e@example.com）だけを残す

といった工夫も考えられます。

ログ設計は、「何を残すか」と同じくらい
「何をあえて残さないか」も重要です。

Day26 後半のまとめ

監査ログをDBで設計する場合は、audit_logs のようなテーブルに occurred_at（いつ）、actor_type / actor_id（誰が）、action（何をしたか）、target_type / target_id（どの対象に） を1行で表現し、必要に応じて detail をJSONで持たせることで、「ログイン成功／失敗」「重要データの更新」「削除操作」などを後から追える“証拠”を残し、これはDBトリガー任せではなく、アプリ側の意図として「どの操作を監査対象にするか」を決めて INSERT するのが現実的なやり方になる。
一方、履歴管理では、有効期間方式（valid_from〜valid_to で「いつその値が有効だったか」を表す）やバージョン番号方式（version で「何番目の状態か」を表す）といったパターンを使って、「過去の状態を業務で参照・集計できるテーブル」を設計しつつ、パスワードやクレジットカード番号のようなセンシティブ情報はログ・履歴に残さない、メールアドレスや住所も必要最小限にとどめるなど、「残すこと自体がリスクになる」視点を持って、“証拠として十分・でも過剰ではない”バランスを取ることが、実務的なログ設計の肝になっていく。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました