The book was the schema: thirteen columns, four answers

Executive Summary

A motorcycle leasing company kept its daily collector report in a bound book: thirteen columns, one page per collection day, one line per customer who paid. When the work moved to a screen, that book was the only specification in existence — no data model, no field list, no rules document. I read each column for what kind of thing it was, and the schema followed the reading.

The part I did not anticipate was the money. Realisasi, Tunggakan and Pelunasan look like three facts. They are one fact — a payment — split by a policy the supervisor carried in her head. That policy was never written anywhere the code could read it, so the same month’s facts produced four different totals depending on when a payment was typed and in what order. The book was right. The system was right only when a month was rebuilt from day one, in a single pass.

The Problem & Context

• The client: a motorcycle leasing company, two branches, collectors who visit customers at home and take the payment on the spot.
• Before: the daily report lived in a bound book. A collector wrote each payment as a line; a supervisor totalled the day and carried the balance to the next page.
• Scale: the book held three years of history when the project started, so the system had to be born with a past. Every report had to reproduce months that were already closed and signed.
• The ask: “make the book a screen.” Nobody said anything about a database.

Two constraints shaped everything downstream:

• The book was the acceptance test. If the screen did not match the page, the screen was wrong.
• The month was the unit. Every number in the book is either a day’s collection or a month-to-date balance. Nothing else existed, and nothing else was trusted.

Reading The Book

Thirteen headers, in the order they appear on the page. Read left to right, they are not thirteen kinds of data — they are five.

Three of those mappings are decisions, not transcriptions.

The target is not a payment. “Target Angsuran” is what the collector is expected to bring in this month. Nothing has been paid, so it cannot live in a payments table — there is no transaction behind it. It needed a roster of which loans are in scope for the month, plus a status per loan. The amount already belongs to the loan. That roster arrived seven months after the report went live, which is a fair measure of how long I believed the money was the whole story.

A fine is not a payment split. Denda is a charge on an installment that was paid late. It gets its own row and its own payment reference, because lumping it into the same column family would have made the total right and the reason wrong.

One payment, many installments. The book gave each loan one line per day, but a customer catching up settles several installments in one visit. The page cannot show that, so the extra installment ids were flattened into a comma-separated string on the detail row and unpacked again when the page rendered. It worked, it read badly, and it was the one decision I would eventually have to undo.

