Spend to CPSC

From you

nothing

Meta lane: direct from Graph since July 7, 2026

Hourly meta-ad-spend-sync at :07 pulls a 3-day window; hours 02, 03 and 04 UTC are skipped on purpose because the nightly job owns the Meta quota then (infra/locals.tf 491-530).
Nightly meta-ad-spend-restatement-sync at 02:37 UTC pulls 28 days plus the creatives crawl; both jobs call runMetaDirectSync and the same upsert keyed on (ad_id, date_start) (jobs/src/lib/meta-direct/sync.ts, pg-write.ts).
The old Hevo to BQ Meta mirror (daily-meta-mirror-sync) is unscheduled and kept unmodified as the rollback lane; re-adding its locals.tf block falls back to BQ.

BigQuery is transport only

ADR-007

Google Ads and TikTok still ride Hevo into levy-law-jdc and reach Neon through the two mirror jobs twice a day (14:20 and 14:30, 22:20 and 22:30 UTC), delta by __hevo__loaded_at.
Hevo still lands Meta in BigQuery for Looker; the Terminal does not read meta_data.hevo_meta_* any more (.claude/rules/external-etl-timing.md line 66, ADR-007 lines 58-62). A Meta spend figure read from BQ after July 7, 2026 is not the Terminal's number.
TikTok: the Hevo pipeline stalled around December 30, 2025, so daily-tiktok-mirror-sync fetches zero rows until it resumes. AppLovin has no Hevo pipeline at all; the job reads the vendor API and replaces a 7-day window.

Attribution: who maps an ad to a campaign

ops.campaign_ads holds one live row per external_ad_id (unique index WHERE archived_at IS NULL, mig 374). The matview joins only this table; ops.campaign_ad_accounts never enters the rollup.
Writers: the admin ad-mapping map route (source_level manual), Terminal-published Meta ads at publish time, and the hourly campaign-ads-reconcile cron, which files Meta ads from adset or Meta-campaign siblings or ops.meta_account_campaign_rules and Google ads from the google rows of ops.campaign_ad_accounts (gcids, reconcile-google.ts 152-160). Anything ambiguous is held, never guessed; Meta skips are written to ops.campaign_ads_reconcile_skips (mig 812).
Shared accounts (the LK and GGH case): ops.meta_account_is_shared() is true when 2+ campaigns hold a live Meta claim on one account in ops.campaign_ad_accounts. A whole-account rule there is refused by trigger and skipped by the reconciler (reconcile-account-rules.ts 33-43), so one ad is never filed under two campaigns (California Schools incident, October 7, 2026).

The rollup: ops.campaign_daily_spend

Grain (campaign_id, spend_date, platform) with spend_usd numeric(18,4). Meta: date_start at UTC, SUM(spend). Google: to_date(__hevo_report_date, 'YYYY/MM/DD'), cost_micros / 1e6. TikTok: TRIM(spend)::numeric. AppLovin: cost by campaign_id_external (mig 763 lines 129-186).
refresh-campaign-daily-spend runs hourly at :25, 18 minutes after the Meta pull and 12 after AppLovin, with REFRESH MATERIALIZED VIEW CONCURRENTLY, then upserts ops.matview_refreshes. It skips with a WARN when ops.campaign_ads has zero live rows (the mig 463 wipe-to-empty lesson).
Mig 793 grants MAINTAIN on the matview to jean_rw, trunks_rw and brick_rw so the agent gateway can run the same refresh under approval.

CPSC and the date-alignment rule

CPSC = spend / signed over ONE window. resolveRanges hands the spend reader an inclusive spend_date range and the signed reader a half-open [from, to) on inception_at at America/New_York midnights covering the same calendar span (dashboard-metrics-source.ts 55-67, metrics-date-range.ts 288-309).
Signed = ops.cases WHERE status = 'active' AND archived_at IS NULL AND operator_excluded_at IS NULL, joined to a live claimant (signed-cases-repository-reads.ts 137-151). The division is avg_cost_per_signed = total_spend / total_signed in campaign-detail-metrics-service.ts line 143; all-in CPSC uses safeCpsc in all-in-cost-ops.ts 254-259.
The pure formula cpsc() in lib/formulas/cpsc.ts refuses a call without a declared windowed or lifetime dateRange (the H-05 fix). At HEAD no production module imports it; the services divide inline, so the alignment is enforced by resolveRanges, not by the formula. CLAUDE.md Critical Business Rule 2 states the rule in prose.

Not on the CPSC path, but worth knowing

The Budget Tracker reads Meta at ACCOUNT grain (ops.campaign_ad_accounts to ad_spend.meta_insights, fetchAccountGrainSpendUsdByCampaign in lib/ad-spend/repository-reads.ts 354-413) because ad-grain understates Meta spend from never-mapped ads; Google, TikTok and AppLovin stay ad-grain through the matview there.
Targets reads per-ad lifetime spend from ops.ad_spend_per_ad (refreshed by the same :25 job) and lib/campaign-targets/spend-reads.ts notes an $834,499 disagreement with the matview; not traced here.
Unknown: no writer for platform = 'applovin' rows in ops.campaign_ads was found beyond the admin map route; unmapped AppLovin campaigns roll up to no Terminal campaign (job header). The ops.cases DDL migration is not cited; the counting reader is. Terminal-published Meta ads self-register via CampaignAdsRepository.upsertCampaignAdFromMetaAd (not drawn).