# Archon Database Schema: Unified Design for SQLite and PostgreSQL Integrations

> Discover Archon's unified database schema for seamless SQLite and PostgreSQL integrations. Learn about its flexible design and migration management.

- Repository: [Cole Medin/Archon](https://github.com/coleam00/Archon)
- Tags: architecture
- Published: 2026-04-10

---

**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`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/migrations/008_workflow_runs.sql) and [`migrations/012_workflow_events.sql`](https://github.com/coleam00/Archon/blob/main/migrations/012_workflow_events.sql), while conversation history moved to [`migrations/014_message_history.sql`](https://github.com/coleam00/Archon/blob/main/migrations/014_message_history.sql) with the `remote_agent_messages` table. Environment variable security features arrived in [`migrations/020_codebase_env_vars.sql`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/packages/core/src/db/connection.ts), the `getDatabaseType()` function returns `'postgresql'` when `DATABASE_URL` is present, otherwise defaulting to `'sqlite'`.

```typescript
// 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`](https://github.com/coleam00/Archon/blob/main/packages/core/src/db/adapters/sqlite.ts) or `PostgresAdapter` from [`packages/core/src/db/adapters/postgres.ts`](https://github.com/coleam00/Archon/blob/main/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):

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/001_initial_schema.sql), [`008_workflow_runs.sql`](https://github.com/coleam00/Archon/blob/main/008_workflow_runs.sql), and [`014_message_history.sql`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/packages/core/src/db/adapters/sqlite.ts) and [`packages/core/src/db/adapters/postgres.ts`](https://github.com/coleam00/Archon/blob/main/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`](https://github.com/coleam00/Archon/blob/main/migrations/001_initial_schema.sql) creates the core infrastructure tables, while subsequent files like [`migrations/008_workflow_runs.sql`](https://github.com/coleam00/Archon/blob/main/migrations/008_workflow_runs.sql), [`migrations/012_workflow_events.sql`](https://github.com/coleam00/Archon/blob/main/migrations/012_workflow_events.sql), and [`migrations/014_message_history.sql`](https://github.com/coleam00/Archon/blob/main/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.