Part II — The portable user database

7. The three actors

• LLM provider — serves model calls. Reached browser-direct (BYOK key or local OpenAI-compatible endpoint) or through a hub’s subscription path.
• Hub provider — a multi-tenant service that provisions Snug apps per user. A hub is a convenience, never a requirement: app execution must work with no hub backend.
• End user — owns apps and data as ONE file, portable across hubs, LLM providers, and devices.

8. The user database

One SQLite file per user (user.snug — §10) is the canonical artifact. PRAGMA user_version carries the storage schema version — currently 6 — and migrations are forward-only. A file stamped newer than the implementation understands is refused, never overwritten. Size cap: MAX_USERDB_BYTES (64 MiB).

Storage version	Change
v2	native per-app tables (structural; v1 blob data does not survive)
v3	added snug_auth_specs (superseded)
v4	added snug_connections — the requirement/grant split of Part III
v5	dropped snug_auth_specs (the one destructive migration — grants are NOT migrated: credential values survive in snug_secrets, and a previously-connected app re-enters the wizard, because inheriting an approval across the shape change would honor consent for a shape the user never saw)
v6	added snug_app_versions.runtime_contract_json (Part IV)

Two self-healing obligations (normative, easy to omit, expensive to omit):

1. Verify tables against sqlite_master; do not trust the version stamp. A forward-only migrator stamps user_version on completion, so the stamp claims which migrations ran — never that the tables they create exist. A conforming hub verifies the expected table set on open and replays the idempotent DDL on any miss. The expected set is the set of tables the replay can recreate: snug_auth_specs is deliberately absent from the replayed DDL, so self-healing can never resurrect the dropped table — and counting its absence as a “miss” would fire the heal (and a spurious persist) on every open of a healthy post-v5 file.
2. Wipe the legacy (pre-slot) credential slice on version advance, never on routine opens. The wipe runs when an open actually advances the file’s schema version — a migration step, not a recurring sweep, because the legacy writers were still live through the cutover and a wipe on every open would delete credentials they had just written. Legacy keys are auth:<appId>:<field> with no slot; the wipe MUST be scoped by segment count, never by prefix — both shapes begin auth:<appId>:, so a prefix delete would also take every live slot-keyed credential.

8.1 Hub-namespace tables (normative DDL in userdb-schema.ts, locked by snapshot test)

Table	Holds
snug_meta	db uuid, created-at (key/value)
snug_profile	display profile (key/value, JSON values)
snug_settings	mode, provider, model, endpoints (key/value, JSON values) — also the sanctioned home for host-internal namespaced keys (below)
snug_secrets	BYOK keys, connection credentials, personal-origin tokens (opaque strings; §12 and §13)
snug_apps	one row per app: display metadata, uses_db, current_version, install_source (unique when present — a starter identity installs at most once)
snug_app_versions	complete HTML per version + pinned + runtime_contract_json (v6, Part IV); hubs retain ≥ VERSIONS_RETAINED (5) unpinned versions, pruning oldest; pinned versions (v1 of a build or install, plus each installed starter update) are NEVER pruned — “the factory version” means the NEWEST pinned row; revert/reset = copy-forward as a NEW version
snug_app_schemas	one row per app with data: the app’s runtime sqlite_master DDL verbatim (objects in creation order + AUTOINCREMENT counters) and its namespace token
snug_app_migrations	append-only DDL audit per app (seq, statement, applied-at)
snug_app_docs	per-app knowledge wiki: (app_id, slug) → markdown. Advisory slugs: vision, requirements, plan, lessons, memory, next-tasks; the table shape is normative. A starter MAY seed rows at install; seeding MUST be absent-slugs-only — an existing row is never overwritten, because the wiki is the app’s living memory
snug_chat_threads / snug_chat_messages	every chat surface’s history; messages carry pinned (bootstrap turns survive pruning) and meta (JSON sidecar)
snug_sync	sync-origin CONFIG only (self-describing when ported)
snug_connections	connection requirements and grants, keyed (app_id, slot) — Part III §12

Namespaced snug_settings keys. Host-side state that is neither an app’s data nor a grant goes into snug_settings under a namespaced key rather than a new table — a new snug_ table is a portable-format change (version bump + migration + spec change), while a settings key is host-internal machinery. Two exist today and both travel in the file, so they are documented for transparency: appModel:<appId> (a per-app model preference; absent means inherit the global setting, live) and sidecarIdentityDirectory (a third-party-identity directory with its own lifecycle rules — §20).

8.2 Per-app data: native namespaced tables

