Skip to content
SYSTEM SPECIFICATION // REV 4.2 · Cloudflare Workers + D1 Edge Ledger

Deterministic click-to-cash reconciliation architecture.

Explore how Skewix joins 1st-party edge click telemetry with 6 ad platform spend ledgers and settled Stripe & HubSpot cash—classifying every dollar of variance and executing cryptographic server-side CAPI write-backs.

Start 14-Day Free Trial Compare Technical Plans

01 // DETERMINISTIC CLICK-TO-CASH JOIN SCHEMA

How Skewix joins browser clicks, ad spend, and settled bank cash.

Instead of relying on probabilistic fingerprinting or siloed pixel cookies, Skewix performs a deterministic 3-way relational join across 1st-party click capture, ad platform campaign ledgers, and verified Stripe & HubSpot transaction records.

LAYER 01 // 1ST-PARTY EDGE CLICK CAPTURE

edge_click_events

/api/collect/script.js (1.4 kB)

Captured on initial landing at the Cloudflare edge and persisted in 1st-party storage + checkout metadata.

click_session_id TEXT PRIMARY KEY
Deterministic edge UUID
gclid TEXT INDEXED
Google Ads & OCI click identifier
fbclid / _fbc / _fbp TEXT INDEXED
Meta Ads click & browser IDs
ttclid TEXT INDEXED
TikTok Ads click identifier
li_fat_id TEXT INDEXED
LinkedIn First-Party Ad Tracking ID
msclkid TEXT INDEXED
Microsoft Advertising click ID
sha256_email CHAR(64)
Web Crypto SHA-256 normalized email
utm_campaign_id TEXT NOT NULL
Normalized platform campaign ID

LAYER 02 // AD PLATFORM CAMPAIGN LEDGERS

ad_platform_campaign_ledgers

6 Paid Media + Analytics APIs

Synchronized daily via OAuth 2.0 or System User Tokens across Meta, Google, GA4, TikTok, LinkedIn, and Microsoft.

ledger_row_id TEXT PRIMARY KEY
Composite tenant + platform + date
platform ENUM(6)
meta | google_ads | ga4 | tiktok | linkedin | microsoft
campaign_id TEXT INDEXED
Foreign key to click & UTM lineage
spend_cents BIGINT NOT NULL
Verified billed ad spend (UTC normalized)
claimed_conversions NUMERIC(12,2)
Platform-reported conversion count
claimed_revenue_cents BIGINT NOT NULL
Platform-claimed gross conversion value
ga4_observed_rev_cents BIGINT
GA4 Data API v1beta session-attributed value
attribution_window TEXT
e.g. 7d_click_1d_view | data_driven

LAYER 03 // SETTLED CASH & CRM LEDGER

revenue_truth_ledger

Stripe Webhooks + HubSpot v3

Bank-grade financial truth ingested from Stripe payment/refund webhooks and HubSpot closed-won pipeline.

transaction_id TEXT PRIMARY KEY
Stripe ch_ / py_ ID or HubSpot hs_object_id
event_type ENUM
charge.succeeded | charge.refunded | closedwon
gross_amount_cents BIGINT NOT NULL
Initial settled checkout or deal value
refunded_disputed_cents BIGINT DEFAULT 0
Real-time refund & dispute deductions
net_settled_cash_cents BIGINT GENERATED
gross_amount_cents - refunded_disputed_cents
captured_click_keys JSONB
{ gclid, fbclid, ttclid, li_fat_id, msclkid }
sha256_email CHAR(64) INDEXED
Deterministic identity fallback join key
capi_recovery_state TEXT
CREDITED | UNCREDITED_RECOVERABLE | BACKFILLED
DETERMINISTIC 3-WAY TIE-OUT QUERY SPECIFICATION

Multi-Key Attribution Lineage & Net Cash Resolution

Executed on Cloudflare D1 · Tenant-Isolated 
SELECT
  l.platform,
  l.campaign_id,
  l.spend_cents,
  l.claimed_revenue_cents,
  l.ga4_observed_rev_cents,
  COALESCE(SUM(r.gross_amount_cents - r.refunded_disputed_cents), 0) AS stripe_net_cash_cents,
  COUNT(DISTINCT CASE WHEN r.capi_recovery_state = 'UNCREDITED_RECOVERABLE' THEN r.transaction_id END) AS recoverable_capi_orders
