Skip to content

Work · Reporting dashboard / warehouse · High-ticket education business · sibling of CS-03

Ads & sales reporting dashboard

Which spend produced which enrollment? Nobody could answer that.

A custom ad-performance reporting dashboard live on the client’s own domain — hourly sync from the ad platform into a dedicated Postgres warehouse, thirteen purpose-built screens, and a CRM sales layer syncing beside the spend so cost-per-enrollment becomes a join, not an integration project.

  • Next.js
  • Supabase
  • Meta Marketing API
  • GoHighLevel
  • Anthropic API
  • Role · sole designer & builder
  • 8 min
automated tests behind the dashboard
1,034Scope
scheduled edge functions with lease, retry & audit rails
6Scope
CRM sales records synced beside the ad spend
10k+Scope
System size
System flow · 10 stages · 44 components reconstructed from the build record

The situation

Ad performance lived inside the ad platform's own reporting. Sales lived in the CRM. There was no shared view joining the two, so the one question the business actually cared about, which spend produced which enrollment, could not be answered without somebody exporting two sets of numbers and lining them up by hand.

Trust was the deeper problem. Attribution had been broken upstream by an empty pixel and a dead token. A third party had produced a competing draft dashboard, so there were now two versions of the truth. Numbers were being screenshotted and quoted in meetings, and nobody in the room could say which ones held.

I built a reporting product for them, live on their own domain: an hourly sync into a dedicated warehouse, purpose-built screens over it, and a CRM sales layer sitting beside the spend so cost per enrollment becomes a join rather than an integration project.

What was broken

  • Ads sat in one tool and sales in another, with no join between them and no shared definition of a lead.
  • Attribution was broken upstream, with an empty pixel and a dead token, so the platform's own numbers had already drifted.
  • A second, competing draft dashboard from a third party gave the business two answers to every question.
  • The platform reports the same conversion action under several names at once, three of them differing over the same period, so any total depended on which name someone picked.
  • Lead geography was built on a platform metric that was materially undercounted, which also poisoned cost-per-lead and conversion rate by region.
  • The platform keeps revising the last three days as conversions are attributed, and nothing on any screen said so.

Constraints

  • No client's data could ever reach another's, which ruled out a shared multi-tenant deployment.
  • The client explicitly asked to be told what cannot be reliably calculated rather than have it quietly excluded.
  • The ad platform's own quirks had to be designed around rather than argued with.
  • The originating agency's name could not appear anywhere in the client-facing product.
  • The sales layer sat outside the ads-scope engagement, so it could be built but not billed as ads work.

FIG 01 · System Flow

How the system actually runs

Every component below is reconstructed from the project’s own build records. Nothing is added to make it look bigger.

