Skald-Circle/docs/database.md

Database

Connection and Init

db::init_pool(path) opens a SQLite connection pool with create_if_missing=true, then calls create_tables(). All CREATE TABLE IF NOT EXISTS statements are idempotent — safe to run on every startup.

The pool is opened in WAL journal mode (synchronous=NORMAL) with a 5 s busy_timeout. The DB is written concurrently (chat loop, cron, and plugins such as the mobile-connector persisting its E2E send_counter before each send). Without busy_timeout, a contended write returns SQLITE_BUSY ("database is locked") immediately and the operation is lost — which silently dropped outbound mobile messages (the inbox_update never reached the device). WAL lets readers run alongside the single writer; busy_timeout makes a writer wait for the lock instead of failing.

---

Table Schemas

chat_sessions

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
title	TEXT	nullable
source	TEXT	NOT NULL DEFAULT 'web'
agent_id	TEXT	NOT NULL DEFAULT 'main' (added via ALTER)
is_interactive	INTEGER	NOT NULL DEFAULT 1 (added via ALTER)
is_ephemeral	INTEGER	NOT NULL DEFAULT 0 (added via ALTER)
run_context	TEXT	nullable — RunContext JSON blob (e.g. {"security_group":"admin"}); formerly run_context_id (renamed in migration 10)
created_at	TEXT	NOT NULL DEFAULT datetime('now')

is_interactive — 1 when a real user is actively participating in the session (web, Telegram). 0 for fully automated sessions where all user-role messages are generated by the application (cron, tic).

is_ephemeral — 1 for short-lived task sessions (cron, tic) with no long-term conversational value. Memory sinks (e.g. Honcho) should skip ephemeral sessions.

Index: idx_stack_session ON chat_sessions_stack(session_id)

---

chat_sessions_stack

Represents one agent call frame. Root frames have depth=0; sub-agent frames increment depth.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_id	INTEGER	NOT NULL REFERENCES chat_sessions(id)
agent_id	TEXT	NOT NULL DEFAULT 'main'
agent_prompt	TEXT	nullable (the call_agent prompt)
depth	INTEGER	NOT NULL DEFAULT 0
parent_tool_call_id	INTEGER	nullable (links to chat_llm_tools.id)
terminated_at	TEXT	nullable; set when dispatch_sub_agent finishes
created_at	TEXT	NOT NULL

---

chat_history

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_stack_id	INTEGER	NOT NULL REFERENCES chat_sessions_stack(id)
role	TEXT	NOT NULL CHECK(user | assistant | agent)
content	TEXT	NOT NULL DEFAULT ''
status	TEXT	NOT NULL DEFAULT ok CHECK(ok | failed)
input_tokens	INTEGER	nullable
output_tokens	INTEGER	nullable
duration_ms	INTEGER	nullable
model_db_id	INTEGER	nullable REFERENCES llm_models(id)
is_synthetic	INTEGER	NOT NULL DEFAULT 0 — 1 when the message was system-generated
reasoning_content	TEXT	nullable — chain-of-thought from reasoning models
cost	REAL	nullable — turn cost in USD, when the provider reports it (OpenRouter usage.cost)
metadata	TEXT	nullable — generic JSON metadata bag (added v17); currently { "attachments": [...] }
created_at	TEXT	NOT NULL

• role = 'agent' is a sub-agent prompt message; sent as user to the LLM but hidden in the UI
• status = 'failed' rows are excluded from for_stack() (not sent to LLM)
• model_db_id is set only on role = 'assistant' rows; null for user/agent messages and for rows created before this column was added
• is_synthetic = 1 marks messages injected by the system (ChatHub notification assistant messages with tool-call injection, or legacy synthetic user turns). They are included in the LLM context but excluded from the UI history (not shown on page reload). Currently used for role = 'assistant' rows that carry the synthetic read_notification tool call and reasoning trace.
• reasoning_content is populated only for role = 'assistant' rows from providers that return chain-of-thought (currently DeepSeek thinking mode); echoed back in the assistant message on subsequent turns
• cost is set on role = 'assistant' rows only when the provider returns a per-request price (OpenRouter exposes it under usage.cost); null for providers that don't bill per-call. Extracted via the ChatbotClient::extract_cost trait method (see llm-clients.md)
• metadata is a generic JSON bag (type MessageMetadata in core-api), set on role = 'user' rows that carry file attachments. The raw [SYSTEM INFO] block the LLM sees is never stored here — it is generated on the fly from metadata.attachments by the message builder, while the UI renders the same list as chips. content always stays the clean typed text. See frontend.md (attachments) and session.md.

