aboutsummaryrefslogtreecommitdiff
path: root/src/exchangedb/0003-kyc_attributes.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/exchangedb/0003-kyc_attributes.sql')
-rw-r--r--src/exchangedb/0003-kyc_attributes.sql33
1 files changed, 33 insertions, 0 deletions
diff --git a/src/exchangedb/0003-kyc_attributes.sql b/src/exchangedb/0003-kyc_attributes.sql
index db7f2e1ed..1547466f8 100644
--- a/src/exchangedb/0003-kyc_attributes.sql
+++ b/src/exchangedb/0003-kyc_attributes.sql
@@ -27,7 +27,10 @@ BEGIN
'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 VARCHAR NOT NULL'
') %s ;'
@@ -47,6 +50,24 @@ BEGIN
,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
@@ -85,6 +106,18 @@ BEGIN
' 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 $$;