SQL & Contract Data Modeling

The contract data model

A working contract-intelligence data model has roughly six core entities. Different platforms name them differently; the shapes are the same.

-- Suppliers — canonical entities (after dedup / normalization)
suppliers (
  supplier_id, canonical_name, parent_supplier_id,
  country, tax_id, industry, first_seen_at
)

-- Documents — every ingested file
documents (
  document_id, customer_id, source_uri, file_hash,
  doc_class, ingested_at, page_count
)

-- Agreements — logical contracts. One agreement = many documents
-- (MSA + amendments + SOWs all roll up to one agreement)
agreements (
  agreement_id, customer_id, supplier_id,
  agreement_type, effective_date, current_term_end,
  parent_agreement_id, status
)

-- Document → agreement linkage (M:N)
document_agreement_links (
  document_id, agreement_id, role  -- master | amendment | sow | exhibit
)

-- Obligations — extracted commitments. One agreement → many obligations
obligations (
  obligation_id, agreement_id, obligation_type,
  party_obligated, value, currency, due_at, status,
  confidence, extracted_from_document_id, extracted_at
)

-- Spend — actuals from invoices / POs joined to agreements (sometimes)
spend (
  spend_id, customer_id, supplier_id, agreement_id,
  invoice_id, amount, currency, accrued_at,
  spend_category
)

Key modeling decisions:

• Document vs agreement. A document is the physical PDF; an agreement is the logical commitment that may be made of many documents. Most analytical questions are about agreements; debugging is at document level.
• Supplier canonicalization. "Acme Corp," "ACME Corporation," "Acme Corp Inc.," "Acme" might all be the same supplier. The canonical_name in the suppliers table is the resolved version; raw extractions point to the canonical via supplier_id.
• Parent-supplier hierarchy. Acme Cloud and Acme Datacenter are subsidiaries of Acme Holdings. Roll-up queries need the parent.
• Obligation status. "Pending," "fulfilled," "overdue," "waived." Status changes over time — sometimes need temporal modeling.

Entity resolution

The hardest data-modeling problem in contract intelligence. Two of your customer's contracts both reference "Acme Corp" — are those the same supplier? Probably. But maybe one is "Acme Corp (UK)" and the other is "Acme Corp (US)," which for tax purposes are distinct entities.

Techniques

• Exact match on tax_id (best when present), otherwise canonical_name. Resolves the easy 60%.
• Fuzzy match on name (Levenshtein, Jaro-Winkler, embedding similarity). Resolves another 25% with confidence.
• Customer-supplied mapping. The customer's procurement team often has an internal supplier master. Use it.
• Manual review. The hard residual goes to HITL or to the customer's data team.

The model

Don't merge silently. Store raw extracted names + resolved canonical IDs, with confidence. If a merge turns out to be wrong, you can re-resolve without re-extracting.

supplier_name_resolutions (
  raw_supplier_name,        -- what was in the document
  resolved_supplier_id,     -- canonical
  resolution_method,        -- exact_tax_id | fuzzy_name | customer_map | manual
  confidence,
  resolved_at,
  resolved_by               -- model_version or analyst_id
)

Obligations & renewals

The renewal query

"What's renewing in the next 90 days?" — the single most-asked question in contract intelligence.

SELECT
  a.agreement_id,
  a.supplier_id,
  s.canonical_name,
  a.current_term_end,
  a.current_term_end - CURRENT_DATE AS days_until_renewal,
  -- Find the renewal-clause obligation
  o.value AS renewal_notice_days,
  a.current_term_end - INTERVAL '1 day' * COALESCE(o.value::INT, 60) AS notice_deadline
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
LEFT JOIN obligations o
  ON o.agreement_id = a.agreement_id
 AND o.obligation_type = 'renewal_notice'
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE
                              AND CURRENT_DATE + INTERVAL '90 days'
ORDER BY days_until_renewal;

The senior touch: notice_deadline (when the customer has to give renewal notice) is often more actionable than the renewal date itself. Customers who miss the notice window are locked in for another term.

The auto-renew trap

Many agreements auto-renew unless cancelled. Your data model needs to capture:

• Auto-renew yes/no
• Renewal term length (often differs from initial term)
• Notice period required to cancel
• Whether notice has been given (often manually tracked elsewhere)

Spend rollups

"How much are we spending with Supplier X?" sounds simple. It is not.

The traps

• Supplier hierarchy. Roll up subsidiaries to the parent or not? Depends on the customer's procurement org.
• Currency. Multi-currency totals require FX rates at a specific date. Which date? Invoice date? Accrual date?
• Time alignment. Fiscal year vs calendar year. Customer's fiscal year might end in July.
• Off-contract spend. Spend on a supplier without a corresponding agreement — often the most interesting line item.
• Pass-through vs primary. A staffing agency invoices for contractors; is that "spend with the staffing agency" or "spend on contractor labor"?

The spend-by-supplier query

WITH supplier_hierarchy AS (
  -- Recursive: find the root parent for each supplier
  SELECT supplier_id, supplier_id AS root_id FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, h.root_id
  FROM suppliers s JOIN supplier_hierarchy h ON s.parent_supplier_id = h.supplier_id
),
spend_in_fy AS (
  SELECT
    sp.supplier_id,
    sp.amount * fx.rate_to_usd AS amount_usd
  FROM spend sp
  JOIN fx_rates fx
    ON fx.from_currency = sp.currency
   AND fx.rate_date = DATE_TRUNC('month', sp.accrued_at)
  WHERE sp.accrued_at >= '2025-07-01'  -- customer FY start
    AND sp.accrued_at <  '2026-07-01'
)
SELECT
  parent.canonical_name AS parent_supplier,
  SUM(sf.amount_usd) AS spend_usd_fy
FROM spend_in_fy sf
JOIN supplier_hierarchy h ON h.supplier_id = sf.supplier_id
JOIN suppliers parent ON parent.supplier_id = h.root_id
GROUP BY parent.canonical_name
ORDER BY spend_usd_fy DESC;

SQL patterns you'll write a hundred times

• "Latest extraction per document" — partition by document_id, order by extracted_at desc, take rank=1.
• "Documents that should belong to an agreement but don't" — anti-join, useful for finding orphaned amendments.
• "Agreements without a parent MSA" — SOWs floating without a master.
• "Obligations missing required fields" — quality check.
• "Spend with no matching agreement" — off-contract spend, often the customer's biggest discovery.
• "Agreements expiring in next N days, segmented by notice-window status" — the renewal pipeline.

Each of these is the kind of question a customer's procurement analyst will want to ask. Write them as dbt models with named columns; their analysts will reuse and extend them.

Fuzzy joins

Joining contracts (with extracted supplier names) to the customer's existing supplier master (cleaner names) is fuzzy by nature.

Approach

-- Requires pg_trgm extension
SELECT
  e.raw_supplier_name,
  s.canonical_name,
  similarity(e.raw_supplier_name, s.canonical_name) AS sim
FROM extracted_supplier_names e
CROSS JOIN LATERAL (
  SELECT canonical_name
  FROM suppliers
  ORDER BY canonical_name % e.raw_supplier_name
  LIMIT 5
) s
WHERE similarity(e.raw_supplier_name, s.canonical_name) > 0.65;

At BigQuery / Snowflake scale, prefer pre-computed embeddings + vector similarity, or LLM-as-judge on a candidate set generated by trigram or n-gram.

Interview probes