Database

Archon supports two database backends: SQLite (default, zero setup) and PostgreSQL (optional, for cloud/advanced deployments). The database backend is selected automatically based on whether the DATABASE_URL environment variable is set.

SQLite (Default - No Setup Required)

Simply omit the DATABASE_URL variable from your .env file. The app will automatically:

• Create a SQLite database at ~/.archon/archon.db
• Initialize the schema on first run
• Use this database for all operations

Pros:

• Zero configuration required
• No external database needed
• Perfect for single-user CLI usage

Cons:

• Not suitable for multi-container deployments
• No network access (CLI and server can’t share database across different hosts)

Remote PostgreSQL (Supabase, Neon, etc.)

Set your remote connection string in .env:

DATABASE_URL=postgresql://user:password@host:5432/dbname

No manual migration step is required. On startup, the Postgres adapter applies the bundled migrations/000_combined.sql inside an advisory-lock transaction. The SQL is idempotent (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS), so both fresh installs and version upgrades converge automatically — including new tables and columns added in later releases.

Upgrades are verified, not assumed: CI applies the current schema on top of a database built from every distinct schema Archon has ever released, re-applies it to confirm idempotence, and compares the result against a fresh install — columns, indexes, column comments, constraints and sequences. Run the same check yourself against any PostgreSQL with bun run check:schema-upgrades.

If schema application fails (permissions, syntax error, network), the process aborts at the first DB operation with the underlying Postgres error logged at db.pg_schema_init_failed.

Local PostgreSQL via Docker

Use the with-db Docker Compose profile for automatic PostgreSQL setup.

Set in .env:

DATABASE_URL=postgresql://postgres:postgres@postgres:5432/remote_coding_agent

The app converges the schema automatically on startup (see the note above for remote Postgres). Both fresh installs and upgrades are handled by the same path; the docker-entrypoint-initdb.d mount in docker-compose.yml is now redundant and is retained only as a no-op on fresh volumes.

Verifying the Database

Health check:

curl http://localhost:3090/health/db
# Expected: {"status":"ok","database":"connected"}

List tables (PostgreSQL):

psql $DATABASE_URL -c "\dt"

Schema Overview

The database has 18 tables, all prefixed with remote_agent_:

1. remote_agent_codebases - Repository metadata

   • Commands stored as JSONB: {command_name: {path, description}}
   • AI assistant type per codebase
   • Default working directory
   • kind ('repo' | 'folder', default 'repo') discriminates git-repo projects from non-git folder projects (which run in place, no worktree)
   • Nullable detected default branch, used as branch context for workspace sync when available

2. remote_agent_conversations - Platform conversation tracking

   • Platform type + conversation ID (unique constraint)
   • Linked to codebase via foreign key
   • AI assistant type locked at creation
   • Nullable user_id records the first user who created the conversation (first-user-wins; later replies in the same thread are attributed on the workflow_run, not here)

3. remote_agent_sessions - AI session management

   • Active session flag (one per conversation)
   • Session ID for resume capability
   • Metadata JSONB for command context

4. remote_agent_isolation_environments - Worktree isolation

   • Tracks git worktrees per issue/PR
   • Enables worktree sharing between linked issues and PRs
   • Nullable created_by_user_id preserves the original creator across re-activation (the ON CONFLICT DO UPDATE clause intentionally omits this column)

