System 3 of 4
Multi-tenant attribution and client economics platform
Franchise directors see only their own leads and disposition them through to revenue. Finance gets lifetime value and acquisition cost by location and by digital source. Shipped to production in one day, then hardened into an enforced access model.
In production, nightly sync
- Role
- Sole architect and engineer
- Stack
- Supabase (Postgres, Auth, row-level security), Cloudflare Workers, React front end, Python sync jobs
- Data
- 124K attribution rows, 4,449 offline conversions, 1,000+ leads dispositioned
- Tenancy
- Enforced across 18 tables, proven by automated cross-tenant tests
- Users
- Franchise directors across 7 locations, operations leadership, the VP of Finance
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
- 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, 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.