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
    """, {"doc_id": document_id, "customer": customer_id, "uri": source_uri,
          "hash": file_hash, "class": doc_class, "pages": page_count})
    # RETURNING yields a row only when the insert happened. No row means the file 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.

✓ Check yourself

Answer from memory before opening each one.

1. The same PDF arrives from SharePoint and from the CLM. A third copy is a fresh scan of that paper contract. How many document rows result, and is that right?

   Show answer

   Two rows. The two byte-identical uploads share a hash, so they share one document_id. The re-scan differs in bytes, so it is a separate document, which is correct because scan quality can change what extraction sees. See document_id derivation.

2. The ID uses only 16 hex characters of the SHA-256, but the full hash is stored and unique. What happens in the unlikely case that two different files share a 16-character prefix?

   Show answer

   The second insert violates the primary key on document_id, because the upsert only absorbs conflicts on (customer_id, file_hash). The failure is loud rather than a silent merge, and the stored full hash lets you tell the files apart. See the recipe.

3. The ingest statement uses ON CONFLICT DO NOTHING with RETURNING. For a file already ingested, what does it return, and how does the caller tell new from existing?

   Show answer

   It returns no row, because RETURNING only reports rows that were actually inserted. An empty result means the file already existed. See idempotent ingest.

4. A renewal alert fires late for a batch of contracts loaded from an old archive. Which timestamp did the alert use wrongly, and what should it use?

   Show answer

   It used ingested_at, which records when the platform saw the file. Renewal logic needs the business date, signed_at or the effective date from extraction. Ingest and extraction times serve SLA reporting. See the ingest and process timeline.

5. A customer uploaded the wrong folder. Someone proposes deleting those rows. What do you do instead, and why?

   Show answer

   Add a status to documents and mark them archived, then filter archived rows out of the mart views. A hard delete breaks foreign keys from links and extractions and removes the audit trail. See soft-delete a document.

Takeaway

• A document is one physical file, so an MSA, an amendment and a SOW are three rows.
• The document ID derives from the file hash, which makes ingest an idempotent upsert and dedupes identical files across sources.
• A re-scan or a format conversion is a different file and gets a different ID; cross-format dedupe needs a separate content fingerprint.
• The table holds metadata only. Bytes sit in object storage, and OCR text and extracted fields live in their own tables.
• Business dates such as signed date drive renewal logic; ingest and extraction times drive operational reporting.
• Wrong uploads are archived, not deleted, because other tables reference the row.

Documents roll up into agreements, the logical contracts that obligations and spend attach to. → Agreements

Further reading

• Secure Hash Standard (FIPS 180-4) — NIST, 2015. The SHA-256 definition behind the content-derived document ID.
• PostgreSQL: INSERT — PostgreSQL documentation. The ON CONFLICT clause and what RETURNING reports on conflict.
• PostgreSQL: Constraints — PostgreSQL documentation. How unique and primary-key constraints interact when a key prefix collides.