Document Version Control

Objective

Implement version control for documents with change history tracking. This enables document revision management, audit trails, and collaborative document editing.

Step 1: Create Documents Table

Create a table for versioned documents.

CREATE TABLE versioned_documents (
    id INTEGER PRIMARY KEY,
    document_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(50),
    current_version INTEGER DEFAULT 1,
    total_versions INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'draft',
    owner VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Versions Table

Create a table for version history.

CREATE TABLE document_versions (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    version_number INTEGER NOT NULL,
    document_file PDF,
    file_size BIGINT,
    page_count INTEGER,
    change_summary TEXT,
    change_type VARCHAR(20) DEFAULT 'update',
    created_by VARCHAR(100) NOT NULL,
    approved_by VARCHAR(100),
    approval_date TIMESTAMP,
    is_current BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES versioned_documents(id)
);

Step 3: Create Change Log Table

Track detailed changes.

CREATE TABLE version_changes (
    id INTEGER PRIMARY KEY,
    version_id INTEGER NOT NULL,
    section VARCHAR(100),
    change_description TEXT NOT NULL,
    change_author VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (version_id) REFERENCES document_versions(id)
);

Step 4: Insert Sample Documents

Add documents for versioning.

INSERT INTO versioned_documents (id, document_code, title, category, current_version, total_versions, status, owner) VALUES
    (1, 'DOC-001', 'Product Requirements Document', 'product', 4, 4, 'approved', 'product_manager'),
    (2, 'DOC-002', 'System Architecture Specification', 'technical', 3, 3, 'approved', 'tech_lead'),
    (3, 'DOC-003', 'Employee Handbook', 'hr', 2, 2, 'published', 'hr_director'),
    (4, 'DOC-004', 'Security Policy', 'security', 5, 5, 'approved', 'security_officer'),
    (5, 'DOC-005', 'API Documentation', 'technical', 2, 2, 'draft', 'api_team');

Step 5: Insert Version History

Add version records.

INSERT INTO document_versions (id, document_id, version_number, file_size, page_count, change_summary, change_type, created_by, approved_by, approval_date, is_current, is_published) VALUES
    -- Product Requirements versions
    (1, 1, 1, 512000, 12, 'Initial draft of product requirements', 'create', 'pm_john', NULL, NULL, FALSE, FALSE),
    (2, 1, 2, 645000, 15, 'Added user stories and acceptance criteria', 'major', 'pm_john', 'director_mary', '2024-01-10 14:00:00', FALSE, FALSE),
    (3, 1, 3, 720000, 18, 'Incorporated stakeholder feedback', 'major', 'pm_john', 'director_mary', '2024-01-20 10:00:00', FALSE, FALSE),
    (4, 1, 4, 780000, 20, 'Final review and formatting updates', 'minor', 'pm_john', 'director_mary', '2024-01-25 16:00:00', TRUE, TRUE),
    -- Architecture Spec versions
    (5, 2, 1, 1024000, 28, 'Initial architecture design', 'create', 'arch_sam', NULL, NULL, FALSE, FALSE),
    (6, 2, 2, 1280000, 35, 'Added microservices details', 'major', 'arch_sam', 'cto', '2024-02-01 11:00:00', FALSE, FALSE),
    (7, 2, 3, 1350000, 38, 'Security review updates', 'minor', 'arch_sam', 'cto', '2024-02-10 15:00:00', TRUE, TRUE),
    -- Employee Handbook versions
    (8, 3, 1, 2048000, 45, 'Initial handbook release', 'create', 'hr_jane', 'hr_director', '2023-06-01 09:00:00', FALSE, TRUE),
    (9, 3, 2, 2200000, 48, 'Updated remote work policies', 'major', 'hr_jane', 'hr_director', '2024-01-05 10:00:00', TRUE, TRUE),
    -- Security Policy versions
    (10, 4, 1, 450000, 10, 'Initial security policy', 'create', 'sec_admin', 'ciso', '2023-01-15 14:00:00', FALSE, FALSE),
    (11, 4, 2, 520000, 12, 'Added cloud security section', 'major', 'sec_admin', 'ciso', '2023-04-20 11:00:00', FALSE, FALSE),
    (12, 4, 3, 580000, 14, 'Updated compliance requirements', 'major', 'sec_admin', 'ciso', '2023-08-10 16:00:00', FALSE, FALSE),
    (13, 4, 4, 620000, 15, 'Added incident response procedures', 'major', 'sec_admin', 'ciso', '2023-11-15 10:00:00', FALSE, TRUE),
    (14, 4, 5, 650000, 16, 'Annual review and updates', 'minor', 'sec_admin', 'ciso', '2024-01-30 14:00:00', TRUE, TRUE);

Step 6: Insert Change Details

Add detailed change records.

INSERT INTO version_changes (id, version_id, section, change_description, change_author) VALUES
    (1, 2, 'Section 2', 'Added 15 new user stories for authentication module', 'pm_john'),
    (2, 2, 'Section 3', 'Defined acceptance criteria for all user stories', 'pm_john'),
    (3, 3, 'Section 2', 'Incorporated feedback from engineering team', 'pm_john'),
    (4, 3, 'Section 4', 'Added performance requirements', 'pm_john'),
    (5, 4, 'Formatting', 'Applied corporate document template', 'pm_john'),
    (6, 6, 'Section 3', 'Detailed microservices communication patterns', 'arch_sam'),
    (7, 6, 'Section 4', 'Added database architecture diagram', 'arch_sam'),
    (8, 7, 'Section 5', 'Added security considerations per CISO review', 'arch_sam'),
    (9, 9, 'Section 8', 'New remote work policy guidelines', 'hr_jane'),
    (10, 9, 'Section 9', 'Updated equipment allowance policy', 'hr_jane');

Step 7: Get Document with Current Version

View document with its current version.

SELECT
    vd.document_code,
    vd.title,
    vd.category,
    vd.current_version,
    vd.status,
    dv.change_summary as latest_changes,
    dv.created_by as last_updated_by,
    dv.created_at as version_date
FROM versioned_documents vd
INNER JOIN document_versions dv ON vd.id = dv.document_id AND dv.is_current = TRUE
ORDER BY vd.document_code;

Step 8: Get Version History

View complete version history for a document.

SELECT
    version_number,
    change_summary,
    change_type,
    created_by,
    approved_by,
    approval_date,
    is_current,
    is_published
FROM document_versions
WHERE document_id = 1
ORDER BY version_number DESC;

Step 9: Get Detailed Changes

View changes for a specific version.

SELECT
    dv.version_number,
    vc.section,
    vc.change_description,
    vc.change_author,
    vc.changed_at
FROM document_versions dv
INNER JOIN version_changes vc ON dv.id = vc.version_id
WHERE dv.document_id = 1
ORDER BY dv.version_number, vc.id;

Step 10: Version Comparison Summary

Compare version sizes and pages.

SELECT
    vd.title,
    dv.version_number,
    dv.page_count,
    dv.file_size / 1024 as size_kb,
    dv.change_type,
    dv.is_current
FROM versioned_documents vd
INNER JOIN document_versions dv ON vd.id = dv.document_id
WHERE vd.id = 1
ORDER BY dv.version_number;

Step 11: Documents with Most Revisions

Find most frequently updated documents.

SELECT
    vd.document_code,
    vd.title,
    vd.total_versions,
    vd.owner,
    COUNT(CASE WHEN dv.change_type = 'major' THEN 1 END) as major_changes,
    COUNT(CASE WHEN dv.change_type = 'minor' THEN 1 END) as minor_changes
FROM versioned_documents vd
INNER JOIN document_versions dv ON vd.id = dv.document_id
GROUP BY vd.id, vd.document_code, vd.title, vd.total_versions, vd.owner
ORDER BY vd.total_versions DESC;

Step 12: Author Contribution Summary

Track author contributions.

SELECT
    created_by as author,
    COUNT(*) as versions_created,
    COUNT(CASE WHEN change_type = 'major' THEN 1 END) as major_updates,
    COUNT(CASE WHEN is_current THEN 1 END) as current_owner
FROM document_versions
GROUP BY created_by
ORDER BY versions_created DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS version_changes;
DROP TABLE IF EXISTS document_versions;
DROP TABLE IF EXISTS versioned_documents;

Expected Outcomes

• Version history tracked
• Changes documented
• Current version identified
• Approval workflow enabled
• Author tracking works

Change Types

Key Concepts Learned

• Document versioning
• Change history tracking
• Approval workflows
• Version comparison
• Author contributions