Database schema

What Ductus keeps in PostgreSQL, for transparency (and for DBAs tuning the server). This is not an API. Tables and columns can change in any release; read and change data through the REST API (/api/v1) and the ductus commands, which keep working across versions. Queries written against these tables may break on upgrade.

Changes only ever add (migrations in crates/ductus-store/migrations, see architecture.md invariant 4), so an older engine still runs on a newer schema: that's what makes rollback safe. A test checks that every table and column is described here.

Statuses (status columns): 0 pending, 1 sent, 2 filtered, 3 quarantined, 4 duplicate (messages only).

messages

Every message received, as received (the original is never changed). Partitioned by retention tier, then by time (migration 0010): messages_t21 holds the 3-week tier (21 days), and its partitions cover a period each, named by their first day (messages_t21_20261005): a day for the 1w tier, a week (from Monday, midnight UTC) for 3w and 7w, about 24 partitions in all. They're created 3 days ahead; a partition is dropped once its whole period is older than its tier's days (see retention).

The held area (migration 0014) is tier 0: messages_held, partitioned by month (messages_held_202609, bounds at midnight UTC). When a partition expires with messages someone still needs (pending, or quarantined for an interface that keeps them), those move here with their deliveries, and the partition drops. A month that's over is dropped once nothing in it is unfinished. The held tables are created when first needed.

Indexes: primary key (id, received_at, tier) (a partitioned table's key must include its partition columns); messages_pending (interface, partition_key, id) WHERE status = 0; messages_by_interface (interface, id) (the message browser, newest first); messages_quarantined (interface, id) WHERE status = 3.

deliveries

One row per queued message and destination: each destination's work queue and its status. Inserted in the same statement as the message. Partitioned like messages (deliveries_t21_20261005, same tier and bounds).

Indexes: primary key (message_id, received_at, destination_id, tier); deliveries_queue (destination_id, message_id, partition_key) WHERE status = 0, which is each destination's queue in arrival order (the lane is checked inside the index; migration 0011 replaced deliveries_pending, which had the lane second); deliveries_quarantined (destination_id, message_id) WHERE status = 3 (quarantine counts without reading the table).

destinations

Every destination the engine has been configured with. Deliveries refer to it by id (a small integer instead of repeating names in every row).

Indexes: primary key id; unique (interface, name).

dedupe_keys

Keys of recently seen messages, for interfaces with dedupe:. Claimed in the same transaction as the message insert. Old keys are deleted hourly.

Indexes: primary key (interface, key); seen_at (for the hourly cleanup).

source_state

What sources remember between runs, always written in the same transaction as the messages it accounts for: file progress (file:<name>: size, time, byte offset, records, done) and schedules (schedule: anchor, previous run).

Index: primary key (interface, key).

api_tokens

REST API tokens (ductus token create, or POST /api/v1/tokens). Only a hash of each token is kept; the token itself is shown once. Revoked tokens stay, for the record.

Indexes: primary key id; unique hash; api_tokens_live_name (name) WHERE revoked_at IS NULL (unique); api_tokens_user (user_id).

users

People who sign in (ductus user create, or POST /api/v1/users). Passwords are argon2id hashes; no password is ever stored or logged. Accounts are disabled, never deleted: the audit trail names them.

| directory_id | text | ldap accounts: the person's id in the directory, <attribute>:<value> (entryUUID:…, or objectGUID:hex:… for Active Directory's 16 bytes; auth.ldap.attributes.id). Set at the first sign-in or refresh that reads it (null before 0.6.7.1, and with id: none). A refresh looks the person up by it. |

Directory accounts (source = 'ldap') are made at their first sign-in; their role, permissions, display name and email are the directory's, rewritten at every sign-in and refresh (migration 0022 added the two directory_ columns). An account is the directory's person once its directory_id is known (0.6.7.1, migration 0025): when the directory renames their sign-in name, username takes the new one; when it gives a departed person's sign-in name to someone new, the earlier account is renamed <name>#<id> (its own id) and refused, so its tokens stay dead, and the new person gets an account of their own.

