Bill underfunded-alert conformance auditSubscriptions & Billing

Task prompt

ENG-7196 is about to ship a fix to the Bill Underfunded alert, and I need the blast-radius sign-off before it goes out. Start from the Confluence page "Bill Reserve & Underfunded/Catch-up/etc" — that page is the underfunded spec, so read it properly rather than inventing your own rules — and pull up the ENG-7196 Jira ticket for the fix context. What I need from you: for smart-reserve bills, recompute which ones are underfunded under the Confluence spec, then compare that against the buggy live logic the ticket is fixing. The live bug is that projected inflow counts automations and recurring transfers that land after the next due date, which can make a bill look funded when the spec says it is not. The blast radius is that false-FUNDED set — bills the spec calls underfunded but the buggy path calls funded — plus how many distinct accounts they touch and how many cents short they are under the spec. Also give me the full spec-underfunded population size, the total spec shortfall in cents across those underfunded bills, and how many smart-reserve bills the spec says we should be tracking (the page's "should I care about this bill" rule). Do not invent filters the Confluence page does not support, and do not drop filters it does require. Map the page's inflow / outflow / tracked-bill language onto the warehouse yourself — especially which inflow events are allowed to land before the due date, which is the whole point of the fix. To keep this efficient and avoid combing the whole schema, work directly against these tables (catalog zeta3, schema rds) instead of running SELECT * or dumping full table schemas. The bills live in rds.bills (with reserve_mode and a deleted_at that marks superseded versions) and their scheduled payments in rds.bill_payments (each carrying a due_date and status). Each bill's reserve pocket is the BillReserve row in rds.subaccounts, linked back to the bill by bill_id. Money feeding that reserve comes from rds.automation_disbursements (each tied to a schedule row in rds.automation_instances, which carries its own process_on date) and from rds.recurring_transfers. Work out from the Confluence spec which of those rows actually count toward projected inflow and outflow, which payments make a bill 'tracked', and which bill versions to keep — that judgment is the whole point of the audit, so don't assume it. A bill counts as tracked only when it has at least one still-pending payment that has not been canceled or skipped; its window due date is the earliest such pending due date, and its projected outflow is the total of the pending payments falling on or before that window date (not every payment on the bill). Projected inflow is the reserve's balance plus the automation disbursements and recurring transfers that the spec says qualify, and the shortfall is outflow minus inflow summed over the underfunded bills only. You do NOT need the ticket's comment history, changelog, or page-version history, so don't fetch them. Compute the numbers in as few SQL queries as possible, then immediately do the write-backs before you run out of room. When you have the numbers: open a Slack channel called "bill-underfunded-alert-audit", post a summary with the headline figures and pin it; file a Jira ticket in the Bank Operations project (project key BO) titled "Bill underfunded alert conformance — ENG-7196 blast radius"; and reply with false_funded_bill_count, false_funded_account_count, false_funded_shortfall_cents, spec_underfunded_bill_count, total_spec_shortfall_cents, and tracked_smart_bill_count.