Sample Corpus SQL Integrations — `DocumentStore`

Deep dive into every SQL surface of the medical + salvage sample corpus:

Section	Focus
0	Setup + open DB
1	DDL / schema version
2	Claims table CRUD
3	Documents upsert + filters
4	`document_fields` ground truth
5	Joins: claim ↔︎ documents ↔︎ fields
6	Splits, source_kind, synthetic flags
7	Provenance events
8	Raw SQL analytics
9	Import / export round-trips
10	Page table hook (optional assets)

The store uses stdlib sqlite3 (same pattern as Discord notes_store.py) — no ORM required.

0. Setup

from __future__ import annotations

import json
import logging
import sqlite3
import sys
import time
from collections import Counter
from pathlib import Path

import matplotlib.pyplot as plt
import pandas as pd
from IPython.display import Markdown, display

CWD = Path.cwd().resolve()
REPO_ROOT = CWD if (CWD / "pyproject.toml").exists() else CWD.parent
assert (REPO_ROOT / "pyproject.toml").exists(), f"Could not find repo root from {CWD}"
if str(REPO_ROOT) not in sys.path:
    sys.path.insert(0, str(REPO_ROOT))

from src.docie import DociePipeline
from src.docie.applications import list_applications, load_application
from src.docie.eval import evaluate_application
from src.docie.pipeline import run_file
from src.storage import DocumentStore
from src.storage.sample_generator import generate_claim_bundle, generate_corpus
from src.storage.schema import DDL, SCHEMA_VERSION
from src.storage.training import (
    fit_tfidf_random_forest,
    prepare_both_applications,
)
from src.storage.types import ClaimRecord, DocumentRecord, FieldRecord
from src.utils.config import Config
from src.utils.io import load_jsonl, read_json, write_json

logging.basicConfig(level=logging.INFO, format="%(levelname)s %(name)s: %(message)s")
cfg = Config.load()

DEMO = REPO_ROOT / "data" / "notebook_demo" / "sample_corpus"
DEMO.mkdir(parents=True, exist_ok=True)
DB_PATH = DEMO / "documents.db"
EXPORTS = DEMO / "exports"
EXPORTS.mkdir(parents=True, exist_ok=True)
PREPARED = DEMO / "prepared"
MODELS = DEMO / "models"
MODELS.mkdir(parents=True, exist_ok=True)

SEED = 42
print(f"repo:     {REPO_ROOT}")
print(f"demo db:  {DB_PATH}")
print(f"exports:  {EXPORTS}")
print(f"schema v: {SCHEMA_VERSION}")
print(f"apps:     {list_applications()}")

repo:     /Users/morningstar/Desktop/Cold_Storage/smol-doc-analyzer
demo db:  /Users/morningstar/Desktop/Cold_Storage/smol-doc-analyzer/data/notebook_demo/sample_corpus/documents.db
exports:  /Users/morningstar/Desktop/Cold_Storage/smol-doc-analyzer/data/notebook_demo/sample_corpus/exports
schema v: 1
apps:     ['acord', 'medical_bills', 'salvage_claims']

# Fresh demo DB for this notebook
if DB_PATH.exists():
    DB_PATH.unlink()
store = DocumentStore(DB_PATH)
corpus = generate_corpus(
    seed=SEED,
    medical_per_type=5,
    salvage_per_type=5,
    bundles_per_app=1,
    include_canonical_fixtures=True,
)
store.bulk_upsert(corpus.documents, claims=corpus.claims)
print(store.summary())

{'db_path': '/Users/morningstar/Desktop/Cold_Storage/smol-doc-analyzer/data/notebook_demo/sample_corpus/documents.db', 'schema_version': 1, 'claims': 38, 'documents': 42, 'fields': 210, 'by_application_type': [{'application': 'medical_bills', 'document_type': 'hcfa', 'count': 7}, {'application': 'medical_bills', 'document_type': 'other', 'count': 7}, {'application': 'medical_bills', 'document_type': 'ub04', 'count': 7}, {'application': 'salvage_claims', 'document_type': 'log', 'count': 7}, {'application': 'salvage_claims', 'document_type': 'other', 'count': 7}, {'application': 'salvage_claims', 'document_type': 'sales', 'count': 7}]}

1. DDL / schema version

print("SCHEMA_VERSION =", SCHEMA_VERSION)
print("--- DDL excerpt ---")
print("\n".join(DDL.strip().splitlines()[:40]))
print("...")

