---

name: backend-database-patterns description: > Use this skill when the user says 'database design', 'schema design', 'query optimization', 'slow query', 'migration', 'index', 'ORM', 'repository pattern', 'N+1 problem', 'transaction', or when designing or troubleshooting the data layer. This skill enforces schema design principles, indexing strategy, N+1 detection and fixing, transaction boundaries, migration best practices, and the repository pattern. Applies to PostgreSQL, MySQL, MongoDB, and ORMs (TypeORM, Prisma, SQLAlchemy, Diesel, GORM, Spring Data). Do NOT use for: API design, caching, or frontend state management. version: "2.0.0" author: "j4flmao" license: "MIT" compatibility: claude-code: true cursor: true codex: true windsurf: true tags: [backend, database, phase-2, universal]

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)

Index Type Selection

Query pattern:
  ├── Equality lookup (WHERE x = ?) → B-tree
  ├── Range query (WHERE x BETWEEN ? AND ?) → B-tree
  ├── Multi-column filter → Composite B-tree (high-selectivity first)
  ├── Partial subset → Partial index (WHERE status = 'active')
  ├── Full-text search → GIN (tsvector)
  ├── JSON/array containment → GIN (jsonb, @>)
  ├── Geospatial query → GiST (geography, geometry)
  └── All columns in query → Covering index (INCLUDE)

ORM vs Raw SQL?

What is your priority?
  ├── Developer productivity, CRUD-heavy → ORM (Prisma, TypeORM, SQLAlchemy)
  ├── Complex queries, performance-critical → Raw SQL + query builder (kysely, SQLAlchemy core)
  ├── Migration management → ORM migrations or standalone (Alembic, Flyway)
  └── Need both → Repository pattern with ORM for 80%, raw SQL for 20%

Workflow

Step 1: Schema Design

• Normalize to 3NF by default. Denormalize only when a performance measurement proves it necessary.
• UUID v7 for primary keys (time-sortable, no collisions in distributed systems).
• Every table has: id (UUID PK), created_at (TIMESTAMPTZ), updated_at (TIMESTAMPTZ).
• Soft deletes: use deleted_at TIMESTAMPTZ or is_active boolean.

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers(id),
  status order_status NOT NULL DEFAULT 'pending',
  total_amount NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ  -- soft delete
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE deleted_at IS NULL;

Step 2: Indexing Strategy

Index Type	When	Example
B-tree	Equality and range lookups	WHERE status = 'active'
Composite B-tree	Multi-column filters	WHERE status = 'active' AND created_at > '2026-01-01'
Partial	Filtered subset	WHERE status = 'active' on 10M rows, 90% inactive
Covering	All columns in query covered	Include selected columns to avoid heap lookups
GIN	JSON, arrays, full-text	WHERE tags @> ['urgent']
Unique	Uniqueness enforcement	WHERE email IS NOT NULL
BRIN	Large, naturally-ordered data	Time-series data, logs
Hash	Equality only, large values	URL lookup (smaller than B-tree)

Step 3: N+1 Detection and Fix

// N+1 — BAD
const users = await db.user.findMany();  // 1 query
for (const user of users) {
  const posts = await db.post.findMany({ where: { userId: user.id } });  // N queries
}

// Fixed — eager loading
const users = await db.user.findMany({ include: { posts: true } });  // 1 query with JOIN

# N+1 — BAD
users = await User.objects.all()  # 1 query
for user in users:
    posts = await Post.objects.filter(user=user).all()  # N queries

# Fixed — select_related
users = await User.objects.select_related('posts').all()  # 1 query

Step 4: Migration Best Practices

1. Every migration must have BOTH up and down scripts.
2. All migrations must be backward-compatible.
3. Three-phase destructive changes:
   • Phase 1: Add new column/table. Dual-write to old and new.
   • Phase 2: Backfill data. Switch reads to new. Verify consistency.
   • Phase 3 (after monitoring period): Remove old column/table.
