Schema Overview

-- Customers (tenant boundary)
CREATE TABLE customers (
  customer_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Suppliers (canonical entities; post-resolution)
CREATE TABLE suppliers (
  supplier_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  canonical_name TEXT NOT NULL,
  parent_supplier_id TEXT REFERENCES suppliers,
  country TEXT,
  tax_id TEXT,
  industry TEXT,
  first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (customer_id, canonical_name)
);
CREATE INDEX idx_suppliers_customer ON suppliers (customer_id);
CREATE INDEX idx_suppliers_parent ON suppliers (parent_supplier_id);

-- Documents (physical files)
CREATE TABLE documents (
  document_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  source_uri TEXT NOT NULL,
  file_hash TEXT NOT NULL,
  doc_class TEXT NOT NULL,
  page_count INT NOT NULL,
  ingested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (customer_id, file_hash)
);
CREATE INDEX idx_documents_customer_class ON documents (customer_id, doc_class);
CREATE INDEX idx_documents_ingested ON documents (ingested_at);

-- Agreements (logical contracts)
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 INDEX idx_agreements_supplier ON agreements (supplier_id);
CREATE INDEX idx_agreements_term_end ON agreements (current_term_end);
CREATE INDEX idx_agreements_status ON agreements (customer_id, status);

-- Document ↔ Agreement linkage (M:N)
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)
);
CREATE INDEX idx_dal_agreement ON document_agreement_links (agreement_id);

-- Obligations (extracted commitments per agreement)
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()
);
CREATE INDEX idx_obligations_agreement ON obligations (agreement_id);
CREATE INDEX idx_obligations_due ON obligations (due_at);

-- Spend (invoice / PO actuals; sometimes linked to agreements)
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,
  invoice_id TEXT,
  amount NUMERIC(15, 2) NOT NULL,
  currency TEXT NOT NULL,
  accrued_at DATE NOT NULL,
  spend_category TEXT
);
CREATE INDEX idx_spend_supplier ON spend (supplier_id);
CREATE INDEX idx_spend_agreement ON spend (agreement_id);
CREATE INDEX idx_spend_accrued ON spend (accrued_at);

-- Extractions (versioned per-document outputs)
CREATE TABLE extractions (
  extraction_id TEXT PRIMARY KEY,
  document_id TEXT NOT NULL REFERENCES documents,
  customer_id TEXT NOT NULL REFERENCES customers,
  model_version TEXT NOT NULL,
  pipeline_version TEXT NOT NULL,
  fields JSONB NOT NULL,
  qa JSONB,
  extracted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (document_id, model_version)
);
CREATE INDEX idx_extractions_doc_version ON extractions (document_id, model_version);

-- Supplier name resolutions (raw extracted names → canonical)
CREATE TABLE supplier_name_resolutions (
  resolution_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  raw_supplier_name TEXT NOT NULL,
  resolved_supplier_id TEXT NOT NULL REFERENCES suppliers,
  resolution_method TEXT NOT NULL,  -- exact_tax_id | fuzzy_name | customer_map | manual
  confidence FLOAT NOT NULL,
  resolved_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_by TEXT NOT NULL  -- model_version or analyst_id
);
CREATE INDEX idx_snr_raw ON supplier_name_resolutions (customer_id, raw_supplier_name);