Ad Network seed script - Manual Test Plan

Field	Value
Branch / PR	feat/admin-and-merchant-portals (#976)
Environment	dev only: live lantern-app-dev Firestore + BigQuery lantern-app-dev.analytics.ad_delivery_daily; admin portal on deployed dev or local :3001
Build flags / config	.env.local must provide GOOGLE_APPLICATION_CREDENTIALS + GCP_PROJECT_ID/FIREBASE_PROJECT_ID
Build (commit)	1bfa4d31 (seed script) + the DATE-param fix commit on the same branch
Tester	agent (scriptable scenarios) + Mechelle (UI pass)
Date	2026-08-30

What is under test?

services/api/analytics/scripts/seed-ad-network.mjs (npm run seed:adnet / npm run seed:adnet:clean): seeds fake offers into Firestore offers and fake delivery rollup rows into BigQuery analytics.ad_delivery_daily, so the admin Ad Network section (Review, Delivery, Placements) and the Report Builder ad_delivery_daily source have data to render in dev.

Money guardrail. The rollup metrics are the designed future billing inputs, so every seeded id is prefixed seed-adnet-, every BQ row is environment='development', and clean deletes by that prefix only. Scenario 5 is the falsifying scenario: it exists to prove seeded offers can never reach a real user, and a nonzero result there reopens the whole design and mandates an immediate seed:adnet:clean.

Summary

#	Scenario	Result	Notes
1	Seed writes the expected Firestore offers	[x] pass	8 docs, statuses and fields verified
2	Seed writes the expected BQ rollup rows, dev-tagged only	[x] pass	128 rows, 0 funnel violations, 0 non-dev
3	Re-running seed is idempotent	[x] pass	replaced its own 128, 0 duplicate cells
4	Admin Ad Network tabs render the seeded data	[x] pass	Review + Delivery verified; found + fixed a latent API bug on the way (see Notes)
5	FALSIFYING: seeded offers are invisible to the user-serving path	[x] pass	prediction held: 0 leaks
6	Review-queue Approve works against a seeded offer	[x] pass	UI button to Firestore, end to end
7	Clean removes exactly the seeded data and nothing else	[x] pass	0 seed rows left, 17-row real baseline intact

Setup / preconditions

• Repo root, .env.local present with the keys above. Writes go to LIVE dev Firebase and dev BigQuery (shared with other sessions), so all ids are seed-adnet-* scoped.
• Record the non-seed baseline before first seed: SELECT COUNT(*) FROM analytics.ad_delivery_daily WHERE offer_id NOT LIKE 'seed-adnet-%' (expected 0 today; whatever it is, it must be identical after scenario 7).
• Clean state at any point: npm run seed:adnet:clean.

Scenarios

1. Seed writes the expected Firestore offers

• Goal: one seed:adnet run creates the full offer spread the admin screens need.
• Steps:
  1. npm run seed:adnet
  2. Read Firestore offers docs with ids starting seed-adnet-offer-.
• Expected: 8 docs: 3 pending_review (with moderation.autoOk: true), 2 active (future expiresAt), 1 active with past expiresAt (serializes as expired), 1 rejected (with reviewNote/reviewedBy), 1 draft. All carry createdBy: 'system:seed', a seed-adnet-merchant-* merchantId, and every field of OfferBaseShape.
• Verify (data): Firestore offers/seed-adnet-offer-*; spot-check status, moderation, expiresAt types (Timestamp, not string).

Result:    [x] pass
Actual:    8 docs; byStatus {pending_review:3, active:3 (one past-expiry), rejected:1, draft:1}; all OfferBaseShape + server fields present; expiresAt is a Timestamp; all 3 pending carry moderation.autoOk=true.
Evidence:  Admin-SDK read-back script, 2026-08-30 (agent run)
Follow-up: none

---

2. Seed writes the expected BQ rollup rows, dev-tagged only

• Goal: the Delivery dashboard's table gets a realistic 45-day history, and none of it can be mistaken for production data.
• Steps:
  1. After scenario 1, query analytics.ad_delivery_daily for offer_id LIKE 'seed-adnet-%'.
• Expected: rows spanning ~45 days ending yesterday, only for the 3 offers that "ran"; funnel shape sane per row (impressions <= fills, clicks <= impressions, redemptions <= claims); some rows with unique_users < 3 (k-anon exercise); zero seed rows where environment != 'development'.
• Verify (data): SELECT environment, COUNT(*) ... GROUP BY environment over seed rows returns only development; min/max day in range; a COUNTIF(impressions > fills OR clicks > impressions OR redemptions > claims) of 0.

Result:    [x] pass
Actual:    128 seed rows, days 2026-07-16..2026-08-29, 0 wrong-environment rows, 0 funnel violations, 0 duplicate cells, 10 rows below the k-anon floor (deliberate). Non-seed baseline at first seed: 17 rows.
Evidence:  BQ console query 2026-08-30 (job__QdhIQVhCSioy0A5dslZOt0XRRKI)
Follow-up: none

---

3. Re-running seed is idempotent

• Goal: a second run must not duplicate rows or docs.
• Steps:
  1. Note seed-row count and offer count from scenarios 1-2.
  2. npm run seed:adnet again.
  3. Re-count both.
• Expected: identical counts; no duplicate (day, offer) cells (SELECT day, offer_id, ... HAVING COUNT(*) > 1 returns nothing).

Result:    [x] pass
Actual:    Second run logged "Seeded 128 rows (replaced 128 prior seed rows)"; counts unchanged, duplicate-cell query returned 0.
Evidence:  seed:adnet output + BQ duplicate-cell count, 2026-08-30
Follow-up: none

---

4. Admin Ad Network tabs render the seeded data

• Goal: the point of the exercise: the admin screens are workable.
• Steps:
  1. Open the admin portal, Ad Network section.
  2. Review tab: expect the 3 pending offers listed with title/description/venue/placement.
  3. Delivery tab: expect non-zero totals, a chart with per-placement series, and a rows table (not the "table is empty" state).
  4. Analytics > Report Builder: pick the ad_delivery_daily ("Offer funnel") source, group by placement, run it; expect rows.
• Expected: all three surfaces show data; no error banners.
• Verify (data/console): Delivery tab's network call to /analytics/admin/ads/delivery returns rows.length > 0 and source: 'bigquery'.

Result:    [x] pass
Actual:    Headless pass on http://localhost:3012 (agent-stood-up portal, this branch, Agent Probe login). Review: 3/3 pending offers listed. Delivery: KPIs 2,955 impressions / 3,799 fills / 78% fill-to-view / 4% CTR / 97 claims / 47% redemption, 101 cells, placement chart incl. the unknown series, no error banners. Direct authed curl to the API returned the seed rows with source bigquery. Report Builder not separately driven; its ad_delivery_daily source reads the same now-verified table.
Evidence:  adnet-delivery.png / adnet-review.png (session scratchpad) + curl body
Follow-up: first pass showed empty despite correct data; root cause was the latent DATE-param bug below, not the seed

---

5. FALSIFYING: seeded offers are invisible to the user-serving path

• Goal: prove the claim that fake active offers cannot reach users, because their seed-adnet-venue-* venue docs do not exist and selectOffers drops venue-less offers.
• Prediction, stated up front: zero seeded offers returned. If any seeded offer comes back, the safety diagnosis is WRONG: stop, run seed:adnet:clean, and redesign before re-seeding.
• Steps:
  1. With seeded active offers in place, exercise the selection path an app user hits (GET /offers/active?lat=&lng= as a signed-in test user, or run selectOffers directly against dev Firestore with a real uid and any lat/lng).
• Expected: response contains no offer whose id starts seed-adnet-.
• Verify (data): the returned offer list, checked by id prefix.

Result:    [x] pass
Actual:    selectOffers against live dev Firestore returned 0 offers, 0 with the seed prefix. Prediction held: the missing venue doc drops seeded offers before geofence or audience filtering, so location cannot matter.
Evidence:  Admin-SDK probe script output, 2026-08-30
Follow-up: none

---

6. Review-queue Approve works against a seeded offer

• Goal: the queue's buttons drive the real admin endpoint end to end.
• Steps:
  1. In the Review tab, Approve one seeded pending offer.
• Expected: the row leaves the queue without an error banner; the doc's status becomes active, reviewedBy/reviewedAt set. (Scenario 5's guarantee keeps even this approved fake offer invisible to users.)
• Verify (data): Firestore offers/<approved id> fields.

