Duplicate-funding R01 lock cohort auditRisk & Fraud

Task prompt

We've got a stack of CX escalations from members who got locked out right after we shipped (and then fixed) an onboarding funding bug: the app re-showed the 'fund your account' step to people who had already set funding up, so a second, identical account-funding deposit went out, hit a source account for money that wasn't there, and came back R01 — and because it was a repeat funding attempt, our auto-lock tripped. Engineering is already tracking the cleanup in Jira as ENG-259 ("Unlock all accounts that were locked due to ACH return for R01") — pull it up for context first. Leadership does NOT want the raw bounce count (that number is huge and misleading); they want the real remediation picture: how many members this actually happened to, what it exposed, and how many were genuinely locked out because of it. Pull this from the warehouse (the transfers and accounts tables). First isolate the double-funding cohort: account-funding deposits (go by the transfer's account-funding flag — don't add a destination-type or counterparty filter) that came back R01 where an earlier identical-amount account-funding transfer was initiated on the same account within the 21 days before it. Match on when each transfer was initiated (its created_at), and count that earlier leg whether or not it ever settled — the flow re-fired the deposit even when the first attempt was later canceled, so don't require the first leg to have gone through. Report the distinct account count (cohort_count) and the total dollars that bounced on those duplicates (returned_exposure_usd — gross, since an R01 means the money never actually moved). Then the harm, and this is the part leadership cares about. Go by whether the account was actually hard-locked — the account-lock timestamp and lock reason on the account record (za3_locked_at / lock_reason) — not whether it later closed. Most of these accounts have since closed for all sorts of unrelated reasons (inactivity, a review, the 2025 sunset), and a plain closure with no lock on it is not a lockout, so don't count those. Tell me how many of the cohort carry that hard lock (locked_count). A batch of those were only swept up in the 2025 product sunset, which has nothing to do with this bug — break that out (sunset_locked_count). The rest are candidates for the real remediation list, but check one more thing before you count them: the lock has to be one this bounce actually caused. The auto-lock trips in the wake of the return — within a few months of the bounce. So count a lock as this bug's only if it landed within roughly six months of the returned duplicate. A hard lock that shows up a year or more after the bounce, with no fresh returned deposit anywhere near it, is a separate investigation, not this bug's lockout, so leave it out. Give me the count of the ones that survive that check (bug_lockout_count), the dollars that bounced on just those accounts (bug_lockout_exposure_usd), and their account ids (ascending, comma-separated, no spaces, as bug_lockout_account_ids). Close with the root cause and the fix — the idempotency guard on funding initiation plus not hard-locking a brand-new account on a single R01, and the fraud-vs-customer-harm tradeoff. Then deliver it: open a Slack channel called "duplicate-funding-r01-lockout-audit", post a summary with the cohort count, the true bug-lockout count and dollars, and your remediation recommendation, and pin it; and file a NEW Engineering follow-up ticket in Jira (create a new issue — do NOT just comment on ENG-259) titled "Duplicate-funding R01 auto-lock remediation" capturing the remediation list. Then reply with exactly one JSON object as the final element of your reply, with exactly these fields: cohort_count, returned_exposure_usd, locked_count, sunset_locked_count, bug_lockout_count, bug_lockout_exposure_usd, bug_lockout_account_ids.