Database

Database

SQLite is the single source of truth: one portable file, WAL mode, foreign keys enforced, explicit transactions, forward-only migrations (current schema v14). If the file's version is newer than the app knows, the app refuses to start.

Tables

• conversations — title, active/archived status, timestamps, nullable workspace_root scope marker (NULL for pre-picker rows).
• messages — immutable after creation; user/assistant roles only; optional provider_id (cleared to NULL if the provider is removed) and model_name; UNIQUE (conversation_id, id) anchors the attachment guard.
• prompts — reusable templates (title + content up to 10,000 chars).
• attachments — draft state (message_id IS NULL) linked to a message at send time; composite FK guarantees an attachment links only a message of its own conversation.
• providers — non-sensitive metadata only (name unique, display_name). No secrets, no timestamps.
• app_settings — key-value store (key PK).
• schema_version — append-only migration audit (MAX(version) is current).
• agent_runs — one row per agent run: model, autonomy mode, status (running, completed, cancelled, budget_exhausted, spend_limit_exceeded, error), timestamps, total_steps, final content/error, optional per-run spend (spent_micro_usd, limit_micro_usd). Terminal rows require finished_at.
• agent_steps — immutable step records (model_turn, tool_call, approval) ordered by seq; raw provider JSON args stored verbatim; rule provenance (rule_id, group_key, decided_by); nullable token counts.
• permission_rules — ordered allow/ask/deny rules over preset + tool pattern + optional path pattern; seeded coding/execute_command/*/ask row.
• agent_tasks / agent_task_steps — task lists with bounded execution (max_steps 1–25, default 25); current_step <= total_steps.
• usage_ledger — local-only per-day counters (agent_run, message, github_read, update_check); counts only, no content column by construction; pruned past 90 days.
• debt_items — debt backlog (audit/manual source, info/warning severity, open/accepted/fixed/wontfix).
• task_locks — at most one expiring edit lock per task; expired rows are stealable.

Search (FTS5)

• conversations_fts (titles), messages_fts (content), prompts_fts (title + content), kept current by triggers on insert/update/delete.
• updated_at triggers on conversations and prompts use monotonic bodies so future-dated rows do not fail their next update.

Integrity and portability

• Hard deletes everywhere; cascades remove messages/attachments/runs/steps/tasks with their parent; provider/task-run links clear to NULL instead of destroying history.
• Import inserts get new surrogate IDs (no merging); the whole import rolls back on validation failure. Export reads a conversation with messages in created_at order.
• Backup is a filesystem copy of the file; restore replaces it, with a version check on launch. On corruption the app refuses the file and only creates a fresh database after explicit user confirmation.

See also: Search-Import-Export, Security-and-Credentials, Agent-Runs.