Spend & Invoices

The spend table

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:

1. Set agreement_id where the link is confident.
2. Leave it null where it isn't.
3. Surface unlinkable spend to the customer's data team as a queue.
4. 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 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;