Skip to main content
skill CX Codex

database-patterns · majiayu000 — Claude Code skill: what it does, how to install

database-patterns guides AI agents on pairing PostgreSQL with Redis: schema types, indexing, JSONB usage, caching strategies, and stampede prevention.

MIT 162 installs ★ 300 updated 2026-10-23

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add majiayu000/spellbook --skill database-patterns -a claude-code
Open on GitHub

What database-patterns does

This skill enforces a clear separation: PostgreSQL acts as the primary relational store for transactions, while Redis handles volatile caching, session states, and counters. It directs models to pick UUID or BIGSERIAL over standard INT primary keys, choose TIMESTAMPTZ over bare timestamps, and represent monetary values with NUMERIC(19,4).

For performance, it outlines indexing rules: standard B-Tree indexes, composite keys respecting the leftmost prefix rule, partial indexes, and GIN indexes for JSONB containment. On the Redis side, it establishes structured key names separated by colons and defines baseline TTL tiers, from 30-second locks to 24-hour sessions.

Schema evolution strictly avoids backward compatibility shims: existing records must be migrated rather than keeping legacy columns alive.

What it helps with

  • design a PostgreSQL schema with status check constraints and an updated_at trigger
  • choose the right Redis data structure and TTL for sessions or queues
  • configure GIN and expression indexes for JSONB search queries
  • implement cache-aside logic with Redis mutex locks against stampedes

Example prompts for the agent

  • Design a PostgreSQL products table with dynamic attributes in JSONB and an expression index
  • Write a TypeScript cache-aside helper with Redis to fetch and invalidate user data
  • Give me an EXPLAIN ANALYZE checklist to fix slow queries on large joined tables

How to install database-patterns

1Skills CLI (Vercel)

npx skills add majiayu000/spellbook --skill database-patterns -a claude-code

2Manually: put the files in place

~/.claude/skills/database-patterns/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/database-patterns/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about database-patterns

How to install database-patterns in Claude Code?

Run npx skills add majiayu000/spellbook --skill database-patterns -a claude-code. The skill file lands in ~/.claude/skills/database-patterns/SKILL.md.

What does database-patterns do?

It guides coding assistants with architectural conventions for PostgreSQL and Redis, covering data types, index design, and caching workflows.

What is the rule on backward compatibility in this skill?

The skill enforces zero backward compatibility: legacy schemas are migrated forward directly instead of carrying obsolete fields or structures.

Source text

SKILL.md · MIT
---
name: database-patterns
description: Use when designing PostgreSQL + Redis data models, indexes, caching strategies, JSONB usage, tiered storage, or cache consistency contracts.
---
# Database Patterns

## Core Principles

- **PostgreSQL Primary** — Relational data, transactions, complex queries
- **Redis Secondary** — Caching, sessions, real-time data
- **Index-First Design** — Design queries before indexes
- **JSONB Sparingly** — Structured data prefers columns
- **Cache-Aside Default** — Read-through, write-around
- **Tiered Storage** — Hot/Warm/Cold data separation
- **No backwards compatibility** — Migrate data, don't keep legacy schemas

---

## PostgreSQL

### Data Type Selection

| Use Case | Type | Avoid |
|----------|------|-------|
| Primary Key | `UUID` / `BIGSERIAL` | `INT` (range limits) |
| Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (no timezone) |
| Money | `NUMERIC(19,4)` | `FLOAT` (precision loss) |
| Status | `TEXT` + CHECK | `INT` (unreadable) |
| Semi-structured | `JSONB` | `JSON` (no indexing) |
| Full-text | `TSVECTOR` | `LIKE '%..%'` |

### Schema Design

```sql
-- Use UUID for distributed-friendly IDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'suspended')),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Updated timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();
```

### Indexing Strategy

```sql
-- B-Tree: Equality, range, sorting (default)
CREATE INDEX idx_users_email ON users(email);

-- Composite: Leftmost prefix rule
-- Supports: (user_id), (user_id, created_at)
-- Does NOT support: (created_at) alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial: Reduce index size
CREATE INDEX idx_active_users ON users(email)
  WHERE status = 'active';

-- GIN for JSONB: Containment queries
CREATE INDEX idx_metadata ON users USING GIN (metadata jsonb_path_ops);

-- Expression: Specific JSONB field
CREATE INDEX idx_user_role ON users ((metadata->>'role'));

-- Full-text search
CREATE INDEX idx_search ON products USING GIN (to_tsvector('english', name || ' ' || description));
```

### JSONB Usage

```sql
-- Good: Dynamic attributes, rarely queried fields
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(19,4) NOT NULL,
  category TEXT NOT NULL,           -- Extracted: frequently queried
  attributes JSONB DEFAULT '{}'     -- Dynamic: color, size, specs
);

-- Query with containment
SELECT * FROM products
WHERE category = 'electronics'              -- B-Tree index
  AND attributes @> '{"brand": "Apple"}';   -- GIN index

-- Query specific field
SELECT * FROM products
WHERE attributes->>'color' = 'black';       -- Expression index

-- Update JSONB field
UPDATE products
SET attributes = attributes || '{"featured": true}'
WHERE id = '...';
```

