/note/tech

DB設計における監査設計に関するメモ

■ 1. 監査設計の全体像

• 監査機能の二大方式:
  • 監査用カラムを本体テーブルに持たせる方式と、変更履歴を別テーブルに記録する方式に大別される
  • 目的や要件に応じて使い分け、組み合わせるのが一般的
• 基本の監査カラム:
  • created_at/created_by、updated_at/updated_by、deleted_at/deleted_by、versionをほぼ全テーブルに持たせる
  • これが最小構成となる
• 論理削除の利点:
  • deleted_atを使えば、本当に消してよいか、参照整合性が壊れないかを後から判断できる
• 論理削除の欠点:
  • 外部キー制約・ユニーク制約との相性が悪く、削除済みレコードも一意制約に引っかかる
• versionの用途:
  • 楽観的排他制御に使い、更新時に自分が読んだバージョンと一致するかをチェックする

■ 2. 変更履歴を残す4方式

• 単純な監査カラムの限界:
  • updated_at/byだけでは変更前の値、何回変更されたかがわからない
  • 変更履歴が必要なら履歴テーブルを設計する
• シャドウテーブル方式:
  • 本体と同構造のxxx_historyを用意し、更新・削除の都度、変更前の行をコピーして保存する
  • トリガーで自動化するか、ORMのフック等アプリケーション層で書き込むかを選択する
  • 各時点のスナップショットがそのまま取れるので監査・復元がしやすい
  • テーブルごとに履歴テーブルが増え、スキーマ変更の二重管理が発生する
• 汎用監査ログテーブル方式:
  • 全テーブル共通の1つのログテーブルに、table_name、record_id、old_values/new_values等をJSONで記録する
  • テーブル追加のたびに履歴テーブルを増やさなくてよい
  • IPアドレスやリクエストIDなど操作コンテキストも一緒に記録しやすい
  • JSONカラムのため検索・集計がしにくく、RDBMSのJSON検索機能に依存する
  • 差分だけを見たい場合はold/newのdiffをアプリ側で計算する必要がある
• イベントソーシング方式:
  • 状態ではなく発生したイベントを全て記録し、現在の状態はイベントの再生で導出する
  • イベントが真実の源であるため、完全な監査証跡が自然に手に入る
  • 設計・実装コストが高く、既存のCRUD中心システムへの後付けは難しい
  • 新規システムや強い監査要件がある場合に向く
• システムバージョニング:
  • SQL Serverやtemporal_tables拡張など、DBMSがネイティブ対応する時系列テーブル機能を使う
  • アプリ側でコードを書かずに履歴管理ができる

■ 3. 方式選定の指針

• 誰がいつ触ったか程度でよい場合:
  • 基本の監査カラムのみで足りる
• 変更前後の値を追いたい場合:
  • シャドウテーブルまたは汎用監査ログを用いる
• 改ざん不可な証跡が必要な場合:
  • 追記専用の監査ログにハッシュチェーン等を組み合わせる
• ドメインイベントとして意味のある履歴が欲しい場合:
  • イベントソーシングを選ぶ
• DB側で自動管理し実装コストを抑えたい場合:
  • DBMSのTemporal Table機能を使う

■ 4. 実装上の注意点

• 書き込み方式のトレードオフ:
  • トリガー実装は漏れなく確実だが、DBロジックが複雑化しデバッグしにくい
  • アプリ層のORMフック・AOPは柔軟だが、バッチ処理やDBAの直接SQLを防げない
  • 両方を併用するケースも多い
• パフォーマンス:
  • 高頻度更新テーブルに毎回履歴INSERTするとI/O負荷が増える
  • パーティショニングやメッセージキュー経由の非同期記録も検討対象になる
• 保持期間とアーカイブ:
  • 会計7年など法令要件に応じて保存期間を決める
  • 古いログはS3等の別ストレージにアーカイブする設計にする
• 改ざん防止:
  • 監査ログをUPDATE/DELETEできないようDB権限を絞り、追記のみ許可する
  • ハッシュチェーンや外部WORMストレージで完全性を担保する
• 変更主体の区別:
  • アプリケーションユーザーだけでなく、バッチ処理・システム連携による変更も区別できるようにする

■ 5. 基本監査フィールドは監査証跡ではない

• 監査としては機能しない:
  • 最終更新しかわからないため、誰が何をいつ変えたかの証跡にはならない
• 実体は運用メタデータ:
  • created_at/by、updated_at/byは一覧画面での最終更新者確認、ソート、キャッシュ無効化のトリガー等に使う
• versionの位置づけ:
  • 監査とは無関係であり、純粋に排他制御の仕組みである
