Improve nexus logic and make bounce bank ID deterministic

8 files changed, 315 insertions, 503 deletions

diff --git a/Makefile b/Makefile
index 8935295a..93429046 100644
--- a/Makefile
+++ b/Makefile
@@ -63,6 +63,11 @@ install-nobuild-bank-files:
 	install -m 644 -D -t $(sql_dir) database-versioning/libeufin-bank*.sql
 	install -m 644 -D -t $(sql_dir) database-versioning/libeufin-conversion*.sql
 
+.PHONY: install-nobuild-nexus-files
+install-nobuild-nexus-files:
+	install -m 644 -D -t $(config_dir) contrib/nexus.conf
+	install -m 644 -D -t $(sql_dir) database-versioning/libeufin-nexus*.sql
+
 .PHONY: install-nobuild-bank
 install-nobuild-bank: install-nobuild-common install-nobuild-bank-files
 	install -d $(spa_dir)
@@ -76,9 +81,8 @@ install-nobuild-bank: install-nobuild-common install-nobuild-bank-files
 	install -m=644 -D -t $(lib_dir) bank/build/install/bank-shadow/lib/bank-*.jar
 
 .PHONY: install-nobuild-nexus
-install-nobuild-nexus: install-nobuild-common
+install-nobuild-nexus: install-nobuild-common install-nobuild-nexus-files
 	install -m 644 -D -t $(config_dir) contrib/nexus.conf
-	install -m 644 -D -t $(sql_dir) database-versioning/libeufin-nexus*.sql
 	install -m 644 -D -t $(man_dir)/man1 doc/prebuilt/man/libeufin-nexus.1
 	install -m 644 -D -t $(man_dir)/man5 doc/prebuilt/man/libeufin-nexus.conf.5
 	install -D -t $(bin_dir) contrib/libeufin-nexus-dbinit
@@ -102,6 +106,10 @@ check: install-nobuild-bank-files
 test: install-nobuild-bank-files
 	./gradlew test --tests $(test) -i
 
+.PHONY: nexus-test
+nexus-test: install-nobuild-nexus-files
+	./gradlew :nexus:test --tests $(test) -i
+
 .PHONY: integration
 integration:
 	./gradlew :integration:run --console=plain --args="$(test)"
diff --git a/database-versioning/libeufin-nexus-0001.sql b/database-versioning/libeufin-nexus-0001.sql
index 52143aa8..6e32e0b1 100644
--- a/database-versioning/libeufin-nexus-0001.sql
+++ b/database-versioning/libeufin-nexus-0001.sql
@@ -50,13 +50,13 @@ CREATE TABLE IF NOT EXISTS incoming_transactions
   ,wire_transfer_subject TEXT NOT NULL
   ,execution_time INT8 NOT NULL
   ,debit_payto_uri TEXT NOT NULL
-  ,bank_transfer_id TEXT NOT NULL -- EBICS or Depolymerizer (generic)
+  ,bank_transfer_id TEXT NOT NULL UNIQUE -- EBICS or Depolymerizer (generic)
   );
 
 -- only active in exchange mode. Note: duplicate keys are another reason to bounce.
 CREATE TABLE IF NOT EXISTS talerable_incoming_transactions
   (incoming_transaction_id INT8 NOT NULL UNIQUE REFERENCES incoming_transactions(incoming_transaction_id) ON DELETE CASCADE
-   ,reserve_public_key BYTEA NOT NULL CHECK (LENGTH(reserve_public_key)=32) UNIQUE
+   ,reserve_public_key BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_public_key)=32)
   );
 
 CREATE TABLE IF NOT EXISTS outgoing_transactions
@@ -65,7 +65,7 @@ CREATE TABLE IF NOT EXISTS outgoing_transactions
   ,wire_transfer_subject TEXT
   ,execution_time INT8 NOT NULL
   ,credit_payto_uri TEXT
-  ,bank_transfer_id TEXT NOT NULL
+  ,bank_transfer_id TEXT NOT NULL UNIQUE
   );
 
 CREATE TABLE IF NOT EXISTS initiated_outgoing_transactions
@@ -76,7 +76,7 @@ CREATE TABLE IF NOT EXISTS initiated_outgoing_transactions
   ,last_submission_time INT8
   ,submission_counter INT NOT NULL DEFAULT 0
   ,credit_payto_uri TEXT NOT NULL
-  ,outgoing_transaction_id INT8 REFERENCES outgoing_transactions (outgoing_transaction_id)
+  ,outgoing_transaction_id INT8 UNIQUE REFERENCES outgoing_transactions (outgoing_transaction_id)
   ,submitted submission_state DEFAULT 'unsubmitted'
   ,hidden BOOL DEFAULT FALSE -- FIXME: explain this.
   ,request_uid TEXT NOT NULL UNIQUE CHECK (char_length(request_uid) <= 35)
diff --git a/database-versioning/libeufin-nexus-procedures.sql b/database-versioning/libeufin-nexus-procedures.sql
index 918bedf3..48250261 100644
--- a/database-versioning/libeufin-nexus-procedures.sql
+++ b/database-versioning/libeufin-nexus-procedures.sql
@@ -1,197 +1,172 @@
 BEGIN;
 SET search_path TO libeufin_nexus;
 
