DATA_MODEL — theme-config-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · MIGRATION_PLAN

Platform anchors: docs/06-data-models.md §4.12 · docs/07-security-compliance-tenancy.md

This document is the DDL-grade persistence specification for theme-config-service. The service runs on Cloud SQL Postgres 16 with tenant_id Row Level Security (RLS) per the platform multi-tenancy model.

1. Database, schemas, roles

Object	Name
Cloud SQL instance (prod)	`melmastoon-prod-pg` (regional HA, europe-west1)
Database	`theme_config`
App schema	`theme_config`
Migrations table	`theme_config.__drizzle_migrations`
Outbox / inbox schema (shared pattern)	`theme_config` (tables `outbox`, `consumed_events`, `idempotency_keys`)
App role	`theme_config_app` (login, RLS-bound)
Migration role	`theme_config_migrator` (DDL only, no row access)
Read-replica role	`theme_config_ro` (analytics; RLS on)

theme_config_app connects via Cloud SQL Auth Proxy + IAM database authentication (no password). All connections go through PgBouncer in transaction-pooling mode.

2. Conventions

IDs: ULIDs (text storage, CHECK length=26). Prefixes per DOMAIN_MODEL §3.
Timestamps: timestamptz UTC; default now().
OCC: every aggregate root has version int not null default 1 updated by version = version + 1 in the UPDATE statement.
Soft delete: aggregates with delete-restore semantics carry deleted_at timestamptz null and a partial unique-index where deleted_at is null. Hard purges run in the tenant-purge worker after the platform retention window.
JSONB: used for token sets, layout selections, content body, locale entries. Always validated by the application layer before write; we add jsonb_check_* CHECK constraints only for shape guarantees that don't require full schema validation.
Naming: snake_case; FK columns named <referenced_singular>_id.
Indexes: every FK has an index; every list endpoint has a covering index.
RLS: every table that carries tenant_id has ENABLE ROW LEVEL SECURITY + a tenant_isolation policy keyed off current_setting('app.tenant_id').

The session GUC app.tenant_id is set by the app on every checked-out connection in a BEGIN … SET LOCAL app.tenant_id = $1 pair before issuing the use-case query batch.

3. Tables

3.1 `themes`

CREATE TABLE theme_config.themes (
  id                       text PRIMARY KEY CHECK (length(id) = 26 AND id LIKE 'thm_%'),
  tenant_id                text NOT NULL CHECK (length(tenant_id) = 26 AND tenant_id LIKE 'tnt_%'),
  property_id              text     NULL CHECK (property_id IS NULL OR (length(property_id) = 26 AND property_id LIKE 'prp_%')),
  name                     text NOT NULL,
  scope                    text NOT NULL CHECK (scope IN ('tenant','property')),
  default_locale           text NOT NULL CHECK (default_locale ~ '^[a-z]{2,3}(-[A-Z]{2})?$'),
  enabled_locales          text[] NOT NULL CHECK (array_length(enabled_locales,1) >= 1),
  fallback_chain           text[] NOT NULL CHECK (array_length(fallback_chain,1) >= 1),
  active_publication_id    text     NULL,
  status                   text NOT NULL CHECK (status IN ('active','suspended','archived')),
  version                  int  NOT NULL DEFAULT 1,
  created_at               timestamptz NOT NULL DEFAULT now(),
  created_by               text NOT NULL,
  updated_at               timestamptz NOT NULL DEFAULT now(),
  updated_by               text NOT NULL,
  deleted_at               timestamptz NULL
);

CREATE UNIQUE INDEX themes_tenant_property_uq
  ON theme_config.themes(tenant_id, COALESCE(property_id,'__null__'))
  WHERE deleted_at IS NULL;

CREATE INDEX themes_tenant_idx ON theme_config.themes(tenant_id);
CREATE INDEX themes_status_idx ON theme_config.themes(status) WHERE deleted_at IS NULL;

ALTER TABLE theme_config.themes ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.themes
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.2 `theme_versions`

