work on KYC support in auditor (WiP, not finished)

11 files changed, 470 insertions, 151 deletions

diff --git a/contrib/auditor-report.tex.j2 b/contrib/auditor-report.tex.j2
index d40dc0c8f..2443fd753 100644
--- a/contrib/auditor-report.tex.j2
+++ b/contrib/auditor-report.tex.j2
@@ -206,51 +206,101 @@ load. Small amounts of lag can occur in normal operation.
 The total amount the exchange currently lags behind in deposits is
 {\bf {{ wire.total_amount_lag }}}.
 
-Note that some lag is perfectly normal, as tiny amounts that are too small to be wired
-are deferred beyond the due date, hoping that additional transfers will push them above
-the tiny threshold. Below, we report {\em non-tiny} wire transfers that are lagging behind.
+Note that some lag is perfectly normal, as tiny amounts that are too small to
+be wired are deferred beyond the due date, hoping that additional transfers
+will push them above the tiny threshold. Below, we report {\em non-tiny} wire
+transfers that are lagging behind.
 
 % Table generation tested by testcase #1 in test-auditor.sh
 
 {% if wire.lag_details|length() == 0 %}
   {\bf No non-tiny wire transfers that are lagging behind detected.}
 {% else %}
-  \begin{longtable}{l|r|r|c}
-  {\bf Deadline} & {\bf Amount} & {\bf Row} & {\bf Claimed done} \\
-  \multicolumn{4}{l}{\bf Coin} \\
-  \multicolumn{4}{l}{\bf Target account} \\ \hline \hline
+  \begin{longtable}{l|r|r}
+  {\bf Deadline} & {\bf Amount} & {\bf Target account} \\ \hline \hline
 \endfirsthead
-  {\bf Deadline} & {\bf Amount} & {\bf Row} & {\bf Claimed done} \\
-  \multicolumn{4}{l}{\bf Coin} \\
-  \multicolumn{4}{l}{\bf Target account} \\ \hline \hline
+  {\bf Deadline} & {\bf Amount} & {\bf Target account} \\ \hline \hline
 \endhead
   \hline \hline
-  {\bf Deadline} & {\bf Amount} & {\bf Row} & {\bf Claimed done} \\
-  \multicolumn{4}{l}{\bf Coin} \\
-  \multicolumn{4}{l}{\bf Target account} \\
+  {\bf Deadline} & {\bf Amount} & {\bf Target account} \\
 \endfoot
   \hline \hline
-  {\bf Deadline} & {\bf Amount} & {\bf Row} & {\bf Claimed done} \\
-  \multicolumn{4}{l}{\bf Coin} \\
-  \multicolumn{4}{l}{\bf Target account} \\
+  {\bf Deadline} & {\bf Amount} & {\bf Target account} \\
     \caption{Lagging non-tiny transactions.}
     \label{table:lag}
 \endlastfoot
 {% for item in wire.lag_details %}
   {{ item.deadline }} &
+  {{ item.total_amount }} &
+  {\tt
+    {% if 'account' in item %}
+       {{ item.account }}
+    {% endif %}
+  } \\ \hline
+{% endfor %}
+  \end{longtable}
+{% endif %}
+
+
+
+{% if wire.lag_kyc_details|length() == 0 %}
+  {\bf No KYC-blocked non-tiny wire transfers that are lagging behind detected.}
+{% else %}
+  \begin{longtable}{l|r|c|r}
+  {\bf Deadline} & {\bf Amount} & {\bf Requirement} & {\bf Target account} \\ \hline \hline
+\endfirsthead
+  {\bf Deadline} & {\bf Amount} & {\bf Requirement} & {\bf Target account} \\ \hline \hline
+\endhead
+  \hline \hline
+  {\bf Deadline} & {\bf Amount} & {\bf Requirement} & {\bf Target account} \\
+\endfoot
+  \hline \hline
+  {\bf Deadline} & {\bf Amount} & {\bf Requirement} & {\bf Target account} \\
+    \caption{Lagging non-tiny transactions due to missing KYC data.}
+    \label{table:lag}
+\endlastfoot
+{% for item in wire.lag_kyc_details %}
+  {{ item.deadline }} &
   {{ item.amount }} &
