cfahlgren1/sf_criminal_court

San Francisco Criminal Court Data

Linked criminal-court records for San Francisco County, combining scraped Superior Court docket data, District Attorney open-data feeds, and a charge-disposition spreadsheet that the Court produced only after sustained pressure under California Rules of Court rule 10.500.

Tables

Data Sources

`cases`, `calendar`, `attorneys`, `register_of_actions` — Scraped from San Francisco Superior Court's public criminal case pages. These include public case numbers, court case IDs, defendant names, filing dates, hearing schedules, listed attorneys, and register-of-actions docket entries.

`da_arrests` — San Francisco District Attorney arrest/open-data feed. Fields: incident number, court number, arrest date, arresting agency, booked charges, booked case type, crime type, DV flag, DA action taken, and data freshness timestamps.

`da_prosecuted` — San Francisco District Attorney prosecuted-cases/open-data feed. Fields: incident number, court number, arrest date, filed charges, filed case type, crime type, DV flag, DA action taken, case status, and data freshness timestamps.

`sfsc_case_matches` and `sfsc_charge_dispositions` — Derived from SFSC_CHARGE_DISPOSITION.xlsx, a charge/disposition spreadsheet produced by San Francisco Superior Court in response to a California Rules of Court rule 10.500 records request.

This was not a public bulk download or an ordinary open-data release. The Court declined to provide public case numbers — the same case numbers it publishes on its own website, on individual case pages, accessible to anyone with a browser. Instead, after extended back-and-forth in which the Court appeared to treat a routine records-access rule as an unreasonable imposition, it supplied anonymized case identifiers. The result: public-interest researchers receive a spreadsheet that is artificially harder to join to other public records, for no apparent reason beyond institutional reluctance to make bulk access straightforward.

sfsc_case_matches was generated locally by matching the anonymized Court-provided spreadsheet back to public court and DA data. The match key is filed_date + normalized charge multiset, accepted only when that composite key is unique on both sides. This is labeled "deterministic-inferred" rather than simply "deterministic" because the Court chose not to provide the obvious join key (the public case number), requiring inference of a link that should have been given outright.

sfsc_charge_dispositions contains the matched charge-level fields from the rule 10.500 spreadsheet, enriched with the public case_number where a strict match succeeded. Fields include statute descriptions, initial and disposition charge types, disposition date/type, diversion type, sentence type/date, probation days, jail days, and prison days.

`judicial_assignment_sources`, `judicial_officers`, `judicial_assignments`, `judicial_department_assignments`, and `calendar_with_judicial_assignments` — Built from public San Francisco Superior Court judicial assignment materials: published assignment memos, department-assignment PDFs, and the current judicial assignment web page.

These files identify the judge publicly assigned to a court department over an effective-date range. They do not prove the historical actual daily sitting judge for a particular hearing; substitute judges, visiting judges, illness, vacations, and calendar coverage changes may not appear in these public assignment documents. A rule 10.500 request for actual presiding-judge-per-hearing data is outstanding with the Court, but given their track record of treating public records access as an adversarial proceeding, we are not holding our breath. Published department assignments are used in the meantime.

Source types used:

• —Public SF Superior Court assignment memos (mostly PDF), with explicit effective dates from 2022–2025.
• —Public SF Superior Court department assignment listing PDFs from 2024–2025.
• —Current SF Superior Court Judicial Assignments page, effective April 27, 2026: https://sf.courts.ca.gov/general-information/judicial-assignments-2026

Some PDFs were scanned/image-only. Those were OCR'd locally with glm-ocr via Ollama; cached OCR output is stored in judicial_assignment_sources/ocr/. Text-based PDFs were parsed with pdftotext.

Judge names are preserved as extracted and also mapped to canonical keys in judicial_officers so analytics can merge harmless variants such as missing middle initials, OCR misspellings, and first-name/last-name ordering while still auditing the source strings.

Generated tables:

• —judicial_assignment_sources.parquet: one row per source document/page set, including URL, local file path, effective start/end dates, extraction method, and date-basis notes.
• —judicial_officers.parquet: canonical judge/officer dimension table with raw extracted variants, cleaned variants, placeholder flags, and normalization method.
• —judicial_assignments.parquet: raw extracted assignment rows from each source, usually one row per department/judge/function.
• —judicial_department_assignments.parquet: normalized one-row-per-source/date/department table, intended for joining by department + hearing date.
• —calendar_with_judicial_assignments.parquet: calendar.parquet left-joined to judicial_department_assignments using calendar department and hearing date falling within the assignment effective range.

Join confidence fields:

• —deterministic_to_published_department_assignment: exact department/date match to a single published assigned judge.
• —published_assignment_month_only_date: joined to a source whose public header only gave month/year, so the script used the first day of that month and flags the date basis.
• —Null confidence: no matching public assignment source was available for that calendar row.

The integration script is integrate_judicial_assignments.py. Re-running it downloads/caches source documents, OCRs scanned PDFs when needed, rebuilds the SQLite tables/views in cases.db, and regenerates the parquet outputs.

Usage

import duckdb

con = duckdb.connect()

# Query a single table
con.sql("SELECT * FROM 'cases.parquet' LIMIT 10").show()

# Join cases to charge dispositions
con.sql("""
    SELECT c.case_number, c.defendant_name, d.statute_description,
           d.dispo_type, d.jail_days, d.prison_days
    FROM 'cases.parquet' c
    JOIN 'sfsc_charge_dispositions.parquet' d USING (case_number)
    WHERE d.prison_days > 0
    ORDER BY d.prison_days DESC
    LIMIT 20
""").show()

License

CC-BY-NC-4.0. Non-commercial use only.