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)
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: The transaction row and all its sending attempt rows are moved after successful AS4 sending AND outbound reporting record created.
• Inbound: The transaction row and all its forwarding attempt rows are moved after successful forwarding to Receiver Backend AND reporting record created (either via sync response or async API call).

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

Outbound Transaction Fields (one row per document)

Field	Type	Description
id	PK	Internal primary key
sender_id	text	Peppol Participant ID of the sender
receiver_id	text	Peppol Participant ID of the receiver
doc_type_id	text	Peppol Document Type Identifier
process_id	text	Peppol Process Identifier
sbdh_instance_id	text	Peppol SBDH Instance Identifier
document_bytes	bytea	The raw document payload
business_document_id	text	Optional: ID from the business document
c1_country_code	char(2)	Country of the sender (C1)
status	text	pending, sending, sent, failed, permanently_failed
attempt_count	int	Total number of sending attempts so far
created_dt	timestamptz	When the transaction was initially created
completed_dt	timestamptz	When the transaction reached its final successful state (null while in progress)
reporting_status	text	Whether outbound reporting has been triggered (pending, reported)
error_details	text	Summary error from the last failed attempt (null on success)

Outbound Sending Attempt Fields (one row per sending try)

Field	Type	Description
id	PK	Internal primary key
outbound_transaction_id	FK	References outbound_transaction.id
as4_message_id	text	AS4 Message ID used for this specific attempt
receipt_message_id	text	AS4 Message ID from the synchronous receipt (null on failure)
http_status_code	int	HTTP status code from the AS4 response
attempt_dt	timestamptz	Date and time of this sending attempt
attempt_status	text	Outcome of this attempt (success, failed)
error_details	text	Error message or reason for failure (null on success)

Inbound Transaction Fields (one row per received document)

Field	Type	Description
id	PK	Internal primary key
incoming_id	text	Unique Incoming ID from phase4
c2_seat_id	text	Peppol Seat ID of the sending AP (C2)
c3_seat_id	text	Peppol Seat ID of the receiving AP (C3, i.e., this AP)
signing_cert_cn	text	Subject CN of the signing certificate from the AS4 message
sender_id	text	Peppol Participant ID of the sender
receiver_id	text	Peppol Participant ID of the receiver
doc_type_id	text	Peppol Document Type Identifier
process_id	text	Peppol Process Identifier
document_bytes	bytea	The raw received SBD (SBDH + business document)
as4_message_id	text	AS4 Message ID from the inbound message
sbdh_instance_id	text	Peppol SBDH Instance Identifier
business_document_id	text	Optional: ID from the business document
c4_country_code	char(2)	Country of the final receiver (C4), set via reporting
is_duplicate	boolean	Whether this was detected as a duplicate AS4 message
status	text	received, forwarding, forwarded, forward_failed, permanently_failed
attempt_count	int	Total number of forwarding attempts so far
received_dt	timestamptz	When the message was received via AS4
completed_dt	timestamptz	When the transaction reached its final successful state (null while in progress)
reporting_status	text	Whether reporting record has been created (pending, reported)
error_details	text	Summary error from the last failed forwarding attempt (null on success)

Inbound Forwarding Attempt Fields (one row per forwarding try)

Field	Type	Description
id	PK	Internal primary key
inbound_transaction_id	FK	References inbound_transaction.id
attempt_dt	timestamptz	Date and time of this forwarding attempt
attempt_status	text	Outcome of this attempt (success, failed)
error_details	text	Error message or reason for failure (null on success)

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