The Postgres schema as migrated. Source of truth is internal/store/migrations/ (0001_init, 0002_agents, 0003_endpoint_extra_body, 0004_sandboxes, 0005_cc_route_decisions, 0006_usage_session, 0007_cc_jobs, 0008_cc_launch_counter, 0009_media_jobs, 0010_agents_m7, 0011_agent_memory), applied by goose at boot; this page was written from those files and last verified at commit 97d4480 (migrations 0005 to 0008 are the only ones added since bf89aee). The schema PLAN.md sketches is larger than what exists; Planned but not migrated lists the difference.
Conventions: UUID primary keys from gen_random_uuid() (except usage_ledger and sandbox_events, which use bigserial, providers and endpoints, which use text slug ids, and join tables, which use composite keys), timestamptz timestamps, jsonb for flexible payloads, and ON DELETE CASCADE from owning rows. Tables with updated_at get a set_updated_at trigger, except sandboxes, whose queries set updated_at explicitly. Extensions: pgcrypto, citext, and vector (pgvector, enabled by 0011; the pool registers its types at connect). River adds its own tables through its own migration (jobs.Migrate), not listed here.
Queries live in internal/store/queries/*.sql and are compiled to Go by sqlc (make sqlc).
owner_id, name, kind (chat, code, design, images; the API accepts any kind, but the web app creates only chat and code), settings, repo_url, default_branch, github_installation_id, archived_at
project_members
Sharing: (project_id, user_id) with role of viewer, editor or admin
project_id, user_id, title, mode (chat, code, design, images, agent), agent_id (nullable FK to agents, added in 0002), model_selector (default auto), settings (including tool_policies), compaction_head_message_seq, archived_at
messages
conversation_id, seq (unique per conversation), role (system, user, assistant, tool), parts (the canonical gateway Part array, provider-neutral), endpoint_id, model, usage, finish_reason, parent_id (branching)
attachments
Uploaded files in the blob store: blob_key, mime, bytes, sha256, filename, width, height
id (slug), kind (anthropic or openai_compat), base_url, api_key_env, headers. Keys are never stored, only the environment variable name. A base URL taken from an environment variable is stored as env:VARNAME and resolved at load time; if unset, the provider is skipped. A provider that names a key variable which is empty is skipped too
endpoints
id (slug such as anthropic/claude-sonnet-5-5), provider_id, model_name, display_name, capabilities, pricing, throughput_class, latency_class, is_local, enabled, health columns maintained by the worker (health_status: unknown, healthy, degraded, down; health_checked_at, health_error, p50_latency_ms, and error_rate, which is reserved and always NULL today), and extra_body (added in 0003, merged into every OpenAI-compatible request)
routing_policies
name (unique), yaml, priority, enabled. Edited in Admin; also seeded from config/policies/
budgets
scope (user, agent, api_key, global), scope_id, period (day, week, month, total), limit_usd, on_exceed (block or downgrade to local endpoints). Unique per (scope, scope_id, period), which Postgres does not enforce for global budgets because their scope_id is NULL (a code gap, not a doc one)
usage_ledger
One row per model call, bigserial id. User, conversation, agent, run and API key ids (stored without foreign keys so history survives deletions, except user_id and conversation_id which are set null), endpoint_id, model, task_class, policy_name, decision (routing decision as JSON), token counts including cache read and write, cost_usd, latency_ms, ttft_ms, finish_reason, error, and session_id (text, nullable, from the client’s x-claude-code-session-id header; set only by the external API; added in 0006)
Endpoint health is written by the worker and read by both roles through the periodic registry reload; see ARCHITECTURE.md.
Every chat turn is a run, so these tables are in use, and since PLAN M7 first cut user-defined agents run on them too.
Table
Purpose and notable columns
agent_runs
conversation_id, agent_id (null for plain chat turns), user_id, trigger_id, status (queued, running, paused_approval, paused_steer, done, failed, cancelled), request (the request skeleton without messages), tool_policies (resolved for the run), max_steps, step_count, first_message_id (the assistant message the UI streams into), cost_usd, error, heartbeat_at and owner_pid (used by the reaper), started_at, ended_at
A pending tool call: run_id, step_seq, tool_call_id, tool_name, args, status (pending, approved, denied, expired), decided_by, decided_at, note
agents
Definitions for long-lived agents: goal, system_prompt, model_policy, tool_allowlist, tool_policies, mcp_servers, memory_config ({disabled, k, top_n}), max_steps, enabled, and from 0010 a nullable project_id (cascades; the project an agent’s runs live in, index agents_project_idx) and last_run_at. Used by internal/agents and /api/agents; the Agents page does not edit mcp_servers or memory_config
agent_triggers
kind (cron, webhook, repo_push, manual; only cron, webhook and manual can be created, repo_push is rejected), spec, secret_hash (sha256 of a webhook’s secret, shown once at creation), enabled, and from 0010name, next_run_at, last_run_at and last_error. Partial index agent_triggers_due_idx(next_run_at) where enabled and kind = 'cron'
One per (project_id, user_id): container_id, volume_name, image, runtime (default runc), status (created, running, stopped, failed), error, last_used_at. The container is disposable; the volume holds the user’s working copy
What an agent remembers across runs (0011). agent_id (cascades), kind (fact, episode, preference), content (at most 1000 characters), content_hash (sha256 of the lowercased content; UNIQUE (agent_id, content_hash) so a repeat only raises importance and fills a missing embedding), embedding vector(768) (nullable: a run without an embedding endpoint stores none, and a vector of another width is stored as NULL), source_run_id (set null on delete), importance (0 to 1, default 0.5), last_used_at, created_at. Indexes: (agent_id, created_at desc), (agent_id, importance desc) and an HNSW index on embedding with cosine distance
One row per calendar week (week, a timestamptz that is Monday 00:00 UTC, primary key) with a count and updated_at (0008). Incremented when a new cc_jobs row is created, in the week the job started; a re-report of a known job is not a launch. The router reads it for the weekly soft cap. It is an approximation: two reports of the same new job that race can both count, and a database error on the lookup is treated as “new”
cc_jobs
The handle of each Claude Code job, for the read-only job tab (0007): where a tmux window lives, never what is in it. id (text, the tmux handle id), user_id (set null on delete), target, session, window, cwd, lane, model, source (wsj, router or tmux, the last for a window a refresh found that nobody reported), status (alive, dead, killed, gone, unknown), started_at, seen_at, ended_at, timestamps. tmux stays authoritative for liveness: status is what the last refresh saw. No prompt, output or credential is stored. Indexed by started_at DESC and, for open jobs, by target
cc_route_decisions
One row per spawn_job call, dry run or not (0005). user_id, conversation_id and run_id are nullable foreign keys that set NULL on delete. prompt_sha256 (the prompt text is never stored), features (jsonb: the counts and flags the rules saw, plus a classification object when the small-model classifier answered), lane (checked against claude-subscription, api, openrouter, local), rule, reason, target, model, dry_run, job_id (the tmux handle id), created_at. Indexed by created_at DESC and by (lane, created_at DESC)
One row per image (later video) generation (0009), written by internal/media and driven by the River job media.generate. user_id and project_id cascade on delete; conversation_id sets NULL. kind (image, video, edit, upscale; only image is generated today), selector (the picker’s choice: endpoint id, alias or auto), endpoint_id (set when the job runs), inputs (jsonb: prompt, size, n, quality, seconds, aspect, source_attachment_id, estimate_usd), provider_job_id, status (queued, running, done, failed, cancelled), progress, output_attachment_ids (uuid array into attachments), cost_usd, error, created_at, started_at, ended_at, updated_at (trigger). Indexes on (project_id, created_at desc), (user_id, created_at desc) and a partial one on open jobs. Each finished job also writes a usage_ledger row under task class image or video
usage_ledger: by (user_id, created_at DESC), (endpoint_id, created_at DESC), partial on agent_id, and partial on (session_id, created_at DESC) where session_id is not null.
agent_runs: partial index on (status, heartbeat_at) for queued and running, which the reaper scans; (conversation_id, created_at DESC) for the runs list.
messages: unique (conversation_id, seq). Inserts must allocate the next seq per conversation.
compactions: (conversation_id, covers_through_seq DESC) to find the latest block.
From PLAN.md, not in any migration yet (the billing-router tables subscription_quota and user_credentials were dropped from the plan and will not be added):
Planned
Milestone
rental_instances, rental_templates
M9
datasets, finetune_jobs, adapters
M10
Differences between the plan and the current schema, so nobody writes queries from PLAN.md by mistake:
PLAN shows conversations.compaction_head_message_id; the column is compaction_head_message_seq.
PLAN describes message parts as AI SDK UIMessage parts; they are canonical gateway parts, converted at the edge.
PLAN names artifacts.current_version_id; the column is current_version (an integer).
PLAN names a ledger column policy_id; the ledger stores policy_name.
A comment in 0001_init.sql says the conversations.agent_id foreign key is added in “0003”; it is added in 0002_agents.sql.