Documents

The documents table

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)
);

One row per physical file. A signed MSA, an amendment, a SOW are three separate rows. The agreements table (chapter 04) links them into logical contracts.

Field-by-field rationale

• document_id: stable identifier derived from file_hash (see next section).
• source_uri: where the file came from. S3 path, SharePoint URL, CLM document ID. Needed for audit and to fetch the file if needed.
• file_hash: SHA-256 of the file contents. The basis for idempotency.
• doc_class: the document class taxonomy entry (see below).
• page_count: stored at ingestion time; useful for routing (single-page invoices vs 100-page MSAs need different handling).
• ingested_at: when this document first entered the platform. Different from when it was created in the source.

What's NOT here

• The file contents. Files are stored in object storage (S3, GCS, Azure Blob) at source_uri. The documents table holds metadata only.
• The OCR'd text. Lives in a separate document_text or ocr_results table, joined by document_id. Keeping it out of the documents table keeps row sizes small for the analytics use case.
• The extracted fields. Those live in extractions (chapter 01 schema).

document_id derivation

The convention: document_id is deterministic based on file contents, not random.

The recipe

import hashlib

def derive_document_id(file_bytes: bytes, customer_id: str) -> tuple[str, str]:
    """Return (document_id, file_hash). Stable across re-ingestion."""
    h = hashlib.sha256(file_bytes).hexdigest()
    # 16-char prefix is enough to avoid collisions in practice;
    # full hash stored separately for cross-checking.
    document_id = f"doc_{customer_id[:8]}_{h[:16]}"
    return document_id, h

Why this matters

• Idempotent ingestion. Same file re-uploaded gets the same document_id. The INSERT is an upsert; nothing changes. No duplicate rows.
• Cross-system tracing. If the customer asks "what's document doc_acme_a3b4c5d6?", they can find it across logs, dashboards, extractions, audit trails.
• Deduplication across sources. If the same file lands in both their SharePoint and their CLM, both ingests produce the same document_id. The platform sees one document, not two.

Edge cases

• Re-scanned versions. A document scanned twice produces two different file_hashes (the binary data differs even if the content is the same). They'd be separate document_ids. That's correct — the platform shouldn't deduplicate across scan-quality variations.
• Format conversions. The same contract as DOCX and as PDF are different files. Different document_ids. Some platforms add a "content fingerprint" (canonicalized text) as a separate column for cross-format dedupe; that's a layer above this schema.

Document class taxonomy

The doc_class string identifies what kind of document this is — drives routing through the extraction pipeline.

A working taxonomy

How classification happens

• Source-system metadata: if the document comes from a CLM tagged "MSA," trust the tag.
• Filename pattern: "MSA_Acme_2024.pdf" → master_service_agreement.
• Header-content classification: read the first page; classify by title.
• Model-based classification: a small classifier model on the first page or full document. Used when other signals fail.

Customer-specific extensions

Some customers have proprietary document types. Extend the taxonomy with customer-scoped entries:

acme_corp:vendor_onboarding_packet
acme_corp:annual_review

Namespace by customer to avoid global-taxonomy bloat.

The ingest + process timeline

The documents table is the join point of multiple timelines:

signed_at is the business-meaningful date. ingested_at is the platform-operational date. For renewal logic, signed_at matters. For SLA reporting, ingested_at + extracted_at matter.

Common operations

Idempotent ingest

def ingest_document(file_bytes: bytes, source_uri: str, customer_id: str) -> dict:
    document_id, file_hash = derive_document_id(file_bytes, customer_id)
    doc_class = classify(file_bytes)  # heuristic or model
    page_count = count_pages(file_bytes)

    db.execute("""
        INSERT INTO documents (document_id, customer_id, source_uri, file_hash,
                               doc_class, page_count, ingested_at)
        VALUES (%(doc_id)s, %(customer)s, %(uri)s, %(hash)s,
                %(class)s, %(pages)s, NOW())
        ON CONFLICT (customer_id, file_hash) DO NOTHING
        RETURNING document_id, (xmax = 0) AS inserted
    """, {"doc_id": document_id, "customer": customer_id, "uri": source_uri,
          "hash": file_hash, "class": doc_class, "pages": page_count})
    # If returning row's inserted=true, this is a new doc; if false, already existed.

Fetch documents that need extraction

Pattern: find documents that haven't been extracted with the current model version.

SELECT d.document_id, d.source_uri, d.doc_class
FROM documents d
LEFT JOIN extractions e
  ON e.document_id = d.document_id
 AND e.model_version = 'extract-v4.2.1'
WHERE d.customer_id = 'cust_acme'
  AND e.extraction_id IS NULL
ORDER BY d.ingested_at;

Reclassify a document

If classification was wrong, update doc_class:

UPDATE documents SET doc_class = 'amendment'
WHERE document_id = 'doc_acme_a3b4c5d6';

Then trigger re-extraction with the new class's extraction config. Old extraction rows stay for audit.

Soft-delete a document

Customers occasionally upload documents in error. Don't hard-delete (breaks foreign-key references in agreements / extractions). Add a status column with values active / archived; filter on it in mart views.