# Sarpy and Council Bluffs serve a rolling 12-month window; records that age out # of those feeds exist nowhere else. This job is the only thing keeping them, so # it refuses to publish an archive smaller than the one it started with. name: daily pull on: schedule: # Twice a day, because the cadence sets how much the rolling feeds drop # before it is captured: a five-hour gap cost 13 Sarpy records once. # Offset from the hour, GitHub drops on-the-hour runs under load. - cron: "17 11 * * *" - cron: "17 23 * * *" workflow_dispatch: inputs: bootstrap: description: "Start a new archive instead of restoring the published one" type: boolean default: false full: description: "Pull each feed in full rather than the last 30 days" type: boolean default: false permissions: contents: write concurrency: group: archive cancel-in-progress: false env: TAG: archive DB: raw_data/metro.db GH_TOKEN: ${{ github.token }} jobs: pull: runs-on: ubuntu-latest timeout-minutes: 45 steps: - uses: actions/checkout@v4 - uses: actions/setup-python@v5 with: python-version: "3.13" cache: pip cache-dependency-path: requirements-ingest.txt - run: pip install -r requirements-ingest.txt - name: Restore archive run: | if gh release download "$TAG" --pattern metro.db.gz --dir .; then gunzip -c metro.db.gz > "$DB" rm metro.db.gz echo "restored $(du -h "$DB" | cut -f1)" elif [ "${{ inputs.bootstrap }}" = "true" ]; then echo "no published archive; starting a new one" else echo "::error::No metro.db.gz on release '$TAG'. Anything that has" \ "already aged out of the Sarpy and Council Bluffs feeds cannot" \ "be recovered. Re-run with bootstrap only if that is intended." exit 1 fi - name: Count rows before run: | : > before.txt if [ -f "$DB" ]; then sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \ > before.txt # absent until the amendment migration has run against this archive sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source || '+amend', COUNT(*) FROM incident_amendments GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true sqlite3 -noheader -separator ' ' "$DB" \ "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" >> before.txt 2>/dev/null || true sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true sqlite3 -noheader -separator ' ' "$DB" \ "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" >> before.txt 2>/dev/null || true fi cat before.txt - name: Pull feeds run: | # A 30-day window cannot see an agency amending a record filed months # ago, and OPD does exactly that, so sweep the whole feed on Sundays. if [ "${{ inputs.full }}" = "true" ] || [ "${{ inputs.bootstrap }}" = "true" ] \ || [ "$(date -u +%u)" = "7" ]; then echo "full sweep" python ingest.py --full else python ingest.py fi - name: Check nothing was lost run: | sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \ > after.txt sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source || '+amend', COUNT(*) FROM incident_amendments GROUP BY source ORDER BY source" >> after.txt sqlite3 -noheader -separator ' ' "$DB" \ "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" >> after.txt sqlite3 -noheader -separator ' ' "$DB" \ "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" >> after.txt cat after.txt test -s after.txt || { echo "::error::archive is empty"; exit 1; } # Keyed on FILENAME, not NR == FNR: before.txt is empty on a bootstrap # run, and awk never resets FNR for a zero-length file. awk -v first=before.txt ' FILENAME == first { was[$1] = $2; next } { now[$1] = $2 } END { for (s in was) if (now[s] + 0 < was[s] + 0) { printf "::error::%s lost rows: %d -> %d\n", s, was[s], now[s] bad = 1 } exit bad }' before.txt after.txt # An amendment identical to its original means the digest drifted # against what SQLite stores, and every run would file the same # phantom again. Cheap to check, silent and cumulative if it happens. phantom=$(sqlite3 -noheader "$DB" " SELECT COUNT(*) FROM incident_amendments a JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key WHERE a.agency IS o.agency AND a.case_id IS o.case_id AND a.occurred_at IS o.occurred_at AND a.category IS o.category AND a.call_type IS o.call_type AND a.disposition IS o.disposition AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon") if [ "$phantom" -ne 0 ]; then echo "::error::$phantom amendments are identical to their original" exit 1 fi - name: Publish archive run: | sqlite3 "$DB" "VACUUM;" gzip -c "$DB" > metro.db.gz gh release view "$TAG" >/dev/null 2>&1 \ || gh release create "$TAG" --title "Incident archive" --notes "building" gh release upload "$TAG" metro.db.gz --clobber { echo "SQLite archive of Omaha metro police incident feeds, rebuilt daily." echo "Sarpy County and Council Bluffs publish a rolling 12-month window," echo "so this holds records their own feeds no longer serve." echo echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql." echo echo "incidents holds each record as first published; every later" echo "version the feed served is a row in incident_amendments" echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)." echo "incidents_current is the newest version of each, and" echo "raw_records keeps the feed's own JSON for every version" echo "so a parse can be redone against what actually arrived." echo echo '```' sqlite3 -header -column "$DB" \ "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops, MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest FROM incidents GROUP BY agency ORDER BY rows DESC" echo '```' } > notes.md gh release edit "$TAG" --notes-file notes.md # Runs after the upload on purpose: a feed that stopped updating should # raise the alarm without also blocking the archive from being published. - name: Check the feeds are still moving run: | sqlite3 -noheader "$DB" \ "SELECT source || ' ' || MAX(occurred_at) FROM incidents WHERE source <> 'opd_csv' GROUP BY source HAVING MAX(occurred_at) < datetime('now', '-7 days')" > stale.txt if [ -s stale.txt ]; then while read -r line; do echo "::error::feed is stale: $line"; done < stale.txt exit 1 fi echo "all feeds current" - name: Summary if: always() run: | { echo "| source | before | after |" echo "|---|---|---|" awk -v first=before.txt ' FILENAME == first { was[$1] = $2; next } { printf "| %s | %s | %s |\n", $1, ($1 in was ? was[$1] : 0), $2 }' \ before.txt after.txt } >> "$GITHUB_STEP_SUMMARY"