-CREATE OR REPLACE FUNCTION create_incoming_and_bounce(
+CREATE FUNCTION register_outgoing(
   IN in_amount taler_amount
   ,IN in_wire_transfer_subject TEXT
   ,IN in_execution_time BIGINT
-  ,IN in_debit_payto_uri TEXT
+  ,IN in_credit_payto_uri TEXT
   ,IN in_bank_transfer_id TEXT
-  ,IN in_timestamp BIGINT
-  ,IN in_request_uid TEXT
-  ,IN in_refund_amount taler_amount
-  ,OUT out_ok BOOLEAN
-) RETURNS BOOLEAN
+  ,OUT out_found BOOLEAN
+  ,OUT out_initiated BOOLEAN
+)
 LANGUAGE plpgsql AS $$
 DECLARE
-new_tx_id INT8;
-new_init_id INT8;
+init_id BIGINT;
+tx_id BIGINT;
 BEGIN
--- creating the bounced incoming transaction.
-INSERT INTO incoming_transactions (
-  amount
-  ,wire_transfer_subject
-  ,execution_time
-  ,debit_payto_uri
-  ,bank_transfer_id
+-- Check if already registered
+SELECT outgoing_transaction_id INTO 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 ?
+ELSE
+  -- Store the transaction in the database
+  INSERT INTO outgoing_transactions (
+    amount
+    ,wire_transfer_subject
+    ,execution_time
+    ,credit_payto_uri
+    ,bank_transfer_id
   ) VALUES (
     in_amount
     ,in_wire_transfer_subject
     ,in_execution_time
-    ,in_debit_payto_uri
+    ,in_credit_payto_uri
     ,in_bank_transfer_id
-  ) RETURNING incoming_transaction_id INTO new_tx_id;
-
--- creating its reimbursement.
-INSERT INTO initiated_outgoing_transactions (
-  amount
-  ,wire_transfer_subject
-  ,credit_payto_uri
-  ,initiation_time
-  ,request_uid
-  ) VALUES (
-    in_refund_amount
-    ,'refund: ' || in_wire_transfer_subject
-    ,in_debit_payto_uri
-    ,in_timestamp
-    ,in_request_uid
-  ) RETURNING initiated_outgoing_transaction_id INTO new_init_id;
+  )
+    RETURNING outgoing_transaction_id
+      INTO tx_id;
 
-INSERT INTO bounced_transactions (
-  incoming_transaction_id
-  ,initiated_outgoing_transaction_id
-) VALUES (
-  new_tx_id
-  ,new_init_id
-);
-out_ok = TRUE;
+  -- Reconciles the related initiated payment
+  UPDATE initiated_outgoing_transactions
+    SET outgoing_transaction_id = 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';
 
-COMMENT ON FUNCTION create_incoming_and_bounce(taler_amount, TEXT, BIGINT, TEXT, TEXT, BIGINT, TEXT, taler_amount)
-  IS 'creates one incoming transaction with a bounced state and initiates its related refund.';
-
-CREATE OR REPLACE FUNCTION create_outgoing_payment(
+CREATE FUNCTION register_incoming(
   IN in_amount taler_amount
   ,IN in_wire_transfer_subject TEXT
   ,IN in_execution_time BIGINT
-  ,IN in_credit_payto_uri TEXT
+  ,IN in_debit_payto_uri TEXT
   ,IN in_bank_transfer_id TEXT
-  ,IN in_initiated_id BIGINT
-  ,OUT out_nx_initiated BOOLEAN
+  ,OUT out_found BOOLEAN
+  ,OUT out_tx_id BIGINT
 )
 LANGUAGE plpgsql AS $$
-DECLARE
-new_outgoing_transaction_id BIGINT;
 BEGIN
-
-IF in_initiated_id IS NULL THEN
-  out_nx_initiated = FALSE;
+-- Check if already registered
+SELECT incoming_transaction_id INTO out_tx_id
+  FROM incoming_transactions
+  WHERE bank_transfer_id = in_bank_transfer_id;
+IF FOUND THEN
+  out_found = true;
+  -- TODO Should we check that amount and other info match ?
 ELSE
-  PERFORM 1
-    FROM initiated_outgoing_transactions
-    WHERE initiated_outgoing_transaction_id = in_initiated_id;
-    IF NOT FOUND THEN
-      out_nx_initiated = TRUE;
-      RETURN;
-      END IF;
-END IF;
-
-INSERT INTO outgoing_transactions (
-  amount
-  ,wire_transfer_subject
-  ,execution_time
-  ,credit_payto_uri
-  ,bank_transfer_id
-) VALUES (
-  in_amount
-  ,in_wire_transfer_subject
-  ,in_execution_time
-  ,in_credit_payto_uri
-  ,in_bank_transfer_id
-)
-  RETURNING outgoing_transaction_id
-    INTO new_outgoing_transaction_id;
-
-IF in_initiated_id IS NOT NULL
-THEN
-  UPDATE initiated_outgoing_transactions
-    SET outgoing_transaction_id = new_outgoing_transaction_id
-    WHERE initiated_outgoing_transaction_id = in_initiated_id;
+  -- Store the transaction in the database
+  INSERT INTO incoming_transactions (
+    amount
+    ,wire_transfer_subject
+    ,execution_time
+    ,debit_payto_uri
+    ,bank_transfer_id
+  ) VALUES (
+    in_amount
+    ,in_wire_transfer_subject
+    ,in_execution_time
+    ,in_debit_payto_uri
+    ,in_bank_transfer_id
+  ) RETURNING incoming_transaction_id INTO out_tx_id;
 END IF;
 END $$;
+COMMENT ON FUNCTION register_incoming
+  IS 'Register an incoming payment';
 
-COMMENT ON FUNCTION create_outgoing_payment(taler_amount, TEXT, BIGINT, TEXT, TEXT, BIGINT)
-  IS 'Creates a new outgoing payment and optionally reconciles the related initiated payment with it.  If the initiated payment to reconcile is not found, it inserts NOTHING.';
-
-CREATE OR REPLACE FUNCTION bounce_payment(
-  IN in_incoming_transaction_id BIGINT
-  ,IN in_initiation_time BIGINT
+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
-  ,OUT out_nx_incoming_payment BOOLEAN
+  ,IN in_bounce_amount taler_amount
+  ,IN in_bounce_subject TEXT
+  ,OUT out_found BOOLEAN -- TODO return tx_id
 )
 LANGUAGE plpgsql AS $$
+DECLARE
+tx_id BIGINT;
+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;
 
+-- Initiate the bounce transaction
 INSERT INTO initiated_outgoing_transactions (
   amount
   ,wire_transfer_subject
   ,credit_payto_uri
   ,initiation_time
   ,request_uid
-  )
-  SELECT
-    amount
-    ,'refund: ' || wire_transfer_subject
-    ,debit_payto_uri
-    ,in_initiation_time
+  ) VALUES (
+    in_bounce_amount
+    ,in_bounce_subject
+    ,in_debit_payto_uri
+    ,in_timestamp
     ,in_request_uid
-    FROM incoming_transactions
-    WHERE incoming_transaction_id = in_incoming_transaction_id;
-
-IF NOT FOUND THEN
-  out_nx_incoming_payment=TRUE;
-  RETURN;
+  )
+  ON CONFLICT (request_uid) DO NOTHING
+  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;
 END IF;
