kensaurus 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
- Observe — quote the column, constraint, advisor row, or query result
- Interpret — what fails at write-time, read-time, or migrate-time?
- Classify — naming / type / constraint / index / RLS / migration / correct
- Severity — missing FK/RLS on public data = P0; type/index drift = P1; naming = P2
Worked example
Observe:
orders.user_idis nullabletext, 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]
- Evidenced — query result or advisor URL, not "Postgres usually…"
- Reproducible — same SQL twice; do not cite a stale
list_tables - Severity justified — P0 = data loss, leak, or unconstrained money type
- Right owner — who-can-read-what →
plan-rls-audit; DELETE/TRUNCATE →plan-data-integrity; RPO →plan-backup-dr - No migrations applied — findings only
Step 0: Auto-Detect Database Environment
0a. Detect Database and ORM
| Signal | Technology |
|---|---|
@supabase/supabase-js in package.json |
Supabase (Postgres) |
prisma in devDependencies, prisma/schema.prisma |
Prisma ORM |
drizzle-orm in dependencies, drizzle/ directory |
Drizzle ORM |
sequelize in dependencies |
Sequelize ORM |
sqlalchemy in requirements |
SQLAlchemy (Python) |
supabase/migrations/*.sql directory |
Supabase migrations |
prisma/migrations/ directory |
Prisma migrations |
drizzle/migrations/ or drizzle/*.sql |
Drizzle migrations |
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:
| Stack | Search Query |
|---|---|
| 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 |
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
| Rule | Standard | Check |
|---|---|---|
| Tables | snake_case, plural (users, posts) |
No camelCase, no singular |
| Columns | snake_case (created_at, user_id) |
No camelCase |
| Primary keys | id |
Not user_id on own table |
| Foreign keys | {referenced_table_singular}_id (user_id) |
Consistent pattern |
| Indexes | idx_{table}_{column(s)} |
Descriptive names |
| Constraints | {table}_{column}_{type} (users_email_unique) |
Descriptive names |
| Enums | snake_case type, UPPER_CASE values |
Consistent casing |
| Boolean columns | is_ or has_ prefix (is_active, has_access) |
Clear intent |
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
| Rule | Standard |
|---|---|
| Primary keys | uuid with gen_random_uuid() or cuid |
| Timestamps | timestamptz (NOT timestamp) |
| Money | numeric(12,2) or bigint (cents) — NEVER float/real |
text with CHECK constraint or citext |
|
| Status/enum | Postgres enum type or text with CHECK |
| JSON | jsonb (NOT json) |
| Short strings | text preferred over varchar(n) in Postgres |
| Booleans | boolean with NOT NULL DEFAULT |
| IP addresses | inet type |
| Arrays | Native text[], integer[] where appropriate |
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:
| Column | Type | Default | Notes |
|---|---|---|---|
id |
uuid |
gen_random_uuid() |
Primary key |
created_at |
timestamptz |
now() |
NOT NULL |
updated_at |
timestamptz |
now() |
NOT NULL, auto-trigger |
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
| Constraint | When Required |
|---|---|
NOT NULL |
Every column unless explicitly optional |
UNIQUE |
Emails, slugs, external IDs, usernames |
CHECK |
Enums, ranges, formats, positive numbers |
DEFAULT |
Booleans, timestamps, status fields |
FOREIGN KEY |
Every relationship column |
ON DELETE |
CASCADE for owned data, SET NULL for optional refs, RESTRICT for critical |
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
| Rule | Standard |
|---|---|
| Foreign keys | Index on EVERY FK column |
| Frequent queries | Index on WHERE/ORDER BY columns |
| Unique lookups | Unique index on email, slug, external_id |
| Composite | Order: equality first, then range, then sort |
| RLS columns | Index columns used in RLS policies |
created_at |
DESC index for chronological queries |
| Partial indexes | WHERE clause for subset queries |
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)
| Rule | Standard |
|---|---|
| RLS enabled | EVERY public table has RLS ON |
| SELECT policy | Exists for every table |
| INSERT policy | WITH CHECK on user ownership |
| UPDATE policy | USING + WITH CHECK on ownership |
| DELETE policy | USING on ownership |
| Service role | Bypasses RLS (never expose to client) |
| Performance | (select auth.uid()) subquery pattern |
| Indexes | On columns used in policies |
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
| Rule | Standard |
|---|---|
| 3NF minimum | No transitive dependencies |
| Junction tables | For many-to-many (user_roles, not JSON arrays) |
| No data duplication | Normalize repeated data into lookup tables |
| Cascade rules | Defined on every FK relationship |
| Self-referencing | Use with parent_id pattern when needed |
| Polymorphic | Avoid — use junction tables or STI instead |
3.8 Migrations
| Rule | Standard |
|---|---|
| Sequential numbering | Timestamps or 0001_, 0002_ prefixes |
| Descriptive names | 0003_add_user_roles.sql not 0003_update.sql |
| Idempotent | IF NOT EXISTS, IF EXISTS guards |
| No data loss | Down migrations or rollback plan |
| Atomic | One logical change per migration |
| No breaking changes | Additive first, then backfill, then cleanup |
3.9 Security
| Rule | Standard |
|---|---|
| No plaintext secrets | Passwords hashed, tokens encrypted |
| PII protection | Sensitive columns identified and protected |
| Audit trail | created_by, updated_by on sensitive tables |
| Grants | Minimal privileges per role |
| Extensions | Only necessary extensions enabled |
| Search path | Explicit schema references |
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