Files
qbit-secured-data/docs/ddl/secured-data-postgres.sql
james.maesandClaude Fable 5.2 1e2d6352d4
maven / build (push) Successful in 1m26s
build / maven (push) Successful in 1m26s
fix: an edit over HTTP is an edit of its row, not a new row
The table customizer finds the stored row of an update by its id, and
compared the two ids as they came: the id of a request over HTTP is text
(it is the id in the path), the stored id is a number. They were never
equal, so an edit made through the API or the UI had no stored row and was
validated and stamped as an insert. The uniqueness check of a
classification compared ids the same way. Both now compare the ids as text.

This is older than the standard dates, but they build on it: the customizer
gives a new row its create date, and a client that knows record versions
edits a table with a modify date by sending only the changed values and the
version it read.

What an edit over HTTP did before:
- A classification, policy or retention rule got a new create_date.
- Releasing a legal hold overwrote when and by whom it was placed (and,
  since the hold has one, its create date).
- Editing the reason of a legal hold without sending its status failed with
  a server error (the validation read the status of a row it did not have).
- Editing a classification while naming its table and field was refused as
  a second classification of that field.
- Editing a retention rule without sending every field was refused.

Tests (SecuredDataHttpEditTest, through the real middleware on the shipped
schema, a day after the rows were written): the reason of a hold is edited
with the changed value and the version, and the stale version is then
refused; a hold is released and keeps when and by whom it was placed and
its create date; a classification is edited naming its table and field; a
policy and a retention rule are renamed and keep their create dates; a row
with neither date is read and edited. All five fail without the fix. They
run in UTC: where the default zone of the JVM is not UTC, core refuses
every update that sends a version, which is core's to fix and is kept out
of these tests.

Notes for an upgrade (shipped DDL block and README): set a lock_timeout
before the ALTER statements and run the block again when one is cancelled;
on a large access log run its backfill UPDATE in ranges of id, or leave it
out, since rows without the two dates are read and written like any
others. Changelog: the fix, that an update naming the create date still
sets it, and that a wipe moves the record version of its retention rule.

Co-Authored-By: Claude Fable 5.2 <noreply@anthropic.com>
2026-11-20 10:40:48 -06:00

202 lines
12 KiB
SQL