• 導かれる結論:
  • 監査要件がある場合、これらのフィールドをもって監査要件を満たしたと考えるべきではない

■ 6. ヒストリーテーブルとイベントソーシングの評価

• ヒストリーテーブルは機能要件:
  • 注文のステータス遷移履歴の画面表示、承認フローの経緯提示など機能要件として必要になる
  • この場合はドメインモデルの一部であり、監査ログとは別物として設計すべきである
• 監査要件も満たしうる:
  • 全カラムの変更前後が追える構造にしておけば、機能要件として作った履歴が結果的に監査要件も満たす
• 量産はコストに見合わない:
  • 監査のためだけにテーブルごとにヒストリーテーブルを量産するのは割に合わず、汎用監査ログに任せるべきである
• イベントソーシングは過剰:
  • CRUD中心の業務システムに監査目的だけで導入するのは、状態導出コスト、実装複雑度、学習コストに見合わない
  • 決済や在庫移動などドメイン自体がイベントの連鎖として自然にモデリングできる場合を除き避けるべきである

■ 7. 汎用監査ログの留保点

• 書き込み経路の網羅性:
  • アプリ層のORMフック等で書く場合、バッチ処理、DBAの直接SQL、他システムからの直接書き込みが抜け穴になる
  • トリガーやCDCでDB層から捕捉する方が漏れがない
  • アプリ層でしか書かない設計は抜け道のある監査となり、要件次第では監査ログとして無意味になりうる
• 検索性能とクエリ要件:
  • JSONに差分を格納する方式はテーブル追加コストがゼロで柔軟である
  • 特定カラムの変更履歴追跡や特定ユーザーの横断検索はJSON演算子頼みで重くなる
  • 監査ログを実際に読む運用の頻度次第で、field_name、old_value、new_valueを行単位で持つ構造化が必要になる
• 改ざん耐性:
  • 汎用監査ログもただのテーブルであり、UPDATE/DELETE権限を絞らないと改ざんされる
  • 汎用ログに一本化する場合は、厳格なアクセス制御の設計とセットで考える必要がある

■ 8. DBに監査ログを置く理由

• トランザクション整合性:
  • 業務データの変更と監査ログの記録は同一トランザクションで確定してほしいものである
  • DBに書けば、業務データは更新されたが監査ログが書かれないという不整合が起きない
  • ロールバックすれば監査ログもロールバックされる
• 非同期経路の弱点:
  • ログ出力から非同期アーカイブ、DWHという経路では業務データと監査記録の一致保証が弱くなる
  • ファイル出力の場合、書き込み成功直後のプロセスクラッシュでフラッシュ前のログが欠落しうる
• 即時参照ニーズ:
  • 管理画面やサポート業務でその場に変更者を確認したい場合、DWH経由ではレイテンシが許容できない
  • BigQueryへのストリーミングインサートとクエリでも数秒から数十秒のラグが発生する
  • この用途はDB内やElasticsearch等の低レイテンシストアに直近履歴を持たせる方が向く

■ 9. DWHに任せる理由

• 分析・集計クエリ:
  • 異常頻度の変更検出やユーザー操作パターンの横断分析はカラムナDWHが圧倒的に得意で安い
• 長期保持コスト:
  • 7年保持等が必要な場合、本番DBに持ち続けるのはストレージ・バックアップ・インデックスの面で非効率である
  • DWHとS3/GCS等の安価なオブジェクトストレージへのアーカイブの方が合理的である
• 本番DBの肥大化回避:
  • 監査ログテーブルは本体テーブルより行数が増えやすい
  • 本番OLTP DBに置き続けるとインデックス肥大化やバックアップ時間増加の要因になる

■ 10. CDCを起点にしたハイブリッド構成

• 構成の概要:
  • 本番DBのWAL/binlogをDebezium等でCDCし、Kafka等のストリームに流す
  • 短期は運用画面用にDBやRedisへ直近履歴を投影し、長期は分析・コンプライアンス用にDWHへロードする
• 利点:
  • 書き込み経路を問わず全変更を捕捉でき、アプリ層のバグやバッチの抜け道を気にしなくてよい
  • WALに書かれた時点でコミット済みのため、業務トランザクションとのズレが原理的に起きない
  • 即時参照は短期ストア、長期保持はDWHと用途ごとに最適なストアを選べる
  • 本番DBの肥大化を避けられる
• 欠点:
  • CDC基盤自体の運用コスト・複雑度が増す
  • 小中規模システムでは明らかにオーバーエンジニアリングになる
• 分岐点:
  • 即時参照ニーズの有無と、書き込み経路の網羅性をどこまで求めるかがDBかログ/DWHかを分ける