CREATE TABLE theme_config.theme_versions (
  id                       text PRIMARY KEY CHECK (id LIKE 'thv_%'),
  tenant_id                text NOT NULL,
  theme_id                 text NOT NULL REFERENCES theme_config.themes(id) ON DELETE CASCADE,
  ordinal                  int  NOT NULL,
  status                   text NOT NULL CHECK (status IN ('draft','preview_ready','published','archived')),
  tokens                   jsonb NOT NULL,             -- DesignTokenSet
  layout_selections        jsonb NOT NULL,             -- LayoutSelections
  ai_provenance            jsonb     NULL,             -- AIProvenance | null
  release_notes            text      NULL,
  published_bundle_url     text      NULL,
  published_bundle_sha256  text      NULL,
  published_bundle_size    int       NULL,
  published_at             timestamptz NULL,
  published_by             text       NULL,
  archived_at              timestamptz NULL,
  archived_reason          text       NULL CHECK (archived_reason IN ('superseded','rolled_back','manual_archive') OR archived_reason IS NULL),
  version                  int  NOT NULL DEFAULT 1,
  created_at               timestamptz NOT NULL DEFAULT now(),
  created_by               text NOT NULL,
  updated_at               timestamptz NOT NULL DEFAULT now(),
  updated_by               text NOT NULL,
  CONSTRAINT theme_versions_ordinal_uq UNIQUE (theme_id, ordinal),
  CONSTRAINT theme_versions_published_consistency CHECK (
    (status = 'published' AND published_at IS NOT NULL AND published_by IS NOT NULL
       AND published_bundle_url IS NOT NULL AND published_bundle_sha256 IS NOT NULL)
    OR
    (status <> 'published')
  )
);

CREATE INDEX theme_versions_theme_status_idx ON theme_config.theme_versions(theme_id, status);
CREATE INDEX theme_versions_tenant_idx       ON theme_config.theme_versions(tenant_id);
CREATE INDEX theme_versions_published_at_idx ON theme_config.theme_versions(theme_id, published_at DESC) WHERE status IN ('published','archived');

ALTER TABLE theme_config.theme_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.theme_versions
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.3 `theme_publications`

CREATE TABLE theme_config.theme_publications (
  id                  text PRIMARY KEY CHECK (id LIKE 'thp_%'),
  tenant_id           text NOT NULL,
  theme_id            text NOT NULL REFERENCES theme_config.themes(id) ON DELETE CASCADE,
  theme_version_id    text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE RESTRICT,
  is_active           boolean NOT NULL DEFAULT false,
  bundle_url          text NOT NULL,
  bundle_sha256       text NOT NULL,
  cdn_invalidation_id text NULL,
  reason              text NOT NULL CHECK (reason IN ('publish','rollback','re_publish')),
  published_at        timestamptz NOT NULL DEFAULT now(),
  published_by        text NULL,                       -- null when system-initiated (provisioning, refresh)
  superseded_at       timestamptz NULL,
  superseded_by_id    text NULL REFERENCES theme_config.theme_publications(id)
);

CREATE UNIQUE INDEX theme_publications_active_uq
  ON theme_config.theme_publications(theme_id)
  WHERE is_active;

CREATE INDEX theme_publications_theme_published_at_idx
  ON theme_config.theme_publications(theme_id, published_at DESC);

ALTER TABLE theme_config.theme_publications ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.theme_publications
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

ALTER TABLE theme_config.themes
  ADD CONSTRAINT themes_active_publication_fk
  FOREIGN KEY (active_publication_id)
  REFERENCES theme_config.theme_publications(id) DEFERRABLE INITIALLY DEFERRED;

The publication-flip in PublishThemeVersionUseCase does:

BEGIN;
  -- mark previous active superseded
  UPDATE theme_config.theme_publications
     SET is_active = false, superseded_at = now(), superseded_by_id = $new_id
   WHERE theme_id = $theme_id AND is_active = true;

  -- insert the new publication as active
  INSERT INTO theme_config.theme_publications (id, tenant_id, theme_id, theme_version_id, is_active, bundle_url, bundle_sha256, reason, published_at, published_by)
  VALUES ($new_id, $tenant_id, $theme_id, $version_id, true, $url, $sha, 'publish', now(), $actor);

  UPDATE theme_config.themes
     SET active_publication_id = $new_id, version = version + 1, updated_at = now(), updated_by = $actor
   WHERE id = $theme_id AND version = $expected_version;
