Obligations & Temporal Modeling

The obligations table

CREATE TABLE obligations (
  obligation_id TEXT PRIMARY KEY,
  agreement_id TEXT NOT NULL REFERENCES agreements,
  obligation_type TEXT NOT NULL,
  party_obligated TEXT,
  value JSONB,
  currency TEXT,
  due_at DATE,
  status TEXT NOT NULL DEFAULT 'pending',
  confidence FLOAT,
  extracted_from_document_id TEXT REFERENCES documents,
  model_version TEXT NOT NULL,
  extracted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Field rationale

• obligation_id: stable identifier. Often agreement_id + obligation_type + model_version hashed.
• agreement_id: which agreement this obligation belongs to.
• obligation_type: see types list below. Drives which fields in value are populated.
• party_obligated: who has to do this — customer, supplier, both. Sometimes null for mutual obligations.
• value: JSONB. Shape varies by obligation_type.
• currency: when obligation involves money. Extracted alongside the amount in value.
• due_at: when this obligation comes due. Null for ongoing obligations (e.g., confidentiality).
• status: pending / fulfilled / overdue / waived / superseded.
• confidence: extraction confidence per obligation.
• extracted_from_document_id: which document produced this obligation. Provenance.
• model_version: which extraction model produced it. Versioning.

Obligation types

The taxonomy of obligation kinds determines the shape of value. A working starter set:

The list isn't exhaustive. Production platforms add types as customers ask for them. The taxonomy lives in a config file or table; extending it doesn't require schema migration.

Versioning across extractions

When a new extraction model version processes a document, the obligations it finds are new rows, not updates to existing rows.

Why insert, not update

• Audit: customer can ask "what did the system say about this obligation 6 months ago?" — the historical row is still there.
• Diffing: "what changed when we upgraded the model?" — compare model_version X obligations to model_version Y obligations for the same agreement.
• Rollback: revert to a previous model version without losing the new extractions; mart picks up the older version.
• Calibration: HITL corrections on old rows produce labeled data without altering the original extraction record.

The shape

For agreement A and obligation type renewal_notice, you might have:

• One row from model_version v3.1 (extracted on 2026-01-15).
• One row from model_version v4.0 (extracted on 2026-04-10).
• One row from model_version v4.2.1 (extracted on 2026-05-20).

Three rows in obligations; one logical obligation. The "current" view (next section) picks the latest version.

Temporal modeling

Some obligations have temporal aspects beyond just versioning: status changes over time, term changes via amendments.

Status changes

A payment obligation moves from pending → fulfilled when the customer's accounting confirms payment. An audit log captures the transition:

CREATE TABLE obligation_status_changes (
  change_id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES obligations,
  from_status TEXT,
  to_status TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changed_by TEXT,
  evidence TEXT  -- e.g., link to invoice or payment record
);

Amendments that supersede obligations

An amendment changes the renewal term. The old obligation (from the original MSA) gets status = superseded; a new obligation is inserted from the amendment.

Both rows reference the same agreement_id. The "current" view shows only non-superseded.

SCD-2 alternative

Some platforms use Slowly Changing Dimension Type 2 patterns directly on obligations — row-effective-from / row-effective-to columns; updates close the old row and insert a new one. More query overhead; cleaner for some temporal-audit workflows. Mart-layer SCD-2 (chapter 09) is more common.

Querying the "current" view

The canonical view downstream consumers want: for each (agreement, obligation_type), the latest non-superseded obligation.

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY o.agreement_id, o.obligation_type
      ORDER BY o.extracted_at DESC, o.model_version DESC
    ) AS rn
  FROM obligations o
  WHERE o.status != 'superseded'
)
SELECT
  agreement_id, obligation_type, party_obligated,
  value, currency, due_at, status, confidence,
  extracted_from_document_id, model_version, extracted_at
FROM ranked
WHERE rn = 1;

This view is what dbt mart models typically materialize. The marts table becomes the source for the customer's BI dashboards.

Why this is not just MAX(extracted_at) per group

You also want to consider model_version because:

• Two extractions could have the same extracted_at if a batch reprocessed many at once.
• Ordering by model_version (lexicographically: v4.2.1 > v3.1 > v2) gives a deterministic tie-break.

Common operations

Insert obligations from an extraction