-  {{ item.row }} &
-{% if 'claimed_done' in item %}
-  {{ item.claimed_done }}
+  {{ item.kyc_pending }} &
+  {\tt
+    {% if 'account' in item %}
+       {{ item.account }}
+    {% endif %}
+  } \\ \hline
+{% endfor %}
+  \end{longtable}
+{% endif %}
+
+
+{% if wire.lag_aml_details|length() == 0 %}
+  {\bf No non-tiny wire transfers that are lagging behind due to AML detected.}
 {% else %}
-  N/A
-{% endif %}  \\
-\nopagebreak
-  \multicolumn{4}{l}{ {\tt \small {{ item.coin_pub }} } } \\
+  \begin{longtable}{l|r|r}
+  {\bf Deadline} & {\bf Amount}/{\bf Limit} & {\bf AML status} \\
+  \multicolumn{3}{l}{\bf Target account} \\ \hline \hline
+\endfirsthead
+  {\bf Deadline} & {\bf Amount}/{\bf Limit} & {\bf AML status} \\
+  \multicolumn{4}{l}{\bf Target account} \\ \hline \hline
+\endhead
+  \hline \hline
+  {\bf Deadline} & {\bf Amount}/{\bf Limit} & {\bf AML status} \\
+  \multicolumn{4}{l}{\bf Target account} \\
+\endfoot
+  \hline \hline
+  {\bf Deadline} & {\bf Amount}/{\bf Limit} & {\bf AML status} \\
+  \multicolumn{4}{l}{\bf Target account} \\
+    \caption{Lagging non-tiny transactions due to AML decisions.}
+    \label{table:lag}
+\endlastfoot
+{% for item in wire.lag_aml_details %}
+  {{ item.deadline }} &
+  {{ item.amount }}/{{ item.aml_limit }} &
+  {{ item.aml_status }} &
 \nopagebreak
   \multicolumn{4}{l}{ {\tt
-    {% if 'payto_uri' in item.account %}
-       {{ item.account.payto_uri }}
+    {% if 'account' in item %}
+       {{ item.account }}
     {% endif %}
   } } \\ \hline
 {% endfor %}
diff --git a/debian/control b/debian/control
index 3ac37c829..4ef9863dc 100644
--- a/debian/control
+++ b/debian/control
@@ -7,6 +7,7 @@ Build-Depends:
  automake (>=1.11.1),
  autopoint,
  bash,
+ gcc-12,
  debhelper-compat (= 12),
  gettext,
  libgnunet-dev (>=0.20),
@@ -14,7 +15,7 @@ Build-Depends:
  libgcrypt20-dev (>=1.8),
  libgnutls28-dev (>=3.2.12),
  libidn2-dev,
- libjansson-dev,
+ libjansson-dev (>= 2.13),
  libltdl-dev (>=2.2),
  libmicrohttpd-dev (>=0.9.71),
  libpq-dev (>=13),
diff --git a/src/auditor/taler-helper-auditor-wire.c b/src/auditor/taler-helper-auditor-wire.c
index edfdd5af1..71775bd4b 100644
--- a/src/auditor/taler-helper-auditor-wire.c
+++ b/src/auditor/taler-helper-auditor-wire.c
@@ -223,6 +223,16 @@ static json_t *report_row_minor_inconsistencies;
 static json_t *report_lags;
 
 /**
+ * Array of reports about lagging transactions from deposits due to missing KYC.
+ */
+static json_t *report_kyc_lags;
+
+/**
+ * Array of reports about lagging transactions from deposits due to pending or frozen AML decisions.
+ */
+static json_t *report_aml_lags;
+
+/**
  * Array of reports about lagging transactions from reserve closures.
  */
 static json_t *report_closure_lags;
@@ -524,6 +534,10 @@ do_shutdown (void *cls)
         /* Tested in test-auditor.sh #1 */
         GNUNET_JSON_pack_array_steal ("lag_details",
                                       report_lags),
+        GNUNET_JSON_pack_array_steal ("lag_aml_details",
+                                      report_aml_lags),
+        GNUNET_JSON_pack_array_steal ("lag_kyc_details",
+                                      report_kyc_lags),
         /* Tested in test-auditor.sh #22 */
         TALER_JSON_pack_amount ("total_closure_amount_lag",
                                 &total_closure_amount_lag),
