エピソード紹介

こんな方へ特におすすめ

新規アプリケーションのER設計を担当する方
監査ログの設計や、データの保持ポリシーに関心のあるエンジニア

概要

こんにちは。サイオステクノロジーのはらちゃんです！

白紙の状態からER設計を考えることをあまり経験したことがないため、ハードルが高いと感じていました。

今回、0から考える良い機会であったため、「どう考えればよいか」「どのようなことを気を付けるべきか」など私なりに調べました。

—

本シリーズでは、Copilotを活用しつつ、クリーンアーキテクチャに沿って小規模なプロダクト「RepoScanner」を設計・実装した経緯をまとめます。

このエピソードは、アプリケーションの根幹となるER設計と、運用を見据えた監査ログの設計について、私がどう考えたかという視点で解説します。

作業手順

要件の整理

いきなりテーブル設計をしろと言われても難しいので、作りたいものの情報を整理していきます。

ここではVSCodeの拡張機能であるdraw.ioを使って、簡単にまとめていきます。

コンテキスト図

まずは、ユーザーと「RepoScanner」の関係性（コンテキスト・ダイアグラム）から整理しました。

DBなどは考えず、何をするシステムにしたいかに注目します。

本来、業務システムでは、より詳細にシステム管理者 / 担当者 / 利用者など踏まえて図示する必要があります。

ユースケース図

続いて、どのようなユースケースが考えられるかを簡単に表現します。

これらの過程で、システムとしてはどこまで考えるか、今回はどこまで実装するかなど考えておくと次の作業がスムーズになると思います。

概念図

最後に、ユースケースを見ながら概念モデルを考えます。

あとから修正する前提で、ざっくりと書いてください。

ER設計

RepoScannerは、GitHub Actionsで収集したデータの情報をDBに保存し、アプリ側から読み取る構成です。

要件を満たしつつ、将来的な監査や障害調査といった運用に耐えうる設計を行うため、主要エンティティを定義します。

repository: スキャン対象となるリポジトリの基本情報。
snapshot: 特定のタイミングで収集したリポジトリのメタデータ群。
pull_request: リポジトリに紐づくPRの情報。
operation_log: 誰が、いつ、何をしたか（収集ジョブの実行など）を記録する監査ログ。

ポイント: エンティティの切り出し方

最初は「repositoryテーブルに全部の情報を入れちゃえば楽じゃない？」と考えがちでした。

以下の視点を持つと、どの粒度でテーブルを分けるか判断しやすいと思います。

データの増え方
リポジトリ名はめったに変わらず、スナップショットは実行するたびに増える。これらを混ぜると、リポジトリ名の取得のために膨大なデータを読み込むことになる。
データの寿命
リポジトリ情報は長く残るが、スナップショットは一定期間で消す可能性がある。

ポイント: 監査ログのテーブル準備

「機能を作るためのテーブル」だけでなく、初期段階から「運用・監査のためのテーブル」を組み込んでおきます。

本番稼働に向けたセキュリティなどの非機能要件に柔軟な対応をすることができます。

PostgreSQL用 DDLの実例

実際のPostgreSQL用のDDLスニペットをご紹介します。

クリーンアーキテクチャのFrameworks & Drivers層で、これらのテーブルに対してCRUD操作を行います。

