From skillry-database-and-data
Use when you need to review Postgres and Supabase schemas, policies, migrations, indexes, and local versus production boundaries.
How this skill is triggered — by the user, by Claude, or both
Slash command
/skillry-database-and-data:36-postgres-supabase-reviewThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Conduct a structured review of a Supabase/Postgres project: schema design, Row-Level Security policies, indexes, foreign keys, migrations directory hygiene, connection pooling configuration, and auth.users coupling. Surface concrete risks with evidence from actual files, never from assumptions.
Conduct a structured review of a Supabase/Postgres project: schema design, Row-Level Security policies, indexes, foreign keys, migrations directory hygiene, connection pooling configuration, and auth.users coupling. Surface concrete risks with evidence from actual files, never from assumptions.
supabase/migrations/ directory has grown and no one has reviewed policy coverage recently.ls supabase/migrations/ # migration files in chronological order
ls supabase/functions/ # edge functions that may use service_role
grep -r "service_role" . --include="*.ts" --include="*.env*" -l
grep -r "supabaseAdmin\|createClient.*service_role" . --include="*.ts" -n
Confirm which client (anon vs service_role) each backend surface uses. service_role bypasses RLS entirely — every usage must be intentional and server-side only.
-- Run in Supabase SQL editor or psql
SELECT
schemaname,
tablename,
rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
Any table with rowsecurity = false that holds user data is a critical finding. Then check existing policies:
SELECT
schemaname,
tablename,
policyname,
permissive,
roles,
cmd,
qual,
with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, cmd;
Look for:
INSERT/UPDATE policy but no SELECT policy (read leak).auth.uid() correctly vs. hardcoded UUIDs or true (open access).auth.users directly instead of auth.uid() — can cause plan regressions.-- Missing indexes on FK columns (common performance trap)
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE tablename = tc.table_name
AND indexdef LIKE '%' || kcu.column_name || '%'
);
Every FK column that is queried from the child side needs an index. Without it, deletes and joins on the parent produce sequential scans.
# Migrations must be append-only; no editing of already-applied files
git log --oneline supabase/migrations/
git diff main -- supabase/migrations/ # should only show NEW files, never edits to old ones
Check that each migration file:
BEGIN/COMMIT) or uses supabase migrate which wraps automatically.DROP TABLE or DROP COLUMN without a preceding backup/rename step.NOT NULL to an existing column in one step (see skill 37 for the safe pattern).In supabase/config.toml or project settings:
[db.pooler]
enabled = true
pool_mode = "transaction" # transaction mode for stateless APIs
default_pool_size = 15
max_client_conn = 100
SET LOCAL, prepared statements, advisory locks) do NOT work in transaction mode.LISTEN/NOTIFY, advisory locks, or session variables. Verify the app does not mix modes.max_client_conn against your Supabase plan's connection limit. Exceeding it causes FATAL: remaining connection slots are reserved.-- Profiles table should reference auth.users via FK
SELECT
tc.table_name, kcu.column_name, ccu.table_name AS ref_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE ccu.table_name = 'users' AND ccu.table_schema = 'auth';
Confirm the profiles/user metadata table has:
id UUID REFERENCES auth.users(id) ON DELETE CASCADE
Missing ON DELETE CASCADE leaves orphaned rows when a user is deleted from Supabase Auth.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, u.email
FROM posts p
JOIN auth.users u ON u.id = p.user_id
WHERE p.team_id = '00000000-0000-0000-0000-000000000001'
ORDER BY p.created_at DESC
LIMIT 20;
Red flags: Seq Scan on large tables, Rows Removed by Filter much larger than actual rows, Buffers: shared hit=0 read=N (cold cache), nested loop with high actual rows.
rowsecurity = true)true as qual for sensitive tablesservice_role client is never used client-side (browser/mobile)NOT NULL additions go through expand-contract (see skill 37)auth.users FK has ON DELETE CASCADE where appropriateanon key is safe to expose; service_role key is in server env onlyEXPLAIN ANALYZE shows index scans, not sequential scans, on hot pathsRLS bypass via service_role in client code. Any JavaScript bundle that contains service_role key is a critical secret leak. Grep for it in next.config.js, .env.local committed to git, or Vite build output.
Policies that call functions with SECURITY DEFINER. A SECURITY DEFINER function runs as its owner (often postgres), bypassing RLS of tables it touches internally. Audit every function referenced in a policy.
Adding NOT NULL in a single migration step on a live table. Postgres takes an ACCESS EXCLUSIVE lock and rewrites the table. On millions of rows this causes downtime. Use ALTER COLUMN ... SET DEFAULT + backfill + SET NOT NULL + DROP DEFAULT across multiple deploys.
Querying auth.users directly from application code. Supabase exposes auth.users but it contains sensitive fields. Build a public.profiles view or table and expose only what you need with RLS.
Missing CONCURRENTLY on index creation in migrations. CREATE INDEX without CONCURRENTLY locks the table for writes. Use CREATE INDEX CONCURRENTLY in a migration that is NOT inside a transaction block (supabase migration new --no-transaction).
Sequence exhaustion on SERIAL columns. Use BIGSERIAL or UUID for tables expected to grow past 2 billion rows. Check current sequence values:
SELECT sequencename, last_value, max_value FROM pg_sequences WHERE schemaname = 'public';
Produce a structured report with:
rowsecurity status, and policy count per command.DROP, TRUNCATE, DELETE, or ALTER TABLE ... DROP COLUMN as part of the review.service_role or anon keys found in files.EXPLAIN ANALYZE runs are read-only and safe against production if you have read access.Creates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.
npx claudepluginhub fluxonlab/skillry --plugin skillry-database-and-data