Database Reviewer Agent

Overview

The database-reviewer agent is an expert PostgreSQL database specialist focused on query optimization, schema design, security, and performance. It incorporates patterns from Supabase’s postgres-best-practices.

string

default:"database-reviewer"

Agent identifier

string

default:"sonnet"

Uses Claude Sonnet for comprehensive database review

array

Available tools: Read, Write, Edit, Bash, Grep, Glob

When to Use

Writing SQL queries or migrations

Designing database schemas

Optimizing slow queries

Implementing Row Level Security (RLS)

Troubleshooting database performance

The database-reviewer agent activates PROACTIVELY when writing SQL, creating migrations, designing schemas, or troubleshooting database performance.

Core Responsibilities

Query Performance — Optimize queries, add proper indexes, prevent table scans
Schema Design — Design efficient schemas with proper data types and constraints
Security & RLS — Implement Row Level Security, least privilege access
Connection Management — Configure pooling, timeouts, limits
Concurrency — Prevent deadlocks, optimize locking strategies
Monitoring — Set up query analysis and performance tracking

Diagnostic Commands

psql $DATABASE_URL

# Slow queries
psql -c "SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"

# Table sizes
psql -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;"

# Index usage
psql -c "SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC;"

Review Workflow

1. Query Performance (CRITICAL)

Are WHERE/JOIN columns indexed?
Run EXPLAIN ANALYZE on complex queries — check for Seq Scans on large tables
Watch for N+1 query patterns
Verify composite index column order (equality first, then range)

2. Schema Design (HIGH)

Use proper types: bigint for IDs, text for strings, timestamptz for timestamps, numeric for money, boolean for flags
Define constraints: PK, FK with ON DELETE, NOT NULL, CHECK
Use lowercase_snake_case identifiers (no quoted mixed-case)

3. Security (CRITICAL)

RLS enabled on multi-tenant tables with (SELECT auth.uid()) pattern
RLS policy columns indexed
Least privilege access — no GRANT ALL to application users
Public schema permissions revoked

Key Principles

Index Foreign Keys

Always, no exceptions

Use Partial Indexes

WHERE deleted_at IS NULL for soft deletes

Covering Indexes

INCLUDE (col) to avoid table lookups

SKIP LOCKED for Queues

10x throughput for worker patterns

Cursor Pagination

WHERE id > $last instead of OFFSET

Batch Inserts

Multi-row INSERT or COPY, never loops

Short Transactions

Never hold locks during external API calls

Consistent Lock Ordering

ORDER BY id FOR UPDATE to prevent deadlocks

Anti-Patterns to Flag

Common database mistakes:

SELECT * in production code
int for IDs (use bigint), varchar(255) without reason (use text)
timestamp without timezone (use timestamptz)
Random UUIDs as PKs (use UUIDv7 or IDENTITY)
OFFSET pagination on large tables
Unparameterized queries (SQL injection risk)
GRANT ALL to application users
RLS policies calling functions per-row (not wrapped in SELECT)

Example: Proper Schema Design

-- BAD: Common mistakes
CREATE TABLE users (
  id int PRIMARY KEY,                    -- Should be bigint
  email varchar(255),                    -- Should be text
  created_at timestamp,                  -- Should be timestamptz
  balance decimal                        -- Needs precision
);

