Data model

The Postgres schema and where bytes vs metadata live. Source of truth: db/migrations/. Terms here (base ⊕ overrides, _library, the live pointer) are introduced in Rendering & themes and Theme pipeline.

Migrations

Forward-only SQL, applied in filename order (scripts/migrate.ts), tracked in schema_migrations (name PK, applied_at) — a bookkeeping table the runner creates, not a migration file.

• 0001_init.sql — the 12 base tables (a squash of the original 0001–0007).
• 0002_audit_enrich.sql — adds duration_ms, request_id, source, actor_name, actor_email to audit_log.
• 0003_tenant_integrations.sql — adds integrations jsonb to tenants.

Multi-tenancy: one shared host; the shared base library is the system tenant _library.

The schema

Reading the diagram

• Solid lines = real foreign keys. Only six are DB-enforced: theme→tenants, theme_bundle_version→theme, theme_file→theme (all ON DELETE CASCADE), and domains→tenants, memberships→tenants, theme.base_theme_id→theme (NO ACTION).
• Dashed lines = app-level relationships with no FK — pages, metafields, page_purge_outbox, audit_log all carry tenant_id but it isn't constrained. tenants.live_theme_id/version logically points at a theme_bundle_version but is only a both-or-neither CHECK, not an FK.
• theme.base_theme_id is a self-reference — a store's theme points at the _library base theme it composes over (base ⊕ overrides). This is the lineage the whole theming model rests on.
• Cascade asymmetry matters: deleting a tenant cascades to its theme → theme_bundle_version + theme_file. This is exactly why deleting the _library tenant is guarded in code — it would wipe every store's base.

Table	Holds
tenants (root)	store row; theme/commerce/integrations jsonb; the live_theme_id + live_theme_version pointer
theme	a theme per tenant; base_theme_id (self-FK) + base_version — the base-library link
theme_bundle_version	(theme_id, version) → source_hash, compiled_hash — the frozen-bundle content addresses
theme_file	(theme_id, path) → content_hash, deleted, revision — the mutable draft index (concurrency)
domains	host → tenant_id, verified — custom-domain routing (only verified=true routes)
memberships	(clerk_user_id, tenant_id) → role — the authZ table
pages	(tenant_id, path) → draft_doc/live_doc jsonb — the page-builder store
metafields	(tenant_id, owner_type, owner_id, namespace, key) → jsonb value
page_purge_outbox	pending CDN tag purges — tags text[], state (durable outbox)
audit_log	who did what (enriched: duration, request id, actor name/email)
idempotency_keys, rate_counters	infra — dedupe writes, per-key rate limits

Postgres vs S3

Postgres holds tenant/theme/page metadata + the file index + version hashes + pages jsonb + audit. S3/MinIO holds the actual bundle bytes, content-addressed:

stores/<tenantId>/themes/<themeId>/
    draft/source.gz
    published/source/<hash>.gz
    published/compiled/<hash>.gz

The origin reads compiled bundles through BUNDLE_CDN_URL (CloudFront) with an S3-API fallback.