Reference

Contract Data Model — Reference

The contract / agreement / spend data model with rationale for every decision — supplier resolution, document-vs-agreement split, obligation versioning, temporal modeling, spend integration. Plus 30+ SQL query patterns with discussion, fuzzy-join recipes, and schema-evolution playbooks.

How to use this

Two ways. Read end-to-end if you're designing a contract data layer from scratch — the chapters build a model piece by piece. Or use it as a query cookbook: skip to the patterns chapter and look up what you need. The chapter index above is the table of contents.

Section A · Core entities

00Start Here The model in one diagram, design principles, how to use this reference. 01Schema Overview All six core entities at a glance, the relationships between them, the design decisions that make this shape work at enterprise scale. Critical 02Suppliers & Entity Resolution The supplier table, parent hierarchies, canonical names, raw-vs-resolved storage. Resolution techniques: exact match, fuzzy, customer master, manual. 03Documents The physical-file table. document_id derivation from content hash, doc class taxonomy, the ingest+process timeline. 04Agreements The logical contract — many documents can roll up to one. MSA + SOWs + amendments. Status modeling, parent-agreement relationships.

Section B · Temporal & transactional

05Obligations & Temporal Modeling Obligations as extracted commitments, versioning across extractions, temporal modeling (status changes over time, slowly-changing-dimension patterns). Critical 06Spend & Invoices Linking invoice/PO data to agreements; the fuzzy match in practice; currency normalization; on-contract vs off-contract spend computation.

Section C · Query patterns

07SQL Pattern Library 30+ named SQL patterns: renewals due in N days, off-contract spend, supplier rollups, latest extraction per document, orphaned amendments, contract risk surfacing, more. Each with rationale. Critical

Section D · Schema evolution & quality

08Schema Evolution Recipes Backward-compatible migrations, the JSON-sidecar pattern, adding a doc class without breaking existing queries, two-phase column rename, customer-specific extension tables. 09Quality Tests & dbt Patterns dbt tests for the contract model — uniqueness on document_id, referential integrity, row-count thresholds, business-logic assertions (no agreement without parties). Materialization patterns for the marts layer.