### Query Optimization

```sql
-- Always use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY u.created_at DESC
LIMIT 20;

-- Watch for:
-- ❌ Seq Scan on large tables → Add index
-- ❌ Sort → Use index for ordering
-- ❌ Nested Loop with many rows → Consider JOIN order
-- ❌ Hash Join on huge tables → Add indexes
```

### Connection Pooling

```typescript
// PgBouncer or built-in pool
import { Pool } from 'pg';

const pool = new Pool({
  max: 20,                      // Max connections
  idleTimeoutMillis: 30000,     // Close idle connections
  connectionTimeoutMillis: 2000, // Fail fast
});

// Connection count formula:
// connections = (cores * 2) + effective_spindle_count
// Usually 10-30 is enough
```

---

## Redis

### Data Structure Selection

| Use Case | Structure | Example |
|----------|-----------|---------|
| Cache objects | String | `user:123` → JSON |
| Counters | String + INCR | `views:article:456` |
| Sessions | Hash | `session:abc` → {userId, ...} |
| Leaderboards | Sorted Set | `scores` → {userId: score} |
| Queues | List/Stream | `tasks` → LPUSH/RPOP |
| Unique sets | Set | `online_users` |
| Real-time | Pub/Sub/Stream | Notifications |

### Key Naming

```
# Format: <entity>:<id>:<attribute>
user:123:profile
user:123:settings
order:456:items
session:abc123

# Use colons for hierarchy
# Enables pattern matching with SCAN
SCAN 0 MATCH "user:*:profile" COUNT 100
```

### TTL Strategy

```typescript
const TTL = {
  SESSION: 24 * 60 * 60,      // 24 hours
  CACHE: 15 * 60,             // 15 minutes
  RATE_LIMIT: 60,             // 1 minute
  LOCK: 30,                   // 30 seconds
};

// Set with TTL
await redis.set(`cache:user:${id}`, JSON.stringify(user), 'EX', TTL.CACHE);

// Check TTL
const remaining = await redis.ttl(`cache:user:${id}`);
```

---

## Caching Patterns

### Cache-Aside (Lazy Loading)

```typescript
async function getUser(id: string): Promise<User> {
  const cacheKey = `user:${id}`;

  // 1. Check cache
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2. Cache miss → Query database
  const user = await db.user.findUnique({ where: { id } });
  if (!user) {
    throw new NotFoundError('User not found');
  }

  // 3. Populate cache
  await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);

  return user;
}
```

### Write-Through

```typescript
async function updateUser(id: string, data: UpdateInput): Promise<User> {
  // 1. Update database
  const user = await db.user.update({
    where: { id },
    data,
  });

  // 2. Update cache immediately
  await redis.set(`user:${id}`, JSON.stringify(user), 'EX', 900);

  return user;
}
```

### Cache Invalidation

```typescript
async function deleteUser(id: string): Promise<void> {
  // 1. Delete from database
  await db.user.delete({ where: { id } });

  // 2. Invalidate cache
  await redis.del(`user:${id}`);

  // 3. Invalidate related caches
  const keys = await redis.keys(`user:${id}:*`);
  if (keys.length > 0) {
    await redis.del(...keys);
  }
}
```

### Cache Stampede Prevention

```typescript
async function getUserWithLock(id: string): Promise<User> {
  const cacheKey = `user:${id}`;
  const lockKey = `lock:user:${id}`;

  // Check cache
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // Try to acquire lock
  const acquired = await redis.set(lockKey, '1', 'EX', 10, 'NX');

  if (!acquired) {
    // Another process is loading, wait and retry
    await sleep(100);
    return getUserWithLock(id);
  }

  try {
    // Double-check cache (another process might have populated it)
    const rechecked = await redis.get(cacheKey);
    if (rechecked) {
      return JSON.parse(rechecked);
    }

    // Load from database
    const user = await db.user.findUnique({ where: { id } });
    await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);
    return user;
  } finally {
    await redis.del(lockKey);
  }
}
```

### Cache Penetration Prevention

```typescript
async function getUserSafe(id: string): Promise<User | null> {
  const cacheKey = `user:${id}`;

  const cached = await redis.get(cacheKey);

  // Check for cached null
  if (cached === 'NULL') {
    return null;
  }

  if (cached) {
    return JSON.parse(cached);
  }

  const user = await db.user.findUnique({ where: { id } });

  if (!user) {
    // Cache null with short TTL
    await redis.set(cacheKey, 'NULL', 'EX', 60);
    return null;
  }

  await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);
  return user;
}
```

---

## Tiered Storage

```
┌─────────────────────────────────────────────────┐
│                   Application                    │
└─────────────────────────────────────────────────┘
                        │
        ┌───────────────┼───────────────┐
        ▼               ▼               ▼
   ┌─────────┐    ┌─────────┐    ┌─────────┐
   │  Redis  │    │ Postgres │    │ Archive │
   │  (Hot)  │    │  (Warm)  │    │  (Cold) │
   └─────────┘    └─────────┘    └─────────┘

   < 1ms          ~10ms           ~100ms+
   Active data    Recent data     Historical
   Memory         SSD             Object storage
```

