Section C · Query patterns

SQL Pattern Library

30+ named SQL patterns against the contract data model. Each with a rationale, a query, and the gotchas worth knowing.

How to read this

Patterns are grouped by entity. Each entry has:

What it answers — the business question.
The query — copy-paste ready (Postgres dialect; trivially portable).
Notes — gotchas, dialect variations, when to materialize as a mart.

These patterns assume the schema from chapter 01. Substitute table names as needed for your variant.

A · Agreements & renewals

A1. Renewals due in next N days

Answers: which agreements end soon enough to warrant action.

SELECT
  a.agreement_id, s.canonical_name AS supplier,
  a.current_term_end,
  a.current_term_end - CURRENT_DATE AS days_until_renewal
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.customer_id = 'cust_acme'
  AND a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE
                              AND CURRENT_DATE + INTERVAL '90 days'
ORDER BY days_until_renewal;

A2. Renewals with notice deadlines

Answers: when does the customer need to give notice to cancel.

WITH renewal_obligations AS (
  SELECT
    agreement_id,
    (value->>'notice_period_days')::INT AS notice_days,
    (value->>'auto_renew')::BOOLEAN AS auto_renew
  FROM obligations
  WHERE obligation_type = 'renewal_notice' AND status != 'superseded'
)
SELECT
  a.agreement_id, s.canonical_name,
  a.current_term_end,
  ro.notice_days,
  a.current_term_end - INTERVAL '1 day' * COALESCE(ro.notice_days, 60) AS notice_deadline,
  ro.auto_renew
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
LEFT JOIN renewal_obligations ro ON ro.agreement_id = a.agreement_id
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '120 days'
ORDER BY notice_deadline;

A3. Auto-renew agreements about to renew silently

SELECT a.agreement_id, s.canonical_name, a.current_term_end
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
JOIN obligations o
  ON o.agreement_id = a.agreement_id
 AND o.obligation_type = 'renewal_notice'
 AND (o.value->>'auto_renew')::BOOLEAN = true
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

A4. Latest amendment per agreement

SELECT a.agreement_id,
       MAX(d.ingested_at) AS latest_amendment_at
FROM agreements a
JOIN document_agreement_links dal ON dal.agreement_id = a.agreement_id
JOIN documents d ON d.document_id = dal.document_id
WHERE dal.role = 'amendment'
GROUP BY a.agreement_id;

A5. Agreements expiring without SOWs

SELECT a.agreement_id, s.canonical_name, a.current_term_end
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.current_term_end <= CURRENT_DATE + INTERVAL '60 days'
  AND a.status = 'active'
  AND NOT EXISTS (
    SELECT 1 FROM document_agreement_links dal
    WHERE dal.agreement_id = a.agreement_id AND dal.role = 'sow'
  );

B · Documents & provenance

B1. Latest extraction per document

SELECT DISTINCT ON (e.document_id)
  e.extraction_id, e.document_id, e.model_version, e.extracted_at
FROM extractions e
ORDER BY e.document_id, e.extracted_at DESC, e.model_version DESC;

B2. Orphaned amendments (no master MSA)

SELECT d.document_id, d.source_uri, d.ingested_at
FROM documents d
WHERE d.doc_class = 'amendment'
  AND NOT EXISTS (
    SELECT 1 FROM document_agreement_links dal
    WHERE dal.document_id = d.document_id
  );

B3. Documents not yet extracted with current model

SELECT d.document_id, d.doc_class, d.ingested_at
FROM documents d
LEFT JOIN extractions e
  ON e.document_id = d.document_id
 AND e.model_version = 'extract-v4.2.1'
WHERE e.extraction_id IS NULL
  AND d.customer_id = 'cust_acme'
ORDER BY d.ingested_at;

B4. Documents with confidence below threshold

SELECT e.document_id, e.fields->'effective_date'->>'confidence' AS conf,
       d.doc_class
FROM extractions e
JOIN documents d ON d.document_id = e.document_id
WHERE e.model_version = 'extract-v4.2.1'
  AND (e.fields->'effective_date'->>'confidence')::FLOAT < 0.85
ORDER BY conf;

B5. Full document trail for an agreement

SELECT dal.role, d.doc_class, d.document_id, d.ingested_at, d.source_uri
FROM document_agreement_links dal
JOIN documents d ON d.document_id = dal.document_id
WHERE dal.agreement_id = 'agr_acme_msa_2020'
ORDER BY d.ingested_at;

C · Obligations

C1. Current obligations (latest extraction per agreement+type)

SELECT DISTINCT ON (o.agreement_id, o.obligation_type)
  o.*
FROM obligations o
WHERE o.status != 'superseded'
ORDER BY o.agreement_id, o.obligation_type,
         o.extracted_at DESC, o.model_version DESC;

C2. Upcoming due obligations

SELECT a.agreement_id, s.canonical_name, o.obligation_type, o.value, o.due_at,
       o.due_at - CURRENT_DATE AS days_until_due
FROM obligations o
JOIN agreements a ON a.agreement_id = o.agreement_id
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE o.status = 'pending'
  AND o.due_at BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '60 days'
ORDER BY o.due_at;

C3. Obligations missing required fields

Quality check: payment obligations should have amount and due_at.

SELECT o.obligation_id, o.agreement_id, o.value
FROM obligations o
WHERE o.obligation_type = 'payment'
  AND (o.value IS NULL
       OR o.value->>'amount' IS NULL
       OR o.due_at IS NULL);

C4. Agreements without an indemnification clause