-out_nx_incoming_payment=FALSE;
-
--- finally setting the payment as bounced.  Not checking
--- the update outcome since the row existence was checked
--- just above.
-
-UPDATE incoming_transactions
-  SET bounced = true
-  WHERE incoming_transaction_id = in_incoming_transaction_id;
 END $$;
+COMMENT ON FUNCTION register_incoming_and_bounce
+  IS 'Register an incoming payment and bounce it';
 
-COMMENT ON FUNCTION bounce_payment(BIGINT, BIGINT, TEXT) IS 'Marks an incoming payment as bounced and initiates its refunding payment';
-
-CREATE OR REPLACE FUNCTION create_incoming_talerable(
+CREATE FUNCTION register_incoming_and_talerable(
   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_reserve_public_key BYTEA
-  ,OUT out_ok BOOLEAN
-) RETURNS BOOLEAN
+  ,OUT out_found BOOLEAN -- TODO return tx_id
+)
 LANGUAGE plpgsql AS $$
 DECLARE
-new_tx_id INT8;
+tx_id INT8;
 BEGIN
-INSERT INTO incoming_transactions (
-  amount
-  ,wire_transfer_subject
-  ,execution_time
-  ,debit_payto_uri
-  ,bank_transfer_id
-  ) VALUES (
-    in_amount
-    ,in_wire_transfer_subject
-    ,in_execution_time
-    ,in_debit_payto_uri
-    ,in_bank_transfer_id
-  ) RETURNING incoming_transaction_id INTO new_tx_id;
+-- 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;
+
+-- Register as talerable bounce
 INSERT INTO talerable_incoming_transactions (
   incoming_transaction_id
   ,reserve_public_key
 ) VALUES (
-  new_tx_id
+  tx_id
   ,in_reserve_public_key
-);
-out_ok = TRUE;
+) ON CONFLICT (incoming_transaction_id) DO NOTHING;
 END $$;
-
-COMMENT ON FUNCTION create_incoming_talerable(taler_amount, TEXT, BIGINT, TEXT, TEXT, BYTEA) IS '
+COMMENT ON FUNCTION register_incoming_and_talerable IS '
 Creates one row in the incoming transactions table and one row
 in the talerable transactions table.  The talerable row links the
 incoming one.';
\ No newline at end of file
diff --git a/nexus/build.gradle b/nexus/build.gradle
index 224f6b60..75491bb3 100644
--- a/nexus/build.gradle
+++ b/nexus/build.gradle
@@ -57,11 +57,6 @@ dependencies {
     testImplementation("io.ktor:ktor-client-mock:$ktor_version")
 }
 