4. Never drop a column in the same deployment that stops writing to it.
5. Test migrations against a copy of production data before running in production.

-- Phase 1: Add new column, dual-write
ALTER TABLE orders ADD COLUMN status_v2 VARCHAR(20);
-- Application writes to both `status` (old) and `status_v2` (new)

-- Phase 2: Backfill and switch reads
UPDATE orders SET status_v2 = status WHERE status_v2 IS NULL;
-- Application reads from `status_v2`, writes to `status_v2` only

-- Phase 3: Remove old column
ALTER TABLE orders DROP COLUMN status;

Step 5: Transaction Boundaries

• Transactions belong in Application use cases, NOT in controllers or repositories.
• Keep transactions as short as possible.
• Never hold a transaction open during external API calls or file I/O.
• Use Unit of Work pattern for coordinating multiple repository operations.

class TransferFundsHandler {
  async execute(command: TransferFundsCommand): Promise<Result> {
    return this.unitOfWork.execute(async () => {
      const from = await this.accountRepo.findById(command.fromAccountId);
      const to = await this.accountRepo.findById(command.toAccountId);
      from.withdraw(command.amount);
      to.deposit(command.amount);
      await this.accountRepo.save(from);
      await this.accountRepo.save(to);
      return Result.success();
    });
  }
}

Step 6: Repository Pattern

Interface (Domain):
  interface UserRepository {
    findById(id: UserId): Promise<User | null>
    findByEmail(email: Email): Promise<User | null>
    save(user: User): Promise<void>
  }

Implementation (Infrastructure):
  class PostgresUserRepository implements UserRepository {
    constructor(private db: Database) {}
    // ORM-specific implementation, mapped to Domain entity
  }

Step 7: Pagination Patterns

-- OFFSET-based (BAD for large datasets — scans skipped rows)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset / cursor-based (GOOD — uses index, constant time)
SELECT * FROM orders WHERE id > 'last-uuid' ORDER BY id LIMIT 20;

// Keyset pagination with composite cursor
async function paginateOrders(cursor?: { id: string; createdAt: Date }, limit = 20) {
  return db.order.findMany({
    where: cursor ? {
      OR: [
        { createdAt: { lt: cursor.createdAt } },
        { createdAt: cursor.createdAt, id: { lt: cursor.id } },
      ],
    } : undefined,
    orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
    take: limit,
  });
}

Step 8: View / Materialized View Patterns

-- Materialized view for expensive aggregations
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
WITH DATA;

-- Refresh on schedule (not on every write)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Query Optimization

EXPLAIN ANALYZE Checklist

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = '123' AND status = 'pending';

-- What to check:
-- 1. Seq Scan vs Index Scan → Seq Scan means missing index
-- 2. Actual rows vs estimated rows → Large difference means stale statistics
-- 3. Execution time → Is it acceptable?
-- 4. Nested Loop vs Hash Join → Nested Loop with many rows means missing index

Common Optimization Patterns

Problem	Pattern	Fix
Seq scan on large table	Missing index	Add index on filtered columns
Slow JOIN	Missing FK index	Index all foreign keys
Slow ORDER BY	Sort-based	Index on sort column
Slow COUNT(*) on big table	Full scan	Use approximate count or index-only scan
Slow pagination	OFFSET-based	Use keyset pagination (WHERE id > ?)
Slow DISTINCT	Sort-based	Add index on distinct column
Slow GROUP BY	Hash/sort	Add index on group + aggregate columns

Query Tuning Workflow

1. Identify slow query (APM, pg_stat_statements, slow query log)
2. Run EXPLAIN ANALYZE
3. Check for Seq Scan → Add index
4. Check for sort (filesort) → Add index on sort column
5. Check row estimate vs actual → Run ANALYZE
6. Check for Nested Loop with large outer → Consider Hash Join or better index
7. Measure improvement, repeat if needed

Performance

Connection Pool Configuration

