#Data model

The Message payload is fully relational: every routing field plus the message body lives in its own typed column. The only JSON TEXT blob is members.member_card_json. The database is SQLite, accessed synchronously and bundled into the binary; the schema is managed by a chain of SQL migrations embedded in the binary — run cafleet setup to migrate to head (idempotent, data-preserving; see Storage). The complete column-level DDL contract is an optional reference for reimplementers: Repository specification. Fleet operation uses the CLI and bundled contract pages; opening that URL is not required for offline operation.

Minted ids are never reused and real ids are always >= 1.

#Query and activity contracts

CLI member lists include send/receive/ACK activity; WebUI rosters query member and placement data directly. Include deregistered roster members only when they own messages. Name lookup deduplicates ids and binds batches of at most 500, returning known active or deregistered members in id order.

Rows are ordered by member_id ASC. last_sent is the maximum creation time of every message sent by the member, including broadcast summaries; last_recv is the maximum creation time of owned unicast deliveries; last_ack is the maximum status timestamp of owned completed unicast deliveries. idle uses the greatest non-null string among all three, parsed with the existing lenient reader against one now for the list. All null or an unparseable selected value yields null; no older timestamp fallback is used. A zero clamp applies to the final whole-second idle result; it does not change stored future timestamps or parsing.

#Tables

#fleets

cafleet fleet create writes the fleet row, the root Director (and its placement), the director_member_id back-reference, and the monitor member (its row, its monitor card marker, and — after the pane spawn — its placement) in one all-or-nothing transaction. The director_member_id back-reference is nullable during bootstrap and required afterward. The pane spawn occurs inside the transaction, between monitor registration and placement insertion. The CLI compensation contract owns failure ordering and diagnostics. The connection holds SQLite's write lock across the pane-spawn subprocess call, so a concurrent cafleet writer on the shared database blocks for the duration of the multiplexer call, backstopped by the connection's busy_timeout=5000 PRAGMA.

#members

Active query paths filter status='active'. A member's kind (director / monitor / member) is derived at read time from the fleet's director_member_id back-reference plus the member card: a monitor-member registration writes the application-level marker "cafleet": {"kind": "monitor"} into member_card_json, while the Director and ordinary members write no $.cafleet object. The marker remains plain JSON with no dedicated column. Schema V8 adds a partial unique index enforcing at most one active monitor per fleet:

CREATE UNIQUE INDEX idx_members_one_active_monitor_per_fleet
ON members(fleet_id)
WHERE status = 'active'
  AND json_extract(member_card_json, '$.cafleet.kind') = 'monitor';

The predicate is the same one used by the active-monitor lookup. It applies to inserts and updates of status, card, or fleet id. Ordinary members and deregistered monitors are outside the constraint; different fleets are independent. Root Director cards continue to omit the monitor marker, and read-time kind resolution still gives the Director back-reference priority. Registration takes an IMMEDIATE transaction and rechecks the monitor slot inside it, before inserting either a member or placement. The CLI's early check preserves validation order; the DB constraint also protects direct broker callers and concurrent registrations. A conflict retains the existing CLI error and exit 1, without creating a losing member, placement, or pane. See duplicate-monitor recovery for migration of databases that already contain conflicting rows.

#messages

One row per unicast delivery, plus a separate summary row for each broadcast. Broadcast deliveries also have type unicast; their summary has type broadcast_summary (see Broadcast grouping). from_member_id, to_member_id, and origin_message_id are deliberately not foreign keys — historical messages may outlive their sender. status_timestamp is updated on every state change and drives ORDER BY DESC listing. The rendered envelope is specified in Message envelope.

Deliveries transition once from input_required → completed on ACK.

#member_placements

Links a member to its multiplexer pane; pane ids are stored verbatim as opaque strings. The root Director keeps its own placement row (it is pane-bound); an ordinary member is a placed row other than the fleet's root Director (member_id != fleets.director_member_id). Placement rows have no historical value.

#monitor_runtime

monitor_runtime is the one-row-per-fleet loop pid/heartbeat table:

A claimed loop always has a non-null wake interval. Do not normalize the legacy null interval into zero or treat a stopped HTTP response as the stored row: the monitor response hides process timestamps when stopped but preserves stored intervals. The cadence rules are defined in Monitoring.

#asset_installs

One upserted row per (coding_agent, path), recording the CLI version whose skills and preset (where one exists) install last landed at that path — the row attests both. path is the agent's resolved identity path (see CLI options), stored absolute exactly as resolved. Consumers partition an agent's rows by comparing path against the currently-resolved identity path: the row at the resolved path (at most one, by the primary key) is current; every other row of that agent is superseded. Written by the assets half of cafleet setup; the current row feeds the stale-assets guard and the cafleet doctor setup column, while superseded rows surface only as informational doctor footnotes (see CLI options).

#Typed broker records

Broker queries decode rows into typed member, placement, message, and monitor records. Presenters preserve the wire shapes below. Missing placements differ from pending panes, and absent monitor rows differ from nullable fields. Unknown stored enums are integrity errors; free-form skills retain their existing JSON representation.

#Foreign key enforcement

SQLite ignores FK declarations unless PRAGMA foreign_keys=ON is issued per connection; the connection opener applies it on every connection. FKs use ON DELETE RESTRICT except the member_id PK=FK of the 1:1 child table (member_placements), which uses CASCADE so a hard-deleted member cannot leave dangling rows. Normal delete paths are soft-deletes, so neither fires in practice.

#Message Visibility Rules

Read access is by id — the subject row carries its own fleet and recipient, so existence (plus, for ACK, message state) is the enforcement:

#Broadcast Grouping

A broadcast produces N+1 rows — one delivery message per active recipient plus one broadcast_summary message — grouped by origin_message_id:

Because ids are DB-assigned, the summary row is inserted first with a temporarily NULL origin_message_id, then self-linked before the delivery rows are inserted. The timeline first selects only type = 'unicast' delivery rows, then groups non-null origin_message_id values into broadcasts; a null origin identifies a standalone unicast. Summary rows remain in the database and in message show / broadcast results, but do not count as recipients or ACKs.

The per-recipient ACK time is read from the completed delivery row's status_timestamp, which is valid because a delivery message makes exactly one state transition over its lifetime. A summary is already completed when created, without any recipient having acknowledged it.

The timeline's 200-row limit can return only part of a broadcast. Its recipient and ACK counts describe the returned deliveries, not the entire broadcast. See timeline selection and grouping for SQL ordering and the separate UI creation-time ordering.