PostgreSQL Schema for Records, Metadata, RBAC/ABAC
Complete schema for documents, versions, metadata, access control, and retention management.
Core Document Tables
-- Core documents (logical record)
CREATE TABLE documents (
doc_id UUID PRIMARY KEY,
path TEXT NOT NULL, -- repo path to .md
current_version INT NOT NULL,
content_hash TEXT NOT NULL, -- hash of current content
worm_object_id TEXT NOT NULL, -- immutable storage pointer
created_at TIMESTAMPTZ NOT NULL,
created_by TEXT NOT NULL,
last_modified_at TIMESTAMPTZ NOT NULL,
last_modified_by TEXT NOT NULL
);
-- Immutable versions (link to WORM objects)
CREATE TABLE document_versions (
doc_id UUID NOT NULL REFERENCES documents(doc_id),
version INT NOT NULL,
worm_object_id TEXT NOT NULL,
content_hash TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
created_by TEXT NOT NULL,
supersedes_version INT,
PRIMARY KEY (doc_id, version)
);
-- Index for version lookups
CREATE INDEX idx_doc_versions_doc_id ON document_versions(doc_id);
Compliance Metadata Table
-- Flattened metadata (HIPAA + FINRA)
CREATE TABLE document_metadata (
doc_id UUID PRIMARY KEY REFERENCES documents(doc_id),
title TEXT NOT NULL,
summary TEXT,
domain TEXT NOT NULL, -- clinical, security-privacy, finance, etc.
document_type TEXT NOT NULL, -- policy, sop, record, communication, etc.
jurisdiction TEXT[] NOT NULL, -- ["US"], ["US","EU"], etc.
regulations TEXT[] NOT NULL, -- ["HIPAA-164.316","FINRA-4511",...]
security_class TEXT NOT NULL, -- public/internal/confidential/restricted
contains_phi BOOLEAN NOT NULL DEFAULT FALSE,
contains_pii BOOLEAN NOT NULL DEFAULT FALSE,
contains_financial BOOLEAN NOT NULL DEFAULT FALSE,
status TEXT NOT NULL, -- draft/in_review/effective/obsolete
effective_date DATE,
review_due_date DATE,
expiry_date DATE,
retention_category TEXT NOT NULL, -- HIPAA-6Y, FINRA-6Y, etc.
retention_period_y INT NOT NULL, -- in years
retain_until DATE NOT NULL,
legal_hold BOOLEAN NOT NULL DEFAULT FALSE,
legal_hold_reason TEXT,
business_unit TEXT,
desk TEXT, -- trading desk, etc.
facility TEXT, -- hospital/facility
owner_user_id TEXT NOT NULL,
owner_role TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
last_modified_at TIMESTAMPTZ NOT NULL
);
-- Indexes for common queries
CREATE INDEX idx_doc_meta_domain ON document_metadata(domain);
CREATE INDEX idx_doc_meta_status ON document_metadata(status);
CREATE INDEX idx_doc_meta_retention ON document_metadata(retention_category);
CREATE INDEX idx_doc_meta_retain_until ON document_metadata(retain_until);
CREATE INDEX idx_doc_meta_legal_hold ON document_metadata(legal_hold) WHERE legal_hold = TRUE;
CREATE INDEX idx_doc_meta_contains_phi ON document_metadata(contains_phi) WHERE contains_phi = TRUE;
Tags Table
CREATE TABLE document_tags (
doc_id UUID NOT NULL REFERENCES documents(doc_id),
tag TEXT NOT NULL,
PRIMARY KEY (doc_id, tag)
);
CREATE INDEX idx_doc_tags_tag ON document_tags(tag);
RBAC Tables
-- Users
CREATE TABLE users (
user_id TEXT PRIMARY KEY,
display_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
business_unit TEXT,
desk TEXT,
facility TEXT,
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_login_at TIMESTAMPTZ
);
-- Roles
CREATE TABLE roles (
role_id TEXT PRIMARY KEY, -- "clinician", "privacy_officer", "trader"
description TEXT NOT NULL
);
-- User-Role assignments
CREATE TABLE user_roles (
user_id TEXT NOT NULL REFERENCES users(user_id),
role_id TEXT NOT NULL REFERENCES roles(role_id),
granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
granted_by TEXT NOT NULL,
PRIMARY KEY (user_id, role_id)
);
-- Permissions
CREATE TABLE permissions (
permission_id TEXT PRIMARY KEY, -- "doc.read", "doc.write", "doc.view_phi"
description TEXT NOT NULL
);
-- Role-Permission grants
CREATE TABLE role_permissions (
role_id TEXT NOT NULL REFERENCES roles(role_id),
permission_id TEXT NOT NULL REFERENCES permissions(permission_id),
PRIMARY KEY (role_id, permission_id)
);
-- Indexes
CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);
CREATE INDEX idx_role_perms_role ON role_permissions(role_id);
ABAC Policy Hints Table
-- ABAC attributes stored per doc (consumed by PDP)
CREATE TABLE document_access_attributes (
doc_id UUID PRIMARY KEY REFERENCES documents(doc_id),
required_roles TEXT[] DEFAULT '{}', -- ["privacy_officer"]
allowed_business_units TEXT[] DEFAULT '{}', -- ["Compliance","Oncology"]
allowed_desks TEXT[] DEFAULT '{}', -- for finance
allowed_facilities TEXT[] DEFAULT '{}', -- site-level scoping
max_class_for_role TEXT -- ceiling on classification
);
Retention Policy Table
CREATE TABLE retention_policies (
retention_category TEXT PRIMARY KEY,
description TEXT NOT NULL,
period_years_default INT NOT NULL,
legal_basis TEXT NOT NULL, -- "HIPAA-164.316", "FINRA-4511/SEC-17a-4"
min_years INT NOT NULL -- safeguard against too-short periods
);
-- Seed with common categories
INSERT INTO retention_policies VALUES
('HIPAA-6Y', 'HIPAA policies and procedures', 6, 'HIPAA 45 CFR 164.316(b)(2)(i)', 6),
('FINRA-6Y', 'FINRA supervisory procedures', 6, 'FINRA 4511', 6),
('SEC-7Y', 'SEC books and records', 7, 'SEC Rule 17a-4', 7),
('HR-7Y', 'HR and employment records', 7, 'State employment law', 7);
Destruction Queue
CREATE TABLE destruction_queue (
id BIGSERIAL PRIMARY KEY,
doc_id UUID NOT NULL REFERENCES documents(doc_id),
queued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
processed_at TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'queued', -- queued|processing|completed|failed
reason TEXT NOT NULL, -- "retention_expired"
processed_by TEXT
);
CREATE INDEX idx_destruction_status ON destruction_queue(status);
CREATE INDEX idx_destruction_doc ON destruction_queue(doc_id);
Audit Events Table
CREATE TABLE audit_events (
event_id BIGSERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
user_id TEXT NOT NULL,
session_id TEXT,
action TEXT NOT NULL, -- create, read, update, delete, approve, etc.
doc_id UUID,
doc_version INT,
old_value JSONB,
new_value JSONB,
source_ip INET,
user_agent TEXT,
decision TEXT -- allow, deny
);
-- Make append-only (remove update/delete)
REVOKE DELETE, UPDATE ON audit_events FROM PUBLIC;
-- Indexes for audit queries
CREATE INDEX idx_audit_timestamp ON audit_events(timestamp);
CREATE INDEX idx_audit_user ON audit_events(user_id);
CREATE INDEX idx_audit_doc ON audit_events(doc_id);
CREATE INDEX idx_audit_action ON audit_events(action);