audit-db-schema

データベーススキーマ監査スキル

自由度：混合 — ステップ 0、1、3 は [HIGH freedom]、ステップ 2 と 4 の MCP/SQL プローブは [LOW freedom — run exactly]（クエリを実行し、スキーマを捏造しないでください）。

推論の手順

1. 観察 — カラム、制約、アドバイザーの行、またはクエリ結果を引用します
2. 解釈 — 書き込み時、読み取り時、または移行時に何が失敗しますか？
3. 分類 — 命名 / 型 / 制約 / インデックス / RLS / 移行 / 正常
4. 重大度 — 公開データに対する外部キーまたは RLS の欠落 = P0、型やインデックスのずれ = P1、命名 = P2

作業例

観察： orders.user_id は NULL を許容する text で、外部キーもインデックスもありません。rowsecurity = false です。 解釈： 孤立した行を挿入できます。クライアントはすべての注文を SELECT できます。検索はシーケンシャルスキャンになります。 分類： 制約 + インデックス + RLS（命名上の細かな問題ではありません）。 重大度： P0 — 公開テーブルで、RLS と外部キーがありません。 検出事項： orders | RLS+FK | P0 | RLS を有効にし、user_id uuid references users(id) を設定してインデックスを追加します。

報告前の自己批評 [LOW freedom — do not skip]

1. 根拠がある — 「Postgres では通常……」ではなく、クエリ結果またはアドバイザーの URL を示します
2. 再現可能 — 同じ SQL を 2 回実行します。古い list_tables を根拠として引用しないでください
3. 重大度に妥当な根拠がある — P0 = データ損失、情報漏えい、または制約のない金額型
4. 適切な担当者 — 誰が何を読めるか → plan-rls-audit、DELETE/TRUNCATE → plan-data-integrity、RPO → plan-backup-dr
5. 移行を適用していない — 検出事項のみ

---

ステップ 0：データベース環境を自動検出する

0a. データベースと ORM を検出する

