challenger

commit 63b304a689f3dc2b5c80e2642b45946a01d0cc4e
parent 363904f29f5ddb14fcb1845c59242cd56b4babc7
Author: Christian Grothoff <christian@grothoff.org>
Date:   Sun, 16 Apr 2023 17:27:27 +0200

first sketch for schema

Diffstat:
M
src/challengerdb/challenger-0001.sql
 | 
69
++++++++++++++++++++++++++++++++++++++++++++++++---------------------

1 file changed, 48 insertions(+), 21 deletions(-)
diff --git a/src/challengerdb/challenger-0001.sql b/src/challengerdb/challenger-0001.sql
@@ -26,35 +26,62 @@ COMMENT ON SCHEMA challenger IS 'challenger data';
 SET search_path TO challenger;
 
 
+CREATE TABLE IF NOT EXISTS clients
+  (client_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  ,url VARCHAR PRIMARY KEY
+  ,psk BYTEA PRIMARY KEY CHECK (length(psk)=32)
+  );
+COMMENT ON TABLE clients
+ IS 'Which clients are eligible to access the OAuth 2.0 client';
+COMMENT ON COLUMN clients.url
+ IS 'URL of the clients where we would send data';
+COMMENT ON COLUMN clients.psk
+ IS 'Pre-shared key with the client';
 
-CREATE TABLE IF NOT EXISTS accounts
-  (account_pub BYTEA PRIMARY KEY CHECK (length(account_pub)=32)
-  ,expiration_date INT8 NOT NULL);
+CREATE INDEX IF NOT EXISTS clients_serial
+  ON clients (client_serial_id);
 
-CREATE INDEX IF NOT EXISTS accounts_expire ON
-  accounts (expiration_date);
 
+CREATE TABLE IF NOT EXISTS validations
+  (validation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  ,address VARCHAR PRIMARY KEY
+  ,last_pin INT4 NOT NULL
+  ,attempts_left INT4 DEFAULT(3)
+  ,last_tx_time INT8 NOT NULL
+  ,expiration_time INT8 NOT NULL
+ );
 
-CREATE TABLE IF NOT EXISTS payments
-  (account_pub BYTEA CHECK (length(account_pub)=32)
-  ,order_id VARCHAR PRIMARY KEY
-  ,token BYTEA CHECK (length(token)=16)
-  ,timestamp INT8 NOT NULL
-  ,amount_val INT8 NOT NULL
-  ,amount_frac INT4 NOT NULL
-  ,paid BOOLEAN NOT NULL DEFAULT FALSE);
+COMMENT ON TABLE validations
+  IS 'Active validations where we send a challenge to an address of a user';
+COMMENT ON COLUMN validations.address
+  IS 'Address we are validating';
+COMMENT ON COLUMN validations.last_pin
+  IS 'Last PIN code send to the user';
+COMMENT ON COLUMN validations.attempts_left
+  IS 'How many more attempts do we permit (guard against brute-forcing)';
+COMMENT ON COLUMN validations.last_tx_time
+  IS 'When did we last sent the challenge (guard against DDoS)';
+COMMENT ON COLUMN validations.expiration_time
+  IS 'When will the challenge expire';
 
-CREATE INDEX IF NOT EXISTS payments_timestamp ON
-  payments (paid,timestamp);
+CREATE INDEX IF NOT EXISTS validations_serial
+  ON validations (validation_serial_id);
+CREATE INDEX IF NOT EXISTS validations_expiration
+  ON validations (expiration_time);
 
 
-CREATE TABLE IF NOT EXISTS backups
-  (account_pub BYTEA PRIMARY KEY REFERENCES accounts (account_pub) ON DELETE CASCADE
-  ,account_sig BYTEA NOT NULL CHECK (length(account_sig)=64)
-  ,prev_hash BYTEA NOT NULL CHECK (length(prev_hash)=64)
-  ,backup_hash BYTEA NOT NULL CHECK (length(backup_hash)=64)
-  ,data BYTEA NOT NULL);
+CREATE TABLE IF NOT EXISTS grants
+  (grant_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  ,address VARCHAR NOT NULL
+  ,expiration_time INT8 NOT NULL
+  );
 
+COMMENT ON TABLE grants
+  IS 'Active grants where a client is authorized to access user data';
+COMMENT ON COLUMN grants.address
+  IS 'Address of the user';
+COMMENT ON COLUMN grants.expiration_time
+  IS 'When will the grant expire';
 
 -- Complete transaction
 COMMIT;