DB Schema
ada-api uses SQLite via Drizzle ORM in WAL mode. 8 tables, idempotent migrations auto-applied on startup.
SQLite Configuration
PRAGMA journal_mode = WAL; -- Concurrent reads/writes
PRAGMA synchronous = NORMAL; -- Good perf/durability trade-off
PRAGMA foreign_keys = ON; -- FK constraints active
PRAGMA temp_store = MEMORY; -- Temp tables in RAM
PRAGMA mmap_size = 268435456; -- 256 MB memory-mapped I/O
PRAGMA cache_size = -16000; -- 16 MB cacheTables
| Table | PK | Key Fields | Notes |
|---|---|---|---|
workspaces | id TEXT | name, slug (unique), profile, color, project_dir, config JSON | Trigger: auto-update updated_at |
conversations | id TEXT | workspace_id FK, title, pinned, archived, run_count, last_run_id | Index: workspace_id, updated_at DESC |
messages | id TEXT | conversation_id FK, role, content, content_type, metadata JSON | Immutable — no updated_at. role: user|assistant|system|tool|error |
run_links | run_id TEXT | conversation_id FK RESTRICT, status, phases JSON, total_cost, total_tokens | status: running|completed|failed|cancelled|completed_with_errors |
sessions | id TEXT | token_hash (unique), expires_at | Hourly cleanup of expired sessions |
workspace_settings | (workspace_id, key) | value JSON nullable | Reserved: auto_summary, default_model, notification_level, max_concurrent_runs |
ipc_events | id TEXT | type, payload JSON, processed boolean | Append-only audit log. 7-day purge of processed events |
Migrations
# Generate migration from schema changes
npm run db:generate
# Apply migrations (also runs automatically on server start)
npm run db:migrate
# Config: drizzle.config.js
export default {
schema: './src/db/schema.js',
out: './src/db/migrations',
dialect: 'sqlite',
dbCredentials: { url: process.env.ADA_DB_PATH }
}