components
44
components
stages
10
stages
decisions & guards
19
decisions & guards
AI
2
AI
human handoffs
1
human handoffs
platforms
7
platforms
  1. Stage 1, Single-tenant deployment: Give this client their own everything, so no client’s data can reach another’s.
    1. Own everything (Guard, Deployment). Deployed once per client: its own database project, its own hosting project, its own platform token and its own domain, with nothing shared between deployments. Connects to Client-facing string gate; Ad platform API when one account, confirmed in scope; Ad warehouse tables when its own database project; Per-user logins.
    2. Client-facing string gate (Guard, CI script). A string gate blocks any originating-agency name from re-entering the client-facing codebase, with a short allowlist for non-client-facing internal package aliases. Branding itself is environment-driven.
  2. Stage 2, Scheduled ingestion: Pull the ad account and the CRM into the warehouse hourly, safely, staggered across the hour.
    1. Ad platform API (Source, Meta Marketing API). Spend, impressions, reach, clicks, leads and derived costs at account, campaign, ad-set and ad level, plus demographic, placement, region and hourly breakdowns and ad disapproval reasons. Connects to Six scheduled functions.
    2. CRM (Source, GoHighLevel). The same CRM covered by the audit — agents, opportunities with status, stage, value, assignment, source and dates, and contact snapshots. Connects to Six scheduled functions.
    3. Database scheduler (Trigger, pg_cron + pg_net). The database itself schedules and fires each function over HTTP with a shared secret header, staggering the phases across the hour rather than firing them together. Connects to Server-to-server auth when shared secret header; Six scheduled functions when staggered slots across the hour.
    4. Server-to-server auth (Guard, Function config). Every function skips end-user token verification and checks its own shared-secret header instead, because the caller is the scheduler or the app’s own internal route, never a signed-in person. Connects to Six scheduled functions when secret matches.
    5. Six scheduled functions (Scheduled job, Supabase Edge Functions). Six functions carry all ingestion and delivery, each taking a lease, writing an audit row and self-healing abandoned runs. Connects to Ad warehouse tables when each phase writes its slice; Sales tables; Lead geography from CRM when lead-locations phase; Single-flight lease when take the account + phase lease; Operational tables; Delivered report when fan-out enqueues, drain delivers.
  3. Stage 3, Warehouse: One Postgres schema holding ad spend and CRM sales in the same query layer.
    1. Ad warehouse tables (Data store, Postgres). Accounts, campaigns, ad sets and ads, plus daily insights and separate demographic, placement, region and metro breakdown tables, an hourly insight table, account-level daily insights and an ad-set change log. Connects to One query layer; One lead definition; Lead-by-state proven wrong when reconcile regions against account totals; Row-level security.
    2. Sales tables (Data store, Postgres). Agents, opportunities, contact snapshots, an append-only events table and a sync-state table — over 10,000 opportunity records spanning 22 months. Connects to One query layer when same database, joinable on account + date; Row-level security; Sales screens when behind the feature flag.
    3. Operational tables (Data store, Postgres). Sync locks, sync runs, cron invocations, token-health runs, monthly budgets, preset settings, report delivery config, report sends, a report send queue and report artifacts. Connects to Row-level security; Single-flight lease when the lock row itself.
    4. One query layer (Outcome, Postgres). Paid ad spend and CRM sales sit in one database under one query layer, joinable on account and date. Connects to Query layer.
  4. Stage 4, Run integrity: Leases, audit rows and redaction — so a silent failure becomes a visible one.
    1. Single-flight lease (Guard, Postgres upsert). A race-free lease taken by an insert-on-conflict whose conflict path updates only when the existing lock has already expired, returning whether this caller now owns it. Connects to Six scheduled functions when not acquired → skip this invocation; Audit trail when lease acquired.
    2. Audit trail (Data store, Postgres). A cron-invocation row and a sync-run row per attempt, with per-phase markers, payload shapes and row counts, summarised as attempted, succeeded, failed or skipped with error strings. Connects to Freshness signal; Settings & diagnostics when diagnostics.
    3. Redacted payload samples (Guard, Diagnostics). Payload samples are redacted to shape rather than content — placeholder markers instead of real values. Connects to Audit trail when samples reduced to shape.
  5. Stage 5, Honesty gates: Refuse, in code, to state what the data cannot support.
    1. Judgement floor (Guard, Rule engine). Below 1,000 impressions, $25 spend and three days live, no comparative rule runs at all. Individual rules may demand more and never less. Connects to Four empty states when sample below the floor; Today when sample above the floor.
    2. Four empty states (Guard, UI). No data for this range (genuinely no delivery), not being collected yet (built, sync not switched on), unknown or unmapped (real delivery the platform could not place, counted in totals but not on the map), and not enough data (sample too small) — each with what to do about it. Connects to Today when shown as a verdict, not hidden; The screen set.
    3. Refuses to total reach (Guard, Metrics layer). Spend, impressions, clicks and leads add up; reach and frequency do not, because the same person may appear under two ads — so wherever reach cannot be added honestly, the dashboard declines to total it. Connects to The screen set when reach and frequency never totalled.
    4. One lead definition (Guard, Metrics layer). One canonical conversion action everywhere, with a single documented fallback. Connects to Query layer when one canonical conversion action.
    5. Freshness signal (Guard, UI header). Every page shows how recent its data actually is, counted in the ad account’s own timezone rather than the viewer’s, with a stale-data banner when the last sync is older than expected. Connects to The screen set.
  6. Stage 6, Geography rebuild: Delete a provably wrong metric and rebuild the real question from a source that can answer it.
    1. Lead-by-state proven wrong (Guard, Investigation). The platform reports geography reliably for delivery but not for conversions: delivery figures reconcile with account totals to within 0.001%, while asking for leads by region returned 1 for a period in which the dashboard counted 322. Connects to Deleted, not caveated when 322 counted, 1 reported; Lead geography from CRM when delivery geography reconciles, so keep that map.
    2. Deleted, not caveated (Outcome, Product decision). The platform’s lead-by-region metric was removed from the product entirely, along with cost-per-lead and conversion-rate by region, because both divide by that same wrong number. Connects to Two maps, two questions when delivery map only, no leads on it; Evidence guard when the report may never conclude about leads by region.
    3. Lead geography from CRM (Data store, Postgres + crosswalk). Lead location rebuilt from the CRM and resolved to county level through a generated postcode-to-county crosswalk with recorded provenance, in a dedicated table with its own data contract, migration-safety notes, privacy tests and reconciliation tests. Connects to Two maps, two questions when placed and unplaced counts stated; Row-level security.
    4. Two maps, two questions (Report, UI). An ad-delivery map from the platform (spend, impressions, reach, clicks — never leads) and a lead-location map from the CRM (lead counts, region then county). Unplaceable delivery is shown as unknown so column totals still reconcile. Connects to The screen set when geography tab.
  7. Stage 7, The screens: Purpose-built pages, from a one-minute morning check to the maps.
    1. Query layer (Data store, Server modules). A dedicated query layer with colocated unit tests, feeding every screen from the warehouse with the honesty gates already applied. Connects to Judgement floor when before any comparative rule runs; Refuses to total reach; The screen set; Deterministic evidence packet when current and prior windows; Sales screens.
    2. Today (Report, UI page). The one-minute morning check. Every flagged item shows the metric, the value observed, the threshold crossed, the date window, the sample size behind it and a suggested action, produced by fixed auditable rules. Connects to Deterministic evidence packet when the rule trigger log.
    3. Settings & diagnostics (Report, UI pages). Sync (connection status, last arrival, run-now and backfill with live progress), Budgets (a monthly budget per account, which is what activates spend pacing), Presets and rules, Reports and delivery, and Diagnostics. Connects to Today when threshold change, previewed before saving; Six scheduled functions when run now / backfill; Editable guidance.
    4. The screen set (Report, UI). Thirteen purpose-built screens, from the overview and the decision queue through ads, campaigns, ad sets and compare, to metrics, pulse, reports, settings, the sales views and the maps. Connects to Live on their domain.
  8. Stage 8, AI weekly report: An operator-voiced analysis that cannot make a claim its evidence does not support.
    1. Materiality thresholds (Guard, Server module). One definition of what counts as a meaningful change and what counts as enough volume — a relative-change floor, a larger floor for noisier rate metrics, and minimum denominators — judged on the smaller of the two comparison windows. Connects to Deterministic evidence packet when labels every delta; Operator prompt when stated verbatim to the model; Evidence guard when validates the model’s own confidence.
    2. Deterministic evidence packet (Data store, Server module). A versioned packet computed in code before the model runs: KPI facts, metric coverage, prior-period deltas with materiality labels, top spend and top lead rows, zero-lead-spend rows, highest cost-per-lead rows, biggest swings, campaign rollups, the rule trigger log, a change log, definitions and the stated data limitations. Connects to Operator prompt; Evidence guard when prose checked against the packet only.
    3. Operator prompt (AI, Prompt module). A versioned prompt casting the model as an ads operator rather than a marketing writer, stating the materiality thresholds verbatim, and separating observations from recommendations. Connects to Model call.
    4. Editable guidance (Human, Settings). Plain-English guidance the team can edit to steer emphasis and tone without touching code — and which explicitly cannot unlock the safeguards. Connects to Model call when steers tone only.
    5. Model call (AI, Multiple providers). A provider-abstracted call with per-provider timeouts, token budgets, reasoning-effort settings and minimum request gaps tuned to each provider’s rate limits. Connects to Evidence guard when structured output parsed; Delivered report when model slow or unavailable.
    6. Evidence guard (Guard, Server module). The model’s structured output is parsed and its own sufficiency and confidence calls validated against the thresholds; then its prose is checked against the evidence packet, most damaging first — invented cause categories, unhedged causal claims, geographic claims, downstream-outcome claims, unsupported currency amounts, unsupported quoted names. Connects to Model call when unsupported claim → repair retry; Delivered report when clean, or degrade to the deterministic report; 1,034 automated tests when fails the build on an unsupported claim.
    7. Delivered report (Outcome, Slack + stored artifact). The report is stored as an artifact and delivered on the configured schedule to the configured destination, with sends recorded. Connects to Live on their domain.
  9. Stage 9, Access & privacy: Per-user identity, database-level restriction, and tests that fail the build.
    1. Per-user logins (Guard, App auth). Individual accounts with their own password rather than a shared credential, with sessions, roles, invites, set-password, rate limiting and revocation. Connects to Default-deny routes.
    2. Default-deny routes (Guard, Policy module). Pure data plus one pure function: everything not explicitly named requires a signed-in user, so adding a page or route protects it automatically. Connects to Build-failing route test when route table asserted.
    3. Build-failing route test (Guard, Test suite). The full route inventory is asserted as a table — every path, method and expected requirement — and a companion test walks the app directory so a route added or moved without updating the table fails the build. Connects to 1,034 automated tests when fails the build if a route is unlisted.
    4. Row-level security (Guard, Postgres). Row-level security enabled on every table in the schema — the ad tables, every insight breakdown, the operational and report tables, the sales tables, lead locations, dashboard users and the targeting tables (32 statements in the migration set). Connects to 1,034 automated tests when fails the build on PII exposure.
    5. 1,034 automated tests (Guard, CI). 1,034 tests across 114 test files at the reporting date, several existing purely to enforce the honesty principles. Connects to Live on their domain when green build.
  10. Stage 10, CRM sales layer: Sales beside spend — and the honest finding about what that data can and cannot measure.
    1. Sales screens (Report, UI pages). A sales overview plus advisors, an advisor detail view, cohorts and readiness — showing opportunities started in range, offers presented, offer rate, sales won, won value and data completeness. Connects to Blank is not “no” when offer-rate denominator; The honest finding when read-only aggregate investigation.
    2. Blank is not “no” (Guard, Metrics layer). The offer-rate denominator counts only records with a recorded yes or no; unanswered records are excluded, never counted as “no offer”. Connects to The honest finding.
    3. The honest finding (Report, Background report). A read-only aggregate investigation — no personal data read, nothing written to any system, and the queries published so they can be re-run in front of the client — establishing that most of the requested metrics cannot be calculated. Connects to Built, gated off when built and tested, the client switches it on.
    4. Built, gated off (Outcome, Feature flag). The CRM sales layer — over 10,000 opportunity records with 22 months of history — is built and tested behind a flag, held until the client switches it on, and explicitly excluded from the ads-scope report and invoice.
    5. Live on their domain (Outcome, Production). Deployed on the client’s own subdomain and in daily use, with a completed second phase — visual redesign, rebuilt geography, individual logins, rule-engine rewrite, evidence-grounded reports — finished and tested.
