Database Design Notes

Database Design Notes

Overview

PostgreSQL is the required database backend. The design uses separate tables for outbound and inbound transactions, with corresponding archive/backup tables for completed transactions.

Table Strategy

Table	Purpose
outbound_attempt	Each AS4 sending attempt (one row per try, correlated by SBDH Instance ID)
outbound_attempt_archive	Successfully completed outbound attempts (entire set of attempts for a document)
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: All attempt rows for a document 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 Attempt Fields (one row per sending attempt)

Field	Type	Description
id	PK	Internal primary key
sbdh_instance_id	text	Peppol SBDH Instance Identifier (correlates all attempts for the same document)
document_bytes	bytea	The raw document payload
business_document_id	text	Optional: ID from the business document
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
c1_country_code	char(2)	Country of the sender (C1)
attempt_status	text	Outcome of this attempt (success, failed)
attempt_dt	timestamptz	Date and time of this sending attempt
reporting_status	text	Whether outbound reporting has been triggered (pending, reported) — only relevant on the successful attempt

Note: The document_bytes and c1_country_code are repeated across attempts for the same document. This is a deliberate denormalization to keep the design simple and each attempt row self-contained. An alternative design with a separate outbound_document parent table is possible but adds complexity.

Inbound Transaction Fields (one row per received document)

Field	Type	Description
id	PK	Internal primary key
incoming_id	text	Unique Incoming ID from phase4
document_bytes	bytea	The raw received document payload
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
received_dt	timestamptz	When the message was received via AS4
reporting_status	text	Whether reporting record has been created (pending, reported)

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_attempt_archive mirrors outbound_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 to find all outbound attempts)
• Partitioning strategy if transaction volume is very high
• Whether the denormalized outbound design (document bytes repeated per attempt) should be normalized into a parent/child table structure