Skip to content

Vinix24/vnx-orchestration / skills/intelligence-engineer

intelligence-engineer

Verified

references/runtime-coordination-schema.md

View on GitHub
# runtime_coordination.db Schema Reference

Authoritative source: `schemas/runtime_coordination.sql` (v1 base) + delta migrations `schemas/runtime_coordination_v2.sql` through `schemas/runtime_coordination_v10.sql` (list the directory: `ls schemas/runtime_coordination_v*.sql`; a later file is picked up by the same loop). Apply v1 → v2 → … in order. Bootstrap with `scripts/lib/coordination_db.init_schema(state_dir)`; do not handcraft. The argument is the state directory that holds `runtime_coordination.db` (a `str` or `Path`), not a connection.

## Core tables

### dispatches

The primary record of every dispatch ever created.

```sql
CREATE TABLE dispatches (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    dispatch_id     TEXT NOT NULL,        -- canonical id, slug-style YYYYMMDD-…
    state           TEXT NOT NULL DEFAULT 'queued',
    terminal_id     TEXT,                  -- T0/T1/T2/T3 when leased
    track           TEXT,                  -- A/B/C
    priority        TEXT DEFAULT 'P2',     -- P0/P1/P2/P3
    pr_ref          TEXT,                  -- PR number when closed
    parent_dispatch TEXT,                  -- for retries / fix-forwards
    created_at      TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at      TEXT,
    closed_at       TEXT,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev',  -- ADDED migration 0010
    -- ... more columns ...
    UNIQUE (project_id, dispatch_id)        -- composite, added round-5 of P4
);
```

**State machine** (source: `DISPATCH_STATES` / `DISPATCH_TRANSITIONS` in `scripts/lib/coordination_db.py`):
```
proposed -> ready -> claimed -> delivering -> accepted -> running -> completed
queued   -> claimed                                  (queued: registered, not yet assigned)
delivering | running -> timed_out | failed_delivery
timed_out | failed_delivery -> recovered | expired | dead_letter
recovered -> queued | claimed | expired | dead_letter
```
`proposed` is the un-approved planning state, `ready` the operator-approved one. `queued` is registered and not yet assigned. `completed`, `expired` and `dead_letter` are terminal. A transition outside `DISPATCH_TRANSITIONS` raises `InvalidTransitionError`. There is no `dispatched`, `executing`, `received`, `archived`, `failed` or `escalated` dispatch state.

### dispatch_attempts

Retry attempts. One row per attempt, including the first.

```sql
CREATE TABLE dispatch_attempts (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    dispatch_id     TEXT NOT NULL,
    attempt_number  INTEGER NOT NULL,
    started_at      TEXT,
    ended_at        TEXT,
    outcome         TEXT,                  -- 'success', 'fail', 'timeout'
    error_summary   TEXT,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev'
);
```

### terminal_leases

Which terminal currently owns which dispatch.

```sql
CREATE TABLE terminal_leases (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    terminal_id     TEXT NOT NULL,         -- T0/T1/T2/T3
    state           TEXT NOT NULL DEFAULT 'idle',
    dispatch_id     TEXT,                  -- NULL when idle
    generation      INTEGER NOT NULL DEFAULT 1,
    leased_at       TEXT,
    expires_at      TEXT,                  -- lease TTL
    last_heartbeat_at TEXT,
    released_at     TEXT,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev',
    UNIQUE (project_id, terminal_id)        -- composite, P4 round-5 fix
);
```

Lease states (`LEASE_STATES` in `scripts/lib/coordination_db.py`): `idle`, `leased`, `expired`, `recovering`, `released`. Transitions: `idle → leased`, `leased → released | expired`, `expired → recovering | idle`, `recovering → idle | leased`, `released → idle`. A terminal acquires its lease before executing and releases on completion. A lease whose `expires_at` has passed becomes `expired` and the reconciler recovers it.

### coordination_events

Append-only event log. Every state transition creates a row.

```sql
CREATE TABLE coordination_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type      TEXT NOT NULL,         -- 'dispatch_created', 'lease_acquired', 'receipt_received', etc.
    entity_type     TEXT NOT NULL,         -- 'dispatch', 'pattern', 'terminal'
    entity_id       TEXT NOT NULL,
    payload         TEXT,                  -- JSON blob with event details
    created_at      TEXT DEFAULT CURRENT_TIMESTAMP,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev'
);
CREATE INDEX idx_coord_events_entity ON coordination_events(project_id, entity_type, entity_id);
```

Used for: audit trail, post-mortem analysis, replay logic.

### incident_log

Flagged incidents (cooldowns, errors, escalations).

