🗺️ MortgageCo Schema — Source of Truth

🔗 How Tracking Works (Parent → Child → Buyer Postback)

Single brand: MortgageCo. Single parent offer: hefty.offers.id=32. Eleven children (ids 12-22) — one per buyer/network we resell leads to. Every Meta/Google/Email click lands on go.mortgageco.com/?aff=8&oid=32&... — parent oid=32 is routed to a child offer before being stored, so the parent never owns clicks directly.

1. Click ingest

Real user lands on the canonical tracking link:

https://go.mortgageco.com/?aff=8&oid=32&s1={{campaign.id}}&s2={{adset.id}}&s3={{ad.id}}&fbclid={{fbclid}}

Nginx on slaives proxies the click to the click-ingest service on port 3033. Service inserts a row into hefty.clicks (partner_id=8, offer_id=<routed child>). App layer routes parent 32 → child via pickRoutedOfferIdOrDefault(), walking hefty.offer_hierarchy. DB trigger trg_clicks_block_parent_offer enforces this at the DB layer.

2. Funnel + lead submit

User completes the multi-step funnel on go.mortgageco.com. Final POST hits https://hefty.cc/api/leads/ingest with click_id, PII (email/phone/first/last/state/zip), and Meta tracking params. Heftycc creates the hefty.leads row with click_row_id FK to the click, inheriting partner_id. PR #25 + #27 ensure body-level aff/utm_source/fbclid/numeric s1 override the organic fallback so attribution is correct even when the referrer is stripped.

3. Buyer distribution (sold path)

distributeLead() iterates hefty.buyers by priority. Each buyer's delivery_url is POSTed with the lead payload. First buyer to accept (HTTP 200 + parsed accept response) marks the lead status='sold'. Buyers in multi_sell_eligible mode also receive the lead. Each acceptance writes to hefty.lead_distributions:

lead_id, buyer_id, accepted=true, price=<negotiated>, http_status, response_time_ms

Each buyer's child offer assignment (buyer ↔ child offers.id 12-22) lives in hefty.buyers.offer_id OR hefty.partner_buyer_routing. The buyer's accepted price = gross revenue in the dashboard (lead_distributions.price).

4. Buyer postback (revenue confirmation)

When a buyer's downstream funnel converts, they fire a GET to:

https://hefty.cc/api/postback?click_id=...&offer_id=...&payout=...&transaction_id=...&status=approved

The /api/postback handler:

1. Resolves the click via click_id (hex UUID from the original click).
2. Resolves the child offer via offer_id (12–22).
3. Inserts hefty.offer_conversions (click_id, offer_id, payout, status='approved', transaction_id).
4. Logs to hefty.postback_logs.

Postback payout = net revenue (what the buyer actually paid per their postback rules). Some buyers post back per-acceptance (immediate), some per-final-funded (delayed).

5. Outbound partner postback (third-party affiliates)

If the lead was sourced from an external affiliate (partners.postback_url IS NOT NULL), Heftycc fires a GET to that URL on sold-lead with {transaction_id} + {amount} placeholders substituted. Affiliates self-manage this URL via their portal at /partner → Postback Setup tab.

6. Revenue rollup

All revenue accrues to MortgageCo (parent 32) by aggregating across children:

SELECT
  (SELECT COUNT(*) FROM hefty.clicks
     WHERE offer_id IN (12,13,14,15,16,17,18,19,20,21,22)
       AND created_at::date = CURRENT_DATE) AS routed_clicks_today,
  (SELECT COUNT(*) FROM hefty.leads
     WHERE data->>'brand' = 'mortgageco'
       AND created_at::date = CURRENT_DATE) AS leads_today,
  (SELECT SUM(price::numeric) FROM hefty.lead_distributions
     WHERE accepted = true AND lead_id IN (
       SELECT id FROM hefty.leads
        WHERE data->>'brand' = 'mortgageco'
          AND created_at::date = CURRENT_DATE
     )) AS gross_revenue_today,
  (SELECT SUM(payout::numeric) FROM hefty.offer_conversions
     WHERE offer_id IN (12,13,14,15,16,17,18,19,20,21,22)
       AND status = 'approved'
       AND created_at::date = CURRENT_DATE) AS net_postback_revenue_today;

Today's snapshot (2026-04-19, captured at write-time, Pacific day):

• 449 parent-fallback clicks + 2,108 child-routed clicks = 2,557 total
• 5 MortgageCo leads today
• 6 distributions (accepted), 1 multi-sell
• $79.89 gross from lead_distributions.price
• $0.00 net from offer_conversions.payout yet today (approved postbacks, often hours-delayed)

7. Why two revenue numbers?

• lead_distributions.price = what the buyer committed to pay at accept time (gross, counted immediately).
• offer_conversions.payout = what the buyer actually paid via postback (net, counted at postback time, often hours/days later).
• Some buyers never fire postbacks (CPL contracts) — for those, lead_distributions.price is the only revenue source.
• Dashboard "Revenue" column uses GREATEST(lead_distributions.price, offer_conversions.payout) per lead so neither path is double-counted or dropped.

Quick reference — partner ID map

Quick reference — child offers (buyers)

Admin dashboard view: https://hefty.cc/dashboard — shows Parent Offer hero card with rolled-up metrics + Child Offers (Buyers) table per this flow.

Affiliates (inbound traffic)

Offers Tree

🚀 Inbound Affiliate Links