0/14

An ad stops producing leads on a Tuesday. What does the system do about it, and what does the weekly report say?

Read this system as text

  1. Stage 01

    Single-tenant deployment

    Give this client their own everything, so no client’s data can reach another’s.

    • Own everything · Guard · Deployment — Deployed once per client: its own database project, its own hosting project, its own platform token and its own domain, with nothing shared between deployments. → Client-facing string gate, Ad platform API (one account, confirmed in scope), Ad warehouse tables (its own database project), Per-user logins
    • Client-facing string gate · Guard · CI script — A string gate blocks any originating-agency name from re-entering the client-facing codebase, with a short allowlist for non-client-facing internal package aliases. Branding itself is environment-driven.

  2. Stage 02

    Scheduled ingestion

    Pull the ad account and the CRM into the warehouse hourly, safely, staggered across the hour.

    • Ad platform API · Source · Meta Marketing API — Spend, impressions, reach, clicks, leads and derived costs at account, campaign, ad-set and ad level, plus demographic, placement, region and hourly breakdowns and ad disapproval reasons. → Six scheduled functions
    • CRM · Source · GoHighLevel — The same CRM covered by the audit — agents, opportunities with status, stage, value, assignment, source and dates, and contact snapshots. → Six scheduled functions
    • Database scheduler · Trigger · pg_cron + pg_net — The database itself schedules and fires each function over HTTP with a shared secret header, staggering the phases across the hour rather than firing them together. → Server-to-server auth (shared secret header), Six scheduled functions (staggered slots across the hour)
    • Server-to-server auth · Guard · Function config — Every function skips end-user token verification and checks its own shared-secret header instead, because the caller is the scheduler or the app’s own internal route, never a signed-in person. → Six scheduled functions (secret matches)
    • Six scheduled functions · Scheduled job · Supabase Edge Functions — Six functions carry all ingestion and delivery, each taking a lease, writing an audit row and self-healing abandoned runs. → Ad warehouse tables (each phase writes its slice), Sales tables, Lead geography from CRM (lead-locations phase), Single-flight lease (take the account + phase lease), Operational tables, Delivered report (fan-out enqueues, drain delivers)

  3. Stage 03

    Warehouse

    One Postgres schema holding ad spend and CRM sales in the same query layer.

    • Ad warehouse tables · Data store · Postgres — Accounts, campaigns, ad sets and ads, plus daily insights and separate demographic, placement, region and metro breakdown tables, an hourly insight table, account-level daily insights and an ad-set change log. → One query layer, One lead definition, Lead-by-state proven wrong (reconcile regions against account totals), Row-level security
    • Sales tables · Data store · Postgres — Agents, opportunities, contact snapshots, an append-only events table and a sync-state table — over 10,000 opportunity records spanning 22 months. → One query layer (same database, joinable on account + date), Row-level security, Sales screens (behind the feature flag)
    • Operational tables · Data store · Postgres — Sync locks, sync runs, cron invocations, token-health runs, monthly budgets, preset settings, report delivery config, report sends, a report send queue and report artifacts. → Row-level security, Single-flight lease (the lock row itself)
    • One query layer · Outcome · Postgres — Paid ad spend and CRM sales sit in one database under one query layer, joinable on account and date. → Query layer

  4. Stage 04

    Run integrity

    Leases, audit rows and redaction — so a silent failure becomes a visible one.

    • Single-flight lease · Guard · Postgres upsert — A race-free lease taken by an insert-on-conflict whose conflict path updates only when the existing lock has already expired, returning whether this caller now owns it. → Six scheduled functions (not acquired → skip this invocation), Audit trail (lease acquired)
    • Audit trail · Data store · Postgres — A cron-invocation row and a sync-run row per attempt, with per-phase markers, payload shapes and row counts, summarised as attempted, succeeded, failed or skipped with error strings. → Freshness signal, Settings & diagnostics (diagnostics)
    • Redacted payload samples · Guard · Diagnostics — Payload samples are redacted to shape rather than content — placeholder markers instead of real values. → Audit trail (samples reduced to shape)

  5. Stage 05

    Honesty gates

    Refuse, in code, to state what the data cannot support.

    • Judgement floor · Guard · Rule engine — Below 1,000 impressions, $25 spend and three days live, no comparative rule runs at all. Individual rules may demand more and never less. → Four empty states (sample below the floor), Today (sample above the floor)
    • Four empty states · Guard · UI — No data for this range (genuinely no delivery), not being collected yet (built, sync not switched on), unknown or unmapped (real delivery the platform could not place, counted in totals but not on the map), and not enough data (sample too small) — each with what to do about it. → Today (shown as a verdict, not hidden), The screen set
    • Refuses to total reach · Guard · Metrics layer — Spend, impressions, clicks and leads add up; reach and frequency do not, because the same person may appear under two ads — so wherever reach cannot be added honestly, the dashboard declines to total it. → The screen set (reach and frequency never totalled)
    • One lead definition · Guard · Metrics layer — One canonical conversion action everywhere, with a single documented fallback. → Query layer (one canonical conversion action)
    • Freshness signal · Guard · UI header — Every page shows how recent its data actually is, counted in the ad account’s own timezone rather than the viewer’s, with a stale-data banner when the last sync is older than expected. → The screen set

  6. Stage 06

    Geography rebuild

    Delete a provably wrong metric and rebuild the real question from a source that can answer it.

    • Lead-by-state proven wrong · Guard · Investigation — The platform reports geography reliably for delivery but not for conversions: delivery figures reconcile with account totals to within 0.001%, while asking for leads by region returned 1 for a period in which the dashboard counted 322. → Deleted, not caveated (322 counted, 1 reported), Lead geography from CRM (delivery geography reconciles, so keep that map)
    • Deleted, not caveated · Outcome · Product decision — The platform’s lead-by-region metric was removed from the product entirely, along with cost-per-lead and conversion-rate by region, because both divide by that same wrong number. → Two maps, two questions (delivery map only, no leads on it), Evidence guard (the report may never conclude about leads by region)
    • Lead geography from CRM · Data store · Postgres + crosswalk — Lead location rebuilt from the CRM and resolved to county level through a generated postcode-to-county crosswalk with recorded provenance, in a dedicated table with its own data contract, migration-safety notes, privacy tests and reconciliation tests. → Two maps, two questions (placed and unplaced counts stated), Row-level security
    • Two maps, two questions · Report · UI — An ad-delivery map from the platform (spend, impressions, reach, clicks — never leads) and a lead-location map from the CRM (lead counts, region then county). Unplaceable delivery is shown as unknown so column totals still reconcile. → The screen set (geography tab)

  7. Stage 07

    The screens

    Purpose-built pages, from a one-minute morning check to the maps.

    • Query layer · Data store · Server modules — A dedicated query layer with colocated unit tests, feeding every screen from the warehouse with the honesty gates already applied. → Judgement floor (before any comparative rule runs), Refuses to total reach, The screen set, Deterministic evidence packet (current and prior windows), Sales screens
    • Today · Report · UI page — The one-minute morning check. Every flagged item shows the metric, the value observed, the threshold crossed, the date window, the sample size behind it and a suggested action, produced by fixed auditable rules. → Deterministic evidence packet (the rule trigger log)
    • Settings & diagnostics · Report · UI pages — Sync (connection status, last arrival, run-now and backfill with live progress), Budgets (a monthly budget per account, which is what activates spend pacing), Presets and rules, Reports and delivery, and Diagnostics. → Today (threshold change, previewed before saving), Six scheduled functions (run now / backfill), Editable guidance
    • The screen set · Report · UI — Thirteen purpose-built screens, from the overview and the decision queue through ads, campaigns, ad sets and compare, to metrics, pulse, reports, settings, the sales views and the maps. → Live on their domain

  8. Stage 08

    AI weekly report

    An operator-voiced analysis that cannot make a claim its evidence does not support.

    • Materiality thresholds · Guard · Server module — One definition of what counts as a meaningful change and what counts as enough volume — a relative-change floor, a larger floor for noisier rate metrics, and minimum denominators — judged on the smaller of the two comparison windows. → Deterministic evidence packet (labels every delta), Operator prompt (stated verbatim to the model), Evidence guard (validates the model’s own confidence)
    • Deterministic evidence packet · Data store · Server module — A versioned packet computed in code before the model runs: KPI facts, metric coverage, prior-period deltas with materiality labels, top spend and top lead rows, zero-lead-spend rows, highest cost-per-lead rows, biggest swings, campaign rollups, the rule trigger log, a change log, definitions and the stated data limitations. → Operator prompt, Evidence guard (prose checked against the packet only)
    • Operator prompt · AI · Prompt module — A versioned prompt casting the model as an ads operator rather than a marketing writer, stating the materiality thresholds verbatim, and separating observations from recommendations. → Model call
    • Editable guidance · Human · Settings — Plain-English guidance the team can edit to steer emphasis and tone without touching code — and which explicitly cannot unlock the safeguards. → Model call (steers tone only)
    • Model call · AI · Multiple providers — A provider-abstracted call with per-provider timeouts, token budgets, reasoning-effort settings and minimum request gaps tuned to each provider’s rate limits. → Evidence guard (structured output parsed), Delivered report (model slow or unavailable)
    • Evidence guard · Guard · Server module — The model’s structured output is parsed and its own sufficiency and confidence calls validated against the thresholds; then its prose is checked against the evidence packet, most damaging first — invented cause categories, unhedged causal claims, geographic claims, downstream-outcome claims, unsupported currency amounts, unsupported quoted names. → Model call (unsupported claim → repair retry), Delivered report (clean, or degrade to the deterministic report), 1,034 automated tests (fails the build on an unsupported claim)
    • Delivered report · Outcome · Slack + stored artifact — The report is stored as an artifact and delivered on the configured schedule to the configured destination, with sends recorded. → Live on their domain

  9. Stage 09

    Access & privacy

    Per-user identity, database-level restriction, and tests that fail the build.

    • Per-user logins · Guard · App auth — Individual accounts with their own password rather than a shared credential, with sessions, roles, invites, set-password, rate limiting and revocation. → Default-deny routes
    • Default-deny routes · Guard · Policy module — Pure data plus one pure function: everything not explicitly named requires a signed-in user, so adding a page or route protects it automatically. → Build-failing route test (route table asserted)
    • Build-failing route test · Guard · Test suite — The full route inventory is asserted as a table — every path, method and expected requirement — and a companion test walks the app directory so a route added or moved without updating the table fails the build. → 1,034 automated tests (fails the build if a route is unlisted)
    • Row-level security · Guard · Postgres — Row-level security enabled on every table in the schema — the ad tables, every insight breakdown, the operational and report tables, the sales tables, lead locations, dashboard users and the targeting tables (32 statements in the migration set). → 1,034 automated tests (fails the build on PII exposure)
    • 1,034 automated tests · Guard · CI — 1,034 tests across 114 test files at the reporting date, several existing purely to enforce the honesty principles. → Live on their domain (green build)

  10. Stage 10

    CRM sales layer

    Sales beside spend — and the honest finding about what that data can and cannot measure.

    • Sales screens · Report · UI pages — A sales overview plus advisors, an advisor detail view, cohorts and readiness — showing opportunities started in range, offers presented, offer rate, sales won, won value and data completeness. → Blank is not “no” (offer-rate denominator), The honest finding (read-only aggregate investigation)
    • Blank is not “no” · Guard · Metrics layer — The offer-rate denominator counts only records with a recorded yes or no; unanswered records are excluded, never counted as “no offer”. → The honest finding
    • The honest finding · Report · Background report — A read-only aggregate investigation — no personal data read, nothing written to any system, and the queries published so they can be re-run in front of the client — establishing that most of the requested metrics cannot be calculated. → Built, gated off (built and tested, the client switches it on)
    • Built, gated off · Outcome · Feature flag — The CRM sales layer — over 10,000 opportunity records with 22 months of history — is built and tested behind a flag, held until the client switches it on, and explicitly excluded from the ads-scope report and invoice.
    • Live on their domain · Outcome · Production — Deployed on the client’s own subdomain and in daily use, with a completed second phase — visual redesign, rebuilt geography, individual logins, rule-engine rewrite, evidence-grounded reports — finished and tested.

