Project databases

Creora can give a project a PostgreSQL schema of its own: tables the agent designs, rows the app reads and writes, accounts for its users. This page covers turning that on, the isolation model, and the data API.

Turning it on

Off unless both DATABASE_URL_OWNER and DATABASE_URL_APP are set; one without the other is refused. Without them the agent is not sent the eight data tools at all, and everything under /api/data answers 503 data_disabled. See configuration.

Docker:

docker run --name creora-pg -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:17

Homebrew works too, with one trap: postgresql@17 on macOS needs a UTF-8 locale in the environment that starts it, or the postmaster dies at startup, saying only that it became multithreaded.

LC_ALL=en_US.UTF-8 brew services start postgresql@17

Then, once per cluster, npm run pg:bootstrap. It reads the four DATABASE_URL_* values and makes what they name: two login roles, two databases, and the hardening. It is idempotent, and with no such variables set it prints four lines with fresh passwords instead. It needs a superuser connection, by default your own account against postgres.

npm run pg:bootstrap -- --admin postgres://postgres:postgres@localhost:5432/postgres

--print prints the environment lines only; --reset-password sets a role's password from its URL. The suites use DATABASE_URL_OWNER_TEST and DATABASE_URL_APP_TEST.

Three roles, and failing closed

• creora_owner owns every project_<id> schema and runs provisioning and migrations.
• creora_app is NOINHERIT, holds no table privileges anywhere, and is a member of every project role. Every request that touches a row connects as it.
• proj_<id> cannot log in. It holds USAGE on one schema and the four row verbs on its tables, never CREATE.

One pool serves every project. Every operation goes through withProjectSchema in src/lib/data/pool.ts, which opens a transaction and issues SET LOCAL ROLE "proj_<id>" with an empty search_path. Since creora_app holds nothing of its own, a path that forgets to scope itself reads nothing and errors on the schema rather than reading the wrong tenant. tests/data/isolation.test.ts asserts this against a real cluster.

First use, and the migration log

A project gets its schema on first use: the first data tool the agent calls, or the first time the Data pane is opened, never at project creation.

The agent gets eight tools beside the file ones: describe_schema, create_table, alter_table, add_index, set_table_access, drop_table, insert_rows and enable_auth. Each one that changes something writes a numbered migration, into a private table the project's own role cannot write. A migration is a structured operation rather than SQL, and carries its own inverse, so a revert is itself a numbered migration. One that destroys data copies the rows first, up to 10000; above that it is refused. Per project: 25 tables, 50000 rows, 100 MB. See the agent.

The Data pane

The Data pane in the editor is the owner's surface, behind the session cookie and checked against the caller, at src/app/api/projects/[id]/data/*. Four tabs: Tables (the schema, and a grid to edit its rows), Changes (the migration history, with an undo on each entry), Accounts and Key.

The generated client module

An app reaches its data through creora.ts, which Creora generates when the bundle is assembled rather than the agent writing it (src/lib/projects/client-module.ts), typed per table. The key is never in the file tree, the download, the version history or a published snapshot. Rotating takes effect on the next load of every preview and published page, with nothing to republish.

The public data API

Eight endpoints: /api/data/query, insert, update, delete, and auth/register, auth/signin, auth/me, auth/signout-all. A request carries the publishable key in the x-creora-key header, a JSON body of at most 64 KB, and optionally an end user's token as a bearer. No route here reads or writes a cookie, so cross site request forgery is structurally impossible.

• Access levels. Each table has a rule per verb: anon is anybody using the app, user is somebody signed in to it, owner never reaches this surface. A new table is owner on all four. A table may also name an owner_column holding the id of the end user a row belongs to; a user then reads only rows matching it, and inserts fill it in from the token.
• Pages. Reads are keyset paginated; there is no offset. A cursor is signed per project and names its table, so the only tuples reaching a comparison are ones Creora minted. A limit above queryLimitMax (100) is refused; the default is 25.
• Limits. Per key: 600 reads a minute, 60 writes a minute, 20 registrations an hour, and 5 sign in attempts a minute per email address. Writes check the row quota first.
• Origins. A key's allowlist adds origins to the deployment's own, which already holds the sandbox hosts and Creora's origin. It stops a hostile page using a key it read, and nothing else: a request with no Origin is allowed through, and curl sends none.

End user accounts

A project's end users are rows in its own accounts table, not Creora users: a different store, a bearer token rather than a cookie, and a key derived per project with HKDF, so a token for one project verifies for nothing else. Tokens last 24 hours. Passwords are hashed with scrypt, at 60 to 100 ms of a worker thread each, which is why the sign in limit runs first. Revocation is token_version, which the owner bumps by disabling an account or setting a new password from the Accounts tab.

Creora sends no email, so there is no verification and no password reset by mail. email_verified is always false, the owner setting a password by hand is the reset, and registration reveals whether an address is already taken. The accounts table is a system table, absent from the map a table name is looked up in, so no name reaches it.

What a stolen key can and cannot do

The key ships inside every published app's JavaScript. It is not a secret: it is an identifier and a rate limit subject, and the access rules are the boundary. It can do what any user of the app can: read and insert into anon tables, register, sign in, and read and write that account's own rows in owner_column tables.

It cannot reach another project's data: the project comes from a server side lookup, and the transaction has assumed a role with USAGE on one schema. It cannot touch a table nobody widened, the accounts table, or another end user's rows. It cannot empty a table in one request, because public update and delete each take a primary key and never a filter. It cannot page past queryLimitMax, forge a cursor, or change a schema. A table set to anon insert, update or delete can be rewritten row by row, which the panel marks in a warning colour.

Deleting a project

Mongo and Postgres cannot be one transaction, so Mongo is the source of truth: the project is gone the moment the user asks, its row is marked dropping, and a project_db_drop job drops the schema and the role, retrying until the cluster answers. Nothing is left behind even when Postgres is down.