Schema
Complete reference for the database schemas.
The database is organized into two main schemas:
opinions- Court opinions and caselaw datalaws- Statutes and regulations
Opinions Schema
The opinions schema contains normalized court opinion data. All tables are in the opinions schema and can be joined using foreign key relationships.
Entity Relationship
courts
└── cases
└── docket_entries
├── citations
└── content
opinion_relations (cross-references between opinions)
Opinions Tables
opinions.courts
Court definitions for federal, state, territorial, tribal, military, and international jurisdictions. Use in_use to distinguish courts that are actively covered.
| Column | Type | Description |
|---|---|---|
id 🔑 |
VARCHAR(50) | Court identifier (e.g., 'ca9', 'nysd', 'scc') |
full_name |
TEXT | Full court name |
court_abbreviation |
VARCHAR(100) | Citation abbreviation |
jurisdiction |
VARCHAR(50) | Federal Appellate, Federal District, Federal Bankruptcy, Federal Special, State Supreme, State Appellate, State Trial, State Special, State Attorney General, Military, Military Appellate, Tribal, U.S. Territory, Committee, International |
state |
VARCHAR(50) | State/province name, 'Federal', 'Tribal', or territory name |
country |
VARCHAR(2) | ISO 3166-1 alpha-2 country code (US, CA, etc.) |
in_use |
BOOLEAN | Whether court is actively scraped |
opinions.cases
Case metadata including docket information and dates.
| Column | Type | Description |
|---|---|---|
id 🔑 |
TEXT | Internal case ID |
court_id → |
VARCHAR(50) | Court identifier |
docket_number |
TEXT | Court docket number |
case_name |
TEXT | Case name/caption |
date_filed |
DATE | Date case was filed |
date_terminated |
DATE | Date case was terminated |
data_origin |
VARCHAR(25) | Source of the data |
opinions.docket_entries
Opinion entries (where is_opinion = true). Each row represents a court opinion.
| Column | Type | Description |
|---|---|---|
id 🔑 |
TEXT | Opinion identifier |
case_id → |
TEXT | Parent case |
description |
TEXT | Opinion description |
date_filed |
DATE | Opinion filing date |
judge_name |
TEXT | Authoring judge name |
is_opinion |
BOOLEAN | Whether this is an opinion (true for opinions) |
opinion_role |
ENUM | Role of opinion (lead, concurrence, dissent, etc.) |
publish_status |
ENUM | Publication status |
data_origin |
VARCHAR(25) | Source of the data |
opinions.citations
Reporter citations for opinions.
| Column | Type | Description |
|---|---|---|
id 🔑 |
UUID | Internal citation ID |
opinion_id → |
TEXT | Parent opinion |
court_id → |
VARCHAR(255) | Court identifier |
volume |
VARCHAR(20) | Reporter volume number |
reporter |
VARCHAR(50) | Reporter abbreviation (e.g., 'U.S.', 'F.3d') |
page |
VARCHAR(20) | Starting page number |
cited_as |
TEXT | Full citation string |
citation_type |
VARCHAR(50) | Type of citation |
year |
INTEGER | Year of citation |
normalized |
VARCHAR(150) | Normalized citation format |
opinions.content
Full text content for opinions.
| Column | Type | Description |
|---|---|---|
id 🔑 |
TEXT | Opinion ID (same as docket_entries.id) |
html_content |
TEXT | Opinion text in HTML format |
source |
VARCHAR(50) | Source of content |
created_at |
TIMESTAMP | When content was added |
opinions.opinion_relations
Citation relationships and treatment analysis between opinions.
| Column | Type | Description |
|---|---|---|
cited_id → |
TEXT | The opinion being cited |
citing_id → |
TEXT | The opinion doing the citing |
citator_version |
TEXT | Version of citator analysis |
is_authoritative |
BOOLEAN | Whether this is an authoritative citation |
treatment_category |
TEXT | How the citing opinion treats the cited opinion |
treatment_description |
TEXT | Detailed treatment explanation |
supporting_quote |
TEXT | Quote from citing opinion supporting the treatment |
confidence_score |
DOUBLE | Confidence score of the treatment analysis |
🔑 = Primary Key, → = Foreign Key
Laws Schema
The laws schema contains statutes (codified laws), regulations (administrative rules), and constitutions. This schema uses a unified document structure with hierarchical support and versioning.
Entity Relationship
collections (uscode, cfr, state codes)
└── documents (hierarchical: title > chapter > section)
└── version_relations (lifecycle edges between document versions)
Laws Tables
laws.collections
Content collections (e.g., U.S. Code, CFR, state statutes and regulations).
| Column | Type | Description |
|---|---|---|
id 🔑 |
VARCHAR(50) | Collection identifier (e.g., 'uscode', 'cfr') |
name |
VARCHAR(200) | Full name (e.g., 'United States Code') |
type |
VARCHAR(30) | Content type: statute, regulation, constitution, guidance, executive_order, or notice |
authority_name |
VARCHAR(200) | Publishing authority (e.g., 'United States', 'California') |
state |
VARCHAR(50) | Jurisdiction name (e.g., 'Federal', 'California', 'New York') |
country |
VARCHAR(2) | ISO 3166-1 alpha-2 country code |
created_at, updated_at |
TIMESTAMPTZ | Record timestamps |
deleted_at |
TIMESTAMPTZ | Soft-deletion timestamp |
Use GET /api/v1/laws/collections for the current machine-readable list, or
the live coverage overview for a human-readable view.
laws.documents
Unified table for hierarchical legal content (statutes, regulations, constitutions).
| Column | Type | Description |
|---|---|---|
id 🔑 |
UUID | Document identifier |
collection_id → |
VARCHAR(50) | Parent collection (e.g., 'uscode', 'cfr') |
parent_id → |
UUID | Parent document node |
path |
LTREE | Hierarchical path for tree queries (e.g., 'uscode.t5.ch1.s101') |
sort_order |
INTEGER | Order within parent |
node_type |
VARCHAR(20) | Structural type: PARENT, LEAF, or TOMBSTONE |
number |
VARCHAR(50) | Section/chapter number (e.g., '5', 'I', '101', '(a)') |
heading |
TEXT | Section heading/title |
citation_canonical |
VARCHAR(200) | Canonical citation (e.g., '5 U.S.C. § 552', '26 C.F.R. § 1.61-1') |
effective_date |
DATE | When this version became effective |
publication_date |
DATE | Source-stated publication date |
source_as_of |
DATE | The source's own as-of or edition date |
is_historical |
HISTORICAL_STATUS | YES when the source marks this version superseded; otherwise SCRAPER_DIDNT_SAY |
html_content |
TEXT | HTML formatted content |
content_hash |
VARCHAR(64) | Hash used to detect content changes |
source_law |
VARCHAR(500) | Public law reference for statutes (e.g., 'Pub. L. 118-100') |
agency_name |
VARCHAR(200) | Agency name for regulations (e.g., 'Environmental Protection Agency') |
agency_code |
VARCHAR(100) | Agency code for regulations (e.g., 'EPA') |
agencies |
JSONB | Structured agency metadata, when supplied by the source |
register_citation |
TEXT | Federal or state register citation (e.g., '89 FR 12345') |
source_id |
VARCHAR(200) | External source identifier |
source_url |
TEXT | Official source URL |
gcs_path |
VARCHAR(500) | GCS path to original source file |
first_scraped_at, last_scraped_at |
TIMESTAMPTZ | Observation timestamps |
created_at, updated_at |
TIMESTAMPTZ | Record timestamps |
deleted_at |
TIMESTAMPTZ | Soft-deletion timestamp |
Currency is derived at query time from the document and its lifecycle edges;
there is no stored is_current, status, end_date, or supersedes_id column.
laws.version_relations
Lifecycle and lineage edges between document versions. Edges point from the
newer or terminal-marker row (from_id) to its predecessor (to_id). Dates
remain on the document rows rather than the edge.
| Column | Type | Description |
|---|---|---|
id 🔑 |
UUID | Relation identifier |
from_id → |
UUID | Newer version or lifecycle marker |
to_id → |
UUID | Predecessor version |
relation_type |
VARCHAR(30) | succeeds, transferred, removed, or repeals |
created_at |
TIMESTAMPTZ | Edge creation timestamp |
🔑 = Primary Key, → = Foreign Key