merchant

merchant-0036-init.sql.fragment (81615B)

---

 --
 -- This file is part of TALER
 -- Copyright (C) 2026 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/>
 
 -- @file merchant-0036-init.sql.fragment
 -- @brief Create initial set of per-instance tables and indices
 -- @author Christian Grothoff
 
 CREATE PROCEDURE merchant.merchant_0036_init(s TEXT)
   LANGUAGE plpgsql
   AS $OUTER$
 BEGIN
   -- All object creation below uses unqualified names that resolve against
   -- the per-instance schema `s`.  Cross-schema references (the merchant.*
   -- types, the global merchant.* tables and the public.* operator classes)
   -- stay explicitly qualified.  Tables are created in FK-dependency order so
   -- that foreign keys can be declared inline with the CREATE TABLE.
   EXECUTE format('SET LOCAL search_path TO %I', s);
 
   -- ===================================================================
   -- Tier 0: tables without intra-schema foreign keys.
   -- ===================================================================
 
   CREATE TABLE merchant_accounts (
     account_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     h_wire BYTEA NOT NULL UNIQUE,
     salt BYTEA NOT NULL,
     credit_facade_url TEXT,
     credit_facade_credentials JSONB,
     last_bank_serial INT8 DEFAULT 0 NOT NULL,
     payto_uri TEXT NOT NULL UNIQUE,
     active BOOLEAN NOT NULL,
     extra_wire_subject_metadata TEXT,
     CONSTRAINT merchant_accounts_h_wire_check CHECK ((LENGTH(h_wire) = 64)),
     CONSTRAINT merchant_accounts_salt_check CHECK ((LENGTH(salt) = 16))
   );
   COMMENT ON TABLE merchant_accounts IS 'bank accounts of the instances';
   COMMENT ON COLUMN merchant_accounts.account_serial IS 'Unique identifier for this account, used to reference this account from other tables';
   COMMENT ON COLUMN merchant_accounts.h_wire IS 'salted hash of payto_uri';
   COMMENT ON COLUMN merchant_accounts.salt IS 'salt used when hashing payto_uri into h_wire';
   COMMENT ON COLUMN merchant_accounts.credit_facade_url IS 'Base URL of a facade where the merchant can inquire about incoming bank transactions into this account';
   COMMENT ON COLUMN merchant_accounts.credit_facade_credentials IS 'JSON with credentials needed to access the credit facade';
   COMMENT ON COLUMN merchant_accounts.last_bank_serial IS 'Serial number of the bank of the last transaction we successfully imported';
   COMMENT ON COLUMN merchant_accounts.payto_uri IS 'payto URI of a merchant bank account';
   COMMENT ON COLUMN merchant_accounts.active IS 'true if we actively use this bank account, false if it is just kept around for older contracts to refer to';
   COMMENT ON COLUMN merchant_accounts.extra_wire_subject_metadata IS 'Additional meta data that the exchange should include in wire transfer subjects made to this account on behalf of this instance';
 
   CREATE TABLE merchant_categories (
     category_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     category_name TEXT NOT NULL UNIQUE,
     category_name_i18n JSONB NOT NULL
   );
   COMMENT ON TABLE merchant_categories IS 'product categories (with translations) to group products from inventory (primarily for the point-of-sale app)';
   COMMENT ON COLUMN merchant_categories.category_name IS 'name of the category';
   COMMENT ON COLUMN merchant_categories.category_name_i18n IS 'JSON with translations of the category name';
   CREATE INDEX trgm_idx_categories_by_name
     ON merchant_categories USING gin (lower(category_name) public.gin_trgm_ops);
 
   CREATE TABLE merchant_contract_terms (
     order_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     order_id TEXT NOT NULL UNIQUE,
     contract_terms JSONB NOT NULL,
     wallet_data TEXT,
     h_contract_terms BYTEA NOT NULL UNIQUE,
     creation_time INT8 NOT NULL,
     pay_deadline INT8 NOT NULL,
     refund_deadline INT8 NOT NULL,
     paid BOOLEAN DEFAULT FALSE NOT NULL,
     wired BOOLEAN DEFAULT FALSE NOT NULL,
     fulfillment_url TEXT,
     session_id TEXT DEFAULT ''::text NOT NULL,
     pos_key TEXT,
     pos_algorithm INT4 DEFAULT 0 NOT NULL,
     claim_token BYTEA NOT NULL,
     choice_index INT2,
     CONSTRAINT merchant_contract_terms_claim_token_check CHECK ((LENGTH(claim_token) = 16)),
     CONSTRAINT merchant_contract_terms_h_contract_terms_check CHECK ((LENGTH(h_contract_terms) = 64))
   );
   COMMENT ON TABLE merchant_contract_terms IS 'Contracts are orders that have been claimed by a wallet';
   COMMENT ON COLUMN merchant_contract_terms.order_id IS 'Not a foreign key into merchant_orders because paid contracts persist after expiration';
   COMMENT ON COLUMN merchant_contract_terms.contract_terms IS 'These contract terms include the wallet nonce';
   COMMENT ON COLUMN merchant_contract_terms.wallet_data IS 'Data provided by the wallet when paying for the contract (subcontract selection, blinded tokens, etc.)';
   COMMENT ON COLUMN merchant_contract_terms.h_contract_terms IS 'Hash over contract_terms';
   COMMENT ON COLUMN merchant_contract_terms.creation_time IS 'time at which the order was originally created';
   COMMENT ON COLUMN merchant_contract_terms.pay_deadline IS 'How long is the offer valid. After this time, the order can be garbage collected';
   COMMENT ON COLUMN merchant_contract_terms.refund_deadline IS 'By what times do refunds have to be approved (useful to reject refund requests)';
   COMMENT ON COLUMN merchant_contract_terms.paid IS 'true implies the customer paid for this contract; order should be DELETEd from merchant_orders once paid is set to release merchant_order_locks; paid remains true even if the payment was later refunded';
   COMMENT ON COLUMN merchant_contract_terms.wired IS 'true implies the exchange wired us the full amount for all non-refunded payments under this contract';
   COMMENT ON COLUMN merchant_contract_terms.fulfillment_url IS 'also included in contract_terms, but we need it here to SELECT on it during repurchase detection; can be NULL if the contract has no fulfillment URL';
   COMMENT ON COLUMN merchant_contract_terms.session_id IS 'last session_id from we confirmed the paying client to use, empty string for none';
   COMMENT ON COLUMN merchant_contract_terms.pos_key IS 'enconded based key which is used for the verification of payment';
   COMMENT ON COLUMN merchant_contract_terms.pos_algorithm IS 'specifies the algorithm used to compute the verification code (usually some OTP variant)';
   COMMENT ON COLUMN merchant_contract_terms.claim_token IS 'Token optionally used to access the status of the order. All zeros (not NULL) if not used';
   COMMENT ON COLUMN merchant_contract_terms.choice_index IS 'Index of selected choice. Refers to the `choices` array in the contract terms. NULL for contracts without choices.';
   CREATE INDEX merchant_contract_terms_by_expiration
     ON merchant_contract_terms (paid, pay_deadline);
   COMMENT ON INDEX merchant_contract_terms_by_expiration IS 'for unlock_contracts';
   CREATE INDEX merchant_contract_terms_by_merchant_and_expiration
     ON merchant_contract_terms (pay_deadline);
   COMMENT ON INDEX merchant_contract_terms_by_merchant_and_expiration IS 'for delete_contract_terms';
   CREATE INDEX merchant_contract_terms_by_merchant_and_payment
     ON merchant_contract_terms (paid);
   CREATE INDEX merchant_contract_terms_by_merchant_and_session
     ON merchant_contract_terms (session_id);
   CREATE INDEX merchant_contract_terms_by_merchant_session_and_fulfillment
     ON merchant_contract_terms (fulfillment_url, session_id);
   CREATE INDEX trgm_idx_contract_summaries
     ON merchant_contract_terms USING gin (lower((contract_terms ->> 'summary'::text)) public.gin_trgm_ops);
 
   CREATE TABLE merchant_custom_units (
     unit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     unit TEXT NOT NULL UNIQUE,
     unit_name_long TEXT NOT NULL,
     unit_name_short TEXT NOT NULL,
     unit_name_long_i18n BYTEA DEFAULT convert_to('{}'::text, 'UTF8'::name) NOT NULL,
     unit_name_short_i18n BYTEA DEFAULT convert_to('{}'::text, 'UTF8'::name) NOT NULL,
     unit_allow_fraction BOOLEAN DEFAULT FALSE NOT NULL,
     unit_precision_level INT4 DEFAULT 0 NOT NULL,
     unit_active BOOLEAN DEFAULT TRUE NOT NULL,
     CONSTRAINT merchant_custom_units_unit_precision_level_check
       CHECK (((unit_precision_level >= 0) AND (unit_precision_level <= 6)))
   );
   COMMENT ON TABLE merchant_custom_units IS 'Per-instance custom measurement units.';
   COMMENT ON COLUMN merchant_custom_units.unit IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_name_long IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_name_short IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_name_long_i18n IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_name_short_i18n IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_allow_fraction IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_precision_level IS 'FIXME';
   COMMENT ON COLUMN merchant_custom_units.unit_active IS 'FIXME';
 
   CREATE TABLE merchant_donau_instances (
     donau_instances_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     donau_url TEXT NOT NULL,
     charity_name TEXT NOT NULL,
     charity_id INT8 NOT NULL,
     charity_max_per_year merchant.taler_amount_currency NOT NULL,
     charity_receipts_to_date merchant.taler_amount_currency NOT NULL,
     current_year INT8 NOT NULL,
     UNIQUE (donau_url, charity_id)
   );
   COMMENT ON TABLE merchant_donau_instances IS 'Here we store information about individual Donau instances, including details about associated charities and donation limits';
   COMMENT ON COLUMN merchant_donau_instances.donau_instances_serial IS 'Unique serial identifier for each Donau instance';
   COMMENT ON COLUMN merchant_donau_instances.donau_url IS 'The URL associated with the Donau system for this instance';
   COMMENT ON COLUMN merchant_donau_instances.charity_name IS 'Name of the charity (for humans)';
   COMMENT ON COLUMN merchant_donau_instances.charity_id IS 'The unique identifier for the charity organization linked to this Donau instance';
   COMMENT ON COLUMN merchant_donau_instances.charity_max_per_year IS 'Maximum allowable donation amount per year for the charity associated with this instance, stored in taler_amount_currency';
   COMMENT ON COLUMN merchant_donau_instances.charity_receipts_to_date IS 'The total amount of donations received to date for this instance, stored in taler_amount_currency';
   COMMENT ON COLUMN merchant_donau_instances.current_year IS 'The current year for tracking donations for this instance, stored as an 8-byte integer';
 
   CREATE TABLE merchant_login_tokens (
     serial INT8 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     token BYTEA NOT NULL UNIQUE,
     creation_time INT8 NOT NULL,
     expiration_time INT8 NOT NULL,
     validity_scope INT4 NOT NULL,
     description TEXT NOT NULL,
     CONSTRAINT merchant_login_tokens_token_check CHECK ((LENGTH(token) = 32))
   );
   COMMENT ON TABLE merchant_login_tokens IS 'login tokens that have been created for the given instance';
   COMMENT ON COLUMN merchant_login_tokens.token IS 'binary value of the login token';
   COMMENT ON COLUMN merchant_login_tokens.creation_time IS 'time when the token was created; currently not used, potentially useful in the future for a forced logout of all tokens issued before a certain date';
   COMMENT ON COLUMN merchant_login_tokens.expiration_time IS 'determines when the token expires';
   COMMENT ON COLUMN merchant_login_tokens.validity_scope IS 'identifies the operations for which the token is valid';
   COMMENT ON COLUMN merchant_login_tokens.description IS 'Description of the login token';
   CREATE INDEX merchant_login_tokens_by_expiration
     ON merchant_login_tokens (expiration_time);
 
   CREATE TABLE merchant_money_pots (
     money_pot_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     money_pot_name TEXT NOT NULL UNIQUE,
     money_pot_description TEXT NOT NULL,
     pot_totals merchant.taler_amount_currency[]
       DEFAULT ARRAY[]::merchant.taler_amount_currency[] NOT NULL
   );
   COMMENT ON TABLE merchant_money_pots IS 'Accounting construct for tracking finances by groups such as net income, taxes, tips to be paid to staff, etc.';
   COMMENT ON COLUMN merchant_money_pots.money_pot_serial IS 'Unique identifier for the money pot';
   COMMENT ON COLUMN merchant_money_pots.money_pot_name IS 'Name for the money pot';
   COMMENT ON COLUMN merchant_money_pots.money_pot_description IS 'Human-readable description for the money pot';
   COMMENT ON COLUMN merchant_money_pots.pot_totals IS 'Total amounts in the pot';
 
   CREATE TABLE merchant_orders (
     order_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     order_id TEXT NOT NULL UNIQUE,
     claim_token BYTEA NOT NULL,
     h_post_data BYTEA NOT NULL,
     pay_deadline INT8 NOT NULL,
     creation_time INT8 NOT NULL,
     contract_terms JSONB NOT NULL,
     pos_key TEXT,
     pos_algorithm INT4 DEFAULT 0 NOT NULL,
     fulfillment_url TEXT,
     session_id TEXT DEFAULT ''::text NOT NULL,
     CONSTRAINT merchant_orders_claim_token_check CHECK ((LENGTH(claim_token) = 16)),
     CONSTRAINT merchant_orders_h_post_data_check CHECK ((LENGTH(h_post_data) = 64))
   );
   COMMENT ON TABLE merchant_orders IS 'Orders we offered to a customer, but that have not yet been claimed';
   COMMENT ON COLUMN merchant_orders.order_id IS 'Unique identifier for the order';
   COMMENT ON COLUMN merchant_orders.claim_token IS 'Token optionally used to authorize the wallet to claim the order. All zeros (not NULL) if not used';
   COMMENT ON COLUMN merchant_orders.h_post_data IS 'Hash of the POST request that created this order, for idempotency checks';
   COMMENT ON COLUMN merchant_orders.pay_deadline IS 'How long is the offer valid. After this time, the order can be garbage collected';
   COMMENT ON COLUMN merchant_orders.creation_time IS 'time at which the order was originally created';
   COMMENT ON COLUMN merchant_orders.contract_terms IS 'Claiming changes the contract_terms, hence we have no hash of the terms in this table';
   COMMENT ON COLUMN merchant_orders.pos_key IS 'encoded based key which is used for the verification of payment';
   COMMENT ON COLUMN merchant_orders.pos_algorithm IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
   COMMENT ON COLUMN merchant_orders.fulfillment_url IS 'URL where the wallet will redirect the user upon payment';
   COMMENT ON COLUMN merchant_orders.session_id IS 'session_id to which the payment will be bound';
   CREATE INDEX merchant_orders_by_creation_time
     ON merchant_orders (creation_time);
   CREATE INDEX merchant_orders_by_expiration
     ON merchant_orders (pay_deadline);
   CREATE INDEX merchant_orders_by_merchant_and_fullfilment_and_session
     ON merchant_orders (fulfillment_url, session_id);
   CREATE INDEX merchant_orders_by_merchant_and_session
     ON merchant_orders (session_id);
   CREATE INDEX trgm_idx_order_summaries
     ON merchant_orders USING gin (lower((contract_terms ->> 'summary'::text)) public.gin_trgm_ops);
 
   CREATE TABLE merchant_otp_devices (
     otp_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     otp_id TEXT NOT NULL UNIQUE,
     otp_description TEXT NOT NULL,
     otp_key TEXT,
     otp_algorithm INT4 DEFAULT 0 NOT NULL,
     otp_ctr INT8 DEFAULT 0 NOT NULL
   );
   COMMENT ON TABLE merchant_otp_devices IS 'OTP device owned by a merchant';
   COMMENT ON COLUMN merchant_otp_devices.otp_id IS 'slug identifying the OTP device in protocols';
   COMMENT ON COLUMN merchant_otp_devices.otp_description IS 'Human-readable OTP device description';
   COMMENT ON COLUMN merchant_otp_devices.otp_key IS 'A base64-encoded key of the point-of-sale. It will be use by the OTP device';
   COMMENT ON COLUMN merchant_otp_devices.otp_algorithm IS 'algorithm to used to generate the confirmation code. It is linked with the otp_key and otp_ctr';
   COMMENT ON COLUMN merchant_otp_devices.otp_ctr IS 'counter for counter-based OTP generators';
 
   CREATE TABLE merchant_reports (
     report_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     report_program_section TEXT NOT NULL,
     report_description TEXT NOT NULL,
     mime_type TEXT NOT NULL,
     report_token BYTEA NOT NULL,
     data_source TEXT NOT NULL,
     target_address TEXT NOT NULL,
     frequency INT8 NOT NULL,
     frequency_shift INT8 NOT NULL,
     next_transmission INT8 NOT NULL,
     last_error_code INT4,
     last_error_detail TEXT,
     one_shot_hidden BOOLEAN DEFAULT FALSE,
     CONSTRAINT merchant_reports_report_token_check CHECK ((LENGTH(report_token) = 32))
   );
   COMMENT ON TABLE merchant_reports IS 'Specifies where we should send periodic reports about instance activities';
   COMMENT ON COLUMN merchant_reports.report_serial IS 'Unique identifier for the report';
   COMMENT ON COLUMN merchant_reports.report_program_section IS 'Which helper program (configuration section) to use to transmit the report';
   COMMENT ON COLUMN merchant_reports.report_description IS 'FIXME';
   COMMENT ON COLUMN merchant_reports.mime_type IS 'Mime-type to request from the backend for the transmission';
   COMMENT ON COLUMN merchant_reports.report_token IS 'Token clients requesting the report must include in the /report request';
   COMMENT ON COLUMN merchant_reports.data_source IS 'Relative URL of the instance for a GET request to request data to send';
   COMMENT ON COLUMN merchant_reports.target_address IS 'Address to which the report should be sent';
   COMMENT ON COLUMN merchant_reports.frequency IS 'Relative time with the desired report frequency';
   COMMENT ON COLUMN merchant_reports.frequency_shift IS 'Relative time by which to offset the actual transmission from the frequency multiple';
   COMMENT ON COLUMN merchant_reports.next_transmission IS 'Absolute time at which we should do the next transmission';
   COMMENT ON COLUMN merchant_reports.last_error_code IS 'ErrorCode of the last attempted transmission, NULL on success';
   COMMENT ON COLUMN merchant_reports.last_error_detail IS 'Additional human-readable text explaining errors from the last transmission attempt (for diagnostics), NULL on success';
   COMMENT ON COLUMN merchant_reports.one_shot_hidden IS 'True for reports that are only supposed to trigger once and that should be hidden from the user interface as they are internally generated. Used for alerts, such as those generated when the KYC status changes.';
 
   CREATE TABLE merchant_product_groups (
     product_group_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     product_group_name TEXT NOT NULL UNIQUE,
     product_group_description TEXT NOT NULL
   );
   COMMENT ON TABLE merchant_product_groups IS 'Specifies a product group';
   COMMENT ON COLUMN merchant_product_groups.product_group_serial IS 'Unique identifier for the group';
   COMMENT ON COLUMN merchant_product_groups.product_group_name IS 'Name for the group';
   COMMENT ON COLUMN merchant_product_groups.product_group_description IS 'Human-readable description for the group';
 
   CREATE TABLE merchant_statistic_bucket_meta (
     bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     slug TEXT NOT NULL,
     description TEXT NOT NULL,
     stype merchant.statistic_type NOT NULL,
     ranges merchant.statistic_range[] NOT NULL,
     ages INT4[] NOT NULL,
     CONSTRAINT equal_array_LENGTH CHECK ((array_LENGTH(ranges, 1) = array_LENGTH(ages, 1))),
     UNIQUE (slug, stype)
   );
   COMMENT ON TABLE merchant_statistic_bucket_meta IS 'meta data about a statistic with events falling into buckets we are tracking';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.bmeta_serial_id IS 'unique identifier for this type of bucket statistic we are tracking';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.slug IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.description IS 'description of the statistic being tracked';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.stype IS 'statistic type, what kind of data is being tracked, amount or number';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.ranges IS 'size of the buckets that are being kept for this statistic';
   COMMENT ON COLUMN merchant_statistic_bucket_meta.ages IS 'determines how long into the past we keep buckets for the range at the given index around (in generations)';
 
   CREATE TABLE merchant_statistic_interval_meta (
     imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     slug TEXT NOT NULL,
     description TEXT NOT NULL,
     stype merchant.statistic_type NOT NULL,
     ranges INT8[] NOT NULL,
     precisions INT8[] NOT NULL,
     CONSTRAINT equal_array_LENGTH CHECK ((array_LENGTH(ranges, 1) = array_LENGTH(precisions, 1))),
     CONSTRAINT merchant_statistic_interval_meta_precisions_check CHECK ((array_LENGTH(precisions, 1) > 0)),
     CONSTRAINT merchant_statistic_interval_meta_ranges_check CHECK ((array_LENGTH(ranges, 1) > 0)),
     UNIQUE (slug, stype)
   );
   COMMENT ON TABLE merchant_statistic_interval_meta IS 'meta data about an interval statistic we are tracking';
   COMMENT ON COLUMN merchant_statistic_interval_meta.imeta_serial_id IS 'unique identifier for this type of interval statistic we are tracking';
   COMMENT ON COLUMN merchant_statistic_interval_meta.slug IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path';
   COMMENT ON COLUMN merchant_statistic_interval_meta.description IS 'description of the statistic being tracked';
   COMMENT ON COLUMN merchant_statistic_interval_meta.stype IS 'statistic type, what kind of data is being tracked, amount or number';
   COMMENT ON COLUMN merchant_statistic_interval_meta.ranges IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing';
   COMMENT ON COLUMN merchant_statistic_interval_meta.precisions IS 'determines how precisely we track which events fall into the range at the same index (allowing us to coalesce events with timestamps in proximity close to the given precision), in seconds, 0 is not allowed';
 
   CREATE TABLE merchant_token_families (
     token_family_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     slug TEXT NOT NULL UNIQUE,
     name TEXT NOT NULL,
     description TEXT,
     description_i18n JSONB NOT NULL,
     valid_after INT8 NOT NULL,
     valid_before INT8 NOT NULL,
     duration INT8 NOT NULL,
     kind TEXT NOT NULL,
     issued INT8 DEFAULT 0,
     used INT8 DEFAULT 0,
     validity_granularity INT8 DEFAULT '2592000000000'::INT8 NOT NULL,
     start_offset INT8 DEFAULT 0 NOT NULL,
     cipher_choice TEXT DEFAULT 'rsa(2048)'::text NOT NULL,
     extra_data JSONB,
     CONSTRAINT merchant_token_families_kind_check
       CHECK ((kind = ANY (ARRAY['subscription'::text, 'discount'::text]))),
     CONSTRAINT merchant_token_families_validity_granularity_check
       CHECK ((validity_granularity = ANY (ARRAY[(60000000)::INT8,
         '3600000000'::INT8, '86400000000'::INT8, '604800000000'::INT8,
         '2592000000000'::INT8, '7776000000000'::INT8,
         '31536000000000'::INT8])))
   );
   COMMENT ON TABLE merchant_token_families IS 'Token families configured by the merchant.';
   COMMENT ON COLUMN merchant_token_families.slug IS 'Unique slug for the token family.';
   COMMENT ON COLUMN merchant_token_families.name IS 'Name of the token family.';
   COMMENT ON COLUMN merchant_token_families.description IS 'Human-readable description or details about the token family.';
   COMMENT ON COLUMN merchant_token_families.description_i18n IS 'JSON map from IETF BCP 47 language tags to localized descriptions';
   COMMENT ON COLUMN merchant_token_families.valid_after IS 'Start time of the token family''s validity period.';
   COMMENT ON COLUMN merchant_token_families.valid_before IS 'End time of the token family''s validity period.';
   COMMENT ON COLUMN merchant_token_families.duration IS 'Duration of the token.';
   COMMENT ON COLUMN merchant_token_families.kind IS 'Kind of the token (e.g., subscription, discount).';
   COMMENT ON COLUMN merchant_token_families.issued IS 'Counter for the number of tokens issued for this token family.';
   COMMENT ON COLUMN merchant_token_families.used IS 'FIXME';
   COMMENT ON COLUMN merchant_token_families.validity_granularity IS 'To compute key lifetimes, we first round the payment deadline down to a multiple of this time; supported values are one minute, one hour, a day, a week, 30 days, 90 days or a year (indicatited using 365 days); adding the start_offset gets the start validity time; adding the duration to get the signature_valid_until value for the key';
   COMMENT ON COLUMN merchant_token_families.start_offset IS 'This allows shifting the validity period of signatures to start a bit before the time rounded to the precision. For example, Swiss vignettes are valid for 14 months, from December of year X to January of year X+2. This can be achieve by setting a start_offset of 30 days, and a duration of 14 months and a precision of 1 year. The value given is in microseconds (but will be rounded to seconds).';
   COMMENT ON COLUMN merchant_token_families.cipher_choice IS 'Specifies the type of cipher that should be used for this token family. Currently supported values are "cs" and "rsa($LEN)" where $LEN is the key length in bits.';
   COMMENT ON COLUMN merchant_token_families.extra_data IS 'JSON field with family-specific meta data, such as the trusted_domains for subscriptions or expected_domains for discount tokens';
 
   CREATE TABLE merchant_unclaim_signatures (
     unclaim_serial INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE,
     h_contract_terms BYTEA NOT NULL,
     unclaim_sig BYTEA NOT NULL PRIMARY KEY,
     expiration_time INT8 NOT NULL,
     CONSTRAINT merchant_unclaim_signatures_h_contract_terms_check CHECK ((LENGTH(h_contract_terms) = 64)),
     CONSTRAINT merchant_unclaim_signatures_unclaim_sig_check CHECK ((LENGTH(unclaim_sig) = 64))
   );
   COMMENT ON TABLE merchant_unclaim_signatures IS 'Here we store proofs of wallets unclaiming a claim to a contract. Right now not really used, but theoretically legally important as this shows that the wallet was the one abandoing its rights to the proposal.';
   COMMENT ON COLUMN merchant_unclaim_signatures.h_contract_terms IS 'Hash over the contract terms of the unclaimed contract';
   COMMENT ON COLUMN merchant_unclaim_signatures.unclaim_sig IS 'Signature of purpose CONTRACT_UNCLAIM';
   COMMENT ON COLUMN merchant_unclaim_signatures.expiration_time IS 'Payment deadline of the original contract, at this point we should be OK to delete the unclaim signature via garbage collection.';
   CREATE INDEX merchant_unclaim_signatures_by_expiration
     ON merchant_unclaim_signatures (expiration_time);
   COMMENT ON INDEX merchant_unclaim_signatures_by_expiration IS 'For garbage collection of unclaim signatrues after payment deadlines have been passed.';
 
   CREATE TABLE merchant_webhook (
     webhook_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     webhook_id TEXT NOT NULL UNIQUE,
     event_type TEXT NOT NULL,
     url TEXT NOT NULL,
     http_method TEXT NOT NULL,
     header_template TEXT,
     body_template TEXT
   );
   COMMENT ON TABLE merchant_webhook IS 'webhook used by the merchant (may be incomplete, frontend can override)';
   COMMENT ON COLUMN merchant_webhook.webhook_id IS 'slug identifying the webhook in protocols';
   COMMENT ON COLUMN merchant_webhook.event_type IS 'Event of the webhook';
   COMMENT ON COLUMN merchant_webhook.url IS 'URL to make the request to';
   COMMENT ON COLUMN merchant_webhook.http_method IS 'http method use by the merchant';
   COMMENT ON COLUMN merchant_webhook.header_template IS 'Template for the header of the webhook, to be modified based on trigger data';
   COMMENT ON COLUMN merchant_webhook.body_template IS 'Template for the body of the webhook, to be modified based on trigger data';
 
   CREATE TABLE tan_challenges (
     challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE,
     h_body BYTEA NOT NULL,
     salt BYTEA NOT NULL,
     op merchant.op_enum NOT NULL,
     code TEXT NOT NULL,
     creation_date INT8 NOT NULL,
     expiration_date INT8 NOT NULL,
     retransmission_date INT8 DEFAULT 0 NOT NULL,
     confirmation_date INT8,
     retry_counter INT4 NOT NULL,
     tan_channel merchant.tan_enum NOT NULL,
     required_address TEXT NOT NULL,
     CONSTRAINT tan_challenges_h_body_check CHECK ((LENGTH(h_body) = 32)),
     CONSTRAINT tan_challenges_salt_check CHECK ((LENGTH(salt) = 16))
   );
   COMMENT ON TABLE tan_challenges IS 'Stores multi-factor authorization (MFA) challenges';
   COMMENT ON COLUMN tan_challenges.challenge_id IS 'Unique identifier for the challenge';
   COMMENT ON COLUMN tan_challenges.h_body IS 'Salted hash of the body of the original request that triggered the challenge, to be replayed once the challenge is satisfied.';
   COMMENT ON COLUMN tan_challenges.salt IS 'Salt used when hashing the original body.';
   COMMENT ON COLUMN tan_challenges.op IS 'The protected operation to run after the challenge';
   COMMENT ON COLUMN tan_challenges.code IS 'The pin code sent to the user and verified';
   COMMENT ON COLUMN tan_challenges.creation_date IS 'Creation date of the code';
   COMMENT ON COLUMN tan_challenges.expiration_date IS 'When will the code expire';
   COMMENT ON COLUMN tan_challenges.retransmission_date IS 'When did we last transmit the challenge to the user';
   COMMENT ON COLUMN tan_challenges.confirmation_date IS 'When was this challenge successfully verified, NULL if pending';
   COMMENT ON COLUMN tan_challenges.retry_counter IS 'How many tries are left for this code; must be > 0';
   COMMENT ON COLUMN tan_challenges.tan_channel IS 'TAN channel to use, if NULL use customer configured one';
   COMMENT ON COLUMN tan_challenges.required_address IS 'Address to which the challenge will be sent';
   CREATE INDEX tan_challenges_expiration_index
     ON tan_challenges (expiration_date);
   COMMENT ON INDEX tan_challenges_expiration_index IS 'for garbage collection';
 
   CREATE TABLE merchant_builtin_unit_overrides (
     builtin_unit_serial INT8 NOT NULL PRIMARY KEY,
     override_allow_fraction BOOLEAN,
     override_precision_level INT4,
     override_active BOOLEAN,
     CONSTRAINT merchant_builtin_unit_overrides_override_precision_level_check
       CHECK (((override_precision_level >= 0) AND (override_precision_level <= 6))),
     CONSTRAINT merchant_builtin_unit_overrides_builtin_unit_serial_fkey
       FOREIGN KEY (builtin_unit_serial)
       REFERENCES merchant.merchant_builtin_units(unit_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_builtin_unit_overrides IS 'Per-instance overrides for builtin units (fraction policy and visibility).';
   COMMENT ON COLUMN merchant_builtin_unit_overrides.override_allow_fraction IS 'FIXME';
   COMMENT ON COLUMN merchant_builtin_unit_overrides.override_precision_level IS 'FIXME';
   COMMENT ON COLUMN merchant_builtin_unit_overrides.override_active IS 'FIXME';
 
   -- ===================================================================
   -- Tier 1: tables referencing only Tier 0 (and global merchant.*) tables.
   -- ===================================================================
 
   CREATE TABLE merchant_deposit_confirmations (
     deposit_confirmation_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     order_serial INT8,
     deposit_timestamp INT8 NOT NULL,
     exchange_url TEXT NOT NULL,
     total_without_fee merchant.taler_amount_currency NOT NULL,
     wire_fee merchant.taler_amount_currency NOT NULL,
     signkey_serial INT8 NOT NULL,
     exchange_sig BYTEA NOT NULL,
     account_serial INT8 NOT NULL,
     wire_transfer_deadline INT8 DEFAULT 0 NOT NULL,
     wire_pending BOOLEAN DEFAULT TRUE NOT NULL,
     exchange_failure TEXT,
     retry_backoff INT8 DEFAULT 0 NOT NULL,
     CONSTRAINT merchant_deposit_confirmations_exchange_sig_check CHECK ((LENGTH(exchange_sig) = 64)),
     UNIQUE (order_serial, exchange_sig),
     CONSTRAINT merchant_deposit_confirmations_account_serial_fkey
       FOREIGN KEY (account_serial)
       REFERENCES merchant_accounts(account_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_deposit_confirmations_order_serial_fkey
       FOREIGN KEY (order_serial)
       REFERENCES merchant_contract_terms(order_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_deposit_confirmations_signkey_serial_fkey
       FOREIGN KEY (signkey_serial)
       REFERENCES merchant.merchant_exchange_signing_keys(signkey_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_deposit_confirmations IS 'Table with the deposit confirmations for each coin we deposited at the exchange';
   COMMENT ON COLUMN merchant_deposit_confirmations.order_serial IS 'Identifies the entry in the contract_terms table that was paid by this deposit';
   COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp IS 'Time when the exchange generated the deposit confirmation';
   COMMENT ON COLUMN merchant_deposit_confirmations.exchange_url IS 'Which exchange received the deposit; a single contract may be paid by multiple (batch) deposits to multiple exchanges';
   COMMENT ON COLUMN merchant_deposit_confirmations.total_without_fee IS 'Total amount that was (batch) deposited, excluding deposit fees (irrespective of who had to pay the fees); so this is the amount the merchant will be credited for this deposit (minus applicable wire fees after aggregation)';
   COMMENT ON COLUMN merchant_deposit_confirmations.wire_fee IS 'We MAY want to see if we should try to get this via merchant_exchange_wire_fees (not sure, may be too complicated with the date range, etc.)';
   COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial IS 'Online signing key of the exchange on the deposit confirmation';
   COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig IS 'Signature of the exchange over the deposit confirmation';
   COMMENT ON COLUMN merchant_deposit_confirmations.account_serial IS 'Identifies the bank account of the merchant that will receive the payment';
   COMMENT ON COLUMN merchant_deposit_confirmations.wire_transfer_deadline IS 'when should the exchange make the wire transfer at the latest';
   COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending IS 'true if we are awaiting wire details for a deposit of this purchase (and are not blocked on KYC); false once the exchange says that the wire transfer has happened (does not mean that we confirmed it happened though)';
   COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure IS 'Text describing exchange failures in making timely wire transfers for this deposit confirmation';
   COMMENT ON COLUMN merchant_deposit_confirmations.retry_backoff IS 'exponentially increasing value we add to the wire_transfer_deadline on each failure to confirm the wire transfer';
   CREATE INDEX merchant_deposit_confirmations_by_pending_wire
     ON merchant_deposit_confirmations (exchange_url, wire_transfer_deadline)
     WHERE wire_pending;
 
   CREATE TABLE merchant_expected_transfers (
     expected_credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     exchange_url TEXT NOT NULL,
     wtid BYTEA NOT NULL,
     expected_credit_amount merchant.taler_amount_currency,
     wire_fee merchant.taler_amount_currency,
     account_serial INT8 NOT NULL,
     expected_time INT8 NOT NULL,
     retry_time INT8 DEFAULT 0 NOT NULL,
     last_http_status INT4,
     last_ec INT4,
     last_detail TEXT,
     retry_needed BOOLEAN DEFAULT TRUE NOT NULL,
     signkey_serial INT8,
     exchange_sig BYTEA,
     h_details BYTEA,
     confirmed BOOLEAN DEFAULT FALSE NOT NULL,
     CONSTRAINT merchant_expected_transfers_exchange_sig_check CHECK ((LENGTH(exchange_sig) = 64)),
     CONSTRAINT merchant_expected_transfers_h_details_check CHECK ((LENGTH(h_details) = 64)),
     CONSTRAINT merchant_expected_transfers_wtid_check CHECK ((LENGTH(wtid) = 32)),
     UNIQUE (wtid, exchange_url, account_serial),
     CONSTRAINT merchant_expected_transfers_account_serial_fkey
       FOREIGN KEY (account_serial)
       REFERENCES merchant_accounts(account_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_expected_transfers_signkey_serial_fkey
       FOREIGN KEY (signkey_serial)
       REFERENCES merchant.merchant_exchange_signing_keys(signkey_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_expected_transfers IS 'expected incoming wire transfers';
   COMMENT ON COLUMN merchant_expected_transfers.expected_credit_serial IS 'Unique identifier for this expected wire transfer in this backend';
   COMMENT ON COLUMN merchant_expected_transfers.exchange_url IS 'Base URL of the exchange that originated the wire transfer as extracted from the wire transfer subject';
   COMMENT ON COLUMN merchant_expected_transfers.wtid IS 'Unique wire transfer identifier (or at least, should be unique by protocol) as selected by the exchange and extracted from the wire transfer subject';
   COMMENT ON COLUMN merchant_expected_transfers.expected_credit_amount IS 'expected actual value of the (aggregated) wire transfer, excluding the wire fee; NULL if unknown';
   COMMENT ON COLUMN merchant_expected_transfers.wire_fee IS 'wire fee the exchange claims to have charged us; NULL if unknown';
   COMMENT ON COLUMN merchant_expected_transfers.account_serial IS 'Merchant bank account that should receive this wire transfer; also implies the merchant instance implicated by the wire transfer';
   COMMENT ON COLUMN merchant_expected_transfers.expected_time IS 'Time when we should expect the exchange do do the wire transfer';
   COMMENT ON COLUMN merchant_expected_transfers.retry_time IS 'Time when we should next inquire at the exchange about this wire transfer; used by taler-merchant-reconciliation to limit retries with the exchange in case of failures';
   COMMENT ON COLUMN merchant_expected_transfers.last_http_status IS 'HTTP status of the last request to the exchange, 0 on timeout or if there was no request (200 on success)';
   COMMENT ON COLUMN merchant_expected_transfers.last_ec IS 'Taler error code from the last request to the exchange, 0 on success or if there was no request';
   COMMENT ON COLUMN merchant_expected_transfers.last_detail IS 'Taler error detail from the last request to the exchange, NULL on success or if there was no request';
   COMMENT ON COLUMN merchant_expected_transfers.retry_needed IS 'true if we need to retry the HTTP request to the exchange (never did it, or transient failure)';
   COMMENT ON COLUMN merchant_expected_transfers.signkey_serial IS 'Identifies the online signing key of the exchange used to make the exchange_sig';
   COMMENT ON COLUMN merchant_expected_transfers.exchange_sig IS 'Signature over the aggregation response from the exchange, or NULL on error or if we did not yet make that request';
   COMMENT ON COLUMN merchant_expected_transfers.h_details IS 'Hash over the aggregation details returned by the exchange, provided here for fast exchange_sig validation';
   COMMENT ON COLUMN merchant_expected_transfers.confirmed IS 'true once the merchant confirmed that this transfer was received and a matching transfer exists in the merchant_transfers table; set automatically via INSERT TRIGGER merchant_expected_transfers_insert_trigger';
   CREATE INDEX merchant_expected_transfers_by_open
     ON merchant_expected_transfers (retry_time)
     WHERE ((NOT confirmed) OR retry_needed);
   COMMENT ON INDEX merchant_expected_transfers_by_open IS 'For select_open_transfers';
 
   CREATE TABLE merchant_inventory (
     product_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     product_id TEXT NOT NULL UNIQUE,
     description TEXT NOT NULL,
     description_i18n JSONB NOT NULL,
     unit TEXT NOT NULL,
     image TEXT NOT NULL,
     taxes JSONB NOT NULL,
     total_stock INT8 NOT NULL,
     total_sold INT8 DEFAULT 0 NOT NULL,
     total_lost INT8 DEFAULT 0 NOT NULL,
     address JSONB NOT NULL,
     next_restock INT8 NOT NULL,
     minimum_age INT4 DEFAULT 0 NOT NULL,
     product_name TEXT NOT NULL,
     image_hash TEXT,
     price_array merchant.taler_amount_currency[]
       DEFAULT ARRAY[]::merchant.taler_amount_currency[] NOT NULL,
     total_stock_frac INT4 DEFAULT 0 NOT NULL,
     total_sold_frac INT4 DEFAULT 0 NOT NULL,
     total_lost_frac INT4 DEFAULT 0 NOT NULL,
     allow_fractional_quantity BOOLEAN DEFAULT FALSE NOT NULL,
     fractional_precision_level INT4 DEFAULT 0 NOT NULL,
     product_group_serial INT8,
     money_pot_serial INT8,
     price_is_net BOOLEAN DEFAULT FALSE,
     CONSTRAINT merchant_inventory_money_pot_serial_fkey
       FOREIGN KEY (money_pot_serial)
       REFERENCES merchant_money_pots(money_pot_serial) ON DELETE SET NULL,
     CONSTRAINT merchant_inventory_product_group_serial_fkey
       FOREIGN KEY (product_group_serial)
       REFERENCES merchant_product_groups(product_group_serial) ON DELETE SET NULL
   );
   COMMENT ON TABLE merchant_inventory IS 'products offered by the merchant (may be incomplete, frontend can override)';
   COMMENT ON COLUMN merchant_inventory.product_id IS 'slug identifying the product in protocols';
   COMMENT ON COLUMN merchant_inventory.description IS 'Human-readable product description';
   COMMENT ON COLUMN merchant_inventory.description_i18n IS 'JSON map from IETF BCP 47 language tags to localized descriptions';
   COMMENT ON COLUMN merchant_inventory.unit IS 'Unit of sale for the product (liters, kilograms, packages)';
   COMMENT ON COLUMN merchant_inventory.image IS 'NOT NULL, but can be 0 bytes; must contain an ImageDataUrl';
   COMMENT ON COLUMN merchant_inventory.taxes IS 'JSON array containing taxes the merchant pays, must be JSON, but can be just "[]"';
   COMMENT ON COLUMN merchant_inventory.total_stock IS 'A value of -1 is used for unlimited (electronic good), may never be lowered';
   COMMENT ON COLUMN merchant_inventory.total_sold IS 'Number of products sold, must be below total_stock, non-negative, may never be lowered';
   COMMENT ON COLUMN merchant_inventory.total_lost IS 'Number of products that used to be in stock but were lost (spoiled, damaged), may never be lowered; total_stock >= total_sold + total_lost must always hold';
   COMMENT ON COLUMN merchant_inventory.address IS 'JSON formatted Location of where the product is stocked';
   COMMENT ON COLUMN merchant_inventory.next_restock IS 'GNUnet absolute time i ndicating when the next restock is expected. 0 for unknown.';
   COMMENT ON COLUMN merchant_inventory.minimum_age IS 'Minimum age of the customer in years, to be used if an exchange supports the age restriction extension.';
   COMMENT ON COLUMN merchant_inventory.product_name IS 'Name of the product';
   COMMENT ON COLUMN merchant_inventory.image_hash IS 'SHA-256 hash of the base64-encoded image data, used by wallets to fetch product images.';
   COMMENT ON COLUMN merchant_inventory.price_array IS 'List of unit prices available for the product (multiple tiers supported).';
   COMMENT ON COLUMN merchant_inventory.total_stock_frac IS 'Fractional part of stock in units of 1/1000000 of the base value';
   COMMENT ON COLUMN merchant_inventory.total_sold_frac IS 'Fractional part of units sold in units of 1/1000000 of the base value';
   COMMENT ON COLUMN merchant_inventory.total_lost_frac IS 'Fractional part of units lost in units of 1/1000000 of the base value';
   COMMENT ON COLUMN merchant_inventory.allow_fractional_quantity IS 'Whether fractional stock (total_stock_frac) should be honored for this product';
   COMMENT ON COLUMN merchant_inventory.fractional_precision_level IS 'Preset number of decimal places for fractional quantities';
   COMMENT ON COLUMN merchant_inventory.product_group_serial IS 'Specifies the product group a given product is a member of. If NULL, the product is in the __default__ group';
   COMMENT ON COLUMN merchant_inventory.money_pot_serial IS 'Specifies that sales amounts of this product (excluding taxes on the product that have their own pot specified) should by default be added to the given money pot. If NULL, the money pot rules of the overall order apply instead.';
   COMMENT ON COLUMN merchant_inventory.price_is_net IS 'If true, the price given is the net price; if false, it is the gross price.';
   CREATE INDEX merchant_inventory_by_image_hash
     ON merchant_inventory (image_hash);
   CREATE INDEX trgm_idx_products_by_description
     ON merchant_inventory USING gin (lower(description) public.gin_trgm_ops);
   CREATE INDEX trgm_idx_products_by_name
     ON merchant_inventory USING gin (lower(product_name) public.gin_trgm_ops);
 
   CREATE TABLE merchant_kyc (
     kyc_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE,
     kyc_timestamp INT8 NOT NULL,
     kyc_ok BOOLEAN DEFAULT FALSE NOT NULL,
     account_serial INT8 NOT NULL,
     exchange_url TEXT NOT NULL,
     access_token BYTEA,
     exchange_http_status INT4 DEFAULT 0,
     exchange_ec_code INT4 DEFAULT 0,
     aml_review BOOLEAN DEFAULT FALSE,
     jaccount_limits JSONB,
     last_rule_gen INT8 DEFAULT 0 NOT NULL,
     next_kyc_poll INT8 DEFAULT 0 NOT NULL,
     kyc_backoff INT8 DEFAULT 0 NOT NULL,
     CONSTRAINT access_token_LENGTH_check CHECK ((LENGTH(access_token) = 32)),
     PRIMARY KEY (account_serial, exchange_url),
     CONSTRAINT merchant_kyc_account_serial_fkey
       FOREIGN KEY (account_serial)
       REFERENCES merchant_accounts(account_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_kyc IS 'Status of the KYC process of a merchant account at an exchange';
   COMMENT ON COLUMN merchant_kyc.kyc_timestamp IS 'Last time we checked our KYC status at the exchange. Useful to re-check if the status is very stale. Also the timestamp used for the exchange signature (if present).';
   COMMENT ON COLUMN merchant_kyc.kyc_ok IS 'true if the KYC check was passed successfully';
   COMMENT ON COLUMN merchant_kyc.account_serial IS 'Which bank account of the merchant is the KYC status for';
   COMMENT ON COLUMN merchant_kyc.exchange_url IS 'Which exchange base URL is this KYC status valid for';
   COMMENT ON COLUMN merchant_kyc.access_token IS 'Access token required to begin the KYC process';
   COMMENT ON COLUMN merchant_kyc.exchange_http_status IS 'Last HTTP status returned by the exchange when inquiring about our KYC status.';
   COMMENT ON COLUMN merchant_kyc.exchange_ec_code IS 'Last Taler error code returned by the exchange when inquiring about our KYC status.';
   COMMENT ON COLUMN merchant_kyc.aml_review IS 'True if our account is under AML review according to the exchange.';
   COMMENT ON COLUMN merchant_kyc.jaccount_limits IS 'JSON with AccountLimits that apply to this account';
   COMMENT ON COLUMN merchant_kyc.last_rule_gen IS 'Row ID from the exchange identifying the last decision change to KYC rules on this account. To be used when long-polling to ask for changes. 0 if no custom decisions were taken for the account';
   COMMENT ON COLUMN merchant_kyc.next_kyc_poll IS 'When should we next do a KYC poll on this exchange and bank account';
   COMMENT ON COLUMN merchant_kyc.kyc_backoff IS 'What is the current backoff value between KYC polls';
   CREATE INDEX merchant_kyc_by_next_kyc_poll
     ON merchant_kyc (next_kyc_poll);
 
   CREATE TABLE merchant_order_token_blinded_sigs (
     order_token_bs_serial INT8 GENERATED BY DEFAULT AS IDENTITY,
     order_serial INT8 NOT NULL,
     token_index INT4 NOT NULL,
     token_blinded_signature BYTEA NOT NULL,
     token_hash BYTEA NOT NULL,
     CONSTRAINT merchant_order_token_blinded_sigs_token_hash_check CHECK ((LENGTH(token_hash) = 64)),
     PRIMARY KEY (order_serial, token_index),
     CONSTRAINT merchant_order_token_blinded_sigs_order_serial_fkey
       FOREIGN KEY (order_serial)
       REFERENCES merchant_contract_terms(order_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_order_token_blinded_sigs IS 'Table linking merchant orders with Donau BUDIS information';
   COMMENT ON COLUMN merchant_order_token_blinded_sigs.order_token_bs_serial IS 'Unique serial identifier for token order linkage';
   COMMENT ON COLUMN merchant_order_token_blinded_sigs.order_serial IS 'Foreign key linking to the corresponding merchant order';
   COMMENT ON COLUMN merchant_order_token_blinded_sigs.token_index IS 'offset of the given signature in the output token array';
   COMMENT ON COLUMN merchant_order_token_blinded_sigs.token_blinded_signature IS 'Blinded signature of the token associated with the order';
   COMMENT ON COLUMN merchant_order_token_blinded_sigs.token_hash IS 'Hash of the token';
 
   CREATE TABLE merchant_pending_webhooks (
     webhook_pending_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     webhook_serial INT8 NOT NULL,
     next_attempt INT8 DEFAULT 0 NOT NULL,
     retries INT4 DEFAULT 0 NOT NULL,
     url TEXT NOT NULL,
     http_method TEXT NOT NULL,
     header TEXT,
     body TEXT,
     CONSTRAINT merchant_pending_webhooks_webhook_serial_fkey
       FOREIGN KEY (webhook_serial)
       REFERENCES merchant_webhook(webhook_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_pending_webhooks IS 'webhooks that still need to be executed by the merchant';
   COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial IS 'Reference to the configured webhook template';
   COMMENT ON COLUMN merchant_pending_webhooks.next_attempt IS 'Time when we should make the next request to the webhook';
   COMMENT ON COLUMN merchant_pending_webhooks.retries IS 'How often have we tried this request so far';
   COMMENT ON COLUMN merchant_pending_webhooks.url IS 'URL to make the request to';
   COMMENT ON COLUMN merchant_pending_webhooks.http_method IS 'http method use for the webhook';
   COMMENT ON COLUMN merchant_pending_webhooks.header IS 'Header of the webhook';
   COMMENT ON COLUMN merchant_pending_webhooks.body IS 'Body of the webhook';
 
   CREATE TABLE merchant_refunds (
     refund_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     order_serial INT8 NOT NULL,
     rtransaction_id INT8 NOT NULL,
     refund_timestamp INT8 NOT NULL,
     coin_pub BYTEA NOT NULL,
     reason TEXT NOT NULL,
     refund_amount merchant.taler_amount_currency NOT NULL,
     UNIQUE (order_serial, coin_pub, rtransaction_id),
     CONSTRAINT merchant_refunds_order_serial_fkey
       FOREIGN KEY (order_serial)
       REFERENCES merchant_contract_terms(order_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_refunds IS 'Refunds approved by the merchant (backoffice) logic, excludes abort refunds';
   COMMENT ON COLUMN merchant_refunds.order_serial IS 'order for which the refund is being applied';
   COMMENT ON COLUMN merchant_refunds.rtransaction_id IS 'Needed for uniqueness in case a refund is increased for the same order';
   COMMENT ON COLUMN merchant_refunds.refund_timestamp IS 'Needed for grouping of refunds in the wallet UI; has no semantics in the protocol (only for UX), but should be from the time when the merchant internally approved the refund';
   COMMENT ON COLUMN merchant_refunds.coin_pub IS 'public key of the coin that is going to be refunded';
   COMMENT ON COLUMN merchant_refunds.reason IS 'human-readable reason given for the refund';
   COMMENT ON COLUMN merchant_refunds.refund_amount IS 'refund amount granted on this coin';
   CREATE INDEX merchant_refunds_by_coin_and_order
     ON merchant_refunds (coin_pub, order_serial);
 
   CREATE TABLE merchant_statistic_amount_event (
     aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     imeta_serial_id INT8,
     slot INT8 NOT NULL,
     delta_curr character varying(12) NOT NULL,
     delta_value INT8 NOT NULL,
     delta_frac INT4 NOT NULL,
     CONSTRAINT event_key UNIQUE (imeta_serial_id, delta_curr, slot),
     CONSTRAINT merchant_statistic_amount_event_imeta_serial_id_fkey
       FOREIGN KEY (imeta_serial_id)
       REFERENCES merchant_statistic_interval_meta(imeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_amount_event IS 'amount to decrement an interval statistic by when a certain time value is reached';
   COMMENT ON COLUMN merchant_statistic_amount_event.aevent_serial_id IS 'unique identifier for this amount event';
   COMMENT ON COLUMN merchant_statistic_amount_event.imeta_serial_id IS 'identifies what the statistic is about; must be of clazz interval and of stype amount';
   COMMENT ON COLUMN merchant_statistic_amount_event.slot IS 'identifies the time slot at which the given event(s) happened';
   COMMENT ON COLUMN merchant_statistic_amount_event.delta_curr IS 'currency of the total cumulative amount that was added at the time identified by slot';
   COMMENT ON COLUMN merchant_statistic_amount_event.delta_value IS 'total cumulative amount (value) that was added at the time identified by slot';
   COMMENT ON COLUMN merchant_statistic_amount_event.delta_frac IS 'total cumulative amount (fraction) that was added at the time identified by slot';
 
   CREATE TABLE merchant_statistic_bucket_amount (
     bmeta_serial_id INT8 NOT NULL,
     bucket_start INT8 NOT NULL,
     bucket_range merchant.statistic_range NOT NULL,
     curr character varying(12) NOT NULL,
     cumulative_value INT8 NOT NULL,
     cumulative_frac INT4 NOT NULL,
     PRIMARY KEY (bmeta_serial_id, curr, bucket_start, bucket_range),
     CONSTRAINT merchant_statistic_bucket_amount_bmeta_serial_id_fkey
       FOREIGN KEY (bmeta_serial_id)
       REFERENCES merchant_statistic_bucket_meta(bmeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_bucket_amount IS 'various amount statistics (in various currencies) being tracked';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.bmeta_serial_id IS 'identifies what the statistic is about';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_start IS 'start date for the bucket in seconds since the epoch';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_range IS 'range of the bucket';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.curr IS 'currency which this statistic is tracking the amount for';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_value IS 'amount in the respective currency, non-fractional amount value';
   COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_frac IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value';
 
   CREATE TABLE merchant_statistic_bucket_counter (
     bmeta_serial_id INT8 NOT NULL,
     bucket_start INT8 NOT NULL,
     bucket_range merchant.statistic_range NOT NULL,
     cumulative_number INT8 NOT NULL,
     PRIMARY KEY (bmeta_serial_id, bucket_start, bucket_range),
     CONSTRAINT merchant_statistic_bucket_counter_bmeta_serial_id_fkey
       FOREIGN KEY (bmeta_serial_id)
       REFERENCES merchant_statistic_bucket_meta(bmeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_bucket_counter IS 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall';
   COMMENT ON COLUMN merchant_statistic_bucket_counter.bmeta_serial_id IS 'identifies what the statistic is about';
   COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_start IS 'start date for the bucket in seconds since the epoch';
   COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_range IS 'range of the bucket';
   COMMENT ON COLUMN merchant_statistic_bucket_counter.cumulative_number IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword';
 
   CREATE TABLE merchant_statistic_counter_event (
     nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     imeta_serial_id INT8,
     slot INT8 NOT NULL,
     delta INT8 NOT NULL,
     UNIQUE (imeta_serial_id, slot),
     CONSTRAINT merchant_statistic_counter_event_imeta_serial_id_fkey
       FOREIGN KEY (imeta_serial_id)
       REFERENCES merchant_statistic_interval_meta(imeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_counter_event IS 'number to decrement an interval statistic by when a certain time value is reached';
   COMMENT ON COLUMN merchant_statistic_counter_event.nevent_serial_id IS 'unique identifier for this number event';
   COMMENT ON COLUMN merchant_statistic_counter_event.imeta_serial_id IS 'identifies what the statistic is about; must be of stype number';
   COMMENT ON COLUMN merchant_statistic_counter_event.slot IS 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value';
   COMMENT ON COLUMN merchant_statistic_counter_event.delta IS 'total cumulative number that was added at the time identified by slot';
 
   CREATE TABLE merchant_template (
     template_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     template_id TEXT NOT NULL UNIQUE,
     template_description TEXT NOT NULL,
     otp_device_id INT8,
     template_contract JSONB NOT NULL,
     editable_defaults JSONB,
     CONSTRAINT merchant_template_otp_device_id_fkey
       FOREIGN KEY (otp_device_id)
       REFERENCES merchant_otp_devices(otp_serial) ON DELETE SET NULL
   );
   COMMENT ON TABLE merchant_template IS 'template used by the merchant (may be incomplete, frontend can override)';
   COMMENT ON COLUMN merchant_template.template_id IS 'slug identifying the templates in protocols (and on the QR code)';
   COMMENT ON COLUMN merchant_template.template_description IS 'Human-readable template description';
   COMMENT ON COLUMN merchant_template.otp_device_id IS 'OTP device ID used to verify payments by the shop using this QR code';
   COMMENT ON COLUMN merchant_template.template_contract IS 'The template contract will contains some additional information.';
   COMMENT ON COLUMN merchant_template.editable_defaults IS 'JSON object with fields matching the template contract, just with default values that are editable by the user';
 
   CREATE TABLE merchant_token_family_keys (
     token_family_key_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     token_family_serial INT8,
     pub BYTEA NOT NULL,
     h_pub BYTEA NOT NULL UNIQUE,
     priv BYTEA,
     cipher TEXT NOT NULL,
     signature_validity_start INT8 DEFAULT 0 NOT NULL,
     signature_validity_end INT8 DEFAULT 0 NOT NULL,
     private_key_deleted_at INT8 DEFAULT 0 NOT NULL,
     private_key_created_at INT8 DEFAULT 0 NOT NULL,
     CONSTRAINT h_pub_LENGTH_check CHECK ((LENGTH(h_pub) = 64)),
     CONSTRAINT merchant_token_family_keys_cipher_check CHECK ((cipher = ANY (ARRAY['rsa'::text, 'cs'::text]))),
     CONSTRAINT merchant_token_family_keys_token_family_serial_fkey
       FOREIGN KEY (token_family_serial)
       REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_token_family_keys IS 'Keys for token families.';
   COMMENT ON COLUMN merchant_token_family_keys.token_family_serial IS 'Token family to which the key belongs.';
   COMMENT ON COLUMN merchant_token_family_keys.pub IS 'Public key of the token family.';
   COMMENT ON COLUMN merchant_token_family_keys.h_pub IS 'Hash of the public key for quick lookup.';
   COMMENT ON COLUMN merchant_token_family_keys.priv IS 'Private key of the token family; can be NULL if no more tokens of this family should be issued.';
   COMMENT ON COLUMN merchant_token_family_keys.cipher IS 'Cipher used (rsa or cs).';
   COMMENT ON COLUMN merchant_token_family_keys.signature_validity_start IS 'Specifies the earliest time at which tokens signed with this key can be considered valid. Allows tokens to be issued way in advance of their validity.';
   COMMENT ON COLUMN merchant_token_family_keys.signature_validity_end IS 'Specifies when the tokens signed by this key expire.';
   COMMENT ON COLUMN merchant_token_family_keys.private_key_deleted_at IS 'Specifies how long tokens signed by this key can be created, that is the point at which the private key may be deleted. Computed by determining when the *next* validity period starts, or when the overall token family validity period ends.';
   COMMENT ON COLUMN merchant_token_family_keys.private_key_created_at IS 'Specifies when the private key was created. Not terribly useful, mostly for debugging.';
 
   CREATE TABLE merchant_transfers (
     credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     exchange_url TEXT NOT NULL,
     wtid BYTEA,
     credit_amount merchant.taler_amount_currency NOT NULL,
     account_serial INT8 NOT NULL,
     bank_serial_id INT8,
     expected BOOLEAN DEFAULT FALSE,
     execution_time INT8 DEFAULT 0,
     CONSTRAINT merchant_transfers_wtid_check CHECK ((LENGTH(wtid) = 32)),
     CONSTRAINT merchant_transfers_unique UNIQUE (wtid, exchange_url, account_serial, bank_serial_id),
     CONSTRAINT merchant_transfers_account_serial_fkey
       FOREIGN KEY (account_serial)
       REFERENCES merchant_accounts(account_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_transfers IS 'table represents confirmed incoming wire transfers';
   COMMENT ON COLUMN merchant_transfers.credit_serial IS 'Unique identifier for this wire transfer in this backend';
   COMMENT ON COLUMN merchant_transfers.exchange_url IS 'Base URL of the exchange that originated the wire transfer as extracted from the wire transfer subject';
   COMMENT ON COLUMN merchant_transfers.wtid IS 'Unique wire transfer identifier (or at least, should be unique by protocol) as selected by the exchange and extracted from the wire transfer subject';
   COMMENT ON COLUMN merchant_transfers.credit_amount IS 'actual value of the confirmed wire transfer';
   COMMENT ON COLUMN merchant_transfers.account_serial IS 'Merchant bank account that received this wire transfer; also implies the merchant instance implicated by the wire transfer';
   COMMENT ON COLUMN merchant_transfers.bank_serial_id IS 'Row ID of the wire transfer from the automated import; NULL if not available (like when a human manually imported the transfer)';
   COMMENT ON COLUMN merchant_transfers.expected IS 'True if this wire transfer was expected (has matching entry in merchant_expected_transfers); set automatically via INSERT TRIGGER merchant_transfers_insert_trigger';
   COMMENT ON COLUMN merchant_transfers.execution_time IS 'Time when the merchant transfer was added and thus roughly received in our bank account';
 
   -- ===================================================================
   -- Tier 2: tables referencing Tier 1 (and earlier) tables.
   -- ===================================================================
 
   CREATE TABLE merchant_deposits (
     deposit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     coin_offset INT4 NOT NULL,
     deposit_confirmation_serial INT8 NOT NULL,
     coin_pub BYTEA NOT NULL,
     coin_sig BYTEA NOT NULL,
     amount_with_fee merchant.taler_amount_currency NOT NULL,
     deposit_fee merchant.taler_amount_currency NOT NULL,
     refund_fee merchant.taler_amount_currency NOT NULL,
     settlement_retry_needed BOOLEAN DEFAULT TRUE,
     settlement_retry_time INT8 DEFAULT 0,
     settlement_last_http_status INT4,
     settlement_last_ec INT4,
     settlement_last_detail TEXT,
     settlement_wtid BYTEA,
     settlement_coin_contribution merchant.taler_amount_currency,
     settlement_expected_credit_serial INT8,
     signkey_serial INT8,
     settlement_exchange_sig BYTEA,
     CONSTRAINT merchant_deposits_coin_pub_check CHECK ((LENGTH(coin_pub) = 32)),
     CONSTRAINT merchant_deposits_coin_sig_check CHECK ((LENGTH(coin_sig) = 64)),
     CONSTRAINT merchant_deposits_settlement_exchange_sig_check CHECK ((LENGTH(settlement_exchange_sig) = 64)),
     CONSTRAINT merchant_deposits_settlement_wtid_check CHECK ((LENGTH(settlement_wtid) = 32)),
     UNIQUE (deposit_confirmation_serial, coin_pub),
     CONSTRAINT merchant_deposits_deposit_confirmation_serial_fkey
       FOREIGN KEY (deposit_confirmation_serial)
       REFERENCES merchant_deposit_confirmations(deposit_confirmation_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_deposits_settlement_expected_credit_serial_fkey
       FOREIGN KEY (settlement_expected_credit_serial)
       REFERENCES merchant_expected_transfers(expected_credit_serial),
     CONSTRAINT merchant_deposits_signkey_serial_fkey
       FOREIGN KEY (signkey_serial)
       REFERENCES merchant.merchant_exchange_signing_keys(signkey_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_deposits IS 'Table with the deposit details for each coin we deposited at the exchange';
   COMMENT ON COLUMN merchant_deposits.coin_offset IS 'Offset of this coin in the batch';
   COMMENT ON COLUMN merchant_deposits.deposit_confirmation_serial IS 'Reference to the deposit confirmation of the exchange';
   COMMENT ON COLUMN merchant_deposits.coin_pub IS 'Public key of the coin that was deposited';
   COMMENT ON COLUMN merchant_deposits.coin_sig IS 'Signature by the coin over the deposit request';
   COMMENT ON COLUMN merchant_deposits.amount_with_fee IS 'Total amount (incl. fee) of the coin that was deposited';
   COMMENT ON COLUMN merchant_deposits.deposit_fee IS 'Deposit fee (for this coin) that was paid';
   COMMENT ON COLUMN merchant_deposits.refund_fee IS 'How high would the refund fee be (for this coin)';
   COMMENT ON COLUMN merchant_deposits.settlement_retry_needed IS 'True if we should ask the exchange in the future about the settlement';
   COMMENT ON COLUMN merchant_deposits.settlement_retry_time IS 'When should we next ask the exchange about the settlement wire transfer for this coin, initially set to the wire transfer deadline plus a bit of slack';
   COMMENT ON COLUMN merchant_deposits.settlement_last_http_status IS 'HTTP status of our last inquiry with the exchange for this deposit, NULL if we never inquired, 0 on timeout';
   COMMENT ON COLUMN merchant_deposits.settlement_last_ec IS 'Taler error code for our last inquiry with the exchange for this deposit, NULL if we never inquired, 0 on success';
   COMMENT ON COLUMN merchant_deposits.settlement_last_detail IS 'Taler error detail for our last inquiry with the exchange for this deposit, NULL if we never inquired or on success';
   COMMENT ON COLUMN merchant_deposits.settlement_wtid IS 'Wire transfer identifier of the transfer of the exchange to this merchant settling the payment';
   COMMENT ON COLUMN merchant_deposits.settlement_coin_contribution IS 'Contribution of this coin to the overall wire transfer made by the exchange as claimed by exchange_sig; should match amount_with_fee minus deposit_fee, NULL if we did not get a reply from the exchange';
   COMMENT ON COLUMN merchant_deposits.settlement_expected_credit_serial IS 'Identifies the expected wire transfer from the exchange to the merchant that settled the deposit of coin, NULL if unknown';
   COMMENT ON COLUMN merchant_deposits.signkey_serial IS 'Identifies the online signing key of the exchange used to make the exchange_sig, NULL for none';
   COMMENT ON COLUMN merchant_deposits.settlement_exchange_sig IS 'Exchange signature of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE, NULL if we did not get such an exchange signature';
   CREATE INDEX merchant_deposits_by_deposit_confirmation
     ON merchant_deposits (deposit_confirmation_serial);
   CREATE INDEX merchant_deposits_by_settlement_open
     ON merchant_deposits (settlement_retry_time)
     WHERE settlement_retry_needed;
   COMMENT ON INDEX merchant_deposits_by_settlement_open IS 'For select_open_deposit_settlements';
 
   CREATE TABLE merchant_inventory_locks (
     product_serial INT8 NOT NULL,
     lock_uuid BYTEA NOT NULL,
     total_locked INT8 NOT NULL,
     expiration INT8 NOT NULL,
     total_locked_frac INT4 DEFAULT 0 NOT NULL,
     CONSTRAINT merchant_inventory_locks_lock_uuid_check CHECK ((LENGTH(lock_uuid) = 16)),
     CONSTRAINT merchant_inventory_locks_product_serial_fkey
       FOREIGN KEY (product_serial)
       REFERENCES merchant_inventory(product_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_inventory_locks IS 'locks on inventory helt by shopping carts; note that locks MAY not be honored if merchants increase total_lost for inventory';
   COMMENT ON COLUMN merchant_inventory_locks.product_serial IS 'identifies the inventory product being locked';
   COMMENT ON COLUMN merchant_inventory_locks.lock_uuid IS 'unique identifier for the lock';
   COMMENT ON COLUMN merchant_inventory_locks.total_locked IS 'how many units of the product does this lock reserve';
   COMMENT ON COLUMN merchant_inventory_locks.expiration IS 'when does this lock automatically expire (if no order is created)';
   COMMENT ON COLUMN merchant_inventory_locks.total_locked_frac IS 'Fractional part of locked stock in units of 1/1000000 of the base value';
   CREATE INDEX merchant_inventory_locks_by_expiration
     ON merchant_inventory_locks (expiration);
   CREATE INDEX merchant_inventory_locks_by_uuid
     ON merchant_inventory_locks (lock_uuid);
 
   CREATE TABLE merchant_issued_tokens (
     issued_token_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     h_contract_terms BYTEA NOT NULL,
     token_family_key_serial INT8,
     blind_sig BYTEA NOT NULL,
     CONSTRAINT merchant_issued_tokens_h_contract_terms_check CHECK ((LENGTH(h_contract_terms) = 64)),
     CONSTRAINT merchant_issued_tokens_token_family_key_serial_fkey
       FOREIGN KEY (token_family_key_serial)
       REFERENCES merchant_token_family_keys(token_family_key_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_issued_tokens IS 'Tokens that have been (blindly) issued to customers.';
   COMMENT ON COLUMN merchant_issued_tokens.h_contract_terms IS 'This is no foreign key by design.';
   COMMENT ON COLUMN merchant_issued_tokens.token_family_key_serial IS 'Token family key to which the spent token belongs.';
   COMMENT ON COLUMN merchant_issued_tokens.blind_sig IS 'Blind signature made with token issue key to prove validity of token.';
 
   CREATE TABLE merchant_order_locks (
     product_serial INT8 NOT NULL,
     total_locked INT8 NOT NULL,
     order_serial INT8 NOT NULL,
     total_locked_frac INT4 DEFAULT 0 NOT NULL,
     CONSTRAINT merchant_order_locks_order_serial_fkey
       FOREIGN KEY (order_serial)
       REFERENCES merchant_orders(order_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_order_locks_product_serial_fkey
       FOREIGN KEY (product_serial)
       REFERENCES merchant_inventory(product_serial)
   );
   COMMENT ON TABLE merchant_order_locks IS 'locks on orders awaiting claim and payment; note that locks MAY not be honored if merchants increase total_lost for inventory';
   COMMENT ON COLUMN merchant_order_locks.total_locked IS 'how many units of the product does this lock reserve';
   COMMENT ON COLUMN merchant_order_locks.order_serial IS 'order for which the lock applies';
   COMMENT ON COLUMN merchant_order_locks.total_locked_frac IS 'Fractional part of locked stock associated with orders in units of 1/1000000 of the base value';
   CREATE INDEX merchant_orders_locks_by_order_and_product
     ON merchant_order_locks (order_serial, product_serial);
 
   CREATE TABLE merchant_product_categories (
     category_serial INT8 NOT NULL,
     product_serial INT8 NOT NULL,
     CONSTRAINT merchant_product_categories_category_serial_fkey
       FOREIGN KEY (category_serial)
       REFERENCES merchant_categories(category_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_product_categories_product_serial_fkey
       FOREIGN KEY (product_serial)
       REFERENCES merchant_inventory(product_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_product_categories IS 'N:M map from products to categories (a product can be in any number of categories, including zero)';
   COMMENT ON COLUMN merchant_product_categories.category_serial IS 'Reference to a category the product is part of';
   COMMENT ON COLUMN merchant_product_categories.product_serial IS 'Reference to a product which is in the given category';
   CREATE INDEX merchant_categories_by_category
     ON merchant_product_categories (category_serial);
   CREATE INDEX merchant_categories_by_product
     ON merchant_product_categories (product_serial);
 
   CREATE TABLE merchant_refund_proofs (
     refund_serial INT8 NOT NULL PRIMARY KEY,
     exchange_sig BYTEA NOT NULL,
     signkey_serial INT8 NOT NULL,
     CONSTRAINT merchant_refund_proofs_exchange_sig_check CHECK ((LENGTH(exchange_sig) = 64)),
     CONSTRAINT merchant_refund_proofs_refund_serial_fkey
       FOREIGN KEY (refund_serial)
       REFERENCES merchant_refunds(refund_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_refund_proofs_signkey_serial_fkey
       FOREIGN KEY (signkey_serial)
       REFERENCES merchant.merchant_exchange_signing_keys(signkey_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_refund_proofs IS 'Refunds confirmed by the exchange (not all approved refunds are grabbed by the wallet)';
   COMMENT ON COLUMN merchant_refund_proofs.refund_serial IS 'refund process to which this proof applies';
   COMMENT ON COLUMN merchant_refund_proofs.exchange_sig IS 'signature by the exchange confirming the refund';
   COMMENT ON COLUMN merchant_refund_proofs.signkey_serial IS 'reference to the public key of the exchange by which exchange_sig was made';
 
   CREATE TABLE merchant_used_tokens (
     spent_token_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     h_contract_terms BYTEA NOT NULL,
     token_family_key_serial INT8,
     token_pub BYTEA NOT NULL UNIQUE,
     token_sig BYTEA NOT NULL,
     blind_sig BYTEA NOT NULL,
     CONSTRAINT merchant_spent_tokens_h_contract_terms_check CHECK ((LENGTH(h_contract_terms) = 64)),
     CONSTRAINT merchant_spent_tokens_token_pub_check CHECK ((LENGTH(token_pub) = 32)),
     CONSTRAINT merchant_spent_tokens_token_sig_check CHECK ((LENGTH(token_sig) = 64)),
     CONSTRAINT merchant_spent_tokens_token_family_key_serial_fkey
       FOREIGN KEY (token_family_key_serial)
       REFERENCES merchant_token_family_keys(token_family_key_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_used_tokens IS 'Tokens that have been spent by customers.';
   COMMENT ON COLUMN merchant_used_tokens.h_contract_terms IS 'This is no foreign key by design.';
   COMMENT ON COLUMN merchant_used_tokens.token_family_key_serial IS 'Token family to which the spent token belongs.';
   COMMENT ON COLUMN merchant_used_tokens.token_pub IS 'Public key of the spent token.';
   COMMENT ON COLUMN merchant_used_tokens.token_sig IS 'Signature that the token was spent on specified order.';
   COMMENT ON COLUMN merchant_used_tokens.blind_sig IS 'Blind signature for the spent token to prove validity of token.';
 
   CREATE TABLE merchant_statistic_interval_amount (
     imeta_serial_id INT8 NOT NULL,
     event_delimiter INT8 NOT NULL,
     range INT8 NOT NULL,
     curr character varying(12) NOT NULL,
     cumulative_value INT8 NOT NULL,
     cumulative_frac INT4 NOT NULL,
     PRIMARY KEY (imeta_serial_id, curr, range),
     CONSTRAINT merchant_statistic_interval_amount_event_delimiter_fkey
       FOREIGN KEY (event_delimiter)
       REFERENCES merchant_statistic_amount_event(aevent_serial_id) ON DELETE RESTRICT,
     CONSTRAINT merchant_statistic_interval_amount_imeta_serial_id_fkey
       FOREIGN KEY (imeta_serial_id)
       REFERENCES merchant_statistic_interval_meta(imeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_interval_amount IS 'various amount statistics (in various currencies) being tracked';
   COMMENT ON COLUMN merchant_statistic_interval_amount.imeta_serial_id IS 'identifies what the statistic is about';
   COMMENT ON COLUMN merchant_statistic_interval_amount.event_delimiter IS 'FIXME';
   COMMENT ON COLUMN merchant_statistic_interval_amount.range IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges';
   COMMENT ON COLUMN merchant_statistic_interval_amount.curr IS 'currency which this statistic is tracking the amount for';
   COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_value IS 'amount in the respective currency, non-fractional amount value';
   COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_frac IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value';
 
   CREATE TABLE merchant_statistic_interval_counter (
     imeta_serial_id INT8 NOT NULL,
     range INT8 NOT NULL,
     event_delimiter INT8 NOT NULL,
     cumulative_number INT8 NOT NULL,
     PRIMARY KEY (imeta_serial_id, range),
     CONSTRAINT merchant_statistic_interval_counter_event_delimiter_fkey
       FOREIGN KEY (event_delimiter)
       REFERENCES merchant_statistic_counter_event(nevent_serial_id) ON DELETE RESTRICT,
     CONSTRAINT merchant_statistic_interval_counter_imeta_serial_id_fkey
       FOREIGN KEY (imeta_serial_id)
       REFERENCES merchant_statistic_interval_meta(imeta_serial_id) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_statistic_interval_counter IS 'various numeric statistics (cumulative counters) being tracked';
   COMMENT ON COLUMN merchant_statistic_interval_counter.imeta_serial_id IS 'identifies what the statistic is about';
   COMMENT ON COLUMN merchant_statistic_interval_counter.range IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges';
   COMMENT ON COLUMN merchant_statistic_interval_counter.event_delimiter IS 'determines the last event currently included in the interval';
   COMMENT ON COLUMN merchant_statistic_interval_counter.cumulative_number IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword';
 
   CREATE TABLE merchant_transfer_signatures (
     expected_credit_serial INT8 NOT NULL PRIMARY KEY,
     signkey_serial INT8 NOT NULL,
     wire_fee merchant.taler_amount_currency NOT NULL,
     credit_amount merchant.taler_amount_currency NOT NULL,
     execution_time INT8 NOT NULL,
     exchange_sig BYTEA NOT NULL,
     CONSTRAINT merchant_transfer_signatures_exchange_sig_check CHECK ((LENGTH(exchange_sig) = 64)),
     CONSTRAINT merchant_transfer_signatures_expected_credit_serial_fkey
       FOREIGN KEY (expected_credit_serial)
       REFERENCES merchant_expected_transfers(expected_credit_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_transfer_signatures_signkey_serial_fkey
       FOREIGN KEY (signkey_serial)
       REFERENCES merchant.merchant_exchange_signing_keys(signkey_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_transfer_signatures IS 'table represents the main information returned from the /transfer request to the exchange.';
   COMMENT ON COLUMN merchant_transfer_signatures.expected_credit_serial IS 'expected wire transfer this signature is about';
   COMMENT ON COLUMN merchant_transfer_signatures.signkey_serial IS 'Online signing key by the exchange that was used for the exchange_sig signature';
   COMMENT ON COLUMN merchant_transfer_signatures.wire_fee IS 'wire fee charged by the exchange for this transfer';
   COMMENT ON COLUMN merchant_transfer_signatures.credit_amount IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange';
   COMMENT ON COLUMN merchant_transfer_signatures.execution_time IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant';
   COMMENT ON COLUMN merchant_transfer_signatures.exchange_sig IS 'signature by the exchange of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE_DEPOSIT';
 
   -- ===================================================================
   -- Tier 3: tables referencing Tier 2 tables.
   -- ===================================================================
 
   CREATE TABLE merchant_expected_transfer_to_coin (
     deposit_serial INT8 NOT NULL UNIQUE,
     expected_credit_serial INT8 NOT NULL,
     offset_in_exchange_list INT8 NOT NULL,
     exchange_deposit_value merchant.taler_amount_currency NOT NULL,
     exchange_deposit_fee merchant.taler_amount_currency NOT NULL,
     CONSTRAINT merchant_expected_transfer_to_coin_deposit_serial_fkey
       FOREIGN KEY (deposit_serial)
       REFERENCES merchant_deposits(deposit_serial) ON DELETE CASCADE,
     CONSTRAINT merchant_expected_transfer_to_coin_expected_credit_serial_fkey
       FOREIGN KEY (expected_credit_serial)
       REFERENCES merchant_expected_transfers(expected_credit_serial) ON DELETE CASCADE
   );
   COMMENT ON TABLE merchant_expected_transfer_to_coin IS 'Mapping of (credit) transfers to (deposited) coins';
   COMMENT ON COLUMN merchant_expected_transfer_to_coin.deposit_serial IS 'Identifies the deposited coin that the wire transfer presumably settles';
   COMMENT ON COLUMN merchant_expected_transfer_to_coin.expected_credit_serial IS 'Identifies the expected wire transfer that settles the given deposited coin';
   COMMENT ON COLUMN merchant_expected_transfer_to_coin.offset_in_exchange_list IS 'The exchange settlement data includes an array of the settled coins; this is the index of the coin in that list, useful to reconstruct the correct sequence of coins as needed to check the exchange signature';
   COMMENT ON COLUMN merchant_expected_transfer_to_coin.exchange_deposit_value IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds';
   COMMENT ON COLUMN merchant_expected_transfer_to_coin.exchange_deposit_fee IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits';
   CREATE INDEX merchant_transfers_by_credit
     ON merchant_expected_transfer_to_coin (expected_credit_serial);
 
   SET LOCAL search_path TO merchant;
 END
 $OUTER$;
 
 INSERT INTO merchant.instance_fixups
   (migration_name
   ,version)
   VALUES
   ('merchant_0036_init'
   ,36);