2026-06-10 — Issue #576 GitHub billing receipt-day migration: post-merge cleanup notes

Outcome: Code complete on fix/576-github-billing-receipt-day. GitHub subscription cost now comes from receipt-day rows sourced from invoice_actuals (one row per receipt email with a parsed subscription component, full amount on the day the receipt arrived) instead of daily proration from github_account_snapshot x dim_github_plan_pricing. This file documents the manual post-merge cleanup that the code change cannot do by itself, plus validation queries. No BigQuery DML was run as part of the branch — everything below is operator-run, after merge.

What the code change does NOT do

fact_cost_line_items and fact_cost_attributed are incremental MERGE tables keyed on source_line_item_id. A MERGE only inserts/updates rows present in the source query — it never deletes rows that stopped being emitted. The old prorated rows (May 10 → June 9, ~$0.4666/day github_plan + copilot_plan) therefore stay in both tables until they are explicitly removed.

Post-merge cleanup (in order)

1. Merge → wait for dataform-flat sync

After the PR merges to dev, the Sync Dataform Flat Branch workflow mirrors services/dataform/ to the dataform-flat branch that Dataform GCP compiles from. Verify it completed before any manual invoke:

gh run list --workflow=sync-dataform-flat.yml --branch=dev --limit=1

2. Delete the prorated GitHub subscription rows (both tables)

usage_unit='seat-months/day' is unique to the prorated branch — receipt rows carry NULL usage_unit, metered rows carry GitHub's own unit strings — so it is the safest predicate. Run both:

DELETE FROM `lantern-app-dev.billing_norm.fact_cost_line_items`
WHERE vendor = 'github' AND sku = 'subscription' AND usage_unit = 'seat-months/day';

DELETE FROM `lantern-app-dev.billing_attrib.fact_cost_attributed`
WHERE vendor = 'github' AND sku = 'subscription' AND usage_unit = 'seat-months/day';

Expected row counts (as of 2026-06-09 data): ~2 rows/day since 2026-05-10 in each table (~62 rows each). Sanity-check first if desired:

SELECT COUNT(*) AS n, MIN(DATE(usage_start)) AS first_day, MAX(DATE(usage_start)) AS last_day
FROM `lantern-app-dev.billing_norm.fact_cost_line_items`
WHERE vendor = 'github' AND sku = 'subscription' AND usage_unit = 'seat-months/day';

3. Run the pipeline so the receipt rows land

Either wait for the daily billing-daily cron (03:30 PT) or trigger a one-off compile + invoke (full snippet in services/dataform/README.md § Daily run — remember invocationConfig.serviceAccount: lantern-app-dev@appspot.gserviceaccount.com, the org strict-act-as policy 400s without it).

A full refresh is NOT required: the receipt branch has no incremental window (it re-reads invoice_actuals in full every run), so a normal incremental invocation inserts the receipt rows via MERGE. If a from-scratch rebuild is ever wanted instead of step 2's targeted DELETE, POST the workflowInvocation with "invocationConfig": {"serviceAccount": ..., "fullyRefreshIncrementalTablesEnabled": true} — note that re-reads the entire GCP billing export, so prefer the DELETE.

4. Redeploy ingest-github (env var is path-gated)

