Agreements

The agreements table

CREATE TABLE agreements (
  agreement_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  supplier_id TEXT NOT NULL REFERENCES suppliers,
  agreement_type TEXT NOT NULL,
  effective_date DATE,
  current_term_end DATE,
  parent_agreement_id TEXT REFERENCES agreements,
  status TEXT NOT NULL DEFAULT 'active',
  status_changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE document_agreement_links (
  document_id TEXT NOT NULL REFERENCES documents,
  agreement_id TEXT NOT NULL REFERENCES agreements,
  role TEXT NOT NULL,  -- master | amendment | sow | exhibit
  PRIMARY KEY (document_id, agreement_id)
);

Field rationale

• agreement_id: platform-assigned. Unlike document_id, this isn't content-derived because the agreement is a logical entity assembled from multiple documents.
• supplier_id: the counterparty. Most queries on agreements join through this.
• agreement_type: msa, sow, supplier_agreement, dpa, baa, etc. Often matches a single document's doc_class, but not always.
• effective_date: when the agreement took effect. Could be earlier than the latest amendment's signed_at.
• current_term_end: when the current term ends. Updated when amendments extend or change the term.
• parent_agreement_id: when this agreement modifies or hangs under another (rare; usually used for partner / reseller chains rather than amendment relationships, which are captured via document_agreement_links).
• status: active / expired / terminated / superseded / draft.

Documents vs agreements

The clearest example: a customer-supplier MSA from 2020, amended twice (2021 and 2023), with five SOWs underneath. That's:

• 1 agreement — the customer-supplier relationship under that MSA.
• 8 documents — original MSA + 2 amendments + 5 SOWs.

Most analytical questions ("what's the customer's current obligation under their MSA with Acme?") want the agreement-level view, with the current term and renewal terms computed from the most recent amendments.

Most debugging questions ("why does the extracted renewal date look wrong?") need document-level granularity.

Document-agreement links

The M:N table document_agreement_links ties documents to agreements with a role.

Roles

How linkages are determined

• Source-system parentage: if the CLM tracks "this amendment is attached to MSA X," use that directly.
• Title and reference matching: amendments often say "amends the MSA between Customer and Supplier dated March 15, 2024" — match on supplier + date.
• Manual linkage: customer's data team or reviewer assigns the link when automation can't.

Orphaned amendments

Sometimes you have an amendment whose master MSA isn't in the corpus. Don't drop it; insert into documents with doc_class = amendment but no link to an agreement until the master surfaces (or until the customer confirms the master is missing). See chapter 07 for the "orphaned amendments" query pattern.

Parent-child agreements

The parent_agreement_id column handles cases where one agreement legally hangs under another. Less common than document-to-agreement linkage, but real:

• A reseller agreement under a master partner agreement.
• A region-specific localization of a global MSA.
• A subsidiary's agreement under a parent-company master contract.

When to use what

• Amendment to an MSA: linked via document_agreement_links (the amendment document is linked to the MSA agreement; same agreement_id).
• Separate agreement modifying terms of another: parent_agreement_id (two distinct agreement rows; one references the other).

Amendments don't get their own agreement_id — they modify an existing agreement. Sub-agreements (e.g., regional contracts) do get their own agreement_id with parent reference.

Status modeling

Agreement status changes over time. The minimal status set:

• draft — not yet signed (rare in this schema, since we usually treat signed documents as input).
• active — currently in effect.
• expired — passed its current_term_end without renewal.
• terminated — ended early by one party.
• superseded — replaced by a newer agreement (common when companies renegotiate the entire MSA rather than amending).

Temporal status modeling

For audit, you sometimes need to know an agreement's status at a historical point. Two patterns:

Pattern 1: Current-only with audit log

The agreements.status column shows current status. A separate agreement_status_changes table logs every change.

CREATE TABLE agreement_status_changes (
  change_id TEXT PRIMARY KEY,
  agreement_id TEXT NOT NULL REFERENCES agreements,
  from_status TEXT,
  to_status TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changed_by TEXT,  -- model_version | analyst_id | system
  reason TEXT
);

Pattern 2: SCD-2 directly on agreements

Add row-effective-from and row-effective-to columns; new status creates a new row. More query overhead; cleaner for some audit workflows. Pattern 1 is more common.

Computing status

Status can be computed in some cases:

• active if current_term_end is in the future and status is not terminated/superseded.
• expired if current_term_end has passed and no renewal has been processed.

Many platforms run a daily job that promotes active → expired when term ends pass. Avoid relying on the stored status if your data isn't auto-updated; compute in queries against current_term_end and last_amendment_date.

Common operations

Create an agreement from extracted documents

When a new MSA is ingested:

1. Extract supplier identity (parties extraction); resolve to supplier_id.
2. Extract effective_date and term details.
3. Create the agreement row.
4. Link the document via document_agreement_links with role='master'.

def create_agreement(extraction: dict, document_id: str, customer_id: str) -> str:
    supplier_id = resolve_supplier(extraction["parties"][1], customer_id)  # other party
    agreement_id = f"agr_{uuid4()}"
    db.execute("""
        INSERT INTO agreements (agreement_id, customer_id, supplier_id,
                                agreement_type, effective_date, current_term_end,
                                status)
        VALUES (%(id)s, %(cust)s, %(sup)s, %(type)s, %(eff)s, %(end)s, 'active')
    """, {
        "id": agreement_id, "cust": customer_id, "sup": supplier_id,
        "type": "master_service_agreement",
        "eff": extraction["effective_date"],
        "end": compute_term_end(extraction["effective_date"], extraction["term_months"])
    })
    db.execute("""
        INSERT INTO document_agreement_links (document_id, agreement_id, role)
        VALUES (%s, %s, 'master')
    """, document_id, agreement_id)
    return agreement_id

Apply an amendment

When an amendment is ingested and linked to an existing agreement:

1. Link the document via document_agreement_links with role='amendment'.
2. Extract what the amendment changes (term extension, price change, scope expansion, etc.).
3. Update the agreement row if material terms change. Log the change in agreement_status_changes.
4. Insert new obligations or supersede old ones (chapter 05).

Find an agreement's full document trail

SELECT
  dal.role,
  d.document_id,
  d.doc_class,
  d.ingested_at
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;

Mark an agreement superseded

When the customer renegotiates the entire MSA rather than amending:

1. Create the new agreement (with its own agreement_id, linked to the new MSA document).
2. Mark the old agreement: UPDATE agreements SET status = 'superseded', status_changed_at = NOW() WHERE agreement_id = 'old_id'.
3. Log the change in agreement_status_changes.
4. Don't delete the old agreement — its obligations remain in history.

✓ Check yourself

Answer from memory before opening each one.

1. A 2020 MSA has two amendments and five SOWs. A subsidiary then signs a regional contract under that MSA. How many agreement rows exist, and how do the regional contract and the amendments differ in the model?

   Show answer

   Two agreement rows. The MSA, its two amendments and its five SOWs are one agreement with eight linked documents. The regional contract is a separate agreement that points to the first through parent_agreement_id. Amendments modify an agreement and never get their own row. See parent-child agreements.

2. An amendment arrives and its master MSA is not in the corpus. What do you store, and what do you avoid doing?

   Show answer

   Store the document with its amendment class and leave it unlinked until the master surfaces or the customer confirms it is missing. Do not drop it, and do not invent an agreement to hold it. A query for unlinked amendments gives the review queue. See orphaned amendments.

3. A dashboard shows an agreement as active three weeks after its term ended. Give the likely cause and two ways to fix it.

   Show answer

   The stored status depends on a job that promotes active to expired, and the job has failed or does not run. Fix the job, or compute status in queries from current_term_end instead of trusting the column. See computing status.

4. An auditor asks what an agreement's status was last March. Compare a status-change log with versioned rows on the agreement, and choose one.

   Show answer

   The log keeps the agreement table small and queries on current state simple, and a lookup of past status reads the last change before the date. Versioned rows make as-of queries a plain filter but add overhead to every read. The log is the common choice; pick versioned rows when as-of reads dominate. See temporal status modeling.

5. The customer renegotiates the whole MSA instead of amending it. What are the steps, and why is deleting the old agreement wrong?

   Show answer

   Create a new agreement linked to the new MSA document, mark the old one superseded with a change timestamp, and log the change. Keep the old row, because its obligations and spend history still refer to it. See mark an agreement superseded.

Takeaway

• An agreement is the logical contract, and many documents roll up to it through a link table with a role.
• Amendments attach to an existing agreement; only a separate contract that hangs under another gets its own row with a parent reference.
• Orphaned amendments are stored and left unlinked until the master appears.
• Stored status goes stale unless a job maintains it, so compute it from the term end when the job is not trusted.
• A status-change log answers as-of questions without making every read pay for versioning.
• A renegotiated contract supersedes the old agreement and never deletes it.

Obligations come from extractions, and they change over time, which needs its own versioning. → Obligations and Temporal Modeling

Further reading

• Bitemporal History — Martin Fowler, 2021. Separating when a fact was true from when you learned it, which is the question behind as-of status.
• Slowly Changing Dimension Type 2 — Kimball Group. The standard definition of versioned rows with effective dates.
• PostgreSQL: WITH queries — PostgreSQL documentation. Recursive queries for walking parent and child agreements.