commit f6e8b3e065fb1cbfda652a6d7f0339667c736030
parent 4d5ae2c629e97ed50d8ab9c3657922f668f66a8e
Author: Antoine A <>
Date: Fri, 12 Jan 2024 10:22:47 +0000
Deterministic bounce ID in SQL
Diffstat:
5 files changed, 174 insertions(+), 111 deletions(-)
diff --git a/database-versioning/libeufin-nexus-procedures.sql b/database-versioning/libeufin-nexus-procedures.sql
@@ -1,28 +1,57 @@
BEGIN;
+SET search_path TO public;
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+
SET search_path TO libeufin_nexus;
+-- Remove all existing functions
+DO
+$do$
+DECLARE
+ _sql text;
+BEGIN
+ SELECT INTO _sql
+ string_agg(format('DROP %s %s CASCADE;'
+ , CASE prokind
+ WHEN 'f' THEN 'FUNCTION'
+ WHEN 'p' THEN 'PROCEDURE'
+ END
+ , oid::regprocedure)
+ , E'\n')
+ FROM pg_proc
+ WHERE pronamespace = 'libeufin_nexus'::regnamespace;
+
+ IF _sql IS NOT NULL THEN
+ EXECUTE _sql;
+ END IF;
+END
+$do$;
+
CREATE FUNCTION register_outgoing(
IN in_amount taler_amount
,IN in_wire_transfer_subject TEXT
,IN in_execution_time BIGINT
,IN in_credit_payto_uri TEXT
,IN in_bank_transfer_id TEXT
+ ,OUT out_tx_id BIGINT
,OUT out_found BOOLEAN
,OUT out_initiated BOOLEAN
)
LANGUAGE plpgsql AS $$
DECLARE
init_id BIGINT;
-tx_id BIGINT;
BEGIN
-- Check if already registered
-SELECT outgoing_transaction_id INTO tx_id
+SELECT outgoing_transaction_id INTO out_tx_id
FROM outgoing_transactions
WHERE bank_transfer_id = in_bank_transfer_id;
IF FOUND THEN
out_found = true;
-- TODO Should we update the subject and credit payto if it's finally found
-- TODO Should we check that amount and other info match ?
+ SELECT true INTO out_initiated
+ FROM initiated_outgoing_transactions
+ WHERE outgoing_transaction_id = out_tx_id;
ELSE
-- Store the transaction in the database
INSERT INTO outgoing_transactions (
@@ -39,17 +68,17 @@ ELSE
,in_bank_transfer_id
)
RETURNING outgoing_transaction_id
- INTO tx_id;
+ INTO out_tx_id;
- -- Reconciles the related initiated payment
+ -- Reconciles the related initiated transaction
UPDATE initiated_outgoing_transactions
- SET outgoing_transaction_id = tx_id
+ SET outgoing_transaction_id = out_tx_id
WHERE request_uid = in_bank_transfer_id
RETURNING true INTO out_initiated;
END IF;
END $$;
COMMENT ON FUNCTION register_outgoing
- IS 'Register an outgoing payment and optionally reconciles the related initiated payment with it';
+ IS 'Register an outgoing transaction and optionally reconciles the related initiated transaction with it';
CREATE FUNCTION register_incoming(
IN in_amount taler_amount
@@ -87,29 +116,30 @@ ELSE
END IF;
END $$;
COMMENT ON FUNCTION register_incoming
- IS 'Register an incoming payment';
+ IS 'Register an incoming transaction';
-CREATE FUNCTION register_incoming_and_bounce(
- IN in_amount taler_amount
- ,IN in_wire_transfer_subject TEXT
- ,IN in_execution_time BIGINT
- ,IN in_debit_payto_uri TEXT
- ,IN in_bank_transfer_id TEXT
- ,IN in_timestamp BIGINT
- ,IN in_request_uid TEXT
+CREATE FUNCTION bounce_incoming(
+ IN tx_id BIGINT
,IN in_bounce_amount taler_amount
- ,IN in_bounce_subject TEXT
- ,OUT out_found BOOLEAN -- TODO return tx_id
+ ,IN in_now_date BIGINT
+ ,OUT out_bounce_id TEXT
)
LANGUAGE plpgsql AS $$
DECLARE
-tx_id BIGINT;
+bank_id TEXT;
+payto_uri TEXT;
init_id BIGINT;
BEGIN
--- Register the incoming transaction
-SELECT reg.out_found, out_tx_id
- FROM register_incoming(in_amount, in_wire_transfer_subject, in_execution_time, in_debit_payto_uri, in_bank_transfer_id) as reg
- INTO out_found, tx_id;
+-- Get incoming transaction bank ID and creditor
+SELECT bank_transfer_id, debit_payto_uri
+ INTO bank_id, payto_uri
+ FROM incoming_transactions
+ WHERE incoming_transaction_id = tx_id;
+-- Generate a bounce ID deterministically from the bank ID
+-- We hash the bank ID with SHA-256 then we encode the hash using base64
+-- As bank id can be at most 35 characters long we truncate the encoded hash
+-- Most banks should be case sensitive but we might have to normalize the id to uppercase for some of them
+SELECT substr(encode(public.digest(bank_id, 'sha256'), 'base64'), 0, 35) INTO out_bounce_id;
-- Initiate the bounce transaction
INSERT INTO initiated_outgoing_transactions (
@@ -120,24 +150,49 @@ INSERT INTO initiated_outgoing_transactions (
,request_uid
) VALUES (
in_bounce_amount
- ,in_bounce_subject
- ,in_debit_payto_uri
- ,in_timestamp
- ,in_request_uid
+ ,'bounce: ' || bank_id
+ ,payto_uri
+ ,in_now_date
+ ,out_bounce_id
)
- ON CONFLICT (request_uid) DO NOTHING
+ ON CONFLICT (request_uid) DO NOTHING -- idempotent
RETURNING initiated_outgoing_transaction_id INTO init_id;
IF FOUND THEN
-- Register the bounce
INSERT INTO bounced_transactions (
incoming_transaction_id ,initiated_outgoing_transaction_id
- ) VALUES (tx_id ,init_id)
- ON CONFLICT
- DO NOTHING;
+ ) VALUES (tx_id, init_id);
END IF;
+END$$;
+COMMENT ON FUNCTION bounce_incoming
+ IS 'Bounce an incoming transaction, initiate a bouce outgoing transaction with a deterministic ID';
+
+CREATE FUNCTION register_incoming_and_bounce(
+ IN in_amount taler_amount
+ ,IN in_wire_transfer_subject TEXT
+ ,IN in_execution_time BIGINT
+ ,IN in_debit_payto_uri TEXT
+ ,IN in_bank_transfer_id TEXT
+ ,IN in_bounce_amount taler_amount
+ ,IN in_now_date BIGINT
+ ,OUT out_found BOOLEAN
+ ,OUT out_tx_id BIGINT
+ ,OUT out_bounce_id TEXT
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+init_id BIGINT;
+BEGIN
+-- Register the incoming transaction
+SELECT reg.out_found, reg.out_tx_id
+ FROM register_incoming(in_amount, in_wire_transfer_subject, in_execution_time, in_debit_payto_uri, in_bank_transfer_id) as reg
+ INTO out_found, out_tx_id;
+
+-- Bounce the incoming transaction
+SELECT b.out_bounce_id INTO out_bounce_id FROM bounce_incoming(out_tx_id, in_bounce_amount, in_now_date) as b;
END $$;
COMMENT ON FUNCTION register_incoming_and_bounce
- IS 'Register an incoming payment and bounce it';
+ IS 'Register an incoming transaction and bounce it';
CREATE FUNCTION register_incoming_and_talerable(
IN in_amount taler_amount
@@ -146,23 +201,22 @@ CREATE FUNCTION register_incoming_and_talerable(
,IN in_debit_payto_uri TEXT
,IN in_bank_transfer_id TEXT
,IN in_reserve_public_key BYTEA
- ,OUT out_found BOOLEAN -- TODO return tx_id
+ ,OUT out_found BOOLEAN
+ ,OUT out_tx_id BIGINT
)
LANGUAGE plpgsql AS $$
-DECLARE
-tx_id INT8;
BEGIN
-- Register the incoming transaction
-SELECT reg.out_found, out_tx_id
+SELECT reg.out_found, reg.out_tx_id
FROM register_incoming(in_amount, in_wire_transfer_subject, in_execution_time, in_debit_payto_uri, in_bank_transfer_id) as reg
- INTO out_found, tx_id;
+ INTO out_found, out_tx_id;
-- Register as talerable bounce
INSERT INTO talerable_incoming_transactions (
incoming_transaction_id
,reserve_public_key
) VALUES (
- tx_id
+ out_tx_id
,in_reserve_public_key
) ON CONFLICT (incoming_transaction_id) DO NOTHING;
END $$;
diff --git a/integration/src/main/kotlin/Main.kt b/integration/src/main/kotlin/Main.kt
@@ -88,7 +88,8 @@ class Cli : CliktCommand("Run integration tests on banks provider") {
var hasClientKeys = clientKeysPath.exists()
var hasBankKeys = bankKeysPath.exists()
- nexusCmd.test("dbinit -r -c $conf").assertOk()
+ if (ask("Reset DB ? y/n>") == "y") nexusCmd.test("dbinit -r -c $conf").assertOk()
+ else nexusCmd.test("dbinit -c $conf").assertOk()
val nexusDb = NexusDb("postgresql:///libeufincheck")
when (kind) {
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
@@ -133,10 +133,24 @@ data class OutgoingPayment(
}
/** Outgoing payments registration result */
-sealed class OutgoingRegistrationResult {
- data class New(val initiated: Boolean): OutgoingRegistrationResult()
- data object AlreadyRegistered: OutgoingRegistrationResult()
-}
+data class OutgoingRegistrationResult(
+ val id: Long,
+ val initiated: Boolean,
+ val new: Boolean
+)
+
+/** Incoming payments registration result */
+data class IncomingRegistrationResult(
+ val id: Long,
+ val new: Boolean
+)
+
+/** Incoming payments bounce registration result */
+data class IncomingBounceRegistrationResult(
+ val id: Long,
+ val bounceId: String,
+ val new: Boolean
+)
/**
* Performs a INSERT, UPDATE, or DELETE operation.
@@ -204,7 +218,7 @@ class Database(dbConfig: String): java.io.Closeable {
*/
suspend fun registerOutgoing(paymentData: OutgoingPayment): OutgoingRegistrationResult = runConn {
val stmt = it.prepareStatement("""
- SELECT out_initiated, out_found
+ SELECT out_tx_id, out_initiated, out_found
FROM register_outgoing(
(?,?)::taler_amount
,?
@@ -225,9 +239,10 @@ class Database(dbConfig: String): java.io.Closeable {
stmt.executeQuery().use {
when {
!it.next() -> throw Exception("Inserting outgoing payment gave no outcome.")
- it.getBoolean("out_found") -> OutgoingRegistrationResult.AlreadyRegistered
- else -> OutgoingRegistrationResult.New(
- it.getBoolean("out_initiated")
+ else -> OutgoingRegistrationResult(
+ it.getLong("out_tx_id"),
+ it.getBoolean("out_initiated"),
+ !it.getBoolean("out_found")
)
}
}
@@ -247,22 +262,17 @@ class Database(dbConfig: String): java.io.Closeable {
*/
suspend fun registerMalformedIncoming(
paymentData: IncomingPayment,
- requestUid: String,
bounceAmount: TalerAmount,
- bounceSubject: String,
now: Instant
- ): Boolean = runConn {
- println("$paymentData $requestUid $bounceAmount $bounceSubject")
+ ): IncomingBounceRegistrationResult = runConn {
val stmt = it.prepareStatement("""
- SELECT out_found
+ SELECT out_found, out_tx_id, out_bounce_id
FROM register_incoming_and_bounce(
(?,?)::taler_amount
,?
,?
,?
,?
- ,?
- ,?
,(?,?)::taler_amount
,?
)"""
@@ -277,16 +287,17 @@ class Database(dbConfig: String): java.io.Closeable {
stmt.setLong(4, executionTime)
stmt.setString(5, paymentData.debitPaytoUri)
stmt.setString(6, paymentData.bankTransferId)
- stmt.setLong(7, refundTimestamp)
- stmt.setString(8, requestUid)
- stmt.setLong(9, bounceAmount.value)
- stmt.setInt(10, bounceAmount.fraction)
- stmt.setString(11, bounceSubject)
+ stmt.setLong(7, bounceAmount.value)
+ stmt.setInt(8, bounceAmount.fraction)
+ stmt.setLong(9, refundTimestamp)
stmt.executeQuery().use {
when {
!it.next() -> throw Exception("Inserting malformed incoming payment gave no outcome")
- it.getBoolean("out_found") -> false
- else -> true
+ else -> IncomingBounceRegistrationResult(
+ it.getLong("out_tx_id"),
+ it.getString("out_bounce_id"),
+ !it.getBoolean("out_found")
+ )
}
}
}
@@ -301,9 +312,9 @@ class Database(dbConfig: String): java.io.Closeable {
suspend fun registerTalerableIncoming(
paymentData: IncomingPayment,
reservePub: ByteArray
- ): Boolean = runConn { conn ->
+ ): IncomingRegistrationResult = runConn { conn ->
val stmt = conn.prepareStatement("""
- SELECT out_found
+ SELECT out_found, out_tx_id
FROM register_incoming_and_talerable(
(?,?)::taler_amount
,?
@@ -325,8 +336,10 @@ class Database(dbConfig: String): java.io.Closeable {
stmt.executeQuery().use {
when {
!it.next() -> throw Exception("Inserting talerable incoming payment gave no outcome")
- it.getBoolean("out_found") -> false
- else -> true
+ else -> IncomingRegistrationResult(
+ it.getLong("out_tx_id"),
+ !it.getBoolean("out_found")
+ )
}
}
}
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt
@@ -273,15 +273,14 @@ private suspend fun ingestOutgoingPayment(
db: Database,
payment: OutgoingPayment
) {
- when (val result = db.registerOutgoing(payment)) {
- OutgoingRegistrationResult.AlreadyRegistered ->
- logger.debug("OUT '${payment.bankTransferId}' already seen")
- is OutgoingRegistrationResult.New -> {
- if (result.initiated)
- logger.debug("$payment")
- else
- logger.debug("$payment recovered")
- }
+ val result = db.registerOutgoing(payment)
+ if (result.new) {
+ if (result.initiated)
+ logger.debug("$payment")
+ else
+ logger.debug("$payment recovered")
+ } else {
+ logger.debug("OUT '${payment.bankTransferId}' already seen")
}
}
@@ -303,23 +302,19 @@ private suspend fun ingestIncomingPayment(
logger.debug("Incoming payment with UID '${payment.bankTransferId}'" +
" has invalid subject: ${payment.wireTransferSubject}."
)
- // Generate bounce bank ID from the bounced transaction bank ID
- val hash = CryptoUtil.hashStringSHA256(payment.bankTransferId)
- val encoded = Base32Crockford.encode(hash)
- val bounceId = encoded.take(35)
- if (db.registerMalformedIncoming(
- payment,
- bounceId,
+ val result = db.registerMalformedIncoming(
+ payment,
payment.amount,
- "Bounce: ${payment.bankTransferId}",
Instant.now()
- )) {
- logger.debug("$payment bounced in '$bounceId'")
+ )
+ if (result.new) {
+ logger.debug("$payment bounced in '${result.bounceId}'")
} else {
- logger.debug("IN '${payment.bankTransferId}' already seen and bounced in '$bounceId'")
+ logger.debug("IN '${payment.bankTransferId}' already seen and bounced in '${result.bounceId}'")
}
} else {
- if (db.registerTalerableIncoming(payment, reservePub)) {
+ val result = db.registerTalerableIncoming(payment, reservePub)
+ if (result.new) {
logger.debug("$payment")
} else {
logger.debug("IN '${payment.bankTransferId}' already seen")
diff --git a/nexus/src/test/kotlin/DatabaseTest.kt b/nexus/src/test/kotlin/DatabaseTest.kt
@@ -18,25 +18,25 @@ class OutgoingPaymentsTest {
PaymentInitiationOutcome.SUCCESS,
db.initiatedPaymentCreate(genInitPay("waiting for reconciliation", "first"))
)
- assertEquals(
- OutgoingRegistrationResult.New(true),
- db.registerOutgoing(this)
- )
- assertEquals(
- OutgoingRegistrationResult.AlreadyRegistered,
- db.registerOutgoing(this)
- )
+ db.registerOutgoing(this).run {
+ assertTrue(new,)
+ assertTrue(initiated)
+ }
+ db.registerOutgoing(this).run {
+ assertFalse(new)
+ assertTrue(initiated)
+ }
}
// Without reconciling
genOutPay("not paid by nexus", "second").run {
- assertEquals(
- OutgoingRegistrationResult.New(false),
- db.registerOutgoing(this)
- )
- assertEquals(
- OutgoingRegistrationResult.AlreadyRegistered,
- db.registerOutgoing(this)
- )
+ db.registerOutgoing(this).run {
+ assertTrue(new)
+ assertFalse(initiated)
+ }
+ db.registerOutgoing(this).run {
+ assertFalse(new)
+ assertFalse(initiated)
+ }
}
}
}
@@ -50,20 +50,20 @@ class IncomingPaymentsTest {
runBlocking {
// creating and bouncing one incoming transaction.
val payment = genInPay("incoming and bounced")
- assertTrue(db.registerMalformedIncoming(
+ db.registerMalformedIncoming(
payment,
- "UID",
TalerAmount(2, 53000000, "KUDOS"),
- "Bounce UID",
Instant.now()
- ))
- assertFalse(db.registerMalformedIncoming(
+ ).run {
+ assertTrue(new)
+ }
+ db.registerMalformedIncoming(
payment,
- "UID",
TalerAmount(2, 53000000, "KUDOS"),
- "Bounce UID",
Instant.now()
- ))
+ ).run {
+ assertFalse(new)
+ }
db.runConn {
// Checking one incoming got created
val checkIncoming = it.prepareStatement("""
@@ -111,10 +111,10 @@ class IncomingPaymentsTest {
val inc = genInPay("reserve-pub")
// Checking the reserve is not found.
assertFalse(db.isReservePubFound(reservePub))
- assertTrue(db.registerTalerableIncoming(inc, reservePub))
+ assertTrue(db.registerTalerableIncoming(inc, reservePub).new)
// Checking the reserve is not found.
assertTrue(db.isReservePubFound(reservePub))
- assertFalse(db.registerTalerableIncoming(inc, reservePub))
+ assertFalse(db.registerTalerableIncoming(inc, reservePub).new)
}
}
}
