Section B · Temporal & transactional

Spend & Invoices

The spend fact table — invoice and PO actuals joined to suppliers and agreements. The fuzzy-link problem, currency normalization, and the on-contract / off-contract distinction that drives platform ROI.

The spend table

spend.sql

CREATE TABLE spend (
  spend_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  supplier_id TEXT NOT NULL REFERENCES suppliers,
  agreement_id TEXT REFERENCES agreements,   -- nullable: off-contract spend
  invoice_id TEXT,
  amount NUMERIC(15, 2) NOT NULL,
  currency TEXT NOT NULL,
  accrued_at DATE NOT NULL,
  spend_category TEXT
);

Field rationale

supplier_id: the canonical supplier from chapter 02. Resolved via entity resolution if extracted from a document; pulled from ERP master if sourced from a procurement system.
agreement_id: nullable. When the spend can be tied to a specific agreement, populated. Off-contract spend has it null.
invoice_id: source-system identifier. Helps trace back to the customer's ERP.
amount: numeric, fixed precision. Avoid floats for money.
currency: ISO 4217 code (USD, EUR, GBP). Always populated; default conversion lives in marts.
accrued_at: when the spend was accrued in accounting. Usually invoice date or service date depending on customer policy.
spend_category: customer's category taxonomy. Often pulled from their ERP.

Where spend data comes from

Customer's ERP

The most common source — SAP, Oracle, NetSuite, Workday, etc. The platform pulls invoice and PO data via API or warehouse extract. Most reliable; this is the customer's system-of-record for spend.

Customer's procurement suite

Coupa, Ariba, etc. Often has more category-level detail than ERP; sometimes cleaner supplier mapping. Use alongside ERP rather than instead of.

Extracted from invoices and POs

The platform's own extraction pipeline can pull spend from invoice and PO documents directly. Useful when ERP integration is gated or incomplete; produces spend rows from the same pipeline that produces obligations.

Customer's data warehouse

Mature customers maintain spend data in their own warehouse, often combined across sources. If you're delivering output into their warehouse anyway, joining to their pre-existing spend table is cleanest.

Linking spend to agreements

The hard problem in spend modeling. An invoice from "Acme Cloud Services" needs to be tied to the right active agreement with that supplier — but:

The customer might have three active agreements with the same supplier.
The invoice might reference a specific PO; the PO might reference a specific SOW; the SOW might be under a specific MSA.
Or the invoice might just say "Services per agreement" without specifying.

Strong links

When the ERP or invoice explicitly references a contract ID that matches an agreement_id in your platform: trivial. Set agreement_id directly.

Inferred links

If the supplier has exactly one active agreement at the invoice date, link to it.
If multiple, look for keyword matches (project name, SOW reference) in the invoice description.
If still ambiguous, use the highest-spend agreement for that supplier in the past 90 days.
If still ambiguous, leave agreement_id null and flag for the customer's data team.

The fuzzy-link confidence

Like supplier resolution, spend-to-agreement linkage can have confidence. Some platforms add a link_confidence column and a separate audit table tracking how each link was made.

The fuzzy-link problem

Even with the best customer data, ~10–30% of spend can't be cleanly tied to a specific agreement. Reasons:

The supplier has no agreement in the platform yet (off-contract).
Multiple plausible agreements; insufficient evidence to pick one.
The invoice covers work spanning multiple agreements (e.g., a master invoice for a quarter of services).
Data quality issues — supplier name doesn't match canonically, invoice date is wrong, etc.

Production approach:

Set agreement_id where the link is confident.
Leave it null where it isn't.
Surface unlinkable spend to the customer's data team as a queue.
Periodically re-link as new agreements come in or as resolution improves.

Currency normalization

Multi-currency customers — most enterprises — accrue spend in many currencies. Some normalization patterns:

Store the original; convert at query time

