🚨 crime

Comprehensive U.S. crime and public safety data from FBI Crime Data Explorer (CDE) and Bureau of Justice Statistics (BJS). CDE tables cover law enforcement agencies, summarized offense rates, agency-level offenses, arrests, supplementary homicide reports (SHR), police employment, hate crimes, use of force, LEOKA (officers killed/assaulted), cargo theft, human trafficking, agency participation rates, and estimated crime counts. BJS tables cover NIBRS national crime estimates, NCVS personal and household victimization, and NCVS population data. All state-level tables join with geo.state_ref via state_abbr.

19 datasets · 313 columns

cde_agencies · table

FBI CDE law enforcement agency registry. ~19,600 agencies across 51 jurisdictions with ORI identifiers, agency type, county, NIBRS reporting status, and coordinates. One row per agency. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
ori	string	no	ORI identifier (e.g., 'CA0150000')
agency_name	string	yes	Agency name
agency_type_name	string	yes	Agency type (City, County, State, Other)
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
county_name	string	yes	County name (title case, e.g., 'Kern')
is_nibrs	boolean	yes	Whether agency reports via NIBRS
nibrs_start_date	string	yes	NIBRS start date (ISO format)
latitude	double	yes	Agency latitude
longitude	double	yes	Agency longitude

cde_offenses · table

FBI CDE summarized crime rates by state and offense type. Includes offense rate, clearance rate, national comparison rates, population, and coverage percentage. One row per state + offense + month. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
offense_code	string	no	FBI offense code (e.g., violent-crime, homicide, burglary)
month	string	yes	MM-YYYY format
offense_rate	double	yes	Offenses per 100k population (state), for this row's month only — not an annualized rate. AVG(offense_rate) across a year's 12 rows understates the true annual rate by roughly 12x; SUM(offense_rate) across the year's months gives the annual per-100k rate.
clearance_rate	double	yes	Clearances per 100k population (state), for this row's month only — same per-month grain as offense_rate; sum across months for an annual rate.
national_offense_rate	double	yes	National offense rate for comparison
national_clearance_rate	double	yes	National clearance rate for comparison
population	long	yes	State population for the period
population_coverage_pct	double	yes	Percentage of population covered by reporting agencies

cde_police_employment · table

FBI CDE police employment data by state and year. Includes officers per 1,000 population, male/female officer and civilian counts, and population coverage.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
officers_per_1000	double	yes	Officers per 1,000 people
male_officers	long	yes	Male sworn officer count
female_officers	long	yes	Female sworn officer count
male_civilians	long	yes	Male civilian employee count
female_civilians	long	yes	Female civilian employee count
participated_population	long	yes	Population covered by reporting agencies

cde_police_employment_by_agency · table

FBI CDE LEOKA (Law Enforcement Officers Killed and Assaulted) Police Employee data at AGENCY-year grain — one row per (ori, year) with the same total-sworn-officer and civilian counts that cde_police_employment carries at state grain, plus the county the FBI itself publishes for each ORI. Sourced to close the gap #266 identified: no table gave county-grain sworn-officer headcount over time, so a policing-intensity-vs-crime-rate analysis at county grain was structurally impossible from this schema. Roll-up: GROUP BY county_name (or JOIN cde_agencies via ori for canonical county naming and geo.counties FK linkage) collapses this to county-year. GROUP BY state_abbr recovers cde_police_employment's totals — both are LEOKA-derived, but the state-grain table pulls the CDE API's state aggregate directly while this table sums per-agency reports, so tiny differences can appear on years with late-arriving amendments between the two API paths' snapshots. Source: two live endpoints, chained in the DataProvider — api.usa.gov/crime/fbi/cde/agency/byStateAbbr/{state} gives each state's ORI list plus FBI's own county label per agency, then cde.ucr.cjis.gov/LATEST/pe/agency/{ori}?from=Y&to=Y returns that agency's PE record for the year. Confirmed live 2026-09-14 (Nome PD AK0010600 FY2020 = 9 male + 1 female officers, matching a real small Alaska department). NULL-response filtering: the pe/agency endpoint returns a fully-null envelope for ORIs that never reported PE data in the queried year. Those rows are dropped rather than stored as all-NULL counts — a real "no data" from LEOKA is the same as "agency did not report", not a distinguishable state worth carrying. Row count per year therefore reflects only agencies that actually reported. County-name convention: FBI publishes county as UPPER-CASE with hyphens ('KENAI PENINSULA'), 'NOT SPECIFIED' for state-police/university/airport/tribal agencies that don't sit in a single county, and occasional multi-county strings for agencies spanning two counties. Kept verbatim from the source; a downstream view can normalize to geo.counties.county_name (title case) via UPPER() / TRIM() plus the NOT-SPECIFIED filter. API key: uses API_DATA_GOV env var (same key already used by cde_agencies). The PE endpoint itself is keyless; only the agency-list call routes through api.usa.gov.