FROM ad_platform_campaign_ledgers l
LEFT JOIN edge_click_events c
  ON c.utm_campaign_id = l.campaign_id
LEFT JOIN revenue_truth_ledger r
  ON (
    r.captured_click_keys->>'$.gclid' = c.gclid
    OR r.captured_click_keys->>'$.fbclid' = c.fbclid
    OR r.captured_click_keys->>'$.ttclid' = c.ttclid
    OR r.captured_click_keys->>'$.li_fat_id' = c.li_fat_id
    OR r.captured_click_keys->>'$.msclkid' = c.msclkid
    OR (r.sha256_email = c.sha256_email AND r.event_timestamp BETWEEN c.click_timestamp AND c.click_timestamp + INTERVAL '7 days')
  )
WHERE l.tenant_id = :tenant_id AND l.report_date BETWEEN :start_date AND :end_date
GROUP BY l.platform, l.campaign_id;

02 // THE 5 FORENSIC VARIANCE CLASSIFICATIONS MATRIX

Every dollar of variance classified into 5 deterministic root causes.

Raw discrepancy percentages don't tell your team what action to take. Skewix evaluates each campaign's 3-way tie-out vector and assigns a deterministic forensic classification with a direct remediation protocol.

CLASS 01

1. Attribution Inflation (Cross-Platform Double Counting)

CRITICAL OVER-CLAIM
Detection Predicate: SUM(Platform_Claimed_Rev) > Stripe_Net_Cash WHERE Shared_Click_Lineage >= 2

Forensic Signature

A single settled Stripe charge (`ch_3Pq9...`) carries both a 7-day `ttclid` and a 1-day `fbclid` or Google Brand `gclid`. Both ad platforms claim 100% of the order value in their respective dashboards.

Financial & Bidding Risk

Inflates blended ROAS by 30–65%, tricking media buyers into scaling overlapping retargeting and brand search budgets that cannibalize the same checkout.

Automated Skewix Remediation

Deduplicates multi-touch lineage against the unique Stripe `transaction_id` and dispatches weighted CAPI/OCI conversion values or throttles redundant retargeting caps.

CLASS 02

2. Missing CAPI / OCI Signal (Recoverable Conversion Loss)

RECOVERABLE SIGNAL
Detection Predicate: Stripe_Charge.succeeded = TRUE AND Click_ID IS NOT NULL AND Platform_Pixel_Fired = FALSE

Forensic Signature

Stripe settles a valid customer charge containing a 1st-party `fbclid`, `gclid`, `ttclid`, or `li_fat_id` captured on landing, but iOS Safari ITP, Brave, or a cross-domain checkout redirect blocked the browser pixel.

Financial & Bidding Risk

Ad platforms under-report revenue on your highest-margin mobile cohorts, starving automated smart-bidding algorithms of optimization signals.

Automated Skewix Remediation

1-Click Server-Side Backfill: Skewix hashes customer PII with Web Crypto SHA-256 and dispatches the missing conversion directly to Meta CAPI v19.0, Google Ads OCI, TikTok Events v1.3, or LinkedIn CAPI.

CLASS 03

3. Refunded / Failed Charge Skew

CASH LEAKAGE
Detection Predicate: Platform_Claimed_Rev - (Stripe_Gross_Cash - Stripe_Refunds - Stripe_Disputes) > Materiality_Threshold

Forensic Signature

Browser pixels fire immediately on checkout confirmation, recording gross pre-refund revenue. Subsequent `charge.refunded`, `charge.dispute.created`, or failed subscription renewals are never deducted in Ads Manager.

Financial & Bidding Risk

Campaigns targeting high-return SKU cohorts or low-intent promo buyers appear highly profitable in Meta/Google while losing net cash in Stripe.

Automated Skewix Remediation

Recalculates True Net Cash ROAS after refunds/chargebacks and triggers a 1-Click Campaign Budget Guardrail when net ROAS drops below break-even.

CLASS 04