Each app’s data lives as REAL tables in the same file under app_<token>__<name>, where token = appDataToken(namespace) — a normative, total, injective function of the host-assigned namespace: UUID-shaped → 32 lowercase hex (dashes stripped); anything else → 'x' + hex(utf8(namespace)) (the x prefix sits outside the hex alphabet, so the ranges cannot collide).

Rules (all normative):

• Reserved prefixes (case-insensitive): snug_, sqlite_, app_. App object names must match ^[A-Za-z][A-Za-z0-9_]{0,40}$ and carry no reserved prefix; the single exemption is the driver-internal snug_kv (at rest app_<token>__snug_kv). A conforming hub REFUSES to persist (fails closed, prior state retained) any runtime whose object names violate the rule — unvalidated names are never interpolated.
• Isolation is physical at runtime: app SQL executes only against a materialized database containing that app’s own objects under natural names; hub-namespace and other apps’ tables are unreachable — absent, not filtered.
• DDL is stored verbatim (tables, indexes, triggers, views, in creation order) and replayed on materialization; DDL bodies are never rewritten. At-rest names are produced by ALTER TABLE … RENAME (a pure name swap), never by editing statement text.
• Per-app export = materialize + export: a standalone .snug with natural names.
• Push-state (last pushed revision/hash) lives OUTSIDE the image (sidecar file), so the file never contains its own revision.

8.3 Client-authoritative writes

The user DB is the single source of truth in every mode. In subscription mode the hub may cache artifacts and thread history server-side, but the client fetches artifact content and writes it into the user DB itself; hub stores are transient caches.

9. Hub provider obligations

A conforming hub:

1. Never requires its backend for app execution — the hub client is static files; app reads/writes hit the browser copy (OPFS) of the user DB.
2. Offers Export/Import — one-click download/upload of the canonical .snug (default export strips snug_secrets and VACUUMs; including secrets is explicit opt-in). Import treats the file’s endpoint settings as executable config and requires user re-confirmation before agent turns run. Import obligations specific to connections and contracts are in §12.4 and §18.2.
3. May host the user DB as the default sync origin via:
   • GET /userdb → 200 bytes + ETag revision (application/octet-stream, nosniff, no-store) or 404 when none.
   • PUT /userdb with If-Match: <revision> (or If-None-Match: * first write) → 204
     • new ETag; mismatch → 412 + current ETag; missing precondition → 428; over-quota → 413. Cookie auth requires CSRF double-submit (x-snug-csrf). Unauthenticated → 401; CORS is fail-closed (explicit origin, credentialed).
   • First login provisions the user record only — a hub never creates an empty DB image that could clobber local state; the client pushes up.
4. Supports pluggable origins through the SyncProvider contract (info/pull/push(bytes, baseRevision)); personal origins (e.g. Dropbox) may carry secrets on explicit opt-in. Conflict policy v1: revision-token CAS; divergence is surfaced to the user; last-writer-wins only on explicit user action.

10. File naming

The canonical user file is user.snug; the artifact a hub offers for download is snug-user.snug. .snug is the Snug Protocol’s extension for the one portable file a user owns.

The extension is a naming convention, not a format claim. A conforming implementation determines a file’s format from its leading bytes, never from its name:

Leading bytes	Format
SQLite format 3\0	a plain user database (§8)
SNUGENC1\n	a protected user database (§11)

• Implementations SHOULD accept the historical .sqlite extension on input — users hold exports and backups made before this revision — and MUST NOT reject a file on its extension alone.
• A hub that finds a pre-existing user.sqlite and no user.snug MUST read it and adopt the canonical name on its next write, without renaming, copying or deleting the original. The old file remains the user’s own backup; once the canonical file exists it takes precedence.

The same read-and-adopt rule applies to any name a hub derives from the user file — sync sidecars, quarantine copies, remote sync paths. Renaming a file an implementation looks for is a data-loss operation unless every derived name moves with it.

11. Protected user files — the SNUGENC1 container

A user MAY protect their file with a passphrase. A protected file is not a SQLite database; it is a container carrying one.

This section is normative because misidentifying a protected file destroys data. A hub that does not recognise the magic concludes the bytes are corrupt. A conforming hub MUST detect it and prompt for a secret; it MUST NOT treat a protected file as corruption, MUST NOT quarantine or overwrite it, and MUST NOT create a fresh empty database beside it.

Protection is optional and reversible. A conforming hub never requires it, opens an unprotected file exactly as §8 describes, and can return a protected file to plaintext on the user’s instruction.

11.1 Layout

