create separate merchant_token_keys table

1 files changed, 60 insertions, 24 deletions

diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index 29b7ba61..380645e8 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -46,13 +46,8 @@ CREATE TABLE IF NOT EXISTS merchant_token_families
   ,name TEXT NOT NULL UNIQUE
   ,description TEXT
   ,description_i18n BYTEA NOT NULL
-  ,start_date INT8 NOT NULL
-  ,expiration_date INT8 NOT NULL
+  ,duration INTERVAL NOT NULL
   ,kind TEXT NOT NULL CHECK (kind IN ('subscription', 'discount'))
-  ,pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(pub)=32)
-  ,h_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(h_pub)=64)
-  ,priv BYTEA UNIQUE CHECK (LENGTH(priv)=32)
-  ,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs'))
   ,issued BIGINT DEFAULT 0
   ,redeemed BIGINT DEFAULT 0
   );
@@ -64,43 +59,84 @@ COMMENT ON COLUMN merchant_token_families.description
  IS 'Human-readable description or details about the token family.';
 COMMENT ON COLUMN merchant_token_families.description_i18n
  IS 'JSON map from IETF BCP 47 language tags to localized descriptions';
-COMMENT ON COLUMN merchant_token_families.start_date
- IS 'Start date/time for the validity of the token family.';
-COMMENT ON COLUMN merchant_token_families.expiration_date
- IS 'Expiration date/time for the validity of the token family.';
+COMMENT ON COLUMN merchant_token_families.duration
+ IS 'Duration of the token family.';
 COMMENT ON COLUMN merchant_token_families.kind
  IS 'Kind of the token (e.g., subscription, discount).';
-COMMENT ON COLUMN merchant_token_families.pub
- IS 'Public key of the token family.';
-COMMENT ON COLUMN merchant_token_families.h_pub
- IS 'Hash of the public key for quick lookup.';
-COMMENT ON COLUMN merchant_token_families.priv
- IS 'Private key of the token family; can be NULL if no more tokens of this familiy should be issued.';
-COMMENT ON COLUMN merchant_token_families.cipher
- IS 'Cipher used (rsa or cs).';
 COMMENT ON COLUMN merchant_token_families.issued
  IS 'Counter for the number of tokens issued for this token family.';
 COMMENT ON COLUMN merchant_token_families.redeemed
  IS 'Counter for the number of tokens redeemed for this token family.';
 
 
+CREATE TABLE IF NOT EXISTS merchant_token_keys
+  (token_keys_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
+  ,start_date TIMESTAMP NOT NULL
+  ,expiration_date TIMESTAMP NOT NULL -- TODO: Do we need this or can we calculate it from the duration?
+  ,pub BYTEA NOT NULL
+  ,h_pub BYTEA NOT NULL UNIQUE
+  ,priv BYTEA
+  ,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs'))
+  ,UNIQUE (token_family_serial, start_date)
+  );
+
+COMMENT ON TABLE merchant_token_keys
+ IS 'Keys for token families.';
+COMMENT ON COLUMN merchant_token_keys.token_family_serial
+ IS 'Token family to which the key belongs.';
+COMMENT ON COLUMN merchant_token_keys.start_date
+ IS 'Start date/time for the validity of the token key.';
+COMMENT ON COLUMN merchant_token_keys.expiration_date
+ IS 'Expiration date/time for the validity of the token key.';
+COMMENT ON COLUMN merchant_token_keys.pub
+ IS 'Public key of the token family.';
+COMMENT ON COLUMN merchant_token_keys.h_pub
+ IS 'Hash of the public key for quick lookup.';
+COMMENT ON COLUMN merchant_token_keys.priv
+ IS 'Private key of the token family; can be NULL if no more tokens of this familiy should be issued.';
+COMMENT ON COLUMN merchant_token_keys.cipher
+ IS 'Cipher used (rsa or cs).';
+
+CREATE OR REPLACE FUNCTION merchant_token_calculate_expiration_date()
+RETURNS TRIGGER AS $$
+BEGIN
+    NEW.expiration_date := NEW.start_date + (SELECT duration
+                                             FROM merchant_token_families
+                                             WHERE token_family_serial = NEW.token_family_serial);
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION merchant_token_calculate_expiration_date
+ IS 'Calculates the expiration date for a token key based on the start date and the associated token family duration.';
+
+CREATE TRIGGER merchant_token_set_expiration_date
+BEFORE INSERT ON merchant_token_keys
+FOR EACH ROW
+EXECUTE FUNCTION merchant_token_calculate_expiration_date();
+COMMENT ON TRIGGER merchant_token_set_expiration_date ON merchant_token_keys
+ IS 'Trigger to set the expiration date for a token key before insertion.';
+
 CREATE TABLE IF NOT EXISTS merchant_spent_tokens
   (spent_token_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,order_serial BIGINT REFERENCES merchant_orders(order_serial) ON DELETE CASCADE
-  ,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
-  ,token_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(token_pub)=32)
-  ,blind_sig BYTEA NOT NULL CHECK (LENGTH(blind_sig)=64)
+  ,order_serial BIGINT REFERENCES merchant_contract_terms(order_serial) ON DELETE CASCADE
+  ,token_key_serial BIGINT REFERENCES merchant_token_key(token_key_serial) ON DELETE CASCADE
+  ,token_pub BYTEA NOT NULL UNIQUE
+  ,token_sig BYTEA NOT NULL CHECK (LENGTH(token_pub)=64)
+  ,blind_sig BYTEA NOT NULL
   );
 COMMENT ON TABLE merchant_spent_tokens
  IS 'Tokens that have been spent by customers.';
 COMMENT ON COLUMN merchant_spent_tokens.order_id
  IS 'Order the token was spent on.';
-COMMENT ON COLUMN merchant_spent_tokens.token_family_id
+COMMENT ON COLUMN merchant_spent_tokens.token_key_serial
  IS 'Token family to which the spent token belongs.';
 COMMENT ON COLUMN merchant_spent_tokens.token_pub
  IS 'Public key of the spent token.';
+COMMENT ON COLUMN merchant_spent_tokens.token_sig
+ IS 'Signature that the token was spent on specified order.';
 COMMENT ON COLUMN merchant_spent_tokens.blind_sig
- IS 'Blind signature for the spent token.';
+ IS 'Blind signature for the spent token to prove validity of token.';
 
 -- Complete transaction
 COMMIT;