Conventions

Naming. A Postgres schema is named in the singular and in lowercase, such as content or persona. A table is named in the plural, such as content.markdown_versions. In the code, each file declares its schema with pgSchema('<name>') and exports each table under a name made from the table and the schema, in the shape <tableCamel>In<Schema>.

Identifiers. A row's id is a varchar(36) holding a UUID string. The persona table and the chat definition tables use a native uuid instead. The address a person sees for an object is kept apart from its id, in rdid.identifiers.

Enumerations. A column that takes one of a fixed set of values is a varchar with a CHECK constraint, not a Postgres enum, so a new value can be added in a migration without rebuilding the type.

Ownership. Every data-plane table has an ecosystem_id, which says which tenant the row belongs to. A customer_id says who wrote it. An object that can be owned by different kinds of thing, such as a persona, a project, a bucket, a site group or a Shipr group, carries the pair owner_kind and owner_id. In the game schema, an empty owner_id means no owner.

Foreign keys. Within one schema, a reference from one table to another is declared so the database enforces it. Across schemas it is not, and the application keeps the two sides consistent. The one exception is access.team_permissions, which references team.teams.

Tombstones. A row that is deleted is marked with deleted_at rather than removed. An organization or an ecosystem that is archived is marked with archived_at instead.

Sync stamps. A table whose rows can be synced to a device carries sync_version, sync_stamped_at and sync_txid, added by syncStampColumns() and written by the sync.stamp_row() trigger.

Row-level security. Every data-plane table carries a policy named ecosystem_isolation, and the policy is forced, which means it applies even to the table's owner. A row is visible when its ecosystem_id matches the session setting app.current_ecosystem_id, or when the session setting app.is_admin is 'true'. The application connects as the role app, which cannot bypass a forced policy, so the only way to see a row is to set those session settings first. That is what the tenancy helpers do: withTenant, withTenantOn, withTenantSnapshot, withPublicScope, readAdminBypass and writeInEcosystemScope.

Full-text search. The tables that are searched by words carry a GIN index: content.markdown, document.blocks, document.documents and discussion.topics.

Vectors. A row that is searched by meaning carries an embedding, a list of numbers a model makes from its text. persona_memory.memories.embedding is a vector(1536) with an HNSW index, and content.markdown_chunks.embedding is a vector(1536). The model that makes them is gemini-embedding-001, at 1536 dimensions.