postcrimedb schema
The canonical datastore is ParadeDB / Postgres postcrimedb (.181) , schema
postcrime (plus stats). 23 tables, 22 foreign keys. documents is the hub —
almost everything references it.
Interactive diagram: open the schema viewer ↗ ?focus=<table> (e.g. ?focus=cases?tables=a,b,c. Static renders also live in docs/schema/*.svg and
docs/postcrime-schema.{svg,pdf,mmd,dbml}. Regenerate with
bun run scripts/build-schema-viewer.ts.
Full model
case_facts 30026 rows ● document_id bigint defendants_json text first_act_date text plea_date text sentence_date text monetary_amount bigint monetary_currency text notes text extracted_at text cases 2665 rows ● case_number text caption text court text ↗ document_id integer slug text court_code text jurisdiction text jurisdiction_type text nature_of_suit text plaintiff text defendants_json jsonb defendant_count bigint lead_entity text scheme text scheme_slug text claims_json jsonb +20 more… defendant_dispositions 19986 rows ● document_id bigint ● defendant_name text ↗ entity_id bigint role text defendant_class text disposition_type text charge_date text disposition_date text charge_count text top_charge text prison_months text probation_months bigint supervised_release_months bigint home_confinement_months bigint cooperation text acceptance_of_responsibility text +6 more… doc_chunk_meta 1381740 rows ● chunk_id integer ↗ document_id integer chunk_index integer text_preview text span_start integer span_end integer doc_chunks 1381740 rows ● chunk_id integer ↗ document_id integer embedding USER-DEFINED doc_page_summaries 150 rows ● id integer ↗ document_id integer page_index integer page_text text summary text char_count integer processed_at text documents 37619 rows ● id integer ↗ source_id integer url text kind text title text published_at text ↗ case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… documents_processing_log 0 rows ● log_id bigint ↗ doc_id bigint stage text started_at text ended_at text status text error text duration_ms bigint chunks bigint metadata text enrichments 11470 rows ● doc_id integer ● enrichment_name text value_text text value_json text model text prompt_version integer created_at bigint entities 231264 rows ● id bigint name text kind text norm text matters 2618 rows ● matter_id bigint slug text title text ↗ primary_case_number text lead_entity text doc_count bigint created_at bigint mentions 157491 rows ● id bigint ↗ document_id bigint ↗ entity_id bigint ↗ ticker_id text role text span_start bigint span_end bigint meta 0 rows ● key text value text schema_version 0 rows ● v bigint scheme_edgar_rules 25 rows ● id bigint scheme_slug text scheme_label text edgar_forms text method text target text maps_to_heuristic text rationale text sources 3 rows ● id integer name text base_url text task_state 7981 rows ● task text ● target_key text status text attempts bigint last_error text last_ts timestamp_with_time_zone watermark timestamp_with_time_zone next_eligible_at timestamp_with_time_zone tickers 0 rows ● id integer symbol text exchange text triples 432836 rows ● id integer ↗ document_id integer subject text verb text object text confidence real triples_attempts 0 rows ● document_id bigint attempts bigint last_error text body_length bigint last_attempted_at text urls 40094 rows ● url text ↗ source_id integer kind text state text attempts integer last_error text enqueued_at text updated_at text workflow_runs 485917 rows ● workflow text ● document_id bigint status text attempts bigint last_error text last_attempt_at text result_summary text panels 36 rows ● panel_id text version integer generated_at timestamp_with_time_zone generated_at_epoch bigint params jsonb data jsonb meta jsonb loaded_at timestamp_with_time_zone Documents core
The crawl/ingest surface: documents (the hub) with its sources, the urls
crawl queue, per-page OCR summaries, and the processing log.
doc_page_summaries 150 rows ● id integer ↗ document_id integer page_index integer page_text text summary text char_count integer processed_at text documents 37619 rows ● id integer ↗ source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… documents_processing_log 0 rows ● log_id bigint ↗ doc_id bigint stage text started_at text ended_at text status text error text duration_ms bigint chunks bigint metadata text sources 3 rows ● id integer name text base_url text urls 40094 rows ● url text ↗ source_id integer kind text state text attempts integer last_error text enqueued_at text updated_at text Search & vectors
BM25 full-text lives on documents (pg_search); semantic search on
doc_chunks.embedding (pgvector, 768-dim) with doc_chunk_meta holding the
chunk spans/preview.
doc_chunk_meta 1381740 rows ● chunk_id integer ↗ document_id integer chunk_index integer text_preview text span_start integer span_end integer doc_chunks 1381740 rows ● chunk_id integer ↗ document_id integer embedding USER-DEFINED documents 37619 rows ● id integer source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… Entities & mentions
Canonical entities (deduped by norm,kind), mentions linking entities and
tickers to documents.
documents 37619 rows ● id integer source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… entities 231264 rows ● id bigint name text kind text norm text mentions 157491 rows ● id bigint ↗ document_id bigint ↗ entity_id bigint ↗ ticker_id text role text span_start bigint span_end bigint tickers 0 rows ● id integer symbol text exchange text Defendant dispositions
Per-defendant outcomes (plea/verdict/settlement/sanctions) keyed by
(document_id, defendant_name), resolved to entities where possible.
defendant_dispositions 19986 rows ● document_id bigint ● defendant_name text ↗ entity_id bigint role text defendant_class text disposition_type text charge_date text disposition_date text charge_count text top_charge text prison_months text probation_months bigint supervised_release_months bigint home_confinement_months bigint cooperation text acceptance_of_responsibility text +6 more… documents 37619 rows ● id integer source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… entities 231264 rows ● id bigint name text kind text norm text Cases & matters
The normalized cases (plaintiff/defendant/jurisdiction/claims/amounts),
matters grouping related cases, and case_facts extracted dates/amounts.
case_facts 30026 rows ● document_id bigint defendants_json text first_act_date text plea_date text sentence_date text monetary_amount bigint monetary_currency text notes text extracted_at text cases 2665 rows ● case_number text caption text court text ↗ document_id integer slug text court_code text jurisdiction text jurisdiction_type text nature_of_suit text plaintiff text defendants_json jsonb defendant_count bigint lead_entity text scheme text scheme_slug text claims_json jsonb +20 more… documents 37619 rows ● id integer source_id integer url text kind text title text published_at text ↗ case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… matters 2618 rows ● matter_id bigint slug text title text ↗ primary_case_number text lead_entity text doc_count bigint created_at bigint Triples
Subject–verb–object extractions in triples, with triples_attempts tracking
per-document extraction state.
documents 37619 rows ● id integer source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… triples 432836 rows ● id integer ↗ document_id integer subject text verb text object text confidence real triples_attempts 0 rows ● document_id bigint attempts bigint last_error text body_length bigint last_attempted_at text Enrichment
Generic enrichments key/value store, workflow_runs gating, and task_state
(the enrich-runner lifecycle).
documents 37619 rows ● id integer source_id integer url text kind text title text published_at text case_number text caption text release_number text fetched_at text http_status integer sha256 text content_type text byte_size bigint body_path text pdf_path text +71 more… enrichments 11470 rows ● doc_id integer ● enrichment_name text value_text text value_json text model text prompt_version integer created_at bigint task_state 7981 rows ● task text ● target_key text status text attempts bigint last_error text last_ts timestamp_with_time_zone watermark timestamp_with_time_zone next_eligible_at timestamp_with_time_zone workflow_runs 485917 rows ● workflow text ● document_id bigint status text attempts bigint last_error text last_attempt_at text result_summary text