Skip to content

EliasOulkadi/shokunin / .pack/skills/db-sculptor

db-sculptor

Verified

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.

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

CommandDescription
designDesign a schema from access patterns and data volume estimates
indexAnalyze queries and recommend/create optimal indexes
optimizeDiagnose slow queries with EXPLAIN ANALYZE and fix them
migrateCreate a safe, zero-downtime migration (expand/contract)
auditAudit existing schema against best practices and anti-patterns

Workflow

Step 1: Model for access patterns

QuestionDetermine
Read/write ratio?How many indexes can the table support
Data volume?Current rows, growth rate/month
Consistency requirements?ACID vs eventual, read replicas OK?
Latency budget?p50 < 5ms, p95 < 50ms, p99 < 200ms

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

PK typeProsConsWhen
UUIDv7Time-sortable, globally unique, no collision riskLarger than bigint (16 bytes vs 8)Default for user-facing. Distributed systems.
UUIDv4Random, globally uniqueNon-sortable = index fragmentationLegacy. Avoid for new schemas.
bigint (auto-increment)Fast, compact, sequentialPredictable, not globally uniqueInternal/analytics tables. Never for user-facing.
ULIDTime-sortable, URL-safe26 charsWhen human-readable time ordering matters.

Default: UUIDv7 for all user-facing tables.

Step 3: Add indexes strategically

Index typeUse caseExample
B-tree (default)Equality, range, sort, ORDER BYCREATE INDEX ON users (email)
CompositeMulti-column WHERE, ORDER BYCREATE INDEX ON orders (user_id, status, created_at DESC)
PartialFiltered queries (WHERE clause)CREATE INDEX ON orders (created_at) WHERE status = 'active'
Covering (INCLUDE)Avoid heap fetchesCREATE INDEX ON users (email) INCLUDE (name, avatar)
GINFTS, arrays, JSONB, trigramCREATE INDEX ON articles USING GIN (to_tsvector('english', body))
GiSTGeometric, range queriesCREATE INDEX ON events USING GiST (tsrange(start_date, end_date))
BRINVery large tables, ordered dataCREATE INDEX ON events USING BRIN (created_at) WITH (pages_per_range = 32)

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';
Scan typeMeaningFix
Seq ScanReading entire tableAdd index on WHERE columns
Index ScanUsing index + heap lookupGood. Consider covering index.
Index Only ScanUsing index only (no heap)Best. Efficient.
Bitmap Heap ScanMultiple indexes → bitmap → heapOK. Consider composite index.
Nested LoopJoin: outer × innerAdd FK index, or consider hash join
Hash JoinBuild hash table, probePreferred for medium-large joins
-- 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

ScenarioCauseFix
Migration lock timeoutLong-running query on same tableSet lock_timeout = '5s'. Run in low-traffic window.
Query slow in prod, fast in devDifferent data volume and distributionTest with prod-size data (~1M+ rows) in staging
CREATE INDEX blocks writesNon-concurrent CREATEAlways use CREATE INDEX CONCURRENTLY
N+1 queriesORM lazy loadingUse eager loading (include/JOIN), DataLoader, or batch queries
Sequence gap on PKROLLBACK increments sequenceAccept gaps. Switch to UUIDv7 for new tables.
DeadlockConflicting lock order across transactionsEnsure consistent lock ordering. Keep transactions short.

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

Anti-patternFix
No primary keyEvery table needs one (UUIDv7 preferred)
Index on every columnMax 3-4 per write-heavy table. Check usage with pg_stat_user_indexes.
SELECT * in application codeName explicit columns. Avoids breaking changes + unnecessary data transfer.
Functions on indexed columns (WHERE LOWER(email) = ...)Use expression index: CREATE INDEX ON users (LOWER(email))
VARCHAR(255) on all stringsTEXT unless max length is a business rule
Migrations without testingTest against prod copy (anonymized). Never just run on dev.
ENUM type for evolving valuesTEXT with CHECK constraint or reference table
Missing FK indexesEvery FK column needs an index for join performance
Sorting by unindexed column on large tablesAdd composite index with sort column last
COUNT(*) on large tables without WHEREUse estimates: SELECT reltuples FROM pg_class WHERE relname = 'table'

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
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 322kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 118kUpdated Claude CodeCodexVerifiedInstall