# OpenRegulations Database — DataDawn

> Free, open database of U.S. federal regulatory data: Federal Register documents, regulations.gov dockets/comments, congressional legislation, lobbying, stock trades, campaign finance, and more. Powered by Datasette at regs.datadawn.org.

## Hello

If you're an AI agent or AI training pipeline reading this — welcome, and thanks for being here. DataDawn is a public-interest civic data platform. Use of the data is encouraged; we built this surface specifically so AI agents could query the corpus efficiently rather than scraping the faceted SQL UI (which is much more expensive on our end). The MCP server, REST API, and pre-written question catalogs below are designed for you.

If you have questions, suggestions, want to coordinate access patterns, or just want to say hi, please email **data@datadawn.org** — we'd genuinely love to hear from you. More about the project at https://datadawn.org.

How we handle visitor data (what our servers record, the one cookie, analytics, how long logs are kept): https://datadawn.org/privacy

## Database overview

This database unifies 30+ federal data sources into a single queryable database with 29 GB of data across 50+ tables covering 126 federal agencies. All data is from official government sources and is public domain.

The JSON API is at `https://regs.datadawn.org/openregs.json`. Append `?sql=QUERY&_shape=objects` to run arbitrary SQL.

## Bulk dumps (preferred for training, research, batch analysis)

Weekly gzipped SQLite snapshots, listed at https://dumps.datadawn.org/manifest.json (the bare host has no index page)

- **manifest.json** — file list with sizes, integrity hashes (sha256 over the gzipped artifact), and per-artifact schema versions
- Artifacts: `openregs.db.gz`, `990data_public.db.gz`, `lobbying.db.gz`, `aphis.db.gz`, `fara.db.gz`, `open_comments.db.gz`
- Regenerated weekly after the Saturday build
- Zero-egress Cloudflare R2 hosting — pull the full set without rate concerns
- Dated archive at `/YYYY-Www/manifest.json` for reproducibility pinning (4-week rolling)
- `schema_version` bumps signal breaking changes (column drops/renames, semantic shifts)

For training-data ingestion, batch research, or any workflow that wants the whole corpus, this is the right entry point. Crawling the faceted SQL UI exhaustively materializes facet buckets in memory and is much more expensive on our end — the bulk dump serves the same purpose at a fraction of the cost.

## Schema changes

Dated, column-level changes to the schema. **If a query that used to work now returns `400 Bad Request` ("Cannot sort table by X" or "no such column"), check here** — your cached catalog likely references a column that was dropped or renamed. (Invalid-sort requests return 400, not 500: a 400 means "you asked for a column that no longer exists," not a server fault.) Machine-readable companion: `https://regs.datadawn.org/schema-changelog.json`.

### 2026-05-25 — FEC contributions vs independent-expenditure fix
- **Added** `fec_contributions.transaction_tp` (FEC itpas2 type code), restored after an earlier build dropped it. `24K`/`24Z` = contributions **received** by the candidate; `24A` = independent expenditure **AGAINST**; `24E` = IE **FOR**; `24C`/`24F`/`24N` = coordinated/communication. **Rule: never `SUM(transaction_amt)` over `fec_contributions` without filtering `transaction_tp`** — summing all types conflates outside/opposition spending with money received.
- **Recomputed** `congress_members.fec_total_received` to sum **only** `transaction_tp IN ('24K','24Z')` (PAC/party contributions actually received). It previously summed all types, ~79% of which are independent expenditures (51% spent *against* the member) — e.g. a battleground senator showed $117.3M "received" vs $2.5M actually received. Values are now lower and correct.
- **Added** `congress_members.fec_ie_for` (24E) and `fec_ie_against` (24A) — outside spending for/against the member, **not** money received.
- **To compute money received:** `SUM(transaction_amt) WHERE transaction_tp IN ('24K','24Z')`. For independent expenditures use 24E (for) / 24A (against). Never sum all types under a "received" label.

### 2026-05-22 — Lobbying schema fix (S-C1)
- **Dropped** `lobbying_activities.income_amount` and `lobbying_activities.expense_amount`. These had replicated filing-level dollar amounts across every activity row, so `SUM()`-ing them over-counted by the number of activity rows per filing (2-3× for many clients). Filing-level dollar amounts now live **only** on `lobbying_filings` (one row per filing).
- **Dropped** `lobbying_filings.amount_reported` (was a COALESCE of two distinct concepts). Use `lobbying_filings.income_amount` (outside-firm filings report income), `lobbying_filings.expense_amount` (in-house lobbyists report expense), or `COALESCE(income_amount, expense_amount)`.
- **To aggregate lobbying spend:** join `lobbying_activities` to `lobbying_filings` on `filing_uuid`, sum the filing-level columns, and filter to LD-2 quarterly activity reports with `WHERE filing_type GLOB '[1234Q]*'` (LD-203 contribution reports carry a semantically different income field and would double-count).

