Juniper Villa sells villas on installment plans, and was tracking sales, staged payments, interest and customer follow-up across manual spreadsheets — error-prone, and hard to audit when a figure was questioned. We designed and built a single system covering the whole cycle: sales, scheduled installments, interest accrual, advance credit and customer communication.
Spreadsheets do not survive interest
Selling a villa on an installment plan is not a single transaction. It is a schedule: a sale price, a deposit, a run of staged payments, a grace period on each one, and interest that starts accruing the moment a payment is late. Multiply that by every villa in every project, and the question "who owes what today" stops having an obvious answer.
Juniper Villa was tracking all of it manually. That works until someone queries a figure. Then you are reconstructing history from a spreadsheet that records the current state and not how it got there — and an interest calculation nobody can retrace is an interest calculation nobody can defend.
What they needed was not a better spreadsheet. It was one system that held the whole cycle and could always show its working.
Status is a question, not a column
The tempting design is a status column on each installment — paid, overdue, partially paid — updated whenever something changes.
We did not do that, and it is the decision the rest of the system rests on.
A stored status is a claim about the past that has to be maintained forever. Every payment, every waiver, every change of terms is another place it can drift, and when it drifts it is silently wrong: the row says paid, the ledger disagrees, and nothing alerts anyone. On a financial system that is not a bug you find in testing. It is a bug you find in an argument with a customer.
Instead, status is computed from the underlying payments on every read, through database views. There is no flag to go stale, because there is no flag. "Overdue" is not a value someone remembered to write — it is what the ledger says, right now, every time you ask.
The cost is real: computing on read is more work than reading a column, and the views have to be written carefully to stay fast. We took that trade deliberately. A system that is slightly more expensive to query is a fair price for one that cannot quietly disagree with itself about money.
All-or-nothing, enforced where it counts
Recording a payment is not one write. It allocates across installments, touches advance credit, may settle accrued interest, and leaves an audit entry. If that sequence fails halfway — a dropped connection, a constraint violation — a naive implementation leaves the ledger in a state that matches no reality: money recorded against one installment and not another.
Those operations run as PL/pgSQL functions inside the database, as single transactions. Either every step lands or none of them do. Interest recalculation and reminder queuing work the same way.
The reason for putting this in the database rather than the application is that the database is the last line. Application code can be bypassed, refactored, or called from somewhere new. A constraint that lives with the data applies to everything that touches the data, including whatever gets written next year.
The same reasoning drives access control. Row Level Security policies sit on every table, so role-based access is enforced at the data layer rather than only in the code paths someone remembered to guard.
Automation with a person in front of it
The system queues customer reminders automatically — who is due, who is overdue, what the follow-up position is.
It does not send them.
Every reminder goes to an approval queue where staff choose the template and read the rendered message before it goes anywhere. That is a deliberate constraint, not an unfinished feature. Automated chasing is useful right up to the moment it emails the wrong person about the wrong amount, and a payment reminder sent in error costs more trust than a reminder sent a day late. The machine decides what is worth flagging; a person decides what gets said.
What it replaced
One source of truth for who owes what, when, and how much interest applies — with a full audit trail behind terms changes, interest waivers and cancellations, so any figure on the screen can be traced back to the events that produced it.
Each client runs on a separate database, so data from one is not merely filtered away from another — it is not in the same place.