COMMIT;

The partial unique index theme_publications_active_uq makes the swap atomic from the readers' perspective.

3.4 `content_blocks`

CREATE TABLE theme_config.content_blocks (
  id               text PRIMARY KEY CHECK (id LIKE 'cnb_%'),
  tenant_id        text NOT NULL,
  theme_version_id text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  surface          text NOT NULL CHECK (surface IN ('home','detail','search','policies','about','custom')),
  kind             text NOT NULL,           -- ContentBlockKind
  ordinal          int  NOT NULL,
  visibility       text NOT NULL CHECK (visibility IN ('all','desktop_only','mobile_only')),
  body             jsonb NOT NULL,          -- I18nMarkup (per-locale entries)
  meta             jsonb NOT NULL,          -- ContentBlockMeta
  ai_provenance    jsonb NULL,
  version          int  NOT NULL DEFAULT 1,
  created_at       timestamptz NOT NULL DEFAULT now(),
  created_by       text NOT NULL,
  updated_at       timestamptz NOT NULL DEFAULT now(),
  updated_by       text NOT NULL,
  CONSTRAINT content_blocks_ordinal_uq UNIQUE (theme_version_id, surface, ordinal)
);

CREATE INDEX content_blocks_version_surface_idx ON theme_config.content_blocks(theme_version_id, surface, ordinal);

ALTER TABLE theme_config.content_blocks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.content_blocks
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.5 `navigation_configs`

CREATE TABLE theme_config.navigation_configs (
  id               text PRIMARY KEY CHECK (id LIKE 'nvc_%'),
  tenant_id        text NOT NULL,
  theme_version_id text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  surface          text NOT NULL CHECK (surface IN ('header','footer','mobile_drawer')),
  items            jsonb NOT NULL,          -- NavigationItem[]
  version          int  NOT NULL DEFAULT 1,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  updated_by       text NOT NULL,
  CONSTRAINT navigation_configs_uq UNIQUE (theme_version_id, surface)
);

ALTER TABLE theme_config.navigation_configs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.navigation_configs
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.6 `booking_flow_configs`

CREATE TABLE theme_config.booking_flow_configs (
  id               text PRIMARY KEY CHECK (id LIKE 'bfc_%'),
  tenant_id        text NOT NULL,
  theme_version_id text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  steps            jsonb NOT NULL,          -- ordered Step[]
  toggles          jsonb NOT NULL,          -- BookingFlowToggles
  consent          jsonb NOT NULL,          -- ConsentBlock
  version          int  NOT NULL DEFAULT 1,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  updated_by       text NOT NULL,
  CONSTRAINT booking_flow_configs_uq UNIQUE (theme_version_id)
);

ALTER TABLE theme_config.booking_flow_configs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.booking_flow_configs
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.7 `email_themes`

CREATE TABLE theme_config.email_themes (
  id               text PRIMARY KEY CHECK (id LIKE 'emt_%'),
  tenant_id        text NOT NULL,
  theme_version_id text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  tokens           jsonb NOT NULL,          -- EmailTokens
  logo_ref         jsonb NOT NULL,          -- MediaRef
  footer           jsonb NOT NULL,          -- EmailFooter
  version          int  NOT NULL DEFAULT 1,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  updated_by       text NOT NULL,
  CONSTRAINT email_themes_uq UNIQUE (theme_version_id)
);

ALTER TABLE theme_config.email_themes ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.email_themes
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.8 `locale_packs`

CREATE TABLE theme_config.locale_packs (
  id                text PRIMARY KEY,                  -- 'lpk_' + ulid; not exposed in URLs
  tenant_id         text NOT NULL,
  theme_version_id  text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  locale            text NOT NULL CHECK (locale ~ '^[a-z]{2,3}(-[A-Z]{2})?$'),
  entries           jsonb NOT NULL,                    -- { key: string }
  ai_provenance     jsonb NULL,
  completeness_pct  numeric(5,2) NOT NULL DEFAULT 0,
  version           int  NOT NULL DEFAULT 1,
  updated_at        timestamptz NOT NULL DEFAULT now(),
  updated_by        text NOT NULL,
  CONSTRAINT locale_packs_uq UNIQUE (theme_version_id, locale)
);

