Legal Document Repository

Objective

Create a legal document management system for storing contracts, agreements, and compliance documents with version control, expiration tracking, and access controls.

Step 1: Create Document Types Table

Define legal document categories.

CREATE TABLE legal_doc_types (
    id INTEGER PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100),
    description TEXT,
    default_retention_years INTEGER DEFAULT 7,
    requires_signature BOOLEAN DEFAULT TRUE,
    requires_witness BOOLEAN DEFAULT FALSE,
    renewal_reminder_days INTEGER DEFAULT 90,
    is_active BOOLEAN DEFAULT TRUE
);

Step 2: Create Legal Entities Table

Store parties involved in agreements.

CREATE TABLE legal_entities (
    id INTEGER PRIMARY KEY,
    entity_code VARCHAR(50) NOT NULL UNIQUE,
    entity_name VARCHAR(200) NOT NULL,
    entity_type VARCHAR(50),
    registration_number VARCHAR(100),
    tax_id VARCHAR(50),
    address TEXT,
    city VARCHAR(100),
    country VARCHAR(100),
    primary_contact VARCHAR(200),
    contact_email VARCHAR(200),
    contact_phone VARCHAR(50),
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Legal Documents Table

Store document metadata.

CREATE TABLE legal_documents (
    id INTEGER PRIMARY KEY,
    doc_number VARCHAR(50) NOT NULL UNIQUE,
    type_id INTEGER NOT NULL,
    title VARCHAR(300) NOT NULL,
    description TEXT,
    internal_party VARCHAR(200),
    department VARCHAR(100),
    document_file PDF,
    original_file PDF,
    file_size_mb DECIMAL(10, 2),
    page_count INTEGER,
    effective_date DATE,
    expiration_date DATE,
    renewal_date DATE,
    termination_date DATE,
    value_amount DECIMAL(15, 2),
    currency VARCHAR(10) DEFAULT 'USD',
    status VARCHAR(50) DEFAULT 'draft',
    confidentiality VARCHAR(50) DEFAULT 'confidential',
    created_by VARCHAR(100),
    approved_by VARCHAR(100),
    approved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (type_id) REFERENCES legal_doc_types(id)
);

Step 4: Create Document Parties Table

Link documents to entities.

CREATE TABLE document_parties (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    entity_id INTEGER NOT NULL,
    party_role VARCHAR(50),
    signing_authority VARCHAR(200),
    signed_date DATE,
    signature_page INTEGER,
    is_primary BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (document_id) REFERENCES legal_documents(id),
    FOREIGN KEY (entity_id) REFERENCES legal_entities(id)
);

Step 5: Create Document Versions Table

Track version history.

CREATE TABLE legal_doc_versions (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    version_number INTEGER DEFAULT 1,
    version_label VARCHAR(50),
    document_file PDF,
    redline_file PDF,
    change_summary TEXT,
    changed_by VARCHAR(100),
    is_current BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES legal_documents(id)
);

Step 6: Create Document Amendments Table

Track contract amendments.

CREATE TABLE document_amendments (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    amendment_number INTEGER,
    amendment_title VARCHAR(200),
    amendment_file PDF,
    description TEXT,
    effective_date DATE,
    approved_by VARCHAR(100),
    approved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES legal_documents(id)
);

Step 7: Create Access Log Table

Track document access.

CREATE TABLE legal_doc_access_log (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    user_name VARCHAR(200),
    user_department VARCHAR(100),
    access_type VARCHAR(50),
    access_reason TEXT,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(50),
    FOREIGN KEY (document_id) REFERENCES legal_documents(id)
);

Step 8: Create Reminders Table

Track renewal and expiration alerts.

CREATE TABLE legal_doc_reminders (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    reminder_type VARCHAR(50),
    reminder_date DATE,
    recipient_email VARCHAR(200),
    recipient_name VARCHAR(200),
    message TEXT,
    is_sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES legal_documents(id)
);

Step 9: Insert Sample Data

Add document types and entities.

INSERT INTO legal_doc_types (id, type_code, type_name, description, default_retention_years, requires_signature, renewal_reminder_days) VALUES
    (1, 'MSA', 'Master Service Agreement', 'Primary service agreement with vendors', 10, TRUE, 90),
    (2, 'NDA', 'Non-Disclosure Agreement', 'Confidentiality agreements', 7, TRUE, 60),
    (3, 'SOW', 'Statement of Work', 'Project-specific work orders', 7, TRUE, 30),
    (4, 'EMPLOY', 'Employment Contract', 'Employee employment agreements', 7, TRUE, 0),
    (5, 'LEASE', 'Lease Agreement', 'Real estate and equipment leases', 10, TRUE, 180),
    (6, 'LICENSE', 'Software License', 'Software licensing agreements', 5, TRUE, 90),
    (7, 'INSURANCE', 'Insurance Policy', 'Corporate insurance policies', 7, FALSE, 60),
    (8, 'COMPLIANCE', 'Compliance Certificate', 'Regulatory compliance documents', 10, TRUE, 30);

INSERT INTO legal_entities (id, entity_code, entity_name, entity_type, registration_number, country, primary_contact, contact_email, status) VALUES
    (1, 'ENT-001', 'TechVendor Inc.', 'vendor', 'REG-12345', 'USA', 'John Smith', 'john@techvendor.com', 'active'),
    (2, 'ENT-002', 'Cloud Services LLC', 'vendor', 'REG-23456', 'USA', 'Jane Doe', 'jane@cloudservices.com', 'active'),
    (3, 'ENT-003', 'Consulting Partners', 'consultant', 'REG-34567', 'UK', 'Mike Johnson', 'mike@consultingpartners.com', 'active'),
    (4, 'ENT-004', 'Property Management Corp', 'landlord', 'REG-45678', 'USA', 'Sarah Wilson', 'sarah@propmgmt.com', 'active'),
    (5, 'ENT-005', 'Insurance Group', 'insurer', 'REG-56789', 'USA', 'Robert Brown', 'robert@insurancegroup.com', 'active');

Step 10: Insert Legal Documents

Add contract records.

INSERT INTO legal_documents (id, doc_number, type_id, title, description, internal_party, department, page_count, effective_date, expiration_date, value_amount, currency, status, confidentiality, created_by, approved_by, approved_at) VALUES
    (1, 'MSA-2023-001', 1, 'Master Service Agreement - TechVendor', 'IT services agreement', 'Our Company Inc.', 'IT', 45, '2023-01-01', '2025-12-31', 500000.00, 'USD', 'active', 'confidential', 'legal.team', 'General Counsel', '2022-12-15 10:00:00'),
    (2, 'NDA-2023-015', 2, 'Mutual NDA - Consulting Partners', 'Mutual confidentiality agreement', 'Our Company Inc.', 'Legal', 8, '2023-03-01', '2026-02-28', NULL, 'USD', 'active', 'confidential', 'legal.team', 'Legal Director', '2023-02-20 14:00:00'),
    (3, 'SOW-2023-008', 3, 'Website Redesign Project', 'SOW under MSA-2023-001', 'Our Company Inc.', 'Marketing', 15, '2023-06-01', '2023-12-31', 75000.00, 'USD', 'completed', 'internal', 'project.manager', 'VP Marketing', '2023-05-25 11:00:00'),
    (4, 'LEASE-2022-001', 5, 'Office Lease - Main Building', 'Primary office space lease', 'Our Company Inc.', 'Facilities', 120, '2022-01-01', '2027-12-31', 2400000.00, 'USD', 'active', 'confidential', 'facilities.team', 'CFO', '2021-11-30 09:00:00'),
    (5, 'LICENSE-2024-001', 6, 'Enterprise Software License', 'Annual software subscription', 'Our Company Inc.', 'IT', 25, '2024-01-01', '2024-12-31', 150000.00, 'USD', 'active', 'internal', 'it.procurement', 'CIO', '2023-12-15 16:00:00'),
    (6, 'INS-2024-001', 7, 'General Liability Insurance', '2024 liability coverage', 'Our Company Inc.', 'Finance', 50, '2024-01-01', '2024-12-31', 85000.00, 'USD', 'active', 'internal', 'risk.manager', 'CFO', '2023-12-20 10:00:00');

Step 11: Insert Parties and Versions

Link entities and track versions.

INSERT INTO document_parties (id, document_id, entity_id, party_role, signing_authority, signed_date, is_primary) VALUES
    (1, 1, 1, 'vendor', 'John Smith, CEO', '2022-12-10', TRUE),
    (2, 2, 3, 'disclosing_party', 'Mike Johnson, Director', '2023-02-18', TRUE),
    (3, 3, 1, 'contractor', 'John Smith, CEO', '2023-05-20', TRUE),
    (4, 4, 4, 'landlord', 'Sarah Wilson, President', '2021-11-25', TRUE),
    (5, 6, 5, 'insurer', 'Robert Brown, VP', '2023-12-18', TRUE);

INSERT INTO legal_doc_versions (id, document_id, version_number, version_label, change_summary, changed_by, is_current, created_at) VALUES
    (1, 1, 1, 'Draft', 'Initial draft', 'legal.team', FALSE, '2022-11-01 10:00:00'),
    (2, 1, 2, 'Review', 'Added SLA terms', 'legal.team', FALSE, '2022-11-15 14:00:00'),
    (3, 1, 3, 'Final', 'Executed version', 'legal.team', TRUE, '2022-12-15 10:00:00'),
    (4, 4, 1, 'Draft', 'Initial lease terms', 'facilities.team', FALSE, '2021-10-01 09:00:00'),
    (5, 4, 2, 'Final', 'Executed lease agreement', 'facilities.team', TRUE, '2021-11-30 09:00:00');

Step 12: Insert Amendments and Reminders

Add amendments and alerts.

INSERT INTO document_amendments (id, document_id, amendment_number, amendment_title, description, effective_date, approved_by, approved_at) VALUES
    (1, 1, 1, 'Amendment 1 - Rate Adjustment', 'Annual rate increase as per contract terms', '2024-01-01', 'General Counsel', '2023-12-01 10:00:00'),
    (2, 4, 1, 'Amendment 1 - Space Expansion', 'Additional 5000 sq ft on floor 3', '2023-06-01', 'CFO', '2023-05-15 14:00:00');

INSERT INTO legal_doc_reminders (id, document_id, reminder_type, reminder_date, recipient_email, recipient_name, message, is_sent) VALUES
    (1, 1, 'renewal', '2025-09-30', 'legal@company.com', 'Legal Team', 'MSA with TechVendor expires in 90 days', FALSE),
    (2, 5, 'expiration', '2024-10-01', 'it@company.com', 'IT Team', 'Software license expires in 90 days', FALSE),
    (3, 6, 'renewal', '2024-11-01', 'finance@company.com', 'Finance Team', 'Insurance policy renewal due in 60 days', FALSE),
    (4, 4, 'renewal', '2027-06-30', 'facilities@company.com', 'Facilities Team', 'Office lease expires in 180 days', FALSE);

Step 13: Active Contracts Dashboard

View current agreements.

SELECT
    ld.doc_number,
    ldt.type_name,
    ld.title,
    le.entity_name as counterparty,
    ld.effective_date,
    ld.expiration_date,
    ld.value_amount,
    ld.status,
    ld.department
FROM legal_documents ld
INNER JOIN legal_doc_types ldt ON ld.type_id = ldt.id
LEFT JOIN document_parties dp ON ld.id = dp.document_id AND dp.is_primary = TRUE
LEFT JOIN legal_entities le ON dp.entity_id = le.id
WHERE ld.status = 'active'
ORDER BY ld.expiration_date;

Step 14: Expiring Contracts Report

Track upcoming renewals.

SELECT
    ld.doc_number,
    ldt.type_name,
    ld.title,
    ld.expiration_date,
    ld.value_amount,
    ld.department,
    CASE
        WHEN ld.expiration_date <= CURRENT_DATE + 30 THEN 'Critical'
        WHEN ld.expiration_date <= CURRENT_DATE + 90 THEN 'Warning'
        ELSE 'Normal'
    END as urgency_level
FROM legal_documents ld
INNER JOIN legal_doc_types ldt ON ld.type_id = ldt.id
WHERE ld.status = 'active'
  AND ld.expiration_date IS NOT NULL
  AND ld.expiration_date <= CURRENT_DATE + 180
ORDER BY ld.expiration_date;

Step 15: Contract Value Summary

Analyze contract portfolio.

SELECT
    ldt.type_name,
    COUNT(ld.id) as contract_count,
    SUM(ld.value_amount) as total_value,
    AVG(ld.value_amount) as avg_value,
    MIN(ld.expiration_date) as earliest_expiration,
    MAX(ld.expiration_date) as latest_expiration
FROM legal_documents ld
INNER JOIN legal_doc_types ldt ON ld.type_id = ldt.id
WHERE ld.status = 'active'
  AND ld.value_amount IS NOT NULL
GROUP BY ldt.id, ldt.type_name
ORDER BY total_value DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS legal_doc_reminders;
DROP TABLE IF EXISTS legal_doc_access_log;
DROP TABLE IF EXISTS document_amendments;
DROP TABLE IF EXISTS legal_doc_versions;
DROP TABLE IF EXISTS document_parties;
DROP TABLE IF EXISTS legal_documents;
DROP TABLE IF EXISTS legal_entities;
DROP TABLE IF EXISTS legal_doc_types;

Expected Outcomes

• Contracts organized by type
• Parties linked to documents
• Versions tracked
• Expirations monitored
• Access logged

Key Concepts Learned

• Legal document management
• Version control
• Party relationships
• Expiration tracking
• Compliance monitoring