Platforms · Next.js · Supabase · Postgres · Meta Marketing API · GoHighLevel · Anthropic API · Slack

What I built

One deployment per client

It is deployed single-tenant: its own database project, its own hosting project, its own platform token and its own domain, with nothing shared between deployments. Multi-tenancy is a data-leak surface this project simply declines to have.

Ingestion with rails

Six scheduled edge functions carry all ingestion and delivery. Each takes a lease, writes an audit row, and self-heals abandoned runs, so an outage leaves a recoverable gap rather than a permanent hole in the history. The six cover the hourly ad sync, a manual backfill per breakdown, a daily credential monitor, the weekly-report fan-out, the report queue drain, and the CRM sales sync.

The database itself schedules them and fires each over HTTP with a shared secret. They are staggered across the hour rather than fired together, which keeps each invocation inside the runtime's wall clock and stops six syncs contending for the same API budget.

The warehouse

One Postgres schema holds accounts, campaigns, ad sets and ads, plus separate tables for daily insights and for the demographic, placement, region and metro breakdowns, an hourly insight table and an ad-set change log. Breakdowns get their own tables so a high-cardinality one cannot degrade the base one. Beside them sit the sales tables, over 10,000 opportunity records spanning 22 months, with an append-only events table that is the only change history that exists at all, because the source system keeps none for custom fields.