■ 11. ログ出力からアーカイブ戦略の要件

• 前提の確認:
  • 即時参照不要かつ調査はエンジニアで可なら、ログ出力からアーカイブが理にかなう
  • DB内に監査ログテーブルを持つ複雑さと運用DB肥大化リスクを負うメリットが小さい
• 監査範囲の事前確定:
  • アプリ層を通らないバッチ、DBAの直接SQL、他システムの直接書き込みは捕捉されない
  • 業務上のユーザー操作が追えればよい程度ならアプリ層ログで十分である
  • 金融・医療系などDB変更全体の証跡が必要ならCDCでログ生成をDB層に寄せる必要がある
• ログの構造化:
  • timestamp、actor_id、actor_type、action、resource_type、resource_id、changes等を構造化する
  • request_id/trace_idを入れると、分散システムでどのAPIリクエストが引き金かを横断的に追える
  • actor_typeでシステム・バッチ由来の変更とユーザー操作を区別する
• 改ざん防止:
  • アーカイブ先も要件次第で追記のみ・削除不可のWORM設定にする
  • S3ならObject LockのCompliance mode、GCSならBucket Lockを使う
  • 要件が厳しい場合は各エントリに前エントリのハッシュを含めるハッシュチェーンを使う
• ライフサイクル管理:
  • ホット期間は直近数ヶ月から1年でDWHから即クエリ可能にする
  • コールド期間は安価なストレージへアーカイブし必要時のみリストアする
• 調査時のクエリ経路:
  • 実際に調査が発生した時、BigQueryにSQLを書けば追える状態かを事前に確認する
  • サービスごとにキー名やネストが不揃いだと調査コストが跳ね上がる
  • 共通のロギングライブラリ・ミドルウェアを整備し、出力時点でスキーマを統一する

■ 12. WAL/binlogを整形してログ保存する方式

• 一般的なアプローチ:
  • Debezium、AWS DMS、Google Cloud Datastreamが実際に採用しているメジャーな方式である
• メリット:
  • アプリ層、バッチ、DBA直接操作のいずれからの変更もWAL/binlogに必ず記録され、経路を完全網羅できる
  • コミット済みの変更しか含まないため、業務データは変わったがログが無いというズレが起きない
  • 読み取りは非同期でレプリケーションと同じ仕組みのため、本番トランザクションへの性能影響が小さい
• 業務的意味の欠落:
  • WALには行のカラム値がどう変わったかしか記録されず、誰が、なぜのアプリケーションコンテキストが存在しない
  • actor_idはupdated_byカラムに書くか、セッション変数をトランザクション内に埋め込んでメタデータ経由で拾う
  • なぜという業務的意図はDBの変更から復元不能であり、アプリ側でイベントとして別途記録する必要がある
• Before/Afterイメージの設定:
  • MySQLはbinlog_row_image = FULLにしないとUPDATE時に変更カラムしか記録されない
  • PostgreSQLはREPLICA IDENTITY FULLを設定しないとUPDATE/DELETEの旧データが完全に取れない
  • ストレージ・I/O負荷とのトレードオフであり、全テーブルに設定するかは検討を要する
• DDLの扱い:
  • ALTER TABLE等でイベントのスキーマ自体が変わるため、下流との整合を取るスキーマレジストリ等が必要になる
  • カラムがいつ追加・削除されたかも証跡として重要であり、DDLイベントも記録する設計が望ましい
• 機密情報のマスキング:
  • WAL/binlogはカラムレベルの制御ができず、行の全カラムがそのまま流れる
  • 個人情報や機密カラムはKafka Connect等の下流でマスキング・暗号化を挟む必要がある
• トランザクション境界の再構成:
  • 複数テーブルにまたがる1トランザクションはWAL上で複数イベントの連なりになる
  • まとめて1つの監査イベントとして扱うにはtransaction_idやLSN/GTIDでの再構成処理が必要になる

■ 13. updated_byの構造的限界

• 誰がまでしか語らない:
  • updated_byからわかるのは最終更新したユーザーという事実のみである
  • なぜその値に変えたのか、どの業務操作の一部か、どういう文脈かは一切わからない
• WALと同じ問題:
  • updated_byはWAL/binlogにactor情報を追加しただけであり、業務的意味の欠落という本質的問題は解消しない
  • actor_id、timestamp、IPアドレスを足しても、行がどう変わったかの記述にとどまる
• アプリケーションログが必須な理由:
  • 業務的な意味はアプリケーションがビジネスロジックを実行している瞬間にしか存在しない
  • DB側にどれだけ工夫を凝らしても事後的には復元できない