offset  size      field
0       9         magic            "SNUGENC1\n"
9       1         version          0x01
10      2         kdf id           0x0001 = PBKDF2-HMAC-SHA256
12      4         iterations       u32 big-endian (reference: 600,000)
16      16        salt
32      2         slot count       u16 big-endian (structural range 1–8; 0 or >8 → corrupt. Creating a 1-slot container is nonetheless forbidden — rule 2)
34      4         header checksum  FNV-1a/32 over the header with this field zeroed
38      …         slot table       slot count × 61 (see below)
…       …         wrapped keys     slot count × 48 (AES-256-GCM of the 32-byte file key)
…       12        payload IV
…       …         payload          AES-256-GCM of the SQLite bytes of §8

Each slot-table entry is 61 bytes and only the first 13 are written: { kind:u8, iv:12 } followed by 48 reserved bytes that MUST be zero. The stride is stated explicitly because it is load-bearing for interoperability rather than cosmetic: the header through the end of the slot table is the GCM additional authenticated data (rule 4 below), so an implementation that packs entries at 13 bytes computes a different AAD span and cannot open a conforming file at all — the failure is a wrong-secret error on a perfectly good container, which rule 6 exists to prevent misreporting. For two slots the header is 38 + 2×61 = 160 bytes, and the wrapped keys begin there.

The reserved region is where each slot’s wrapped key would sit if the two were interleaved; the reference implementation stores the wrapped keys contiguously after the header instead, so the space is carried and zeroed rather than reclaimed. It is not a version-negotiation seat — a future revision that uses it takes a new magic string.

Slot kinds: 0x01 passphrase, 0x02 recovery key. The iteration count lives in the header so it can be raised later without orphaning old files.

11.2 Rules (all normative)

1. Key wrapping. A random 32-byte file key encrypts the payload; each slot independently wraps that file key under a key derived from its own secret. Changing one secret MUST NOT require re-encrypting the payload or invalidate other slots.
2. Two slots minimum. An implementation MUST NOT create a container with only a passphrase slot. A single point of loss with no recovery path is not an acceptable shape for a user’s only copy of their data.
3. Recovery-key entropy MUST be at least 128 bits. Mind the arithmetic when the alphabet excludes ambiguous glyphs: 26 symbols of a 30-glyph alphabet is 127.6 bits, and a base-32 assumption hides the shortfall. (The reference implementation uses 27 symbols ≈ 132.5 bits.)
4. AAD. The header — offset 0 through the end of the slot table — MUST be supplied as GCM additional authenticated data for every slot unwrap and for the payload.
5. Nonces. Every IV MUST be 12 fresh CSPRNG bytes per encryption operation. Counters and derived nonces are forbidden: an implementation may write one logical save into two physical slots, so a repeat is reachable in ordinary operation, and a repeated GCM nonce discloses plaintext and forges the authentication key. One stated exception: re-wrapping a slot under a NEW KEK (a passphrase change) MAY reuse that slot’s existing IV — the key/IV pair is still unique because the key is fresh, and reuse is what keeps the header (and therefore the AAD every other slot was bound to) unchanged. An IV MUST NOT be reused with the same key over differing plaintext or AAD.
6. Failure reporting. Implementations MUST distinguish locked (a structurally valid container that no supplied secret opened) from corrupt (malformed, truncated, or a failed header checksum). Reporting damage as a wrong passphrase sends a user hunting for a secret that was never the problem; reporting a wrong passphrase as damage invites them to destroy a healthy file. The header checksum is an integrity hint for this purpose — it is unkeyed and does not resist a tamperer; rule 4 is what makes tampering fail.
7. A locked file is never quarantined, rewritten or replaced. It is healthy.
8. Portability. The container MUST be self-opening: everything needed to unwrap it, apart from the secret, travels inside it. No implementation may require state held outside the file — which is what keeps §7’s portability promise true for protected files.
9. Size limits (MAX_USERDB_BYTES, §8) apply to the PLAINTEXT the container carries, not to the container.

11.3 Custody, unchanged

Protection changes where the file is readable, not who holds custody. §13’s custody rules stand: the key was always the user’s and still is. The claim this supports is exactly “the file can be encrypted with a passphrase only the user holds” — it is not zero-knowledge, not end-to-end encryption, and it says nothing about a host page that has already unlocked the file. A hub-origin sync copy remains the secrets-stripped plaintext of §9.

A file whose passphrase and recovery key are both lost is unrecoverable. There is no escrow and no reset. That is the property, not a gap in it, and an implementation MUST state it plainly to the user before protection is enabled rather than afterwards.

---