← Back to index | ← 03 Format taxonomy | 05 Configuration flow →

The full Postgres schema for every per-tenant configurable surface. Reflects state after migration 0009 (2026-05-19).

Tables

                       ┌────────────────┐
                       │   tenants      │
                       │   id (PK)      │
                       │   name         │
                       │   tier         │
                       │   locale       │
                       └────────┬───────┘
                                │
              ┌─────────────────┼─────────────────────┐
              │                 │                     │
              ▼                 ▼                     ▼
   ┌────────────────┐  ┌─────────────────┐  ┌────────────────────┐
   │ tenant_brand   │  │ framework_      │  │ framework_config_  │
   │ (1:1)          │  │ configs (1:N    │  │ snapshots (audit)  │
   │                │  │  per framework) │  │                    │
   │ display_name   │  │ archetype_      │  │ snapshot_at        │
   │ coach_*        │  │   overrides     │  │ snapshot_by        │
   │ brand JSONB    │  │ voice_overrides │  │ diff JSONB         │
   │ slug_prefix    │  │ scoring_*       │  │ full_config JSONB  │
   │ canonical_*    │  │ mode_overrides  │  │                    │
   │ archetype_     │  │ landing_*       │  └────────────────────┘
   │   scores_*     │  │ communications  │
   │ analytics_*    │  │ results_display │
   │ coach_         │  │ providers       │
   │   notification │  │ version         │
   └────────────────┘  └─────────────────┘

All tables use ON DELETE CASCADE from tenants so removing a tenant cleans up cleanly.

tenants

The base table. Light — just identity + tier.

CREATE TABLE tenants (
    id          VARCHAR(64)  PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    tier        VARCHAR(32)  DEFAULT 'starter',
    locale      VARCHAR(8)   DEFAULT 'en',
    created_at  TIMESTAMPTZ  DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  DEFAULT NOW(),
    metadata    JSONB        DEFAULT '{}'::jsonb
);

Feature gating lives in api-gateway/feature_gate.py::ASSESSMENT_ACTION_FEATURES.

tenant_brand

One row per tenant. Framework-agnostic — drives identity, white-labelling, URL composition, webhooks, notification routing.

CREATE TABLE tenant_brand (
    tenant_id                 VARCHAR(64)  PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    display_name              VARCHAR(255) NOT NULL,
    coach_name                VARCHAR(255),
    coach_title               VARCHAR(255),
    coach_photo               TEXT,
    coach_email               VARCHAR(255),
    website_url               TEXT,
    booking_url               TEXT,
    legal_entity              VARCHAR(255),
    brand                     JSONB        NOT NULL DEFAULT '{}'::jsonb,
    voice_id                  VARCHAR(64),
    slug_prefix               VARCHAR(64)  NOT NULL DEFAULT '',  -- 0008
    canonical_quiz_url        VARCHAR(512),                       -- 0008
    archetype_scores_webhook  TEXT,                               -- 0009
    archetype_scores_secret   VARCHAR(255),                       -- 0009
    analytics_webhook         TEXT,                               -- 0009
    coach_notification_email  VARCHAR(255),                       -- 0009
    updated_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_by                VARCHAR(255)
);

Field semantics

brand JSONB shape

{
  "primary":      "#401405",
  "accent":       "#B39F65",
  "cream":        "#EDEBDE",
  "sage":         "#919C82",
  "logoUrl":      "https://...",
  "fontHeading":  "Newsreader",
  "fontBody":     "Mulish",
  "theme_mode":   "override"   // or "inherit" to opt out of theming
}

Both camelCase (logoUrl, fontHeading) and snake_case (logo_url, font_heading) are accepted by the theme provider for backwards-compat.

framework_configs

One row per (tenant_id, framework_id) pair. The 8 override columns are the editable surface for the dashboard.

CREATE TABLE framework_configs (
    tenant_id            VARCHAR(64) NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    framework_id         VARCHAR(64) NOT NULL,
    archetype_overrides  JSONB DEFAULT '{}'::jsonb,
    voice_overrides      JSONB DEFAULT '{}'::jsonb,
    scoring_overrides    JSONB DEFAULT '{}'::jsonb,
    mode_overrides       JSONB DEFAULT '{}'::jsonb,
    landing_overrides    JSONB DEFAULT '{}'::jsonb,
    communications       JSONB DEFAULT '{}'::jsonb,
    results_display      JSONB DEFAULT '{}'::jsonb,
    providers            JSONB DEFAULT '{}'::jsonb,
    test_clients         JSONB DEFAULT '[]'::jsonb,    -- 0005
    version              INT  NOT NULL DEFAULT 1,
    updated_at           TIMESTAMPTZ DEFAULT NOW(),
    updated_by           VARCHAR(255),
    reason               TEXT,
    PRIMARY KEY (tenant_id, framework_id)
);

