LumiBaseDocs

Data Model (Drizzle / Postgres)

This document describes the tables declared in packages/database/src/schema/. Internal database rows use text IDs by default. No-code content collections also carry a logical item primary key strategy through collections.primaryKeyField and collections.primaryKeyType.

Site-plane domain rows carry site_id; organization-plane rows carry organization_id.

Physical table names carry a lumibase_ prefix. Every LumiBase system table is physically named lumibase_<name> (e.g. the logical users table is physically lumibase_users), created by the 0000_lumibase_init migration. This reserves the lumibase_ namespace for the platform, so any table WITHOUT that prefix is unambiguously user-created (or a mat_* materialization). See ADR-010. The section headings below use the short logical name for readability; the summary table lists the physical names.

Schema files are split by domain:

FileTables (physical)
core.tslumibase_organizations, lumibase_organization_members, lumibase_sites, lumibase_users, lumibase_user_sites, lumibase_teams, lumibase_team_members, lumibase_notifications
access.tslumibase_roles, lumibase_policies, lumibase_role_policies, lumibase_user_policies, lumibase_permissions, lumibase_refresh_tokens
cms.tslumibase_pages, lumibase_collections, lumibase_fields, lumibase_relations, lumibase_items, lumibase_revisions, lumibase_releases, lumibase_release_items, lumibase_activity, lumibase_flows, lumibase_flow_runs, lumibase_operations, lumibase_materialized_collections
platform.tslumibase_folders, lumibase_files, lumibase_presets, lumibase_translations, lumibase_settings, lumibase_webhooks, lumibase_extensions, lumibase_translation_memory, lumibase_glossary, lumibase_push_subscriptions
ai.tslumibase_ai_approvals, lumibase_agent_*
firebase-sync.tslumibase_firebase_sync_pipelines, lumibase_firebase_sync_log
external-auth.tslumibase_auth_external_issuers
social-auth.tslumibase_user_identities, lumibase_social_auth_providers
imports.tslumibase_import_jobs
activation.tslumibase_activation_milestones, lumibase_telemetry_state

Migrations live in packages/database/migrations/ and packages/database/drizzle/.


1. Core tenancy & identity (core.ts)

organizations

  • id text PK (nanoid), unique slug, name, settings jsonb default {}, createdAt, updatedAt.

organization_members

  • userId, organizationId, role, createdAt; composite PK (userId, organizationId). Role CHECK: owner, admin, billing, member. Roles use the fixed canOrg matrix, not site policies. Organization tables have RLS using lumibase.organization_id.

sites

  • organizationId is a required FK to organizations with delete RESTRICT. Migration 0024_organizations backfills existing sites into the default organization before enforcing NOT NULL. Setup creates the personal organization and its bootstrap owner before creating the default site.
  • id, name, domain, createdAt, plus identity/branding/theme columns and defaultLanguage, defaultAppearance, and defaultSaveAction (stay|return|create_new, default stay — the site-wide default Studio save action; per-user override lives in users.preferences.saveAction).
  • emailIdentityStrict boolean, default false (migration 0027) — per-site email identity tier: when on, one membership per root mailbox (see user_sites.identityKey).

users

ColumnTypeNote
idtext PKnanoid
logtoIdtext uniqueOIDC subject
email, firstName, lastName, avatartext
statustextactive/invited/suspended
language, theme, tfajsonbpreferences
preferencesjsonbper-user UI prefs: { language, theme, timezone, defaultPresets, saveAction }. saveAction (stay/return/create_new) overrides sites.default_save_action.
lastSeenAttimestamp
passwordChangedAttimestampSet on every reset/change (migration 0006). A password-reset token whose iat predates it is rejected → single-use reset links.
emailCanonicaltext uniqueAccount key under the instance email identity mode: lower(email) (exact) or the root mailbox (root). Written by a trigger (migration 0027), never by the app. Nullable for one release; lookups fall back to lower(email) for null rows.
emailRoottextRoot mailbox: +tag stripped on every domain; Gmail dots removed and googlemail.com → gmail.com. Derived by lumibase_email_root() in the same trigger. Plain index.
createdAt, updatedAttimestamp

Unique indexes: users_external_id_unique, users_is_bootstrap_unique (partial, is_bootstrap = true), and users_email_lower_unique on lower(email) (migration 0006) — email is identity-global, one account per address. ⚠️ Migrating an existing instance fails if it already holds case-insensitive duplicate emails; de-duplicate first. Migration 0027 adds users_email_canonical_unique on email_canonical and users_email_root_idx on email_root; under the root mode the canonical index makes "one root mailbox = one account" a database guarantee.