• 具体例:
  • 顧客によるキャンセル、在庫切れの自動キャンセル、不正検知によるブロックは行変更としては全く同じである
  • 監査・コンプライアンスの文脈では全く異なる意味を持ち、区別できるのは業務ロジックを実行したコードだけである
• 両者は補完関係:
  • WAL/binlogは網羅的だが業務的意味を持たず、改ざん耐性のある事実を提供する
  • アプリケーションログは業務的意味を与えるが、アプリ層を経由しない変更を捕捉できない
  • 厳しい要件があるほどCDCとアプリログの両方が必要になり、CDCはアプリログを不要にするものではない

■ 14. 3層構成ではなく主従関係のある2層

• 誰が・いつだけでは手詰まり:
  • updated_by/updated_atだけでは、何を何から何に変えたかがわからず調査時に行き詰まる
• 基本監査フィールドは冗長:
  • アプリケーションログをactor_id、old/new値、業務コンテキスト付きで設計するなら情報が重複する
  • 独自の価値は排他制御用のversionと、一覧画面用に最終更新者を複製するUI都合のキャッシュのみである
  • 監査証跡としての独立した役割はほぼ無い
• 実質的な構成:
  • アプリケーションログを主軸とし、必要に応じてCDC/binlogを完全性担保のために足す2層構成が筋がよい
  • 基本監査フィールドはUI用の複製データという位置づけに下げる
• 過剰設計の回避:
  • 3つが対等に並ぶセットと捉えるより、主従関係のある構成と捉える方が過剰設計を避けやすい

■ 15. アプリログのみでは直接更新を捕捉できない

• 運用ルールによる統制はザル:
  • バッチもDBA操作も全てアプリ経由という前提は現実にはほぼ成立しない
  • 障害対応では、運用ルールを整備していてもDBAや開発者が直接SQLを叩く事態が現実に起きる
  • マイグレーションやデータ補正は、性質上ビジネスロジックを経由しない直接SQLで行われることが多い
  • DB接続情報にアクセスできる人が1人でもいれば技術的には直接更新が可能である
• 監査観点での評価:
  • 運用ルールでアプリ層に閉じている状態は、監査の観点では閉じていないのと同義である
  • 監査・コンプライアンスで問われるのはルール上禁止かではなく、技術的に迂回可能かである
• 判断基準の修正:
  • 正しい分岐点はDBに直接書き込める技術的な経路や認証情報が存在するかである
  • DBAや緊急対応用アカウント等が存在するならアプリログでは足りず、CDCまたはDBトリガーが必要になる
  • 書き込み経路が物理的にアプリケーションサーバーのみに制限された組織は稀である
• 穴を塞ぐ2つの方向性:
  • 本番DBへの直接接続権限を原則ゼロにし、緊急時のみ一時発行・自動失効させ、その発行と利用もログ化する
  • 権限を絞る方式は運用コストが高く、多くの組織で緊急対応の柔軟性とトレードオフになる
  • 直接アクセスを許容した上でWAL/binlog経由で全て捕捉する方が、柔軟性と監査の完全性を両立でき現実的である
• 訂正後の結論:
  • 監査・コンプライアンス要件がある時点で、CDCまたはDBトリガーベースの捕捉をデフォルト前提とすべきである
  • アプリログは意味づけを担う層として必須だが、DB変更全体を追うことの代替にはならない

■ 16. CDC/binlogのデータ量試算

• サイズを決める要因:
  • トランザクション量と行サイズの積が、設定と実装の選択で数倍から十数倍のレンジに変わる
• WAL/binlog自体のオーバーヘッド:
  • MySQLのbinlog_row_image = FULLでは変更されていないカラムも含め変更前後の全カラムが記録される
  • PostgreSQLのREPLICA IDENTITYをFULLにするとUPDATE/DELETEのWAL量が大きく増える
  • FULLイメージ化すると1行あたりの記録サイズは行サイズの2倍前後になる
• シリアライズ形式が最大のレバー:
  • スキーマ埋め込みなしのJSONは行サイズの2倍から4倍になる
  • Debeziumデフォルトのスキーマ埋め込みJSONは5倍から10倍以上になることもある
  • Avro/Protobufとスキーマレジストリの組み合わせは1.5倍から3倍程度に収まる
  • 本番運用ではAvro/ProtobufとSchema Registryの組み合わせがほぼ必須である
• 圧縮の効果:
  • KafkaのProducer側圧縮はZstdで3倍から5倍程度の圧縮率が一般的である
  • BigQueryやParquetのカラムナ格納では元データの5分の1から10分の1程度まで縮む
