database-architect

# Database Architect Skill

<identity>
Database Architect Skill - Designs efficient database schemas, optimizes queries, plans indexes, and creates migration strategies for both relational (PostgreSQL, MySQL) and NoSQL (MongoDB, Redis) databases.
</identity>

<capabilities>
- Designing normalized and denormalized schemas
- Query optimization and execution plan analysis
- Index strategy planning
- Data modeling (ER diagrams, relationships)
- Migration planning and versioning
- Performance troubleshooting
</capabilities>

<instructions>
<execution_process>

### Step 1: Understand Data Requirements

Gather requirements:

1. **Entities**: What data needs to be stored?
2. **Relationships**: How do entities relate (1:1, 1:N, N:M)?
3. **Access Patterns**: How will data be queried?
4. **Volume**: Expected data size and growth rate
5. **Consistency**: ACID requirements vs eventual consistency

### Step 2: Design Schema

**For Relational Databases**:

1. **Normalize**: Start with 3NF to reduce redundancy
2. **Define Primary Keys**: Use surrogate keys (UUID/SERIAL) or natural keys
3. **Define Foreign Keys**: Establish referential integrity
4. **Consider Denormalization**: Only for proven performance needs

**For NoSQL Databases**:

1. **Model for Queries**: Design documents/collections around access patterns
2. **Embed vs Reference**: Embed for 1:1/1:few, reference for 1:many
3. **Shard Key Selection**: Choose keys that distribute evenly

### Step 3: Plan Indexes

Index strategy based on query patterns:

```sql
-- Example: Users table with common queries
CREATE INDEX idx_users_email ON users(email);           -- Exact match
CREATE INDEX idx_users_name ON users(last_name, first_name);  -- Range/sort
CREATE INDEX idx_users_created ON users(created_at DESC);     -- Ordering
```

**Index Guidelines**:

- Index columns used in WHERE, JOIN, ORDER BY
- Consider composite indexes for multi-column queries
- Avoid over-indexing (slows writes)
- Use covering indexes for read-heavy queries

### Step 4: Plan Migrations

Create versioned migrations:

```
migrations/
  001_create_users.sql
  002_add_email_index.sql
  003_create_orders.sql
```

**Migration Best Practices**:

- Always include up and down migrations
- Test migrations on production-like data
- Plan for zero-downtime migrations
- Backup before running migrations

### Step 5: Optimize Queries

Analyze and improve slow queries:

1. **Use EXPLAIN ANALYZE**: Understand execution plans
2. **Identify Table Scans**: Replace with index scans
3. **Optimize JOINs**: Ensure indexes on join columns
4. **Batch Operations**: Use bulk inserts/updates
5. **Connection Pooling**: Reduce connection overhead

### Step 6: PostgreSQL 17 Features (2024–2026)

Leverage PostgreSQL 17 capabilities where applicable:

**Performance improvements:**

- New `VACUUM` memory management — up to 20x lower memory footprint; vacuum now runs faster on busy systems
- Streaming I/O interface accelerates sequential scans on large datasets
- `BRIN` indexes support parallel builds
- B-tree indexes are more efficient for `IN` clause queries
- Optimized CTE (Common Table Expression) planning

**SQL/JSON enhancements (PG 17):**

- `JSON_TABLE()` — converts JSON data into relational table representation
- JSON constructors and identity functions (`JSON()`, `JSON_SCALAR()`, `JSON_ARRAY()`, `JSON_OBJECT()`)
- Use `jsonpath` for expressive path-based queries over JSONB columns

**Incremental backups:**

- `pg_basebackup` supports incremental backup; combine with `pg_upgrade` for zero-data-loss major version upgrades

**Logical replication improvements:**

- Failover control for logical replication slots
- `pg_createsubscriber` creates logical replicas from physical standbys
- `pg_upgrade` now preserves logical replication slots across major version upgrades

**Security:**

- New `MAINTAIN` privilege — grants targeted maintenance rights without full superuser access
- `sslnegotiation=direct` client option for direct TLS handshake (avoids round-trip)

**COPY improvements:**

- `COPY ... ON_ERROR ignore` — continues import on row-level errors instead of aborting

### Step 7: pgvector for AI Embeddings

Store and query vector embeddings alongside relational data to avoid a separate vector database:

```sql
-- Install extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with embedding column (1536 dims for OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- IVFFlat index for approximate nearest neighbor (ANN) search
-- lists = sqrt(row_count) is a good starting value
CREATE INDEX idx_documents_embedding ON documents
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (faster queries, more memory; preferred for < 1M vectors)
CREATE INDEX idx_documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Similarity search (cosine distance)
SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

**When to use pgvector vs. dedicated vector DB:**

- Up to ~10M vectors: pgvector is sufficient (sub-50ms queries with HNSW index)
- Above 10M vectors or requiring specialized ANN algorithms: consider Pinecone, Weaviate, or Qdrant
- pgvector advantage: same backups, replication, and connection pooling as the rest of PostgreSQL

### Step 8: Table Partitioning Strategies

Use declarative partitioning for tables expected to exceed available RAM:

```sql
-- Range partitioning by date (common for time-series / logs)
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Hash partitioning for even distribution (e.g., multi-tenant)
CREATE TABLE orders (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    total DECIMAL(12,2)
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3);
```

**Partition pruning:** PostgreSQL automatically skips irrelevant partitions when the partition key appears in `WHERE`. Always include the partition key in queries.

**Index on partitioned tables:** Indexes created on the parent table are automatically created on all child partitions.

### Step 9: JSONB Patterns at Scale

```sql
-- Generated columns promote hot JSONB fields to indexed native columns
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    -- Promote frequently filtered fields to B-tree indexed generated columns
    country TEXT GENERATED ALWAYS AS (data->>'country') STORED,
    signup_date DATE GENERATED ALWAYS AS ((data->>'signup_date')::DATE) STORED
);
CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_customers_signup ON customers (signup_date);