Column	Type	Null	Description
ori	string	no	9-character ORI identifier (PK partial, FK to cde_agencies.ori)
year	integer	no	Calendar year
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
agency_name	string	yes	Agency name as published by FBI (e.g., 'Nome Police Department')
agency_type_name	string	yes	Agency type (City, County, State Police, Tribal, University or College, Other)
county_name	string	yes	County name as FBI publishes it (UPPER-CASE, hyphenated; 'NOT SPECIFIED' for state/university/airport/tribal agencies not tied to one county)
male_officers	long	yes	Male sworn officer count
female_officers	long	yes	Female sworn officer count
male_civilians	long	yes	Male civilian employee count
female_civilians	long	yes	Female civilian employee count
participated_population	long	yes	Population this agency's ORI covers per FBI's own coverage estimate
officers_per_1000	double	yes	Law enforcement employees per 1,000 people covered (rate = (officers+civilians)/participated_population*1000)

cde_hate_crimes · table

FBI CDE hate crime statistics by state and year. Flattened from nested sections (bias_section, incident_section) with categories (victim_type, offense_type, offender_race, bias, etc.) and labels with counts.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
section	string	yes	Data section grouping
category	string	yes	Category within section
label	string	yes	Metric or value label
count	long	yes	Count for this combination

cde_use_of_force · table

FBI CDE use of force data by state and year, flattened into generic section/category/label rows with a single numeric value that may be a count or a percentage (see the value column). One row per state + year + section + category + label. The CDE API rejects years before 2019 outright ("Year field cannot be less than 2019"); the schema's 2010 floor is shared with every CDE table but this dataset has no years before 2019 to backfill.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
section	string	yes	Data section grouping
category	string	yes	Category within section
label	string	yes	Metric or value label
value	double	yes	Metric value (count or percentage)

cde_reta · table