-- 1. リポジトリテーブル
CREATE TABLE repository (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  owner TEXT NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- 2. スナップショットテーブル
CREATE TABLE snapshot (
  id UUID PRIMARY KEY,
  repository_id UUID REFERENCES repository(id) ON DELETE CASCADE,
  collected_at timestamptz NOT NULL,
  data jsonb NOT NULL -- 柔軟なメタデータはJSONBで保持
);

-- 3. 監査・操作ログテーブル
CREATE TABLE operation_log (
  id BIGSERIAL PRIMARY KEY,
  actor TEXT NOT NULL,       -- 実行者（ユーザーやCIのBot名）
  action TEXT NOT NULL,      -- アクション内容（例: "SNAPSHOT_COLLECTED"）
  target_type TEXT,          -- 対象のリソース種別
  target_id TEXT,            -- 対象のID
  details jsonb,             -- 変更内容などの詳細
  occurred_at timestamptz DEFAULT now()
);

PostgreSQLのjsonb型は、スキーマレスにデータを放り込めるため非常に柔軟で便利です。

snapshot.dataやoperation_log.detailsのように、構造が頻繁に変わるメタデータを保存するのに適しています。

GitHubから取得できるデータは多岐にわたります。

最初から全ての項目をカラム定義するのは現実的ではないため、まずは jsonb で丸ごと保存し、システムが成長して「この項目で検索したい！」と確定した段階で、カラムとして独立させる戦略を取りました。

ただし、「頻繁に検索やソートに使用する項目」は、必ず独立したカラムとして切り出してください。

すべてをjsonbの中に閉じ込めてしまうと、インデックスが効きにくくなり、データ量が増えた際にクエリのパフォーマンスが著しく低下（スロークエリ）する原因になります。

ポイント: 実務で必須の共通カラム

今回のDDLではシンプルにしていますが、実際の現場ではほぼ全てのテーブルに以下のカラムを含めることが多いです。

created_at (作成日時)
updated_at (更新日時)
deleted_at (論理削除フラグ：データを物理的に消さず、削除日を入れることで「削除済み」と扱う)

deleted_atが必要かどうかはデータをどのように扱いたいかで判断できます。

実装のメリットとしては、間違えて消した時の復旧や、監査の視点では「いつ消されたか」の情報が保持できることです。

逆にこれらを保持せず完全に削除することが要件の場合は不要となります。

テーブル定義とセットで決める「運用ポリシー」

ER設計（DDL）が完成したら、それで終わりではありません。

データは運用とともに増え続けるため、「そのデータをどう扱うか」という運用ポリシーを合わせて定義することで、運用時の混乱を防ぐことができます。

RepoScannerにおけるポリシー例

保持期間とアーカイブ
- 　snapshot データはストレージ容量を圧迫しやすいため、「作成から90日間DBに保持」とする。
- 　90日を過ぎた重要データ（月次のサマリなど）は、DBから削除し、安価な外部ストレージ（S3など）へアーカイブとしてエクスポートする。
ログへのアクセス権限
- 　operation_log はセキュリティインシデントの調査に使われるため、一般のアプリケーションユーザーや開発者からはアクセスできず、特定の「監査ロール」を持つ管理者のみ閲覧可能とする。
個人情報の取り扱い
- 　ログの中にユーザーのメールアドレス等の個人情報が含まれる場合、保存時にマスキング処理を行うか、退会時に別フローで物理削除できる設計にしておく。

ポイント: 監査ログを書くタイミング

この operation_log は、単なるDBの更新履歴ではありません。

アプリケーション側のUseCaseで、「誰がいつ何をしたか」というビジネス上の意味を持つタイミングで明示的に保存するように設計します。

まとめ

DDLと運用ポリシーをセットで定義することで、運用開始後のデータ肥大化やパフォーマンス低下を未然に防ぐことができます。
監査ログのテーブルは、アプリケーションのER設計と同時に考えるべきです。
PostgreSQLの jsonb は強力ですが、検索要件と照らし合わせて「カラムとして切り出すべきデータ」を見極めましょう。

エピソード4では、このテーブル設計をベースにして、クリーンアーキテクチャに沿ったスナップショット一覧のAPI実装とテストについて解説しています。お楽しみに！

参考

ご覧いただきありがとうございます！この投稿はお役に立ちましたか？

役に立った役に立たなかった

0人がこの投稿は役に立ったと言っています。

Copilot × Clean Architecture | ER設計と監査ログ

エピソード紹介

こんな方へ特におすすめ

概要

作業手順