5. remote_agent_workflow_runs - Workflow execution tracking

   • Tracks active workflows per conversation
   • Locks concurrent execution per working_path: a second dispatch on a path with an active run (status pending/running/paused) is auto-cancelled with an actionable message. Stale pending rows older than 5 minutes are treated as orphaned and ignored. A workflow: sub-run shares its parent’s checkout unless the node declares isolation: worktree, so the path-lock excludes both the run’s ancestor chain and its descendants (via parent_run_id) — a child never contends with its own parent. Siblings are not excluded.
   • Stores workflow state, step progress, and parent conversation linkage
   • Nullable user_id records which user triggered the run
   • Nullable parent_run_id (#2121 Phase 2) — self-referential FK (ON DELETE SET NULL) linking a workflow: sub-run to the run that spawned it; null for top-level runs. Makes the run tree walkable (findChildRuns/getRunAncestry) for the abandon cascade and cost roll-up.

6. remote_agent_workflow_events - Step-level workflow event log

   • Records step transitions, artifacts, and errors per workflow run
   • Lean UI-relevant events (verbose logs stored in JSONL files)
   • Enables workflow run detail views and debugging
   • Indexed on created_at (idx_workflow_events_created_at) for the dashboard event poller’s cross-run tail. On PostgreSQL an AFTER INSERT trigger (archon_workflow_event_notify) calls pg_notify('archon_dashboard_event', …) so runs started out of process (the archon CLI / --detach) stream live to the console; on SQLite the poller picks them up within its interval. The trigger is Postgres-only and best-effort (a role without CREATE TRIGGER degrades to poll-only, not a boot failure).

7. remote_agent_messages - Conversation message history

   • Persists user and assistant messages with timestamps
   • Stores tool call metadata (name, input, duration) in JSONB
   • Enables message history in Web UI across page refreshes
   • Nullable user_id on user-role rows (NULL on assistant rows since the AI isn’t a user)

8. remote_agent_codebase_env_vars - Per-project env vars for workflow execution

   • Key-value pairs scoped to a codebase
   • Injected into Claude SDK subprocess environment at execution time
   • Managed via Web UI Settings panel; env: in .archon/config.yaml for CLI users

9. remote_agent_users - Archon-internal user identity

   • One row per human (or bot) across all platforms
   • Created lazily on first sight by any chat/forge adapter
   • display_name and email are nullable until enrichment succeeds
   • role (VARCHAR, default 'admin') is the identity seam for future per-resource scoping; everyone is admin today (visibility stays open), 'member' is reserved

10. remote_agent_user_identities - Platform-to-Archon user mapping

    • One row per (platform, platform_user_id) pair — Slack U-id, Telegram chat id, Discord snowflake, GitHub login, the web Better Auth user id, etc.
    • UNIQUE(platform, platform_user_id) enforces deduplication at the DB level
    • References users.id with ON DELETE CASCADE (deleting a user removes their identity mappings)
    • All user_id FKs on the four tables above use ON DELETE SET NULL so future user deletion never destructively cascades

11. remote_agent_workflow_node_sessions - Per-node provider session IDs persisted across workflow re-runs

    • Opt-in via persist_session; keyed by (workflow_name, node_id, scope_key, provider)
    • scope_key is typically the conversation UUID

12. remote_agent_user_github_tokens - Per-user GitHub device-flow tokens

    • Encrypted at rest (AES-256-GCM); one row per Archon user (UNIQUE(user_id)), cascades on user deletion
    • Numeric github_user_id anchors the commit no-reply email

13. remote_agent_user_provider_keys - Per-user AI-provider credentials (API key or OAuth subscription blob)

    • Encrypted at rest (AES-256-GCM, same TOKEN_ENCRYPTION_KEY); one row per (user_id, provider), cascades on user deletion
    • kind records api_key vs oauth; resolved + injected into the user’s runs/chat env at execution time
    • provider holds vendor-canonical credential ids (anthropic, openai, github-copilot, plus Pi backend vendors) — legacy claude/codex/copilot rows are renamed by an idempotent startup data fix (the vendor row wins when both exist)

14. remote_agent_user_ai_prefs - Per-user AI preferences (personal model tiers, @custom aliases, default assistant + default chat model)

    • NON-encrypted (model names aren’t secrets); one row per user (UNIQUE(user_id)), cascades on user deletion
    • tiers / aliases are JSON-as-TEXT; folded into model resolution as the highest-precedence layer. default_model pins the user’s direct-chat model (written atomically with default_provider; applied only when the effective chat provider matches — workflows still resolve the large tier). Resolution follows the acting user: workflow runs use the run starter; chat turns use the message sender (the conversation creator’s row is only a fallback when no sender identity resolves)
    • Editable via the console “Just me” scope, archon ai … --scope user, or /api/auth/me/ai-prefs*

15–18. remote_agent_auth_user / remote_agent_auth_session / remote_agent_auth_account / remote_agent_auth_verification - Better Auth tables for opt-in web login - PostgreSQL only. Always created on Postgres via the idempotent schema apply, but populated only when web auth is enabled (DATABASE_URL + BETTER_AUTH_SECRET) - Owned and shaped by Better Auth (text ids, camelCase columns); Archon never queries them directly — a session maps to the canonical users row via user_identities('web', <betterAuthUserId>)

Migration List

Migration	Description
000_combined.sql	Combined initial schema (use for fresh installs)
001_initial_schema.sql	Initial schema (codebases, conversations, sessions)
002_command_templates.sql	Command templates table
003_add_worktree.sql	Add worktree columns
004_worktree_sharing.sql	Worktree sharing support
005_isolation_abstraction.sql	Isolation abstraction layer
006_isolation_environments.sql	Isolation environments table
007_drop_legacy_columns.sql	Drop legacy worktree columns
008_workflow_runs.sql	Workflow runs table
009_workflow_last_activity.sql	Workflow last activity tracking
010_immutable_sessions.sql	Immutable session model
011_partial_unique_constraint.sql	Partial unique constraint
012_workflow_events.sql	Workflow events table
013_conversation_titles.sql	Conversation titles
014_message_history.sql	Message history table
015_background_dispatch.sql	Background dispatch support
016_session_ended_reason.sql	Session ended reason field
017_drop_command_templates.sql	Drop command templates table
018_fix_workflow_status_default.sql	Fix workflow status default value
019_workflow_resume_path.sql	Workflow resume path support
020_codebase_env_vars.sql	Per-project environment variables
021_add_allow_env_keys_to_codebases.sql	Allow-listed env keys per codebase
022_workflow_node_sessions.sql	Per-node provider session persistence
023_add_default_branch_to_codebases.sql	Detected default branch on codebases

The remote_agent_codebases.kind column (project 'repo' | 'folder' discriminator, commented “From migration 024”), the remote_agent_users.role column, and the four remote_agent_auth_* Better Auth tables (opt-in web login) are applied inline in 000_combined.sql rather than as numbered migrations, and converge on startup via the idempotent schema apply.