Added reserve_pub to kyc legitimization_processes

Where applicable, the reserve_pub will be passed on to the
kcy-legitimization process and persisted along with h_payto.

This allows us to set a birthday on the reserve itself, once a
related kyc-process has provided one.

9 files changed, 72 insertions, 10 deletions

diff --git a/src/exchangedb/0002-legitimization_processes.sql b/src/exchangedb/0002-legitimization_processes.sql
index 4544a02ea..576527bce 100644
--- a/src/exchangedb/0002-legitimization_processes.sql
+++ b/src/exchangedb/0002-legitimization_processes.sql
@@ -29,6 +29,8 @@ BEGIN
       ',provider_section VARCHAR NOT NULL'
       ',provider_user_id VARCHAR DEFAULT NULL'
       ',provider_legitimization_id VARCHAR DEFAULT NULL'
+      ',finished BOOLEAN DEFAULT (FALSE)'
+      ',reserve_pub BYTEA'
       ',UNIQUE (h_payto, provider_section)'
     ') %s ;'
     ,'legitimization_processes'
@@ -76,6 +78,18 @@ BEGIN
     ,'legitimization_processes'
     ,shard_suffix
   );
+  PERFORM comment_partitioned_column(
+     'Set to TRUE when the specific legitimization process is finished.'
+    ,'finished'
+    ,'legitimization_processes'
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'If h_payto refers to a reserve, this is its public key, otherwise NULL.'
+    ,'reserve_pub'
+    ,'legitimization_processes'
+    ,shard_suffix
+  );
 END
 $$;
 
diff --git a/src/exchangedb/0003-kyc_attributes.sql b/src/exchangedb/0003-kyc_attributes.sql
index 18093358e..56e274a31 100644
--- a/src/exchangedb/0003-kyc_attributes.sql
+++ b/src/exchangedb/0003-kyc_attributes.sql
@@ -33,6 +33,7 @@ BEGIN
       ',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)'
@@ -85,6 +86,12 @@ BEGIN
     ,table_name
     ,partition_suffix
   );
+  PERFORM comment_partitioned_column(
+     'Reference the legitimization process for which theses attributes are gathered for.'
+    ,'legitimization_serial'
+    ,table_name
+    ,partition_suffix
+  );
 END $$;
 
 COMMENT ON FUNCTION create_table_kyc_attributes
@@ -106,6 +113,15 @@ BEGIN
       ' 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 '
diff --git a/src/exchangedb/exchange_do_insert_kyc_attributes.sql b/src/exchangedb/exchange_do_insert_kyc_attributes.sql
index ae6a65759..c80033154 100644
--- a/src/exchangedb/exchange_do_insert_kyc_attributes.sql
+++ b/src/exchangedb/exchange_do_insert_kyc_attributes.sql
@@ -31,6 +31,8 @@ CREATE OR REPLACE FUNCTION exchange_do_insert_kyc_attributes(
   OUT out_ok BOOLEAN)
 LANGUAGE plpgsql
 AS $$
+DECLARE
+   orig_reserve_pub BYTEA;
 BEGIN
 
 INSERT INTO exchange.kyc_attributes
@@ -48,20 +50,17 @@ INSERT INTO exchange.kyc_attributes
   ,in_expiration_time_ts
   ,in_enc_attributes);
 
--- FIXME-Oec: modify to 'return' the reserve_pub here
--- (requires of course to modify other code to store
--- the reserve pub in the right table in the first place)
 UPDATE exchange.legitimization_processes
   SET provider_user_id=in_provider_account_id
      ,provider_legitimization_id=in_provider_legitimization_id
      ,expiration_time=GREATEST(expiration_time,in_expiration_time)
  WHERE h_payto=in_h_payto
    AND legitimization_process_serial_id=in_process_row
-   AND provider_section=in_provider_section;
+   AND provider_section=in_provider_section
+ RETURNING reserve_pub INTO orig_reserve_pub;
 out_ok = FOUND;
 
--- FIXME-Oec: update exchange reserve table to store in_birthday here!
--- UPDATE exchange.reserves SET birthday=in_birthday WHERE reserve_pub=X;
+UPDATE exchange.reserves SET birthday=in_birthday WHERE reserve_pub=orig_reserve_pub;
 
 IF in_require_aml
 THEN