Honesty enforced in code

This is the part I would point at first. Below 1,000 impressions, $25 spend and three days live, no comparative rule runs at all. An ad with five clicks and two leads shows a 40% conversion rate on paper, and acting on that is how budget gets wasted.

Spend, impressions, clicks and leads add up. Reach and frequency do not, because the same person may appear under two ads, so wherever reach cannot be added honestly the dashboard declines to total it. One canonical conversion action is used everywhere with a single documented fallback, because adding the platform's several names for it would roughly triple the number.

Every page shows how recent its data actually is, counted in the ad account's own timezone rather than the viewer's, with a stale-data banner when the last sync is older than expected.

A metric deleted rather than caveated

Delivery geography reconciles with account totals to within 0.001%. Asking the same platform for leads by region returned 1 for a period in which the dashboard counted 322. That is not a slightly low number better handling could improve. It answers a different question nobody asked.

So the platform's lead-by-region metric was removed from the product entirely, along with cost-per-lead and conversion rate by region, because both divide by that same wrong number. A number like that cannot be shown with a footnote. It gets screenshotted, quoted, and believed. Lead location was rebuilt from the CRM instead, resolved to county level through a generated postcode crosswalk with recorded provenance, in its own table with a data contract, migration-safety notes, privacy tests and reconciliation tests. Placed and unplaced counts are stated openly rather than dropped.

