124K attribution rows. Tenant boundaries tested across 18 tables.
Enforce tenant scope and column-level write permissions in the database. Preserve human-entered data during sync.
Automated cross-tenant read and write tests; client economics reconciled within 0.7% across platforms.
| Lead | Source | Keyword | Disposition | Start | Wk hrs | Wk rev |
|---|---|---|---|---|---|---|
| L-1041 | Google Ads | [service] near me | client | 06/02 | 24 | $••• |
| L-1042 | Google Ads | [service] cost | consult set | – | – | – |
| L-1043 | Organic | – | contacted | – | – | – |
| L-1044 | Google Ads | [brand] reviews | client | 05/28 | 16 | $••• |
| L-1045 | Google Ads | [service] agency | no answer | – | – | – |
Problem
Cost per lead ends at the lead. Finance wanted lifetime value and acquisition cost by location and by digital source, and that required the people who actually close leads to record what happened afterward: whether the lead became a client, when they started, weekly hours, weekly revenue. Directors at each location needed a tool that showed them only their own leads, and finance needed the roll-up. The first version shipped to production in one day. The hardening took longer, on purpose.
Architecture
Architecture model. Scroll horizontally on smaller screens →
- Nightly syncQualified leads and their attribution flow from the ads engine into Postgres, with idempotent user provisioning that can never overwrite human-entered data.
- Row-level securityEvery query is scoped to the director's tenant by Postgres policy, not by application code. A defense-in-depth tenant firewall means no client-facing user can see that another portfolio company exists.
- Edge deliveryCloudflare Workers serve the React app and its API. Supabase Auth handles identity.
- Disposition workflowDirectors work each lead through a field-sales flow and record start dates, weekly hours, and weekly revenue.
- Client economics pipelineReplaces a 4.5-hour per-client scrape with report pulls that finish in minutes: 1,130 client rows across 7 locations, validated within 0.7% across platforms.
- Finance outputThe digitally sourced share of $15.9M in lifetime client revenue, and LTV/CAC by location and by source, presented to the VP of Finance as the top-of-funnel half of an acquisition-cost ceiling.
Hardening after day one
- Closed a row-level-security bypass in the reporting layer.
- Column-level write boundaries so directors can edit dispositions but not attribution facts.
- Automated cross-tenant tests across all 18 tables that attempt to read and write another tenant's rows and must fail.
- Fixed RLS policy recursion with a SECURITY DEFINER function, fixed an auth deadlock, and added a boot watchdog.
- Provisioning is idempotent and never overwrites data a human entered.
Numbers
| Attribution rows | 124K |
|---|---|
| Offline conversions uploaded to Google | 4,449 |
| Tables under enforced tenant isolation | 18 |
| Leads dispositioned by directors | 1,000+ |
| Client economics rows | 1,130 across 7 locations |
| Cross-platform validation | within 0.7% |
| Per-client report time | 4.5 hours to minutes |
| Lifetime client revenue analyzed | $15.9M |
| Time from design to production | one day |
Why it matters for the ads engine
The ads engine optimizes on qualified leads. This platform is what turns a qualified lead into a known client with known revenue, which is the only way to say what a lead is worth by location and by source. The two together are one closed loop from ad click to lifetime value.