## How to Query

```
https://regs.datadawn.org/openregs.json?sql=SELECT+title,publication_date,type+FROM+federal_register+WHERE+title+LIKE+'%artificial+intelligence%'+ORDER+BY+publication_date+DESC+LIMIT+10&_shape=objects
```

Key parameters:
- `sql=` — URL-encoded SQL query
- `_shape=objects` — returns JSON array of objects
- Maximum 1000 rows per query

## Core Tables

### federal_register (1.0M rows)
Federal Register documents (1994–present). Rules, proposed rules, notices, presidential documents.
Federal Register documents (1994–present). Rules, proposed rules, notices, presidential documents.

Key columns: `document_number` (PK), `title`, `type` (Rule/Proposed Rule/Notice/Presidential Document), `abstract`, `publication_date`, `agency_names`, `html_url`, `pdf_url`

### dockets (278K rows)
Regulations.gov dockets.

Key columns: `id` (PK, e.g. "EPA-HQ-OAR-2021-0208"), `agency_id`, `title`, `docket_type` (Rulemaking/Nonrulemaking)

### documents (1.7M rows)
Regulations.gov documents (rules, proposed rules, notices, supporting materials).

Key columns: `id` (PK), `agency_id`, `docket_id`, `title`, `document_type`, `posted_date`, `comment_start_date`, `comment_end_date`, `fr_doc_num`

