merchant-0001.sql (42416B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2023 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- @file merchant-0001.sql 18 -- @brief database schema for the merchant 19 -- @author Christian Grothoff 20 -- @author Priscilla Huang 21 22 -- Everything in one big transaction 23 BEGIN; 24 25 -- Check patch versioning is in place. 26 SELECT _v.register_patch('merchant-0001', NULL, NULL); 27 28 CREATE SCHEMA merchant; 29 COMMENT ON SCHEMA merchant IS 'taler-merchant data'; 30 31 SET search_path TO merchant; 32 33 CREATE TYPE taler_amount_currency 34 AS 35 (val INT8 36 ,frac INT4 37 ,curr VARCHAR(12) 38 ); 39 COMMENT ON TYPE taler_amount_currency 40 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 41 42 ---------------- Exchange information --------------------------- 43 44 CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees 45 (wirefee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 46 ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) 47 ,h_wire_method BYTEA NOT NULL CHECK (LENGTH(h_wire_method)=64) 48 ,start_date INT8 NOT NULL 49 ,end_date INT8 NOT NULL 50 ,wire_fee taler_amount_currency NOT NULL 51 ,closing_fee taler_amount_currency NOT NULL 52 ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) 53 ,UNIQUE (master_pub,h_wire_method,start_date) 54 ); 55 COMMENT ON TABLE merchant_exchange_wire_fees 56 IS 'Here we store proofs of the wire fee structure of the various exchanges'; 57 COMMENT ON COLUMN merchant_exchange_wire_fees.master_pub 58 IS 'Master public key of the exchange with these wire fees'; 59 60 CREATE TABLE IF NOT EXISTS merchant_exchange_signing_keys 61 (signkey_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 62 ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) 63 ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32) 64 ,start_date INT8 NOT NULL 65 ,expire_date INT8 NOT NULL 66 ,end_date INT8 NOT NULL 67 ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64), 68 UNIQUE (exchange_pub, start_date, master_pub) 69 ); 70 COMMENT ON TABLE merchant_exchange_signing_keys 71 IS 'Here we store proofs of the exchange online signing keys being signed by the exchange master key'; 72 COMMENT ON COLUMN merchant_exchange_signing_keys.master_pub 73 IS 'Master public key of the exchange with these online signing keys'; 74 75 76 -------------------------- Instances --------------------------- 77 78 CREATE TABLE IF NOT EXISTS merchant_instances 79 (merchant_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 80 ,merchant_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_pub)=32) 81 ,auth_hash BYTEA CHECK(LENGTH(auth_hash)=64) 82 ,auth_salt BYTEA CHECK(LENGTH(auth_salt)=32) 83 ,merchant_id TEXT NOT NULL UNIQUE 84 ,merchant_name TEXT NOT NULL 85 ,website TEXT 86 ,email TEXT 87 ,logo BYTEA 88 ,address BYTEA NOT NULL 89 ,jurisdiction BYTEA NOT NULL 90 ,use_stefan BOOLEAN NOT NULL DEFAULT TRUE 91 ,default_wire_transfer_delay INT8 NOT NULL 92 ,default_pay_delay INT8 NOT NULL 93 ,user_type INT4 94 ); 95 COMMENT ON TABLE merchant_instances 96 IS 'all the instances supported by this backend'; 97 COMMENT ON COLUMN merchant_instances.merchant_id 98 IS 'identifier of the merchant as used in the base URL (required)'; 99 COMMENT ON COLUMN merchant_instances.merchant_name 100 IS 'legal name of the merchant as a simple string (required)'; 101 COMMENT ON COLUMN merchant_instances.address 102 IS 'physical address of the merchant as a location in JSON format (required)'; 103 COMMENT ON COLUMN merchant_instances.jurisdiction 104 IS 'jurisdiction of the merchant as a location in JSON format (required)'; 105 COMMENT ON COLUMN merchant_instances.website 106 IS 'merchant site URL'; 107 COMMENT ON COLUMN merchant_instances.use_stefan 108 IS 'use STEFAN curve of exchange to determine acceptable fees (unless given explicitly)'; 109 COMMENT ON COLUMN merchant_instances.email 110 IS 'email'; 111 COMMENT ON COLUMN merchant_instances.logo 112 IS 'data image url'; 113 COMMENT ON COLUMN merchant_instances.auth_hash 114 IS 'hash used for merchant back office authorization, NULL for no check'; 115 COMMENT ON COLUMN merchant_instances.auth_salt 116 IS 'salt to use when hashing Authorization header before comparing with auth_hash'; 117 COMMENT ON COLUMN merchant_instances.user_type 118 IS 'what type of user is this (individual or business)'; 119 120 121 CREATE TABLE IF NOT EXISTS merchant_login_tokens 122 (token BYTEA NOT NULL UNIQUE CHECK (LENGTH(token)=32), 123 creation_time INT8 NOT NULL, 124 expiration_time INT8 NOT NULL, 125 validity_scope INT4 NOT NULL, 126 merchant_serial BIGINT 127 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 128 ); 129 COMMENT ON TABLE merchant_login_tokens 130 IS 'login tokens that have been created for the given instance'; 131 COMMENT ON COLUMN merchant_login_tokens.token 132 IS 'binary value of the login token'; 133 COMMENT ON COLUMN merchant_login_tokens.creation_time 134 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'; 135 COMMENT ON COLUMN merchant_login_tokens.expiration_time 136 IS 'determines when the token expires'; 137 COMMENT ON COLUMN merchant_login_tokens.validity_scope 138 IS 'identifies the operations for which the token is valid'; 139 COMMENT ON COLUMN merchant_login_tokens.merchant_serial 140 IS 'identifies the instance for which the token is valid'; 141 142 CREATE INDEX IF NOT EXISTS merchant_login_tokens_by_expiration 143 ON merchant_login_tokens 144 (expiration_time); 145 146 147 CREATE TABLE IF NOT EXISTS merchant_keys 148 (merchant_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_priv)=32), 149 merchant_serial BIGINT PRIMARY KEY 150 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 151 ); 152 COMMENT ON TABLE merchant_keys 153 IS 'private keys of instances that have not been deleted'; 154 155 CREATE TABLE IF NOT EXISTS merchant_accounts 156 (account_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 157 ,merchant_serial BIGINT NOT NULL 158 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 159 ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) 160 ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=16) 161 ,credit_facade_url TEXT 162 ,credit_facade_credentials TEXT 163 ,last_bank_serial INT8 NOT NULL DEFAULT (0) 164 ,payto_uri TEXT NOT NULL 165 ,active BOOLEAN NOT NULL 166 ,UNIQUE (merchant_serial,payto_uri) 167 ,UNIQUE (h_wire) 168 ); 169 COMMENT ON TABLE merchant_accounts 170 IS 'bank accounts of the instances'; 171 COMMENT ON COLUMN merchant_accounts.h_wire 172 IS 'salted hash of payto_uri'; 173 COMMENT ON COLUMN merchant_accounts.salt 174 IS 'salt used when hashing payto_uri into h_wire'; 175 COMMENT ON COLUMN merchant_accounts.payto_uri 176 IS 'payto URI of a merchant bank account'; 177 COMMENT ON COLUMN merchant_accounts.active 178 IS 'true if we actively use this bank account, false if it is just kept around for older contracts to refer to'; 179 COMMENT ON COLUMN merchant_accounts.credit_facade_url 180 IS 'Base URL of a facade where the merchant can inquire about incoming bank transactions into this account'; 181 COMMENT ON COLUMN merchant_accounts.credit_facade_credentials 182 IS 'JSON with credentials needed to access the credit facade'; 183 COMMENT ON COLUMN merchant_accounts.last_bank_serial 184 IS 'Serial number of the bank of the last transaction we successfully imported'; 185 COMMENT ON COLUMN merchant_accounts.account_serial 186 IS 'Unique identifier for this account, used to reference this account from other tables'; 187 COMMENT ON COLUMN merchant_accounts.merchant_serial 188 IS 'Merchant instance that controls this bank account'; 189 190 191 -------------------------- Inventory --------------------------- 192 193 CREATE TABLE IF NOT EXISTS merchant_inventory 194 (product_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 195 ,merchant_serial BIGINT NOT NULL 196 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 197 ,product_id TEXT NOT NULL 198 ,description TEXT NOT NULL 199 ,description_i18n BYTEA NOT NULL 200 ,unit TEXT NOT NULL 201 ,image BYTEA NOT NULL -- NOTE: merchant-0006 changes this to TEXT! 202 ,taxes BYTEA NOT NULL 203 ,price taler_amount_currency NOT NULL 204 ,total_stock BIGINT NOT NULL 205 ,total_sold BIGINT NOT NULL DEFAULT 0 206 ,total_lost BIGINT NOT NULL DEFAULT 0 207 ,address BYTEA NOT NULL 208 ,next_restock INT8 NOT NULL 209 ,minimum_age INT4 NOT NULL DEFAULT 0 210 ,UNIQUE (merchant_serial, product_id) 211 ); 212 COMMENT ON TABLE merchant_inventory 213 IS 'products offered by the merchant (may be incomplete, frontend can override)'; 214 COMMENT ON COLUMN merchant_inventory.description 215 IS 'Human-readable product description'; 216 COMMENT ON COLUMN merchant_inventory.description_i18n 217 IS 'JSON map from IETF BCP 47 language tags to localized descriptions'; 218 COMMENT ON COLUMN merchant_inventory.unit 219 IS 'Unit of sale for the product (liters, kilograms, packages)'; 220 COMMENT ON COLUMN merchant_inventory.image 221 IS 'NOT NULL, but can be 0 bytes; must contain an ImageDataUrl'; 222 COMMENT ON COLUMN merchant_inventory.taxes 223 IS 'JSON array containing taxes the merchant pays, must be JSON, but can be just "[]"'; 224 COMMENT ON COLUMN merchant_inventory.price 225 IS 'Current price of one unit of the product'; 226 COMMENT ON COLUMN merchant_inventory.total_stock 227 IS 'A value of -1 is used for unlimited (electronic good), may never be lowered'; 228 COMMENT ON COLUMN merchant_inventory.total_sold 229 IS 'Number of products sold, must be below total_stock, non-negative, may never be lowered'; 230 COMMENT ON COLUMN merchant_inventory.total_lost 231 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'; 232 COMMENT ON COLUMN merchant_inventory.address 233 IS 'JSON formatted Location of where the product is stocked'; 234 COMMENT ON COLUMN merchant_inventory.next_restock 235 IS 'GNUnet absolute time i ndicating when the next restock is expected. 0 for unknown.'; 236 COMMENT ON COLUMN merchant_inventory.minimum_age 237 IS 'Minimum age of the customer in years, to be used if an exchange supports the age restriction extension.'; 238 239 240 CREATE TABLE IF NOT EXISTS merchant_inventory_locks 241 (product_serial BIGINT NOT NULL 242 REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion! 243 ,lock_uuid BYTEA NOT NULL CHECK (LENGTH(lock_uuid)=16) 244 ,total_locked BIGINT NOT NULL 245 ,expiration INT8 NOT NULL 246 ); 247 CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_expiration 248 ON merchant_inventory_locks 249 (expiration); 250 CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_uuid 251 ON merchant_inventory_locks 252 (lock_uuid); 253 COMMENT ON TABLE merchant_inventory_locks 254 IS 'locks on inventory helt by shopping carts; note that locks MAY not be honored if merchants increase total_lost for inventory'; 255 COMMENT ON COLUMN merchant_inventory_locks.total_locked 256 IS 'how many units of the product does this lock reserve'; 257 COMMENT ON COLUMN merchant_inventory_locks.expiration 258 IS 'when does this lock automatically expire (if no order is created)'; 259 260 261 ---------------- Orders and contracts --------------------------- 262 263 CREATE TABLE IF NOT EXISTS merchant_orders 264 (order_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 265 ,merchant_serial BIGINT NOT NULL 266 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 267 ,order_id TEXT NOT NULL 268 ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16) 269 ,h_post_data BYTEA NOT NULL CHECK (LENGTH(h_post_data)=64) 270 ,pay_deadline INT8 NOT NULL 271 ,creation_time INT8 NOT NULL 272 ,contract_terms BYTEA NOT NULL 273 ,pos_key TEXT DEFAULT NULL 274 ,pos_algorithm INT NOT NULL DEFAULT (0) 275 ,UNIQUE (merchant_serial, order_id) 276 ); 277 COMMENT ON TABLE merchant_orders 278 IS 'Orders we offered to a customer, but that have not yet been claimed'; 279 COMMENT ON COLUMN merchant_orders.contract_terms 280 IS 'Claiming changes the contract_terms, hence we have no hash of the terms in this table'; 281 COMMENT ON COLUMN merchant_orders.h_post_data 282 IS 'Hash of the POST request that created this order, for idempotency checks'; 283 COMMENT ON COLUMN merchant_orders.claim_token 284 IS 'Token optionally used to authorize the wallet to claim the order. All zeros (not NULL) if not used'; 285 COMMENT ON COLUMN merchant_orders.merchant_serial 286 IS 'Identifies the instance offering the contract'; 287 COMMENT ON COLUMN merchant_orders.pay_deadline 288 IS 'How long is the offer valid. After this time, the order can be garbage collected'; 289 COMMENT ON COLUMN merchant_orders.pos_key 290 IS 'encoded based key which is used for the verification of payment'; 291 COMMENT ON COLUMN merchant_orders.pos_algorithm 292 IS 'algorithm to used to generate the confirmation code. It is link with the pos_key'; 293 294 295 CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration 296 ON merchant_orders 297 (pay_deadline); 298 CREATE INDEX IF NOT EXISTS merchant_orders_by_creation_time 299 ON merchant_orders 300 (creation_time); 301 302 CREATE TABLE IF NOT EXISTS merchant_order_locks 303 (product_serial BIGINT NOT NULL 304 REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion! 305 ,total_locked BIGINT NOT NULL 306 ,order_serial BIGINT NOT NULL 307 REFERENCES merchant_orders (order_serial) ON DELETE CASCADE 308 ); 309 CREATE INDEX IF NOT EXISTS merchant_orders_locks_by_order_and_product 310 ON merchant_order_locks 311 (order_serial, product_serial); 312 COMMENT ON TABLE merchant_order_locks 313 IS 'locks on orders awaiting claim and payment; note that locks MAY not be honored if merchants increase total_lost for inventory'; 314 COMMENT ON COLUMN merchant_order_locks.total_locked 315 IS 'how many units of the product does this lock reserve'; 316 317 CREATE TABLE IF NOT EXISTS merchant_contract_terms 318 (order_serial BIGINT PRIMARY KEY 319 ,merchant_serial BIGINT NOT NULL 320 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 321 ,order_id TEXT NOT NULL 322 ,contract_terms BYTEA NOT NULL 323 ,wallet_data TEXT DEFAULT NULL 324 ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) 325 ,creation_time INT8 NOT NULL 326 ,pay_deadline INT8 NOT NULL 327 ,refund_deadline INT8 NOT NULL 328 ,paid BOOLEAN DEFAULT FALSE NOT NULL 329 ,wired BOOLEAN DEFAULT FALSE NOT NULL 330 ,fulfillment_url TEXT 331 ,session_id TEXT DEFAULT '' NOT NULL 332 ,pos_key TEXT DEFAULT NULL 333 ,pos_algorithm INT NOT NULL DEFAULT (0) 334 ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16) 335 ,UNIQUE (merchant_serial, order_id) 336 ,UNIQUE (merchant_serial, h_contract_terms) 337 ); 338 COMMENT ON TABLE merchant_contract_terms 339 IS 'Contracts are orders that have been claimed by a wallet'; 340 COMMENT ON COLUMN merchant_contract_terms.order_id 341 IS 'Not a foreign key into merchant_orders because paid contracts persist after expiration'; 342 COMMENT ON COLUMN merchant_contract_terms.merchant_serial 343 IS 'Identifies the instance offering the contract'; 344 COMMENT ON COLUMN merchant_contract_terms.contract_terms 345 IS 'These contract terms include the wallet nonce'; 346 COMMENT ON COLUMN merchant_contract_terms.wallet_data 347 IS 'Data provided by the wallet when paying for the contract (subcontract selection, blinded tokens, etc.)'; 348 COMMENT ON COLUMN merchant_contract_terms.h_contract_terms 349 IS 'Hash over contract_terms'; 350 COMMENT ON COLUMN merchant_contract_terms.pay_deadline 351 IS 'How long is the offer valid. After this time, the order can be garbage collected'; 352 COMMENT ON COLUMN merchant_contract_terms.refund_deadline 353 IS 'By what times do refunds have to be approved (useful to reject refund requests)'; 354 COMMENT ON COLUMN merchant_contract_terms.paid 355 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'; 356 COMMENT ON COLUMN merchant_contract_terms.wired 357 IS 'true implies the exchange wired us the full amount for all non-refunded payments under this contract'; 358 COMMENT ON COLUMN merchant_contract_terms.fulfillment_url 359 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'; 360 COMMENT ON COLUMN merchant_contract_terms.session_id 361 IS 'last session_id from we confirmed the paying client to use, empty string for none'; 362 COMMENT ON COLUMN merchant_contract_terms.pay_deadline 363 IS 'How long is the offer valid. After this time, the order can be garbage collected'; 364 COMMENT ON COLUMN merchant_contract_terms.pos_key 365 IS 'enconded based key which is used for the verification of payment'; 366 COMMENT ON COLUMN merchant_orders.pos_algorithm 367 IS 'algorithm to used to generate the confirmation code. It is link with the pos_key'; 368 369 COMMENT ON COLUMN merchant_contract_terms.claim_token 370 IS 'Token optionally used to access the status of the order. All zeros (not NULL) if not used'; 371 372 CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_expiration 373 ON merchant_contract_terms 374 (merchant_serial,pay_deadline); 375 COMMENT ON INDEX merchant_contract_terms_by_merchant_and_expiration 376 IS 'for delete_contract_terms'; 377 CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_expiration 378 ON merchant_contract_terms 379 (paid,pay_deadline); 380 COMMENT ON INDEX merchant_contract_terms_by_expiration 381 IS 'for unlock_contracts'; 382 CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_payment 383 ON merchant_contract_terms 384 (merchant_serial,paid); 385 CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_session_and_fulfillment 386 ON merchant_contract_terms 387 (merchant_serial,fulfillment_url,session_id); 388 389 390 ---------------- Payment and refunds --------------------------- 391 392 CREATE TABLE IF NOT EXISTS merchant_deposit_confirmations 393 (deposit_confirmation_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 394 ,order_serial BIGINT 395 REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE 396 ,deposit_timestamp INT8 NOT NULL 397 ,exchange_url TEXT NOT NULL 398 ,total_without_fee taler_amount_currency NOT NULL 399 ,wire_fee taler_amount_currency NOT NULL 400 ,signkey_serial BIGINT NOT NULL 401 REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE 402 ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) 403 ,account_serial BIGINT NOT NULL 404 REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE 405 ,UNIQUE (order_serial, exchange_sig) 406 ); 407 COMMENT ON TABLE merchant_deposit_confirmations 408 IS 'Table with the deposit confirmations for each coin we deposited at the exchange'; 409 COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial 410 IS 'Online signing key of the exchange on the deposit confirmation'; 411 COMMENT ON COLUMN merchant_deposit_confirmations.account_serial 412 IS 'Identifies the bank account of the merchant that will receive the payment'; 413 COMMENT ON COLUMN merchant_deposit_confirmations.exchange_url 414 IS 'Which exchange received the deposit; a single contract may be paid by multiple (batch) deposits to multiple exchanges'; 415 COMMENT ON COLUMN merchant_deposit_confirmations.order_serial 416 IS 'Identifies the entry in the contract_terms table that was paid by this deposit'; 417 COMMENT ON COLUMN merchant_deposit_confirmations.total_without_fee 418 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)'; 419 COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp 420 IS 'Time when the exchange generated the deposit confirmation'; 421 COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig 422 IS 'Signature of the exchange over the deposit confirmation'; 423 COMMENT ON COLUMN merchant_deposit_confirmations.wire_fee 424 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.)'; 425 426 427 CREATE TABLE IF NOT EXISTS merchant_deposits 428 (deposit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 429 ,coin_offset INT4 NOT NULL 430 ,deposit_confirmation_serial BIGINT NOT NULL 431 REFERENCES merchant_deposit_confirmations (deposit_confirmation_serial) ON DELETE CASCADE 432 ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) 433 ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) 434 ,amount_with_fee taler_amount_currency NOT NULL 435 ,deposit_fee taler_amount_currency NOT NULL 436 ,refund_fee taler_amount_currency NOT NULL 437 ,UNIQUE (deposit_confirmation_serial, coin_pub) 438 ); 439 COMMENT ON TABLE merchant_deposits 440 IS 'Table with the deposit details for each coin we deposited at the exchange'; 441 COMMENT ON COLUMN merchant_deposits.coin_offset 442 IS 'Offset of this coin in the batch'; 443 COMMENT ON COLUMN merchant_deposits.deposit_confirmation_serial 444 IS 'Reference to the deposit confirmation of the exchange'; 445 COMMENT ON COLUMN merchant_deposits.coin_pub 446 IS 'Public key of the coin that was deposited'; 447 COMMENT ON COLUMN merchant_deposits.amount_with_fee 448 IS 'Total amount (incl. fee) of the coin that was deposited'; 449 COMMENT ON COLUMN merchant_deposits.deposit_fee 450 IS 'Deposit fee (for this coin) that was paid'; 451 COMMENT ON COLUMN merchant_deposits.refund_fee 452 IS 'How high would the refund fee be (for this coin)'; 453 454 455 CREATE TABLE IF NOT EXISTS merchant_refunds 456 (refund_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 457 ,order_serial BIGINT NOT NULL 458 REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE 459 ,rtransaction_id BIGINT NOT NULL 460 ,refund_timestamp INT8 NOT NULL 461 ,coin_pub BYTEA NOT NULL 462 ,reason TEXT NOT NULL 463 ,refund_amount taler_amount_currency NOT NULL 464 ,UNIQUE (order_serial, coin_pub, rtransaction_id) 465 ); 466 COMMENT ON TABLE merchant_refunds 467 IS 'Refunds approved by the merchant (backoffice) logic, excludes abort refunds'; 468 COMMENT ON COLUMN merchant_refunds.rtransaction_id 469 IS 'Needed for uniqueness in case a refund is increased for the same order'; 470 COMMENT ON COLUMN merchant_refunds.refund_timestamp 471 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'; 472 CREATE INDEX IF NOT EXISTS merchant_refunds_by_coin_and_order 473 ON merchant_refunds 474 (coin_pub,order_serial); 475 476 CREATE TABLE IF NOT EXISTS merchant_refund_proofs 477 (refund_serial BIGINT PRIMARY KEY 478 REFERENCES merchant_refunds (refund_serial) ON DELETE CASCADE 479 ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) 480 ,signkey_serial BIGINT NOT NULL 481 REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE 482 ); 483 COMMENT ON TABLE merchant_refund_proofs 484 IS 'Refunds confirmed by the exchange (not all approved refunds are grabbed by the wallet)'; 485 486 -------------------- Wire transfers --------------------------- 487 488 CREATE TABLE IF NOT EXISTS merchant_transfers 489 (credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 490 ,exchange_url TEXT NOT NULL 491 ,wtid BYTEA CHECK (LENGTH(wtid)=32) 492 ,credit_amount taler_amount_currency NOT NULL 493 ,account_serial INT8 NOT NULL 494 REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE 495 ,ready_time INT8 NOT NULL DEFAULT (0) 496 ,validation_status INT4 DEFAULT NULL 497 ,failed BOOLEAN NOT NULL DEFAULT FALSE 498 ,verified BOOLEAN NOT NULL DEFAULT FALSE 499 ,confirmed BOOLEAN NOT NULL DEFAULT FALSE 500 ,UNIQUE (wtid, exchange_url, account_serial) 501 ); 502 COMMENT ON TABLE merchant_transfers 503 IS 'table represents the information provided by the (trusted) merchant about incoming wire transfers'; 504 COMMENT ON COLUMN merchant_transfers.credit_serial 505 IS 'Unique identifier for this wire transfer in this backend'; 506 COMMENT ON COLUMN merchant_transfers.account_serial 507 IS 'Merchant bank account that received this wire transfer; also implies the merchant instance implicated by the wire transfer'; 508 COMMENT ON COLUMN merchant_transfers.ready_time 509 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'; 510 COMMENT ON COLUMN merchant_transfers.exchange_url 511 IS 'Base URL of the exchange that originated the wire transfer as extracted from the wire transfer subject'; 512 COMMENT ON COLUMN merchant_transfers.wtid 513 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'; 514 COMMENT ON COLUMN merchant_transfers.verified 515 IS 'true once we got an acceptable response from the exchange for this transfer'; 516 COMMENT ON COLUMN merchant_transfers.confirmed 517 IS 'true once the merchant confirmed that this transfer was received'; 518 COMMENT ON COLUMN merchant_transfers.credit_amount 519 IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the merchant'; 520 COMMENT ON COLUMN merchant_transfers.failed 521 IS 'set to true on permanent verification failures'; 522 COMMENT ON COLUMN merchant_transfers.validation_status 523 IS 'Taler error code describing the state of the validation'; 524 525 CREATE INDEX merchant_transfers_by_open 526 ON merchant_transfers 527 (ready_time ASC) 528 WHERE confirmed AND NOT (failed OR verified); 529 COMMENT ON INDEX merchant_transfers_by_open 530 IS 'For select_open_transfers'; 531 532 533 CREATE TABLE IF NOT EXISTS merchant_transfer_signatures 534 (credit_serial BIGINT PRIMARY KEY 535 REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE 536 ,signkey_serial BIGINT NOT NULL 537 REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE 538 ,wire_fee taler_amount_currency NOT NULL 539 ,credit_amount taler_amount_currency NOT NULL 540 ,execution_time INT8 NOT NULL 541 ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) 542 ); 543 COMMENT ON TABLE merchant_transfer_signatures 544 IS 'table represents the main information returned from the /transfer request to the exchange.'; 545 COMMENT ON COLUMN merchant_transfer_signatures.credit_serial 546 IS 'wire transfer this signature is about'; 547 COMMENT ON COLUMN merchant_transfer_signatures.signkey_serial 548 IS 'Online signing key by the exchange that was used for the exchange_sig signature'; 549 COMMENT ON COLUMN merchant_transfer_signatures.wire_fee 550 IS 'wire fee charged by the exchange for this transfer'; 551 COMMENT ON COLUMN merchant_transfer_signatures.exchange_sig 552 IS 'signature by the exchange of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE_DEPOSIT'; 553 COMMENT ON COLUMN merchant_transfer_signatures.execution_time 554 IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; 555 COMMENT ON COLUMN merchant_transfer_signatures.credit_amount 556 IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange'; 557 558 559 CREATE TABLE IF NOT EXISTS merchant_transfer_to_coin 560 (deposit_serial BIGINT UNIQUE NOT NULL 561 REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE 562 ,credit_serial BIGINT NOT NULL 563 REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE 564 ,offset_in_exchange_list INT8 NOT NULL 565 ,exchange_deposit_value taler_amount_currency NOT NULL 566 ,exchange_deposit_fee taler_amount_currency NOT NULL 567 ); 568 CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit 569 ON merchant_transfer_to_coin 570 (credit_serial); 571 COMMENT ON TABLE merchant_transfer_to_coin 572 IS 'Mapping of (credit) transfers to (deposited) coins'; 573 COMMENT ON COLUMN merchant_transfer_to_coin.deposit_serial 574 IS 'Identifies the deposited coin that the wire transfer presumably settles'; 575 COMMENT ON COLUMN merchant_transfer_to_coin.credit_serial 576 IS 'Identifies the wire transfer that settles the given deposited coin'; 577 COMMENT ON COLUMN merchant_transfer_to_coin.offset_in_exchange_list 578 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'; 579 COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value 580 IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds'; 581 COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee 582 IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits'; 583 584 CREATE TABLE IF NOT EXISTS merchant_deposit_to_transfer 585 (deposit_serial BIGINT NOT NULL 586 REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE 587 ,coin_contribution_value taler_amount_currency NOT NULL 588 ,credit_serial BIGINT NOT NULL 589 REFERENCES merchant_transfers (credit_serial) 590 ,execution_time INT8 NOT NULL 591 ,signkey_serial BIGINT NOT NULL 592 REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE 593 ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) 594 ,UNIQUE(deposit_serial,credit_serial) 595 ); 596 COMMENT ON TABLE merchant_deposit_to_transfer 597 IS 'Mapping of deposits to (possibly unconfirmed) wire transfers; note: currently taler-merchant-depositcheck only INSERTs into this table, but we never use it'; 598 COMMENT ON COLUMN merchant_deposit_to_transfer.deposit_serial 599 IS 'identifies the deposited coin for which we inquired about the wire transfer status at the exchange'; 600 COMMENT ON COLUMN merchant_deposit_to_transfer.coin_contribution_value 601 IS 'Contribution of this coin to the overall wire transfer made by the exchange'; 602 COMMENT ON COLUMN merchant_deposit_to_transfer.credit_serial 603 IS 'Identifies the wire transfer from the exchange to the merchant that settled the deposit of coin'; 604 COMMENT ON COLUMN merchant_deposit_to_transfer.execution_time 605 IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; 606 COMMENT ON COLUMN merchant_deposit_to_transfer.signkey_serial 607 IS 'Identifies the online signing key of the exchange used to make the exchange_sig'; 608 COMMENT ON COLUMN merchant_deposit_to_transfer.exchange_sig 609 IS 'Exchange signature of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE'; 610 611 612 -------------------------- Rewards --------------------------- 613 614 CREATE TABLE IF NOT EXISTS merchant_reward_reserves 615 (reserve_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 616 ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32) 617 ,merchant_serial BIGINT NOT NULL 618 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 619 ,creation_time INT8 NOT NULL 620 ,expiration INT8 NOT NULL 621 ,merchant_initial_balance taler_amount_currency NOT NULL 622 ,exchange_initial_balance taler_amount_currency NOT NULL 623 ,rewards_committed taler_amount_currency NOT NULL 624 ,rewards_picked_up taler_amount_currency NOT NULL 625 ); 626 COMMENT ON TABLE merchant_reward_reserves 627 IS 'balances of the reserves available for rewards'; 628 COMMENT ON COLUMN merchant_reward_reserves.expiration 629 IS 'FIXME: EXCHANGE API needs to tell us when reserves close if we are to compute this'; 630 COMMENT ON COLUMN merchant_reward_reserves.merchant_initial_balance 631 IS 'Set to the initial balance the merchant told us when creating the reserve'; 632 COMMENT ON COLUMN merchant_reward_reserves.exchange_initial_balance 633 IS 'Set to the initial balance the exchange told us when we queried the reserve status'; 634 COMMENT ON COLUMN merchant_reward_reserves.rewards_committed 635 IS 'Amount of outstanding approved rewards that have not been picked up'; 636 COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up 637 IS 'Total amount rewards that have been picked up from this reserve'; 638 639 CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_reserve_pub_and_merchant_serial 640 ON merchant_reward_reserves 641 (reserve_pub,merchant_serial,creation_time); 642 CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_merchant_serial_and_creation_time 643 ON merchant_reward_reserves 644 (merchant_serial,creation_time); 645 CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance 646 ON merchant_reward_reserves 647 (exchange_initial_balance); 648 649 650 651 CREATE TABLE IF NOT EXISTS merchant_reward_reserve_keys 652 (reserve_serial BIGINT NOT NULL UNIQUE 653 REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE 654 ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32) 655 ,exchange_url TEXT NOT NULL 656 ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) 657 ); 658 COMMENT ON TABLE merchant_reward_reserves 659 IS 'private keys of reserves that have not been deleted'; 660 COMMENT ON COLUMN merchant_reward_reserve_keys.master_pub 661 IS 'Master public key of the exchange to which the reserve belongs'; 662 663 664 CREATE TABLE IF NOT EXISTS merchant_rewards 665 (reward_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 666 ,reserve_serial BIGINT NOT NULL 667 REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE 668 ,reward_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(reward_id)=64) 669 ,justification TEXT NOT NULL 670 ,next_url TEXT NOT NULL 671 ,expiration INT8 NOT NULL 672 ,amount taler_amount_currency NOT NULL 673 ,picked_up taler_amount_currency NOT NULL 674 ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE 675 ); 676 CREATE INDEX IF NOT EXISTS merchant_rewards_by_pickup_and_expiration 677 ON merchant_rewards 678 (was_picked_up,expiration); 679 COMMENT ON TABLE merchant_rewards 680 IS 'rewards that have been authorized'; 681 COMMENT ON COLUMN merchant_rewards.amount 682 IS 'Overall reward amount'; 683 COMMENT ON COLUMN merchant_rewards.picked_up 684 IS 'Reward amount left to be picked up'; 685 COMMENT ON COLUMN merchant_rewards.reserve_serial 686 IS 'Reserve from which this reward is funded'; 687 COMMENT ON COLUMN merchant_rewards.expiration 688 IS 'by when does the client have to pick up the reward'; 689 690 CREATE TABLE IF NOT EXISTS merchant_reward_pickups 691 (pickup_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL 692 ,reward_serial BIGINT NOT NULL 693 REFERENCES merchant_rewards (reward_serial) ON DELETE CASCADE 694 ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64) 695 ,amount taler_amount_currency NOT NULL 696 ); 697 COMMENT ON TABLE merchant_reward_pickups 698 IS 'rewards that have been picked up'; 699 COMMENT ON COLUMN merchant_rewards.amount 700 IS 'total transaction cost for all coins including withdraw fees'; 701 702 CREATE TABLE IF NOT EXISTS merchant_reward_pickup_signatures 703 (pickup_serial INT8 NOT NULL 704 REFERENCES merchant_reward_pickups (pickup_serial) ON DELETE CASCADE 705 ,coin_offset INT4 NOT NULL 706 ,blind_sig BYTEA NOT NULL 707 ,PRIMARY KEY (pickup_serial, coin_offset) 708 ); 709 COMMENT ON TABLE merchant_reward_pickup_signatures 710 IS 'blind signatures we got from the exchange during the reward pickup'; 711 712 713 714 715 CREATE TABLE IF NOT EXISTS merchant_kyc 716 (kyc_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE 717 ,kyc_timestamp INT8 NOT NULL 718 ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE) 719 ,aml_decision INT4 NOT NULL DEFAULT (0) 720 ,exchange_sig BYTEA CHECK(LENGTH(exchange_sig)=64) 721 ,exchange_pub BYTEA CHECK(LENGTH(exchange_pub)=32) 722 ,exchange_kyc_serial INT8 NOT NULL DEFAULT(0) 723 ,account_serial INT8 NOT NULL 724 REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE 725 ,exchange_url TEXT NOT NULL 726 ,PRIMARY KEY (account_serial,exchange_url) 727 ); 728 COMMENT ON TABLE merchant_kyc 729 IS 'Status of the KYC process of a merchant account at an exchange'; 730 COMMENT ON COLUMN merchant_kyc.kyc_timestamp 731 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).'; 732 COMMENT ON COLUMN merchant_kyc.exchange_kyc_serial 733 IS 'Number to use in the KYC-endpoints of the exchange to check the KYC status or begin the KYC process. 0 if we do not know it yet.'; 734 COMMENT ON COLUMN merchant_kyc.kyc_ok 735 IS 'true if the KYC check was passed successfully'; 736 COMMENT ON COLUMN merchant_kyc.exchange_sig 737 IS 'signature of the exchange affirming the KYC passed (or NULL if exchange does not require KYC or not kyc_ok)'; 738 COMMENT ON COLUMN merchant_kyc.exchange_pub 739 IS 'public key used with exchange_sig (or NULL if exchange_sig is NULL)'; 740 COMMENT ON COLUMN merchant_kyc.aml_decision 741 IS 'current AML decision for our account at the exchange'; 742 COMMENT ON COLUMN merchant_kyc.account_serial 743 IS 'Which bank account of the merchant is the KYC status for'; 744 COMMENT ON COLUMN merchant_kyc.exchange_url 745 IS 'Which exchange base URL is this KYC status valid for'; 746 747 748 CREATE TABLE IF NOT EXISTS merchant_otp_devices 749 (otp_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 750 ,merchant_serial BIGINT NOT NULL 751 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 752 ,otp_id TEXT NOT NULL 753 ,otp_description TEXT NOT NULL 754 ,otp_key TEXT DEFAULT NULL 755 ,otp_algorithm INT NOT NULL DEFAULT (0) 756 ,otp_ctr INT8 NOT NULL DEFAULT (0) 757 ,UNIQUE (merchant_serial, otp_id) 758 ); 759 COMMENT ON TABLE merchant_otp_devices 760 IS 'OTP device owned by a merchant'; 761 COMMENT ON COLUMN merchant_otp_devices.otp_description 762 IS 'Human-readable OTP device description'; 763 COMMENT ON COLUMN merchant_otp_devices.otp_key 764 IS 'A base64-encoded key of the point-of-sale. It will be use by the OTP device'; 765 COMMENT ON COLUMN merchant_otp_devices.otp_algorithm 766 IS 'algorithm to used to generate the confirmation code. It is linked with the otp_key and otp_ctr'; 767 COMMENT ON COLUMN merchant_otp_devices.otp_ctr 768 IS 'counter for counter-based OTP generators'; 769 770 771 CREATE TABLE IF NOT EXISTS merchant_template 772 (template_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 773 ,merchant_serial BIGINT NOT NULL 774 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 775 ,template_id TEXT NOT NULL 776 ,template_description TEXT NOT NULL 777 ,otp_device_id BIGINT 778 REFERENCES merchant_otp_devices (otp_serial) ON DELETE SET NULL 779 ,template_contract TEXT NOT NULL -- in JSON format 780 ,UNIQUE (merchant_serial, template_id) 781 ); 782 COMMENT ON TABLE merchant_template 783 IS 'template used by the merchant (may be incomplete, frontend can override)'; 784 COMMENT ON COLUMN merchant_template.template_description 785 IS 'Human-readable template description'; 786 COMMENT ON COLUMN merchant_template.template_contract 787 IS 'The template contract will contains some additional information.'; 788 789 790 791 CREATE TABLE IF NOT EXISTS merchant_webhook 792 (webhook_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 793 ,merchant_serial BIGINT NOT NULL 794 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 795 ,webhook_id TEXT NOT NULL 796 ,event_type TEXT NOT NULL 797 ,url TEXT NOT NULL 798 ,http_method TEXT NOT NULL 799 ,header_template TEXT 800 ,body_template TEXT 801 ,UNIQUE (merchant_serial, webhook_id) 802 ); 803 COMMENT ON TABLE merchant_webhook 804 IS 'webhook used by the merchant (may be incomplete, frontend can override)'; 805 COMMENT ON COLUMN merchant_webhook.event_type 806 IS 'Event of the webhook'; 807 COMMENT ON COLUMN merchant_webhook.url 808 IS 'URL to make the request to'; 809 COMMENT ON COLUMN merchant_webhook.http_method 810 IS 'http method use by the merchant'; 811 COMMENT ON COLUMN merchant_webhook.header_template 812 IS 'Template for the header of the webhook, to be modified based on trigger data'; 813 COMMENT ON COLUMN merchant_webhook.body_template 814 IS 'Template for the body of the webhook, to be modified based on trigger data'; 815 816 817 CREATE TABLE IF NOT EXISTS merchant_pending_webhooks 818 (webhook_pending_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 819 ,merchant_serial BIGINT NOT NULL 820 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 821 ,webhook_serial BIGINT NOT NULL 822 REFERENCES merchant_webhook (webhook_serial) ON DELETE CASCADE 823 ,next_attempt INT8 NOT NULL DEFAULT(0) 824 ,retries INT4 NOT NULL DEFAULT(0) 825 ,url TEXT NOT NULL 826 ,http_method TEXT NOT NULL 827 ,header TEXT 828 ,body TEXT 829 ,UNIQUE (merchant_serial, webhook_pending_serial) 830 ); 831 COMMENT ON TABLE merchant_pending_webhooks 832 IS 'webhooks that still need to be executed by the merchant'; 833 COMMENT ON COLUMN merchant_pending_webhooks.url 834 IS 'URL to make the request to'; 835 COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial 836 IS 'Reference to the configured webhook template'; 837 COMMENT ON COLUMN merchant_pending_webhooks.retries 838 IS 'How often have we tried this request so far'; 839 COMMENT ON COLUMN merchant_pending_webhooks.next_attempt 840 IS 'Time when we should make the next request to the webhook'; 841 COMMENT ON COLUMN merchant_pending_webhooks.http_method 842 IS 'http method use for the webhook'; 843 COMMENT ON COLUMN merchant_pending_webhooks.header 844 IS 'Header of the webhook'; 845 COMMENT ON COLUMN merchant_pending_webhooks.body 846 IS 'Body of the webhook'; 847 848 849 CREATE TABLE IF NOT EXISTS merchant_exchange_accounts 850 (mea_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 851 ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) 852 ,payto_uri TEXT NOT NULL 853 ,conversion_url TEXT 854 ,debit_restrictions TEXT NOT NULL 855 ,credit_restrictions TEXT NOT NULL 856 ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) 857 ); 858 COMMENT ON TABLE merchant_exchange_accounts 859 IS 'Here we store which bank accounts the exchange uses and with which constraints'; 860 COMMENT ON COLUMN merchant_exchange_accounts.master_pub 861 IS 'Master public key of the exchange with these accounts'; 862 COMMENT ON COLUMN merchant_exchange_accounts.payto_uri 863 IS 'RFC 8905 URI of the exchange bank account'; 864 COMMENT ON COLUMN merchant_exchange_accounts.conversion_url 865 IS 'NULL if this account does not require currency conversion'; 866 COMMENT ON COLUMN merchant_exchange_accounts.debit_restrictions 867 IS 'JSON array with account restrictions'; 868 COMMENT ON COLUMN merchant_exchange_accounts.credit_restrictions 869 IS 'JSON array with account restrictions'; 870 871 872 CREATE TABLE IF NOT EXISTS merchant_exchange_keys 873 (mek_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE 874 ,exchange_url TEXT PRIMARY KEY 875 ,keys_json TEXT NOT NULL 876 ,expiration_time INT8 NOT NULL 877 ); 878 COMMENT ON TABLE merchant_exchange_keys 879 IS 'Here we store the cached /keys response from an exchange in JSON format'; 880 COMMENT ON COLUMN merchant_exchange_keys.exchange_url 881 IS 'Base URL of the exchange with these keys'; 882 COMMENT ON COLUMN merchant_exchange_keys.keys_json 883 IS 'JSON string of the /keys as generated by libtalerexchange'; 884 COMMENT ON COLUMN merchant_exchange_keys.expiration_time 885 IS 'When should this /keys object be deleted'; 886 887 888 -- Complete transaction 889 COMMIT;