postgres.c2ec.xml (14841B)
1 <?xml version="1.0" encoding="UTF-8"?><database name="postgres" schema="c2ec" type="PostgreSQL - 15.2 (Debian 15.2-1.pgdg110+1)"> 2 <tables> 3 <table name="provider" remarks="Table describing providers of c2ec terminal" schema="c2ec" type="TABLE"> 4 <column autoUpdated="true" defaultValue="null" digits="0" id="0" name="provider_id" nullable="false" remarks="Uniquely identifies a provider" size="19" type="int8" typeCode="-5"> 5 <child column="provider_id" foreignKey="terminal_provider_id_fkey" implied="false" onDeleteCascade="false" schema="c2ec" table="terminal"/> 6 </column> 7 <column autoUpdated="false" defaultValue="null" digits="0" id="1" name="name" nullable="false" remarks="Name of the provider, used for selection in transaction proofing" size="2147483647" type="text" typeCode="12"/> 8 <column autoUpdated="false" defaultValue="null" digits="0" id="2" name="payto_target_type" nullable="false" remarks="The Payto target type associated with the provider. Each payto target type has exctly one provider. This is needed so that the attestor client can be dynamically selected by C2EC." size="2147483647" type="text" typeCode="12"/> 9 <column autoUpdated="false" defaultValue="null" digits="0" id="3" name="backend_base_url" nullable="false" remarks="URL of the provider backend for transaction proofing" size="2147483647" type="text" typeCode="12"/> 10 <column autoUpdated="false" defaultValue="null" digits="0" id="4" name="backend_credentials" nullable="false" remarks="Credentials used to access the backend of the provider" size="2147483647" type="text" typeCode="12"/> 11 <primaryKey column="provider_id" sequenceNumberInPK="1"/> 12 <index name="provider_pkey" unique="true"> 13 <column ascending="true" name="provider_id"/> 14 </index> 15 <index name="provider_name_key" unique="true"> 16 <column ascending="true" name="name"/> 17 </index> 18 <index name="provider_payto_target_type_key" unique="true"> 19 <column ascending="true" name="payto_target_type"/> 20 </index> 21 </table> 22 <table name="terminal" remarks="Table containing information about terminals of providers" schema="c2ec" type="TABLE"> 23 <column autoUpdated="true" defaultValue="null" digits="0" id="0" name="terminal_id" nullable="false" remarks="Uniquely identifies a terminal" size="19" type="int8" typeCode="-5"> 24 <child column="terminal_id" foreignKey="withdrawal_terminal_id_fkey" implied="false" onDeleteCascade="false" schema="c2ec" table="withdrawal"/> 25 </column> 26 <column autoUpdated="false" defaultValue="null" digits="0" id="1" name="access_token" nullable="false" remarks="The access token of the terminal used for authentication against the c2ec API. It is hashed using a PBKDF." size="2147483647" type="text" typeCode="12"/> 27 <column autoUpdated="false" defaultValue="true" digits="0" id="2" name="active" nullable="false" remarks="Indicates if the terminal is active or deactivated" size="1" type="bool" typeCode="-7"/> 28 <column autoUpdated="false" defaultValue="null" digits="0" id="3" name="description" nullable="true" remarks="Description to help identify the terminal. This may include the location and an identifier of the terminal." size="2147483647" type="text" typeCode="12"/> 29 <column autoUpdated="false" defaultValue="null" digits="0" id="4" name="provider_id" nullable="false" remarks="Indicates the terminal provider to which the terminal belongs" size="19" type="int8" typeCode="-5"> 30 <parent column="provider_id" foreignKey="terminal_provider_id_fkey" implied="false" onDeleteCascade="false" schema="c2ec" table="provider"/> 31 </column> 32 <primaryKey column="terminal_id" sequenceNumberInPK="1"/> 33 <index name="terminal_pkey" unique="true"> 34 <column ascending="true" name="terminal_id"/> 35 </index> 36 </table> 37 <table name="transfer" remarks="Table storing transfers which are sent by the exchange." schema="c2ec" type="TABLE"> 38 <column autoUpdated="false" defaultValue="null" digits="0" id="0" name="request_uid" nullable="false" remarks="A unique identifier for the transfer." size="2147483647" type="bytea" typeCode="-2"/> 39 <column autoUpdated="true" defaultValue="null" digits="0" id="1" name="row_id" nullable="false" remarks="The row id is used to support the history outgoing" size="19" type="int8" typeCode="-5"/> 40 <column autoUpdated="false" defaultValue="null" digits="0" id="2" name="amount" nullable="false" remarks="The amount to be transferred" size="2147483647" type=""c2ec"."taler_amount_currency"" typeCode="2002"/> 41 <column autoUpdated="false" defaultValue="null" digits="0" id="3" name="exchange_base_url" nullable="false" remarks="The base url of the exchange, sending the transfer request" size="2147483647" type="text" typeCode="12"/> 42 <column autoUpdated="false" defaultValue="null" digits="0" id="4" name="wtid" nullable="false" remarks="The id of the transaction" size="2147483647" type="text" typeCode="12"/> 43 <column autoUpdated="false" defaultValue="null" digits="0" id="5" name="credit_account" nullable="false" remarks="The payto address of the transfer target" size="2147483647" type="text" typeCode="12"/> 44 <column autoUpdated="false" defaultValue="null" digits="0" id="6" name="transfer_ts" nullable="false" remarks="Timestamp when the transfer was last processesd" size="19" type="int8" typeCode="-5"/> 45 <column autoUpdated="false" defaultValue="1" digits="0" id="7" name="transfer_status" nullable="false" remarks="Non-zero when the transfer failed at the last retry. Zero if transfer succeeded. Negative, when max amount of retries was exceeded. Because the transfer was not yet triggered when it is added, the status is set to 1 by default." size="5" type="int2" typeCode="5"/> 46 <column autoUpdated="false" defaultValue="0" digits="0" id="8" name="retries" nullable="false" remarks="Number of retries" size="5" type="int2" typeCode="5"/> 47 <column autoUpdated="false" defaultValue="0" digits="0" id="9" name="transferred_row_id" nullable="true" remarks="Used by the wire-gateway API do not mess up pending and succeeded transfers." size="19" type="int8" typeCode="-5"/> 48 <primaryKey column="request_uid" sequenceNumberInPK="1"/> 49 <index name="transfer_pkey" unique="true"> 50 <column ascending="true" name="request_uid"/> 51 </index> 52 </table> 53 <table name="withdrawal" numRows="128" remarks="Table representing withdrawal processes initiated by terminals" schema="c2ec" type="TABLE"> 54 <column autoUpdated="true" defaultValue="null" digits="0" id="0" name="withdrawal_row_id" nullable="false" remarks="The withdrawal id is used a technical id used by the wire gateway to sequentially select new transactions" size="19" type="int8" typeCode="-5"/> 55 <column autoUpdated="false" defaultValue="null" digits="0" id="1" name="request_uid" nullable="false" remarks="The request uid identifies each request and is stored to make the API interacting with withdrawals idempotent." size="2147483647" type="text" typeCode="12"/> 56 <column autoUpdated="false" defaultValue="null" digits="0" id="2" name="wopid" nullable="false" remarks="The wopid (withdrawal operation id) is a nonce generated by the terminal requesting a withdrawal. 	The wopid identifies a specific withdrawal spawning all involved systems." size="2147483647" type="bytea" typeCode="-2"/> 57 <column autoUpdated="false" defaultValue="null" digits="0" id="3" name="reserve_pub_key" nullable="true" remarks="Reserve public key for the reserve which will hold the withdrawal amount after completion" size="2147483647" type="bytea" typeCode="-2"/> 58 <column autoUpdated="false" defaultValue="null" digits="0" id="4" name="registration_ts" nullable="false" remarks="Timestamp of when the withdrawal request was registered" size="19" type="int8" typeCode="-5"/> 59 <column autoUpdated="false" defaultValue="null" digits="0" id="5" name="amount" nullable="true" remarks="Effective amount to be put into the reserve after completion" size="2147483647" type=""c2ec"."taler_amount_currency"" typeCode="2002"/> 60 <column autoUpdated="false" defaultValue="null" digits="0" id="6" name="suggested_amount" nullable="true" remarks="The suggested amount is given by the entity initializing the wihdrawal. If the suggested amount is given, the wallet may still change the amount." size="2147483647" type=""c2ec"."taler_amount_currency"" typeCode="2002"/> 61 <column autoUpdated="false" defaultValue="null" digits="0" id="7" name="terminal_fees" nullable="true" remarks="Fees associated with the withdrawal but not related to the taler payment system." size="2147483647" type=""c2ec"."taler_amount_currency"" typeCode="2002"/> 62 <column autoUpdated="false" defaultValue="'pending'::c2ec.withdrawal_operation_status" digits="0" id="8" name="withdrawal_status" nullable="false" remarks="Status of the withdrawal process" size="2147483647" type=""c2ec"."withdrawal_operation_status"" typeCode="12"/> 63 <column autoUpdated="false" defaultValue="null" digits="0" id="9" name="terminal_id" nullable="false" remarks="ID of the terminal that initiated the withdrawal" size="19" type="int8" typeCode="-5"> 64 <parent column="terminal_id" foreignKey="withdrawal_terminal_id_fkey" implied="false" onDeleteCascade="false" schema="c2ec" table="terminal"/> 65 </column> 66 <column autoUpdated="false" defaultValue="null" digits="0" id="10" name="provider_transaction_id" nullable="true" remarks="Transaction identifier supplied by the provider for backend request" size="2147483647" type="text" typeCode="12"/> 67 <column autoUpdated="false" defaultValue="null" digits="0" id="11" name="last_retry_ts" nullable="true" remarks="Timestamp of the last retry attempt" size="19" type="int8" typeCode="-5"/> 68 <column autoUpdated="false" defaultValue="0" digits="0" id="12" name="retry_counter" nullable="false" remarks="Number of retry attempts" size="10" type="int4" typeCode="4"/> 69 <column autoUpdated="false" defaultValue="null" digits="0" id="13" name="completion_proof" nullable="true" remarks="Proof of transaction upon final completion delivered by the providers system" size="2147483647" type="bytea" typeCode="-2"/> 70 <column autoUpdated="false" defaultValue="0" digits="0" id="14" name="confirmed_row_id" nullable="true" remarks="Used by the wire-gateway API do not mess up confirmed and unconfirmed transactions row ids." size="19" type="int8" typeCode="-5"/> 71 <primaryKey column="withdrawal_row_id" sequenceNumberInPK="1"/> 72 <index name="withdrawal_pkey" unique="true"> 73 <column ascending="true" name="withdrawal_row_id"/> 74 </index> 75 <index name="withdrawal_request_uid_key" unique="true"> 76 <column ascending="true" name="request_uid"/> 77 </index> 78 <index name="withdrawal_wopid_key" unique="true"> 79 <column ascending="true" name="wopid"/> 80 </index> 81 <index name="wopid_index" unique="false"> 82 <column ascending="true" name="wopid"/> 83 </index> 84 <checkConstraint constraint="((length(reserve_pub_key) = 32))" name="withdrawal_reserve_pub_key_check"/> 85 <checkConstraint constraint="((length(wopid) = 32))" name="withdrawal_wopid_check"/> 86 </table> 87 </tables> 88 <routines> 89 <routine dataAccess="MODIFIES" deterministic="false" name="emit_payment_notification()" returnType="trigger" securityType="INVOKER" type="FUNCTION"> 90 <comment><![CDATA[The function emits the name of the provider, row id of the withdrawal 91 and the provider_transaction_id, on the channel "payment_notification". 92 The format of the payload is as follows: 93 "{PROVIDER_NAME}|{WITHDRAWAL_ID}|{PROVIDER_TRANSACTION_ID}". The subscriber 94 shall decide which attestation process to use, based on the name of 95 the provider.]]></comment> 96 <definition language="plpgsql"><![CDATA[DECLARE 97 provider_name TEXT; 98 BEGIN 99 SELECT p.name INTO provider_name FROM c2ec.provider AS p 100 LEFT JOIN c2ec.terminal AS t 101 ON t.provider_id = p.provider_id 102 LEFT JOIN c2ec.withdrawal AS w 103 ON t.terminal_id = NEW.terminal_id 104 WHERE w.withdrawal_row_id = NEW.withdrawal_row_id; 105 PERFORM pg_notify('payment_notification', 106 provider_name || '|' || 107 NEW.withdrawal_row_id || '|' || 108 NEW.provider_transaction_id 109 ); 110 RETURN NULL; 111 END;]]></definition> 112 <parameters> 113 <parameter mode="IN"/> 114 </parameters> 115 </routine> 116 <routine dataAccess="MODIFIES" deterministic="false" name="emit_retry_notification()" returnType="trigger" securityType="INVOKER" type="FUNCTION"> 117 <comment><![CDATA[The function emits the id of the withdrawal for which the last 118 retry timestamp was updated. This shall trigger a retry operation. 119 How many retries are attempted is specified and handled by the application]]></comment> 120 <definition language="plpgsql"><![CDATA[BEGIN 121 PERFORM pg_notify('retry', '' || NEW.withdrawal_row_id); 122 RETURN NULL; 123 END;]]></definition> 124 <parameters> 125 <parameter mode="IN"/> 126 </parameters> 127 </routine> 128 <routine dataAccess="MODIFIES" deterministic="false" name="emit_transfer_notification()" returnType="trigger" securityType="INVOKER" type="FUNCTION"> 129 <comment><![CDATA[The function emits the request_uid of a transfer which shall trigger a transfer 130 by the receiver of the notification.]]></comment> 131 <definition language="plpgsql"><![CDATA[BEGIN 132 PERFORM pg_notify('transfer', encode(NEW.request_uid::BYTEA, 'base64')); 133 RETURN NULL; 134 END;]]></definition> 135 <parameters> 136 <parameter mode="IN"/> 137 </parameters> 138 </routine> 139 <routine dataAccess="MODIFIES" deterministic="false" name="emit_withdrawal_status()" returnType="trigger" securityType="INVOKER" type="FUNCTION"> 140 <comment><![CDATA[The function encodes the wopid in base64 and 141 sends a notification on the channel "w_{wopid}" 142 with the status in the payload.]]></comment> 143 <definition language="plpgsql"><![CDATA[BEGIN 144 PERFORM pg_notify('w_' || encode(NEW.wopid::BYTEA, 'base64'), NEW.withdrawal_status::TEXT); 145 RETURN NULL; 146 END;]]></definition> 147 <parameters> 148 <parameter mode="IN"/> 149 </parameters> 150 </routine> 151 </routines> 152 </database>