Document Compliance Tracking

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

Step 7: Insert Audit Trail

Add audit history.

INSERT INTO document_audit_trail (id, document_id, action_type, action_description, performed_by, performed_at, ip_address) VALUES
    (1, 1, 'create', 'Document uploaded to compliance system', 'finance_admin', '2024-01-15 09:00:00', '192.168.1.100'),
    (2, 1, 'review', 'Annual compliance review completed', 'cfo', '2024-01-15 14:30:00', '192.168.1.101'),
    (3, 1, 'approve', 'Document approved as compliant', 'compliance_officer', '2024-01-15 16:00:00', '192.168.1.102'),
    (4, 2, 'create', 'Tax documents uploaded', 'tax_accountant', '2024-04-15 10:00:00', '192.168.1.103'),
    (5, 2, 'review', 'Tax compliance review', 'cfo', '2024-04-15 15:00:00', '192.168.1.101'),
    (6, 5, 'flag', 'Document flagged for review - approaching retention deadline', 'system', '2024-01-10 00:00:00', 'system'),
    (7, 7, 'status_change', 'Status changed from compliant to pending_destruction', 'system', '2024-01-01 00:00:00', 'system');

Step 8: Insert Compliance Checks

Add compliance verifications.

INSERT INTO compliance_checks (id, document_id, check_type, check_result, findings, checked_by, checked_at, next_check_date) VALUES
    (1, 1, 'annual_review', 'pass', 'Document meets all SOX requirements', 'compliance_officer', '2024-01-15 16:00:00', '2025-01-15'),
    (2, 2, 'annual_review', 'pass', 'Tax documents properly stored and encrypted', 'compliance_officer', '2024-04-15 16:00:00', '2025-04-15'),
    (3, 3, 'annual_review', 'pass', 'HR records comply with retention policy', 'hr_compliance', '2024-01-05 11:00:00', '2025-01-05'),
    (4, 5, 'semi_annual_review', 'warning', 'Review date approaching - requires attention', 'compliance_officer', '2024-01-10 09:00:00', '2024-07-10'),
    (5, 6, 'semi_annual_review', 'pass', 'GDPR consent records properly maintained', 'dpo', '2024-01-20 14:00:00', '2024-07-20');

Step 9: Compliance Status Overview

Get compliance status for all documents.

SELECT
    cd.document_number,
    cd.title,
    cp.policy_name,
    cp.regulation,
    cd.compliance_status,
    cd.retention_date,
    cd.next_review_date
FROM compliance_documents cd
INNER JOIN compliance_policies cp ON cd.policy_id = cp.id
ORDER BY
    CASE cd.compliance_status
        WHEN 'review_required' THEN 1
        WHEN 'pending_destruction' THEN 2
        WHEN 'pending' THEN 3
        ELSE 4
    END,
    cd.next_review_date;

Step 10: Documents Due for Review

Get documents needing review.

SELECT
    cd.document_number,
    cd.title,
    cp.policy_name,
    cd.last_review_date,
    cd.next_review_date,
    cd.reviewed_by,
    CASE
        WHEN cd.next_review_date < CURRENT_DATE THEN 'Overdue'
        WHEN cd.next_review_date <= CURRENT_DATE + 30 THEN 'Due Soon'
        ELSE 'On Track'
    END as review_status
FROM compliance_documents cd
INNER JOIN compliance_policies cp ON cd.policy_id = cp.id
WHERE cd.next_review_date IS NOT NULL
  AND cd.compliance_status NOT IN ('pending_destruction', 'destroyed')
ORDER BY cd.next_review_date;

Step 11: Audit Trail Report

Get audit history for a document.

SELECT
    cd.document_number,
    dat.action_type,
    dat.action_description,
    dat.performed_by,
    dat.performed_at,
    dat.ip_address
FROM compliance_documents cd
INNER JOIN document_audit_trail dat ON cd.id = dat.document_id
WHERE cd.id = 1
ORDER BY dat.performed_at DESC;

Step 12: Retention Compliance Report

Analyze retention status by policy.

SELECT
    cp.policy_name,
    cp.regulation,
    cp.retention_years,
    COUNT(cd.id) as document_count,
    COUNT(CASE WHEN cd.compliance_status = 'compliant' THEN 1 END) as compliant,
    COUNT(CASE WHEN cd.compliance_status = 'review_required' THEN 1 END) as needs_review,
    COUNT(CASE WHEN cd.compliance_status = 'pending_destruction' THEN 1 END) as pending_destruction
FROM compliance_policies cp
LEFT JOIN compliance_documents cd ON cp.id = cd.policy_id
GROUP BY cp.id, cp.policy_name, cp.regulation, cp.retention_years
ORDER BY document_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS compliance_checks;
DROP TABLE IF EXISTS document_audit_trail;
DROP TABLE IF EXISTS compliance_documents;
DROP TABLE IF EXISTS compliance_policies;

Expected Outcomes

• Policies defined by regulation
• Retention dates tracked
• Audit trail complete
• Review schedules monitored
• Compliance status visible

Compliance Statuses

Key Concepts Learned

• Regulatory policy management
• Retention scheduling
• Audit trail tracking
• Compliance verification
• Review management