The AI weekly report, and the guard it cannot talk its way past

Materiality is defined once and consumed three times: the evidence builder labels every delta with it, the prompt states it verbatim to the model, and the schema validates the model's own sufficiency and confidence calls against it. So the report cannot disagree with itself. A 100% lead increase from one to two is not a trend.

Before the model runs, a versioned evidence packet is computed in code: KPI facts, coverage, prior-period deltas with materiality labels, top spend and lead rows, zero-lead-spend rows, highest cost-per-lead rows, biggest swings, rollups, the rule trigger log, definitions and the stated data limitations. Computing it in code is what makes a claim checkable at all.

Afterwards the output is parsed, its own sufficiency calls validated, and its prose checked against the packet, most damaging first: invented cause categories, unhedged causal claims, geographic claims, downstream-outcome claims, unsupported currency amounts, unsupported quoted names. Nothing is advisory. A hit rejects the report, which triggers a repair retry or a fall back to the deterministic version.

Access as a build gate

Per-user logins with their own passwords, sessions, roles, invites and revocation, rather than a shared credential. Route policy is pure data plus one pure function, so everything not explicitly named requires a signed-in user and adding a page protects it automatically. Row-level security is enabled on every table in the schema. The full route inventory is asserted as a table, and a companion test walks the app directory so a route added or moved without updating that table fails the build.