4. Unattributed / Orphaned Ad Spend (Budget Bleed)

SPEND GUARDRAIL
Detection Predicate: Ad_Spend > $0 AND Stripe_Net_Cash = $0 AND Assisted_Click_Lineage = 0 OVER Rolling_Window

Forensic Signature

Active ad sets or PMax asset groups continue spending daily budget with either zero verified Stripe/HubSpot conversions or 100% view-through phantom claims with zero click-ID lineage.

Financial & Bidding Risk

Silent budget bleed across fragmented ad accounts that goes unnoticed until monthly finance close.

Automated Skewix Remediation

Flags orphaned spend immediately in the morning forensic digest and exposes 1-Click Campaign Pause or -25% Daily Budget Cap execution.

CLASS 05

5. Window & Timezone Cut-Off Drift

TEMPORAL SKEW
Detection Predicate: ABS(Ad_Event_Timestamp_Normalized_UTC - Stripe_Settlement_Timestamp_UTC) > Attribution_Cutoff

Forensic Signature

Ad accounts configured in PST/EST report conversions on click date, whereas GA4 and Stripe record settlement in UTC or upon asynchronous B2B invoice / HubSpot `closedwon` deal progression.

Financial & Bidding Risk

Creates artificial end-of-week and end-of-month variance alarms between Growth and Finance teams even when underlying cash is healthy.

Automated Skewix Remediation

Normalizes all 8 connector timestamps to UTC with configurable cohort lag windows so Finance and Growth sign off on the exact same CFO Audit Packet.

03 // MULTI-PLATFORM SERVER-SIDE WRITE-BACK & GUARDRAIL PROTOCOL

Cryptographic SHA-256 CAPI recovery and 1-click campaign guardrails.

Skewix closes the loop between your cash ledger and the ad auction. Uncredited Stripe & HubSpot conversions are hashed at the Cloudflare edge using Web Crypto SHA-256 and streamed back to the originating ad platform, while unprofitable campaigns are throttled automatically.

Meta Conversions API (CAPI v19.0)

SHA-256 VERIFIED

POST https://graph.facebook.com/v19.0/{pixel_id}/events

Match Keys: em (SHA-256), ph (SHA-256), fbc, fbp, external_id, client_ip_address, client_user_agent
Deduplication: Deterministic event_id (`skewix_capi_{stripe_charge_id}`) + action_source: "system_generated"
Audit Receipt: Persists `events_received` and immutable `fbtrace_id` in Cloudflare D1 audit log; supports `test_event_code` sandbox verification. 
{
  "data": [{
    "event_name": "Purchase",
    "event_time": 1729184402,
    "event_id": "skewix_capi_ch_3Pq9L2K8mN",
    "action_source": "website",
    "user_data": {
      "em": ["f660ab912ec121d1b1e928a0bb4bc61b15f5ad44d5efdc4e1c92a25e99b8e44a"],
      "fbc": "fb.1.1729182001.IwAR3x9P8kL2",
      "fbp": "fb.1.1729181998.901283741"
    },
    "custom_data": {
      "currency": "USD",
      "value": 490.00,
      "order_id": "ch_3Pq9L2K8mN"
    }
  }]
}

Google Ads Enhanced Conversions (OCI v17)

SHA-256 VERIFIED

POST https://googleads.googleapis.com/v17/customers/{customer_id}:uploadClickConversions

Match Keys: gclid, gbraid, wbraid + hashedEmail (SHA-256 normalized), hashedPhoneNumber
Deduplication: orderId (`ch_3Pq9L2K8mN`) + conversionDateTime with UTC timezone offset
Audit Receipt: Validates `partialFailureError` stream and logs `results[].gclid` + `conversionAction` resource receipt. 
{
  "conversions": [{
    "gclid": "Cj0KCQjwyL24BhCtARIsALo0fSD9xK2m",
    "conversionAction": "customers/8921049102/conversionActions/77482910",
    "conversionDateTime": "2026-10-17 16:40:02+00:00",
    "conversionValue": 490.00,
    "currencyCode": "USD",
    "orderId": "ch_3Pq9L2K8mN",
    "userIdentifiers": [{
      "hashedEmail": "f660ab912ec121d1b1e928a0bb4bc61b15f5ad44d5efdc4e1c92a25e99b8e44a"
    }]
  }],
  "partialFailure": true
}

