Database Design Notes

Database Design Notes

Overview

PostgreSQL and MySQL are the supported database backends. Both outbound and inbound use a parent/child table design: the parent table holds the document and its metadata, while the child table records each individual attempt (sending or forwarding).

MySQL requires version 8.0 or later (for FOR UPDATE SKIP LOCKED support).

Table Strategy

Table	Purpose
outbound_transaction	The outbound document and its metadata (one row per document — both business documents and MLS responses)
outbound_sending_attempt	Each AS4 sending attempt for an outbound transaction (one row per try)
outbound_transaction_archive	Successfully completed outbound transactions
outbound_sending_attempt_archive	Sending attempts belonging to archived outbound transactions
inbound_transaction	Active inbound transactions — the received document and its metadata
inbound_forwarding_attempt	Each forwarding attempt for an inbound transaction (one row per try)
inbound_transaction_archive	Successfully completed inbound transactions
inbound_forwarding_attempt_archive	Forwarding attempts belonging to archived inbound transactions
as4_duplicate_item	Persistent record of incoming AS4 message IDs for duplicate detection (see AS4 Duplicate Item)

Archival Policy

Transactions are moved to the archive tables when they are fully completed successfully:

• Outbound (business document): The transaction row and all its sending attempt rows are moved after successful AS4 sending AND outbound reporting record created AND MLS response received (if applicable).
• Outbound (MLS response): The transaction row and its sending attempt rows are moved after successful AS4 sending.
• Inbound: The transaction row and all its forwarding attempt rows are moved after successful forwarding to Receiver Backend AND reporting record created AND MLS response sent (if applicable).

Permanently failed and rejected transactions remain in the primary tables (they are not considered "final" for archival purposes).

Outbound Transaction Fields (one row per document)

The outbound_transaction table stores both regular business documents and MLS responses. The transaction_type column distinguishes them.

Field	Type	Nullable	Description
id	PK	no	Internal primary key
transaction_type	text	no	Type of outbound transaction: business_document or mls_response
sender_id	text	no	Peppol Participant ID of the sender
receiver_id	text	no	Peppol Participant ID of the receiver
doc_type_id	text	no	Peppol Document Type Identifier
process_id	text	no	Peppol Process Identifier
sbdh_instance_id	text	no	Peppol SBDH Instance Identifier
source_type	text	no	How the document was submitted: payload_only (AP creates SBDH) or prebuilt_sbd (SBDH already present)
document_path	text	no	Path to the document file — filesystem path or s3://bucket/key URI depending on storage.mode
document_size	bigint	no	Size of the document in bytes
document_hash	char(64)	no	SHA-256 hash of the document payload for integrity verification
c1_country_code	char(2)	no	Country of the sender (C1)
sbdh_standard	text	yes	SBDH Standard override for non-XML payloads (e.g. urn:peppol:doctype:pdf+xml). Null if auto-derived.
sbdh_type_version	text	yes	SBDH TypeVersion override (e.g. 0). Null if auto-derived.
sbdh_type	text	yes	SBDH Type override (e.g. factur-x). Null if auto-derived.
payload_mime_type	text	yes	MIME type for binary payloads (e.g. application/pdf). Null for XML payloads. When set, the payload is wrapped in <BinaryContent>.
status	text	no	pending, rejected, sending, sent, failed, permanently_failed
attempt_count	int	no	Total number of sending attempts so far (default 0)
created_dt	timestamptz	no	When the transaction was initially created
completed_dt	timestamptz	yes	When the transaction reached its final successful state
reporting_status	text	no	Whether outbound reporting has been triggered (pending, reported)
next_retry_dt	timestamptz	yes	Planned date/time of the next sending retry (set when status is failed, cleared on success or permanent failure)
error_details	text	yes	Summary error from the permanently failed attempt
mls_to	text	yes	MLS_TO override — alternative Peppol participant ID to receive MLS response (only for business_document)
mls_status	text	yes	Status of MLS response reception: pending, received_ap, received_ab, received_re, not_applicable (only for business_document)
mls_received_dt	timestamptz	yes	When the MLS response was received (only for business_document)
mls_id	text	yes	The MLS message's own ID (cbc:ID) from the received MLS (only for business_document)
mls_inbound_transaction_id	FK	yes	References the inbound_transaction.id that triggered this MLS response (only for mls_response)