-- PostgreSQL 15+ (NULLS NOT DISTINCT makes the table-wide classification unique).
-- Extra value-free fingerprint column supports enumeration detection across restarts/nodes.
CREATE TABLE secured_data_classification (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
table_name VARCHAR(255) NOT NULL,
field_name VARCHAR(255),
classification VARCHAR(255) NOT NULL,
mask_style VARCHAR(255),
low_cardinality BOOLEAN,
notes TEXT,
create_date TIMESTAMP WITH TIME ZONE,
modify_date TIMESTAMP WITH TIME ZONE,
modified_by VARCHAR(255),
CONSTRAINT secured_data_classification_target UNIQUE NULLS NOT DISTINCT (table_name, field_name)
);
CREATE TABLE secured_data_policy (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL,
permission_name VARCHAR(255) NOT NULL,
classification VARCHAR(255) NOT NULL,
table_name VARCHAR(255),
tier VARCHAR(255) NOT NULL,
reveal_minutes INTEGER DEFAULT 5,
daily_reveal_cap INTEGER,
reason_options TEXT,
allow_free_text_reason BOOLEAN DEFAULT true,
require_free_text BOOLEAN DEFAULT false,
client_channel VARCHAR(255),
exact_match_per_hour INTEGER DEFAULT 20,
miss_alert_consecutive INTEGER DEFAULT 5,
distinct_value_alert INTEGER DEFAULT 15,
min_entropy_enabled BOOLEAN DEFAULT true,
is_active BOOLEAN DEFAULT true,
create_date TIMESTAMP WITH TIME ZONE,
modify_date TIMESTAMP WITH TIME ZONE,
modified_by VARCHAR(255),
CHECK (reveal_minutes BETWEEN 1 AND 60),
CHECK (exact_match_per_hour >= 0 AND miss_alert_consecutive >= 0 AND distinct_value_alert >= 0)
);
CREATE TABLE secured_data_reveal_grant (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id VARCHAR(255) NOT NULL,
user_name VARCHAR(255),
table_name VARCHAR(255) NOT NULL,
record_id VARCHAR(255) NOT NULL,
field_name VARCHAR(255) NOT NULL,
reason VARCHAR(500),
reason_text TEXT,
granted_at TIMESTAMP WITH TIME ZONE NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
access_log_id INTEGER NOT NULL
);
CREATE TABLE secured_data_break_glass (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id VARCHAR(255) NOT NULL,
user_name VARCHAR(255),
reason VARCHAR(500),
reason_text TEXT,
classification_ceiling VARCHAR(255) DEFAULT 'PHI',
table_name VARCHAR(255),
started_at TIMESTAMP WITH TIME ZONE NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
ended_at TIMESTAMP WITH TIME ZONE,
ended_by VARCHAR(255),
status VARCHAR(255) DEFAULT 'ACTIVE'
);
CREATE TABLE secured_data_access_log (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
event_type VARCHAR(255) NOT NULL,
user_id VARCHAR(255),
user_name VARCHAR(255),
table_name VARCHAR(255) NOT NULL,
record_id TEXT,
field_name TEXT,
record_count INTEGER,
action_name VARCHAR(255),
reason VARCHAR(500),
reason_text TEXT,
client_channel VARCHAR(255),
outcome VARCHAR(255),
anomaly_flag BOOLEAN DEFAULT false,
break_glass_id INTEGER,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
search_value_fingerprints TEXT,
rule_id INTEGER
);
CREATE INDEX secured_data_policy_lookup ON secured_data_policy (is_active, permission_name, classification, table_name);
CREATE INDEX secured_data_break_glass_lookup ON secured_data_break_glass (user_id, status, expires_at);
CREATE INDEX secured_data_reveal_lookup ON secured_data_reveal_grant (user_id, table_name, field_name, record_id, expires_at);
CREATE INDEX secured_data_access_lookup ON secured_data_access_log (user_id, table_name, field_name, event_type, created_at);
-- Phase (e): bounded user counters and home time windows.
CREATE INDEX secured_data_anomaly_lookup ON secured_data_access_log (user_id, event_type, created_at);
CREATE INDEX secured_data_home_lookup ON secured_data_access_log (event_type, created_at);
-- Phase (f): all policy targets and audit tables must share this transactional backend.
CREATE TABLE secured_data_retention_rule (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL, table_name VARCHAR(255) NOT NULL, field_names TEXT NOT NULL,
age_field_name VARCHAR(255) NOT NULL, age_days INTEGER NOT NULL CHECK (age_days >= 1),
action VARCHAR(40) DEFAULT 'NULL_WITH_MARKER' NOT NULL CHECK (action IN ('NULL_WITH_MARKER','OVERWRITE_TEXT','KEYED_HASH','DELETE_RECORD','ARCHIVE_THEN_WIPE')),
overwrite_text TEXT DEFAULT '[wiped per policy]',
audit_treatment VARCHAR(30) DEFAULT 'SHREDDABLE' NOT NULL CHECK (audit_treatment IN ('SHREDDABLE','CHANGED_ONLY')),
archive_storage_name VARCHAR(255), batch_size INTEGER DEFAULT 500 NOT NULL CHECK (batch_size >= 1),
is_active BOOLEAN DEFAULT true, last_run_at TIMESTAMP WITH TIME ZONE, last_run_count INTEGER,
create_date TIMESTAMP WITH TIME ZONE, modify_date TIMESTAMP WITH TIME ZONE, modified_by VARCHAR(255)
);
CREATE TABLE secured_data_wipe_run (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, rule_id INTEGER NOT NULL REFERENCES secured_data_retention_rule(id),
started_at TIMESTAMP WITH TIME ZONE NOT NULL, finished_at TIMESTAMP WITH TIME ZONE, status VARCHAR(30),
eligible_count INTEGER, held_count INTEGER, wiped_count INTEGER, error_count INTEGER,
record_ids VARCHAR(4000), message VARCHAR(500)
);
CREATE TABLE secured_data_wipe_marker (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(255) NOT NULL, record_id VARCHAR(255) NOT NULL,
field_name VARCHAR(255) NOT NULL, rule_id INTEGER NOT NULL REFERENCES secured_data_retention_rule(id), action VARCHAR(40) NOT NULL,
wiped_at TIMESTAMP WITH TIME ZONE NOT NULL, run_id INTEGER NOT NULL REFERENCES secured_data_wipe_run(id),
UNIQUE (table_name, record_id, field_name)
);
CREATE TABLE secured_data_legal_hold (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(255) NOT NULL, record_id VARCHAR(255), reason TEXT NOT NULL,
placed_by VARCHAR(255), placed_at TIMESTAMP WITH TIME ZONE, released_by VARCHAR(255), released_at TIMESTAMP WITH TIME ZONE,
status VARCHAR(20) DEFAULT 'ACTIVE' NOT NULL CHECK (status IN ('ACTIVE','RELEASED'))
);
-- Mutable key material: never register this table in ESB, WORM or long-term logical backups.
CREATE TABLE secured_data_audit_key (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(255) NOT NULL,
record_id VARCHAR(255) NOT NULL, field_name VARCHAR(255) NOT NULL, wrapped_key TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE (table_name, record_id, field_name)
);
CREATE INDEX secured_data_hold_lookup ON secured_data_legal_hold (table_name, status, record_id);
CREATE INDEX secured_data_retention_lookup ON secured_data_retention_rule (table_name, is_active);
CREATE INDEX secured_data_wipe_rule_lookup ON secured_data_wipe_marker (rule_id);
-- Standard dates: create_date and modify_date on the seven tables created above without them.
-- A new database runs the whole file. An existing database runs this block alone, BEFORE the release
-- that writes the two columns is deployed. The columns are nullable and default to the current time, so
-- a release that does not know them goes on inserting while both run. Adding a nullable column and
-- setting a default change the catalog only (no table rewrite). Each UPDATE gives the rows that were
-- already there the row's own time, once: it touches only rows whose create_date is still null and
-- takes row locks only. Every statement can be run again and then changes nothing.
--
-- Locks. Each ALTER takes a brief exclusive lock on its table. It waits behind any open transaction
-- that has touched the table (one that has inserted a log row, for one), and every other reader and
-- writer of the table then waits behind the ALTER. On a live database set a lock timeout for the
-- session first (SET lock_timeout = '5s') and run the block again when a statement is cancelled.
--
-- A large access log. The UPDATE of secured_data_access_log writes every existing row in one
-- transaction. One measurement on PostgreSQL 16 with 2,000,000 rows: 24 s, and the table grew from
-- 146 MB to 324 MB. On a large log run it in ranges of id instead, one range per transaction (add
-- AND id BETWEEN 1 AND 100000 to its WHERE, then the next range, up to the highest id), or leave it
-- out: a row without the two dates is read and written like any other, and the columns are filled
-- for every row inserted from then on.
ALTER TABLE secured_data_reveal_grant ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_reveal_grant ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_reveal_grant ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_reveal_grant ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_reveal_grant SET create_date = granted_at, modify_date = granted_at WHERE create_date IS NULL;
ALTER TABLE secured_data_break_glass ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_break_glass ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_break_glass ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_break_glass ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_break_glass SET create_date = started_at, modify_date = COALESCE(ended_at, started_at) WHERE create_date IS NULL;
ALTER TABLE secured_data_access_log ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_access_log ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_access_log ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_access_log ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_access_log SET create_date = created_at, modify_date = created_at WHERE create_date IS NULL;
ALTER TABLE secured_data_wipe_run ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_wipe_run ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_wipe_run ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_wipe_run ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_wipe_run SET create_date = COALESCE(finished_at, started_at), modify_date = COALESCE(finished_at, started_at) WHERE create_date IS NULL;
ALTER TABLE secured_data_wipe_marker ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_wipe_marker ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_wipe_marker ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_wipe_marker ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_wipe_marker SET create_date = wiped_at, modify_date = wiped_at WHERE create_date IS NULL;
ALTER TABLE secured_data_legal_hold ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_legal_hold ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_legal_hold ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_legal_hold ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_legal_hold SET create_date = COALESCE(placed_at, CURRENT_TIMESTAMP), modify_date = COALESCE(released_at, placed_at, CURRENT_TIMESTAMP) WHERE create_date IS NULL;
ALTER TABLE secured_data_audit_key ADD COLUMN IF NOT EXISTS create_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_audit_key ADD COLUMN IF NOT EXISTS modify_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE secured_data_audit_key ALTER COLUMN create_date SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE secured_data_audit_key ALTER COLUMN modify_date SET DEFAULT CURRENT_TIMESTAMP;
UPDATE secured_data_audit_key SET create_date = created_at, modify_date = created_at WHERE create_date IS NULL;