with sqlite3.connect(DB_PATH) as conn:
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).fetchall()
    indexes = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='index' ORDER BY name"
    ).fetchall()
print("tables:", [t[0] for t in tables])
print("indexes:", [i[0] for i in indexes])

SCHEMA_VERSION = 1
--- DDL excerpt ---
CREATE TABLE IF NOT EXISTS schema_meta (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS claims (
    claim_id TEXT PRIMARY KEY,
    application TEXT NOT NULL,
    carrier_name TEXT NOT NULL,
    state TEXT,
    date_of_loss TEXT,
    loss_type TEXT,
    policy_number TEXT,
    insured_name TEXT,
    metadata_json TEXT NOT NULL DEFAULT '{}',
    created_at REAL NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_claims_application
    ON claims(application);
CREATE INDEX IF NOT EXISTS idx_claims_carrier
    ON claims(carrier_name);

CREATE TABLE IF NOT EXISTS documents (
    document_id TEXT PRIMARY KEY,
    claim_id TEXT,
    application TEXT NOT NULL,
    document_type TEXT NOT NULL,
    title TEXT,
    text TEXT NOT NULL,
    source_kind TEXT NOT NULL DEFAULT 'synthetic_seed',
    is_synthetic INTEGER NOT NULL DEFAULT 1,
    split TEXT,
    source_path TEXT,
    skeleton_json TEXT NOT NULL DEFAULT '{}',
    metadata_json TEXT NOT NULL DEFAULT '{}',
    created_at REAL NOT NULL,
    updated_at REAL NOT NULL,
    FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
);
...
tables: ['claims', 'document_fields', 'document_pages', 'documents', 'provenance_events', 'schema_meta', 'sqlite_sequence']
indexes: ['idx_claims_application', 'idx_claims_carrier', 'idx_document_fields_document', 'idx_document_fields_name', 'idx_documents_application_type', 'idx_documents_claim', 'idx_documents_source_kind', 'idx_documents_split', 'idx_provenance_document', 'idx_provenance_stage', 'sqlite_autoindex_claims_1', 'sqlite_autoindex_document_fields_1', 'sqlite_autoindex_document_pages_1', 'sqlite_autoindex_documents_1', 'sqlite_autoindex_schema_meta_1']

2. Claims table CRUD

claim = ClaimRecord(
    claim_id="CLM-DEMO-SQL-001",
    application="salvage_claims",
    carrier_name="American Family Insurance",
    state="WI",
    date_of_loss="2024-05-10",
    loss_type="collision",
    policy_number="AF-42-1002003",
    insured_name="Jamie Demo",
    metadata={"notebook": "sql_integrations"},
)
store.upsert_claim(claim)
loaded = store.get_claim("CLM-DEMO-SQL-001")
display(loaded.to_dict() if loaded else None)

# Update carrier branding via upsert
claim.carrier_name = "AmFam"
store.upsert_claim(claim)
print("updated carrier:", store.get_claim("CLM-DEMO-SQL-001").carrier_name)

{'claim_id': 'CLM-DEMO-SQL-001',
 'application': 'salvage_claims',
 'carrier_name': 'American Family Insurance',
 'state': 'WI',
 'date_of_loss': '2024-05-10',
 'loss_type': 'collision',
 'policy_number': 'AF-42-1002003',
 'insured_name': 'Jamie Demo',
 'metadata': {'notebook': 'sql_integrations'},
 'created_at': 1784764130.092731}

updated carrier: AmFam

3. Documents upsert + filter APIs

doc = DocumentRecord(
    document_id="sal-demo-log-sql",
    claim_id="CLM-DEMO-SQL-001",
    application="salvage_claims",
    document_type="log",
    title="Demo Letter of Guarantee",
    text=(
        "LETTER OF GUARANTEE\n"
        "Heartland Bank Title Department\n"
        "Claim Number: CLM-DEMO-SQL-001\n"
        "VIN: 1HGCM82633A004352\n"
        "Year: 2018\n"
        "Make: Honda\n"
        "Model: Accord\n"
        "Payoff Amount: $3,100.00\n"
    ),
    source_kind="notebook_demo",
    is_synthetic=True,
    split="train",
    skeleton={
        "vehicle": {
            "vin": "1HGCM82633A004352",
            "year": "2018",
            "make": "Honda",
            "model": "Accord",
        }
    },
    fields=[
        FieldRecord("claim_id", "CLM-DEMO-SQL-001"),
        FieldRecord("vin", "1HGCM82633A004352"),
        FieldRecord("year", "2018"),
        FieldRecord("make", "Honda"),
        FieldRecord("model", "Accord"),
    ],
)
store.upsert_document(doc)

filters = [
    {"application": "salvage_claims"},
    {"application": "salvage_claims", "document_type": "log"},
    {"claim_id": "CLM-DEMO-SQL-001"},
    {"split": "train"},
    {"source_kind": "notebook_demo"},
]
for f in filters:
    n = len(store.list_documents(**f))
    print(f"{f} → {n}")

{'application': 'salvage_claims'} → 22
{'application': 'salvage_claims', 'document_type': 'log'} → 8
{'claim_id': 'CLM-DEMO-SQL-001'} → 1
{'split': 'train'} → 21
{'source_kind': 'notebook_demo'} → 1

4. `document_fields` ground truth vs extracted roles

store.set_fields(
    "sal-demo-log-sql",
    {"payoff_amount": "3100.00", "lienholder": "Heartland Bank"},
    role="annotation",
)
store.set_fields(
    "sal-demo-log-sql",
    {"vin": "1HGCM82633A004352", "make": "Honda"},
    role="extracted",
    confidence=0.91,
)

with sqlite3.connect(DB_PATH) as conn:
    conn.row_factory = sqlite3.Row
    field_rows = conn.execute(
        "SELECT field_name, field_value, field_role, confidence "
        "FROM document_fields WHERE document_id = ? "
        "ORDER BY field_role, field_name",
        ("sal-demo-log-sql",),
    ).fetchall()
pd.DataFrame([dict(r) for r in field_rows])

	field_name	field_value	field_role	confidence
0	lienholder	Heartland Bank	annotation	NaN
1	payoff_amount	3100.00	annotation	NaN
2	make	Honda	extracted	0.91
3	vin	1HGCM82633A004352	extracted	0.91
4	claim_id	CLM-DEMO-SQL-001	ground_truth	NaN
5	make	Honda	ground_truth	NaN
6	model	Accord	ground_truth	NaN
7	vin	1HGCM82633A004352	ground_truth	NaN
8	year	2018	ground_truth	NaN

5. Joins: claim ↔︎ documents ↔︎ fields

sql = """
SELECT
  c.claim_id,
  c.carrier_name,
  c.loss_type,
  d.document_id,
  d.document_type,
  d.split,
  COUNT(f.field_id) AS n_ground_truth_fields
FROM claims c
JOIN documents d ON d.claim_id = c.claim_id
LEFT JOIN document_fields f
  ON f.document_id = d.document_id AND f.field_role = 'ground_truth'
WHERE c.application = 'salvage_claims'
GROUP BY c.claim_id, d.document_id
ORDER BY c.claim_id, d.document_type
LIMIT 20
"""
with sqlite3.connect(DB_PATH) as conn:
    df = pd.read_sql_query(sql, conn)
df

	claim_id	carrier_name	loss_type	document_id	document_type	split	n_ground_truth_fields
0	CLM-2022-618317	AmFam	flood	sal-log-102	log	train	5
1	CLM-2023-152727	American Family Mutual	fire	sal-log-101	log	val	5
2	CLM-2023-728492	AmFam	fire	sal-other-104	other	train	5
3	CLM-2023-950544	American Family	collision	sal-log-103	log	train	5
4	CLM-2024-100200	American Family	NaN	sal-log-001	log	test	5
5	CLM-2024-100201	American Family	NaN	sal-sales-002	sales	test	5
6	CLM-2024-100202	American Family	NaN	sal-other-003	other	test	5
7	CLM-2024-288158	American Family Insurance	flood	sal-other-102	other	train	5
8	CLM-2024-531641	American Family Insurance	collision	sal-log-104	log	train	5
9	CLM-2024-888295	American Family	collision	sal-sales-100	sales	test	5
10	CLM-2025-355836	American Family Insurance	collision	sal-log-100	log	test	5
11	CLM-2025-508396	AmFam	collision	sal-other-100	other	test	5
12	CLM-2025-801978	AmFam	flood	sal-sales-101	sales	val	5
13	CLM-2025-822858	American Family Mutual	theft	sal-sales-103	sales	train	5
14	CLM-2025-823846	AmFam	theft	sal-bundle00-log	log	test	5
15	CLM-2025-823846	AmFam	theft	sal-bundle00-other	other	train	5
16	CLM-2025-823846	AmFam	theft	sal-bundle00-sales	sales	val	5
17	CLM-2026-237377	American Family	flood	sal-sales-104	sales	train	5
18	CLM-2026-486945	AmFam	fire	sal-sales-102	sales	train	5
19	CLM-2026-666211	AmFam	flood	sal-other-103	other	train	5

6. Splits, source kinds, synthetic flags

with sqlite3.connect(DB_PATH) as conn:
    split_df = pd.read_sql_query(
        """
        SELECT application, document_type, split, COUNT(*) AS n
        FROM documents
        GROUP BY 1, 2, 3
        ORDER BY 1, 2, 3
        """,
        conn,
    )
    source_df = pd.read_sql_query(
        """
        SELECT source_kind, is_synthetic, COUNT(*) AS n
        FROM documents
        GROUP BY 1, 2
        """,
        conn,
    )
display(Markdown("### Split distribution"))
display(split_df)
display(Markdown("### Source kinds"))
display(source_df)

Split distribution

	application	document_type	split	n
0	medical_bills	hcfa	test	3
1	medical_bills	hcfa	train	3
2	medical_bills	hcfa	val	1
3	medical_bills	other	test	2
4	medical_bills	other	train	4
5	medical_bills	other	val	1
6	medical_bills	ub04	test	2
7	medical_bills	ub04	train	3
8	medical_bills	ub04	val	2
9	salvage_claims	log	test	3
10	salvage_claims	log	train	4
11	salvage_claims	log	val	1
12	salvage_claims	other	test	2
13	salvage_claims	other	train	4
14	salvage_claims	other	val	1
15	salvage_claims	sales	test	2
16	salvage_claims	sales	train	3
17	salvage_claims	sales	val	2

Source kinds

	source_kind	is_synthetic	n
0	canonical_fixture	1	6
1	notebook_demo	1	1
2	synthetic_seed	1	36

7. Provenance events

store.add_provenance(
    stage="sql_notebook_demo",
    source="sample_corpus_sql_integrations",
    document_id="sal-demo-log-sql",
    claim_id="CLM-DEMO-SQL-001",
    detail={"action": "annotated_extra_fields"},
)
with sqlite3.connect(DB_PATH) as conn:
    prov = pd.read_sql_query(
        "SELECT event_id, document_id, claim_id, stage, source, detail_json "
        "FROM provenance_events",
        conn,
    )
prov

	event_id	document_id	claim_id	stage	source	detail_json
0	1	sal-demo-log-sql	CLM-DEMO-SQL-001	sql_notebook_demo	sample_corpus_sql_integrations	{"action": "annotated_extra_fields"}

8. Raw SQL analytics

analytics = """
SELECT
  application,
  document_type,
  ROUND(AVG(LENGTH(text)), 1) AS avg_chars,
  MIN(LENGTH(text)) AS min_chars,
  MAX(LENGTH(text)) AS max_chars,
  COUNT(*) AS n
FROM documents
GROUP BY application, document_type
ORDER BY application, document_type
"""
carrier_sql = """
SELECT carrier_name, COUNT(*) AS n_claims
FROM claims
GROUP BY carrier_name
ORDER BY n_claims DESC
"""
missing_vin = """
SELECT d.document_id, d.document_type, f.field_value AS vin
FROM documents d
LEFT JOIN document_fields f
  ON f.document_id = d.document_id
 AND f.field_name = 'vin'
 AND f.field_role = 'ground_truth'
WHERE d.application = 'salvage_claims'
ORDER BY CASE WHEN f.field_value IS NULL OR f.field_value = '' THEN 0 ELSE 1 END,
         d.document_id
LIMIT 15
"""
with sqlite3.connect(DB_PATH) as conn:
    display(Markdown("### Text length by type"))
    display(pd.read_sql_query(analytics, conn))
    display(Markdown("### Carrier mix (synthetic branding)"))
    display(pd.read_sql_query(carrier_sql, conn))
    display(Markdown("### Salvage VIN coverage"))
    display(pd.read_sql_query(missing_vin, conn))

Text length by type

	application	document_type	avg_chars	min_chars	max_chars	n
0	medical_bills	hcfa	505.9	292	556	7
1	medical_bills	other	195.3	155	218	7
2	medical_bills	ub04	356.6	226	393	7
3	salvage_claims	log	512.1	168	623	8
4	salvage_claims	other	272.3	150	297	7
5	salvage_claims	sales	424.0	233	471	7

Carrier mix (synthetic branding)

	carrier_name	n_claims
0	AmFam	14
1	American Family	11
2	American Family Insurance	7
3	American Family Mutual	7

Salvage VIN coverage

	document_id	document_type	vin
0	sal-other-003	other	NaN
1	sal-bundle00-log	log	3VWDP7AJ5DM123789
2	sal-bundle00-other	other	1G1ZD5ST1JF012345
3	sal-bundle00-sales	sales	1FADP3F20EL123456
4	sal-demo-log-sql	log	1HGCM82633A004352
5	sal-log-001	log	1HGCM82633A004352
6	sal-log-100	log	KM8J3CA46KU123456
7	sal-log-101	log	5YJSA1E26HF000111
8	sal-log-102	log	3VWDP7AJ5DM123789
9	sal-log-103	log	2T1BURHE0JC123456
10	sal-log-104	log	KM8J3CA46KU123456
11	sal-other-100	other	2T1BURHE0JC123456
12	sal-other-101	other	3VWDP7AJ5DM123789
13	sal-other-102	other	KM8J3CA46KU123456
14	sal-other-103	other	WBA8E9G50JNU12345

9. Import / export round-trips

roundtrip_path = EXPORTS / "sql_roundtrip_docie.jsonl"
exported = store.export_jsonl(roundtrip_path, format="docie", application="salvage_claims")
print("exported", exported)

# Import into a second DB to prove portability
db2 = DEMO / "documents_roundtrip.db"
if db2.exists():
    db2.unlink()
store2 = DocumentStore(db2)
imported = store2.import_docie_jsonl(roundtrip_path, source_kind="roundtrip")
print("imported", imported)
print(store2.summary())

# Field fidelity check
a = store.get_document("sal-log-001")
b = store2.get_document("sal-log-001")
print("field match:", a.ground_truth_fields() == b.ground_truth_fields() if a and b else None)

exported 22
imported 22
{'db_path': '/Users/morningstar/Desktop/Cold_Storage/smol-doc-analyzer/data/notebook_demo/sample_corpus/documents_roundtrip.db', 'schema_version': 1, 'claims': 20, 'documents': 22, 'fields': 110, 'by_application_type': [{'application': 'salvage_claims', 'document_type': 'log', 'count': 8}, {'application': 'salvage_claims', 'document_type': 'other', 'count': 7}, {'application': 'salvage_claims', 'document_type': 'sales', 'count': 7}]}
field match: True

10. Optional `document_pages` hook

# The pages table is reserved for rendered/OCR page assets (DICIE cache paths, etc.)
now = time.time()
with sqlite3.connect(DB_PATH) as conn:
    conn.execute(
        "INSERT INTO document_pages ("
        " document_id, page_index, image_path, width, height, dpi,"
        " ocr_text, words_json, created_at"
        ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        " ON CONFLICT(document_id, page_index) DO UPDATE SET"
        " image_path=excluded.image_path, ocr_text=excluded.ocr_text",
        (
            "sal-demo-log-sql",
            0,
            str(DEMO / "pages" / "sal-demo-log-sql_p0.png"),
            1000,
            1200,
            200,
            "LETTER OF GUARANTEE ...",
            json.dumps([{"text": "LETTER", "bbox": [10, 10, 80, 30]}]),
            now,
        ),
    )
    conn.commit()
    pages = pd.read_sql_query(
        "SELECT document_id, page_index, width, height, dpi, image_path "
        "FROM document_pages",
        conn,
    )
pages

	document_id	page_index	width	height	dpi	image_path
0	sal-demo-log-sql	0	1000	1200	200	/Users/morningstar/Desktop/Cold_Storage/smol-d...

Takeaways

Canonical house for synthetic medical + salvage docs is SQLite via DocumentStore
Ground-truth lives in document_fields with roles (ground_truth / extracted / annotation)
Claim bundles are just multiple documents rows sharing claim_id
JSONL import/export keeps DICIE + training pipelines file-compatible
Raw SQL is available anytime for analytics that the Python API does not wrap

0. Setup

1. DDL / schema version

2. Claims table CRUD

3. Documents upsert + filter APIs

4. document_fields ground truth vs extracted roles

5. Joins: claim ↔︎ documents ↔︎ fields

6. Splits, source kinds, synthetic flags

Split distribution

Source kinds

7. Provenance events

8. Raw SQL analytics

Text length by type

Carrier mix (synthetic branding)

Salvage VIN coverage

9. Import / export round-trips

10. Optional document_pages hook

Takeaways

4. `document_fields` ground truth vs extracted roles

10. Optional `document_pages` hook