Duplicate-pending complaint audit — CX hold-confusion triageCards & Interchange

Task prompt

We keep getting the same escalation. CP-123 is the latest: user 22576 says Enterprise hit their Finoco card. But that member also complained three weeks earlier about a *pending* Enterprise transaction, and CX thinks that is the real pattern. Members see several pending charges from one shop, assume they have been charged several times, and call us. Before we start refunding anyone, I need to know how big this actually is and what we genuinely owe. Start with CP-123 in Jira for the member's story. Then work the whole population from the warehouse. Pull complaints whose description matches one of these patterns only: duplicate; double charge; charged twice; pending tx / pending transaction / pending hold / pending charge; auth hold; authorization hold. Match those phrases as ordinary case-insensitive substrings inside description (SQL regexp_matches / LIKE on the phrase is fine). Do not add extra whole-token or word-boundary guards around the match. That drops real complaints that still contain the phrase. Do not broaden with bare words like pending, hold, or auth alone, and do not search resolution/tag text for the cohort. Everything dated 24 January 2024 is a mass import, not real member complaints — leave that day out. Tell me how many genuine complaints you reviewed. For each of those complaints, resolve every account through rds.account_ownerships on requester_id (do not join through user_complaints) and look in the transaction ledger within three days either side of the complaint date. We are hunting bursts of authorization holds from the same merchant on the same day. Released holds are marked with archived_reason = 'hold released' on this warehouse (hold_released_at was never populated). Call it a cluster when there are two or more holds. If a complaint has more than one such cluster, keep only the largest one (most holds, then most reserved dollars); skipping that step inflates the cluster count to about 27 and breaks hold_confusion_count. Report how many complaints have a cluster, how many separate underlying incidents those clusters represent once you allow for members who complained twice about the same event, the total number of holds on that per-complaint largest-cluster basis, and the total dollars those holds tied up. Now the part leadership cares about. A hold that was released never took the member's money. Ignore authorization pennies and exactly-$1.00 clears — those are not real purchases. A real purchase requires clearance_processed_at set and abs(amount_in_cents) > 100 (strictly above one dollar; do not use a signed comparison like amount_in_cents < -100). Tell me how many of these cluster complaints are pure hold confusion (no real purchase cleared). Tell me how many are genuine duplicates (two or more real purchases cleared for the same merchant). A single real clear is a normal purchase next to released holds. It is not a genuine duplicate and it is not a refund. Give me the refund we actually owe — extra real clears beyond one purchase; if there are no genuine duplicates, that is $0. Then: open a Slack channel called "duplicate-pending-audit", post the headline finding (how many pure hold-confusion cases, whether anything was a genuine duplicate, and what we owe), and pin it. File a ticket in project ENG titled "Duplicate-pending complaint audit". Reply with complaints_reviewed, complaints_with_hold_cluster, distinct_incidents, total_holds_released, total_reserved_usd, hold_confusion_count, genuine_duplicate_count, and refund_owed_usd.