FBI Return A master file: agency-level monthly counts of Part I offenses (reported/unfounded/actual/cleared/juvenile-cleared) plus officers killed and assaulted, from the CDE bulk download (one national fixed-width file per year) instead of per-ORI API calls. One row per agency (ORI7) per month, unpivoted from the wide 12-month record. Cross-references cde_agencies via ori (ORI7 prefix). Values are the FBI master file's own, unmodified. The file carries no per-month completeness flag: a month row exists for every agency-year, and an agency that did not report a month can hold 0 or a small late-adjustment count there, which is indistinguishable from a genuine low month. number_of_months_reported is a single header-level count and does not identify which months are empty, so 12 rows are not 12 reported months. Some agencies also carry a second record under the same ori (a different population_group, typically zero or small adjustments) that must be summed with the primary record. Some agencies record a whole year's counts in a single month (typically December), so month-level seasonality is unreliable; annual totals are the safer grain. The FBI revises this master file for the current and recent years as agencies file late or corrected submissions (e.g. NYPD's 2024 monthly murder counts increased after initial publication); a fixed year=YYYY.zip URL is not a stable snapshot. The month dimension below is a pure cache-buster (not present in the URL) so every calendar month re-fetches and re-materializes each year, picking up FBI-side revisions instead of freezing the ingest-time snapshot. Even the fully revised FBI total for a year can still trail an agency's own published figure (e.g. NYPD's own site), since Return A submissions lag and consolidate on the FBI's schedule, not the agency's; that residual gap is a source/methodology difference, not a defect.

Column	Type	Null	Description
identifier_code	string	yes	Return A header field identifier code
state	string	yes	FBI Return A internal state numbering, NOT a FIPS code (e.g. Texas is '42', not '48'); cross-reference agency_state_name or geo.state_ref for the FIPS crosswalk
ori	string	no	7-character originating agency identifier (ORI7)
population_group	string	yes	Return A header field population group
country_division	string	yes	Return A header field country division
year	integer	no	Reporting year (4-digit, from the file batch)
month	integer	no	Calendar month 1-12 (unpivoted from the monthly record groups)
sequence_number	string	yes	Return A header field sequence number
juvenile_age	string	yes	Return A header field juvenile age
core_city_indication	string	yes	Return A header field core city indication
covered_by_ori	string	yes	Return A header field covered by ori
covered_by_population_group	string	yes	Return A header field covered by population group
last_update	string	yes	Return A header field last update
fbi_field_office	string	yes	Return A header field fbi field office
number_of_months_reported	integer	yes	Return A header field number of months reported; an agency-year total, not a per-month indicator, and it can read 12 while several months hold only near-zero counts
agency_count	integer	yes	Return A header field agency count
population_1	long	yes	Return A header field population 1
population_1_county	long	yes	Return A header field population 1 county
population_1_msa	long	yes	Return A header field population 1 msa
population_2	long	yes	Return A header field population 2
population_2_county	long	yes	Return A header field population 2 county
population_2_msa	long	yes	Return A header field population 2 msa
population_3	long	yes	Return A header field population 3
population_3_county	long	yes	Return A header field population 3 county
population_3_msa	long	yes	Return A header field population 3 msa
last_population_1	long	yes	Return A header field last population 1
last_population_2	long	yes	Return A header field last population 2
last_population_3	long	yes	Return A header field last population 3
population_source	string	yes	Return A header field population source
followup_indication	string	yes	Return A header field followup indication
special_mailing_group	string	yes	Return A header field special mailing group
special_mailing_address	string	yes	Return A header field special mailing address
agency_name	string	yes	Return A header field agency name
agency_state_name	string	yes	Return A header field agency state name
first_line_of_mailing_address	string	yes	Return A header field first line of mailing address
second_line_of_mailing_address	string	yes	Return A header field second line of mailing address
third_line_of_mailing_address	string	yes	Return A header field third line of mailing address
fourth_line_of_mailing_address	string	yes	Return A header field fourth line of mailing address
zip_code	string	yes	Return A header field zip code
population_group_in_previous_year	string	yes	Return A header field population group in previous year
blank	string	yes	Unused filler field from the Return A record
month_included_in	string	yes	Monthly card field month included in
date_of_last_update	string	yes	Monthly card field date of last update
card_unfounded_type	string	yes	Monthly reporting card indicator card unfounded type
card_actual_type	string	yes	Monthly reporting card indicator card actual type
card_total_cleared_type	string	yes	Monthly reporting card indicator card total cleared type
card_cleared_18_type	string	yes	Monthly reporting card indicator card cleared 18 type
card_officers_type	string	yes	Monthly reporting card indicator card officers type
card_unfounded_pt	string	yes	Monthly reporting card indicator card unfounded pt
card_actual_pt	string	yes	Monthly reporting card indicator card actual pt
card_total_cleared_pt	string	yes	Monthly reporting card indicator card total cleared pt
card_cleared_18_pt	string	yes	Monthly reporting card indicator card cleared 18 pt
unfounded_murder	long	yes	Monthly count of unfounded murder
unfounded_manslaughter	long	yes	Monthly count of unfounded manslaughter
unfounded_rape_total	long	yes	Monthly count of unfounded rape total
unfounded_rape_by_force	long	yes	Monthly count of unfounded rape by force
unfounded_rape_attempted	long	yes	Monthly count of unfounded rape attempted
unfounded_robbery_total	long	yes	Monthly count of unfounded robbery total
unfounded_robbery_with_a_gun	long	yes	Monthly count of unfounded robbery with a gun
unfounded_robbery_with_a_knife	long	yes	Monthly count of unfounded robbery with a knife
unfounded_robbery_other_weapon	long	yes	Monthly count of unfounded robbery other weapon
unfounded_robbery_unarmed	long	yes	Monthly count of unfounded robbery unarmed
unfounded_assault_total	long	yes	Monthly count of unfounded assault total, all assault types combined (aggravated + simple); the aggravated-assault-only figure is with_a_gun + with_a_knife + other_weapon + unarmed, excluding simple
unfounded_assault_with_a_gun	long	yes	Monthly count of unfounded assault with a gun
unfounded_assault_with_a_knife	long	yes	Monthly count of unfounded assault with a knife
unfounded_assault_other_weapon	long	yes	Monthly count of unfounded assault other weapon
unfounded_assault_unarmed	long	yes	Monthly count of unfounded assault unarmed
unfounded_assault_simple	long	yes	Monthly count of unfounded assault simple
unfounded_burglary_total	long	yes	Monthly count of unfounded burglary total
unfounded_burglary_force_entry	long	yes	Monthly count of unfounded burglary force entry
unfounded_burglary_nonforce_entry	long	yes	Monthly count of unfounded burglary nonforce entry
unfounded_burglary_attempted	long	yes	Monthly count of unfounded burglary attempted
unfounded_theft_total	long	yes	Monthly count of unfounded theft total
unfounded_motor_vehicle_theft_total	long	yes	Monthly count of unfounded motor vehicle theft total
unfounded_motor_vehicle_theft_car	long	yes	Monthly count of unfounded motor vehicle theft car
unfounded_motor_vehicle_theft_truck	long	yes	Monthly count of unfounded motor vehicle theft truck
unfounded_motor_vehicle_theft_other	long	yes	Monthly count of unfounded motor vehicle theft other
unfounded_all_crimes	long	yes	Monthly count of unfounded all crimes
unfounded_theft_under50_dollar	long	yes	Monthly count of unfounded theft under50 dollar
unfounded_blank	long	yes	Monthly count of unfounded blank
actual_murder	long	yes	Monthly count of actual murder
actual_manslaughter	long	yes	Monthly count of actual manslaughter
actual_rape_total	long	yes	Monthly count of actual rape total
actual_rape_by_force	long	yes	Monthly count of actual rape by force
actual_rape_attempted	long	yes	Monthly count of actual rape attempted
actual_robbery_total	long	yes	Monthly count of actual robbery total
actual_robbery_with_a_gun	long	yes	Monthly count of actual robbery with a gun
actual_robbery_with_a_knife	long	yes	Monthly count of actual robbery with a knife
actual_robbery_other_weapon	long	yes	Monthly count of actual robbery other weapon
actual_robbery_unarmed	long	yes	Monthly count of actual robbery unarmed
actual_assault_total	long	yes	Monthly count of actual assault total, all assault types combined (aggravated + simple) — NOT the published "aggravated assault" figure used in state Crime Index/Part I statistics, which is with_a_gun + with_a_knife + other_weapon + unarmed (excluding simple)
actual_assault_with_a_gun	long	yes	Monthly count of actual assault with a gun
actual_assault_with_a_knife	long	yes	Monthly count of actual assault with a knife
actual_assault_other_weapon	long	yes	Monthly count of actual assault other weapon
actual_assault_unarmed	long	yes	Monthly count of actual assault unarmed
actual_assault_simple	long	yes	Monthly count of actual assault simple
actual_burglary_total	long	yes	Monthly count of actual burglary total
actual_burglary_force_entry	long	yes	Monthly count of actual burglary force entry
actual_burglary_nonforce_entry	long	yes	Monthly count of actual burglary nonforce entry
actual_burglary_attempted	long	yes	Monthly count of actual burglary attempted
actual_theft_total	long	yes	Monthly count of actual theft total
actual_motor_vehicle_theft_total	long	yes	Monthly count of actual motor vehicle theft total
actual_motor_vehicle_theft_car	long	yes	Monthly count of actual motor vehicle theft car
actual_motor_vehicle_theft_truck	long	yes	Monthly count of actual motor vehicle theft truck
actual_motor_vehicle_theft_other	long	yes	Monthly count of actual motor vehicle theft other
actual_all_crimes	long	yes	Monthly count of actual all crimes
actual_theft_under50_dollar	long	yes	Monthly count of actual theft under50 dollar
actual_blank	long	yes	Monthly count of actual blank
total_cleared_murder	long	yes	Monthly count of total cleared murder
total_cleared_manslaughter	long	yes	Monthly count of total cleared manslaughter
total_cleared_rape_total	long	yes	Monthly count of total cleared rape total
total_cleared_rape_by_force	long	yes	Monthly count of total cleared rape by force
total_cleared_rape_attempted	long	yes	Monthly count of total cleared rape attempted
total_cleared_robbery_total	long	yes	Monthly count of total cleared robbery total
total_cleared_robbery_with_a_gun	long	yes	Monthly count of total cleared robbery with a gun
total_cleared_robbery_with_a_knife	long	yes	Monthly count of total cleared robbery with a knife
total_cleared_robbery_other_weapon	long	yes	Monthly count of total cleared robbery other weapon
total_cleared_robbery_unarmed	long	yes	Monthly count of total cleared robbery unarmed
total_cleared_assault_total	long	yes	Monthly count of total cleared assault total, all assault types combined (aggravated + simple); the aggravated-assault-only figure is with_a_gun + with_a_knife + other_weapon + unarmed, excluding simple
total_cleared_assault_with_a_gun	long	yes	Monthly count of total cleared assault with a gun
total_cleared_assault_with_a_knife	long	yes	Monthly count of total cleared assault with a knife
total_cleared_assault_other_weapon	long	yes	Monthly count of total cleared assault other weapon
total_cleared_assault_unarmed	long	yes	Monthly count of total cleared assault unarmed
total_cleared_assault_simple	long	yes	Monthly count of total cleared assault simple
total_cleared_burglary_total	long	yes	Monthly count of total cleared burglary total
total_cleared_burglary_force_entry	long	yes	Monthly count of total cleared burglary force entry
total_cleared_burglary_nonforce_entry	long	yes	Monthly count of total cleared burglary nonforce entry
total_cleared_burglary_attempted	long	yes	Monthly count of total cleared burglary attempted
total_cleared_theft_total	long	yes	Monthly count of total cleared theft total
total_cleared_motor_vehicle_theft_total	long	yes	Monthly count of total cleared motor vehicle theft total
total_cleared_motor_vehicle_theft_car	long	yes	Monthly count of total cleared motor vehicle theft car
total_cleared_motor_vehicle_theft_truck	long	yes	Monthly count of total cleared motor vehicle theft truck
total_cleared_motor_vehicle_theft_other	long	yes	Monthly count of total cleared motor vehicle theft other
total_cleared_all_crimes	long	yes	Monthly count of total cleared all crimes
total_cleared_theft_under50_dollar	long	yes	Monthly count of total cleared theft under50 dollar
total_cleared_blank	long	yes	Monthly count of total cleared blank
cleared_18_murder	long	yes	Monthly count of cleared 18 murder
cleared_18_manslaughter	long	yes	Monthly count of cleared 18 manslaughter
cleared_18_rape_total	long	yes	Monthly count of cleared 18 rape total
cleared_18_rape_by_force	long	yes	Monthly count of cleared 18 rape by force
cleared_18_rape_attempted	long	yes	Monthly count of cleared 18 rape attempted
cleared_18_robbery_total	long	yes	Monthly count of cleared 18 robbery total
cleared_18_robbery_with_a_gun	long	yes	Monthly count of cleared 18 robbery with a gun
cleared_18_robbery_with_a_knife	long	yes	Monthly count of cleared 18 robbery with a knife
cleared_18_robbery_other_weapon	long	yes	Monthly count of cleared 18 robbery other weapon
cleared_18_robbery_unarmed	long	yes	Monthly count of cleared 18 robbery unarmed
cleared_18_assault_total	long	yes	Monthly count of cleared 18 assault total, all assault types combined (aggravated + simple); the aggravated-assault-only figure is with_a_gun + with_a_knife + other_weapon + unarmed, excluding simple
cleared_18_assault_with_a_gun	long	yes	Monthly count of cleared 18 assault with a gun
cleared_18_assault_with_a_knife	long	yes	Monthly count of cleared 18 assault with a knife
cleared_18_assault_other_weapon	long	yes	Monthly count of cleared 18 assault other weapon
cleared_18_assault_unarmed	long	yes	Monthly count of cleared 18 assault unarmed
cleared_18_assault_simple	long	yes	Monthly count of cleared 18 assault simple
cleared_18_burglary_total	long	yes	Monthly count of cleared 18 burglary total
cleared_18_burglary_force_entry	long	yes	Monthly count of cleared 18 burglary force entry
cleared_18_burglary_nonforce_entry	long	yes	Monthly count of cleared 18 burglary nonforce entry
cleared_18_burglary_attempted	long	yes	Monthly count of cleared 18 burglary attempted
cleared_18_theft_total	long	yes	Monthly count of cleared 18 theft total
cleared_18_motor_vehicle_theft_total	long	yes	Monthly count of cleared 18 motor vehicle theft total
cleared_18_motor_vehicle_theft_car	long	yes	Monthly count of cleared 18 motor vehicle theft car
cleared_18_motor_vehicle_theft_truck	long	yes	Monthly count of cleared 18 motor vehicle theft truck
cleared_18_motor_vehicle_theft_other	long	yes	Monthly count of cleared 18 motor vehicle theft other
cleared_18_all_crimes	long	yes	Monthly count of cleared 18 all crimes
cleared_18_theft_under50_dollar	long	yes	Monthly count of cleared 18 theft under50 dollar
cleared_18_blank	long	yes	Monthly count of cleared 18 blank
officers_killed_by_felony	long	yes	Monthly count of officers killed by felony
officers_killed_by_accident	long	yes	Monthly count of officers killed by accident
officers_assaulted	long	yes	Monthly count of officers assaulted

cde_arrests · table

FBI CDE arrest data by state, aggregated to annual (Jan-Dec) totals for all offenses combined — the offense dimension is fixed to 'all', so individual offense categories (e.g., drug abuse, DUI, violent crime) are not broken out. Counts are broken down by age group, sex, race, and ethnicity via the section/category/label columns. One row per state + year + section + category + label.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
section	string	yes	Data section (age, sex, race, ethnicity)
category	string	yes	Category within section (e.g., Under 10, Male, White)
label	string	yes	Metric label
count	long	yes	Arrest count

cde_shr · table

FBI CDE Supplementary Homicide Report data, aggregated to annual (Jan-Dec) counts by state — not incident-level microdata. Victim and offender demographics for homicide incidents including age, sex, race, relationship, weapon, and circumstance details, flattened into section/category/label rows with a count. One row per state + year + section + category + label.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
section	string	yes	Data section (victim demographics, offender demographics, circumstances)
category	string	yes	Category (age, sex, race, relationship, weapon, circumstance)
label	string	yes	Value label
count	long	yes	Count for this combination

cde_leoka · table

FBI CDE Law Enforcement Officers Killed and Assaulted (LEOKA) data. Totals for officers killed (feloniously and accidentally) and assaulted in the line of duty, by state and year. The CDE API returns a null chart for every state for years before 2020; the schema's 2010 floor is shared with every CDE table but this dataset has no years before 2020 to backfill.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
section	string	yes	Data section (killed, assaulted, activity, weapon, assignment)
category	string	yes	Category within section
label	string	yes	Metric label
count	long	yes	Count for this combination

cde_trends · table

FBI CDE national crime trend percentages. Year-over-year change for major offense categories from a rolling 12-month comparison window. Single snapshot refreshed periodically (~monthly). No state dimension.

Column	Type	Null	Description
offense_name	string	no	Offense category (e.g., Murder, Robbery, Violent Crime)
trend_pct	double	yes	Year-over-year percentage change (negative = decrease)
current_range	string	yes	Comparison time window (e.g., 'November 2024 - October 2025')
last_refresh_date	string	yes	Date this data was last refreshed

ice_detention_population_by_criminality · table

ICE detention population by arresting agency (ICE vs. CBP) crossed with criminal-history category (Convicted Criminal, Pending Criminal Charges, Other Immigration Violator), from TRAC's (Transactional Records Access Clearinghouse, Syracuse University) "ICE Detainees" tracker. TRAC compiles this from ICE's own internal data obtained via FOIA/litigation — it is not ICE's own raw publication. Individually-hosted-but-public-domain source (university-hosted, not a government domain): re-verify if this 404s, same risk pattern as housing.opportunity_zones and housing.hud_subsidized_housing. Point-in-time snapshots, irregular cadence (roughly monthly-ish with gaps), spanning 2019-05-04 onward. One row per snapshot_date + arresting_agency + criminality.

Column	Type	Null	Description
snapshot_date	date	no	Point-in-time date of the detention population count (source publishes irregularly, not a fixed cadence)
arresting_agency	string	no	Arresting agency: 'ICE', 'CBP', or 'Total' (sum of ICE + CBP)
criminality	string	no	Criminal-history category: 'Convicted Criminal', 'Pending Criminal Charges', 'Other Immigration Violator', or 'All' (sum of the three)
detained_population	integer	yes	Detained population count for this agency + criminality combination

cde_supplemental · table

FBI CDE supplemental property crime data. Stolen and recovered property values by category (Firearms, Motor Vehicles, Currency, Jewelry, etc.) aggregated at the state level. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA') or 'national'
year	integer	no	Calendar year
property_type	string	no	Property category (Firearms, Locally Stolen Motor Vehicles, Currency, etc.)
stolen_value	long	yes	Total dollar value of stolen property
recovered_value	long	yes	Total dollar value of recovered property

bjs_nibrs_estimates · table

Bureau of Justice Statistics NIBRS national crime estimates from 6 SODA endpoints. Includes weighted estimates, confidence intervals, standard errors, agency counts, and population coverage for violent and property crime indicators.

Column	Type	Null	Description
endpoint_id	string	no	SODA dataset ID (e.g., r32q-bdaw)
indicator_name	string	yes	Crime indicator name
estimate	double	yes	Weighted national estimate
estimate_unweighted	long	yes	Raw unweighted count
estimate_type	string	yes	Estimate type (count, percentage, or rate)
estimate_domain_1	string	yes	Primary domain (Incident count, Weapon involved, etc.)
estimate_geographic_location	string	yes	Geographic location (typically National)
estimate_standard_error	double	yes	Standard error
estimate_upper_bound	double	yes	Upper 95% confidence bound
estimate_lower_bound	double	yes	Lower 95% confidence bound
relative_standard_error	double	yes	Relative standard error
agency_counts	integer	yes	Number of contributing agencies
pop_cov	double	yes	Population coverage fraction
time_series_start_year	integer	yes	Year for the estimate
full_table	string	yes	BJS table reference

bjs_ncvs_personal · table

Bureau of Justice Statistics NCVS personal victimization microdata, one row per victimization incident recorded by survey respondents, covering 2010 onward by default (start year configurable) and lagging about 2 years behind the current year. Contains 17 fields including crime/offense type codes, police notification status, weapon use, injury, offender age and sex, location (region/MSA), and person survey weight (newwgt). Use bjs_ncvs_personal_pop as the survey-respondent denominator for computing victimization rates.

Column	Type	Null	Description
year	integer	yes	Survey year
yearq	string	yes	Year and quarter
newcrime	string	yes	Crime type code
newoff	string	yes	Offense type code
seriousviolent	string	yes	Serious violent crime indicator
notify	string	yes	Police notification status
weapon	string	yes	Weapon presence indicator
weapcat	string	yes	Weapon category
injury	string	yes	Injury indicator
offenderage	string	yes	Offender age category
offendersex	string	yes	Offender sex
locationr	string	yes	Location of crime
direl	string	yes	Victim-offender relationship
region	string	yes	Census region
msa	string	yes	Metropolitan statistical area indicator
series	string	yes	NCVS series-victimization indicator — a flag for incidents reported as a series of similar crimes (six or more, too repetitive to recall individually), NOT a time-series identifier. This table is microdata, one row per victimization, so do not treat this column as a series key.
newwgt	double	yes	Person weight

bjs_ncvs_personal_pop · table

Bureau of Justice Statistics NCVS personal population data. Demographic information for all survey respondents regardless of victimization status. Includes age, sex, race, Hispanic origin, marital status, education, income. Same 2010-onward coverage and ~2-year publish lag as bjs_ncvs_personal. Used as denominator for computing victimization rates from bjs_ncvs_personal.

Column	Type	Null	Description
year	integer	yes	Survey year
yearq	string	yes	Year and quarter
ager	string	yes	Respondent age category
sex	string	yes	Respondent sex
race	string	yes	Respondent race
hispanic	string	yes	Hispanic origin indicator
marital	string	yes	Marital status
educatn1	string	yes	Education level (detailed)
educatn2	string	yes	Education level (recoded)
hincome1	string	yes	Household income category (detailed)
hincome2	string	yes	Household income category (recoded)
region	string	yes	Census region
msa	string	yes	Metropolitan statistical area indicator
wgtpercy	double	yes	Person weight

bjs_ncvs_household · table

Bureau of Justice Statistics NCVS household victimization microdata, one row per household-level victimization incident, coded by newcrime/newoff crime and offense type codes. Includes police notification status, region/MSA, household income bracket, and household victimization weight (wgtviccy). Same 2010-onward coverage and ~2-year publish lag as bjs_ncvs_personal. Cross-references bjs_ncvs_household_pop as the denominator for household victimization rates.

Column	Type	Null	Description
year	integer	yes	Survey year
yearq	string	yes	Year and quarter
newcrime	string	yes	Crime type code
newoff	string	yes	Offense type code
notify	string	yes	Police notification status
region	string	yes	Census region
msa	string	yes	Metropolitan statistical area indicator
hincome1	string	yes	Household income category (detailed)
hincome2	string	yes	Household income category (recoded)
series	string	yes	NCVS series-victimization indicator — a flag for incidents reported as a series of similar crimes (six or more, too repetitive to recall individually), NOT a time-series identifier. This table is microdata, one row per victimization, so do not treat this column as a series key.
wgtviccy	double	yes	Household victimization weight

bjs_ncvs_household_pop · table

Bureau of Justice Statistics NCVS household population data — demographic data (region, MSA, household income bracket, household size) for all participating households regardless of victimization, with household weight (wgthhcy). Same 2010-onward coverage and ~2-year publish lag as bjs_ncvs_personal. Used as the denominator for computing bjs_ncvs_household victimization rates.

Column	Type	Null	Description
year	integer	yes	Survey year
yearq	string	yes	Year and quarter
region	string	yes	Census region
msa	string	yes	Metropolitan statistical area indicator
hincome1	string	yes	Household income category (detailed)
hincome2	string	yes	Household income category (recoded)
hnumber	string	yes	Number of persons in household
wgthhcy	double	yes	Household weight

crime_enforcement_equity · view

Correlates FBI CDE monthly offense and clearance rates — averaged across each year's reported months, NOT summed to a true annual rate (see cde_offenses.offense_rate for why AVG understates the annual figure) — with police employment and an offense-rate-per-officer ratio, by state, year, and offense_code. One row per state + year + offense_code.

View — columns are resolved by the query engine at runtime.