Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Data Model

Database Schema

pam uses PostgreSQL as its single source of truth (ADR-012, overturning the original SQLite decision). Core tables follow. All IDs are UUID (gen_random_uuid() where defaulted). All timestamps are TIMESTAMPTZ (UTC). Enumeration-like columns use TEXT with CHECK constraints — the application layer (pam-core enums) remains the authority for legal values.

hosts

CREATE TABLE hosts (
    id UUID PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,  -- operator-chosen worker/host name
    platform TEXT NOT NULL CHECK(platform IN ('macos', 'linux')),
    token_fingerprint TEXT,  -- fingerprint of the worker auth token (never the token itself)
    status TEXT NOT NULL CHECK(status IN ('registered', 'online', 'offline', 'disabled')),
    instance_id UUID,  -- worker-generated, persisted in the worker's local data dir; NULL until the first instance-id-aware registration
    instance_suspect BOOLEAN NOT NULL DEFAULT false,  -- pinned instance_id disagrees with a recent/concurrent presenter (OQ-14)
    instance_flagged_at TIMESTAMPTZ,  -- when instance_suspect last transitioned false -> true; NULL while not suspect
    last_seen_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

Columns:

  • id: Unique host identifier (UUID)
  • name: Operator-chosen unique worker name (used for dispatch targeting and display)
  • platform: macos or linux — worker hosts are Unix-only (ADR-011)
  • token_fingerprint: Hash fingerprint identifying which worker token this host authenticates with
  • status: Registry state — registered (known, not yet seen), online (heartbeat current), offline (heartbeat lost), disabled (excluded from dispatch)
  • instance_id: Per-process worker instance UUID (OQ-14, 071-risks-and-open-questions.md) — generated once by the worker process and persisted in its local data dir, then sent at every registration and heartbeat. Guards against one worker token cloned onto two machines aliasing a single hosts row. Pinned, not overwritten: when an incoming instance_id disagrees with the stored value, the column is updated only when the mismatch resolves as a legitimate reinstall (see instance_suspect below) — overwriting on every mismatch would let an alternating clone erase its own trail
  • instance_suspect: Set true when an incoming instance_id disagrees with the pinned value AND the gap between now and the pinned instance’s last last_seen_at is within the offline threshold (90s) — i.e., the pinned instance still looked alive when the new one showed up, so the mismatch reads as a concurrent clone rather than a reinstall. A host in this state is excluded from new dispatch (alongside disabled) but its already-running runs are left untouched; the mismatch is journaled once on the false→true transition, not on every subsequent heartbeat (debounced) while it stays suspect. Cleared by the operator (pam host clear-suspect, 040-cli.md), which also accepts the new instance_id as the pinned value
  • instance_flagged_at: Timestamp of the instance_suspect false→true transition; NULL while not suspect. Reset to NULL when the operator clears the flag
  • last_seen_at: Last heartbeat/registration timestamp; drives online/offline transitions (default cadence: heartbeat every 30s; a host is marked offline after 90s without one — 3 missed beats). This same 90s window is the recency threshold instance_suspect compares against — when the gap since the pinned instance’s last heartbeat already exceeds it, the mismatch is read as a legitimate reinstall instead: instance_id updates silently and an info-level event is journaled, no instance_suspect flag set
  • created_at / updated_at: Record lifecycle timestamps

projects

CREATE TABLE projects (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,  -- URL-safe identifier
    description TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL CHECK(status IN ('active', 'archived')),
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

Columns:

  • id: Unique project identifier (UUIDv4)
  • name: Human-readable project name
  • slug: URL-safe identifier for CLI/API references
  • description: Optional project description
  • status: Project lifecycle state (active/archived)
  • created_at: Creation timestamp
  • updated_at: Last modification timestamp

Archived semantics: an archived project is read-only for structural mutation — no new work items may be created in it, and no new runs may be created against its work items. Runs already in flight when the project was archived continue to completion, and their events still journal. Un-archiving (archived → active) is permitted and restores full mutability.

work_items

CREATE TABLE work_items (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    parent_id UUID,  -- Self-referential FK for hierarchy
    kind TEXT NOT NULL CHECK(kind IN ('milestone', 'epic', 'task', 'subtask')),
    title TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL CHECK(status IN ('todo', 'doing', 'done', 'cancelled')),
    assignee TEXT,  -- Optional assignee identifier
    path TEXT NOT NULL,  -- Materialized path for subtree queries
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES work_items(id) ON DELETE CASCADE,
    CHECK (parent_id IS NULL OR parent_id <> id)  -- hierarchy guard: no self-parenting
);

Columns:

  • id: Unique work item identifier (UUIDv4)
  • project_id: Parent project reference
  • parent_id: Parent work item for hierarchical structure (NULL for root items)
  • kind: Work item type (milestone/epic/task/subtask)
  • title: Work item name
  • description: Optional detailed description
  • status: Current workflow state (todo/doing/done/cancelled)
  • assignee: Optional person/team assigned
  • path: Materialized path for efficient subtree queries (see Path Semantics)
  • sort_order: Ordering within siblings
  • created_at: Creation timestamp
  • updated_at: Last modification timestamp

Hierarchy guards: CHECK (parent_id IS NULL OR parent_id <> id) rejects self-parenting at the database level. Additionally, a parent and its children must share the same project_id — a documented constraint enforced at the application layer, since a column CHECK cannot compare values across rows. Reparenting is the mutation that must re-check this invariant: the new parent must belong to the same project_id as the moved item, and cross-project moves are rejected. Preventing deeper cycles (A → B → A beyond the trivial self-cycle) likewise remains an application-layer concern, tracked as OQ-6 in 071-risks-and-open-questions.md.

runs

CREATE TABLE runs (
    id UUID PRIMARY KEY,
    task_id UUID NOT NULL,
    host_id UUID,  -- Worker host assigned to execute this run (NULL while pending)
    provider TEXT NOT NULL,  -- claude, codex, grok, gemini
    session_name TEXT NOT NULL,  -- tmux session name: pam-xxxxxxxx (unique per host)
    status TEXT NOT NULL CHECK(status IN ('pending', 'spawning', 'running', 'exited', 'failed', 'stopped')),
    attempt INT NOT NULL DEFAULT 1,  -- incarnation epoch: incremented centrally on each restart requeue
    last_progress_seq BIGINT,  -- highest applied progress seq within the current attempt (NULL until first progress)
    failure_reason TEXT,  -- machine-readable terminal reason (host_lost, session_lost, spawn_failed, restart_exhausted, dispatch_timeout, no_eligible_host)
    pending_since TIMESTAMPTZ NOT NULL,  -- dispatch-timeout anchor; set to created_at at insert, reset to now() on every failed -> pending requeue
    cwd TEXT NOT NULL,  -- Working directory for CLI execution (on the worker host)
    exit_code INTEGER,  -- Process exit code (NULL if not exited)
    started_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    FOREIGN KEY (task_id) REFERENCES work_items(id) ON DELETE CASCADE,
    FOREIGN KEY (host_id) REFERENCES hosts(id),
    UNIQUE (host_id, session_name)
);

Columns:

  • id: Unique run identifier (UUID)
  • task_id: Associated task work item; must reference a work item of kind task or subtask only. Application-enforced — a column CHECK cannot read the referenced row. The central reconciliation pass treats a run pointing at a milestone or epic as a data-integrity anomaly to journal, not to auto-repair
  • host_id: Worker host executing the run; assigned at dispatch (NULL while pending)
  • session_name: tmux session name (format: pam-<short_run_id>); uniqueness is scoped per host via UNIQUE (host_id, session_name)
  • provider: LLM provider identifier (claude/codex/grok/gemini)
  • status: Run lifecycle state (pending/spawning/running/exited/failed/stopped)
  • attempt: Incarnation epoch — which execution incarnation of the run is speaking. Incremented centrally each time the restart policy requeues the run (failed → pending); workers never allocate attempts (they never write the database). The value is injected into the wrapper’s spawn environment so the wrapper can stamp it on every signal, where it is REQUIRED. A signal whose attempt is less than runs.attempt comes from a dead incarnation and is discarded — no status write, no event emission. An attempt greater than runs.attempt is impossible under central issuance and is rejected as a protocol error
  • last_progress_seq: Highest applied seq among repeated, dedup-exempt signal kinds (progress) within the current attempt (NULL until the first progress signal is applied). Rule: apply a progress signal only if seq > last_progress_seq. Reset on restart requeue — the new incarnation numbers its progress signals from scratch
  • failure_reason: Machine-readable terminal reason (host_lost, session_lost, spawn_failed, restart_exhausted, dispatch_timeout, no_eligible_host); NULL while the run is non-terminal or ended without a distinct reason
  • pending_since: Dispatch-timeout anchor (OQ-15, 071-risks-and-open-questions.md). Set equal to created_at when the row is first inserted, and reset to now() on every failed → pending requeue. Kept distinct from created_at (which never changes after insert) precisely so a requeued run gets a fresh dispatch-timeout window instead of inheriting the original creation time — a run that failed after 55 minutes and requeued would otherwise be measured against a created_at already 55 minutes stale, and would breach the 5-minute dispatch timeout the instant it re-entered pending
  • cwd: Working directory for CLI process execution (set at pam run start via --cwd flag; default: current working directory of CLI invocation)
  • exit_code: Process exit code when terminated
  • started_at: Process start timestamp
  • finished_at: Process completion timestamp
  • created_at: Record creation timestamp
  • updated_at: Last modification timestamp

UNIQUE-NULL semantics on UNIQUE (host_id, session_name): PostgreSQL treats NULLs as distinct in unique constraints, so rows with host_id IS NULL — runs still in pending, not yet dispatched to a host — never collide with each other or with host-assigned rows. The constraint binds only once a host is assigned at dispatch. This is intended, not a bug: it enforces per-host session-name uniqueness exactly when a session can actually exist (post-dispatch) without blocking concurrent pending runs.

processed_signals

Dedup marker table that gives one-shot lifecycle signals exactly-once semantics under at-least-once delivery (spool + replay):

CREATE TABLE processed_signals (
    run_id UUID NOT NULL,
    attempt INT NOT NULL,
    kind TEXT NOT NULL,
    received_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (run_id, attempt, kind),
    FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE
);

Columns:

  • run_id / attempt / kind: The dedup key — one row per applied one-shot signal of a given kind within a given incarnation
  • received_at: When the signal was first applied (forensics only; not part of matching)

Ingest semantics — the guard lives centrally, at the signal-ingest edge; the worker never queries the database, so the judgment cannot live worker-side:

  • One-shot kinds (started, finished, failed) are exactly-once via this table: ingest performs INSERT ... ON CONFLICT DO NOTHING before applying; a conflict means the signal was already applied and it is dropped. These kinds carry no seq — it would be pure cost
  • Repeated kinds (progress) are dedup-exempt: naturally repeatable, ordered instead via runs.last_progress_seq (apply only if seq > last_progress_seq). seq is optional on the signal payload and carried only by these repeated kinds
  • The stale-epoch guard and the dedup both run strictly upstream of BOTH the run-status write AND the event journal / SSE emission — skipping the status write while still journaling the event would leak phantom events to the dashboard
  • Ordering is a lexicographic comparison on (attempt, seq). The two axes are orthogonal: attempt answers “which execution incarnation is speaking”; seq answers “is this newer than what I already applied within that incarnation”. Neither subsumes the other

events

CREATE TABLE events (
    seq BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    topic TEXT NOT NULL,
    payload JSONB NOT NULL,  -- structured event data
    created_at TIMESTAMPTZ NOT NULL
);

Columns:

  • seq: Monotonically increasing sequence number
  • topic: Event type identifier (see Event Topics)
  • payload: Structured event data as JSONB
  • created_at: Event timestamp

_sqlx_migrations

Standard sqlx migration tracking table (auto-created by sqlx).

Indexes

CREATE INDEX work_items_project_id ON work_items(project_id);
CREATE INDEX work_items_parent_id ON work_items(parent_id);
CREATE INDEX work_items_path ON work_items(path text_pattern_ops);
CREATE INDEX runs_task_id ON runs(task_id);
CREATE INDEX runs_host_id ON runs(host_id);
CREATE INDEX events_topic_created_at ON events(topic, created_at);

These indexes optimize common queries:

  • Project-scoped work item lookups
  • Parent-child relationship queries
  • Path-prefix subtree scans (board rendering, subtree listing — see Path Semantics)
  • Task-associated run queries
  • Host-scoped run queries (worker reconciliation)
  • Topic-filtered event queries (SSE ?topic= filter, retention purge)

Path Semantics

The path column in work_items implements materialized path storage for efficient subtree queries:

Format: <short_id>/<parent_short_id>/<grandparent_short_id>/...

  • Root items: path = own short ID (first 8 characters of UUID)
  • Child items: path = <parent_path>/<own_short_id>
  • Short IDs: First 8 lowercase hex characters of UUID

Example:

Item A (id: a1b2c3d4-...)        path: "a1b2c3d4"
  └─ Item B (id: e5f6a7b8-...)    path: "a1b2c3d4/e5f6a7b8"
      └─ Item C (id: c9d0e1f2-...) path: "a1b2c3d4/e5f6a7b8/c9d0e1f2"

Subtree Queries: two forms exist; which one is used depends on what the traversal needs.

Path-prefix (primary):

SELECT * FROM work_items
WHERE path = ? OR path LIKE ? || '/%'
ORDER BY path, sort_order;

A single index scan on work_items_path (text_pattern_ops exists for exactly this LIKE 'prefix%' shape). This is the default for read paths — board rendering, subtree listing.

Recursive CTE:

WITH RECURSIVE subtree AS (
    SELECT * FROM work_items WHERE id = ?
    UNION ALL
    SELECT w.* FROM work_items w
    INNER JOIN subtree s ON w.parent_id = s.id
)
SELECT * FROM subtree ORDER BY path, sort_order;

Used when the traversal needs live parent links rather than the materialized path — for example during a reparent, when path is mid-rewrite.

Reparent rewrite: moving a work item rewrites path for the moved item and every descendant. This is a single transaction; the subtree is located by the old path prefix before the rewrite begins. work_item.reparented is journaled once, for the moved item — not once per descendant.

Short-id collisions: path segments and tmux session names (pam-<short_run_id>) both use the first 8 lowercase hex characters of a UUID, and an 8-hex prefix is NOT guaranteed unique. The scopes differ: runs.session_name is unique per host via UNIQUE (host_id, session_name), while path short-ids are unique only within a sibling set in practice. path is a lookup accelerator, never an identity — resolution always goes through id. Where a collision would break a display, the UI disambiguates by showing more characters.

Status Transitions

Work Item Status

stateDiagram-v2
    [*] --> todo
    todo --> doing
    doing --> done
    todo --> done: complete without starting
    done --> doing: reopen
    doing --> todo
    todo --> cancelled
    doing --> cancelled
    done --> cancelled
    cancelled --> todo: reopen
    cancelled --> [*]

Legal transitions:

  • todo → doing: Start work on item
  • doing → done: Complete work
  • todo → done: Complete without starting (work finished out-of-band); justified by the pam task done CLI verb (040-cli.md) and Kanban board drag (050-ui-dashboard.md)
  • done → doing: Reopen for additional work
  • doing → todo: Defer work
  • any → cancelled: Cancel work
  • cancelled → todo: Reopen cancelled item

Parent/child status coupling:

  • Cancelling a parent cascades cancelled to all non-done descendants; done descendants keep their status — history is not rewritten
  • A parent does NOT auto-transition to done when its children complete. Roll-up is a display concern (the dashboard may show a completion ratio); the stored parent status only changes through an explicit transition, because auto-roll-up would make the parent’s status unattributable to any actor, which conflicts with the transition-enforcement model in ### Enforcement

Run Status

stateDiagram-v2
    [*] --> pending
    pending --> spawning: spawn requested
    spawning --> running: process started
    running --> exited: clean exit
    running --> failed: error exit
    running --> stopped: user stopped
    spawning --> failed: spawn failed
    pending --> failed: dispatch timeout / no eligible host
    failed --> pending: automatic restart (bounded retries)
    exited --> [*]
    failed --> [*]
    stopped --> [*]

Legal transitions:

  • pending → spawning: Initiate tmux session creation
  • spawning → running: CLI process confirmed running
  • spawning → failed: tmux or CLI process creation failed
  • pending → failed: Dispatch-timeout failure — the only exit for a run no worker ever claims. Two causes: dispatch timeout — a run sits in pending beyond the dispatch timeout (default 5 minutes, configurable, measured from runs.pending_since — NOT created_at; see the runs schema above) without being claimed by any worker, and the central orchestrator writes failed with failure_reason = 'dispatch_timeout'; no eligible host — POST /runs accepted the run but no online, non-disabled, non-suspect host matched the requested target, so the run is created in pending and fails at the same timeout with failure_reason = 'no_eligible_host'. Host unassignment — a pending run whose target host is disabled, marked suspect (hosts.instance_suspect, OQ-14), or goes offline before the claim — does NOT immediately fail the run: it stays pending and may be claimed by another eligible host, failing only at the dispatch timeout. The 5-minute default is confirmed (OQ-15, 071-risks-and-open-questions.md)
  • running → exited: Process terminated with exit code 0
  • running → failed: Process terminated with non-zero exit code
  • running → stopped: User-initiated stop
  • failed → pending: The canonical restart-requeue edge. Performed centrally by the restart policy; on requeue, runs.attempt is incremented and the per-attempt columns (started_at, finished_at, exit_code, last_progress_seq, pending_since) are reset — pending_since resets to now() so the requeued attempt gets a fresh dispatch-timeout window (OQ-15) rather than inheriting the original created_at. A requeue MAY select a different host than the failed attempt used — it is the only automatic escape hatch when the original host is lost or unhealthy, and this includes runs tombstoned failure_reason = 'host_lost' (OQ-12, confirmed: requeue-eligible) — see 010-tmux-executor.md § Restart Policy for the accepted trade-off that decision carries. 010-tmux-executor.md owns restart POLICY only (trigger conditions, attempt caps, backoff)
  • exited/failed/stopped → [*]: Terminal states

Ownership: This document owns the run state machine as canon — the legal edges above are the single authority for run-status transitions. 010-tmux-executor.md owns restart POLICY only (trigger conditions, attempt caps, backoff) and conforms to this machine; other renderings of the graph (provider adapter transition tables, lifecycle diagrams) are derived views and must not introduce edges absent here.

Enforcement

Value legality — which values a status column may hold — is enforced by the CHECK constraints in the schema blocks above. Transition legality — which edges between those values are permitted — is enforced at the application layer by a single pam-core state-machine module, which is the sole gate: every mutating path must pass through it, including HTTP handlers, CLI commands, and signal normalization. DB-level transition enforcement is deliberately not used: a CHECK constraint cannot cheaply see the previous state (it evaluates the incoming row in isolation), so encoding a (previous, next) pair would require triggers or two-row gymnastics that buy nothing under the single-writer architecture (see Concurrency Model — all writes flow through central). The cheap DB guards that are taken are the structural ones — the work_items hierarchy CHECK and same-project constraint above — not transition rules.

Event Topics

The append-only event journal uses these topics:

Project Management:

  • project.created: New project created
  • project.updated: Project metadata modified
  • project.archived: Project marked as archived

Work Items:

  • work_item.created: New work item created
  • work_item.updated: Work item metadata modified
  • work_item.status_changed: Work item status transition
  • work_item.reparented: Work item moved to new parent
  • work_item.deleted: Work item deleted

Runs:

  • run.created: Run record created (pending status)
  • run.spawning: tmux session creation started
  • run.started: CLI process confirmed running
  • run.finished: Wrapper process exited — any exit code; journaled on every wrapper exit, without exception
  • run.failed: Reserved for the failure shapes where no wrapper exit occurred or the provider hook reported failure: spawn failure (the session never started) and a provider completion hook reporting Failed
  • run.stopped: Process stopped by user
  • run.signal_received: Signal sent to process
  • run.output_appended: New output captured (NOTE: payload contains only metadata, not full text)
  • run.lease_acquired: Interactive attach lease granted
  • run.lease_released: Interactive attach lease released or expired
  • run.orphan_detected: Unmapped tmux session discovered during reconciliation

Run topic rules (normative): every run.* payload carries the run’s attempt value alongside run_id — a consumer (SSE client, activity feed) must be able to tell which incarnation of a restarted run an event describes; without it, events from a dead incarnation are indistinguishable from the live one. run.finished and run.failed do not overlap: a non-zero exit journals run.finished with exit_code ≠ 0 and sets runs.status = failed — it does NOT additionally journal run.failed. One rule, no overlap.

Workers / Hosts:

  • worker.registered: Worker authenticated to the orchestrator (host → online)
  • worker.offline: Heartbeat lost past the offline threshold (host → offline)
  • worker.instance_suspect: A registration or heartbeat presented an instance_id that disagrees with the pinned value while the pinned instance still looked recently alive (OQ-14) — journaled once on the false→true transition, debounced while the host stays suspect
  • worker.instance_reinstalled: A registration presented a different instance_id after the pinned instance had already gone quiet past the offline threshold — read as a legitimate reinstall, info-level only, hosts.instance_id updates silently

Important: Run output text is NOT journaled to events table. Only structured metadata (line count, byte count, timestamp) is included in payloads.

Storage Policy

Hybrid Persistence Model

pam uses hybrid persistence combining traditional state tables with an append-only event journal:

Current State: Stored in normalized tables (projects, work_items, runs) for efficient queries and updates.

Event Journal: Append-only events table for:

  • Activity feed and audit trail
  • Server-Sent Events (SSE) streaming at /api/v1/events/stream
  • In-memory fan-out via tokio::sync::broadcast

Rationale: Full event sourcing was rejected to avoid complexity in replay and projection maintenance. The hybrid model provides current state query performance with event streaming capabilities.

Data Retention

Never in PostgreSQL:

  • tmux pane text and scrollback (held in the central snapshot cache in-memory + per-run on-disk logs on the worker host under its data dir runs/)
  • Large output payloads (events carry only metadata)

In PostgreSQL:

  • Current project/work item/run state
  • Worker/host registry
  • Event journal with small structured payloads (JSONB)
  • Schema migration history

Events retention: Events table rows are retained for 30 days by default. Retention period is configurable via [retention] events_days setting in the configuration file. Purge is best-effort and executed at orchestrator startup. No special events are emitted during purge; the HTTP event stream may report purged sequence ranges for client awareness.

Concurrency Model

Write Model:

  • PostgreSQL connection pool (sqlx) owned by the central orchestrator
  • Workers never connect to PostgreSQL directly — all writes flow through the central API/uplink, keeping database credentials on exactly one host
  • Short transactions; run dispatch transitions use SELECT ... FOR UPDATE on the run row to serialize dispatch claims

Read Model:

  • Multiple read connections from the same pool
  • Read-only transactions for queries

Test Support:

  • MemStore implementation for in-memory testing
  • Same Store trait interface as the PostgreSQL implementation
  • Enables fast unit tests without database I/O; a containerized PostgreSQL (ephemeral database per run) covers migration and dialect tests in CI

Entity Relationships

erDiagram
    hosts ||--o{ runs : "executes on"
    projects ||--o{ work_items : "contains"
    work_items ||--o{ work_items : "parent-child"
    work_items ||--o{ runs : "executes as"
    work_items ||--o{ events : "generates"
    runs ||--o{ events : "generates"
    projects ||--o{ events : "generates"
    runs ||--o{ processed_signals : "dedup ledger"

    hosts {
        uuid id PK
        text name UK "Human-readable host name"
        text platform "macos|linux"
        text token_fingerprint "Worker token hash — raw token never stored"
        text status "registered|online|offline|disabled"
        uuid instance_id "Worker-generated, per-process — NULL until first instance-id-aware registration (OQ-14)"
        boolean instance_suspect "Pinned instance_id disagrees with a recent/concurrent presenter (OQ-14)"
        timestamptz instance_flagged_at "instance_suspect false->true transition time; NULL while not suspect"
        timestamptz last_seen_at "Worker heartbeat"
        timestamptz created_at
        timestamptz updated_at
    }

    projects {
        uuid id PK
        text name "Human-readable name"
        text slug UK "URL-safe identifier"
        text description "Optional description"
        text status "active|archived"
        timestamptz created_at
        timestamptz updated_at
    }

    work_items {
        uuid id PK
        uuid project_id FK "References projects(id)"
        uuid parent_id FK "Self-reference for hierarchy"
        text kind "milestone|epic|task|subtask"
        text title "Item name"
        text description "Optional details"
        text status "todo|doing|done|cancelled"
        text assignee "Optional assignee"
        text path "Materialized path"
        integer sort_order "Sibling ordering"
        timestamptz created_at
        timestamptz updated_at
    }

    runs {
        uuid id PK
        uuid task_id FK "References work_items(id)"
        uuid host_id FK "References hosts(id) — NULL while pending"
        text provider "claude|codex|grok|gemini"
        text session_name "tmux session name — unique per host"
        text status "pending|spawning|running|exited|failed|stopped"
        integer attempt "Incarnation epoch — incremented on restart requeue"
        bigint last_progress_seq "Highest applied progress seq in this attempt"
        text failure_reason "host_lost|session_lost|spawn_failed|restart_exhausted|dispatch_timeout|no_eligible_host"
        text cwd "Working directory"
        integer exit_code "Process exit code"
        timestamptz pending_since "Dispatch-timeout anchor — created_at at insert, reset to now() on failed->pending requeue (OQ-15)"
        timestamptz started_at "Process start time"
        timestamptz finished_at "Process end time"
        timestamptz created_at
        timestamptz updated_at
    }

    processed_signals {
        uuid run_id PK,FK
        integer attempt PK
        text kind PK
        timestamptz received_at
    }

    events {
        bigint seq PK "GENERATED ALWAYS AS IDENTITY"
        text topic "Event type identifier"
        jsonb payload "Event data"
        timestamptz created_at
    }

The entity relationship diagram shows the key relationships and cardinalities between the five core tables, including the processed_signals dedup ledger (one row per applied one-shot signal kind per attempt, removed with its run via ON DELETE CASCADE). For complete schema definitions, see the SQL blocks above.