1 files changed, 194 insertions, 0 deletions
diff --git a/src/stasis/stasis-0001.sql b/src/stasis/stasis-0001.sql
new file mode 100644
index 0000000..beb886d
--- /dev/null
+++ b/src/stasis/stasis-0001.sql
@@ -0,0 +1,194 @@
+--
+-- 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 <http://www.gnu.org/licenses/>
+--
+
+-- 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;