Load caseNumber and offenseType from Flock search audits !10
2 files changed, +18 −3
Layout: unified · split
ingest.py +16 −3
| @@ -403,11 +403,18 @@ def ingest_flock(conn, _since): | |||
| 403 | for r in reader: | 403 | for r in reader: |
| 404 | rows.append((agency, r["id"], r["searchDate"], | 404 | rows.append((agency, r["id"], r["searchDate"], |
| 405 | int(r["networkCount"]) if r["networkCount"] else None, | 405 | int(r["networkCount"]) if r["networkCount"] else None, |
| 406 | r["reason"].strip() or None, r["userId"], now)) | 406 | r["reason"].strip() or None, r["userId"], |
| 407 | (r.get("caseNumber") or "").strip() or None, | ||
| 408 | (r.get("offenseType") or "").strip() or None, now)) | ||
| 409 | # imported_at is left alone on conflict: the staleness alarm reads it. | ||
| 407 | conn.executemany( | 410 | conn.executemany( |
| 408 | """INSERT OR IGNORE INTO alpr_searches | 411 | """INSERT INTO alpr_searches |
| 409 | (agency, search_id, searched_at, network_count, reason, user_id, | 412 | (agency, search_id, searched_at, network_count, reason, user_id, |
| 410 | imported_at) VALUES (?,?,?,?,?,?,?)""", rows) | 413 | case_number, offense_type, imported_at) VALUES (?,?,?,?,?,?,?,?,?) |
| 414 | ON CONFLICT (agency, search_id) DO UPDATE SET | ||
| 415 | case_number = COALESCE(excluded.case_number, case_number), | ||
| 416 | offense_type = COALESCE(excluded.offense_type, offense_type)""", | ||
| 417 | rows) | ||
| 411 | return len(rows), 0 | 418 | return len(rows), 0 |
| 412 | 419 | ||
| 413 | 420 | ||
| @@ -439,6 +446,12 @@ def ingest_alpr(conn, _since): | |||
| 439 | def migrate(conn): | 446 | def migrate(conn): |
| 440 | """Add columns introduced after a database was first built. Runs before | 447 | """Add columns introduced after a database was first built. Runs before |
| 441 | schema.sql so its views and indexes can reference the new columns.""" | 448 | schema.sql so its views and indexes can reference the new columns.""" |
| 449 | searches = {r[1] for r in conn.execute("PRAGMA table_info(alpr_searches)")} | ||
| 450 | if searches and "case_number" not in searches: | ||
| 451 | conn.execute("ALTER TABLE alpr_searches ADD COLUMN case_number TEXT") | ||
| 452 | conn.execute("ALTER TABLE alpr_searches ADD COLUMN offense_type TEXT") | ||
| 453 | conn.commit() | ||
| 454 | |||
| 442 | have = {r[1] for r in conn.execute("PRAGMA table_info(incidents)")} | 455 | have = {r[1] for r in conn.execute("PRAGMA table_info(incidents)")} |
| 443 | if not have: | 456 | if not have: |
| 444 | return | 457 | return |
schema.sql +2
| @@ -101,6 +101,8 @@ CREATE TABLE IF NOT EXISTS alpr_searches ( | |||
| 101 | network_count INTEGER, -- camera networks the search reached across | 101 | network_count INTEGER, -- camera networks the search reached across |
| 102 | reason TEXT, -- free text, blank on most searches | 102 | reason TEXT, -- free text, blank on most searches |
| 103 | user_id TEXT, -- redacted upstream | 103 | user_id TEXT, -- redacted upstream |
| 104 | case_number TEXT, -- absent from exports before 2026-09 | ||
| 105 | offense_type TEXT, -- absent from exports before 2026-09 | ||
| 104 | imported_at TEXT NOT NULL, | 106 | imported_at TEXT NOT NULL, |
| 105 | PRIMARY KEY (agency, search_id) | 107 | PRIMARY KEY (agency, search_id) |
| 106 | ); | 108 | ); |