### Partitioning for Cold Data

```sql
-- Partition by date range
CREATE TABLE orders (
  id UUID NOT NULL,
  user_id UUID NOT NULL,
  total NUMERIC(19,4) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Archive old data
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- Move old data to archive
WITH moved AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
```

---

## Transactions

### ACID Compliance

```typescript
// Use transactions for multi-table operations
async function transferFunds(fromId: string, toId: string, amount: number) {
  await db.$transaction(async (tx) => {
    // Deduct from source
    const from = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    if (from.balance < 0) {
      throw new Error('Insufficient funds');
    }

    // Add to destination
    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });
  });
}
```

### Optimistic Locking

```sql
-- Add version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Update with version check
UPDATE products
SET
  stock = stock - 1,
  version = version + 1
WHERE id = $1 AND version = $2
RETURNING *;

-- If no rows returned, concurrent modification occurred
```

---

## Checklist

```markdown
## Schema
- [ ] UUID or BIGSERIAL for primary keys
- [ ] TIMESTAMPTZ for all timestamps
- [ ] NUMERIC for money, not FLOAT
- [ ] CHECK constraints for enums
- [ ] Foreign keys with ON DELETE

## Indexing
- [ ] Index for each WHERE clause pattern
- [ ] Composite indexes match query order
- [ ] GIN index for JSONB containment
- [ ] EXPLAIN ANALYZE for slow queries

## Caching
- [ ] Cache-aside as default pattern
- [ ] TTL on all cached data
- [ ] Cache invalidation on writes
- [ ] Stampede/penetration protection

## Operations
- [ ] Connection pooling configured
- [ ] Slow query logging enabled
- [ ] Backup and recovery tested
- [ ] Partition strategy for growth
```

---

## See Also

- [reference/postgresql.md](reference/postgresql.md) — PostgreSQL deep dive
- [reference/redis.md](reference/redis.md) — Redis patterns
- [reference/caching.md](reference/caching.md) — Caching strategies

© majiayu000 · MIT · majiayu000/spellbook

Similar skills

All skills for Data and databases →

database-patterns by other authors comes first.

database-patterns OrchestKit The database-patterns skill guides schema design, Alembic migrations, and database versioning. It fits work on normalization, migration branches, and schema drift. CX Codex +1 238installs database-patterns c0x12c database-patterns hands your agent a fixed set of database rules: column types, UUID keys, soft deletes and migration templates. Useful when it writes tables or repositories. CX Codex +1 ★ 111repo stars database-expert travisjneuman The database-expert Claude Code skill covers database schema design, query tuning, and administration. It activates for work with PostgreSQL, MongoDB, and Redis. CX Codex +1 255installs database-schema-design aj-geddes database-schema-design designs relational database schemas with normalization, constraints, and table relationships. Built for modeling data in PostgreSQL and MySQL. CX Codex +1 599installs database-schema-designer OneWave-AI database-schema-designer designs relational and document database schemas based on real app queries. Generates tables, keys, constraints, ERDs, and reversible migrations. CX Codex +1 432installs discover-database rand discover-database routes agent workflows to tailored guides for SQL, NoSQL, connection pooling, and caching patterns. CX Codex +1 125installs database-management-patterns manutej database-management-patterns — a reference skill for PostgreSQL and MongoDB work: schema design, indexing, transactions, replication and sharding. CX Codex +1 467installs database-design-patterns curiositech database-design-patterns guides relational schema design, 3NF normalization, index selection, and zero-downtime migrations using the expand-contract pattern. CX Codex +1 152installs

From the same set13

The whole set →
technical-spec technical-spec enforces strict structures for technical design docs, RFCs, and ADRs with mandatory alternatives, diagrams, and risk matrices. CX Codex +1 245installs product-ux-expert product-ux-expert — a skill for reviewing interfaces against Nielsen's heuristics, building user journey maps, and auditing accessibility against WCAG 2.2 AA. CX Codex +1 212installs prd-master prd-master is a skill for writing product specifications, user stories, and acceptance criteria. It structures requirements with RICE and MoSCoW and sets measurable goals. CX Codex +1 166installs observability-sre The observability-sre skill guides monitoring, SLO design, tracing, logging, and incident response. Use it when a project needs Prometheus, Grafana, or OpenTelemetry practices. CX Codex +1 165installs clash-doctor clash-doctor diagnoses Clash Verge and mihomo connectivity, routes AI traffic, and manages TUN bypass rules directly from your agent. CX Codex +1 142installs trip-planner trip-planner builds detailed day-by-day travel itineraries with navigation addresses, transit times, and evening prep checklists. Useful when organizing holidays or road trips. CX Codex +1 134installs auth-security auth-security is a Claude Code skill that sets OAuth 2.1 and RFC 9700 rules for secure API auth, including PKCE, refresh token rotation, and strict JWT verification. CX Codex +1 117installs devops-excellence devops-excellence enforces DevOps best practices: eliminates root users in containers, strips static secrets, and configures OIDC in pipelines. CX Codex +1 114installs

Project sponsors

Companies that keep this analytics open