@@ -550,6 +564,8 @@ do_shutdown (void *cls)
     report_row_minor_inconsistencies = NULL;
     report_misattribution_in_inconsistencies = NULL;
     report_lags = NULL;
+    report_kyc_lags = NULL;
+    report_aml_lags = NULL;
     report_closure_lags = NULL;
     report_account_progress = NULL;
     report_wire_format_inconsistencies = NULL;
@@ -843,56 +859,94 @@ commit (enum GNUNET_DB_QueryStatus qs)
  * Function called on deposits that are past their due date
  * and have not yet seen a wire transfer.
  *
- * @param cls closure
- * @param rowid deposit table row of the coin's deposit
- * @param coin_pub public key of the coin
- * @param amount value of the deposit, including fee
+ * @param cls closure, points to a `struct GNUNET_TIME_Timestamp`
+ * @param total_amount value of the missing deposits, including fee
  * @param payto_uri where should the funds be wired
- * @param deadline what was the requested wire transfer deadline
- * @param done did the exchange claim that it made a transfer?
- *             NOTE: only valid in internal audit mode!
+ * @param deadline what was the earliest requested wire transfer deadline
+ * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement
+ * @param aml_status status of AML possibly blocking the transfer
+ * @param aml_limit current monthly AML limit
  */
 static void
 wire_missing_cb (void *cls,
-                 uint64_t rowid,
-                 const struct TALER_CoinSpendPublicKeyP *coin_pub,
-                 const struct TALER_Amount *amount,
+                 const struct TALER_Amount *total_amount,
                  const char *payto_uri,
                  struct GNUNET_TIME_Timestamp deadline,
-                 bool done)
+                 const char *kyc_pending,
+                 enum TALER_AmlDecisionState status,
+                 const struct TALER_Amount *aml_limit)
 {
+  struct GNUNET_TIME_Timestamp *nt = cls;
   json_t *rep;
 
+  *nt = GNUNET_TIME_timestamp_min (deadline,
+                                   *nt);
   (void) cls;
+  // TODO: maybe split up by category?
   TALER_ARL_amount_add (&total_amount_lag,
                         &total_amount_lag,
-                        amount);
+                        total_amount);
   /* For now, we simplify and only check that the
      amount was tiny */