Result:    [x] pass
Actual:    Approved "First-visit pastry on us" via the queue button; row left the queue (2 remain), no error banner; doc status active with reviewedBy/reviewedAt set (Agent Probe). Re-seed later reset it to pending_review.
Evidence:  headless run + Firestore read-back, 2026-08-30
Follow-up: none

---

7. Clean removes exactly the seeded data and nothing else

• Goal: the exit is as precise as the entrance; this is the control against collateral deletion.
• Steps:
  1. npm run seed:adnet:clean
  2. Re-run the scenario 1 and 2 queries, plus the non-seed baseline count from Setup.
• Expected: 0 seed-adnet-* offers, 0 seed BQ rows; non-seed BQ row count identical to the recorded baseline.
• Verify (data): the three counts.

Result:    [x] pass
Actual:    Clean removed 8 offer docs + 128 BQ rows; 0 seed rows remained; non-seed count still exactly 17 (the recorded baseline). Re-seeded afterwards so the surfaces stay populated.
Evidence:  seed:adnet:clean output + BQ counts, 2026-08-30
Follow-up: none

Notes

Found on the way: the delivery API could never return rows (fixed on this branch)

• The first delivery-tab pass showed the empty state while BigQuery provably held the rows. Root cause was in services/api/analytics/src/services/adDelivery.service.js: the read path bound fromDay/toDay as plain strings with an explicit 'DATE' parameter type, and @google-cloud/bigquery 7.9.4 silently serializes that combination as NULL (it expects its own typed wrapper object). day BETWEEN NULL AND NULL matches nothing, no error anywhere.
• Latent since the endpoint shipped; unobservable while the table was empty, which is exactly what this seed ended. The merchant-facing endpoint shared the bug via the same buildFilters.
• Fix: dates stay STRING params and the SQL casts them (CAST(@fromDay AS DATE)); regression unit test added asserting no string param ever carries a DATE/TIMESTAMP type. Post-fix: admin read returns all rows, merchant-gated read works and suppresses exactly the 10 below-floor cells.
• The TIMESTAMP-typed params in bqMetrics.service.js are safe: they receive Date objects, which the client serializes correctly (verified by probe).

(operator remarks land here)