TikTok Events API v1.3

SHA-256 VERIFIED

POST https://business-api.tiktok.com/open_api/v1.3/event/track/

Match Keys: ttclid, email (SHA-256), phone_number (SHA-256), external_id
Deduplication: event_id (`skewix_ttk_{stripe_charge_id}`) + event: "CompletePayment"
Audit Receipt: Captures `code: 0` and `request_id` from TikTok Business API and links to reconciled Stripe charge. 
{
  "pixel_code": "C9K2L8M1N0P3Q4R5",
  "event": "CompletePayment",
  "event_id": "skewix_ttk_ch_3Pq9L2K8mN",
  "timestamp": "2026-10-17T16:40:02Z",
  "context": {
    "ad": { "callback": "E.C.P.71928374659182" },
    "user": {
      "email": "f660ab912ec121d1b1e928a0bb4bc61b15f5ad44d5efdc4e1c92a25e99b8e44a"
    }
  },
  "properties": { "currency": "USD", "value": 490.00 }
}

LinkedIn Conversions API (REST v202405)

SHA-256 VERIFIED

POST https://api.linkedin.com/rest/conversionEvents

Match Keys: li_fat_id (FIRST_PARTY_ADS_TRACKING_UUID) + SHA256_EMAIL + ACXIOM_ID
Deduplication: eventId (`skewix_lin_{hubspot_deal_id}`) + conversion HappeningAt epoch ms
Audit Receipt: Streams B2B Stripe & HubSpot `closedwon` deal values directly into LinkedIn Campaign Manager attribution. 
{
  "conversion": "urn:lla:llaPartnerConversion:981024",
  "conversionHappenedAt": 1729184402000,
  "eventId": "skewix_lin_deal_9948102",
  "conversionValue": { "currencyCode": "USD", "amount": "12500.00" },
  "user": {
    "userIds": [
      { "idType": "SHA256_EMAIL", "idValue": "f660ab912ec121d1b1e928a0bb4bc61b15f5ad44d5efdc4e1c92a25e99b8e44a" },
      { "idType": "LINKEDIN_FIRST_PARTY_ADS_TRACKING_UUID", "idValue": "li_fat_88291049a" }
    ]
  }
}
ACTIVE SPEND PROTECTION // 1-CLICK GUARDRAIL MUTATION

Enforce campaign pause & budget cap guardrails from the tie-out ledger.

When a campaign is diagnosed with Class 01 (Attribution Inflation), Class 03 (Refund Skew), or Class 04 (Orphaned Spend), operators can execute an immediate guardrail mutation via the Meta Graph API or Google Ads Mutate service—either pausing the offending campaign or throttling daily budget by 25–50% until net Stripe ROAS recovers.

  • Pre-execution materiality check & dry-run preview
  • Immutable operator audit log with rollback state snapshot
  • Slack & email materiality alert dispatch on execution 
POST /api/guardrails/execute HTTP/1.1
Host: app.skewix.com
Content-Type: application/json

{
  "campaign_id": "cmp_gads_pmax_nonbrand_09",
  "platform": "google_ads",
  "variance_classification": "CLASS_03_REFUND_SKEW",
  "observed_metrics": {
    "platform_claimed_roas": 4.54,
    "stripe_net_cash_roas": 1.72,
    "refund_drift_cents": 1420000
  },
  "mutation": {
    "action": "CAP_DAILY_BUDGET",
    "reduction_bps": 2500,
    "previous_budget_cents": 140000,
    "new_budget_cents": 105000,
    "require_cfo_packet_entry": true
  }
}

Audit your ad spend against settled cash today.

Connect Stripe or HubSpot alongside Meta, Google, GA4, TikTok, LinkedIn, and Microsoft Ads in under 60 seconds—and start recovering dropped conversion signals immediately.

Start 14-Day Free Trial Sign In to Workspace
8 Production Connectors Live Meta CAPI · Google OCI · TikTok Events · LinkedIn CAPI AES-256-GCM Encrypted Token Vault