A counting house that manages debt recovery by email and memory is not a counting house — it is a hope. The Firm currently has no structured record of who owes what, what has been promised, what has been paid, and what remains outstanding. Every engagement lives in a correspondence thread. That is the condition of a business that does not yet believe it will survive long enough to need records.
The Firm is older than most of its debtors' grandparents. It requires a ledger.
The Ledger is a structured debt management system within the admin dashboard. Each debt is a record: debtor name and contact, principal amount, date incurred, interest rate, repayment schedule (if any), payments received to date, outstanding balance calculated to the day, and current status.
The creditor who engaged the Firm is linked to each debt. The Firm's fee is tracked separately from the principal. Neither field is optional.
The public-facing component is a secure, token-authenticated status page a creditor can visit to see the current state of their engagement. The page shows exactly three things: status, amount recovered to date, and outstanding balance. Nothing more. The Firm does not explain itself to creditors — it reports to them.
Each creditor receives a unique URL containing a token. The token is generated by the Firm and delivered by correspondence. It does not expire by default, but can be revoked or set with an expiry at the Firm's discretion. The token grants read-only access to the single debt it references. It grants nothing else.
Five new tables. All in Neon PostgreSQL. All with row-level security enabled.
-- Debtors
CREATE TABLE IF NOT EXISTS debtors (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
address TEXT,
contact_email TEXT,
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE debtors ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_debtors" ON debtors
FOR ALL USING (true) WITH CHECK (true);
-- Debt accounts
CREATE TABLE IF NOT EXISTS debts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debtor_id UUID NOT NULL REFERENCES debtors(id) ON DELETE RESTRICT,
creditor_name TEXT NOT NULL, -- who engaged the Firm
principal_pence INTEGER NOT NULL, -- stored in pence — no floats near money
interest_rate_bps INTEGER DEFAULT 0, -- basis points per annum (500 = 5.00%)
date_incurred DATE NOT NULL,
date_referred DATE NOT NULL, -- when the Firm took it on
firm_fee_pence INTEGER DEFAULT 0, -- the Firm's recovery fee, tracked separately
status TEXT DEFAULT 'active', -- active | settled | written_off | disputed
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE debts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_debts" ON debts
FOR ALL USING (true) WITH CHECK (true);
-- Repayment schedule (optional — not all debts have agreed schedules)
CREATE TABLE IF NOT EXISTS repayment_schedule (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debt_id UUID NOT NULL REFERENCES debts(id) ON DELETE CASCADE,
due_date DATE NOT NULL,
amount_pence INTEGER NOT NULL,
status TEXT DEFAULT 'pending', -- pending | paid | missed | waived
paid_date DATE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE repayment_schedule ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_schedule" ON repayment_schedule
FOR ALL USING (true) WITH CHECK (true);
-- Payments received
CREATE TABLE IF NOT EXISTS payments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debt_id UUID NOT NULL REFERENCES debts(id) ON DELETE CASCADE,
amount_pence INTEGER NOT NULL,
received_date DATE NOT NULL,
method TEXT, -- cash | draft | cheque | wire
notes TEXT,
recorded_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_payments" ON payments
FOR ALL USING (true) WITH CHECK (true);
-- Creditor access tokens (for the public status page)
CREATE TABLE IF NOT EXISTS creditor_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debt_id UUID NOT NULL REFERENCES debts(id) ON DELETE CASCADE,
token TEXT NOT NULL UNIQUE, -- UUID v4, generated server-side
expires_at TIMESTAMPTZ, -- NULL = no expiry
revoked BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE creditor_tokens ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_tokens" ON creditor_tokens
FOR ALL USING (true) WITH CHECK (true);The outstanding balance is never stored — it is computed at render time from the principal, accrued interest, and payments received. Storing it would create a second source of truth that will eventually disagree with the first.
-- Outstanding balance query (run at render time, not stored)
SELECT
d.principal_pence,
d.interest_rate_bps,
d.date_incurred,
COALESCE(SUM(p.amount_pence), 0) AS total_paid_pence,
-- Accrued interest: principal × rate × (days elapsed / 365)
-- Calculated in pence throughout; round only at display layer
ROUND(
d.principal_pence
* (d.interest_rate_bps::NUMERIC / 10000)
* (CURRENT_DATE - d.date_incurred) / 365.0
)::INTEGER AS accrued_interest_pence,
d.principal_pence
+ ROUND(
d.principal_pence
* (d.interest_rate_bps::NUMERIC / 10000)
* (CURRENT_DATE - d.date_incurred) / 365.0
)::INTEGER
- COALESCE(SUM(p.amount_pence), 0)
AS outstanding_pence
FROM debts d
LEFT JOIN payments p ON p.debt_id = d.id
WHERE d.id = $1
GROUP BY d.id;NUMERIC arithmetic in PostgreSQL. JavaScript receives integers.
Formatting to pounds and pence (£X,XXX.XX) happens only at the display layer.
A float that represents £1,234.56 as 1234.5600000000002 has no place in a ledger.
The /account/[token] route is a public page — no login required — but the token must be valid, unexpired, and not revoked. If any of these conditions fail, the page returns a 404. It does not explain why. It does not invite the visitor to try a different token. The Firm does not assist people who arrive without proper credentials.
The page renders three values and nothing else:
| Field | Value shown | Notes |
|---|---|---|
| Status | Active / Settled / Disputed / Written Off | The status field from the debt record, formatted for a layperson. |
| Recovered to date | £X,XXX.XX | Sum of all payments received, formatted from pence. |
| Outstanding | £X,XXX.XX | Computed at render time. Includes accrued interest. |
The page does not show the debtor's name, the creditor's name, the debt reference, the interest rate, or any correspondence history. A token grants the right to know the current state of one engagement. It grants nothing else.
| Item | Detail |
|---|---|
| Effort estimate | Medium — 8–12 days |
| New database tables | 5 |
| New admin routes | 4 pages, 7 API routes |
| New public routes | 1 (token-gated) |
| External dependencies | Resend (for token delivery by email — optional at launch) |
| Required by | The Dead Letter Office, The Counting Room — both depend on this extension |
The schema and routes are straightforward. The complexity is in the outstanding balance calculation — interest accrual to the day, partial payments applied correctly, balance computed at render time not stored. The creditor token page introduces a new auth pattern (token-in-URL rather than session cookie) but is entirely self-contained and does not touch the existing admin auth.