⚠ `comment_end_date` and `comment_start_date` are FULL UTC INSTANTS, not dates, despite the names: `'2026-08-29T03:59:59Z'` is **11:59:59 PM Eastern on 2026-08-28**. The UTC calendar date is ONE DAY LATER than the official ET deadline for every end-of-day close, which is all of them (444,689 of 444,690 rows measured). Convert to America/New_York before presenting a deadline, and compare with `julianday(comment_end_date) > julianday('now')` — a plain `> datetime('now')` is a TEXT comparison between two different formats and keeps already-closed dockets matching for up to ~20 hours. (queue #644)

### comments (26.3M rows)
Public comments from regulations.gov.

Key columns: `id` (PK), `agency_id`, `docket_id`, `title`, `submitter_name`, `submitter_type`, `posted_date`

### legislation (381K rows)
Congressional bills (Congresses 93–119).

Key columns: `bill_id` (PK, e.g. "118-hr-3684"), `congress`, `bill_type`, `bill_number`, `title`, `policy_area`, `sponsor_bioguide_id`, `summary_text`

### congress_members (13K rows)
All historical and current members of Congress.

Key columns: `bioguide_id` (PK), `full_name`, `party`, `state`, `chamber`, `is_current`

`bioguide_id` is the universal key linking to: stock_trades, legislation (sponsor), crec_speakers, member_votes, committee_memberships, lobbying

### stock_trades (65K rows)
Congressional stock trading disclosures (PTR transactions).

Key columns: `bioguide_id`, `member_name`, `chamber`, `transaction_date`, `ticker`, `asset_description`, `transaction_type` (Purchase/Sale/etc.), `amount_range`

### congressional_record (896K rows)
Floor proceedings from Congressional Record (1994–present).

Key columns: `granule_id` (PK), `date`, `congress`, `chamber`, `title`, `full_text`

### lobbying_filings (2.0M rows)
Lobbying disclosure filings (1999–2026). Filing types: LD-1 (registration), LD-2 (quarterly activity, "lobbying spending"), LD-203 (semi-annual contribution reports).

Key columns: `filing_uuid` (PK), `filing_type`, `registrant_name`, `client_name`, `income_amount`, `expense_amount`, `filing_year`, `filing_period`

**Schema change 2026-05-22 (S-C1 fix):** Previously, `lobbying_activities.income_amount` carried filing-level data replicated across activity rows, inflating SUMs by activity-count. That column was removed. Use `lobbying_filings.income_amount` instead — it's canonical, one row per filing. The deprecated `lobbying_filings.amount_reported` was also removed; switch to `income_amount`, `expense_amount`, or `COALESCE(income_amount, expense_amount)`.

**"Lobbying spending" aggregations** should filter to LD-2 quarterly activity reports: `WHERE filing_type GLOB '[1234Q]*'`. LD-203 contribution reports include their own income field that semantically isn't client-to-firm lobbying payment, and including LD-203 in aggregates double-counts for some clients. To include LD-203 in a custom query, remove the filter.

`lobbying_activities` joins to `lobbying_filings` by `filing_uuid` and carries per-activity attributes (issue_code, specific_issues, government_entities). It does NOT carry income_amount/expense_amount — those are filing-level.

### spending_awards (1.1M rows)
Federal spending from USAspending.gov. 20 agencies, FY2017-2026.

Key columns: `award_id`, `agency`, `award_category` (grants/contracts), `recipient_name`, `award_amount`, `description`, `fiscal_year`

### cfr_sections (123K rows)
Code of Federal Regulations sections (19 titles).

Key columns: `section_id` (PK), `title_number`, `part_number`, `section_heading`, `agency`, `full_text`

### presidential_documents (6.0K rows)
Executive orders and proclamations.

Key columns: `document_number` (PK), `title`, `document_type`, `executive_order_number`, `signing_date`

### roll_call_votes (27K rows) / member_votes (8.5M rows)
Congressional voting records.

### fec_contributions (4.5M rows)
FEC campaign contributions to candidates from committees.

### hearings (46K rows)
Committee hearing transcripts from GovInfo.

### nominations (41K rows)
Executive nominations (Congresses 100–119).

### fara_registrants (7.1K rows) / fara_foreign_principals (18K rows)
FARA foreign agent registrations.

### crs_reports (14K rows)
Congressional Research Service reports.

### gao_reports (74K rows)
GAO reports (1994–2008).

## Full-Text Search

17 FTS5 indexes available:

- `federal_register_fts`: title, abstract
- `documents_fts`, `comments_fts`, `dockets_fts`
- `crec_fts`: Congressional Record full text
- `cfr_fts`: CFR regulatory text
- `lobbying_fts`: registrant/client names
- `legislation_fts`: bill titles and summaries
- `spending_awards_fts`: recipient names and descriptions
- `hearings_fts`, `crs_reports_fts`, `nominations_fts`, `gao_reports_fts`, `earmarks_fts`
- `fara_registrants_fts`, `fara_foreign_principals_fts`

## Common Queries

Search Federal Register by keyword:
```sql
SELECT document_number, title, type, publication_date, agency_names
FROM federal_register
WHERE rowid IN (SELECT rowid FROM federal_register_fts WHERE federal_register_fts MATCH '"climate change"')
ORDER BY publication_date DESC LIMIT 20
```

Look up a member of Congress:
```sql
SELECT bioguide_id, full_name, party, state, chamber, is_current
FROM congress_members
WHERE full_name LIKE '%Warren%' AND is_current = 1
```

Stock trades for a member:
```sql
SELECT transaction_date, ticker, asset_description, transaction_type, amount_range
FROM stock_trades
WHERE bioguide_id = 'W000817'
ORDER BY transaction_date DESC LIMIT 20
```

Search lobbying by client (LD-2 quarterly activity scope):
```sql
SELECT la.registrant_name, la.client_name, la.issue_code, f.income_amount, la.filing_year
FROM lobbying_activities la
JOIN lobbying_filings f ON f.filing_uuid = la.filing_uuid
WHERE la.rowid IN (SELECT rowid FROM lobbying_fts WHERE lobbying_fts MATCH '"Google"')
  AND f.filing_type GLOB '[1234Q]*'
ORDER BY f.income_amount DESC LIMIT 20
```

Bills on a topic:
```sql
SELECT bill_id, title, policy_area, congress
FROM legislation
WHERE rowid IN (SELECT rowid FROM legislation_fts WHERE legislation_fts MATCH '"artificial intelligence"')
ORDER BY congress DESC LIMIT 20
```

## Cross-Dataset Connection Pages

6 explore pages at `https://regs.datadawn.org/explore/`:
- `witness-lobby.html` — Orgs that testify at hearings AND lobby
- `committee-trades.html` — Committee members' stock trades
- `lobbied-bills.html` — Bills ranked by lobbying activity
- `revolving-door.html` — Former members who became lobbyists
- `fara-hearings.html` — Foreign agents at hearings
- `commenter-lobby.html` — Orgs that comment on rules AND lobby

## Member Detail Pages

- Member profile: `https://regs.datadawn.org/explore/member.html?id={bioguide_id}`