CAFleet

    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 exact column-level DDL contract lives in the repository's SPEC.md.

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

    Tables

    TablePrimary keyParentFK ON DELETERow removal
    fleetsINTEGER PRIMARY KEY AUTOINCREMENTmembers.member_id, via the nullable director_member_id back-referenceRESTRICTSoft-delete keyed on deleted_at
    membersINTEGER PRIMARY KEY AUTOINCREMENTfleets.fleet_idRESTRICTSoft-delete (status='deregistered' + deregistered_at)
    messagesINTEGER PRIMARY KEY AUTOINCREMENTmembers.member_id, via owner_member_idRESTRICTNot deleted
    member_placementsReuses members.member_idmembersCASCADEHard-deleted on deregistration
    monitor_configReuses members.member_idmembersCASCADEHard-deleted alongside the placement on deregistration, and inside the fleet delete transaction
    monitor_runtimeReuses fleets.fleet_idfleetsRESTRICTRemoved inside the fleet delete transaction; "no monitor" is modeled as "no row"
    asset_installscoding_agent (the agent name)——Upserted, one row per coding agent

    fleets

    cafleet fleet create writes the fleet row, the root Director (and its placement), and the director_member_id back-reference in one all-or-nothing transaction — which is why director_member_id is DB-nullable despite the post-bootstrap NOT NULL invariant.

    members

    Active query paths filter status='active'. Special members are marked by a broker-owned cafleet.kind flag inside member_card_json rather than a column: "monitoring-member" marks the fleet's single monitoring member (which skips monitor_config enrollment and is located by this marker — see Monitoring). Callers cannot set cafleet.kind through any public path.

    messages

    One row per delivery: a unicast row, a broadcast delivery row, or a broadcast summary row (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.

    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 state tables

    monitor_config holds one row per enrolled member — exactly the four schedule columns: interval_seconds, enabled, last_ping_at, and last_stall_check_at (the nullable UTC ISO timestamp of the last successfully dispatched stall-check wake, kept durable so the stall-check cadence survives a monitor-loop restart). Disabling or losing a pane clears last_stall_check_at; soft deregistration explicitly deletes the config row. The monitoring member's own stall notes (quiet baselines, ping history) live in its conversation context, not in the database.

    monitor_runtime remains the one-row-per-fleet loop pid/heartbeat table. Which members are enrolled and the cadence semantics are defined in Monitoring.

    asset_installs

    One upserted row per coding agent, recording the CLI version whose skills and preset (where one exists) install last landed there — the row attests both. Written by the assets half of cafleet setup; feeds the stale-assets guard and the cafleet doctor report (see CLI options).

    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 two 1:1 child tables (member_placements, monitor_config), 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 fleet-scoped; per-member identity is enforced only on state transitions:

    OperationEnforcement
    message pollReturns the input_required deliveries whose owner_member_id equals --member-id; any in-fleet caller can poll any in-fleet inbox by id.
    message showReturns the message iff at least one endpoint belongs to --fleet-id; cross-fleet lookups return "not found".
    message ackRecipient-only — the caller must equal the message's owner_member_id.

    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 kindorigin_message_id value
    Unicast deliveryNULL
    Broadcast delivery row (one per recipient)The summary message's message_id
    Broadcast summary rowIts 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 grouping predicate origin_message_id IS NOT NULL cleanly partitions the timeline into standalone unicasts vs broadcast groups. 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.