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', status_changed_at = NOW()
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;