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
|
--
-- 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 TEXT DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
table_name TEXT 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 TEXT NOT NULL'
',collection_time INT8 NOT NULL'
',expiration_time INT8 NOT NULL'
',encrypted_attributes BYTEA NOT NULL'
',legitimization_serial INT8 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(
'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
);
PERFORM comment_partitioned_column(
'Reference the legitimization process for which these attributes are gathered for.'
,'legitimization_serial'
,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 TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
table_name TEXT 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)'
);
-- The legitimization_serial is a foreign key.
-- TODO: due to partitioning by h_payto, we can not simply reference
-- the serial id of the legitimization_processes
-- EXECUTE FORMAT (
-- 'ALTER TABLE ' || table_name ||
-- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes'
-- ' FOREIGN KEY (legitimization_serial) '
-- ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE
-- );
-- 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-0002'
,'create'
,TRUE
,FALSE),
('kyc_attributes'
,'exchange-0002'
,'constrain'
,TRUE
,FALSE);
|
