Contract Management System: Data Model, Alert Engine, and Workflow Design

CREATE TABLE contracts (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_no           VARCHAR(50) UNIQUE NOT NULL,  -- e.g., "CON-2026-001234"
  title                 VARCHAR(300) NOT NULL,
  contract_type         VARCHAR(30) NOT NULL CHECK (contract_type IN (
                          'vendor','customer','lease','employment','nda',
                          'partnership','loan','insurance','other'
                        )),
  
  -- Parties
  counterparty_name     VARCHAR(200) NOT NULL,
  counterparty_gstin    VARCHAR(20),
  counterparty_pan      VARCHAR(20),
  counterparty_type     VARCHAR(20) CHECK (counterparty_type IN (
                          'vendor','customer','landlord','employee','bank','government','other'
                        )),
  
  -- Ownership
  owner_id              UUID NOT NULL,  -- FK → users
  department            VARCHAR(50),
  company_entity_id     UUID,  -- for multi-entity companies
  
  -- Status
  status                VARCHAR(20) DEFAULT 'draft' CHECK (status IN (
                          'draft','pending_approval','active','expired',
                          'terminated','renewed','suspended'
                        )),
  
  -- Key dates
  execution_date        DATE,
  effective_date        DATE NOT NULL,
  expiry_date           DATE,
  
  -- Renewal configuration
  renewal_type          VARCHAR(20) DEFAULT 'manual' CHECK (renewal_type IN (
                          'manual','auto','evergreen','none'
                        )),
  renewal_notice_days   INTEGER DEFAULT 30,
  renewal_term_months   INTEGER,
  
  -- Financial
  contract_value        DECIMAL(15,2),
  annual_value          DECIMAL(15,2),
  currency              CHAR(3) DEFAULT 'INR',
  payment_terms         VARCHAR(100),
  
  -- Metadata
  tags                  TEXT[],
  notes                 TEXT,
  created_at            TIMESTAMPTZ DEFAULT now(),
  updated_at            TIMESTAMPTZ DEFAULT now(),
  created_by            UUID NOT NULL
);

CREATE INDEX idx_contracts_owner ON contracts(owner_id);
CREATE INDEX idx_contracts_status ON contracts(status);
CREATE INDEX idx_contracts_expiry ON contracts(expiry_date) WHERE status = 'active';
CREATE INDEX idx_contracts_counterparty ON contracts(counterparty_name);