user_sites (membership N-N)

  • userId, siteId, roleId, createdAt. PK composite.
  • identityKey text (migration 0027) — users.email_root when the site is strict, otherwise null. Unique index user_sites_site_identity_unique on (site_id, identity_key) allows one membership per root mailbox on a strict site (nulls never collide). Set by a trigger on insert and re-keyed when the member's email changes.

Email identity policy (migration 0027). The instance tier lives in system_state.email_identity_mode (exact | root, default exact; existing instances are backfilled to exact, so nothing changes on upgrade). Every writer — routes, SCIM, setup, seeds, an older app version — gets the same derived columns because triggers compute them: lumibase_users_email_identity() on users (reads the singleton FOR SHARE), lumibase_user_sites_identity_key() on user_sites (reads the site FOR SHARE) and lumibase_users_rekey_memberships() after an email change. The guarded change endpoint holds FOR UPDATE on the same rows, so a row is never keyed under a stale policy. See User management and /api/v1/admin/email-identity in the API spec.

teams / team_members

  • teams: id, siteId, name, description.
  • team_members: teamId, userId. PK composite.

notifications

  • Persistent inbox for mentions, denial reasons, and system events. Realtime delivery is handled above this persistence layer.

2. Schema (no-code) — cms.ts

pages

  • Page-builder pages consumed by /deliver.

collections

ColumnTypeNote
idtext PK
siteIdtext FK
nametextmachine name, unique per site
label, pluralLabeltextauthor-facing labels
hidden, systemboolean
singletonboolean
icon, color, notetext
primaryKeyFieldtextlogical item identifier field, defaults to id
primaryKeyTypetextnanoid, uuid, integer, bigInteger, string
storageModetextjsonb, materialized, physical, external
displayTemplatetextmustache template default
sortField, archiveField, archiveValue, unarchiveValuetext
itemDuplicationFields, translationsjsonb
accountabilitytextall / activity / none
versioningboolean
metajsonbextra UI hints, including safe system-field presentation overrides
createdAt, updatedAt

Primary key strategy:

  • nanoid: default logical string identifier generated by LumiBase.
  • uuid: service-generated UUID string.
  • string: caller-provided string identifier.
  • integer / bigInteger: sequence-backed strategies reserved for materialized/physical storage support; JSONB collections block these combinations.

Storage modes are intentionally explicit:

  • jsonb: default logical collection backed by items.data; fastest schema evolution, no runtime DDL, advisory SQL-native indexes/unique constraints.
  • materialized: JSONB source of truth plus managed physical read projection for hot paths.
  • physical: future Directus-like owned table mode; schema diffs mark this as a storage runtime change.
  • external: future introspected table mode; destructive DDL/relation actions are limited because LumiBase does not own the table.

fields

ColumnTypeNote
idtext PK
siteId, collectionIdtext FK
nametextmachine
typetextstring,text,integer,decimal,boolean,json,uuid,date,datetime,time,csv,hash,geometry,alias
interfacetextUI editor key
displaytextdisplay formatter key
options, displayOptions, validation, conditions, translationsjsonb
required, readonly, hidden, encrypted, versioned, rawEnabledboolean
classificationtextData sensitivity (Req 5): none/internal/pii/phi. pii/phi must be encrypted=true; drives default masking, read_decrypted gating, and field_access_log audit.
sortOrderinteger
width, grouptext

Compiled schemas expose generated system fields in addition to user-defined rows:

FieldTypeNotes
idstringPrimary item identifier, readonly/generated.
statusstringWorkflow status; visible when status/archive behavior is enabled.
sortintegerManual ordering value.
user_createdstringCreator user ID, readonly/generated.
user_updatedstringLast updater user ID, readonly/generated.
created_atdatetimeCreation timestamp, readonly/generated.
updated_atdatetimeLast update timestamp, readonly/generated.
deleted_atdatetimeSoft-delete timestamp, readonly/generated and hidden.

relations

  • id, siteId, manyCollection, manyField, oneCollection, oneField, junctionCollection?, type, aliasField?, relatedDisplayTemplate?, junctionManyField?, junctionOneField?, sortField?, onDelete, meta jsonb.
  • Relation types are m2o, o2m, m2m, with m2a reserved.
  • The schema service validates referenced collections and fields, and blocks collection deletes when relations still reference either side.

