merchant

commit 5531fb6d4a63ec99c5ba585843f95ae881e79c9c
parent 8c8c1cf59d61cc8cad020d699d193bf657a219d7
Author: Christian Grothoff <grothoff@gnunet.org>
Date:   Mon, 11 Aug 2025 14:02:34 +0200

auth endpoint design

Diffstat:
A
src/backenddb/future.sql
 | 
50
++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 50 insertions(+), 0 deletions(-)
diff --git a/src/backenddb/future.sql b/src/backenddb/future.sql
@@ -0,0 +1,50 @@
+
+CREATE TYPE tan_enum
+  AS ENUM ('sms', 'email');
+
+CREATE TYPE op_enum
+  AS ENUM ('account_provision', 'account_bank_reconfig', 'account_auth_reconfig', 'account_delete', 'create_token');
+
+CREATE TABLE tan_challenges
+  (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  ,body TEXT NOT NULL -- FIXME: use hash instead of plaintext, may contain passwords!
+  ,op op_enum NOT NULL
+  ,code TEXT NOT NULL
+  ,creation_date INT8 NOT NULL
+  ,expiration_date INT8 NOT NULL
+  ,retransmission_date INT8 NOT NULL DEFAULT 0
+  ,confirmation_date INT8 DEFAULT NULL
+  ,retry_counter INT4 NOT NULL
+  ,merchant_serial INT8 NOT NULL
+    REFERENCES merchant_instances(merchant_serial)
+    ON DELETE CASCADE
+  ,tan_channel tan_enum NULL DEFAULT NULL
+  ,tan_info TEXT NULL DEFAULT NULL -- FIXME: when is this NULL?
+);
+COMMENT ON TABLE tan_challenges
+  IS 'Stores 2FA challenges';
+COMMENT ON COLUMN tan_challenges.op
+  IS 'The protected operation to run after the challenge';
+COMMENT ON COLUMN tan_challenges.body
+  IS 'Request body of the original request that triggered the challenge, to be replayed once the challenge is satisfied. FIXME: probably should just store the hash of it.';
+COMMENT ON COLUMN tan_challenges.code
+  IS 'The pin code sent to the user and verified';
+COMMENT ON COLUMN tan_challenges.creation_date
+  IS 'Creation date of the code';
+COMMENT ON COLUMN tan_challenges.retransmission_date
+  IS 'When did we last transmit the challenge to the user';
+COMMENT ON COLUMN tan_challenges.expiration_date
+  IS 'When will the code expire';
+COMMENT ON COLUMN tan_challenges.confirmation_date
+  IS 'When was this challenge successfully verified, NULL if pending';
+COMMENT ON COLUMN tan_challenges.retry_counter
+  IS 'How many tries are left for this code; must be > 0';
+COMMENT ON COLUMN tan_challenges.tan_channel
+  IS 'TAN channel to use, if NULL use customer configured one';
+COMMENT ON COLUMN tan_challenges.tan_info
+  IS 'TAN information message to use to inform the user about where the challenge was sent';
+
+CREATE INDEX tan_challenges_expiration_index
+  ON tan_challenges (expiration_date);
+COMMENT ON INDEX tan_challenges_expiration_index
+  IS 'for garbage collection';