version: "1.0.1"

name: db-sculptor description: Design database schemas with Prisma/Drizzle, PostgreSQL index strategy (B-tree, GIN, GiST, BRIN, Hash), query optimization (EXPLAIN ANALYZE), migration safety (expand/contract, zero-downtime), and sharding/partitioning. Use when user asks to design schema, create migrations, optimize slow queries, add indexes, choose between SQL/NoSQL, or set up Prisma/Drizzle. Do NOT use for data warehouse dimensional modeling, ETL pipeline design, or non-relational (MongoDB, DynamoDB) schema design. triggers:

• "design database schema"
• "Prisma schema"
• "Drizzle schema"
• "PostgreSQL index"
• "optimize query"
• "EXPLAIN ANALYZE"
• "create migration"
• "zero-downtime migration"
• "database design"
• "SQL schema"
• "data model"
• "ORM setup"

negatives:

• "database backup"
• "database administration"
• "data warehouse"
• "ETL pipeline"
• "MongoDB"
• "DynamoDB"
• "non-relational"

license: MIT compatibility: opencode metadata: workflow: backend audience: developers version: "4.0.0" author: shokunin allowed-tools: Read Bash Write Grep

DB Sculptor

Design performant database schemas. Model for access patterns first, normalize later. Based on PostgreSQL internals, Prisma/Drizzle best practices, and production patterns from PlanetScale, Neon, and pganalyze.

Sub-Commands

Workflow

Step 1: Model for access patterns

Decision tree:

• High write volume, simple reads → Normalize (3NF). Minimum indexes.
• High read volume, complex joins → Denormalize strategically. Add composite + covering indexes.
• Time-series data → Partition by time (monthly). BRIN indexes on timestamp.
• Full-text search needed → GIN index with tsvector + tsquery.
• JSON queries → GIN index on JSONB column.

Step 2: Primary key strategy

Default: UUIDv7 for all user-facing tables.

Step 3: Add indexes strategically

Rules:

• Max 3-4 indexes per write-heavy table
• Order composite columns by selectivity (most selective FIRST)
• Use partial indexes for common WHERE filters
• Use INCLUDE for read-heavy queries with small related columns

Step 4: Optimize queries

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 AND status = 'active';

-- Slow (~235ms): Seq Scan on 500k rows
-- Fix: composite index
CREATE INDEX CONCURRENTLY idx_orders_user_status
  ON orders (user_id, status, created_at DESC);
 
-- After fix: Index Only Scan, ~0.3ms

Common optimization patterns:

-- Pattern 1: Function on indexed column → expression index
-- Slow: WHERE LOWER(email) = 'user@example.com'
-- Fix:
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
 
-- Pattern 2: Missing FK index → Nested Loop join
-- Slow: SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE users.name = 'Alice'
-- Fix:
CREATE INDEX idx_orders_user_id ON orders (user_id);
 
-- Pattern 3: ORDER BY + LIMIT without index → full sort
-- Slow: SELECT * FROM events ORDER BY created_at DESC LIMIT 20;
-- Fix:
CREATE INDEX idx_events_created_at ON events (created_at DESC);
 
-- Pattern 4: COUNT on large table without WHERE → estimated count
-- Slow: SELECT COUNT(*) FROM huge_table;
-- Fix (approximate):
SELECT reltuples::bigint FROM pg_class WHERE relname = 'huge_table';
 
-- Pattern 5: Pagination with OFFSET → performance degrades
-- Slow: SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 100000;
-- Fix (keyset pagination):
SELECT * FROM items WHERE id > 100000 ORDER BY id LIMIT 20;
 
-- Pattern 6: Unused indexes bloating writes
-- Identify unused indexes:
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Pattern 7: Parallel query for large scans
-- Force parallel workers on large analytical queries:
SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Step 5: Safe migrations (expand/contract)

Prisma example:

// prisma/schema.prisma
model User {
  id        String   @id @default(uuid()) @db.Uuid
  email     String   @unique
  timezone  String?  // Phase 1: nullable
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
 
  @@index([email])
  @@map("users")
}

Drizzle ORM example:

// db/schema/users.ts
import { pgTable, text, timestamp, uuid, index } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  timezone: text("timezone"), // Phase 1: nullable
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: index("idx_users_email").on(table.email),
}));
// Migration command: npx drizzle-kit generate && npx drizzle-kit migrate

Expand/contract SQL:

-- Phase 1 (expand): Add column as nullable
ALTER TABLE users ADD COLUMN timezone text;
 
-- Phase 2 (backfill): Fill data in separate deployment
UPDATE users SET timezone = 'UTC' WHERE timezone IS NULL;
 
-- Phase 3 (contract): Make NOT NULL, clean up
ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;
 
-- Phase 4 (future): Drop old column
ALTER TABLE users DROP COLUMN old_timezone;

Safety rules:

• CREATE INDEX CONCURRENTLY — never blocks writes
• lock_timeout = '5s' on migration connections
• One logical change per migration file
• Backfill in separate migration from schema change
• Rollback written BEFORE applying forward
• Never rename + change type in same migration

PgBouncer connection pooling

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200
max_db_connections = 25

Rules:

• Use pool_mode = transaction (default) for most workloads — session-level pooling breaks SET and LISTEN/NOTIFY
• Use pool_mode = session only when you need prepared statements, SET session variables, or listen/notify
• Set max_db_connections to 25-50% of PostgreSQL max_connections — PgBouncer multiplexes client connections
• Set default_pool_size based on CPU cores: 4× cores for mixed workloads, 1× for CPU-bound
• Never use pool_mode = statement — breaks multi-statement transactions

Error Handling

Production Checklist

• [ ] Primary key: UUIDv7 or bigint. Never expose sequential PKs in URLs.
• [ ] created_at + updated_at on every table. updated_at auto-set via trigger.
• [ ] Indexes match WHERE + ORDER BY + JOIN columns. Verified with EXPLAIN ANALYZE.
• [ ] Composite indexes ordered by selectivity (most selective first)
• [ ] Partial indexes for common filtered queries
• [ ] Covering indexes (INCLUDE) for read-heavy lookups
• [ ] lock_timeout = '5s' on migration connections
• [ ] Migrations tested on staging with production-like data volume
• [ ] Connection pooling configured (PgBouncer for PostgreSQL)
• [ ] TEXT over VARCHAR(n) — no arbitrary length limits
• [ ] TIMESTAMPTZ over TIMESTAMP — always store with timezone
• [ ] FKs indexed — mandatory for join performance
• [ ] Soft deletes via deleted_at TIMESTAMPTZ (nullable)

Anti-Patterns

Sources

• PostgreSQL docs (postgresql.org/docs)
• Use the Index, Luke! (use-the-index-luke.com)
• pganalyze EXPLAIN analyzer
• Prisma migration docs
• Drizzle ORM docs
• PlanetScale schema migration patterns
• Stormatics — composite and partial indexes
• Neon serverless PostgreSQL patterns

Checklist

• [ ] Skill loads without errors in the AI agent
• [ ] YAML frontmatter is valid (description, compatibility, audience)
• [ ] Workflow section provides clear step-by-step instructions
• [ ] Error handling section covers common failure modes
• [ ] All referenced files (references/, scripts/, assets/) exist
• [ ] Skill triggers correctly for intended use cases
• [ ] No broken links or missing resources