database-designer

# Database Designer - POWERFUL Tier Skill

## Overview

A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.

## Core Competencies

### Schema Design & Analysis
- **Normalization Analysis**: Automated detection of normalization levels (1NF through BCNF)
- **Denormalization Strategy**: Smart recommendations for performance optimization
- **Data Type Optimization**: Identification of inappropriate types and size issues
- **Constraint Analysis**: Missing foreign keys, unique constraints, and null checks
- **Naming Convention Validation**: Consistent table and column naming patterns
- **ERD Generation**: Automatic Mermaid diagram creation from DDL

### Index Optimization
- **Index Gap Analysis**: Identification of missing indexes on foreign keys and query patterns
- **Composite Index Strategy**: Optimal column ordering for multi-column indexes
- **Index Redundancy Detection**: Elimination of overlapping and unused indexes
- **Performance Impact Modeling**: Selectivity estimation and query cost analysis
- **Index Type Selection**: B-tree, hash, partial, covering, and specialized indexes

### Migration Management
- **Zero-Downtime Migrations**: Expand-contract pattern implementation
- **Schema Evolution**: Safe column additions, deletions, and type changes
- **Data Migration Scripts**: Automated data transformation and validation
- **Rollback Strategy**: Complete reversal capabilities with validation
- **Execution Planning**: Ordered migration steps with dependency resolution

## Tool Workflow (run these — do not analyze schemas by hand)

All paths relative to this skill folder; sample inputs in `assets/`.

### 1. Analyze the schema

```bash
python3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json
```

Accepts SQL DDL or JSON schema (`assets/sample_schema.sql` / `sample_schema.json`). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.

### 2. Optimize indexes against real query patterns

```bash
python3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json
```

Write the user's hot queries into a query-patterns JSON first (copy `assets/sample_query_patterns.json`). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.

### 3. Generate the migration

```bash
python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql
```

`--zero-downtime` emits an expand-contract plan; `--validate-only` checks feasibility without generating SQL.

### 4. Verification loop

Re-run step 1 on the *target* schema and assert the issues found in the first pass are gone; run `migration_generator.py --validate-only` before handing over the migration.

## Database Design Principles
→ See references/database-design-reference.md for details

## Best Practices

### Schema Design
1. **Use meaningful names**: Clear, consistent naming conventions
2. **Choose appropriate data types**: Right-sized columns for storage efficiency
3. **Define proper constraints**: Foreign keys, check constraints, unique indexes
4. **Consider future growth**: Plan for scale from the beginning
5. **Document relationships**: Clear foreign key relationships and business rules

### Performance Optimization
1. **Index strategically**: Cover common query patterns without over-indexing
2. **Monitor query performance**: Regular analysis of slow queries
3. **Partition large tables**: Improve query performance and maintenance
4. **Use appropriate isolation levels**: Balance consistency with performance
5. **Implement connection pooling**: Efficient resource utilization

### Security Considerations
1. **Principle of least privilege**: Grant minimal necessary permissions
2. **Encrypt sensitive data**: At rest and in transit
3. **Audit access patterns**: Monitor and log database access
4. **Validate inputs**: Prevent SQL injection attacks
5. **Regular security updates**: Keep database software current

## Query Generation Patterns

### SELECT with JOINs

```sql
-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
```

### Common Table Expressions (CTEs)

```sql
-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;
```

### Window Functions

```sql
-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;
```

### Aggregation Patterns

```sql
-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;

-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());
```

---

## Migration Patterns

### Up/Down Migration Scripts

Every migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:

```
migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql
```

### Zero-Downtime Migrations (Expand/Contract)

Use the expand-contract pattern to avoid locking or breaking running code:

1. **Expand** — add the new column/table (nullable, with default)
2. **Migrate data** — backfill in batches; dual-write from application
3. **Transition** — application reads from new column; stop writing to old
4. **Contract** — drop old column in a follow-up migration

### Data Backfill Strategies

```sql
-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected
```

### Rollback Procedures

- Always test the `down.sql` in staging before deploying `up.sql` to production
- Keep rollback window short — if the contract step has run, rollback requires a new forward migration
- For irreversible changes (dropping columns with data), take a logical backup first

---

## Performance Optimization

### Indexing Strategies

| Index Type | Use Case | Example |
|------------|----------|---------|
| **B-tree** (default) | Equality, range, ORDER BY | `CREATE INDEX idx_users_email ON users(email);` |
| **GIN** | Full-text search, JSONB, arrays | `CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));` |
| **GiST** | Geometry, range types, nearest-neighbor | `CREATE INDEX idx_locations ON places USING gist(coords);` |
| **Partial** | Subset of rows (reduce size) | `CREATE INDEX idx_active ON users(email) WHERE active = true;` |
| **Covering** | Index-only scans | `CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);` |

