Inception Space holds many worlds, a world holds many rooms, and a user is global. · Rendered from docs/decisions/0007-one-database-worlds-rooms-users.md in the project repository · view as Markdown

ADR-0007: One database for Inception Space — worlds, rooms, global users

The shape of the product, as the schema sees it

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

-- 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:

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:

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/<id>.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:

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:

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