Skip to content

CORPUS-SCHEMA.md

Latest commit

 

History

History
227 lines (186 loc) · 13.9 KB

CORPUS-SCHEMA.md

File metadata and controls

227 lines (186 loc) · 13.9 KB
Raw

CORPUS-SCHEMA.md — versioned primary-law data contracts

Companion to SCHEMA.md (which covers the opinions database). This file documents the primary-law corpora: the ND Constitution, court rules, statutes (N.D.C.C.), and administrative code (N.D.A.C.).

Model

Opinions are immutable point-in-time events and live in opinions.db. The four primary-law sources are living, amendable documents, so each is a separate SQLite file with an identical, shared schema (defined once in ndcourts_mcp/corpus.py::create_corpus_schema). The MCP server ATTACH-es whichever corpus files are present onto the opinions connection, so one session answers cross-corpus, point-in-time questions.

corpus DB file ATTACH alias citation form ingester source point-in-time
const constitution.db const N.D. Const. art. I, § 8 ingest_constitution.py ndconst.org (CC0 wiki) current text; full amendment chronology (1889–present)
rule rules.db rules N.D.R.Civ.P. 56 ingest_rules.py ~/refs/rule git repo full (every commit = a version, commit date = effective date)
ndcc statutes.db statutes N.D.C.C. § 12.1-20-03 ingest_statutes.py ~/refs/statute/NDCC current text only (no local historical editions)
admin admincode.db admin N.D.A.C. § 75-02-04.1-02 ingest_admin.py ~/refs/reg/NDAC current text only

The corpus column on provisions carries the short key (const / rule / ndcc / admin); the registry mapping key → file/alias/label is corpus.CORPORA. Corpus DB files are gitignored (data ships separately, like opinions.db); resolution order is NDCOURTS_<KEY>_DB env var → repo root → per-user data dir (corpus.resolve_corpus_db_path).

Field-identity warning (read first)

The citation lives in three related-but-distinct forms; do not conflate them:

field what it is use
provisions.citation the canonical, display citation (N.D. Const. art. I, § 8) what the MCP returns and what jetcite's text_citations.normalized equals — the cross-link key to opinions
provisions.cite_key a normalized lookup key (nd const art i 8) fuzzy citation resolution; see corpus.cite_key
provisions.hierarchy structural path as JSON (title/chapter/section, etc.) grouping/navigation only — never for lookup or citation

Cross-link contract: provisions.citation is built to equal jetcite's text_citations.normalized form for the same authority, so "opinions construing X" is an exact-string match. The six core rule sets, all statutes, all admin sections, and all constitutional provisions follow this. (N.D.A.C. cites are cite_type='regulation' in text_citations, not 'admin'.)

Tables

provisions — one row per stable provision (section / rule / article-section)

column type notes
id INTEGER PK
corpus TEXT NOT NULL const | rule | ndcc | admin
citation TEXT NOT NULL canonical display citation (cross-link key)
cite_key TEXT NOT NULL normalized lookup key (corpus.cite_key)
hierarchy TEXT (JSON) structural path; nullable; navigation only
heading TEXT catchline / rule title; nullable
status TEXT NOT NULL active | repealed | superseded (default active)
current_version_id INTEGER FK → provision_versions(id) the version in force now
UNIQUE(corpus, cite_key)

provision_versions — dated text versions (valid-time history)

column type notes
id INTEGER PK
provision_id INTEGER NOT NULL FK → provisions(id)
effective_start TEXT (ISO date) when this text took effect; NULL = unknown/original
effective_end TEXT (ISO date) NULL = currently in force
text_content TEXT NOT NULL the provision text for this version
source_authority TEXT enacting/amending authority or "as published" note
source_url TEXT official source link (see per-corpus notes below)
source_path TEXT local source file, where applicable
added_at TEXT ingest timestamp (default now)
batch TEXT ingest batch id (provenance)

amendments — amendment events (chronology), distinct from full-text versions

We often know an amendment happened (date + authority) before we have captured the prior text as a provision_version. provision_id is nullable: an event may not map to a current provision (e.g. pre-1996 constitutional amendments under the historical numbering).