Edge cases and failure handling

  • Each function refuses to start when its secret is unset, and names the missing variable without ever echoing a value, because that response goes back to whatever called it.
  • Four distinct empty states are separated: no data for this range, not being collected yet, unknown or unmapped, and not enough data. An empty screen is never ambiguous.
  • Without a monthly budget, spend pacing stays inactive rather than guessing, because a pacing card with an invented denominator is exactly the kind of number this product refuses to show.
  • The client-editable report guidance cannot relax the safeguards, by construction: the check functions receive only the text and the evidence packet, never the prompt and never the guidance.
  • Public pages accept GET and HEAD only, because a server action posts to whatever page path the browser is on, so a public page accepting POST would let an unauthenticated caller invoke any server action in the app.

How I verified it

There are 1,034 automated tests across 114 test files as of the reporting date, several of which exist purely to enforce the honesty principles. Three headline guarantees each have a test that breaks the build: if lead geography starts exposing personal data, if a page ships without an access policy, or if a generated report makes a claim its evidence does not support.

The evidence guard has its own scored eval harness with a baseline and an adversarial suite, so it is regression-tested rather than trusted.

What changed

The dashboard is live on the client's own subdomain and in daily use, across thirteen purpose-built screens fed by one query layer, so no two screens can tell different stories.

