Archon Database Schema: Unified Design for SQLite and PostgreSQL Integrations

Archon utilizes a single relational database schema that operates identically on both SQLite (file-based default) and PostgreSQL (server-based), with migrations stored in migrations/ and JSON data stored as TEXT in SQLite versus native JSONB in PostgreSQL.

Archon is an open-source AI agent framework that persists conversation history, workflow runs, and codebase metadata using a unified database schema. Whether running locally with SQLite or scaling with PostgreSQL, the application relies on the same table definitions located in the migrations/ directory, ensuring consistent data structures across both storage engines.

Core Tables and Relationships

The schema centers around AI agent operations, tracking everything from registered codebases to individual workflow steps. All tables use UUID primary keys and TIMESTAMP fields for auditing, with foreign key relationships maintaining referential integrity between conversations, sessions, and workflow runs.

Infrastructure and Codebase Tables

• remote_agent_codebases stores registered repositories with columns id (UUID PK), path (TEXT), and created_at (TIMESTAMP).
• remote_agent_codebase_env_vars captures environment variable snapshots for security gating, containing codebase_id (UUID FK), key (TEXT), and value (TEXT).

Conversation and Session Management

• remote_agent_conversations tracks chat threads per platform with codebase_id (UUID FK), platform (TEXT), and thread_id (TEXT).
• remote_agent_sessions links AI assistant sessions to conversations via conversation_id (UUID FK) and includes an active BOOLEAN flag.
• remote_agent_messages stores the actual message history with role (TEXT), content (TEXT), and metadata (JSONB/TEXT).

Workflow and Isolation Tracking

• remote_agent_workflow_runs records workflow executions with workflow_name (TEXT), status (TEXT), and timestamp fields.
• remote_agent_workflow_events provides granular step-level logging using step_index (INT), step_name (TEXT), and data (JSONB/TEXT).
• remote_agent_isolation_environments manages Git worktree isolation with branch_name (TEXT) and status (TEXT).
• remote_agent_command_templates (legacy) optionally stores command templates with name (TEXT) and template (TEXT).

Migration Files and Schema Evolution

The database schema is defined through sequential SQL migration scripts. Each file contains DDL statements that create tables identically for both SQLite and PostgreSQL engines.

The initial schema creation resides in migrations/001_initial_schema.sql, establishing the foundational remote_agent_codebases, remote_agent_conversations, and remote_agent_sessions tables. Workflow persistence comes from migrations/008_workflow_runs.sql and migrations/012_workflow_events.sql, while conversation history moved to migrations/014_message_history.sql with the remote_agent_messages table. Environment variable security features arrived in migrations/020_codebase_env_vars.sql, creating the remote_agent_codebase_env_vars table for the "env-leak gate" functionality.

Runtime Database Selection and Adapters

Archon determines the active database engine at startup by checking for the DATABASE_URL environment variable. As implemented in packages/core/src/db/connection.ts, the getDatabaseType() function returns 'postgresql' when DATABASE_URL is present, otherwise defaulting to 'sqlite'.

// packages/core/src/db/connection.ts
import { SqliteAdapter, sqliteDialect } from './adapters/sqlite';
import { PostgresAdapter, postgresDialect } from './adapters/postgres';

export function getDatabaseType(): 'postgresql' | 'sqlite' {
  return process.env.DATABASE_URL ? 'postgresql' : 'sqlite';
}

Both adapters implement the IAdapter interface, exposing query<T>(), transaction(), and runMigrations() methods. This abstraction allows the workflow store and session handlers to remain agnostic to the underlying engine, whether using SqliteAdapter from packages/core/src/db/adapters/sqlite.ts or PostgresAdapter from packages/core/src/db/adapters/postgres.ts.

SQLite vs PostgreSQL Implementation Differences

While the schema DDL remains identical, the adapters handle engine-specific nuances for JSON storage, UUID generation, and timestamp functions.

