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
- Board/todo/event identities are separate lowercase UUIDs. Contracts are strict; signatures and unknown fields are rejected. Titles are trimmed/bounded by Zod.
created_atis an explicit nonnegative safe integer in Unix milliseconds, not a transport cursor or conflict resolver. Each event has the acting FIPSpubkey.- Board/todo
pubkeystays its creator. Boardowneris a FIPS node, distinct from the master identity and latest task editor. Membership revision is a positive integer. - Peer
pubkeyis compressed lowercase 66-character FIPS hex; nullablemaster_pubkeyis 64-character lowercase hex. Null is unknown, not an invented authenticated binding. Metadata comes from authenticated FIPS roster bindings, not payload master claims. - Membership identity is
(board_id, peer_pubkey), with adding actor/time metadata. - Todo stores board, nullable parent, title, pending/in_progress/done status, a soft
deletion flag and creation metadata. Children are derived from parent links;
recursive
TodoTreeSchemavalidates parent/board consistency. SQL booleans are integer 0/1 and are converted by the row adapter. - Todo details is nonnull text, default empty and bounded to 50,000 characters. Existing
rows gain an empty-default column; legacy create payloads default it only when decoded.
todo.detailsupdates and details-valuedtodo.resolveuse the normal immutable event path. Startup extends the event CHECK without rewriting raw source JSON or release order.
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 and causal links
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
- Peers: master/node lookup; boards: owner/creation/ID; memberships: board/peer PK and reverse peer/board lookup.
- Todos: unique board/ID; child and status indexes include live/deleted state and stable display keys. Deferred same-board parent FK plus insert/update cycle guards; creation identity metadata is immutable. No cascading task/history deletion.
- Events: board, entity and origin history indexes; reference PK supports forward lookup, and referenced-ID/relation/source index supports dependents.
- Publications: event PK and unique board/sequence, independent of causal dependencies. Only own-origin events can be released. Sync validates origin and copies semantic payloads, never SQLite pages/files or another origin's source as its own.
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.