Skip to content

SOP-FIN-01: Ad Spend Sync v1.2

Automated daily ingestion of ad spend from Meta and Google Ads into Supabase for CAC reporting, with attribution-ready architecture for real CAC computation.

Document ID: SOP-FIN-01-v1.2 Version: 1.2.1 Status: 🟢 Deployed Last Updated: 2026-03-20 Owner: Protocol Raw Operations Replaces: SOP-04-v4.0 (deprecated — Make.com-based architecture) Review Date: 2026-06-19

Key Changes in v1.2.1

  • ✅ Deployed to production (2026-03-20)
  • ✅ Adapted all ops_events usage to match actual table schema: entity_type (not event_type), kind (not status), meta (not details), plus mandatory entity_id (UUID, generated via gen_random_uuid())
  • ✅ Adapted monitoring_runs usage: result_json (not result), result_summary (not omitted), status values limited to running/success/error (not healthy/alert_triggered)
  • ✅ Monitoring function return format adapted to match run-monitor MonitorResult interface: returns success, run_id, check_name, duration_ms, alert_config (not top-level alert boolean)
  • ✅ Meta account ID updated to act_921829787442603 (was placeholder act_1834567227424666)

Implementation Notes: Schema Adaptations

The raw_ops.ops_events and raw_ops.monitoring_runs tables pre-date this SOP. The following column mappings were applied during implementation:

ops_events column mapping:

SOP spec (original) Actual column Notes
event_type entity_type e.g. 'ad_spend_sync', 'fx_rate_sync'
status kind e.g. 'SUCCESS', 'FAILED', 'WARNING', 'PARTIAL', 'AUTH_FAILED'
details meta JSONB payload with provider, date, counts, etc.
(not in spec) entity_id Required NOT NULL UUID — use gen_random_uuid() for finance events with no natural entity
(not in spec) message Required NOT NULL text — human-readable summary of the event

monitoring_runs column mapping:

SOP spec (original) Actual column Notes
result result_json JSONB with check details
(not in spec) result_summary Text summary for quick scanning
completed_at (not present) Column does not exist; omit
status = 'healthy' status = 'success' CHECK constraint only allows: running, success, error
status = 'alert_triggered' status = 'success' Alert state is communicated via alert_config in the return value, not via status

fn_check_ad_spend_freshness_v2() return format:

Must return run-monitor's MonitorResult interface — not a top-level alert boolean:

{
  "success": true,
  "run_id": "uuid",
  "check_name": "ad_spend_freshness",
  "duration_ms": 37,
  "alert_config": {
    "channel": "ops-alerts",
    "severity": "warning",
    "title": "Ad Spend Sync Health Check",
    "message": "Issues detected for 2026-03-19",
    "fields": [...]
  }
}

When no alert is needed, alert_config is null (not omitted).

Key Changes in v1.2

  • ✅ Fixed FX cross-rate logic: EUR→GBP derived from USD-base response (GBP_per_EUR = GBP_per_USD / EUR_per_USD)
  • ✅ Added raw_ops.ad_accounts config table as deterministic currency source for each provider account
  • ✅ Replaced hand-wavy retry language with explicit "no automatic retry" Phase A design + escalation path
  • ✅ Added Phase 2: CAC Attribution section with v_cac_attributed view spec and SOP-ORD-01 dependency
  • ✅ Simplified FX fallback wording to "import-date rate" and "previous available daily rate (max 1 day stale)"

Key Changes in v1.1

  • ✅ Added GOOGLE_ADS_DEVELOPER_TOKEN and GOOGLE_ADS_LOGIN_CUSTOMER_ID to required secrets
  • ✅ Added "Cron-to-Edge Authentication" section documenting the app.settings.service_role_key contract
  • ✅ Hardened all SECURITY DEFINER functions with SET search_path, REVOKE ALL, explicit GRANT EXECUTE
  • ✅ Added "Business Day Convention" section — all dates are Europe/London, documented throughout
  • ✅ Added fx_rate_is_fallback column to ad_campaigns for FX audit trail
  • ✅ Added Google variance monitoring (was missing in v1.0)
  • ✅ FX freshness monitor now checks for expected currencies explicitly (USD, EUR), not just row count
  • ✅ Split Metabase dashboards: "Ad Spend Sync Health" (ops) and "Ad Spend & CAC" (business)
  • ✅ Renamed Edge Function from fin-ad-spend-sync to fin-ad-spend-provider-sync
  • ✅ Added raw_payload storage note for future migration to run-level ingestion log
  • ✅ All Metabase date references standardised to London business date

Key Changes from SOP-04

  • ✅ Rebuilt from scratch around Supabase-native control flow (pg_cron → pg_net → Edge Function → PostgreSQL function → ops-alerter)
  • ✅ Make.com removed entirely — zero Make scenarios
  • ✅ Credentials moved from Make.com Data Store to Supabase Edge Function secrets
  • ✅ N+1 per-campaign API calls replaced with bulk account-level queries
  • ✅ FX conversion moved into PostgreSQL (computed on UPSERT, recomputable)
  • ✅ Monitoring built in from day one — SOP-MON-01 compliant
  • ✅ ops_events logging is transactional, not a post-launch add-on
  • ✅ Explicit SLOs, failure states, and alerting thresholds defined

Purpose

Import previous-day ad spend from Meta Ads and Google Ads into raw_ops.ad_campaigns daily, with automatic FX conversion to GBP. This data feeds the CAC dashboard in Metabase, which is a core input to the Weekly Growth Review.

Zero manual intervention required at any scale.

Scope

This SOP covers: - Daily FX rate ingestion from ExchangeRate-API - Daily ad spend ingestion from Meta Marketing API (bulk, account-level) - Daily ad spend ingestion from Google Ads API (searchStream, account-level) - FX conversion to GBP within PostgreSQL - Monitoring and alerting for sync health

Does not cover: Dashboard construction (Metabase), ad creative management, budget allocation decisions.

Related SOPs: - SOP-MON-01: Monitoring & Alerting Architecture (master pattern, including run-monitor dispatch and ops-alerter payload contract) - SOP-META-01: Phase A Paid Acquisition Strategy (upstream — what campaigns exist)

Business Day Convention

All date logic in this SOP uses Europe/London as the business timezone.

  • D-1 (yesterday): Always computed as (now() AT TIME ZONE 'Europe/London')::date - 1 in PostgreSQL, and new Date().toLocaleDateString('en-CA', { timeZone: 'Europe/London' }) minus 1 day in Edge Functions.
  • Cron schedules are defined in UTC. During BST (last Sunday in March to last Sunday in October), 08:00 UTC = 09:00 London. During GMT, 08:00 UTC = 08:00 London. The chosen UTC times ensure syncs always run after midnight London time, so D-1 data is complete regardless of DST transition.
  • SLOs in the monitoring section refer to UTC wall-clock times.
  • Metabase queries must use (now() AT TIME ZONE 'Europe/London')::date - 1 for "yesterday", not bare CURRENT_DATE - 1 (which uses database timezone).
  • ops_events.details.date always stores the London business date being imported, not the UTC date of the import run.

Architecture

Data Flow

pg_cron: fin-fx-sync (07:55 UTC)
    ↓
pg_net (async HTTP)
    ↓
Edge Function: fin-fx-sync
    ↓
ExchangeRate-API → raw_ops.currency_rates (UPSERT)
    ↓
ops_events (logged)


pg_cron: fin-ad-spend-meta (08:00 UTC)
    ↓
pg_net (async HTTP)
    ↓
Edge Function: fin-ad-spend-provider-sync
    ↓
Meta Marketing API (bulk account-level insights)
    ↓
raw_ops.fn_import_ad_spend_v1(provider, payload)
    ↓
Validate → Normalize → FX Convert → UPSERT ad_campaigns → Log ops_events
    ↓ (on failure)
ops-alerter → Slack #ops-alerts


pg_cron: fin-ad-spend-google (08:05 UTC)
    ↓
Same pattern as Meta (same Edge Function, different provider param)


pg_cron: monitor-ad-spend-freshness (08:30 UTC)
    ↓
