SQLite entity, event and incoming schemas

Status: command-owned contracts, one real FIPS identity/store and a physically separate incoming inbox. See architecture and implementation progress.

Owners and tables

Owner Contracts / persistence
commands/boards/{schema,db}.ts Boards, board members and indexed listing
commands/boards/create/handler.ts Atomic board/entity/source-event creation
commands/boards/{list,show}/ Local navigation and incoming-aware real UI
commands/peers/{schema,db}.ts FIPS/master metadata; master binding may be unknown/null
commands/tasks/{schema,db,handler,web,styles}.ts Todo, recursive output, hierarchy, local changes and inline UI controls
commands/changes/{schema,db,causality,summary}.ts Source events, operation/reference contracts, causal field classification and exhaustive contextual summaries
commands/incoming/{db,handler,review,web}.ts External staging, individual/explicit context decisions, resolution transactions and compact review UI
commands/peers/{network,network-db,wire}.ts Own publications, authenticated direct-origin delivery, bounded frames and durable stream cursors
commands/invitations/{schema,web}.ts Strict versioned board ID/title/creator/revision metadata; no task/history or grant writes
commands/common/ Actual reused UUID/time/FIPS, definition and WebNode primitives
db/{schema,settings}.ts Peer schema composition and key/value settings

Each peer DB has peers, boards, board_members, todos, events, event_references, settings and publications in data/local.sqlite. The separate data/network.sqlite has incoming, network_cursors, network_receipts and settings. The network identity is pinned in settings; local settings retain each board's durable publication stream UUID. It also holds private personal_root_order (peer/board PK, JSON IDs, revision) and personal_branch_winners (peer/board/parent PK, winner UUID, context fingerprint, representative hash, version). These tables have no shared-event FK or replication role. Personal decisions do not mutate peer databases. Simulator reset commands are removed. All DDL uses supported Database.run and CREATE ... IF NOT EXISTS, with strict tables and foreign keys enabled on the peer connections. Startup uses WAL/FULL.

Generic invitation envelopes/status/retry/decisions live in FIPS's fips_app_invitations, not either app store. FIPS migrates its member-selection table with a nullable invitation draft, preserving existing grants/pairing. An explicit owner approval commits recipient grant + invitation decision atomically in FIPS; app board/tasks still require ordinary individual acceptance. See the invitation contract.

Domain contracts

Existing SQLite rows are retained as stored; no historical creation event is fabricated for an earlier configuration snapshot. New board creation writes a complete source event.

Events carry id, version: 1, board_id, entity_id, pubkey, created_at, typed operation/data, based_on and requires. Supported schemas cover board create/title/ owner/members and todo create/title/details/status/parent/delete/resolve. Current UI/review handlers implement board creation and todo create/title/details/status/delete/resolve; other operations remain future work.

todo.resolve.data is a strict title/details/status discriminated union with its chosen value and 2–64 unique resolves IDs included in based_on. Stateful validation requires same-entity/same-field concurrent branches. Resolution context is also required before projection. Local edits reference all accepted causal heads rather than rowid order. Inline resolver forms prefill only the visible field and leave the action's value argument empty: generic FormData merging appends to nonempty action arguments. This keeps chosen statuses and edited titles scalar through command parsing. Existing databases receive a transactional operation-CHECK table rebuild on startup, preserving event contents/rowids, regenerated immutable reference indexes and all publication positions. Normal immutable guards are restored before committing.

Creation payload IDs/authors/timestamps must match the event. A new todo must be live and belong to its event board. Board operations target the board ID. References are unique, non-self UUIDs, bounded to 64 per role. Edit context is distinct from prerequisites.

SQL derives indexed event_references from immutable JSON reference arrays on insertion. Source update/delete/replacement and independent reference rewrites are rejected. Events have no foreign keys to potentially absent prerequisite targets; stateful acceptance validates accepted dependencies/context before insertion into a peer DB.

Local board/task actions insert source events and update affected entities in one transaction. Received events follow the same atomic source/projection boundary only after individual acceptance. No automatic history replay on each list/show is required.

