Architecture

How the parts fit together: database design, transaction model, indexing strategy, and the design decisions behind 50-agent coordination on a single SQLite file.

Database schema

Five tables. One sequence counter. Four B-tree indexes. The schema is embedded in the binary and auto-applied on open.

schema
-- Task sequence (single-row: INSERT OR IGNORE prevents duplicates across init calls)
CREATE TABLE task_seq (
    id      INTEGER PRIMARY KEY CHECK (id = 1),
    next_id INTEGER NOT NULL DEFAULT 0
);

-- Tasks: the coordination state
CREATE TABLE tasks (
    id             TEXT PRIMARY KEY,      -- 'TASK-101'
    title          TEXT NOT NULL,
    status         TEXT CHECK(status IN
                   ('TODO','IN_PROGRESS','BLOCKED','IN_REVIEW','DONE')),
    role_boundary  TEXT NOT NULL,         -- 'worker' | 'reviewer' | ...
    project        TEXT NOT NULL DEFAULT 'default',
    priority       INTEGER NOT NULL DEFAULT 100, -- lower = more urgent
    assigned_agent TEXT,                  -- current lease holder (nullable)
    lease_until    DATETIME,             -- nullable when unclaimed
    claimed_by     TEXT,                  -- immutable snapshot of first claimer
    depends_on     TEXT,                  -- comma-separated dependency IDs
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Notes: progress logs and review feedback
CREATE TABLE notes (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id    TEXT NOT NULL REFERENCES tasks(id),
    author     TEXT NOT NULL,
    note_type  TEXT,                      -- PROGRESS|ERROR|DECISION|REVIEW|SYSTEM
    content    TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- History: audit trail of state transitions
CREATE TABLE history (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id    TEXT NOT NULL REFERENCES tasks(id),
    agent      TEXT NOT NULL,
    action     TEXT NOT NULL,              -- DISPATCH|CLAIM|BLOCK|COMPLETE|REVIEW
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Events: append-only log with TTL
CREATE TABLE events (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    event_type  TEXT NOT NULL,
    payload     TEXT NOT NULL,
    ttl_seconds INTEGER DEFAULT 259200   -- 3 days; NULL = never expires
);

Indexes

indexes
-- Primary claim index: role + status + priority + lease
CREATE INDEX idx_tasks_claim
    ON tasks(role_boundary, status, priority, created_at, lease_until);

-- Project-filtered claim index
CREATE INDEX idx_tasks_claim_project
    ON tasks(role_boundary, project, status, priority, created_at, lease_until);

-- Lease expiry query (used by claim-next for reclaim)
CREATE INDEX idx_tasks_lease
    ON tasks(status, lease_until);

-- Project rollups
CREATE INDEX idx_tasks_project
    ON tasks(project, status, priority);

WAL mode and persistence

The database opens with PRAGMA journal_mode = WAL. This allows concurrent reads while a write is in progress — readers see the last committed checkpoint, writers append to the WAL. The WAL checkpoint runs automatically every 1000 pages:

Connection model

Two connections: one writer, one reader pool.

Transaction isolation

All state mutations use Serializable isolation (sql.TxOptions{Isolation: sql.LevelSerializable}). This prevents phantom reads — two concurrent claim-next calls cannot both receive the same task. The SQLite layer enforces this with its internal lock escalation:

  1. claim-next opens a Serializable transaction.
  2. SELECT candidates — reads pending TODO and expired IN_PROGRESS tasks.
  3. UPDATE to assign the agent — the WHERE id = ? AND status IN ('TODO', 'IN_PROGRESS') guard ensures only unclaimed tasks are updated.
  4. If RowsAffected is 0, another writer claimed it first. The loop continues to the next candidate.
  5. Commit releases the serialization lock.
double-claim prevention
# Agent A and Agent B call claim-next simultaneously
Agent A: BEGIN SERIALIZABLE
Agent A: SELECT ... WHERE status = 'TODO' ORDER BY priority LIMIT 1
Agent A: UPDATE tasks SET ... WHERE id = 'TASK-1' AND status = 'TODO'
Agent A: COMMIT

Agent B: BEGIN SERIALIZABLE
Agent B: SELECT ... WHERE status = 'TODO' ORDER BY priority LIMIT 1
Agent B: → TASK-1 is already IN_PROGRESS, not in candidate set
Agent B: → gets TASK-2

Migration strategy

Schema migrations run on every Open() call, automatically. Each migration checks for the column's existence via pragma_table_info and applies ALTER TABLE ADD COLUMN if missing. This means:

migration pattern
var hasProject bool
_ = db.QueryRow(
    `SELECT COUNT(*) > 0 FROM pragma_table_info('tasks') WHERE name = 'project'`
).Scan(&hasProject)
if !hasProject {
    db.Exec("ALTER TABLE tasks ADD COLUMN project TEXT NOT NULL DEFAULT 'default'")
}

Contention model

At 3–10 agents, contention is negligible. The retry loop (100ms, 200ms, 400ms base with jitter) handles transient SQLITE_BUSY. At 50+ agents, claim-next becomes the bottleneck — every agent calling it competes for the Serializable write lock during the SELECT-UPDATE window.

What breaks first at scale: not the database rows, but the write serialization. SQLite is still correct (no double-claims), but latency increases as more agents queue for the write lock. Past 1000 agents, the WAL checkpoint process itself becomes a bottleneck. At that point, switch to Postgres or a distributed queue.

Production notes