All Docs
FeaturesagentOS Direct DebitUpdated March 12, 2026

Building the Foundation: Core Schema & Enums in v1.0.0

Building the Foundation: Core Schema & Enums in v1.0.0

Every robust service starts with a well-considered data model. v1.0.0 of the Direct Debit Collection Service lays that foundation by defining the complete Drizzle ORM schema for the entire DD domain — from mandate creation right through to clawback reserve management.

This post walks through what was built, the design decisions behind it, and why it matters for everything that comes next.

What Was Built

v1.0.0 introduces eight core database tables and two domain enums. Together, they model the full lifecycle of a Direct Debit operation: from a letting agent sending a tenant a mandate invite link, through BACS collection and fund holding, to clawback protection and alerting.

The Tables

mandates

The central entity of the service. A mandate represents a Direct Debit authorisation from a tenant — registered with Modulr via AUDDIS — that permits recurring collections. The table tracks everything from the tenant's bank details to the current lifecycle state of the mandate.

collections

Each individual BACS collection request gets its own row here. A single active mandate will generate many collection records over its lifetime. The status enum on this table tracks the BACS cycle from scheduling through to successful collection — or failure and clawback.

holding_transactions

Once Modulr confirms a collection, funds are swept into a Griffin DD holding account. This table records every debit and credit against that holding account, giving a full audit trail of fund movements before they are forwarded to the agent's client account.

clawback_reserves

Tenants' banks can reverse a DD payment after it has been collected — a clawback. To protect against this, a reserve is maintained in the Griffin holding account. This table tracks the reserve balance per organisation, calculated from a configurable risk factor and a minimum threshold floor.

alert_thresholds

The alerting system is driven by per-organisation configuration. This table stores the threshold values that, when breached, trigger an alert — for example, more than 5 mandate creations per hour, or the clawback reserve dropping below its minimum.

alerts

When a threshold is breached, an alert record is written here. The table tracks the alert type, severity, current acknowledgement state, and the metadata needed to understand and action it.

mandate_invite_tokens

The public-facing mandate form is accessed via a unique, time-limited token link sent to a tenant. This table manages those tokens — their association to a pending mandate, expiry, and whether they have been consumed.

bank_holidays

BACS operates on UK banking working days only. This table stores UK bank holidays (seeded from the GOV.UK bank holidays API) and is used throughout the service whenever a working-day-aware date calculation is needed — such as determining a tenant's actual collection date or computing BACS submission deadlines.

The Enums

Mandate Status

The mandate lifecycle is modelled with six states:

draft → pending_submission → active
                          ↘ failed
active → suspended → active
active → cancelled
  • draft — The mandate record exists but hasn't been submitted to AUDDIS yet. This covers the window between a tenant starting the form and final submission.
  • pending_submission — The mandate has been completed and is queued for the next BACS/AUDDIS submission window.
  • active — The mandate is live. Collections can be scheduled against it.
  • suspended — Collections are paused. The mandate remains valid and can be reactivated.
  • cancelled — Permanently ended. No further collections will run.
  • failed — The submission or a processing step failed and requires investigation.

Collection Status

Each collection request moves through five states:

scheduled → submitted → collected
          ↘ failed
collected → clawback
  • scheduled — The collection date has been set but the BACS submission window hasn't opened yet.
  • submitted — The collection request has been sent to Modulr and entered the BACS cycle.
  • collected — Funds have been successfully debited from the tenant's account and received by Modulr.
  • failed — The collection was rejected or returned by the tenant's bank.
  • clawback — A previously successful collection has been reversed. The clawback reserve will be drawn against.

Multi-Tenancy by Design

Every single table carries an orgId column. This is a deliberate, non-negotiable constraint: no query in the service should ever return data without being scoped to an organisation. Each letting agent operates in complete isolation — their mandates, collections, reserves, thresholds, and alerts are entirely separate from every other agent on the platform.

This design decision at the schema level enforces multi-tenancy as a structural guarantee rather than an application-level afterthought.

What Comes Next

With the schema in place, the service can now build:

  • Mandate invite flow — token generation, public form handling, and AUDDIS submission
  • BACS collection scheduling — working-day-aware scheduling against active mandates
  • Fund sweep logic — Modulr → Griffin holding account transfers
  • Hold & forward pipeline — time-gated forwarding to agent client accounts with reserve enforcement
  • Alerting engine — threshold evaluation and notification dispatch
  • Admin API — endpoints for agentOS (and other consumers) to manage the full DD lifecycle

The data model is designed to support all of this without structural changes — the shape of the schema reflects the shape of the business process end-to-end.