Index: idx_history_stack ON chat_history(session_stack_id)

---

chat_summaries

Persisted conversation summaries generated by the context compactor (see compaction.md). Each row covers all chat_history messages up to and including covers_up_to_message_id. build_openai_messages loads only messages after this boundary and injects the summary as context.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
stack_id	INTEGER	NOT NULL REFERENCES chat_sessions_stack(id)
content	TEXT	NOT NULL — the summary text
covers_up_to_message_id	INTEGER	NOT NULL — boundary: messages with id > this are loaded raw
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Index: idx_chat_summaries_stack ON chat_summaries(stack_id)

Multiple rows can exist per stack (one per compaction cycle). Only the most recent row is used; older rows are retained for historical reference.

---

chat_llm_tools

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
message_id	INTEGER	NOT NULL REFERENCES chat_history(id)
name	TEXT	NOT NULL
arguments	TEXT	nullable (JSON string)
result	TEXT	nullable
status	TEXT	NOT NULL DEFAULT running CHECK(running | pending | done | failed | cancelled | rejected)
created_at	TEXT	NOT NULL

• running → tool is executing (or app crashed mid-execution — re-run on resume)
• pending → blocked on a human approval / clarification (re-gate / re-ask on resume)
• done → result is in result column
• failed → tool/runtime error message is in result column
• cancelled → stopped by the user via /stop (terminal, not re-run on resume) — distinct from failed
• rejected → denied by an approval policy or a human (terminal) — distinct from failed

The richer in-memory ToolExecutionState maps onto these strings (see tools.md). The table is created directly with the full 6-value CHECK constraint; cancelled/rejected (distinct from failed) were historically added by a table rebuild in schema versions 15–16, now folded into the baseline (see Migration Pattern).

Index: idx_tools_message ON chat_llm_tools(message_id)

---

mcp_servers

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
name	TEXT	NOT NULL UNIQUE
transport	TEXT	NOT NULL DEFAULT 'stdio'
command	TEXT	nullable
args_json	TEXT	nullable (JSON array)
env_json	TEXT	nullable (JSON object)
url	TEXT	nullable
api_key	TEXT	nullable
enabled	INTEGER	NOT NULL DEFAULT 1
created_at	TEXT	NOT NULL

---

llm_providers

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
name	TEXT	NOT NULL UNIQUE
type	TEXT	NOT NULL (lm_studio, ollama, open_ai, anthropic)
api_key	TEXT	nullable
base_url	TEXT	nullable
description	TEXT	nullable
removed_at	TEXT	nullable; set on soft-delete (api_key is also cleared)
created_at	TEXT	NOT NULL

Providers are never hard-deleted. DELETE /api/llm/providers/{id} sets removed_at and clears api_key, and cascades soft-delete to all models belonging to that provider. Rows with removed_at IS NOT NULL are excluded from all queries.

---