CREATE TABLE contract_clauses (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id     UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
  clause_type     VARCHAR(50) NOT NULL CHECK (clause_type IN (
                    'termination','liability_cap','exclusivity','price_escalation',
                    'penalty','sla','confidentiality','ip_ownership','governing_law','other'
                  )),
  title           VARCHAR(200) NOT NULL,
  description     TEXT NOT NULL,
  value           DECIMAL(15,2),  -- for monetary clauses (liability cap, penalty)
  effective_date  DATE,
  expiry_date     DATE,
  is_critical     BOOLEAN DEFAULT false,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE contract_obligations (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id     UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
  title           VARCHAR(200) NOT NULL,
  description     TEXT,
  obligation_type VARCHAR(30) CHECK (obligation_type IN (
                    'report','payment','audit','review','renewal','insurance',
                    'compliance','delivery','other'
                  )),
  owner_id        UUID NOT NULL,  -- FK → users
  
  -- Recurrence
  is_recurring    BOOLEAN DEFAULT false,
  recurrence_type VARCHAR(20) CHECK (recurrence_type IN ('daily','weekly','monthly','quarterly','annual','custom')),
  recurrence_day  INTEGER,  -- day of month for monthly, day of year for annual
  
  -- Next due
  next_due_date   DATE NOT NULL,
  
  -- Status
  status          VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','completed','overdue','waived')),
  completed_at    TIMESTAMPTZ,
  completed_by    UUID,
  notes           TEXT,
  
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_obligations_due ON contract_obligations(next_due_date) WHERE status = 'pending';

CREATE TABLE contract_amendments (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id     UUID NOT NULL REFERENCES contracts(id),
  amendment_no    INTEGER NOT NULL,
  title           VARCHAR(200) NOT NULL,
  description     TEXT,
  effective_date  DATE NOT NULL,
  
  -- What changed
  changes         JSONB,  -- structured diff of changed fields
  
  -- Document
  document_id     UUID,  -- FK → documents
  
  executed_by     UUID NOT NULL,
  executed_at     TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE renewal_decisions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id     UUID NOT NULL REFERENCES contracts(id),
  decision        VARCHAR(20) NOT NULL CHECK (decision IN (
                    'renew_as_is','renew_with_negotiation','terminate','escalate','pending'
                  )),
  decided_by      UUID NOT NULL,
  decided_at      TIMESTAMPTZ DEFAULT now(),
  
  -- Negotiation objectives (if renew_with_negotiation)
  negotiation_objectives TEXT,
  target_price_change    DECIMAL(5,2),  -- percentage change target
  
  -- Termination (if terminate)
  termination_reason     TEXT,
  termination_notice_sent_at TIMESTAMPTZ,
  
  notes           TEXT
);

-- View: contracts requiring alerts
CREATE VIEW contract_alert_schedule AS
SELECT
  c.id AS contract_id,
  c.contract_no,
  c.title,
  c.expiry_date,
  c.renewal_type,
  c.renewal_notice_days,
  c.annual_value,
  c.owner_id,
  u.email AS owner_email,
  u.manager_id,
  
  -- Termination notice deadline
  (c.expiry_date - c.renewal_notice_days) AS termination_deadline,
  
  -- Days until termination deadline
  ((c.expiry_date - c.renewal_notice_days) - CURRENT_DATE) AS days_to_deadline,
  
  -- Alert level
  CASE
    WHEN ((c.expiry_date - c.renewal_notice_days) - CURRENT_DATE) <= 0 THEN 'CRITICAL'
    WHEN ((c.expiry_date - c.renewal_notice_days) - CURRENT_DATE) <= 7 THEN 'URGENT'
    WHEN ((c.expiry_date - c.renewal_notice_days) - CURRENT_DATE) <= 30 THEN 'WARNING'
    WHEN ((c.expiry_date - c.renewal_notice_days) - CURRENT_DATE) <= 60 THEN 'NOTICE'
    ELSE 'UPCOMING'
  END AS alert_level,
  
  -- Has a renewal decision been made?
  rd.decision AS renewal_decision
  
FROM contracts c
JOIN users u ON u.id = c.owner_id
LEFT JOIN renewal_decisions rd ON rd.contract_id = c.id
  AND rd.decided_at = (SELECT MAX(decided_at) FROM renewal_decisions WHERE contract_id = c.id)
WHERE c.status = 'active'
  AND c.expiry_date IS NOT NULL
  AND c.renewal_type != 'none'
  AND (c.expiry_date - c.renewal_notice_days) - CURRENT_DATE <= 120;

async function processContractAlerts() {
  const alerts = await db.query(`
    SELECT * FROM contract_alert_schedule
    WHERE renewal_decision IS NULL OR renewal_decision = 'pending'
    ORDER BY days_to_deadline ASC
  `);

  for (const alert of alerts.rows) {
    // Check if alert already sent today
    const alreadySent = await db.query(`
      SELECT id FROM contract_alert_log
      WHERE contract_id = $1
        AND alert_level = $2
        AND sent_at > NOW() - INTERVAL '20 hours'
    `, [alert.contract_id, alert.alert_level]);

    if (alreadySent.rows.length > 0) continue;

    // Determine recipients based on alert level
    const recipients = [alert.owner_email];
    if (['CRITICAL', 'URGENT'].includes(alert.alert_level)) {
      const manager = await getManagerEmail(alert.manager_id);
      if (manager) recipients.push(manager);
    }
    if (alert.alert_level === 'CRITICAL') {
      const deptHead = await getDeptHeadEmail(alert.contract_id);
      if (deptHead) recipients.push(deptHead);
    }

    // Send alert
    await sendContractAlert({
      recipients,
      contract: alert,
      alertLevel: alert.alert_level,
      actionUrl: `${process.env.APP_URL}/contracts/${alert.contract_id}/renewal`
    });

    // Log alert
    await db.query(`
      INSERT INTO contract_alert_log (contract_id, alert_level, recipients, sent_at)
      VALUES ($1, $2, $3, NOW())
    `, [alert.contract_id, alert.alert_level, JSON.stringify(recipients)]);
  }
}

-- Total value of contracts that will auto-renew in next 12 months
SELECT
  DATE_TRUNC('month', expiry_date) AS renewal_month,
  COUNT(*) AS contract_count,
  SUM(annual_value) AS total_annual_value,
  SUM(contract_value) AS total_contract_value
FROM contracts
WHERE status = 'active'
  AND renewal_type = 'auto'
  AND expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', expiry_date)
ORDER BY renewal_month;

SELECT
  counterparty_type,
  COUNT(*) AS contract_count,
  SUM(annual_value) AS total_annual_spend,
  AVG(annual_value) AS avg_contract_value
FROM contracts
WHERE status = 'active'
  AND contract_type = 'vendor'
GROUP BY counterparty_type
ORDER BY total_annual_spend DESC;

SELECT
  co.id,
  co.title,
  c.contract_no,
  c.title AS contract_title,
  c.counterparty_name,
  co.next_due_date,
  CURRENT_DATE - co.next_due_date AS days_overdue,
  u.name AS owner_name,
  u.email AS owner_email
FROM contract_obligations co
JOIN contracts c ON c.id = co.contract_id
JOIN users u ON u.id = co.owner_id
WHERE co.status = 'pending'
  AND co.next_due_date < CURRENT_DATE
ORDER BY days_overdue DESC;

CREATE TABLE documents (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id     UUID REFERENCES contracts(id),
  document_type   VARCHAR(30) CHECK (document_type IN (
                    'original','amendment','termination_notice','renewal',
                    'correspondence','supporting'
                  )),
  filename        VARCHAR(300) NOT NULL,
  storage_path    VARCHAR(500) NOT NULL,  -- S3 key or file path
  file_size       INTEGER,
  mime_type       VARCHAR(100),
  checksum        VARCHAR(64),  -- SHA-256 for integrity verification
  uploaded_by     UUID NOT NULL,
  uploaded_at     TIMESTAMPTZ DEFAULT now(),
  is_signed       BOOLEAN DEFAULT false,
  signed_at       TIMESTAMPTZ
);

// Generate pre-signed URL for document access (S3)
async function getDocumentUrl(documentId, userId) {
  // Verify user has access to this document's contract
  const doc = await db.query(`
    SELECT d.*, c.owner_id, c.department
    FROM documents d
    JOIN contracts c ON c.id = d.contract_id
    WHERE d.id = $1
  `, [documentId]);

  if (!doc.rows[0]) throw new Error('Document not found');

  const hasAccess = await checkContractAccess(userId, doc.rows[0].contract_id);
  if (!hasAccess) throw new Error('Access denied');

  // Generate pre-signed URL (expires in 15 minutes)
  const url = await s3.getSignedUrlPromise('getObject', {
    Bucket: process.env.CONTRACTS_BUCKET,
    Key: doc.rows[0].storage_path,
    Expires: 900
  });

  // Log access
  await db.query(`
    INSERT INTO document_access_log (document_id, user_id, accessed_at)
    VALUES ($1, $2, NOW())
  `, [documentId, userId]);

  return url;
}

GET    /api/v1/contracts?status=&type=&owner=&expiring_in_days=
POST   /api/v1/contracts
GET    /api/v1/contracts/:id
PATCH  /api/v1/contracts/:id
GET    /api/v1/contracts/:id/clauses
POST   /api/v1/contracts/:id/clauses
GET    /api/v1/contracts/:id/obligations
POST   /api/v1/contracts/:id/obligations
GET    /api/v1/contracts/:id/amendments
POST   /api/v1/contracts/:id/amendments
POST   /api/v1/contracts/:id/renewal-decision
GET    /api/v1/contracts/:id/documents
POST   /api/v1/contracts/:id/documents
GET    /api/v1/contracts/:id/documents/:docId/url
GET    /api/v1/analytics/renewal-exposure
GET    /api/v1/analytics/spend-by-category
GET    /api/v1/alerts/overdue-obligations