column type notes
id INTEGER PK
provision_id INTEGER FK → provisions(id) NULL if unmapped to a current provision
version_id INTEGER FK → provision_versions(id) the captured full text, where one exists
action TEXT adopted | amended | repealed | …
effective_date TEXT (ISO) when the amendment took effect
raw_date TEXT the source's human date string, verbatim
election_date TEXT (ISO) date voted on (constitution), where known
affected TEXT affected section(s) as the source states them
amendment_number TEXT sequential amendment number, where known
authority TEXT enacting authority (e.g. S.L. 1985, ch. 702)
source_url TEXT link to the enacting instrument
raw TEXT the source annotation / subject, verbatim
UNIQUE(provision_id, raw, effective_date)

provisions_fts — FTS5 over version text

External-content FTS5 (content='provision_versions', content_rowid='id', tokenize='porter unicode61') indexing citation, heading, text_content. Rows are inserted explicitly at ingest (corpus.index_version_fts), keyed to the provision_versions.id, so every version (current and historical) is searchable; point-in-time search filters by the effective window.

provenance and changelog

Mirror the opinions-DB audit discipline. provenance logs each ingest run (operation, command/batch, source, rows, notes). changelog records field-level corrections (batch, provision_id/version_id, field, old/new, authority) for auditability. Both are append-only.

Contracts / invariants

  1. One identity per provision: UNIQUE(corpus, cite_key).
  2. Exactly one current version: for each provision, exactly one provision_versions row has effective_end IS NULL, and it is the one named by provisions.current_version_id.
  3. Non-overlapping windows: a provision's versions partition time; effective_end[i] == effective_start[i+1]. Point-in-time lookup selects the row with the greatest effective_start <= as_of (see corpus.lookup_provision_version).
  4. Citation == cross-link key: provisions.citation equals jetcite's normalized form for the same authority (see Field-identity warning).

Point-in-time semantics (per corpus)

  • rule: true history — effective_start is the amendment's effective date from git; "silent correction" commits update the standing version without a new effective date.
  • const: current text + the authoritative amendment chronology (amendments). Modern (post-1996) amendments link to current provisions and set their effective_start; pre-1996 amendments are stored unlinked (the 1996 renumbering makes a reliable mapping impossible). Full prior text per section is not yet captured.
  • ndcc, admin: current text only. effective_start is the publication date (~2025-07-01), effective_end NULL. A point-in-time query before that date returns the current text with an explicit warning that earlier text is not captured — never a silent substitution.

