do_gc.sql (4129B)
1 -- 2 -- This file is part of Anastasis 3 -- Copyright (C) 2026 Anastasis SARL 4 -- 5 -- ANASTASIS is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- ANASTASIS is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- ANASTASIS; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 DROP FUNCTION IF EXISTS anastasis_do_gc; 18 CREATE FUNCTION anastasis_do_gc ( 19 IN in_expire_backups INT8, -- accounts and truths expired before this are removed 20 IN in_expire_pending_payments INT8) -- unpaid payment records older than this are removed 21 RETURNS void 22 LANGUAGE plpgsql 23 AS $$ 24 BEGIN 25 26 -- The children of anastasis_user reference it with NO ACTION, so they have to 27 -- go first; deleting the account rows on their own raises 28 -- foreign_key_violation for every user that ever uploaded a document, which 29 -- used to abort the whole collection and reclaim nothing. 30 DELETE FROM anastasis_recoverydocument 31 WHERE user_id IN 32 (SELECT user_id 33 FROM anastasis_user 34 WHERE expiration_date < in_expire_backups); 35 36 DELETE FROM anastasis_recdoc_payment 37 WHERE user_id IN 38 (SELECT user_id 39 FROM anastasis_user 40 WHERE expiration_date < in_expire_backups); 41 42 DELETE FROM anastasis_user 43 WHERE expiration_date < in_expire_backups; 44 45 -- Expired truths hold encrypted key shares, so they must not be kept past the 46 -- date they were paid for. anastasis_truth has no foreign key to the account. 47 DELETE FROM anastasis_challengecode 48 WHERE expiration_date < in_expire_backups; 49 50 DELETE FROM anastasis_truth 51 WHERE expiration < in_expire_backups; 52 53 DELETE FROM anastasis_truth_payment 54 WHERE expiration < in_expire_backups; 55 56 -- Payment records that never resulted in a payment (or were refunded) are 57 -- only useful while the client may still come back with the receipt. 58 DELETE FROM anastasis_recdoc_payment 59 WHERE paid=FALSE 60 AND creation_date < in_expire_pending_payments; 61 62 -- anastasis_challenge_payment has no expiration column, so the 63 -- pending-payment horizon doubles as its retention policy. 64 DELETE FROM anastasis_challenge_payment 65 WHERE (paid=FALSE OR refunded) 66 AND creation_date < in_expire_pending_payments; 67 68 -- A paid challenge payment is not covered by the horizon above, and 69 -- truth_uuid is not a foreign key, so nothing would ever remove it. Once the 70 -- truth it paid for is gone -- deleted just above, or never created -- the 71 -- record cannot be acted on again, so it is collected here regardless of age. 72 -- This runs after the truth deletes on purpose, so that a truth expiring in 73 -- this same pass takes its payments with it. 74 DELETE FROM anastasis_challenge_payment cp 75 WHERE NOT EXISTS 76 (SELECT 1 77 FROM anastasis_truth t 78 WHERE t.truth_uuid = cp.truth_uuid); 79 80 -- Bank statements collected by the IBAN authorization helper carry customer 81 -- account details, so they should not be kept indefinitely either. The 82 -- newest row per credit account is retained whatever its age: the helper 83 -- resumes from the highest wire_reference it finds here, and deleting that 84 -- would make it re-download the bank history from the beginning. 85 DELETE FROM anastasis_auth_iban_in ai 86 WHERE ai.execution_date < in_expire_backups 87 AND ai.wire_reference <> 88 (SELECT MAX(x.wire_reference) 89 FROM anastasis_auth_iban_in x 90 WHERE x.credit_account_details = ai.credit_account_details); 91 92 END $$; 93 94 COMMENT ON FUNCTION anastasis_do_gc 95 IS 'Removes expired accounts with their recovery documents and payments, expired truths with their challenge codes and payments, challenge payments whose truth is gone, payment records that were never paid or were refunded, and stale IBAN authentication transfers. Runs as a single statement, so it either collects everything or nothing.';