Legal Document Repository with Contract Tracking

Objective

Create a legal document repository for managing contracts, agreements, and legal filings. This enables contract lifecycle management, compliance tracking, and legal document search.

Step 1: Create Legal Documents Table

Create a table for legal documents.

CREATE TABLE legal_documents (
    id INTEGER PRIMARY KEY,
    document PDF,
    document_number VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    category VARCHAR(50),
    status VARCHAR(20) DEFAULT 'draft',
    effective_date DATE,
    expiration_date DATE,
    parties TEXT,
    jurisdiction VARCHAR(100),
    confidentiality VARCHAR(20) DEFAULT 'confidential',
    page_count INTEGER,
    summary TEXT,
    created_by VARCHAR(100),
    approved_by VARCHAR(100),
    approved_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Clauses Table

Track important contract clauses.

CREATE TABLE document_clauses (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    clause_number VARCHAR(20),
    clause_title VARCHAR(255) NOT NULL,
    clause_type VARCHAR(50),
    clause_text TEXT,
    is_critical BOOLEAN DEFAULT FALSE,
    review_status VARCHAR(20) DEFAULT 'pending',
    notes TEXT,
    FOREIGN KEY (document_id) REFERENCES legal_documents(id)
);

Step 3: Insert Sample Legal Documents

Add sample legal documents.

INSERT INTO legal_documents (id, document_number, title, document_type, category, status, effective_date, expiration_date, parties, jurisdiction, confidentiality, page_count, summary, created_by, approved_by, approved_date) VALUES
    (1, 'CTR-2024-001', 'Master Service Agreement - TechCorp', 'contract', 'service', 'active', '2024-01-01', '2026-12-31', 'Company Inc, TechCorp LLC', 'Delaware', 'confidential', 25, 'Master services agreement for software development and consulting services', 'legal_team', 'general_counsel', '2023-12-15'),
    (2, 'CTR-2024-002', 'Software License Agreement', 'license', 'software', 'active', '2024-02-01', '2025-01-31', 'Company Inc, SoftVendor Inc', 'California', 'confidential', 18, 'Enterprise software license for CRM platform', 'legal_team', 'cto', '2024-01-20'),
    (3, 'NDA-2024-001', 'Mutual Non-Disclosure Agreement', 'nda', 'confidentiality', 'active', '2024-01-15', '2027-01-14', 'Company Inc, Partner Corp', 'New York', 'highly_confidential', 8, 'Mutual NDA for partnership discussions', 'legal_team', 'general_counsel', '2024-01-10'),
    (4, 'CTR-2024-003', 'Employment Agreement - Executive', 'employment', 'hr', 'active', '2024-03-01', NULL, 'Company Inc, John Executive', 'Delaware', 'highly_confidential', 15, 'Executive employment agreement with equity provisions', 'hr_legal', 'ceo', '2024-02-25'),
    (5, 'CTR-2024-004', 'Vendor Services Agreement', 'contract', 'vendor', 'pending', '2024-04-01', '2025-03-31', 'Company Inc, CloudHost Inc', 'Washington', 'confidential', 22, 'Cloud hosting services agreement', 'legal_team', NULL, NULL),
    (6, 'POL-2024-001', 'Data Privacy Policy', 'policy', 'compliance', 'active', '2024-01-01', '2024-12-31', 'Company Inc', 'Multi-jurisdiction', 'public', 12, 'Company data privacy and protection policy', 'compliance_team', 'dpo', '2023-12-20'),
    (7, 'CTR-2023-045', 'Office Lease Agreement', 'lease', 'real_estate', 'active', '2023-06-01', '2028-05-31', 'Company Inc, Property Holdings LLC', 'Texas', 'confidential', 35, 'Commercial office space lease', 'legal_team', 'cfo', '2023-05-15');

Step 4: Insert Document Clauses

Add key clauses from documents.

INSERT INTO document_clauses (id, document_id, clause_number, clause_title, clause_type, clause_text, is_critical, review_status) VALUES
    -- MSA clauses
    (1, 1, '4.1', 'Payment Terms', 'financial', 'Payment due within 30 days of invoice date. Late payments subject to 1.5% monthly interest.', TRUE, 'approved'),
    (2, 1, '7.2', 'Limitation of Liability', 'liability', 'Total liability limited to fees paid in preceding 12 months.', TRUE, 'approved'),
    (3, 1, '8.1', 'Termination for Convenience', 'termination', 'Either party may terminate with 90 days written notice.', TRUE, 'approved'),
    (4, 1, '10.3', 'Indemnification', 'indemnity', 'Mutual indemnification for third-party claims arising from breach.', TRUE, 'approved'),
    -- License Agreement clauses
    (5, 2, '2.1', 'Grant of License', 'rights', 'Non-exclusive, non-transferable license to use software.', TRUE, 'approved'),
    (6, 2, '5.1', 'Maintenance and Support', 'support', 'Vendor provides 24/7 technical support during term.', FALSE, 'approved'),
    -- NDA clauses
    (7, 3, '3.1', 'Confidentiality Obligations', 'confidentiality', 'Receiving party shall protect information using same care as own confidential information.', TRUE, 'approved'),
    (8, 3, '4.1', 'Permitted Disclosures', 'confidentiality', 'May disclose to employees and advisors on need-to-know basis.', FALSE, 'approved'),
    -- Employment Agreement clauses
    (9, 4, '6.1', 'Non-Compete', 'restrictive', '12-month non-compete within defined industry sector.', TRUE, 'approved'),
    (10, 4, '7.1', 'Equity Vesting', 'compensation', 'Stock options vest over 4 years with 1-year cliff.', TRUE, 'approved');

Step 5: Query Active Contracts

Get all active legal documents.

SELECT
    document_number,
    title,
    document_type,
    effective_date,
    expiration_date,
    parties,
    status
FROM legal_documents
WHERE status = 'active'
ORDER BY expiration_date;

Step 6: Expiring Documents

Find documents expiring soon.

SELECT
    document_number,
    title,
    document_type,
    expiration_date,
    parties,
    CASE
        WHEN expiration_date < CURRENT_DATE THEN 'Expired'
        ELSE 'Expiring Soon'
    END as alert_status
FROM legal_documents
WHERE expiration_date IS NOT NULL
  AND expiration_date <= CURRENT_DATE + 90
  AND status = 'active'
ORDER BY expiration_date;

Step 7: Get Document with Clauses

View document with key clauses.

SELECT
    ld.title,
    dc.clause_number,
    dc.clause_title,
    dc.clause_type,
    dc.is_critical,
    dc.review_status
FROM legal_documents ld
INNER JOIN document_clauses dc ON ld.id = dc.document_id
WHERE ld.id = 1
ORDER BY dc.clause_number;

Step 8: Critical Clauses Report

Get all critical clauses across documents.

SELECT
    ld.document_number,
    ld.title as document_title,
    dc.clause_number,
    dc.clause_title,
    dc.clause_type
FROM legal_documents ld
INNER JOIN document_clauses dc ON ld.id = dc.document_id
WHERE dc.is_critical = TRUE
ORDER BY ld.document_number, dc.clause_number;

Step 9: Documents by Type

Analyze repository by document type.

SELECT
    document_type,
    COUNT(*) as doc_count,
    SUM(page_count) as total_pages,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count
FROM legal_documents
GROUP BY document_type
ORDER BY doc_count DESC;

Step 10: Pending Approvals

Find documents awaiting approval.

SELECT
    document_number,
    title,
    document_type,
    created_by,
    created_at
FROM legal_documents
WHERE status = 'pending'
  OR approved_by IS NULL
ORDER BY created_at;

Step 11: Jurisdiction Analysis

Review documents by jurisdiction.

SELECT
    jurisdiction,
    COUNT(*) as document_count,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count
FROM legal_documents
GROUP BY jurisdiction
ORDER BY document_count DESC;

Step 12: Contract Renewal Calendar

Create renewal schedule.

SELECT
    document_number,
    title,
    parties,
    expiration_date,
    CASE
        WHEN expiration_date < CURRENT_DATE THEN 'Overdue'
        WHEN expiration_date <= CURRENT_DATE + 30 THEN 'Urgent - 30 days'
        WHEN expiration_date <= CURRENT_DATE + 90 THEN 'Soon - 90 days'
        ELSE 'Not Urgent'
    END as renewal_priority
FROM legal_documents
WHERE status = 'active'
  AND expiration_date IS NOT NULL
ORDER BY expiration_date;

Cleanup (Optional)

DROP TABLE IF EXISTS document_clauses;
DROP TABLE IF EXISTS legal_documents;

Expected Outcomes

• Legal documents stored securely
• Contract tracking enabled
• Clause extraction works
• Expiration alerts generated
• Compliance tracking ready

Document Types

Key Concepts Learned

• Legal document management
• Clause extraction and tracking
• Contract lifecycle management
• Expiration monitoring
• Jurisdiction tracking