All Docs
FeaturesagentOS Direct DebitUpdated March 12, 2026

v1.0.2 — Laying the Foundation: Direct Debit Core Schema

Laying the Foundation: Direct Debit Core Schema

Release: v1.0.2

Before a single mandate can be created, a collection submitted, or an alert fired, the service needs a solid data model to underpin everything. That's exactly what v1.0.2 delivers: the complete Drizzle/Postgres schema for the Direct Debit domain.

Every feature in the service — mandate setup, BACS collections, fund sweeping, clawback reserves, alerting, fraud prevention — has a table (or tables) defined here. This is the bedrock.

What Was Built

Mandates

The mandates table is the central entity of the service. It records every DD mandate raised between an agent and a tenant, storing status, scheduling configuration, and linkage to the owning organisation.

Collections

The collections table tracks each BACS collection request submitted against a mandate — capturing the requested amount, the BACS submission date, and the outcome (pending, successful, failed, clawed back).

Fund Sweeps

fund_sweeps records every transfer of collected funds from the Modulr collection account into the Griffin DD holding account. This table provides a full audit trail of the sweep stage of the fund flow.

Clawback Reserves

The clawback_reserves table maintains the running reserve balance per customer. The reserve protects the service against tenant banks reversing DD payments after funds have already been forwarded. The schema supports configurable risk factors and minimum thresholds.

Alerts & Alert Thresholds

Two tables work together for alerting:

  • alerts — stores individual alert events as they are triggered (e.g. mandate creation rate exceeded, daily collection amount breached).
  • alert_thresholds — stores the per-customer configuration that determines when an alert fires. Thresholds include mandate creation rate, daily new DD collection amount, weekly collection amount, and clawback reserve minimum.

BACS Working Days

The bacs_working_days table is a reference table populated with UK banking working days. It is the data source for all BACS collection date calculations — ensuring the service correctly accounts for weekends and UK bank holidays when scheduling collections and presenting date pairs to tenants.

Mandate Form Tokens

The mandate_form_tokens table manages the short-lived, unique tokens embedded in the links sent to tenants. Each token is tied to a specific mandate invitation, enabling a fully public-facing form flow without exposing internal IDs.

Video Verifications

The video_verifications table records metadata and storage references for the video verification step of the mandate form. When a tenant records their verification video, the reference is persisted here for fraud protection and audit purposes.

ID Verifications

The id_verifications table tracks the result of identity verification checks run during the mandate form flow. Results are linked back to the mandate for downstream gatekeeping decisions.

Customer Configs

The customer_configs table is the anchor for multi-tenancy. Each letting agent organisation has exactly one row, binding it to:

  • Its Service User Number (SUN) — required for BACS/AUDDIS mandate registration via Modulr.
  • Its Modulr account reference — the collection account where BACS-debited funds land.
  • Its Griffin account reference — the DD holding account where funds are swept before forwarding.

All other tables reference customer_configs to enforce strict per-organisation data scoping.

Why This Matters

Building the schema first, before any application logic, means:

  1. Every feature has a clear contract. API handlers, background jobs, and form processors all know exactly what shape the data takes.
  2. Multi-tenancy is enforced at the data layer. Rather than being bolted on later, isolation between agents is structural.
  3. The audit trail is complete from day one. Fund sweeps, collections, verifications, and alerts all have dedicated tables — nothing is inferred or reconstructed from application logs.

What's Next

With the schema in place, the next releases will layer on application logic:

  • Mandate form token generation and the public-facing mandate form
  • BACS working day calculations and collection scheduling
  • Modulr and Griffin API integrations for mandate registration and fund sweeping
  • Alert threshold evaluation and notification delivery