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	Nullable	Description
id	PK	no	Internal primary key
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_bytes	bytea	no	The raw document payload
document_size	bigint	no	Size of the document in bytes
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, 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 last failed attempt

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
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	boolean	no	Whether this was detected as a duplicate AS4 message (default false)
status	text	no	received, 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

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