aboutsummaryrefslogtreecommitdiff
path: root/src/exchangedb/exchange-0001-part.sql
diff options
context:
space:
mode:
authorMarco Boss <bossm8@bfh.ch>2022-03-31 17:00:44 +0200
committerMarco Boss <bossm8@bfh.ch>2022-03-31 17:00:44 +0200
commit0cf9a9984ce733d8315057b810bfda90cec5c04a (patch)
tree767e0d72b2a3dcc8b4cbd2cea5cadd1c6139492e /src/exchangedb/exchange-0001-part.sql
parenteadee56dcf9d2fed8b8e94d271db31e3c3832cfa (diff)
add sharding logic
Diffstat (limited to 'src/exchangedb/exchange-0001-part.sql')
-rw-r--r--src/exchangedb/exchange-0001-part.sql2826
1 files changed, 2826 insertions, 0 deletions
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
new file mode 100644
index 000000000..359fe9f81
--- /dev/null
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -0,0 +1,2826 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+-- Everything in one big transaction
+BEGIN;
+
+-- ------------------------------ denominations ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS denominations
+ (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
+ ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default later!)
+ ,age_mask INT4 NOT NULL DEFAULT (0)
+ ,denom_pub BYTEA NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,valid_from INT8 NOT NULL
+ ,expire_withdraw INT8 NOT NULL
+ ,expire_deposit INT8 NOT NULL
+ ,expire_legal INT8 NOT NULL
+ ,coin_val INT8 NOT NULL
+ ,coin_frac INT4 NOT NULL
+ ,fee_withdraw_val INT8 NOT NULL
+ ,fee_withdraw_frac INT4 NOT NULL
+ ,fee_deposit_val INT8 NOT NULL
+ ,fee_deposit_frac INT4 NOT NULL
+ ,fee_refresh_val INT8 NOT NULL
+ ,fee_refresh_frac INT4 NOT NULL
+ ,fee_refund_val INT8 NOT NULL
+ ,fee_refund_frac INT4 NOT NULL
+ );
+COMMENT ON TABLE denominations
+ IS 'Main denominations table. All the valid denominations the exchange knows about.';
+COMMENT ON COLUMN denominations.denom_type
+ IS 'determines cipher type for blind signatures used with this denomination; 0 is for RSA';
+COMMENT ON COLUMN denominations.age_mask
+ IS 'bitmask with the age restrictions that are being used for this denomination; 0 if denomination does not support the use of age restrictions';
+COMMENT ON COLUMN denominations.denominations_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
+ ON denominations
+ (expire_legal);
+
+
+-- ------------------------------ denomination_revocations ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS denomination_revocations
+ (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ );
+COMMENT ON TABLE denomination_revocations
+ IS 'remembering which denomination keys have been revoked';
+
+
+-- ------------------------------ wire_targets ----------------------------------------
+
+SELECT create_table_wire_targets();
+
+COMMENT ON TABLE wire_targets
+ IS 'All senders and recipients of money via the exchange';
+COMMENT ON COLUMN wire_targets.payto_uri
+ IS 'Can be a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)';
+COMMENT ON COLUMN wire_targets.wire_target_h_payto
+ IS 'Unsalted hash of payto_uri';
+COMMENT ON COLUMN wire_targets.kyc_ok
+ IS 'true if the KYC check was passed successfully';
+COMMENT ON COLUMN wire_targets.external_id
+ IS 'Name of the user that was used for OAuth 2.0-based legitimization';
+
+CREATE TABLE IF NOT EXISTS wire_targets_default
+ PARTITION OF wire_targets
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_wire_targets_partition('default');
+
+-- ------------------------------ reserves ----------------------------------------
+
+SELECT create_table_reserves();
+
+COMMENT ON TABLE reserves
+ IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.';
+COMMENT ON COLUMN reserves.reserve_pub
+ IS 'EdDSA public key of the reserve. Knowledge of the private key implies ownership over the balance.';
+COMMENT ON COLUMN reserves.current_balance_val
+ IS 'Current balance remaining with the reserve';
+COMMENT ON COLUMN reserves.expiration_date
+ IS 'Used to trigger closing of reserves that have not been drained after some time';
+COMMENT ON COLUMN reserves.gc_date
+ IS 'Used to forget all information about a reserve during garbage collection';
+
+CREATE TABLE IF NOT EXISTS reserves_default
+ PARTITION OF reserves
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+-- ------------------------------ reserves_in ----------------------------------------
+
+SELECT create_table_reserves_in();
+
+COMMENT ON TABLE reserves_in
+ IS 'list of transfers of funds into the reserves, one per incoming wire transfer';
+COMMENT ON COLUMN reserves_in.wire_source_h_payto
+ IS 'Identifies the debited bank account and KYC status';
+COMMENT ON COLUMN reserves_in.reserve_pub
+ IS 'Public key of the reserve. Private key signifies ownership of the remaining balance.';
+COMMENT ON COLUMN reserves_in.credit_val
+ IS 'Amount that was transferred into the reserve';
+
+CREATE TABLE IF NOT EXISTS reserves_in_default
+ PARTITION OF reserves_in
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_reserves_in_partition('default');
+
+-- ------------------------------ reserves_close ----------------------------------------
+
+SELECT create_table_reserves_close();
+
+COMMENT ON TABLE reserves_close
+ IS 'wire transfers executed by the reserve to close reserves';
+COMMENT ON COLUMN reserves_close.wire_target_h_payto
+ IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.';
+
+CREATE TABLE IF NOT EXISTS reserves_close_default
+ PARTITION OF reserves_close
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_reserves_close_partition('default');
+
+
+-- ------------------------------ reserves_out ----------------------------------------
+
+SELECT create_table_reserves_out();
+
+COMMENT ON TABLE reserves_out
+ IS 'Withdraw operations performed on reserves.';
+COMMENT ON COLUMN reserves_out.h_blind_ev
+ IS 'Hash of the blinded coin, used as primary key here so that broken clients that use a non-random coin or blinding factor fail to withdraw (otherwise they would fail on deposit when the coin is not unique there).';
+COMMENT ON COLUMN reserves_out.denominations_serial
+ IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive';
+
+CREATE TABLE IF NOT EXISTS reserves_out_default
+ PARTITION OF reserves_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_reserves_out_partition('default');
+
+
+SELECT create_table_reserves_out_by_reserve();
+
+COMMENT ON TABLE reserves_out_by_reserve
+ IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.';
+
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
+ PARTITION OF reserves_out_by_reserve
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO reserves_out_by_reserve
+ (reserve_uuid
+ ,h_blind_ev)
+ VALUES
+ (NEW.reserve_uuid
+ ,NEW.h_blind_ev);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
+ IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_insert
+ AFTER INSERT
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM reserves_out_by_reserve
+ WHERE reserve_uuid = OLD.reserve_uuid;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
+ IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_delete
+ AFTER DELETE
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+
+
+-- ------------------------------ auditors ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS auditors
+ (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
+ ,auditor_name VARCHAR NOT NULL
+ ,auditor_url VARCHAR NOT NULL
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
+ );
+COMMENT ON TABLE auditors
+ IS 'Table with auditors the exchange uses or has used in the past. Entries never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN auditors.auditor_pub
+ IS 'Public key of the auditor.';
+COMMENT ON COLUMN auditors.auditor_url
+ IS 'The base URL of the auditor.';
+COMMENT ON COLUMN auditors.is_active
+ IS 'true if we are currently supporting the use of this auditor.';
+COMMENT ON COLUMN auditors.last_change
+ IS 'Latest time when active status changed. Used to detect replays of old messages.';
+
+
+-- ------------------------------ auditor_denom_sigs ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS auditor_denom_sigs
+ (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE CASCADE
+ ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
+ ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
+ ,PRIMARY KEY (denominations_serial, auditor_uuid)
+ );
+COMMENT ON TABLE auditor_denom_sigs
+ IS 'Table with auditor signatures on exchange denomination keys.';
+COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid
+ IS 'Identifies the auditor.';
+COMMENT ON COLUMN auditor_denom_sigs.denominations_serial
+ IS 'Denomination the signature is for.';
+COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
+ IS 'Signature of the auditor, of purpose TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
+
+
+-- ------------------------------ exchange_sign_keys ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS exchange_sign_keys
+ (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,valid_from INT8 NOT NULL
+ ,expire_sign INT8 NOT NULL
+ ,expire_legal INT8 NOT NULL
+ );
+COMMENT ON TABLE exchange_sign_keys
+ IS 'Table with master public key signatures on exchange online signing keys.';
+COMMENT ON COLUMN exchange_sign_keys.exchange_pub
+ IS 'Public online signing key of the exchange.';
+COMMENT ON COLUMN exchange_sign_keys.master_sig
+ IS 'Signature affirming the validity of the signing key of purpose TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
+COMMENT ON COLUMN exchange_sign_keys.valid_from
+ IS 'Time when this online signing key will first be used to sign messages.';
+COMMENT ON COLUMN exchange_sign_keys.expire_sign
+ IS 'Time when this online signing key will no longer be used to sign.';
+COMMENT ON COLUMN exchange_sign_keys.expire_legal
+ IS 'Time when this online signing key legally expires.';
+
+
+-- ------------------------------ signkey_revocations ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS signkey_revocations
+ (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON DELETE CASCADE
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ );
+COMMENT ON TABLE signkey_revocations
+ IS 'Table storing which online signing keys have been revoked';
+
+
+-- ------------------------------ extension ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS extensions
+ (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,name VARCHAR NOT NULL UNIQUE
+ ,config BYTEA
+ );
+COMMENT ON TABLE extensions
+ IS 'Configurations of the activated extensions';
+COMMENT ON COLUMN extensions.name
+ IS 'Name of the extension';
+COMMENT ON COLUMN extensions.config
+ IS 'Configuration of the extension as JSON-blob, maybe NULL';
+
+
+-- ------------------------------ known_coins ----------------------------------------
+
+SELECT create_table_known_coins();
+
+COMMENT ON TABLE known_coins
+ IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations';
+COMMENT ON COLUMN known_coins.denominations_serial
+ IS 'Denomination of the coin, determines the value of the original coin and applicable fees for coin-specific operations.';
+COMMENT ON COLUMN known_coins.coin_pub
+ IS 'EdDSA public key of the coin';
+COMMENT ON COLUMN known_coins.remaining_val
+ IS 'Value of the coin that remains to be spent';
+COMMENT ON COLUMN known_coins.age_commitment_hash
+ IS 'Optional hash of the age commitment for age restrictions as per DD 24 (active if denom_type has the respective bit set)';
+COMMENT ON COLUMN known_coins.denom_sig
+ IS 'This is the signature of the exchange that affirms that the coin is a valid coin. The specific signature type depends on denom_type of the denomination.';
+
+CREATE TABLE IF NOT EXISTS known_coins_default
+ PARTITION OF known_coins
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_known_coins_partition('default');
+
+
+-- ------------------------------ refresh_commitments ----------------------------------------
+
+SELECT create_table_refresh_commitments();
+
+COMMENT ON TABLE refresh_commitments
+ IS 'Commitments made when melting coins and the gamma value chosen by the exchange.';
+COMMENT ON COLUMN refresh_commitments.noreveal_index
+ IS 'The gamma value chosen by the exchange in the cut-and-choose protocol';
+COMMENT ON COLUMN refresh_commitments.rc
+ IS 'Commitment made by the client, hash over the various client inputs in the cut-and-choose protocol';
+COMMENT ON COLUMN refresh_commitments.old_coin_pub
+ IS 'Coin being melted in the refresh process.';
+
+CREATE TABLE IF NOT EXISTS refresh_commitments_default
+ PARTITION OF refresh_commitments
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_refresh_commitments_partition('default');
+
+
+-- ------------------------------ refresh_revealed_coins ----------------------------------------
+
+SELECT create_table_refresh_revealed_coins();
+
+COMMENT ON TABLE refresh_revealed_coins
+ IS 'Revelations about the new coins that are to be created during a melting session.';
+COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
+ IS 'needed for exchange-auditor replication logic';
+COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the melt operation.';
+COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index
+ IS 'index of the fresh coin being created (one melt operation may result in multiple fresh coins)';
+COMMENT ON COLUMN refresh_revealed_coins.coin_ev
+ IS 'envelope of the new coin to be signed';
+COMMENT ON COLUMN refresh_revealed_coins.ewv
+ IS 'exchange contributed values in the creation of the fresh coin (see /csr)';
+COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
+ IS 'hash of the envelope of the new coin to be signed (for lookups)';
+COMMENT ON COLUMN refresh_revealed_coins.ev_sig
+ IS 'exchange signature over the envelope';
+
+CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
+ PARTITION OF refresh_revealed_coins
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_refresh_revealed_coins_partition('default');
+
+
+-- ------------------------------ refresh_transfer_keys ----------------------------------------
+
+SELECT create_table_refresh_transfer_keys();
+
+COMMENT ON TABLE refresh_transfer_keys
+ IS 'Transfer keys of a refresh operation (the data revealed to the exchange).';
+COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
+ IS 'needed for exchange-auditor replication logic';
+COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the operation.';
+COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
+ IS 'transfer public key for the gamma index';
+COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
+ IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been revealed, with the gamma entry being skipped';
+
+CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
+ PARTITION OF refresh_transfer_keys
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_refresh_transfer_keys_partition('default');
+
+
+-- ------------------------------ extension_details ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS extension_details
+ (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,extension_options VARCHAR)
+ PARTITION BY HASH (extension_details_serial_id);
+COMMENT ON TABLE extension_details
+ IS 'Extensions that were provided with deposits (not yet used).';
+COMMENT ON COLUMN extension_details.extension_options
+ IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the extensions supported by the exchange.';
+
+CREATE TABLE IF NOT EXISTS extension_details_default
+ PARTITION OF extension_details
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ deposits ----------------------------------------
+
+SELECT create_table_deposits();
+
+COMMENT ON TABLE deposits
+ IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).';
+COMMENT ON COLUMN deposits.shard
+ IS 'Used for load sharding in the materialized indices. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
+COMMENT ON COLUMN deposits.known_coin_id
+ IS 'Used for garbage collection';
+COMMENT ON COLUMN deposits.wire_target_h_payto
+ IS 'Identifies the target bank account and KYC status';
+COMMENT ON COLUMN deposits.wire_salt
+ IS 'Salt used when hashing the payto://-URI to get the h_wire';
+COMMENT ON COLUMN deposits.done
+ IS 'Set to TRUE once we have included this deposit in some aggregate wire transfer to the merchant';
+COMMENT ON COLUMN deposits.extension_blocked
+ IS 'True if the aggregation of the deposit is currently blocked by some extension mechanism. Used to filter out deposits that must not be processed by the canonical deposit logic.';
+COMMENT ON COLUMN deposits.extension_details_serial_id
+ IS 'References extensions table, NULL if extensions are not used';
+
+CREATE TABLE IF NOT EXISTS deposits_default
+ PARTITION OF deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_deposits_partition('default');
+
+
+SELECT create_table_deposits_by_ready();
+
+COMMENT ON TABLE deposits_by_ready
+ IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below';
+
+CREATE TABLE IF NOT EXISTS deposits_by_ready_default
+ PARTITION OF deposits_by_ready
+ DEFAULT;
+
+
+SELECT create_table_deposits_for_matching();
+
+COMMENT ON TABLE deposits_for_matching
+ IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below';
+
+CREATE TABLE IF NOT EXISTS deposits_for_matching_default
+ PARTITION OF deposits_for_matching
+ DEFAULT;
+
+
+CREATE OR REPLACE FUNCTION deposits_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ is_ready BOOLEAN;
+BEGIN
+ is_ready = NOT (NEW.done OR NEW.extension_blocked);
+
+ IF (is_ready)
+ THEN
+ INSERT INTO deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ INSERT INTO deposits_for_matching
+ (refund_deadline
+ ,merchant_pub
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.merchant_pub
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_insert_trigger()
+ IS 'Replicate deposit inserts into materialized indices.';
+
+CREATE TRIGGER deposits_on_insert
+ AFTER INSERT
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger();
+
+CREATE OR REPLACE FUNCTION deposits_update_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+DECLARE
+ is_ready BOOLEAN;
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.extension_blocked);
+ is_ready = NOT (NEW.done OR NEW.extension_blocked);
+ IF (was_ready AND NOT is_ready)
+ THEN
+ DELETE FROM deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ DELETE FROM deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND merchant_pub = OLD.merchant_pub
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ IF (is_ready AND NOT was_ready)
+ THEN
+ INSERT INTO deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ INSERT INTO deposits_for_matching
+ (refund_deadline
+ ,merchant_pub
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.merchant_pub
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_update_trigger()
+ IS 'Replicate deposits changes into materialized indices.';
+
+CREATE TRIGGER deposits_on_update
+ AFTER UPDATE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger();
+
+CREATE OR REPLACE FUNCTION deposits_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.extension_blocked);
+
+ IF (was_ready)
+ THEN
+ DELETE FROM deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ DELETE FROM deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND merchant_pub = OLD.merchant_pub
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_delete_trigger()
+ IS 'Replicate deposit deletions into materialized indices.';
+
+CREATE TRIGGER deposits_on_delete
+ AFTER DELETE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
+
+
+-- ------------------------------ refunds ----------------------------------------
+
+SELECT create_table_refunds();
+
+COMMENT ON TABLE refunds
+ IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.';
+COMMENT ON COLUMN refunds.deposit_serial_id
+ IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.';
+COMMENT ON COLUMN refunds.rtransaction_id
+ IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund';
+
+CREATE TABLE IF NOT EXISTS refunds_default
+ PARTITION OF refunds
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_refunds_partition('default');
+
+
+-- ------------------------------ wire_out ----------------------------------------
+
+SELECT create_table_wire_out();
+
+COMMENT ON TABLE wire_out
+ IS 'wire transfers the exchange has executed';
+COMMENT ON COLUMN wire_out.exchange_account_section
+ IS 'identifies the configuration section with the debit account of this payment';
+COMMENT ON COLUMN wire_out.wire_target_h_payto
+ IS 'Identifies the credited bank account and KYC status';
+
+CREATE TABLE IF NOT EXISTS wire_out_default
+ PARTITION OF wire_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_wire_out_partition('default');
+
+CREATE OR REPLACE FUNCTION wire_out_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM aggregation_tracking
+ WHERE wtid_raw = OLD.wtid_raw;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION wire_out_delete_trigger()
+ IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint and conflicted with nice partitioning.';
+
+CREATE TRIGGER wire_out_on_delete
+ AFTER DELETE
+ ON wire_out
+ FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger();
+
+
+
+-- ------------------------------ aggregation_transient ----------------------------------------
+
+SELECT create_table_aggregation_transient();
+
+COMMENT ON TABLE aggregation_transient
+ IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated';
+COMMENT ON COLUMN aggregation_transient.amount_val
+ IS 'Sum of all of the aggregated deposits (without deposit fees)';
+COMMENT ON COLUMN aggregation_transient.wtid_raw
+ IS 'identifier of the wire transfer';
+
+CREATE TABLE IF NOT EXISTS aggregation_transient_default
+ PARTITION OF aggregation_transient
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ aggregation_tracking ----------------------------------------
+
+SELECT create_table_aggregation_tracking();
+
+COMMENT ON TABLE aggregation_tracking
+ IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
+COMMENT ON COLUMN aggregation_tracking.wtid_raw
+ IS 'identifier of the wire transfer';
+
+CREATE TABLE IF NOT EXISTS aggregation_tracking_default
+ PARTITION OF aggregation_tracking
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_aggregation_tracking_partition('default');
+
+
+-- ------------------------------ wire_fee ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wire_fee
+ (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,wire_method VARCHAR NOT NULL
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,wire_fee_val INT8 NOT NULL
+ ,wire_fee_frac INT4 NOT NULL
+ ,closing_fee_val INT8 NOT NULL
+ ,closing_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (wire_method, start_date)
+ );
+COMMENT ON TABLE wire_fee
+ IS 'list of the wire fees of this exchange, by date';
+COMMENT ON COLUMN wire_fee.wire_fee_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
+ ON wire_fee
+ (end_date);
+
+
+-- ------------------------------ global_fee ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS global_fee
+ (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,history_fee_val INT8 NOT NULL
+ ,history_fee_frac INT4 NOT NULL
+ ,kyc_fee_val INT8 NOT NULL
+ ,kyc_fee_frac INT4 NOT NULL
+ ,account_fee_val INT8 NOT NULL
+ ,account_fee_frac INT4 NOT NULL
+ ,purse_fee_val INT8 NOT NULL
+ ,purse_fee_frac INT4 NOT NULL
+ ,purse_timeout INT8 NOT NULL
+ ,kyc_timeout INT8 NOT NULL
+ ,history_expiration INT8 NOT NULL
+ ,purse_account_limit INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (start_date)
+ );
+COMMENT ON TABLE global_fee
+ IS 'list of the global fees of this exchange, by date';
+COMMENT ON COLUMN global_fee.global_fee_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
+ ON global_fee
+ (end_date);
+
+
+-- ------------------------------ recoup ----------------------------------------
+
+SELECT create_table_recoup();
+
+COMMENT ON TABLE recoup
+ IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.';
+COMMENT ON COLUMN recoup.coin_pub
+ IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
+COMMENT ON COLUMN recoup.reserve_out_serial_id
+ IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.';
+COMMENT ON COLUMN recoup.coin_sig
+ IS 'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP';
+COMMENT ON COLUMN recoup.coin_blind
+ IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.';
+
+CREATE TABLE IF NOT EXISTS recoup_default
+ PARTITION OF recoup
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_recoup_partition('default');
+
+
+SELECT create_table_recoup_by_reserve();
+
+COMMENT ON TABLE recoup_by_reserve
+ IS 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.';
+
+CREATE TABLE IF NOT EXISTS recoup_by_reserve_default
+ PARTITION OF recoup_by_reserve
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION recoup_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO recoup_by_reserve
+ (reserve_out_serial_id
+ ,coin_pub)
+ VALUES
+ (NEW.reserve_out_serial_id
+ ,NEW.coin_pub);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION recoup_insert_trigger()
+ IS 'Replicate recoup inserts into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_insert
+ AFTER INSERT
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
+
+CREATE OR REPLACE FUNCTION recoup_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM recoup_by_reserve
+ WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
+ AND coin_pub = OLD.coin_pub;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION recoup_delete_trigger()
+ IS 'Replicate recoup deletions into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_delete
+ AFTER DELETE
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
+
+
+-- ------------------------------ recoup_refresh ----------------------------------------
+
+SELECT create_table_recoup_refresh();
+
+COMMENT ON TABLE recoup_refresh
+ IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.';
+COMMENT ON COLUMN recoup_refresh.coin_pub
+ IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
+COMMENT ON COLUMN recoup_refresh.known_coin_id
+ IS 'FIXME: (To be) used for garbage collection (in the future)';
+COMMENT ON COLUMN recoup_refresh.rrc_serial
+ IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).';
+COMMENT ON COLUMN recoup_refresh.coin_blind
+ IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.';
+
+CREATE TABLE IF NOT EXISTS recoup_refresh_default
+ PARTITION OF recoup_refresh
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_recoup_refresh_partition('default');
+
+
+-- ------------------------------ prewire ----------------------------------------
+
+SELECT create_table_prewire();
+
+COMMENT ON TABLE prewire
+ IS 'pre-commit data for wire transfers we are about to execute';
+COMMENT ON COLUMN prewire.failed
+ IS 'set to TRUE if the bank responded with a non-transient failure to our transfer request';
+COMMENT ON COLUMN prewire.finished
+ IS 'set to TRUE once bank confirmed receiving the wire transfer request';
+COMMENT ON COLUMN prewire.buf
+ IS 'serialized data to send to the bank to execute the wire transfer';
+
+CREATE TABLE IF NOT EXISTS prewire_default
+ PARTITION OF prewire
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ wire_accounts ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wire_accounts
+ (payto_uri VARCHAR PRIMARY KEY
+ ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
+ );
+COMMENT ON TABLE wire_accounts
+ IS 'Table with current and historic bank accounts of the exchange. Entries never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN wire_accounts.payto_uri
+ IS 'payto URI (RFC 8905) with the bank account of the exchange.';
+COMMENT ON COLUMN wire_accounts.master_sig
+ IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS';
+COMMENT ON COLUMN wire_accounts.is_active
+ IS 'true if we are currently supporting the use of this account.';
+COMMENT ON COLUMN wire_accounts.last_change
+ IS 'Latest time when active status changed. Used to detect replays of old messages.';
+-- "wire_accounts" has no sequence because it is a 'mutable' table
+-- and is of no concern to the auditor
+
+
+-- ------------------------------ cs_nonce_locks ----------------------------------------
+
+SELECT create_table_cs_nonce_locks();
+
+COMMENT ON TABLE cs_nonce_locks
+ IS 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash';
+COMMENT ON COLUMN cs_nonce_locks.nonce
+ IS 'actual nonce submitted by the client';
+COMMENT ON COLUMN cs_nonce_locks.op_hash
+ IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with';
+COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
+ IS 'Maximum number of a CS denomination serial the nonce could be used with, for GC';
+
+CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
+ PARTITION OF cs_nonce_locks
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_cs_nonce_locks_partition('default');
+
+
+-- ------------------------------ work_shards ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT8 NOT NULL
+ ,end_row INT8 NOT NULL
+ ,completed BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
+ );
+COMMENT ON TABLE work_shards
+ IS 'coordinates work between multiple processes working on the same job';
+COMMENT ON COLUMN work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN work_shards.completed
+ IS 'set to TRUE once the shard is finished by a worker';
+COMMENT ON COLUMN work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+COMMENT ON COLUMN work_shards.job_name
+ IS 'unique name of the job the workers on this shard are performing';
+
+CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
+ ON work_shards
+ (job_name
+ ,completed
+ ,last_attempt
+ );
+
+
+-- ------------------------------ revolving_work_shards ----------------------------------------
+
+CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT4 NOT NULL
+ ,end_row INT4 NOT NULL
+ ,active BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
+ );
+COMMENT ON TABLE revolving_work_shards
+ IS 'coordinates work between multiple processes working on the same job with partitions that need to be repeatedly processed; unlogged because on system crashes the locks represented by this table will have to be cleared anyway, typically using "taler-exchange-dbinit -s"';
+COMMENT ON COLUMN revolving_work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN revolving_work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN revolving_work_shards.active
+ IS 'set to TRUE when a worker is active on the shard';
+COMMENT ON COLUMN revolving_work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN revolving_work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+COMMENT ON COLUMN revolving_work_shards.job_name
+ IS 'unique name of the job the workers on this shard are performing';
+
+CREATE INDEX IF NOT EXISTS revolving_work_shards_by_job_name_active_last_attempt_index
+ ON revolving_work_shards
+ (job_name
+ ,active
+ ,last_attempt
+ );
+
+--------------------------------------------------------------------------
+-- Tables for P2P payments
+--------------------------------------------------------------------------
+
+-- ------------------------------ partners ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS partners
+ (partner_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32)
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,wad_frequency INT8 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,partner_base_url TEXT NOT NULL
+ );
+COMMENT ON TABLE partners
+ IS 'exchanges we do wad transfers to';
+COMMENT ON COLUMN partners.partner_master_pub
+ IS 'offline master public key of the partner';
+COMMENT ON COLUMN partners.start_date
+ IS 'starting date of the partnership';
+COMMENT ON COLUMN partners.end_date
+ IS 'end date of the partnership';
+COMMENT ON COLUMN partners.wad_frequency
+ IS 'how often do we promise to do wad transfers';
+COMMENT ON COLUMN partners.wad_fee_val
+ IS 'how high is the fee for a wallet to be added to a wad to this partner';
+COMMENT ON COLUMN partners.partner_base_url
+ IS 'base URL of the REST API for this partner';
+COMMENT ON COLUMN partners.master_sig
+ IS 'signature of our master public key affirming the partnership, of purpose TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
+
+
+-- ------------------------------ purse_requests ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS purse_requests
+ (purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)
+ ,purse_expiration INT8 NOT NULL
+ ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
+ ,age_limit INT4 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,balance_val INT8 NOT NULL DEFAULT (0)
+ ,balance_frac INT4 NOT NULL DEFAULT (0)
+ ,purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)
+ ,PRIMARY KEY (purse_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE purse_requests
+ IS 'Requests establishing purses, associating them with a contract but without a target reserve';
+COMMENT ON COLUMN purse_requests.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN purse_requests.purse_expiration
+ IS 'When the purse is set to expire';
+COMMENT ON COLUMN purse_requests.h_contract_terms
+ IS 'Hash of the contract the parties are to agree to';
+COMMENT ON COLUMN purse_requests.amount_with_fee_val
+ IS 'Total amount expected to be in the purse';
+COMMENT ON COLUMN purse_requests.balance_val
+ IS 'Total amount actually in the purse';
+COMMENT ON COLUMN purse_requests.purse_sig
+ IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST';
+
+-- FIXME: change to materialized index by marge_pub!
+CREATE INDEX IF NOT EXISTS purse_requests_merge_pub
+ ON purse_requests (merge_pub);
+
+CREATE TABLE IF NOT EXISTS purse_requests_default
+ PARTITION OF purse_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_requests_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key '
+ 'UNIQUE (purse_requests_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_requests_partition('default');
+
+
+
+-- ------------------------------ purse_merges ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS purse_merges
+ (purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)--REFERENCES reserves (reserve_pub) ON DELETE CASCADE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE
+ ,merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)
+ ,merge_timestamp INT8 NOT NULL
+ ,PRIMARY KEY (purse_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE purse_merges
+ IS 'Merge requests where a purse-owner requested merging the purse into the account';
+COMMENT ON COLUMN purse_merges.partner_serial_id
+ IS 'identifies the partner exchange, NULL in case the target reserve lives at this exchange';
+COMMENT ON COLUMN purse_merges.reserve_pub
+ IS 'public key of the target reserve';
+COMMENT ON COLUMN purse_merges.purse_pub
+ IS 'public key of the purse';
+COMMENT ON COLUMN purse_merges.merge_sig
+ IS 'signature by the purse private key affirming the merge, of type TALER_SIGNATURE_WALLET_PURSE_MERGE';
+COMMENT ON COLUMN purse_merges.merge_timestamp
+ IS 'when was the merge message signed';
+
+CREATE INDEX IF NOT EXISTS purse_merges_purse_pub
+ ON purse_merges (purse_pub);
+-- FIXME: change to materialized index by reserve_pub!
+CREATE INDEX IF NOT EXISTS purse_merges_reserve_pub
+ ON purse_merges (reserve_pub);
+COMMENT ON INDEX purse_merges_reserve_pub
+ IS 'needed in reserve history computation';
+
+CREATE TABLE IF NOT EXISTS purse_merges_default
+ PARTITION OF purse_merges
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_merges_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key '
+ 'UNIQUE (purse_merge_request_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_merges_partition('default');
+
+
+
+-- ------------------------------ account_merges ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS account_merges
+ (account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) -- REFERENCES purse_requests (purse_pub)
+ ,PRIMARY KEY (purse_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE account_merges
+ IS 'Merge requests where a purse- and account-owner requested merging the purse into the account';
+COMMENT ON COLUMN account_merges.reserve_pub
+ IS 'public key of the target reserve';
+COMMENT ON COLUMN account_merges.purse_pub
+ IS 'public key of the purse';
+COMMENT ON COLUMN account_merges.reserve_sig
+ IS 'signature by the reserve private key affirming the merge, of type TALER_SIGNATURE_WALLET_ACCOUNT_MERGE';
+
+CREATE INDEX IF NOT EXISTS account_merges_purse_pub
+ ON account_merges (purse_pub);
+COMMENT ON INDEX account_merges_purse_pub
+ IS 'needed when checking for a purse merge status';
+
+-- FIXME: change to materialized index by reserve_pub!
+CREATE INDEX IF NOT EXISTS account_merges_by_reserve_pub
+ ON account_merges (reserve_pub);
+
+CREATE TABLE IF NOT EXISTS account_merges_default
+ PARTITION OF account_merges
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE account_merges_' || partition_suffix || ' '
+ 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key '
+ 'UNIQUE (account_merge_request_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_account_merges_partition('default');
+
+
+-- ------------------------------ contracts ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS contracts
+ (contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)
+ ,e_contract BYTEA NOT NULL
+ ,purse_expiration INT8 NOT NULL
+ ,PRIMARY KEY (purse_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE contracts
+ IS 'encrypted contracts associated with purses';
+COMMENT ON COLUMN contracts.purse_pub
+ IS 'public key of the purse that the contract is associated with';
+COMMENT ON COLUMN contracts.pub_ckey
+ IS 'Public ECDH key used to encrypt the contract, to be used with the purse private key for decryption';
+COMMENT ON COLUMN contracts.e_contract
+ IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after decryption)';
+
+CREATE TABLE IF NOT EXISTS contracts_default
+ PARTITION OF contracts
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE contracts_' || partition_suffix || ' '
+ 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key '
+ 'UNIQUE (contract_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_contracts_partition('default');
+
+
+-- ------------------------------ history_requests ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS history_requests
+ (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE
+ ,request_timestamp INT8 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,history_fee_val INT8 NOT NULL
+ ,history_fee_frac INT4 NOT NULL
+ ,PRIMARY KEY (reserve_pub,request_timestamp)
+ )
+ PARTITION BY HASH (reserve_pub);
+COMMENT ON TABLE history_requests
+ IS 'Paid history requests issued by a client against a reserve';
+COMMENT ON COLUMN history_requests.request_timestamp
+ IS 'When was the history request made';
+COMMENT ON COLUMN history_requests.reserve_sig
+ IS 'Signature approving payment for the history request';
+COMMENT ON COLUMN history_requests.history_fee_val
+ IS 'History fee approved by the signature';
+
+CREATE TABLE IF NOT EXISTS history_requests_default
+ PARTITION OF history_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ close_requests ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS close_requests
+ (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE
+ ,close_timestamp INT8 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,close_val INT8 NOT NULL
+ ,close_frac INT4 NOT NULL
+ ,PRIMARY KEY (reserve_pub,close_timestamp)
+ )
+ PARTITION BY HASH (reserve_pub);
+COMMENT ON TABLE close_requests
+ IS 'Explicit requests by a reserve owner to close a reserve immediately';
+COMMENT ON COLUMN close_requests.close_timestamp
+ IS 'When the request was created by the client';
+COMMENT ON COLUMN close_requests.reserve_sig
+ IS 'Signature affirming that the reserve is to be closed';
+COMMENT ON COLUMN close_requests.close_val
+ IS 'Balance of the reserve at the time of closing, to be wired to the associated bank account (minus the closing fee)';
+
+CREATE TABLE IF NOT EXISTS close_requests_default
+ PARTITION OF close_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ purse_deposits ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS purse_deposits
+ (purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
+ -- ,PRIMARY KEY (purse_pub,coin_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE purse_deposits
+ IS 'Requests depositing coins into a purse';
+COMMENT ON COLUMN purse_deposits.partner_serial_id
+ IS 'identifies the partner exchange, NULL in case the target purse lives at this exchange';
+COMMENT ON COLUMN purse_deposits.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN purse_deposits.coin_pub
+ IS 'Public key of the coin being deposited';
+COMMENT ON COLUMN purse_deposits.amount_with_fee_val
+ IS 'Total amount being deposited';
+COMMENT ON COLUMN purse_deposits.coin_sig
+ IS 'Signature of the coin affirming the deposit into the purse, of type TALER_SIGNATURE_PURSE_DEPOSIT';
+
+-- FIXME: change to materialized index by coin_pub!
+CREATE INDEX IF NOT EXISTS purse_deposits_by_coin_pub
+ ON purse_deposits (coin_pub);
+
+CREATE TABLE IF NOT EXISTS purse_deposits_default
+ PARTITION OF purse_deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_deposits_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key '
+ 'UNIQUE (purse_deposit_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_deposits_partition('default');
+
+
+-- ------------------------------ wads_out ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wads_out
+ (wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
+ ,partner_serial_id INT8 NOT NULL REFERENCES partners(partner_serial_id) ON DELETE CASCADE
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,execution_time INT8 NOT NULL
+ )
+ PARTITION BY HASH (wad_id);
+COMMENT ON TABLE wads_out
+ IS 'Wire transfers made to another exchange to transfer purse funds';
+COMMENT ON COLUMN wads_out.wad_id
+ IS 'Unique identifier of the wad, part of the wire transfer subject';
+COMMENT ON COLUMN wads_out.partner_serial_id
+ IS 'target exchange of the wad';
+COMMENT ON COLUMN wads_out.amount_val
+ IS 'Amount that was wired';
+COMMENT ON COLUMN wads_out.execution_time
+ IS 'Time when the wire transfer was scheduled';
+
+CREATE INDEX IF NOT EXISTS wads_out_index_by_wad_id
+ ON wads_out (wad_id);
+
+CREATE TABLE IF NOT EXISTS wads_out_default
+ PARTITION OF wads_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wads_out_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key '
+ 'UNIQUE (wad_out_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wads_out_partition('default');
+
+
+-- ------------------------------ wads_out_entries ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wad_out_entries
+ (wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_out_serial_id INT8 -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
+ ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
+ ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
+ ,purse_expiration INT8 NOT NULL
+ ,merge_timestamp INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,deposit_fees_val INT8 NOT NULL
+ ,deposit_fees_frac INT4 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
+ )
+ PARTITION BY HASH (purse_pub);
+-- FIXME: convert to materialized index!
+CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_reserve_pub
+ ON wad_out_entries (reserve_pub);
+COMMENT ON TABLE wad_out_entries
+ IS 'Purses combined into a wad';
+COMMENT ON COLUMN wad_out_entries.wad_out_serial_id
+ IS 'Wad the purse was part of';
+COMMENT ON COLUMN wad_out_entries.reserve_pub
+ IS 'Target reserve for the purse';
+COMMENT ON COLUMN wad_out_entries.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN wad_out_entries.h_contract
+ IS 'Hash of the contract associated with the purse';
+COMMENT ON COLUMN wad_out_entries.purse_expiration
+ IS 'Time when the purse expires';
+COMMENT ON COLUMN wad_out_entries.merge_timestamp
+ IS 'Time when the merge was approved';
+COMMENT ON COLUMN wad_out_entries.amount_with_fee_val
+ IS 'Total amount in the purse';
+COMMENT ON COLUMN wad_out_entries.wad_fee_val
+ IS 'Wat fee charged to the purse';
+COMMENT ON COLUMN wad_out_entries.deposit_fees_val
+ IS 'Total deposit fees charged to the purse';
+COMMENT ON COLUMN wad_out_entries.reserve_sig
+ IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE';
+COMMENT ON COLUMN wad_out_entries.purse_sig
+ IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
+
+CREATE TABLE IF NOT EXISTS wad_out_entries_default
+ PARTITION OF wad_out_entries
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wad_out_entries_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key '
+ 'UNIQUE (wad_out_entry_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wad_out_entries_partition('default');
+
+
+-- ------------------------------ wads_in ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wads_in
+ (wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
+ ,origin_exchange_url TEXT NOT NULL
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,arrival_time INT8 NOT NULL
+ ,UNIQUE (wad_id, origin_exchange_url)
+ )
+ PARTITION BY HASH (wad_id);
+COMMENT ON TABLE wads_in
+ IS 'Incoming exchange-to-exchange wad wire transfers';
+COMMENT ON COLUMN wads_in.wad_id
+ IS 'Unique identifier of the wad, part of the wire transfer subject';
+COMMENT ON COLUMN wads_in.origin_exchange_url
+ IS 'Base URL of the originating URL, also part of the wire transfer subject';
+COMMENT ON COLUMN wads_in.amount_val
+ IS 'Actual amount that was received by our exchange';
+COMMENT ON COLUMN wads_in.arrival_time
+ IS 'Time when the wad was received';
+
+CREATE TABLE IF NOT EXISTS wads_in_default
+ PARTITION OF wads_in
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wads_in_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key '
+ 'UNIQUE (wad_in_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wads_in_partition('default');
+
+
+-- ------------------------------ wads_in_entries ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS wad_in_entries
+ (wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_in_serial_id INT8 -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
+ ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
+ ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
+ ,purse_expiration INT8 NOT NULL
+ ,merge_timestamp INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,deposit_fees_val INT8 NOT NULL
+ ,deposit_fees_frac INT4 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE wad_in_entries
+ IS 'list of purses aggregated in a wad according to the sending exchange';
+COMMENT ON COLUMN wad_in_entries.wad_in_serial_id
+ IS 'wad for which the given purse was included in the aggregation';
+COMMENT ON COLUMN wad_in_entries.reserve_pub
+ IS 'target account of the purse (must be at the local exchange)';
+COMMENT ON COLUMN wad_in_entries.purse_pub
+ IS 'public key of the purse that was merged';
+COMMENT ON COLUMN wad_in_entries.h_contract
+ IS 'hash of the contract terms of the purse';
+COMMENT ON COLUMN wad_in_entries.purse_expiration
+ IS 'Time when the purse was set to expire';
+COMMENT ON COLUMN wad_in_entries.merge_timestamp
+ IS 'Time when the merge was approved';
+COMMENT ON COLUMN wad_in_entries.amount_with_fee_val
+ IS 'Total amount in the purse';
+COMMENT ON COLUMN wad_in_entries.wad_fee_val
+ IS 'Total wad fees paid by the purse';
+COMMENT ON COLUMN wad_in_entries.deposit_fees_val
+ IS 'Total deposit fees paid when depositing coins into the purse';
+COMMENT ON COLUMN wad_in_entries.reserve_sig
+ IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE';
+COMMENT ON COLUMN wad_in_entries.purse_sig
+ IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
+-- FIXME: convert to materialized index!
+CREATE INDEX IF NOT EXISTS wad_in_entries_reserve_pub
+ ON wad_in_entries (reserve_pub);
+COMMENT ON INDEX wad_in_entries_reserve_pub
+ IS 'needed to compute reserve history';
+
+CREATE TABLE IF NOT EXISTS wad_in_entries_default
+ PARTITION OF wad_in_entries
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wad_in_entries_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key '
+ 'UNIQUE (wad_in_entry_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wad_in_entries_partition('default');
+
+
+-- ------------------------------ partner_accounts ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS partner_accounts
+ (payto_uri VARCHAR PRIMARY KEY
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
+ ,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64)
+ ,last_seen INT8 NOT NULL
+ );
+CREATE INDEX IF NOT EXISTS partner_accounts_index_by_partner_and_time
+ ON partner_accounts (partner_serial_id,last_seen);
+COMMENT ON TABLE partner_accounts
+ IS 'Table with bank accounts of the partner exchange. Entries never expire as we need to remember the signature for the auditor.';
+COMMENT ON COLUMN partner_accounts.payto_uri
+ IS 'payto URI (RFC 8905) with the bank account of the partner exchange.';
+COMMENT ON COLUMN partner_accounts.partner_master_sig
+ IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS by the partner master public key';
+COMMENT ON COLUMN partner_accounts.last_seen
+ IS 'Last time we saw this account as being active at the partner exchange. Used to select the most recent entry, and to detect when we should check again.';
+
+
+---------------------------------------------------------------------------
+-- Stored procedures
+---------------------------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION exchange_do_withdraw(
+ IN cs_nonce BYTEA,
+ IN amount_val INT8,
+ IN amount_frac INT4,
+ IN h_denom_pub BYTEA,
+ IN rpub BYTEA,
+ IN reserve_sig BYTEA,
+ IN h_coin_envelope BYTEA,
+ IN denom_sig BYTEA,
+ IN now INT8,
+ IN min_reserve_gc INT8,
+ OUT reserve_found BOOLEAN,
+ OUT balance_ok BOOLEAN,
+ OUT kycok BOOLEAN,
+ OUT account_uuid INT8,
+ OUT ruuid INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ reserve_gc INT8;
+DECLARE
+ denom_serial INT8;
+DECLARE
+ reserve_val INT8;
+DECLARE
+ reserve_frac INT4;
+BEGIN
+-- Shards: reserves by reserve_pub (SELECT)
+-- reserves_out (INSERT, with CONFLICT detection) by wih
+-- reserves by reserve_pub (UPDATE)
+-- reserves_in by reserve_pub (SELECT)
+-- wire_targets by wire_target_h_payto
+
+SELECT denominations_serial
+ INTO denom_serial
+ FROM denominations
+ WHERE denom_pub_hash=h_denom_pub;
+
+IF NOT FOUND
+THEN
+ -- denomination unknown, should be impossible!
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=0;
+ ASSERT false, 'denomination unknown';
+ RETURN;
+END IF;
+
+
+SELECT
+ current_balance_val
+ ,current_balance_frac
+ ,gc_date
+ ,reserve_uuid
+ INTO
+ reserve_val
+ ,reserve_frac
+ ,reserve_gc
+ ,ruuid
+ FROM reserves
+ WHERE reserves.reserve_pub=rpub;
+
+IF NOT FOUND
+THEN
+ -- reserve unknown
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=2;
+ RETURN;
+END IF;
+
+-- We optimistically insert, and then on conflict declare
+-- the query successful due to idempotency.
+INSERT INTO reserves_out
+ (h_blind_ev
+ ,denominations_serial
+ ,denom_sig
+ ,reserve_uuid
+ ,reserve_sig
+ ,execution_date
+ ,amount_with_fee_val
+ ,amount_with_fee_frac)
+VALUES
+ (h_coin_envelope
+ ,denom_serial
+ ,denom_sig
+ ,ruuid
+ ,reserve_sig
+ ,now
+ ,amount_val
+ ,amount_frac)
+ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- idempotent query, all constraints must be satisfied
+ reserve_found=TRUE;
+ balance_ok=TRUE;
+ kycok=TRUE;
+ account_uuid=0;
+ RETURN;
+END IF;
+
+-- Check reserve balance is sufficient.
+IF (reserve_val > amount_val)
+THEN
+ IF (reserve_frac >= amount_frac)
+ THEN
+ reserve_val=reserve_val - amount_val;
+ reserve_frac=reserve_frac - amount_frac;
+ ELSE
+ reserve_val=reserve_val - amount_val - 1;
+ reserve_frac=reserve_frac + 100000000 - amount_frac;
+ END IF;
+ELSE
+ IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac)
+ THEN
+ reserve_val=0;
+ reserve_frac=reserve_frac - amount_frac;
+ ELSE
+ reserve_found=TRUE;
+ balance_ok=FALSE;
+ kycok=FALSE; -- we do not really know or care
+ account_uuid=0;
+ RETURN;
+ END IF;
+END IF;
+
+-- Calculate new expiration dates.
+min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc);
+
+-- Update reserve balance.
+UPDATE reserves SET
+ gc_date=min_reserve_gc
+ ,current_balance_val=reserve_val
+ ,current_balance_frac=reserve_frac
+WHERE
+ reserves.reserve_pub=rpub;
+
+reserve_found=TRUE;
+balance_ok=TRUE;
+
+
+
+-- Special actions needed for a CS withdraw?
+IF NOT NULL cs_nonce
+THEN
+ -- Cache CS signature to prevent replays in the future
+ -- (and check if cached signature exists at the same time).
+ INSERT INTO cs_nonce_locks
+ (nonce
+ ,max_denomination_serial
+ ,op_hash)
+ VALUES
+ (cs_nonce
+ ,denom_serial
+ ,h_coin_envelope)
+ ON CONFLICT DO NOTHING;
+
+ IF NOT FOUND
+ THEN
+ -- See if the existing entry is identical.
+ SELECT 1
+ FROM cs_nonce_locks
+ WHERE nonce=cs_nonce
+ AND op_hash=h_coin_envelope;
+ IF NOT FOUND
+ THEN
+ reserve_found=FALSE;
+ balance_ok=FALSE;
+ kycok=FALSE;
+ account_uuid=0;
+ ruuid=1; -- FIXME: return error message more nicely!
+ ASSERT false, 'nonce reuse attempted by client';
+ END IF;
+ END IF;
+END IF;
+
+
+
+-- Obtain KYC status based on the last wire transfer into
+-- this reserve. FIXME: likely not adequate for reserves that got P2P transfers!
+SELECT
+ kyc_ok
+ ,wire_target_serial_id
+ INTO
+ kycok
+ ,account_uuid
+ FROM reserves_in
+ JOIN wire_targets ON (wire_source_h_payto = wire_target_h_payto)
+ WHERE reserve_pub=rpub
+ LIMIT 1; -- limit 1 should not be required (without p2p transfers)
+
+
+END $$;
+
+COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA, BYTEA, BYTEA, BYTEA, INT8, INT8)
+ IS 'Checks whether the reserve has sufficient balance for a withdraw operation (or the request is repeated and was previously approved) and if so updates the database with the result';
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_withdraw_limit_check(
+ IN ruuid INT8,
+ IN start_time INT8,
+ IN upper_limit_val INT8,
+ IN upper_limit_frac INT4,
+ OUT below_limit BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ total_val INT8;
+DECLARE
+ total_frac INT8; -- INT4 could overflow during accumulation!
+BEGIN
+-- NOTE: Read-only, but crosses shards.
+-- Shards: reserves by reserve_pub
+-- reserves_out by reserve_uuid -- crosses shards!!
+
+
+SELECT
+ SUM(amount_with_fee_val) -- overflow here is not plausible
+ ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+ INTO
+ total_val
+ ,total_frac
+ FROM reserves_out
+ WHERE reserve_uuid=ruuid
+ AND execution_date > start_time;
+
+-- normalize result
+total_val = total_val + total_frac / 100000000;
+total_frac = total_frac % 100000000;
+
+-- compare to threshold
+below_limit = (total_val < upper_limit_val) OR
+ ( (total_val = upper_limit_val) AND
+ (total_frac <= upper_limit_frac) );
+END $$;
+
+COMMENT ON FUNCTION exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4)
+ IS 'Check whether the withdrawals from the given reserve since the given time are below the given threshold';
+
+
+-- NOTE: experiment, currently dead, see postgres_Start_deferred_wire_out;
+-- now done inline. FIXME: Remove code here once inline version is confirmed working nicely!
+CREATE OR REPLACE PROCEDURE defer_wire_out()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+IF EXISTS (
+ SELECT 1
+ FROM information_Schema.constraint_column_usage
+ WHERE table_name='wire_out'
+ AND constraint_name='wire_out_ref')
+THEN
+ SET CONSTRAINTS wire_out_ref DEFERRED;
+END IF;
+
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_deposit(
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_h_contract_terms BYTEA,
+ IN in_wire_salt BYTEA,
+ IN in_wallet_timestamp INT8,
+ IN in_exchange_timestamp INT8,
+ IN in_refund_deadline INT8,
+ IN in_wire_deadline INT8,
+ IN in_merchant_pub BYTEA,
+ IN in_receiver_wire_account VARCHAR,
+ IN in_h_payto BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_pub BYTEA,
+ IN in_coin_sig BYTEA,
+ IN in_shard INT8,
+ IN in_extension_blocked BOOLEAN,
+ IN in_extension_details VARCHAR,
+ OUT out_exchange_timestamp INT8,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ wtsi INT8; -- wire target serial id
+DECLARE
+ xdi INT8; -- eXstension details serial id
+BEGIN
+-- Shards: INSERT extension_details (by extension_details_serial_id)
+-- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
+-- INSERT deposits (by coin_pub, shard), ON CONFLICT DO NOTHING;
+-- UPDATE known_coins (by coin_pub)
+
+IF NOT NULL in_extension_details
+THEN
+ INSERT INTO extension_details
+ (extension_options)
+ VALUES
+ (in_extension_details)
+ RETURNING extension_details_serial_id INTO xdi;
+ELSE
+ xdi=NULL;
+END IF;
+
+
+INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,payto_uri)
+ VALUES
+ (in_h_payto
+ ,in_receiver_wire_account)
+ON CONFLICT DO NOTHING -- for CONFLICT ON (wire_target_h_payto)
+ RETURNING wire_target_serial_id INTO wtsi;
+
+IF NOT FOUND
+THEN
+ SELECT wire_target_serial_id
+ INTO wtsi
+ FROM wire_targets
+ WHERE wire_target_h_payto=in_h_payto;
+END IF;
+
+
+INSERT INTO deposits
+ (shard
+ ,coin_pub
+ ,known_coin_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,wallet_timestamp
+ ,exchange_timestamp
+ ,refund_deadline
+ ,wire_deadline
+ ,merchant_pub
+ ,h_contract_terms
+ ,coin_sig
+ ,wire_salt
+ ,wire_target_h_payto
+ ,extension_blocked
+ ,extension_details_serial_id
+ )
+ VALUES
+ (in_shard
+ ,in_coin_pub
+ ,in_known_coin_id
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac
+ ,in_wallet_timestamp
+ ,in_exchange_timestamp
+ ,in_refund_deadline
+ ,in_wire_deadline
+ ,in_merchant_pub
+ ,in_h_contract_terms
+ ,in_coin_sig
+ ,in_wire_salt
+ ,in_h_payto
+ ,in_extension_blocked
+ ,xdi)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ -- Note that by checking 'coin_sig', we implicitly check
+ -- identity over everything that the signature covers.
+ -- We do select over merchant_pub and wire_target_h_payto
+ -- primarily here to maximally use the existing index.
+ SELECT
+ exchange_timestamp
+ INTO
+ out_exchange_timestamp
+ FROM deposits
+ WHERE shard=in_shard
+ AND merchant_pub=in_merchant_pub
+ AND wire_target_h_payto=in_h_payto
+ AND coin_pub=in_coin_pub
+ AND coin_sig=in_coin_sig;
+
+ IF NOT FOUND
+ THEN
+ -- Deposit exists, but with differences. Not allowed.
+ out_balance_ok=FALSE;
+ out_conflict=TRUE;
+ RETURN;
+ END IF;
+
+ -- Idempotent request known, return success.
+ out_balance_ok=TRUE;
+ out_conflict=FALSE;
+
+ RETURN;
+END IF;
+
+
+out_exchange_timestamp=in_exchange_timestamp;
+
+-- Check and update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac-in_amount_with_fee_frac
+ + CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val-in_amount_with_fee_val
+ - CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub
+ AND ( (remaining_val > in_amount_with_fee_val) OR
+ ( (remaining_frac >= in_amount_with_fee_frac) AND
+ (remaining_val >= in_amount_with_fee_val) ) );
+
+IF NOT FOUND
+THEN
+ -- Insufficient balance.
+ out_balance_ok=FALSE;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+
+-- Everything fine, return success!
+out_balance_ok=TRUE;
+out_conflict=FALSE;
+
+END $$;
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_melt(
+ IN in_cs_rms BYTEA,
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_rc BYTEA,
+ IN in_old_coin_pub BYTEA,
+ IN in_old_coin_sig BYTEA,
+ IN in_known_coin_id INT8, -- not used, but that's OK
+ IN in_noreveal_index INT4,
+ IN in_zombie_required BOOLEAN,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_zombie_bad BOOLEAN,
+ OUT out_noreveal_index INT4)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ denom_max INT8;
+BEGIN
+-- Shards: INSERT refresh_commitments (by rc)
+-- (rare:) SELECT refresh_commitments (by old_coin_pub) -- crosses shards!
+-- (rare:) SEELCT refresh_revealed_coins (by melt_serial_id)
+-- (rare:) PERFORM recoup_refresh (by rrc_serial) -- crosses shards!
+-- UPDATE known_coins (by coin_pub)
+
+INSERT INTO refresh_commitments
+ (rc
+ ,old_coin_pub
+ ,old_coin_sig
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,noreveal_index
+ )
+ VALUES
+ (in_rc
+ ,in_old_coin_pub
+ ,in_old_coin_sig
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac
+ ,in_noreveal_index)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ out_noreveal_index=-1;
+ SELECT
+ noreveal_index
+ INTO
+ out_noreveal_index
+ FROM refresh_commitments
+ WHERE rc=in_rc;
+ out_balance_ok=FOUND;
+ out_zombie_bad=FALSE; -- zombie is OK
+ RETURN;
+END IF;
+
+
+IF in_zombie_required
+THEN
+ -- Check if this coin was part of a refresh
+ -- operation that was subsequently involved
+ -- in a recoup operation. We begin by all
+ -- refresh operations our coin was involved
+ -- with, then find all associated reveal
+ -- operations, and then see if any of these
+ -- reveal operations was involved in a recoup.
+ PERFORM
+ FROM recoup_refresh
+ WHERE rrc_serial IN
+ (SELECT rrc_serial
+ FROM refresh_revealed_coins
+ WHERE melt_serial_id IN
+ (SELECT melt_serial_id
+ FROM refresh_commitments
+ WHERE old_coin_pub=in_old_coin_pub));
+ IF NOT FOUND
+ THEN
+ out_zombie_bad=TRUE;
+ out_balance_ok=FALSE;
+ RETURN;
+ END IF;
+END IF;
+
+out_zombie_bad=FALSE; -- zombie is OK
+
+
+-- Check and update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac-in_amount_with_fee_frac
+ + CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val-in_amount_with_fee_val
+ - CASE
+ WHEN remaining_frac < in_amount_with_fee_frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub
+ AND ( (remaining_val > in_amount_with_fee_val) OR
+ ( (remaining_frac >= in_amount_with_fee_frac) AND
+ (remaining_val >= in_amount_with_fee_val) ) );
+
+IF NOT FOUND
+THEN
+ -- Insufficient balance.
+ out_noreveal_index=-1;
+ out_balance_ok=FALSE;
+ RETURN;
+END IF;
+
+
+
+-- Special actions needed for a CS melt?
+IF NOT NULL in_cs_rms
+THEN
+ -- Get maximum denominations serial value in
+ -- existence, this will determine how long the
+ -- nonce will be locked.
+ SELECT
+ denominations_serial
+ INTO
+ denom_max
+ FROM denominations
+ ORDER BY denominations_serial DESC
+ LIMIT 1;
+
+ -- Cache CS signature to prevent replays in the future
+ -- (and check if cached signature exists at the same time).
+ INSERT INTO cs_nonce_locks
+ (nonce
+ ,max_denomination_serial
+ ,op_hash)
+ VALUES
+ (cs_rms
+ ,denom_serial
+ ,in_rc)
+ ON CONFLICT DO NOTHING;
+
+ IF NOT FOUND
+ THEN
+ -- Record exists, make sure it is the same
+ SELECT 1
+ FROM cs_nonce_locks
+ WHERE nonce=cs_rms
+ AND op_hash=in_rc;
+
+ IF NOT FOUND
+ THEN
+ -- Nonce reuse detected
+ out_balance_ok=FALSE;
+ out_zombie_bad=FALSE;
+ out_noreveal_index=42; -- FIXME: return error message more nicely!
+ ASSERT false, 'nonce reuse attempted by client';
+ END IF;
+ END IF;
+END IF;
+
+-- Everything fine, return success!
+out_balance_ok=TRUE;
+out_noreveal_index=in_noreveal_index;
+
+END $$;
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_refund(
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_amount_val INT8,
+ IN in_amount_frac INT4,
+ IN in_deposit_fee_val INT8,
+ IN in_deposit_fee_frac INT4,
+ IN in_h_contract_terms BYTEA,
+ IN in_rtransaction_id INT8,
+ IN in_deposit_shard INT8,
+ IN in_known_coin_id INT8,
+ IN in_coin_pub BYTEA,
+ IN in_merchant_pub BYTEA,
+ IN in_merchant_sig BYTEA,
+ OUT out_not_found BOOLEAN,
+ OUT out_refund_ok BOOLEAN,
+ OUT out_gone BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ dsi INT8; -- ID of deposit being refunded
+DECLARE
+ tmp_val INT8; -- total amount refunded
+DECLARE
+ tmp_frac INT8; -- total amount refunded
+DECLARE
+ deposit_val INT8; -- amount that was originally deposited
+DECLARE
+ deposit_frac INT8; -- amount that was originally deposited
+BEGIN
+-- Shards: SELECT deposits (coin_pub, shard, h_contract_terms, merchant_pub)
+-- INSERT refunds (by coin_pub, rtransaction_id) ON CONFLICT DO NOTHING
+-- SELECT refunds (by coin_pub)
+-- UPDATE known_coins (by coin_pub)
+
+SELECT
+ deposit_serial_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ ,done
+INTO
+ dsi
+ ,deposit_val
+ ,deposit_frac
+ ,out_gone
+FROM deposits
+ WHERE coin_pub=in_coin_pub
+ AND shard=in_deposit_shard
+ AND merchant_pub=in_merchant_pub
+ AND h_contract_terms=in_h_contract_terms;
+
+IF NOT FOUND
+THEN
+ -- No matching deposit found!
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=TRUE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+
+INSERT INTO refunds
+ (deposit_serial_id
+ ,coin_pub
+ ,merchant_sig
+ ,rtransaction_id
+ ,amount_with_fee_val
+ ,amount_with_fee_frac
+ )
+ VALUES
+ (dsi
+ ,in_coin_pub
+ ,in_merchant_sig
+ ,in_rtransaction_id
+ ,in_amount_with_fee_val
+ ,in_amount_with_fee_frac)
+ ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+ -- Idempotency check: see if an identical record exists.
+ -- Note that by checking 'coin_sig', we implicitly check
+ -- identity over everything that the signature covers.
+ -- We do select over merchant_pub and h_contract_terms
+ -- primarily here to maximally use the existing index.
+ PERFORM
+ FROM refunds
+ WHERE coin_pub=in_coin_pub
+ AND deposit_serial_id=dsi
+ AND rtransaction_id=in_rtransaction_id
+ AND amount_with_fee_val=in_amount_with_fee_val
+ AND amount_with_fee_frac=in_amount_with_fee_frac;
+
+ IF NOT FOUND
+ THEN
+ -- Deposit exists, but have conflicting refund.
+ out_refund_ok=FALSE;
+ out_conflict=TRUE;
+ out_not_found=FALSE;
+ RETURN;
+ END IF;
+
+ -- Idempotent request known, return success.
+ out_refund_ok=TRUE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+
+IF out_gone
+THEN
+ -- money already sent to the merchant. Tough luck.
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+
+-- Check refund balance invariant.
+SELECT
+ SUM(amount_with_fee_val) -- overflow here is not plausible
+ ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+ INTO
+ tmp_val
+ ,tmp_frac
+ FROM refunds
+ WHERE coin_pub=in_coin_pub
+ AND deposit_serial_id=dsi;
+IF tmp_val IS NULL
+THEN
+ RAISE NOTICE 'failed to sum up existing refunds';
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+
+-- Normalize result before continuing
+tmp_val = tmp_val + tmp_frac / 100000000;
+tmp_frac = tmp_frac % 100000000;
+
+-- Actually check if the deposits are sufficient for the refund. Verbosely. ;-)
+IF (tmp_val < deposit_val)
+THEN
+ out_refund_ok=TRUE;
+ELSE
+ IF (tmp_val = deposit_val) AND (tmp_frac <= deposit_frac)
+ THEN
+ out_refund_ok=TRUE;
+ ELSE
+ out_refund_ok=FALSE;
+ END IF;
+END IF;
+
+IF (tmp_val = deposit_val) AND (tmp_frac = deposit_frac)
+THEN
+ -- Refunds have reached the full value of the original
+ -- deposit. Also refund the deposit fee.
+ in_amount_frac = in_amount_frac + in_deposit_fee_frac;
+ in_amount_val = in_amount_val + in_deposit_fee_val;
+
+ -- Normalize result before continuing
+ in_amount_val = in_amount_val + in_amount_frac / 100000000;
+ in_amount_frac = in_amount_frac % 100000000;
+END IF;
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac+in_amount_frac
+ - CASE
+ WHEN remaining_frac+in_amount_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val+in_amount_val
+ + CASE
+ WHEN remaining_frac+in_amount_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub;
+
+
+out_conflict=FALSE;
+out_not_found=FALSE;
+
+END $$;
+
+-- COMMENT ON FUNCTION exchange_do_refund(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
+-- IS 'Executes a refund operation, checking that the corresponding deposit was sufficient to cover the refunded amount';
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_out_serial_id INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_reserve_gc INT8,
+ IN in_reserve_expiration INT8,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ tmp_val INT8; -- amount recouped
+DECLARE
+ tmp_frac INT8; -- amount recouped
+BEGIN
+-- Shards: SELECT known_coins (by coin_pub)
+-- SELECT recoup (by coin_pub)
+-- UPDATE known_coins (by coin_pub)
+-- UPDATE reserves (by reserve_pub)
+-- INSERT recoup (by coin_pub)
+
+out_internal_failure=FALSE;
+
+
+-- Check remaining balance of the coin.
+SELECT
+ remaining_frac
+ ,remaining_val
+ INTO
+ tmp_frac
+ ,tmp_val
+FROM known_coins
+ WHERE coin_pub=in_coin_pub;
+
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+
+IF tmp_val + tmp_frac = 0
+THEN
+ -- Check for idempotency
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM recoup
+ WHERE coin_pub=in_coin_pub;
+
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=0
+ ,remaining_val=0
+ WHERE coin_pub=in_coin_pub;
+
+
+-- Credit the reserve and update reserve timers.
+UPDATE reserves
+ SET
+ current_balance_frac=current_balance_frac+tmp_frac
+ - CASE
+ WHEN current_balance_frac+tmp_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ current_balance_val=current_balance_val+tmp_val
+ + CASE
+ WHEN current_balance_frac+tmp_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ gc_date=GREATEST(gc_date, in_reserve_gc),
+ expiration_date=GREATEST(expiration_date, in_reserve_expiration)
+ WHERE reserve_pub=in_reserve_pub;
+
+
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase reserve balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+
+
+INSERT INTO recoup
+ (coin_pub
+ ,coin_sig
+ ,coin_blind
+ ,amount_val
+ ,amount_frac
+ ,recoup_timestamp
+ ,reserve_out_serial_id
+ )
+VALUES
+ (in_coin_pub
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp_val
+ ,tmp_frac
+ ,in_recoup_timestamp
+ ,in_reserve_out_serial_id);
+
+-- Normal end, everything is fine.
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
+
+END $$;
+
+-- COMMENT ON FUNCTION exchange_do_recoup_to_reserve(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
+-- IS 'Executes a recoup of a coin that was withdrawn from a reserve';
+
+
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin(
+ IN in_old_coin_pub BYTEA,
+ IN in_rrc_serial INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ tmp_val INT8; -- amount recouped
+DECLARE
+ tmp_frac INT8; -- amount recouped
+BEGIN
+
+-- Shards: UPDATE known_coins (by coin_pub)
+-- SELECT recoup_refresh (by coin_pub)
+-- UPDATE known_coins (by coin_pub)
+-- INSERT recoup_refresh (by coin_pub)
+
+
+out_internal_failure=FALSE;
+
+
+-- Check remaining balance of the coin.
+SELECT
+ remaining_frac
+ ,remaining_val
+ INTO
+ tmp_frac
+ ,tmp_val
+FROM known_coins
+ WHERE coin_pub=in_coin_pub;
+
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+
+IF tmp_val + tmp_frac = 0
+THEN
+ -- Check for idempotency
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM recoup_refresh
+ WHERE coin_pub=in_coin_pub;
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+
+-- Update balance of the coin.
+UPDATE known_coins
+ SET
+ remaining_frac=0
+ ,remaining_val=0
+ WHERE coin_pub=in_coin_pub;
+
+
+-- Credit the old coin.
+UPDATE known_coins
+ SET
+ remaining_frac=remaining_frac+tmp_frac
+ - CASE
+ WHEN remaining_frac+tmp_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining_val=remaining_val+tmp_val
+ + CASE
+ WHEN remaining_frac+tmp_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub;
+
+
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase old coin balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+
+
+INSERT INTO recoup_refresh
+ (coin_pub
+ ,known_coin_id
+ ,coin_sig
+ ,coin_blind
+ ,amount_val
+ ,amount_frac
+ ,recoup_timestamp
+ ,rrc_serial
+ )
+VALUES
+ (in_coin_pub
+ ,in_known_coin_id
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp_val
+ ,tmp_frac
+ ,in_recoup_timestamp
+ ,in_rrc_serial);
+
+-- Normal end, everything is fine.
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
+
+END $$;
+
+
+-- COMMENT ON FUNCTION exchange_do_recoup_to_coin(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
+-- IS 'Executes a recoup-refresh of a coin that was obtained from a refresh-reveal process';
+
+
+
+CREATE OR REPLACE PROCEDURE exchange_do_gc(
+ IN in_ancient_date INT8,
+ IN in_now INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ reserve_uuid_min INT8; -- minimum reserve UUID still alive
+DECLARE
+ melt_min INT8; -- minimum melt still alive
+DECLARE
+ coin_min INT8; -- minimum known_coin still alive
+DECLARE
+ deposit_min INT8; -- minimum deposit still alive
+DECLARE
+ reserve_out_min INT8; -- minimum reserve_out still alive
+DECLARE
+ denom_min INT8; -- minimum denomination still alive
+BEGIN
+
+DELETE FROM prewire
+ WHERE finished=TRUE;
+
+DELETE FROM wire_fee
+ WHERE end_date < in_ancient_date;
+
+-- TODO: use closing fee as threshold?
+DELETE FROM reserves
+ WHERE gc_date < in_now
+ AND current_balance_val = 0
+ AND current_balance_frac = 0;
+
+SELECT
+ reserve_out_serial_id
+ INTO
+ reserve_out_min
+ FROM reserves_out
+ ORDER BY reserve_out_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM recoup
+ WHERE reserve_out_serial_id < reserve_out_min;
+-- FIXME: recoup_refresh lacks GC!
+
+SELECT
+ reserve_uuid
+ INTO
+ reserve_uuid_min
+ FROM reserves
+ ORDER BY reserve_uuid ASC
+ LIMIT 1;
+
+DELETE FROM reserves_out
+ WHERE reserve_uuid < reserve_uuid_min;
+
+-- FIXME: this query will be horribly slow;
+-- need to find another way to formulate it...
+DELETE FROM denominations
+ WHERE expire_legal < in_now
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM reserves_out)
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
+ FROM recoup))
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
+ FROM recoup_refresh));
+
+SELECT
+ melt_serial_id
+ INTO
+ melt_min
+ FROM refresh_commitments
+ ORDER BY melt_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM refresh_revealed_coins
+ WHERE melt_serial_id < melt_min;
+
+DELETE FROM refresh_transfer_keys
+ WHERE melt_serial_id < melt_min;
+
+SELECT
+ known_coin_id
+ INTO
+ coin_min
+ FROM known_coins
+ ORDER BY known_coin_id ASC
+ LIMIT 1;
+
+DELETE FROM deposits
+ WHERE known_coin_id < coin_min;
+
+SELECT
+ deposit_serial_id
+ INTO
+ deposit_min
+ FROM deposits
+ ORDER BY deposit_serial_id ASC
+ LIMIT 1;
+
+DELETE FROM refunds
+ WHERE deposit_serial_id < deposit_min;
+
+DELETE FROM aggregation_tracking
+ WHERE deposit_serial_id < deposit_min;
+
+SELECT
+ denominations_serial
+ INTO
+ denom_min
+ FROM denominations
+ ORDER BY denominations_serial ASC
+ LIMIT 1;
+
+DELETE FROM cs_nonce_locks
+ WHERE max_denomination_serial <= denom_min;
+
+END $$;
+
+
+
+
+
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_purse_deposit(
+ IN in_purse_pub BYTEA,
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_coin_pub BYTEA,
+ IN in_coin_sig BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_purse_merge(
+ IN in_purse_pub BYTEA,
+ IN in_merge_sig BYTEA,
+ IN in_merge_timestamp INT8,
+ IN in_partner_url VARCHAR,
+ IN in_reserve_pub BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_account_merge(
+ IN in_purse_pub BYTEA,
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_history_request(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ IN in_request_timestamp INT8,
+ IN in_history_fee_val INT8,
+ IN in_history_fee_frac INT4,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_close_request(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ OUT out_final_balance_val INT8,
+ OUT out_final_balance_frac INT4,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+-------------------------------------------------------------
+-- THE END
+-------------------------------------------------------------
+
+-- Complete transaction
+COMMIT;