-test {
-    failFast = true
-    testLogging.showStandardStreams = false
-}
-
 application {
     mainClassName = "tech.libeufin.nexus.MainKt"
     applicationName = "libeufin-nexus"
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
index 7d9cb88a..745aaeb5 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
@@ -48,7 +48,7 @@ data class IncomingPayment(
     val bankTransferId: String
 )  {
     override fun toString(): String {
-        return ">> ${executionTime.fmtDate()} $amount $bankTransferId debitor=$debitPaytoUri subject=$wireTransferSubject"
+        return "IN ${executionTime.fmtDate()} '$amount $bankTransferId' debitor=$debitPaytoUri subject=$wireTransferSubject"
     }
 }
 
@@ -128,28 +128,14 @@ data class OutgoingPayment(
     val wireTransferSubject: String? = null // not showing in camt.054
 ) {
     override fun toString(): String {
-        return "<< ${executionTime.fmtDate()} $amount $bankTransferId creditor=$creditPaytoUri subject=$wireTransferSubject"
+        return "OUT ${executionTime.fmtDate()} $amount '$bankTransferId' creditor=$creditPaytoUri subject=$wireTransferSubject"
     }
 }
 
-/**
- * Witnesses the outcome of inserting an outgoing
- * payment into the database.
- */
-enum class OutgoingPaymentOutcome {
-    /**
-     * The caller wanted to link a previously initiated payment
-     * to this outgoing one, but the row ID passed to the inserting
-     * function could not be found in the payment initiations table.
-     * Note: NO insertion takes place in this case.
-     */
-    INITIATED_COUNTERPART_NOT_FOUND,
-    /**
-     * The outgoing payment got inserted and _in case_ the caller
-     * wanted to link a previously initiated payment to this one, that
-     * succeeded too.
-     */
-    SUCCESS
+/** Outgoing payments registration result */
+sealed class OutgoingRegistrationResult {
+    data class New(val initiated: Boolean): OutgoingRegistrationResult()
+    data object AlreadyRegistered: OutgoingRegistrationResult()
 }
 
 /**
@@ -210,28 +196,21 @@ class Database(dbConfig: String): java.io.Closeable {
     // OUTGOING PAYMENTS METHODS
 
     /**
-     * Creates one outgoing payment OPTIONALLY reconciling it with its
+     * Register an outgoing payment OPTIONALLY reconciling it with its
      * initiated payment counterpart.
      *
      * @param paymentData information about the outgoing payment.
-     * @param reconcileId optional row ID of the initiated payment
-     *        that will reference this one.  If null, then only the
-     *        outgoing payment record gets inserted.
      * @return operation outcome enum.
      */
-    suspend fun outgoingPaymentCreate(
-        paymentData: OutgoingPayment,
-        reconcileId: Long? = null
-    ): OutgoingPaymentOutcome = runConn {
+    suspend fun registerOutgoing(paymentData: OutgoingPayment): OutgoingRegistrationResult = runConn {        
         val stmt = it.prepareStatement("""
-            SELECT out_nx_initiated
-              FROM create_outgoing_payment(
+            SELECT out_initiated, out_found
+              FROM register_outgoing(
                 (?,?)::taler_amount
                 ,?
                 ,?
                 ,?
                 ,?
-                ,?
               )"""
         )
         val executionTime = paymentData.executionTime.toDbMicros()
@@ -242,116 +221,113 @@ class Database(dbConfig: String): java.io.Closeable {
         stmt.setLong(4, executionTime)
         stmt.setString(5, paymentData.creditPaytoUri)
         stmt.setString(6, paymentData.bankTransferId)
-        if (reconcileId == null)
-            stmt.setNull(7, java.sql.Types.BIGINT)
-        else
-            stmt.setLong(7, reconcileId)
 
         stmt.executeQuery().use {
-            if (!it.next()) throw Exception("Inserting outgoing payment gave no outcome.")
-            if (it.getBoolean("out_nx_initiated"))
-                return@runConn OutgoingPaymentOutcome.INITIATED_COUNTERPART_NOT_FOUND
-        }
-        return@runConn OutgoingPaymentOutcome.SUCCESS
-    }
-
-    /**
-     * Checks if the outgoing payment was already processed by Nexus.
-     *
-     * @param bankUid unique identifier assigned by the bank to the payment.
-     *        Normally, that's the <UETR> value found in camt.05x records.  Outgoing
-     *        payment have been observed to _lack_ the <AcctSvcrRef> element.
-     * @return true if found, false otherwise
-     */
-    suspend fun isOutgoingPaymentSeen(bankUid: String): Boolean = runConn { conn ->
-        val stmt = conn.prepareStatement("""
-             SELECT 1
-               FROM outgoing_transactions
-               WHERE bank_transfer_id = ?;
-        """)
-        stmt.setString(1, bankUid)
-        val res = stmt.executeQuery()
-        res.use {
-            return@runConn it.next()
+            when {
+                !it.next() -> throw Exception("Inserting outgoing payment gave no outcome.")
+                it.getBoolean("out_found") -> OutgoingRegistrationResult.AlreadyRegistered
+                else -> OutgoingRegistrationResult.New(
+                    it.getBoolean("out_initiated")
+                )
+            }
         }
     }
 
     // INCOMING PAYMENTS METHODS
 
     /**
-     * Flags an incoming payment as bounced.  NOTE: the flag merely means
-     * that the payment had an invalid subject for a Taler withdrawal _and_
-     * it got initiated as an outgoing payments.  In NO way this flag
-     * means that the actual value was returned to the initial debtor.
+     * Register an incoming payment and bounce it
      *
-     * @param rowId row ID of the payment to flag as bounced.
-     * @param initiatedRequestUid unique identifier for the outgoing payment to
-     *                            initiate for this bouncing.
-     * @return true if the payment could be set as bounced, false otherwise.
+     * @param paymentData information about the incoming payment
+     * @param requestUid unique identifier of the bounce outgoing payment to
+     *                   initiate
+     * @param bounceAmount amount to send back to the original debtor
+     * @param bounceSubject subject of the bounce outhoing payment
+     * @return true if new
      */
-    suspend fun incomingPaymentSetAsBounced(rowId: Long, initiatedRequestUid: String): Boolean = runConn { conn ->
-        val timestamp = Instant.now().toDbMicros()
-            ?: throw Exception("Could not convert Instant.now() to microseconds, won't bounce this payment.")
-        val stmt = conn.prepareStatement("""
-             SELECT out_nx_incoming_payment
-               FROM bounce_payment(?,?,?)
-             """
+    suspend fun registerMalformedIncoming(
+        paymentData: IncomingPayment,
+        requestUid: String,
+        bounceAmount: TalerAmount,
+        bounceSubject: String,
+        now: Instant
+    ): Boolean = runConn {       
+        println("$paymentData $requestUid $bounceAmount $bounceSubject") 
+        val stmt = it.prepareStatement("""
+            SELECT out_found
+              FROM register_incoming_and_bounce(
+                (?,?)::taler_amount
+                ,?
+                ,?
+                ,?
+                ,?
+                ,?
+                ,?
+                ,(?,?)::taler_amount
+                ,?
+              )"""
         )
-        stmt.setLong(1, rowId)
-        stmt.setLong(2, timestamp)
-        stmt.setString(3, initiatedRequestUid)
-        stmt.executeQuery().use { maybeResult ->
-            if (!maybeResult.next()) throw Exception("Expected outcome from the SQL bounce_payment function")
-            return@runConn !maybeResult.getBoolean("out_nx_incoming_payment")
+        val refundTimestamp = now.toDbMicros()
+            ?: throw Exception("Could not convert refund execution time from Instant.now() to microsends.")
+        val executionTime = paymentData.executionTime.toDbMicros()
+            ?: throw Exception("Could not convert payment execution time from Instant to microseconds.")
+        stmt.setLong(1, paymentData.amount.value)
+        stmt.setInt(2, paymentData.amount.fraction)
+        stmt.setString(3, paymentData.wireTransferSubject)
+        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.executeQuery().use {
+            when {
+                !it.next() -> throw Exception("Inserting malformed incoming payment gave no outcome")
+                it.getBoolean("out_found") -> false
+                else -> true
+            }
         }
     }
 
     /**
-     * Creates an incoming payment as bounced _and_ initiates its
-     * reimbursement.
+     * Register an talerable incoming payment
      *
-     * @param paymentData information related to the incoming payment.
-     * @param requestUid unique identifier of the outgoing payment to
-     *                   initiate, in order to reimburse the bounced tx.
-     * @param refundAmount amount to send back to the original debtor.  If
-     *                     null, it defaults to the amount of the bounced
-     *                     incoming payment.
+     * @param paymentData incoming talerable payment.
+     * @param reservePub reserve public key.  The caller is
+     *        responsible to check it.
      */
-    suspend fun incomingPaymentCreateBounced(
+    suspend fun registerTalerableIncoming(
         paymentData: IncomingPayment,
-        requestUid: String,
-        refundAmount: TalerAmount? = null
-        ): Boolean = runConn { conn ->
-        val refundTimestamp = Instant.now().toDbMicros()
-            ?: throw Exception("Could not convert refund execution time from Instant.now() to microsends.")
+        reservePub: ByteArray
+    ): Boolean = runConn { conn ->
+        val stmt = conn.prepareStatement("""
+            SELECT out_found
+              FROM register_incoming_and_talerable(
+                (?,?)::taler_amount
+                ,?
+                ,?
+                ,?
+                ,?
+                ,?
+              )"""
+        )
         val executionTime = paymentData.executionTime.toDbMicros()
             ?: throw Exception("Could not convert payment execution time from Instant to microseconds.")
-        val stmt = conn.prepareStatement("""
-            SELECT out_ok FROM create_incoming_and_bounce (
-              (?,?)::taler_amount
-              ,?
-              ,?
-              ,?
-              ,?
-              ,?
-              ,?
-              ,(?,?)::taler_amount
-            )""")
         stmt.setLong(1, paymentData.amount.value)
         stmt.setInt(2, paymentData.amount.fraction)
         stmt.setString(3, paymentData.wireTransferSubject)
         stmt.setLong(4, executionTime)
         stmt.setString(5, paymentData.debitPaytoUri)
         stmt.setString(6, paymentData.bankTransferId)
-        stmt.setLong(7, refundTimestamp)
-        stmt.setString(8, requestUid)
-        val finalRefundAmount: TalerAmount = refundAmount ?: paymentData.amount
-        stmt.setLong(9, finalRefundAmount.value)
-        stmt.setInt(10, finalRefundAmount.fraction)
-        val res = stmt.executeQuery()
-        res.use {
-            if (!it.next()) return@runConn false
-            return@runConn it.getBoolean("out_ok")
+        stmt.setBytes(7, reservePub)
+        stmt.executeQuery().use {
+            when {
+                !it.next() -> throw Exception("Inserting talerable incoming payment gave no outcome")
+                it.getBoolean("out_found") -> false
+                else -> true
+            }
         }
     }
 
@@ -392,26 +368,6 @@ class Database(dbConfig: String): java.io.Closeable {
     }
 
     /**
-     * Checks if the incoming payment was already processed by Nexus.
-     *
-     * @param bankUid unique identifier assigned by the bank to the payment.
-     *        Normally, that's the <AcctSvcrRef> value found in camt.05x records.
-     * @return true if found, false otherwise
-     */
-    suspend fun isIncomingPaymentSeen(bankUid: String): Boolean = runConn { conn ->
-        val stmt = conn.prepareStatement("""
-             SELECT 1
-               FROM incoming_transactions
-               WHERE bank_transfer_id = ?;
-        """)
-        stmt.setString(1, bankUid)
-        val res = stmt.executeQuery()
-        res.use {
-            return@runConn it.next()
-        }
-    }
-
-    /**
      * Checks if the reserve public key already exists.
      *
      * @param maybeReservePub reserve public key to look up
@@ -430,84 +386,6 @@ class Database(dbConfig: String): java.io.Closeable {
         }
     }
 
-    /**
-     * Creates an incoming transaction row and  links a new talerable
-     * row to it.
-     *
-     * @param paymentData incoming talerable payment.
-     * @param reservePub reserve public key.  The caller is
-     *        responsible to check it.
-     */
-    suspend fun incomingTalerablePaymentCreate(
-        paymentData: IncomingPayment,
-        reservePub: ByteArray
-    ): Boolean = runConn { conn ->
-        val stmt = conn.prepareStatement("""
-           SELECT out_ok FROM create_incoming_talerable(
-              (?,?)::taler_amount
-              ,?
-              ,?
-              ,?
-              ,?
-              ,?
-           )""")
-        bindIncomingPayment(paymentData, stmt)
-        stmt.setBytes(7, reservePub)
-        stmt.executeQuery().use {
-            if (!it.next()) return@runConn false
-            return@runConn it.getBoolean("out_ok")
-        }
-    }
-
-    /**
-     * Binds the values of an incoming payment to the prepared
-     * statement's placeholders.  Warn: may easily break in case
-     * the placeholders get their positions changed!
-     *
-     * @param data incoming payment to bind to the placeholders
-     * @param stmt statement to receive the values in its placeholders
-     */
-    private fun bindIncomingPayment(
-        data: IncomingPayment,
-        stmt: PreparedStatement
-    ) {
-        stmt.setLong(1, data.amount.value)
-        stmt.setInt(2, data.amount.fraction)
-        stmt.setString(3, data.wireTransferSubject)
-        val executionTime = data.executionTime.toDbMicros() ?: run {
-            throw Exception("Execution time could not be converted to microseconds for the database.")
-        }
-        stmt.setLong(4, executionTime)
-        stmt.setString(5, data.debitPaytoUri)
-        stmt.setString(6, data.bankTransferId)
-    }
-    /**
-     * Creates a new incoming payment record in the database.  It does NOT
-     * update the "talerable" table.
-     *
-     * @param paymentData information related to the incoming payment.
-     * @return true on success, false otherwise.
-     */
-    suspend fun incomingPaymentCreate(paymentData: IncomingPayment): Boolean = runConn { conn ->
-        val stmt = conn.prepareStatement("""
-            INSERT INTO incoming_transactions (
-              amount
-              ,wire_transfer_subject
-              ,execution_time
-              ,debit_payto_uri
-              ,bank_transfer_id
-            ) VALUES (
-              (?,?)::taler_amount
-              ,?
-              ,?
-              ,?
-              ,?
-            )
-        """)
-        bindIncomingPayment(paymentData, stmt)
-        return@runConn stmt.maybeUpdate()
-    }
-
     // INITIATED PAYMENTS METHODS
 
     /**
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt
index 0f42fee3..473b9f96 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/EbicsFetch.kt
@@ -247,25 +247,17 @@ fun removeSubjectNoise(subject: String): String? {
  * Checks the two conditions that may invalidate one incoming
  * payment: subject validity and availability.
  *
- * @param db database connection.
  * @param payment incoming payment whose subject is to be checked.
  * @return [ByteArray] as the reserve public key, or null if the
  *         payment cannot lead to a Taler withdrawal.
  */
 private suspend fun getTalerReservePub(
-    db: Database,
     payment: IncomingPayment
 ): ByteArray? {
     // Removing noise around the potential reserve public key.
     val maybeReservePub = removeSubjectNoise(payment.wireTransferSubject) ?: return null
     // Checking validity first.
     val dec = isReservePub(maybeReservePub) ?: return null
-    // Now checking availability.
-    val maybeUnavailable = db.isReservePubFound(dec)
-    if (maybeUnavailable) {
-        logger.error("Incoming payment with subject '${payment.wireTransferSubject}' exists already")
-        return null
-    }
     return dec
 }
 
@@ -281,27 +273,15 @@ private suspend fun ingestOutgoingPayment(
     db: Database,
     payment: OutgoingPayment
 ) {
-    logger.debug("Ingesting outgoing payment UID ${payment.bankTransferId}, subject ${payment.wireTransferSubject}")
-    // Check if the payment was ingested already.
-    if (db.isOutgoingPaymentSeen(payment.bankTransferId)) {
-        logger.debug("Outgoing payment with UID '${payment.bankTransferId}' already seen.")
-        return
-    }
-    /**
-     * Getting the initiate payment to link to this.  A missing initiated
-     * payment could mean that a third party is downloading the bank account
-     * history (to conduct an audit, for example)
-     */
-    val initId: Long? = db.initiatedPaymentGetFromUid(payment.bankTransferId);
-    if (initId == null)
-        logger.info("Outgoing payment lacks initiated counterpart with UID ${payment.bankTransferId}")
-    // store the payment and its (maybe null) linked init
-    val insertionResult = db.outgoingPaymentCreate(payment, initId)
-    if (insertionResult != OutgoingPaymentOutcome.SUCCESS) {
-        throw Exception("Could not store outgoing payment with UID " +
-                "'${payment.bankTransferId}' and update its related initiation." +
-                "  DB result: $insertionResult"
-        )
+    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")
+        }
     }
 }
 
@@ -312,29 +292,39 @@ private suspend fun ingestOutgoingPayment(
  *
  * @param db database handle.
  * @param currency fiat currency of the watched bank account.
- * @param incomingPayment payment to (maybe) ingest.
+ * @param payment payment to (maybe) ingest.
  */
 private suspend fun ingestIncomingPayment(
     db: Database,
-    incomingPayment: IncomingPayment
+    payment: IncomingPayment
 ) {
-    logger.debug("Ingesting incoming payment UID: ${incomingPayment.bankTransferId}, subject: ${incomingPayment.wireTransferSubject}")
-    if (db.isIncomingPaymentSeen(incomingPayment.bankTransferId)) {
-        logger.debug("Incoming payment with UID '${incomingPayment.bankTransferId}' already seen.")
-        return
-    }
-    val reservePub = getTalerReservePub(db, incomingPayment)
+    val reservePub = getTalerReservePub(payment)
     if (reservePub == null) {
-        logger.debug("Incoming payment with UID '${incomingPayment.bankTransferId}'" +
-                " has invalid subject: ${incomingPayment.wireTransferSubject}."
-        )
-        db.incomingPaymentCreateBounced(
-            incomingPayment,
-            UUID.randomUUID().toString().take(35)
+        logger.debug("Incoming payment with UID '${payment.bankTransferId}'" +
+                " has invalid subject: ${payment.wireTransferSubject}."
         )
-        return
+        // 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, 
+            payment.amount, 
+            "Bounce: ${payment.bankTransferId}", 
+            Instant.now()
+        )) {
+            logger.debug("$payment bounced in '$bounceId'")
+        } else {
+            logger.debug("IN '${payment.bankTransferId}' already seen and bounced in '$bounceId'")
+        }
+    } else {
+        if (db.registerTalerableIncoming(payment, reservePub)) {
+            logger.debug("$payment")
+        } else {
+            logger.debug("IN '${payment.bankTransferId}' already seen")
+        }
     }
-    db.incomingTalerablePaymentCreate(incomingPayment, reservePub)
 }
 
 /**
@@ -388,11 +378,9 @@ private fun ingestNotification(
 
     runBlocking {
         incomingPayments.forEach {
-            logger.debug("$it")
             ingestIncomingPayment(db, it)
         }
         outgoingPayments.forEach {
-            logger.debug("$it")
             ingestOutgoingPayment(db, it)
         }
     }
diff --git a/nexus/src/test/kotlin/Common.kt b/nexus/src/test/kotlin/Common.kt
index 82d31feb..d35315d6 100644
--- a/nexus/src/test/kotlin/Common.kt
+++ b/nexus/src/test/kotlin/Common.kt
@@ -91,7 +91,7 @@ fun genInitPay(
     )
 
 // Generates an incoming payment, given its subject.
-fun genIncPay(subject: String = "test wire transfer") =
+fun genInPay(subject: String) =
     IncomingPayment(
         amount = TalerAmount(44, 0, "KUDOS"),
         debitPaytoUri = "payto://iban/not-used",
@@ -101,11 +101,11 @@ fun genIncPay(subject: String = "test wire transfer") =
     )
 
 // Generates an outgoing payment, given its subject.
-fun genOutPay(subject: String = "outgoing payment") =
+fun genOutPay(subject: String, bankTransferId: String) =
     OutgoingPayment(
         amount = TalerAmount(44, 0, "KUDOS"),
         creditPaytoUri = "payto://iban/TEST-IBAN?receiver-name=Test",
         wireTransferSubject = subject,
         executionTime = Instant.now(),
-        bankTransferId = "entropic"
+        bankTransferId = bankTransferId
     )
\ No newline at end of file
diff --git a/nexus/src/test/kotlin/DatabaseTest.kt b/nexus/src/test/kotlin/DatabaseTest.kt
index 03a709a7..ec9bfae3 100644
--- a/nexus/src/test/kotlin/DatabaseTest.kt
+++ b/nexus/src/test/kotlin/DatabaseTest.kt
@@ -8,145 +8,113 @@ import kotlin.test.assertEquals
 
 
 class OutgoingPaymentsTest {
-
-    /**
-     * Tests the insertion of outgoing payments, including
-     * the case where we reconcile with an initiated payment.
-     */
-    @Test
-    fun outgoingPaymentCreation() {
-        val db = prepDb(TalerConfig(NEXUS_CONFIG_SOURCE))
-        runBlocking {
-            // inserting without reconciling
-            assertFalse(db.isOutgoingPaymentSeen("entropic"))
-            assertEquals(
-                OutgoingPaymentOutcome.SUCCESS,
-                db.outgoingPaymentCreate(genOutPay("paid by nexus"))
-            )
-            assertTrue(db.isOutgoingPaymentSeen("entropic"))
-            // inserting trying to reconcile with a non-existing initiated payment.
-            assertEquals(
-                OutgoingPaymentOutcome.INITIATED_COUNTERPART_NOT_FOUND,
-                db.outgoingPaymentCreate(genOutPay(), 5)
-            )
-            // initiating a payment to reconcile later.  Takes row ID == 1
-            assertEquals(
-                PaymentInitiationOutcome.SUCCESS,
-                db.initiatedPaymentCreate(genInitPay("waiting for reconciliation"))
-            )
-            // Creating an outgoing payment, reconciling it with the one above.
-            assertEquals(
-                OutgoingPaymentOutcome.SUCCESS,
-                db.outgoingPaymentCreate(genOutPay(), 1)
-            )
-        }
-    }
-}
-
-// @Ignore // enable after having modified the bouncing logic in Kotlin
-class IncomingPaymentsTest {
     @Test
-    fun bounceWithCustomRefund() {
+    fun register() {
         val db = prepDb(TalerConfig(NEXUS_CONFIG_SOURCE))
         runBlocking {
-            // creating and bouncing one incoming transaction.
-            assertTrue(
-                db.incomingPaymentCreateBounced(
-                    genIncPay("incoming and bounced"),
-                    "UID",
-                    TalerAmount(2, 53000000, "KUDOS")
+            // With reconciling
+            genOutPay("paid by nexus", "first").run {
+                assertEquals(
+                    PaymentInitiationOutcome.SUCCESS,
+                    db.initiatedPaymentCreate(genInitPay("waiting for reconciliation", "first"))
                 )
-            )
-            db.runConn {
-                // check incoming shows up.
-                val checkIncoming = it.prepareStatement("""
-                    SELECT 
-                      (amount).val as amount_value
-                      ,(amount).frac as amount_frac
-                     FROM incoming_transactions
-                     WHERE incoming_transaction_id = 1;
-                """).executeQuery()
-                assertTrue(checkIncoming.next())
-                assertEquals(44, checkIncoming.getLong("amount_value"))
-                assertEquals(0, checkIncoming.getLong("amount_frac"))
-                // check bounced has the custom value
-                val findBounced = it.prepareStatement("""
-                    SELECT 
-                      initiated_outgoing_transaction_id
-                      FROM bounced_transactions
-                      WHERE incoming_transaction_id = 1;
-                """).executeQuery()
-                assertTrue(findBounced.next())
-                val initiatedId = findBounced.getLong("initiated_outgoing_transaction_id")
-                assertEquals(1, initiatedId)
-                val findInitiatedAmount = it.prepareStatement("""
-                    SELECT
-                      (amount).val as amount_value
-                      ,(amount).frac as amount_frac
-                    FROM initiated_outgoing_transactions
-                    WHERE initiated_outgoing_transaction_id = 1;
-                """).executeQuery()
-                assertTrue(findInitiatedAmount.next())
                 assertEquals(
-                    53000000,
-                    findInitiatedAmount.getInt("amount_frac")
+                    OutgoingRegistrationResult.New(true),
+                    db.registerOutgoing(this)
                 )
                 assertEquals(
-                    2,
-                    findInitiatedAmount.getInt("amount_value")
+                    OutgoingRegistrationResult.AlreadyRegistered,
+                    db.registerOutgoing(this)
+                )
+            }
+            // Without reconciling
+            genOutPay("not paid by nexus", "second").run {
+                assertEquals(
+                    OutgoingRegistrationResult.New(false),
+                    db.registerOutgoing(this)
+                )
+                assertEquals(
+                    OutgoingRegistrationResult.AlreadyRegistered,
+                    db.registerOutgoing(this)
                 )
             }
         }
     }
+}
+
+class IncomingPaymentsTest {
     // Tests creating and bouncing incoming payments in one DB transaction.
     @Test
-    fun incomingAndBounce() {
+    fun bounce() {
         val db = prepDb(TalerConfig(NEXUS_CONFIG_SOURCE))
         runBlocking {
             // creating and bouncing one incoming transaction.
-            assertTrue(db.incomingPaymentCreateBounced(
-                genIncPay("incoming and bounced"),
-                "UID"
+            val payment = genInPay("incoming and bounced")
+            assertTrue(db.registerMalformedIncoming(
+                payment,
+                "UID",
+                TalerAmount(2, 53000000, "KUDOS"),
+                "Bounce UID",
+                Instant.now()
+            ))
+            assertFalse(db.registerMalformedIncoming(
+                payment,
+                "UID",
+                TalerAmount(2, 53000000, "KUDOS"),
+                "Bounce UID",
+                Instant.now()
             ))
             db.runConn {
                 // Checking one incoming got created
                 val checkIncoming = it.prepareStatement("""
-                    SELECT 1 FROM incoming_transactions WHERE incoming_transaction_id = 1;
+                    SELECT (amount).val as amount_value, (amount).frac as amount_frac 
+                    FROM incoming_transactions WHERE incoming_transaction_id = 1
                 """).executeQuery()
                 assertTrue(checkIncoming.next())
+                assertEquals(payment.amount.value, checkIncoming.getLong("amount_value"))
+                assertEquals(payment.amount.fraction, checkIncoming.getInt("amount_frac"))
                 // Checking the bounced table got its row.
                 val checkBounced = it.prepareStatement("""
-                    SELECT 1 FROM bounced_transactions WHERE incoming_transaction_id = 1;
+                    SELECT 1 FROM bounced_transactions 
+                    WHERE incoming_transaction_id = 1 AND initiated_outgoing_transaction_id = 1
                 """).executeQuery()
                 assertTrue(checkBounced.next())
                 // check the related initiated payment exists.
                 val checkInitiated = it.prepareStatement("""
-                    SELECT 
-                      COUNT(initiated_outgoing_transaction_id) AS how_many
-                      FROM initiated_outgoing_transactions
+                    SELECT
+                        (amount).val as amount_value
+                        ,(amount).frac as amount_frac
+                    FROM initiated_outgoing_transactions
+                    WHERE initiated_outgoing_transaction_id = 1
                 """).executeQuery()
                 assertTrue(checkInitiated.next())
-                assertEquals(1, checkInitiated.getInt("how_many"))
+                assertEquals(
+                    53000000,
+                    checkInitiated.getInt("amount_frac")
+                )
+                assertEquals(
+                    2,
+                    checkInitiated.getInt("amount_value")
+                )
             }
         }
     }
 
     // Tests the creation of a talerable incoming payment.
     @Test
-    fun incomingTalerableCreation() {
+    fun talerable() {
         val db = prepDb(TalerConfig(NEXUS_CONFIG_SOURCE))
         val reservePub = ByteArray(32)
         Random.nextBytes(reservePub)
 
         runBlocking {
-            val inc = genIncPay("reserve-pub")
+            val inc = genInPay("reserve-pub")
             // Checking the reserve is not found.
             assertFalse(db.isReservePubFound(reservePub))
-            assertFalse(db.isIncomingPaymentSeen(inc.bankTransferId))
-            assertTrue(db.incomingTalerablePaymentCreate(inc, reservePub))
+            assertTrue(db.registerTalerableIncoming(inc, reservePub))
             // Checking the reserve is not found.
             assertTrue(db.isReservePubFound(reservePub))
-            assertTrue(db.isIncomingPaymentSeen(inc.bankTransferId))
+            assertFalse(db.registerTalerableIncoming(inc, reservePub))
         }
     }
 }