The most complex specialist. 22 specialty-teams organized across schema design, performance, operations, and sync โ plus 3 consulting-teams as cross-cutting verification gates.
| Team | Description | Cookbook Source |
|---|
| SCHEMA DESIGN (9) |
| naming-conventions | snake_case identifiers, PK/FK naming (table_name_id not bare id), reserved word avoidance, index/constraint naming | guidelines/database-design/naming-conventions.md |
| data-types | SQLite type affinity, STRICT tables, the STRING gotcha, cross-database type mapping (SQLite โ PostgreSQL) | guidelines/database-design/data-types.md |
| primary-keys | INTEGER PRIMARY KEY, AUTOINCREMENT tradeoffs, UUID strategies (v4 vs v7, TEXT vs BLOB), WITHOUT ROWID | guidelines/database-design/primary-keys.md |
| foreign-keys | PRAGMA foreign_keys = ON, ON DELETE/UPDATE actions, deferred constraints, FK column indexing | guidelines/database-design/foreign-keys.md |
| constraints-and-validation | CHECK constraints, enum-like constraints, boolean enforcement, range/pattern validation, NULL truthiness | guidelines/database-design/constraints-and-validation.md |
| relationships | One-to-many, many-to-many join tables, polymorphic FK patterns, self-referential, tree hierarchies | guidelines/database-design/relationships.md |
| normalization-and-denormalization | 3NF starting point, selective denormalization for measured hotspots, sync impact of denormalization | guidelines/database-design/normalization-and-denormalization.md |
| json-columns | JSON in TEXT columns, extraction operators, generated columns for B-tree indexing, when JSON is a schema smell | guidelines/database-design/json-columns.md |
| schema-evolution | Migration strategies (user_version pragma), ALTER TABLE limitations, backwards-compatible and sync-compatible migrations | guidelines/database-design/schema-evolution.md |
| PERFORMANCE (4) |
| indexing | B-tree fundamentals, partial/expression indexes, composite ordering, covering indexes, EXPLAIN QUERY PLAN | guidelines/database-design/indexing.md |
| query-optimization | SQLite query planner, rewriting slow queries, avoiding full table scans, JSON query performance, CTEs vs subqueries | guidelines/database-design/query-optimization.md |
| transactions-and-concurrency | WAL mode, BEGIN IMMEDIATE vs DEFERRED, single writer + multiple readers, busy_timeout, PRAGMA tuning | guidelines/database-design/transactions-and-concurrency.md |
| access-pattern-analysis | Query pattern analysis, read/write tradeoffs, WHERE/JOIN/ORDER BY design, batch sizing for sync | guidelines/database-design/access-pattern-analysis.md |
| OPERATIONS (2) |
| backup-and-recovery | SQLite backup API, Litestream WAL streaming, corruption detection, VACUUM strategy, tombstone purging | guidelines/database-design/backup-and-recovery.md |
| testing | In-memory vs file-based tests, migration testing, sync logic testing, conflict resolution testing | guidelines/database-design/testing.md |
| SYNC (7) |
| sync-schema-design | Dual schema (SQLite + PostgreSQL), UUID PKs for offline creation, soft deletes, dirty tracking, sync metadata | guidelines/database-design/sync-schema-design.md |
| conflict-resolution | Last-Write-Wins, field-level merge, CRDTs, conflict queues, choosing strategy per data type | guidelines/database-design/conflict-resolution.md |
| sync-protocol | Push/pull/bidirectional sync, incremental delta sync, change tracking, UPSERT idempotency, outbox pattern | guidelines/database-design/sync-protocol.md |
| clock-systems | Physical clocks, Lamport timestamps, vector clocks, Hybrid Logical Clocks, server-assigned monotonic versions | guidelines/database-design/clock-systems.md |
| offline-first-architecture | WAL as offline foundation, operation queue pattern, optimistic UI, rollback on rejection, connectivity-aware sync | guidelines/database-design/offline-first-architecture.md |
| sync-engine-design | Orchestrator layers, Syncable interface, sync cycle, scheduling, snapshot rebuilding, circuit breakers | guidelines/database-design/sync-engine-design.md |
| sync-tooling | SQLite Session Extension, cr-sqlite, Litestream, ElectricSQL, PowerSync, Turso/libSQL, sqlite-sync evaluation | guidelines/database-design/sync-tooling.md |
| CONSULTING TEAMS (3) |
| cross-database-compatibility | Verifies every schema decision works on both SQLite and PostgreSQL โ type mappings, PK strategies, constraints | consulting gate |
| sync-impact | Catches non-sync team decisions that would break sync, create merge conflicts, or hinder offline operation | consulting gate |
| access-pattern-coherence | Ensures structural decisions serve actual query patterns โ catches schemas that look correct but perform poorly | consulting gate |