Bill-pay lifecycle: reconcile paid bills to ledger transactionsReconciliation & Settlement

Task prompt

Priya (Bills PM) needs the paid-vs-outstanding tie-out before Zetaverse ships. The Bills calendar and history spec in Confluence ('4.3.4 Bill Cal & History') is clear: a paid bill must show the matching settled debit in history — start there for the lifecycle definitions. Reconstruct the lifecycle buckets from the bill-payments table in the warehouse: outstanding/scheduled = status 'pending'; paid/history = status 'completed'. For every completed payment, match it against the ledger transactions and work out whether the ledger genuinely shows the matching settled debit the spec calls for — count it reconciled only when it does. One thing you do need to know about the ledger: it soft-deletes rather than removing anything, so when a payment is reversed, backed out, or its bill is deleted, the row stays behind carrying an archive marker. Those rows are not real activity — discount them everywhere in this, including when you are deciding whether a payment has any ledger presence at all. And a payment can settle across more than one row. Flag phantom_paid (no real ledger activity behind the payment) and amount_mismatch (there is activity, but it doesn't tie out). Also total state-machine contradictions: completed with canceled_at, completed with user_skipped_at, expired/canceled with processed_at, and orphan status='processed'. And don't just hand me the phantom-paid number — Priya will ask why those bills think they're paid when the ledger disagrees, and I need a real mechanism, not 'data quality'. That Confluence page lists the engineering tickets that shipped with the bill calendar work; chase them down and tell me what actually causes it. When you've got it: open a Slack channel called "bill-pay-paid-reconcile-audit", post a summary with the headline numbers and exception totals and pin it; file a Bank Operations ticket titled "Bill-pay paid transaction reconcile — ledger tie-out"; and reply with outstanding_scheduled_count, completed_bill_payments, paid_reconciliation_rate_pct, phantom_paid_count, amount_mismatch_count, state_contradiction_count, and phantom_paid_root_cause — a few sentences on the mechanism, naming the tickets you pinned it on.