items

ColumnType
idtext PK
siteId, collectionIdtext FK
statustext
datajsonb
sortinteger
userCreated, userUpdatedtext FK users
publishAt, unpublishAttimestamp nullable
editorialStatetext nullable
dekWrappedtext nullable
createdAt, updatedAttimestamp
deletedAttimestamp nullable

Indexes: (siteId, collectionId, status), GIN on data, (siteId, status, publishAt), (siteId, status, unpublishAt).

revisions

  • id, siteId, itemId, collectionId, delta jsonb, parentId, userId, createdAt.

releases (Content Releases)

  • A cross-collection publish bundle. id, siteId, name, description, status (draft|scheduled|published|failed|partially_failed), atomicityMode (all_or_nothing|best_effort), publishAt, publishedAt, maintenanceWindow jsonb, statusReason, createdBy → users.id (set null), createdAt, updatedAt.
  • Indexes: releases_site_status_idx (siteId, status), releases_publish_due_idx (siteId, status, publishAt) (mirrors items.publishDueIdx for the scheduler sweep).

release_items (Content Releases)

  • Junction release ↔ item, optionally pinned to one revision. id, siteId, releaseId → releases.id (cascade), collection, itemId → items.id (cascade), targetStatus (default published), revisionId → revisions.id (set null), outcome (published|skipped|failed), outcomeReason, createdAt.
  • Unique (releaseId, collection, itemId) (upsert key); index (siteId, releaseId).

activity

  • id, siteId, action, userId, collection, itemId, ip, userAgent, comment, payload jsonb, createdAt.

flows (POST-GA3)

ColumnTypeNote
id, siteIdPK + FK
name, descriptiontext
statustextactive / inactive / draft
triggerTypetextwebhook / event / schedule / manual
triggerOptions, graphjsonbgraph: { entry?, nodes: [{ id, key, options, next?, onError? }] }
nextRunAttimestampused by the schedule trigger
accountabilitytext

flow_runs

  • Each run stores status, input, steps (per-node output), output, error, startedAt, finishedAt.

operations

  • Declares each operation node in the flow (key + type + options + position).
  • Type: condition / transform / http / mail / log / sleep / run-extension / item.create|update|delete / notify.

materialized_collections (POST-GA6)

  • Defines denormalized read tables for hot paths.
  • target, refreshStrategy (auto/cron/manual), refreshCron, projection jsonb, filter jsonb, lastRefreshedAt, rowCount, status, error.