CREATE INDEX locale_packs_tenant_idx ON theme_config.locale_packs(tenant_id);

ALTER TABLE theme_config.locale_packs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.locale_packs
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

3.9 `layout_presets` (platform-global, no RLS)

CREATE TABLE theme_config.layout_presets (
  id                  text PRIMARY KEY CHECK (id LIKE 'lpr_%'),
  key                 text NOT NULL,
  surface             text NOT NULL CHECK (surface IN ('home','detail','search','custom')),
  supported_variants  text[] NOT NULL,
  supports_rtl        boolean NOT NULL DEFAULT true,
  min_viewport_px     int NOT NULL DEFAULT 360,
  thumbnail_url       text NOT NULL,
  documentation_url   text NULL,
  is_active           boolean NOT NULL DEFAULT true,
  registered_at       timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT layout_presets_key_uq UNIQUE (key)
);

This table is owned by the Frontend Platform squad. No RLS — it is a global registry. App role has SELECT only.

3.10 `preview_tokens`

CREATE TABLE theme_config.preview_tokens (
  id                text PRIMARY KEY CHECK (id LIKE 'pvt_%'),
  tenant_id         text NOT NULL,
  theme_version_id  text NOT NULL REFERENCES theme_config.theme_versions(id) ON DELETE CASCADE,
  token_hash        text NOT NULL,                     -- sha256 hex of secret
  expires_at        timestamptz NOT NULL,
  revoked_at        timestamptz NULL,
  last_accessed_at  timestamptz NULL,
  access_count      int NOT NULL DEFAULT 0,
  note              text NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),
  created_by        text NOT NULL
);

CREATE UNIQUE INDEX preview_tokens_hash_uq ON theme_config.preview_tokens(token_hash);
CREATE INDEX preview_tokens_version_idx     ON theme_config.preview_tokens(theme_version_id) WHERE revoked_at IS NULL;
CREATE INDEX preview_tokens_expires_idx     ON theme_config.preview_tokens(expires_at) WHERE revoked_at IS NULL;

ALTER TABLE theme_config.preview_tokens ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.preview_tokens
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

The CDN edge fetches preview bundles by tokenHash — this is the only path that bypasses app.tenant_id; the read happens via a dedicated theme_config_preview_reader role with a permissive policy guarded by HMAC verification at the edge worker.

3.11 `palette_suggestions`, `translation_drafts`, `content_drafts` (AI HITL workspace)

Each one is a small append-only table for read models that back the HITL approval surface. Schema for palette_suggestions:

CREATE TABLE theme_config.palette_suggestions (
  id                text PRIMARY KEY CHECK (id LIKE 'ais_%'),
  tenant_id         text NOT NULL,
  theme_id          text NOT NULL REFERENCES theme_config.themes(id) ON DELETE CASCADE,
  drafted_tokens    jsonb NOT NULL,
  ai_provenance     jsonb NOT NULL,
  status            text NOT NULL CHECK (status IN ('pending','approved','rejected','superseded')),
  approver_user_id  text NULL,
  approver_note     text NULL,
  applied_to_version_id text NULL REFERENCES theme_config.theme_versions(id),
  created_at        timestamptz NOT NULL DEFAULT now(),
  decided_at        timestamptz NULL
);

