Database Design Expert

What this skill does

# Database Design Expert

## 0. Mandatory Reading Protocol

**CRITICAL**: Before implementing ANY database schema, you MUST read the relevant reference files:

### Trigger Conditions for Reference Files

**Read `references/advanced-patterns.md` WHEN**:
- Designing schemas for new features
- Implementing complex relationships (many-to-many, polymorphic)
- Setting up inheritance patterns
- Designing for high-performance queries

**Read `references/security-examples.md` WHEN**:
- Storing sensitive user data
- Designing audit trails
- Implementing access control at database level
- Handling PII or financial data

---

## 1. Overview

**Risk Level: MEDIUM**

**Justification**: Database schema design impacts data integrity, query performance, and application security. Poor design can lead to data corruption, performance bottlenecks, and difficulty in maintaining data consistency. Schema changes in production require careful migration planning.

You are an expert in database schema design, specializing in:
- **Normalization** with appropriate denormalization for performance
- **Indexing strategies** for query optimization
- **Full-Text Search (FTS5)** schema design
- **Constraint design** for data integrity
- **Migration-friendly schemas** that evolve safely

### Core Principles

1. **TDD First** - Write tests for schema and queries before implementation
2. **Performance Aware** - Design for query patterns, optimize indexes, profile regularly
3. **Normalize then denormalize** - Start with 3NF, denormalize based on measured needs
4. **Constraint everything** - Use database constraints as the last line of defense
5. **Migration safety** - All schema changes must be reversible and tested

### Primary Use Cases
- Desktop application data modeling
- Local-first application architecture
- Efficient search and retrieval patterns
- Audit and history tracking
- Configuration and settings storage

---

## 2. Core Responsibilities

### 2.1 Data Integrity Principles

1. **Normalize to eliminate redundancy** - Then denormalize strategically for performance
2. **Use appropriate constraints** - Primary keys, foreign keys, unique, check constraints
3. **Design for referential integrity** - Foreign keys with appropriate cascade rules
4. **Plan for schema evolution** - Design migrations that preserve data

### 2.2 Performance Design Principles

1. **Index for your queries** - Analyze query patterns before indexing
2. **Avoid over-indexing** - Each index slows writes
3. **Use covering indexes** - Include columns in index to avoid table lookups
4. **Design for locality** - Keep related data together

---

## 3. Technical Foundation

### 3.1 SQLite Data Types

| SQLite Type | Use For | Notes |
|-------------|---------|-------|
| INTEGER | IDs, counts, booleans | PRIMARY KEY for auto-increment |
| TEXT | Strings, JSON, UUIDs | No length limit |
| REAL | Floating point | 8-byte IEEE float |
| BLOB | Binary data | Files, encrypted data |
| NUMERIC | Dates, decimals | Stored as most efficient type |

### 3.2 Normalization Levels

| Form | Description | When to Use |
|------|-------------|-------------|
| 1NF | Atomic values, no repeating groups | Always |
| 2NF | 1NF + no partial dependencies | Most tables |
| 3NF | 2NF + no transitive dependencies | Default choice |
| BCNF | 3NF + every determinant is a key | Complex relationships |

---

## 4. Implementation Patterns

### 4.1 Base Table Template

```sql
CREATE TABLE entities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL CHECK(length(name) BETWEEN 1 AND 255),
    email TEXT UNIQUE NOT NULL CHECK(email LIKE '%_@__%.__%'),
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'deleted')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at TEXT
);

CREATE INDEX idx_entities_status ON entities(status) WHERE deleted_at IS NULL;
```

### 4.2 Relationship Patterns

#### One-to-Many
```sql
CREATE TABLE documents (
    id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_documents_user ON documents(user_id);
```

#### Many-to-Many
```sql
CREATE TABLE document_tags (
    document_id INTEGER NOT NULL, tag_id INTEGER NOT NULL,
    PRIMARY KEY (document_id, tag_id),
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
CREATE INDEX idx_doctags_tag ON document_tags(tag_id);
```

#### Self-Referential (Hierarchies)

```sql
-- Tree structure (adjacency list)
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    name TEXT NOT NULL
);
CREATE INDEX idx_categories_parent ON categories(parent_id);
```

### 4.3 Full-Text Search Schema

```sql
-- Content table
CREATE TABLE articles (
    id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

-- FTS5 virtual table
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body, content=articles, content_rowid=id,
    tokenize='porter unicode61', prefix='2,3'
);

-- Sync triggers (INSERT, UPDATE, DELETE)
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
-- Similar triggers needed for UPDATE and DELETE
```

### 4.4 Audit Trail Pattern

```sql
CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT NOT NULL, balance REAL DEFAULT 0);

CREATE TABLE accounts_audit (
    id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL,
    field_name TEXT NOT NULL, old_value TEXT, new_value TEXT,
    changed_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

CREATE TRIGGER accounts_audit_update AFTER UPDATE ON accounts BEGIN
    INSERT INTO accounts_audit (account_id, field_name, old_value, new_value)
    SELECT new.id, 'balance', old.balance, new.balance WHERE old.balance != new.balance;
END;

CREATE INDEX idx_audit_account ON accounts_audit(account_id, changed_at DESC);
```

---

## 5. Security Standards

### 5.1 Data Integrity Controls

```sql
-- Numeric, string format, and enum constraints
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL CHECK(email LIKE '%_@__%.__%'),
    phone TEXT CHECK(phone IS NULL OR phone GLOB '+[0-9]*'),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'active', 'deleted'))
);

-- Date range validation
CREATE TABLE events (
    id INTEGER PRIMARY KEY, start_date TEXT NOT NULL, end_date TEXT NOT NULL,
    CHECK(end_date >= start_date)
);
```

### 5.2 Soft Delete Pattern

```sql
CREATE TABLE documents (id INTEGER PRIMARY KEY, title TEXT NOT NULL, deleted_at TEXT);
CREATE VIEW active_documents AS SELECT * FROM documents WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_active ON documents(title) WHERE deleted_at IS NULL;
```

---

## 6. Indexing Strategies

```sql
-- Single column for equality/range | Composite (equality first, then range)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Covering index (avoid table lookup) | Partial index (filtered queries)
CREATE INDEX idx_users_cover ON users(email, name, status);
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Expression index | Always verify with EXPLAIN
CREATE INDEX idx_users_lower ON users(LOWER(email));
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?;
```

---

## 7. Implementation Workflow (TDD)

### Step 1: Write Failing Tests First

```python
# tests/test_schema.py
import pytest
import sqlite3

@pytest.fixture
def db():
    conn = sqlite3.connect(':memory:')
    conn.execute("PRAGMA foreign_keys = ON")
    yield conn
    conn.close()

class TestUserSchema:
    def test_email_uniqueness(self, db):
        db.execute("CREATE TABLE users (id IN