1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
--
-- This file is part of TALER
-- Copyright (C) 2023 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-0002.sql
-- @brief database schema for the merchant
-- @author Christian Blättler
-- Everything in one big transaction
BEGIN;
-- Check patch versioning is in place.
SELECT _v.register_patch('merchant-0002', NULL, NULL);
SET search_path TO merchant;
ALTER TABLE merchant_orders
ADD COLUMN fulfillment_url TEXT DEFAULT NULL
,ADD COLUMN session_id TEXT DEFAULT '' NOT NULL;
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 IF NOT EXISTS merchant_orders_by_merchant_and_session
ON merchant_orders
(merchant_serial,session_id);
CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_fullfilment_and_session
ON merchant_orders
(merchant_serial,fulfillment_url,session_id);
CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session
ON merchant_contract_terms
(merchant_serial,session_id);
ALTER TABLE merchant_deposit_confirmations
ADD COLUMN wire_transfer_deadline INT8 DEFAULT (0) NOT NULL,
ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL,
ADD COLUMN exchange_failure TEXT DEFAULT NULL;
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';
CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire
ON merchant_deposit_confirmations
(exchange_url,wire_transfer_deadline)
WHERE wire_pending;
CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial
ON merchant_deposits
(deposit_confirmation_serial);
-------------------------- Tokens -----------------------------
CREATE TABLE IF NOT EXISTS merchant_token_families
(token_family_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,slug TEXT NOT NULL UNIQUE
,name TEXT NOT NULL
,description TEXT
,description_i18n BYTEA NOT NULL
,valid_after BIGINT NOT NULL
,valid_before BIGINT NOT NULL
,duration BIGINT NOT NULL
,kind TEXT NOT NULL CHECK (kind IN ('subscription', 'discount'))
,issued BIGINT DEFAULT 0
,redeemed BIGINT DEFAULT 0
);
COMMENT ON TABLE merchant_token_families
IS 'Token families configured by the merchant.';
COMMENT ON COLUMN merchant_token_families.merchant_serial
IS 'Instance where the token family is configured.';
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.redeemed
IS 'Counter for the number of tokens redeemed for this token family.';
CREATE TABLE IF NOT EXISTS merchant_token_family_keys
(token_family_key_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
,valid_after BIGINT NOT NULL
,valid_before BIGINT NOT NULL
,pub BYTEA NOT NULL
,h_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(h_pub)=32)
,priv BYTEA
,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs'))
,UNIQUE (token_family_serial, valid_after)
);
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.valid_after
IS 'Start time for the validity of the token key.';
COMMENT ON COLUMN merchant_token_family_keys.valid_before
IS 'Expiration time for the validity of the token key.';
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).';
CREATE TABLE IF NOT EXISTS merchant_spent_tokens
(spent_token_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
,token_family_key_serial BIGINT REFERENCES merchant_token_family_keys(token_family_key_serial) ON DELETE CASCADE
,token_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(token_pub)=32)
,token_sig BYTEA NOT NULL CHECK (LENGTH(token_sig)=64)
,blind_sig BYTEA NOT NULL
);
COMMENT ON TABLE merchant_spent_tokens
IS 'Tokens that have been spent by customers.';
COMMENT ON COLUMN merchant_spent_tokens.merchant_serial
IS 'Merchant serial where the token was spent.';
COMMENT ON COLUMN merchant_spent_tokens.h_contract_terms
IS 'This is no foreign key by design.';
COMMENT ON COLUMN merchant_spent_tokens.token_family_key_serial
IS 'Token family to which the spent token belongs.';
COMMENT ON COLUMN merchant_spent_tokens.token_pub
IS 'Public key of the spent token.';
COMMENT ON COLUMN merchant_spent_tokens.token_sig
IS 'Signature that the token was spent on specified order.';
COMMENT ON COLUMN merchant_spent_tokens.blind_sig
IS 'Blind signature for the spent token to prove validity of token.';
-- Complete transaction
COMMIT;
|