• 試算例:
  • 1行500バイト、平均100 UPDATE/秒のordersテーブルを想定する
  • WAL/binlogは日次約8.6GB、Debezium経由のKafkaは圧縮前で日次約11GBとなる
  • Zstd圧縮後は約2.7GB/day、S3へのParquetアーカイブでは0.5から1GB/dayまで縮む
  • スキーマ埋め込みJSONのまま運用すると数十GB/dayに膨らむこともある
• テーブル数の考慮:
  • 監査対象テーブルは数十から数百あるため、テーブルごとの更新頻度と行サイズを積み上げる必要がある
  • 注文ステータス、在庫数、セッション情報など高頻度更新テーブルが全体のボリュームを支配しがちである
  • 金銭・個人情報関連を優先的に対象化し、それ以外を対象外や低頻度スナップショットにすれば大きく削減できる
• 見積もり手順:
  • 対象テーブルごとに1日の平均更新件数をスロークエリログやAPMから実測する
  • information_schemaから1行あたりのバイト数を概算取得する
  • 件数、行サイズ、before/afterの2倍、シリアライズ係数を掛けて粗く見積もる
  • KafkaとDWH側の圧縮率は理論値でなく実データのPoCで測定する

■ 17. 監査対象テーブルの選定基準

• マスタテーブルの意味が薄い理由:
  • 商品マスタやカテゴリマスタは初期投入後ほとんど更新されず、容量コストに対しリターンが小さい
  • マスタデータの改ざんが実害に直結するケースは少ない
  • マスタは参照される側であり、いつ変わったかさえ分かれば以降の取引データとの整合性は追跡可能である
• 優先度が高いテーブルの特徴:
  • 注文金額、決済ステータス、返金処理、ポイント残高など金銭が動くもの
  • 権限ロール、APIキー、認証情報などアクセス制御に関わるもの
  • 氏名・住所等のPIIカラムを含むもの
  • 在庫調整、値引き・クーポン適用、承認フローなど不正の温床になりうるもの
  • 会計仕訳や取引履歴など法令が直接保存を求めるもの
• 選定プロセス:
  • まず更新頻度で機械的にフィルタし、ほぼ変更されないテーブルを除外する
  • 残りを金銭・PII・権限のいずれかに該当するかで監査必須・任意・対象外にランク分けする
  • 法令上必須な範囲をコンプライアンス担当・法務とすり合わせる
  • 容量・コストの見積もりを踏まえ、任意ランクをどこまでCDC対象に含めるか判断する
• マスタ除外の例外:
  • 価格マスタの改ざんによる不正値引き、権限マスタの改ざんによる不正アクセスは実質的に金銭・権限に直結する
  • 更新頻度が低くてもむしろ優先的に監査対象にすべきである
  • マスタかトランザクションかという技術的分類ではなく、金銭・権限・PIIに直結するかという業務的軸で判断する

■ 18. リスクベースの層分けと組織的合意

• 設計判断としての妥当性:
  • クリティカルなもののみCDCで捕捉し、残りをアプリケーションログとするのは実務的に妥当な落とし所である
  • 全テーブル一律CDCは多くの組織にとってコスト超過となる
• リスク受容としての明文化:
  • アプリログのみにはDB直接更新を捕捉できない構造的な穴がある
  • この判断はクリティカルでないテーブルの捕捉漏れリスクを受容するという意思決定そのものである
  • エンジニアの設計判断だけで決めると、監査時に完全性が担保されない理由を組織として説明できなくなる
  • CDC対象テーブルのリストと選定基準をコンプライアンス担当・法務と正式に合意しておく
• 3段階の層分け:
  • Tier 1: 金銭・権限・PIIに直結し法令が要求するもので、CDCとアプリログを併用する
  • Tier 2: 注文ステータスや在庫など業務上重要なもので、アプリログとトリガーベースの変更検知を用いる
  • Tier 3: マスタや低頻度・低リスクのもので、基本監査フィールドのみか監査なしとする
• トリガーベースの中間層:
  • DBトリガーで変更前後の値を汎用監査ログテーブルに書き込む方式はCDCほどのインフラコストがかからない
  • DB直接更新もある程度捕捉でき、アプリログのみとフルCDCの中間として機能する
• リスクと軽減策:
  • 新機能追加時にテーブルが金銭に触れるかをチェックリスト化しないと、Tier分類が次第に形骸化する
  • Tier 3では直接アクセスできる人・システムを最小化し、アクセス制御の強化でリスクを相殺する
  • 選定基準と承認履歴を文書化しておくと、実際の監査対応がスムーズになる

(2026/08/10)