qbit-secured-data/docs/ddl/secured-data-postgres.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);