Database

Where data lives

All of Andromeda's own state lives in the app state directory: .andromeda/ in the andromeda-js checkout, or ~/.andromeda for a bundled build. Set ANDROMEDA_STATE_DIR to move it. The workspace you point Andromeda at doesn't change where these files go.

The shared file name comes from storage.database_path (DATABASE_PATH, default andromeda.db). A relative path is resolved against the state directory. Artifacts can be split into their own file with ANDROMEDA_ARTIFACTS_DB.

Every SQLite file is opened the same way (openDatabase in src/libs/utils/sqlite.ts): its folder is created, journal_mode=WAL lets readers work while one process writes, busy_timeout=5000 makes a writer wait up to five seconds for a lock instead of failing, and every query is timed by the profiler. Because WAL relies on shared memory, a host process and a Docker container must not open the same file; the containers use their own state directory and share history through Postgres instead.

Tables in andromeda.db

Each feature creates its own tables with CREATE TABLE IF NOT EXISTS when it starts, so a fresh file fills in as features are used. Types are SQLite's: TEXT timestamps are ISO 8601 UTC, REAL timestamps in the learning tables are Unix seconds, and columns holding lists or objects store JSON text.

Usage ledger: runs and what happened in them

Written by UsageLedger (src/libs/observability/usage.ts) through the trace recorder. It powers the usage dashboard, /usage, the audit views and the Command Center. Turn it off with usage.enabled: false.

Learning

Written by Experience (src/libs/learning/experience.ts) when learning.experience.enabled is on (the default). See Learning.

Services

The durable services that survive restarts. See Background tasks and Schedules.

Older databases may also contain a schedule_runs table. The current code doesn't write to it; schedule results are recorded as runs with source schedule.

Conversation search and artifacts

SQLite creates conversation_text_config, _content, _data, _docsize and _idx to back the FTS5 table. Don't write to them directly.

Bookkeeping

How the tables refer to each other

The tables in andromeda.db are linked by shared ids, not by FOREIGN KEY constraints. SQLite doesn't enforce foreign keys unless a connection turns them on, and these tables are written by several processes, so a row can be written even when its parent hasn't been yet. Joins on these ids should allow for a missing parent (use LEFT JOIN). The only enforced foreign key is in web-auth.db. The run id is the hub: everything that happens during a run is tagged with it.

Indexes

Indexes follow the queries the dashboard and services run most: time ranges (calls_at, logs_at, media_at, feedback_at, runs_started), everything for one run (calls_run, tools_run, logs_run, media_run, feedback_run, runs_parent), and the due-work polls (schedules_due on (status, next_run_at), triggers_due on (status, next_check_at), background_tasks_status). reviews_status serves the review queue and artifacts_source finds an existing copy of a page by (source, fingerprint).

Example: one chat turn, row by row

This example follows a single turn through the database. You type "Summarize the open PRs and screenshot the PR list" in the TUI. The agent calls a model, starts a subagent to read the PRs, takes a browser screenshot, and you give the answer a thumbs up. The ids below are illustrative.

1. The turn starts. The trace recorder inserts the run with status = 'running':

   runs   id = 'run_7f3a'  parent_id = NULL  agent = 'andromeda'  source = 'interactive'
          thread_id = 'tui-2026-10-15'  prompt = 'Summarize the open PRs…'  started_at = '2026-10-15T09:00:00Z'

2. The model is called. Each request adds a calls row with its tokens and timings, and each step in the run appends an events row with the next event_id, using Temporal-style event names:

   calls  id = 1201  run_id = 'run_7f3a'  engine = 'ollama'  model = 'llama3.2:3b'
          prompt_tokens = 2140  completion_tokens = 96  ttft_ms = 410  tool_calls = '["spawn_subagent"]'
   events run_id = 'run_7f3a'  event_id = 2  type = 'ActivityTaskScheduled'  attributes = '{…}'

3. A subagent runs. It gets its own run, pointing back at the turn, and its own calls and tools:

   runs   id = 'run_91c0'  parent_id = 'run_7f3a'  agent = 'researcher'  status = 'completed'
   tools  run_id = 'run_91c0'  tool = 'git_log'  outcome = 'ok'  duration_ms = 84  result_chars = 3120

4. A screenshot is taken. The tool call is recorded with redacted arguments, and because the tool made a file, the file is registered as an artifact:

   tools     run_id = 'run_7f3a'  tool = 'browser_screenshot'  outcome = 'ok'  duration_ms = 920
   artifacts id = 'a1b2c3'  kind = 'screenshot'  source = 'browser_screenshot'  path = '…/pr-list.png'

5. The turn ends. The run is closed, the messages are appended to .memory/<resource>/threads/tui-2026-10-15.json, and the conversation index adds the new messages to conversation_text and updates conversation_threads.count. With Qdrant memory, the turn's messages are embedded and stored as points that share one turn_id.

   runs   id = 'run_7f3a'  status = 'completed'  ended_at = '2026-10-15T09:00:14Z'  result = 'There are 3 open PRs…'

6. Learning records it. Because the run used several tools, an experience_runs row is written with trace_run_id = 'run_7f3a', and a recipe may be learned into experience_recipes.
7. You rate the answer. A feedback row is added for run_7f3a, and the matching experience_runs.feedback moves to 1, crediting the recipes it used. A thumbs down would also add a reviews row with reason = 'feedback' and status = 'needs-review'.

Querying it

Open the file read-only with the sqlite3 shell (sqlite3 -readonly .andromeda/andromeda.db) or the dashboard's Database page. Read-only access is safe while Andromeda runs, because WAL lets readers work alongside the writer.

