libeufin

commit 57e371e614689d5a479cc197dffd454b1886e56e
parent 097e0498bf1f7c3dc7aa4ed3587f2adf59946fcd
Author: Florian Dold <dold@taler.net>
Date:   Wed,  4 Nov 2026 14:31:23 +0100

nexus: add withdrawal CSV export with salted IBAN hashes

Assisted-by: LLM

Diffstat:
A
contrib/export-nexus-withdrawals.sh
 | 
121
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 121 insertions(+), 0 deletions(-)
diff --git a/contrib/export-nexus-withdrawals.sh b/contrib/export-nexus-withdrawals.sh
@@ -0,0 +1,121 @@
+#!/bin/bash
+# This file is part of TALER
+# Copyright (C) 2026 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/>
+
+set -euo pipefail
+
+usage() {
+    cat <<'EOF'
+Usage: export-nexus-withdrawals.sh YYYY-MM-dd OUTPUT.csv [PSQL-CONNECTION]
+
+Export Nexus incoming transfers of type 'reserve', from midnight UTC on the
+given date (inclusive). Requires psql, OpenSSL, and PostgreSQL 12 or later.
+PSQL-CONNECTION is a database name or libpq connection string; if omitted,
+psql uses its usual PGDATABASE, PGHOST, PGUSER, etc. environment variables.
+
+Columns: incoming_transaction_id, execution_time_utc, amount, sender_iban_hash.
+Amounts use the currency configured for this Nexus instance (not stored in SQL).
+The hash is SHA-256(random 32-byte salt || uppercase IBAN). One fresh salt is
+used for the entire export, so senders can be grouped within a file but hashes
+change between runs. The salt is not included in the CSV or saved by the script.
+Names, payment subjects, and raw payto URIs are not exported.
+Unsupported/missing IBANs cause an error instead of silently dropping transfers.
+The output file is replaced only after a successful export, with mode 0600.
+
+Example:
+  export-nexus-withdrawals.sh 2026-01-01 withdrawals.csv 'dbname=libeufin-nexus'
+EOF
+}
+
+if [[ ${1:-} == --help || ${1:-} == -h ]]; then
+    usage
+    exit 0
+fi
+if (( $# < 2 || $# > 3 )); then
+    usage >&2
+    exit 1
+fi
+if [[ ! $1 =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ ]]; then
+    echo 'Start date must have the format YYYY-MM-dd.' >&2
+    exit 1
+fi
+
+since=$1
+output=$2
+connection=()
+if (( $# == 3 )); then
+    connection=(--dbname="$3")
+fi
+
+umask 077
+salt=$(openssl rand -hex 32)
+temporary=$(mktemp -- "${output}.tmp.XXXXXXXX")
+trap 'rm -f -- "$temporary"' EXIT
+
+# Feed the salt through stdin, keeping it out of the psql process arguments.
+# A repeatable-read snapshot keeps validation and export consistent.
+{
+    printf '\\set export_salt %s\n' "$salt"
+    cat <<'SQL'
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
+SET LOCAL TIME ZONE 'UTC';
+SET LOCAL DateStyle = 'ISO, YMD';
+
+-- Timestamps in Nexus are microseconds since the Unix epoch.
+SELECT (extract(epoch FROM :'since'::date::timestamptz) * 1000000)::bigint AS cutoff
+\gset
+
+-- Match both payto://iban/IBAN and payto://iban/BIC/IBAN, ignoring
+-- query parameters (including the sender's name). Fail closed on other forms.
+\set iban_pattern '^payto://iban/(?:[A-Z0-9]+/)?([A-Z]{2}[0-9]{2}[A-Z0-9]{11,30})(?:[?].*)?$'
+SELECT NOT EXISTS (
+    SELECT 1
+    FROM libeufin_nexus.incoming_transactions AS i
+    JOIN libeufin_nexus.talerable_incoming_transactions AS t
+      USING (incoming_transaction_id)
+    WHERE t.type = 'reserve' AND i.execution_time >= :cutoff
+      AND (i.debit_payto IS NULL OR i.debit_payto !~* :'iban_pattern')
+) AS valid_ibans
+\gset
+\if :valid_ibans
+\else
+    \echo 'Export failed: a withdrawal has a missing or unsupported IBAN payto URI.'
+    -- Make ON_ERROR_STOP terminate with a nonzero exit status.
+    DO $$ BEGIN RAISE EXCEPTION 'Cannot export all sender IBANs'; END $$;
+\endif
+
+COPY (
+    SELECT i.incoming_transaction_id,
+           to_char(
+               timestamptz 'epoch'
+                   + (i.execution_time / 1000000) * interval '1 second'
+                   + (i.execution_time % 1000000) * interval '1 microsecond',
+               'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
+           ) AS execution_time_utc,
+           (i.amount).val::text || '.' || lpad((i.amount).frac::text, 8, '0') AS amount,
+           encode(sha256(
+               decode(:'export_salt', 'hex')
+               || convert_to(upper(substring(i.debit_payto FROM ('(?i)' || :'iban_pattern'))), 'UTF8')
+           ), 'hex') AS sender_iban_hash
+    FROM libeufin_nexus.incoming_transactions AS i
+    JOIN libeufin_nexus.talerable_incoming_transactions AS t
+      USING (incoming_transaction_id)
+    WHERE t.type = 'reserve' AND i.execution_time >= :cutoff
+    ORDER BY i.execution_time, i.incoming_transaction_id
+) TO STDOUT WITH (FORMAT CSV, HEADER);
+COMMIT;
+SQL
+} | psql -X --quiet --set=ON_ERROR_STOP=1 --set=since="$since" "${connection[@]}" > "$temporary"
+
+mv -f -- "$temporary" "$output"