Schemas and migrations

The numbered SQL migrations in the repository are the schema source of truth. Table summaries here are orientation, not a substitute for reading the latest migrations.

Supabase (Postgres)

• Migrations: supabase/migrations/*.sql
• Ledgered runner: scripts/migrate-supabase.ts
• Deployment gate: scripts/migrate-on-deploy.ts, invoked by the application build

Supabase is the configuration and tenancy plane: clients, users, roles, account bindings, connector pointers, platform-service settings, report configuration, and operational metadata.

Migration names: early files are numbered (001 to 062); later files use a timestamp prefix (YYYYMMDDHHMMSS_name.sql, from late August 2026). The ledger tracks the full filename.

Tables and columns added since September 2026 (orientation only; read the migration):

Area	Objects	Migration
Ad account sign-off and exclusion	validation status and reporting-excluded flag on client_ad_accounts	20260918120000, 20260921120000
Spend Guards	spend_guard_settings, spend_guard_snapshots, spend_guard_events; scope and prefix columns	20260923120000, 20260924120000, 20260930140000
Issues	system_issues	20260930120000
Vrio	vrio platform value	20260930130000
Export templates	report_export_templates	20261015120000
Campaign on/off	clients.campaign_status_actions_enabled, audit campaign_status_actions	20261015130000
Budget edits	clients.budget_edits_enabled, audit ad_budget_changes (decimal dollars)	20261016120000
Report switches	saved-view CRM columns, clients.show_crm_new_leads, clients.count_no_account_orders	20261016130000, 20261016140000

Important invariants:

• clients.id is the Postgres primary key; clients.slug is the tenant key carried into ClickHouse as client_id.
• client_ad_accounts.credential_ref is a pointer. Connector ciphertext lives in platform_credentials.encrypted_creds.
• Credential ciphertext uses AES-256-GCM with CREDENTIAL_ENCRYPTION_KEY; it is not derived from the Supabase service-role key.
• New tenant-facing tables need reviewed RLS policies in the same migration.

ClickHouse

• Migrations: clickhouse/migrations/*.sql
• Ledgered runner: scripts/migrate-clickhouse.ts
• Deployment gate: scripts/migrate-on-deploy.ts

The main table families are:

Family	Purpose
marketing_paid_acquisition + latest-state views	Paid media facts at campaign/ad set/ad/segment grain
marketing_sales	Normalized transaction facts from commerce and CRM sources
marketing_crm_order_events	Event-grain CRM/order lineage
marketing_attribution_events + latest-state views	Event-grain direct-attribution facts
Creative/check tables	Creative metadata, review, and QC results
Sync/activity tables	Operational evidence for connector and ingestion health

marketing_paid_acquisition uses ReplacingMergeTree(synced_at) with an ORDER BY grain. Query maintained latest-state views or use FINAL where the source contract requires it; do not treat event_id as the deduplication key.

Applying a migration

1. Choose a unique filename in the correct migration directory. Numeric prefixes are historical ordering aids; the migration ledger tracks the full filename.
2. Make it safe for the ledgered runner and document any baseline/fresh-install precondition.
3. Confirm the exact authorized non-production target, then run npm run migrate:supabase or npm run migrate:clickhouse as applicable and inspect the ledger/result.
4. Run focused tests for every affected reader/writer. There is no single generic schema-test command.
5. Only with explicitly confirmed authorized deployment credentials, run the application build; it executes scripts/migrate-on-deploy.ts before next build.
6. Verify both the migration ledger and the affected runtime query. A successful build alone is not proof that a skipped or unconfigured migration ran.

Do not use supabase db reset --linked against a shared or production project. Do not run migration scripts until the exact target and credentials are confirmed.

Cross-store checks

ClickHouse has no foreign key to Supabase. Application and audit tooling must verify that analytics client_id values map to current clients.slug values, and must preserve the distinction between missing, unavailable, null, and numeric zero.