use-database

Informational, not binding. Know a better way for the case in front of you — do it your way and say so. You are trusted with the creative work on this project.

The door itself, the credential and the permission classes are in use-data. This is about tables.

1. 🔒 A table is declared in SCHEMA, not created by a query

lib/db/index.ts holds one constant, SCHEMA. Both paths execute it — makeLocalDb() on a laptop and initRemoteSchema() against the data service — so a table added there exists on both machines after the next start. There are no migration files and no button.

Add to SCHEMA → deploy → the table is there. Creating it with a query instead gives you a table that exists on the server and not in the repository: the next clone, the next developer and the next deployment know nothing about it.

🔒 A COLUMN IS NOT A TABLE, AND SCHEMA WILL NOT DELIVER IT (2026-08-24). CREATE TABLE IF NOT EXISTS does nothing at all where the table already exists — which is every machine except a brand-new one. ✗ paid live on 2026-08-17: kind was declared in SCHEMA, deployed, and the column never appeared; the data layer then answered no such column to every catalogue query, taking the storefront, the sitemap and the products page down together.

A column added AFTER its table goes into LATE_COLUMNS in the same file, and nowhere else. It is a plain ALTER TABLE … ADD COLUMN, applied blind, swallowing exactly one error — "column already exists", the normal outcome on every server past the first run.

🔒 One ladder, two walkers. LATE_COLUMNS is executed by BOTH makeLocalDb() and initRemoteSchema(). ✗ it once lived inside the local path only: while the app wrote to a file nobody noticed, and the day it moved to the data layer the ladder stopped running entirely. Pairs like that diverge silently — each branch is healthy on its own.

2. 🔒 Which database you are actually talking to

REMOTE_DATA_URL set AND a key present → the data service (the real, shared data)
otherwise                             → a local SQLite file with sample rows

Both branches are healthy; only the destination differs — which is why the wrong one is quiet. A missing key does not shout, it silently gives you your own empty copy. If rows appear on your machine and never on the site, this is the first thing to check.

🔒 With the key, a local npm run dev writes to the CUSTOMER's data. Deleting rows in an experiment deletes them for real. There is no staging copy in between.

3. Two doors to the same rows, and their asymmetry

🔒 The second door bypasses the first one's protection, and that is why it is behind the machine secret only — a session cookie is refused there whatever the role (use-data §2). A hash that leaves the database lives on in logs, in responses, in screenshots and in an agent's context, where nobody can take it back. Use /db/tables unless you genuinely cannot.

When /db/migrate is the right tool: a join the row API cannot express, a bulk update, a one-off data repair, reading pragma_table_info to see what a table really holds. Say in the step that you used it — raw SQL against a customer's data is a decision, not a detail.

4. Reading rows in a page

A subject has ONE reader in lib/<subject>/, shared by the page, the sitemap and the machine twin — check:static refuses a direct @/lib/db import from a public route folder. A query living in a page entry is repeated by the next entry and reused by nothing.

Rows that a page shows must be fetched on the server. A public page stays static: the shell is prerendered and the dynamic part wakes up inside it (use-dynamic-pages).

🔒 Paging is a property of the DOOR you called, not of "the data layer". The layer's own table door takes limit (default 500, hard ceiling 1000) and offset, plus search across the text columns: /db/tables/<table>?limit=100&offset=200&search=…. The services it PROXIES speak their own dialect — the knowledge graph, for instance, pages with page_size and refuses a value below its schema minimum. ✗ assuming one vocabulary for all doors returns a 400 that reads like a broken query. Ask GET /capabilities when you are not sure; it answers for the door you are about to call.

🔒 A table door never returns a column just because it exists. /db/tables/users?limit=1 once handed back the whole row including the bcrypt password hash. The door now selects columns deliberately — when you add a table holding anything secret, that decision is yours to make explicitly, not something the door guesses.

5. What belongs in a column, and what does not

• Translations of a row live in its i18n column as JSON, not in a column per language: a language per column means a schema migration for every new market.
• Derived values that are expensive per row (image dimensions, a blur placeholder) are stored at write time. Twenty products on a page means twenty extra round-trips otherwise — the catalogue was built that way for exactly this reason.
• Nothing secret that you invented. If a new secret column feels necessary, it belongs to the auth layer, not to the application's tables — and the secret-column filter only knows names it has been told about (SECRET_COLUMNS in the data service).

6. Before you call it done

1. The table is in SCHEMA, in this repository — not created by a query on the server.
2. npm run check:types, then the page that reads it, opened for real.
3. You know which database answered: with a key it is the customer's data; without one, your own file with samples. Say which one you tested against.
4. If you used /db/migrate, the step says so, and says why the row API could not do it.