Ad spend and CRM sales now sit in one database under one query layer, joinable on account and date, which is the structural advantage no general BI tool gives without rebuilding this pipeline first. A provably wrong metric was deleted rather than caveated, and lead geography was rebuilt from a source that can actually answer the question.

The CRM sales layer, over 10,000 opportunity records with 22 months of history, is built and tested behind a flag until the client turns it on, and excluded from the ads-scope invoice.

The sales investigation also returned a finding the client had asked for in advance: most of the metrics they wanted cannot be calculated. Not for technical reasons, but because the outcomes are not being recorded.

Before / after

  • Before
  • ✕ Ads in one tool, sales in another, no join between them
  • ✕ Attribution broken upstream — empty pixel, dead token
  • ✕ A second, competing version of the truth in a third party’s draft dashboard
  • ✕ Lead geography built on a provably undercounted metric
  • After
  • ✓ One warehouse: ad spend and CRM sales in the same query layer
  • ✓ Thirteen screens from a one-minute decision queue to an AI weekly report
  • ✓ Reports that cannot state what the evidence doesn’t support
  • ✓ Lead geography rebuilt from the CRM, placed and unplaced counts stated

ScopeFigures on this page describe what was built, covered or verified — not a measured lift.

Related systems

Have something close to this? Book a call. I’ll tell you if I’ve already built it.

One Manila business day to reply. All client work is under NDA; what you see here is redrawn architecture with real metrics.

Book