agenthropic

Data model

This page is the annotated SQLite schema reference for agenthropic: the append-only events_raw substrate that every hook payload and every JSONL line lands in, the deterministic events → sessions/agents/orchestration_edges/token_usage projection built on top of it, model_pricing, and the Phase 5 alert/webhook tables grafted from hoangsonww. The key takeaway: the schema is a one-way pipeline, not a two-store merge — both ingestion sources write into one immutable, idempotency-keyed log, and everything queryable (the subagent tree, the DAG, cost) is a pure, replayable projection over that log, computed once at projection time rather than reconciled ad hoc on every read (concept-analysis-v2 CD-2). Only one table’s DDL is fixed verbatim by the design basis today — agents (DESIGN §4). Every other table below is a reference schema synthesized from the documented column-level decisions in CD-4 and development-plan.md Track D/C/A; the literal migrations land in WP-D4…WP-D10, WP-C1, and WP-A2, none of which are written yet — the project is still in its pre-code bootstrap phase. Column names for raw token counts and price rates are illustrative pending those migrations; every bucket dimension, constraint, and invariant named in the tables is sourced.

Update — 2026-07 (as built). The paragraph above described the pre-code state. Implementation began 2026-07-11, and the schema is now real: twenty ordered, idempotent, in-code migrations in apps/server/src/db/migrations.ts (thirteen when this note was first written; the ledger table below is current as of 2026-09-26), each applied inside a transaction that also records its id, name and a sha-256 content checksum in the runner’s own schema_version table (running the runner twice applies nothing). The SQL blocks on this page have been replaced with the actual migration DDL; the original synthesized sketches are kept only where they document design rationale, clearly marked. Two structural differences from the design narrative matter throughout:

Table inventory

Table Layer Status Purpose Primary source
events_raw Substrate Built (migration 1) Immutable, idempotency-keyed landing zone — as built, for hook deliveries only (JSONL never lands here) CD-2, CD-4, WP-D4
events Normalized Built (migration 3) As built: the hook liveness timeline — identifiers only, FK-linked to events_raw, written in the same transaction CD-4, WP-D5
sessions Projection Built (migration 2) One row per Claude Code session WP-D6
agents Projection Built (migration 4; outcome_cause added by 17) Self-referential subagent tree — a data fact, not a UI reconstruction; as built the status CHECK carries five values incl. 'unknown', and a nullable six-value outcome_cause says why an agent ended DESIGN §4, WP-D6, WP-U13
orchestration_edges Projection (moat) Built (migration 5; rebuilt by migration 13, indexed by 12) Persisted, per-instance parent→child edges; the source every tree/DAG view queries; as built derived from JSONL via five provenance-tagged join paths DESIGN §4/§6, CD-4, WP-D7, WP-IN8
token_usage Projection Built (migration 6; attribution repaired by 8, indexed by 10, occurred_at canonicalized and guarded by 15) Ground-truth token rows — as built one row per (message_id, bucket) over five priced buckets; never pruned DESIGN §4, CD-3/CD-4, WP-D8
token_usage_rollup Derived (work already done) Built (migration 16) Persisted rollup of token_usage at grain (session_id, model, bucket, day, rate_effective_from) — tokens and the resolved pricing row, never dollars — kept exact by triggers on both token_usage and model_pricing; the table GET /api/cost/summary reads; equal to a direct grouped scan by construction and pinned to it by an equivalence suite review M-19, WP-U4
model_pricing Reference Built (migration 7, converged by 11, effective_from canonicalized and guarded by 14, ten rows for two models added by 18; PROVISIONAL seed) Versioned per-token rates, dated, per (model, bucket) CD-4, WP-C1
ingest_checkpoints Operational cache Built (migration 9) Opt-in durable replay memory: which sessions’ bytes have not moved since the last run. A cache of work already done — never dashboard truth WP-IN10
schema_version Runner bookkeeping Built (the runner itself) One row per applied migration: id, name, applied-at, sha-256 content checksum WP-D3
alert_rules Alerting (post-1.0, KC-5 gated) Designed, not built Operator-defined trigger conditions DESIGN §4/§7, WP-A2
alert_events Alerting (post-1.0, KC-5 gated) Designed, not built Fired-alert log DESIGN §4/§7, WP-A2
webhook_targets Alerting (post-1.0, KC-5 gated) Designed, not built Outbound delivery targets (Telegram, etc.), secret held by reference only DESIGN §4/§7, WP-A2, WP-A3
webhook_deliveries Alerting (post-1.0, KC-5 gated) Designed, not built Delivery attempts with retry/backoff DESIGN §4/§7, WP-A2, WP-A7

