• Download the FBI NIBRS CSV bundle for the state + year (e.g. MI-2023.zip).
• Unzip into C:\NIBRS\MI-YYYY\MI\ — every CSV lives in one flat folder.
• Install PostgreSQL locally (14+) and open DBeaver with a connection to localhost:5432.
• Create a clean per-year database: nibrs_YYYY_michigan. One database per year keeps schema drift contained.

• Run an adapted postgres_setup.sql that creates the nibrs schema plus every lookup and fact table.
• Primary keys and foreign keys are added at the end of the script — that matches the order rows will be loaded and avoids FK friction mid-load.
• The schema is derived directly from the FBI NIBRS data dictionary, so field names and types match the published standard.

• Use straight COPY (not \COPY) so the command runs inside DBeaver’s JDBC session.
• Forward-slash Windows paths: 'C:/NIBRS/MI-YYYY/MI/FILE.csv'.
• Standard clause: WITH (FORMAT csv, HEADER true, NULL '').
• For the rare file with special encoding (e.g. 2023 NIBRS_BIAS_LIST), add ENCODING 'WIN1251' to that one COPY.

Before the bulk load, disable referential-integrity triggers on every FK-constrained table:

ALTER TABLE tablename DISABLE TRIGGER ALL;

Then COPY the “straight” fact tables — the ones whose CSV aligns perfectly with the target schema — in an order that minimises FK friction:

1. nibrs_month → nibrs_incident → nibrs_offense
2. nibrs_property, nibrs_property_desc, nibrs_suspect_using, nibrs_suspected_drug
3. nibrs_victim (+ circumstances, + injury, + offense), nibrs_weapon, nibrs_bias_motivation

Some CSVs don’t load cleanly. Typical symptoms:

• ERROR: extra data after last expected column — trailing empty column (seen in 2023 Group B arrestee).
• ERROR: missing data for column "X" — fewer CSV fields than target (e.g. 2024 Group B arrestee omits high age_range_* fields).
• ERROR: invalid input syntax for type smallint: 'NS'/'BB' — non-numeric literals in numeric columns.

The fix is a TEMP staging pattern:

CREATE TEMP TABLE temp_arrestee (columns as TEXT matching CSV order exactly);

COPY temp_arrestee FROM 'C:/NIBRS/MI-YYYY/MI/arrestee.csv'
  WITH (FORMAT csv, HEADER true, NULL '');

INSERT INTO nibrs_arrestee (col1, col2, …)
SELECT
  CAST(…)::bigint,
  CASE WHEN txt_col ~ '^[0-9]+$' THEN txt_col::smallint ELSE NULL END,
  CASE WHEN sex_code IN ('', 'NS') THEN NULL ELSE LEFT(sex_code, 1) END,
  …
FROM temp_arrestee;

Used on arrestee, arrestee_groupb, victim, and sometimes offender depending on the year.

• Re-enable every trigger disabled in Step 4: ALTER TABLE … ENABLE TRIGGER ALL;
• Row-count check: SELECT COUNT(*) FROM nibrs_… vs the CSV header count.
• Spot-check problematic records — age fields, Group B rows with trailing commas.
• Confirm no orphan FKs now that integrity is back on.

• Build the agency-offense pivot view — one row per agency with 60+ offence columns plus the three Crimes-Against rollups. Name it by year: agency_offense_pivot_summary_2023.
• (Optional) Materialise the view to a table with a PK on agency_id for snappy ArcGIS joins.
• In ArcGIS Pro, join the Michigan agency point layer (from the gazetteer geodatabase) to the pivot view on agency_id. Reconcile nibrs_missing_in_police and police_extra mismatches before publishing.
• Publish the joined layer as a hosted feature service in ArcGIS Online. That feature service is what this web map consumes.

The core query is agency_offense_category_summary — a PostgreSQL view that joins agencies to incidents, offences, and offence types, then SUMs each NIBRS offence category into its own column with a CASE expression:

CREATE OR REPLACE VIEW nibrs.agency_offense_category_summary AS
SELECT
    a.agency_id,
    COUNT(o.offense_id) AS total_offenses,

    -- Category summaries (one CASE per NIBRS offence category)
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'Homicide Offenses'
        THEN 1 ELSE 0 END), 0) AS "Homicide_Offenses",
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'Sex Offenses'
        THEN 1 ELSE 0 END), 0) AS "Sex_Offenses",
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'Larceny/Theft Offenses'
        THEN 1 ELSE 0 END), 0) AS "Larceny_Theft_Offenses",
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'Robbery'
        THEN 1 ELSE 0 END), 0) AS "Robbery",
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'Assault Offenses'
        THEN 1 ELSE 0 END), 0) AS "Assault_Offenses",
    -- … (28 more CASE rollups: Burglary, Motor_Vehicle_Theft,
    --       Arson, Fraud_Offenses, Drug_Narcotic_Offenses,
    --       Weapon_Law_Violations, Human_Trafficking, …)
    COALESCE(SUM(CASE WHEN ot.offense_category_name = 'All Other Offenses'
        THEN 1 ELSE 0 END), 0) AS "All_Other_Offenses"

FROM nibrs.agencies a
LEFT JOIN nibrs.nibrs_incident     i  ON a.agency_id       = i.agency_id
LEFT JOIN nibrs.nibrs_offense      o  ON i.incident_id     = o.incident_id
LEFT JOIN nibrs.nibrs_offense_type ot ON o.offense_type_id = ot.offense_type_id
GROUP BY a.agency_id
ORDER BY a.agency_id;

The full script lives on the workstation at C:\Users\gregg\AppData\Roaming\DBeaverData\workspace6\General\Scripts\agency_offense_category_summary.sql and contains all 33 category rollups. For the published feature service the view is joined against the Michigan gazetteer agency point layer on agency_id and the three high-level NIBRS crime-category totals (Persons / Property / Society) are added as computed fields.

The hosted feature service that powers this map has 65 attributes per agency, grouped in three bands. Fields 1–13 are agency metadata and population; the next block is the three high-level Crimes-Against rollups; the remainder are the individual NIBRS offences, organised so Group A maps cleanly to the three categories used in the sidebar bar chart.

The bars in the Crimes Summary panel are server-side SUMs over the red/blue/gold blocks above. The popup shows all 65 fields, grouped the same way, so every individual offence is visible per agency.