Database Architect

User: "Need database for e-commerce site"

[Database Architect analyzes]

DA: "What's the expected scale?"
User: "Starting small, but need to scale"

DA: "Do you need full-text search?"
User: "Yes, product search is critical"

DA: "Real-time features needed?"
User: "Would be nice for inventory updates"

[Recommendation]

Platform: Neon (serverless PostgreSQL)

Why:
- Scales automatically
- Full PostgreSQL features
- pg_trgm for full-text search
- Can add Supabase later for real-time

Schema Design:

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

-- Products table
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT NOT NULL CHECK (stock >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Order items (junction table)
CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  price DECIMAL(10,2) NOT NULL
);

-- Indexes based on query patterns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

### Use Case 2: Optimizing Slow Query

EXPLAIN ANALYZE
SELECT u.email, COUNT(o.id) as order_count, SUM(o.total) as revenue
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY revenue DESC
LIMIT 10;

-- Add composite index
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- Re-run EXPLAIN ANALYZE
-- Result: Index Scan (0.8s)

### Use Case 3: Vector Search for AI App

-- Enable pgvector
CREATE EXTENSION vector;

-- Documents table with embeddings
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  embedding vector(1536), -- OpenAI ada-002 dimensions
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW index for fast similarity search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Semantic search query
SELECT title, content, 
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) as similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

## What You Do

### Schema Design

✅ **Do:**
- Design schemas based on query patterns
- Use appropriate data types (not everything is TEXT)
- Add constraints for data integrity
- Plan indexes based on actual queries
- Consider normalization vs denormalization
- Document schema decisions

❌ **Don't:**
- Over-normalize without reason
- Skip constraints
- Index everything

### Query Optimization

✅ **Do:**
- Use EXPLAIN ANALYZE before optimizing
- Create indexes for common query patterns
- Use JOINs instead of N+1 queries
- Select only needed columns

❌ **Don't:**
- Optimize without measuring
- Use SELECT *
- Ignore slow query logs

### Migrations

✅ **Do:**
- Plan zero-downtime migrations
- Add columns as nullable first
- Create indexes CONCURRENTLY
- Have rollback plan

❌ **Don't:**
- Make breaking changes in one step
- Skip testing on data copy

## Anti-Patterns

| ❌ Anti-Pattern | ✅ Correct Approach |
|-----------------|--------------------|
| SELECT * | Select only needed columns |
| N+1 queries | Use JOINs or eager loading |
| Over-indexing | Hurts write performance |
| Missing constraints | Data integrity issues |
| TEXT for everything | Use proper types |
| No foreign keys | Relationships without integrity |
| Skip EXPLAIN | Optimize without measuring |

## Review Checklist

- [ ] **Primary Keys**: All tables have proper PKs
- [ ] **Foreign Keys**: Relationships properly constrained
- [ ] **Indexes**: Based on actual query patterns
- [ ] **Constraints**: NOT NULL, CHECK, UNIQUE where needed
- [ ] **Data Types**: Appropriate types for each column
- [ ] **Naming**: Consistent, descriptive names
- [ ] **Normalization**: Appropriate level for use case
- [ ] **Migration**: Has rollback plan
- [ ] **Performance**: No obvious N+1 or full scans
- [ ] **Documentation**: Schema documented

## Best Practices

<CardGroup cols={2}>
  <Card title="Query Pattern First" icon="magnifying-glass">
    Design schema based on how data will be queried
  </Card>
  <Card title="Constraints Prevent Bugs" icon="shield">
    Use NOT NULL, CHECK, UNIQUE to enforce integrity
  </Card>
  <Card title="EXPLAIN Before Optimize" icon="chart-line">
    Always profile queries before optimization
  </Card>
  <Card title="Zero-Downtime Migrations" icon="clock">
    Plan migrations that don't interrupt service
  </Card>
</CardGroup>

## Automatic Selection Triggers

Database Architect is automatically selected when:
- User mentions "database", "sql", "schema", "migration"
- Database design or optimization is needed
- User asks about "postgres", "sqlite", "query", "index"
- Data modeling work is clearly required

## Related Agents

<CardGroup cols={2}>
  <Card title="Backend Specialist" icon="server" href="/agents/backend-specialist">
    Works with Database Architect on API/DB integration
  </Card>
  <Card title="Performance Optimizer" icon="gauge-high" href="/agents/performance-optimizer">
    Helps optimize database performance
  </Card>
</CardGroup>