Outbound Sending Attempt Fields (one row per sending try)

Field	Type	Nullable	Description
id	PK	no	Internal primary key
outbound_transaction_id	FK	no	References outbound_transaction.id
as4_message_id	text	no	AS4 Message ID used for this specific attempt
as4_timestamp	timestamptz	no	AS4 MessageInfo/Timestamp from this attempt's AS4 message (UTC). For business documents this is MLS Milestone M1; for MLS responses this is MLS Milestone M2.
receipt_message_id	text	yes	AS4 Message ID from the synchronous receipt
http_status_code	int	yes	HTTP status code from the AS4 response
attempt_dt	timestamptz	no	Date and time of this sending attempt
attempt_status	text	no	Outcome of this attempt (success, failed)
error_details	text	yes	Error message or reason for failure
sending_report	text	yes	Phase4 Peppol sending report as JSON (contains full details of the AS4 exchange including lookup results, timing, and the AS4 receipt)

Inbound Transaction Fields (one row per received document)

Field	Type	Nullable	Description
id	PK	no	Internal primary key
incoming_id	text	no	Unique Incoming ID from phase4
c2_seat_id	text	no	Peppol Seat ID of the sending AP (C2)
c3_seat_id	text	no	Peppol Seat ID of the receiving AP (C3, i.e., this AP)
signing_cert_cn	text	no	Subject CN of the signing certificate from the AS4 message
sender_id	text	no	Peppol Participant ID of the sender
receiver_id	text	no	Peppol Participant ID of the receiver
doc_type_id	text	no	Peppol Document Type Identifier
process_id	text	no	Peppol Process Identifier
document_path	text	no	Path to the document file — filesystem path or s3://bucket/key URI depending on storage.mode
document_size	bigint	no	Size of the received SBD in bytes
document_hash	char(64)	no	SHA-256 hash of the received SBD payload for integrity verification
as4_message_id	text	no	AS4 Message ID from the inbound message
as4_timestamp	timestamptz	no	AS4 MessageInfo/Timestamp from the incoming AS4 message (UTC). For business documents this is MLS Milestone M1 (from C2); for MLS messages this is MLS Milestone M2 (from C3).
sbdh_instance_id	text	no	Peppol SBDH Instance Identifier
c1_country_code	char(2)	yes	Country of the original sender (C1), set via reporting
c4_country_code	char(2)	yes	Country of the final receiver (C4), set via reporting
is_duplicate_as4	boolean	no	Duplicate detected on AS4 Message ID level (default false)
is_duplicate_sbdh	boolean	no	Duplicate detected on SBDH Instance Identifier level (default false)
status	text	no	received, rejected, forwarding, forwarded, forward_failed, permanently_failed
attempt_count	int	no	Total number of forwarding attempts so far (default 0)
received_dt	timestamptz	no	When the message was received via AS4
completed_dt	timestamptz	yes	When the transaction reached its final successful state
reporting_status	text	no	Whether reporting record has been created (pending, reported)
next_retry_dt	timestamptz	yes	Planned date/time of the next forwarding retry (set when status is forward_failed, cleared on success or permanent failure)
error_details	text	yes	Summary error from the last failed forwarding attempt
mls_to	text	yes	MLS_TO target participant ID extracted from SBDH extension
mls_type	text	no	MLS sending strategy active at time of reception: FAILURE_ONLY or ALWAYS_SEND
mls_response_code	text	yes	MLS response code to be sent or already sent: RE, AP, AB
mls_outbound_transaction_id	FK	yes	References the outbound_transaction.id that represents the MLS response sending

Inbound Forwarding Attempt Fields (one row per forwarding try)

