Fix drop and account soft delete

12 files changed, 100 insertions, 141 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
index f78947e7..49ca49c0 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
@@ -194,15 +194,17 @@ class AccountDAO(private val db: Database) {
         login: String, 
         is2fa: Boolean
     ): AccountDeletionResult = db.serializable { conn ->
+        val now = Instant.now().toDbMicros() ?: throw faultyTimestampByBank()
         val stmt = conn.prepareStatement("""
             SELECT
               out_not_found,
               out_balance_not_zero,
               out_tan_required
-              FROM account_delete(?,?);
+              FROM account_delete(?,?,?)
         """)
         stmt.setString(1, login)
-        stmt.setBoolean(2, is2fa)
+        stmt.setLong(2, now)
+        stmt.setBoolean(3, is2fa)
         stmt.executeQuery().use {
             when {
                 !it.next() -> throw internalServerError("Deletion returned nothing.")
@@ -265,7 +267,7 @@ class AccountDAO(private val db: Database) {
             FROM customers
                 JOIN bank_accounts 
                 ON customer_id=owning_customer_id
-            WHERE login=?
+            WHERE login=? AND deleted_at IS NULL
         """).run {
             setString(1, login)
             oneOrNull {
@@ -388,7 +390,8 @@ class AccountDAO(private val db: Database) {
     ): AccountPatchAuthResult = db.serializable {
         it.transaction { conn ->
             val (currentPwh, tanRequired) = conn.prepareStatement("""
-                SELECT password_hash, (NOT ? AND tan_channel IS NOT NULL) FROM customers WHERE login=?
+                SELECT password_hash, (NOT ? AND tan_channel IS NOT NULL) 
+                FROM customers WHERE login=? AND deleted_at IS NULL
             """).run {
                 setBoolean(1, is2fa)
                 setString(2, login)
@@ -415,7 +418,7 @@ class AccountDAO(private val db: Database) {
     /** Get password hash of account [login] */
     suspend fun passwordHash(login: String): String? = db.conn { conn ->
         val stmt = conn.prepareStatement("""
-            SELECT password_hash FROM customers WHERE login=?
+            SELECT password_hash FROM customers WHERE login=? AND deleted_at IS NULL
         """)
         stmt.setString(1, login)
         stmt.oneOrNull { 
@@ -432,9 +435,8 @@ class AccountDAO(private val db: Database) {
              ,name
              ,is_taler_exchange
             FROM bank_accounts
-                JOIN customers 
-                ON customer_id=owning_customer_id
-                WHERE login=?
+                JOIN customers ON customer_id=owning_customer_id
+            WHERE login=? AND deleted_at IS NULL
         """)
         stmt.setString(1, login)
         stmt.oneOrNull {
@@ -466,7 +468,7 @@ class AccountDAO(private val db: Database) {
             FROM customers 
                 JOIN bank_accounts
                     ON customer_id=owning_customer_id
-            WHERE login=?
+            WHERE login=? AND deleted_at IS NULL
         """)
         stmt.setString(1, login)
         stmt.oneOrNull {
@@ -512,7 +514,7 @@ class AccountDAO(private val db: Database) {
               bank_account_id
               FROM bank_accounts JOIN customers
                 ON owning_customer_id = customer_id 
-              WHERE is_public=true AND name LIKE ? AND
+              WHERE is_public=true AND name LIKE ? AND deleted_at IS NULL AND
             """,
             {
                 setString(1, params.loginFilter)
@@ -555,7 +557,7 @@ class AccountDAO(private val db: Database) {
             ,bank_account_id
             FROM bank_accounts JOIN customers
               ON owning_customer_id = customer_id 
-            WHERE name LIKE ? AND
+            WHERE name LIKE ? AND deleted_at IS NULL AND
             """,
             {
                 setString(1, params.loginFilter)
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/CashoutDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/CashoutDAO.kt
index 3f044ea1..0f9d8f86 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/CashoutDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/CashoutDAO.kt
@@ -104,7 +104,7 @@ class CashoutDAO(private val db: Database) {
                 JOIN bank_accounts ON bank_account=bank_account_id
                 JOIN customers ON owning_customer_id=customer_id
                 LEFT JOIN bank_account_transactions ON local_transaction=bank_transaction_id
-            WHERE cashout_id=? AND login=?
+            WHERE cashout_id=? AND login=? AND deleted_at IS NULL
         """)
         stmt.setLong(1, id)
         stmt.setString(2, login)
@@ -134,7 +134,7 @@ class CashoutDAO(private val db: Database) {
             FROM cashout_operations
                 JOIN bank_accounts ON bank_account=bank_account_id
                 JOIN customers ON owning_customer_id=customer_id
-            WHERE
+            WHERE deleted_at IS NULL AND
         """) {
             GlobalCashoutInfo(
                 cashout_id = it.getLong("cashout_id"),
@@ -150,7 +150,7 @@ class CashoutDAO(private val db: Database) {
             FROM cashout_operations
                 JOIN bank_accounts ON bank_account=bank_account_id
                 JOIN customers ON owning_customer_id=customer_id
-            WHERE login = ? AND
+            WHERE login = ? AND deleted_at IS NULL AND
         """, 
             bind = { 
                 setString(1, login)
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt
index 9017540d..66771c93 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/TanDAO.kt
@@ -163,7 +163,7 @@ class TanDAO(private val db: Database) {
             SELECT body, tan_challenges.tan_channel, tan_info
             FROM tan_challenges
                 JOIN customers ON customer=customer_id
-            WHERE challenge_id=? AND op=?::op_enum AND login=?
+            WHERE challenge_id=? AND op=?::op_enum AND login=? AND deleted_at IS NULL
         """)
         stmt.setLong(1, id)
         stmt.setString(2, op.name)
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/TokenDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/TokenDAO.kt
index 8a5c594c..0723ac88 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/TokenDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/TokenDAO.kt
@@ -40,7 +40,7 @@ class TokenDAO(private val db: Database) {
     ): Boolean = db.serializable { conn ->
         // TODO single query
         val bankCustomer = conn.prepareStatement("""
-            SELECT customer_id FROM customers WHERE login=?
+            SELECT customer_id FROM customers WHERE login=? AND deleted_at IS NULL
         """).run {
             setString(1, login)
             oneOrNull { it.getLong(1) }!!
@@ -75,7 +75,7 @@ class TokenDAO(private val db: Database) {
               is_refreshable
             FROM bearer_tokens
                 JOIN customers ON bank_customer=customer_id
-            WHERE content=?;            
+            WHERE content=? AND deleted_at IS NULL
         """)
         stmt.setBytes(1, token)
         stmt.oneOrNull { 
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/TransactionDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/TransactionDAO.kt
index 81fd558f..bbd70bcf 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/TransactionDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/TransactionDAO.kt
@@ -165,7 +165,7 @@ class TransactionDAO(private val db: Database) {
             FROM bank_account_transactions
                 JOIN bank_accounts ON bank_account_transactions.bank_account_id=bank_accounts.bank_account_id
                 JOIN customers ON customer_id=owning_customer_id 
-	        WHERE bank_transaction_id=? AND login=?
+	        WHERE bank_transaction_id=? AND login=? AND deleted_at IS NULL
         """)
         stmt.setLong(1, rowId)
         stmt.setString(2, login)
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt
index 330006a1..27b4002e 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt
@@ -189,7 +189,7 @@ class WithdrawalDAO(private val db: Database) {
             FROM taler_withdrawal_operations
                 JOIN bank_accounts ON wallet_bank_account=bank_account_id
                 JOIN customers ON customer_id=owning_customer_id
-            WHERE withdrawal_uuid=?
+            WHERE withdrawal_uuid=? AND deleted_at IS NULL
         """)
         stmt.setObject(1, uuid)
         stmt.oneOrNull { it.getString(1) }
@@ -250,7 +250,7 @@ class WithdrawalDAO(private val db: Database) {
                     FROM taler_withdrawal_operations
                         JOIN bank_accounts ON wallet_bank_account=bank_account_id
                         JOIN customers ON customer_id=owning_customer_id
-                    WHERE withdrawal_uuid=?
+                    WHERE withdrawal_uuid=? AND deleted_at IS NULL
                 """)
                 stmt.setObject(1, uuid)
                 stmt.oneOrNull {
diff --git a/bank/src/test/kotlin/CoreBankApiTest.kt b/bank/src/test/kotlin/CoreBankApiTest.kt
index b1b89cc9..0bd13207 100644
--- a/bank/src/test/kotlin/CoreBankApiTest.kt
+++ b/bank/src/test/kotlin/CoreBankApiTest.kt
@@ -137,8 +137,7 @@ class CoreBankTokenApiTest {
     @Test
     fun delete() = bankSetup { _ -> 
         // TODO test restricted
-        val token = client.post("/accounts/merchant/token") {
-            pwAuth("merchant")
+        val token = client.postA("/accounts/merchant/token") {
             json { "scope" to "readonly" }
         }.assertOkJson<TokenSuccessResponse>().access_token
         // Check OK
@@ -151,9 +150,7 @@ class CoreBankTokenApiTest {
         }.assertUnauthorized()
 
         // Checking merchant can still be served by basic auth, after token deletion.
-        client.get("/accounts/merchant") {
-            pwAuth("merchant")
-        }.assertOk()
+        client.getA("/accounts/merchant").assertOk()
     }
 }
 
@@ -424,19 +421,6 @@ class CoreBankAccountsApiTest {
             .assertConflict(TalerErrorCode.BANK_ACCOUNT_BALANCE_NOT_ZERO)
         // Successful deletion
         tx("john", "KUDOS:1", "customer")
-        // TODO remove with gc
-        db.conn { conn ->
-            val id = conn.prepareStatement("SELECT bank_account_id FROM bank_accounts JOIN customers ON customer_id=owning_customer_id WHERE login = ?").run {
-                setString(1, "john")
-                oneOrNull {
-                    it.getLong(1)
-                }!!
-            }
-            conn.prepareStatement("DELETE FROM bank_account_transactions WHERE bank_account_id=?").run {
-                setLong(1, id)
-                execute()
-            }
-        }
         client.deleteA("/accounts/john")
             .assertChallenge()
             .assertNoContent()
@@ -513,7 +497,6 @@ class CoreBankAccountsApiTest {
             json(req)
         }.assertNoContent()
 
-        
         checkAdminOnly(
             obj(req) { "debit_threshold" to "KUDOS:100" },
             TalerErrorCode.BANK_NON_ADMIN_PATCH_DEBT_LIMIT
diff --git a/common/src/main/kotlin/DB.kt b/common/src/main/kotlin/DB.kt
index b44e1fcd..27b8eabe 100644
--- a/common/src/main/kotlin/DB.kt
+++ b/common/src/main/kotlin/DB.kt
@@ -276,9 +276,7 @@ fun resetDatabaseTables(conn: PgConnection, cfg: DatabaseConfig, sqlFilePrefix:
         SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='_v') AND
             EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='${sqlFilePrefix.replace("-", "_")}')
         """
-    ).oneOrNull {
-        it.getBoolean(1)
-    }!!
+    ).one{ it.getBoolean(1) }
     if (!isInitialized) {
         logger.info("versioning schema not present, not running drop sql")
         return
diff --git a/database-versioning/libeufin-bank-0003.sql b/database-versioning/libeufin-bank-0003.sql
index 14f1075f..6956b3f8 100644
--- a/database-versioning/libeufin-bank-0003.sql
+++ b/database-versioning/libeufin-bank-0003.sql
@@ -24,5 +24,11 @@ CREATE TABLE bank_transaction_operations
     REFERENCES bank_account_transactions(bank_transaction_id)
       ON DELETE CASCADE
   );
+COMMENT ON TABLE bank_transaction_operations
+  IS 'Operation table for idempotent bank transactions.';
+
+ALTER TABLE customers ADD deleted_at INT8;
+COMMENT ON COLUMN customers.deleted_at
+  IS 'Indicates a deletion request, we keep the account in the database until all its transactions have been deleted for compliance.';
 
 COMMIT;
diff --git a/database-versioning/libeufin-bank-drop.sql b/database-versioning/libeufin-bank-drop.sql
index 7fbcc342..52ef772b 100644
--- a/database-versioning/libeufin-bank-drop.sql
+++ b/database-versioning/libeufin-bank-drop.sql
@@ -1,11 +1,16 @@
 BEGIN;
 
--- NOTE: The following unregistration would affect the
--- legacy database schema too.  That's acceptable as the
--- legacy schema is being removed.
-SELECT _v.unregister_patch('libeufin-bank-0001');
-SELECT _v.unregister_patch('libeufin-bank-0002');
-SELECT _v.unregister_patch('libeufin-bank-0003');
+DO
+$do$
+DECLARE
+    patch text;
+BEGIN
+    for patch in SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_bank_%' loop 
+        PERFORM _v.unregister_patch(patch);
+    end loop;
+END
+$do$;
+
 DROP SCHEMA libeufin_bank CASCADE;
 
 COMMIT;
diff --git a/database-versioning/libeufin-bank-procedures.sql b/database-versioning/libeufin-bank-procedures.sql
index 8f82fc65..7ef7c3f1 100644
--- a/database-versioning/libeufin-bank-procedures.sql
+++ b/database-versioning/libeufin-bank-procedures.sql
@@ -142,6 +142,7 @@ COMMENT ON FUNCTION account_balance_is_sufficient IS 'Check if an account have e
 
 CREATE FUNCTION account_delete(
   IN in_login TEXT,
+  IN in_now INT8,
   IN in_is_tan BOOLEAN,
   OUT out_not_found BOOLEAN,
   OUT out_balance_not_zero BOOLEAN,
@@ -150,45 +151,26 @@ CREATE FUNCTION account_delete(
 LANGUAGE plpgsql AS $$
 DECLARE
 my_customer_id INT8;
-my_balance_val INT8;
-my_balance_frac INT4;
 BEGIN
--- check if login exists and if 2FA is required
-SELECT customer_id, (NOT in_is_tan AND tan_channel IS NOT NULL) 
-  INTO my_customer_id, out_tan_required
-  FROM customers
-  WHERE login = in_login;
-IF NOT FOUND THEN
-  out_not_found=TRUE;
-  RETURN;
-END IF;
-
--- get the balance
-SELECT
-  (balance).val as balance_val,
-  (balance).frac as balance_frac
-  INTO
-    my_balance_val,
-    my_balance_frac
-  FROM bank_accounts
-  WHERE owning_customer_id = my_customer_id;
-IF NOT FOUND THEN
-  RAISE EXCEPTION 'Invariant failed: customer lacks bank account';
-END IF;
-
--- check that balance is zero.
-IF my_balance_val != 0 OR my_balance_frac != 0 THEN
-  out_balance_not_zero=TRUE;
-  RETURN;
-END IF;
-
--- check tan required
-IF out_tan_required THEN
+-- check if account exists, has zero balance and if 2FA is required
+SELECT 
+   customer_id
+  ,(NOT in_is_tan AND tan_channel IS NOT NULL)
+  ,((balance).val != 0 OR (balance).frac != 0)
+  INTO 
+     my_customer_id
+    ,out_tan_required
+    ,out_balance_not_zero
+  FROM customers 
+    JOIN bank_accounts ON owning_customer_id = customer_id
+  WHERE login = in_login AND deleted_at IS NULL;
+IF NOT FOUND OR out_balance_not_zero OR out_tan_required THEN
+  out_not_found=NOT FOUND;
   RETURN;
 END IF;
 
 -- actual deletion
-DELETE FROM customers WHERE login = in_login;
+UPDATE customers SET deleted_at = in_now WHERE customer_id = my_customer_id;
 END $$;
 COMMENT ON FUNCTION account_delete IS 'Deletes an account if the balance is zero';
 
@@ -310,11 +292,9 @@ SELECT
   FROM bank_accounts 
       JOIN customers 
         ON customer_id=owning_customer_id
-  WHERE login = in_username;
-IF NOT FOUND THEN
-  out_debtor_not_found=TRUE;
-  RETURN;
-ELSIF out_debtor_not_exchange THEN
+  WHERE login = in_username AND deleted_at IS NULL;
+IF NOT FOUND OR out_debtor_not_exchange THEN
+  out_debtor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Find receiver bank account id
@@ -323,10 +303,8 @@ SELECT
   INTO receiver_bank_account_id, out_both_exchanges
   FROM bank_accounts
   WHERE internal_payto_uri = in_credit_account_payto;
-IF NOT FOUND THEN
-  out_creditor_not_found=TRUE;
-  RETURN;
-ELSIF out_both_exchanges THEN
+IF NOT FOUND OR out_both_exchanges THEN
+  out_creditor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Perform bank transfer
@@ -392,11 +370,9 @@ SELECT
   FROM bank_accounts 
       JOIN customers 
         ON customer_id=owning_customer_id
-  WHERE login = in_username;
-IF NOT FOUND THEN
-  out_creditor_not_found=TRUE;
-  RETURN;
-ELSIF out_creditor_not_exchange THEN
+  WHERE login = in_username AND deleted_at IS NULL;
+IF NOT FOUND OR out_creditor_not_exchange THEN
+  out_creditor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Find sender bank account id
@@ -405,10 +381,8 @@ SELECT
   INTO sender_bank_account_id, out_both_exchanges
   FROM bank_accounts
   WHERE internal_payto_uri = in_debit_account_payto;
-IF NOT FOUND THEN
-  out_debtor_not_found=TRUE;
-  RETURN;
-ELSIF out_both_exchanges THEN
+IF NOT FOUND OR out_both_exchanges THEN
+  out_debtor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Perform bank transfer
@@ -468,7 +442,7 @@ SELECT bank_account_id, is_taler_exchange, login='admin'
   INTO out_credit_bank_account_id, out_creditor_is_exchange, out_creditor_admin
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
-  WHERE internal_payto_uri = in_credit_account_payto;
+  WHERE internal_payto_uri = in_credit_account_payto AND deleted_at IS NULL;
 IF NOT FOUND OR out_creditor_admin THEN
   out_creditor_not_found=NOT FOUND;
   RETURN;
@@ -478,7 +452,7 @@ SELECT bank_account_id, is_taler_exchange, out_credit_bank_account_id=bank_accou
   INTO out_debit_bank_account_id, out_debtor_is_exchange, out_same_account, out_tan_required
   FROM bank_accounts 
     JOIN customers ON customer_id=owning_customer_id
-  WHERE login = in_debit_account_username;
+  WHERE login = in_debit_account_username AND deleted_at IS NULL;
 IF NOT FOUND OR out_same_account THEN
   out_debtor_not_found=NOT FOUND;
   RETURN;
@@ -546,11 +520,9 @@ SELECT bank_account_id, is_taler_exchange
   INTO account_id, out_account_is_exchange
   FROM bank_accounts
   JOIN customers ON bank_accounts.owning_customer_id = customers.customer_id
-  WHERE login=in_account_username;
-IF NOT FOUND THEN
-  out_account_not_found=TRUE;
-  RETURN;
-ELSIF out_account_is_exchange THEN
+  WHERE login=in_account_username AND deleted_at IS NULL;
+IF NOT FOUND OR out_account_is_exchange THEN
+  out_account_not_found=NOT FOUND;
   RETURN;
 END IF;
 
@@ -598,10 +570,8 @@ SELECT
   INTO not_selected, out_status, out_already_selected
   FROM taler_withdrawal_operations
   WHERE withdrawal_uuid=in_withdrawal_uuid;
-IF NOT FOUND THEN
-  out_no_op=TRUE;
-  RETURN;
-ELSIF out_already_selected THEN
+IF NOT FOUND OR out_already_selected THEN
+  out_no_op=NOT FOUND;
   RETURN;
 END IF;
 
@@ -619,10 +589,8 @@ IF not_selected THEN
     INTO out_account_is_not_exchange
     FROM bank_accounts
     WHERE internal_payto_uri=in_selected_exchange_payto;
-  IF NOT FOUND THEN
-    out_account_not_found=TRUE;
-    RETURN;
-  ELSIF out_account_is_not_exchange THEN
+  IF NOT FOUND OR out_account_is_not_exchange THEN
+    out_account_not_found=NOT FOUND;
     RETURN;
   END IF;
 
@@ -649,10 +617,8 @@ UPDATE taler_withdrawal_operations
   WHERE withdrawal_uuid=in_withdrawal_uuid
   RETURNING confirmation_done
   INTO out_already_confirmed;
-IF NOT FOUND THEN
-  out_no_op=TRUE;
-  RETURN;
-ELSIF out_already_confirmed THEN
+IF NOT FOUND OR out_already_confirmed THEN
+  out_no_op=NOT FOUND;
   RETURN;
 END IF;
 
@@ -705,27 +671,20 @@ SELECT
   FROM taler_withdrawal_operations
     JOIN bank_accounts ON wallet_bank_account=bank_account_id
     JOIN customers ON owning_customer_id=customer_id
-  WHERE withdrawal_uuid=in_withdrawal_uuid AND login=in_login;
-IF NOT FOUND THEN
-  out_no_op=TRUE;
-  RETURN;
-ELSIF already_confirmed OR out_aborted OR out_not_selected THEN
+  WHERE withdrawal_uuid=in_withdrawal_uuid AND login=in_login AND deleted_at IS NULL;
+IF NOT FOUND OR already_confirmed OR out_aborted OR out_not_selected THEN
+  out_no_op=NOT FOUND;
   RETURN;
 END IF;
 
--- sending the funds to the exchange, but needs first its bank account row ID
+-- Check exchange account then 2faa
 SELECT
   bank_account_id
   INTO exchange_bank_account_id
   FROM bank_accounts
   WHERE internal_payto_uri = selected_exchange_payto_local;
-IF NOT FOUND THEN
-  out_exchange_not_found=TRUE;
-  RETURN;
-END IF;
-
--- Check 2FA
-IF out_tan_required THEN
+IF NOT FOUND OR out_tan_required THEN
+  out_exchange_not_found=NOT FOUND;
   RETURN;
 END IF;
 
@@ -1183,7 +1142,7 @@ DECLARE
 account_id INT8;
 BEGIN
 -- Retrieve account id
-SELECT customer_id INTO account_id FROM customers WHERE login = in_login;
+SELECT customer_id INTO account_id FROM customers WHERE login = in_login AND deleted_at IS NULL;
 -- Create challenge
 INSERT INTO tan_challenges (
   body,
@@ -1235,7 +1194,7 @@ SELECT customer_id, tan_channel, CASE tan_channel
     WHEN 'email' THEN email
   END
 INTO account_id, out_tan_channel, out_tan_info
-FROM customers WHERE login = in_login;
+FROM customers WHERE login = in_login AND deleted_at IS NULL;
 
 -- Recover expiration date
 SELECT 
@@ -1295,7 +1254,7 @@ DECLARE
 account_id INT8;
 BEGIN
 -- Retrieve account id
-SELECT customer_id INTO account_id FROM customers WHERE login = in_login;
+SELECT customer_id INTO account_id FROM customers WHERE login = in_login AND deleted_at IS NULL;
 -- Check challenge
 UPDATE tan_challenges SET 
   confirmation_date = CASE 
@@ -1309,10 +1268,8 @@ RETURNING
   retry_counter <= 0 AND confirmation_date IS NULL,
   in_now_date >= expiration_date AND confirmation_date IS NULL
 INTO out_ok, out_no_retry, out_expired;
-IF NOT FOUND THEN
-  out_no_op = true;
-  RETURN;
-ELSIF NOT out_ok OR out_no_retry OR out_expired THEN
+IF NOT FOUND OR NOT out_ok OR out_no_retry OR out_expired THEN
+  out_no_op = NOT FOUND;
   RETURN;
 END IF;
 
diff --git a/database-versioning/libeufin-nexus-drop.sql b/database-versioning/libeufin-nexus-drop.sql
index 77ac722a..199f1cb9 100644
--- a/database-versioning/libeufin-nexus-drop.sql
+++ b/database-versioning/libeufin-nexus-drop.sql
@@ -1,7 +1,15 @@
 BEGIN;
 
-SELECT _v.unregister_patch('libeufin-nexus-0001');
-SELECT _v.unregister_patch('libeufin-nexus-0002');
+DO
+$do$
+DECLARE
+    patch text;
+BEGIN
+    for patch in SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_nexus_%' loop 
+        PERFORM _v.unregister_patch(patch);
+    end loop;
+END
+$do$;
 DROP SCHEMA libeufin_nexus CASCADE;
 
 COMMIT;