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
|
--
-- This file is part of TALER
-- Copyright (C) 2014--2022 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/>
--
CREATE OR REPLACE FUNCTION create_table_kyc_attributes(
IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
table_name VARCHAR DEFAULT 'kyc_attributes';
BEGIN
PERFORM create_partitioned_table(
'CREATE TABLE IF NOT EXISTS %I'
'(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)'
',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)'
',provider VARCHAR NOT NULL'
',birthdate VARCHAR'
',collection_time INT8 NOT NULL'
',expiration_time INT8 NOT NULL'
',encrypted_attributes BYTEA NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (h_payto)'
,partition_suffix
);
PERFORM comment_partitioned_table(
'KYC data about particular payment addresses'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'hash of payto://-URI the attributes are about'
,'h_payto'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users'
,'kyc_prox'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'birth date of the user, in format YYYY-MM-DD where a value of 0 is used to indicate unknown (in official documents); NULL if the birth date was not collected by the provider; used for KYC-driven age restrictions'
,'birthdate'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'time when the attributes were collected by the provider'
,'collection_time'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'time when the attributes should no longer be considered validated'
,'expiration_time'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'configuration section name of the provider that affirmed the attributes'
,'provider'
,table_name
,partition_suffix
);
PERFORM comment_partitioned_column(
'(encrypted) JSON object (as string) with the attributes'
,'encrypted_attributes'
,table_name
,partition_suffix
);
END $$;
COMMENT ON FUNCTION create_table_kyc_attributes
IS 'Creates the kyc_attributes table';
CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
table_name VARCHAR DEFAULT 'kyc_attributes';
BEGIN
table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_serial_key '
'UNIQUE (kyc_attributes_serial_id)'
);
-- To search similar users (e.g. during AML checks)
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_similarity_index '
'ON ' || table_name || ' '
'(kyc_prox);'
);
-- For garbage collection
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_expiration_time '
'ON ' || table_name || ' '
'(expiration_time ASC);'
);
END $$;
INSERT INTO exchange_tables
(name
,version
,action
,partitioned
,by_range)
VALUES
('kyc_attributes'
,'exchange-0003'
,'create'
,TRUE
,FALSE),
('kyc_attributes'
,'exchange-0003'
,'constrain'
,TRUE
,FALSE);
|