CREATE INDEX palette_suggestions_tenant_status_idx ON theme_config.palette_suggestions(tenant_id, status);
ALTER TABLE theme_config.palette_suggestions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON theme_config.palette_suggestions
  USING (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK (tenant_id = current_setting('app.tenant_id'));

Mirror schemas for translation_drafts and content_drafts.

3.12 `outbox`, `consumed_events`, `idempotency_keys`

Per the platform pattern in docs/04-event-driven-architecture.md §10:

CREATE TABLE theme_config.outbox (
  id              bigserial PRIMARY KEY,
  event_id        text NOT NULL UNIQUE CHECK (length(event_id) = 26),
  tenant_id       text NOT NULL,
  event_type      text NOT NULL,
  topic           text NOT NULL,
  partition_key   text NOT NULL,
  envelope        jsonb NOT NULL,
  attributes      jsonb NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz NULL,
  attempt_count   int NOT NULL DEFAULT 0,
  last_error      text NULL
);
CREATE INDEX outbox_unpublished_idx ON theme_config.outbox(created_at) WHERE published_at IS NULL;

CREATE TABLE theme_config.consumed_events (
  event_id     text PRIMARY KEY,
  tenant_id    text NOT NULL,
  event_type   text NOT NULL,
  consumed_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX consumed_events_tenant_idx ON theme_config.consumed_events(tenant_id);

CREATE TABLE theme_config.idempotency_keys (
  key           text PRIMARY KEY,
  tenant_id     text NOT NULL,
  endpoint      text NOT NULL,
  request_hash  text NOT NULL,
  response      jsonb NULL,
  status_code   int NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  expires_at    timestamptz NOT NULL
);
CREATE INDEX idempotency_keys_expires_idx ON theme_config.idempotency_keys(expires_at);

outbox, consumed_events, and idempotency_keys carry tenant_id but are not RLS-protected — they are accessed by infrastructure code under a service-level connection, and the partition keys + indexes make them safe for the workers.

4. JSONB shape contracts

4.1 `theme_versions.tokens`

Validated by application before write; CHECK (jsonb_typeof(tokens) = 'object' AND tokens ? 'color' AND tokens ? 'typography' AND tokens ? 'spacing' AND tokens ? 'radius' AND tokens ? 'shadow' AND tokens ? 'motion' AND tokens ? 'direction').

4.2 `content_blocks.body`

{ "<locale>": { "format": "markdown"|"html"|"structured", "value": <string|object> } }. Constraint: every key matches ^[a-z]{2,3}(-[A-Z]{2})?$.

4.3 `content_blocks.meta`

Discriminated union by kind. We add CHECK (meta ? 'kind' AND meta->>'kind' = kind) so the column-level kind always equals meta.kind.

4.4 `navigation_configs.items`

Recursive array of { id, kind, target, label: { <locale>: string }, ordinal, openInNewTab?, rel?, children?[] }. Application validates max depth 3.

4.5 `booking_flow_configs.steps`

Ordered [{ id, ordinal, required, fields: string[] }]. Application enforces canonical step IDs and field allow-list.

4.6 `locale_packs.entries`

Flat { "<key>": "<icu-message>" }. Application validates ICU placeholder parity vs the default-locale pack.

5. Cross-aggregate consistency

5.1 Single active publication per theme

Enforced by partial unique index theme_publications_active_uq. Concurrent flips fail with unique_violation, which the application maps to MELMASTOON.THEME.PUBLISH_CONFLICT.

5.2 Active publication points to a `published` version

Enforced by the deferrable themes_active_publication_fk plus the theme_versions_published_consistency check. The publish use case orders the writes inside the TX so the constraint always holds at COMMIT.

5.3 Child aggregates belong to their version's tenant

There is no FK on tenant_id (it lives on the parent), but the RLS policy ensures a child write in a different tenant context fails. We additionally add CHECK (tenant_id = (SELECT tenant_id FROM theme_versions WHERE id = theme_version_id)) as a trigger-based assertion in test environments only (too expensive in prod).

5.4 Locale pack locale ∈ theme.enabled_locales

Enforced by application; we cannot express this in SQL without a trigger because enabled_locales lives on themes and locale_packs references theme_versions. The application validates on PutLocalePack and RemoveLocale.

6. Read models / materialised views

6.1 `published_theme_view`

CREATE MATERIALIZED VIEW theme_config.published_theme_view AS
SELECT t.id          AS theme_id,
       t.tenant_id,
       t.property_id,
       t.default_locale,
       t.enabled_locales,
       p.id          AS publication_id,
       p.theme_version_id,
       p.bundle_url,
       p.bundle_sha256,
       p.published_at
  FROM theme_config.themes t
  JOIN theme_config.theme_publications p ON p.id = t.active_publication_id
 WHERE t.deleted_at IS NULL;

CREATE UNIQUE INDEX published_theme_view_pk ON theme_config.published_theme_view(theme_id);
CREATE INDEX        published_theme_view_tenant_idx ON theme_config.published_theme_view(tenant_id);

Refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY theme_config.published_theme_view triggered by the theme.published.v1 event handler in the read-side worker (≤ 5s lag SLO).

6.2 `host_to_theme_view`

CREATE MATERIALIZED VIEW theme_config.host_to_theme_view AS
SELECT host_row.host,
       t.id          AS theme_id,
       t.tenant_id,
       p.bundle_url
  FROM tenant_service.tenant_hosts host_row
  JOIN theme_config.themes t   ON t.tenant_id = host_row.tenant_id
                              AND COALESCE(t.property_id, '') = COALESCE(host_row.property_id, '')
  JOIN theme_config.theme_publications p ON p.id = t.active_publication_id
 WHERE t.deleted_at IS NULL;

CREATE UNIQUE INDEX host_to_theme_view_host_uq ON theme_config.host_to_theme_view(host);

(Cross-schema tenant_service.tenant_hosts is a foreign-data-wrapper read-only view exposed by tenant-service to theme-config-service.)

7. Indexing & query patterns

Query	Index
List themes for tenant	`themes_tenant_idx` + RLS
Active publication for theme	`theme_publications_active_uq`
List versions by status for editor	`theme_versions_theme_status_idx`
Resolve published bundle by theme	`published_theme_view_pk`
Resolve published bundle by host	`host_to_theme_view_host_uq`
Preview token by hash	`preview_tokens_hash_uq`
Locale pack by version+locale	`locale_packs_uq`
Drain outbox	`outbox_unpublished_idx`

8. Retention & purge

Data	Retention	Purge mechanism
Soft-deleted themes (tenant deleted)	30 days	`tenant-purge` worker hard-deletes; cascade on FKs handles children
Archived theme versions	365 days, then bundle URL retained but row purged	retention worker (cron daily)
Preview tokens	7 days post-expiry	`preview-token-sweeper` worker
Outbox	7 days post-publish	nightly `DELETE FROM outbox WHERE published_at < now() - interval '7 days'`
Consumed events	90 days	nightly purge with `tenant_id` partition pruning
Idempotency keys	24h post `expires_at`	nightly purge
AI HITL workspace tables	365 days	retention worker

9. Backups & DR

Automated backups: Cloud SQL automated nightly backups, 30-day retention.
PITR: 7 days, sub-second granularity via WAL.
Cross-region replica: asynchronous read replica in europe-west4 for DR; promotion runbook in DEPLOYMENT_TOPOLOGY.
Bundle URLs: GCS bucket has versioning + dual-region storage; CDN purges do not affect storage durability.

10. References

Domain shapes: DOMAIN_MODEL
Use case orchestration: APPLICATION_LOGIC
Platform data conventions: docs/06-data-models.md §4.12
RLS contract: docs/07-security-compliance-tenancy.md

1. Database, schemas, roles​

2. Conventions​

3. Tables​

3.1 themes​

3.2 theme_versions​

3.3 theme_publications​

3.4 content_blocks​

3.5 navigation_configs​

3.6 booking_flow_configs​

3.7 email_themes​

3.8 locale_packs​

3.9 layout_presets (platform-global, no RLS)​

3.10 preview_tokens​

3.11 palette_suggestions, translation_drafts, content_drafts (AI HITL workspace)​

3.12 outbox, consumed_events, idempotency_keys​

4. JSONB shape contracts​

4.1 theme_versions.tokens​

4.2 content_blocks.body​

4.3 content_blocks.meta​

4.4 navigation_configs.items​

4.5 booking_flow_configs.steps​

4.6 locale_packs.entries​

5. Cross-aggregate consistency​

5.1 Single active publication per theme​

5.2 Active publication points to a published version​

5.3 Child aggregates belong to their version's tenant​

5.4 Locale pack locale ∈ theme.enabled_locales​

6. Read models / materialised views​

6.1 published_theme_view​

6.2 host_to_theme_view​

7. Indexing & query patterns​

8. Retention & purge​

9. Backups & DR​

10. References​