meta-core/docs/metadata-storage-structure.md

# Metadata Storage Structure

How meta-core lays out metadata on disk since **v2.0.0 (claims model)**: the
two SQLite databases, their tables, how a record is assembled from signed
votes, and the schema-version gate. Implementation basis:
[`docs/implementation/metacore-claims-model.md`](../../../docs/implementation/metacore-claims-model.md).
This document does **not** enumerate field semantics — that is the job of the
repo-root [`METADATA_KEYS.md`](../../../METADATA_KEYS.md), the authoritative
key registry for everyone who writes or reads metadata.

Related: why rids are UUIDs and hidden —
[`uuid-rooted-metadata.md`](uuid-rooted-metadata.md); why the engine is
shaped this way, its guarantees and measurements —
[`metacore-storage-engine.md`](../../../docs/project-architecture/metacore-storage-engine.md);
the external-access contract (HTTP-only, SSE for events) —
[`api-mediated-access.md`](api-mediated-access.md).

> **History.** Until 2026-10 metadata lived in Redis; v1.1.0–v1.2.x kept the
> same flat keys (`file:<rid>/<field>`, `cid:<cid>`, `idx:*`, `dup:*`,
> `udl:*`, `ev:*`) in one SQLite string→string `kv` table. v2.0.0 replaced
> that keyspace entirely; the old layout is summarised at the end
> ([Previous layout](#previous-layout-v11v12-kv-table)).

## Quick summary

- **Every metadata value is a signed claim**: `claim(uid, cid, key) → value`
  + `ts` + `sig` (MMUR2, secp256k1). One row per signer per field. Writers
  sign; meta-core never signs.
- **Two databases.** *Truth* `/meta-core/db/meta-core.db`
  (`META_CORE_DB_PATH`, backed up, primary keys only): `principal`, `claim`,
  `cid_group`, `meta`. *Cache* `/meta-core/db-cache/cache.db`
  (`META_CORE_CACHE_PATH`, never backed up, rebuildable): the v1 `kv` table
  (merged view as `file:<rid>/<field>` rows, lookup, counters, event streams,
  bookkeeping, service scratch keys) with its `kv_field_v` index and `fts`,
  plus two real tables, `claim_by_pid` and `local_file`.
- **Schema-free.** A field is a `key` string in a row; a new field never
  needs DDL.
- **Records are cid groups.** A vote names a cid, never a rid. `cid_group`
  maps each cid to a **rid** — a hidden, locally minted UUIDv7 (26-char
  Crockford) naming the group. Examples use `01JKR8XW5T4QMV7AHNJ2DEFGHK`.
- **What readers get is the merged view**: one precomputed value per
  `(rid, key)`, stored as the cache row `file:<rid>/<key>`. `nsrc` (how many
  signers support it) is computed by the merge and returned by
  `POST /api/merge`, not stored. `canonical_cid` is still **derived on read**
  by rank, never stored.
- **Watcher facts are not claims**: `filePath`, size, `mtimeNano`, midhash
  live in `local_file` and are joined into the record view unsigned.
- **A schema-version sentinel** (`schema-version = "3"` in truth `meta`).
  meta-core explicitly refuses a truth file holding the v1 `kv` table, and any
  store with claims but no sentinel or another version.

## Truth database — `/meta-core/db/meta-core.db`

Backed up; only primary keys (plus `principal.uid`'s implicit UNIQUE index).

| Table | Columns (PK first) | Notes |
|---|---|---|
| `principal` | `pid` INTEGER PK, `uid` TEXT UNIQUE | `uid` = `"z" + base58btc(33-byte compressed pubkey)`. `pid` is a local surrogate: never signed, never on the wire |
| `claim` | `(cid, key, pid)` PK, `value` ANY, `ts`, `sig`, `seq` | `STRICT, WITHOUT ROWID`. `value`: TEXT = plaintext, BLOB = ciphertext envelope, NULL = retraction (tombstone, kept). `ts` (ms) is the version. `seq` = global change counter, not indexed |
| `cid_group` | `cid` PK, `rid` | the cid → rid map: the record identity layer |
| `meta` | `name` PK, `value` | `schema-version = "3"`, `last_seq` |

`claim` is written **only** by `POST /api/claims`. A claim replaces the
stored row for its `(cid, key, pid)` only if its `ts` is strictly greater;
identical `(ts, sig)` is `unchanged`; the **same value** from the same signer
(any `ts`) is also `unchanged` and keeps the stored row with its older `ts`;
otherwise older or equal is `stale`, and a bad signature is refused. Why `STRICT` with an `ANY` column: a non-STRICT `ANY`
column gets NUMERIC affinity and turns the text `"007"` into the integer 7;
STRICT keeps the written type, which is the cipher flag.

## Cache database — `/meta-core/db-cache/cache.db`

Never backed up (the store app lists the folder in `backup.exclude`).
Deleting it is the recovery procedure: meta-core rebuilds it from truth at
boot (no `meta:applied_seq` → full rebuild), and `local_file` refills from a
rescan.

It keeps the **v1 key/value layout** (`internal/storage/client.go`): one
`kv(k TEXT PRIMARY KEY, v TEXT NOT NULL) WITHOUT ROWID` table, whose rows are
all derived — no client writes `file:` rows any more.

| `kv` rows | Holds | Written by | Read by |
|---|---|---|---|
| `file:<rid>/<field>` | the **merged view**, one row per `(rid, key)` — the only rows under `file:`. Includes the synthesised `cids/<cid> = "true"` members (from `cid_group`) and the box-local `filePath` / `sizeByte` / `mtimeNano` (from `local_file`) | apply loop | every record read; search |
| `cid:<cid>` | cid → rid (copy of truth `cid_group`) | apply loop, watcher | every cid-addressed request |
| `idx:roots/<rid>` | the set of every record | apply loop | listing, counts |
| `idx:fts/<rid>` | rowid of the record's `fts` row | apply loop | FTS maintenance |
| `idx:count/*`, `idx:schema/*` | record count, per-field schema counters | the cache engine's change hook, from `file:` deltas | search `total`, `/api/stats`, `/api/schema` |
| `ev:<stream>/<ms13>-<seq10>`, `evhwm:<stream>` | `meta:events` (every `file:` change, same transaction) and `file:events` (watcher) | cache engine; watcher | `/api/events/meta`, `/api/events/files` (SSE) |
| `meta:applied_seq`, `meta:rebuilt_at` | how far the cache has applied truth; last rebuild (older SSE cursors get `gap`) | apply loop, rebuild | boot catch-up; SSE |
| anything else | service scratch KV (`btmagnet:*`, `searchcov:*`, `nzb:*`) | other services via `/api/kv/value` (reserved prefixes refused) | same |

| Index / table | Shape | Written by | Read by |
|---|---|---|---|
| `kv_field_v` (index) | `kv(lower(field parsed from k), lower(v), v)` over `file:` rows only | — | filter search |
| `fts` | FTS5 trigram `(rid UNINDEXED, hay)`, one row per record | apply loop, when a haystack field's merged value changes | free-text search |
| `claim_by_pid` | `(pid, cid, key)` PK | apply loop | `?as=<uid>` overlay, `/api/udl/*` facade, identity retraction, `/api/udl/users/stats` |
| `local_file` | `path` PK → `midhash`, `size`, `mtime`; index `(midhash, path)` | file watcher, `POST /api/files/register` | record view (`filePath`, `sizeByte`, `mtimeNano`) |

The cache is **behind, never wrong**: after an unclean shutdown, boot finds
`meta:applied_seq < last_seq` and re-applies every truth claim with `seq >
applied_seq` (or rebuilds in full when one of them is a `cids/*` link). Write
path, apply loop and durability are detailed in
[`metacore-storage-engine.md`](../../../docs/project-architecture/metacore-storage-engine.md) §4.

## Keys

The `key` column carries the same field paths METADATA_KEYS.md defines —
what used to follow `file:<rid>/`:

```
claim(cid=bagacbabaec…, key=title,                          uid=<tmdb plugin>) = "Inception"
claim(cid=bagacbabaec…, key=fileinfo/duration,              uid=<ffmpeg plugin>) = "8878.5"
claim(cid=bagacbabaec…, key=fileinfo/streamdetails/video/0/codec, uid=<ffmpeg plugin>) = "h264"
claim(cid=bagacbabaec…, key=genres/Drama,                   uid=<tmdb plugin>) = "true"
claim(cid=bagacbabaec…, key=cids/bafybeigd…,                uid=<full-hash plugin>) = "true"
```

- `/` is the path separator; nested objects are flattened on write and
  reconstructed on read.
- **Key-sets** put the member inside the key (`genres/<g>`, `cids/<cid>`,
  `titles/eng/<name>`, `anchoredBy/tmdb:123`): one claim per member, union by
  the ordinary merge, removal by a signed retraction.
- Plugins keep writing under their prefixes (`{plugin-id}/<field>`,
  `{plugin-id}/<group>/<n>/<field>`), each signing with its own key. Two
  sources compete on one key only when they make the same kind of statement;
  a different fact (e.g. a filename guess vs a TMDB lookup) gets a different
  key.

## Records: `cid_group` and the hidden rid

```
votes (truth, signed)                    cid_group (truth)     cache kv rows (merged)
(A, M, title)    = "The Matrix"          M → R1                file:R1/title    = "The Matrix"
(A, M, cids/Q)   = "true"                Q → R1                file:R1/cids/M   = "true" (synthesised)
(B, Q, title)    = "Matrix, The"                               file:R1/cids/Q   = "true" (synthesised)
                                                               file:R1/sizeByte = "734003200"
(B, Q, sizeByte) = "734003200"
```

- A text `cids/<other>` vote on cid X links X and `<other>` into one
  group. Retracting it does **not** split the group (phase 1). In the merged
  view, membership is synthesised from `cid_group` — `cids/<cid> = "true"` for
  every cid of the group — and `cids/*` claims are never merged. Record R = every vote on every cid of R's group, merged per key,
  each signer counted once per key.
- First-seen cid with no link → new group with a fresh rid
  (`storage.NewUUID()`, UUIDv7 → 26-char Crockford Base32, ULID layout).
- Linking two groups → the **older rid survives**; the other group's cids
  move to it; `/api/events/meta` emits `del` for each key of the losing rid
  and `set` for the survivor. There is no rid alias table.
- `GET /api/meta/<cid>` is a `cid:<cid>` lookup in the cache, then a range
  read `[file:<rid>/, file:<rid>0)` — any known cid, any hash algorithm, no
  primary.
- Rids are local names: never signed, never on the swarm or gateway wire.
  Local consumers (meta-sort, meta-fuse, meta-stremio) do hold them, which is
  why `cid_group` lives in truth.

There is **no stored `canonical_cid`**. `GetMetadataDocument` ranks the
group's cids through `internal/cid/rank.go` (`cid.Better`) and returns the
winner as `canonical_cid` in the JSON response only.

## Merge

The cache row `file:<rid>/<key>` holds the winner over the group's votes
(each signer's newest vote counts once; retractions and ciphertext drop out):
most distinct signers (values compared after **trim + case-fold only**, no
NFC) → oldest `ts` → byte-wise smallest. The stored value is the winner's
exact text; `nsrc` is not stored (`POST /api/merge` returns it). With
`?as=<uid>`, the viewer's own live text votes (found via `claim_by_pid`)
override at read time; they are never written to the cache. Box-local
fields: when several local paths hold the same content, `filePath` is the
lexicographically first (size and mtime from the same `local_file` row); a
signed `sizeByte` claim wins over the on-disk size. Full rules:
implementation basis §7 (`internal/storage/merge.go`).

## Reads and writes

| Operation | Endpoint | Store access |
|---|---|---|
| write metadata | `POST /api/claims` (batch of signed claims) | truth upsert, then the apply loop updates the cache |
| removed writes | `PUT/PATCH/DELETE /meta/*`, `POST /meta/{h}/_add/{k}`, `PUT/DELETE /api/metadata/{id}`, `PUT /api/metadata/{id}/property`, `POST /api/metadata/batch` | `410 Gone`, pointing at `POST /api/claims` |
| read a record | `GET /api/meta/{cid}`, `GET /api/metadata/{rid}`, `GET /file/{cid}`, `GET /meta/{h}` | `cid:` lookup → `file:<rid>/*` rows, optional `?as=` |
| list / search | `GET /api/metadata/hash-ids`, `/list`, `POST /api/metadata/search`, `GET /api/files/tuples`, `/api/schema`, `/api/stats` | `file:` rows, `kv_field_v`, `fts`, `idx:count/*` / `idx:schema/*` |
| raw votes | `GET /api/meta/{cid}/claims`, `GET /api/metadata/{rid}/claims` (`?key=`, `?uid=`) | `{rid, cids, claims}`: every live signed claim of the group, all signers, tombstones excluded; 404 for an unknown cid / rid |
| merge without storing | `POST /api/merge` `{claims, as?}` → `{fields, nsrc, rejected}` | none — verify + merge in memory (plus `as`'s votes this core holds) |
| watcher facts | the watcher, `POST /api/files/register` | `local_file` + `cid_group` |
| service scratch | `PUT/GET/DELETE /api/kv/value` | non-reserved cache `kv` rows |
| wipe | `POST /api/metadata/clear` | both databases |

Truth writes go through **one writer goroutine** with group commit; reads use
a separate pool and see committed state (WAL). Read paths and shapes are
unchanged from v1.x — they are now served from the derived `file:` rows.

## Reconstruction (flat → nested)

The HTTP API reconstructs the merged flat keys into a nested JSON object:

```
flat                                          nested
─────────────────────────────────────         ───────────────────────────
title                       = "Inception"     title: "Inception"
fileinfo/duration           = "8878.5"        fileinfo: { duration: "8878.5",
fileinfo/streamdetails/                                  streamdetails: { video: { 0: {
  video/0/codec             = "h264"                       codec: "h264", ... } }, ... } }
streams/0/language          = "eng"           streams: [ { language: "eng" }, ... ]
```

- Numeric path segments become array indices on read.
- All values are strings on the wire; type interpretation is the caller's
  job (documented per field in `METADATA_KEYS.md`).
- Box-local fields from `local_file` are joined in unsigned; they never
  appear in `GET /api/meta/{cid}/claims` or on the swarm.

## Search structures

Both live in the cache and are maintained in the same transaction as the
merged-row change, so results are fresh as soon as a change is applied:

- **`kv_field_v`** — an expression index over the field name parsed out of
  the key, `lower(v)` and `v`, restricted to `file:` rows. Answers
  "which records have field F with a value like V" for any field, including
  ones invented tomorrow; covering for the substring test.
- **`fts`** — FTS5 trigram `(rid UNINDEXED, hay)`, one row per record: the
  haystack of its searchable merged fields plus the rid. Every 3+ character substring is
  indexable.

`POST /api/metadata/search` builds a candidate superset in SQL and verifies
each hydrated merged record with the Go matchers (`recordMatchesFilter`,
`matchAllTokens`); results come in rid order. Plan, rejected shapes and the
measurement status are in
[`metacore-storage-engine.md`](../../../docs/project-architecture/metacore-storage-engine.md) §8.

## Schema-version sentinel

Truth `meta` row `schema-version` = `"3"`, set at first boot and checked on
every boot (`internal/storage/schema_version.go`), after both files are
opened and before the cache catch-up. A truth file holding the v1 `kv` table
(v1.x, `meta-core:schema-version = "2"`) is **explicitly refused**, naming the
migration tool; claims without a sentinel, or another version, are refused
too; a fresh store is stamped. Alpha policy: no migration code in the
service binary; operators wipe and restart. watch.nsl.sh is converted once by
the separate one-off tool `packages/meta-core/tools/claims-migrate` (not in
the image; rids keep their UUIDs; legacy fields re-signed by a throwaway key;
UDL cells re-signed by their account keys — implementation basis §11).

## Event streams

| Stream | Producer | Contents |
|---|---|---|
| `/api/events/meta` | the cache engine, as `ev:meta:events/…` rows of the cache `kv` table | `{type: set\|del, key, ts}` for every change of a **merged** value, in the same cache transaction as the change |
| `/api/events/files` | `internal/watcher` dispatcher (`ev:file:events/…` rows) | `add` / `change` / `delete` / `rename` / `reset` for files on `/files` (unchanged) |

- A vote that does not change the merged value emits nothing.
- A group merge emits `del` for every key of the losing rid and `set` for the
  survivor's keys.
- IDs keep the `<ms>-<seq>` form and stay time-based, so persisted cursors
  keep comparing, including across a cache rebuild.
- `Last-Event-ID: 0` / `0-0` returns a snapshot (one `set` per merged key)
  then the live tail; only the last snapshot event carries an `id:`.
- A trimmed cursor gets one `event: gap`. A cache rebuild emits no per-key
  events; consumers get `gap` and take a snapshot.
- Heartbeats are SSE comments (`:keep-alive\n\n`) every 30 s of silence.
  Contract: `internal/api/sse_events.go`, [`api-mediated-access.md`](api-mediated-access.md).

## Debugging

Paths: truth `/meta-core/db/meta-core.db`, cache `/meta-core/db-cache/cache.db`
(image default and store app); dev stacks keep both on named volumes (the
`/meta-core` bind is 9p on Windows dev boxes, where SQLite WAL is unsafe).
Always open **read-only**; writes go through `POST /api/claims`.

```bash
T=/meta-core/db/meta-core.db; C=/meta-core/db-cache/cache.db   # dev: the named-volume paths
QT() { docker exec metacore-app sqlite3 -readonly "$T" "$1"; }
QC() { docker exec metacore-app sqlite3 -readonly "$C" "$1"; }

QT "SELECT rid FROM cid_group WHERE cid = 'bafkreih...'"                 # cid → rid
QT "SELECT cid FROM cid_group WHERE rid = '01JKR8XW5T4QMV7AHNJ2DEFGHK'"  # the group (full scan: no rid index)
QC "SELECT k, v FROM kv WHERE k >= 'file:01JKR8XW5T4QMV7AHNJ2DEFGHK/' AND k < 'file:01JKR8XW5T4QMV7AHNJ2DEFGHK0'"   # merged record
QT "SELECT c.key, typeof(c.value), c.value, p.uid, c.ts, c.seq
    FROM claim c JOIN principal p USING(pid) WHERE c.cid = 'bafkreih...'" # every vote on one cid
QT "SELECT name, value FROM meta"; QC "SELECT k, v FROM kv WHERE k LIKE 'meta:%'"   # last_seq vs applied_seq
QC "SELECT path, midhash FROM local_file WHERE path LIKE '/files/watch/%' LIMIT 10"
QC "SELECT k, v FROM kv WHERE k >= 'ev:meta:events/' AND k < 'ev:meta:events0' ORDER BY k DESC LIMIT 10"

# HTTP equivalents — preferred
curl -k 'https://metacore-dev.localhost:8083/api/meta/<cid>'          # merged document (+ canonical_cid)
curl -k 'https://metacore-dev.localhost:8083/api/meta/<cid>/claims'   # signed votes
curl -k 'https://metacore-dev.localhost:8083/api/events/meta'         # live SSE
```

## Key implementation files

| Component | Path |
|---|---|
| Single-writer, group-commit SQLite engine | `internal/kv/` |
| Truth, apply loop, merge, local files, counters | `internal/storage/{truth,apply,merge,localfile,client}.go` |
| Search (`kv_field_v` + FTS) | `internal/storage/search.go` |
| MMUR2 claim encoding + verification | `internal/claim/` (secp256k1 in `internal/identity/`) |
| UUIDv7 minting | `internal/storage/uuid.go` |
| Canonical-CID rank | `internal/cid/rank.go` |
| Schema-version gate | `internal/storage/schema_version.go` |
| SSE event mirror + snapshot | `internal/api/sse_events.go` |
| Watcher → `local_file`, `/api/events/files` | `internal/watcher/` |
| Field semantics (authoritative registry) | [`/METADATA_KEYS.md`](../../../METADATA_KEYS.md) |

## Previous layout (v1.1–v1.2 `kv` table)

Kept so old notes and scripts can be read. In v2.0.0 the **truth** database
has no `kv` table at all; the **cache** keeps the same key layout for derived
rows (see above), but nothing in it is written by clients.

| Key | Value | Replaced by |
|---|---|---|
| `file:<rid>/<field>` | one value per field, last writer wins | `claim` (one row per signer) → derived cache `file:` rows |
| `cid:<cid>` | `<rid>` | `cid_group` (truth), copied to cache `cid:` rows |
| `idx:roots/<rid>`, `idx:count/*`, `idx:schema/*`, `idx:fts/<rid>` | root set, counters, FTS rowid | same keys, derived, in the cache |
| `dup:<rid>/<path>` | duplicate path | `local_file` rows |
| `udl:rec:<uid>/<cid>/<key>`, `udl:idx:*` | User Data Layer cells (MMUR1, `version`) | `claim` (MMUR2, `ts`) + `claim_by_pid` |
| `ev:<stream>/<ms13>-<seq10>`, `evhwm:<stream>` | stream rows | same keys, in the cache |
| `btmagnet:*`, `searchcov:*`, `nzb:*` | service scratch | same keys, in the cache |
| `meta-core:schema-version` = `"2"` | gate | truth `meta` `schema-version` = `"3"` |

The flat-key idea survived — a field is still one string key, a key-set
member is still inside the key — but the record moved from "one value per
field" to "one value per signer per field".