0011-aml_staff.sql (2535B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER 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 -- TALER 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 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- Make aml_staff append-only. 18 -- 19 -- The table used to hold exactly one row per officer, keyed by decider_pub 20 -- and UPSERTed in place on every status change. Two things followed from 21 -- that, both bad. The officer's history was unrecoverable, so nobody -- 22 -- least of all an auditor -- could answer "was this officer allowed to 23 -- decide at the time of that decision?". And because an update left 24 -- aml_staff_uuid where it was, a status change was invisible to every 25 -- cursor that walks a table by its serial ID: taler-auditor-sync appends 26 -- only, so it replicated an officer's first appearance and then silently 27 -- never carried a single change of it to the auditor's replica. 28 -- 29 -- From here on the table records one row per status change and the current 30 -- status of an officer is the row with the greatest last_change. 31 32 ALTER TABLE aml_staff 33 DROP CONSTRAINT aml_staff_pkey; 34 35 -- Redundant once aml_staff_uuid becomes the primary key below. 36 ALTER TABLE aml_staff 37 DROP CONSTRAINT aml_staff_aml_staff_uuid_key; 38 39 ALTER TABLE aml_staff 40 ADD PRIMARY KEY (aml_staff_uuid); 41 42 -- Serves the "most recent status of this officer" lookups (by backward 43 -- scan), and keeps the exchange from recording the same status twice. 44 CREATE UNIQUE INDEX aml_staff_by_decider_pub_last_change 45 ON aml_staff 46 (decider_pub, last_change); 47 48 COMMENT ON TABLE aml_staff 49 IS 'Status changes of the AML staff members the exchange uses or has used in the past, one row per change. Append-only: the current status of a member is the row with the greatest last_change. Entries never expire as we need to remember the history indefinitely.'; 50 COMMENT ON COLUMN aml_staff.last_change 51 IS 'Time from which this status was in force, until superseded by a row with a greater last_change for the same decider_pub. Used to detect replays of old messages.';