Data model

Launchpad joins a Supabase configuration plane to a ClickHouse analytics plane. The stores use different identifiers deliberately: Supabase clients.id is the relational primary key, while ClickHouse client_id carries clients.slug as the analytics tenant key.

Paid-acquisition hierarchy

Client
 └── Ad account / source
      └── Campaign
           └── Ad set
                └── Ad
                     └── Creative

Campaign, ad-set, ad, and creative identifiers are separate. One creative asset may appear in more than one ad, so ad_id cannot be replaced by creative_id.

Canonical paid row

PaidAcquisitionRow in src/lib/marketing/row.ts is the exact TypeScript contract for marketing_paid_acquisition. Its identity and dimensions include:

interface PaidAcquisitionRow {
  event_id: string
  client_id: string       // clients.slug
  brand_id: string
  account_id: string
  account_name: string
  synced_at: string
  date: string
  platform: string        // facebook, google, tiktok, ...
  campaign_id: string
  ad_set_id: string
  ad_id: string
  creative_id: string
  segment_key: string
  country_code: string
  device_type: string
  age_group: string
  gender: string
  // names, other dimensions, and metric columns omitted here
}

Read the live interface before adding a fetcher. There is no raw payload column, the account field is account_id, and the segment field is segment_key.

Event identity and latest state

paidEventId() hashes exactly these 12 pipe-delimited parts:

client_id | date | platform | campaign_id | ad_set_id | ad_id |
creative_id | segment_key | country_code | device_type | age_group | gender

That identity matches the ClickHouse ORDER BY grain. marketing_paid_acquisition is a ReplacingMergeTree(synced_at); deduplication is defined by the ordered grain and version, not by event_id alone. Use the maintained latest-state views or FINAL where the query contract requires it.

brand_id, account_id, names, and selected attributes are functionally dependent values rather than identity parts. Changing an attribute must not fork the fact grain.

Analytics families

Family	Grain / responsibility
marketing_paid_acquisition	Paid-media metrics at day + entity + segment grain
marketing_sales	Normalized transactions and lifecycle classification
marketing_crm_order_events	Event-grain CRM/order lineage
marketing_attribution_events	Event-grain direct-attribution evidence with latest-state views
Creative/check tables	Creative metadata, transcript/QC outputs, and review state
Sync/activity tables	Connector execution and freshness evidence

Do not collapse platform purchases, CRM orders, and attributed sales into one denominator. Each metric keeps its own source and grain.

Reporting catalog and breakdowns

The current reporting catalog is composed in src/lib/marketing/table/columnOptions.ts and src/lib/marketing/table/mergedColumns.ts; PerformanceReport consumes the merged catalog. Breakdown definitions live with the marketing types and segment/query code. Avoid hard-coded column or breakdown counts in documentation because both are generated/composed and change frequently.

Missing, unavailable, null, and numeric zero are distinct states. A source that was not queried or cannot support a grain must not be rendered as zero.

Configuration and credentials

Supabase owns clients, users/RBAC, brands, account bindings, report settings, and credential pointers. client_ad_accounts.credential_ref points to sealed connector credentials; no connector secret belongs inline on the account row. See Schemas and migrations and Credentials and secret stores.