Instalments vs payments: a money model that adds up

Keep the plan and the money apart. Instalments are what the client agreed to pay and when: a deposit, maybe milestones, a final payment, each in percent or a fixed amount. Payments are money that actually arrived, into a real account, sometimes only part of what was due. Each instalment's status is calculated from both, never stored, so it cannot drift from the truth.

This is part 3 of the build log of the ZS Tech CRM. Part 1 modelled the quote, and part 2 hid its cost from people who should not see it. This part is about getting paid.

Why "mark as paid" is wrong

The first design had a button on each instalment: mark paid. It is the obvious interface and it loses money. Clients pay part of a deposit now and the rest next week. Some pay cash to a technician on site, some by InstaPay, some by bank transfer. A boolean cannot say how much arrived, when, how, or where it went.

So the build records a payment instead: amount, date, method (cash, InstaPay, transfer, cheque, other), the account it landed in, and optionally which instalment it pays. Partial payments are normal, not an edge case.

Instalments are the plan

create table public.instalments (
  id uuid primary key default gen_random_uuid(),
  deal_id uuid not null references public.deals(id) on delete cascade,
  label text not null,
  sort int not null default 0,
  mode public.term_mode not null default 'pct',   -- 'pct' or 'amount'
  value numeric(14,2) not null check (value >= 0),
  due_at date,
  note text
);

When a deal is marked won, the stage function creates a default plan if none exists: 50% deposit due today and 50% final due at the handover offset, which comes from the project's task types. Any instalment still without a due date is spread across the project timeline. A deal that needs a different plan is edited on the deal page.

Making the plan add up

A plan mixing percentages and fixed amounts rarely sums to the total by accident. Three small, unit-tested functions handle it:

• allocation(terms, total) sums the plan and reports what is left. The plan counts as balanced when the remainder is under one pound, so rounding never blocks a save.
• balanceOnFinal(terms, total) rewrites the last term as a fixed amount that absorbs whatever the others leave. In the interface this is the "put the rest on final" action.
• convertTermMode(term, to, total) switches a term between percent and amount without changing what it is worth.

Payments are the money

The record-payment dialog opens from the instalment you clicked, so the link is filled in, but it can be cleared. An unlinked payment still counts toward the deal's collected and outstanding totals; it just does not mark any instalment as paid. The dialog also remembers which account each method usually lands in, so cash goes to the cash box without anyone picking it every time.

Status is calculated, never stored

An instalment's expected amount depends on the deal total, which changes until the deal is won. Its paid amount depends on payments. Storing a status would mean updating it every time either changes, and forgetting once. So a view computes it:

case
  when t.total > 0 and coalesce(p.paid, 0) >= expected then 'paid'
  when coalesce(p.paid, 0) > 0                         then 'partial'
  when i.due_at is null                                then 'unscheduled'
  when i.due_at < current_date                         then 'overdue'
  else 'due'
end as status

The order of the checks is the rule: money received beats dates, so a partly paid instalment shows as partial even after its due date, and a client who has paid something is not shown as simply overdue. The same rule exists in TypeScript, with unit tests.

Accounts: where the money actually is

Owners do not ask "how much did we invoice". They ask "how much money do we have, and where". Every payment lands in an account (a cash box, a bank account, a wallet), every expense leaves one, and transfers move money between them. A balance is never stored; it is a sum:

a.opening_balance
  + (sum of payments into the account)
  - (sum of expenses paid from it)
  + (transfers in) - (transfers out)  as balance

A second view groups payments and expenses by month, which is the cash in versus cash out chart on the dashboard.

Quoted cost versus what the job really cost

Expenses can be tagged to a deal. That gives every project two costs: the one frozen on the quote when it was won (part 1) and the actual one from expenses. A per-deal finance view shows collected, outstanding, quoted cost, actual cost, actual margin, and the variance between them. This is the report that tells an installer which kinds of jobs actually make money.

Two money bugs worth knowing about

Floating point

19.99 multiplied by 50.5 is 1009.495, which should round to 1009.50. In JavaScript it comes out as 1009.4949999... and rounds to 1009.49. One piastre is nothing until the sum of instalments stops matching the total. The rounding helper nudges by a tiny amount in the direction of the sign before rounding, and a test pins that exact case:

/** Round to 2 dp, half-up, tolerant of binary float noise (19.99 x 50.5 -> 1009.50 not 1009.49). */
export const r2 = (n: number) => Math.round(n * 100 + 1e-9 * Math.sign(n)) / 100

Exchange rates

Supplier costs and expenses can be in another currency. Each record stores the currency and the rate on the day it was entered, and the EGP value is a generated column. When the rate changes tomorrow, last month's expenses do not move, and the project's actual cost stays what it was.

The short version

• Instalments are the plan; payments are the money. Never one table with a paid flag.
• Allow partial payments from day one.
• Calculate status in a view, with "money received" checked before dates.
• Every payment and expense touches an account, so balances are sums, not stored numbers.
• Round half-up with a float-noise guard, and test the exact numbers that broke.
• Store the exchange rate on the record, not just on a settings page.

Part 4 covers who can see all of this: per-module None, View, and Edit access, enforced by Postgres. If your company still tracks deposits in a spreadsheet, this is what I build on the CRM and ERP page.