Objective
Implement document compliance tracking with retention policies, audit trails, and regulatory requirements. This enables meeting legal requirements, managing document lifecycles, and maintaining audit readiness.
Step 1: Create Compliance Policies Table
Define compliance policies.
CREATE TABLE compliance_policies (
id INTEGER PRIMARY KEY,
policy_name VARCHAR(255) NOT NULL,
regulation VARCHAR(100),
document_types VARCHAR(500),
retention_years INTEGER NOT NULL,
review_frequency_months INTEGER,
requires_encryption BOOLEAN DEFAULT FALSE,
requires_approval BOOLEAN DEFAULT TRUE,
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
effective_date DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create Compliance Documents Table
Track compliant documents.
CREATE TABLE compliance_documents (
id INTEGER PRIMARY KEY,
document_pdf PDF,
document_number VARCHAR(50) NOT NULL,
title VARCHAR(255) NOT NULL,
document_type VARCHAR(50) NOT NULL,
policy_id INTEGER NOT NULL,
department VARCHAR(100),
classification VARCHAR(20) DEFAULT 'internal',
retention_date DATE,
destruction_date DATE,
compliance_status VARCHAR(20) DEFAULT 'pending',
last_review_date DATE,
next_review_date DATE,
reviewed_by VARCHAR(100),
created_by VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (policy_id) REFERENCES compliance_policies(id)
);
Step 3: Create Audit Trail Table
Track document actions.
CREATE TABLE document_audit_trail (
id INTEGER PRIMARY KEY,
document_id INTEGER NOT NULL,
action_type VARCHAR(50) NOT NULL,
action_description TEXT,
performed_by VARCHAR(100) NOT NULL,
performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(50),
previous_value TEXT,
new_value TEXT,
FOREIGN KEY (document_id) REFERENCES compliance_documents(id)
);
Step 4: Create Compliance Checks Table
Track compliance verifications.
CREATE TABLE compliance_checks (
id INTEGER PRIMARY KEY,
document_id INTEGER NOT NULL,
check_type VARCHAR(50) NOT NULL,
check_result VARCHAR(20) NOT NULL,
findings TEXT,
checked_by VARCHAR(100) NOT NULL,
checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
next_check_date DATE,
FOREIGN KEY (document_id) REFERENCES compliance_documents(id)
);
Step 5: Insert Compliance Policies
Add regulatory policies.
INSERT INTO compliance_policies (id, policy_name, regulation, document_types, retention_years, review_frequency_months, requires_encryption, requires_approval, effective_date) VALUES
(1, 'Financial Records Retention', 'SOX', 'financial,audit,tax', 7, 12, TRUE, TRUE, '2020-01-01'),
(2, 'Healthcare Records', 'HIPAA', 'medical,patient,clinical', 6, 6, TRUE, TRUE, '2019-01-01'),
(3, 'Employee Records', 'Labor Law', 'hr,employment,payroll', 7, 12, TRUE, TRUE, '2018-01-01'),
(4, 'Contract Documents', 'Corporate', 'contract,agreement,legal', 10, 12, FALSE, TRUE, '2020-01-01'),
(5, 'Data Privacy Records', 'GDPR', 'privacy,consent,data', 5, 6, TRUE, TRUE, '2018-05-25'),
(6, 'Tax Documents', 'IRS', 'tax,1099,w2', 7, 12, TRUE, TRUE, '2015-01-01');
Step 6: Insert Compliance Documents
Add documents under compliance.
INSERT INTO compliance_documents (id, document_number, title, document_type, policy_id, department, classification, retention_date, compliance_status, last_review_date, next_review_date, reviewed_by, created_by) VALUES
(1, 'FIN-2024-001', 'Q4 2023 Financial Audit', 'audit', 1, 'Finance', 'confidential', '2031-01-15', 'compliant', '2024-01-15', '2025-01-15', 'cfo', 'finance_admin'),
(2, 'FIN-2024-002', 'Tax Returns 2023', 'tax', 6, 'Finance', 'confidential', '2031-04-15', 'compliant', '2024-04-15', '2025-04-15', 'cfo', 'tax_accountant'),
(3, 'HR-2024-001', 'Employee Records - Active', 'employment', 3, 'HR', 'restricted', '2031-01-01', 'compliant', '2024-01-01', '2025-01-01', 'hr_director', 'hr_admin'),
(4, 'LEGAL-2024-001', 'Vendor Master Agreement', 'contract', 4, 'Legal', 'confidential', '2034-02-01', 'compliant', '2024-02-01', '2025-02-01', 'general_counsel', 'legal_admin'),
(5, 'MED-2024-001', 'Patient Records Archive 2023', 'medical', 2, 'Healthcare', 'restricted', '2030-01-01', 'review_required', '2023-06-15', '2024-01-15', 'compliance_officer', 'medical_admin'),
(6, 'PRIV-2024-001', 'GDPR Consent Records', 'consent', 5, 'Legal', 'confidential', '2029-05-25', 'compliant', '2024-01-20', '2024-07-20', 'dpo', 'privacy_admin'),
(7, 'FIN-2023-050', 'Expense Reports 2022', 'financial', 1, 'Finance', 'internal', '2030-12-31', 'pending_destruction', '2023-12-31', NULL, 'finance_mgr', 'accounting');