Building the Data Foundation: Direct Debit Core Schema

Release: v1.0.1

v1.0.1 lays the complete database schema for the Direct Debit collection service. This post walks through the design decisions behind the eight new tables and ten new enums that form the backbone of the entire DD lifecycle.

Why Start With the Schema?

The Direct Debit service is data-heavy by nature. Every step of the lifecycle — mandate setup, BACS submission, fund sweeping, clawback protection, alerting — has a clear data shape. Getting these structures right up front means every subsequent feature can be built against a stable, predictable contract.

The Tables

`mandate_invite_tokens`

Mandates begin with an invite. A letting agent triggers an invite for a tenant, which generates a unique token and a public-facing URL. The mandate_invite_tokens table tracks these tokens, their expiry, and their status (pending, completed, expired, cancelled).

This decoupling means the mandate form is stateless from the tenant's perspective — they follow a link, the token is validated, and the form flow begins.

`mandates`

The central table of the service. A mandate record holds:

Tenant PII — name, address, contact details
Bank details — account number and sort code (flagged for encryption at the application layer)
Collection configuration — amount, frequency, collection day of month (clamped to 1–28 at application layer)
External references — Modulr mandate ID, Modulr SUN
Lifecycle state — tracked via the mandate_status enum: draft → pending_submission → active, with suspended, cancelled, and failed as terminal/hold states
Mandate type — property (includes Right to Rent) or non_property

Every mandate is scoped to an orgId, enforcing strict multi-tenant isolation.

`collections`

Each time funds are collected against a mandate, a collections record is created. This table stores:

The BACS date pair: the collection date (when funds leave the tenant's account) and the receipt date (when they arrive)
The Modulr payment reference
The amount in pence (GBP)
Status: scheduled → submitted → collected, with failed and clawback as exception states
A reference to the associated holding_transaction once the sweep has occurred (stored as text to avoid a Drizzle circular FK issue — enforced at the application layer)

`holding_transactions`

A full ledger of every fund movement through the Griffin DD holding account. Transaction types cover the complete flow:

Type	Description
`sweep_in`	Funds swept from Modulr collection account into Griffin holding
`forward_out`	Funds forwarded from Griffin holding to the agent's client account
`clawback_debit`	Funds debited from holding due to a tenant bank reversal
`reserve_adjustment`	Manual or automatic adjustment to the clawback reserve

`clawback_reserves`

Each organisation has exactly one clawback reserve record (enforced via a unique constraint on orgId). The reserve tracks:

Current balance (in pence)
Configurable risk factor (used to calculate required reserve level)
Minimum reserve threshold
Last updated timestamp

Funds are only forwarded to the agent's client account once the reserve requirement is satisfied.

`alert_thresholds`

Per-organisation, per-alert-type thresholds. A composite unique constraint on (orgId, alertType) ensures there is exactly one threshold configuration per alert type per organisation. Alert types cover:

Mandate creation rate
Daily and weekly new DD collection amounts
Clawback reserve dropping below minimum
And more (8 types total)

`dd_alerts`

When a threshold is breached, an alert record is written to dd_alerts. The table tracks:

Alert type and severity (info, warning, critical)
Status: open → acknowledged → resolved
Acknowledgement metadata (who acknowledged it, when)
Whether notification emails have been sent

`bank_holidays`

BACS operates on UK banking working days only. The bank_holidays table stores UK bank holiday dates, scoped by region, and is populated by a background sync job. This table is the source of truth for all working day calculations in the service — collection date scheduling, receipt date calculation, and mandate form display.

Multi-Tenancy

Every table includes an orgId column. There are no shared records across organisations. Query paths that filter by orgId are indexed, as are status columns and external ID fields, to keep hot paths performant at scale.

Security Considerations

Bank account numbers and sort codes are stored in the mandates table but are explicitly marked for encryption at the application layer. The schema does not enforce encryption — this is a responsibility of the service layer before any write to the database.

CI

A GitHub Actions workflow (.github/workflows/ci.yml) was added alongside the schema. It runs on every push and pull request to main using Node 20, executes npm run build, and runs the Vitest test suite. This provides a baseline quality gate for all future work.

What's Next

With the data layer in place, the next releases will build on top of it: mandate invite flows, BACS collection scheduling, fund sweep orchestration, and the public-facing mandate form.