1 files changed, 61 insertions, 2 deletions
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
index 7ec6ce6a..ae5d452a 100644
--- a/src/exchangedb/common-0001.sql
+++ b/src/exchangedb/common-0001.sql
@@ -56,8 +56,8 @@ BEGIN
       '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
       ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)'
       ',payto_uri VARCHAR NOT NULL'
-      ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)'
-      ',external_id VARCHAR'
+      ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' -- FIXME: REMOVE!
+      ',external_id VARCHAR' -- FIXME: REMOVE!
     ') %s ;'
     ,'wire_targets'
     ,'PARTITION BY HASH (wire_target_h_payto)'
@@ -85,6 +85,65 @@ BEGIN
 END
 $$;
 
+
+----------------------- legitimizations ---------------------------
+
+CREATE OR REPLACE FUNCTION create_table_legitimizations(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(legitimization_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=64)'
+      ',expiration_time INT8 NOT NULL DEFAULT (0)'
+      ',provider_section VARCHAR NOT NULL'
+      ',provider_user_id VARCHAR DEFAULT NULL'
+      ',provider_legitimization_id VARCHAR DEFAULT NULL'
+    ') %s ;'
+    ,'legitimizations'
+    ,'PARTITION BY HASH (h_payto)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+-- We need a separate function for this, as we call create_table only once but need to add
+-- those constraints to each partition which gets created
+CREATE OR REPLACE FUNCTION add_constraints_to_legitimizations_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  partition_name VARCHAR;
+BEGIN
+
+  partition_name = concat_ws('_', 'legitimizations', partition_suffix);
+
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || partition_name
+    || ' '
+      'ADD CONSTRAINT ' || partition_name || '_legitimization_serial_id_key '
+        'UNIQUE (legitimization_serial_id)');
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index '
+        'ON '|| partition_name || ' '
+        '(provider_section,provider_legitimization_id)'
+  );
+  EXECUTE FORMAT (
+    'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index '
+    'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';'
+  );
+END
+$$;
+
 ------------------------ reserves -------------------------------
 
 CREATE OR REPLACE FUNCTION create_table_reserves(