-- 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;