> For AI agents: the complete documentation index is available at /cafleet/llms.txt, the full documentation bundle is available at /cafleet/llms-full.txt. # 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](/cafleet/concepts/storage.md)). The complete column-level DDL contract is an optional reference for reimplementers: [Repository specification](https://github.com/himkt/cafleet/blob/main/SPEC.md). 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 | Table | Primary key | Parent | FK ON DELETE | Row removal | | ------------------- | ----------------------------------- | ------------------------------------------------------------------------- | ------------ | ---------------------------------------------------------------------------------- | | `fleets` | `INTEGER PRIMARY KEY AUTOINCREMENT` | `members.member_id`, via the nullable `director_member_id` back-reference | `RESTRICT` | Soft-delete keyed on `deleted_at` | | `members` | `INTEGER PRIMARY KEY AUTOINCREMENT` | `fleets.fleet_id` | `RESTRICT` | Soft-delete (`status='deregistered'` + `deregistered_at`) | | `messages` | `INTEGER PRIMARY KEY AUTOINCREMENT` | `members.member_id`, via `owner_member_id` | `RESTRICT` | Not deleted | | `member_placements` | Reuses `members.member_id` | `members` | `CASCADE` | Hard-deleted on deregistration | | `monitor_runtime` | Reuses `fleets.fleet_id` | `fleets` | `RESTRICT` | Removed inside the `fleet delete` transaction; "no monitor" is modeled as "no row" | | `asset_installs` | `(coding_agent, path)` composite | — | — | Upserted, one row per coding agent and install path | ### `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](/cafleet/spec/cli-options.md#creation-failure-compensation) 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: ```sql 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](/cafleet/concepts/storage.md#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](#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](/cafleet/spec/message-envelope.md). 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: | Field | Rust type | Meaning and lifecycle | | ----------------------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fleet_id` | `i64` | Non-null fleet key; an absent row is `None`, not an id-zero row. | | `pid` | `Option` | Null means no claim; claim stores the direct loop pid and normal clear nulls it. Preserve the existing process probe's handling of zero. | | `started_at` | `Option` | Null means no claim start time; claim/reclaim stamps it and normal clear nulls it. | | `last_tick_at` | `Option` | Null or an unparseable timestamp is not a fresh heartbeat. Claim/tick updates it and normal clear nulls it. | | `last_wake_at` | `Option` | Null means no successful wake is recorded; cadence then falls back to `started_at`. Successful scheduled/forced delivery updates it, and clear/reclaim preserve it. | | `wake_requested_at` | `Option` | Null means no forced-wake request; repeated requests overwrite/coalesce. Successful delivery or reclaim clears it; normal clear alone preserves it. | | `tick_seconds` | `i64` | Non-null tick cadence, DB default 5; CLI rejects zero rather than treating it as disabled. Normal clear preserves it. | | `wake_interval_seconds` | `Option` | Null is the legacy state before a row has been claimed since V5 added the column; zero disables scheduled wakes while permitting forced wake. Positive values are seconds; claim/reclaim/PATCH stamps the value and normal clear preserves it. | 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](/cafleet/spec/webui-api.md#get-apimonitor--fleet-monitor-runtime) hides process timestamps when stopped but preserves stored intervals. The cadence rules are defined in [Monitoring](/cafleet/concepts/monitoring.md#cadence-and-tick-precision). ### `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](/cafleet/spec/cli-options.md#config-dir-resolution)), 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](/cafleet/spec/cli-options.md#stale-assets-guard)). ## 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: | Operation | Enforcement | | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `message poll` | Returns the `input_required` deliveries whose `owner_member_id` equals the positional `MEMBER_ID`; the member must exist, and any caller can poll any inbox by id. | | `message show` | Returns the message iff the `MESSAGE_ID` exists; unknown ids return "not found". | | `message ack` | Transitions the message iff it exists and is in the `input_required` state; the recipient is derived from the message row. | ## Broadcast Grouping A broadcast produces N+1 rows — one delivery message per active recipient plus one `broadcast_summary` message — grouped by `origin_message_id`: | Row kind | `origin_message_id` value | | ------------------------------------------ | ------------------------------------- | | Unicast delivery | `NULL` | | Broadcast delivery row (one per recipient) | The summary message's `message_id` | | Broadcast summary row | Its own `message_id` (self-reference) | 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](/cafleet/spec/webui-api.md#get-apitimeline--unified-fleet-timeline) for SQL ordering and the separate UI creation-time ordering.