-  if (0 > TALER_amount_cmp (amount,
+  if (0 > TALER_amount_cmp (total_amount,
                             &tiny_amount))
     return; /* acceptable, amount was tiny */
-  rep = GNUNET_JSON_PACK (
-    GNUNET_JSON_pack_uint64 ("row",
-                             rowid),
-    TALER_JSON_pack_amount ("amount",
-                            amount),
-    TALER_JSON_pack_time_abs_human ("deadline",
-                                    deadline.abs_time),
-    GNUNET_JSON_pack_data_auto ("coin_pub",
-                                coin_pub),
-    GNUNET_JSON_pack_string ("account",
-                             payto_uri));
-  if (internal_checks)
+  if (NULL != kyc_pending)
+  {
+    rep = GNUNET_JSON_PACK (
+      TALER_JSON_pack_amount ("total_amount",
+                              total_amount),
+      TALER_JSON_pack_time_abs_human ("deadline",
+                                      deadline.abs_time),
+      GNUNET_JSON_pack_string ("kyc_pending",
+                               kyc_pending),
+      GNUNET_JSON_pack_string ("account",
+                               payto_uri));
+    TALER_ARL_report (report_kyc_lags,
+                      rep);
+  }
+  else if (TALER_AML_NORMAL != status)
+  {
+    const char *sstatus;
+
+    switch (status)
+    {
+    case TALER_AML_NORMAL:
+      GNUNET_assert (0);
+      break;
+    case TALER_AML_PENDING:
+      sstatus = "pending";
+      break;
+    case TALER_AML_FROZEN:
+      sstatus = "frozen";
+      break;
+    }
+    rep = GNUNET_JSON_PACK (
+      TALER_JSON_pack_amount ("total_amount",
+                              total_amount),
+      TALER_JSON_pack_amount ("aml_limit",
+                              aml_limit),
+      TALER_JSON_pack_time_abs_human ("deadline",
+                                      deadline.abs_time),
+      GNUNET_JSON_pack_string ("aml_status",
+                               sstatus),
+      GNUNET_JSON_pack_string ("account",
+                               payto_uri));
+    TALER_ARL_report (report_aml_lags,
+                      rep);
+  }
+  else
   {
-    /* the 'done' bit is only useful in 'internal' mode */
-    GNUNET_assert (0 ==
-                   json_object_set (rep,
-                                    "claimed_done",
-                                    json_string ((done) ? "yes" : "no")));
+    rep = GNUNET_JSON_PACK (
+      TALER_JSON_pack_amount ("total_amount",
+                              total_amount),
+      TALER_JSON_pack_time_abs_human ("deadline",
+                                      deadline.abs_time),
+      GNUNET_JSON_pack_string ("account",
+                               payto_uri));
+    TALER_ARL_report (report_lags,
+                      rep);
   }
-  TALER_ARL_report (report_lags,
-                    rep);
 }
 
 
@@ -2391,6 +2445,10 @@ run (void *cls,
   GNUNET_assert (NULL !=
                  (report_lags = json_array ()));
   GNUNET_assert (NULL !=
+                 (report_aml_lags = json_array ()));
+  GNUNET_assert (NULL !=
+                 (report_kyc_lags = json_array ()));
+  GNUNET_assert (NULL !=
                  (report_closure_lags = json_array ()));
   GNUNET_assert (NULL !=
                  (report_account_progress = json_array ()));
diff --git a/src/exchange/taler-exchange-httpd_batch-deposit.c b/src/exchange/taler-exchange-httpd_batch-deposit.c
index 6bf70efb4..fc3217303 100644
--- a/src/exchange/taler-exchange-httpd_batch-deposit.c
+++ b/src/exchange/taler-exchange-httpd_batch-deposit.c
@@ -469,7 +469,7 @@ parse_coin (struct MHD_Connection *connection,
         &dc->h_wire,
         &bd->h_contract_terms,
         &bd->wallet_data_hash,
-        NULL != cdi->coin.no_age_commitment
+        cdi->coin.no_age_commitment
         ? NULL
         : &cdi->coin.h_age_commitment,
         NULL != dc->policy_json ? &dc->h_policy : NULL,
diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in
index 9a810aa2e..b202d75ff 100644
--- a/src/exchangedb/exchange-0002.sql.in
+++ b/src/exchangedb/exchange-0002.sql.in
@@ -35,6 +35,20 @@ CREATE TYPE exchange_do_array_reserve_insert_return_type
 COMMENT ON TYPE exchange_do_array_reserve_insert_return_type
   IS 'Return type for exchange_do_array_reserves_insert() stored procedure';
 
+CREATE TYPE exchange_do_select_deposits_missing_wire_return_type
+  AS
+  (
+    total_amount_with_fee taler_amount,
+    payto_uri TEXT,
+    kyc_pending TEXT, -- can be NULL
+    wire_deadline INT8,
+    aml_status INT4,
+    aml_limit taler_amount
+  );
+COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
+  IS 'Return type for exchange_do_select_deposits_missing_wire';
+
+
 #include "0002-denominations.sql"
 #include "0002-denomination_revocations.sql"
 #include "0002-wire_targets.sql"
diff --git a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql
new file mode 100644
index 000000000..9132e5b12
--- /dev/null
+++ b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql
@@ -0,0 +1,217 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2023 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/>
+--
+-- @author: Christian Grothoff
+
+--CREATE TYPE exchange_do_select_deposits_missing_wire_return_type
+--  AS
+--  (
+--    total_amount taler_amount,
+--    payto_uri TEXT,
+--    kyc_pending TEXT,
+--    wire_deadline INT8,
+--    aml_status INT4
+--  );
+
+-- FIXME: this function is not working as intended at all yet, work in progress!
+
+CREATE OR REPLACE FUNCTION exchange_do_select_deposits_missing_wire(
+  IN in_start_date INT8,
+  IN in_end_date INT8)
+RETURNS SETOF exchange_do_select_deposits_missing_wire_return_type
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  missing CURSOR
+  FOR
+  SELECT
+    bdep.batch_deposit_serial_id
+   ,bdep.wire_target_h_payto
+   ,bdep.wire_deadline
+    FROM batch_deposits bdep
+    WHERE bdep.wire_deadline >= in_start_date
+      AND bdep.wire_deadline < in_end_date
+      AND NOT EXISTS (SELECT 1
+        FROM aggregation_tracking atr
+        WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id));
+DECLARE
+  my_earliest_deadline INT8; -- earliest deadline that was missed
+DECLARE
+  my_total_val INT8; -- all deposits without wire
+DECLARE
+  my_total_frac INT8; -- all deposits without wire (fraction, not normalized)
+DECLARE
+  my_refund_val INT8; -- all refunds without wire
+DECLARE
+  my_refund_frac INT8; -- all refunds without wire (fraction, not normalized)
+DECLARE
+  my_wire_target_h_payto BYTEA; -- hash of the target account
+DECLARE
+  my_payto_uri TEXT; -- the target account
+DECLARE
+  my_kyc_pending TEXT; -- pending KYC operations
+DECLARE
+  my_required_checks TEXT[];
+DECLARE
+  my_aml_status INT4; -- AML status (0: normal)
+DECLARE
+  my_total taler_amount; -- amount that was originally deposited
+DECLARE
+  my_batch_record RECORD;
+DECLARE
+  my_aml_data RECORD;
+DECLARE
+  my_aml_threshold taler_amount; -- threshold above which AML is triggered
+DECLARE
+  i RECORD;
+BEGIN
+
+OPEN missing;
+LOOP
+  FETCH NEXT FROM missing INTO i;
+  EXIT WHEN NOT FOUND;
+
+  IF ( (my_earliest_deadline IS NULL) OR
+       (my_earliest_deadline > i.wire_deadline) )
+  THEN
+    my_earliest_deadline = i.wire_deadline;
+  END IF;
+  SELECT
+    SUM((cdep.amount_with_fee).val) AS total_val
+   ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac
+   ,SUM((r.amount_with_fee).val) AS refund_val
+   ,SUM((r.amount_with_fee).frac::INT8) AS refund_frac
+    INTO
+      my_batch_record
+    FROM coin_deposits cdep
+    LEFT JOIN refunds r
+      ON ( (r.coin_pub = cdep.coin_pub) AND
+           (r.batch_deposit_serial_id = cdep.batch_deposit_serial_id) )
+    WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id;
+--    GROUP BY bdep.wire_target_h_payto; -- maybe use temporary table intead of cursor, or accumulate C-side?
+
+  my_total_val=my_batch_record.total_val;
+  my_total_frac=my_batch_record.total_frac;
+  my_refund_val=my_batch_record.refund_val;
+  my_refund_frac=my_batch_record.refund_frac;
+
+  RAISE WARNING 'tval: %', my_total_val;
+  RAISE WARNING 'tfrac: %', my_total_frac;
+  RAISE WARNING 'rval: %', my_refund_val;
+  RAISE WARNING 'rfrac: %', my_refund_frac;
+
+  IF my_refund_val IS NOT NULL
+  THEN
+    -- subtract refunds from total
+    my_total_val = my_total_val - my_refund_val;
+    -- note: frac could go negative here, that's OK
+    my_total_frac = my_total_frac - my_refund_frac;
+  END IF;
+  -- Normalize total amount
+  IF my_total_frac < 0
+  THEN
+    my_total.val = my_total_val - 1 + my_total_frac / 100000000;
+    my_total.frac = 100000000 + my_total_frac % 100000000;
+  ELSE
+    my_total.val = my_total_val + my_total_frac / 100000000;
+    my_total.frac = my_total_frac % 100000000;
+  END IF;
+  RAISE WARNING 'val: %', my_total.val;
+  RAISE WARNING 'frac: %', my_total.frac;
+  ASSERT my_total.frac >= 0, 'Normalized amount fraction must be non-negative';
+  ASSERT my_total.frac < 100000000, 'Normalized amount fraction must be below 100000000';
+
+  IF (my_total.val < 0)
+  THEN
+    -- Refunds above deposits. That's a problem, but not one for this auditor pass.
+    CONTINUE;
+  END IF;
+
+  -- Note: total amount here is NOT the exact amount due for the
+  -- wire transfer, as we did not consider deposit, refund and wire fees.
+  -- The amount given in the report is thus ONLY indicative of the non-refunded
+  -- gross amount, not the net transfer amount.
+
+  IF 0 = my_total_val + my_total_frac
+  THEN
+    -- full refund, skip report entirely
+    CONTINUE;
+  END IF;
+
+  -- Fetch payto URI
+  -- NOTE: we want to group by my_wire_target_h_payto and not do this repeatedly per batch deposit!
+  my_payto_uri = NULL;
+  SELECT payto_uri
+    INTO my_payto_uri
+    FROM wire_targets
+   WHERE wire_target_h_payto=my_wire_target_h_payto;
+
+  -- Get last AML decision
+  SELECT
+      new_threshold
+     ,kyc_requirements
+     ,new_status
+    INTO
+     my_aml_data
+     FROM aml_history
+    WHERE h_payto=my_wire_target_h_payto
+    ORDER BY aml_history_serial_id -- get last decision
+      DESC LIMIT 1;
+  IF FOUND
+  THEN
+    my_aml_threshold=my_aml_data.new_threshold;
+    my_kyc_pending=my_aml_data.kyc_requirements;
+    my_aml_status=my_aml_data.kyc_status;
+  ELSE
+    my_aml_threshold=NULL;
+    my_kyc_pending=NULL;
+    my_aml_status=0;
+  END IF;
+  IF 0 != my_aml_status
+  THEN
+    RETURN NEXT (
+       my_total
+      ,my_payto_uri
+      ,my_kyc_pending
+      ,my_earliest_deadline
+      ,my_aml_status
+      ,NULL);
+  END IF;
+
+  -- Check KYC status
+  SELECT string_to_array (required_checks, ' ')
+    INTO my_required_checks
+    FROM legitimization_requirements
+    WHERE h_payto=my_wire_target_h_payto;
+
+
+--  PERFORM -- provider
+--    FROM kyc_attributes
+--    WHERE legitimization_serial=my_legitimization_serial;
+  -- FIXME: can't tell if providers cover all required checks from DB!!!
+  -- Idea: expand kyc_attributes table with list of satisfied checks!??!
+
+    RETURN NEXT (
+       my_total
+      ,my_payto_uri
+      ,my_kyc_pending
+      ,my_earliest_deadline
+      ,my_aml_status
+      ,NULL::taler_amount);
+
+END LOOP;
+CLOSE missing;
+RETURN;
+END $$;
diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.c b/src/exchangedb/pg_select_batch_deposits_missing_wire.c
index 7118135a1..25b532ad6 100644
--- a/src/exchangedb/pg_select_batch_deposits_missing_wire.c
+++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.c
@@ -69,25 +69,34 @@ missing_wire_cb (void *cls,
 
   while (0 < num_results)
   {
-    uint64_t rowid;
-    struct TALER_CoinSpendPublicKeyP coin_pub;
-    struct TALER_Amount amount;
-    char *payto_uri;
+    struct TALER_Amount total_amount;
+    struct TALER_Amount aml_limit;
+    char *payto_uri = NULL;
+    char *kyc_pending = NULL;
+    uint32_t aml_status32 = TALER_AML_NORMAL;
     struct GNUNET_TIME_Timestamp deadline;
-    bool done;
+    bool no_aml_limit;
     struct GNUNET_PQ_ResultSpec rs[] = {
-      GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id",
-                                    &rowid),
-      GNUNET_PQ_result_spec_auto_from_type ("coin_pub",
-                                            &coin_pub),
-      TALER_PQ_RESULT_SPEC_AMOUNT ("amount_with_fee",
-                                   &amount),
-      GNUNET_PQ_result_spec_string ("payto_uri",
-                                    &payto_uri),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount_with_fee",
+                                   &total_amount),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_string ("payto_uri",
+                                      &payto_uri),
+        NULL),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_string ("kyc_pending",
+                                      &kyc_pending),
+        NULL),
       GNUNET_PQ_result_spec_timestamp ("wire_deadline",
                                        &deadline),
-      GNUNET_PQ_result_spec_bool ("done",
-                                  &done),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_uint32 ("aml_status",
+                                      &aml_status32),
+        NULL),
+      GNUNET_PQ_result_spec_allow_null (
+        TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit",
+                                     &aml_limit),
+        &no_aml_limit),
       GNUNET_PQ_result_spec_end
     };
 
