Contract Management System

Objective

Build a comprehensive contract management system tracking parties, terms, obligations, and renewals. This enables contract lifecycle management, obligation monitoring, and risk assessment.

Step 1: Create Contracts Table

Create a table for contract documents.

CREATE TABLE contracts (
    id INTEGER PRIMARY KEY,
    contract_number VARCHAR(50) NOT NULL UNIQUE,
    contract_pdf PDF,
    title VARCHAR(255) NOT NULL,
    contract_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    effective_date DATE,
    end_date DATE,
    auto_renew BOOLEAN DEFAULT FALSE,
    renewal_period_months INTEGER,
    notice_period_days INTEGER DEFAULT 30,
    total_value DECIMAL(15, 2),
    currency VARCHAR(3) DEFAULT 'USD',
    risk_level VARCHAR(20) DEFAULT 'medium',
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Contract Parties Table

Track parties involved in contracts.

CREATE TABLE contract_parties (
    id INTEGER PRIMARY KEY,
    contract_id INTEGER NOT NULL,
    party_name VARCHAR(255) NOT NULL,
    party_type VARCHAR(20) NOT NULL,
    contact_name VARCHAR(100),
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    address TEXT,
    tax_id VARCHAR(50),
    FOREIGN KEY (contract_id) REFERENCES contracts(id)
);

Step 3: Create Obligations Table

Track contractual obligations.

CREATE TABLE contract_obligations (
    id INTEGER PRIMARY KEY,
    contract_id INTEGER NOT NULL,
    obligation_type VARCHAR(50) NOT NULL,
    responsible_party VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    due_date DATE,
    frequency VARCHAR(20),
    status VARCHAR(20) DEFAULT 'pending',
    last_completed DATE,
    next_due DATE,
    is_critical BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (contract_id) REFERENCES contracts(id)
);

Step 4: Insert Sample Contracts

Add sample contracts.

INSERT INTO contracts (id, contract_number, title, contract_type, status, effective_date, end_date, auto_renew, renewal_period_months, notice_period_days, total_value, risk_level, created_by) VALUES
    (1, 'CTR-2024-001', 'Enterprise Software License', 'license', 'active', '2024-01-01', '2026-12-31', TRUE, 12, 90, 250000.00, 'high', 'legal_team'),
    (2, 'CTR-2024-002', 'Cloud Infrastructure Services', 'service', 'active', '2024-02-01', '2025-01-31', TRUE, 12, 60, 180000.00, 'high', 'it_procurement'),
    (3, 'CTR-2024-003', 'Marketing Agency Retainer', 'service', 'active', '2024-01-15', '2024-12-31', FALSE, NULL, 30, 120000.00, 'medium', 'marketing'),
    (4, 'CTR-2024-004', 'Office Cleaning Services', 'service', 'active', '2024-01-01', '2024-12-31', TRUE, 12, 30, 36000.00, 'low', 'facilities'),
    (5, 'CTR-2024-005', 'Consulting Engagement', 'consulting', 'active', '2024-03-01', '2024-08-31', FALSE, NULL, 14, 150000.00, 'medium', 'strategy'),
    (6, 'CTR-2024-006', 'Equipment Lease', 'lease', 'active', '2024-01-01', '2027-12-31', FALSE, NULL, 90, 75000.00, 'medium', 'finance');

Step 5: Insert Contract Parties

Add parties to contracts.

INSERT INTO contract_parties (id, contract_id, party_name, party_type, contact_name, contact_email, tax_id) VALUES
    -- Software License parties
    (1, 1, 'Our Company Inc', 'licensee', 'Jane Doe', 'jane@ourcompany.com', '12-3456789'),
    (2, 1, 'SoftVendor Corp', 'licensor', 'Bob Smith', 'bob@softvendor.com', '98-7654321'),
    -- Cloud Services parties
    (3, 2, 'Our Company Inc', 'customer', 'IT Director', 'it@ourcompany.com', '12-3456789'),
    (4, 2, 'CloudProvider Inc', 'provider', 'Account Manager', 'support@cloudprovider.com', '45-6789012'),
    -- Marketing Agency parties
    (5, 3, 'Our Company Inc', 'client', 'Marketing VP', 'marketing@ourcompany.com', '12-3456789'),
    (6, 3, 'Creative Agency LLC', 'agency', 'Account Lead', 'lead@creativeagency.com', '67-8901234'),
    -- Consulting parties
    (7, 5, 'Our Company Inc', 'client', 'Strategy Director', 'strategy@ourcompany.com', '12-3456789'),
    (8, 5, 'Strategy Consultants', 'consultant', 'Partner', 'partner@stratconsult.com', '89-0123456');

Step 6: Insert Obligations

Add contractual obligations.

INSERT INTO contract_obligations (id, contract_id, obligation_type, responsible_party, description, due_date, frequency, status, is_critical) VALUES
    -- Software License obligations
    (1, 1, 'payment', 'Our Company Inc', 'Annual license fee payment', '2024-01-01', 'annual', 'completed', TRUE),
    (2, 1, 'compliance', 'Our Company Inc', 'User count audit and reporting', NULL, 'quarterly', 'pending', FALSE),
    (3, 1, 'support', 'SoftVendor Corp', 'Provide technical support', NULL, 'ongoing', 'ongoing', TRUE),
    -- Cloud Services obligations
    (4, 2, 'payment', 'Our Company Inc', 'Monthly service fee', NULL, 'monthly', 'ongoing', TRUE),
    (5, 2, 'sla', 'CloudProvider Inc', 'Maintain 99.9% uptime SLA', NULL, 'ongoing', 'ongoing', TRUE),
    (6, 2, 'security', 'CloudProvider Inc', 'Annual security audit report', '2024-02-01', 'annual', 'pending', TRUE),
    -- Marketing obligations
    (7, 3, 'payment', 'Our Company Inc', 'Monthly retainer payment', NULL, 'monthly', 'ongoing', TRUE),
    (8, 3, 'deliverable', 'Creative Agency LLC', 'Monthly campaign report', NULL, 'monthly', 'ongoing', FALSE),
    -- Consulting obligations
    (9, 5, 'payment', 'Our Company Inc', 'Milestone payments per SOW', NULL, 'milestone', 'pending', TRUE),
    (10, 5, 'deliverable', 'Strategy Consultants', 'Weekly status reports', NULL, 'weekly', 'ongoing', FALSE),
    (11, 5, 'deliverable', 'Strategy Consultants', 'Final strategy document', '2024-08-15', 'one-time', 'pending', TRUE);

Step 7: Contract Overview

Get all active contracts with summary.

SELECT
    c.contract_number,
    c.title,
    c.contract_type,
    c.effective_date,
    c.end_date,
    c.total_value,
    c.risk_level,
    c.auto_renew
FROM contracts c
WHERE c.status = 'active'
ORDER BY c.end_date;

Step 8: Contract with Parties

View contract with all parties.

SELECT
    c.contract_number,
    c.title,
    cp.party_name,
    cp.party_type,
    cp.contact_name,
    cp.contact_email
FROM contracts c
INNER JOIN contract_parties cp ON c.id = cp.contract_id
WHERE c.id = 1
ORDER BY cp.party_type;

Step 9: Pending Obligations

Get upcoming and overdue obligations.

SELECT
    c.contract_number,
    c.title,
    co.obligation_type,
    co.responsible_party,
    co.description,
    co.due_date,
    co.is_critical,
    CASE
        WHEN co.due_date < CURRENT_DATE THEN 'Overdue'
        WHEN co.due_date <= CURRENT_DATE + 30 THEN 'Due Soon'
        ELSE 'Upcoming'
    END as urgency
FROM contracts c
INNER JOIN contract_obligations co ON c.id = co.contract_id
WHERE co.status = 'pending'
  AND co.due_date IS NOT NULL
ORDER BY co.due_date;

Step 10: Renewal Calendar

Get contracts due for renewal.

SELECT
    contract_number,
    title,
    end_date,
    auto_renew,
    notice_period_days,
    end_date - notice_period_days as notice_deadline,
    CASE
        WHEN end_date - notice_period_days < CURRENT_DATE THEN 'Notice Period Passed'
        WHEN end_date - notice_period_days <= CURRENT_DATE + 30 THEN 'Action Required'
        ELSE 'Monitor'
    END as action_status
FROM contracts
WHERE status = 'active'
  AND end_date IS NOT NULL
ORDER BY end_date;

Step 11: Contract Value by Type

Analyze contract value by type.

SELECT
    contract_type,
    COUNT(*) as contract_count,
    SUM(total_value) as total_value,
    AVG(total_value) as avg_value
FROM contracts
WHERE status = 'active'
GROUP BY contract_type
ORDER BY total_value DESC;

Step 12: Risk Assessment

View contracts by risk level.

SELECT
    c.contract_number,
    c.title,
    c.risk_level,
    c.total_value,
    c.end_date,
    COUNT(co.id) as total_obligations,
    SUM(CASE WHEN co.is_critical THEN 1 ELSE 0 END) as critical_obligations
FROM contracts c
LEFT JOIN contract_obligations co ON c.id = co.contract_id
WHERE c.status = 'active'
GROUP BY c.id, c.contract_number, c.title, c.risk_level, c.total_value, c.end_date
ORDER BY
    CASE c.risk_level
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        ELSE 3
    END;

Cleanup (Optional)

DROP TABLE IF EXISTS contract_obligations;
DROP TABLE IF EXISTS contract_parties;
DROP TABLE IF EXISTS contracts;

Expected Outcomes

• Contracts tracked with metadata
• Parties linked to contracts
• Obligations monitored
• Renewals calendared
• Risk assessment enabled

Contract Types

Key Concepts Learned

• Contract lifecycle management
• Multi-party tracking
• Obligation management
• Renewal calendaring
• Risk categorization