SELECT a.agreement_id, s.canonical_name
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.agreement_type = 'master_service_agreement'
  AND NOT EXISTS (
    SELECT 1 FROM obligations o
    WHERE o.agreement_id = a.agreement_id
      AND o.obligation_type = 'indemnification'
      AND o.status != 'superseded'
  );

C5. Liability caps below a threshold

SELECT a.agreement_id, s.canonical_name,
       (o.value->>'cap_amount')::NUMERIC AS cap_amount
FROM obligations o
JOIN agreements a ON a.agreement_id = o.agreement_id
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE o.obligation_type = 'liability_cap'
  AND o.status != 'superseded'
  AND (o.value->>'cap_amount')::NUMERIC < 100000;

D · Suppliers & rollups

D1. Supplier hierarchy (root parent per supplier)

WITH RECURSIVE chain AS (
  SELECT supplier_id, supplier_id AS root_id, 0 AS depth
  FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, c.root_id, c.depth + 1
  FROM suppliers s JOIN chain c ON c.supplier_id = s.parent_supplier_id
)
SELECT supplier_id, root_id, depth FROM chain;

D2. Supplier consolidation candidates (similar names)

Suppliers within trigram similarity threshold of each other; surfaced for human review.

SELECT s1.supplier_id AS a_id, s1.canonical_name AS a_name,
       s2.supplier_id AS b_id, s2.canonical_name AS b_name,
       similarity(s1.canonical_name, s2.canonical_name) AS sim
FROM suppliers s1
JOIN suppliers s2
  ON s1.customer_id = s2.customer_id
 AND s1.supplier_id < s2.supplier_id
 AND s1.canonical_name % s2.canonical_name  -- pg_trgm operator
WHERE similarity(s1.canonical_name, s2.canonical_name) > 0.75
ORDER BY sim DESC;

D3. Spend per supplier (rolled up to parent)

See chapter 06 §operations.

D4. Suppliers with no spend but active agreements

SELECT s.supplier_id, s.canonical_name, a.agreement_id, a.effective_date
FROM suppliers s
JOIN agreements a ON a.supplier_id = s.supplier_id AND a.status = 'active'
WHERE NOT EXISTS (
  SELECT 1 FROM spend sp
  WHERE sp.supplier_id = s.supplier_id
    AND sp.accrued_at >= a.effective_date
);

E · Spend

E1. Off-contract spend per supplier

SELECT s.supplier_id, sp.canonical_name,
       SUM(s.amount) AS off_contract_total,
       s.currency
FROM spend s
JOIN suppliers sp ON sp.supplier_id = s.supplier_id
LEFT JOIN agreements a
  ON a.supplier_id = s.supplier_id
 AND a.status = 'active'
 AND s.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
WHERE a.agreement_id IS NULL
  AND s.customer_id = 'cust_acme'
GROUP BY s.supplier_id, sp.canonical_name, s.currency
ORDER BY off_contract_total DESC;

E2. Off-contract subcategorized

Distinguish "no agreement ever" from "agreement expired."

WITH off_contract AS (
  SELECT s.*
  FROM spend s
  LEFT JOIN agreements a
    ON a.supplier_id = s.supplier_id
   AND a.status = 'active'
   AND s.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
  WHERE a.agreement_id IS NULL
)
SELECT
  oc.supplier_id,
  CASE
    WHEN EXISTS (SELECT 1 FROM agreements a2 WHERE a2.supplier_id = oc.supplier_id) THEN 'expired_agreement'
    ELSE 'no_agreement'
  END AS subcategory,
  SUM(oc.amount) AS total
FROM off_contract oc
GROUP BY oc.supplier_id, subcategory;

E3. Spend trend by month

SELECT
  DATE_TRUNC('month', s.accrued_at) AS month,
  SUM(s.amount) AS total_spend
FROM spend s
WHERE s.customer_id = 'cust_acme'
  AND s.currency = 'USD'
GROUP BY 1
ORDER BY 1;

E4. Top suppliers by spend in FY

SELECT s.canonical_name, SUM(sp.amount) AS fy_spend
FROM spend sp
JOIN suppliers s ON s.supplier_id = sp.supplier_id
WHERE sp.customer_id = 'cust_acme'
  AND sp.accrued_at >= '2025-07-01' AND sp.accrued_at < '2026-07-01'
GROUP BY s.canonical_name
ORDER BY fy_spend DESC
LIMIT 20;

F · Quality & auditability

F1. Extraction confidence distribution

Reliability-diagram input.

SELECT
  WIDTH_BUCKET((fields->'effective_date'->>'confidence')::FLOAT, 0, 1, 10) AS bucket,
  COUNT(*) AS n
FROM extractions
WHERE model_version = 'extract-v4.2.1'
GROUP BY bucket
ORDER BY bucket;

F2. Documents where extraction differed between two model versions

SELECT e1.document_id,
       e1.fields->'effective_date'->>'value' AS v_old,
       e2.fields->'effective_date'->>'value' AS v_new
FROM extractions e1
JOIN extractions e2
  ON e1.document_id = e2.document_id
WHERE e1.model_version = 'extract-v4.1.0'
  AND e2.model_version = 'extract-v4.2.1'
  AND COALESCE(e1.fields->'effective_date'->>'value', '') !=
      COALESCE(e2.fields->'effective_date'->>'value', '');

F3. Audit trail for a supplier

SELECT raw_supplier_name, canonical_name, resolution_method, confidence, resolved_at
FROM supplier_name_resolutions snr
JOIN suppliers s ON s.supplier_id = snr.resolved_supplier_id
WHERE snr.customer_id = 'cust_acme'
  AND s.canonical_name = 'Acme Corp'
ORDER BY snr.resolved_at DESC;

F4. dbt test patterns

See chapter 09 for the test taxonomy.