The cost of debt recovery is correspondence. A clerk who writes the same letter fifteen times to fifteen debtors who have not paid is a clerk who could be doing something else. The letter does not change. The tone escalates on a schedule. The schedule is known in advance. This is a machine's work, not a man's.
The Dead Letter Office automates dunning — the sequence of increasingly firm correspondence dispatched to a debtor who has missed a payment or failed to respond. It does not negotiate. It does not apologise. It states the sum, the date it was due, the consequence of continued non-payment, and the address to which funds should be directed.
Each letter in a dunning sequence is written in one of four registers. The register escalates as the sequence progresses. It never de-escalates. A debt that has reached Pre-Legal does not receive a Formal notice unless a new sequence is opened by the Firm.
The opening notice. Factual, impersonal, professional. States the sum, the due date, and the Firm's banking details. Assumes the debtor may have overlooked the obligation. Does not threaten. Does not remind them of what the Firm can do. That comes later.
Typical trigger: 7 days after payment missed.
The second notice. Acknowledges that the first letter was received and not acted upon. States that this is the second correspondence on the matter. Notes that continued non-payment will result in escalation. Does not specify what escalation means — that specificity belongs to the next register.
Typical trigger: 14 days after Formal notice, no payment received.
The final demand. States explicitly that legal proceedings are being considered. Specifies the exact sum now due including accrued interest and the Firm's recovery costs. Sets a final date by which payment must be received to avoid further action. Short. Unambiguous.
Typical trigger: 21 days after Firm notice, no payment received.
Formal notice of intended legal action. Drafted to satisfy the pre-action protocol requirements of the courts. References the correspondence history by date. States that proceedings will be initiated on a specified date unless the outstanding sum is received in full. Reviewed by the Firm before despatch without exception.
Requires manual approval before despatch regardless of auto_despatch setting.
Letter templates use a minimal handlebars-style syntax. The renderer resolves variables at despatch time against the live debt record. Templates are stored as plain text in the database — no HTML, no rich text. The Firm writes letters, not web pages.
| Variable | Resolves to | Source |
|---|---|---|
{{debtor_name}} |
Full name of the debtor | debtors.name |
{{debtor_address}} |
Debtor's address for formal correspondence | debtors.address |
{{debt_ref}} |
First 8 characters of the debt UUID — used as the account reference | debts.id |
{{principal}} |
Original principal, formatted: £X,XXX.XX | debts.principal_pence |
{{outstanding}} |
Current outstanding balance including accrued interest, formatted: £X,XXX.XX | Computed at render time |
{{due_date}} |
Next scheduled payment date, or original due date if no schedule | repayment_schedule.due_date |
{{days_overdue}} |
Calendar days since the debt or instalment fell due | Computed: CURRENT_DATE - due_date |
{{despatch_date}} |
Date this letter is being despatched, formatted: DD Month YYYY | NOW() at despatch time |
{{final_payment_date}} |
Deadline stated in Final and Pre-Legal notices — 14 days from despatch | Computed: NOW() + INTERVAL '14 days' |
{{firm_name}} |
Scrooge & Marley | public/theme.json |
{{firm_address}} |
1 Cornhill, London EC3V 3ND | public/theme.json |
Three new tables. The despatch log has no DELETE policy — it is a permanent record and cannot be removed.
-- Letter templates (the named sequences and their steps)
CREATE TABLE IF NOT EXISTS dunning_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL, -- e.g. 'Standard Arrears — First Notice'
sequence_name TEXT NOT NULL, -- e.g. 'Standard Arrears'
sequence_position INTEGER NOT NULL, -- 1, 2, 3... within the sequence
register TEXT NOT NULL, -- formal | firm | final | pre_legal
subject_template TEXT NOT NULL, -- e.g. 'Account {{debt_ref}} — Notice of Arrears'
body_template TEXT NOT NULL, -- full letter body with {{variables}}
trigger_days INTEGER NOT NULL, -- days since last despatch (or referral date for step 1)
requires_approval BOOLEAN -- pre_legal always true; others configurable
GENERATED ALWAYS AS (register = 'pre_legal') STORED,
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(sequence_name, sequence_position)
);
ALTER TABLE dunning_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_templates" ON dunning_templates
FOR ALL USING (true) WITH CHECK (true);
-- Assignment of a dunning sequence to a specific debt
CREATE TABLE IF NOT EXISTS dunning_assignments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debt_id UUID NOT NULL REFERENCES debts(id) ON DELETE CASCADE,
sequence_name TEXT NOT NULL,
auto_despatch BOOLEAN DEFAULT false, -- must be explicitly enabled per debt
paused BOOLEAN DEFAULT false,
current_position INTEGER DEFAULT 1,
next_trigger_date DATE, -- set by cron after each despatch
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(debt_id) -- one active sequence per debt
);
ALTER TABLE dunning_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_assignments" ON dunning_assignments
FOR ALL USING (true) WITH CHECK (true);
-- Despatch log — permanent record, no DELETE policy
CREATE TABLE IF NOT EXISTS correspondence_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
debt_id UUID NOT NULL REFERENCES debts(id) ON DELETE RESTRICT,
assignment_id UUID REFERENCES dunning_assignments(id),
template_id UUID REFERENCES dunning_templates(id),
despatched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
recipient_email TEXT NOT NULL,
subject TEXT NOT NULL,
body_rendered TEXT NOT NULL, -- full rendered text at despatch time, immutable
resend_message_id TEXT, -- Resend delivery ID for tracking
status TEXT DEFAULT 'sent', -- sent | bounced | failed
approved_by TEXT -- 'auto' or admin identifier for manual approvals
);
ALTER TABLE correspondence_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_log_read" ON correspondence_log FOR SELECT USING (true);
CREATE POLICY "service_role_log_insert" ON correspondence_log FOR INSERT WITH CHECK (true);
-- No UPDATE or DELETE policy — the log is permanentA Vercel cron job fires daily at 08:00. It evaluates all active dunning assignments and queues any letters whose trigger date has been reached. It does not despatch — it queues. Despatch requires a second step.
-- Letters ready to queue (run by cron at 08:00 daily)
SELECT
da.id AS assignment_id,
da.debt_id,
da.current_position,
da.auto_despatch,
dt.id AS template_id,
dt.register,
dt.requires_approval,
dt.subject_template,
dt.body_template,
d.debtor_id
FROM dunning_assignments da
JOIN dunning_templates dt
ON dt.sequence_name = da.sequence_name
AND dt.sequence_position = da.current_position
JOIN debts d ON d.id = da.debt_id
WHERE da.paused = false
AND da.next_trigger_date <= CURRENT_DATE
AND d.status = 'active'
-- Do not queue if already despatched today (idempotency guard)
AND NOT EXISTS (
SELECT 1 FROM correspondence_log cl
WHERE cl.assignment_id = da.id
AND cl.despatched_at::DATE = CURRENT_DATE
);The cron job must not despatch a letter twice. Two mechanisms enforce this. First, the query above excludes assignments that have already produced a log entry today. Second, the cron sets a next_trigger_date on the assignment immediately after queuing — before Resend is called. If Resend fails, the letter is in the queue but not logged as sent; the administrator is notified and can retry manually. The assignment is not advanced until despatch is confirmed.
requires_approval column is a generated column — it is always
true when register = 'pre_legal' and cannot be overridden.
The cron job places pre-legal letters in the approval queue and stops.
It does not despatch them. An administrator must review and approve before the
letter leaves the Firm. This is not a configuration option.
| Item | Detail |
|---|---|
| Effort estimate | High — 14–18 days |
| New database tables | 3 |
| New admin routes | 3 pages, 5 API routes, 1 cron endpoint |
| External dependencies | Resend (email despatch — one env var, one API call). The Ledger (required). |
| New environment variable | CRON_SECRET — validates Vercel cron requests to prevent external triggering |
| Requires | The Ledger must be implemented and tested before this extension begins |
The template engine and trigger evaluation are straightforward in isolation. The complexity is in the guarantees: no letter despatched without a log entry, no log entry deleted, no trigger misfiring on a paused or settled debt, no pre-legal letter despatched without human approval. The Vercel cron pattern is well-documented but requires careful idempotency design — the query above handles the common case; edge cases (Resend timeout, partial failure) require explicit retry logic in the queue processor.