Database Conventions

7 Tables (all prefixed remote_agent_)

Table	Purpose
remote_agent_conversations	Platform conversations, soft-delete (deleted_at), title, hidden flag
remote_agent_sessions	AI SDK sessions with parent_session_id audit chain, transition_reason
remote_agent_codebases	Repository metadata, commands JSONB
remote_agent_isolation_environments	Git worktree tracking, workflow_type, workflow_id
remote_agent_workflow_runs	Execution state, working_path, last_activity_at
remote_agent_workflow_events	Step-level event log per run
remote_agent_messages	Conversation history, tool call metadata as JSONB

IDatabase Interface

Auto-detects at startup: PostgreSQL if DATABASE_URL set, SQLite (~/.archon/archon.db) otherwise.

import { pool, getDialect } from './connection';  // pool = IDatabase instance

// $1, $2 placeholders work for both PostgreSQL and SQLite
const result = await pool.query<Conversation>(
  'SELECT * FROM remote_agent_conversations WHERE id = $1',
  [id]
);
const row = result.rows[0]; // rows is readonly T[]

Use getDialect() for dialect-specific expressions: dialect.generateUuid(), dialect.now(), dialect.jsonMerge(col, paramIdx), dialect.jsonArrayContains(col, path, paramIdx), dialect.nowMinusDays(paramIdx).

Import Pattern — Namespaced Exports

// Use namespace imports for DB modules (consistent project-wide pattern)
import * as conversationDb from '@archon/core/db/conversations';
import * as sessionDb from '@archon/core/db/sessions';
import * as codebaseDb from '@archon/core/db/codebases';
import * as workflowDb from '@archon/core/db/workflows';
import * as messageDb from '@archon/core/db/messages';

INSERT Error Handling

try {
  const result = await pool.query('INSERT INTO remote_agent_conversations ...', params);
  return result.rows[0];
} catch (error) {
  log.error({ err: error, params }, 'db_insert_failed');
  throw new Error('Failed to create conversation');
}

UPDATE with rowCount Verification

updateConversation() and similar throw ConversationNotFoundError / SessionNotFoundError when rowCount === 0. Callers must handle:

try {
  await db.updateConversation(conversationId, { codebase_id: codebaseId });
} catch (error) {
  if (error instanceof ConversationNotFoundError) {
    // Handle missing conversation specifically
  }
  throw error; // Re-throw unexpected errors
}

Session Audit Trail

Sessions are immutable. Every new session links back: parent_session_id → previous session, transition_reason: TransitionTrigger. Query the chain to understand history. active = true means the current session.

Soft Delete

Conversations use soft-delete: deleted_at IS NULL filter should be included in all user-facing queries. hidden = true conversations are worker conversations (background workflows) — excluded from UI listings.

Anti-patterns

• Never SELECT * in production queries on large tables — select specific columns
• Never write raw SQL strings in application code outside packages/core/src/db/ modules
• Never bypass the IDatabase interface to call database drivers directly from other packages
• Never assume rows[0] exists without null-checking — queries can return empty arrays
• Never use RETURNING * in UPDATE when only checking success — check rowCount instead