Indexes: primary key id; unique users_username (lower(username)); unique users_directory_id (directory_id) WHERE directory_id IS NOT NULL.

user_names

What an account was called before (0.6.7.1, migration 0025): every change of its username, display name or email (an admin's edit, the directory's at a sign-in or refresh, a name moved aside) keeps the old value here, in the same transaction as the change. Kept as long as the account. Empty old values aren't kept.

Indexes: primary key id; user_names_value (lower(value)); user_names_user (user_id).

user_settings

A user's own choices, so they follow them to any machine. Today: the theme and the custom colors.

api_sessions

Web UI sessions: a browser signs in with a username and password, or a token (POST /api/v1/session), and gets a cookie instead of keeping either. A session acts with its user's or token's role and ends when the user is disabled or the token revoked, when it's been idle too long (12 hours unless auth.session says otherwise), or at most 7 days after sign-in. Expired sessions are deleted when the next one is made.

Indexes: primary key id; unique hash; api_sessions_expires (expires_at); api_sessions_user (user_id).

message_actions

What people did to messages through the API (POST /api/v1/messages/{id}/resend, /messages/resend, /messages/{id}/edit): one row per message and destination acted on, with the delivery as it was before, so nothing an action changes is lost. A resend puts the delivery back to pending in place (same message, same id); an edit queues a corrected copy (a new message) and marks the original's quarantined delivery to that destination filtered ("replaced by edited copy N"). Not dropped with the messages' partitions: it's the record of who did what (step 8's audit log builds on it; no purge yet).

Indexes: primary key id; message_actions_message (message_id); message_actions_copy (copy_id) WHERE copy_id IS NOT NULL; message_actions_audit (audit_id) WHERE audit_id IS NOT NULL; message_actions_outputs (at) WHERE previous_output IS NOT NULL (finds the copies to clear). Rows older than audit.keep (default 6 years) are deleted hourly in batches of 5,000.

audit_log

Who did what, to what, from where, and how it ended. Written in the transaction of the change itself where there is one (a resend, a dismissal, an account change), and before the action where there isn't (a pause, a drain; the action is refused if the row can't be written). Payload reads are written before the payload is returned. Not tied to users or tokens by a foreign key: it must say who acted long after the account is gone. Rows older than audit.keep are deleted hourly in batches of 5,000.

Indexes: primary key id; audit_log_at (at); audit_log_actor (actor_kind, actor_id, at); audit_log_user (user_id, at); audit_log_action (action, at); audit_log_interface (interface, at); audit_log_message (message_id).

retention

Each interface's retention as the engine applied it, and the change in progress. Changes run one at a time per interface: a different value configured meanwhile waits in waiting, and so does a shorter one (it deletes messages) until someone confirms it (ductus retention confirm).

Index: primary key interface.

engines

Every run of an engine on this database (migration 0023). While it runs, an engine holds a session advisory lock with lock_key on a connection of its own; that is what "alive" means here (pg_locks), so a crash or power loss needs no timeout to wait out (the server frees the lock with the session; the lock connection asks it to notice a vanished client within ~25 s). A refused start leaves no row. Rows of engines that started more than audit.keep ago, own nothing and aren't alive are deleted by the hourly audit job.

Indexes: primary key instance; unique lock_key.

interface_owners

Which engine runs each interface's workers, file pickup and schedule: one engine per interface across every engine on the database. An engine claims all of its (queued) interfaces at start, before changing anything for them, all or none; a row whose engine isn't alive any more is taken over. Respond-mode HTTP interfaces aren't claimed (no queue). A clean stop deletes its rows.

Indexes: primary key interface; interface_owners_instance (instance).

interface_applied

The lanes each interface last ran with: ordering.partitions, or 1 without ordering.key. At start, an interface with fewer lanes than recorded (or none recorded) has its waiting deliveries checked: any in lanes it no longer has refuse the start (no worker would read them). More lanes with messages waiting only warn (interface.partitions_raised).