# PostgreSQL connection pool
pool:
  min: 2          # Keep at least 2 connections
  max: 20         # Max connections (CPU cores × 2 + disk)
  idleTimeout: 10000     # Close idle connections after 10s
  acquireTimeout: 30000  # Wait 30s before timing out
  maxUses: 5000          # Recycle connection after 5000 uses

Batch Operations

// Batch insert — avoid individual INSERT statements
await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  // ... up to 1000 rows
]);

Read Replicas

Write → Primary node
Read  → Replica nodes (load-balanced)

Application patterns:
  - Use read replicas for reporting, analytics, background jobs
  - Be aware of replication lag (typically <100ms in same region)
  - Read-your-writes: after a write, route reads to primary for N seconds
  - Never assume replica is fully in sync with primary

Security

SQL Injection Prevention

// BAD — string interpolation
await db.query(`SELECT * FROM users WHERE email = '${email}'`);  // SQL injection!

// GOOD — parameterized query
await db.query('SELECT * FROM users WHERE email = $1', [email]);
await db.user.findUnique({ where: { email } });  // ORM handles parameterization

Least Privilege

-- Application user should have minimal permissions
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT USAGE ON SEQUENCE orders_id_seq TO app_user;
-- No DELETE, no DROP, no schema modifications

Anti-Patterns

1. SELECT *: Retrieves more columns than needed, breaks index-only scans.
2. No migration down script: Makes rollbacks impossible.
3. Breaking migration: Adding NOT NULL without default to existing table.
4. N+1 in API response: Lazy-loading in serializers causes N+1 queries.
5. Too many indexes: Each INSERT/UPDATE has to update all indexes.
6. No FK indexes: DELETE on parent locks full child table scan.
7. Oversized transactions: Holding transactions during API calls or file I/O.
8. Enum as string: Using VARCHAR with app-level validation instead of native DB enum.
9. Missing unique constraint: Relying on application-level uniqueness only.
10. No read replicas for reporting: Heavy reporting queries impact user-facing transactions.
11. Connection leak: Not releasing connections in all code paths.
12. Lazy loading in serializers: N+1 generated by framework serialization.

Rules

• Never expose raw database entities (ORM models) outside the Infrastructure layer.
• Always use parameterized queries. Never string interpolation in SQL.
• Every SELECT explicitly lists columns. No SELECT *.
• Migrations must be reviewed and tested. Never run untested migrations on production.
• Before optimizing, run EXPLAIN ANALYZE. Assumptions are often wrong.
• Index every foreign key column.
• Every migration has both up and down scripts.
• Soft deletes by default. Hard deletes are a privileged operation.
• Transactions belong in Application layer, never in controllers or repositories.
• Use keyset pagination for large datasets, never OFFSET.
• Test migrations against production-sized data before deployment.
• Always use native DB enums instead of VARCHAR with app-level validation.

References

• references/connection-pooling.md — Connection Pooling
• references/database-fundamentals.md — Database Patterns Fundamentals
• references/database-migration-patterns.md — Database Migration Patterns
• references/database-sharding.md — Database Sharding
• references/database-testing.md — Database Testing
• references/migration-guide.md — Database Migration Guide
• references/migration-strategies.md — Migration Strategies
• references/query-optimization.md — Database Query Optimization Guide
• references/table-design-rules.md — Table Design Rules
• references/transaction-isolation.md — Transaction Isolation Levels

Handoff

No artifact produced. Next skill: backend-auth-patterns — secure the data layer. Carry forward: schema design, repository interfaces, database type, ORM framework.

Implementation Patterns

Observer Pattern for Event Handling

` interface EventObserver { onEvent(event: T): Promise; }

class EventBus { private observers: Set<EventObserver> = new Set(); subscribe(observer: EventObserver): void { this.observers.add(observer); } unsubscribe(observer: EventObserver): void { this.observers.delete(observer); } async emit(event: T): Promise { const results = Array.from(this.observers).map(o => o.onEvent(event)); await Promise.allSettled(results); } } `

