# ADR-0007: One database for Inception Space — worlds, rooms, global users - Status: **accepted 2026-08-04, with the owner's amendments** (same day). The design review's shape stands: "the whole of Inception Space uses ONE database; Inception Space has many worlds — the Space Museum is a world; a world has many rooms; users are global." Amendments: permissions stay a simple public/private visibility (no three-layer status machinery); classes/memberships are deferred; avatars get an ownership registry but NO web upload (content-safety choice); AI approval records stay a future note. The database was renamed `inception-db` by the owner the same day. - Amended 2026-08-07 (owner): on an ASSET — an object, an avatar — `private` means only its owner may **use** it; everything is visible to everyone. See "`private` on an asset means...". Rooms are unaffected: a private room is still a hidden document. - Amended 2026-08-06 (owner): a room's ownership reaches its **corridor door** and its **transit chamber** — see "How far ownership reaches" below and `docs/archive/ROOM-DOOR-AND-TRANSIT.md`. - Amended 2026-08-07 (owner): the **object library is owned too**, the same way avatars are — `objects` below, created by migration 3. - Amended 2026-08-07 (owner): **a saved room is what everyone sees** — the database is the source of truth for room documents, the repository file is only a seed, and the client reads the API. See "How a room is read". - Supersedes: the single-table sketch in ADR-0005 (the `rooms` table without a world above it) and the `museum-db` name. - Related: ADR-0004 (auth stays in the Lucas Account service), ADR-0005 (documents in Postgres, blobs in R2, cache ≠ database), `docs/archive/AVATAR-STUDIO.md` (paintings), execution plan §2.3 (the three authorization layers) and §5 (artwork pipeline), `docs/DISCOVERY-SEO-GEO.md` (what may ever be published). ## The shape of the product, as the schema sees it ```text Inception Space (one database: inception-db) ├─ worlds 1..n ("space-museum" is one; more will come) │ └─ rooms 1..n per world (the room document stays the unit of editing) ├─ users global (identity = verified email from Lucas Account JWTs) │ └─ avatar_paintings global per user — NOT per world └─ [future] artworks, classes, avatars — see below ``` Two principles carried over unchanged from ADR-0005: 1. **This database stores product documents, not identity.** Accounts, OTP codes and signing keys live in the Lucas Account service's own D1; here an email is just a verified string from a JWT. 2. **Small mutable documents in Postgres; big immutable blobs in R2; ephemeral state in memory.** Presence, room passes (stateless HMAC) and the painting relay cache deliberately have NO tables. ## Schema v2 ```sql -- Worlds: the museum is one row. Its geometry still comes from the code -- manifest; `config` is the seam where per-world knobs (sky, gravity -- default, corridor length) migrate out of code when they need to differ. CREATE TABLE IF NOT EXISTS worlds ( id TEXT PRIMARY KEY, -- "space-museum" name TEXT NOT NULL, -- "Space Museum" kind TEXT NOT NULL DEFAULT 'museum', -- world template owners TEXT[] NOT NULL, visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public','private')), config JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_by TEXT ); -- Rooms: exactly today's table, plus the world above it. A room id stays -- readable ("room-a") and unique WITHIN its world. CREATE TABLE IF NOT EXISTS rooms ( world_id TEXT NOT NULL REFERENCES worlds(id) ON DELETE CASCADE, id TEXT NOT NULL, title TEXT NOT NULL, owners TEXT[] NOT NULL, -- projection of doc.owners visibility TEXT NOT NULL CHECK (visibility IN ('public','private')), doc JSONB NOT NULL, -- the room document v2 (truth) updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_by TEXT, PRIMARY KEY (world_id, id) ); CREATE INDEX IF NOT EXISTS rooms_owners_idx ON rooms USING GIN (owners); -- Users are global: one row per verified email, holding what the PRODUCT -- needs (never what auth needs). `display` carries the public pseudonym -- -- the discovery rules (R6) forbid real names on any published surface -- -- and synced preferences (chosen avatar, gravity) when we want them to -- follow the child across devices. CREATE TABLE IF NOT EXISTS users ( email TEXT PRIMARY KEY, display JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT now(), last_seen TIMESTAMPTZ ); -- Avatar paintings are user-global (the owner's word): your coat follows -- you into every world. Ids stay content hashes so the relay cache, the -- database and presence all name the same painting the same way. -- `avatar_id` is load-bearing, not decoration: a painting's material keys -- are positions in THAT avatar's material list, so a painting may only ever -- be worn by the avatar it was made for (docs/archive/AVATAR-STUDIO.md). CREATE TABLE IF NOT EXISTS avatar_paintings ( id TEXT PRIMARY KEY, -- content hash email TEXT NOT NULL REFERENCES users(email) ON DELETE CASCADE, avatar_id TEXT NOT NULL, painting JSONB NOT NULL, -- v1 record; move to R2 if it grows worn BOOLEAN NOT NULL DEFAULT true, -- the one you wear created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- "One painting per email for now": enforce with UNIQUE (email) today; -- relax later to UNIQUE (email, avatar_id, slot) with slot < 3 and at most -- one worn=true per (email, avatar_id). CREATE UNIQUE INDEX IF NOT EXISTS avatar_paintings_one_per_email ON avatar_paintings (email); ``` ## How far ownership reaches (owner 2026-08-06) A room's owners own more than the space behind the wall: - **the door on the corridor** that leads into the room (the sheet of light at that portal slot, and its nameplate), and - **the transit chamber** ridden while the room loads — today one shared chamber, tomorrow the room's own arrival. Both are part of the room's welcome, so both ride in the ROOM document and inherit its owners; nothing in the schema changes, and `authorizeWrite` already refuses a stranger. A slot with no room behind it, and the corridor itself, stay with the world's owners. The door is currently a flat colored plane and is meant to become an `.lpr` surface like every other face. The document seam is declared in `docs/archive/ROOM-DOOR-AND-TRANSIT.md` (a `door` block and an optional `transit` block, each naming a package plus the photograph it replays, validated by the same rule the faces use), together with the door's authoring dimensions and palette. Editing them inside the room editor is a later step; the ownership rule above is what makes it a room owner's control and not the world owner's. ## `private` on an asset means "only the owner may USE it" (owner 2026-08-07) The word was doing two jobs, and one of them was wrong. For a ROOM, `private` really does hide the document: a stranger gets `room_locked` and nothing inside it travels. For an **asset** — an object in the library, an avatar — the owner meant only usability: > the "private" I meant is actually usability (only owner can use it), and > visibility should be: all objects are visible by all So both asset registries answer exactly one question. `GET /api/objects` and `GET /api/avatars` list **every** row, each with `usable` — public, or private and yours. Neither publishes `visibility` any more: the column stays (this is a wording problem, not a schema one) but the name means the opposite of what it says for these two tables, and a child-facing payload should not carry that. It could not have worked any other way, which is how the bug was found. A registry is also where a client learns where an asset's BYTES are, so filtering it by ownership made a private asset undrawable by everyone except its owner: a guest walked into room-a on the deployed museum and the easel and the painted horse were simply absent (the parametric objects beside them were fine — they need no bytes). Presence makes the same point about avatars: the museum draws other people, so an avatar a visitor cannot list is a peer a visitor cannot see. Because the listing is no longer the permission, the permission had to be somewhere real: - **objects** — `server/rooms.js` already refused a saved document naming an archetype its writer may not use (`object_not_yours`), computed from `listObjectsFor`. Unchanged. - **avatars** — nothing enforced it at all; the filtered picker WAS the rule. `POST /api/paintings` now refuses `avatar_not_yours`, since painting an avatar is the write that wearing one amounts to. An avatar the registry has never heard of passes: that is a build/database mismatch, not a verdict. ## How a room is read (owner 2026-08-07) The write path has worked since 2026-08-06: the editor's **Save to museum** PUTs a room document and Postgres stores it. Nothing read it back. A room was built from *your own* localStorage draft, then from `content/inception/rooms/.room.json` — which Vite inlines into the JS bundle at build time — then from the code default. So an owner saw their own edits (their draft) and everybody else saw whatever was committed to the repository the last time the site was built. Two saved rooms sat in production that no visitor could see. The agreed model, in one line each: - **Postgres is the source of truth** for a room document. Not the file, and not memory: this service sleeps and restarts on the free tier, and a copy that only lives in a process is a copy that disappears. - **The repository file is a SEED.** On boot, every committed room that has no row yet is inserted — idempotent, never overwriting. A fresh database has to produce a museum, the prerendered discovery pages and the tests read those files, and a new world begins in code. After that the row is authoritative and the file is history. - **The server keeps the documents in memory** and answers reads from there: write-through when a save lands, read-through on a miss, and a short TTL so a second instance could not stay wrong for long. This is an optimisation, not the design — at two kilobytes a row the database read is a millisecond. If the museum ever needs more than one instance, that is what Redis is for (owner: "if run more than 1, will be redis — that means the project is doing great"). - **The client reads a room from the API when it enters it.** Rooms already stream one at a time; a document is one to three kilobytes and always fresh. - **The bundled file stays as an offline fallback.** The museum opens with no API at all today and must keep doing so. Read order: your own draft (only for you, while you are editing) → the API → the bundled seed → the code default. - **A successful save clears your local draft.** Otherwise you keep seeing your own copy whatever the server holds, which is precisely what hid this. - **A room's floor is part of the room** (owner 2026-08-07: "in general floor should be in room setting as well"). The document carries the floor the room starts on; the centre orb still switches it live for everyone present through the vote, and that live state stays ephemeral — The Sky's console is a special affordance, not a thing to store. Deliberately NOT changed: private rooms stay in the bundle. The owner's call — "private just means private view, not private data" — so the simple thing wins and every committed document keeps shipping in the JS. The API's own read still applies `authorizeRead`, so the live copy of a private room needs its owner or its pass. ## Declared now, created later (the audit of "what else needs storing") Walking every plan document turned up these — declared here so the model already has their seams, created only when their phase arrives: - **`artworks`** (ACCEPTED; execution plan §5, ADR-0005 phase 4+): student `.lpp` sources and compiled `.lpr` packages. Metadata row here — owner email, binding (world_id, room id, face), photo sha256, provenance fields — with the BYTES in R2 (`lpp_ref`, `lpr_ref`), never in Postgres. Permissions are the SAME simple rule as rooms: `visibility IN ('public','private')` — the owner rejected the three-layer status machinery from execution plan §2.3 as over-engineering ("能设置 private or public 就可以了"). Private ≈ the old personal_draft; public ≈ the old public_snapshot; the class-shared middle layer is deferred with classes themselves. - **`objects`** (ACCEPTED and BUILT 2026-08-07, migration 3): the object LIBRARY's ownership, the exact shape `avatars` has — `objects (id PK, owners TEXT[], visibility CHECK ('public','private'))`. The owner's rule: *"objects belong to the room and its owner — not everyone can use the objects."* Two questions kept apart: - **Placement** was already owned, and still is, through the room document (`props[]` inside `rooms.doc`, guarded by that room's `owners`). Nobody could ever move your furniture. - **The library** was not: every room was offered all ten archetypes. Now a private object appears in the editor of the accounts that own it and nowhere else, and `authorizeWrite` refuses a document naming an archetype its writer may not build with (`object_not_yours`). Seeing is never gated — a public room shows what it contains, whoever made it. Today the museum's eight parametric pieces are public and the two student houses (owner-authored art) are private to `WORLD_OWNERS`; the policy lives in `src/inception/world/props.js`, the emails do not. The table carries `source` ('code' | 'r2') and `ref` from the start, because the owner named the future the same day: **most objects will become GLB files in R2**. That day adds rows, not a migration — and the ownership question is already answered by the same two columns. Bytes still never enter Postgres (ADR-0005), and there is still **no upload endpoint**: an object arrives through the repository's provenance + budget review, exactly like an avatar. - **`classes` + `memberships`**: explicitly DEFERRED (owner: not needed for now). When a pilot class arrives, this is also where the public pseudonym for attribution lives (R6: real names never reach a published surface). - **An account maps to the avatars it owns** (owner 2026-08-05). That is the `avatars` registry below, read as: `users 1..n avatars` (ownership) and `users 1..n avatar_paintings`, with each painting naming the ONE avatar it was painted for. A private avatar appears in the catalog only for its owners; nothing here is per-world, because a painted body follows its person into every world. Since 2026-08-05 the client side already behaves this way — painting requires an account, an identity (account hash or a persisted guest id) makes one person one explorer however many tabs are open, and presence carries the avatar slug so paintings are never worn by the wrong body. Persisting it is the step still to come. - **`avatars`** (ACCEPTED, with a content-safety constraint): there is deliberately **NO web upload** — avatar assets keep arriving through the repository's provenance + budget review (PROVENANCE.md, inspect-glb), because uploads would make the museum an unmoderated content channel. The table is an **ownership registry** over repo-shipped assets: `avatars (id PK, owners TEXT[], visibility CHECK ('public','private'))` — a private avatar appears in the catalog only for its owners ("自己用 自己 own 的 avatars"), a public one for everyone. Blob refs (R2) join the row only if assets ever leave the repository. - **AI planner/critic approvals** (KEEP AS A NOTE — owner: interesting, likely an important step later for AUTOMATED content review, but it needs an LLM API, so not now): review requests and approval decisions as an audit table; the dialogue itself is never published (R6) and probably never stored beyond the decision record. When automated moderation arrives, it plugs into the same simple visibility switch: nothing goes `public` without a decision row. - Deliberately **NOT stored, ever**: presence (ephemeral), room passes (stateless HMAC), OTP/accounts (Lucas Account's D1), the painting relay cache (memory; `avatar_paintings` is its durable big brother), analytics of children (nothing in any plan wants it — keep it that way). ## Renaming the database — DONE 2026-08-04 The Render blueprint matches resources BY NAME, so editing `render.yaml` first would have CREATED a second database (and free workspaces allow only one free Postgres) while orphaning the old one. Executed in the safe order: 1. ✅ Owner renamed the instance in the dashboard: `museum-db` → **`inception-db`** (rename keeps the instance, data and connection strings; the rooms table was empty — the zero-cost window). 2. ✅ `render.yaml`'s `databases[0].name` and the API's `fromDatabase.name` now say `inception-db`; on the next blueprint sync, confirm it links to the EXISTING instance instead of proposing a new one. 3. The internal `databaseName: museum` / `user: museum` inside the connection string are cosmetic; change them only if we ever migrate instances (e.g. at the free-tier expiry upgrade). ## Consequences - `server/store.js` grows `worlds` and the room key becomes `(world_id, id)`; the API's `/api/rooms/:id` becomes `/api/worlds/:world/rooms/:id` with a compatibility read for the museum world while the client catches up. Rules in `server/rooms.js` are pure and keep working; they gain a world parameter. - Schema management graduates from "self-applied CREATE IF NOT EXISTS" to ordered migration files the moment the first ALTER lands — self-applying is only safe while every statement is idempotent creation. - `avatar_paintings` activates together with login-gated saving (docs/archive/AVATAR-STUDIO.md), not before. **Done 2026-08-06:** `POST /api/paintings` verifies the account token (dev sessions refused), touches the `users` row that the painting's foreign key needs, and stores the record; `GET /api/paintings/:id` reads the relay cache first and the database second, re-serializing the row by the studio's canonical rule so the served bytes still hash to the requested id. A database failure degrades to "cached only" (`persisted:false`) rather than failing a child's Save.