Ad Network Data Layer - Implementation Plan

Date: 2026-07-22 Branch: claude/ad-network-rotation-data-gs1sja (off origin/dev) Status: live plan; section 5 is the running dashboard. Issues: #692, #694, #695, #696, #697Canonical refs: docs/economics/AD_PLACEMENT_ECONOMICS.md (Part 2 rotation design, Part 5 tech specifics, Part 6 open questions, Part 7 instrumentation order); docs/engineering/analytics/analytics-infrastructure.md; PR #553 (selection baseline + rotation-ready scaffolding).

1. Why this, why now

The rotation/pacing engine is fully designed in the economics doc but deliberately unbuilt; several of its open questions (Part 6: inline impressions per DAU, sampling rate, frequency-cap window, live-event boost, monthly floor policy) can only be answered with real delivery data. Part 7 is explicit: instrument first, build rotation second, because the data is hard to retrofit and the engine is easy to build once the data is honest.

This plan builds the data layer: honest events, durable aggregates, an internal dashboard, and real merchant-facing numbers. The rotation engine itself is out of scope and slots into the existing RotationStrategy / PacingStore seams later, unchanged.

Operator direction (2026-07-22): scope approved, executive decision delegated, emulator use approved (including creating test logins on the dev backend), document everything.

2. Current state (verified against code, 2026-07-22)

