backend-database-patterns

Backend Database Patterns

Purpose

Design efficient, maintainable database schemas and queries. Every schema change must be backward-compatible. Every query must be verifiable with EXPLAIN ANALYZE.

Agent Protocol

Trigger

Exact user phrases: "database design", "schema design", "query optimization", "slow query", "migration", "index", "ORM pattern", "repository pattern", "N+1 problem", "transaction", "table design", "data model".

Input Context

Before activating, verify:

• The database type is known (PostgreSQL, MySQL, MongoDB, SQLite).
• The ORM or query framework is known (TypeORM, Prisma, SQLAlchemy, Diesel, GORM, Spring Data JDBC/JPA).
• The specific schema, query, or problem is described.

Output Artifact

No file output unless requested. Produces text guidance.

Response Format

Schema design:

## {entity}
| Field | Type | Constraints | Index |
|-------|------|-------------|-------|

Query fix:

## Problem: {description}
Root cause: {specific cause}
Fix: {specific change}
Verification: EXPLAIN ANALYZE {query}

Migration:

## Migration: {description}
Up: {SQL}
Down: {SQL}
Backward-compatible: {yes/no}

Completion Criteria

• Schema design follows normalization principles (3NF by default).
• Indexes are specified for every foreign key and filtered column.
• N+1 queries are identified and fixed.
• Migration includes up AND down scripts.
• Migration is verified to be backward-compatible.
• Transaction boundaries are documented.

Max Response Length

Schema: unlimited. Query fix: 6 lines. Migration: 10 lines.

Architecture Decision Tree

Normalize or Denormalize?

Is the data write-heavy with complex relationships?
  ├── Yes → Normalize (3NF by default)
  └── No → Is there a proven read performance problem (profiled)?
            ├── Yes → Denormalize (with documented trade-offs)
            └── No → Normalize first, optimize later

Primary Key Choice

Is the system single-node with no sharding?
  ├── Yes → BIGSERIAL (auto-increment)
  └── No → Is time-sortable ordering needed?
            ├── Yes → UUID v7 (time-ordered, distributed-safe)
            └── No → UUID v4 (random, distributed-safe)