Correlation — bills ↔ committees ↔ members

Commands

# Step 1: parse cached HTML into the flat table (parser lane — already done)
python -m scraper parse --input cache/raw --db db/daily_file.sqlite

# Step 2: create the relational schema (idempotent)
python -m scraper migrate --db db/daily_file.sqlite

# Step 3: derive bill<->committee<->member relations from the flat table
python -m scraper correlate --db db/daily_file.sqlite

# Optional: backfill committee membership from a roster JSON file
python -m scraper correlate --db db/daily_file.sqlite --roster rosters.json

# Or: one-shot — migrate then correlate in process order
python -m scraper migrate --db db/daily_file.sqlite && \
   python -m scraper correlate --db db/daily_file.sqlite

What correlate derives

Source column / field	Target table	Rule
`bill_id` (any row)	`bills`	One bill row per unique `(bill_id, session, chamber_origin)`
`committee` on hearing / committee-bill rows	`committees`	One committee row per unique slug
`author LIKE 'Committee on …'`	`committees`	Strips the "Committee on " prefix; same slug rules
`bill_id AND committee` non-null	`bill_committees`	The "reviewed by" edge: bill was acted on by this committee
`author` = "Name" or "Name et al." (not committee)	`members`	`kind = 'author-seed'`; real legislator name, never a guess
roster JSON `members[]`	`committee_members`	`kind = 'roster'`; only when a roster is supplied

Never-fabricate rule

committee_members is populated only from a supplied roster file (--roster rosters.json).
When a committee seen in the daily file has no roster, it is still created in committees but its membership is left empty.
A WARN is logged for each committee missing a roster.
No member is ever invented. test_committee_without_roster_has_no_members asserts COUNT(*) = 0 for a committee with no roster.

The roster JSON format:

[
  {
    "committee": "Emergency Management",
    "session": "2025-2026",
    "chamber": "senate",
    "members": [
      {"name": "Perez", "role": "Chair"},
      {"name": "Davis", "role": "Member"}
    ]
  }
]

Roster keying

load_rosters() returns a dict keyed by (slug, session, chamber) — three fields, not just the slug — so a committee appearing in two different (session, chamber) contexts keeps separate membership sets instead of clobbering.

_ingest_relations() builds a roster_slugs set from that dict. A committee (slug, session, chamber) slot is reported "missing a roster" when no roster block covers that slug at all, regardless of (session, chamber). This allows one roster file to backfill multiple slots for the same slug.

Idempotency

Operation	Idempotency mechanism
`migrate()`	All DDL is `CREATE … IF NOT EXISTS`;
	`schema_migrations` uses `INSERT OR IGNORE`
`correlate()`	All `INSERT` is `INSERT OR IGNORE` on natural
	keys; re-running produces zero new rows
`--rebuild`	`DELETE FROM` all 5 derived tables, then re-
	correlate — a clean deterministic rebuild

Roster-driven slot matching

The chamber axis defaults to chamber_origin (for bill rows, the origin house of the bill) and falls back to house (the publishing chamber) when chamber_origin is absent. This matters for hearing rows, which have chamber_origin = NULL but house = 'assembly' — without the fallback the hearing slot would get chamber = 'unknown' and never match a roster entry with chamber = 'assembly'.

CLI registration

scraper/cli.py is a shared hotspot co-owned by the ingestion lane (ingest/list/check) and the parser lane (parse). This lane registers migrate and correlate additively, through scraper/relate_cli.py::register() called in a try/except block in cli.py::build_parser() — exactly the same guarded pattern that parse_cli.py::register() uses. The new subcommand names (migrate, correlate) do not collide with any sibling owner.

4.3 KiB Raw Permalink Blame History