-- The last five turns, with model calls, tokens and tool calls for each.
-- Subqueries avoid the row multiplication you'd get from joining calls and tools together.
SELECT r.id, r.agent, r.source, r.status, r.started_at,
       (SELECT COUNT(*) FROM calls c WHERE c.run_id = r.id) AS model_calls,
       (SELECT COALESCE(SUM(c.prompt_tokens + c.completion_tokens), 0) FROM calls c WHERE c.run_id = r.id) AS tokens,
       (SELECT COUNT(*) FROM tools t WHERE t.run_id = r.id) AS tool_calls
FROM runs r
WHERE r.parent_id IS NULL
ORDER BY r.started_at DESC
LIMIT 5;

-- The subagents a turn started.
SELECT child.id, child.agent, child.status
FROM runs child
WHERE child.parent_id = 'run_7f3a';

-- Which tools fail most, and how long they take.
SELECT tool, outcome, COUNT(*) AS calls, ROUND(AVG(duration_ms)) AS avg_ms
FROM tools
GROUP BY tool, outcome
ORDER BY calls DESC;

-- Keyword search across every past conversation (FTS5).
SELECT thread_id, role, snippet(conversation_text, 0, '[', ']', '…', 12) AS excerpt
FROM conversation_text
WHERE conversation_text MATCH 'qdrant'
ORDER BY rank
LIMIT 5;

-- Learned recipes and how they've done, with the run each was learned from.
SELECT rec.id, rec.task, rec.uses, rec.wins, rec.losses, e.trace_run_id
FROM experience_recipes rec
JOIN experience_runs e ON e.learned_recipe = rec.id;

-- What the scheduler checks each tick (served by the schedules_due index).
SELECT id, name, cron, next_run_at
FROM schedules
WHERE status = 'active' AND next_run_at <= strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
ORDER BY next_run_at;

The side databases

cache.db

One table, shared_cache, keyed by (namespace, key) with value, expires_at and last_access. Processes share web fetch and embedding results through it. shared_cache_expiry finds expired entries to drop, and shared_cache_lru on (namespace, last_access) evicts the least recently used once a namespace is full. Hit and miss counts go to cache_stats in andromeda.db. It is kept apart because it churns constantly and can be deleted at any time.

db-stats.db

The query profiler's output. db_query_stats holds per-minute totals keyed by (bucket, pid, db, sql): calls, total_ms, max_ms, rows, errors and the process name. db_slow_queries logs each query slower than logging.database.slow_ms (default 50 ms). The db column names which database file the query ran against. Statements are stored with their placeholders, never with the values bound to them. Each process flushes every 10 seconds, and rows older than logging.database.retention_days (default 7) are pruned. Set logging.database.enabled: false to turn profiling off.

web-auth.db

Accounts for the web app, written by api serve:

Outside SQLite

Conversation files

Message history isn't in SQLite. The local memory store writes each thread to .memory/<resource_id>/threads/<thread_id>.json (or .jsonl when the store uses the append format), with .meta.json for the title and timestamps and .goals.json for goals. conversation_text is a search index built from these files and can be rebuilt from them.

Qdrant

With memory.provider: qdrant, messages are embedded into the andromeda_memory collection (memory.qdrant.collection) and ingested pages and documents into andromeda_downloads (downloads.collection). Point payloads carry the same ids as the SQLite tables, so a hit can be traced back to its conversation:

A point's id is a UUIDv5 of its content and payload without created_at, so storing the same text again reuses the existing point instead of making a duplicate. The web app's Memory page browses, searches and prunes both collections.

Postgres (optional)

Set usage.postgres_url (USAGE_POSTGRES_URL) to share the usage ledger between machines and containers. SQLite is still what each process writes and reads; Postgres is a meeting point:

1. Triggers on calls, tools, logs, feedback, media, runs, events and reviews append every change to sync_outbox.
2. Every couple of seconds, one process per database (holding the lease in sync_state) pushes the outbox into a single Postgres table, andromeda_ledger, keyed by (origin, tbl, key) with the row as JSONB.
3. Rows with an integer id (calls, tools, logs, feedback, media) belong to the ledger that made them: origin is that ledger's id. Other machines import them under new local ids and remember the mapping in sync_map. Rows with a natural key (runs, events, reviews) are shared under origin = '*'.
4. Pulls take everything other writers changed since the last pull. sync_flags stops imported rows from being pushed back.

Cache counters, schedules, tasks, triggers, learning and artifacts stay local. If Postgres is unreachable, changes wait in the outbox. History from before you turned sync on is sent on the first sync.

Schema changes and legacy files

There is no migration framework. Each store updates its own tables at startup:

• New columns are added in place. The store reads PRAGMA table_info and runs ALTER TABLE … ADD COLUMN for anything missing. For example, calls gained run_id, cached_tokens, ttft_ms, think and load_ms this way, and runs gained result, pid and host.
• Data fixes run once and are idempotent, such as clearing cache estimates that were wrongly recorded as full hits.
• Legacy per-feature files (usage.db, experience.db, background_tasks.db, schedules.db, triggers.db, and the workspace's .andromeda/artifacts.db) are imported into andromeda.db at startup by migrateLegacyDatabases. It attaches each old file, copies its tables while keeping their ids (INSERT OR IGNORE, only columns both sides have), and records the file in andromeda_migrations so the import runs once. The learning tables are renamed to experience_runs and experience_recipes because usage also has a table named runs. The old files are kept as a rollback copy.

To start fresh, stop every Andromeda process and delete .andromeda/andromeda.db together with its -wal and -shm files. andromeda usage reset clears only the usage ledger.