create token_families and spent_tokens tables

1 files changed, 62 insertions, 0 deletions

diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index d063ce64..29b7ba61 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -39,6 +39,68 @@ CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session
   ON merchant_contract_terms
   (merchant_serial,session_id);
 
+-------------------------- Tokens -----------------------------
+
+CREATE TABLE IF NOT EXISTS merchant_token_families
+  (token_family_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,name TEXT NOT NULL UNIQUE
+  ,description TEXT
+  ,description_i18n BYTEA NOT NULL
+  ,start_date INT8 NOT NULL
+  ,expiration_date INT8 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
+  );
+COMMENT ON TABLE merchant_token_families
+ IS 'Token families configured by the merchant.';
+COMMENT ON COLUMN merchant_token_families.name
+ IS 'Name of the token family.';
+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.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_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)
+  );
+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
+ 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.blind_sig
+ IS 'Blind signature for the spent token.';
 
 -- Complete transaction
 COMMIT;