Part of Stem. This page defines the database a Stem daemon keeps: which tables exist, what each holds, which code writes it, and which tables are rebuilt from the blobs. It is the storage counterpart of The runtime model and of Privacy.

Principles

blobs is the only permanent mirror of network data. Every other table is either derived from the blobs by the handler and rebuilt by a full reindex, or local-only state of this peer that no other peer ever sees.

Three classes, no exceptions. Every table is one of: permanent mirror (never touched by reindex), derived index (DELETE FROM and rebuilt by reindex), or local-only state (never rebuilt, never synced). The classification is declared next to the schema, and a test asserts every table has one.

Stable ids. blobs.id and public_keys.id are integers that survive a reindex. Node ids are strings from node-id and are stored as text; no derived integer stands for a node. HM24's resources.id was derived and changed on every reindex, which is why local-only tables had to key by IRI text. Stem has no such table.

One evaluator, many joins. Authority and readers are materialised once at indexing time (Authority, Privacy). Every read surface (fetch, reconcile, listings, search, feeds, backlinks) filters by joining blob_access or node_readers. No query surface has its own access rule.

Uniform denial. A blob the caller may not read and a blob this peer does not hold produce the same result from every recipe below.

Connection setup is unchanged from today's daemon: WAL, one writer, PRAGMA foreign_keys=ON, synchronous=NORMAL, a dedicated checkpointer, no ANALYZE, plans pinned with INDEXED BY where they matter.

Today (HM24)

The shipping schema is backend/storage/schema.sql. Each of its tables maps onto Stem as follows.

HM24 table	class	becomes in Stem
`blobs`	permanent	`blobs`, unchanged
`public_keys`	permanent	`public_keys`, unchanged
`blob_visibility`, `blob_visibility_rules`	derived / static	`blob_access` plus `node_readers` and `node_public`; the rule table is replaced by the `dep`, `proof` and `file` link kinds of link-kind
`structural_blobs`	derived	`structural_blobs`, with the six Stem types and the five legacy types kept as a conversion record
`stashed_blobs`	derived	`stashed_blobs`, unchanged
`resources`	derived	`nodes`; the IRI interning table disappears because a node's identity is `(space, id)` text
`document_generations`	derived	folded into `nodes` (`heads`, `is_deleted`, redirect columns, `head_blobs`); generations do not exist
`document_attributes`, `document_attribute_keys`	derived	`facts` with `provenance = 'signed'`
`document_reference_summaries`, `document_reference_targets`	derived	`node_links` (the references of the current state)
`comment_live`	derived	`nodes` rows of kind comment; a comment is a node whose latest Snapshot is its state
`document_comment_stats`	derived	`facts` with `provenance = 'derived'` (`commentCount`, `lastCommentTime`)
`blob_links`	derived	`blob_links`, same shape, link types from `link-kind`
`resource_links`	derived	`node_links`
`spaces`	derived	`facts` on the root node of each space
`subscriptions`	local-only	`policies`, materialised from the account's policy resource
`peers`	local-only	`peers`, unchanged, plus `peer_accounts`
`wallets`	local-only	`wallets`, unchanged
`domains`	local-only	`sites`
`kv`	local-only	`kv`, unchanged
`unread_resources`	local-only	`unread_nodes`
`fts`, `fts_index`	derived	`fts`, `fts_index`, keyed by node instead of resource
`embeddings`, `embeddings_index`	derived	unchanged
`rbsr_scope`, `rbsr_item`	derived	`scopes`, `scope_items`, keyed by the scope record
(none)	local-only	`disclosures`, `transfers`, `transfer_blobs`, `sync_runs`: the privacy bookkeeping and sync state that HM24 kept only in memory or not at all

Permanent mirror

`blobs`

CREATE TABLE blobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    multihash BLOB UNIQUE NOT NULL,
    codec INTEGER NOT NULL,
    size INTEGER DEFAULT (-1) NOT NULL,
    insert_time INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    data BLOB
);
CREATE INDEX blobs_metadata ON blobs (id, multihash, codec, size, insert_time);
CREATE INDEX blobs_metadata_by_hash ON blobs (multihash, codec, size, insert_time);

Unchanged. One row per content-addressed blob, keyed by multihash so the two CIDs of one byte string (daemon BLAKE2b, SDK SHA-256) share a row. size = -1 is a placeholder for a blob that a link names but this peer has not received; when the bytes arrive the row is filled and its id is reassigned to the next sequence value, so id order is arrival order and reindex replays in that order. Written by the blockstore on every transfer and local publish. Read by everything. Blobs are append-only in practice; the only deletion path is garbage collection of blobs that no pin policy covers and no retained link reaches.

`public_keys`

CREATE TABLE public_keys (
    id INTEGER PRIMARY KEY,
    principal BLOB UNIQUE NOT NULL
);