• Selection: centralized in packages/shared/ads/index.js (selectOffers / getSponsoredOffer), proximity strategy, no-op pacing store. Rotation seams in place.
• Events registered in packages/shared/analytics/index.js: offer_viewed, offer_claimed, offer_redeemed, sponsored_ad_clicked, sponsored_offer_filled, sponsored_ad_impression.
• Events actually emitted: only the three sponsored_* events, hero placement only, from HomeView.jsx (FILLED on commit, IMPRESSION via useInViewOnce, CLICKED on card tap). Payload metadata: offerId, surface, placement.
• Known count inflation: the FILLED/IMPRESSION dedupe Sets are per-mount refs; login-flow remounts re-fire both events per page session (#692).
• Inline gap: OfferPill on venue rows fills with no impression event (TODO at HomeView.jsx ~1156) (#694).
• No claim flow exists in apps/web; offer_claimed / offer_redeemed have no call sites. The attribution chain can only be contract-defined today (#694).
• Pipeline: Flash (browser) batches to analytics-api (Cloud Run), Forge writes to BigQuery analytics.events (daily partitions, 90-day expiry) and optionally Firestore analytics_events (90-day TTL).
• Merchant dashboard (apps/web/src/screens/merchant/MerchantDashboard.jsx): hardcoded offers, Math.random() impressions. No real pipeline (#697).

3. Phases

Phase A: honest events (#692, #694)

Step	What	Where
A1	Page-session-scoped dedupe for FILLED/IMPRESSION (module scope, survives remounts; fresh on full reload). Regression test.	HomeView.jsx + new test
A2	Inline OfferPill impressions via useInViewOnce, placement: 'inline', same dedupe.	HomeView.jsx
A3	Payload completeness: add areaBucket (coarse areaKey, never raw GPS), targetAudience, merchantId to FILLED/IMPRESSION/CLICKED metadata; update registry parameter docs.	HomeView.jsx, packages/shared/analytics
A4	Define claim-attribution metadata contract on offer_claimed/offer_redeemed (registry only; no UX exists).	packages/shared/analytics

Phase B: durable aggregates + k-anonymity (#695)

Daily rollup per (offer, merchant, placement, audience, day) with COUNT(DISTINCT userId), materialized into analytics.ad_delivery_daily (no expiry), plus a serving path on analytics-api. K-anon gate (suppress cells below the floor) applied to anything merchant-visible; internal views ungated. Invoke privacy-architecture and cloud-service skills before building. Depends on Phase A (aggregate honest counts only).

Phase C: internal dashboard (#696)

Admin portal page next to Venue Activity: impressions vs fills per placement/merchant/audience, CTR, trend. Answers Part 6 questions 2 and 7 and tells us when a market approaches the ~30-merchant pacing threshold.

Phase D: merchant-facing data (#697)

Replace the mock MerchantDashboard numbers with real, k-anon-gated aggregates: per-offer impressions/clicks/redemptions, CTR, month-to-date delivered (the seed of the monthly true-up story). Suppressed cells render as a dash with "not enough data yet".

4. Decisions made under delegated authority

Recorded here as they happen; flag disagreements any time.

#	Decision	Rationale
D1	Phase A4 is contract-only	No claim/redeem UX exists to instrument; defining registry parameters now means the claim flow ships pre-wired.
D2	Rollup table is analytics.ad_delivery_daily, no partition expiry	Merchant history and monthly accountability must outlive the 90-day raw window; rollups are k-anon-safe aggregates so indefinite retention matches the econ doc's retention posture (row-level capped, aggregates kept).
D3	K-anon floor stays at 3 for v1	Matches the econ doc's working number everywhere (PRIVACY_MIN_GROUP_SIZE); revisiting to 5 is a one-line threshold change recorded in Part 6 question 11.
D4	Dedupe scope is per page session (module scope), not sessionStorage	Module scope survives remounts (the actual bug) with zero storage semantics; a full reload legitimately starts a new impression window, which sessionStorage would wrongly suppress.
D5	areaBucket is NOT a rollup dimension	Slicing daily cells by ~0.4 km buckets would collapse most cells under the k-anon floor, and no merchant-facing question needs it. Area diagnostics stay internal on the raw (retention-capped) events.
D6	Cell-level suppression, unique_users always stripped	A below-floor cell nulls every metric and flags suppressed: true; unique_users never appears in gated output even above the floor. Merchant totals sum unsuppressed cells only (a floor, never an overstatement).
D7	'unknown' fallback dimensions instead of dropping rows	Pre-instrumentation events (no merchantId/targetAudience in metadata) land in 'unknown' cells rather than vanishing, keeping totals honest across the instrumentation cutover. Rows with no resolvable offer id are excluded (unattributable).
D8	errorHandler learned err.code === 'VALIDATION' -> 400	Service-layer range validation errors are client mistakes, not 500s; matches the existing ZodError branch.
D9	MerchantDashboard's fake "weekly merchant drop" stats replaced with a real month-to-date delivery strip	The section presented fabricated numbers (38% open rate) as the merchant's own data; the month strip is the seed of the monthly true-up story (#697's v1 surface). The static placement explainer cards stay.
D10	Unknown is not zero on the merchant surface	Analytics outage renders dashes plus a quiet notice, never zeros ("your ad never ran") and never the "No offers yet" empty state when the offers call failed.
D11	Live-verification finding (2026-07-22): getCurrentUser() in apps/web resolves a Promise	merchantDeliveryService initially read .uid synchronously and always soft-failed; caught by the emulator browser run, fixed with an await. Unit tests now mock the async shape faithfully.
D12	Offer-form redesign dropped (operator call, 2026-07-22)	Flow mockups and an unrouted one-page-composer prototype (built from the real section components, commit ba20d1d, removed in c0c1bec) surfaced that the existing form's per-placement content/design depth is load-bearing. Existing OfferForm stays as-is; revisit only on real merchant friction. Reference screenshots of all eight pages were captured during the decision.
D13	Merchant analytics scope is the minted merchant id, never the auth uid (multi-angle review, 2026-07-23)	The pre-review code bound req.user.uid as merchant_id, which never matches the merchants/{id} doc id the rollup keys on (minted by generateMerchantId, linked via users/{uid}.merchantId). requireRole now attaches the linked merchantId, resolveMerchantScope pins merchants to it (403 when unlinked), and only admins may pass ?merchantId.
D14	Gated totals are computed in SQL, per cell, alongside a bounded row read	Client-summed totals were both truncation-blind (LIMIT capped rows silently understated headline numbers) and derivable-leak-adjacent. The totals query floors each cell (SUM(IF(unique_users >= @floor, col, 0))), counts suppressed cells, and is exact regardless of row-list truncation, which is flagged as truncated.
D15	client_untrusted claim/redeem events are excluded from the rollup	Claims/redemptions are billing-adjacent; the tracking route stamps client submissions attributionSource='client_untrusted', and the MERGE drops those two event names from that source so they cannot be spoofed into month-end numbers. Fills/impressions/clicks are client-observed by nature and aggregate regardless.
D16	Scheduled lookback default is 3 days (self-healing window)	The MERGE is idempotent, so re-aggregating already-correct days is a free overwrite; a 3-day window means one missed nightly firing heals on the next run without operator action. The MERGE target is also day-bounded for partition pruning (the rollup table has no expiry and would otherwise be scanned in full nightly).
D17	Read-side environment tag derives from the project id, never NODE_ENV (review round 2, 2026-07-23)	Cloud Run DEV deploys set NODE_ENV=production for Express perf, so the old NODE_ENV-based resolveEnvironment() filtered environment='production' against rows the forge stamps 'development': every BQ-backed dashboard read zero rows on dev with a clean 200. Reads now mirror the write side (project name contains 'prod'), with ANALYTICS_ENVIRONMENT still available as an explicit override. This also corrects the same latent mismatch for the pre-existing venue-activity BQ reads.
D18	Claim/redeem rows count only when stamped attributionSource='server_authoritative'	The prior NOT (... = 'client_untrusted') filter silently dropped NULL-stamped rows via SQL three-valued logic while reading as client-only exclusion. Rewritten as a NULL-safe positive allowlist: fail-closed for anything unstamped, and the A4 attribution contract now requires the future server-side claim/redeem flow to set the stamp and populate user_id (else its cells can never clear the k-anon floor).
D19	Every slot that emits ad clicks also emits FILLED + verified IMPRESSION, and the venue page credits placement 'inline'	Click-only slots create impossible cells (clicks with zero impressions) that inflate CTR and fill-to-view for the whole window. The app AdSlot shim now instruments fill + in-view impression (covers the feed), and the venue-detail card does the same, crediting placement 'inline' (its taxonomy slot) instead of the offer's configured placement so hero cells stop absorbing venue-page activity.
D20	Admin delivery cache is identity-keyed and manual refresh bypasses it	The 60s client cache was keyed by path alone; the merchant endpoint's path is identical for every non-admin caller, so on a shared browser merchant B could be served merchant A's cached numbers inside the TTL. The key now carries the merchant scope, and the Ad Delivery dashboard's Refresh button forces a network fetch (the button exists precisely to check for post-backfill rows). Range switches drop stale data before fetching so old-window numbers can never render under new-window labels.

4b. Dev enablement (automatic on merge)

Provisioning rides the dev deploy pipeline: the deploy-analytics-api job in .github/workflows/deploy-dev.yml now (a) creates analytics.ad_delivery_daily if absent (day-partitioned, NO expiry, schema from tooling/schemas/bigquery-ad-delivery-daily.json) and (b) creates/updates the analytics-ad-delivery-daily Cloud Scheduler job (01:30 UTC, after the 01:00 event-counts job; same self-actAs OIDC auth as the sibling jobs, see schedulerAuth.js). Both steps are idempotent, so merging this PR to dev is the whole flip-on: no manual gcloud steps. The scheduled run sends no body; the route's default 3-day lookback self-heals a missed firing.

Fallbacks if a step fails visibly in Actions: a BigQuery permissions error means the deploy SA needs one-time table-creation rights on the analytics dataset; bash tooling/scripts/setup-bigquery.sh lantern-app-dev remains the manual equivalent. An admin can also backfill any time: POST /analytics/scheduled/aggregate-ad-delivery-daily with { "lookbackDays": 90 } (integer 1-365; body validated strictly, unknown keys rejected).

Flip-on verification runbook (first run against real BigQuery)

Everything is covered by unit/route/component tests, live browser verification, and the full validation suite, EXCEPT actual execution against BigQuery (no emulator exists), so the first real run is the test for the MERGE SQL and the metadata extraction. The sequence below verifies each step and is safe to repeat: the MERGE is idempotent, the table is additive, and every dashboard fails soft.

1. Merge the PR to dev. The deploy workflow creates the table and arms the scheduler job (section 4b). Verify in the Actions run: "ad_delivery_daily created" (or "already exists") and "analytics-ad-delivery-daily armed".
2. Manual first aggregation as an admin: POST /analytics/scheduled/aggregate-ad-delivery-daily body { "lookbackDays": 90 }. Verify: 200 with a rowsAffected count. Zero rows is a valid outcome if no ad events exist in the window yet (the honest-events instrumentation only started emitting merchantId/audience on this branch).
3. Sanity-check the cells in the console: SELECT * FROM lantern-app-dev.analytics.ad_delivery_daily ORDER BY day DESC LIMIT 20. Eyeball: offer_id populated (not 'unknown' everywhere), placements in {hero, inline, feed, unknown}, unique_users >= 1.
4. Cross-check one day against raw events: SELECT COUNTIF(event_name='sponsored_ad_impression') FROM analytics.events WHERE DATE(timestamp)='<day>' AND environment='development' should be >= the summed impressions for that day in the rollup (the rollup drops only unattributable rows).
5. Re-run step 2 with the same body. Verify: rowsAffected is the same and the table row count did not grow (idempotency against real BQ).
6. Load Admin > Analytics > Dashboards > Ad Delivery and the merchant portal Overview: numbers render (or the freshly-provisioned empty state if step 3 legitimately found nothing).
7. The scheduler job is already armed by the deploy; check it fired the next morning (aggregated_at timestamps advance).

5. Live status

Phase	Status	Notes
Plan + issues	done	#692, #694, #695, #696, #697 filed 2026-07-22
A1 dedupe fix	done	adTelemetry.js module-scope window; regression tests in HomeView.adTelemetry.test.jsx
A2 inline impressions	done	InlineOffer wrapper in HomeView.jsx (fill on commit, useInViewOnce impression)
A3 payload completeness	done	merchantId, targetAudience, areaBucket on all ad events; registry docs updated
A4 claim contract	done	sourcePlacement/sourceSurface/merchantId metadata on offer_claimed/offer_redeemed (contract only)
B rollups + gate	done (code)	adDelivery.service.js + scheduled/admin/merchant routes + openapi; dev enablement checklist in 4b pending
C internal dashboard	done	AdDeliveryDashboard under Analytics > Dashboards > Ad Delivery; verified live in Chromium against the dev server (data, empty, and table states)
D merchant data	done	MerchantDashboard on real gated aggregates via merchantDeliveryService; verified live via auth-emulator merchant sign-in; suppressed/unavailable/empty states in Storybook stories
D2 merchant portal (canonical surface)	done	Operator clarified (2026-07-22) that the admin-based MerchantShell is the real merchant page. Overview tab gained a live Ad Delivery band (banner now scoped: venue metrics still sample); Offers list rows carry 30d delivery lines. Pure join/gate helpers moved to @lantern/shared/ads/delivery (one implementation for both apps). Admin viewers read the admin endpoint filtered by merchant with the same display gate applied, so they see exactly what the merchant sees. Verified live in Chromium.

The PR that delivers this theme closes the issues it actually finishes; later phases keep their issues open if they slip to a follow-up session.