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 namedlumibase_<name>(e.g. the logicaluserstable is physicallylumibase_users), created by the0000_lumibase_initmigration. This reserves thelumibase_namespace for the platform, so any table WITHOUT that prefix is unambiguously user-created (or amat_*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:
| File | Tables (physical) |
|---|---|
core.ts | lumibase_organizations, lumibase_organization_members, lumibase_sites, lumibase_users, lumibase_user_sites, lumibase_teams, lumibase_team_members, lumibase_notifications |
access.ts | lumibase_roles, lumibase_policies, lumibase_role_policies, lumibase_user_policies, lumibase_permissions, lumibase_refresh_tokens |
cms.ts | lumibase_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.ts | lumibase_folders, lumibase_files, lumibase_presets, lumibase_translations, lumibase_settings, lumibase_webhooks, lumibase_extensions, lumibase_translation_memory, lumibase_glossary, lumibase_push_subscriptions |
ai.ts | lumibase_ai_approvals, lumibase_agent_* |
firebase-sync.ts | lumibase_firebase_sync_pipelines, lumibase_firebase_sync_log |
external-auth.ts | lumibase_auth_external_issuers |
social-auth.ts | lumibase_user_identities, lumibase_social_auth_providers |
imports.ts | lumibase_import_jobs |
activation.ts | lumibase_activation_milestones, lumibase_telemetry_state |
Migrations live in packages/database/migrations/ and packages/database/drizzle/.
1. Core tenancy & identity (core.ts)
organizations
idtext PK (nanoid), uniqueslug,name,settingsjsonb default{},createdAt,updatedAt.
organization_members
userId,organizationId,role,createdAt; composite PK(userId, organizationId). Role CHECK:owner,admin,billing,member. Roles use the fixedcanOrgmatrix, not site policies. Organization tables have RLS usinglumibase.organization_id.
sites
organizationIdis a required FK toorganizationswith delete RESTRICT. Migration0024_organizationsbackfills existing sites into thedefaultorganization 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 anddefaultLanguage,defaultAppearance, anddefaultSaveAction(stay|return|create_new, defaultstay— the site-wide default Studio save action; per-user override lives inusers.preferences.saveAction).emailIdentityStrictboolean, defaultfalse(migration0027) — per-site email identity tier: when on, one membership per root mailbox (seeuser_sites.identityKey).
users
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
logtoId | text unique | OIDC subject |
email, firstName, lastName, avatar | text | |
status | text | active/invited/suspended |
language, theme, tfa | jsonb | preferences |
preferences | jsonb | per-user UI prefs: { language, theme, timezone, defaultPresets, saveAction }. saveAction (stay/return/create_new) overrides sites.default_save_action. |
lastSeenAt | timestamp | |
passwordChangedAt | timestamp | Set on every reset/change (migration 0006). A password-reset token whose iat predates it is rejected → single-use reset links. |
emailCanonical | text unique | Account 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. |
emailRoot | text | Root 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, updatedAt | timestamp |
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.identityKeytext (migration0027) —users.email_rootwhen the site is strict, otherwise null. Unique indexuser_sites_site_identity_uniqueon(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
| Column | Type | Note |
|---|---|---|
id | text PK | |
siteId | text FK | |
name | text | machine name, unique per site |
label, pluralLabel | text | author-facing labels |
hidden, system | boolean | |
singleton | boolean | |
icon, color, note | text | |
primaryKeyField | text | logical item identifier field, defaults to id |
primaryKeyType | text | nanoid, uuid, integer, bigInteger, string |
storageMode | text | jsonb, materialized, physical, external |
displayTemplate | text | mustache template default |
sortField, archiveField, archiveValue, unarchiveValue | text | |
itemDuplicationFields, translations | jsonb | |
accountability | text | all / activity / none |
versioning | boolean | |
meta | jsonb | extra 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 byitems.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
| Column | Type | Note |
|---|---|---|
id | text PK | |
siteId, collectionId | text FK | |
name | text | machine |
type | text | string,text,integer,decimal,boolean,json,uuid,date,datetime,time,csv,hash,geometry,alias |
interface | text | UI editor key |
display | text | display formatter key |
options, displayOptions, validation, conditions, translations | jsonb | |
required, readonly, hidden, encrypted, versioned, rawEnabled | boolean | |
classification | text | Data sensitivity (Req 5): none/internal/pii/phi. pii/phi must be encrypted=true; drives default masking, read_decrypted gating, and field_access_log audit. |
sortOrder | integer | |
width, group | text |
Compiled schemas expose generated system fields in addition to user-defined rows:
| Field | Type | Notes |
|---|---|---|
id | string | Primary item identifier, readonly/generated. |
status | string | Workflow status; visible when status/archive behavior is enabled. |
sort | integer | Manual ordering value. |
user_created | string | Creator user ID, readonly/generated. |
user_updated | string | Last updater user ID, readonly/generated. |
created_at | datetime | Creation timestamp, readonly/generated. |
updated_at | datetime | Last update timestamp, readonly/generated. |
deleted_at | datetime | Soft-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, withm2areserved. - The schema service validates referenced collections and fields, and blocks collection deletes when relations still reference either side.
items
| Column | Type |
|---|---|
id | text PK |
siteId, collectionId | text FK |
status | text |
data | jsonb |
sort | integer |
userCreated, userUpdated | text FK users |
publishAt, unpublishAt | timestamp nullable |
editorialState | text nullable |
dekWrapped | text nullable |
createdAt, updatedAt | timestamp |
deletedAt | timestamp 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)(mirrorsitems.publishDueIdxfor 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(defaultpublished),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)
| Column | Type | Note |
|---|---|---|
id, siteId | PK + FK | |
name, description | text | |
status | text | active / inactive / draft |
triggerType | text | webhook / event / schedule / manual |
triggerOptions, graph | jsonb | graph: { entry?, nodes: [{ id, key, options, next?, onError? }] } |
nextRunAt | timestamp | used by the schedule trigger |
accountability | text |
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),principaljsonb (re-resolved at every step),created_by. - Progress:
total_rows,cursor(next row),created_rows,updated_rows,failed_rows,errorsjsonb ({ row, id?, message }[], capped) anderrors_truncated. - Worker coordination:
lease_owner,lease_until,timeout_ms,cancel_requested_at,started_at,finished_at.sourceholds 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 persite_idandmilestone(site_created/item_published/delivery_request/agent_run), withreached_atandsource(event/derived). Written once, never updated. Site-isolated under RLS.telemetry_state: instance-level singleton (id = 'singleton'), not site-scoped. Holds the randominstall_idandsentjsonb (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/diffcompares proposed collection metadata, fields, and relations with the current schema.PUT /api/v1/collections/{name}/schemavalidates 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.changedevent.
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, andprimaryKeyType; - user fields plus compiled system fields;
required,nullable,readonly,generated,system,encrypted, andprimaryKeyflags;- 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,adminAccessboolean,appAccessboolean.- Note:
adminAccess/appAccessare 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_policiesallows 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. Seesecurity/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 byuser_id, nosite_id, not under RLS — a passkey belongs to an RP ID (domain), not to a site. Nothing secret is stored. Seesecurity/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
scopeissite(default) ororganization. CHECK requires exactly one matching target: site scope hassiteIdand noorganizationId; organization scope hasorganizationIdand nositeId. 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,bookmarktext 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
| Column | Type | Note |
|---|---|---|
id | text PK | |
siteId | nullable text FK | null = global |
name, version | text | |
type | text | hook/endpoint/module/interface/display/layout/panel/operation |
enabled | boolean | |
bundleUrl | text | R2/S3 path |
manifest | jsonb | |
capabilities | jsonb | granted 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
| Column | Type | Note |
|---|---|---|
id | text PK | existing IDs preserved; UUIDv7 for organization approvals |
siteId | text nullable FK → sites CASCADE | site-scoped approval parent |
organizationId | text nullable FK → organizations CASCADE | organization-scoped approval parent |
organizationRequester | jsonb nullable | requesting user or API-key identity |
result | jsonb nullable | committed human receipt, possibly a new key |
agentName | text | default 'lumibase-copilot' |
skillName | text | from the CORE_SKILLS registry |
arguments | jsonb | the skill's arguments |
status | text | pending / approved / rejected |
context | text nullable | the user's original message |
createdAt, decidedAt | timestamp | |
decidedBy | text 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
| Collection | Purpose |
|---|---|
agent_goals | Business goals created by a user, workflow, or generation template |
agent_runs | Individual execution attempts with status, budget, metrics, and retry linkage |
agent_plans | Planned steps associated with a run |
agent_tools | Tool registry entries with schemas, owner, enabled state, capability, risk, and rate policy |
agent_permissions | Agent/role/policy capability grants |
agent_tool_calls | Scoped audit records for each tool call or denial |
agent_approvals | Generalized approvals for plans, tool calls, artifacts, and schema diffs |
agent_artifacts | Versioned generated outputs such as schema diffs, specs, seed data, prompts, and migrations |
agent_evaluations | Evaluation results used as gates before approval/publish |
agent_memory | Scoped 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-appcreatespage_spec,component_spec,seed_data, andapi_specartifacts and evaluates each artifact before returning.agent_runs.metrics.stopReasonrecords completion or budget stop reasons such ascompleted,error, andmax_tool_calls.agent_tool_calls.coststores token/cost estimates after secret masking.agent_runs.statuswaiting_user(#710): the run asked a human withaskUserand 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 anagent_approvalsrow (see below).- If the same goal fails at least three runs and a runtime
QueueProvideris available, the run service enqueuesagent-dead-letterwith the scoped run failure payload.
Draft review tables (Jev)
| Table | Purpose |
|---|---|
lumibase_review_profiles | Versioned review rubrics; rows are immutable, unique (site_id, key, version) |
lumibase_review_runs | One 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_feedback | Accept/edit/reject per run, question and principal; deleted with its run |
lumibase_review_sessions | Pilot 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_actions | Jev 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)
| Collection | Purpose |
|---|---|
content_intents | Declared 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_drifts | Detected 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_grants | Earned trust per (site, agentRole, capability): level 0–4, evidence, expiry |
agent_incidents | Demotion evidence and exception-inbox feed (veto, eval_fail, load_guard, …) |
agent_freezes | Kill-switch freezes (scope site/role); liftedAt IS NULL = active; doubles as audit trail |
agent_roles | Multi-agent role library with minimal capability sets; harness enforces role ∩ grant |
constitutions | Versioned publish-gate evaluator sets; sha256 hash identity; at most one active per site |
site_skills | Site 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_versions | Immutable 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_secrets | Secrets 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. Aquestionrow (askUser, #710) uses the existing columns:subject_idis theaskUsertool call (whoseinputholds the question, options and context),statusispending/answered/expired/cancelled,expires_atis the deadline, anddecision_reason/decided_by/decided_atrecord the answer and who gave it.
Directus-inspired tables (cms.ts)
| Collection | Purpose |
|---|---|
content_versions | Named 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. |
dashboards | Insights dashboard container per site (name/icon/color/note). |
panels | One 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_presets | Named 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)
| Table | Purpose |
|---|---|
lumibase_cdc_change_events | Append-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_subscriptions | Consumer 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_deliveries | Append-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 theissclaim),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 onuser_id. - Identity-global like
users— nosite_id, not under RLS; site access stays inuser_sites. Migration0032backfills everyusers.external_idunder issuerurn: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); RLSsite_isolation. - Secrets are KeyProvider AES-GCM envelopes bound to site + provider, never plaintext and never returned by the API.
lumibase_firebase_sync_pipelines
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
siteId | text | FK sites.id (cascade) |
name | text | unique per (siteId, name) |
target | text | firestore / rtdb |
status | text | active / paused / error |
statusMessage | text | the most recent error (nullable) |
projectId | text | Firebase project id |
credentialsEncrypted | text | AES-GCM encrypted credential blob (write-only) |
collections | jsonb | machine-names; [] = every collection |
targetPath | text | template, defaults to {collection} |
syncOnCreate / syncOnUpdate / syncOnDelete | integer | 1/0 to enable each action |
lastSyncAt | timestamp | the most recent successful sync |
lastSyncItemCount | integer | nullable |
createdAt / updatedAt | timestamp |
lumibase_firebase_sync_log
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
pipelineId | text | FK lumibase_firebase_sync_pipelines.id (cascade) |
siteId | text | FK sites.id (cascade) |
collection / itemId | text | the source of the change |
action | text | create / update / delete |
result | text | success / error |
errorMessage | text | nullable |
durationMs | integer | round-trip of the Firebase REST call |
recordedAt | timestamp | index (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).
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
siteId | text FK nullable | null ⇒ global key |
keyId | text | version id embedded in the ciphertext envelope (e.g. v1) |
status | text | active (encrypt new) / retired (decrypt only) |
algo | text | default AES-GCM |
createdAt, retiredAt | timestamp | |
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.
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
siteId | text FK | |
collection | text | |
recordIds | jsonb | affected record ids (aggregate for list reads) |
fields | jsonb | field names decrypted |
actor, action, requestId | text | |
timestamp | timestamp | |
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).
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
siteId | text FK | |
itemId | text FK nullable | set null on item delete |
revisionId | text | |
requestedBy, decidedBy | text FK users (set null) | |
assignedTo | text | user id or role token |
status | text | pending/approved/rejected |
reason | text | |
decidedAt, createdAt | timestamp | |
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.
| Column | Type | Note |
|---|---|---|
id | text PK | nanoid |
siteId | text FK | |
scope | jsonb | { collection, filter } |
subjectHash | text | hash of subject id (never plaintext) |
reason | text | |
requestedBy, confirmedBy | text FK users (set null) | confirmedBy = second admin (dual-control, Req 11.4) |
status | text | pending/confirmed/executing/completed/failed |
recordCount | integer | |
createdAt, completedAt | timestamp | |
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) | Purpose | Key columns |
|---|---|---|
lumibase_git_integrations | One 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_requests | Cached PR/MR state | unique (integration_id, number); state, ci_status, mergeable, head_sha, preview_url, raw |
lumibase_git_ci_runs | CI run + jobs + stored-log ref | unique (integration_id, provider_run_id); status, jobs, duration_ms, log_ref (runtime blob) |
lumibase_git_webhook_events | Raw inbound events (replay-able) | unique (provider, delivery_id); event, payload, processed, error |
lumibase_git_preview_envs | Ephemeral preview site per PR | unique (pr_id); ephemeral_site_id, status, url, expires_at |
lumibase_git_provenance | Commit/PR → content/schema/intent link | commit_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 withapp.site_idset, andwithRls()(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:migratedoes not. Each listed table gets two policies with the same predicate:site_isolation(RESTRICTIVE) andsite_isolation_permit(PERMISSIVE). The exception islumibase_extensions, whose global rows (site_id IS NULL) stay visible. Withapp.site_idunset, no row of a scoped table is visible.