# Db Sculptor

> 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 dimen…

- **Type:** Skill
- **Install:** `agentstack add skill-eliasoulkadi-shokunin-db-sculptor`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [EliasOulkadi](https://agentstack.voostack.com/s/eliasoulkadi)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [EliasOulkadi](https://github.com/EliasOulkadi)
- **Source:** https://github.com/EliasOulkadi/shokunin/tree/master/.pack/skills/db-sculptor
- **Website:** https://eliasoulkadi.github.io/shokunin/

## Install

```sh
agentstack add skill-eliasoulkadi-shokunin-db-sculptor
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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

| Command | Description |
|---------|-------------|
| `design` | Design a schema from access patterns and data volume estimates |
| `index` | Analyze queries and recommend/create optimal indexes |
| `optimize` | Diagnose slow queries with EXPLAIN ANALYZE and fix them |
| `migrate` | Create a safe, zero-downtime migration (expand/contract) |
| `audit` | Audit existing schema against best practices and anti-patterns |

## Workflow

### Step 1: Model for access patterns

| Question | Determine |
|----------|-----------|
| 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  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
// 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:**
```typescript
// 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:**

```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

```ini
# 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

| Scenario | Cause | Fix |
|----------|-------|-----|
| Migration lock timeout | Long-running query on same table | Set `lock_timeout = '5s'`. Run in low-traffic window. |
| Query slow in prod, fast in dev | Different data volume and distribution | Test with prod-size data (~1M+ rows) in staging |
| CREATE INDEX blocks writes | Non-concurrent CREATE | Always use `CREATE INDEX CONCURRENTLY` |
| N+1 queries | ORM lazy loading | Use eager loading (`include`/`JOIN`), DataLoader, or batch queries |
| Sequence gap on PK | ROLLBACK increments sequence | Accept gaps. Switch to UUIDv7 for new tables. |
| Deadlock | Conflicting lock order across transactions | Ensure 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-pattern | Fix |
|-------------|-----|
| No primary key | Every table needs one (UUIDv7 preferred) |
| Index on every column | Max 3-4 per write-heavy table. Check usage with `pg_stat_user_indexes`. |
| `SELECT *` in application code | Name 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 strings | `TEXT` unless max length is a business rule |
| Migrations without testing | Test against prod copy (anonymized). Never just run on dev. |
| ENUM type for evolving values | `TEXT` with CHECK constraint or reference table |
| Missing FK indexes | Every FK column needs an index for join performance |
| Sorting by unindexed column on large tables | Add composite index with sort column last |
| `COUNT(*)` on large tables without WHERE | Use 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

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [EliasOulkadi](https://github.com/EliasOulkadi)
- **Source:** [EliasOulkadi/shokunin](https://github.com/EliasOulkadi/shokunin)
- **License:** MIT
- **Homepage:** https://eliasoulkadi.github.io/shokunin/

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-eliasoulkadi-shokunin-db-sculptor
- Seller: https://agentstack.voostack.com/s/eliasoulkadi
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.