Configuration-Driven Approach

config: defaults: timeout: 30s retryCount: 3 overrides: production: timeout: 60s retryCount: 5 development: timeout: 300s retryCount: 1

Production Considerations

Deployment Checklist

• Configuration validated against schema before startup
• Health check endpoints registered and monitored
• Graceful shutdown with draining period (30s timeout)
• Resource limits configured (CPU, memory, file descriptors)
• Log level set appropriate for environment
• Metrics endpoint secured and exposed
• Rate limiting configured per-tier
• TLS certificates valid and auto-renewing
• Database migrations run as separate deployment step
• Feature flags ready for gradual rollout

Monitoring and Alerting

Metric	Threshold	Severity	Action
Error rate	> 1% over 5min	Critical	Page on-call
p99 latency	> 2s over 5min	Warning	Investigate
Throughput drop	> 50% over 1min	Critical	Check upstream
Queue depth	> 1000 over 1min	Warning	Scale consumers
Disk usage	> 85%	Warning	Clean or expand
Memory usage	> 90% heap	Critical	Restart or scale

Anti-Patterns

Anti-Pattern	Symptom	Root Cause	Solution
Premature optimization	Complex code for no measured benefit	Guessing instead of profiling	Measure first, optimize based on data
Copy-paste reuse	Duplicate code across codebase	Lack of abstraction	Extract shared logic into libraries
Gold-plating	Features with no current requirement	Over-engineering	YAGNI — build what's needed now
Magical thinking	Assumptions without validation	Skipping error handling	Handle all failure modes explicitly

Performance Optimization

Caching Strategy

Cache hierarchy: L1 (in-memory local) → L2 (distributed Redis/Memcached) → L3 (CDN/Edge). Cache invalidation: TTL-based (simple, stale), event-based (complex, fresh), write-through (consistent, higher write latency), write-behind (fast writes, eventual consistency).

Resource Pooling

• Database connections: Pool of reusable connections (HikariCP, pgBouncer)
• HTTP connections: Keep-alive + connection pooling for external calls
• Thread pool: Bounded thread pools for async task execution

Profiling Methodology

1. Establish baseline with production traffic profile
2. Profile CPU with sampling profiler (pprof, perf, async-profiler)
3. Profile memory with heap dumps and allocation tracking
4. Profile I/O with strace/perf trace for syscall analysis
5. Profile latency with distributed tracing (OpenTelemetry)
6. Identify bottleneck, formulate hypothesis, implement fix
7. Re-profile to verify improvement, repeat

Security Considerations

Threat Modeling (STRIDE)

• Spoofing: Identity validation, authentication
• Tampering: Integrity checks, digital signatures
• Repudiation: Audit logs, non-repudiation
• Information disclosure: Encryption, access control
• Denial of service: Rate limiting, resource quotas
• Elevation of privilege: Principle of least privilege

Supply Chain Security

• Dependency scanning: Snyk, Dependabot, Trivy
• SBOM generation: CycloneDX or SPDX format
• Signed commits: GPG or SSH commit signing
• Artifact verification: Checksum validation, signature verification

Secrets Management

• Secrets never in code — always in secrets manager (Vault, AWS Secrets Manager)
• Rotation policy: Rotate database credentials every 90 days
• Access audit: Log every secrets access, alert on anomalies
• Encryption at rest and in transit for all secrets
• Principle of least privilege: each service gets only its own secrets

Rules

• Default-deny security posture — allow only explicitly required access.
• All inputs validated, all outputs encoded, all errors handled.
• Defend in depth — multiple layers of security controls.
• Fail securely — errors default to safe behavior.
• Log security-relevant events for audit and investigation.
• Keep dependencies updated — automate vulnerability scanning.
• Design for observability from day one, not as an afterthought.
• Document all architectural decisions with rationale.
• Review code for security, performance, and correctness before merging.