```sql
CREATE TABLE incident_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    severity        TEXT NOT NULL,         -- 'blocking', 'warn', 'info'
    incident_type   TEXT NOT NULL,
    description     TEXT,
    related_dispatch_id TEXT,
    flagged_at      TEXT DEFAULT CURRENT_TIMESTAMP,
    resolved_at     TEXT,                  -- NULL if still open
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev'
);
```

Used for: T0 visibility into open incidents, escalation triggers.

### intelligence_injections

Record of which intelligence was injected into which session.

```sql
CREATE TABLE intelligence_injections (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      TEXT NOT NULL,
    pattern_id      TEXT,                  -- → quality_intelligence.success_patterns.pattern_id
    dispatch_id     TEXT,                  -- which dispatch triggered the injection
    scope_match     TEXT,                  -- JSON: how the scope matched
    injected_at     TEXT DEFAULT CURRENT_TIMESTAMP,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev'
);
```

Lets you analyze: "which patterns get injected most? which never do? what's the recall vs precision of injection?"

## Retry / escalation tables

### retry_budgets

Per-dispatch retry budget (e.g. "max 3 retries on chunk_timeout").

```sql
CREATE TABLE retry_budgets (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    dispatch_id     TEXT NOT NULL,
    failure_class   TEXT NOT NULL,         -- 'chunk_timeout', 'tool_use_error', etc.
    budget_remaining INTEGER NOT NULL,
    last_consumed_at TEXT,
    project_id      TEXT NOT NULL DEFAULT 'vnx-dev'
);
```

### retry_state

Current retry state per dispatch.

### escalation_log

Records when a dispatch was escalated (operator wake, manual intervention, etc).

## Schema versions (v1 → v10, then v11 in code)

Files in `schemas/`, applied in order by `init_schema` (the loop stops at the first missing `runtime_coordination_v<N>.sql`):

- **v1** (`runtime_coordination.sql`): `runtime_schema_version`, `dispatches`, `dispatch_attempts`, `terminal_leases`, `coordination_events`
- **v2**: `incident_log`, `retry_budgets`
- **v3**: `retry_state`, `escalation_log`
- **v4**: `execution_targets`, `intelligence_injections`, `inbound_inbox`, `recommendations`, `recommendation_outcomes`, plus routing columns on `dispatches` (`task_class`, `target_type`, `target_id`, `channel_origin`, `intelligence_payload`)
- **v5**: `escalation_state`, `governance_overrides`
- **v6**: `provenance_registry` (receipt, commit and PR linkage per dispatch)
- **v7**: `provenance_verifications`
- **v8**: `headless_runs` (headless run identity, heartbeat, lifecycle state)
- **v9**: `worker_states` (one row per terminal)
- **v10**: baseline for multi-tenant lease isolation: composite UNIQUE on `terminal_leases` and `dispatches`, `project_id` on `worker_states`. Its `project_id` indexes are created by migrations 0010 and 0017, which run after `init_schema`
- **v11** (no SQL file): `_migrate_v11_composite_keys` in `coordination_db.py` creates the ADR-007 `ux_<table>_pid` unique indexes over `project_id`, only when `project_id` is already present. Check which are missing with `composite_index_audit()`

`init_schema` records progress in `PRAGMA user_version` (v1 = 1, each file +1). Call it with the state directory: `coordination_db.init_schema(state_dir)`. **Don't run only a single delta file**: v2 to v10 assume the earlier tables exist, and you will miss the rest.

## Migration 0010 / 0015 layered on top

`schemas/migrations/0010_add_project_id.sql` adds `project_id TEXT NOT NULL DEFAULT 'vnx-dev'` to: dispatches, dispatch_attempts, terminal_leases, coordination_events, incident_log, intelligence_injections.

`schemas/migrations/0015_complete_project_id.sql` extends to: retry_budgets, retry_state, escalation_log, execution_targets, inbound_inbox, recommendations, recommendation_outcomes (and many QI tables).

**Composite keys:** v10 (`terminal_leases`, `dispatches`) and the v11 `ux_<table>_pid` indexes put `project_id` into the uniqueness of the tables in `_RC_COMPOSITE_KEYS`. The base v1 file still declares `dispatch_id` and `terminal_id` as single-column UNIQUE.

## Common gotchas

- **Every `runtime_coordination_v<N>.sql` is a delta, not a full schema**: running one alone produces an incomplete DB. Use `coordination_db.init_schema(state_dir)` always.
- **`dispatch_id` is unique within a project, NOT globally.** The composite UNIQUE on `(project_id, dispatch_id)` was added in P4 round-5; older code that assumes globally-unique dispatch_id will break for cross-project central queries.
- **`coordination_events.entity_id`** can reference dispatch_id, pattern_id, etc. For cross-project queries, use prefix-rewrite (`<project_id>:<entity_id>`) when entity_type matches a project-scoped table.
- **`dispatches.parent_dispatch`** is also project-scoped. Walking the chain across projects requires prefix-rewriting.
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall