Sync download stats #75
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Sync download stats | |
| # Hourly pull of aggregate download counts from Cloudflare Workers Analytics | |
| # Engine (populated by the pearos-dl Worker on every real download), plus a | |
| # manual trigger for re-reading on demand instead of waiting for the next hour. | |
| # This only feeds /stats/ -- the WhatsApp report runs on its own separate | |
| # schedule (whatsapp-report.yml, 11:00 UTC) and isn't affected by this cadence. | |
| on: | |
| schedule: | |
| - cron: "7 * * * *" # every hour, at :07 -- GitHub's own docs warn that | |
| # exact-hour ("0 * * * *") schedules are the most congested slot on their | |
| # infrastructure and get delayed/dropped more often; a few minutes' offset | |
| # avoids that pile-up. | |
| push: | |
| branches: [main] | |
| paths-ignore: | |
| # This job's own commit only ever touches these two files -- excluding | |
| # them stops its PAT-authored push (which, unlike the default token, | |
| # does trigger other workflows) from re-triggering itself in a loop. | |
| # Any other push (new/changed pages, a redeploy, etc.) still fires it, | |
| # so /stats/ reflects a rebuild immediately instead of waiting up to an | |
| # hour for the next cron tick. | |
| - 'assets/data/download-stats.json' | |
| - 'assets/data/download-chart.png' | |
| workflow_dispatch: | |
| permissions: | |
| contents: write | |
| jobs: | |
| sync-stats: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Checking out source code | |
| uses: actions/checkout@v4 | |
| with: | |
| # A push made with the default GITHUB_TOKEN does NOT trigger other | |
| # workflows (GitHub's anti-loop safeguard) -- this repo's | |
| # whatsapp-report.yml is path-filtered on this workflow's own | |
| # commit, so it needs a real PAT here to fire correctly. | |
| token: ${{ secrets.WEB_REPO_PAT }} | |
| - name: Query Analytics Engine | |
| env: | |
| CF_ACCOUNT_ID: ${{ secrets.CF_ACCOUNT_ID }} | |
| CF_ANALYTICS_TOKEN: ${{ secrets.CF_ANALYTICS_TOKEN }} | |
| run: | | |
| query() { | |
| curl -fsSL -X POST \ | |
| "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/analytics_engine/sql" \ | |
| -H "Authorization: Bearer ${CF_ANALYTICS_TOKEN}" \ | |
| -H "Content-Type: text/plain" \ | |
| --data "$1" | |
| } | |
| # Single atomic query for all the headline counts -- three separate | |
| # sequential queries (total / 30d / 24h) can each land a few seconds | |
| # apart, and a real download landing in that gap gets counted by a | |
| # later query but missed by an earlier one, producing "impossible" | |
| # numbers like last_24h > total_all_time. countIf() over one pass | |
| # guarantees they all come from the same snapshot. | |
| totals=$(query "SELECT count() AS total, countIf(timestamp > NOW() - INTERVAL '30' DAY) AS last30d, countIf(timestamp > NOW() - INTERVAL '1' DAY) AS last24h FROM pearos_downloads") | |
| total_all_time=$(echo "$totals" | jq -r '.data[0].total // 0') | |
| last_30d=$(echo "$totals" | jq -r '.data[0].last30d // 0') | |
| last_24h=$(echo "$totals" | jq -r '.data[0].last24h // 0') | |
| by_file=$(query "SELECT blob1 AS file, blob2 AS tier, count() AS downloads, sum(double1) AS bytes FROM pearos_downloads GROUP BY blob1, blob2" | jq -c '.data // []') | |
| daily=$(query "SELECT toStartOfInterval(timestamp, INTERVAL '1' DAY) AS day, blob2 AS tier, count() AS downloads FROM pearos_downloads WHERE timestamp > NOW() - INTERVAL '30' DAY GROUP BY day, tier ORDER BY day" | jq -c '[.data[]? | {date: (.day[0:10]), tier: .tier, downloads: (.downloads | tonumber)}]') | |
| by_country=$(query "SELECT blob3 AS country, count() AS downloads FROM pearos_downloads GROUP BY blob3 ORDER BY downloads DESC LIMIT 10" | jq -c '[.data[]? | {country: .country, downloads: (.downloads | tonumber)}]') | |
| by_referrer=$(query "SELECT blob4 AS referrer, count() AS downloads FROM pearos_downloads GROUP BY blob4 ORDER BY downloads DESC LIMIT 10" | jq -c '[.data[]? | {referrer: .referrer, downloads: (.downloads | tonumber)}]') | |
| by_hour=$(query "SELECT toHour(timestamp) AS hour, count() AS downloads FROM pearos_downloads GROUP BY hour ORDER BY hour" | jq -c '[.data[]? | {hour: (.hour | tonumber), downloads: (.downloads | tonumber)}]') | |
| # Friends/family link usage, tracked separately -- a sudden spike | |
| # here (especially in the last 24h) is the tell that the static | |
| # FRIENDS_TOKEN leaked out to a forum somewhere instead of staying | |
| # with the people it was actually given to. | |
| friend_totals=$(query "SELECT countIf(blob2 = 'friend') AS total, countIf(blob2 = 'friend' AND timestamp > NOW() - INTERVAL '1' DAY) AS last24h FROM pearos_downloads") | |
| friend_all_time=$(echo "$friend_totals" | jq -r '.data[0].total // 0') | |
| friend_last_24h=$(echo "$friend_totals" | jq -r '.data[0].last24h // 0') | |
| echo "All-time: $total_all_time, last 30d: $last_30d, last 24h: $last_24h" | |
| echo "By file: $by_file" | |
| echo "Daily: $daily" | |
| echo "By country: $by_country" | |
| echo "By referrer: $by_referrer" | |
| echo "By hour: $by_hour" | |
| echo "Friend link -- all-time: $friend_all_time, last 24h: $friend_last_24h" | |
| jq -n \ | |
| --arg generated_at "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \ | |
| --argjson total_all_time "$total_all_time" \ | |
| --argjson last_30d "$last_30d" \ | |
| --argjson last_24h "$last_24h" \ | |
| --argjson by_file "$by_file" \ | |
| --argjson daily "$daily" \ | |
| --argjson by_country "$by_country" \ | |
| --argjson by_referrer "$by_referrer" \ | |
| --argjson by_hour "$by_hour" \ | |
| --argjson friend_all_time "$friend_all_time" \ | |
| --argjson friend_last_24h "$friend_last_24h" \ | |
| '{generated_at:$generated_at, total_all_time:$total_all_time, last_30d:$last_30d, last_24h:$last_24h, by_file:$by_file, daily:$daily, by_country:$by_country, by_referrer:$by_referrer, by_hour:$by_hour, friend_all_time:$friend_all_time, friend_last_24h:$friend_last_24h}' \ | |
| > assets/data/download-stats.json | |
| - name: Render trend chart PNG (for WhatsApp / anywhere a plain image is needed) | |
| run: | | |
| pip install --quiet matplotlib | |
| python3 .github/scripts/render_chart.py | |
| - name: Commit and push if changed | |
| run: | | |
| git config user.name "pearOS build bot" | |
| git config user.email "actions@users.noreply.github.com" | |
| if git diff --quiet -- assets/data/download-stats.json assets/data/download-chart.png; then | |
| echo "Stats unchanged, nothing to commit." | |
| exit 0 | |
| fi | |
| git add assets/data/download-stats.json assets/data/download-chart.png | |
| git commit -m "Sync download stats" | |
| # Other workflows (uptime state, DNS failover reruns) commit to | |
| # main independently -- a plain push here can lose a race and fail | |
| # outright. Retry with a rebase instead of failing on collision. | |
| for i in 1 2 3 4 5; do | |
| git push origin HEAD:main && exit 0 | |
| git fetch origin main | |
| git rebase origin/main | |
| sleep $((RANDOM % 5 + 1)) | |
| done | |
| git push origin HEAD:main |