Per-column shape (Money Archetypes)

See 02 Tenancy model §Override merge rules for the per-column merge semantics.

framework_config_snapshots

Audit + restore log. Auto-written on every PATCH.

CREATE TABLE framework_config_snapshots (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       VARCHAR(64) NOT NULL,
    framework_id    VARCHAR(64) NOT NULL,
    snapshot_at     TIMESTAMPTZ DEFAULT NOW(),
    snapshot_by     VARCHAR(255),
    reason          TEXT,
    diff            JSONB,
    full_config     JSONB NOT NULL,
    FOREIGN KEY (tenant_id, framework_id)
        REFERENCES framework_configs(tenant_id, framework_id) ON DELETE CASCADE
);

CREATE INDEX idx_snapshots_tenant_framework
    ON framework_config_snapshots(tenant_id, framework_id, snapshot_at DESC);

• Cap: 50 snapshots per (tenant, framework). Older ones are deleted on PATCH.
• diff is a compact JSON diff vs the previous snapshot (lazy — may be NULL).
• full_config is the entire row state at snapshot time. Restore writes this back as the live config + creates a new snapshot for the restore action.

assess_sessions (analytics + Personas)

Per-completion row, populated from the agent and /api/quiz/results. Drives platform /admin/analytics.

CREATE TABLE assess_sessions (
    session_id     VARCHAR(64) PRIMARY KEY,
    tenant_id      VARCHAR(64) NOT NULL,
    framework_id   VARCHAR(64) NOT NULL,
    format         VARCHAR(32),       -- 'qa', 'game', 'deal', 'realm', 'likert'
    user_email     VARCHAR(255),
    user_name      VARCHAR(255),
    scores         JSONB,             -- calibrated final scores
    primary_id     VARCHAR(64),       -- dominant archetype id
    secondary_id   VARCHAR(64),
    completed_at   TIMESTAMPTZ DEFAULT NOW(),
    metadata       JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX idx_sessions_tenant_format ON assess_sessions(tenant_id, format, completed_at DESC);

Note: MMC's Legacy Likert sessions live in legacy_mq_* tables (in WP DB), NOT here. See 08a MMC analytics.

assess_audit_log

Phase 10 (2026-05-15+). Every state-changing operation (PATCH config, upload doc, run AI, restore snapshot, etc.) writes a row.

CREATE TABLE assess_audit_log (
    id                BIGSERIAL PRIMARY KEY,
    tenant_id         VARCHAR(64) NOT NULL,
    framework_id      VARCHAR(64),
    target_kind       VARCHAR(64),       -- 'config', 'tenant_brand', 'client_doc', ...
    target_slug       VARCHAR(255),
    action            VARCHAR(64),
    actor_user_id     VARCHAR(255),
    actor_role        VARCHAR(64),
    payload_hash      VARCHAR(64),
    metadata          JSONB DEFAULT '{}'::jsonb,
    created_at        TIMESTAMPTZ DEFAULT NOW()
);

Critical implementation note (from feedback_dashboard_runner_defensive.md): audit-log inserts must use their OWN short-lived autocommit connection. The per-request transaction rolls back on HTTPException, including any audit row written inside the handler.

Migrations

All in api-gateway/migrations/. Idempotent. Apply with kubectl exec postgres-0 -- psql < <file>.

Seed scripts (seed_*.sql) populate tenants + initial tenant_brand rows. Not in the migration sequence — applied manually on cluster bootstrap.

Adding a new override column

Order of operations:

1. Write the migration (migrations/00XX_<name>.sql). Use ADD COLUMN IF NOT EXISTS. Add COMMENT ON COLUMN .... Backfill if needed.
2. Update _TENANT_BRAND_FIELDS or _CONFIG_OVERRIDE_COLUMNS (whichever applies) in registry/framework_config.py.
3. Update TypeScript interface in dashboard/src/lib/api.ts (TenantBrand or FrameworkConfig).
4. Update the editor: extend the BrandForm / config form and add the new UI section + i18n keys (4 locales).
5. Update consumers: if the resolved value affects the quiz, update loadResolvedFramework consumers in moneyquiz-app.
6. Apply migration to production: kubectl exec postgres-0 ... < <file>.
7. Build + push new gateway + dashboard images.
8. Smoke: GET the resolved view, verify new field present.
9. Update this section with the new column.

Removed / deprecated

None yet. Format-naming-related cleanups (C-phase 2026-05-18) were schema-additive — no columns deleted.

Next

→ 05 Configuration flow — the PATCH → snapshot → resolve → cache lifecycle.