diff --git a/src/exchangedb/pg_insert_kyc_requirement_for_account.c b/src/exchangedb/pg_insert_kyc_requirement_for_account.c
index 2552aae40..b0b38a336 100644
--- a/src/exchangedb/pg_insert_kyc_requirement_for_account.c
+++ b/src/exchangedb/pg_insert_kyc_requirement_for_account.c
@@ -30,11 +30,15 @@ TEH_PG_insert_kyc_requirement_for_account (
   void *cls,
   const char *provider_section,
   const struct TALER_PaytoHashP *h_payto,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   uint64_t *requirement_row)
 {
   struct PostgresClosure *pg = cls;
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_auto_from_type (h_payto),
+    (NULL == reserve_pub)
+    ? GNUNET_PQ_query_param_null ()
+    : GNUNET_PQ_query_param_auto_from_type (reserve_pub),
     GNUNET_PQ_query_param_string (provider_section),
     GNUNET_PQ_query_param_end
   };
@@ -48,9 +52,10 @@ TEH_PG_insert_kyc_requirement_for_account (
            "insert_legitimization_requirement",
            "INSERT INTO legitimization_requirements"
            "  (h_payto"
+           "  ,reserve_pub"
            "  ,required_checks"
            "  ) VALUES "
-           "  ($1, $2)"
+           "  ($1, $2, $3)"
            " ON CONFLICT (h_payto,required_checks) "
            "   DO UPDATE SET h_payto=$1" /* syntax requirement: dummy op */
            " RETURNING legitimization_requirement_serial_id");
diff --git a/src/exchangedb/pg_insert_kyc_requirement_for_account.h b/src/exchangedb/pg_insert_kyc_requirement_for_account.h
index c2f03b02a..331c8ba0c 100644
--- a/src/exchangedb/pg_insert_kyc_requirement_for_account.h
+++ b/src/exchangedb/pg_insert_kyc_requirement_for_account.h
@@ -32,6 +32,7 @@
  * @param cls closure
  * @param provider_section provider that must be checked
  * @param h_payto account that must be KYC'ed
+ * @param reserve_pub if the account is a reserve, its public key.  Maybe NULL
  * @param[out] requirement_row set to legitimization requirement row for this check
  * @return database transaction status
  */
@@ -40,6 +41,7 @@ TEH_PG_insert_kyc_requirement_for_account (
   void *cls,
   const char *provider_section,
   const struct TALER_PaytoHashP *h_payto,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   uint64_t *requirement_row);
 
 #endif
diff --git a/src/exchangedb/pg_insert_kyc_requirement_process.c b/src/exchangedb/pg_insert_kyc_requirement_process.c
index f1ea5b490..ddd765b99 100644
--- a/src/exchangedb/pg_insert_kyc_requirement_process.c
+++ b/src/exchangedb/pg_insert_kyc_requirement_process.c
@@ -24,6 +24,7 @@
 #include "taler_pq_lib.h"
 #include "pg_insert_kyc_requirement_process.h"
 #include "pg_helper.h"
