Design Chargeback Ingestion and CSV Delivery

Design a system that ingests card chargeback and dispute transaction events. It must validate and deduplicate incoming events, track their processing state, persist them, and export eligible transactions as CSV files for delivery to a card network over FTP or SFTP. Explain the data flow, state model, and recovery behavior. Pay particular attention to a database-read bottleneck during export generation. ### Constraints and Clarifying Questions - Clarify event volume, burst patterns, export deadlines, file-size limits, and acceptable recovery delay. No numerical targets are supplied. - Ask how upstream events are identified, whether events can be corrected or arrive out of order, and what makes a transaction eligible for export. - Treat the CSV schema, transfer protocol, and receiver's acknowledgment or duplicate-handling behavior as contracts to establish with the receiver. - Do not assume that a successful file transfer proves that every transaction in the file was accepted for business processing. ### Part 1 — Ingest and Track Events Propose the ingestion, validation, deduplication, persistence, and state-transition path. Explain how retries and concurrent processing affect correctness. #### What This Part Should Cover - A defensible identity for an event and a distinction between a duplicate and a legitimate update. - Durable records of accepted or rejected input and recoverable processing progress. - Consistent changes to processing state and any downstream work that must follow them. ### Part 2 — Select and Generate CSV Exports Explain how to select eligible transactions, assign them to files, and generate the CSVs. Describe how you would diagnose and address a database-read bottleneck without making the export inconsistent. #### What This Part Should Cover - A bounded selection and retrieval strategy that avoids unnecessary per-transaction reads. - Stable export membership and record versions despite concurrent ingestion or updates. - Coordination between multiple export workers and recovery after a worker fails. - Consistent CSV formatting and a way to identify the exact completed file. ### Part 3 — Deliver and Recover Design the FTP or SFTP delivery process. Explain a partial upload, a timeout after a possibly successful upload, and a crash before local delivery state is updated. #### What This Part Should Cover - Durable file identity, retry state, and an explicit distinction between generation, upload, and receiver acknowledgment. - A policy for ambiguous delivery that accounts for the receiver's actual capabilities. - Operational measurements that expose stalled processing, export backlogs, and delivery failures. ```hint Follow one event across a crash Track an event from durable ingestion to a file and then to the receiver. At each boundary, identify what evidence a restarted worker can use to continue without losing the event or inventing a new delivery. ``` ### What a Strong Answer Covers - A coherent state model connecting ingestion, export membership, file artifacts, and delivery evidence. - Database-read improvements that preserve completeness and consistency. - Explicit handling of retries and uncertainty at the external transfer boundary. ### Follow-up Questions - What happens when a correction arrives after the original transaction has been assigned to an export? - How would you tell whether the read bottleneck is excessive query count, an unsuitable index, or too much data per query? - What delivery guarantee can you offer if the receiver provides neither acknowledgments nor duplicate suppression?