-- -- This file is part of Anastasis -- Copyright (C) 2020, 2021 Anastasis SARL SA -- -- ANASTASIS 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. -- -- ANASTASIS 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 -- ANASTASIS; see the file COPYING. If not, see -- -- Everything in one big transaction BEGIN; -- Check patch versioning is in place. SELECT _v.register_patch('stasis-0001', NULL, NULL); CREATE TABLE IF NOT EXISTS anastasis_truth_payment (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32), amount_val INT8 NOT NULL, amount_frac INT4 NOT NULL, expiration INT8 NOT NULL); COMMENT ON TABLE anastasis_truth_payment IS 'Records about payments for truth uploads'; COMMENT ON COLUMN anastasis_truth_payment.truth_uuid IS 'Identifier of the truth'; COMMENT ON COLUMN anastasis_truth_payment.amount_val IS 'Amount we were paid'; COMMENT ON COLUMN anastasis_truth_payment.amount_frac IS 'Amount we were paid fraction'; COMMENT ON COLUMN anastasis_truth_payment.expiration IS 'At which date will the truth payment expire'; CREATE TABLE IF NOT EXISTS anastasis_truth (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32), key_share_data BYTEA CHECK(LENGTH(key_share_data)=80) NOT NULL, method_name VARCHAR NOT NULL, encrypted_truth BYTEA NOT NULL, truth_mime VARCHAR NOT NULL, expiration INT8 NOT NULL); COMMENT ON TABLE anastasis_truth IS 'Truth data is needed to authenticate clients during recovery'; COMMENT ON COLUMN anastasis_truth.truth_uuid IS 'The truth UUID uniquely identifies this truth record. Not a foreign key as we may offer storing truth for free.'; COMMENT ON COLUMN anastasis_truth.key_share_data IS 'Stores the encrypted key share used to recover the key (nonce, tag and keyshare)'; COMMENT ON COLUMN anastasis_truth.method_name IS 'Defines the authentication method (SMS, E-Mail, Question..)'; COMMENT ON COLUMN anastasis_truth.encrypted_truth IS 'Stores the encrypted authentication data'; COMMENT ON COLUMN anastasis_truth.truth_mime IS 'Defines the mime type of the stored authentcation data'; COMMENT ON COLUMN anastasis_truth.expiration IS 'At which date will the truth record expire'; CREATE TABLE IF NOT EXISTS anastasis_user (user_id BYTEA PRIMARY KEY CHECK(LENGTH(user_id)=32), expiration_date INT8 NOT NULL); COMMENT ON TABLE anastasis_user IS 'Saves a user which is using Anastasis'; COMMENT ON COLUMN anastasis_user.user_id IS 'Identifier of the user account'; COMMENT ON COLUMN anastasis_user.expiration_date IS 'At which date will the user record expire'; CREATE TABLE IF NOT EXISTS anastasis_recdoc_payment (payment_id BIGSERIAL PRIMARY KEY, user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id), post_counter INT4 NOT NULL DEFAULT 0 CHECK(post_counter >= 0), amount_val INT8 NOT NULL, amount_frac INT4 NOT NULL, payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32), creation_date INT8 NOT NULL, paid BOOLEAN NOT NULL DEFAULT FALSE); COMMENT ON TABLE anastasis_recdoc_payment IS 'Records a payment for a recovery document'; COMMENT ON COLUMN anastasis_recdoc_payment.payment_id IS 'Serial number which identifies the payment'; COMMENT ON COLUMN anastasis_recdoc_payment.user_id IS 'Link to the corresponding user who paid'; COMMENT ON COLUMN anastasis_recdoc_payment.post_counter IS 'For how many posts does the user pay'; COMMENT ON COLUMN anastasis_recdoc_payment.amount_val IS 'Amount we were paid'; COMMENT ON COLUMN anastasis_recdoc_payment.amount_frac IS 'Amount we were paid fraction'; COMMENT ON COLUMN anastasis_recdoc_payment.payment_identifier IS 'Payment identifier which the user has to provide'; COMMENT ON COLUMN anastasis_recdoc_payment.creation_date IS 'Creation date of the payment'; COMMENT ON COLUMN anastasis_recdoc_payment.paid IS 'Is the payment finished'; CREATE TABLE IF NOT EXISTS anastasis_challenge_payment (payment_id BIGSERIAL PRIMARY KEY, truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL, amount_val INT8 NOT NULL, amount_frac INT4 NOT NULL, payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32), creation_date INT8 NOT NULL, counter INT4 NOT NULL DEFAULT 3, paid BOOLEAN NOT NULL DEFAULT FALSE, refunded BOOLEAN NOT NULL DEFAULT FALSE ); COMMENT ON TABLE anastasis_recdoc_payment IS 'Records a payment for a challenge'; COMMENT ON COLUMN anastasis_challenge_payment.payment_id IS 'Serial number which identifies the payment'; COMMENT ON COLUMN anastasis_challenge_payment.truth_uuid IS 'Link to the corresponding challenge which is paid'; COMMENT ON COLUMN anastasis_challenge_payment.amount_val IS 'Amount we were paid'; COMMENT ON COLUMN anastasis_challenge_payment.amount_frac IS 'Amount we were paid fraction'; COMMENT ON COLUMN anastasis_challenge_payment.payment_identifier IS 'Payment identifier which the user has to provide'; COMMENT ON COLUMN anastasis_challenge_payment.counter IS 'How many more times will we issue the challenge for the given payment'; COMMENT ON COLUMN anastasis_challenge_payment.creation_date IS 'Creation date of the payment'; COMMENT ON COLUMN anastasis_challenge_payment.paid IS 'Is the payment finished'; COMMENT ON COLUMN anastasis_challenge_payment.refunded IS 'Was the payment refunded'; CREATE TABLE IF NOT EXISTS anastasis_recoverydocument (user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id), version INT4 NOT NULL, account_sig BYTEA NOT NULL CHECK(LENGTH(account_sig)=64), recovery_data_hash BYTEA NOT NULL CHECK(length(recovery_data_hash)=64), recovery_data BYTEA NOT NULL, PRIMARY KEY (user_id, version)); COMMENT ON TABLE anastasis_recoverydocument IS 'Stores a recovery document which contains the policy and the encrypted core secret'; COMMENT ON COLUMN anastasis_recoverydocument.user_id IS 'Link to the owner of this recovery document'; COMMENT ON COLUMN anastasis_recoverydocument.version IS 'The version of this recovery document'; COMMENT ON COLUMN anastasis_recoverydocument.account_sig IS 'Signature of the recovery document'; COMMENT ON COLUMN anastasis_recoverydocument.recovery_data_hash IS 'Hash of the recovery document to prevent unnecessary uploads'; COMMENT ON COLUMN anastasis_recoverydocument.recovery_data IS 'Contains the encrypted policy and core secret'; CREATE TABLE IF NOT EXISTS anastasis_challengecode (truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL, code INT8 NOT NULL, creation_date INT8 NOT NULL, expiration_date INT8 NOT NULL, retransmission_date INT8 NOT NULL DEFAULT 0, retry_counter INT4 NOT NULL); COMMENT ON TABLE anastasis_challengecode IS 'Stores a code which is checked for the authentication by SMS, E-Mail..'; COMMENT ON COLUMN anastasis_challengecode.truth_uuid IS 'Link to the corresponding challenge which is solved'; COMMENT ON COLUMN anastasis_challengecode.code IS 'The pin code which is sent to the user and verified'; COMMENT ON COLUMN anastasis_challengecode.creation_date IS 'Creation date of the code'; COMMENT ON COLUMN anastasis_challengecode.retransmission_date IS 'When did we last transmit the challenge to the user'; COMMENT ON COLUMN anastasis_challengecode.expiration_date IS 'When will the code expire'; COMMENT ON COLUMN anastasis_challengecode.retry_counter IS 'How many tries are left for this code must be > 0'; CREATE INDEX IF NOT EXISTS anastasis_challengecode_uuid_index ON anastasis_challengecode (truth_uuid,expiration_date); COMMENT ON INDEX anastasis_challengecode_uuid_index IS 'for challenge lookup'; CREATE INDEX IF NOT EXISTS anastasis_challengecode_expiration_index ON anastasis_challengecode (truth_uuid,expiration_date); COMMENT ON INDEX anastasis_challengecode_expiration_index IS 'for challenge garbage collection'; -- Complete transaction COMMIT;