pg_net → run-monitor → fn_check_ad_spend_freshness_v2()
    ↓
monitoring_runs → ops-alerter (if thresholds breached)
    ↓
Slack #ops-alerts / #ops-urgent

Make.com Involvement

None. This is single-system data ingestion (external API → Supabase). There is no multi-system orchestration. The three existing Make.com scenarios (Currency Rate Sync, Meta Ads Daily Sync, Google Ads Daily Sync) should be deactivated after this SOP is deployed and validated.

Credential Management

All credentials stored as Supabase Edge Function secrets (Deno.env.get()). Never in Make.com Data Store, never in code, never in database.

Required Secrets

Secret Name Purpose Rotation
META_ACCESS_TOKEN Meta system user token (long-lived, business-owned) No expiry for system user tokens. Verify annually.
GOOGLE_CLIENT_ID Google OAuth client ID Static
GOOGLE_CLIENT_SECRET Google OAuth client secret Rotate if compromised
GOOGLE_REFRESH_TOKEN Google OAuth refresh token No expiry unless revoked. Re-auth if invalid_grant error.
GOOGLE_ADS_DEVELOPER_TOKEN Required header for all Google Ads API calls Static. Applied for during Google Ads API onboarding.
GOOGLE_ADS_LOGIN_CUSTOMER_ID Manager account ID (if using MCC structure). Omit if direct account access. Static
EXCHANGERATE_API_KEY ExchangeRate-API key Static

Setting Secrets

supabase secrets set META_ACCESS_TOKEN=<value>
supabase secrets set GOOGLE_CLIENT_ID=<value>
supabase secrets set GOOGLE_CLIENT_SECRET=<value>
supabase secrets set GOOGLE_REFRESH_TOKEN=<value>
supabase secrets set GOOGLE_ADS_DEVELOPER_TOKEN=<value>
supabase secrets set GOOGLE_ADS_LOGIN_CUSTOMER_ID=<value>  # only if MCC structure
supabase secrets set EXCHANGERATE_API_KEY=<value>

Meta System User Setup

The Meta access token must be a system user token from Business Manager, not a personal user token. System user tokens do not expire and survive personnel changes.

  1. Go to business.facebook.com → Business Settings → System Users
  2. Create or verify Protocol Raw API system user exists
  3. Assign Assets → Ad Account → act_921829787442603
  4. Generate token with ads_read permission
  5. Store as META_ACCESS_TOKEN secret
  1. Google Ads Developer Token: apply via Google Ads API Center in the Google Ads UI. Required for all API calls (sent as developer-token header).
  2. OAuth credentials: created in console.cloud.google.com → APIs & Services → Credentials.
  3. Login Customer ID: only required if the ad account is managed under a Manager (MCC) account. If so, set GOOGLE_ADS_LOGIN_CUSTOMER_ID and include as login-customer-id header in API calls. If direct account access, omit.

Google OAuth Token Refresh

The Edge Function handles access token refresh automatically using the stored refresh token. If Google returns invalid_grant:

  1. Go to console.cloud.google.com → APIs & Services → Credentials
  2. Use OAuth Playground to re-authorize with the Protocol Raw OAuth client
  3. Generate new refresh token
  4. Update secret: supabase secrets set GOOGLE_REFRESH_TOKEN=<new_value>

Database Schema

Table: raw_ops.ad_campaigns