シグナル	テクノロジー
package.json に @supabase/supabase-js がある	Supabase（Postgres）
devDependencies に prisma があり、prisma/schema.prisma がある	Prisma ORM
dependencies に drizzle-orm があり、drizzle/ ディレクトリがある	Drizzle ORM
dependencies に sequelize がある	Sequelize ORM
requirements に sqlalchemy がある	SQLAlchemy（Python）
supabase/migrations/*.sql ディレクトリがある	Supabase migrations
prisma/migrations/ ディレクトリがある	Prisma migrations
drizzle/migrations/ または drizzle/*.sql がある	Drizzle migrations

0b. Supabase プロジェクト ID を特定する

.env / .env.local の SUPABASE_URL のホスト （https://<ref>.supabase.co）から PROJECT_ID を取得し、 supabase:get_project_url で確認します。supabase:list_projects は、MCP サーバーがこれを公開している場合（プロジェクト ref なしで起動した場合）にのみ呼び出します。プロジェクトスコープのサーバーは、 このパックでの設定方法では公開されず、そのツールは project_id を引数に取りません。スコープなしのサーバーでは、以下の各呼び出しに "project_id": "<PROJECT_ID>" を追加します。

0c. スキーマのソースファイルを検出する

Glob: **/supabase/migrations/*.sql → Supabase SQL migrations
Glob: **/prisma/schema.prisma → Prisma schema
Glob: **/drizzle/schema.ts → Drizzle schema
Glob: **/src/db/schema.ts → Drizzle alt location
Glob: **/knexfile.* → Knex migrations
Glob: **/alembic/versions/*.py → SQLAlchemy migrations

0d. 検出結果を記録する

DATABASE ENVIRONMENT:
- Database: [Supabase Postgres / raw Postgres / MySQL / SQLite]
- ORM: [Prisma / Drizzle / Sequelize / none]
- Project ID: [Supabase project ID or N/A]
- Migration tool: [Supabase CLI / Prisma Migrate / Drizzle Kit / Knex]
- Schema files: [list paths]
- Migration count: [N]

---

ステップ 1：スキーマのベストプラクティスを調査する

1a. Context7 — ORM のドキュメント

Prisma を使用している場合：

context7:resolve-library-id
{
 "libraryName": "prisma",
 "query": "schema best practices indexes relations"
}

context7:query-docs
{
 "libraryId": "<RESOLVED_ID>",
 "query": "schema best practices naming conventions indexes onDelete"
}

Drizzle を使用している場合は、代わりに drizzle-orm を解決します。

1b. Firecrawl — 最新のデータベースパターン

firecrawl:firecrawl_search
{
 "query": "PostgreSQL schema design best practices [current year]",
 "limit": 5,
 "sources": [{ "type": "web" }]
}

検出したスタックに応じた追加の検索：

スタック	検索クエリ
Supabase	Supabase RLS policies best practices performance [current year]
Prisma	Prisma schema design relations indexes best practices [current year]
Drizzle	Drizzle ORM schema patterns migrations [current year]
General	PostgreSQL indexing strategy production optimization

最も信頼できる検索結果をスクレイピングします：

firecrawl:firecrawl_scrape
{
 "url": "<BEST_RESULT_URL>",
 "formats": ["markdown"],
 "onlyMainContent": true
}

1c. Supabase ドキュメントの検索

Supabase を使用している場合：

supabase:search_docs
{
 "graphql_query": "{ searchDocs(query: \"RLS policy performance best practices\", limit: 3) { nodes { title href content } } }"
}

---

ステップ 2：スキーマ全体を収集する

2a. すべてのテーブルを一覧表示する（Supabase MCP）

supabase:list_tables
{
 "schemas": ["public"],
 "verbose": true
}

2b. 詳細な監査クエリを実行する

supabase:execute_sql
{
 "query": "SELECT table_name, column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position"
}

supabase:execute_sql
{
 "query": "SELECT tc.table_name, tc.constraint_name, tc.constraint_type, kcu.column_name, ccu.table_name AS foreign_table FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name WHERE tc.table_schema = 'public'"
}

2c. インデックスを収集する

supabase:execute_sql
{
 "query": "SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename"
}

2d. RLS のステータスとポリシーを収集する

supabase:execute_sql
{
 "query": "SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename"
}

supabase:execute_sql
{
 "query": "SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'public' ORDER BY tablename"
}

2e. Supabase アドバイザーを実行する

supabase:get_advisors
{
 "type": "security"
}

supabase:get_advisors
{
 "type": "performance"
}

最終レポートには、アドバイザーの結果に含まれる修正用 URL をクリック可能なリンクとして記載してください。

---

ステップ 3：監査カテゴリ

3.1 命名規則

ルール	標準	確認項目
テーブル	snake_case、複数形（users、posts）	camelCase、単数形は使用しない
カラム	snake_case（created_at、user_id）	camelCaseは使用しない
主キー	id	自身のテーブルでuser_idは使用しない
外部キー	{referenced_table_singular}_id（user_id）	一貫したパターンにする
インデックス	idx_{table}_{column(s)}	説明的な名前にする
制約	{table}_{column}_{type}（users_email_unique）	説明的な名前にする
列挙型	snake_caseの型、UPPER_CASEの値	大文字・小文字の使い方を統一する
ブール型カラム	is_またはhas_の接頭辞（is_active、has_access）	意図が明確になるようにする

監査クエリ：

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
 AND (table_name ~ '[A-Z]' OR table_name !~ 's$');

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name ~ '[A-Z]';

3.2 データ型

ルール	標準
主キー	gen_random_uuid()を使うuuidまたはcuid
タイムスタンプ	timestamptz（timestampは使用しない）
金額	numeric(12,2)またはbigint（セント単位）。float／realは使用しない（2進数の丸め誤差によりセント単位の値が失われます）
メールアドレス	CHECK制約付きのtextまたはcitext
ステータス／列挙型	Postgresのenum型、またはCHECK制約付きのtext
JSON	jsonb（jsonは使用しない）
短い文字列	Postgresではvarchar(n)よりtextを推奨
ブール値	NOT NULL DEFAULT付きのboolean
IPアドレス	inet型
配列	適切な場合はネイティブのtext[]、integer[]

監査クエリ：

SELECT table_name, column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'timestamp without time zone';

SELECT table_name, column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public'
 AND data_type IN ('real', 'double precision')
 AND (column_name LIKE '%price%' OR column_name LIKE '%amount%'
 OR column_name LIKE '%cost%' OR column_name LIKE '%balance%');

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'json';

3.3 必須カラムとタイムスタンプ

すべてのテーブルに以下が必要です。

カラム	型	デフォルト	備考
id	uuid	gen_random_uuid()	主キー
created_at	timestamptz	now()	NOT NULL
updated_at	timestamptz	now()	NOT NULL、自動トリガー

監査クエリ：

SELECT t.table_name,
 EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'created_at') AS has_created_at,
 EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'updated_at') AS has_updated_at
FROM information_schema.tables t
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE';

SELECT event_object_table, trigger_name FROM information_schema.triggers
WHERE trigger_schema = 'public' AND action_statement LIKE '%updated_at%';

3.4 制約と検証

制約	必須となる場合
NOT NULL	明示的に任意とされていないすべてのカラム
UNIQUE	メールアドレス、スラッグ、外部ID、ユーザー名
CHECK	列挙型、範囲、形式、正の数値
DEFAULT	ブール値、タイムスタンプ、ステータスフィールド
FOREIGN KEY	すべてのリレーションシップカラム
ON DELETE	所有データにはCASCADE、任意の参照にはSET NULL、重要なデータにはRESTRICT

監査クエリ：

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name LIKE '%_id'
 AND is_nullable = 'YES' AND column_name != 'id';

SELECT c.table_name, c.column_name FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.column_name LIKE '%_id' AND c.column_name != 'id'
 AND NOT EXISTS (
 SELECT 1 FROM information_schema.key_column_usage kcu
 JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name
 WHERE tc.constraint_type = 'FOREIGN KEY'
 AND kcu.table_name = c.table_name AND kcu.column_name = c.column_name
 );

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'boolean' AND column_default IS NULL;

3.5 インデックス

ルール	標準
外部キー	すべての外部キーカラムにインデックスを設定
頻繁に実行されるクエリ	WHERE／ORDER BY句で使うカラムにインデックスを設定
一意な検索	email、slug、external_idに一意インデックスを設定
複合インデックス	順序：まず等価条件、次に範囲条件、最後にソート
RLSカラム	RLSポリシーで使うカラムにインデックスを設定
created_at	時系列順のクエリにはDESCインデックスを設定
部分インデックス	サブセットを検索するクエリにはWHERE句を使用

監査クエリ：

SELECT c.table_name, c.column_name FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.column_name LIKE '%_id' AND c.column_name != 'id'
 AND NOT EXISTS (
 SELECT 1 FROM pg_indexes i
 WHERE i.schemaname = 'public' AND i.tablename = c.table_name
 AND i.indexdef LIKE '%' || c.column_name || '%'
 );

SELECT t.table_name, COUNT(i.indexname) as idx_count
FROM information_schema.tables t
LEFT JOIN pg_indexes i ON i.tablename = t.table_name AND i.schemaname = 'public'
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name HAVING COUNT(i.indexname) <= 1;

3.6 行レベルセキュリティ（Supabase）

ルール	標準
RLSの有効化	すべての公開テーブルでRLSを有効にする。有効にしないと、anonキーですべての行を読み取れます
SELECTポリシー	すべてのテーブルに設定する
INSERTポリシー	ユーザーの所有権に対するWITH CHECKを設定する
UPDATEポリシー	所有権に対するUSINGとWITH CHECKを設定する
DELETEポリシー	所有権に対するUSINGを設定する
サービスロール	RLSをバイパスします（クライアントに決して公開しないでください）
パフォーマンス	(select auth.uid())サブクエリパターンを使用する
インデックス	ポリシーで使うカラムに設定する

監査クエリ：

SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND rowsecurity = false;

SELECT t.tablename FROM pg_tables t
WHERE t.schemaname = 'public' AND t.rowsecurity = true
 AND NOT EXISTS (
 SELECT 1 FROM pg_policies p WHERE p.tablename = t.tablename AND p.schemaname = 'public'
 );

SELECT tablename, policyname, qual FROM pg_policies
WHERE schemaname = 'public'
 AND qual::text LIKE '%auth.uid()%'
 AND qual::text NOT LIKE '%(select auth.uid())%';

3.7 リレーションシップと正規化

ルール	標準
第3正規形以上	推移的関数従属がないこと
中間テーブル	多対多の関係には使用する（JSON配列ではなくuser_rolesなど）
データの重複なし	繰り返されるデータはルックアップテーブルに正規化する
カスケードルール	すべての外部キーリレーションシップで定義する
自己参照	必要に応じてparent_idパターンを使用する
ポリモーフィック	避ける。代わりに中間テーブルまたはSTIを使用する

3.8 マイグレーション

ルール	標準
連番	タイムスタンプまたは 0001_、0002_ のプレフィックス
説明的な名前	0003_add_user_roles.sql ではなく 0003_update.sql
冪等性	IF NOT EXISTS、IF EXISTS によるガード
データ損失なし	ダウンマイグレーションまたはロールバック計画
アトミック	1つのマイグレーションにつき、論理的な変更を1つ
破壊的変更なし	まず追加し、次にバックフィルし、その後クリーンアップ

3.9 セキュリティ

ルール	標準
平文のシークレットなし	パスワードはハッシュ化し、トークンは暗号化
PII の保護	機密列を特定して保護
監査証跡	機密テーブルに created_by、updated_by
権限付与	ロールごとに最小限の権限
拡張機能	必要な拡張機能のみを有効化
検索パス	スキーマを明示的に参照

監査クエリ：

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public'
 AND (column_name LIKE '%password%' OR column_name LIKE '%secret%'
 OR column_name LIKE '%token%' OR column_name LIKE '%ssn%'
 OR column_name LIKE '%credit_card%');

SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges
WHERE table_schema = 'public' ORDER BY grantee, table_name;

---

ステップ 4：スキーマ全体のヘルスチェック（単一クエリ）

supabase:execute_sql
{
 "query": "WITH table_info AS (SELECT t.table_name, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'id') AS has_id, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'created_at') AS has_created_at, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'updated_at') AS has_updated_at, (SELECT rowsecurity FROM pg_tables pt WHERE pt.tablename = t.table_name AND pt.schemaname = 'public') AS rls_enabled, (SELECT COUNT(*) FROM pg_policies p WHERE p.tablename = t.table_name AND p.schemaname = 'public') AS policy_count, (SELECT COUNT(*) FROM pg_indexes i WHERE i.tablename = t.table_name AND i.schemaname = 'public') AS index_count FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE') SELECT table_name, CASE WHEN has_id THEN 'Y' ELSE 'N' END AS id, CASE WHEN has_created_at THEN 'Y' ELSE 'N' END AS created_at, CASE WHEN has_updated_at THEN 'Y' ELSE 'N' END AS updated_at, CASE WHEN rls_enabled THEN 'Y' ELSE 'N' END AS rls, policy_count AS policies, index_count AS indexes FROM table_info ORDER BY table_name"
}

---

参考資料

• ステップ 5：Prisma スキーマの監査など