Database Design Notes

Database Design Notes

Overview

PostgreSQL is the required database backend. 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).

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

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
document_type	text	no	Type of the submitted document (raw_xml, prebuilt_sbd)
document_bytes	bytea	no	The raw document/Peppol SBDH payload
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
business_document_id	text	yes	ID from the business document
c1_country_code	char(2)	no	Country of the sender (C1)
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)
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
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

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_bytes	bytea	no	The raw received SBD (SBDH + business document)
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
sbdh_instance_id	text	no	Peppol SBDH Instance Identifier
business_document_id	text	yes	ID from the business document
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)
is_duplicate_business	boolean	no	Duplicate detected on Business Document ID 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)
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_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

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