ADR-010: lumibase_ prefix for all system tables

Date: 2026-07-01 Status: Accepted

Context

LumiBase stores no-code content as JSONB rows in a generic items table, but the Phase-2 materialization feature (apps/cms/src/services/materialize-service.ts) dynamically creates physical Postgres tables named after user collections (mat_<id>). System tables (users, sites, items, agent_runs, …) lived in the same public schema with no marker distinguishing platform-owned tables from anything a tenant might cause to be created. That ambiguity is a latent collision and security-reasoning hazard: there was no single rule to answer "is this table ours or the user's?".

Only two tables already carried a prefix (lumibase_firebase_sync_*), set as an ad-hoc precedent with no documented convention.

Decision

Prefix every system table with lumibase_, applied uniformly — including tables that already had semantic sub-prefixes (agent_*, ai_*, cdc_* → lumibase_agent_*, lumibase_ai_*, lumibase_cdc_*). This yields one invariant:

A table whose name starts with lumibase_ is platform-owned. Any other table is user-created (or a mat_* materialization).

Name prefix, not a Postgres schema namespace. We keep all tables in public and prefix their names rather than moving them into a dedicated lumibase schema via pgSchema(). Rationale:

• No search_path dependence. LumiBase runs on Cloudflare Workers through Hyperdrive (pooled connections). A schema namespace would require a reliable search_path on every pooled connection or fully-qualified names everywhere; a name prefix has neither risk.
• Simpler RLS. The hand-written rls-policies.sql addresses tables by bare %I-formatted names; only the name list changes.
• Cheap to reset. Because no instance had shipped, the whole migration history could be squashed to a single init that creates the prefixed tables directly (see Consequences), rather than carrying a rename step.

Explicit index-name literals are left unprefixed (e.g. items_data_gin_idx). They are not tables, so they are not in the collision-prone namespace; keeping the existing literals avoids churn. Auto-derived FK constraint names, however, do carry the prefix since drizzle-kit derives them from the (prefixed) table names.

Consequences

• Greenfield migration reset. The 39 legacy migrations plus the interim rename migration were removed and the history was squashed into a single packages/database/drizzle/0000_lumibase_init.sql, regenerated by drizzle-kit generate from the (now prefixed) schema. Tables are created with lumibase_ names from the start — no separate rename step. This is a fresh-install-only change: there is no upgrade path from a pre-prefix database; an existing DB must be dropped and recreated. (Chosen because no instance had been deployed yet.) The migrate runner guards this: it compares the hashes in drizzle.__drizzle_migrations against the local journal and refuses to apply onto a database carrying the pre-squash history (FORCE_MIGRATE=true bypasses).
• Reserved names enforced at the API. Collection names starting with lumibase_ or mat_ are rejected (Zod refine in routes/collections.ts + RESERVED_NAME error in SchemaService.ensureName), keeping the namespace invariant intact.
• Schema is now the complete source of truth. Three DDL artifacts that were previously hand-written in migrations only — the shares CHECK constraints (max_uses, used_count) and the agent_approvals_veto_due_idx partial index — were added to the Drizzle schema (check() / partial index().where()), so generate reproduces the full schema (GIN partial index, all partial indexes, all CHECK constraints) with no loss and no drift.
• Snapshots regenerated clean. All stale meta/*_snapshot.json were removed and a single fresh 0000_snapshot.json now matches the schema exactly — drizzle-kit generate reports "No schema changes". This resolves the prior drift (snapshots had lagged at 0031 while the journal was at 0038).
• Drizzle ORM code is unaffected. Table const exports keep their names (export const users = pgTable('lumibase_users', …)), so all references() FKs and query code propagate automatically. Auto-derived FK constraint names now reflect the prefixed tables (e.g. lumibase_collections_site_id_lumibase_sites_id_fk); explicit index-name literals (e.g. items_data_gin_idx) are kept as-is. Raw-SQL touchpoints (RLS, seed scripts, materialize-service triggers, login-guard settings read, integration-test TRUNCATEs) and DB-mock unit tests that switch on getTableName() were updated by hand.
• RLS re-apply required. After the init migration, run rls-policies.sql (it targets the lumibase_* names). While updating it, a pre-existing nested $ dollar-quote bug in its DO block was fixed (inner tag changed to $pol$) so the script applies via psql at all.