Device Login Round-Trip — audit_logs cleanup #1
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
| # SMI-4460 — weekly cleanup of audit_logs rows generated by the device-login | |
| # round-trip e2e test user. Prevents the test user from dominating the staging | |
| # audit-log distribution (~30 PR runs/week + nightly). | |
| # | |
| # Filter: event_type='auth:device_code:consumed' AND metadata->>'user_id'=$E2E_TEST_USER_ID | |
| # AND created_at < now() - interval '7 days'. | |
| # | |
| # IMPORTANT: audit_logs has no user_id column (per CLAUDE.md memory | |
| # `feedback_audit_logs_no_user_id_column.md`). The metadata->>'user_id' | |
| # predicate is required. | |
| name: Device Login Round-Trip — audit_logs cleanup | |
| on: | |
| schedule: | |
| - cron: '0 4 * * 0' # Sundays 04:00 UTC | |
| workflow_dispatch: {} | |
| permissions: | |
| contents: read | |
| jobs: | |
| cleanup: | |
| name: Delete >7-day audit_logs rows for test user | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 5 | |
| environment: e2e-staging | |
| permissions: | |
| contents: read | |
| env: | |
| STAGING_SUPABASE_URL: ${{ secrets.STAGING_SUPABASE_URL }} | |
| E2E_TEST_USER_ID: ${{ secrets.E2E_TEST_USER_ID }} | |
| steps: | |
| - name: Refuse if env points at prod | |
| run: | | |
| set -eu | |
| if printf '%s' "$STAGING_SUPABASE_URL" | grep -q 'vrcnzpmndtroqxxoqkzy'; then | |
| echo "::error::STAGING_SUPABASE_URL contains prod ref — refusing to run" | |
| exit 1 | |
| fi | |
| - name: Delete stale audit_logs rows (metadata->>'user_id') | |
| env: | |
| PGPASSWORD: ${{ secrets.STAGING_DB_PASSWORD }} | |
| run: | | |
| set -eu | |
| REF="$(printf '%s' "$STAGING_SUPABASE_URL" | sed -E 's#^https?://([^.]+)\..*#\1#')" | |
| if [ -z "$REF" ] || [ -z "$E2E_TEST_USER_ID" ]; then | |
| echo "::error::missing REF or E2E_TEST_USER_ID" | |
| exit 1 | |
| fi | |
| # NOTE: audit_logs.metadata is jsonb; ->>'user_id' yields text. | |
| # Bound the delete with a sub-second statement_timeout fallback if | |
| # something pathological happens; pg_safeupdate is enforced at the | |
| # database level so the WHERE clause is mandatory. | |
| psql \ | |
| "host=aws-0-us-west-1.pooler.supabase.com port=6543 dbname=postgres user=postgres.${REF} sslmode=require" \ | |
| -v ON_ERROR_STOP=1 \ | |
| -P pager=off \ | |
| -c "DELETE FROM audit_logs WHERE event_type = 'auth:device_code:consumed' AND metadata->>'user_id' = '${E2E_TEST_USER_ID}' AND created_at < now() - interval '7 days';" |