### EXPLAIN Plan Reading

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
```

Key signals to watch:
- **Seq Scan** on large tables — missing index
- **Nested Loop** with high row estimates — consider hash/merge join or add index
- **Buffers shared read** much higher than **hit** — working set exceeds memory

### N+1 Query Detection

Symptoms: application issues one query per row (e.g., fetching related records in a loop).

Fixes:
- Use `JOIN` or subquery to fetch in one round-trip
- ORM eager loading (`select_related` / `includes` / `with`)
- DataLoader pattern for GraphQL resolvers

### Connection Pooling

| Tool | Protocol | Best For |
|------|----------|----------|
| **PgBouncer** | PostgreSQL | Transaction/statement pooling, low overhead |
| **ProxySQL** | MySQL | Query routing, read/write splitting |
| **Built-in pool** (HikariCP, SQLAlchemy pool) | Any | Application-level pooling |

**Rule of thumb:** Set pool size to `(2 * CPU cores) + disk spindles`. For cloud SSDs, start with `2 * vCPUs` and tune.

### Read Replicas and Query Routing

- Route all `SELECT` queries to replicas; writes to primary
- Account for replication lag (typically <1s for async, 0 for sync)
- Use `pg_last_wal_replay_lsn()` to detect lag before reading critical data

---

## Multi-Database Decision Matrix

| Criteria | PostgreSQL | MySQL | SQLite | SQL Server |
|----------|-----------|-------|--------|------------|
| **Best for** | Complex queries, JSONB, extensions | Web apps, read-heavy workloads | Embedded, dev/test, edge | Enterprise .NET stacks |
| **JSON support** | Excellent (JSONB + GIN) | Good (JSON type) | Minimal | Good (OPENJSON) |
| **Replication** | Streaming, logical | Group replication, InnoDB cluster | N/A | Always On AG |
| **Licensing** | Open source (PostgreSQL License) | Open source (GPL) / commercial | Public domain | Commercial |
| **Max practical size** | Multi-TB | Multi-TB | ~1 TB (single-writer) | Multi-TB |

**When to choose:**
- **PostgreSQL** — default choice for new projects; best extensibility and standards compliance
- **MySQL** — existing MySQL ecosystem; simple read-heavy web applications
- **SQLite** — mobile apps, CLI tools, unit test databases, IoT/edge
- **SQL Server** — mandated by enterprise policy; deep .NET/Azure integration

### NoSQL Considerations

| Database | Model | Use When |
|----------|-------|----------|
| **MongoDB** | Document | Schema flexibility, rapid prototyping, content management |
| **Redis** | Key-value / cache | Session store, rate limiting, leaderboards, pub/sub |
| **DynamoDB** | Wide-column | Serverless AWS apps, single-digit-ms latency at any scale |

> Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.

---

## Sharding & Replication

### Horizontal vs Vertical Partitioning

- **Vertical partitioning**: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.
- **Horizontal partitioning (sharding)**: Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.

### Sharding Strategies

| Strategy | How It Works | Pros | Cons |
|----------|-------------|------|------|
| **Hash** | `shard = hash(key) % N` | Even distribution | Resharding is expensive |
| **Range** | Shard by date or ID range | Simple, good for time-series | Hot spots on latest shard |
| **Geographic** | Shard by user region | Data locality, compliance | Cross-region queries are hard |

### Replication Patterns

| Pattern | Consistency | Latency | Use Case |
|---------|------------|---------|----------|
| **Synchronous** | Strong | Higher write latency | Financial transactions |
| **Asynchronous** | Eventual | Low write latency | Read-heavy web apps |
| **Semi-synchronous** | At-least-one replica confirmed | Moderate | Balance of safety and speed |

---

## Cross-References

- **sql-database-assistant** — query writing, optimization, and debugging for day-to-day SQL work
- **database-schema-designer** — ERD modeling, normalization analysis, and schema generation
- **migration-architect** — large-scale migration planning across database engines or major schema overhauls
- **senior-backend** — application-layer patterns (connection pooling, ORM best practices)
- **senior-devops** — infrastructure provisioning for database clusters and replicas

{
  "format": "aiskill/v1",
  "skill": {
    "name": "database-designer",
    "slug": "database-designer",
    "version": "1.0.0",
    "description": "Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.",
    "category": "Development",
    "tags": [
      "engineering",
      "sql"
    ],
    "platforms": [
      "chatgpt",
      "claude-code",
      "cursor"
    ],
    "tested_environment": "Agent Skills convention (SKILL.md) — works with Claude Code, Cursor, ChatGPT/Codex and compatible agents",
    "license": "MIT"
  },
  "prompts": {
    "system": "# Database Designer - POWERFUL Tier Skill\r\n\r\n## Overview\r\n\r\nA comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.\r\n\r\n## Core Competencies\r\n\r\n### Schema Design & Analysis\r\n- **Normalization Analysis**: Automated detection of normalization levels (1NF through BCNF)\r\n- **Denormalization Strategy**: Smart recommendations for performance optimization\r\n- **Data Type Optimization**: Identification of inappropriate types and size issues\r\n- **Constraint Analysis**: Missing foreign keys, unique constraints, and null checks\r\n- **Naming Convention Validation**: Consistent table and column naming patterns\r\n- **ERD Generation**: Automatic Mermaid diagram creation from DDL\r\n\r\n### Index Optimization\r\n- **Index Gap Analysis**: Identification of missing indexes on foreign keys and query patterns\r\n- **Composite Index Strategy**: Optimal column ordering for multi-column indexes\r\n- **Index Redundancy Detection**: Elimination of overlapping and unused indexes\r\n- **Performance Impact Modeling**: Selectivity estimation and query cost analysis\r\n- **Index Type Selection**: B-tree, hash, partial, covering, and specialized indexes\r\n\r\n### Migration Management\r\n- **Zero-Downtime Migrations**: Expand-contract pattern implementation\r\n- **Schema Evolution**: Safe column additions, deletions, and type changes\r\n- **Data Migration Scripts**: Automated data transformation and validation\r\n- **Rollback Strategy**: Complete reversal capabilities with validation\r\n- **Execution Planning**: Ordered migration steps with dependency resolution\r\n\r\n## Tool Workflow (run these — do not analyze schemas by hand)\r\n\r\nAll paths relative to this skill folder; sample inputs in `assets/`.\r\n\r\n### 1. Analyze the schema\r\n\r\n```bash\r\npython3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json\r\n```\r\n\r\nAccepts SQL DDL or JSON schema (`assets/sample_schema.sql` / `sample_schema.json`). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.\r\n\r\n### 2. Optimize indexes against real query patterns\r\n\r\n```bash\r\npython3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json\r\n```\r\n\r\nWrite the user's hot queries into a query-patterns JSON first (copy `assets/sample_query_patterns.json`). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.\r\n\r\n### 3. Generate the migration\r\n\r\n```bash\r\npython3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql\r\n```\r\n\r\n`--zero-downtime` emits an expand-contract plan; `--validate-only` checks feasibility without generating SQL.\r\n\r\n### 4. Verification loop\r\n\r\nRe-run step 1 on the *target* schema and assert the issues found in the first pass are gone; run `migration_generator.py --validate-only` before handing over the migration.\r\n\r\n## Database Design Principles\r\n→ See references/database-design-reference.md for details\r\n\r\n## Best Practices\r\n\r\n### Schema Design\r\n1. **Use meaningful names**: Clear, consistent naming conventions\r\n2. **Choose appropriate data types**: Right-sized columns for storage efficiency\r\n3. **Define proper constraints**: Foreign keys, check constraints, unique indexes\r\n4. **Consider future growth**: Plan for scale from the beginning\r\n5. **Document relationships**: Clear foreign key relationships and business rules\r\n\r\n### Performance Optimization\r\n1. **Index strategically**: Cover common query patterns without over-indexing\r\n2. **Monitor query performance**: Regular analysis of slow queries\r\n3. **Partition large tables**: Improve query performance and maintenance\r\n4. **Use appropriate isolation levels**: Balance consistency with performance\r\n5. **Implement connection pooling**: Efficient resource utilization\r\n\r\n### Security Considerations\r\n1. **Principle of least privilege**: Grant minimal necessary permissions\r\n2. **Encrypt sensitive data**: At rest and in transit\r\n3. **Audit access patterns**: Monitor and log database access\r\n4. **Validate inputs**: Prevent SQL injection attacks\r\n5. **Regular security updates**: Keep database software current\r\n\r\n## Query Generation Patterns\r\n\r\n### SELECT with JOINs\r\n\r\n```sql\r\n-- INNER JOIN: only matching rows\r\nSELECT o.id, c.name, o.total\r\nFROM orders o\r\nINNER JOIN customers c ON c.id = o.customer_id;\r\n\r\n-- LEFT JOIN: all left rows, NULLs for non-matches\r\nSELECT c.name, COUNT(o.id) AS order_count\r\nFROM customers c\r\nLEFT JOIN orders o ON o.customer_id = c.id\r\nGROUP BY c.name;\r\n\r\n-- Self-join: hierarchical data (employees/managers)\r\nSELECT e.name AS employee, m.name AS manager\r\nFROM employees e\r\nLEFT JOIN employees m ON m.id = e.manager_id;\r\n```\r\n\r\n### Common Table Expressions (CTEs)\r\n\r\n```sql\r\n-- Recursive CTE for org chart\r\nWITH RECURSIVE org AS (\r\n  SELECT id, name, manager_id, 1 AS depth\r\n  FROM employees WHERE manager_id IS NULL\r\n  UNION ALL\r\n  SELECT e.id, e.name, e.manager_id, o.depth + 1\r\n  FROM employees e INNER JOIN org o ON o.id = e.manager_id\r\n)\r\nSELECT * FROM org ORDER BY depth, name;\r\n```\r\n\r\n### Window Functions\r\n\r\n```sql\r\n-- ROW_NUMBER for pagination / dedup\r\nSELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn\r\nFROM orders;\r\n\r\n-- RANK with gaps, DENSE_RANK without gaps\r\nSELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;\r\n\r\n-- LAG/LEAD for comparing adjacent rows\r\nSELECT date, revenue,\r\n  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change\r\nFROM daily_sales;\r\n```\r\n\r\n### Aggregation Patterns\r\n\r\n```sql\r\n-- FILTER clause (PostgreSQL) for conditional aggregation\r\nSELECT\r\n  COUNT(*) AS total,\r\n  COUNT(*) FILTER (WHERE status = 'active') AS active,\r\n  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive\r\nFROM accounts;\r\n\r\n-- GROUPING SETS for multi-level rollups\r\nSELECT region, product, SUM(revenue)\r\nFROM sales\r\nGROUP BY GROUPING SETS ((region, product), (region), ());\r\n```\r\n\r\n---\r\n\r\n## Migration Patterns\r\n\r\n### Up/Down Migration Scripts\r\n\r\nEvery migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:\r\n\r\n```\r\nmigrations/\r\n├── 20260101_000001_create_users.up.sql\r\n├── 20260101_000001_create_users.down.sql\r\n├── 20260115_000002_add_users_email_index.up.sql\r\n└── 20260115_000002_add_users_email_index.down.sql\r\n```\r\n\r\n### Zero-Downtime Migrations (Expand/Contract)\r\n\r\nUse the expand-contract pattern to avoid locking or breaking running code:\r\n\r\n1. **Expand** — add the new column/table (nullable, with default)\r\n2. **Migrate data** — backfill in batches; dual-write from application\r\n3. **Transition** — application reads from new column; stop writing to old\r\n4. **Contract** — drop old column in a follow-up migration\r\n\r\n### Data Backfill Strategies\r\n\r\n```sql\r\n-- Batch update to avoid long-running locks\r\nUPDATE users SET email_normalized = LOWER(email)\r\nWHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);\r\n-- Repeat in a loop until 0 rows affected\r\n```\r\n\r\n### Rollback Procedures\r\n\r\n- Always test the `down.sql` in staging before deploying `up.sql` to production\r\n- Keep rollback window short — if the contract step has run, rollback requires a new forward migration\r\n- For irreversible changes (dropping columns with data), take a logical backup first\r\n\r\n---\r\n\r\n## Performance Optimization\r\n\r\n### Indexing Strategies\r\n\r\n| Index Type | Use Case | Example |\r\n|------------|----------|---------|\r\n| **B-tree** (default) | Equality, range, ORDER BY | `CREATE INDEX idx_users_email ON users(email);` |\r\n| **GIN** | Full-text search, JSONB, arrays | `CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));` |\r\n| **GiST** | Geometry, range types, nearest-neighbor | `CREATE INDEX idx_locations ON places USING gist(coords);` |\r\n| **Partial** | Subset of rows (reduce size) | `CREATE INDEX idx_active ON users(email) WHERE active = true;` |\r\n| **Covering** | Index-only scans | `CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);` |\r\n\r\n### EXPLAIN Plan Reading\r\n\r\n```sql\r\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;\r\n```\r\n\r\nKey signals to watch:\r\n- **Seq Scan** on large tables — missing index\r\n- **Nested Loop** with high row estimates — consider hash/merge join or add index\r\n- **Buffers shared read** much higher than **hit** — working set exceeds memory\r\n\r\n### N+1 Query Detection\r\n\r\nSymptoms: application issues one query per row (e.g., fetching related records in a loop).\r\n\r\nFixes:\r\n- Use `JOIN` or subquery to fetch in one round-trip\r\n- ORM eager loading (`select_related` / `includes` / `with`)\r\n- DataLoader pattern for GraphQL resolvers\r\n\r\n### Connection Pooling\r\n\r\n| Tool | Protocol | Best For |\r\n|------|----------|----------|\r\n| **PgBouncer** | PostgreSQL | Transaction/statement pooling, low overhead |\r\n| **ProxySQL** | MySQL | Query routing, read/write splitting |\r\n| **Built-in pool** (HikariCP, SQLAlchemy pool) | Any | Application-level pooling |\r\n\r\n**Rule of thumb:** Set pool size to `(2 * CPU cores) + disk spindles`. For cloud SSDs, start with `2 * vCPUs` and tune.\r\n\r\n### Read Replicas and Query Routing\r\n\r\n- Route all `SELECT` queries to replicas; writes to primary\r\n- Account for replication lag (typically <1s for async, 0 for sync)\r\n- Use `pg_last_wal_replay_lsn()` to detect lag before reading critical data\r\n\r\n---\r\n\r\n## Multi-Database Decision Matrix\r\n\r\n| Criteria | PostgreSQL | MySQL | SQLite | SQL Server |\r\n|----------|-----------|-------|--------|------------|\r\n| **Best for** | Complex queries, JSONB, extensions | Web apps, read-heavy workloads | Embedded, dev/test, edge | Enterprise .NET stacks |\r\n| **JSON support** | Excellent (JSONB + GIN) | Good (JSON type) | Minimal | Good (OPENJSON) |\r\n| **Replication** | Streaming, logical | Group replication, InnoDB cluster | N/A | Always On AG |\r\n| **Licensing** | Open source (PostgreSQL License) | Open source (GPL) / commercial | Public domain | Commercial |\r\n| **Max practical size** | Multi-TB | Multi-TB | ~1 TB (single-writer) | Multi-TB |\r\n\r\n**When to choose:**\r\n- **PostgreSQL** — default choice for new projects; best extensibility and standards compliance\r\n- **MySQL** — existing MySQL ecosystem; simple read-heavy web applications\r\n- **SQLite** — mobile apps, CLI tools, unit test databases, IoT/edge\r\n- **SQL Server** — mandated by enterprise policy; deep .NET/Azure integration\r\n\r\n### NoSQL Considerations\r\n\r\n| Database | Model | Use When |\r\n|----------|-------|----------|\r\n| **MongoDB** | Document | Schema flexibility, rapid prototyping, content management |\r\n| **Redis** | Key-value / cache | Session store, rate limiting, leaderboards, pub/sub |\r\n| **DynamoDB** | Wide-column | Serverless AWS apps, single-digit-ms latency at any scale |\r\n\r\n> Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.\r\n\r\n---\r\n\r\n## Sharding & Replication\r\n\r\n### Horizontal vs Vertical Partitioning\r\n\r\n- **Vertical partitioning**: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.\r\n- **Horizontal partitioning (sharding)**: Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.\r\n\r\n### Sharding Strategies\r\n\r\n| Strategy | How It Works | Pros | Cons |\r\n|----------|-------------|------|------|\r\n| **Hash** | `shard = hash(key) % N` | Even distribution | Resharding is expensive |\r\n| **Range** | Shard by date or ID range | Simple, good for time-series | Hot spots on latest shard |\r\n| **Geographic** | Shard by user region | Data locality, compliance | Cross-region queries are hard |\r\n\r\n### Replication Patterns\r\n\r\n| Pattern | Consistency | Latency | Use Case |\r\n|---------|------------|---------|----------|\r\n| **Synchronous** | Strong | Higher write latency | Financial transactions |\r\n| **Asynchronous** | Eventual | Low write latency | Read-heavy web apps |\r\n| **Semi-synchronous** | At-least-one replica confirmed | Moderate | Balance of safety and speed |\r\n\r\n---\r\n\r\n## Cross-References\r\n\r\n- **sql-database-assistant** — query writing, optimization, and debugging for day-to-day SQL work\r\n- **database-schema-designer** — ERD modeling, normalization analysis, and schema generation\r\n- **migration-architect** — large-scale migration planning across database engines or major schema overhauls\r\n- **senior-backend** — application-layer patterns (connection pooling, ORM best practices)\r\n- **senior-devops** — infrastructure provisioning for database clusters and replicas",
    "user_template": ""
  },
  "variables": [],
  "install": {
    "steps": [
      {
        "title": "Download & unzip",
        "detail": "Download the .zip package from SkillVault and unzip it. The database-designer/ folder contains SKILL.md plus any companion scripts, references and assets."
      },
      {
        "title": "Copy into your agent skills directory",
        "detail": "Move the folder into your agent's skills directory — e.g. ~/.claude/skills/ (Claude Code), .codex/skills/, .cursor/skills/ or your project's .claude/skills/ folder — then restart or reload the session."
      },
      {
        "title": "Use it",
        "detail": "Describe the task in natural language; the agent loads the skill automatically when the request matches the skill description."
      }
    ]
  },
  "usage": {
    "steps": []
  },
  "metadata": {
    "author": "SkillVault",
    "source": "https://skillvault.top/skills/database-designer",
    "exported_at": "2026-11-03T07:41:01.328Z"
  }
}

format: aiskill/v1
skill:
  name: database-designer
  slug: database-designer
  version: 1.0.0
  description: >-
    Use when the user asks to design database schemas, plan data migrations, optimize queries,
    choose between SQL and NoSQL, or model data relationships.
  category: Development
  tags:
    - engineering
    - sql
  platforms:
    - chatgpt
    - claude-code
    - cursor
  tested_environment: >-
    Agent Skills convention (SKILL.md) — works with Claude Code, Cursor, ChatGPT/Codex and
    compatible agents
  license: MIT
prompts:
  system: "# Database Designer - POWERFUL Tier Skill\r\n\r\n## Overview\r\n\r\nA comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.\r\n\r\n## Core Competencies\r\n\r\n### Schema Design & Analysis\r\n- **Normalization Analysis**: Automated detection of normalization levels (1NF through BCNF)\r\n- **Denormalization Strategy**: Smart recommendations for performance optimization\r\n- **Data Type Optimization**: Identification of inappropriate types and size issues\r\n- **Constraint Analysis**: Missing foreign keys, unique constraints, and null checks\r\n- **Naming Convention Validation**: Consistent table and column naming patterns\r\n- **ERD Generation**: Automatic Mermaid diagram creation from DDL\r\n\r\n### Index Optimization\r\n- **Index Gap Analysis**: Identification of missing indexes on foreign keys and query patterns\r\n- **Composite Index Strategy**: Optimal column ordering for multi-column indexes\r\n- **Index Redundancy Detection**: Elimination of overlapping and unused indexes\r\n- **Performance Impact Modeling**: Selectivity estimation and query cost analysis\r\n- **Index Type Selection**: B-tree, hash, partial, covering, and specialized indexes\r\n\r\n### Migration Management\r\n- **Zero-Downtime Migrations**: Expand-contract pattern implementation\r\n- **Schema Evolution**: Safe column additions, deletions, and type changes\r\n- **Data Migration Scripts**: Automated data transformation and validation\r\n- **Rollback Strategy**: Complete reversal capabilities with validation\r\n- **Execution Planning**: Ordered migration steps with dependency resolution\r\n\r\n## Tool Workflow (run these — do not analyze schemas by hand)\r\n\r\nAll paths relative to this skill folder; sample inputs in `assets/`.\r\n\r\n### 1. Analyze the schema\r\n\r\n```bash\r\npython3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json\r\n```\r\n\r\nAccepts SQL DDL or JSON schema (`assets/sample_schema.sql` / `sample_schema.json`). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.\r\n\r\n### 2. Optimize indexes against real query patterns\r\n\r\n```bash\r\npython3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json\r\n```\r\n\r\nWrite the user's hot queries into a query-patterns JSON first (copy `assets/sample_query_patterns.json`). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.\r\n\r\n### 3. Generate the migration\r\n\r\n```bash\r\npython3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql\r\n```\r\n\r\n`--zero-downtime` emits an expand-contract plan; `--validate-only` checks feasibility without generating SQL.\r\n\r\n### 4. Verification loop\r\n\r\nRe-run step 1 on the *target* schema and assert the issues found in the first pass are gone; run `migration_generator.py --validate-only` before handing over the migration.\r\n\r\n## Database Design Principles\r\n→ See references/database-design-reference.md for details\r\n\r\n## Best Practices\r\n\r\n### Schema Design\r\n1. **Use meaningful names**: Clear, consistent naming conventions\r\n2. **Choose appropriate data types**: Right-sized columns for storage efficiency\r\n3. **Define proper constraints**: Foreign keys, check constraints, unique indexes\r\n4. **Consider future growth**: Plan for scale from the beginning\r\n5. **Document relationships**: Clear foreign key relationships and business rules\r\n\r\n### Performance Optimization\r\n1. **Index strategically**: Cover common query patterns without over-indexing\r\n2. **Monitor query performance**: Regular analysis of slow queries\r\n3. **Partition large tables**: Improve query performance and maintenance\r\n4. **Use appropriate isolation levels**: Balance consistency with performance\r\n5. **Implement connection pooling**: Efficient resource utilization\r\n\r\n### Security Considerations\r\n1. **Principle of least privilege**: Grant minimal necessary permissions\r\n2. **Encrypt sensitive data**: At rest and in transit\r\n3. **Audit access patterns**: Monitor and log database access\r\n4. **Validate inputs**: Prevent SQL injection attacks\r\n5. **Regular security updates**: Keep database software current\r\n\r\n## Query Generation Patterns\r\n\r\n### SELECT with JOINs\r\n\r\n```sql\r\n-- INNER JOIN: only matching rows\r\nSELECT o.id, c.name, o.total\r\nFROM orders o\r\nINNER JOIN customers c ON c.id = o.customer_id;\r\n\r\n-- LEFT JOIN: all left rows, NULLs for non-matches\r\nSELECT c.name, COUNT(o.id) AS order_count\r\nFROM customers c\r\nLEFT JOIN orders o ON o.customer_id = c.id\r\nGROUP BY c.name;\r\n\r\n-- Self-join: hierarchical data (employees/managers)\r\nSELECT e.name AS employee, m.name AS manager\r\nFROM employees e\r\nLEFT JOIN employees m ON m.id = e.manager_id;\r\n```\r\n\r\n### Common Table Expressions (CTEs)\r\n\r\n```sql\r\n-- Recursive CTE for org chart\r\nWITH RECURSIVE org AS (\r\n  SELECT id, name, manager_id, 1 AS depth\r\n  FROM employees WHERE manager_id IS NULL\r\n  UNION ALL\r\n  SELECT e.id, e.name, e.manager_id, o.depth + 1\r\n  FROM employees e INNER JOIN org o ON o.id = e.manager_id\r\n)\r\nSELECT * FROM org ORDER BY depth, name;\r\n```\r\n\r\n### Window Functions\r\n\r\n```sql\r\n-- ROW_NUMBER for pagination / dedup\r\nSELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn\r\nFROM orders;\r\n\r\n-- RANK with gaps, DENSE_RANK without gaps\r\nSELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;\r\n\r\n-- LAG/LEAD for comparing adjacent rows\r\nSELECT date, revenue,\r\n  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change\r\nFROM daily_sales;\r\n```\r\n\r\n### Aggregation Patterns\r\n\r\n```sql\r\n-- FILTER clause (PostgreSQL) for conditional aggregation\r\nSELECT\r\n  COUNT(*) AS total,\r\n  COUNT(*) FILTER (WHERE status = 'active') AS active,\r\n  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive\r\nFROM accounts;\r\n\r\n-- GROUPING SETS for multi-level rollups\r\nSELECT region, product, SUM(revenue)\r\nFROM sales\r\nGROUP BY GROUPING SETS ((region, product), (region), ());\r\n```\r\n\r\n---\r\n\r\n## Migration Patterns\r\n\r\n### Up/Down Migration Scripts\r\n\r\nEvery migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:\r\n\r\n```\r\nmigrations/\r\n├── 20260101_000001_create_users.up.sql\r\n├── 20260101_000001_create_users.down.sql\r\n├── 20260115_000002_add_users_email_index.up.sql\r\n└── 20260115_000002_add_users_email_index.down.sql\r\n```\r\n\r\n### Zero-Downtime Migrations (Expand/Contract)\r\n\r\nUse the expand-contract pattern to avoid locking or breaking running code:\r\n\r\n1. **Expand** — add the new column/table (nullable, with default)\r\n2. **Migrate data** — backfill in batches; dual-write from application\r\n3. **Transition** — application reads from new column; stop writing to old\r\n4. **Contract** — drop old column in a follow-up migration\r\n\r\n### Data Backfill Strategies\r\n\r\n```sql\r\n-- Batch update to avoid long-running locks\r\nUPDATE users SET email_normalized = LOWER(email)\r\nWHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);\r\n-- Repeat in a loop until 0 rows affected\r\n```\r\n\r\n### Rollback Procedures\r\n\r\n- Always test the `down.sql` in staging before deploying `up.sql` to production\r\n- Keep rollback window short — if the contract step has run, rollback requires a new forward migration\r\n- For irreversible changes (dropping columns with data), take a logical backup first\r\n\r\n---\r\n\r\n## Performance Optimization\r\n\r\n### Indexing Strategies\r\n\r\n| Index Type | Use Case | Example |\r\n|------------|----------|---------|\r\n| **B-tree** (default) | Equality, range, ORDER BY | `CREATE INDEX idx_users_email ON users(email);` |\r\n| **GIN** | Full-text search, JSONB, arrays | `CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));` |\r\n| **GiST** | Geometry, range types, nearest-neighbor | `CREATE INDEX idx_locations ON places USING gist(coords);` |\r\n| **Partial** | Subset of rows (reduce size) | `CREATE INDEX idx_active ON users(email) WHERE active = true;` |\r\n| **Covering** | Index-only scans | `CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);` |\r\n\r\n### EXPLAIN Plan Reading\r\n\r\n```sql\r\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;\r\n```\r\n\r\nKey signals to watch:\r\n- **Seq Scan** on large tables — missing index\r\n- **Nested Loop** with high row estimates — consider hash/merge join or add index\r\n- **Buffers shared read** much higher than **hit** — working set exceeds memory\r\n\r\n### N+1 Query Detection\r\n\r\nSymptoms: application issues one query per row (e.g., fetching related records in a loop).\r\n\r\nFixes:\r\n- Use `JOIN` or subquery to fetch in one round-trip\r\n- ORM eager loading (`select_related` / `includes` / `with`)\r\n- DataLoader pattern for GraphQL resolvers\r\n\r\n### Connection Pooling\r\n\r\n| Tool | Protocol | Best For |\r\n|------|----------|----------|\r\n| **PgBouncer** | PostgreSQL | Transaction/statement pooling, low overhead |\r\n| **ProxySQL** | MySQL | Query routing, read/write splitting |\r\n| **Built-in pool** (HikariCP, SQLAlchemy pool) | Any | Application-level pooling |\r\n\r\n**Rule of thumb:** Set pool size to `(2 * CPU cores) + disk spindles`. For cloud SSDs, start with `2 * vCPUs` and tune.\r\n\r\n### Read Replicas and Query Routing\r\n\r\n- Route all `SELECT` queries to replicas; writes to primary\r\n- Account for replication lag (typically <1s for async, 0 for sync)\r\n- Use `pg_last_wal_replay_lsn()` to detect lag before reading critical data\r\n\r\n---\r\n\r\n## Multi-Database Decision Matrix\r\n\r\n| Criteria | PostgreSQL | MySQL | SQLite | SQL Server |\r\n|----------|-----------|-------|--------|------------|\r\n| **Best for** | Complex queries, JSONB, extensions | Web apps, read-heavy workloads | Embedded, dev/test, edge | Enterprise .NET stacks |\r\n| **JSON support** | Excellent (JSONB + GIN) | Good (JSON type) | Minimal | Good (OPENJSON) |\r\n| **Replication** | Streaming, logical | Group replication, InnoDB cluster | N/A | Always On AG |\r\n| **Licensing** | Open source (PostgreSQL License) | Open source (GPL) / commercial | Public domain | Commercial |\r\n| **Max practical size** | Multi-TB | Multi-TB | ~1 TB (single-writer) | Multi-TB |\r\n\r\n**When to choose:**\r\n- **PostgreSQL** — default choice for new projects; best extensibility and standards compliance\r\n- **MySQL** — existing MySQL ecosystem; simple read-heavy web applications\r\n- **SQLite** — mobile apps, CLI tools, unit test databases, IoT/edge\r\n- **SQL Server** — mandated by enterprise policy; deep .NET/Azure integration\r\n\r\n### NoSQL Considerations\r\n\r\n| Database | Model | Use When |\r\n|----------|-------|----------|\r\n| **MongoDB** | Document | Schema flexibility, rapid prototyping, content management |\r\n| **Redis** | Key-value / cache | Session store, rate limiting, leaderboards, pub/sub |\r\n| **DynamoDB** | Wide-column | Serverless AWS apps, single-digit-ms latency at any scale |\r\n\r\n> Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.\r\n\r\n---\r\n\r\n## Sharding & Replication\r\n\r\n### Horizontal vs Vertical Partitioning\r\n\r\n- **Vertical partitioning**: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.\r\n- **Horizontal partitioning (sharding)**: Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.\r\n\r\n### Sharding Strategies\r\n\r\n| Strategy | How It Works | Pros | Cons |\r\n|----------|-------------|------|------|\r\n| **Hash** | `shard = hash(key) % N` | Even distribution | Resharding is expensive |\r\n| **Range** | Shard by date or ID range | Simple, good for time-series | Hot spots on latest shard |\r\n| **Geographic** | Shard by user region | Data locality, compliance | Cross-region queries are hard |\r\n\r\n### Replication Patterns\r\n\r\n| Pattern | Consistency | Latency | Use Case |\r\n|---------|------------|---------|----------|\r\n| **Synchronous** | Strong | Higher write latency | Financial transactions |\r\n| **Asynchronous** | Eventual | Low write latency | Read-heavy web apps |\r\n| **Semi-synchronous** | At-least-one replica confirmed | Moderate | Balance of safety and speed |\r\n\r\n---\r\n\r\n## Cross-References\r\n\r\n- **sql-database-assistant** — query writing, optimization, and debugging for day-to-day SQL work\r\n- **database-schema-designer** — ERD modeling, normalization analysis, and schema generation\r\n- **migration-architect** — large-scale migration planning across database engines or major schema overhauls\r\n- **senior-backend** — application-layer patterns (connection pooling, ORM best practices)\r\n- **senior-devops** — infrastructure provisioning for database clusters and replicas"
  user_template: ""
variables: []
install:
  steps:
    - title: Download & unzip
      detail: >-
        Download the .zip package from SkillVault and unzip it. The database-designer/ folder
        contains SKILL.md plus any companion scripts, references and assets.
    - title: Copy into your agent skills directory
      detail: >-
        Move the folder into your agent's skills directory — e.g. ~/.claude/skills/ (Claude Code),
        .codex/skills/, .cursor/skills/ or your project's .claude/skills/ folder — then restart or
        reload the session.
    - title: Use it
      detail: >-
        Describe the task in natural language; the agent loads the skill automatically when the
        request matches the skill description.
usage:
  steps: []
metadata:
  author: SkillVault
  source: https://skillvault.top/skills/database-designer
  exported_at: "2026-11-03T07:41:01.328Z"