@@ -101,12 +110,12 @@ missing_wire_cb (void *cls,
       return;
     }
     mwc->cb (mwc->cb_cls,
-             rowid,
-             &coin_pub,
-             &amount,
+             &total_amount,
              payto_uri,
              deadline,
-             done);
+             kyc_pending,
+             (enum TALER_AmlDecisionState) aml_status32,
+             no_aml_limit ? NULL : &aml_limit);
     GNUNET_PQ_cleanup_result (rs);
   }
 }
@@ -134,39 +143,17 @@ TEH_PG_select_batch_deposits_missing_wire (
   };
   enum GNUNET_DB_QueryStatus qs;
 
-  // FIXME: used by the auditor; can probably be done
-  // smarter by checking if 'done' or 'blocked'
-  // are set correctly when going over deposits, instead
-  // of JOINing with refunds.
-  // Also unclear why we return by coin_pub here;
-  // Also fails to check overdue in case of PARTIAL refunds.
-
   PREPARE (pg,
            "deposits_get_overdue",
            "SELECT"
-           " bdep.batch_deposit_serial_id"
-           ",cdep.coin_pub"
-           ",cdep.amount_with_fee"
-           ",wt.payto_uri"
-           ",bdep.wire_deadline"
-           ",bdep.done"
-           " FROM batch_deposits bdep"
-           "   JOIN coin_deposits cdep"
-           "     USING (batch_deposit_serial_id)"
-           "   JOIN known_coins"
-           "     USING (coin_pub)"
-           "   JOIN wire_targets wt"
-           "     USING (wire_target_h_payto)"
-           " WHERE bdep.wire_deadline >= $1"
-           " AND bdep.wire_deadline < $2"
-           " AND NOT (EXISTS (SELECT 1"
-           "            FROM refunds r"
-           "            WHERE (r.coin_pub = cdep.coin_pub)"
-           "               AND (r.batch_deposit_serial_id = bdep.batch_deposit_serial_id))"
-           "       OR EXISTS (SELECT 1"
-           "            FROM aggregation_tracking atr"
-           "            WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id)))"
-           " ORDER BY bdep.wire_deadline ASC");
+           " total_amount_with_fee"
+           ",payto_uri"
+           ",kyc_pending"
+           ",wire_deadline"
+           ",aml_status"
+           ",aml_limit"
+           " FROM exchange_do_select_deposits_missing_wire"
+           " ($1,$2);");
   qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                              "deposits_get_overdue",
                                              params,
diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.h b/src/exchangedb/pg_select_batch_deposits_missing_wire.h
index 697baa837..27e6b0850 100644
--- a/src/exchangedb/pg_select_batch_deposits_missing_wire.h
+++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.h
@@ -37,13 +37,12 @@
  * @return transaction status code
  */
 enum GNUNET_DB_QueryStatus
-TEH_PG_select_batch_deposits_missing_wire (void *cls,
-                                           struct GNUNET_TIME_Timestamp
-                                           start_date,
-                                           struct GNUNET_TIME_Timestamp
-                                           end_date,
-                                           TALER_EXCHANGEDB_WireMissingCallback
-                                           cb,
-                                           void *cb_cls);
+TEH_PG_select_batch_deposits_missing_wire (
+  void *cls,
+  struct GNUNET_TIME_Timestamp start_date,
+  struct GNUNET_TIME_Timestamp end_date,
+  TALER_EXCHANGEDB_WireMissingCallback
+  cb,
+  void *cb_cls);
 
 #endif
diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in
index cc67249fd..4a23ec742 100644
--- a/src/exchangedb/procedures.sql.in
+++ b/src/exchangedb/procedures.sql.in
@@ -26,6 +26,7 @@ SET search_path TO exchange;
 #include "exchange_do_recoup_by_reserve.sql"
 #include "exchange_do_deposit.sql"
 #include "exchange_do_melt.sql"
