Buyer disposition files (the daily rolling 30-day export) go here. The gateway maps columns per the reporter's configured csv_map, matches each row to a lead by submission id, and skips rows it has already seen — so re-uploading tomorrow's version of the same window is safe and expected.
Every box names the view where that step is visible. A submission enters at the left, gets a buyer verdict in the middle, and becomes a canonical deduped lead on the right. v_lead_pipeline is this whole picture pre-joined into one row per submission — start there for most questions.
flowchart LR P["Publisher server
(host-and-post)"] --> GW F["Our funnels
homeprosusa.org · truequote.org"] --> GW F -.-> CLK["every page hit →
alb_access_logs
(the Clicks / Athena source)"] GW{"Gateway
auth · field map · validation"} GW -- "invalid → 422" --> REJ["rejected back to sender"] GW --> IL["v_lead_intake
one row per submission"] IL --> POST["post to buyer
(Select Home Warranty)"] POST -- "accepted" --> ACC["v_delivery
post_accepted = true · payout"] POST -- "rejected / error" --> NAK["v_delivery
post_accepted = false"] ACC --> PB["affiliate postback
(Everflow conversion)"] ACC --> IDR["identity resolution
(background)"] NAK --> IDR IDR --> LEAD["v_lead — canonical, deduped
v_lead_capture — consent record"] LEAD --> EVT["v_lead_status_event
captured → delivered"] LEAD --> DISP["v_disposition
sold / returned, reported later"]
submission_id ties intake to delivery; request_log_id ties intake to the consent capture; lead_id ties everything on the canonical-lead side. v_attribution is the same row as v_lead_intake with the tracking JSONB flattened. Hover any column in the Schema sidebar for its meaning.
erDiagram
v_vertical ||--o{ v_campaign : "vertical_id"
v_vertical ||--o{ v_buyer : "vertical_id"
v_offer ||--o{ v_campaign : "offer_id"
v_campaign ||--o{ v_lead_intake : "campaign_id"
v_lead_intake ||--o| v_delivery : "submission_id"
v_lead_intake ||--|| v_attribution : "same row, flattened"
v_lead_intake ||--o| v_lead_capture : "id = request_log_id"
v_lead ||--o{ v_lead_capture : "lead_id"
v_lead ||--o{ v_lead_status_event : "lead_id"
v_lead ||--o{ v_disposition : "lead_id"
v_person ||--o{ v_lead : "person_id"
v_buyer ||--o{ v_dnc_log : "buyer_id"
v_lead_intake {
varchar submission_id PK
timestamptz requested_at
varchar campaign_id FK
varchar person_email
}
v_delivery {
varchar submission_id FK
boolean post_accepted
numeric payout
}
v_lead {
bigint lead_id PK
varchar universal_lead_id
varchar pipeline_status
}
v_lead_capture {
bigint lead_id FK
bigint request_log_id FK
text consent_ip
text consent_text
}