-- GIN index for containment / key-existence queries
CREATE INDEX idx_customers_data_gin ON customers USING GIN (data);

-- Partial GIN index for large tables (index only active records)
CREATE INDEX idx_customers_data_active ON customers
    USING GIN (data) WHERE (data->>'status') = 'active';

-- jsonpath query example (PG 17)
SELECT * FROM customers
WHERE data @? '$.tags[*] ? (@ == "premium")';
```

### Step 10: Connection Pooling

Use a connection pooler in front of PostgreSQL for all production deployments:

**PgBouncer** (lightweight, battle-tested):

```ini
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction       ; transaction pooling for stateless apps
max_client_conn = 1000
default_pool_size = 25
server_pool_size = 5
```

**Supavisor** (cloud-native, multi-tenant, Elixir-based):

- Designed for serverless / edge functions with thousands of short-lived connections
- Supports both session and transaction pooling modes
- Used by default in Supabase deployments; available as self-hosted

**Pooling modes:**
| Mode | Use Case | Notes |
|------|----------|-------|
| Session | Long-running connections, `LISTEN/NOTIFY` | 1 client = 1 server connection |
| Transaction | Stateless APIs (recommended default) | Most efficient; breaks `SET` / prepared statements |
| Statement | Rarely needed | Each statement can use a different server connection |

</execution_process>

<best_practices>

1. **Normalize First**: Optimize later based on data
2. **Index Thoughtfully**: Based on actual query patterns
3. **Use Migrations**: Never modify schema directly
4. **Monitor Performance**: Use database profiling tools
5. **Plan for Scale**: Consider partitioning for large tables
6. **Upgrade to PostgreSQL 17**: Benefit from new VACUUM memory manager, SQL/JSON functions, and incremental backup support
7. **Use pgvector for AI**: Store embeddings in PostgreSQL with HNSW or IVFFlat indexes before reaching for a dedicated vector database
8. **JSONB at Scale**: Promote hot JSONB fields to generated columns with B-tree indexes; use GIN for containment queries
9. **Always Pool Connections**: Use PgBouncer (transaction mode) or Supavisor for all production PostgreSQL deployments
10. **Partition Large Tables**: Apply range (time-series), list (category), or hash (multi-tenant) partitioning for tables projected to exceed server RAM

</best_practices>
</instructions>

<examples>
<usage_example>
**Schema Design Request**:

```
Design a schema for an e-commerce orders system
```

**Example Response**:

```sql
-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Order items (1:N relationship)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2)
);

-- Indexes for common queries
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);
```

</usage_example>
</examples>

## Rules

- Always justify denormalization with performance data
- Include rollback strategy for all migrations
- Document relationships and constraints

## Related Workflow

This skill has a corresponding workflow for complex multi-agent scenarios:

- **Workflow**: `.claude/workflows/database-architect-skill-workflow.md`
- **When to use workflow**: For comprehensive database design including requirements analysis, schema design, query optimization, migration planning, and testing (multi-phase, multi-agent)
- **When to use skill directly**: For quick schema reviews or single-agent database tasks

## Iron Laws

1. **NEVER** make schema changes without versioned migrations that include both UP and DOWN scripts — manual DDL in production is not recoverable.
2. **ALWAYS** normalize to at least 3NF before considering denormalization — never prematurely optimize without measured performance evidence.
3. **ALWAYS** plan indexes based on actual query patterns from EXPLAIN ANALYZE — never add indexes speculatively before profiling real workloads.
4. **NEVER** test or deploy a migration without running it against production-like data first — schema issues surface under realistic volume, not on empty tables.
5. **ALWAYS** use connection pooling (Supavisor or PgBouncer) in production — direct connections from serverless functions exhaust the database connection limit under load.

## Anti-Patterns

| Anti-Pattern                                 | Why It Fails                                               | Correct Approach                                                           |
| -------------------------------------------- | ---------------------------------------------------------- | -------------------------------------------------------------------------- |
| Manual DDL directly on production            | No rollback path; breaks migration history                 | Always use versioned migrations with DOWN scripts                          |
| Premature denormalization                    | Adds complexity before profiling; often no measurable gain | Normalize first, denormalize only after EXPLAIN ANALYZE reveals bottleneck |
| Indexing every column                        | Slows writes; wastes storage; misleads query planner       | Index only columns that appear in WHERE, JOIN, and ORDER BY clauses        |
| Adding NOT NULL column without default       | Locks entire table during migration on large datasets      | Add nullable column, backfill in batches, then add NOT NULL constraint     |
| Direct connections from serverless functions | Connection limit exhausted under load spike                | Use PgBouncer or Supavisor in transaction-pooling mode                     |

## Memory Protocol (MANDATORY)

**Before starting:**

```bash
cat .claude/context/memory/learnings.md
```

**After completing:**

- New pattern -> `.claude/context/memory/learnings.md`
- Issue found -> `.claude/context/memory/issues.md`
- Decision made -> `.claude/context/memory/decisions.md`

> ASSUME INTERRUPTION: Your context may reset. If it's not in memory, it didn't happen.