0001-c2ec_schema.sql (7298B)
1 BEGIN; 2 3 SELECT _v.register_patch('0001-c2ec-schema', NULL, NULL); 4 5 CREATE SCHEMA c2ec; 6 COMMENT ON SCHEMA c2ec 7 IS 'Schema containing all tables and types related to c2ec (cashless2ecash)'; 8 9 SET search_path TO c2ec; 10 11 CREATE TYPE withdrawal_operation_status AS ENUM ( 12 'pending', 13 'selected', 14 'aborted', 15 'confirmed' 16 ); 17 COMMENT ON TYPE withdrawal_operation_status 18 IS 'Enumerates the states of a withdrawal operation. 19 The states are the same as in the bank-integration API: 20 pending : the operation is pending parameters selection (exchange and reserve public key) 21 selected : the operations has been selected and is pending confirmation 22 aborted : the operation has been aborted 23 confirmed: the transfer has been confirmed and registered by the bank'; 24 25 26 CREATE TYPE taler_amount_currency 27 AS (val INT8, frac INT4 , curr VARCHAR(12)); 28 COMMENT ON TYPE taler_amount_currency 29 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value. 30 copied from https://git.taler.net/merchant.git/tree/src/backenddb/merchant-0001.sql'; 31 32 33 CREATE TABLE IF NOT EXISTS provider ( 34 provider_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 35 name TEXT NOT NULL UNIQUE, 36 payto_target_type TEXT NOT NULL UNIQUE, 37 backend_base_url TEXT NOT NULL, 38 backend_credentials TEXT NOT NULL 39 ); 40 COMMENT ON TABLE provider 41 IS 'Table describing providers of c2ec terminal'; 42 COMMENT ON COLUMN provider.provider_id 43 IS 'Uniquely identifies a provider'; 44 COMMENT ON COLUMN provider.name 45 IS 'Name of the provider, used for selection in transaction proofing'; 46 COMMENT ON COLUMN provider.payto_target_type 47 IS 'The Payto target type associated with the provider. Each payto target type 48 has exctly one provider. This is needed so that the attestor client can be dynamically 49 selected by C2EC.'; 50 COMMENT ON COLUMN provider.backend_base_url 51 IS 'URL of the provider backend for transaction proofing'; 52 COMMENT ON COLUMN provider.backend_credentials 53 IS 'Credentials used to access the backend of the provider'; 54 55 56 CREATE TABLE IF NOT EXISTS terminal ( 57 terminal_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 58 access_token TEXT NOT NULL, 59 active BOOLEAN NOT NULL DEFAULT TRUE, 60 description TEXT, 61 provider_id INT8 NOT NULL REFERENCES provider(provider_id) 62 ); 63 COMMENT ON TABLE terminal 64 IS 'Table containing information about terminals of providers'; 65 COMMENT ON COLUMN terminal.terminal_id 66 IS 'Uniquely identifies a terminal'; 67 COMMENT ON COLUMN terminal.access_token 68 IS 'The access token of the terminal used for authentication against the c2ec API. It is hashed using a PBKDF.'; 69 COMMENT ON COLUMN terminal.active 70 IS 'Indicates if the terminal is active or deactivated'; 71 COMMENT ON COLUMN terminal.description 72 IS 'Description to help identify the terminal. This may include the location and an identifier of the terminal.'; 73 COMMENT ON COLUMN terminal.provider_id 74 IS 'Indicates the terminal provider to which the terminal belongs'; 75 76 77 CREATE TABLE IF NOT EXISTS withdrawal ( 78 withdrawal_row_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 79 request_uid TEXT UNIQUE NOT NULL, 80 wopid BYTEA CHECK (LENGTH(wopid)=32) UNIQUE NOT NULL, 81 reserve_pub_key BYTEA CHECK (LENGTH(reserve_pub_key)=32), 82 registration_ts INT8 NOT NULL, 83 amount taler_amount_currency, 84 suggested_amount taler_amount_currency, 85 terminal_fees taler_amount_currency, 86 withdrawal_status withdrawal_operation_status NOT NULL DEFAULT 'pending', 87 terminal_id INT8 NOT NULL REFERENCES terminal(terminal_id), 88 provider_transaction_id TEXT, 89 last_retry_ts INT8, 90 retry_counter INT4 NOT NULL DEFAULT 0, 91 completion_proof BYTEA 92 ); 93 COMMENT ON TABLE withdrawal 94 IS 'Table representing withdrawal processes initiated by terminals'; 95 COMMENT ON COLUMN withdrawal.request_uid 96 IS 'The request uid identifies each request and is stored to make the API interacting 97 with withdrawals idempotent.'; 98 COMMENT ON COLUMN withdrawal.withdrawal_row_id 99 IS 'The withdrawal id is used a technical id used by the wire gateway to sequentially select new transactions'; 100 COMMENT ON COLUMN withdrawal.wopid 101 IS 'The wopid (withdrawal operation id) is a nonce generated by the terminal requesting a withdrawal. 102 The wopid identifies a specific withdrawal spawning all involved systems.'; 103 COMMENT ON COLUMN withdrawal.reserve_pub_key 104 IS 'Reserve public key for the reserve which will hold the withdrawal amount after completion'; 105 COMMENT ON COLUMN withdrawal.registration_ts 106 IS 'Timestamp of when the withdrawal request was registered'; 107 COMMENT ON COLUMN withdrawal.amount 108 IS 'Effective amount to be put into the reserve after completion'; 109 COMMENT ON COLUMN withdrawal.suggested_amount 110 IS 'The suggested amount is given by the entity initializing the wihdrawal. 111 If the suggested amount is given, the wallet may still change the amount.'; 112 COMMENT ON COLUMN withdrawal.terminal_fees 113 IS 'Fees associated with the withdrawal but not related to the taler payment system.'; 114 COMMENT ON COLUMN withdrawal.withdrawal_status 115 IS 'Status of the withdrawal process'; 116 COMMENT ON COLUMN withdrawal.terminal_id 117 IS 'ID of the terminal that initiated the withdrawal'; 118 COMMENT ON COLUMN withdrawal.provider_transaction_id 119 IS 'Transaction identifier supplied by the provider for backend request'; 120 COMMENT ON COLUMN withdrawal.last_retry_ts 121 IS 'Timestamp of the last retry attempt'; 122 COMMENT ON COLUMN withdrawal.retry_counter 123 IS 'Number of retry attempts'; 124 COMMENT ON COLUMN withdrawal.completion_proof 125 IS 'Proof of transaction upon final completion delivered by the providers system'; 126 127 CREATE INDEX wopid_index ON withdrawal (wopid); 128 COMMENT ON INDEX wopid_index 129 IS 'The wopid is the search key for each bank-integration api related request. 130 Thus it makes sense to create an index on the column.'; 131 132 CREATE TABLE IF NOT EXISTS transfer ( 133 request_uid BYTEA UNIQUE PRIMARY KEY, 134 row_id INT8 GENERATED BY DEFAULT AS IDENTITY, 135 amount taler_amount_currency NOT NULL, 136 exchange_base_url TEXT NOT NULL, 137 wtid TEXT NOT NULL, 138 credit_account TEXT NOT NULL, 139 transfer_ts INT8 NOT NULL, 140 transfer_status INT2 NOT NULL DEFAULT 1, 141 retries INT2 NOT NULL DEFAULT 0 142 ); 143 COMMENT ON TABLE transfer 144 IS 'Table storing transfers which are sent by the exchange.'; 145 COMMENT ON COLUMN transfer.row_id 146 IS 'The row id is used to support the history outgoing'; 147 COMMENT ON COLUMN transfer.request_uid 148 IS 'A unique identifier for the transfer. '; 149 COMMENT ON COLUMN transfer.amount 150 IS 'The amount to be transferred'; 151 COMMENT ON COLUMN transfer.exchange_base_url 152 IS 'The base url of the exchange, sending the transfer request'; 153 COMMENT ON COLUMN transfer.wtid 154 IS 'The id of the transaction'; 155 COMMENT ON COLUMN transfer.credit_account 156 IS 'The payto address of the transfer target'; 157 COMMENT ON COLUMN transfer.transfer_ts 158 IS 'Timestamp when the transfer was last processesd'; 159 COMMENT ON COLUMN transfer.transfer_status 160 IS 'Non-zero when the transfer failed at the last retry. 161 Zero if transfer succeeded. Negative, when max amount of 162 retries was exceeded. Because the transfer was not yet triggered 163 when it is added, the status is set to 1 by default.'; 164 COMMENT ON COLUMN transfer.retries 165 IS 'Number of retries'; 166 167 COMMIT;