Not in this inventory: projects and filters. DESIGN §4 names them as part of the simple10-derived base schema alongside sessions/agents/events: “Start from simple10’s clean normalised base (projects, sessions, agents, events, filters + disciplined migration tables)…” No source document — not DESIGN.md, not development-plan.md’s Track D catalog — gives either table a column-level shape, a purpose beyond that one-line mention, or an owning work package, so neither is modeled here. Tracked as an open gap in the table below, not invented.

“Phase 5” follows the canonical, adversarially-verified phase table in development-plan.md §3 (Track A, Phase 5–6). Note: DESIGN.md’s own, earlier roadmap sketch (§9) labels the same Telegram/alert work “Phase 2” — the development plan supersedes it as the reconciled schedule; this page follows the development plan.

Migrations and the schema_version ledger

Every table below is created by a numbered migration in apps/server/src/db/migrations.ts. There is no .sql directory and no external migration tool: a migration is an { id, name, up(db) } record, the list is asserted to be ordered at startup, and the runner applies each pending up() inside its own transaction together with the row that records it. A second run applies nothing and leaves the schema byte-identical.

# Name What it does
1 events-raw-append-only events_raw + the two ABORT triggers
2 sessions sessions
3 events events + idx_events_session_id
4 agents-self-referential agents (five-value status CHECK) + two indexes
5 orchestration-edges orchestration_edges + idx_orchestration_edges_session_id
6 token-usage token_usage + two indexes
7 model-pricing-with-seed model_pricing + the PROVISIONAL rate seed
8 token-usage-main-agent-attribution Data repair: attribute main-transcript usage rows written before the writer did it
9 ingest-checkpoints ingest_checkpoints (WITHOUT ROWID)
10 retention-scan-indexes (occurred_at, id) indexes on events and token_usage
11 model-pricing-seed-convergence Data repair: converge databases that ran migration 7 before its seed was edited
12 orchestration-edge-endpoint-indexes parent_agent_id / child_agent_id indexes on the edge table
13 orchestration-edges-legacy-explore-source Rebuilds the edge table to admit the fifth source value, legacy_explore
14 model-pricing-canonical-effective-from Rewrites every model_pricing.effective_from to the one canonical spelling YYYY-MM-DDTHH:mm:ss.sssZ, then installs a guard/canonicalize trigger pair so no other spelling can be stored again
15 token-usage-canonical-occurred-at The same for token_usage.occurred_at — the other operand of the dated-rate comparison; the rewrite halts on an unparseable value rather than guessing
16 token-usage-rollup token_usage_rollup (WITHOUT ROWID) + its index, seeded from token_usage, then six triggers that keep it exact under every mutation of token_usage or model_pricing
17 agents-outcome-cause ALTER TABLE agents ADD COLUMN outcome_cause, nullable, with an inline six-value CHECK
18 model-pricing-opus-5-fable-5-1 Ten explicit rate rows — all five buckets for each of the two model ids the real corpus exposed as unpriced
19 model-pricing-opus-5-5 Five explicit rate rows for claude-opus-5-5, the next model id the real corpus exposed as unpriced
20 model-pricing-sonnet-5-official Rewrites the five seeded claude-sonnet-5 floor rows to the official 2 / 10 rate (decision D10); adds no row

Three properties of that list are worth stating explicitly, because they are the reason the ledger table exists at all.

An applied migration is immutable, and the database can prove it. Each recorded row carries a sha-256 over the migration’s own up() source plus the frozen pricing constants it closes over. Before applying anything, the runner recomputes every checksum and throws if one no longer matches — naming the migration and telling the operator to restore its original content and ship the change as a new migration. This is not defensive decoration: migration 7’s seed was edited in place after operator databases had applied it, the runner skipped it by id, and every real message then failed the pricing halt gate. Migration 11 exists to repair exactly that, and the checksum exists so it cannot recur silently.

Legacy databases are upgraded, not rejected. CREATE TABLE IF NOT EXISTS never alters an existing shape, so a database migrated before checksums existed keeps the three-column schema_version; the runner ALTERs the checksum column in and leaves it nullable. NULL there means precisely “applied before checksums existed” — the runner cannot prove what content actually ran, so it does the only honest thing available: it backfills the current checksum once (trust-on-first-verify) and makes every future edit loud. It does not pretend the past was verified.

Data-repair migrations are first-class. Migrations 8 and 11 write no DDL at all. They exist because a schema that only ever adds tables cannot fix a database that already holds rows written under an older understanding — and re-ingesting is not always available, since the transcripts behind an old session may no longer be on disk.

A rewrite is only half a repair; the other half is a trigger (migrations 14 and 15, added 2026-09). The dated-rate comparison effective_from <= occurred_at runs as a BINARY-collated text comparison in the API’s priced CTE, while the ingest-side halt gate compares epoch milliseconds; the two agreed only while the stored text sorted chronologically, and mixed spellings of one instant (…T00:00:00Z beside …T00:00:00.000Z, the form Claude Code JSONL actually writes) made the API silently bill a rate the gate never approved. Each of the two migrations rewrites every stored value to one spelling and installs a BEFORE guard that rejects anything that is not a bare UTC date or a zoned ISO-8601 instant plus an AFTER trigger that canonicalizes what the guard admitted — so the repair cannot be undone by the next INSERT, whether it comes from the writer or from an operator at the sqlite3 prompt.