+#include "exchange_do_select_deposits_missing_wire.sql"
 #include "exchange_do_refund.sql"
 #include "exchange_do_recoup_to_reserve.sql"
 #include "exchange_do_recoup_to_coin.sql"
diff --git a/src/exchangedb/test_exchangedb.c b/src/exchangedb/test_exchangedb.c
index 6e1d3a006..081126637 100644
--- a/src/exchangedb/test_exchangedb.c
+++ b/src/exchangedb/test_exchangedb.c
@@ -1117,44 +1117,36 @@ drop:
  * and have not yet seen a wire transfer.
  *
  * @param cls closure a `struct TALER_EXCHANGEDB_Deposit *`
- * @param rowid deposit table row of the coin's deposit
- * @param coin_pub public key of the coin
- * @param amount value of the deposit, including fee
- * @param payto_uri where should the funds be wired
- * @param deadline what was the requested wire transfer deadline
- * @param done did the exchange claim that it made a transfer?
+ * @param total_amount value of all missing deposits, including fees
+ * @param payto_uri where should the funds be wired; URI in payto://-format
+ * @param deadline what was the earliest requested wire transfer deadline
+ * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement
+ * @param aml_status status of AML possibly blocking the transfer
+ * @param aml_limit current monthly AML limit
  */
 static void