CREATE TABLE IF NOT EXISTS raw_ops.ad_campaigns (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    platform        text NOT NULL CHECK (platform IN ('meta', 'google')),
    account_id      text NOT NULL,
    campaign_id     text NOT NULL,
    campaign_name   text NOT NULL,
    campaign_status text,              -- 'ACTIVE', 'PAUSED', 'DELETED', etc.
    date            date NOT NULL,
    spend_local     numeric(12,4) NOT NULL DEFAULT 0,
    currency_code   text NOT NULL DEFAULT 'GBP',
    fx_rate_to_gbp  numeric(10,6) NOT NULL DEFAULT 1.0,
    fx_rate_is_fallback boolean NOT NULL DEFAULT false,
    spend_gbp       numeric(12,4) GENERATED ALWAYS AS (spend_local * fx_rate_to_gbp) STORED,
    impressions     integer NOT NULL DEFAULT 0,
    clicks          integer NOT NULL DEFAULT 0,
    conversions     integer NOT NULL DEFAULT 0,
    cost_per_click  numeric(10,4) GENERATED ALWAYS AS (
                      CASE WHEN clicks > 0 THEN (spend_local * fx_rate_to_gbp) / clicks ELSE NULL END
                    ) STORED,
    cost_per_conversion numeric(10,4) GENERATED ALWAYS AS (
                      CASE WHEN conversions > 0 THEN (spend_local * fx_rate_to_gbp) / conversions ELSE NULL END
                    ) STORED,
    raw_payload     jsonb,             -- full API response for debugging (see note below)
    imported_at     timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT uq_ad_campaigns_platform_account_campaign_date
        UNIQUE (platform, account_id, campaign_id, date)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_ad_campaigns_date ON raw_ops.ad_campaigns (date DESC);
CREATE INDEX IF NOT EXISTS idx_ad_campaigns_platform_date ON raw_ops.ad_campaigns (platform, date DESC);
CREATE INDEX IF NOT EXISTS idx_ad_campaigns_imported_at ON raw_ops.ad_campaigns (imported_at DESC);

-- RLS
ALTER TABLE raw_ops.ad_campaigns ENABLE ROW LEVEL SECURITY;
-- Service role bypasses RLS. No anon/authenticated access needed.

COMMENT ON TABLE raw_ops.ad_campaigns IS 'Daily ad spend by campaign, imported from Meta/Google APIs. SOP-FIN-01.';

Design notes: - spend_gbp is a GENERATED column computed from spend_local * fx_rate_to_gbp. If FX rates are corrected, update fx_rate_to_gbp and spend_gbp recomputes automatically. - fx_rate_is_fallback marks rows where the same-day FX rate was unavailable and a D-2 rate was used instead. This enables downstream reporting to flag approximate values. If fresh rates arrive later, re-run the affected date to overwrite with exact rates. - raw_payload stores the full API response per campaign-day for debugging. At Phase A/B volumes this is fine. When campaign count exceeds ~500/day, migrate full payloads to a separate raw_ops.ad_spend_ingestion_log table keyed by run, and keep only a minimal forensic subset per row. - The UNIQUE constraint on (platform, account_id, campaign_id, date) enables idempotent UPSERT. Running the sync twice for the same day is safe.

Table: raw_ops.currency_rates

CREATE TABLE IF NOT EXISTS raw_ops.currency_rates (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    base_currency   text NOT NULL,
    target_currency text NOT NULL DEFAULT 'GBP',
    rate            numeric(10,6) NOT NULL,
    rate_date       date NOT NULL,
    source          text NOT NULL DEFAULT 'exchangerate-api',
    fetched_at      timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT uq_currency_rates_pair_date
        UNIQUE (base_currency, target_currency, rate_date)
);

CREATE INDEX IF NOT EXISTS idx_currency_rates_date ON raw_ops.currency_rates (rate_date DESC);

ALTER TABLE raw_ops.currency_rates ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE raw_ops.currency_rates IS 'Daily FX rates for ad spend conversion. SOP-FIN-01.';

Table: raw_ops.ad_accounts

Static config table. Stores known ad accounts and their currencies. The Edge Function reads currency from here rather than querying the provider API at runtime or guessing.

CREATE TABLE IF NOT EXISTS raw_ops.ad_accounts (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    platform        text NOT NULL CHECK (platform IN ('meta', 'google')),
    account_id      text NOT NULL,
    account_name    text NOT NULL,
    currency_code   text NOT NULL,     -- 'GBP', 'USD', 'EUR'
    is_active       boolean NOT NULL DEFAULT true,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT uq_ad_accounts_platform_account
        UNIQUE (platform, account_id)
);

ALTER TABLE raw_ops.ad_accounts ENABLE ROW LEVEL SECURITY;

-- Seed data for Phase A
INSERT INTO raw_ops.ad_accounts (platform, account_id, account_name, currency_code)
VALUES
    ('meta',   'act_921829787442603', 'Protocol Raw - Meta',   'GBP'),
    ('google', '9345978544',           'Protocol Raw - Google', 'GBP')
ON CONFLICT (platform, account_id) DO NOTHING;

COMMENT ON TABLE raw_ops.ad_accounts IS 'Ad account config: platform, ID, currency. Source of truth for currency in ad spend imports. SOP-FIN-01.';

Usage: The Edge Function queries SELECT currency_code FROM raw_ops.ad_accounts WHERE platform = $1 AND account_id = $2 AND is_active = true before normalizing the campaign payload. If the account is not found, the sync fails with a clear error rather than guessing. 

### View: `analytics.v_cac_daily`

```sql
CREATE OR REPLACE VIEW analytics.v_cac_daily AS
SELECT
    ac.date,
    ac.platform,
    SUM(ac.spend_gbp) AS spend_gbp,
    SUM(ac.impressions) AS impressions,
    SUM(ac.clicks) AS clicks,
    SUM(ac.conversions) AS conversions,
    CASE WHEN SUM(ac.clicks) > 0
        THEN SUM(ac.spend_gbp) / SUM(ac.clicks)
        ELSE NULL
    END AS cpc_gbp,
    CASE WHEN SUM(ac.conversions) > 0
        THEN SUM(ac.spend_gbp) / SUM(ac.conversions)
        ELSE NULL
    END AS cpa_gbp,
    bool_or(ac.fx_rate_is_fallback) AS includes_fallback_fx
FROM raw_ops.ad_campaigns ac
GROUP BY ac.date, ac.platform;

COMMENT ON VIEW analytics.v_cac_daily IS 'Daily ad spend aggregated by platform for CAC dashboard. [SOP-FIN-01](SOP-FIN-01_Ad_Spend_Sync_v1_2 (1).md).';

GRANT SELECT ON analytics.v_cac_daily TO authenticated;

This is a standard view, not materialized. It reads directly from ad_campaigns and is always current. At Phase A/B volumes (tens of campaigns), query time is sub-second. Materialization is unnecessary until hundreds of thousands of rows accumulate.

View: analytics.v_cac_weekly

CREATE OR REPLACE VIEW analytics.v_cac_weekly AS
SELECT
    date_trunc('week', ac.date)::date AS week_starting,
    ac.platform,
    SUM(ac.spend_gbp) AS spend_gbp,
    SUM(ac.impressions) AS impressions,
    SUM(ac.clicks) AS clicks,
    SUM(ac.conversions) AS conversions,
    CASE WHEN SUM(ac.clicks) > 0
        THEN SUM(ac.spend_gbp) / SUM(ac.clicks)
        ELSE NULL
    END AS cpc_gbp,
    CASE WHEN SUM(ac.conversions) > 0
        THEN SUM(ac.spend_gbp) / SUM(ac.conversions)
        ELSE NULL
    END AS cpa_gbp,
    bool_or(ac.fx_rate_is_fallback) AS includes_fallback_fx
FROM raw_ops.ad_campaigns ac
GROUP BY date_trunc('week', ac.date)::date, ac.platform;

GRANT SELECT ON analytics.v_cac_weekly TO authenticated;

PostgreSQL Functions

Function Hardening Standard

All functions in this SOP follow this security pattern:

SECURITY DEFINER
SET search_path = raw_ops, public

After creation, permissions are locked:

REVOKE ALL ON FUNCTION raw_ops.<function_name> FROM PUBLIC;
GRANT EXECUTE ON FUNCTION raw_ops.<function_name> TO service_role;

This ensures functions execute with the owner's privileges, cannot be hijacked via search_path manipulation, and are only callable through the service role (i.e., from Edge Functions, not from anon or authenticated clients).

raw_ops.fn_import_ad_spend_v1()

Core import function. Called by the Edge Function with a normalized payload. Handles validation, FX lookup, UPSERT, and audit logging in a single transaction.

CREATE OR REPLACE FUNCTION raw_ops.fn_import_ad_spend_v1(
    p_provider text,           -- 'meta' or 'google'
    p_account_id text,
    p_date date,
    p_campaigns jsonb          -- array of {campaign_id, campaign_name, campaign_status, spend, currency, impressions, clicks, conversions, raw}
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_campaign jsonb;
    v_fx_rate numeric(10,6);
    v_fx_is_fallback boolean;
    v_currency text;
    v_rows_upserted integer := 0;
    v_rows_skipped integer := 0;
    v_total_spend_gbp numeric(12,4) := 0;
    v_start_time timestamptz := clock_timestamp();
BEGIN
    -- Validate provider
    IF p_provider NOT IN ('meta', 'google') THEN
        RAISE EXCEPTION 'Invalid provider: %. Must be meta or google.', p_provider;
    END IF;

    -- Process each campaign
    FOR v_campaign IN SELECT * FROM jsonb_array_elements(p_campaigns)
    LOOP
        v_currency := UPPER(COALESCE(v_campaign->>'currency', 'GBP'));
        v_fx_is_fallback := false;

        -- Look up FX rate (GBP→GBP = 1.0)
        IF v_currency = 'GBP' THEN
            v_fx_rate := 1.0;
        ELSE
            SELECT rate INTO v_fx_rate
            FROM currency_rates
            WHERE base_currency = v_currency
              AND target_currency = 'GBP'
              AND rate_date = p_date;

            IF v_fx_rate IS NULL THEN
                -- Fallback: try previous day's rate
                SELECT rate INTO v_fx_rate
                FROM currency_rates
                WHERE base_currency = v_currency
                  AND target_currency = 'GBP'
                  AND rate_date = p_date - 1;

                IF v_fx_rate IS NOT NULL THEN
                    v_fx_is_fallback := true;
                END IF;
            END IF;

            IF v_fx_rate IS NULL THEN
                -- No rate available. Log warning, skip this campaign.
                INSERT INTO ops_events (entity_type, entity_id, kind, message, meta)
                VALUES (
                    'ad_spend_sync',
                    gen_random_uuid(),
                    'WARNING',
                    format('No FX rate found for %s on %s or %s. Campaign skipped.', v_currency, p_date, p_date - 1),
                    jsonb_build_object(
                        'provider', p_provider,
                        'campaign_id', v_campaign->>'campaign_id'
                    )
                );
                v_rows_skipped := v_rows_skipped + 1;
                CONTINUE;
            END IF;
        END IF;

        -- UPSERT campaign data
        INSERT INTO ad_campaigns (
            platform, account_id, campaign_id, campaign_name, campaign_status,
            date, spend_local, currency_code, fx_rate_to_gbp, fx_rate_is_fallback,
            impressions, clicks, conversions, raw_payload, imported_at, updated_at
        )
        VALUES (
            p_provider,
            p_account_id,
            v_campaign->>'campaign_id',
            v_campaign->>'campaign_name',
            v_campaign->>'campaign_status',
            p_date,
            COALESCE((v_campaign->>'spend')::numeric, 0),
            v_currency,
            v_fx_rate,
            v_fx_is_fallback,
            COALESCE((v_campaign->>'impressions')::integer, 0),
            COALESCE((v_campaign->>'clicks')::integer, 0),
            COALESCE((v_campaign->>'conversions')::integer, 0),
            v_campaign->'raw',
            now(),
            now()
        )
        ON CONFLICT (platform, account_id, campaign_id, date)
        DO UPDATE SET
            campaign_name = EXCLUDED.campaign_name,
            campaign_status = EXCLUDED.campaign_status,
            spend_local = EXCLUDED.spend_local,
            currency_code = EXCLUDED.currency_code,
            fx_rate_to_gbp = EXCLUDED.fx_rate_to_gbp,
            fx_rate_is_fallback = EXCLUDED.fx_rate_is_fallback,
            impressions = EXCLUDED.impressions,
            clicks = EXCLUDED.clicks,
            conversions = EXCLUDED.conversions,
            raw_payload = EXCLUDED.raw_payload,
            updated_at = now();

        v_rows_upserted := v_rows_upserted + 1;
        v_total_spend_gbp := v_total_spend_gbp + (COALESCE((v_campaign->>'spend')::numeric, 0) * v_fx_rate);
    END LOOP;

    -- Log result to ops_events
    INSERT INTO ops_events (entity_type, entity_id, kind, message, meta)
    VALUES (
        'ad_spend_sync',
        gen_random_uuid(),
        CASE WHEN v_rows_skipped > 0 THEN 'PARTIAL' ELSE 'SUCCESS' END,
        format('Ad spend import: %s upserted, %s skipped for %s on %s', v_rows_upserted, v_rows_skipped, p_provider, p_date),
        jsonb_build_object(
            'provider', p_provider,
            'account_id', p_account_id,
            'date', p_date,
            'campaigns_upserted', v_rows_upserted,
            'campaigns_skipped', v_rows_skipped,
            'total_spend_gbp', round(v_total_spend_gbp, 2),
            'duration_ms', EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000
        )
    );

    RETURN jsonb_build_object(
        'status', CASE WHEN v_rows_skipped > 0 THEN 'partial' ELSE 'success' END,
        'provider', p_provider,
        'date', p_date,
        'campaigns_upserted', v_rows_upserted,
        'campaigns_skipped', v_rows_skipped,
        'total_spend_gbp', round(v_total_spend_gbp, 2)
    );
END;
$$;

REVOKE ALL ON FUNCTION raw_ops.fn_import_ad_spend_v1(text, text, date, jsonb) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION raw_ops.fn_import_ad_spend_v1(text, text, date, jsonb) TO service_role;

COMMENT ON FUNCTION raw_ops.fn_import_ad_spend_v1 IS 'Bulk UPSERT ad spend with FX conversion. [SOP-FIN-01](SOP-FIN-01_Ad_Spend_Sync_v1_2 (1).md).';

FX Fallback Policy

The fallback logic above implements the following policy:

  1. Import-date rate preferred. If currency_rates has a rate for the date being imported, use it. fx_rate_is_fallback = false.
  2. Previous available daily rate as fallback (max 1 day stale). If the import-date rate is missing (e.g., FX sync failed), use the previous day's rate. fx_rate_is_fallback = true.
  3. No rate available. Campaign row is skipped entirely and logged as WARNING. It will be picked up on the next successful sync re-run for that date.
  4. Correction policy. If fallback rows exist and fresh rates later become available, re-run the affected date via manual trigger (see Retry Policy). The UPSERT will overwrite with the correct rate and clear the fallback flag.
  5. Maximum acceptable stale rate: 1 day. If rates are missing for 2+ consecutive days, the monitoring function will alert and campaigns will be skipped until rates are restored.
  6. Metabase visibility. The includes_fallback_fx flag on v_cac_daily allows dashboards to show a warning indicator when any row in a day's aggregation used a fallback rate.

raw_ops.fn_import_fx_rates_v1()

CREATE OR REPLACE FUNCTION raw_ops.fn_import_fx_rates_v1(
    p_rates jsonb,    -- array of {base_currency, rate, rate_date}
    p_source text DEFAULT 'exchangerate-api'
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_rate jsonb;
    v_count integer := 0;
BEGIN
    FOR v_rate IN SELECT * FROM jsonb_array_elements(p_rates)
    LOOP
        INSERT INTO currency_rates (base_currency, target_currency, rate, rate_date, source)
        VALUES (
            UPPER(v_rate->>'base_currency'),
            'GBP',
            (v_rate->>'rate')::numeric,
            (v_rate->>'rate_date')::date,
            p_source
        )
        ON CONFLICT (base_currency, target_currency, rate_date)
        DO UPDATE SET
            rate = EXCLUDED.rate,
            fetched_at = now();

        v_count := v_count + 1;
    END LOOP;

    INSERT INTO ops_events (entity_type, entity_id, kind, message, meta)
    VALUES ('fx_rate_sync', gen_random_uuid(), 'SUCCESS', 'FX rates imported', jsonb_build_object('rates_upserted', v_count, 'source', p_source));

    RETURN jsonb_build_object('status', 'success', 'rates_upserted', v_count);
END;
$$;

REVOKE ALL ON FUNCTION raw_ops.fn_import_fx_rates_v1(jsonb, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION raw_ops.fn_import_fx_rates_v1(jsonb, text) TO service_role;

COMMENT ON FUNCTION raw_ops.fn_import_fx_rates_v1 IS 'Bulk UPSERT FX rates. [SOP-FIN-01](SOP-FIN-01_Ad_Spend_Sync_v1_2 (1).md).';

Edge Functions

1. fin-fx-sync

Purpose: Fetch daily FX rates from ExchangeRate-API and store in database.

Endpoint: POST /functions/v1/fin-fx-sync

Authentication: Validates Authorization: Bearer <service_role_key> header. See "Cron-to-Edge Authentication" in Security section.

Logic: 1. Read EXCHANGERATE_API_KEY from environment 2. Compute target date as D-1 in Europe/London 3. Fetch GET https://v6.exchangerate-api.com/v6/{key}/latest/USD 4. The response contains USD-base rates. Extract: - USD→GBP directly from conversion_rates.GBP - USD→EUR directly from conversion_rates.EUR - Derive EUR→GBP as: GBP_per_EUR = GBP_per_USD / EUR_per_USD 5. Build rates array with both {base_currency: 'USD', rate: GBP_per_USD} and {base_currency: 'EUR', rate: GBP_per_EUR} 6. Call raw_ops.fn_import_fx_rates_v1() with rates for the target date 7. Return result

Cross-rate example: If API returns GBP = 0.7900 and EUR = 0.9200 (both per 1 USD), then EUR→GBP = 0.7900 / 0.9200 = 0.8587. This means 1 EUR = 0.8587 GBP.

Error handling: - HTTP 4xx/5xx from ExchangeRate-API: log error to ops_events with type fx_rate_sync and status FAILED, return error response - Missing expected rates in response (USD or EUR absent): log PARTIAL, return partial

Retry: No automatic retry. See Retry Policy section above.

2. fin-ad-spend-provider-sync

Purpose: Fetch previous-day ad spend from a single provider and import into database. Called once per provider via separate pg_cron jobs with different provider parameters.

Endpoint: POST /functions/v1/fin-ad-spend-provider-sync

Authentication: Validates Authorization: Bearer <service_role_key> header. See "Cron-to-Edge Authentication" in Security section.

Request body: 

{
    "provider": "meta",
    "date": "2026-03-18"
}

date is optional; defaults to D-1 in Europe/London.

Normalized campaign payload contract (output of provider-specific parsing, input to fn_import_ad_spend_v1): 

{
    "campaign_id": "123456",
    "campaign_name": "Protocol Raw - Reconsiderer - London",
    "campaign_status": "ACTIVE",
    "spend": "42.50",
    "currency": "GBP",
    "impressions": "12500",
    "clicks": "340",
    "conversions": "8",
    "raw": { ... }
}

All fields are strings (provider APIs return strings). The PostgreSQL function handles type casting. raw contains the original provider response object for this campaign, stored in raw_payload for debugging.

Meta flow: 1. Read META_ACCESS_TOKEN from environment 2. Compute target date (D-1 London) 3. Fetch account-level insights: 

GET https://graph.facebook.com/v21.0/act_921829787442603/insights
  ?level=campaign
  &fields=campaign_id,campaign_name,spend,impressions,clicks,actions
  &time_range={"since":"2026-03-18","until":"2026-03-18"}
  &limit=500
This returns ALL campaigns in a single response (paginated if >500). No per-campaign iteration needed. 4. Handle pagination: follow paging.next URL if present, accumulate all pages 5. Normalize each campaign into the payload contract above 6. Extract conversions from actions array where action_type = 'offsite_conversion.fb_pixel_purchase'. If no matching action, conversions = 0. 7. Currency: looked up from raw_ops.ad_accounts for this account ID. Not parsed from API response. 8. Call raw_ops.fn_import_ad_spend_v1('meta', 'act_921829787442603', date, campaigns_array) 9. If function returns status: 'partial' or import throws, call ops-alerter with severity warning to #ops-alerts

Google flow: 1. Read GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET, GOOGLE_REFRESH_TOKEN, GOOGLE_ADS_DEVELOPER_TOKEN from environment 2. Optionally read GOOGLE_ADS_LOGIN_CUSTOMER_ID (only if MCC structure) 3. Compute target date (D-1 London) 4. Exchange refresh token for access token: 

POST https://oauth2.googleapis.com/token
Body: grant_type=refresh_token&client_id=...&client_secret=...&refresh_token=...
5. If invalid_grant error: log to ops_events with status AUTH_FAILED, alert via ops-alerter to #ops-urgent, return error. Do not retry auth failures. 6. Fetch campaign metrics: 
POST https://googleads.googleapis.com/v18/customers/9345978544/googleAds:searchStream
Headers:
  Authorization: Bearer {access_token}
  developer-token: {GOOGLE_ADS_DEVELOPER_TOKEN}
  login-customer-id: {GOOGLE_ADS_LOGIN_CUSTOMER_ID}  (only if MCC)
Body: {
    "query": "SELECT campaign.id, campaign.name, campaign.status,
              metrics.cost_micros, metrics.impressions, metrics.clicks,
              metrics.conversions, segments.date
              FROM campaign
              WHERE segments.date = '2026-03-18'"
}
This returns ALL campaigns in a single streamed response. 7. Normalize: cost_micros / 1_000_000 → spend. Currency looked up from raw_ops.ad_accounts for this account (not guessed from API response). If account not found in ad_accounts, fail with clear error. 8. Call raw_ops.fn_import_ad_spend_v1('google', '9345978544', date, campaigns_array) 9. Same alerting logic as Meta

Error handling (both providers):

Error Action
HTTP 401 (auth) Log AUTH_FAILED to ops_events, alert #ops-urgent, do not retry
HTTP 429 (rate limit) Log FAILED to ops_events with error detail, alert #ops-alerts. Manual rerun required (see Retry Policy).
HTTP 5xx (server) Log FAILED to ops_events with error detail, alert #ops-alerts. Manual rerun required (see Retry Policy).
Empty response (200 OK, 0 campaigns) Log SUCCESS with campaigns_upserted: 0. This is normal for days with no active campaigns.
Partial response (some campaigns parsed, some malformed) Process valid campaigns, skip malformed, log PARTIAL, alert #ops-alerts

Key distinction: An API returning 200 OK with an empty data array is different from an API returning an error. The Edge Function must distinguish these and log them differently. Zero spend days are expected and should not trigger alerts. A malformed individual campaign row does not abort the batch — it is skipped and logged.

Retry Policy

Phase A design: no automatic retry. Alert + manual rerun.

At Phase A volumes (2-5 campaigns, once daily), a failed sync is a low-frequency event. The monitoring function at 08:30 catches any missing data and alerts Slack. The ops team manually reruns the affected sync using the runbook instructions.

This is a deliberate design choice, not an omission. Building a retry table, replay Edge Function, and recovery cron job for a once-daily job that fails maybe once a month is over-engineering at this stage.

Manual rerun procedure: 

-- Rerun FX sync
SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/fin-fx-sync',
    headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
    ),
    body := '{}'::jsonb
);

-- Rerun ad spend for a specific provider and date
SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/fin-ad-spend-provider-sync',
    headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
    ),
    body := '{"provider": "meta", "date": "2026-03-18"}'::jsonb
);

Escalation trigger: If sync failures exceed 2 per week sustained over a rolling 2-week window, add a dedicated fin-ad-spend-retry pg_cron job at 09:00 UTC that queries ops_events for FAILED syncs in the last 2 hours and replays them. The monitoring function already tracks rolling 7-day failure count and will escalate to #ops-urgent at 3+ failures, providing the signal to implement this.

pg_cron Jobs

FX Rate Sync

SELECT cron.schedule(
    'fin-fx-sync',
    '55 7 * * *',  -- 07:55 UTC daily (before ad spend sync)
    $$
    SELECT net.http_post(
        url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/fin-fx-sync',
        headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
        ),
        body := '{}'::jsonb
    );
    $$
);

Meta Ad Spend Sync

SELECT cron.schedule(
    'fin-ad-spend-meta',
    '0 8 * * *',  -- 08:00 UTC daily
    $$
    SELECT net.http_post(
        url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/fin-ad-spend-provider-sync',
        headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
        ),
        body := '{"provider": "meta"}'::jsonb
    );
    $$
);

SELECT cron.schedule(
    'fin-ad-spend-google',
    '5 8 * * *',  -- 08:05 UTC daily
    $$
    SELECT net.http_post(
        url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/fin-ad-spend-provider-sync',
        headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
        ),
        body := '{"provider": "google"}'::jsonb
    );
    $$
);

Ad Spend Freshness Monitor

SELECT cron.schedule(
    'monitor-ad-spend-freshness',
    '30 8 * * *',  -- 08:30 UTC daily (after both syncs should have completed)
    $$
    SELECT net.http_post(
        url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/run-monitor',
        headers := jsonb_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
        ),
        body := '{"check": "ad_spend_freshness"}'::jsonb
    );
    $$
);

DST note: These UTC times are chosen so that even during BST (UTC+1), all syncs complete well after midnight London time. Provider D-1 data finalisation is typically complete by 04:00-06:00 local time, so 07:55-08:05 UTC provides ample margin in both GMT and BST.

Job Management

-- View all FIN jobs
SELECT jobid, jobname, schedule, active
FROM cron.job
WHERE jobname LIKE 'fin-%' OR jobname = 'monitor-ad-spend-freshness'
ORDER BY jobname;

-- Disable a job
UPDATE cron.job SET active = false WHERE jobname = 'fin-ad-spend-google';

-- Enable a job
UPDATE cron.job SET active = true WHERE jobname = 'fin-ad-spend-google';

Monitoring

SLO Definition

Metric Target Severity
FX rates for D-1 ingested (USD and EUR) By 08:00 UTC warning
Meta spend for D-1 ingested By 08:15 UTC (if Meta sync job active) warning
Google spend for D-1 ingested By 08:20 UTC (if Google sync job active) warning
Zero failed provider syncs (rolling 7 days) 0 failures warning at 1, critical at 3
Row count variance vs 7-day mean (per platform) Within 2σ warning

Monitoring Function: raw_ops.fn_check_ad_spend_freshness_v2()

Follows SOP-MON-01 pattern exactly. Dispatched by run-monitor Edge Function when called with {"check": "ad_spend_freshness"}. Alert payload follows the ops-alerter contract defined in SOP-MON-01.

CREATE OR REPLACE FUNCTION raw_ops.fn_check_ad_spend_freshness_v2()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_run_id uuid;
    v_start_time timestamptz;
    v_duration_ms integer;
    v_alert_needed boolean := false;
    v_alert_severity text := 'warning';
    v_alert_fields jsonb := '[]'::jsonb;
    v_yesterday date := (now() AT TIME ZONE 'Europe/London')::date - 1;
    v_meta_count integer;
    v_google_count integer;
    v_fx_usd_exists boolean;
    v_fx_eur_exists boolean;
    v_failed_7d integer;
    v_meta_7d_avg numeric;
    v_meta_7d_stddev numeric;
    v_google_7d_avg numeric;
    v_google_7d_stddev numeric;
BEGIN
    v_start_time := clock_timestamp();

    -- Create monitoring run record
    INSERT INTO monitoring_runs (check_name, status)
    VALUES ('ad_spend_freshness', 'running')
    RETURNING id INTO v_run_id;

    -- ── FX Rate Checks ──
    -- Check for specific expected currencies, not just row count
    SELECT EXISTS (
        SELECT 1 FROM currency_rates
        WHERE base_currency = 'USD' AND target_currency = 'GBP' AND rate_date = v_yesterday
    ) INTO v_fx_usd_exists;

    SELECT EXISTS (
        SELECT 1 FROM currency_rates
        WHERE base_currency = 'EUR' AND target_currency = 'GBP' AND rate_date = v_yesterday
    ) INTO v_fx_eur_exists;

    IF NOT v_fx_usd_exists OR NOT v_fx_eur_exists THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object(
                'label', 'FX Rates',
                'value', format('USD: %s, EUR: %s for %s',
                    CASE WHEN v_fx_usd_exists THEN 'OK' ELSE 'MISSING' END,
                    CASE WHEN v_fx_eur_exists THEN 'OK' ELSE 'MISSING' END,
                    v_yesterday),
                'inline', true
            )
        );
    END IF;

    -- ── Meta Sync Check ──
    SELECT COUNT(*) INTO v_meta_count
    FROM ad_campaigns
    WHERE platform = 'meta' AND date = v_yesterday;

    IF NOT EXISTS (
        SELECT 1 FROM ops_events
        WHERE entity_type = 'ad_spend_sync'
          AND meta->>'provider' = 'meta'
          AND (meta->>'date')::date = v_yesterday
          AND kind IN ('SUCCESS', 'PARTIAL')
          AND created_at > now() - interval '24 hours'
    ) THEN
        -- Only alert if Meta sync job is active
        IF EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'fin-ad-spend-meta' AND active = true) THEN
            v_alert_needed := true;
            v_alert_fields := v_alert_fields || jsonb_build_array(
                jsonb_build_object('label', 'Meta Sync', 'value', 'NO SUCCESS event for ' || v_yesterday, 'inline', true)
            );
        END IF;
    END IF;

    -- ── Google Sync Check ──
    SELECT COUNT(*) INTO v_google_count
    FROM ad_campaigns
    WHERE platform = 'google' AND date = v_yesterday;

    IF NOT EXISTS (
        SELECT 1 FROM ops_events
        WHERE entity_type = 'ad_spend_sync'
          AND meta->>'provider' = 'google'
          AND (meta->>'date')::date = v_yesterday
          AND kind IN ('SUCCESS', 'PARTIAL')
          AND created_at > now() - interval '24 hours'
    ) THEN
        IF EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'fin-ad-spend-google' AND active = true) THEN
            v_alert_needed := true;
            v_alert_fields := v_alert_fields || jsonb_build_array(
                jsonb_build_object('label', 'Google Sync', 'value', 'NO SUCCESS event for ' || v_yesterday, 'inline', true)
            );
        END IF;
    END IF;

    -- ── Rolling 7-Day Failure Count ──
    SELECT COUNT(*) INTO v_failed_7d
    FROM ops_events
    WHERE entity_type IN ('ad_spend_sync', 'fx_rate_sync')
      AND kind = 'FAILED'
      AND created_at > now() - interval '7 days';

    IF v_failed_7d >= 3 THEN
        v_alert_needed := true;
        v_alert_severity := 'critical';
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Failed Syncs (7d)', 'value', v_failed_7d::text, 'inline', true)
        );
    ELSIF v_failed_7d >= 1 THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Failed Syncs (7d)', 'value', v_failed_7d::text, 'inline', true)
        );
    END IF;

    -- ── Meta Row Count Variance (7-day rolling) ──
    SELECT AVG(cnt), STDDEV(cnt) INTO v_meta_7d_avg, v_meta_7d_stddev
    FROM (
        SELECT date, COUNT(*) AS cnt
        FROM ad_campaigns
        WHERE platform = 'meta' AND date >= v_yesterday - 7 AND date < v_yesterday
        GROUP BY date
    ) sub;

    IF v_meta_7d_avg IS NOT NULL AND v_meta_7d_stddev IS NOT NULL AND v_meta_7d_stddev > 0 THEN
        IF ABS(v_meta_count - v_meta_7d_avg) > 2 * v_meta_7d_stddev THEN
            v_alert_needed := true;
            v_alert_fields := v_alert_fields || jsonb_build_array(
                jsonb_build_object('label', 'Meta Variance',
                    'value', format('Today: %s, 7d avg: %s (+/-%s)', v_meta_count, round(v_meta_7d_avg,1), round(v_meta_7d_stddev,1)),
                    'inline', false)
            );
        END IF;
    END IF;

    -- ── Google Row Count Variance (7-day rolling) ──
    SELECT AVG(cnt), STDDEV(cnt) INTO v_google_7d_avg, v_google_7d_stddev
    FROM (
        SELECT date, COUNT(*) AS cnt
        FROM ad_campaigns
        WHERE platform = 'google' AND date >= v_yesterday - 7 AND date < v_yesterday
        GROUP BY date
    ) sub;

    IF v_google_7d_avg IS NOT NULL AND v_google_7d_stddev IS NOT NULL AND v_google_7d_stddev > 0 THEN
        IF ABS(v_google_count - v_google_7d_avg) > 2 * v_google_7d_stddev THEN
            v_alert_needed := true;
            v_alert_fields := v_alert_fields || jsonb_build_array(
                jsonb_build_object('label', 'Google Variance',
                    'value', format('Today: %s, 7d avg: %s (+/-%s)', v_google_count, round(v_google_7d_avg,1), round(v_google_7d_stddev,1)),
                    'inline', false)
            );
        END IF;
    END IF;

    v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

    -- Update monitoring run
    UPDATE monitoring_runs
    SET
        status = 'success',
        result_json = jsonb_build_object(
            'yesterday', v_yesterday,
            'fx_usd', v_fx_usd_exists,
            'fx_eur', v_fx_eur_exists,
            'meta_campaigns', v_meta_count,
            'google_campaigns', v_google_count,
            'failed_7d', v_failed_7d,
            'alert_needed', v_alert_needed
        ),
        result_summary = format('FX: USD=%s EUR=%s | Meta: %s | Google: %s | Failed 7d: %s',
            CASE WHEN v_fx_usd_exists THEN 'OK' ELSE 'MISS' END,
            CASE WHEN v_fx_eur_exists THEN 'OK' ELSE 'MISS' END,
            v_meta_count, v_google_count, v_failed_7d),
        duration_ms = v_duration_ms
    WHERE id = v_run_id;

    -- Return in run-monitor MonitorResult format
    RETURN jsonb_build_object(
        'success', true,
        'run_id', v_run_id,
        'check_name', 'ad_spend_freshness',
        'duration_ms', v_duration_ms,
        'alert_config', CASE
            WHEN NOT v_alert_needed THEN NULL
            ELSE jsonb_build_object(
                'channel', CASE WHEN v_alert_severity = 'critical' THEN 'ops-urgent' ELSE 'ops-alerts' END,
                'severity', v_alert_severity,
                'title', 'Ad Spend Sync Health Check',
                'message', format('Issues detected for %s', v_yesterday),
                'fields', v_alert_fields
            )
        END
    );

EXCEPTION WHEN OTHERS THEN
    v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;
    UPDATE monitoring_runs
    SET status = 'error', duration_ms = v_duration_ms, error_message = SQLERRM
    WHERE id = v_run_id;
    RETURN jsonb_build_object(
        'success', false,
        'run_id', v_run_id,
        'check_name', 'ad_spend_freshness',
        'error', SQLERRM,
        'duration_ms', v_duration_ms
    );
END;
$$;

REVOKE ALL ON FUNCTION raw_ops.fn_check_ad_spend_freshness_v2() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION raw_ops.fn_check_ad_spend_freshness_v2() TO service_role;

COMMENT ON FUNCTION raw_ops.fn_check_ad_spend_freshness_v2 IS '[SOP-MON-01](../monitoring/SOP_MON_01_Monitoring_Alerting_Architecture_v1_0.md) compliant ad spend freshness monitor. [SOP-FIN-01](SOP-FIN-01_Ad_Spend_Sync_v1_2 (1).md).';

Metabase Dashboards

Dashboard 1: "Ad Spend Sync Health" (Operations)

Monitors the health of the sync pipeline itself. Reviewed when alerts fire or during ops standup.

Tile Query Source
Last Successful Sync by Provider SELECT meta->>'provider' AS provider, MAX(created_at) AS last_sync FROM raw_ops.ops_events WHERE entity_type = 'ad_spend_sync' AND kind IN ('SUCCESS','PARTIAL') GROUP BY meta->>'provider'
Degraded Syncs (PARTIAL) last 7d SELECT created_at, meta FROM raw_ops.ops_events WHERE entity_type = 'ad_spend_sync' AND kind = 'PARTIAL' AND created_at > now() - interval '7 days' ORDER BY created_at DESC
Failed Syncs last 7d SELECT created_at, meta FROM raw_ops.ops_events WHERE entity_type IN ('ad_spend_sync','fx_rate_sync') AND kind = 'FAILED' AND created_at > now() - interval '7 days' ORDER BY created_at DESC
FX Rate Freshness SELECT base_currency, rate, rate_date FROM raw_ops.currency_rates WHERE rate_date = (now() AT TIME ZONE 'Europe/London')::date - 1 ORDER BY base_currency
Fallback FX Rows (last 7d) SELECT date, platform, COUNT(*) FROM raw_ops.ad_campaigns WHERE fx_rate_is_fallback = true AND date > (now() AT TIME ZONE 'Europe/London')::date - 8 GROUP BY date, platform ORDER BY date DESC
Campaign Count by Platform (yesterday) SELECT platform, COUNT(DISTINCT campaign_id) FROM raw_ops.ad_campaigns WHERE date = (now() AT TIME ZONE 'Europe/London')::date - 1 GROUP BY platform

Dashboard 2: "Ad Spend & CAC" (Business)

Business metrics for the Weekly Growth Review. Read by the founder and eventually the marketing lead.

Tile Query Source
Yesterday's Total Spend (GBP) SELECT SUM(spend_gbp) FROM raw_ops.ad_campaigns WHERE date = (now() AT TIME ZONE 'Europe/London')::date - 1
Spend by Platform (last 30d) analytics.v_cac_daily filtered last 30 days, grouped by platform. Line chart.
Daily CPA Trend (last 30d) analytics.v_cac_daily line chart, cpa_gbp by date, split by platform
Weekly Spend & CPA analytics.v_cac_weekly bar (spend) + line (CPA) chart
Blended CAC vs Target analytics.v_cac_daily aggregated across platforms, compared to Phase A target (£80-100)
Data Quality Indicator SELECT bool_or(includes_fallback_fx) FROM analytics.v_cac_daily WHERE date = (now() AT TIME ZONE 'Europe/London')::date - 1 — show warning badge if true

Operational Runbook

Daily Schedule (Automated)

Time (UTC) London (GMT) London (BST) Job What Happens
07:55 07:55 08:55 fin-fx-sync Fetch USD→GBP, EUR→GBP rates for D-1
08:00 08:00 09:00 fin-ad-spend-meta Fetch Meta D-1 spend (bulk)
08:05 08:05 09:05 fin-ad-spend-google Fetch Google D-1 spend (bulk)
08:30 08:30 09:30 monitor-ad-spend-freshness Check all data arrived, alert if not

Troubleshooting

Scenario: Meta API returns 401 - Cause: System user token revoked or permissions changed - Check: SELECT * FROM raw_ops.ops_events WHERE entity_type = 'ad_spend_sync' AND meta->>'provider' = 'meta' ORDER BY created_at DESC LIMIT 5; - Fix: Regenerate system user token in Business Manager, update secret: supabase secrets set META_ACCESS_TOKEN=<new_value>

Scenario: Google API returns invalid_grant - Cause: Refresh token revoked (user changed password, app access revoked) - Fix: Re-authorize via OAuth Playground, update secret: supabase secrets set GOOGLE_REFRESH_TOKEN=<new_value>

Scenario: Google API returns 403 with "developer token not approved" - Cause: Developer token not yet approved by Google, or wrong token used - Check: Verify GOOGLE_ADS_DEVELOPER_TOKEN secret matches the approved token in the Google Ads API Center - Fix: If pending approval, wait for Google. If wrong token, update secret.

Scenario: Data present but spend_gbp is 0 when spend_local is not - Cause: FX rate missing for that currency/date, or fx_rate_to_gbp is 0 - Check: SELECT * FROM raw_ops.currency_rates WHERE rate_date = '2026-03-18'; - Fix: Manually trigger FX sync, then re-run the ad spend sync for that date to overwrite with correct rates

Scenario: Rows show fx_rate_is_fallback = true - Cause: Import-date FX rate was unavailable; previous day's rate used instead - Action: Check if FX sync failed that day. Once fresh rates are available, re-run the affected date (see Retry Policy). The UPSERT will overwrite with correct rate and clear the fallback flag.

Scenario: Monitoring alert but data is actually present - Cause: Sync completed after the 08:30 monitor ran - Check: SELECT * FROM raw_ops.ops_events WHERE entity_type = 'ad_spend_sync' AND created_at > now() - interval '2 hours' ORDER BY created_at DESC; - Action: If data arrived late, no action needed. If this happens repeatedly, investigate API latency or adjust monitor schedule.

Scenario: Campaign count drops to 0 unexpectedly - Causes: All campaigns paused (legitimate), API auth issue returning empty (check ops_events status — SUCCESS with 0 campaigns vs FAILED) - Check: Verify in Meta/Google dashboards that campaigns are actually paused

Launch Activation Checklist

Phase 1: FX Sync and Config (deploy first)

  • [ ] Create raw_ops.currency_rates table
  • [ ] Create raw_ops.ad_accounts table and seed Phase A accounts
  • [ ] Create raw_ops.fn_import_fx_rates_v1() function (with REVOKE/GRANT)
  • [ ] Deploy fin-fx-sync Edge Function
  • [ ] Set EXCHANGERATE_API_KEY secret
  • [ ] Create fin-fx-sync pg_cron job
  • [ ] Manually trigger and verify rates appear in database (check both USD→GBP and EUR→GBP derived rates)
  • [ ] Verify ops_events log entry with type fx_rate_sync

Phase 2: Meta Sync

  • [ ] Create raw_ops.ad_campaigns table (DROP existing from SOP-04 if schema differs)
  • [ ] Create raw_ops.fn_import_ad_spend_v1() function (with REVOKE/GRANT)
  • [ ] Deploy fin-ad-spend-provider-sync Edge Function
  • [ ] Set META_ACCESS_TOKEN secret
  • [ ] Create fin-ad-spend-meta pg_cron job (initially disabled)
  • [ ] Manually trigger with {"provider": "meta", "date": "YYYY-MM-DD"} for a known spend day
  • [ ] Verify data in ad_campaigns: spend_gbp computed correctly, fx_rate_is_fallback = false, ops_events logged
  • [ ] Enable pg_cron job

Phase 3: Google Sync (when first Google campaign launches)

  • [ ] Verify Google Ads account has billing configured
  • [ ] Verify Developer Token is approved in Google Ads API Center
  • [ ] Set secrets: GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET, GOOGLE_REFRESH_TOKEN, GOOGLE_ADS_DEVELOPER_TOKEN
  • [ ] Set GOOGLE_ADS_LOGIN_CUSTOMER_ID if MCC structure applies
  • [ ] Create fin-ad-spend-google pg_cron job (initially disabled)
  • [ ] Manually trigger with {"provider": "google", "date": "YYYY-MM-DD"}
  • [ ] Verify data in ad_campaigns
  • [ ] Enable pg_cron job

Phase 4: Monitoring & Dashboards

  • [ ] Create raw_ops.fn_check_ad_spend_freshness_v2() function (with REVOKE/GRANT)
  • [ ] Add ad_spend_freshness case to run-monitor Edge Function
  • [ ] Create monitor-ad-spend-freshness pg_cron job
  • [ ] Create analytics.v_cac_daily and analytics.v_cac_weekly views
  • [ ] Build Metabase "Ad Spend Sync Health" dashboard (ops)
  • [ ] Build Metabase "Ad Spend & CAC" dashboard (business)
  • [ ] Test alert by temporarily disabling the Meta sync job and waiting for monitor

Phase 5: Cleanup

  • [ ] Deactivate Make.com scenario: "SOP 04: Currency Rate Sync"
  • [ ] Deactivate Make.com scenario: "SOP 04: Meta Ads Daily Sync"
  • [ ] Deactivate Make.com scenario: "SOP 04: Google Ads Daily Sync"
  • [ ] Deactivate Make.com scenario: "Ad Spend CSV Import"
  • [ ] Update Technical Operations Handbook to reflect SOP-FIN-01 replacing SOP-04

Phase 2: CAC Attribution (Post-Launch)

Status: Blocked on SOP-ORD-01 v5.1

This SOP currently delivers spend visibility — how much was spent on each platform per day. It does not yet deliver actual CAC because that requires joining spend data against attributed customer acquisitions.

What's Missing

The raw_ops.orders table does not currently extract UTM parameters into queryable columns. Shopify captures UTMs via landing_site and referring_site fields in the order webhook payload, but these are buried in webhook_inbox.raw_payload and not parsed into first-class columns.

Dependency: SOP-ORD-01 v5.1

The order ingestion pipeline (SOP-ORD-01) needs a v5.1 update to:

  1. Parse UTM params from Shopify webhook landing_site URL during order processing
  2. Store as columns on raw_ops.orders:
ALTER TABLE raw_ops.orders ADD COLUMN IF NOT EXISTS
    utm_source      text,    -- 'meta', 'google', 'organic', 'referral', etc.
    utm_medium      text,    -- 'paid', 'cpc', 'email', etc.
    utm_campaign    text,    -- campaign name
    utm_content     text;    -- creative/variant ID

CREATE INDEX IF NOT EXISTS idx_orders_utm_source ON raw_ops.orders (utm_source) WHERE utm_source IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_orders_created_date_utm ON raw_ops.orders (((created_at AT TIME ZONE 'Europe/London')::date), utm_source);

This belongs in SOP-ORD-01 because UTM data on orders benefits more than just CAC: referral attribution, lifecycle segmentation, cohort analysis by acquisition channel, and Box-2 retention by source.

Target View: analytics.v_cac_attributed

Once UTM columns exist on orders, create this view:

CREATE OR REPLACE VIEW analytics.v_cac_attributed AS
WITH daily_spend AS (
    SELECT
        date,
        platform,
        SUM(spend_gbp) AS spend_gbp
    FROM raw_ops.ad_campaigns
    GROUP BY date, platform
),
daily_acquisitions AS (
    SELECT
        (o.created_at AT TIME ZONE 'Europe/London')::date AS date,
        CASE
            WHEN o.utm_source = 'meta' THEN 'meta'
            WHEN o.utm_source = 'google' THEN 'google'
            WHEN o.utm_source = 'referral' THEN 'referral'
            ELSE 'organic'
        END AS channel,
        COUNT(DISTINCT o.customer_id) FILTER (
            WHERE o.id = (
                SELECT MIN(o2.id) FROM raw_ops.orders o2
                WHERE o2.customer_id = o.customer_id
            )
        ) AS new_customers
    FROM raw_ops.orders o
    WHERE o.status = 'PAID'
    GROUP BY 1, 2
)
SELECT
    COALESCE(s.date, a.date) AS date,
    COALESCE(s.platform, a.channel) AS channel,
    COALESCE(s.spend_gbp, 0) AS spend_gbp,
    COALESCE(a.new_customers, 0) AS new_customers,
    CASE WHEN COALESCE(a.new_customers, 0) > 0
        THEN COALESCE(s.spend_gbp, 0) / a.new_customers
        ELSE NULL
    END AS cac_gbp
FROM daily_spend s
FULL OUTER JOIN daily_acquisitions a
    ON s.date = a.date AND s.platform = a.channel;

COMMENT ON VIEW analytics.v_cac_attributed IS 'Daily CAC by channel: ad spend joined against attributed new customer orders. [SOP-FIN-01](SOP-FIN-01_Ad_Spend_Sync_v1_2 (1).md) Phase 2.';

GRANT SELECT ON analytics.v_cac_attributed TO authenticated;

Note: The new_customers subquery identifies first-time buyers by checking if the order is the customer's earliest order. This avoids counting repeat orders as acquisitions. The logic may need refinement based on how subscription renewals are recorded (they should not count as new customer acquisitions).

Blended CAC View: analytics.v_cac_blended

CREATE OR REPLACE VIEW analytics.v_cac_blended AS
SELECT
    date,
    SUM(spend_gbp) AS total_spend_gbp,
    SUM(new_customers) AS total_new_customers,
    CASE WHEN SUM(new_customers) > 0
        THEN SUM(spend_gbp) / SUM(new_customers)
        ELSE NULL
    END AS blended_cac_gbp
FROM analytics.v_cac_attributed
WHERE channel IN ('meta', 'google')  -- only paid channels in blended CAC
GROUP BY date;

GRANT SELECT ON analytics.v_cac_blended TO authenticated;

Metabase Additions (Phase 2)

Once attribution is live, add to the "Ad Spend & CAC" business dashboard:

Tile Query Source
Blended CAC (rolling 7d) analytics.v_cac_blended — 7-day rolling average
CAC by Channel (last 30d) analytics.v_cac_attributed — line chart, cac_gbp by date per channel
New Customers by Channel (last 30d) analytics.v_cac_attributed — stacked bar, new_customers by channel
CAC vs Target analytics.v_cac_blended — blended CAC line vs £80-100 Phase A target band

Implementation Timeline

  • SOP-ORD-01 v5.1: Add UTM columns to orders. Can be implemented immediately as a standalone change.
  • SOP-FIN-01 Phase 2 views: Create once UTM data is flowing. Views are backward-compatible (they'll return empty results until orders with UTMs exist).
  • Target: Week 1-2 of Phase A. CAC data is critical for the Weekly Growth Review from launch.

Security

Cron-to-Edge Authentication

All pg_cron jobs in this SOP call Edge Functions via pg_net with:

'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')

This is the established project-wide pattern used across SOP-MON-01, SOP-HDI-01, and SOP-ORD-01. The contract:

  • What credential: The Supabase service_role JWT, which provides full database access bypassing RLS.
  • Where stored: app.settings.service_role_key is a Supabase-managed project setting, set automatically by the platform. It is not a user-created database config value.
  • Who can read it: Only database sessions running as postgres (superuser) or via pg_cron execution context. Not readable by anon, authenticated, or service_role SQL sessions through the PostgREST API. Not exposed via any Supabase client.
  • How Edge Functions validate: Each Edge Function reads the Authorization header and verifies it is a valid Supabase JWT. The Supabase Edge Function runtime handles this automatically when the function is invoked with --no-verify-jwt disabled (the default).
  • Why this does not broaden blast radius: The service role key already exists as a Supabase secret accessible to Edge Functions via Deno.env.get('SUPABASE_SERVICE_ROLE_KEY'). Using it in pg_cron→pg_net calls does not create a new secret surface — it re-uses the existing platform credential within the platform boundary.

General Security Posture

  • RLS enabled on ad_campaigns, currency_rates, and ad_accounts. Service role bypasses. No anon/authenticated write access.
  • All functions are SECURITY DEFINER with SET search_path = raw_ops, public — execute with owner privileges, immune to search_path injection.
  • All functions have REVOKE ALL FROM PUBLIC and explicit GRANT EXECUTE TO service_role — not callable from anon or authenticated contexts.
  • No dynamic SQL in any function. All queries use parameterized inputs via PL/pgSQL variables.
  • No credentials in code or database. All secrets in Supabase Edge Function environment variables.

Version History

Version Date Changes Author
1.2 2026-03-19 Fixed FX cross-rate derivation. Added ad_accounts config table. Explicit retry policy (no auto-retry Phase A). Phase 2 CAC Attribution section with v_cac_attributed view spec. Protocol Raw Operations
1.1 2026-03-19 Added Google Ads developer token. Hardened SECURITY DEFINER. London timezone standardised. FX fallback marking. Google variance monitoring. Split dashboards. Edge Function renamed. Cron auth documented. Protocol Raw Operations
1.0 2026-03-19 Complete rewrite from SOP-04. Supabase-native architecture. Make.com removed. Bulk API calls. FX in PostgreSQL. SOP-MON-01 monitoring. Protocol Raw Operations

SOP-FIN-01: Ad Spend Sync v1.2 Replaces: SOP-04 v4.0 Status: 🔵 Ready to Deploy