A collections data model that survives five lakh accounts

Scale in collections is not mainly about traffic. It is about the number of rows that accumulate behind each account: every contact attempt, every message receipt, every disposition, every flow step, every audit entry. A book of five lakh accounts worked for a year is a few hundred million rows of history, and the parts of the system that get slow are the parts nobody thought of as the product.

The account is not the unit

The first modelling mistake is treating the borrower as the unit of work. A person can hold several loans, and the same phone number can belong to two customers in a badly maintained file. Recovery is worked per loan, but contact is experienced per person, and a system that cannot hold both ideas at once will either call the same person three times in a morning or fail to notice that they cured one loan while defaulting on another.

History grows without limit, and has to be designed for

• Contact history is the largest table you will have, and it is written constantly and read narrowly. It wants partitioning by time, not one table growing forever.
• Audit entries are append-only and never updated. Treat them that way and they stay cheap.
• Recordings and transcripts do not belong in the database. Store the pointer, not the payload.
• Anything you might want to report on by month should carry the month in a way that does not require scanning a year of rows to find out.

The queries that get slow are the ones nobody benchmarked

Everyone tests the borrower list. Nobody tests what happens when a supervisor opens one borrower with four hundred contacts against them, or when a report asks for kept-promise rate by bucket by campaign for a quarter, or when the dialer asks which of two lakh accounts are dispatchable right now. Those three shapes, the deep single record, the wide aggregate and the hot eligibility check, are the ones worth designing around.

Isolation between clients is a design decision, not a setting

If you work books for more than one lender, the separation between them has to be structural rather than a filter somebody remembered to apply. Isolation by schema or by database makes a wrong query return nothing instead of returning another lender data, which is the difference between a bug and an incident.

The questions to ask before the pilot, not after

• What is the largest single table after a year at full volume, and what maintains it?
• How does a borrower with four hundred contacts render, and how long does it take?
• Can a report for last quarter run while the dialer is at peak, without slowing it?
• If a client leaves, what does extracting and then deleting their entire book actually involve?