llm_models

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
provider_id	INTEGER	NOT NULL REFERENCES llm_providers(id) ON DELETE CASCADE
model_id	TEXT	NOT NULL (provider's internal model name)
name	TEXT	NOT NULL UNIQUE (display name, used as client key)
strength	TEXT	nullable (very_low | low | average | high | very_high)
scope	TEXT	NOT NULL DEFAULT '[]' (JSON array of scope strings)
is_default	INTEGER	NOT NULL DEFAULT 0
priority	INTEGER	NOT NULL DEFAULT 100 (lower = tried first by AUTO)
extra_params	TEXT	nullable (JSON object, merged into request body at call time)
context_length / max_output_tokens / knowledge_cutoff / capabilities	INTEGER / INTEGER / TEXT / TEXT	nullable catalog metadata (see docs/llm-clients.md)
reasoning	TEXT	nullable; selected reasoning value (JSON string for a ValueSet mode, JSON number for a Range mode; NULL = off) — see Reasoning in docs/llm-clients.md
removed_at	TEXT	nullable; set on soft-delete
created_at	TEXT	NOT NULL

The only uniqueness constraint is name (also the resolution key). There is no UNIQUE(provider_id, model_id) — the same underlying model may be registered several times under one provider with different aliases/reasoning (e.g. glm-4.6 vs glm-4.6-thinking). Migration v20 dropped the historical (provider_id, model_id) constraint by rebuilding the table (preserving id values, which are FK targets in llm_requests.model_db_id / chat_history.model_db_id) and added the reasoning column.

Models are never hard-deleted. DELETE /api/llm/models/{id} sets removed_at. Rows with removed_at IS NOT NULL are excluded from all queries, including the AUTO selector. The id column remains a valid FK target for chat_history.model_db_id even after removal. Re-adding a model with the same name revives the soft-deleted row (insert_model upserts on name).

---

transcribe_models

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
provider_id	INTEGER	NOT NULL REFERENCES llm_providers(id)
model_id	TEXT	NOT NULL (provider's internal model name, e.g. openai/whisper-1)
name	TEXT	NOT NULL UNIQUE (display name)
language	TEXT	nullable; BCP-47 code (e.g. "it", "en") or NULL for auto-detect
priority	INTEGER	NOT NULL DEFAULT 100 (lower = tried first by TranscribeManager)
removed_at	TEXT	nullable; soft-delete
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Only providers that declare ServiceType::Transcribe in ApiProvider::supported_types() should have rows here (OpenAI, OpenRouter). See providers/transcribe.md.

---

image_generate_models

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
provider_id	INTEGER	NOT NULL REFERENCES llm_providers(id)
model_id	TEXT	NOT NULL (provider's internal model name, e.g. x-ai/grok-2-vision)
name	TEXT	NOT NULL UNIQUE (display name, also used as provider_id in the image_generate LLM tool)
priority	INTEGER	NOT NULL DEFAULT 100 (lower = tried first)
removed_at	TEXT	nullable; soft-delete
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Only providers that declare ServiceType::ImageGenerate in ApiProvider::supported_types() should have rows here (OpenRouter, OpenAI). See providers/image.md.

---

secrets

Column	Type	Constraints
key	TEXT	PRIMARY KEY (uppercase by convention, e.g. HUGGINGFACE_TOKEN)
value	TEXT	NOT NULL — stored in plain text, never log
created_at	TEXT	NOT NULL DEFAULT datetime('now')
updated_at	TEXT	NOT NULL DEFAULT datetime('now') — updated on upsert

Managed via SecretsStore (src/core/secrets.rs). See secrets.md.

---

tts_models

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
provider_id	INTEGER	NOT NULL REFERENCES llm_providers(id)
model_id	TEXT	NOT NULL (generation model, e.g. tts-1-hd, eleven_multilingual_v2)
voice_id	TEXT	nullable; speaker voice ID — required for ElevenLabs, NULL for OpenAI (added in schema v2)
name	TEXT	NOT NULL UNIQUE (display name, also used as the synthesiser id())
description	TEXT	nullable; human-readable description shown in UI
instructions	TEXT	nullable; default voice style / tone / speed (overridable per call)
response_format	TEXT	nullable; audio format mp3/opus/aac/flac/wav/pcm sent to the provider — NULL ⇒ mp3 (added in schema v18)
priority	INTEGER	NOT NULL DEFAULT 100 (lower = tried first by TtsManager)
removed_at	TEXT	nullable; soft-delete
created_at	TEXT	NOT NULL DEFAULT datetime('now')

See providers/tts.md.

---

scheduled_jobs

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
title	TEXT	NOT NULL
description	TEXT	NOT NULL DEFAULT ''
cron	TEXT	NOT NULL (7-field format)
prompt	TEXT	NOT NULL
agent_id	TEXT	NOT NULL DEFAULT 'main' (column default; in practice always set explicitly — agent_id is required at task creation)
enabled	INTEGER	NOT NULL DEFAULT 1
last_run_at	TEXT	nullable (RFC3339 datetime)
next_run_at	TEXT	nullable (RFC3339); pre-computed next fire time; used by list_due() (added via ALTER)
single_run	INTEGER	NOT NULL DEFAULT 0; if 1 the job is disabled after its first execution (added via ALTER)
running_session_id	INTEGER	nullable; non-null while a run is in-flight; cleared by finish_run(); used by list_interrupted() for restart recovery (added via ALTER)
kind	TEXT	NOT NULL DEFAULT 'cron' — 'cron' scheduled, 'sync' run-now-blocking, 'async' run-now fire-and-forget
parent_session_id	INTEGER	nullable FK → chat_sessions(id); set for kind='async' tasks to route the result back to the calling session
run_context	TEXT	nullable; RunContext JSON blob inherited from the creating session (or overridden via the Tasks UI); applied to the ephemeral child session at run time. Formerly run_context_id (renamed in migration 10).
origin_ref	TEXT	nullable; free-form reference to the entity that originated this job (e.g. "PROJECT_TASK:42"); used by TaskManager for post-completion callbacks (added in migration 11)
created_at	TEXT	NOT NULL

next_run_at is set at job creation (first upcoming fire time after now), advanced after each successful run, cleared on disable, and recalculated by toggle_item (kind=cron) when re-enabling. list_due(pool, now) queries WHERE kind='cron' AND enabled=1 AND next_run_at <= now AND running_session_id IS NULL. Sync and async tasks run immediately on creation and are never picked up by the scheduler tick.

origin_ref is set only for jobs created by ProjectTicketManager.start(). When the job completes, run_job() checks this field and dispatches ticket_manager.on_job_completed() to update the ticket's status and result.

running_session_id is set by set_running() before handle_message() starts and cleared by finish_run() after the run completes. At startup, recover_interrupted() queries list_interrupted() and re-spawns any jobs still in-flight from a crash.

---

job_runs

Audit trail of every cron job and immediate task execution. One row per run, written after each execution by TaskManager::run_job().

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
job_id	INTEGER	NOT NULL REFERENCES scheduled_jobs(id)
session_id	INTEGER	nullable — the ephemeral session that ran the job
started_at	TEXT	NOT NULL (RFC3339)
completed_at	TEXT	nullable (RFC3339)
duration_ms	INTEGER	nullable
status	TEXT	NOT NULL (success | failed)
final_response	TEXT	nullable — last assistant message from the session
error	TEXT	nullable — error message on failure
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Index: idx_job_runs_job_id ON job_runs(job_id)

DAO in src/core/db/job_runs.rs: insert(pool, ...), list_for_job(pool, job_id, limit).

---

sources

One row per source (e.g. "web", "telegram"). Tracks the active session for each source.

Column	Type	Constraints
id	TEXT	PRIMARY KEY
active_session_id	INTEGER	nullable REFERENCES chat_sessions(id)
updated_at	TEXT	NOT NULL DEFAULT datetime('now')

ChatHub::get_or_create_session() reads this table; sources::upsert() writes it.

---

relay_clients

Created and owned by the mobile-connector plugin (crates/plugin-mobile-connector/src/db.rs), not the main crate. One row per paired mobile device. Counters MUST persist across restarts to prevent AES-GCM nonce reuse / replay (see mobile-connector.md).

Column	Type	Constraints
ed25519_pub	BLOB	PRIMARY KEY (32 B)
x25519_pub	BLOB	NOT NULL (32 B, for ECDH)
state	TEXT	NOT NULL (pending | authorized)
platform	TEXT	nullable (ios | android)
device_info	TEXT	nullable JSON (from the E2E hello)
send_counter	INTEGER	NOT NULL DEFAULT 0 (dir agent→client)
recv_counter	INTEGER	NOT NULL DEFAULT 0 (last seen, dir client→agent)
authorized_at	INTEGER	nullable unix ms
last_seen	INTEGER	nullable unix ms

---

config

Global key/value store for runtime configuration.

Column	Type	Constraints
key	TEXT	PRIMARY KEY
value	TEXT	NOT NULL
updated_at	TEXT	NOT NULL DEFAULT datetime('now')

Currently used keys:

Key	Owner	Description
source_home	ChatHub	Source ID that receives background agent notifications. Default: "web"
tic.run_context	TicManager	Run context ID applied to each TIC session tick. Empty = no run context.
tic.interval_minutes	TicManager	Override tick interval in minutes. Empty = use tic.interval_secs from config.yml.

Keys are managed via GET /api/config + PUT /api/config/:key. Registered properties are declared by each subsystem via core_api::ConfigProperty and collected in Skald::config_properties.

---

mcp_events

Persistent store for push notifications received from MCP servers via JSON-RPC notifications (stdout, no id field). Written by McpManager::notification_consumer; consumed by TicManager at a configurable interval (tic.interval_secs in config.yml, default 900 s).

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
source	TEXT	NOT NULL — MCP server name (e.g. "whatsapp", "gmail", "gcal")
method	TEXT	NOT NULL — notification method (e.g. "event/new_email")
payload	TEXT	NOT NULL — JSON string of the notification params field
processed	INTEGER	NOT NULL DEFAULT 0 — set to 1 by TicManager before running the agent
processed_at	TEXT	nullable — timestamp set when processed is flipped
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Index: idx_mcp_events_pending ON mcp_events(id) WHERE processed = 0 — partial index for efficient pending-event queries.

DAO in src/core/db/mcp_events.rs: insert, pending_limited(limit), pending, mark_processed(ids), all_recent(limit).

---

session_mcp_grants

Root-agent record of which tool groups have been activated via activate_tools — MCP server names and/or the reserved keyword config (which unlocks the built-in Config-category tools). Grants survive restarts and persist for the whole session lifetime. Tools for granted groups are included in the LLM's tool list on every subsequent turn via the dynamic all_tool_defs() call.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_id	INTEGER	NOT NULL (FK to chat_sessions.id)
mcp_name	TEXT	NOT NULL — MCP server name (e.g. "gmail")
granted_at	TEXT	NOT NULL DEFAULT datetime('now')

Unique constraint: (session_id, mcp_name) — granting the same server twice is a no-op (INSERT OR IGNORE).

DAO in src/core/db/session_mcp_grants.rs: grant(pool, session_id, mcp_name), list_for_session(pool, session_id).

Sub-agents do not write here. They use stack_mcp_grants instead.

---

stack_mcp_grants

Sub-agent record of which MCP servers have been activated by a specific stack frame. Grants are:

• Isolated from the parent session — they do not affect session_mcp_grants.
• Persistent across restarts — if a sub-agent is interrupted, dispatch_sub_agent re-loads these grants from DB when execution resumes.
• Cleaned up automatically when the stack frame terminates: dispatch_sub_agent calls delete_for_stack(pool, stack_id) before returning.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
stack_id	INTEGER	NOT NULL (FK to chat_sessions_stack.id)
mcp_name	TEXT	NOT NULL — MCP server name
granted_at	TEXT	NOT NULL DEFAULT datetime('now')

Unique constraint: (stack_id, mcp_name) — granting the same server twice is a no-op (INSERT OR IGNORE).

DAO in src/core/db/stack_mcp_grants.rs: grant(pool, stack_id, mcp_name), list_for_stack(pool, stack_id), delete_for_stack(pool, stack_id).

---

llm_requests

Debug log of every chat_with_tools call. Populated by LoggingChatbotClient (fire-and-forget, invisible to the LLM loop). Enabled via config.yml — see llm.request_log.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_id	INTEGER	nullable — chat_sessions.id of the calling session
stack_id	INTEGER	nullable — chat_sessions_stack.id of the calling frame
model_name	TEXT	NOT NULL — display name of the model (e.g. "claude")
request_json	TEXT	NOT NULL — full HTTP request body sent to the provider (compact JSON)
request_headers	TEXT	nullable — HTTP request headers as JSON object (api-key redacted)
response_json	TEXT	nullable — full HTTP response body (compact JSON); NULL on error
response_headers	TEXT	nullable — HTTP response headers as JSON object
error_text	TEXT	nullable — error message when the HTTP call failed
input_tokens	INTEGER	nullable
output_tokens	INTEGER	nullable
duration_ms	INTEGER	NOT NULL DEFAULT 0 — wall-clock round-trip time
created_at	TEXT	NOT NULL DEFAULT datetime('now')

Index: idx_llm_requests_created ON llm_requests(created_at) — used by the retention cleanup.

Rows are purged automatically by a background task (runs at boot + every hour) via db::llm_requests::cleanup(pool, retention_days). Default retention: 14 days.

DAO in src/core/db/llm_requests.rs: insert(pool, LlmRequestRow), cleanup(pool, retention_days).

Config (config.yml):

llm:
  request_log:
    enabled: true          # default false
    retention_days: 14     # default 14

---

session_scratchpad

Per-session key-value store used by the update_scratchpad tool. Shared by all agents in the same chat session (including async sub-tasks, which are transparently redirected to the parent session's scratchpad at runtime); not persisted across sessions (tied to chat_sessions.id).

Column	Type	Constraints
session_id	INTEGER	NOT NULL REFERENCES chat_sessions(id)
key	TEXT	NOT NULL
value	TEXT	NOT NULL

Primary key: (session_id, key).

upsert() uses INSERT ... ON CONFLICT DO UPDATE — writing the same key twice overwrites the previous value. Contents are injected into every agent's system context via build_openai_messages.

---

Schema Creation

The database has no migration system. create_tables() builds every table directly at its final shape on first boot (CREATE TABLE IF NOT EXISTS). There is no schema_version tracking, no incremental migration logic — the schema is a single flat baseline.

To change the schema, edit the relevant CREATE TABLE in create_tables() (src/core/db/mod.rs). Since the system has no existing users, schema changes simply start from a fresh DB (delete the old data/skald.db if needed).

---

projects

Filesystem-linked projects, each with an independent Kanban board of tickets. updated_at is refreshed on every project or ticket operation (via db::projects::touch) so the UI can order by recency.

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
name	TEXT	NOT NULL
path	TEXT	NOT NULL — absolute filesystem path for the project folder
description	TEXT	NOT NULL DEFAULT ''
run_context	TEXT	nullable — RunContext JSON blob applied to tickets when they have no own run_context
created_at	TEXT	NOT NULL DEFAULT datetime('now')
updated_at	TEXT	NOT NULL DEFAULT datetime('now') — refreshed on every project/ticket operation

DAO in src/core/db/projects.rs: list(pool) (ORDER BY updated_at DESC), get(pool, id), create(...), update(...), touch(pool, id), delete(pool, id) (CASCADE deletes tickets).

---

project_tickets

Individual work items belonging to a project. Each ticket can be executed by a background agent.

Lifecycle: todo → pending (start() called) → in_progress (job starts) → done / failed

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
project_id	INTEGER	NOT NULL REFERENCES projects(id) ON DELETE CASCADE
title	TEXT	NOT NULL
description	TEXT	NOT NULL DEFAULT ''
status	TEXT	NOT NULL DEFAULT 'todo' CHECK(todo | pending | in_progress | done | failed)
agent_id	TEXT	NOT NULL DEFAULT 'main'
run_context	TEXT	nullable — RunContext JSON blob; stores only static config set at creation time (e.g. security_group). Runtime fields (working_directory, allow_fs_writes, system_prompt) are computed by ProjectTicketManager.start() and never persisted here. Falls back to projects.run_context if NULL.
job_id	INTEGER	nullable FK → scheduled_jobs(id) (no ON DELETE action); set when start() creates the job. Callers that delete a scheduled_jobs row (scheduled_jobs::delete(), cron's cleanup_expired_single_runs) must null this first, or the delete fails with a FK constraint error.
result	TEXT	nullable — final agent output (populated when status = done)
error	TEXT	nullable — error message (populated when status = failed)
created_at	TEXT	NOT NULL DEFAULT datetime('now')
started_at	TEXT	nullable — set when the job begins execution
completed_at	TEXT	nullable — set when the job finishes

DAO in src/core/db/project_tickets.rs: list_for_project(pool, project_id), get(pool, id), create(...), delete(pool, id), set_status(pool, id, status), start(pool, id, job_id), complete(pool, id, result, error), reset(pool, id).

---

Soft-Failure Pattern

On handle_message start, two recovery operations run:

1. Orphaned user message check — if the last chat_history row for the stack has role='user' or role='agent' (no following assistant message), it is marked status='failed'. This prevents sending a user→user sequence to strict LLM APIs (e.g. OpenRouter).

2. resume_pending_tools(stack_id, config, tx) — finds all chat_llm_tools rows still in status='pending' for the stack. For each one:

   • Re-runs it through the ApprovalManager gate (rules may have changed since the interruption).
   • Deny → marks the tool call failed immediately.
   • Require → emits an ApprovalRequired/PendingWrite event and waits for human input, just like a live turn. If the WS is disconnected, returns early leaving the calls still pending.
   • Allow → executes the tool directly; marks it done or failed depending on outcome.

   After all pending calls are resolved, run_agent_turn runs normally and the LLM sees a complete history.

Note: fail_pending_for_stack() exists in db/chat_llm_tools.rs but is not called from the session handler. Pending tool calls are re-executed (not hard-failed) so the LLM receives real results rather than synthetic errors.

---

When to Update This File

• Any table schema changes (new column, new table, removed table)
• Migration approach changes
• A new soft-failure recovery step is added to handle_message