The cleanest approach. amount and currency are the original. A separate fx_rates table converts to USD (or the customer's reporting currency) at query time.

CREATE TABLE fx_rates (
  from_currency TEXT NOT NULL,
  to_currency TEXT NOT NULL,
  rate_date DATE NOT NULL,
  rate NUMERIC(20, 10) NOT NULL,
  PRIMARY KEY (from_currency, to_currency, rate_date)
);

-- Spend in USD at the accrual date
SELECT s.spend_id, s.amount * fx.rate AS amount_usd, s.accrued_at
FROM spend s
JOIN fx_rates fx
  ON fx.from_currency = s.currency
 AND fx.to_currency = 'USD'
 AND fx.rate_date = s.accrued_at;

Pre-converted columns

Some platforms denormalize a amount_usd column for query convenience. Acceptable but locks in a specific conversion-date convention.

Which date to convert on

Accrual date: most accurate for matching to financial period.
Invoice date: easier to look up; aligns with payable accounting.
Month-average rate: simpler; less precise for high-volatility periods.

Pick one with the customer's finance team; document it; don't change silently.

On-contract vs off-contract

The single most valuable analytical question this schema supports:

Definition

On-contract spend: spend with a supplier where an active agreement covers the time period.
Off-contract spend: spend where no active agreement covers it. Either with an unknown supplier, or with a supplier whose agreement has expired.

Why customers care

Off-contract spend means:

The customer didn't capture negotiated pricing they could have.
Policy controls (preferred supplier lists, payment terms) weren't enforced.
Risk exposure: no contractual protections (liability caps, indemnification, DPAs).

Customers typically discover 15–30% of their spend is off-contract. That's often the headline ROI number the platform is sold on.

Subcategories

Unknown-supplier off-contract: the supplier isn't in any agreement. Often opportunistic purchases or new vendors that bypassed procurement.
Expired-agreement off-contract: the supplier had an agreement that expired and was never renewed. Often preventable.
Out-of-scope-of-agreement: the supplier has an agreement but the spend is for a category outside what the agreement covers. Harder to detect; needs category matching.

SQL pattern for computing each is in chapter 07.

Common operations

Ingest spend from an ERP extract

def ingest_spend_batch(rows: list[dict], customer_id: str):
    for row in rows:
        supplier_id = resolve_supplier(row["vendor_name"], customer_id, row.get("vendor_tax_id"))
        agreement_id = infer_agreement(supplier_id, row["invoice_date"], row.get("po_reference"))
        db.execute("""
            INSERT INTO spend (spend_id, customer_id, supplier_id, agreement_id,
                               invoice_id, amount, currency, accrued_at, spend_category)
            VALUES (%(id)s, %(cust)s, %(sup)s, %(agr)s, %(inv)s, %(amt)s,
                    %(cur)s, %(acc)s, %(cat)s)
            ON CONFLICT (spend_id) DO NOTHING
        """, {...})

Compute supplier spend per FY (with parent rollup)

WITH RECURSIVE supplier_hierarchy AS (
  SELECT supplier_id, supplier_id AS root_id FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, h.root_id
  FROM suppliers s JOIN supplier_hierarchy h ON s.parent_supplier_id = h.supplier_id
)
SELECT
  parent.canonical_name AS parent_supplier,
  SUM(s.amount * fx.rate) AS total_spend_usd
FROM spend s
JOIN supplier_hierarchy h ON h.supplier_id = s.supplier_id
JOIN suppliers parent ON parent.supplier_id = h.root_id
JOIN fx_rates fx
  ON fx.from_currency = s.currency
 AND fx.to_currency = 'USD'
 AND fx.rate_date = s.accrued_at
WHERE s.customer_id = 'cust_acme'
  AND s.accrued_at >= '2025-07-01'  -- customer FY start
  AND s.accrued_at <  '2026-07-01'
GROUP BY parent.canonical_name
ORDER BY total_spend_usd DESC;

Find off-contract spend

See chapter 07 for the full SQL pattern. Briefly:

SELECT s.supplier_id, SUM(s.amount) AS off_contract_total
FROM spend s
LEFT JOIN agreements a
  ON a.supplier_id = s.supplier_id
 AND a.status = 'active'
 AND s.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
WHERE a.agreement_id IS NULL
  AND s.customer_id = 'cust_acme'
GROUP BY s.supplier_id;

✓ Check yourself

Answer from memory before opening each one.

An invoice arrives from a supplier with three active agreements and no PO reference. In what order do you try to link it, and what happens if every step fails?

Show answer
Use an explicit contract reference if there is one, then the single active agreement if only one exists, then keywords such as project or SOW names, then the highest recent spend. If none of these is confident, leave agreement_id null and send the row to a review queue. A wrong link is worse than an honest null. See linking spend to agreements.
The currency query joins spend to fx_rates on the exact accrual date. What happens to an invoice accrued on a Saturday, and how do you fix it?

Show answer
If the rate table has no weekend rows, the inner join drops the invoice without any error, and totals come out low. Join to the latest rate on or before the accrual date, or fill the rate table for every calendar day. See currency normalization.
A report says 40% of spend rows have a null agreement_id. Is that 40% off-contract? Explain.

Show answer
No. A null link means the row could not be tied to one agreement, which may be because several plausible agreements exist. Off-contract means no active agreement covers that supplier and date. Some null rows are on-contract but unlinked. See the fuzzy-link problem and on-contract vs off-contract.
Finance wants a stored amount_usd column. What do you weigh, and what do you insist on if you add it?

Show answer
A stored column makes queries simple and fast but fixes one rate convention, and a changed convention then needs a backfill. Converting at query time stays flexible but costs a join. If you store it, record the rate date and source and keep the original amount and currency. See which date to convert on.
The nightly ERP extract resends overlapping rows, and the load uses ON CONFLICT DO NOTHING. What does that protect, and what does it miss?

Show answer
It stops duplicate rows when the same spend ID arrives again. It also ignores a corrected amount on an existing ID, so credit notes and fixes never land. Use an update on conflict, or version the rows, if the source revises history. See ingest spend from an ERP extract.

Takeaway

Spend is a fact table at line-item grain, with supplier resolved and agreement optional.
Link spend to an agreement by explicit reference first, then inference, and leave the link null when evidence is thin.
Typically 10 to 30 percent of spend cannot be linked, so build a review queue and relink as resolution improves.
Store original amount and currency, convert at query time, and agree the conversion date with finance.
Off-contract spend is defined by coverage of supplier and date, not by a null link, and it splits into unknown supplier, expired agreement and out of scope.
Exact-date rate joins silently drop rows on days without rates.

The patterns in the next chapter query everything built so far. → SQL Pattern Library