Physical incoming boundary

incoming is not a table in the accepted database. It lives in network.sqlite: recipient key, event ID, board ID, authenticated direct origin, raw JSON payload, pending/declined/accepted decision and receipt time. Its primary key is recipient/event; an index supports recipient/board/decision lookup. Accepted copies remain external as the decision record; the peer receives its own immutable source record on acceptance. An expression index supports recipient/board/entity/decision queries for conflict marking across review pages.

network_cursors is external, keyed by origin/board in the identity-bound store, with stream UUID, position, advertised head and last checked time. Sync transactionally stages proposals and advances cursors there. network_receipts separately records remote durable receipt evidence for local releases. Stream changes/rollback/gaps fail closed. Sync must not write even the received event, peer metadata or references into the target peer DB before approval. Decline likewise changes the inbox only. Received and declined data is not refetched indefinitely.

Automatic delivery after Send uses the same validated inbox transaction and cursor, not a new accepted-state path. Released-head announcements merely wake bounded direct- origin pulls. Multi-page catch-up, reconnect/startup reconciliation and retry share origin/board locks with manual Sync. Pending/declined decisions are preserved, and no received board/event/entity is accepted automatically. Queue state is transient; durable publications and receipt cursors reconstruct work after restart. No schema reset or new delivery database is required.

Acceptance validates origin, target, prerequisites and edit context. A supported projection update plus source insertion commits in the peer DB, then the inbox is marked accepted. Because these are separate DBs, retry compares an existing identical event and finishes the decision idempotently if interrupted after the peer commit. Conflicting IDs are errors, not upserts. Divergent title/details/status field heads require an explicit resolution; independent fields/equal values are compatible. Received resolutions may commit an exact, explicitly selected set of staged edit prerequisites in the same peer transaction. Creation/unavailable prerequisites are never accepted implicitly. Stable resolution IDs recover local resolution commits before inbox updates. Accepted changes cannot be declined in place; retraction/compensation remains future work.

Indexes and guards

Verification and pending scope

Targeted lint and required generation pass. One-off in-memory checks cover DDL, hierarchy/cycle constraints, real command parsing and WebNode validation, A/B/C isolation, unsent privacy, inbox-only Sync/Decline, prerequisite gating, atomic acceptance, duplicate/retry recovery, conflict rejection and origin-only publication. TypeScript reports only the three pre-existing FIPS vendor errors.

Current FIPS checks additionally cover bounded framed delivery, cursor continuation, worst-case escaped details and revoked origin filtering through in-memory capability boundaries. Pending: authenticated visual and actual network/restart acceptance, resource budgets, full deterministic replay/repair, deletion/hierarchy merge resolution, agreement, owner invitations and board-local resets. No automated test suite was added.

Historical simulation reset (removed)

The following records the former phase only. commands/reset/ and all simulation reset UI/commands are removed. The old four databases are retained unopened; the owner approved starting fresh in the two real-identity stores above.

commands/reset/ owns definition, confirmation UI, schema clearing and orchestration. All-peer reset drops/recreates the known peer tables in FK-safe order and restores their normal source immutability/cycle constraints. It clears all three peers before clearing the separate inbox, review decisions, receipt cursors and selected-peer setting. An external simulation.resetting marker makes interruption retryable on startup; the final inbox transaction removes the marker. No FIPS or other-app database is touched. One-off validation covered empty preview, full reset, cached statement reuse, future writes, retained immutable guards, no old publication resurrection and interrupted-reset recovery. Local mutation UI was also checked to contain the newly written A todo without changing B/C or staging its own local event as incoming. The owner-requested live reset was performed and all peer/inbox tables were verified empty; an existing independent read connection observed the cleared state too.

Peer-only reset recreates just the selected peer's schema, removes its recipient inbox records, clears cursors where it is recipient or origin, and selects that peer. It retains all other peer data and other recipients' already-received proposals. Scoped markers resume interrupted peer resets. Validation covered preserving complete other peer contents, retained proposals/unrelated cursors, renewed receipt with acceptance, restarted publication positions, scoped recovery and the all-peer fallback.