def insert_obligations(agreement_id: str, extraction: dict, document_id: str, model_version: str):
    """Each obligation extracted from a document becomes a new row, keyed by model_version."""
    for ob_type, ob_data in extraction.get("obligations", {}).items():
        obligation_id = f"ob_{agreement_id}_{ob_type}_{model_version}"
        db.execute("""
            INSERT INTO obligations (obligation_id, agreement_id, obligation_type,
                                     party_obligated, value, currency, due_at,
                                     status, confidence, extracted_from_document_id,
                                     model_version)
            VALUES (%(id)s, %(agr)s, %(type)s, %(party)s, %(val)s, %(cur)s,
                    %(due)s, 'pending', %(conf)s, %(doc)s, %(ver)s)
            ON CONFLICT (obligation_id) DO UPDATE SET
                value = EXCLUDED.value,
                confidence = EXCLUDED.confidence,
                extracted_at = NOW()
        """, {...})

Mark an obligation fulfilled

UPDATE obligations SET status = 'fulfilled'
WHERE obligation_id = 'ob_acme_msa_payment_q1_v4.2.1';

INSERT INTO obligation_status_changes (change_id, obligation_id, from_status, to_status, changed_by, evidence)
VALUES ('chg_uuid', 'ob_acme_msa_payment_q1_v4.2.1', 'pending', 'fulfilled',
        'customer_ar_system', 'invoice INV-2026-0142 paid 2026-04-30');

Supersede an obligation when an amendment changes it

-- Mark old obligations from this agreement, of this type, as superseded
UPDATE obligations
SET status = 'superseded'
WHERE agreement_id = 'agr_acme_msa_2020'
  AND obligation_type = 'renewal_notice'
  AND status != 'superseded'
  AND obligation_id != 'ob_new_from_amendment';
-- Then the new obligation row from the amendment is the "current" one.

Compute upcoming due obligations

SELECT a.agreement_id, s.canonical_name AS supplier,
       o.obligation_type, o.value, o.due_at,
       o.due_at - CURRENT_DATE AS days_until_due
FROM current_obligations o  -- the view from earlier
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 '90 days'
ORDER BY o.due_at;

✓ Check yourself

Answer from memory before opening each one.

1. Model v4.0 and v4.2.1 both extract a renewal notice for one agreement. Why keep two rows instead of updating the first, and what does that choice cost you?

   Show answer

   Separate rows keep an audit trail, let you diff two model versions, and let you roll back by reading the older version. The cost is that every consumer needs a current view that picks one row per agreement and type. See versioning across extractions.

2. The current view orders by extracted_at and then by model_version. Why the second key, and what is risky about it?

   Show answer

   A batch reprocess can give many rows the same timestamp, so the second key makes the pick deterministic. Version strings sort as text, so v10 sorts before v9. Store a numeric release or rank column if versions can reach two digits. See the current view.

3. An amendment extends the renewal term, so the old renewal row is marked superseded. Later a newer model re-extracts the original MSA. What does the current view show, and how would you fix it?

   Show answer

   The new row from the re-extraction has status pending and the latest timestamp, so it wins and the view shows the pre-amendment term. Supersession was set on one row, not on the pair of agreement and type. Rank by the effective date of the source document, or apply supersession again after every extraction. See amendments that supersede obligations.

4. Compare an audit table for status changes with versioned rows for obligations. When would you pick the second?

   Show answer

   The audit table is cheaper and answers who changed what and when. Versioned rows suit workloads that constantly ask what was true on a given date across many obligations, since the filter is a plain range. Many teams start with the audit table and keep versioned rows in the mart layer. See temporal modeling.

5. The insert code builds obligation_id from agreement, type and model version, and updates on conflict. What happens when the same model reruns, and when a new model runs?

   Show answer

   The same model hits the same ID, so the row is updated in place and the rerun is idempotent. A new model gets a new ID and adds a row, which preserves history. See insert obligations from an extraction.

Takeaway

• Each extraction run inserts new obligation rows, so history, diffs and rollback come for free.
• The current view picks one row per agreement and obligation type, ranked by extraction time with a tie-break.
• Version strings sort as text, so use a numeric rank once versions pass nine.
• Marking one row superseded is not enough when a later run re-extracts the same source, so rank by source effective date or reapply supersession.
• Status changes go to an audit table with their evidence, and the obligation row carries the latest status.
• Deterministic obligation IDs make the same model rerun idempotent and a new model additive.

Spend is the other half of the picture, and it links back to these agreements. → Spend and Invoices

Further reading

• PostgreSQL: JSON types — PostgreSQL documentation. How the JSONB value column is stored and indexed.
• PostgreSQL: JSON functions and operators — PostgreSQL documentation. The path and cast operators the obligation queries rely on.
• PostgreSQL: Window functions — PostgreSQL documentation. How ROW_NUMBER over a partition builds the current view.
• Slowly Changing Dimension Type 2 — Kimball Group. The versioned-row alternative to the audit table.
• Bitemporal History — Martin Fowler, 2021. Valid time against record time, the distinction behind amendments and re-extractions.