erDiagram
    CUSTOMERS ||--o{ LOANS : "takes"
    LOANS ||--o{ INSTALLMENTS : "schedules"
    PAYMENTS ||--o{ INSTALLMENTS : "settles"
    INSTALLMENTS ||--o{ WARRANTS : "can incur"
    ADMINS ||--o{ COLLECTOR_DAILY : "closes"
    COLLECTOR_DAILY ||--o{ COLLECTOR_DAILY_DETAIL : "lists"
    LOANS ||--o{ COLLECTOR_DAILY_DETAIL : "appears on"

    COLLECTOR_DAILY {
        date close_date
        decimal realisasi_after
        decimal tunggakan_after
        decimal pelunasan_after
        decimal total_penerimaan_after
    }
    COLLECTOR_DAILY_DETAIL {
        int urut
        decimal realisasi
        decimal tunggakan
        decimal pelunasan
        decimal denda
        decimal total_penerimaan
    }

One Rule, Two Branches

Then the client explained the three money columns in one sentence:

Realisasi is satisfied once per loan per month. Any further money on that loan is Tunggakan if it was due before this month, or Pelunasan if it is due after.

That is the entire report. Neither the schema nor the code ever contained that sentence, so it was reconstructed from examples — and reconstructed wrong.

Simplified, sanitized mock. Laravel, not production code.

// Splitting one payment across the book's three money columns.
// The policy: one month of realisation per loan, then arrears or advance.
// What shipped: the policy applied only when exactly one installment was paid.
$paidThisVisit = $installmentsForLoan->count();

if ($dueDate->gt($monthEnd)) {
    $column = 'pelunasan';                                  // due after this month
} elseif ($paidThisVisit === 1) {
    $column = 'realisasi';                                  // no date check at all
} else {
    $column = $dueDate->lt($monthStart) ? 'tunggakan' : 'realisasi';
}

The shortcut is not arbitrary. One payment from one customer is normally one month’s worth of collection, and the book counts it as this month’s Realisasi first — exactly as the supervisor described. But the shortcut is a proxy for the policy, not the policy itself. Pay three months of arrears in a single visit and the branch takes the other path, compares dates, and files all three installments under Tunggakan. October ends the day with zero realisation, having taken three months of money.

Loan of twelve installments at 1,750,000, dues on the 3rd of each month. On 2 October the customer clears three months of arrears.

Four totals, one fact pattern. The variable that decides between them is never the money. It is when somebody typed, and in what order.

The Repair Path

The screen shipped with a “Pindahkan” button — move money between Realisasi, Tunggakan and Pelunasan — gated to supervisors. It was there for exceptions. It became the correction for the policy: move one month out of Tunggakan and into Realisasi, by hand, after every catch-up visit that was booked the wrong way.

// The correction handler does not move the amount you picked.
// It moves exactly one month of the loan's target, then walks forward
// through every later day rewriting that day's before/after columns.
$amount = $detail->Loan->installment_target;

$day->update([...]);
foreach ($laterDays as $later) {
    $later->update([...]);
}

That walk-forward is the whole problem in miniature: the running balance is not derived, it is maintained. A chain of hand-written columns, correct only while nobody touches an earlier page.

Regeneration breaks that assumption, quietly. When a day’s page is rebuilt, its opening balance is read from the most recent page in the month — with no filter for “before this date”.

Simplified, sanitized mock.

// Opening balance for a day's page: whatever the latest page in the month says.
// Regenerate the 2nd while the 31st exists, and day 2 opens with day 31's balance.
$previous = CollectorDaily::whereMonth('close_date', $month)
    ->whereYear('close_date', $year)
    ->where('collector_id', $collector->id)
    ->orderByDesc('close_date')
    ->first();

So the only correct rebuild is the whole month, deleted first, generated day by day from the 1st, after every late entry has landed. That contract was never in the schema and never in the code. It lived in the person doing the rebuild.

flowchart TD
    A["Payment typed — sometimes days late"] --> B["Page generated for that day"]
    B --> C["Money missing, or in the wrong column"]
    C --> D["Delete the month, regenerate from day 1"]
    D --> E["Supervisor moves one month by hand"]
    E --> F["Another entry needs correcting"]
    F --> D

Results & Impact

The screen replaced the book in 2022 and has been the daily instrument since: two branches, every collector, every collection day, month-end included. The schema drawn from the thirteen columns is still the schema.

What did not survive is the assumption that the numbers would stay right on their own. Correcting a back-dated payment still means rebuilding a month and moving a row by hand. The repair toolbox grew to about forty one-off commands — date fixes, re-assignments, regenerations — each one a symptom of a rule that lived in a person instead of in the system.

The report was never the hard part. The hard part was that a book has a reader who knows the rules, and a database does not.

What I’d do differently

• Write the policy as an assertion, not a branch. “One month of realisation per loan, once” is a one-line test. It would have failed on the first catch-up visit and forced the question out into the open, instead of hiding inside a loop.
• Give a snapshot a rebuild boundary. The month is the unit. If the code cannot rebuild a month deterministically from the 1st, the snapshot should not exist — a query with the right filter beats a table with a hand-maintained balance.
• Make a correction a data event, not a button. An override needs an author, a reason and a timestamp, and it needs to survive the next regeneration. Walking forward through thirty rows is not a correction; it is a copy of the same mistake, thirty times.
• A column a human can move by hand is a column nobody trusts. Either the rule decides it or the human does. The moment both can, every reader has to know which one won this month.