API documentation

https://regs.datadawn.org/openregs

curl "https://regs.datadawn.org/openregs.json?sql=SELECT+bioguide_id,full_name,party,state,chamber+FROM+congress_members+WHERE+full_name+LIKE+'%25Pelosi%25'&_shape=objects"

curl "https://regs.datadawn.org/openregs.json?sql=SELECT+id,agency_id,title,comment_end_date+FROM+documents+WHERE+comment_end_date>datetime('now')+AND+withdrawn=0+ORDER+BY+comment_end_date+LIMIT+10&_shape=objects"

curl "https://regs.datadawn.org/openregs.json?sql=SELECT+client_name,SUM(income_amount)+AS+total+FROM+lobbying_activities+GROUP+BY+client_name+ORDER+BY+total+DESC+LIMIT+10&_shape=objects"

{
  "ok": true,
  "rows": [
    {"bioguide_id": "P000197", "full_name": "Nancy Pelosi", "party": "Democrat", ...},
    ...
  ],
  "truncated": false
}

congress_members.bioguide_id
  ├── stock_trades.bioguide_id          (trades)
  ├── crec_speakers.bioguide_id         (speeches)
  ├── legislation.sponsor_bioguide_id   (sponsored bills)
  ├── legislation_cosponsors.bioguide_id (cosponsored bills)
  ├── committee_memberships.bioguide_id  (committees)
  └── member_stats.bioguide_id          (pre-computed counts)

legislation.bill_id
  ├── legislation_cosponsors.bill_id   (cosponsors)
  ├── legislation_subjects.bill_id     (policy subjects)
  ├── legislation_actions.bill_id      (action timeline)
  └── crec_bills.bill_id              (floor speeches about this bill)
        └── congressional_record.granule_id (speech text)

dockets.id
  ├── documents.docket_id              (regulatory documents)
  │     └── fr_regs_crossref.regs_document_id → federal_register (FR links)
  └── comments.docket_id              (public comments)

documents.comment_end_date             (open comment periods: WHERE > datetime('now'))

lobbying_activities.filing_uuid
  ├── lobbying_lobbyists.filing_uuid   (who lobbied)
  │     └── covered_position           (revolving door: former gov't roles)
  └── lobbying_issue_codes.code = lobbying_activities.issue_code

Group by client_name for client view, registrant_name for firm view

SELECT d.id, d.title, d.agency_id
FROM dockets_fts JOIN dockets d ON d.rowid = dockets_fts.rowid
WHERE dockets_fts MATCH '"clean water"'
ORDER BY rank LIMIT 20

-- AND (default): both terms must appear
MATCH 'climate change'

-- OR: either term
MATCH 'climate OR environment'

-- NOT: exclude a term
MATCH 'emissions NOT carbon'

-- Phrase: exact phrase match
MATCH '"net neutrality"'

SELECT c.id, c.title, c.submitter_name, c.docket_id, c.posted_date
FROM comments_fts JOIN comments c ON c.rowid = comments_fts.rowid
WHERE comments_fts MATCH '"endangered species"'
ORDER BY rank LIMIT 20

SELECT cm.full_name, cm.party, cm.state, st.ticker,
    COUNT(*) AS trades, c.name AS committee
FROM stock_trades st
JOIN congress_members cm ON cm.bioguide_id = st.bioguide_id
LEFT JOIN committee_memberships cmm ON cmm.bioguide_id = cm.bioguide_id
LEFT JOIN committees c ON c.committee_id = cmm.committee_id
WHERE st.ticker = 'MSFT'
GROUP BY cm.bioguide_id, c.committee_id
ORDER BY trades DESC LIMIT 20

-- Lobbying spending
SELECT client_name, SUM(income_amount) AS total
FROM lobbying_activities WHERE client_name LIKE '%Boeing%'
GROUP BY client_name;

-- Federal contracts
SELECT recipient_name, SUM(award_amount) AS total, COUNT(*) AS awards
FROM spending_awards WHERE recipient_name LIKE '%Boeing%'
GROUP BY recipient_name;

-- Regulations mentioning them
SELECT d.id, d.title, d.agency_id FROM dockets_fts
JOIN dockets d ON d.rowid = dockets_fts.rowid
WHERE dockets_fts MATCH 'Boeing' LIMIT 10;

SELECT l.bill_id, l.title, l.policy_area,
    COUNT(DISTINCT cb.granule_id) AS floor_mentions
FROM crec_bills cb
JOIN legislation l ON l.bill_id = cb.bill_id
GROUP BY cb.bill_id
ORDER BY floor_mentions DESC LIMIT 20

SELECT la.client_name, ll.lobbyist_name, ll.covered_position, la.filing_year
FROM lobbying_lobbyists ll
JOIN lobbying_activities la ON la.filing_uuid = ll.filing_uuid
WHERE ll.covered_position IS NOT NULL AND ll.covered_position != ''
  AND la.client_name LIKE '%Pfizer%'
ORDER BY la.filing_year DESC