database-design ยท diff

v5.0.0 to v6.0.0

66 added, 5 removed. Audit A to A.

What this update changes in the checks

No check changed: every fixed check gives the same answer before and after this update.

A tripped check is a label, not a verdict: read the matched text and the lines below. This week's updates that newly trip a check.

---
name: database-design
- description: "Use when Database design mastery. Schema design with normalization, denormalization strategies, indexing, migration pipelines, ORM selection (Prisma/Drizzle/SQLAlchemy/EF Core), connection pooling, soft deletes, audit trails, multi-tenancy, and serverless database patterns. Use when designing schemas, choosing databases, planning migrations, or architecting data layers."
- version: 5.0.0
- last-updated: 2026-09-13
+ description: "Use when designing schemas, querying, indexing, optimizing, and securing database design databases and data models."
+ version: 6.0.0
+ last-updated: 2026-09-29
skills:
- supabase-postgres-best-practices
- sql-pro
- db-latency-auditor
tools: Read, Grep, Glob, Bash, Edit, Write
scripts-binding:
- .agent/scripts/lint_runner.js
- .agent/scripts/verify_all.js
---
# Database Design โ€” Schema & Architecture Mastery
- ---
+ ## Mandatory Pre-Flight Context Inspection
+ Before reading, generating, or refactoring code in the `database-design` domain, inspect these 5 critical parameters:
+ 1. **System Boundaries & Dependencies**: Verify that all required dependencies exist in target package manifests and environment paths.
+ 2. **Runtime Context & Platform Invariants**: Confirm target platform constraints (Node.js, Browser, Mobile OS, Edge runtime) before applying APIs.
+ 3. **Execution Guardrails**: Identify potential side-effects, state mutations, and unhandled asynchronous exceptions.
+ 4. **Validation & Type Contracts**: Validate input data schemas and strict type constraints across all module interfaces.
+ 5. **Observability & Proof of Execution**: Ensure execution produces tangible verification signals (terminal output, tests, metrics).
- ## ๐Ÿ› ๏ธ Technical Architecture & Reference Recipes
+ ## Activation Boundaries
+ - **Activate when:** Use when designing schemas, querying, indexing, optimizing, and securing database design databases and data models.
+ - **DO NOT activate when:** The task falls outside the `database-design` domain or is managed by a different dedicated specialist agent.
+
+
+ ## ๐Ÿ” Multi-Pass Execution Protocol
+
+ | Pass | Phase | Core Action | Adaptive Depth |
+ |:---|:---|:---|:---|
+ | **Pass 1** | **Understand** | Deconstruct the user's explicit objective, implicit requirements, and platform constraints. | Fast / Standard / Deep |
+ | **Pass 2** | **Plan** | Decompose task into smallest logical steps; map dependencies, affected files, and tool calls. | Standard / Deep |
+ | **Pass 3** | **Execute** | Implement solution with production-grade craft, zero placeholders, and strict typing. | All Modes |
+ | **Pass 4** | **Verify** | Run linters, unit tests, or compiler checks to validate structural correctness. | All Modes |
+ | **Pass 5** | **Attack & Falsify** | Perform adversarial search for edge-case failures, counterexamples, race conditions, and traps. | Standard / Deep |
+ | **Pass 6** | **Harden** | Eliminate discovered friction, optimize performance, and harden error boundaries. | Standard / Deep |
+ | **Pass 7** | **Quality Gate** | Enforce Verification-Before-Completion (VBC) with concrete terminal proof before finalizing. | All Modes |
+
+
---
+ ## ๐Ÿ› ๏ธ Technical Architecture & Reference Recipes
+
## 2026 Database Performance & Schema Invariants
1. **Time-Ordered UUID v7 (RFC 9562)**: When UUIDs are required across distributed systems, use UUID v7 so records append sequentially to B-tree indexes, avoiding fragmentation.
2. **Partial Indexing on Soft Deletes**:
```sql
CREATE INDEX idx_users_active_email ON users (email) WHERE deleted_at IS NULL;
```
3. **Covering Indexes**: Use `INCLUDE (col_a, col_b)` to allow index-only scans without table heap lookups on read-heavy query patterns.
4. **Connection Pooling in Serverless**: Always route serverless connections through Supavisor, PgBouncer, or Neon connection poolers with transaction-mode pooling.
## Hallucination Traps (Read First)
- โŒ `TIMESTAMP` without timezone โ†’ โœ… Always `TIMESTAMPTZ`
- โŒ UUID v4 as primary key โ†’ โœ… UUID v7 (time-ordered) or `BIGINT GENERATED ALWAYS AS IDENTITY`
- โŒ Omitting indexes on foreign keys โ†’ โœ… Postgres does NOT auto-index FKs; always create explicit indexes
- โŒ Adding `NOT NULL` column without default directly on large tables โ†’ โœ… Add nullable first, backfill in batches, then set `NOT NULL`
- โŒ Soft delete without partial index โ†’ โœ… Always index `WHERE deleted_at IS NULL`
- โŒ Direct DB connection inside serverless functions โ†’ โœ… Use pooled connection string (PgBouncer/Supavisor)
---
## Database Selection
```
Relational / Complex queries โ†’ PostgreSQL (primary choice)
Serverless PG โ†’ Neon, Supabase
Edge / Ultra-low latency โ†’ Turso (SQLite @ edge)
Simple / Embedded โ†’ SQLite
Global distribution (MySQL) โ†’ PlanetScale (no FK support)
Key-value / Cache โ†’ Redis / Valkey / Upstash
Document store โ†’ MongoDB / Firestore
Full-text search โ†’ PostgreSQL tsvector (built-in) or Meilisearch / Typesense
Time-series โ†’ TimescaleDB / ClickHouse
Vector (AI embeddings) โ†’ pgvector (PostgreSQL ext) / Pinecone / Weaviate
```
---
## Standard Table Template
```sql
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-- OR: id UUID DEFAULT gen_random_uuid() PRIMARY KEY (use v7 for perf)
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'user', 'moderator')),
is_active BOOLEAN NOT NULL DEFAULT true,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ -- soft delete
);
-- Required: auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();
-- Required indexes
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL; -- partial index for soft delete
CREATE INDEX idx_users_created_at ON users (created_at DESC);
```
---
## Schema Patterns
### Relationships
```sql
-- One-to-Many: FK on the "many" side + INDEX
CREATE TABLE posts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
...
);
CREATE INDEX idx_posts_author_id ON posts (author_id); -- REQUIRED in Postgres
-- Many-to-Many: junction table with composite PK
CREATE TABLE post_tags (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX idx_post_tags_tag_id ON post_tags (tag_id); -- index the non-PK side
```
### Multi-Tenancy
```sql
-- Pattern 1: tenant_id column (simplest โ€” enforce via RLS)
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::bigint);
-- Pattern 2: Schema per tenant (better isolation, harder migrations)
-- CREATE SCHEMA tenant_acme;
-- Pattern 3: DB per tenant โ€” only for compliance/regulatory needs
```
---
## ORM Selection
| ORM | Best For | Trade-offs |
| ------------------ | --------------------------------------- | -------------------------- |
| **Drizzle** | Edge, TypeScript, bundle-size sensitive | Newer, fewer examples |
| **Prisma** | DX, schema management, Prisma Studio | Heavy, NOT edge-compatible |
| **Kysely** | Type-safe SQL builder, full control | Manual migrations |
| **Raw SQL** | Complex queries, performance-critical | Manual type safety |
| **SQLAlchemy 2.0** | Python async ecosystem | Python only |
```typescript
// Drizzle โ€” SQL-like, edge-compatible
const result = await db
.select({ id: users.id, name: users.name })
.from(users)
.where(and(eq(users.role, 'admin'), eq(users.isActive, true)))
.orderBy(desc(users.createdAt))
.limit(20);
// Prisma โ€” โŒ TRAP: can't express complex joins natively โ†’ use prisma.$queryRaw<Type>
const user = await prisma.user.findUnique({ where: { email }, include: { posts: { take: 10 } } });
```
---
## Migrations (Zero-Downtime Strategy)
```sql
-- Safe column add on a large production table:
-- Step 1: Add nullable (no lock)
ALTER TABLE users ADD COLUMN phone TEXT;
-- Step 2: Backfill in batches (non-blocking)
UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
-- Step 3: Add constraint AFTER all code deploys write the column
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
```
**Migration Rules:**
- Never modify a migration already applied to production โ€” create a new one
- Remove column in 2 deploys: first remove all code references, then `DROP COLUMN`
- `CREATE INDEX CONCURRENTLY` to avoid table locks on existing data
- Test migrations against a copy of production data before running live
---
## Indexing Reference
| Index Type | Use For |
| ------------------ | ---------------------------------------------------- |
| **B-tree** | General purpose โ€” equality & range queries (default) |
| **Hash** | Equality-only lookups (faster than B-tree for =) |
| **GIN** | JSONB, arrays, full-text (`tsvector`) |
| **GiST** | Geometric, range types |
| **HNSW / IVFFlat** | Vector similarity (pgvector) |
**Composite index column order:** equality columns first โ†’ range columns last โ†’ most selective first
---
## Audit Trail
```sql
CREATE TABLE audit_log (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name TEXT NOT NULL, record_id BIGINT NOT NULL,
action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
old_data JSONB, new_data JSONB,
changed_by BIGINT REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log USING brin (changed_at); -- BRIN for time-ordered append-only tables
```
---
## Connection Pooling
```
Without pooling: 100 concurrent requests โ†’ 100 DB connections โ†’ overwhelms DB
With pooling: 100 concurrent requests โ†’ 10โ€“20 reused connections
Sizing formula: max_connections = (cpu_cores ร— 2) + disk_spindles (typically 25โ€“50)
Poolers:
PgBouncer โ†’ External, most common for self-hosted Postgres
Prisma Accelerate โ†’ Managed, for Prisma projects
Supabase Supavisor โ†’ Managed, for Supabase projects
```
+
+ ## ๐Ÿšจ Edge-Case & Failure Mode Matrix
+
+ | Scenario | Risk | Production Mitigation |
+ |:---|:---|:---|
+ | **Empty or Null Inputs** | Unhandled exception or unexpected rendering collapse | Enforce fallback guards, optional chaining, and explicit empty state handlers |
+ | **Network Timeout / Latency** | Hanging operations or duplicate side-effects | Implement bounded abort controllers, exponential backoff, and idempotency keys |
+ | **Concurrency / Race Conditions** | Stale state overwrite or inconsistent data mutations | Use atomic transactions, mutex locking, or cancel-on-resubmit controls |
+ | **Invalid Schema / Malformed Payload** | Downstream runtime errors or security injection | Validate boundary payloads with Zod/Pydantic schemas prior to execution |
+ | **Resource / Memory Saturation** | OOM errors, frame drops, or memory leaks | Clean up listeners, cancel active timers, and enforce pagination/virtualization |
+
+
+ ## ๐Ÿ›๏ธ Tribunal Verification & Guardrails
+
+ **Active Reviewers:** `database-architect` ยท `sql-pro` ยท `security-auditor` ยท `schema-validator`
+ **Slash Command:** `/review` or `/tribunal-full`
+
+ ### ๐Ÿ”ฌ Evidence Standard (Tri-State Verification)
+ Every finding, audit statement, or completion claim must classify its factual certainty:
+ - **`[OBSERVED]`**: Directly confirmed in the codebase or verified via executed terminal command.
+ - **`[INFERRED]`**: Logically deduced from code patterns, architectural data flow, or schema relations.
+ - **`[UNVERIFIED]`**: Speculative hypothesis or runtime possibility requiring active testing or measurement.
+
+ ### โœ… Pre-Flight Self-Audit Checklist
+ ```
+ โœ… Are all queries parameterized against SQL injection vulnerabilities?
+ โœ… Are composite indexes ordered by Equality, Sort, then Range (ESR)?
+ โœ… Are multi-table writes wrapped in atomic transactions with rollback handlers?
+ โœ… Are schema migrations backwards-compatible (expand-and-contract pattern)?
+ โœ… Did I verify table and column names against active schema definitions?
+ ```
+
+ ### ๐Ÿ›‘ Verification-Before-Completion (VBC) Protocol
+ **CRITICAL:** You must follow a strict "evidence-based closeout" state machine.
+ - โŒ **Forbidden:** Declaring a task complete because the output "looks correct."
+ - โœ… **Required:** You are explicitly forbidden from finalizing any task without providing **concrete evidence** (terminal output, passing test suites, compiler success, or equivalent operational proof) that your output works as intended.