Field	Type	Nullable	Description
id	PK	no	Internal primary key
inbound_transaction_id	FK	no	References inbound_transaction.id
attempt_dt	timestamptz	no	Date and time of this forwarding attempt
attempt_status	text	no	Outcome of this attempt (success, failed)
error_code	text	yes	Machine-readable error code classifying the failure (e.g., connection_refused, timeout, http_error)
error_details	text	yes	Error message or reason for failure

Archive Tables

The archive tables have the same column layout as their respective primary tables. Rows are moved (DELETE + INSERT) when the transaction is fully completed:

• outbound_transaction_archive mirrors outbound_transaction
• outbound_sending_attempt_archive mirrors outbound_sending_attempt
• inbound_transaction_archive mirrors inbound_transaction
• inbound_forwarding_attempt_archive mirrors inbound_forwarding_attempt

Archive tables intentionally have no foreign keys (unlike the primary tables, where attempt rows cascade-delete with their parent). Archival code deletes attempt rows before the parent row, and cleanup does the same.

Two-stage Lifecycle: Active → Archived → Cleaned (since 0.2.4)

A transaction passes through three storage stages:

1. Active in the primary tables (outbound_transaction / inbound_transaction) while it progresses through the state machine: pending → sending/forwarding → sent/forwarded/failed/permanently_failed.
2. Archived when fully completed (terminal status AND reporting_status='reported'). The archival scheduler moves the row plus its attempt children to the *_archive tables. Document files remain on disk untouched. See archival.scheduler.* properties.
3. Cleaned (optional) once completed_dt is older than cleanup.scheduler.retention. The cleanup scheduler deletes the archive row, the archived attempt rows, and the document file from storage. See cleanup.scheduler.* properties.

Cleanup operates only on archived rows — primary tables are never touched. To prevent orphan files, the document file is deleted before the database row; if file deletion fails (e.g. transient S3 outage) the row is left for the next cycle. To prevent races between archival and cleanup, the configured retention must be longer than 2 × archival.scheduler.interval.ms.

AS4 Duplicate Item

The as4_duplicate_item table backs phase4's IAS4DuplicateManager. Every incoming AS4 message ID is recorded here on receipt; subsequent receptions of the same ID are flagged as duplicates. Storing this in the database (rather than in memory) ensures duplicate detection survives a restart and works consistently across multiple AP instances sharing one database.

Field	Type	Nullable	Description
message_id	PK	no	The AS4 MessageInfo/MessageId of the incoming message
profile_id	text	yes	Active AS4 profile ID at time of reception (e.g. the Peppol profile ID)
pmode_id	text	yes	Active AS4 PMode ID at time of reception
created_dt	timestamptz	no	When the message was first seen — used by retention/eviction

Retention is controlled by the existing phase4 setting phase4.incoming.duplicate.disposal.minutes. phase4's built-in AS4DuplicateCleanupJob periodically calls IAS4DuplicateManager.evictAllItemsBefore(...) which deletes rows older than the configured threshold.

Multi-Instance Concurrency

All DB-based schedulers (retry, archival, cleanup) use SELECT ... FOR UPDATE SKIP LOCKED when picking up work items. This syntax is supported by both PostgreSQL (9.5+) and MySQL (8.0+). This allows multiple AP instances to run concurrently against the same database without leader election:

-- Example: retry scheduler picking up eligible outbound transactions
SELECT * FROM outbound_transaction
 WHERE status = 'failed'
   AND next_retry_dt <= NOW()
 ORDER BY next_retry_dt
 LIMIT :batchSize
   FOR UPDATE SKIP LOCKED

Duplicate detection is protected by unique constraints on as4_message_id and sbdh_instance_id — concurrent inserts from different instances are safely handled by the database.

Open Considerations

• Index strategy for common query patterns (e.g., lookup by AS4 Message ID for reporting API, query failed transactions for retry scheduler, lookup by SBDH Instance ID)
• Partitioning strategy if transaction volume is very high