Index: primary key interface.

interface_activity

What monitoring must remember across a restart or a failover: when each interface last received something. Written every few seconds by one task (only the times that changed; a time never moves back).

Index: primary key interface.

destination_activity

Each destination's last send and failure (written like interface_activity), and its pause (written at once).

Index: primary key destination_id.

docs_snapshot

The project's documentation as the company wrote it, for SQL reports and BI tools ("which interfaces have no signed BAA": SELECT name FROM docs_snapshot WHERE kind = 'interface' AND docs->'custom'->>'baa_signed' = 'no'). One row per interface and per resources.yaml entry. The YAML in Git is the source of truth: every engine start makes the table match the project (changed rows are rewritten, entries the project no longer has are removed), so it is as current as the last start. Versioned history comes with versioned releases (v0.6).

Indexes: primary key (kind, name); docs_snapshot_docs (GIN on docs).

alert_state

One row per thing that can alert: each interface (iface:<interface>), each destination (dest:<interface>/<destination>) and each certificate the engine connects to (cert:<resource>:<host>:<port>) or presents (cert:<name>:<engine's host>:0), made when an engine with alerts: in ductus.yaml first runs it (so it can be silenced before anything fires). Changed by compare-and-set on rev: two engines on one database judge the same state and only one records each change.

Indexes: primary key id; unique key.

alert_events

What happened to each alert: fired, escalated, reminded, recovered (the engine) and acknowledged, silenced, unsilenced (people, also in audit_log). Rows older than alerts.keep (default a year) are deleted hourly with their notifications.

Indexes: primary key id; alert_events_alert (alert_id, id); alert_events_at (at).

alert_deliveries

The outbox: one row per notification (an event to a channel), stored with the event before anything is sent. Senders claim due rows with FOR UPDATE SKIP LOCKED and a two-minute lease, so two engines send each once; a crash between sending and marking it sent sends it again after the lease (at least once).

Indexes: primary key id; alert_deliveries_due (next_try_at) WHERE status = 'pending'; alert_deliveries_event (event_id).

certificate_status

The certificate each TLS server the engine connects to presented at its last check (migration 0021): HTTPS APIs and destinations, the alerts' mail server, webhooks and heartbeat. Written by the engine's scheduled checks (doctor.schedule, every 6 hours by default) and by checks someone asks for (POST /api/v1/doctor). Read even when this server doesn't trust it (a private CA). A check that couldn't read a certificate sets error and checked_at and keeps the dates from the check before.

Our own certificates are rows too (migration 0024, side = 'ours'): each certificates: pair the engine presents, to senders on a TLS listener or as a client certificate. They are read from what the engine loaded (no network), written at its start and again only when a renewal is picked up or a renewed pair can't be used.

Index: primary key (resource, host, port).

doctor_runs

Each connection check the engine ran on its schedule or was asked for, with its whole report (migration 0021). The last 30 are kept.

Index: primary key id.

Functions

• ductus_tiers(): the tiers in days, {7,21,49}. ductus_tier_unit(tier): the period one partition of a tier covers (day for 7, else week).
• ductus_create_tier_partition(table, tier, day): the partition of messages or deliveries holding that tier's rows of that day, created if missing.
• ductus_create_held_partition(table, day): the held area's partition of messages or deliveries for that day's month (and the held table itself), created if missing.
• ductus_ensure_tier_partitions(table, tier, days_ahead), ductus_ensure_delivery_partitions(tier): one tier's missing partitions; the engine runs them hourly, one table and tier per transaction, with a short lock timeout (see architecture.md, Storage).
• ductus_ensure_message_partitions(days_ahead), ductus_ensure_delivery_partitions(), ductus_ensure_partitions(days_ahead): every tier at once, shortest tier first: what older engines call. Removed when the migrations are squashed.