Clean SQL schema and add indexes for future gc feature

9 files changed, 178 insertions, 139 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt b/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
index 5e48a1cc..69df2ab3 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
@@ -456,7 +456,7 @@ private fun Routing.coreBankWithdrawalApi(db: Database, ctx: BankConfig) {
             val req = call.receive<BankAccountCreateWithdrawalRequest>()
             ctx.checkRegionalCurrency(req.amount)
             val opId = UUID.randomUUID()
-            when (db.withdrawal.create(username, opId, req.amount)) {
+            when (db.withdrawal.create(username, opId, req.amount, Instant.now())) {
                 WithdrawalCreationResult.UnknownAccount -> throw unknownAccount(username)
                 WithdrawalCreationResult.AccountIsExchange -> throw conflict(
                     "Exchange account cannot perform withdrawal operation",
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 9fd258b1..9694d357 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/AccountDAO.kt
@@ -81,7 +81,7 @@ class AccountDAO(private val db: Database) {
                     CryptoUtil.checkpw(password, it.getString(1)) && it.getBoolean(2)
                 } 
             }
-            println(idempotent)
+            
             if (idempotent != null) {
                 if (idempotent) {
                     AccountCreationResult.Success
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 dc4522fa..f6b912cc 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/WithdrawalDAO.kt
@@ -41,19 +41,21 @@ class WithdrawalDAO(private val db: Database) {
     suspend fun create(
         login: String,
         uuid: UUID,
-        amount: TalerAmount
+        amount: TalerAmount,
+        now: Instant
     ): WithdrawalCreationResult = db.serializable { conn ->
         val stmt = conn.prepareStatement("""
             SELECT
                 out_account_not_found,
                 out_account_is_exchange,
                 out_balance_insufficient
-            FROM create_taler_withdrawal(?, ?, (?,?)::taler_amount);
+            FROM create_taler_withdrawal(?, ?, (?,?)::taler_amount, ?);
         """)
         stmt.setString(1, login)
         stmt.setObject(2, uuid)
         stmt.setLong(3, amount.value)
         stmt.setInt(4, amount.frac)
+        stmt.setLong(5, now.toDbMicros() ?: throw faultyTimestampByBank())
         stmt.executeQuery().use {
             when {
                 !it.next() ->
diff --git a/bank/src/test/kotlin/AmountTest.kt b/bank/src/test/kotlin/AmountTest.kt
index afa427ad..32e51197 100644
--- a/bank/src/test/kotlin/AmountTest.kt
+++ b/bank/src/test/kotlin/AmountTest.kt
@@ -69,6 +69,7 @@ class AmountTest {
                 login = "merchant",
                 uuid = UUID.randomUUID(),
                 amount = due,
+                now = Instant.now()
             )
             val wBool = when (wRes) {
                 WithdrawalCreationResult.BalanceInsufficient -> false
diff --git a/bank/src/test/kotlin/CoreBankApiTest.kt b/bank/src/test/kotlin/CoreBankApiTest.kt
index 0507b2a6..7e56f0b8 100644
--- a/bank/src/test/kotlin/CoreBankApiTest.kt
+++ b/bank/src/test/kotlin/CoreBankApiTest.kt
@@ -364,7 +364,7 @@ class CoreBankAccountsApiTest {
 
     // DELETE /accounts/USERNAME
     @Test
-    fun delete() = bankSetup { _ -> 
+    fun delete() = bankSetup { db -> 
         authRoutine(HttpMethod.Delete, "/accounts/merchant", allowAdmin = true)
 
         // Reserved account
@@ -391,6 +391,19 @@ class CoreBankAccountsApiTest {
             .assertConflict(TalerErrorCode.BANK_ACCOUNT_BALANCE_NOT_ZERO)
         // Sucessful 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()
diff --git a/database-versioning/libeufin-bank-0001.sql b/database-versioning/libeufin-bank-0001.sql
index 63b39df2..5272eeae 100644
--- a/database-versioning/libeufin-bank-0001.sql
+++ b/database-versioning/libeufin-bank-0001.sql
@@ -21,10 +21,7 @@ CREATE SCHEMA libeufin_bank;
 SET search_path TO libeufin_bank;
 
 CREATE TYPE taler_amount
-  AS
-  (val INT8
-  ,frac INT4
-  );
+  AS (val INT8 ,frac INT4);
 COMMENT ON TYPE taler_amount
   IS 'Stores an amount, fraction is in units of 1/100000000 of the base value';
 
@@ -59,7 +56,7 @@ CREATE TYPE rounding_mode
 -- start of: bank accounts
 
 CREATE TABLE IF NOT EXISTS customers
-  (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  (customer_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,login TEXT NOT NULL UNIQUE
   ,password_hash TEXT NOT NULL
   ,name TEXT
@@ -72,27 +69,10 @@ COMMENT ON COLUMN customers.cashout_payto
 COMMENT ON COLUMN customers.name
   IS 'Full name of the customer.';
 
-CREATE TABLE IF NOT EXISTS bearer_tokens
-  (bearer_token_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
-  ,content BYTEA NOT NULL UNIQUE CHECK (LENGTH(content)=32)
-  ,creation_time INT8
-  ,expiration_time INT8
-  ,scope token_scope_enum
-  ,is_refreshable BOOLEAN
-  ,bank_customer BIGINT NOT NULL REFERENCES customers(customer_id) ON DELETE CASCADE
-);
-COMMENT ON TABLE bearer_tokens
-  IS 'Login tokens associated with one bank customer.  There is currently'
-     ' no garbage collector that deletes the expired tokens from the table';
-COMMENT ON COLUMN bearer_tokens.bank_customer
-  IS 'The customer that directly created this token, or the customer that'
-     ' created the very first token that originated all the refreshes until'
-     ' this token was created.';
-
 CREATE TABLE IF NOT EXISTS bank_accounts 
-  (bank_account_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  (bank_account_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,internal_payto_uri TEXT NOT NULL UNIQUE
-  ,owning_customer_id BIGINT NOT NULL UNIQUE -- UNIQUE enforces 1-1 map with customers
+  ,owning_customer_id INT8 NOT NULL UNIQUE -- UNIQUE enforces 1-1 map with customers
     REFERENCES customers(customer_id)
     ON DELETE CASCADE
   ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default
@@ -116,8 +96,26 @@ can be publicly shared';
 COMMENT ON COLUMN bank_accounts.owning_customer_id
   IS 'Login that owns the bank account';
 
+CREATE TABLE IF NOT EXISTS bearer_tokens
+  (bearer_token_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  ,content BYTEA NOT NULL UNIQUE CHECK (LENGTH(content)=32)
+  ,creation_time INT8
+  ,expiration_time INT8
+  ,scope token_scope_enum
+  ,is_refreshable BOOLEAN
+  ,bank_customer INT8 NOT NULL 
+    REFERENCES customers(customer_id)
+    ON DELETE CASCADE
+);
+COMMENT ON TABLE bearer_tokens
+  IS 'Login tokens associated with one bank customer.';
+COMMENT ON COLUMN bearer_tokens.bank_customer
+  IS 'The customer that directly created this token, or the customer that'
+     ' created the very first token that originated all the refreshes until'
+     ' this token was created.';
+
 CREATE TABLE IF NOT EXISTS iban_history 
-  (iban TEXT PRIMARY key
+  (iban TEXT PRIMARY KEY
   ,creation_time INT8 NOT NULL
   );
 COMMENT ON TABLE iban_history IS 'Track all generated iban, some might be unused.';
@@ -127,21 +125,19 @@ COMMENT ON TABLE iban_history IS 'Track all generated iban, some might be unused
 -- start of: money transactions
 
 CREATE TABLE IF NOT EXISTS bank_account_transactions 
-  (bank_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  (bank_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,creditor_payto_uri TEXT NOT NULL
   ,creditor_name TEXT NOT NULL
   ,debtor_payto_uri TEXT NOT NULL
   ,debtor_name TEXT NOT NULL
   ,subject TEXT NOT NULL
   ,amount taler_amount NOT NULL
-  ,transaction_date BIGINT NOT NULL -- is this ISO20022 terminology? document format (microseconds since epoch)
+  ,transaction_date INT8 NOT NULL
   ,account_servicer_reference TEXT
   ,payment_information_id TEXT
   ,end_to_end_id TEXT
   ,direction direction_enum NOT NULL
-  ,bank_account_id BIGINT NOT NULL
-    REFERENCES bank_accounts(bank_account_id)
-    ON DELETE CASCADE ON UPDATE RESTRICT
+  ,bank_account_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id)
   );
 
 COMMENT ON COLUMN bank_account_transactions.direction
@@ -157,7 +153,7 @@ COMMENT ON COLUMN bank_account_transactions.bank_account_id
 
 -- start of: TAN challenge
 CREATE TABLE IF NOT EXISTS challenges
-  (challenge_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE,
+  (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE,
    code TEXT NOT NULL,
    creation_date INT8 NOT NULL,
    expiration_date INT8 NOT NULL,
@@ -184,27 +180,23 @@ COMMENT ON COLUMN challenges.confirmation_date
 -- start of: cashout management
 
 CREATE TABLE IF NOT EXISTS cashout_operations 
-  (cashout_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  (cashout_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,request_uid BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(request_uid)=32)
   ,amount_debit taler_amount NOT NULL
   ,amount_credit taler_amount NOT NULL
   ,subject TEXT NOT NULL
-  ,creation_time BIGINT NOT NULL
-  ,bank_account BIGINT NOT NULL
+  ,creation_time INT8 NOT NULL
+  ,bank_account INT8 NOT NULL
     REFERENCES bank_accounts(bank_account_id)
-    ON DELETE CASCADE
-    ON UPDATE RESTRICT
-  ,challenge BIGINT NOT NULL UNIQUE
+  ,challenge INT8 NOT NULL UNIQUE
     REFERENCES challenges(challenge_id)
-    ON DELETE CASCADE
-    ON UPDATE RESTRICT
+      ON DELETE SET NULL
   ,tan_channel TEXT NULL DEFAULT NULL
   ,tan_info TEXT NULL DEFAULT NULL
   ,aborted BOOLEAN NOT NULL DEFAULT FALSE
-  ,local_transaction BIGINT UNIQUE DEFAULT NULL-- FIXME: Comment that the transaction only gets created after the TAN confirmation
+  ,local_transaction INT8 UNIQUE DEFAULT NULL
     REFERENCES bank_account_transactions(bank_transaction_id)
-    ON DELETE RESTRICT
-    ON UPDATE RESTRICT
+      ON DELETE CASCADE
   );
 COMMENT ON COLUMN cashout_operations.bank_account IS 'Bank amount to debit during confirmation';
 COMMENT ON COLUMN cashout_operations.challenge IS 'TAN challenge used to confirm the operation';
@@ -216,31 +208,28 @@ COMMENT ON COLUMN cashout_operations.tan_info IS 'Info of the last successful tr
 
 -- start of: Taler integration
 CREATE TABLE IF NOT EXISTS taler_exchange_outgoing
-  (exchange_outgoing_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (exchange_outgoing_id INT8 GENERATED BY DEFAULT AS IDENTITY
   ,request_uid BYTEA UNIQUE CHECK (LENGTH(request_uid)=64)
   ,wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32)
   ,exchange_base_url TEXT NOT NULL
-  ,bank_transaction BIGINT UNIQUE NOT NULL
+  ,bank_transaction INT8 UNIQUE NOT NULL
     REFERENCES bank_account_transactions(bank_transaction_id)
-      ON DELETE RESTRICT
-      ON UPDATE RESTRICT
-  ,creditor_account_id BIGINT NOT NULL
+      ON DELETE CASCADE
+  ,creditor_account_id INT8 NOT NULL
     REFERENCES bank_accounts(bank_account_id)
-    ON DELETE CASCADE ON UPDATE RESTRICT
   );
 
 CREATE TABLE IF NOT EXISTS taler_exchange_incoming
-  (exchange_incoming_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (exchange_incoming_id INT8 GENERATED BY DEFAULT AS IDENTITY
   ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32)
-  ,bank_transaction BIGINT UNIQUE NOT NULL
+  ,bank_transaction INT8 UNIQUE NOT NULL
     REFERENCES bank_account_transactions(bank_transaction_id)
-      ON DELETE RESTRICT
-      ON UPDATE RESTRICT
+      ON DELETE CASCADE
   );
 
 CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
-  (withdrawal_id BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,withdrawal_uuid uuid NOT NULL PRIMARY KEY
+  (withdrawal_id INT8 GENERATED BY DEFAULT AS IDENTITY
+  ,withdrawal_uuid uuid NOT NULL UNIQUE
   ,amount taler_amount NOT NULL
   ,selection_done BOOLEAN DEFAULT FALSE NOT NULL
   ,aborted BOOLEAN DEFAULT FALSE NOT NULL
@@ -248,10 +237,9 @@ CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
   ,reserve_pub BYTEA UNIQUE CHECK (LENGTH(reserve_pub)=32)
   ,subject TEXT
   ,selected_exchange_payto TEXT
-  ,wallet_bank_account BIGINT NOT NULL
+  ,wallet_bank_account INT8 NOT NULL
     REFERENCES bank_accounts(bank_account_id)
-      ON DELETE RESTRICT
-      ON UPDATE RESTRICT
+      ON DELETE CASCADE
   );
 COMMENT ON COLUMN taler_withdrawal_operations.selection_done
   IS 'Signals whether the wallet specified the exchange and gave the reserve public key';
@@ -264,32 +252,44 @@ COMMENT ON COLUMN taler_withdrawal_operations.confirmation_done
 CREATE TABLE IF NOT EXISTS bank_stats (
   timeframe stat_timeframe_enum NOT NULL
   ,start_time timestamp NOT NULL
-  ,taler_in_count BIGINT NOT NULL DEFAULT 0
+  ,taler_in_count INT8 NOT NULL DEFAULT 0
   ,taler_in_volume taler_amount NOT NULL DEFAULT (0, 0)
-  ,taler_out_count BIGINT NOT NULL DEFAULT 0
+  ,taler_out_count INT8 NOT NULL DEFAULT 0
   ,taler_out_volume taler_amount NOT NULL DEFAULT (0, 0)
-  ,cashin_count BIGINT NOT NULL DEFAULT 0
+  ,cashin_count INT8 NOT NULL DEFAULT 0
   ,cashin_regional_volume taler_amount NOT NULL DEFAULT (0, 0)
   ,cashin_fiat_volume taler_amount NOT NULL DEFAULT (0, 0)
-  ,cashout_count BIGINT NOT NULL DEFAULT 0
+  ,cashout_count INT8 NOT NULL DEFAULT 0
   ,cashout_regional_volume taler_amount NOT NULL DEFAULT (0, 0)
   ,cashout_fiat_volume taler_amount NOT NULL DEFAULT (0, 0)
   ,PRIMARY KEY (start_time, timeframe) 
 );
--- TODO garbage collection
-COMMENT ON TABLE bank_stats IS 'Stores statistics about the bank usage.';
-COMMENT ON COLUMN bank_stats.timeframe IS 'particular timeframe that this row accounts for';
-COMMENT ON COLUMN bank_stats.start_time IS 'timestamp of the start of the timeframe that this row accounts for, truncated according to the precision of the timeframe';
-COMMENT ON COLUMN bank_stats.taler_out_count IS 'how many internal payments were made by a Taler exchange';
-COMMENT ON COLUMN bank_stats.taler_out_volume IS 'how much internal currency was paid by a Taler exchange';
-COMMENT ON COLUMN bank_stats.taler_in_count IS 'how many internal payments were made to a Taler exchange';
-COMMENT ON COLUMN bank_stats.taler_in_volume IS 'how much internal currency was paid to a Taler exchange';
-COMMENT ON COLUMN bank_stats.cashin_count IS 'how many cashin operations took place in the timeframe';
-COMMENT ON COLUMN bank_stats.cashin_regional_volume IS 'how much regional currency was cashed in in the timeframe';
-COMMENT ON COLUMN bank_stats.cashin_fiat_volume IS 'how much fiat currency was cashed in in the timeframe';
-COMMENT ON COLUMN bank_stats.cashout_count IS 'how many cashout operations took place in the timeframe';
-COMMENT ON COLUMN bank_stats.cashout_regional_volume IS 'how much regional currency was payed by the bank to customers in the timeframe';
-COMMENT ON COLUMN bank_stats.cashout_fiat_volume IS 'how much fiat currency was payed by the bank to customers in the timeframe';
+COMMENT ON TABLE bank_stats 
+  IS 'Stores statistics about the bank usage.';
+COMMENT ON COLUMN bank_stats.timeframe 
+  IS 'particular timeframe that this row accounts for';
+COMMENT ON COLUMN bank_stats.start_time 
+  IS 'timestamp of the start of the timeframe that this row accounts for, truncated according to the precision of the timeframe';
+COMMENT ON COLUMN bank_stats.taler_out_count
+  IS 'how many internal payments were made by a Taler exchange';
+COMMENT ON COLUMN bank_stats.taler_out_volume
+  IS 'how much internal currency was paid by a Taler exchange';
+COMMENT ON COLUMN bank_stats.taler_in_count
+  IS 'how many internal payments were made to a Taler exchange';
+COMMENT ON COLUMN bank_stats.taler_in_volume
+  IS 'how much internal currency was paid to a Taler exchange';
+COMMENT ON COLUMN bank_stats.cashin_count
+  IS 'how many cashin operations took place in the timeframe';
+COMMENT ON COLUMN bank_stats.cashin_regional_volume
+  IS 'how much regional currency was cashed in in the timeframe';
+COMMENT ON COLUMN bank_stats.cashin_fiat_volume
+  IS 'how much fiat currency was cashed in in the timeframe';
+COMMENT ON COLUMN bank_stats.cashout_count
+  IS 'how many cashout operations took place in the timeframe';
+COMMENT ON COLUMN bank_stats.cashout_regional_volume
+  IS 'how much regional currency was payed by the bank to customers in the timeframe';
+COMMENT ON COLUMN bank_stats.cashout_fiat_volume 
+  IS 'how much fiat currency was payed by the bank to customers in the timeframe';
 
 -- end of: Statistics
 
diff --git a/database-versioning/libeufin-bank-0002.sql b/database-versioning/libeufin-bank-0002.sql
index 8092f1e1..0859d80f 100644
--- a/database-versioning/libeufin-bank-0002.sql
+++ b/database-versioning/libeufin-bank-0002.sql
@@ -18,10 +18,10 @@ BEGIN;
 SELECT _v.register_patch('libeufin-bank-0002', NULL, NULL);
 SET search_path TO libeufin_bank;
 
--- forget about all pending operations
+-- Forget about all pending operations
 DELETE FROM cashout_operations WHERE local_transaction IS NULL;
 
--- TODO drop pending cashout operations
+-- Remove challenge logic from cashout tables
 ALTER TABLE cashout_operations 
   DROP COLUMN challenge,
   DROP COLUMN tan_channel,
@@ -50,7 +50,6 @@ CREATE TABLE tan_challenges
   ,customer INT8 NOT NULL
     REFERENCES customers(customer_id)
     ON DELETE CASCADE
-    ON UPDATE RESTRICT
   ,tan_channel tan_enum NULL DEFAULT NULL
   ,tan_info TEXT NULL DEFAULT NULL
 );
@@ -65,4 +64,27 @@ COMMENT ON COLUMN tan_challenges.retry_counter IS 'How many tries are left for t
 COMMENT ON COLUMN tan_challenges.tan_channel IS 'TAN channel to use, if null use customer configured one';
 COMMENT ON COLUMN tan_challenges.tan_info IS 'TAN info to use, if null use customer configured one';
 
+CREATE INDEX tan_challenges_expiration_index
+  ON tan_challenges (expiration_date);
+COMMENT ON INDEX tan_challenges_expiration_index
+  IS 'for garbage collection';
+
+CREATE INDEX bearer_tokens_expiration_index
+  ON bearer_tokens (expiration_time);
+COMMENT ON INDEX bearer_tokens_expiration_index
+  IS 'for garbage collection';
+
+CREATE INDEX bank_account_transactions_expiration_index
+  ON bank_account_transactions (transaction_date);
+COMMENT ON INDEX bank_account_transactions_expiration_index
+  IS 'for garbage collection';
+
+ALTER TABLE taler_withdrawal_operations
+  ADD creation_date INT8 NOT NULL;
+CREATE INDEX taler_withdrawal_operations_expiration_index
+  ON taler_withdrawal_operations (creation_date);
+COMMENT ON INDEX taler_withdrawal_operations_expiration_index
+  IS 'for garbage collection';
+
+
 COMMIT;
diff --git a/database-versioning/libeufin-bank-procedures.sql b/database-versioning/libeufin-bank-procedures.sql
index 0daf9b1d..99b0bee3 100644
--- a/database-versioning/libeufin-bank-procedures.sql
+++ b/database-versioning/libeufin-bank-procedures.sql
@@ -31,7 +31,7 @@ CREATE FUNCTION amount_normalize(
 LANGUAGE plpgsql AS $$
 BEGIN
   normalized.val = amount.val + amount.frac / 100000000;
-  IF (normalized.val > 1::bigint<<52) THEN
+  IF (normalized.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
   normalized.frac = amount.frac % 100000000;
@@ -85,7 +85,7 @@ COMMENT ON FUNCTION amount_left_minus_right
   IS 'Subtracts the right amount from the left and returns the difference and TRUE, if the left amount is larger than the right, or an invalid amount and FALSE otherwise.';
 
 CREATE FUNCTION account_balance_is_sufficient(
-  IN in_account_id BIGINT,
+  IN in_account_id INT8,
   IN in_amount taler_amount,
   OUT out_balance_insufficient BOOLEAN
 )
@@ -149,7 +149,7 @@ CREATE FUNCTION account_delete(
 )
 LANGUAGE plpgsql AS $$
 DECLARE
-my_customer_id BIGINT;
+my_customer_id INT8;
 my_balance_val INT8;
 my_balance_frac INT4;
 BEGIN
@@ -196,15 +196,15 @@ CREATE PROCEDURE register_outgoing(
   IN in_request_uid BYTEA,
   IN in_wtid BYTEA,
   IN in_exchange_base_url TEXT,
-  IN in_debtor_account_id BIGINT,
-  IN in_creditor_account_id BIGINT,
-  IN in_debit_row_id BIGINT,
-  IN in_credit_row_id BIGINT
+  IN in_debtor_account_id INT8,
+  IN in_creditor_account_id INT8,
+  IN in_debit_row_id INT8,
+  IN in_credit_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE 
   local_amount taler_amount;
-  local_bank_account_id BIGINT;
+  local_bank_account_id INT8;
 BEGIN
 -- register outgoing transaction
 INSERT
@@ -235,12 +235,12 @@ COMMENT ON PROCEDURE register_outgoing
 
 CREATE PROCEDURE register_incoming(
   IN in_reserve_pub BYTEA,
-  IN in_tx_row_id BIGINT
+  IN in_tx_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 local_amount taler_amount;
-local_bank_account_id BIGINT;
+local_bank_account_id INT8;
 BEGIN
 -- Register incoming transaction
 INSERT
@@ -271,7 +271,7 @@ CREATE FUNCTION taler_transfer(
   IN in_exchange_base_url TEXT,
   IN in_credit_account_payto TEXT,
   IN in_username TEXT,
-  IN in_timestamp BIGINT,
+  IN in_timestamp INT8,
   -- Error status
   OUT out_debtor_not_found BOOLEAN,
   OUT out_debtor_not_exchange BOOLEAN,
@@ -280,14 +280,14 @@ CREATE FUNCTION taler_transfer(
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_exchange_balance_insufficient BOOLEAN,
   -- Success return
-  OUT out_tx_row_id BIGINT,
-  OUT out_timestamp BIGINT
+  OUT out_tx_row_id INT8,
+  OUT out_timestamp INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
-exchange_bank_account_id BIGINT;
-receiver_bank_account_id BIGINT;
-credit_row_id BIGINT;
+exchange_bank_account_id INT8;
+receiver_bank_account_id INT8;
+credit_row_id INT8;
 BEGIN
 -- Check for idempotence and conflict
 SELECT (amount != in_amount 
@@ -361,7 +361,7 @@ CREATE FUNCTION taler_add_incoming(
   IN in_amount taler_amount,
   IN in_debit_account_payto TEXT,
   IN in_username TEXT,
-  IN in_timestamp BIGINT,
+  IN in_timestamp INT8,
   -- Error status
   OUT out_creditor_not_found BOOLEAN,
   OUT out_creditor_not_exchange BOOLEAN,
@@ -370,12 +370,12 @@ CREATE FUNCTION taler_add_incoming(
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_debitor_balance_insufficient BOOLEAN,
   -- Success return
-  OUT out_tx_row_id BIGINT
+  OUT out_tx_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
-exchange_bank_account_id BIGINT;
-sender_bank_account_id BIGINT;
+exchange_bank_account_id INT8;
+sender_bank_account_id INT8;
 BEGIN
 -- Check conflict
 SELECT true FROM taler_exchange_incoming WHERE reserve_pub = in_reserve_pub
@@ -441,7 +441,7 @@ CREATE FUNCTION bank_transaction(
   IN in_debit_account_username TEXT,
   IN in_subject TEXT,
   IN in_amount taler_amount,
-  IN in_timestamp BIGINT,
+  IN in_timestamp INT8,
   IN in_is_tan BOOLEAN,
   -- Error status
   OUT out_creditor_not_found BOOLEAN,
@@ -451,10 +451,10 @@ CREATE FUNCTION bank_transaction(
   OUT out_creditor_admin BOOLEAN,
   OUT out_tan_required BOOLEAN,
   -- Success return
-  OUT out_credit_bank_account_id BIGINT,
-  OUT out_debit_bank_account_id BIGINT,
-  OUT out_credit_row_id BIGINT,
-  OUT out_debit_row_id BIGINT,
+  OUT out_credit_bank_account_id INT8,
+  OUT out_debit_bank_account_id INT8,
+  OUT out_credit_row_id INT8,
+  OUT out_debit_row_id INT8,
   OUT out_creditor_is_exchange BOOLEAN,
   OUT out_debtor_is_exchange BOOLEAN
 )
@@ -510,6 +510,7 @@ CREATE FUNCTION create_taler_withdrawal(
   IN in_account_username TEXT,
   IN in_withdrawal_uuid UUID,
   IN in_amount taler_amount,
+  IN in_now_date INT8,
    -- Error status
   OUT out_account_not_found BOOLEAN,
   OUT out_account_is_exchange BOOLEAN,
@@ -517,7 +518,7 @@ CREATE FUNCTION create_taler_withdrawal(
 )
 LANGUAGE plpgsql AS $$ 
 DECLARE
-account_id BIGINT;
+account_id INT8;
 BEGIN
 -- Check account exists
 SELECT bank_account_id, is_taler_exchange
@@ -540,8 +541,8 @@ END IF;
 
 -- Create withdrawal operation
 INSERT INTO taler_withdrawal_operations
-    (withdrawal_uuid, wallet_bank_account, amount)
-  VALUES (in_withdrawal_uuid, account_id, in_amount);
+    (withdrawal_uuid, wallet_bank_account, amount, creation_date)
+  VALUES (in_withdrawal_uuid, account_id, in_amount, in_now_date);
 END $$;
 COMMENT ON FUNCTION create_taler_withdrawal IS 'Create a new withdrawal operation';
 
@@ -642,7 +643,7 @@ COMMENT ON FUNCTION abort_taler_withdrawal IS 'Abort a withdrawal operation.';
 CREATE FUNCTION confirm_taler_withdrawal(
   IN in_login TEXT,
   IN in_withdrawal_uuid uuid,
-  IN in_confirmation_date BIGINT,
+  IN in_confirmation_date INT8,
   IN in_is_tan BOOLEAN,
   OUT out_no_op BOOLEAN,
   OUT out_balance_insufficient BOOLEAN,
@@ -658,10 +659,10 @@ DECLARE
   subject_local TEXT;
   reserve_pub_local BYTEA;
   selected_exchange_payto_local TEXT;
-  wallet_bank_account_local BIGINT;
+  wallet_bank_account_local INT8;
   amount_local taler_amount;
-  exchange_bank_account_id BIGINT;
-  tx_row_id BIGINT;
+  exchange_bank_account_id INT8;
+  tx_row_id INT8;
 BEGIN
 -- Check op exists
 SELECT
@@ -740,19 +741,19 @@ COMMENT ON FUNCTION confirm_taler_withdrawal
   IS 'Set a withdrawal operation as confirmed and wire the funds to the exchange.';
 
 CREATE FUNCTION bank_wire_transfer(
-  IN in_creditor_account_id BIGINT,
-  IN in_debtor_account_id BIGINT,
+  IN in_creditor_account_id INT8,
+  IN in_debtor_account_id INT8,
   IN in_subject TEXT,
   IN in_amount taler_amount,
-  IN in_transaction_date BIGINT, -- GNUnet microseconds.
+  IN in_transaction_date INT8,
   IN in_account_servicer_reference TEXT,
   IN in_payment_information_id TEXT,
   IN in_end_to_end_id TEXT,
   -- Error status
   OUT out_balance_insufficient BOOLEAN,
   -- Success return
-  OUT out_credit_row_id BIGINT,
-  OUT out_debit_row_id BIGINT
+  OUT out_credit_row_id INT8,
+  OUT out_debit_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
@@ -961,7 +962,7 @@ PERFORM pg_notify('bank_tx', in_debtor_account_id || ' ' || in_creditor_account_
 END $$;
 
 CREATE FUNCTION cashin(
-  IN in_now_date BIGINT,
+  IN in_now_date INT8,
   IN in_reserve_pub BYTEA,
   IN in_amount taler_amount,
   IN in_subject TEXT,
@@ -974,9 +975,9 @@ CREATE FUNCTION cashin(
 LANGUAGE plpgsql AS $$ 
 DECLARE
   converted_amount taler_amount;
-  admin_account_id BIGINT;
-  exchange_account_id BIGINT;
-  tx_row_id BIGINT;
+  admin_account_id INT8;
+  exchange_account_id INT8;
+  tx_row_id INT8;
 BEGIN
 -- TODO check reserve_pub reuse ?
 
@@ -1056,13 +1057,13 @@ CREATE FUNCTION cashout_create(
   OUT out_no_cashout_payto BOOLEAN,
   OUT out_tan_required BOOLEAN,
   -- Success return
-  OUT out_cashout_id BIGINT
+  OUT out_cashout_id INT8
 )
 LANGUAGE plpgsql AS $$ 
 DECLARE
-account_id BIGINT;
-admin_account_id BIGINT;
-tx_id BIGINT;
+account_id INT8;
+admin_account_id INT8;
+tx_id INT8;
 BEGIN
 -- check conversion
 SELECT too_small OR no_config OR in_amount_credit!=converted INTO out_bad_conversion FROM conversion_to(in_amount_debit, 'cashout'::text);
@@ -1188,7 +1189,7 @@ END $$;
 COMMENT ON FUNCTION tan_challenge_create IS 'Create a new challenge, return the generated id';
 
 CREATE FUNCTION tan_challenge_send (
-  IN in_challenge_id BIGINT,
+  IN in_challenge_id INT8,
   IN in_login TEXT,
   IN in_code TEXT,              -- New code to use if the old code expired
   IN in_now_date INT8,        
@@ -1241,7 +1242,7 @@ END $$;
 COMMENT ON FUNCTION tan_challenge_send IS 'Get the challenge to send, return NULL if nothing should be sent';
 
 CREATE FUNCTION tan_challenge_mark_sent (
-  IN in_challenge_id BIGINT,
+  IN in_challenge_id INT8,
   IN in_now_date INT8,
   IN in_retransmission_period INT8
 ) RETURNS void
@@ -1253,7 +1254,7 @@ $$;
 COMMENT ON FUNCTION tan_challenge_mark_sent IS 'Register a challenge as successfully sent';
 
 CREATE FUNCTION tan_challenge_try (
-  IN in_challenge_id BIGINT, 
+  IN in_challenge_id INT8, 
   IN in_login TEXT,
   IN in_code TEXT,    
   IN in_now_date INT8,
@@ -1305,15 +1306,15 @@ CREATE FUNCTION stats_get_frame(
   IN now TIMESTAMP,
   IN in_timeframe stat_timeframe_enum,
   IN which INTEGER,
-  OUT cashin_count BIGINT,
+  OUT cashin_count INT8,
   OUT cashin_regional_volume taler_amount,
   OUT cashin_fiat_volume taler_amount,
-  OUT cashout_count BIGINT,
+  OUT cashout_count INT8,
   OUT cashout_regional_volume taler_amount,
   OUT cashout_fiat_volume taler_amount,
-  OUT taler_in_count BIGINT,
+  OUT taler_in_count INT8,
   OUT taler_in_volume taler_amount,
-  OUT taler_out_count BIGINT,
+  OUT taler_out_count INT8,
   OUT taler_out_volume taler_amount
 )
 LANGUAGE plpgsql AS $$
@@ -1466,7 +1467,7 @@ BEGIN
   -- Extract product parts
   result = (trunc(product_numeric / 100000000)::int8, (product_numeric % 100000000)::int4);
 
-  IF (result.val > 1::bigint<<52) THEN
+  IF (result.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
 END $$;
@@ -1503,7 +1504,7 @@ BEGIN
   -- Extract division parts
   result = (trunc(fraction_numeric / 100000000)::int8, (fraction_numeric % 100000000)::int4);
 
-  IF (result.val > 1::bigint<<52) THEN
+  IF (result.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
 END $$;
diff --git a/database-versioning/libeufin-conversion-setup.sql b/database-versioning/libeufin-conversion-setup.sql
index 0bf1e506..37661112 100644
--- a/database-versioning/libeufin-conversion-setup.sql
+++ b/database-versioning/libeufin-conversion-setup.sql
@@ -5,7 +5,7 @@ CREATE OR REPLACE FUNCTION cashout_link()
 RETURNS trigger 
 LANGUAGE plpgsql AS $$
   DECLARE
-    now_date BIGINT;
+    now_date INT8;
     payto_uri TEXT;
   BEGIN
     -- TODO should send to an exchange
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION cashin_link()
 RETURNS trigger 
 LANGUAGE plpgsql AS $$
   DECLARE
-    now_date BIGINT;
+    now_date INT8;
     local_amount libeufin_bank.taler_amount;
     subject TEXT;
     too_small BOOLEAN;