krz/omaha-metro-blotter

ci: the pull-and-publish step runs from a script !7

merged merged by cmc on 2026-09-10 03:36 UTC · krz/omaha-metro-blotter:step-cap into main

2 files changed, +102 −99

Layout: unified · split

.gitbay/ci.yml +1 −99
@@ -26,105 +26,7 @@ jobs:
2626 printf '%s\n' "$BOT_SSH_KEY" > "$PWD/.bot_key"
2727 printf 'IdentityFile %s\nIdentitiesOnly yes\nStrictHostKeyChecking accept-new\nUserKnownHostsFile %s\n' "$PWD/.bot_key" "$PWD/.known_hosts" > "$PWD/.ssh_config"
2828 ssh -F "$PWD/.ssh_config" "$GITBAY_SSH" whoami
29 - |
30 set -e
31 export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db HOST=$GITBAY_SSH R=krz/omaha-metro-blotter
32 SSH="ssh -F $PWD/.ssh_config"
33 mkdir -p raw_data
34
35 # Restore the published archive; a crashed publish leaves metro.db.new.gz.
36 if $SSH $HOST release asset get $R archive metro.db.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
37 :
38 elif $SSH $HOST release asset get $R archive metro.db.new.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
39 echo "recovered from interrupted publish"
40 else
41 echo "ERROR: no metro.db.gz on release 'archive'; aged-out records cannot be recovered"
42 exit 1
43 fi
44 gunzip -c metro.db.gz > "$DB" && rm metro.db.gz
45 echo "restored $(du -h "$DB" | cut -f1)"
46
47 counts() {
48 sqlite3 -noheader -separator ' ' "$DB" \
49 "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source"
50 sqlite3 -noheader -separator ' ' "$DB" \
51 "SELECT source || '+amend', COUNT(*) FROM incident_amendments GROUP BY source ORDER BY source" 2>/dev/null || true
52 sqlite3 -noheader -separator ' ' "$DB" \
53 "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" 2>/dev/null || true
54 sqlite3 -noheader -separator ' ' "$DB" \
55 "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" 2>/dev/null || true
56 }
57 counts > before.txt
58 cat before.txt
59
60 # OPD amends records filed months ago; sweep the whole feed on Sundays.
61 if [ "$(date -u +%u)" = "7" ]; then
62 echo "full sweep"
63 python ingest.py --full opd sarpy cbpd alpr flock opd_archive
64 else
65 python ingest.py
66 fi
67
68 counts > after.txt
69 cat after.txt
70 test -s after.txt || { echo "ERROR: archive is empty"; exit 1; }
71 awk -v first=before.txt '
72 FILENAME == first { was[$1] = $2; next }
73 { now[$1] = $2 }
74 END {
75 for (s in was)
76 if (now[s] + 0 < was[s] + 0) {
77 printf "ERROR: %s lost rows: %d -> %d\n", s, was[s], now[s]
78 bad = 1
79 }
80 exit bad
81 }' before.txt after.txt
82
83 # An amendment identical to its original means the digest drifted.
84 phantom=$(sqlite3 -noheader "$DB" "
85 SELECT COUNT(*) FROM incident_amendments a
86 JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key
87 WHERE a.agency IS o.agency AND a.case_id IS o.case_id
88 AND a.occurred_at IS o.occurred_at AND a.category IS o.category
89 AND a.call_type IS o.call_type AND a.disposition IS o.disposition
90 AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop
91 AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon")
92 if [ "$phantom" -ne 0 ]; then
93 echo "ERROR: $phantom amendments are identical to their original"
94 exit 1
95 fi
96
97 # Publish. The .new asset makes the sequence crash-safe: at every
98 # point at least one asset holds the full archive.
99 sqlite3 "$DB" "VACUUM;"
100 gzip -c "$DB" > metro.db.gz
101 $SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
102 $SSH $HOST release asset add $R archive metro.db.new.gz < metro.db.gz
103 $SSH $HOST release asset remove $R archive metro.db.gz 2>/dev/null || true
104 $SSH $HOST release asset add $R archive metro.db.gz < metro.db.gz
105 $SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
106 rm metro.db.gz
107 {
108 echo "SQLite archive of Omaha metro police incident feeds, rebuilt twice daily."
109 echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
110 echo "so this holds records their own feeds no longer serve."
111 echo
112 echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
113 echo
114 echo "incidents holds each record as first published; every later"
115 echo "version the feed served is a row in incident_amendments"
116 echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)."
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 $SSH $HOST "release edit $R archive --title 'Incident archive' --file -" < notes.md
126 rm notes.md
127 echo "archive published"
29 - sh ci/pull-publish.sh
12830 - |
12931 set -e
13032 export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db GIT_SSH_COMMAND="ssh -F $PWD/.ssh_config"
ci/pull-publish.sh added +101
@@ -0,0 +1,101 @@
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 python ingest.py
40fi
41
42counts > after.txt
43cat after.txt
44test -s after.txt || { echo "ERROR: archive is empty"; exit 1; }
45awk -v first=before.txt '
46 FILENAME == first { was[$1] = $2; next }
47 { now[$1] = $2 }
48 END {
49 for (s in was)
50 if (now[s] + 0 < was[s] + 0) {
51 printf "ERROR: %s lost rows: %d -> %d\n", s, was[s], now[s]
52 bad = 1
53 }
54 exit bad
55 }' before.txt after.txt
56
57# An amendment identical to its original means the digest drifted.
58phantom=$(sqlite3 -noheader "$DB" "
59 SELECT COUNT(*) FROM incident_amendments a
60 JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key
61 WHERE a.agency IS o.agency AND a.case_id IS o.case_id
62 AND a.occurred_at IS o.occurred_at AND a.category IS o.category
63 AND a.call_type IS o.call_type AND a.disposition IS o.disposition
64 AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop
65 AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon")
66if [ "$phantom" -ne 0 ]; then
67 echo "ERROR: $phantom amendments are identical to their original"
68 exit 1
69fi
70
71# Publish. The .new asset makes the sequence crash-safe: at every
72# point at least one asset holds the full archive.
73sqlite3 "$DB" "VACUUM;"
74gzip -c "$DB" > metro.db.gz
75$SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
76$SSH $HOST release asset add $R archive metro.db.new.gz < metro.db.gz
77$SSH $HOST release asset remove $R archive metro.db.gz 2>/dev/null || true
78$SSH $HOST release asset add $R archive metro.db.gz < metro.db.gz
79$SSH $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
80rm metro.db.gz
81{
82 echo "SQLite archive of Omaha metro police incident feeds, rebuilt twice daily."
83 echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
84 echo "so this holds records their own feeds no longer serve."
85 echo
86 echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
87 echo
88 echo "incidents holds each record as first published; every later"
89 echo "version the feed served is a row in incident_amendments"
90 echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)."
91 echo
92 echo '```'
93 sqlite3 -header -column "$DB" \
94 "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
95 MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
96 FROM incidents GROUP BY agency ORDER BY rows DESC"
97 echo '```'
98} > notes.md
99$SSH $HOST "release edit $R archive --title 'Incident archive' --file -" < notes.md
100rm notes.md
101echo "archive published"