krz/omaha-metro-blotter

  1# Sarpy and Council Bluffs serve a rolling 12-month window; records that age out
  2# of those feeds exist nowhere else. This job is the only thing keeping them, so
  3# it refuses to publish an archive smaller than the one it started with.
  4name: daily pull
  5
  6on:
  7  schedule:
  8    # 06:00 America/Chicago in summer, 05:00 in winter. Offset from the hour
  9    # because GitHub drops on-the-hour scheduled runs under load.
 10    - cron: "17 11 * * *"
 11  workflow_dispatch:
 12    inputs:
 13      bootstrap:
 14        description: "Start a new archive instead of restoring the published one"
 15        type: boolean
 16        default: false
 17      full:
 18        description: "Pull each feed in full rather than the last 30 days"
 19        type: boolean
 20        default: false
 21
 22permissions:
 23  contents: write
 24
 25concurrency:
 26  group: archive
 27  cancel-in-progress: false
 28
 29env:
 30  TAG: archive
 31  DB: raw_data/metro.db
 32  GH_TOKEN: ${{ github.token }}
 33
 34jobs:
 35  pull:
 36    runs-on: ubuntu-latest
 37    timeout-minutes: 30
 38
 39    steps:
 40      - uses: actions/checkout@v4
 41
 42      - uses: actions/setup-python@v5
 43        with:
 44          python-version: "3.13"
 45          cache: pip
 46          cache-dependency-path: requirements-ingest.txt
 47
 48      - run: pip install -r requirements-ingest.txt
 49
 50      - name: Restore archive
 51        run: |
 52          if gh release download "$TAG" --pattern metro.db.gz --dir .; then
 53            gunzip -c metro.db.gz > "$DB"
 54            rm metro.db.gz
 55            echo "restored $(du -h "$DB" | cut -f1)"
 56          elif [ "${{ inputs.bootstrap }}" = "true" ]; then
 57            echo "no published archive; starting a new one"
 58          else
 59            echo "::error::No metro.db.gz on release '$TAG'. Anything that has" \
 60                 "already aged out of the Sarpy and Council Bluffs feeds cannot" \
 61                 "be recovered. Re-run with bootstrap only if that is intended."
 62            exit 1
 63          fi
 64
 65      - name: Count rows before
 66        run: |
 67          : > before.txt
 68          if [ -f "$DB" ]; then
 69            sqlite3 -noheader -separator ' ' "$DB" \
 70              "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
 71              > before.txt
 72          fi
 73          cat before.txt
 74
 75      - name: Pull feeds
 76        run: |
 77          if [ "${{ inputs.full }}" = "true" ] || [ "${{ inputs.bootstrap }}" = "true" ]; then
 78            python ingest.py --full
 79          else
 80            python ingest.py
 81          fi
 82
 83      - name: Check nothing was lost
 84        run: |
 85          sqlite3 -noheader -separator ' ' "$DB" \
 86            "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
 87            > after.txt
 88          cat after.txt
 89          test -s after.txt || { echo "::error::archive is empty"; exit 1; }
 90          # Keyed on FILENAME, not NR == FNR: before.txt is empty on a bootstrap
 91          # run, and awk never resets FNR for a zero-length file.
 92          awk -v first=before.txt '
 93               FILENAME == first { was[$1] = $2; next }
 94               { now[$1] = $2 }
 95               END {
 96                 for (s in was)
 97                   if (now[s] + 0 < was[s] + 0) {
 98                     printf "::error::%s lost rows: %d -> %d\n", s, was[s], now[s]
 99                     bad = 1
100                   }
101                 exit bad
102               }' before.txt after.txt
103
104      - name: Publish archive
105        run: |
106          sqlite3 "$DB" "VACUUM;"
107          gzip -c "$DB" > metro.db.gz
108          gh release view "$TAG" >/dev/null 2>&1 \
109            || gh release create "$TAG" --title "Incident archive" --notes "building"
110          gh release upload "$TAG" metro.db.gz --clobber
111          {
112            echo "SQLite archive of Omaha metro police incident feeds, rebuilt daily."
113            echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
114            echo "so this holds records their own feeds no longer serve."
115            echo
116            echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
117            echo
118            echo '```'
119            sqlite3 -header -column "$DB" \
120              "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
121                      MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
122               FROM incidents GROUP BY agency ORDER BY rows DESC"
123            echo '```'
124          } > notes.md
125          gh release edit "$TAG" --notes-file notes.md
126
127      # Runs after the upload on purpose: a feed that stopped updating should
128      # raise the alarm without also blocking the archive from being published.
129      - name: Check the feeds are still moving
130        run: |
131          sqlite3 -noheader "$DB" \
132            "SELECT source || ' ' || MAX(occurred_at) FROM incidents
133             WHERE source <> 'opd_csv' GROUP BY source
134             HAVING MAX(occurred_at) < datetime('now', '-7 days')" > stale.txt
135          if [ -s stale.txt ]; then
136            while read -r line; do echo "::error::feed is stale: $line"; done < stale.txt
137            exit 1
138          fi
139          echo "all feeds current"
140
141      - name: Summary
142        if: always()
143        run: |
144          {
145            echo "| source | before | after |"
146            echo "|---|---|---|"
147            awk -v first=before.txt '
148                 FILENAME == first { was[$1] = $2; next }
149                 { printf "| %s | %s | %s |\n", $1, ($1 in was ? was[$1] : 0), $2 }' \
150                before.txt after.txt
151          } >> "$GITHUB_STEP_SUMMARY"