commit fab77323629eae1b92e9dc2f18b7a5f687925273
parent 477b26f1cdd78569e2665141333b2be455ccdcae
Author: Antoine A <>
Date: Thu, 20 Feb 2025 14:57:00 +0100
DD58 for a unique tx id for libeufin-nexus
Diffstat:
2 files changed, 57 insertions(+), 0 deletions(-)
diff --git a/design-documents/058-ebics-tx-unique-id.rst b/design-documents/058-ebics-tx-unique-id.rst
@@ -0,0 +1,55 @@
+DD 58: EBICS Transaction Unique ID
+##################################
+
+Summary
+=======
+
+LibEufin Nexus need to have a single unique ID for each registered incoming transaction. For outgoing transaction we generate a unique ID ourselves but for incoming transaction we are dependent of whatever the bank provide use. EBICS and ISO20022 do not provide a perfect transaction identifier and we need to have an ID that is compatible with different ISO20022 dialects and will be compatible with future specification changes.
+
+Problem
+=======
+
+ISO20022 provides three many transaction identifier:
+* *AcctSvcrRef (AccountServicerReference)*: unique reference, as assigned by the account servicing institution, to unambiguously identify the instruction. The format is ambiguous and is only unique within the account servicing institution database. This identifier can be present a the entry level and/or at the transaction level (a single entry can be a batch of transaction), this make this identifier sometimes a bit ambiguous.
+* *UETR (unique end-to-end transaction reference)*: universally unique identifier to provide an end-to-end reference of a payment transaction. This is the perfect unique ID, it's a UUID v4 that is shared with all participant of the transfer.
+* *TxId (TransactionIdentification)*: unique identification, as assigned by the first instructing agent, to unambiguously identify the transaction that is passed on, unchanged, throughout the entire interbank chain. The format is ambiguous and only unique for a “pre-agreed period”, whatever that means, but it is shared all participant of the transfer.
+* *EndToEndId (EndToEndIdentification)*: unique identification, as assigned by the initiating party, to unambiguously identify the transaction. This identification is passed on, unchanged, throughout the entire end-to-end chain. The format is unspecified and nothing guaranteed it will actually be unique as institution are not expected to enforce it, it is also often NOTPROVIDED.
+
+All those identifiers are optional and of course not a single one of them is consistently present in all dialects and all files.
+
+* *DE dialect*: AcctSvcrRef is mandatory in camt.53 and camt.52 but is never present in camt.54 (for instant payments). UETR is optional but never found in practice. TxId is optional but can be found everywhere (also in camt.54). If a transaction is made between two institutions the TxId is constantly provided but when a transaction is made within a single institution (between two accounts at the same bank) only the AcctSvcrRef is present.
+* *CH dialect*: AcctSvcrRef is mandatory everywhere. UETR is but can be found everywhere. If a transaction is made between two institutions the UETR is constantly provided but when a transaction is made within a single institution (between two accounts at the same bank) only the AcctSvcrRef is present. TxId is not used.
+
+Currently libeufin-nexus stores a single bank id in a bank_id column. This is the TxId for DE banks and UETR for CH banks. When a transaction have nether of those we ignore the transaction.
+
+The current state is bad as we ignore transaction that we could actually correctly store and as ISO20022 is going to move to mandatory UETR in the future we should be able to support those when they will be available in DE.
+
+Solution
+========
+
+We should embrace this imperfection and store all the identifiers that are provided as there is always at least one present:
+
+.. code-block:: sql
+ CREATE TABLE incoming_transactions (
+ acct_svcr_ref TEXT UNIQUE CHECK (char_length(acct_svcr_ref) <= 35),
+ tx_id TEXT UNIQUE CHECK (char_length(tx_id) <= 35),
+ uetr UUID UNIQUE,
+ CONSTRAINT bank_id CHECK(COALESCE(acct_svcr_ref, tx_id, uetr) IS NOT NULL
+ );
+
+We should then be able to store all transaction and support UETR everywhere in the future.
+
+Migration
+=========
+
+There is two way to migrate the database schema:
+
+SQL only
+--------
+
+A solution using only SQL would be to rename the bank_id column to compat_id and we would check the compat_id using the current id selection logic. I would prefer not to keep another column just for compatibility as checking three column is already complicated enough.
+
+SQL and code
+------------
+
+We can use some non SQL logic during the migration to move the current bank_id to the appropriate column based on the configured dialect. I am not sure how to do that cleanly.
+\ No newline at end of file
diff --git a/design-documents/index.rst b/design-documents/index.rst
@@ -69,4 +69,5 @@ Design documents that start with "XX" are considered deprecated.
055-wallet-problem-report.rst
056-weblate-integration.rst
057-libeufin-bank-account-lockout.rst
+ 058-ebics-tx-unique-id.rst
999-template
\ No newline at end of file