Unchanged. Interns every principal seen as a signer, delegate, owner, subject or audience key. Classified permanent so the integer ids stay stable across reindex.

Derived index

Every table in this section is emptied and rebuilt by a full reindex, in the order given in the reindex section.

`structural_blobs`

CREATE TABLE structural_blobs (
    id INTEGER PRIMARY KEY REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    type TEXT NOT NULL,
    ts INTEGER,
    author INTEGER REFERENCES public_keys (id),
    extra_attrs JSONB
) WITHOUT ROWID;
CREATE INDEX structural_blobs_by_author ON structural_blobs (author, ts);
CREATE INDEX structural_blobs_by_type ON structural_blobs (type, ts);
CREATE INDEX structural_blobs_by_ts ON structural_blobs (ts, id);

One row per blob the handler decoded and whose signature verified. type is one of Node, Change, Snapshot, Grant, Revocation, Group, or one of the HM24 types Ref, Capability, Comment, Profile, Contact for a legacy blob that the conversion pass translated (see Migration). extra_attrs holds small type-specific values that no other table needs: for a Change its genesis and depth, for a Snapshot its schema, for a legacy blob the CID of the Stem record it was converted into. The resource and genesis_blob columns of HM24 are gone: a blob's resource is in node_blobs, and document identity is in nodes. Written only by the handler's save step. Read by the state fold, the authority pass, listings and the activity feed.

`nodes`

CREATE TABLE nodes (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    id TEXT NOT NULL,                 -- the node id: SHA-256 CID of the creating Node blob
    created_by INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    kind TEXT NOT NULL,
    parent TEXT,                      -- NULL only for the space root
    name TEXT,
    access TEXT NOT NULL DEFAULT 'inherit',
    target_kind TEXT NOT NULL,
    heads JSON NOT NULL DEFAULT ('[]'),
    snapshot INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE,
    redirect_space INTEGER REFERENCES public_keys (id),
    redirect_node TEXT,
    republish INTEGER NOT NULL DEFAULT 0,
    is_deleted INTEGER NOT NULL DEFAULT 0,
    head_blobs JSON NOT NULL DEFAULT ('[]'),
    version TEXT NOT NULL DEFAULT '',
    genesis INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE,
    create_time INTEGER NOT NULL,
    last_change_time INTEGER NOT NULL DEFAULT (0),
    legacy_key TEXT,                  -- the HM24 path or TSID this node was migrated from; NULL for native nodes
    PRIMARY KEY (space, id)
) WITHOUT ROWID;
CREATE INDEX nodes_by_parent ON nodes (space, parent, name) WHERE parent IS NOT NULL;
CREATE INDEX nodes_by_legacy_key ON nodes (space, legacy_key) WHERE legacy_key IS NOT NULL;
CREATE INDEX nodes_by_kind ON nodes (kind, space);
CREATE INDEX nodes_by_genesis ON nodes (genesis) WHERE genesis IS NOT NULL;
CREATE INDEX nodes_by_redirect ON nodes (redirect_space, redirect_node) WHERE redirect_node IS NOT NULL;
CREATE INDEX nodes_by_change_time ON nodes (space, last_change_time);

legacy_key is how HM24 URLs keep resolving: hm://<space>/<path> and hm://<author>/<tsid> look the node up by its migrated path or TSID when the first segment is not a node id and no name matches.

