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.