+#include <gnunet/gnunet_pq_lib.h>
 
 enum GNUNET_DB_QueryStatus
 TEH_PG_insert_kyc_requirement_process (
@@ -32,6 +33,7 @@ TEH_PG_insert_kyc_requirement_process (
   const char *provider_section,
   const char *provider_account_id,
   const char *provider_legitimization_id,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   uint64_t *process_row)
 {
   struct PostgresClosure *pg = cls;
@@ -44,6 +46,9 @@ TEH_PG_insert_kyc_requirement_process (
     (NULL != provider_legitimization_id)
     ? GNUNET_PQ_query_param_string (provider_legitimization_id)
     : GNUNET_PQ_query_param_null (),
+    (NULL != reserve_pub)
+    ? GNUNET_PQ_query_param_auto_from_type (reserve_pub)
+    : GNUNET_PQ_query_param_null (),
     GNUNET_PQ_query_param_end
   };
   struct GNUNET_PQ_ResultSpec rs[] = {
@@ -60,12 +65,14 @@ TEH_PG_insert_kyc_requirement_process (
            "  ,provider_section"
            "  ,provider_user_id"
            "  ,provider_legitimization_id"
+           "  ,reserve_pub"
            "  ) VALUES "
-           "  ($1, $2, $3, $4)"
+           "  ($1, $2, $3, $4, $5)"
            " ON CONFLICT (h_payto,provider_section) "
            "   DO UPDATE SET"
            "      provider_user_id=$3"
            "     ,provider_legitimization_id=$4"
+           "     ,reserve_pub=$5"
            " RETURNING legitimization_process_serial_id");
   return GNUNET_PQ_eval_prepared_singleton_select (
     pg->conn,
diff --git a/src/exchangedb/pg_insert_kyc_requirement_process.h b/src/exchangedb/pg_insert_kyc_requirement_process.h
index df21db8cd..af90b8c14 100644
--- a/src/exchangedb/pg_insert_kyc_requirement_process.h
+++ b/src/exchangedb/pg_insert_kyc_requirement_process.h
@@ -34,6 +34,7 @@
  * @param provider_section provider that must be checked
  * @param provider_account_id provider account ID
  * @param provider_legitimization_id provider legitimization ID
+ * @param reserve_pub if the processes is related to a reserve, the reserve's public key, NULL otherwise
  * @param[out] process_row row the process is stored under
  * @return database transaction status
  */
@@ -44,6 +45,7 @@ TEH_PG_insert_kyc_requirement_process (
   const char *provider_section,
   const char *provider_account_id,
   const char *provider_legitimization_id,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   uint64_t *process_row);
 
 #endif
diff --git a/src/exchangedb/pg_lookup_kyc_requirement_by_row.c b/src/exchangedb/pg_lookup_kyc_requirement_by_row.c
index 6f9d76786..a167c0458 100644
--- a/src/exchangedb/pg_lookup_kyc_requirement_by_row.c
+++ b/src/exchangedb/pg_lookup_kyc_requirement_by_row.c
@@ -31,7 +31,8 @@ TEH_PG_lookup_kyc_requirement_by_row (
   uint64_t requirement_row,
   char **requirements,
   enum TALER_AmlDecisionState *aml_status,
-  struct TALER_PaytoHashP *h_payto)
+  struct TALER_PaytoHashP *h_payto,
+  struct TALER_ReservePublicKeyP **reserve_pub)
 {
   struct PostgresClosure *pg = cls;
   uint32_t status = TALER_AML_NORMAL;
@@ -39,12 +40,19 @@ TEH_PG_lookup_kyc_requirement_by_row (
     GNUNET_PQ_query_param_uint64 (&requirement_row),
     GNUNET_PQ_query_param_end
   };
+  bool no_reserve_pub;
+  struct TALER_ReservePublicKeyP *rp =
+    GNUNET_new (struct TALER_ReservePublicKeyP);
   struct GNUNET_PQ_ResultSpec rs[] = {
     GNUNET_PQ_result_spec_string ("required_checks",
                                   requirements),
     GNUNET_PQ_result_spec_auto_from_type ("h_payto",
                                           h_payto),
     GNUNET_PQ_result_spec_allow_null (
+      GNUNET_PQ_result_spec_auto_from_type ("reserve_pub",
+                                            rp),
+      &no_reserve_pub),
+    GNUNET_PQ_result_spec_allow_null (
       GNUNET_PQ_result_spec_uint32 ("status",
                                     &status),
       NULL),
@@ -57,6 +65,7 @@ TEH_PG_lookup_kyc_requirement_by_row (
            "SELECT "
            " lr.required_checks"
            ",lr.h_payto"
+           ",lr.reserve_pub"
            ",aml.status"
            " FROM legitimization_requirements lr"
            " LEFT JOIN aml_status aml USING (h_payto)"
@@ -67,5 +76,11 @@ TEH_PG_lookup_kyc_requirement_by_row (
     params,
     rs);
   *aml_status = (enum TALER_AmlDecisionState) status;
+  if (no_reserve_pub)
+  {
+    GNUNET_free (rp);
+    rp = NULL;
+  }
+  *reserve_pub = rp;
   return qs;
 }
diff --git a/src/exchangedb/pg_lookup_kyc_requirement_by_row.h b/src/exchangedb/pg_lookup_kyc_requirement_by_row.h
index 3d223c985..54759f932 100644
--- a/src/exchangedb/pg_lookup_kyc_requirement_by_row.h
+++ b/src/exchangedb/pg_lookup_kyc_requirement_by_row.h
@@ -34,6 +34,7 @@
  * @param[out] requirements provider that must be checked
  * @param[out] aml_status set to the AML status of the account
  * @param[out] h_payto account that must be KYC'ed
+ * @param[out] reserve_pub if account is a reserve, its public key, NULL otherwise. Must be freed by caller
  * @return database transaction status
  */
 enum GNUNET_DB_QueryStatus
@@ -42,6 +43,7 @@ TEH_PG_lookup_kyc_requirement_by_row (
   uint64_t requirement_row,
   char **requirements,
   enum TALER_AmlDecisionState *aml_status,
-  struct TALER_PaytoHashP *h_payto);
+  struct TALER_PaytoHashP *h_payto,
+  struct TALER_ReservePublicKeyP **reserve_pub);
 
 #endif