The materialised state of every resource this peer knows. One row per (space, id), where id is the SHA-256 CID string of the creating Node blob (created_by is that blob's row; the root row's id is the CID of the owner's deterministic root blob), created when that blob is authorized and updated by every later Node blob naming it through the fold defined in Resources. head_blobs is the sorted list of Node blob ids that no other Node blob of this node supersedes through prev; normally one. target_kind is heads, snapshot, tombstone or redirect after the merge rule. For heads, heads holds the sorted Change blob ids and version their CIDs joined with .; for snapshot, snapshot holds the blob and version its CID. genesis is the genesis Change for Change-graph kinds, the same identity HM24 used for comment_live, kept so a converted document and its comments stay joined. kind, parent, name and access come from the head Node blob; kind never changes after creation. Written by the handler when a Node blob or a Change or Snapshot that advances the state is indexed. Read by every resource API, by the readers pass (access, parent), by placement resolution and by scope materialisation.

`node_names`

CREATE TABLE node_names (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    parent TEXT NOT NULL,
    name TEXT NOT NULL,
    node TEXT NOT NULL,
    rank INTEGER NOT NULL,
    PRIMARY KEY (space, parent, name, node),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;

Every live claim of a name under a parent. Names may collide; rank is the position of this claim under the resolution rule of Placement, and rank 1 is the node a pretty path shows. Tombstoned and redirected nodes have no row, which is what frees a name. Written with nodes. Read by pretty-path resolution and by directory listings.

`node_blobs`

CREATE TABLE node_blobs (
    blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    role TEXT NOT NULL,
    is_head INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (blob, space, node),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX node_blobs_by_node ON node_blobs (space, node, role, is_head);

Which blobs make up which resource's state: the node's Node blobs (role = 'node'), its applied Changes and Snapshots (role = 'state'), and the files its state embeds (role = 'file'). is_head marks the current Node blob heads and the current state heads. A Change shared by two nodes (never, by construction, since genesis is per document) would appear twice. This is the table the readers pass projects through to fill blob_access. Written by the handler; read by blob_access derivation, by retention and by the scope-set query.

`grants`

CREATE TABLE grants (
    blob INTEGER PRIMARY KEY REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    issuer INTEGER REFERENCES public_keys (id) NOT NULL,
    ts INTEGER NOT NULL,
    subject_kind TEXT NOT NULL,
    subject_space INTEGER REFERENCES public_keys (id),
    subject_node TEXT,
    subject_exact INTEGER NOT NULL DEFAULT 0,
    subject_group INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    audience_kind TEXT NOT NULL,
    audience_key INTEGER REFERENCES public_keys (id),
    audience_group INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    audience_hash BLOB,
    audience_space INTEGER REFERENCES public_keys (id),
    audience_node TEXT,
    access TEXT NOT NULL,
    proof INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    expires INTEGER
) WITHOUT ROWID;
CREATE INDEX grants_by_subject_node ON grants (subject_space, subject_node) WHERE subject_kind = 'node';
CREATE INDEX grants_by_subject_group ON grants (subject_group) WHERE subject_kind = 'group';
CREATE INDEX grants_by_audience_key ON grants (audience_key) WHERE audience_kind = 'key';
CREATE INDEX grants_by_audience_group ON grants (audience_group) WHERE audience_kind = 'group';
CREATE INDEX grants_by_audience_hash ON grants (audience_hash) WHERE audience_kind = 'bearer';
CREATE INDEX grants_by_audience_node ON grants (audience_space, audience_node) WHERE audience_kind = 'readers';
CREATE INDEX grants_by_proof ON grants (proof) WHERE proof IS NOT NULL;

One row per Grant blob, with the subject and audience unions flattened into typed columns so that the authority pass is a set of index seeks. subject_group and audience_group reference the Group blob's row in blobs; a Group that has not arrived yet is a size = -1 placeholder, which is why these references omit ON DELETE CASCADE. Nothing in this table says whether the grant is live: that is authority. Written by the handler for every Grant, valid or not yet provable. Read by the authority pass only.

`revocations`

CREATE TABLE revocations (
    blob INTEGER PRIMARY KEY REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    grant_blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE NOT NULL,
    signer INTEGER REFERENCES public_keys (id) NOT NULL,
    ts INTEGER NOT NULL
) WITHOUT ROWID;
CREATE INDEX revocations_by_grant ON revocations (grant_blob);

One row per Revocation blob. Whether the revocation is valid (signed by the issuer, the delegate key, or an admin over the subject) is decided in the live pass, not here. Written by the handler; read by the authority pass.

`groups`

CREATE TABLE groups (
    blob INTEGER PRIMARY KEY REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    owner INTEGER REFERENCES public_keys (id) NOT NULL,
    label TEXT
) WITHOUT ROWID;
CREATE INDEX groups_by_owner ON groups (owner);

One row per Group blob. The group id is the blob id. Membership is not stored here; it is the authority rows whose subject is the group.

`authority`

CREATE TABLE authority (
    subject_kind TEXT NOT NULL,
    subject_space INTEGER REFERENCES public_keys (id),
    subject_node TEXT,
    subject_group INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    principal INTEGER REFERENCES public_keys (id) NOT NULL,
    level INTEGER NOT NULL,
    exact INTEGER NOT NULL DEFAULT 0,
    via JSON NOT NULL DEFAULT ('[]'),
    PRIMARY KEY (subject_kind, subject_space, subject_node, subject_group, principal)
) WITHOUT ROWID;
CREATE INDEX authority_by_principal ON authority (principal, level);
CREATE INDEX authority_by_node ON authority (subject_space, subject_node, level) WHERE subject_kind = 'node';
CREATE INDEX authority_by_group ON authority (subject_group, level) WHERE subject_kind = 'group';

The result of the two-pass evaluation in Authority: for each subject that any live grant names, the highest live access level each principal holds over it, with level encoded 1 sync, 2 read, 3 write, 4 admin and via the Grant blob ids of the path that gives it. Rows exist only for principals named directly or reached through groups; readers, bearer and everyone audiences are projected in the readers pass, not here. The owner of a space has an implicit admin row on the space root that the pass writes explicitly so every join is uniform. Rewritten for a subject's space whenever a Grant, Revocation or Group indexes, and for a group whenever its membership changes. Read by the write-authorization check (may this signer publish this Node), by the readers pass, and by the Access RPC.

`node_public` and `node_readers`

CREATE TABLE node_public (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    PRIMARY KEY (space, node),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;

CREATE TABLE node_readers (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    principal INTEGER REFERENCES public_keys (id) NOT NULL,
    PRIMARY KEY (space, node, principal),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX node_readers_by_principal ON node_readers (principal, space, node);

CREATE TABLE node_bearers (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    hash BLOB NOT NULL,
    PRIMARY KEY (space, node, hash),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;

The materialised readers of every node. node_public lists nodes whose readers include everyone. node_readers lists every named principal that may read a node: owner, admins, key audiences, expanded group members, and the readers of another node for readers audiences, all after applying the node's access mode (inherit adds the parent's rows, own does not, target adds the target node's rows). node_bearers lists the bearer-secret hashes that unlock a node. Public nodes still get node_readers rows for their writers, so one join answers both "who may read" and "who may write". Rewritten top-down for a space whenever authority changes for it or a node's parent or access changes. Read by every listing and by the blob_access derivation.

`blob_access`

CREATE TABLE blob_access (
    blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    PRIMARY KEY (blob, space, node),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX blob_access_by_node ON blob_access (space, node, blob);

"Blob B is readable by whoever can read node N." One row per blob per node whose state it belongs to, projected from node_blobs and closed over dep, proof and file links in blob_links. A Change shared by nothing else maps to one node; a file embedded by two documents maps to two, and is readable if either is. Grant, Revocation and Group blobs map to the node of their subject (or, for a group, to every node that has a grant naming the group). This is the generalisation of HM24's blob_visibility (blob, space): the audience column became a node, and public became node_public. Rewritten with node_blobs. Read by Fetch, Reconcile, the HTTP blockstore and every blob-level listing.

`blob_links`

CREATE TABLE blob_links (
    source INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    target INTEGER REFERENCES blobs (id) ON UPDATE CASCADE NOT NULL,
    kind TEXT NOT NULL,
    PRIMARY KEY (source, kind, target)
) WITHOUT ROWID;
CREATE UNIQUE INDEX blob_backlinks ON blob_links (target, kind, source);

Same shape as today, with kind drawn from link-kind where the target is a blob: dep (Change deps, Node heads and snapshot, Snapshot prev, Node prev), proof (Node and Grant proof, Revocation grant), schema (Snapshot schema blob when pinned by ipfs://), file (embedded media). Targets are placeholder-ensured in blobs, so the target reference has no ON DELETE CASCADE. Written by the handler. Read by the state fold (walking dep), by blob_access closure, by authority-first ordering in Fetch, and by garbage collection.

`node_links`

CREATE TABLE node_links (
    id INTEGER PRIMARY KEY,
    source INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    kind TEXT NOT NULL,
    target_space INTEGER REFERENCES public_keys (id) NOT NULL,
    target_node TEXT NOT NULL,
    version TEXT,
    anchor TEXT,
    fragment TEXT
);
CREATE INDEX node_links_by_source ON node_links (source, kind);
CREATE INDEX node_links_by_target ON node_links (target_space, target_node, kind, source);

One row per link whose target is a resource: parent, embed, link, mention, target, and schema when the schema is named by hm://. The target node need not exist locally; the pair is stored as text and resolved on read. This is the backlinks, citations and mentions table, and the source of the target audience for comments. Written by the handler from the Kind's links rules. Read by ListCitations, mentions, the readers pass (target kind), and the scope-set query (comments facet).

`facts`

CREATE TABLE facts (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    key TEXT NOT NULL,
    kind TEXT NOT NULL,
    value,
    provenance TEXT NOT NULL,
    source INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE,
    ts INTEGER NOT NULL,
    PRIMARY KEY (space, node, key),
    FOREIGN KEY (space, node) REFERENCES nodes (space, id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX facts_by_key ON facts (key, kind, value);

The fact records: the resolved attributes of a node's current state (provenance = 'signed', source the Change or Snapshot that set the value), and the values this peer computes (provenance = 'derived', source NULL): childCount, commentCount, lastCommentTime, isCollection, firstImage. kind is the scalar kind n, s, b, i, f; structured values are not indexed. Replaces document_attributes, document_comment_stats, spaces and the $db.* keys. Rewritten per node when its state changes; derived facts are recomputed when a comment or child indexes. Read by listings, the query grammar and attribute autocomplete.

`stashed_blobs`

CREATE TABLE stashed_blobs (
    id INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    reason TEXT NOT NULL,
    extra_attrs JSON NOT NULL,
    PRIMARY KEY (id, reason, extra_attrs)
) WITHOUT ROWID;

Unchanged. A blob this peer holds but could not apply: FailedPrecondition with the missing blob CIDs (a Change before its deps, a Node before its heads or its parent, a Snapshot before its prev), PermissionDenied with the signer principal (a Node, Change or Snapshot whose signer holds no live write), BadData. The handler retries matching rows when the named blob or a Grant naming the signer indexes. Authority-first fetching makes stashing rare but it stays, because arrival order is never guaranteed.

`fts` and `fts_index`

CREATE VIRTUAL TABLE fts USING fts5(
    raw_content, type UNINDEXED, blob_id UNINDEXED, block_id UNINDEXED, version UNINDEXED
);
CREATE TABLE fts_index (
    rowid INTEGER PRIMARY KEY,
    blob_id INTEGER NOT NULL,
    version TEXT NOT NULL,
    block_id TEXT NOT NULL,
    type TEXT NOT NULL,
    ts INTEGER,
    space INTEGER NOT NULL,
    node TEXT NOT NULL
) WITHOUT ROWID;
CREATE INDEX fts_index_by_blob ON fts_index (blob_id);
CREATE INDEX fts_index_by_node ON fts_index (space, node, rowid);
CREATE INDEX fts_index_by_block ON fts_index (block_id, space, node, rowid);

Full-text search, with the shadow table now keyed by (space, node) instead of a genesis blob. type is title, meta, document, comment, profile, contact. Search results are filtered by joining node_readers or node_public on (space, node), so the index itself never needs an access column. The composite fts_index_by_block index is what the 2026-09-09 reindex stall lacked.

`embeddings` and `embeddings_index`

CREATE VIRTUAL TABLE embeddings USING vec0(
    multilingual_minilm_l12_v2 int8[384] distance_metric=cosine,
    fts_id int
);
CREATE TABLE embeddings_index (fts_id INTEGER PRIMARY KEY);

Unchanged. Semantic vectors per fts row; embeddings_index is the anti-join target for the embedder's pending scan. Results join back through fts_index to (space, node) and then through node_readers.

`scopes` and `scope_items`

CREATE TABLE scopes (
    id INTEGER PRIMARY KEY,
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    depth TEXT NOT NULL,
    facets TEXT NOT NULL,
    materialized INTEGER NOT NULL DEFAULT 0,
    last_access INTEGER NOT NULL DEFAULT 0,
    UNIQUE (space, node, depth, facets)
);
CREATE TABLE scope_items (
    scope INTEGER NOT NULL REFERENCES scopes (id) ON UPDATE CASCADE ON DELETE CASCADE,
    blob INTEGER NOT NULL REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (scope, blob)
) WITHOUT ROWID;
CREATE INDEX scope_items_by_blob ON scope_items (blob);

The maintained scope set index: for each scope a peer has asked about or a policy names, the unfiltered blob set, so Reconcile does not recompute it per round. facets is the sorted facet list joined with ,. Access filtering is applied at serve time by joining blob_access against the caller's readable nodes; the stored set is never filtered, so one scope row serves every caller. Rows are created lazily on the first Reconcile, Watch or policy rule for the scope, patched incrementally by the handler after each commit (a blob joins every scope whose node set contains one of its blob_access nodes, plus the comments and authority facets through node_links and grants), and verified by a background sweep. A scope nobody has touched for a long time is dropped. Replaces rbsr_scope and rbsr_item; the dir_structure kind becomes depth = children with facets = state.

Local-only state

Nothing in this section is derived from blobs or rebuilt by reindex, and nothing here is ever sent to another peer.

`kv`

CREATE TABLE kv (
    key TEXT PRIMARY KEY,
    value TEXT
) WITHOUT ROWID;

Unchanged: last_reindex_time, the embedding model checksum, the daemon auth secret, wallet settings.

`peers` and `peer_accounts`

CREATE TABLE peers (
    id INTEGER PRIMARY KEY,
    pid TEXT UNIQUE NOT NULL,
    addresses TEXT NOT NULL,
    explicitly_connected BOOLEAN DEFAULT false NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL,
    updated_at INTEGER DEFAULT (strftime('%s', 'now')) NOT NULL
);

CREATE TABLE peer_accounts (
    peer INTEGER REFERENCES peers (id) ON DELETE CASCADE NOT NULL,
    account INTEGER REFERENCES public_keys (id) NOT NULL,
    expires INTEGER NOT NULL,
    PRIMARY KEY (peer, account)
) WITHOUT ROWID;

peers is unchanged: known peers and their multiaddrs, written on identify and by peer exchange. peer_accounts is the result of Authenticate: which accounts a connected peer has proven it holds, until expires or until the connection closes. The daemon may keep this table in memory only, since a binding never outlives a connection; it is listed here because every access recipe joins it, and a daemon that persists it must clear it on start.

`policies`

CREATE TABLE policies (
    account INTEGER REFERENCES public_keys (id) NOT NULL,
    rule INTEGER NOT NULL,
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    depth TEXT NOT NULL,
    facets TEXT NOT NULL,
    mode TEXT NOT NULL,
    peers JSON NOT NULL DEFAULT ('[]'),
    interval INTEGER,
    source INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    PRIMARY KEY (account, rule)
) WITHOUT ROWID;
CREATE INDEX policies_by_scope ON policies (space, node, mode);

The flattened rules of each local account's policy resource. The policy itself is a Snapshot resource that syncs between the account's devices like any other blob; this table is the local projection the scheduler reads, rewritten whenever the policy resource's state changes. It is local-only because it is a view of a resource, and because a daemon may also carry operator rules (a site daemon follows every space it serves) that no account signed. Replaces subscriptions.

`disclosures`

CREATE TABLE disclosures (
    id INTEGER PRIMARY KEY,
    blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    peer INTEGER REFERENCES peers (id) NOT NULL,
    ts INTEGER NOT NULL,
    basis_kind TEXT NOT NULL,
    basis_account INTEGER REFERENCES public_keys (id),
    basis_grant INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    basis_space INTEGER REFERENCES public_keys (id),
    basis_request TEXT
);
CREATE INDEX disclosures_by_blob ON disclosures (blob, ts);
CREATE INDEX disclosures_by_peer ON disclosures (peer, ts);
CREATE INDEX disclosures_by_grant ON disclosures (basis_grant) WHERE basis_grant IS NOT NULL;

The outbound ledger of Privacy: one row per blob served to a peer by Fetch, with the basis flattened: public, grant (account and grant), bearer (grant), site (space and grant), offer (request id). Public serves may be sampled or aggregated by configuration; private serves are always recorded. Written by the Fetch handler after the access check passes. Read by ListDisclosures, and by the revocation report that answers "who already holds this".

`transfers` and `transfer_blobs`

CREATE TABLE transfers (
    id TEXT PRIMARY KEY,
    peer INTEGER REFERENCES peers (id),
    account INTEGER REFERENCES public_keys (id),
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    depth TEXT NOT NULL,
    facets TEXT NOT NULL,
    ts INTEGER NOT NULL,
    received INTEGER NOT NULL DEFAULT 0,
    indexed INTEGER NOT NULL DEFAULT 0,
    stashed INTEGER NOT NULL DEFAULT 0,
    rejected INTEGER NOT NULL DEFAULT 0
) WITHOUT ROWID;
CREATE INDEX transfers_by_peer ON transfers (peer, ts);
CREATE INDEX transfers_by_scope ON transfers (space, node, ts);

CREATE TABLE transfer_blobs (
    transfer TEXT REFERENCES transfers (id) ON DELETE CASCADE NOT NULL,
    blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    verdict TEXT NOT NULL,
    reason TEXT,
    PRIMARY KEY (transfer, blob)
) WITHOUT ROWID;
CREATE INDEX transfer_blobs_by_blob ON transfer_blobs (blob);

The inbound ledger: one transfer per batch received from a peer (through Offer followed by the receiver's Fetch, through a sync run's Fetch, or through a local Publish, where peer is NULL), with the claimed scope and the per-blob verdict indexed, stashed or rejected and its reason from rejection. A rejected blob has a transfer_blobs row but no blobs row, so blob for it is the placeholder id. Written by the ingest pipeline. Read by ListTransfers and by abuse controls (a peer whose rejections dominate is benched).

`sync_runs`

CREATE TABLE sync_runs (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    depth TEXT NOT NULL,
    facets TEXT NOT NULL,
    state TEXT NOT NULL,
    version TEXT,
    last_run INTEGER,
    peers_asked INTEGER NOT NULL DEFAULT 0,
    peers_ok INTEGER NOT NULL DEFAULT 0,
    blobs_wanted INTEGER NOT NULL DEFAULT 0,
    blobs_received INTEGER NOT NULL DEFAULT 0,
    error TEXT,
    PRIMARY KEY (space, node, depth, facets)
) WITHOUT ROWID;

The state behind sync-status: the latest run per scope. Written by the scheduler; read by Sync and SyncStatus. Rows for scopes no policy names and no client has touched recently are pruned.

`watches`

CREATE TABLE watches (
    peer INTEGER REFERENCES peers (id) ON DELETE CASCADE NOT NULL,
    scope INTEGER REFERENCES scopes (id) ON DELETE CASCADE NOT NULL,
    since INTEGER NOT NULL,
    PRIMARY KEY (peer, scope)
) WITHOUT ROWID;

Open Watch streams: which connected peer is watching which scope. Kept in memory in practice, since a watch dies with its connection; listed here because the handler's post-commit step reads it to decide which peers get a notification, filtered through blob_access and peer_accounts for each.

`sites`

CREATE TABLE sites (
    space INTEGER REFERENCES public_keys (id) PRIMARY KEY,
    url TEXT NOT NULL,
    peer TEXT NOT NULL,
    grant_blob INTEGER REFERENCES blobs (id) ON UPDATE CASCADE,
    last_check INTEGER,
    last_status TEXT NOT NULL DEFAULT 'unknown',
    last_error TEXT
) WITHOUT ROWID;

The declared site of each space this peer knows: url and peer from the space root's site attribute, grant_blob the sync Grant held by that peer, and the last reachability check. The URL alone grants nothing; the peer is an authority peer only while grant_blob is live in authority. Replaces domains, which cached /hm/api/config lookups.

`unread_nodes`

CREATE TABLE unread_nodes (
    space INTEGER REFERENCES public_keys (id) NOT NULL,
    node TEXT NOT NULL,
    PRIMARY KEY (space, node)
) WITHOUT ROWID;

Nodes whose state changed through a transfer and that the local user has not opened. Replaces unread_resources; keyed by node id, so a move does not reset read state.

`wallets`

Unchanged from today: Lightning wallet credentials per local account.

The lifecycle of a blob

flowchart TD
  A[Transfer: Fetch result, Offer, or local Publish<br/>claimed scope + peer + account] --> B[blobs row<br/>fill placeholder or insert]
  B --> C{indexable codec?}
  C -- no --> R[raw or dag-pb: stop]
  C -- yes --> D[handler: decode by type, verify signature]
  D -- missing dep or no authority --> S[stashed_blobs]
  D --> E[structural_blobs + blob_links + node_links]
  E --> F{type}
  F -- Grant, Revocation, Group --> G[grants / revocations / groups]
  G --> H[authority pass for the subject's space]
  F -- Node, Change, Snapshot --> N[nodes fold: head_blobs, target, heads, version<br/>node_names, node_blobs]
  H --> K[readers pass: node_public, node_readers, node_bearers]
  N --> K
  K --> L[blob_access: project node_blobs, close over dep / proof / file]
  N --> M[facts, fts, fts_index]
  L --> O[unstash cascade for blobs waiting on this CID or signer]
  O --> P[commit; transfer_blobs verdicts]
  P --> Q[post-commit: scope_items patch, watch notifications, embeddings]

Every blob enters the same way regardless of sender: as part of a transfer with a claimed scope. The handler never consults the claimed scope to decide validity; it decides from the blob and the authority graph, then checks that the blob landed in the claimed scope and records the verdict. The authority pass always runs before the readers pass, and the readers pass before blob_access, within one write transaction, so no commit ever exposes a blob under a stale audience.

Reindex takes the single writer and runs in one transaction:

DELETE FROM every derived table in dependency order: scope_items, scopes, blob_access, node_readers, node_bearers, node_public, authority, facts, fts_index, fts, embeddings_index, embeddings, node_links, blob_links, node_blobs, node_names, nodes, revocations, grants, groups, stashed_blobs, structural_blobs.

Replay SELECT * FROM blobs WHERE codec IN (dag-cbor, dag-pb) AND size > 0 ORDER BY id through the handler with the authority and readers passes deferred.

Run the authority pass once per space, then the readers pass once per space top-down from the space root, then blob_access once.

Recompute derived facts once per node.

Set kv.last_reindex_time.

Local-only tables are untouched. policies, sites and sync_runs key by text ids, so they remain valid afterwards. Scopes re-materialise lazily.

Query recipes

Every recipe uses :peer for the connected peer's peers.id (NULL for an HTTP caller) and :principals for the set of public_keys.id the caller has proven: the accounts in peer_accounts for the peer, plus any bearer hashes presented in the request.

May the caller read blob B?

SELECT EXISTS (
  SELECT 1 FROM blob_access ba
  WHERE ba.blob = :blob
    AND (
      EXISTS (SELECT 1 FROM node_public np WHERE np.space = ba.space AND np.node = ba.node)
      OR EXISTS (SELECT 1 FROM node_readers nr WHERE nr.space = ba.space AND nr.node = ba.node
                 AND nr.principal IN (SELECT account FROM peer_accounts WHERE peer = :peer AND expires > :now))
      OR EXISTS (SELECT 1 FROM node_bearers nb WHERE nb.space = ba.space AND nb.node = ba.node
                 AND nb.hash IN carray(:bearer_hashes))
    )
);

A 0 is returned both when the blob is not held and when it is held but not readable. Fetch lists such CIDs under missing.

Readers of node N.

SELECT 'everyone' AS who FROM node_public WHERE space = :space AND node = :node
UNION ALL
SELECT pk.principal FROM node_readers nr JOIN public_keys pk ON pk.id = nr.principal
WHERE nr.space = :space AND nr.node = :node;

Children of N visible to the caller.

SELECT n.id, n.name, n.kind, n.version
FROM nodes n
WHERE n.space = :space AND n.parent = :node AND n.is_deleted = 0
  AND (EXISTS (SELECT 1 FROM node_public np WHERE np.space = n.space AND np.node = n.id)
       OR EXISTS (SELECT 1 FROM node_readers nr WHERE nr.space = n.space AND nr.node = n.id
                  AND nr.principal IN carray(:principals)))
ORDER BY n.name;

Unnamed children are listed only to callers who can read them, and the count returned to anyone else is computed with the same filter, so a private child never changes a public count.

Scope set for scope S filtered to the caller (what Reconcile fingerprints and Fetch may serve).

SELECT si.blob, COALESCE(sb.ts, 0) AS ts, b.multihash, b.codec
FROM scopes s
JOIN scope_items si ON si.scope = s.id
JOIN blobs b ON b.id = si.blob AND b.size >= 0
LEFT JOIN structural_blobs sb ON sb.id = si.blob
WHERE s.space = :space AND s.node = :node AND s.depth = :depth AND s.facets = :facets
  AND EXISTS (
    SELECT 1 FROM blob_access ba
    WHERE ba.blob = si.blob
      AND (EXISTS (SELECT 1 FROM node_public np WHERE np.space = ba.space AND np.node = ba.node)
           OR EXISTS (SELECT 1 FROM node_readers nr WHERE nr.space = ba.space AND nr.node = ba.node
                      AND nr.principal IN carray(:principals))))
ORDER BY ts, b.multihash;

Who already holds blob B (the revocation report).

SELECT p.pid, d.ts, d.basis_kind, d.basis_account, d.basis_grant
FROM disclosures d JOIN peers p ON p.id = d.peer
WHERE d.blob = :blob ORDER BY d.ts DESC;

What did we accept from peer Q.

SELECT t.id, t.ts, t.space, t.node, t.depth, t.facets, t.received, t.indexed, t.stashed, t.rejected
FROM transfers t WHERE t.peer = :peer ORDER BY t.ts DESC;

May principal P publish Node N (the write check the handler runs).

SELECT MAX(a.level) FROM authority a
WHERE a.subject_kind = 'node' AND a.subject_space = :space AND a.principal = :signer
  AND (a.subject_node = :node
       OR (a.exact = 0 AND a.subject_node IN (SELECT id FROM ancestors_of(:space, :node))));

where ancestors_of is the recursive CTE over nodes.parent. A result of at least 3 (write) authorizes a Node, Change or Snapshot; for a creating Node blob the check runs against the parent.

What is not in the database

Account keys. They live in the encrypted vault file beside the database, wrapped by the OS keychain. SQLite holds public keys only.

The device key. A separate file; it signs connections, never blobs, and appears in no table.

Drafts. Unsigned work in progress belongs to the editing application, not to the daemon.

Bearer secrets. Only their hashes, in grants.audience_hash and node_bearers; a secret presented in a request is hashed and discarded.

Encrypted content keys. There are none yet; when encryption arrives it is a layer on Grants, not a table.

Migrating today's database

The conversion from HM24 is the reindex. The replay step passes every legacy blob (Ref, Capability, Comment, Profile, Contact) through the conversion box described in Migration, which derives the Stem records the handler would have produced from an equivalent Node, Grant or Snapshot: a Ref becomes a nodes row whose id is the SHA-256 CID of the earliest Ref for that path and genesis, a Capability a grants row whose subject is the migrated node and whose audience is the delegate key, a Comment an unnamed node of kind comment, a Profile the facts of the space root, a Contact an unnamed node of kind contact. The legacy blob keeps its structural_blobs row with the legacy type and records the derived node in extra_attrs, so a legacy blob and its conversion can always be joined. blobs and public_keys are untouched, subscriptions rows are rewritten into policies under the owning local account, domains rows seed sites without a grant_blob until the site publishes its sync grant, and unread_resources IRIs are mapped to unread_nodes through the migrated path lookup. No data leaves the machine and no blob is re-signed.

Principles

Today (HM24)

Permanent mirror

blobs

public_keys

Derived index

structural_blobs

nodes

node_names

node_blobs

grants

revocations

groups

authority

node_public and node_readers

blob_access

blob_links

node_links

facts

stashed_blobs

fts and fts_index

embeddings and embeddings_index

scopes and scope_items