-wire_missing_cb (void *cls,
-                 uint64_t rowid,
-                 const struct TALER_CoinSpendPublicKeyP *coin_pub,
-                 const struct TALER_Amount *amount,
-                 const char *payto_uri,
-                 struct GNUNET_TIME_Timestamp deadline,
-                 bool done)
+wire_missing_cb (
+  void *cls,
+  const struct TALER_Amount *total_amount,
+  const char *payto_uri,
+  struct GNUNET_TIME_Timestamp deadline,
+  const char *kyc_pending,
+  enum TALER_AmlDecisionState status,
+  const struct TALER_Amount *aml_limit)
 {
   const struct TALER_EXCHANGEDB_CoinDepositInformation *deposit = cls;
 
   (void) payto_uri;
   (void) deadline;
-  (void) rowid;
-  if (done)
-  {
-    GNUNET_break (0);
-    result = 66;
-  }
-  if (0 != TALER_amount_cmp (amount,
+  (void) kyc_pending;
+  (void) status;
+  (void) aml_limit;
+  if (0 != TALER_amount_cmp (total_amount,
                              &deposit->amount_with_fee))
   {
     GNUNET_break (0);
     result = 66;
   }
-  if (0 != GNUNET_memcmp (coin_pub,
-                          &deposit->coin.coin_pub))
-  {
-    GNUNET_break (0);
-    result = 66;
-  }
 }
 
 
diff --git a/src/include/taler_exchangedb_plugin.h b/src/include/taler_exchangedb_plugin.h
index 8be26a75d..9f1060601 100644
--- a/src/include/taler_exchangedb_plugin.h
+++ b/src/include/taler_exchangedb_plugin.h
@@ -3421,22 +3421,22 @@ typedef void
  * and have not yet seen a wire transfer.
  *
  * @param cls closure
- * @param rowid deposit table row of the coin's deposit
- * @param coin_pub public key of the coin
- * @param amount value of the deposit, including fee
+ * @param total_amount value of all missing deposits, including fees
  * @param payto_uri where should the funds be wired; URI in payto://-format
- * @param deadline what was the requested wire transfer deadline
- * @param done did the exchange claim that it made a transfer?
+ * @param deadline what was the earliest requested wire transfer deadline
+ * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement
+ * @param aml_status status of AML possibly blocking the transfer
+ * @param aml_limit current monthly AML limit
  */
 typedef void
 (*TALER_EXCHANGEDB_WireMissingCallback)(
   void *cls,
-  uint64_t rowid,
-  const struct TALER_CoinSpendPublicKeyP *coin_pub,
-  const struct TALER_Amount *amount,
+  const struct TALER_Amount *total_amount,
   const char *payto_uri,
   struct GNUNET_TIME_Timestamp deadline,
-  bool done);
+  const char *kyc_pending,
+  enum TALER_AmlDecisionState status,
+  const struct TALER_Amount *aml_limit);
 
 
 /**