-- GOOD: Proper types and constraints
CREATE TABLE users (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  balance numeric(12, 2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

CREATE INDEX idx_users_email ON users(email);  -- Index for WHERE/JOIN

Example: Query Optimization

-- BAD: N+1 query pattern
SELECT * FROM users;
-- Then in application code, for each user:
SELECT * FROM posts WHERE user_id = ?;

-- GOOD: Single query with JOIN
SELECT 
  u.id, u.email, u.created_at,
  json_agg(json_build_object(
    'id', p.id,
    'title', p.title,
    'created_at', p.created_at
  )) as posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

Example: Row Level Security (RLS)

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- BAD: Calling function per-row (slow)
CREATE POLICY "Users can read own posts" ON posts
  FOR SELECT
  USING (user_id = auth.uid());

-- GOOD: Using SELECT subquery (fast)
CREATE POLICY "Users can read own posts" ON posts
  FOR SELECT
  USING (user_id = (SELECT auth.uid()));

-- Index RLS policy column
CREATE INDEX idx_posts_user_id ON posts(user_id);

Example: Pagination

-- BAD: OFFSET pagination (slow on large tables)
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;  -- Scans 10,020 rows!

-- GOOD: Cursor pagination (fast)
SELECT * FROM posts
WHERE created_at < $last_created_at
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

Example: Avoiding Deadlocks

-- BAD: Inconsistent lock ordering (deadlock risk)
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- In another transaction (reverse order):
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE id = 2;
  UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
-- DEADLOCK!

-- GOOD: Consistent lock ordering (no deadlock)
BEGIN;
  SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Example: Worker Queue Pattern

-- BAD: Concurrent workers fight over same rows
SELECT * FROM jobs WHERE status = 'pending' LIMIT 1;
-- Process job...
UPDATE jobs SET status = 'completed' WHERE id = ?;

-- GOOD: SKIP LOCKED for 10x throughput
BEGIN;
  SELECT * FROM jobs 
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
  
  -- Process job...
  
  UPDATE jobs SET status = 'completed' WHERE id = ?;
COMMIT;

Review Checklist

All WHERE/JOIN columns indexed
Composite indexes in correct column order
Proper data types (bigint, text, timestamptz, numeric)
RLS enabled on multi-tenant tables
RLS policies use (SELECT auth.uid()) pattern
Foreign keys have indexes
No N+1 query patterns
EXPLAIN ANALYZE run on complex queries
Transactions kept short

Usage Example

# Invoke database-reviewer directly
ask database-reviewer "Review this migration for performance issues"

# Or let it activate automatically
# (after writing SQL/migrations, it activates proactively)

Success Criteria

All queries indexed properly

No N+1 query patterns

RLS policies performant

Schema uses proper data types

No security vulnerabilities

Transactions are short

For detailed index patterns, schema design examples, connection management, concurrency strategies, JSONB patterns, and full-text search, see skills: postgres-patterns and database-migrations.

Database issues are often the root cause of application performance problems. Optimize queries and schema design early. Use EXPLAIN ANALYZE to verify assumptions. Always index foreign keys and RLS policy columns.

Patterns adapted from Supabase Agent Skills under MIT license.

Overview

Development Agents

Specialized Agents

Language-Specific Agents

Overview

When to Use

Core Responsibilities

Diagnostic Commands

Review Workflow

1. Query Performance (CRITICAL)

2. Schema Design (HIGH)

3. Security (CRITICAL)

Key Principles

Index Foreign Keys

Use Partial Indexes

Covering Indexes

SKIP LOCKED for Queues

Cursor Pagination

Batch Inserts

Short Transactions

Consistent Lock Ordering

Anti-Patterns to Flag

Example: Proper Schema Design

Example: Query Optimization

Example: Row Level Security (RLS)

Example: Avoiding Deadlocks

Example: Worker Queue Pattern

Review Checklist

Usage Example

Success Criteria

​Overview

​When to Use

​Core Responsibilities

​Diagnostic Commands

​Review Workflow

​1. Query Performance (CRITICAL)

​2. Schema Design (HIGH)

​3. Security (CRITICAL)

​Key Principles

Index Foreign Keys

Use Partial Indexes

Covering Indexes

SKIP LOCKED for Queues

Cursor Pagination

Batch Inserts

Short Transactions

Consistent Lock Ordering

​Anti-Patterns to Flag

​Example: Proper Schema Design

​Example: Query Optimization

​Example: Row Level Security (RLS)

​Example: Pagination

​Example: Avoiding Deadlocks

​Example: Worker Queue Pattern

​Review Checklist

​Usage Example

​Success Criteria

Overview

When to Use

Core Responsibilities

Diagnostic Commands

Review Workflow

1. Query Performance (CRITICAL)

2. Schema Design (HIGH)

3. Security (CRITICAL)

Key Principles

Anti-Patterns to Flag

Example: Proper Schema Design

Example: Query Optimization

Example: Row Level Security (RLS)

Example: Pagination

Example: Avoiding Deadlocks

Example: Worker Queue Pattern

Review Checklist

Usage Example

Success Criteria