krz/omaha-metro-blotter

  1-- Incidents as first observed. Rows here are never updated: when a feed serves a
  2-- changed version of a record it goes to incident_amendments instead, so the
  3-- original survives a reclassification, a reopened case or a withdrawn record.
  4-- occurred_at is local time (America/Chicago); the services return UTC epochs
  5-- and ingest.py converts on the way in.
  6CREATE TABLE IF NOT EXISTS incidents (
  7    source       TEXT NOT NULL,  -- opd | sarpy | cbpd | opd_csv
  8    source_key   TEXT NOT NULL,  -- PK (opd) | IncidentId (sarpy) | cfs_number (cbpd)
  9    agency       TEXT NOT NULL,
 10    case_id      TEXT,
 11    occurred_at  TEXT NOT NULL,  -- ISO 8601, no offset
 12    category     TEXT,           -- each feed's own taxonomy, not comparable
 13    call_type    TEXT,           -- CadTypeDesc (sarpy) | incident_code (cbpd)
 14    disposition  TEXT,           -- CAD disposition, sarpy and cbpd
 15    offense_desc TEXT,           -- StatuteDesc (sarpy) | statute text (opd_csv)
 16    is_stop      INTEGER NOT NULL DEFAULT 0,  -- officer-initiated vehicle stop
 17    address      TEXT,
 18    lat          REAL,
 19    lon          REAL,
 20    digest       TEXT NOT NULL,  -- hash of the payload, for change detection
 21    first_seen   TEXT,           -- when ingest first saw it; NULL if pre-dating
 22    PRIMARY KEY (source, source_key)
 23);
 24
 25CREATE INDEX IF NOT EXISTS incidents_occurred ON incidents (occurred_at);
 26CREATE INDEX IF NOT EXISTS incidents_agency   ON incidents (agency, occurred_at);
 27CREATE INDEX IF NOT EXISTS incidents_category ON incidents (category);
 28CREATE INDEX IF NOT EXISTS incidents_stop     ON incidents (is_stop, occurred_at);
 29
 30-- Every distinct later version of a record, one row per version. Keyed on the
 31-- payload digest, so a version is stored once no matter how many runs serve it.
 32-- A record that reverts to a payload already on file is therefore not recorded
 33-- again: this holds the set of distinct states observed, not a strict timeline.
 34CREATE TABLE IF NOT EXISTS incident_amendments (
 35    source       TEXT NOT NULL,
 36    source_key   TEXT NOT NULL,
 37    agency       TEXT NOT NULL,
 38    case_id      TEXT,
 39    occurred_at  TEXT NOT NULL,
 40    category     TEXT,
 41    call_type    TEXT,
 42    disposition  TEXT,
 43    offense_desc TEXT,
 44    is_stop      INTEGER NOT NULL DEFAULT 0,
 45    address      TEXT,
 46    lat          REAL,
 47    lon          REAL,
 48    digest       TEXT NOT NULL,
 49    seen_at      TEXT NOT NULL,  -- when this version was first observed
 50    PRIMARY KEY (source, source_key, digest)
 51);
 52
 53CREATE INDEX IF NOT EXISTS amendments_key  ON incident_amendments (source, source_key);
 54CREATE INDEX IF NOT EXISTS amendments_seen ON incident_amendments (seen_at);
 55
 56-- The newest known version of each record: its latest amendment, or the
 57-- original where a record has never been amended.
 58CREATE VIEW IF NOT EXISTS incidents_current AS
 59SELECT source, source_key, agency, case_id, occurred_at, category, call_type,
 60       disposition, offense_desc, is_stop, address, lat, lon, observed_at,
 61       amended
 62FROM (
 63    SELECT *, ROW_NUMBER() OVER (PARTITION BY source, source_key
 64                                 ORDER BY amended DESC, observed_at DESC) AS rn
 65    FROM (
 66        SELECT source, source_key, agency, case_id, occurred_at, category,
 67               call_type, disposition, offense_desc, is_stop, address, lat, lon,
 68               first_seen AS observed_at, 0 AS amended
 69        FROM incidents
 70        UNION ALL
 71        SELECT source, source_key, agency, case_id, occurred_at, category,
 72               call_type, disposition, offense_desc, is_stop, address, lat, lon,
 73               seen_at AS observed_at, 1 AS amended
 74        FROM incident_amendments
 75    )
 76)
 77WHERE rn = 1;
 78
 79-- What the feed actually served, one row per version, keyed the same way
 80-- incident_amendments is. A parse that turns out wrong or a field a feed adds
 81-- later can only be applied to history if the bytes were kept, and the feeds
 82-- age out, so there is no second chance to fetch them.
 83CREATE TABLE IF NOT EXISTS raw_records (
 84    source     TEXT NOT NULL,
 85    source_key TEXT NOT NULL,
 86    digest     TEXT NOT NULL,  -- the version of the record this payload produced
 87    fetched_at TEXT NOT NULL,
 88    payload    TEXT NOT NULL,  -- the feature object as served, JSON
 89    PRIMARY KEY (source, source_key, digest)
 90);
 91
 92-- Searches run against an agency's ALPR network, from its Flock transparency
 93-- portal. The portal serves a rolling 30 days and the export is already the
 94-- whole record: no field here is derived, so there is no separate raw copy.
 95-- userId is redacted to *** by Flock, so searches cannot be attributed to an
 96-- officer, and most carry no reason despite the portals' stated access policy.
 97CREATE TABLE IF NOT EXISTS alpr_searches (
 98    agency        TEXT NOT NULL,  -- portal slug the export came from
 99    search_id     TEXT NOT NULL,  -- Flock's own UUID, stable across exports
100    searched_at   TEXT NOT NULL,  -- ISO 8601 UTC, as published
101    network_count INTEGER,        -- camera networks the search reached across
102    reason        TEXT,           -- free text, blank on most searches
103    user_id       TEXT,           -- redacted upstream
104    imported_at   TEXT NOT NULL,
105    PRIMARY KEY (agency, search_id)
106);
107
108CREATE INDEX IF NOT EXISTS searches_when ON alpr_searches (searched_at);
109
110-- ALPR cameras from OpenStreetMap (ODbL). first_seen/last_seen track when a node
111-- entered and was last present in the Overpass result, so cameras that appear or
112-- are removed are visible over time.
113CREATE TABLE IF NOT EXISTS alpr_cameras (
114    osm_id       INTEGER PRIMARY KEY,
115    lat          REAL NOT NULL,
116    lon          REAL NOT NULL,
117    manufacturer TEXT,
118    operator     TEXT,
119    direction    TEXT,
120    first_seen   TEXT NOT NULL,
121    last_seen    TEXT NOT NULL,
122    tags         TEXT NOT NULL  -- full OSM tag dict as JSON
123);