Files
member-portal/tests/test_balance_backfill.py
inference-bot cad5897e9a metrics: self-heal missing OC balance days from the OC ledger
The 2026-11-12 hole was NOT a missed sample: the 03:15 run recorded
oc_balance_usd=1357.39, and the evening repair backfill's full-row
upsert of the then-current day — whose dict carries no balance key —
nulled it on conflict. Nothing ever revisited a day's balance, so the
hole was permanent.

Two fixes:
- upsert() can no longer erase a balance it did not measure: the
  conflict clause COALESCEs, so a NULL (a failed OC read) keeps the
  previously recorded value.
- the nightly snapshot heals any recent day (within a week, after the
  series' first recorded balance) whose balance is missing: the value is
  reconstructed from Open Collective's transaction ledger
  (balance(cutoff) = current - net flow after the 03:15 sample moment,
  ledger window clamped to the balance read), matching the daily sample
  semantics exactly. It is written balance-column-only, so membership
  and usage series are untouched. Days before 2026-11-08 stay NULL by
  design. Fail-soft: an OC failure prints and retries next night.

The reconstruction was verified to the cent against OC's own ledger:
full-ledger signed sum == stats.balance (1470.44 on 2026-11-13), and
1470.44 - 113.05 (net flow 2026-11-12 03:15 -> 2026-11-13 03:15:58)
== 1357.39, exactly the sample the 03:15 run had recorded.
2026-11-13 04:04:43 +00:00

196 lines
8.7 KiB
Python

#!/usr/bin/env python3
"""Regression tests for the OC-balance self-heal (the Nov 2026 chart hole).
Incident: the 03:15 snapshot on 2026-11-12 DID record oc_balance_usd=1357.39,
but a repair backfill run hours later rewrote the day through backfill()'s
full-row upsert — whose row dict carries no balance key — and ON CONFLICT wrote
NULL over it. Nothing revisited a day's balance afterwards, so the hole was
permanent and the money chart broke mid-line.
Fixes under test here:
* upsert() never erases a balance it did not measure (COALESCE on conflict);
* the nightly snapshot heals any RECENT day with a missing balance from OC's
transaction ledger (balance column ONLY — membership/usage never touched);
* days before the series' first recorded balance stay NULL by design;
* an OC failure is fail-soft: hole stays, next night retries, nothing raises.
Runnable:
* from the repo root: python3 tests/test_balance_backfill.py
* inside the portal app: PORTAL_CODE_DIR=/app/code python3 tests/test_balance_backfill.py
"""
import datetime as dt
import os
import sqlite3
import sys
import tempfile
_TEST_DB = os.path.join(tempfile.mkdtemp(prefix="balance-heal-test-"), "members.db")
os.environ["DB_PATH"] = _TEST_DB # must exist BEFORE metrics_snapshot is imported
for _cand in (os.environ.get("PORTAL_CODE_DIR"),
os.path.dirname(os.path.dirname(os.path.abspath(__file__)))):
if _cand and os.path.isfile(os.path.join(_cand, "metrics_snapshot.py")):
sys.path.insert(0, _cand)
break
import metrics_snapshot as ms # noqa: E402
TODAY = ms.today()
YESTERDAY = (dt.date.fromisoformat(TODAY) - dt.timedelta(days=1)).isoformat()
DAY_MINUS2 = (dt.date.fromisoformat(TODAY) - dt.timedelta(days=2)).isoformat()
DAY_MINUS5 = (dt.date.fromisoformat(TODAY) - dt.timedelta(days=5)).isoformat()
FAILED = []
def check(name, cond, detail=""):
if cond:
print(f" ok {name}")
else:
print(f" FAIL {name} {detail}")
FAILED.append(name)
def read_row(day):
conn = sqlite3.connect(_TEST_DB)
try:
conn.row_factory = sqlite3.Row
return conn.execute("SELECT * FROM metrics_daily WHERE day = ?", (day,)).fetchone()
finally:
conn.close()
def wipe():
ms.connect().close() # ensure the table exists (schema init)
conn = sqlite3.connect(_TEST_DB)
conn.execute("DELETE FROM metrics_daily")
conn.commit()
conn.close()
def stub_sources(**kw):
"""Replace every network source with fixed in-memory data."""
ms.from_sqlite = lambda: {"active": kw.get("active", 40), "total": kw.get("total", 71),
"joins": list(kw.get("joins", []))}
ms.from_listmonk = lambda: kw.get("subs", 71)
ms.listmonk_joins = lambda: list(kw.get("joins", []))
ms.from_matrix = lambda: kw.get("matrix", 65)
ms.oc_collective_balance = lambda: kw.get("balance", 123.45)
ms.spend_by_day = lambda since=None: dict(kw.get("spend", {}))
ms.operator_spend_by_day = lambda since=None: None if kw.get("op_spend") is None else dict(kw.get("op_spend") or {})
ms.tokens_by_day_model = lambda since=None: {k: dict(v) for k, v in (kw.get("tokens") or {}).items()}
def seed(day, **cols):
ms.upsert(day, {"spend_usd": None, "requests": None, "out_tokens": None,
"energy_wh": None, "co2e_g": None, "tokens_measured": None,
"tokens_estimated": None, "factor_revision": None, **cols})
# ----------------------------------------------------------------------
print("1. REGRESSION: a full-row rewrite without a balance never erases one "
"(the 2026-11-12 incident)")
wipe()
# The day as the 03:15 snapshot wrote it: membership + usage + a live balance.
seed(TODAY, spend_usd=3.38, requests=674, members_active=113, members_total=113,
members_new=2, newsletter_subs=122, matrix_space_members=36, oc_balance_usd=1357.39)
# Full backfill (the shape the 2026-11-12 18:42 repair ran): its today-branch
# rewrites the day with a dict that has NO oc_balance_usd key.
stub_sources(joins=["2026-11-01"], spend={TODAY: (3.38, 674)}, op_spend={TODAY: 0.9},
tokens={TODAY: {"greenpt/glm-5.3-flash": 379040}})
ms.backfill()
t = read_row(TODAY)
check("backfill rewrote the day (test is live)", t["members_total"] == 71, t["members_total"])
check("the recorded balance SURVIVED the rewrite", t["oc_balance_usd"] == 1357.39,
t["oc_balance_usd"])
print("2. snapshot() with a failed OC read keeps a balance recorded earlier that day")
wipe()
seed(TODAY, spend_usd=0.5, requests=10, members_active=40, members_total=71,
oc_balance_usd=1357.39)
stub_sources(spend={YESTERDAY: (5.0, 100), TODAY: (0.7, 20)}, op_spend=None, tokens={},
balance=None)
ms.snapshot()
t = read_row(TODAY)
check("today's row still refreshed (spend updated)", t["spend_usd"] == 0.7, t["spend_usd"])
check("earlier balance kept when the fresh read failed", t["oc_balance_usd"] == 1357.39,
t["oc_balance_usd"])
print("3. self-heal reconstructs a missing day from the ledger — balance column ONLY")
wipe()
seed(DAY_MINUS2, spend_usd=1.0, requests=50, members_active=39, members_total=70,
oc_balance_usd=1200.0)
# The hole: yesterday's row exists, everything but the balance.
seed(YESTERDAY, spend_usd=2.0, requests=60, members_active=39, members_total=70,
newsletter_subs=71, matrix_space_members=65)
seed(TODAY, spend_usd=0.5, requests=10, members_active=40, members_total=71,
oc_balance_usd=1470.44)
READ_AT = TODAY + "T03:15:58.123Z"
# Ledger after the two cutoffs: yesterday +73.05, day-before +113.05 (all days
# cumulative toward the anchor). Signed as OC stores them: CREDIT +, DEBIT -.
ledger = [
{"createdAt": DAY_MINUS2 + "T10:00:00.000Z", "type": "CREDIT", "amount": {"valueInCents": 5000}},
{"createdAt": DAY_MINUS2 + "T10:00:00.200Z", "type": "DEBIT", "amount": {"valueInCents": -500}},
{"createdAt": YESTERDAY + "T12:00:00.000Z", "type": "CREDIT", "amount": {"valueInCents": 7805}},
{"createdAt": READ_AT, "type": "DEBIT", "amount": {"valueInCents": -1000}}, # exactly at read_at: in the (C, read_at] window
]
ms.oc_fetch_transactions = lambda date_from, date_to: list(ledger)
ms.heal_missing_balances(1470.44, READ_AT)
y = read_row(YESTERDAY)
d2 = read_row(DAY_MINUS2)
t = read_row(TODAY)
check("hole filled: yesterday = anchor - net(yesterday 03:15 .. read_at)",
y["oc_balance_usd"] == 1470.44 - (7805 - 1000) / 100.0, y["oc_balance_usd"])
check("existing days untouched by the heal", d2["oc_balance_usd"] == 1200.0 and t["oc_balance_usd"] == 1470.44)
check("membership columns of the healed day intact",
y["members_active"] == 39 and y["members_total"] == 70 and y["newsletter_subs"] == 71)
check("usage columns of the healed day intact", y["spend_usd"] == 2.0 and y["requests"] == 60)
print("4. pre-history stays NULL — a hole BEFORE the first recorded day is not invented")
wipe()
seed(DAY_MINUS5, spend_usd=1.0, requests=5) # before the series' first balance
seed(DAY_MINUS2, spend_usd=1.0, requests=5, oc_balance_usd=1200.0)
seed(TODAY, oc_balance_usd=1470.44)
ms.oc_fetch_transactions = lambda date_from, date_to: []
ms.heal_missing_balances(1470.44, READ_AT)
d5 = read_row(DAY_MINUS5)
check("day before the first balance stays NULL (gap by design)", d5["oc_balance_usd"] is None,
d5["oc_balance_usd"])
print("5. OC ledger failure is fail-soft: hole stays, nothing raises, no zero written")
wipe()
seed(DAY_MINUS2, oc_balance_usd=1200.0)
seed(YESTERDAY, spend_usd=2.0, members_active=39, oc_balance_usd=None)
seed(TODAY, oc_balance_usd=1470.44)
def _boom(date_from, date_to):
raise RuntimeError("OC unreachable (test)")
ms.oc_fetch_transactions = _boom
ms.heal_missing_balances(1470.44, READ_AT) # must not raise
y = read_row(YESTERDAY)
check("hole left NULL for the next night", y["oc_balance_usd"] is None, y["oc_balance_usd"])
check("no zero written on failure", y["oc_balance_usd"] != 0.0)
print("6. write_balance on a day with NO row inserts balance + NULL everything else")
wipe()
ms.write_balance(YESTERDAY, 1357.39)
y = read_row(YESTERDAY)
check("balance written", y["oc_balance_usd"] == 1357.39)
check("membership/usage columns NULL (fail-closed, never zero)",
y["members_active"] is None and y["spend_usd"] is None and y["requests"] is None)
print("7. anchor=None (OC read failed this run) heals nothing")
wipe()
seed(DAY_MINUS2, oc_balance_usd=1200.0)
seed(YESTERDAY, oc_balance_usd=None)
seed(TODAY, oc_balance_usd=1470.44)
ms.oc_fetch_transactions = lambda date_from, date_to: list(ledger)
ms.heal_missing_balances(None, READ_AT)
y = read_row(YESTERDAY)
check("no heal without an anchor balance", y["oc_balance_usd"] is None, y["oc_balance_usd"])
print()
if FAILED:
print(f"FAILED: {len(FAILED)} check(s): {FAILED}")
sys.exit(1)
print("ALL CHECKS PASSED")