Media Compliance and Audit

Objective

Create a media compliance and audit system that tracks access, enforces retention policies, maintains audit trails, and supports regulatory compliance requirements.

Step 1: Create Media Assets Table

Store media with compliance metadata.

CREATE TABLE compliant_media (
    id INTEGER PRIMARY KEY,
    asset_id VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    asset_type VARCHAR(50),
    classification VARCHAR(50),
    sensitivity_level VARCHAR(20),
    contains_pii BOOLEAN DEFAULT FALSE,
    contains_phi BOOLEAN DEFAULT FALSE,
    data_subjects TEXT,
    legal_hold BOOLEAN DEFAULT FALSE,
    retention_policy_id INTEGER,
    retention_until DATE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_by VARCHAR(100),
    modified_at TIMESTAMP,
    status VARCHAR(50) DEFAULT 'active'
);

Step 2: Create Retention Policies Table

Define retention rules.

CREATE TABLE retention_policies (
    id INTEGER PRIMARY KEY,
    policy_code VARCHAR(50) NOT NULL UNIQUE,
    policy_name VARCHAR(200),
    description TEXT,
    asset_types TEXT,
    classification VARCHAR(50),
    retention_days INTEGER,
    action_on_expiry VARCHAR(50),
    requires_approval BOOLEAN DEFAULT TRUE,
    regulatory_reference TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Access Audit Log Table

Track all access events.

CREATE TABLE media_access_audit (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    user_name VARCHAR(200),
    user_department VARCHAR(100),
    user_role VARCHAR(100),
    access_type VARCHAR(50),
    access_reason TEXT,
    access_granted BOOLEAN,
    denial_reason VARCHAR(200),
    ip_address VARCHAR(50),
    user_agent TEXT,
    session_id VARCHAR(100),
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES compliant_media(id)
);

Step 4: Create Change Audit Log Table

Track modifications.

CREATE TABLE media_change_audit (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    change_type VARCHAR(50),
    field_changed VARCHAR(100),
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(100),
    change_reason TEXT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES compliant_media(id)
);

Step 5: Create Legal Holds Table

Manage litigation holds.

CREATE TABLE legal_holds (
    id INTEGER PRIMARY KEY,
    hold_code VARCHAR(50) NOT NULL UNIQUE,
    hold_name VARCHAR(200),
    description TEXT,
    matter_reference VARCHAR(100),
    custodian VARCHAR(200),
    hold_reason TEXT,
    start_date DATE,
    end_date DATE,
    status VARCHAR(50) DEFAULT 'active',
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    released_by VARCHAR(100),
    released_at TIMESTAMP
);

Step 6: Create Hold Assets Table

Link assets to holds.

CREATE TABLE hold_assets (
    id INTEGER PRIMARY KEY,
    hold_id INTEGER NOT NULL,
    asset_id INTEGER NOT NULL,
    added_by VARCHAR(100),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    released_at TIMESTAMP,
    FOREIGN KEY (hold_id) REFERENCES legal_holds(id),
    FOREIGN KEY (asset_id) REFERENCES compliant_media(id)
);

Step 7: Create Compliance Certifications Table

Track compliance attestations.

CREATE TABLE compliance_certifications (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    certification_type VARCHAR(50),
    regulation VARCHAR(100),
    certified_by VARCHAR(100),
    certification_date DATE,
    expiry_date DATE,
    status VARCHAR(50) DEFAULT 'valid',
    notes TEXT,
    FOREIGN KEY (asset_id) REFERENCES compliant_media(id)
);

Step 8: Create Deletion Requests Table

Track deletion workflows.

CREATE TABLE deletion_requests (
    id INTEGER PRIMARY KEY,
    request_code VARCHAR(50) NOT NULL UNIQUE,
    asset_id INTEGER NOT NULL,
    request_type VARCHAR(50),
    request_reason TEXT,
    requester_id VARCHAR(50),
    requester_name VARCHAR(200),
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approval_status VARCHAR(50) DEFAULT 'pending',
    approved_by VARCHAR(100),
    approved_at TIMESTAMP,
    executed_at TIMESTAMP,
    blocked_reason TEXT,
    FOREIGN KEY (asset_id) REFERENCES compliant_media(id)
);

Step 9: Insert Sample Data

Add compliance records.

INSERT INTO retention_policies (id, policy_code, policy_name, description, asset_types, classification, retention_days, action_on_expiry, regulatory_reference, is_active) VALUES
    (1, 'RET-FIN-7Y', 'Financial Records 7 Year', 'Retain financial documents for 7 years per SOX', 'document', 'financial', 2555, 'archive', 'SOX Section 802', TRUE),
    (2, 'RET-HR-5Y', 'HR Records 5 Year', 'Employee records retention post-termination', 'document,image', 'hr', 1825, 'delete', 'EEOC Guidelines', TRUE),
    (3, 'RET-MKT-2Y', 'Marketing Content 2 Year', 'Marketing materials and campaigns', 'image,video,audio', 'marketing', 730, 'archive', 'Internal Policy', TRUE),
    (4, 'RET-LEGAL-PERM', 'Legal Documents Permanent', 'Contracts and legal documents', 'document', 'legal', 36500, 'archive', 'Record Retention Law', TRUE),
    (5, 'RET-GDPR-CONSENT', 'GDPR Consent Based', 'Retain while consent valid', 'all', 'personal', 365, 'delete', 'GDPR Article 17', TRUE);

INSERT INTO compliant_media (id, asset_id, title, asset_type, classification, sensitivity_level, contains_pii, contains_phi, retention_policy_id, retention_until, created_by, created_at, status) VALUES
    (1, 'DOC-FIN-001', 'Q4 2023 Financial Report', 'document', 'financial', 'confidential', FALSE, FALSE, 1, '2031-01-31', 'finance_team', '2024-01-15 10:00:00', 'active'),
    (2, 'DOC-HR-001', 'Employee Records - John Doe', 'document', 'hr', 'restricted', TRUE, FALSE, 2, '2029-03-15', 'hr_team', '2024-01-10 09:00:00', 'active'),
    (3, 'VID-MKT-001', 'Product Launch Video', 'video', 'marketing', 'internal', FALSE, FALSE, 3, '2026-01-20', 'marketing_team', '2024-01-20 14:00:00', 'active'),
    (4, 'DOC-LEGAL-001', 'Vendor Contract - TechCorp', 'document', 'legal', 'confidential', FALSE, FALSE, 4, '2124-01-01', 'legal_team', '2024-01-05 11:00:00', 'active'),
    (5, 'IMG-HR-001', 'Employee Badge Photo', 'image', 'hr', 'restricted', TRUE, FALSE, 5, '2025-01-25', 'hr_team', '2024-01-25 08:00:00', 'active'),
    (6, 'DOC-MED-001', 'Medical Records Summary', 'document', 'medical', 'highly_restricted', TRUE, TRUE, 2, '2029-06-01', 'hr_team', '2024-01-12 10:00:00', 'active');

Step 10: Insert Audit Records

Add access and change logs.

INSERT INTO media_access_audit (id, asset_id, user_id, user_name, user_department, user_role, access_type, access_reason, access_granted, ip_address, access_time) VALUES
    (1, 1, 'USR-001', 'Alice Finance', 'Finance', 'Analyst', 'view', 'Quarterly review', TRUE, '192.168.1.100', '2024-01-25 10:30:00'),
    (2, 1, 'USR-002', 'Bob Manager', 'Finance', 'Manager', 'download', 'Board presentation', TRUE, '192.168.1.101', '2024-01-25 11:00:00'),
    (3, 2, 'USR-003', 'Carol HR', 'HR', 'Specialist', 'view', 'Employment verification', TRUE, '192.168.1.102', '2024-01-25 09:15:00'),
    (4, 2, 'USR-004', 'Dave Sales', 'Sales', 'Rep', 'view', 'Reference check', FALSE, '192.168.1.103', '2024-01-25 14:00:00'),
    (5, 6, 'USR-003', 'Carol HR', 'HR', 'Specialist', 'view', 'Benefits administration', TRUE, '192.168.1.102', '2024-01-25 10:00:00'),
    (6, 3, 'USR-005', 'Eve Marketing', 'Marketing', 'Coordinator', 'download', 'Campaign assets', TRUE, '192.168.1.104', '2024-01-25 15:30:00');

UPDATE media_access_audit SET denial_reason = 'Insufficient permissions for HR records' WHERE id = 4;

INSERT INTO media_change_audit (id, asset_id, change_type, field_changed, old_value, new_value, changed_by, change_reason, change_time) VALUES
    (1, 1, 'update', 'classification', 'internal', 'confidential', 'finance_admin', 'Upgraded classification per policy', '2024-01-20 09:00:00'),
    (2, 3, 'update', 'retention_until', '2025-01-20', '2026-01-20', 'marketing_admin', 'Extended retention for reuse', '2024-01-22 11:00:00'),
    (3, 5, 'update', 'contains_pii', 'false', 'true', 'compliance_officer', 'Corrected PII flag', '2024-01-24 14:00:00');

Step 11: Insert Legal Holds

Add litigation holds.

INSERT INTO legal_holds (id, hold_code, hold_name, description, matter_reference, custodian, hold_reason, start_date, status, created_by, created_at) VALUES
    (1, 'HOLD-2024-001', 'Employee Dispute Matter', 'Legal hold for employee dispute investigation', 'LEGAL-2024-EMP-001', 'General Counsel', 'Pending litigation', '2024-01-15', 'active', 'legal_admin', '2024-01-15 08:00:00'),
    (2, 'HOLD-2024-002', 'Regulatory Investigation', 'Document preservation for regulatory inquiry', 'LEGAL-2024-REG-001', 'Compliance Officer', 'SEC investigation', '2024-01-20', 'active', 'compliance_admin', '2024-01-20 09:00:00');

INSERT INTO hold_assets (id, hold_id, asset_id, added_by, added_at) VALUES
    (1, 1, 2, 'legal_admin', '2024-01-15 08:30:00'),
    (2, 1, 5, 'legal_admin', '2024-01-15 08:35:00'),
    (3, 2, 1, 'compliance_admin', '2024-01-20 09:30:00');

UPDATE compliant_media SET legal_hold = TRUE WHERE id IN (1, 2, 5);

Step 12: Insert Deletion Requests

Track deletion workflows.

INSERT INTO deletion_requests (id, request_code, asset_id, request_type, request_reason, requester_id, requester_name, request_date, approval_status, blocked_reason) VALUES
    (1, 'DEL-2024-001', 3, 'retention_expiry', 'Content past retention date', 'system', 'Automated', '2024-01-25 00:00:00', 'pending', NULL),
    (2, 'DEL-2024-002', 2, 'gdpr_request', 'Subject access request - deletion', 'USR-006', 'Data Subject', '2024-01-24 10:00:00', 'blocked', 'Asset under legal hold'),
    (3, 'DEL-2024-003', 5, 'gdpr_request', 'Right to be forgotten request', 'USR-006', 'Data Subject', '2024-01-24 10:30:00', 'blocked', 'Asset under legal hold');

Step 13: Compliance Dashboard

View compliance status.

SELECT
    cm.classification,
    COUNT(*) as total_assets,
    COUNT(CASE WHEN cm.contains_pii THEN 1 END) as pii_assets,
    COUNT(CASE WHEN cm.contains_phi THEN 1 END) as phi_assets,
    COUNT(CASE WHEN cm.legal_hold THEN 1 END) as on_hold,
    COUNT(CASE WHEN cm.retention_until < CURRENT_DATE THEN 1 END) as past_retention
FROM compliant_media cm
WHERE cm.status = 'active'
GROUP BY cm.classification
ORDER BY total_assets DESC;

Step 14: Access Audit Report

Review access patterns.

SELECT
    cm.asset_id,
    cm.title,
    cm.sensitivity_level,
    COUNT(maa.id) as total_accesses,
    COUNT(CASE WHEN maa.access_granted THEN 1 END) as granted,
    COUNT(CASE WHEN NOT maa.access_granted THEN 1 END) as denied,
    COUNT(DISTINCT maa.user_id) as unique_users,
    MAX(maa.access_time) as last_accessed
FROM compliant_media cm
LEFT JOIN media_access_audit maa ON cm.id = maa.asset_id
WHERE maa.access_time >= CURRENT_DATE - 30
GROUP BY cm.id, cm.asset_id, cm.title, cm.sensitivity_level
ORDER BY total_accesses DESC;

Step 15: Retention Expiry Report

Track upcoming expirations.

SELECT
    cm.asset_id,
    cm.title,
    cm.classification,
    rp.policy_name,
    cm.retention_until,
    rp.action_on_expiry,
    cm.legal_hold,
    CASE
        WHEN cm.legal_hold THEN 'Blocked - Legal Hold'
        WHEN cm.retention_until < CURRENT_DATE THEN 'Past Due'
        WHEN cm.retention_until < CURRENT_DATE + 30 THEN 'Expiring Soon'
        ELSE 'Active'
    END as status
FROM compliant_media cm
INNER JOIN retention_policies rp ON cm.retention_policy_id = rp.id
WHERE cm.retention_until < CURRENT_DATE + 90
  AND cm.status = 'active'
ORDER BY cm.retention_until;

Cleanup (Optional)

DROP TABLE IF EXISTS deletion_requests;
DROP TABLE IF EXISTS compliance_certifications;
DROP TABLE IF EXISTS hold_assets;
DROP TABLE IF EXISTS legal_holds;
DROP TABLE IF EXISTS media_change_audit;
DROP TABLE IF EXISTS media_access_audit;
DROP TABLE IF EXISTS compliant_media;
DROP TABLE IF EXISTS retention_policies;

Expected Outcomes

• Media classified by sensitivity
• Retention policies enforced
• Access fully audited
• Legal holds managed
• Deletion workflows tracked

Compliance Levels

Key Concepts Learned

• Data classification
• Retention management
• Access auditing
• Legal hold processes
• GDPR compliance