The one-way pipeline: raw → normalized → projected

CD-2 (concept-analysis-v2.md §3) states the design in one sentence: “Both sources write into append-only, idempotency-keyed events_raw; sessions/agents/ orchestration_edges/token_usage are a pure replayable projection over it.” Concretely, three stages, each owned by a distinct work package:

┌────────────────────────────┐        ┌──────────────────────────────┐
│   Claude Code hooks (HTTP)  │        │  ~/.claude/projects/*.jsonl  │
│   via HookSource port       │        │  via TokenReader/TokenSource │
└──────────────┬──────────────┘        └───────────────┬──────────────┘
               │                                        │
               └───────────────────┬────────────────────┘
                                    ▼
                     ┌──────────────────────────┐
                     │        events_raw        │   append-only, idempotency-keyed
                     │        (immutable)        │   WP-D4
                     └─────────────┬─────────────┘
                                    │  Normalizer — pure, deterministic (WP-IN6)
                                    ▼
                     ┌──────────────────────────┐
                     │          events           │   normalized, queryable
                     │   raw_event_id FK (WP-D5) │
                     └─────────────┬─────────────┘
                                    │  Projection — precedence-aware (WP-IN7)
                                    ▼
        ┌───────────┬────────────────────┬───────────────────────┬───────────────┐
        │ sessions  │       agents        │  orchestration_edges   │  token_usage  │
        │  (WP-D6)  │  self-ref (WP-D6)   │   the moat (WP-D7)     │   (WP-D8)     │
        └───────────┴────────────────────┴───────────────────────┴───────────────┘

Two consequences follow directly from this shape:

  1. Reconciliation happens once, at projection time, per field — never at query time. CD-2’s own rule: “Reconciliation is per-field precedence at projection time, not a two-store merge at query time.” A read path (the API, the realtime hub, the webhook sink) never has to decide “hook value or JSONL value?” — the projection already decided, deterministically, and wrote one row. Full precedence rules (which source wins per field, and why) are the subject of ingest & reconciliation, not this page.
  2. Replay is the correctness contract, not an optimization. Because events → sessions/agents/orchestration_edges/token_usage is a pure function of the immutable log, WP-IN10’s acceptance test is that double-replay produces a byte-identical projected database, and a kill-and-restart mid-session must reconstruct identical state with zero loss (concept-analysis-v2 §6). This is also why the schema below never lets a projection table’s write path be the row of record — events_raw is.

As built: the diagram above is the design record. In the running system the JSONL leg bypasses events_raw entirely: the pure parser reconstructs the whole session from the transcript, cost is computed as a halt-gate, and one transaction writes sessions/agents/orchestration_edges/token_usage directly. events_raw → events exists exactly as drawn — but only for the hook leg, as a liveness timeline. Both consequences survive in different clothes: reconciliation is still decided at write time, never at query time (hooks simply never write structure at all), and replay is still the correctness contract — re-ingesting an unchanged corpus is provably a no-op because the parse is pure and every write is an upsert/INSERT OR IGNORE (the double-replay P0 test). For JSONL, the transcript file itself is the immutable row of record — Claude Code owns it, and the projections can always be rebuilt from it alone.

events_raw — the append-only substrate

The design intent (DESIGN §3; CD-2) was that both ingestion sources write every fact they see into this one table before anything is interpreted. As built, only the hook receiver does — JSONL is projected directly (see the pipeline note above) — but the table’s own contract shipped intact. It accepts any event_type, including ones the system has never seen, so a new or unrecognized Claude Code hook is preserved rather than dropped or crashing the ingest path (WP-IN3: “accept-any-event… Never-seen event_type → 202 + a row lands (audit-preserving)”).

The real DDL (migration 1, events-raw-append-only; WAL mode and FK enforcement are asserted on every connection open by the WP-D2 connection module):

CREATE TABLE events_raw (
  id              INTEGER PRIMARY KEY,
  idempotency_key TEXT NOT NULL UNIQUE,
  source          TEXT NOT NULL CHECK (source IN ('hook','jsonl')),
  event_type      TEXT NOT NULL,
  payload         TEXT NOT NULL,
  received_at     TEXT NOT NULL
);
CREATE TRIGGER events_raw_no_update
BEFORE UPDATE ON events_raw
BEGIN
  SELECT RAISE(ABORT, 'events_raw is append-only');
END;
CREATE TRIGGER events_raw_no_delete
BEFORE DELETE ON events_raw
BEGIN
  SELECT RAISE(ABORT, 'events_raw is append-only');
END;

Rationale, per column/constraint — updated to the as-built facts:

Open tension in the sources, not resolved here. WP-D10 also names a “retention TTL sweeper,” and CD-10 requires “retention TTL… from Phase 1.” Neither DESIGN.md nor development-plan.md states how a TTL sweeper’s eventual row removal is reconciled with the same table’s “no UPDATE/DELETE path (enforced by test)” acceptance criterion — e.g. whether the sweeper targets only the normalized/projected layer, uses an archive-and- truncate strategy, or is a documented, narrowly-scoped exception to the trigger above. Tracked as an open issue.

(As built, the mechanism answers the tension without resolving the decision. events_raw sits on a hard protected list the pruner refuses to touch, alongside sessions, agents, orchestration_edges, model_pricing and schema_version — so the append-only trigger is never contradicted, and only events and token_usage are prunable at all. The archive-and-truncate option is declared and rejected loudly: configuring rawEvents: 'archive-segments' throws, naming itself as the recommended but unimplemented resolution of OPEN-1, rather than silently degrading to keep-forever. The library default NO_RETENTION deletes nothing, ever; the *server has run under the signed v1.0 policy since 2026-09-10 — events at 90 days, token_usage never, backup files at 30 days behind a floor of 7 — so WP-D10 is done; see the closing note under What’s decided vs. open and backup & restore.)*

events — the hook liveness timeline

The design called this the output of a pure Normalizer stage (WP-IN6). As built there is no separate Normalizer — events is the hook liveness projection (WP-D5): when (and only when) a hook envelope actually lands in events_raw, one normalized row is written here in the same transaction, pointing back at the raw row. A duplicate delivery inserts zero rows in both tables. Only identifiers are projected — never the payload body — so no secrets and no free text leave events_raw.

The real DDL (migration 3, events):

CREATE TABLE events (
  id           INTEGER PRIMARY KEY,
  raw_event_id INTEGER NOT NULL REFERENCES events_raw(id),
  session_id   TEXT,
  agent_id     TEXT,
  event_type   TEXT,
  occurred_at  TEXT
);
CREATE INDEX idx_events_session_id ON events(session_id);

sessions

WP-D6 groups sessions and agents as the two self-contained “projection tables” of the hierarchy layer. The column-level shape was an open issue when this page was written; it is now fixed by the real migration (migration 2, sessions):

CREATE TABLE sessions (
  id               TEXT PRIMARY KEY,
  project_slug     TEXT,
  started_at       TEXT,
  last_activity_at TEXT,
  status           TEXT
);

The primary key is the session UUID, never the project slug — the parser spec (§6.2) requires that two concurrent sessions in the same project directory stay two distinct roots. Rows are upserted whole by the per-session ingest transaction.

agents — the self-referential subagent tree

The design basis fixed this table’s DDL verbatim (DESIGN §4):

-- DESIGN §4 (the design-basis sketch, kept for the record):
CREATE TABLE agents (
  id              TEXT PRIMARY KEY,
  session_id      TEXT NOT NULL,
  type            TEXT CHECK(type IN ('main','subagent')),
  subagent_type   TEXT,
  status          TEXT CHECK(status IN ('working','waiting','completed','error')),
  parent_agent_id TEXT,          -- self-ref: builds the subagent tree
  FOREIGN KEY (parent_agent_id) REFERENCES agents(id) ON DELETE SET NULL
);

The real DDL (migration 4, agents-self-referential) keeps that shape and extends it in exactly the ways the later acceptance criteria demanded:

CREATE TABLE agents (
  id              TEXT PRIMARY KEY,
  session_id      TEXT NOT NULL REFERENCES sessions(id),
  type            TEXT CHECK (type IN ('main','subagent')),
  subagent_type   TEXT,
  status          TEXT CHECK (status IN ('working','waiting','completed','error','unknown')),
  parent_agent_id TEXT REFERENCES agents(id) ON DELETE SET NULL,
  first_seen_at   TEXT,
  last_seen_at    TEXT
);
CREATE INDEX idx_agents_parent_agent_id ON agents(parent_agent_id);
CREATE INDEX idx_agents_session_id ON agents(session_id);

This is the invariant the whole project is built to protect: “Agents & subagents are first-class, queryable, persisted entities — the subagent tree is a data fact, not a client-side UI reconstruction from a flat event log” (DESIGN §3). ON DELETE SET NULL on the self-reference is what WP-D6 calls “orphan-safe”: removing a parent row never cascades into deleting its subtree, it just detaches it.

Schema/roadmap mismatch, flagged not silently resolved. The status CHECK constraint above allows only working, waiting, completed, error — it has no unknown value. But the missing-SubagentStop watchdog rule that WP-IN12 implements and concept-analysis-v2 §6 requires as an acceptance criterion states: “A missing SubagentStop → explicit ‘unknown’ state within the watchdog window, never a permanent ‘working’.” As written, the verbatim DESIGN §4 DDL cannot represent that state. This table’s CHECK constraint will need 'unknown' added before WP-D6/WP-IN12 land; until then this is a genuine open gap between the fixed DDL and the later, more detailed acceptance criteria — not something this page invents a fix for. See troubleshooting for the watchdog itself.

Resolved as built: exactly as predicted — migration 4 adds 'unknown' to the status CHECK (five values), and the WP-IN12 watchdog assigns it: a non-terminal agent not seen within the watchdog window flips to unknown, a visible real state, never a permanent working. The added first_seen_at/last_seen_at columns are the watchdog’s staleness anchor. A later re-ingest upserts whatever status the JSONL evidence supports, so a stale unknown yields to the durable record.

As built, 2026-09 — one more column. Migration 17 (agents-outcome-cause) adds

ALTER TABLE agents ADD COLUMN outcome_cause TEXT
  CHECK (outcome_cause IS NULL OR outcome_cause IN
    ('concurrency_limit','user_interrupt','permission_failed',
     'dispatch_unavailable','terminated_early','unclassified'));

status answers where is this agent now; outcome_cause answers why did it end that way, and most causes are not failures — a user interrupt is the commonest cause that resolves to a real agent, and concurrency_limit is a scheduling fact with no failed agent in it — which is why the two are not folded together. It is a column and not a side table because the fact is one closed enum per agent, 1:1 with the row that already exists. Every pre-existing row reads NULL, the honest value for an outcome nobody observed. Ingest writes it from the transcript’s terminal record; the API serves it as outcomeCause, a required nullable field of the agent DTO; the session tree lists any non-NULL value verbatim. Whether the Live view shows it is an open owner decision (D9 on the TODO.md closing board).

orchestration_edges — the persisted DAG (the moat artifact)

This table is the concrete artifact behind the project’s central differentiator (DESIGN §2.1): “Global, persistent, per-instance orchestration DAG… A real, queryable, cross-session per-instance graph is unclaimed ground.” DESIGN §4 states the extension requirement precisely: edges “must be persisted (not event-derived at render time) and per-instance (not type-aggregated), and carry an instance/host key for future fleet aggregation.” CD-4 pins the column set: “self-ref parent_agent_id, instance/ host_id, derived_from_event_id, idempotent.”

The real DDL — as it stands after migration 13, orchestration-edges-legacy-explore-source, with the endpoint indexes migration 12 added:

CREATE TABLE orchestration_edges (
  id              INTEGER PRIMARY KEY,
  session_id      TEXT NOT NULL,
  parent_agent_id TEXT NOT NULL,
  child_agent_id  TEXT NOT NULL,
  source          TEXT NOT NULL CHECK (source IN ('tool_use','directory','task_notification','queue_operation','legacy_explore')),
  instance        TEXT NOT NULL,
  host_id         TEXT NOT NULL,
  created_at      TEXT,
  UNIQUE (session_id, parent_agent_id, child_agent_id)
);
CREATE INDEX idx_orchestration_edges_session_id ON orchestration_edges(session_id);
CREATE INDEX idx_orchestration_edges_parent_agent_id ON orchestration_edges(parent_agent_id);
CREATE INDEX idx_orchestration_edges_child_agent_id ON orchestration_edges(child_agent_id);

Migration 5 created this table with a four-value source CHECK and the session index alone. Two later migrations reshaped it, and both are instructive:

Rationale — updated to the as-built facts:

Empirically confirmed by the desktop probe. The 2026-07-04 read-only corpus probe (phase0-probe.md) found zero Task tool blocks across the real ~/.claude/projects/ tree (Agent = 142, Workflow = 29) — a Task-keyed reader would reconstruct an empty DAG. It also confirmed the two layouts coexist within the same Claude Code versions and are driven by the spawn mechanism (Agent → flat, Workflow → nested), so the derivation must branch on directory shape, not version. This pre-answers CD-1 as CONDITIONAL-GO → build (confidence 85); the formal Phase-0 spike (WP-S1/WP-S5, WP-S7 GO gate) still confirms it on the paired-capture corpus before any production code.

Full derivation and rebuild-from-JSONL-alone guarantees belong to the DAG moat; this page stops at the schema and its constraints.

token_usage — fine-grained cost buckets

DESIGN §4 (the hoangsonww graft) states the bucketing directly: “token_usage bucketed by speed / inference_geo / service_tier (each changes the per-token rate), preserving compaction baselines so historical totals still price correctly after a context rewrite.” CD-3 adds the nullability/backfill rule: “token_usage.agent_id is nullable at first write, deterministically backfilled once the agent is known.”

The real DDL (migration 6, token-usage) reshaped the bucketing after contact with the real JSONL — the speed/inference_geo/service_tier dimensions do not appear in Claude Code transcripts; what does is per-message usage with five priced token kinds:

CREATE TABLE token_usage (
  id                      INTEGER PRIMARY KEY,
  session_id              TEXT NOT NULL,
  agent_id                TEXT,
  message_id              TEXT NOT NULL,
  model                   TEXT NOT NULL,
  bucket                  TEXT NOT NULL CHECK (bucket IN ('input','output','cache_read','cache_write_5m','cache_write_1h')),
  tokens                  INTEGER NOT NULL,
  is_compaction_baseline  INTEGER NOT NULL DEFAULT 0,
  occurred_at             TEXT,
  UNIQUE (message_id, bucket)
);
CREATE INDEX idx_token_usage_session_id ON token_usage(session_id);
CREATE INDEX idx_token_usage_agent_id ON token_usage(agent_id);

Migration 10 later adds idx_token_usage_occurred_at_id ON token_usage(occurred_at, id) (and the matching index on events) for the retention scan: pruning selects by age in id order, in bounded batches, so the trailing id makes the batch cursor a covering range read and keeps the batch boundary stable across runs instead of depending on whatever order the engine happens to return for equal timestamps.

Rationale — updated to the as-built facts:

Full cost mechanics — dated-price resolution, delegation-savings, and PreCompact repricing — belong to the cost model.

token_usage_rollup — the bounded cost read

Added 2026-09 by migration 16 (token-usage-rollup, review M-19). token_usage is the one table whose retention is deliberately refused — cost history is the product — so it grows with corpus age forever, and GET /api/cost/summary used to price and group all of it on every cold read. This table bounds that read. It is a table of work already done, in the sense the ingest_checkpoints entry uses: dropping it costs a rebuild and changes no output, because it equals a direct grouped scan of token_usage by construction.

CREATE TABLE token_usage_rollup (
  session_id          TEXT    NOT NULL,
  model               TEXT    NOT NULL,
  bucket              TEXT    NOT NULL,
  day                 TEXT    NOT NULL,
  rate_effective_from TEXT    NOT NULL,
  tokens              INTEGER NOT NULL,
  row_count           INTEGER NOT NULL,
  PRIMARY KEY (session_id, model, bucket, day, rate_effective_from)
) WITHOUT ROWID;
CREATE INDEX idx_token_usage_rollup_model_bucket ON token_usage_rollup(model, bucket);

model_pricing — versioned rates

CD-4: “versioned model_pricing (effective_from, verified_on).” WP-C1 adds the concurrency shape: “Multiple effective_from rows per bucket without conflict.”

The real DDL (migration 7, model-pricing-with-seed) is long-format to match token_usage — one rate row per (model, bucket, effective_from):

CREATE TABLE model_pricing (
  model          TEXT NOT NULL,
  bucket         TEXT NOT NULL CHECK (bucket IN ('input','output','cache_read','cache_write_5m','cache_write_1h')),
  usd_per_mtok   REAL NOT NULL,
  effective_from TEXT NOT NULL,
  PRIMARY KEY (model, bucket, effective_from)
);

What the seed actually contains

Eight models at one effective_from floor of 2026-01-01 — five from the original seed (migrations 7 and 11), each expanded into all five buckets, two added explicitly by migration 18 on 2026-09-10 and one by migration 19 on 2026-09-26. Migration 20 (2026-09-26) adds no model; it corrects the seeded Sonnet 5 rows in place:

model input $/Mtok output $/Mtok
claude-opus-4-8 5 25
claude-sonnet-5 (corrected by migration 20) 2 10
claude-fable-5 10 50
claude-haiku-4-5-20251001 1 5
<synthetic> 0 0
claude-opus-5 (migration 18) 5 25
claude-fable-5-1 (migration 18) 10 50
claude-opus-5-5 (migration 19) 4 20

For the four other seed models the three cache buckets are derived from the input rate rather than listed separately: cache_read at 0.1×, cache_write_5m at 1.25×, cache_write_1h at 2.0×. Migration 18’s two models carry all five buckets explicitly, copied from the platform pricing page fetched 2026-09-10: Opus 5 reads cache at 0.50 and writes it at 6.25 (5-minute) / 10 (1-hour), which happens to follow the derivation; Fable 5.1 reads cache at 0.25 — 0.025× its input rate, not 0.1× — and writes it at 12.50 / 20. A derived row would have priced every Fable 5.1 cache read four times too high, which is why the derivation was not reused. Migration 19’s Opus 5.5 reads cache at 0.20 — 0.05× its input rate, a third ratio no derivation covers — and writes it at 5 / 8, copied from the same page fetched 2026-09-26. Migration 20 writes Sonnet 5’s five rows explicitly too (cache read 0.20, writes 2.50 / 4 — the standard ratios, spelled out so every figure sits in the checksummed SQL). All 40 rows carry the same PROVISIONAL label.

Two details in that table are load-bearing rather than cosmetic. The keys are the exact message.model byte-strings emitted in the corpus, verified 2026-07-13 against ~/.claude/projects (claude-opus-4-8 ×4819, claude-sonnet-5 ×3286, claude-fable-5 ×1849, claude-haiku-4-5-20251001 ×2, <synthetic> ×17). The cost engine does a hard exact-string lookup and halts on any id absent from the table, so a bare opus-4-8 key without the claude- prefix — or a haiku key without its date suffix — would make every real ingest halt. The right fix for a new model is always to add its exact string, never to “normalize” the id on the read side.

That is exactly what happened next. A boot over the real corpus on 2026-09-09 (docs/measurement/time-to-understand-log.md §0.4) found the corpus had moved on to claude-opus-5 (48 of 60 sessions) and claude-fable-5-1 (4 sessions), neither in the seed, so 52 sessions halted at the gate and 8 reached the database. A second boot on 2026-09-18, at schema 18 over the same machine’s corpus (54 sessions by then), admitted 54 of 54 with sessionsExcluded 0 (§0.5 of the same log). Migration 18 adds those two exact strings and nothing else; it is the first migration shipped under the rule the migration-11 paragraph below ends on. A third boot on 2026-09-26, still at schema 18, found the corpus had moved again, to claude-opus-5-5: 27 of 61 sessions refused, 34 admitted (§0.6). Migration 19 adds that one exact string the same way.

The effective_from floor is not the seed’s authoring date. computeCostUsd resolves the latest rate with effective_from <= the message timestamp and throws when none is effective; the real corpus contains messages reaching back to 2026-07-03, roughly 12.2k of them before the seed was written, so a floor at the authoring date would have halted every historical ingest. These are one flat mechanism-proof price applied across the whole observed window, so the floor is set before the corpus begins. The price numbers remain PROVISIONAL and still await ratification — only the coverage floor moved, so that the engine can price historical data at all.

Migration 11 exists because those constants were once edited in place. The original migration 7 wrote bare keys (opus-4-8, sonnet-5, fable-5, haiku-4-5) at a 2026-07-11 floor; the seed was later corrected to the corpus-exact keys and the earlier floor. Because the runner skips by recorded id, a database that had already applied the original 7 kept the old rows — and under the corrected code every real message then failed the PricingError halt gate. Migration 11 converges both histories: it deletes exactly the original seed’s rows (matched on that specific effective_from and that specific set of model keys) and upserts the canonical ones. On a database that ran the corrected 7 the delete matches nothing and the upsert rewrites identical values, so either starting state ends row-identical, and operator-authored rows — any other model key or effective_from — are never touched. The seed derivation is duplicated inside migration 11’s own body rather than shared through a helper, on purpose: the content checksum covers the function’s source, and a shared helper would let a future rate-multiplier edit escape it. Since then, the pricing constants are frozen and covered by every migration’s checksum; a price change must ship as a new migration carrying its own inline data.

Migration 18 is that rule applied once. It inserts ten rows inline — the two corpus models above, five buckets each, at the same floor spelled in the canonical form migration 14 enforces — with ON CONFLICT DO UPDATE on the primary key, so a row an operator wrote by hand at that instant (the measurement log’s scratch-database workaround) converges to the official figure instead of aborting the boot, while a row at any other instant or for any other model is never touched. Its numbers live inside the SQL text rather than as JavaScript literals, so the content checksum comes out identical under every executor the repo runs (apps/server/test/migration-checksum-pin.test.ts explains why a JavaScript 0.5 would not). One figure it deliberately leaves alone: the pricing page fetched the same day lists Sonnet 5 at 2 / 10 where the seed carries 3 / 15. That is a rate change, not a coverage gap, and it waited for the owner’s decision (D10) rather than being folded in here.

Migration 20 is that decision (2026-09-26). The pricing page re-read that day says the $2 / $10 launch price “is now the standard price” and that the scheduled increase to $3 / $15 “will not occur” — so 3 / 15 was never in force, and every Sonnet 5 dollar shown before it was 1.5× too high on every bucket. The correction therefore rewrites the seed’s five floor rows in place through the same ON CONFLICT DO UPDATE rather than adding a later-dated row, which would have kept the cancelled price in force for every earlier message. Only usd_per_mtok changes, which migration 16’s rollup trigger deliberately does not watch (the rate is applied at read time), so stored Sonnet 5 usage re-prices on the next read with no re-ingest. A Sonnet 5 row an operator wrote at any other instant is left alone.

ingest_checkpoints — durable replay memory

This table has no design-basis ancestor; it was added by migration 9 for WP-IN10, and it is the one table on this page that is explicitly not a source of truth.

CREATE TABLE ingest_checkpoints (
  scope           TEXT NOT NULL,
  session_id      TEXT NOT NULL,
  fingerprint     TEXT NOT NULL,
  ingest_revision INTEGER NOT NULL,
  recorded_at     TEXT NOT NULL,
  PRIMARY KEY (scope, session_id)
) WITHOUT ROWID;

Its job is to let a restart skip the sessions whose bytes have not moved since the last run. Every row is a cache of work already done: dropping the whole table costs exactly one full replay and changes no result the dashboard shows. That framing is what licenses the store to degrade rather than crash — a checkpoint that cannot be read or written is a performance loss, never a correctness one, so the ingest path continues without it.

Three column decisions carry the reasoning:

There is a fourth safeguard that is not a column: reuse is granted only when the projection it stands for actually exists. The lookup joins EXISTS (SELECT 1 FROM sessions …), so a checkpoint whose session row has since been pruned or never landed is ignored rather than believed. A checkpoint may never be the reason a session is invisible.

The fingerprint itself, and the in-memory tier that backs this table, are described in ingest & reconciliation.

Alert & webhook tables (Phase 5, not yet built)

DESIGN §4 names these four tables as a graft from hoangsonww, already fully modeled there: “alert_rules + alert_events + webhook_targets + webhook_deliveries — outbound HTTP delivery already modelled; the natural Telegram integration point.” WP-A2 owns their migration: “Alert & webhook schema migration (clean-room-safe, hoangsonww-attributed). Forward-only, idempotent.”

CREATE TABLE alert_rules (
  id          TEXT PRIMARY KEY,
  kind        TEXT NOT NULL CHECK(kind IN ('cost_threshold','stuck_agent','error')),
  config      TEXT NOT NULL,   -- JSON: e.g. {"threshold_usd": 5.00}
  enabled     INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL
);

CREATE TABLE alert_events (
  id           TEXT PRIMARY KEY,
  rule_id      TEXT NOT NULL REFERENCES alert_rules(id),
  session_id   TEXT,
  agent_id     TEXT,
  fired_at     TEXT NOT NULL,
  dedupe_key   TEXT NOT NULL   -- rate-limit/dedupe boundary, WP-A7
);

CREATE TABLE webhook_targets (
  id          TEXT PRIMARY KEY,
  kind        TEXT NOT NULL CHECK(kind IN ('telegram')),
  token_ref   TEXT NOT NULL,   -- NEVER the secret itself — see below
  enabled     INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE webhook_deliveries (
  id               TEXT PRIMARY KEY,
  target_id        TEXT NOT NULL REFERENCES webhook_targets(id),
  alert_event_id   TEXT NOT NULL REFERENCES alert_events(id),
  status           TEXT NOT NULL CHECK(status IN ('pending','sent','failed')),
  attempt_count    INTEGER NOT NULL DEFAULT 0,
  next_retry_at    TEXT,
  delivered_at     TEXT
);

Rationale:

These four tables are designed, not implemented — Phase 5–6 in development-plan.md (Track A, WP-A1…WP-A10), well behind Phase 1’s storage/foundation work. Nothing above should be read as an in-repo migration. (As built, this remains true: v1 ships without any of them, and the alerting phase is entered only via the KC-5 gate — earned by real daily use, per the roadmap of record.)

What’s decided vs. open

Update — 2026-07 (as built). The table below is the design-time record; here is where each open row landed:

Aspect Status
agents DDL Fixed, verbatim, DESIGN §4
events_raw / events split, append-only enforcement, idempotency key Decided (CD-2, CD-4); DDL here is a reference synthesis pending WP-D4/WP-D5
orchestration_edges column set (parent_agent_id, instance/host_id, derived_from_event_id, idempotent) Decided (CD-4, WP-D7); exact FK/child-column naming is a synthesis
token_usage bucket dimensions + compaction baseline + nullable agent_id Decided (DESIGN §4, CD-3/CD-4); raw token-count column names are illustrative
model_pricing versioning (effective_from, verified_on) Decided (CD-4); rate-column shape is illustrative
Alert/webhook table set (alert_rules, alert_events, webhook_targets, webhook_deliveries) Decided at the table level (DESIGN §4, WP-A2); column-level DDL is a synthesis; not scheduled before Phase 5
sessions column set Open — not specified beyond “one row per session” (WP-D6)
agents.status missing 'unknown' vs. the watchdog requirement Open gap, flagged above
Retention TTL sweeper vs. events_raw’s no-DELETE invariant Open tension, flagged above
MVP schema scope (which tables land in the first cut) Open decision — DESIGN §10 notes agents/sessions/events + token_usage as the core, alert/webhook following later; see roadmap
projects / filters (named in DESIGN §4’s simple10 base schema) Open gap — no owning work package, no column-level shape in any source; not modeled in this inventory, flagged above

See also

This site is open source. Improve this page.