Schema

Complete reference for the database schemas.

The database is organized into three schemas:

• cases - Court opinions and caselaw data (current)
• opinions - The previous shape of the same data. Deprecated — see Migrating from opinions to cases
• laws - Statutes and regulations

cases is where new work should go. It carries everything opinions carried, plus the full docket rather than opinions alone: roughly 17 million additional docket entries and their document metadata. opinions continues to work unchanged during the overlap window and will be withdrawn on a date we will announce with notice.

Cases Schema

The cases schema is the current shape of our caselaw data. Where opinions carried opinions only, cases carries the whole docket of every case it publishes — motions, orders, notices and the rest — along with document metadata for each entry.

Two structural differences are worth reading before you write queries:

1. An opinion and a docket entry are now separate rows. opinions.docket_entries was one wide table; it is now cases.docket_entries joined to cases.opinions on docket_entries.opinion_id = opinions.id. An entry with a NULL opinion_id is a non-opinion docket entry — the new corpus.
2. Text is keyed by document, not by opinion. opinions.content became cases.documents ⋈ cases.documents_content, joined on documents_content.id = documents.id.

Every table carries is_promoted, which is always true in your replica — it is the flag that determines what reaches you at all. You can ignore it; it is present because our replication requires it.

Every table also carries deleted_at. Rows are not removed when deleted; they are marked. Filter WHERE deleted_at IS NULL for live rows. See Change Tracking for the one case this does not cover.

Entity Relationship

courts
  └── cases
        └── docket_entries ──── opinions        (entries with an opinion_id)
              └── documents
                    └── documents_content

opinions
  └── citations

document_relations (citation graph)
  └── treatments   (how the citing document treats the cited one)

Cases Tables

cases.courts

Court definitions for federal, state, territorial, tribal, military, and international jurisdictions. Use in_use to distinguish courts that are actively covered.

pacer_court_id and freelaw_court_id are the join keys if you also license PACER/RECAP or CourtListener data.

cases.cases

One row per case.

cases.docket_entries

One row per document filed in a case. This is the table that grew: it now includes non-opinion filings.

Two things to know:

• opinion_id IS NOT NULL is how you identify an opinion. This replaces the old is_opinion boolean.
• opinion_id is not unique. One opinion can be reached from several entries in several cases. If your code assumes one row per opinion, it needs a DISTINCT or a join through cases.opinions.

The primary key is (case_id, id).

cases.opinions

The opinion-specific attributes of a docket entry.

cases.documents

Document metadata for a docket entry.

cases.documents_content

Full text, keyed by document.

cases.citations

Citations extracted from opinions.

cases.document_relations

The citation graph: one row per citing→cited edge. Where the old schema put the edge and its treatment in one table, the edge lives here and the treatment in cases.treatments.

citing_table and cited_table are opinion for every edge today. They exist so that citations to statutes and regulations can be added later without changing the shape of this table.

cases.treatments

How a citing document treats the document it cites. Joins to document_relations on relation_id = document_relations.id.

🔑 = Primary Key, → = Foreign Key

Opinions Schema

Deprecated. Superseded by the cases schema. Still live and still maintained during the overlap window; no date is set yet. The mapping between the two is in Migrating from opinions to cases.

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.

opinions.cases

Case metadata including docket information and dates.

opinions.docket_entries

Opinion entries (where is_opinion = true). Each row represents a court opinion.

opinions.citations

Reporter citations for opinions.

opinions.content

Full text content for opinions.

opinions.opinion_relations

Citation relationships and treatment analysis between opinions.

🔑 = Primary Key, → = Foreign Key

Migrating from opinions to cases

Everything opinions gave you is available in cases. Most of the work is mechanical; three changes need a real edit.

Opinion IDs are unchanged. cases.opinions.id holds exactly the value opinions.docket_entries.id held, so any mapping you keep keyed on opinion id survives the move untouched. Document, citation and relation IDs are new.

The three real edits

1. is_opinion → opinion_id IS NOT NULL.

-- before
SELECT * FROM opinions.docket_entries WHERE is_opinion = true;

-- after
SELECT * FROM cases.docket_entries WHERE opinion_id IS NOT NULL;

2. Opinion text now joins through documents.

-- before
SELECT de.id, c.html_content
  FROM opinions.docket_entries de
  JOIN opinions.content c ON c.id = de.id
 WHERE de.is_opinion = true;

-- after
SELECT de.opinion_id, dc.html_content
  FROM cases.docket_entries de
  JOIN cases.documents          d  ON d.docket_entry_id = de.id
  JOIN cases.documents_content  dc ON dc.id = d.id
 WHERE de.opinion_id IS NOT NULL
   AND de.deleted_at IS NULL;

3. Treatments join through the edge.

-- before
SELECT citing_id, cited_id, treatment_category, is_authoritative
  FROM opinions.opinion_relations;

-- after
SELECT r.citing_id, r.cited_id, t.treatment_category, r.is_authoritative
  FROM cases.document_relations r
  LEFT JOIN cases.treatments t ON t.relation_id = r.id;

Use LEFT JOIN: an edge can exist without a treatment.

Two things that are not carried over

Citation counts. The old surface exposed a precomputed citation_counts view. It is not part of cases; compute it directly:

SELECT cited_id, count(*) AS citation_count
  FROM cases.document_relations
 WHERE deleted_at IS NULL
 GROUP BY cited_id;

If that aggregate is on a hot path for you, tell us — we would rather know than have you discover the cost in production.

_active views. There are none, in either schema. Filter WHERE deleted_at IS NULL yourself, as you do today.

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).

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).

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.

🔑 = Primary Key, → = Foreign Key