The GITHUB_COPILOT_PLAN=pro → pro_plus fix lives in deploy-dev.yml, but deploy-ingest-github-job only runs when services/jobs/ingest-github/** (or shared-deps) changes — editing the workflow file alone does not trigger it. Use the deploy workflow's force-all dispatch input (or fold the next ingest-github change in). Until it redeploys, daily snapshots keep recording copilot_plan_type='pro'. Snapshots from 2026-05-10 → redeploy keep the wrong tier in history; cost no longer derives from them, so this is cosmetic — do not bother rewriting them.

Validation queries (after steps 2-3)

-- (a) Zero prorated rows remain (run against both tables)
SELECT COUNT(*) AS should_be_zero
FROM `lantern-app-dev.billing_norm.fact_cost_line_items`
WHERE vendor = 'github' AND usage_unit = 'seat-months/day';

-- (b) Receipt rows present and keyed by raw_email_id.
-- Expected as of 2026-06-10: exactly ONE row — 2026-05-04, $3.20
-- (see "Known data gaps" for why May 28 and Feb/Mar/Apr are absent).
SELECT source_line_item_id, DATE(usage_start) AS receipt_day, cost_usd,
       JSON_VALUE(labels, '$.invoice_period_start') AS period
FROM `lantern-app-dev.billing_norm.fact_cost_line_items`
WHERE vendor = 'github'
  AND JSON_VALUE(labels, '$.source_kind') = 'subscription_receipt'
ORDER BY usage_start;

-- (c) June-to-date GitHub = SUM(June github_usage_raw net) + June receipts.
-- Currently both $0.00 — the Overview card should show $0 from BQ, with
-- the admin UI falling back to the CF live figure per the new merge rule.
SELECT ROUND(SUM(cost_usd), 4) AS june_github
FROM `lantern-app-dev.billing_attrib.fact_cost_attributed`
WHERE vendor = 'github' AND DATE(usage_start) >= '2026-06-01';

-- (d) Daily chart shape: spike on 2026-05-04 ($3.20), metered-only
-- elsewhere — no flat ~$0.4666/day band.
SELECT DATE(usage_start) AS day, ROUND(SUM(cost_usd), 4) AS cost
FROM `lantern-app-dev.billing_attrib.fact_cost_attributed`
WHERE vendor = 'github' AND DATE(usage_start) >= '2026-05-01'
GROUP BY day HAVING cost != 0 ORDER BY day;

-- (e) Reconciliation: 2026-06 github estimated_usd drops $4.20 → $0
-- (until a June receipt or metered cost lands); 2026-05 estimated now
-- includes the $3.20 receipt row.
SELECT invoice_period_start, estimated_usd, invoiced_usd, drift_usd, is_drift_alert
FROM `lantern-app-dev.billing_marts.invoice_reconciliation`
WHERE vendor = 'github' ORDER BY invoice_period_start DESC;

-- (f) Idempotency: trigger the workflow invocation a second time, then
-- confirm no duplicate receipt rows (receipt rows key on raw_email_id).
SELECT JSON_VALUE(labels, '$.raw_email_id') AS email, COUNT(*) AS n
FROM `lantern-app-dev.billing_norm.fact_cost_line_items`
WHERE vendor = 'github'
  AND JSON_VALUE(labels, '$.source_kind') = 'subscription_receipt'
GROUP BY email HAVING n > 1;  -- expect zero rows

-- (g) Regression: Anthropic / Cloudflare / GCP monthly totals unchanged
-- vs pre-cleanup (only github rows were touched).
SELECT vendor, DATE_TRUNC(DATE(usage_start), MONTH) AS month, ROUND(SUM(cost_usd), 2) AS total
FROM `lantern-app-dev.billing_attrib.fact_cost_attributed`
WHERE vendor != 'github'
GROUP BY vendor, month ORDER BY month DESC, vendor;

Known data gaps (expected, not caused by this change)

• May 28 receipt ($48.97, raw_email_id 19e6fcfb53991ace) has no parsed breakdown — its only invoice_actuals capture (2026-05-29) carries NULL subscription_charge_usd, so no receipt row is emitted for it and 2026-05 still under-reconciles by its ~$39 subscription component. The other GitHub emails got their breakdowns from later parser re-captures (2026-05-26); a re-run of ingest-invoices over that email should backfill the breakdown, and the receipt row then appears on the next pipeline run automatically (no code change needed). Worth checking why the 05-29 capture parsed without a breakdown if a re-run doesn't fix it.
• Feb/Mar/Apr GitHub invoices intentionally do NOT get receipt rows — their entire periods predate GitHub's metered-ingest floor (2026-05-01), so invoice_synthetic_line_items already emits them in full (subscription + metered + tax segments). The receipt branch gates on the exact complement of the synthetic view's gap_invoices filter, so every invoice is covered exactly once.
• No June GitHub receipt exists in invoice_actuals yet (expected ~06-28 based on the billing cycle; the May 4 receipt was a mid-cycle tier-upgrade pro-rata). If one shows up in Gmail but not in BQ, that is an ingest-invoices lag/parse issue, separate from this migration.