Database Architect Skill

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:

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

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

</best_practices>

Design a schema for an e-commerce orders system

Example Response:

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

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

Memory Protocol (MANDATORY)

Before starting:

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.