• JSON Storage: SQLite stores JSON columns as TEXT strings, whereas PostgreSQL uses native JSONB types for efficient querying and indexing.
• UUID Generation: The SQLite dialect generates UUIDs using LOWER(HEX(RANDOMBLOB(16))), while PostgreSQL invokes gen_random_uuid().
• Timestamp Functions: SQLite uses datetime('now') for current timestamps, compared to PostgreSQL's NOW() function.
• Conflict Resolution: SQLite employs INSERT OR REPLACE syntax for upserts, while PostgreSQL utilizes ON CONFLICT ... DO UPDATE clauses.

Practical Connection Examples

Establishing a database connection requires importing getDatabase from the connection module and invoking runMigrations() to ensure the schema exists.

For SQLite (default):

import { getDatabase } from '@archon/core/src/db/connection';

// No DATABASE_URL set → SQLite selected
const db = await getDatabase();
await db.runMigrations(); // Applies migrations/001_initial_schema.sql, etc.

For PostgreSQL:

process.env.DATABASE_URL = 'postgresql://user:pass@localhost:5432/archon';
import { getDatabase } from '@archon/core/src/db/connection';

const db = await getDatabase(); // Returns PostgresAdapter instance
await db.runMigrations(); // Same SQL files, dialect-adjusted execution

Querying data works identically across both engines. For example, retrieving recent workflow runs:

const rows = await db.query<{
  id: string;
  workflow_name: string;
  status: string
}>(`
  SELECT id, workflow_name, status
  FROM remote_agent_workflow_runs
  ORDER BY started_at DESC
  LIMIT 10
`);

Inserting records with JSON metadata functions on both adapters:

await db.query(`
  INSERT INTO remote_agent_messages 
    (conversation_id, role, content, metadata, created_at)
  VALUES ($1, $2, $3, $4, NOW())
`, [conversationId, 'assistant', 'Hello!', { tokens: 42 }]);

Summary

• Archon uses a unified database schema defined in migrations/ that runs unchanged on both SQLite and PostgreSQL.
• Core tables include remote_agent_codebases, remote_agent_conversations, remote_agent_sessions, remote_agent_workflow_runs, remote_agent_workflow_events, and remote_agent_messages.
• The DATABASE_URL environment variable determines whether SqliteAdapter or PostgresAdapter is instantiated from packages/core/src/db/connection.ts.
• JSON data stores as TEXT in SQLite and JSONB in PostgreSQL, with dialect-specific handling for UUIDs and timestamps.
• Migration files such as 001_initial_schema.sql, 008_workflow_runs.sql, and 014_message_history.sql create tables automatically on first startup.

Frequently Asked Questions

Can I switch from SQLite to PostgreSQL without changing the application code?

Yes. The schema DDL is identical for both engines, and the getDatabase() function in packages/core/src/db/connection.ts automatically selects the appropriate adapter based on the DATABASE_URL environment variable. Simply set DATABASE_URL to your PostgreSQL connection string, and the same migration files will create the tables in PostgreSQL without requiring schema modifications.

How does Archon handle JSON data differently between SQLite and PostgreSQL?

According to the adapter implementations, SQLite stores JSON columns as TEXT strings containing serialized JSON, while PostgreSQL utilizes native JSONB columns. The dialect adapters in packages/core/src/db/adapters/sqlite.ts and packages/core/src/db/adapters/postgres.ts abstract these differences, allowing the same INSERT and SELECT queries to work on both engines.

Where are the database tables defined in the Archon source code?

Table definitions reside in the migrations/ directory. The initial schema in migrations/001_initial_schema.sql creates the core infrastructure tables, while subsequent files like migrations/008_workflow_runs.sql, migrations/012_workflow_events.sql, and migrations/014_message_history.sql add workflow tracking and message history capabilities. These SQL files execute through the runMigrations() method available on both database adapters.

Does the schema support foreign key constraints between tables?

Yes. The schema defines foreign key relationships such as codebase_id references in remote_agent_conversations and conversation_id references in remote_agent_sessions and remote_agent_messages. These constraints maintain referential integrity across the codebase, conversation, and workflow run tables, regardless of whether running on SQLite or PostgreSQL.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}