ci/pull-publish.sh

  1#!/bin/sh
  2# The daily-pull job's pull-and-publish step. A ci.yml step is capped at
  3# 4096 bytes; this one is longer, so it lives here and the step runs it.
  4set -e
  5export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db HOST=$GITBAY_SSH R=krz/omaha-metro-blotter
  6SSH="ssh -F $PWD/.ssh_config"
  7mkdir -p raw_data
  8
  9# Restore the published archive; a crashed publish leaves metro.db.new.gz.
 10if $SSH $HOST release asset get $R archive metro.db.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
 11  :
 12elif $SSH $HOST release asset get $R archive metro.db.new.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
 13  echo "recovered from interrupted publish"
 14else
 15  echo "ERROR: no metro.db.gz on release 'archive'; aged-out records cannot be recovered"
 16  exit 1
 17fi
 18gunzip -c metro.db.gz > "$DB" && rm metro.db.gz
 19echo "restored $(du -h "$DB" | cut -f1)"
 20
 21counts() {
 22  sqlite3 -noheader -separator ' ' "$DB" \
 23    "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source"
 24  sqlite3 -noheader -separator ' ' "$DB" \
 25    "SELECT source || '+amend', COUNT(*) FROM incident_amendments GROUP BY source ORDER BY source" 2>/dev/null || true
 26  sqlite3 -noheader -separator ' ' "$DB" \
 27    "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" 2>/dev/null || true
 28  sqlite3 -noheader -separator ' ' "$DB" \
 29    "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" 2>/dev/null || true
 30}
 31counts > before.txt
 32cat before.txt
 33
 34# OPD amends records filed months ago; sweep the whole feed on Sundays.
 35if [ "$(date -u +%u)" = "7" ]; then
 36  echo "full sweep"
 37  python ingest.py --full opd sarpy cbpd alpr flock opd_archive
 38else
 39  # The default set, spelled out: Python 3.14's argparse rejects an empty
 40  # list against choices, so a bare call errors before it pulls anything.
 41  python ingest.py opd sarpy cbpd alpr flock
 42fi
 43
 44counts > after.txt
 45cat after.txt
 46test -s after.txt || { echo "ERROR: archive is empty"; exit 1; }
 47awk -v first=before.txt '
 48     FILENAME == first { was[$1] = $2; next }
 49     { now[$1] = $2 }
 50     END {
 51       for (s in was)
 52         if (now[s] + 0 < was[s] + 0) {
 53           printf "ERROR: %s lost rows: %d -> %d\n", s, was[s], now[s]
 54           bad = 1
 55         }
 56       exit bad
 57     }' before.txt after.txt
 58
 59# An amendment identical to its original means the digest drifted.
 60phantom=$(sqlite3 -noheader "$DB" "
 61  SELECT COUNT(*) FROM incident_amendments a
 62  JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key
 63  WHERE a.agency IS o.agency AND a.case_id IS o.case_id
 64    AND a.occurred_at IS o.occurred_at AND a.category IS o.category
 65    AND a.call_type IS o.call_type AND a.disposition IS o.disposition
 66    AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop
 67    AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon")
 68if [ "$phantom" -ne 0 ]; then
 69  echo "ERROR: $phantom amendments are identical to their original"
 70  exit 1
 71fi
 72
 73# Publish. The .new asset makes the sequence crash-safe: at every
 74# point at least one asset holds the full archive.
 75sqlite3 "$DB" "VACUUM;"
 76gzip -c "$DB" > metro.db.gz
 77$SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
 78$SSH $HOST release asset add $R archive metro.db.new.gz < metro.db.gz
 79$SSH $HOST release asset remove $R archive metro.db.gz 2>/dev/null || true
 80$SSH $HOST release asset add $R archive metro.db.gz < metro.db.gz
 81$SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
 82rm metro.db.gz
 83{
 84  echo "SQLite archive of Omaha metro police incident feeds, rebuilt twice daily."
 85  echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
 86  echo "so this holds records their own feeds no longer serve."
 87  echo
 88  echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
 89  echo
 90  echo "incidents holds each record as first published; every later"
 91  echo "version the feed served is a row in incident_amendments"
 92  echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)."
 93  echo
 94  echo '```'
 95  sqlite3 -header -column "$DB" \
 96    "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
 97            MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
 98     FROM incidents GROUP BY agency ORDER BY rows DESC"
 99  echo '```'
100} > notes.md
101$SSH $HOST "release edit $R archive --title 'Incident archive' --file -" < notes.md
102rm notes.md
103echo "archive published"