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