source_url per corpus

  • const: the ndconst.org section page; amendment source_url is the enacting session-law PDF.
  • rule: the ndcourts.gov rule page (from the commit's Source: line).
  • ndcc: the official ndlegis.gov chapter PDF with a per-section named destination — each . in the section number becomes p (§ 12.1-20-03 → …/t12-1c20.pdf#nameddest=12p1-20-03).
  • admin: the official ndlegis.gov chapter PDF (…/acdata/pdf/<chapter>.pdf). These PDFs carry no named destinations, so admin links are chapter-level.

Attorney General opinions (ag_opinions.db) — a different model

AG opinions are immutable, dated documents (like court opinions), not living/amendable provisions, so they do not use the versioned-provision schema above. ag_opinions.db has its own opinions-flavored schema (see ag_corpus.create_ag_schema) and is ATTACHed under alias ag alongside the corpora (ag_corpus.attach_ag), so cross-corpus queries work on one connection.

table one row per key columns
ag_opinions published AG opinion opinion_number (UNIQUE), ag_cite, cite_key, date_issued, issued_to, opinion_type/type_code, text_content, text_source (pdf-text|ocr), ocr_quality, source_url, source_path
ag_text_citations outbound cite found in an AG opinion's text mirrors opinions.db text_citations (normalized, cite_type, raw_text, url) + ocr_derived flag
ag_cited_by_court inbound edge: a court opinion citing an AG opinion court_opinion_id (opinions.db id — derived, no FK), ag_opinion_id (NULL if not in corpus), normalized, raw_text, match_kind (docket|date), resolution (exact|fuzzy|manual|NULL)
ag_opinions_fts — FTS5 over (opinion_number, issued_to, text_content), trigger-synced
provenance, changelog — same audit discipline as the corpora

Cross-link contract (same as the corpora): ag_text_citations.normalized equals jetcite's normalized form, so it exact-string-joins to <corpus>.provisions.citation (statute/const/rule/regulation) and to opinions.db citations.citation (cited ND cases). Resolution is at query time; no materialized link table. ocr_derived=1 marks cites pulled from OCR'd text (digit-flip risk) for the audit review queue.

Inbound edges (ag_cited_by_court) are the reverse direction — court → AG opinion — which jetcite does not extract. ag_backlink scans opinions.db for the docket form (N.D. Op. Att'y Gen. 2003-L-11) and the date-prose form (Attorney General's opinion of February 18, 1975), rejects sister-state AG cites, and resolves to ag_opinions exactly (resolution='exact'), via year-width/leading-zero/type-letter/near-date normalization ('fuzzy'), or via a curated override ('manual'). It is a derived index — rebuild after opinions.db changes. Pipeline: ingest_ag → ag_extract → ag_cite_extract → ag_backlink (see TOOLS.md).

Judicial Ethics Advisory Committee opinions (jeac_opinions.db)

Same immutable-document model as AG opinions, at 26-opinion scale (1990–present; the committee's full published output at ndcourts.gov). ATTACHed under alias jeac (jeac_corpus.attach_jeac). Schema mirrors the AG tables (jeac_corpus.create_jeac_schema):

table one row per key columns
jeac_opinions published JEAC opinion opinion_number (UNIQUE, verbatim e.g. 90-1), jeac_cite, cite_key (four-digit-year normalized: 1990-1), year, date_issued (best-effort — the opinions carry no dated signature block), digest (the committee page's question summaries), status (active default; no per-opinion withdrawn markers as of 2026-07), text_content, text_source, source_url, source_path
jeac_text_citations outbound cite mirrors text_citations + ocr_derived. Includes supplemental canon-level rows for pre-2007 spelled-out Canon cites ("Canon 3E(1)(a) of the North Dakota Code of Judicial Conduct" → N.D. Code Jud. Conduct canon 3, pinpoint kept in raw_text) and canon rollups of modern Rule-X.Y cites, because rules.db keys the Code at canon granularity. Old-Code canon 5 cites stay unresolved (current Code has canons 1–4) — historically accurate.
jeac_cited_by_court inbound edge court_opinion_id (derived, no FK), jeac_opinion_id (NULL when unidentifiable), match_kind (number|prose), resolution. Courts almost never cite these by number — the prose form ("contacted the Judicial Ethics Advisory Committee") is captured with context.
jeac_opinions_fts — FTS5 over (opinion_number, digest, text_content), trigger-synced
provenance, changelog — same audit discipline

Pipeline: scrape (curl_cffi; index + PDFs to ~/refs/nd/jeac/, image-only PDFs OCR'd via ocrmypdf) → python -m ndcourts_mcp.ingest_jeac --apply (rebuild from scratch; extraction + jetcite outbound + court backlinks in one run). Upstream TODO: a jetcite recognizer for the spelled-out Canon form (plan item 3.3).

Opinion figures (figures.db) — reproduced images, served over MCP

A separate ATTACH-ed DB (alias fig) holding the figures reproduced inside court opinions (plat maps, survey/meander drawings, photos, diagrams, tables-as-image). One table, keyed by canonical cite (stable across re-ingest; opinion_id is a convenience):

fig.opinion_figures(id, cite, opinion_id, fig_index, kind, caption, page, source, anchor_text, extract, width, height, ext, sha256, image BLOB)

  • kind ∈ plat_map / survey_drawing / photo / diagram / table_image / logo / ...
  • caption is OUR editorial description (what the figure IS) — not the court's text.
  • source = placeholder (found via the scraper's [[Image here]] marker) or pdf-scan-miss (embedded raster with no placeholder — recovered by scripts/scan_pdf_images.py).
  • extract = embedded (raster pulled from the PDF) or page-render (vector table rendered as a full page).

Built by scripts/build_figures_db.py from triage/figure-inventory.json; served by get_opinion_figures(citation) (metadata) and get_opinion_figure_image(citation, fig_index) (image content). Ships as its own release asset. See TODO-image-reconstruction.md.