audit-db-schema

Database Schema Audit Skill

Degree of freedom: MIXED — Steps 0, 1, 3 [HIGH freedom]; Steps 2 and 4 MCP/SQL probes [LOW freedom — run exactly] (run the query; do not invent a schema).

How to reason

1. Observe — quote the column, constraint, advisor row, or query result
2. Interpret — what fails at write-time, read-time, or migrate-time?
3. Classify — naming / type / constraint / index / RLS / migration / correct
4. Severity — missing FK/RLS on public data = P0; type/index drift = P1; naming = P2

Worked example

Observe: orders.user_id is nullable text, no FK, no index; rowsecurity = false. Interpret: orphan rows can insert; the client can SELECT every order; lookups seq-scan. Classify: constraint + index + RLS (not a naming nit). Severity: P0 — public table, no RLS, no FK. Finding: orders | RLS+FK | P0 | enable RLS + user_id uuid references users(id) + index.

Self-critique before reporting [LOW freedom — do not skip]

1. Evidenced — query result or advisor URL, not "Postgres usually…"
2. Reproducible — same SQL twice; do not cite a stale list_tables
3. Severity justified — P0 = data loss, leak, or unconstrained money type
4. Right owner — who-can-read-what → plan-rls-audit; DELETE/TRUNCATE → plan-data-integrity; RPO → plan-backup-dr
5. No migrations applied — findings only

Step 0: Auto-Detect Database Environment

0a. Detect Database and ORM

0b. Find Supabase Project ID

supabase:list_projects
{}

Match the project by name or URL from .env, .env.local, or supabase/config.toml. Record the PROJECT_ID for all subsequent MCP calls.

0c. Detect Schema Source Files

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. Record Discovery

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]

Step 1: Research Schema Best Practices

1a. Context7 — ORM Documentation

If using 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"
}

If using Drizzle, resolve drizzle-orm instead.

1b. Firecrawl — Current Database Patterns

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

Additional searches based on detected stack:

Scrape the most authoritative result:

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

1c. Supabase Docs Search

If Supabase:

supabase:search_docs
{
 "query": "RLS policy performance best practices"
}

Step 2: Gather Full Schema

2a. List All Tables (Supabase MCP)

supabase:list_tables
{
 "project_id": "<PROJECT_ID>",
 "schemas": ["public"],
 "verbose": true
}

2b. Run Detailed Audit Queries

supabase:execute_sql
{
 "project_id": "<PROJECT_ID>",
 "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
{
 "project_id": "<PROJECT_ID>",
 "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. Gather Indexes

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

2d. Gather RLS Status and Policies

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

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

2e. Run Supabase Advisors

supabase:get_advisors
{
 "project_id": "<PROJECT_ID>",
 "type": "security"
}

supabase:get_advisors
{
 "project_id": "<PROJECT_ID>",
 "type": "performance"
}

Include remediation URLs from advisor results in the final report as clickable links.

Step 3: Audit Categories

3.1 Naming Conventions

Audit query:

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 Data Types

Audit queries:

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 Required Columns and Timestamps

Every table MUST have:

Audit queries:

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 Constraints and Validation

Audit queries:

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 Indexes

Audit query:

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 Row Level Security (Supabase)

Audit queries:

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 Relationships and Normalization

3.8 Migrations

3.9 Security

Audit query:

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;

Step 4: Full Schema Health Check (Single Query)

supabase:execute_sql
{
 "project_id": "<PROJECT_ID>",
 "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"
}

Further reading

• Step 5: Prisma Schema Audit and more