import_jobs (imports.ts, migration 0028)

  • One background import (#726): collection, format (csv/json), mode (create/upsert), status (queued/running/succeeded/failed/cancelled/timed_out), principal jsonb (re-resolved at every step), created_by.
  • Progress: total_rows, cursor (next row), created_rows, updated_rows, failed_rows, errors jsonb ({ row, id?, message }[], capped) and errors_truncated.
  • Worker coordination: lease_owner, lease_until, timeout_ms, cancel_requested_at, started_at, finished_at. source holds the uploaded file until the job ends, then is cleared. Site-isolated under RLS.

activation_milestones and telemetry_state (activation.ts, migration 0029)

  • activation_milestones: one row per site_id and milestone (site_created/item_published/delivery_request/agent_run), with reached_at and source (event/derived). Written once, never updated. Site-isolated under RLS.
  • telemetry_state: instance-level singleton (id = 'singleton'), not site-scoped. Holds the random install_id and sent jsonb (event name → day delivered). The row is created only after an operator opts in to usage telemetry.

Schema diff/apply runtime contract

The schema service exposes a Directus-parity lifecycle:

  • POST /api/v1/collections/diff compares proposed collection metadata, fields, and relations with the current schema.
  • PUT /api/v1/collections/{name}/schema validates and applies metadata, field, and relation changes transactionally when the database runtime supports transactions.
  • Apply invalidates compiled schema, permission, and typegen cache keys and emits a schema.changed event.

Diff output includes root risk, runtimeImpact, and per collection/field/relation entries. Runtime impact values include cache_invalidation, permission_recompile, typegen_rebuild, data_migration_required, relation_reindex, and storage_runtime_change.

Typegen manifest v2

GET /api/v1/typegen/schema returns a versioned manifest with:

  • collection primaryKey, primaryKeyField, and primaryKeyType;
  • user fields plus compiled system fields;
  • required, nullable, readonly, generated, system, encrypted, and primaryKey flags;
  • relation descriptors for expanded response types.

SDK generation uses this manifest to emit base collection interfaces and CollectionExpanded relation response types.

3. Permissions (access.ts)

roles

  • id, siteId, name, description, icon, adminAccess boolean, appAccess boolean.
  • Note: adminAccess/appAccess are legacy compatibility flags. New RBAC work migrates these flags to policies so roles remain grouping units. See Role Flag to Policy Flag Migration.

policies

  • id, siteId, name, description, rules jsonb. A standalone policy can be attached to many roles/users.
  • Explicit flags: adminAccess, appAccess, enforceTfa, ipAllow, ipDeny, validFrom, validUntil. These are the new source of truth for admin/app/TFA/IP/time guards.

role_policies / user_policies

  • Many-to-many with priority. user_policies allows attaching a policy straight to a user (overriding the role).

permissions

  • id, siteId, policyId, collection, action (create/read/update/delete/share), permissions jsonb (row-level rule DSL), validation jsonb, presets jsonb, fields text[] (field-level allow list, * = all).

refresh_tokens (migration 0005, physical lumibase_refresh_tokens)

  • id, siteId, userId, audience (studio/frontend), tokenHash (sha256, unique — plaintext never stored), familyId (rotation chain), replacedBy, expiresAt, revokedAt, lastIp, lastUserAgent, createdAt.
  • Rotating, revocable session-renewal tokens. Reuse of a revoked row revokes the whole familyId (theft detection). Swept on the hourly cron once expired. See security/user-management.md §4d.

user_passkeys (migration 0031, physical lumibase_user_passkeys)

  • id (nanoid), userId, credentialId (base64url, unique), publicKey (COSE public key), counter, transports, aaguid, name, deviceType, backedUp, rpId, createdAt, lastUsedAt, updatedAt.
  • Identity-scoped like lumibase_user_totp_credentials: keyed by user_id, no site_id, not under RLS — a passkey belongs to an RP ID (domain), not to a site. Nothing secret is stored. See security/passkeys.md.

System collections seed permissions

When seeding local/staging/prod, seed system collection permissions explicitly. Sensitive collections such as system_state, audit_log, login_attempts, login_baselines, admin_backup_codes, scim_tokens, and future API key tables are admin/security-only. See System Collections & Sensitive Access.

api_keys

  • scope is site (default) or organization. CHECK requires exactly one matching target: site scope has siteId and no organizationId; organization scope has organizationId and no siteId. Both targets are foreign keys with delete CASCADE.
  • prefix, tokenHash, name/expiry/revocation/rotation metadata; plaintext is not stored in this table. Site keys retain their role/policy links. Organization keys have fixed admin authority on their organization, and inherit administrator on its sites.

4. Files & Assets (platform.ts)

files

  • id, siteId, storage (r2/s3/external), filenameDisk, filenameDownload, mime, filesize, width, height, duration, folder, metadata jsonb, uploadedBy, createdAt.

folders

  • id, siteId, name, parent.

5. UX state (platform.ts)

presets (bookmark + view state)

  • id, siteId, bookmark text nullable, collection, userId?, roleId?, layout (tabular/cards/kanban/calendar/map), layoutQuery jsonb, layoutOptions jsonb, search, filter jsonb, icon, color, refreshInterval.

translations (UI strings + content)

  • id, siteId, language, namespace (ui/field/content), key, value. Unique (siteId, language, namespace, key).

6. Settings & Config (platform.ts)

settings (key/value per site)

  • id, siteId, key, value jsonb, scope (site/module), updatedAt.

webhooks

  • id, siteId, name, url, actions text[], collections text[], headers jsonb, status, secret, createdAt.

7. Extensions + Marketplace (platform.ts)

extensions

ColumnTypeNote
idtext PK
siteIdnullable text FKnull = global
name, versiontext
typetexthook/endpoint/module/interface/display/layout/panel/operation
enabledboolean
bundleUrltextR2/S3 path
manifestjsonb
capabilitiesjsonbgranted subset of manifest
installedBy, installedAt
Marketplace fields (POST-GA5): signature, signatureAlg (ed25519/rsa-pss-sha256), publisherKeyId, publisher, marketplaceSlug, publishedAt, bundleSha256

Indexes: (siteId, name), (publisher, publishedAt), marketplaceSlug.

8. Translation Memory (platform.ts, POST-GA1)

translation_memory

  • (sourceLang, targetLang, sourceText, targetText) + quality (0-100), source (human/mt/imported), provider, hits, context.

glossary

  • Term-level constraints: rule (do-not-translate/prefer/forbidden), term, translation, note.

9. Realtime / Notifications (core.ts)

notifications

  • id, siteId, recipient (userId), sender?, subject, message, collection?, item?, status, createdAt.

Realtime cursor data (CRDT-lite) is not persisted in Postgres — it is only broadcast through the Durable Object/host process. See apps/cms/src/services/cursor-protocol.ts.

10. AI Copilot and Agent Harness (ai.ts)

The existing Copilot tables provide chat/HITL history. The Agent Harness extends that foundation with first-class lifecycle, tools, approvals, artifacts, evaluations, and memory. See Agent Harness Layer for the user-facing and runtime contract.

ai_approvals

ColumnTypeNote
idtext PKexisting IDs preserved; UUIDv7 for organization approvals
siteIdtext nullable FK → sites CASCADEsite-scoped approval parent
organizationIdtext nullable FK → organizations CASCADEorganization-scoped approval parent
organizationRequesterjsonb nullablerequesting user or API-key identity
resultjsonb nullablecommitted human receipt, possibly a new key
agentNametextdefault 'lumibase-copilot'
skillNametextfrom the CORE_SKILLS registry
argumentsjsonbthe skill's arguments
statustextpending / approved / rejected
contexttext nullablethe user's original message
createdAt, decidedAttimestamp
decidedBytext FK → users SET NULL

Index: (siteId, status), (organizationId, status).

Behavior: dangerous skills (schema:write or names starting with delete) must create an ai_approvals row and wait for approval instead of executing directly. See AI Copilot. New dangerous harness actions also write linked/generalized agent_approvals records where applicable.

Migration 0025_organization_approvals makes siteId nullable and adds organizationId, organizationRequester, and result. The CHECK allows one site parent or one organization parent; organization approvals also require requester provenance. Both parents cascade on deletion. Paired restrictive/permissive RLS policies isolate receipts by the active site or organization scope.

Organization MCP writes always wait for human approval. Requester reauthorization, the domain write, and its stored receipt share a transaction. Only authorized humans can retrieve a receipt containing a newly minted key; list responses and agent results omit that secret. Organization-only requests do not borrow the default site. IDs for these organization approvals are UUIDv7; existing approval IDs remain valid.

agent_* harness tables

CollectionPurpose
agent_goalsBusiness goals created by a user, workflow, or generation template
agent_runsIndividual execution attempts with status, budget, metrics, and retry linkage
agent_plansPlanned steps associated with a run
agent_toolsTool registry entries with schemas, owner, enabled state, capability, risk, and rate policy
agent_permissionsAgent/role/policy capability grants
agent_tool_callsScoped audit records for each tool call or denial
agent_approvalsGeneralized approvals for plans, tool calls, artifacts, and schema diffs
agent_artifactsVersioned generated outputs such as schema diffs, specs, seed data, prompts, and migrations
agent_evaluationsEvaluation results used as gates before approval/publish
agent_memoryScoped long-lived memory with provenance, confidence, expiry, and optional embedding

All harness tables include siteId and must be queried through tenant-scoped filters.

Operational notes:

  • POST /api/v1/agent/generate-app creates page_spec, component_spec, seed_data, and api_spec artifacts and evaluates each artifact before returning.
  • agent_runs.metrics.stopReason records completion or budget stop reasons such as completed, error, and max_tool_calls.
  • agent_tool_calls.cost stores token/cost estimates after secret masking.
  • agent_runs.status waiting_user (#710): the run asked a human with askUser and is parked until the question is answered (succeeded, stopReason: answered), times out (failed, stopReason: question_timeout) or is cancelled. No schema change: the question is an agent_approvals row (see below).
  • If the same goal fails at least three runs and a runtime QueueProvider is available, the run service enqueues agent-dead-letter with the scoped run failure payload.

Draft review tables (Jev)

TablePurpose
lumibase_review_profilesVersioned review rubrics; rows are immutable, unique (site_id, key, version)
lumibase_review_runsOne decision run of a profile on an item or snapshot, with provider, model, content hash and answers; purged after DECISION_REVIEW_RETENTION_DAYS
lumibase_review_feedbackAccept/edit/reject per run, question and principal; deleted with its run
lumibase_review_sessionsPilot timing (#516): one editor session from opening the editor to the first save, mode baseline/assisted, duration_ms, locale, the viewed run_id and viewed_questions. No content. run_id is set null when the run is purged
lumibase_ai_automation_actionsJev automation (#518): one metadata action (tag / route) per flow run, node and item, unique (site_id, flow_run_id, node_id, item_id). mode shadow/live, status, reason, proposed, previous, applied, approval_id, reverted_at, plus the chain ids revision_id, review_run_id and content_hash. review_run_id is set null when the run is purged

All five tables have site_id (FK cascade) and every query is site scoped. All five are also in the site_isolation RLS list of packages/database/migrations/rls-policies.sql. The retention purge of lumibase_review_runs runs site by site, each in a transaction that sets app.site_id (apps/cms/src/services/review-run-retention.ts), so it keeps deleting on a database where those policies are applied. Turning the draft-review pilot off deletes none of these rows.

Content OS tables (content-os.ts)

CollectionPurpose
content_intentsDeclared desired state (SLO) per collection: rules, cron schedule, budget, autonomy cap, maintenance window. scan_cursor resumes the deterministic scan; semantic_scan_cursor (migration 0022, J12) resumes the separately budgeted semantic pass
content_driftsDetected rule violations; unique (siteId, fingerprint) dedupes detection across scans. status is open/assigned/resolved/stale, or unevaluated for a semantic rule that could not be judged (plain text column, no CHECK constraint). Semantic rows use rule_type = 'semantic' and rule_key = <key>@v<version>:<profileVersion>:<contentHash>
agent_autonomy_grantsEarned trust per (site, agentRole, capability): level 0–4, evidence, expiry
agent_incidentsDemotion evidence and exception-inbox feed (veto, eval_fail, load_guard, …)
agent_freezesKill-switch freezes (scope site/role); liftedAt IS NULL = active; doubles as audit trail
agent_rolesMulti-agent role library with minimal capability sets; harness enforces role ∩ grant
constitutionsVersioned publish-gate evaluator sets; sha256 hash identity; at most one active per site
site_skillsSite Skills (site-skills.ts, migration 0026): playbooks injected into matching agent runs. Unique (siteId, slug); status draft/active/archived; riskClass; scope columns surfaces, tools, collections, triggers, requiredCapabilities; current body, version, hash
site_skill_versionsImmutable version trail of a site skill: unique (skillId, version), content snapshot, sha256 hash, source/sourceRef, changeNote, author. Both tables are in the site_isolation RLS list
agent_tool_secretsSecrets of external-action tools (ai.ts, migration 0030). Unique (siteId, name); ciphertext is a KeyProvider envelope bound to site and name, keyId the key version, updatedBy the administrator. Never returned by an API. In the site_isolation RLS set. See external-action tools

Content OS columns on existing tables:

  • revisions: authorType, createdByRunId, model, constitutionHash, sources, confidence, staged, autoCommitAt (provenance + veto staging).
  • items: pinnedFields (Law Zero — fields a human edit locked against agent writes).
  • agent_goals: parentGoalId (goal tree), origin (user/reconciler/planner/flow), intentId, driftFingerprint, agentRole.
  • agent_approvals: kind (approval/veto/promotion/question), autoCommitAt, approverType (human/agent), approverRunId. A question row (askUser, #710) uses the existing columns: subject_id is the askUser tool call (whose input holds the question, options and context), status is pending/answered/expired/cancelled, expires_at is the deadline, and decision_reason/decided_by/decided_at record the answer and who gave it.

Directus-inspired tables (cms.ts)

CollectionPurpose
content_versionsNamed parallel draft branches of an item, distinct from linear revisions. Snapshots item data + a hash of main at snapshot time (divergence detection). Promote applies a version to main via ItemService (writes a revision). Unique (siteId, collectionId, itemId, key). See .kiro/specs/content-versioning.
dashboardsInsights dashboard container per site (name/icon/color/note).
panelsOne visualization on a dashboard: type (metric/timeSeries/bar/pie/list/table), position ({x,y,w,h}), query (a PanelQuery), options. Aggregates run safely (field whitelist + siteId scope). See .kiro/specs/insights-dashboard.
transform_presetsNamed image-transform presets: URL-safe key → a TransformDsl ({ width?, height?, format?, quality?, fit?, focal? }). Resolved by GET /media/:key?preset=<key>. Unique (siteId, key). Migration 0004_transform_presets. See .kiro/specs/image-transform-dsl.

11. Firebase Sync (firebase-sync.ts)

See features/firebase-sync.md. Migration: 0000_lumibase_init (consolidated).

11d. Change Feed (cdc.ts — spec cdc-extension-integration)

TablePurpose
lumibase_cdc_change_eventsAppend-only transactional outbox: one row per committed mutation (resource item/collection/field/setting — default item, migration 0008; collection, item_id, operation, masked payload, changed_fields, actor/source, occurred_at). The envelope type is <plural-resource>.<operation>. Feed order = keyset (occurred_at, id) — nanoid PKs carry no order. Indexes (site_id, occurred_at, id) and (site_id, collection, occurred_at, id).
lumibase_cdc_subscriptionsConsumer registry + checkpoint (cursor_occurred_at + cursor_id), kind pull/webhook/extension, status active/paused/dead/stale, filters, consecutive_failures. Unique (site_id, name).
lumibase_cdc_deliveriesAppend-only delivery-attempt log per batch (attempt, status, http status, bounded error message, duration). Pruned on the same retention window as the outbox.

All three are site_id-scoped with site_isolation RLS. See docs/en/features/cdc-change-feed.md.

11c. External JWT auth (external-auth.ts)

auth_external_issuers

  • Per-site trusted external JWT issuer. Public config only — no secrets (signatures verify against the issuer's JWKS). id, siteId (FK sites, cascade), issuer (matches the iss claim), jwksUri/discoveryUrl (one required), audience (jsonb: string|string[]), algorithms (jsonb: asymmetric allowlist), claimMapping (jsonb: { email, roles, siteId?, externalId? }), roleMapping (jsonb: { "<claim role>": { roleId|systemKey } }), defaultRoleId, jitProvisioning, clockSkewSeconds, enabled, createdAt, updatedAt.
  • Unique (siteId, issuer); index (siteId, enabled). Migration: 0000_lumibase_init (consolidated). See security/external-jwt-auth.md.

user_identities (social-auth.ts, migration 0032)

  • External login identities linked to an account: id, userId (FK users, cascade), issuer, subject, emailAtLink, createdAt. Unique (issuer, subject); index on user_id.
  • Identity-global like users — no site_id, not under RLS; site access stays in user_sites. Migration 0032 backfills every users.external_id under issuer urn:lumibase:external-id. See security/social-login.md.

social_auth_providers (social-auth.ts, migration 0032)

  • Per-site Google / Apple / GitHub configuration: id, siteId (FK sites, cascade), provider, enabled, clientId, clientSecretCiphertext, appleTeamId, appleKeyId, applePrivateKeyCiphertext, allowedRedirectUris (jsonb), createdAt, updatedAt. Unique (siteId, provider); RLS site_isolation.
  • Secrets are KeyProvider AES-GCM envelopes bound to site + provider, never plaintext and never returned by the API.

lumibase_firebase_sync_pipelines

ColumnTypeNote
idtext PKnanoid
siteIdtextFK sites.id (cascade)
nametextunique per (siteId, name)
targettextfirestore / rtdb
statustextactive / paused / error
statusMessagetextthe most recent error (nullable)
projectIdtextFirebase project id
credentialsEncryptedtextAES-GCM encrypted credential blob (write-only)
collectionsjsonbmachine-names; [] = every collection
targetPathtexttemplate, defaults to {collection}
syncOnCreate / syncOnUpdate / syncOnDeleteinteger1/0 to enable each action
lastSyncAttimestampthe most recent successful sync
lastSyncItemCountintegernullable
createdAt / updatedAttimestamp

lumibase_firebase_sync_log

ColumnTypeNote
idtext PKnanoid
pipelineIdtextFK lumibase_firebase_sync_pipelines.id (cascade)
siteIdtextFK sites.id (cascade)
collection / itemIdtextthe source of the change
actiontextcreate / update / delete
resulttextsuccess / error
errorMessagetextnullable
durationMsintegerround-trip of the Firebase REST call
recordedAttimestampindex (pipelineId, recordedAt)

11b. Regulated / sensitive content (regulated.ts)

Opt-in, additive tables for the regulated-content-readiness capability set. Default Tier 1 installations never write to them. ID convention: nanoid for all (domain + audit-grade), matching the existing audit_log PK.

encryption_keys

Metadata only for key versioning/rotation (Req 3.3) — never stores key material; the bytes live in the runtime KeyProvider (Workers Secrets / env).

ColumnTypeNote
idtext PKnanoid
siteIdtext FK nullablenull ⇒ global key
keyIdtextversion id embedded in the ciphertext envelope (e.g. v1)
statustextactive (encrypt new) / retired (decrypt only)
algotextdefault AES-GCM
createdAt, retiredAttimestamp
Unique: (siteId, keyId).

field_access_log

Audit of every decrypted read of a pii/phi field (Req 6). Never stores the decrypted value; written in batches, site-isolated by RLS.

ColumnTypeNote
idtext PKnanoid
siteIdtext FK
collectiontext
recordIdsjsonbaffected record ids (aggregate for list reads)
fieldsjsonbfield names decrypted
actor, action, requestIdtext
timestamptimestamp
Indexes: (siteId, timestamp), (actor, timestamp).

content_reviews

Human editorial sign-off records (Req 9). itemId is onDelete: set null so review history survives erasure (Req 11.3).

ColumnTypeNote
idtext PKnanoid
siteIdtext FK
itemIdtext FK nullableset null on item delete
revisionIdtext
requestedBy, decidedBytext FK users (set null)
assignedTotextuser id or role token
statustextpending/approved/rejected
reasontext
decidedAt, createdAttimestamp
Indexes: (siteId, status), (siteId, assignedTo).

erasure_requests

GDPR right-to-erasure lifecycle (Req 11). Stores a hash of the subject identifier, never plaintext; supports dual-control.

ColumnTypeNote
idtext PKnanoid
siteIdtext FK
scopejsonb{ collection, filter }
subjectHashtexthash of subject id (never plaintext)
reasontext
requestedBy, confirmedBytext FK users (set null)confirmedBy = second admin (dual-control, Req 11.4)
statustextpending/confirmed/executing/completed/failed
recordCountinteger
createdAt, completedAttimestamp
Index: (siteId, status).

11d. Git Integration (git-integration.ts)

Per-tenant GitHub/GitLab connections + cached PR/CI state, raw webhook log, ephemeral preview environments, and commit↔content provenance. All tables carry site_id (cascade) and are registered for RLS. Physical names carry the lumibase_ prefix (ADR-010). Migration 0009_git_integration.

Table (physical)PurposeKey columns
lumibase_git_integrationsOne repo connection per (site, provider, repo)provider, repo_full_name, auth_method (app|pat), installation_id, encrypted_token, webhook_secret_enc, status, scopes, sync_config
lumibase_git_pull_requestsCached PR/MR stateunique (integration_id, number); state, ci_status, mergeable, head_sha, preview_url, raw
lumibase_git_ci_runsCI run + jobs + stored-log refunique (integration_id, provider_run_id); status, jobs, duration_ms, log_ref (runtime blob)
lumibase_git_webhook_eventsRaw inbound events (replay-able)unique (provider, delivery_id); event, payload, processed, error
lumibase_git_preview_envsEphemeral preview site per PRunique (pr_id); ephemeral_site_id, status, url, expires_at
lumibase_git_provenanceCommit/PR → content/schema/intent linkcommit_sha, pr_number, collection, item_id, change_type

Tokens + webhook secrets are encrypted at rest via CryptoService (AES-GCM, AAD-bound to { siteId, integrationId, field }); plaintext is never stored.

12. Indexing & RLS

  • An index on (siteId, …) is mandatory on every domain table.
  • Apply the Drizzle scopeSite(siteId) helper at the repository layer.
  • Defence in depth: with LUMIBASE_DB_RLS=enforce, withDb() (apps/cms/src/middleware/db.ts) runs each request statement in its own transaction with app.site_id set, and withRls() (apps/cms/src/middleware/rls.ts) fails closed if the request db is not scoped. Off by default (B130).
  • The policies live in packages/database/migrations/rls-policies.sql. The operator applies that file by hand; pnpm db:migrate does not. Each listed table gets two policies with the same predicate: site_isolation (RESTRICTIVE) and site_isolation_permit (PERMISSIVE). The exception is lumibase_extensions, whose global rows (site_id IS NULL) stay visible. With app.site_id unset, no row of a scoped table is visible.
Last modified: 15/11/2026