fly-ops

name	fly-ops
description	Database, log, and performance ops against the deployed Petra Fly Machines. Use this skill when the user asks to run a query, read logs, capture a profile, snapshot the database, or otherwise inspect/mutate a deployed environment. Handles waking the scale-to-zero instance, defaults to production with explicit safety steps for writes.
argument-hint	[task description, e.g. 'how many users on staging' or 'profile prod cpu']
allowed-tools	["Bash(make fly-wake*)","Bash(make fly-sql-readonly*)","Bash(make fly-sql-write*)","Bash(make fly-backup*)","Bash(make fly-logs*)","Bash(make fly-pprof-cpu*)","Bash(make fly-pprof-goroutine*)","Bash(make fly-sqlite3*)","Bash(make migratetest*)","Bash(curl https://petra.fly.dev*)","Bash(curl https://petra-staging.fly.dev*)","Bash(curl https://pr-*-myrjola-petrapp.fly.dev*)","Bash(fly status*)","Bash(fly logs*)","Bash(fly proxy*)","Bash(fly releases*)","Bash(fly ssh console*)","Bash(fly ssh sftp*)","Bash(go tool pprof*)"]

Fly ops for Petra

You're operating against deployed Fly Machines. The deployment is a single-node SQLite app that scales to zero, with Litestream replicating to S3.

Environments

Target	FLY_APP	URL
Prod	petra	https://petra.fly.dev
Staging	petra-staging	https://petra-staging.fly.dev
Review app	pr-<N>-myrjola-petrapp	https://pr-<N>-myrjola-petrapp.fly.dev

Default target is production (petra). All make fly-* targets accept FLY_APP=... to retarget. Be explicit in every command — pass FLY_APP=... even when targeting prod, so the target is visible in the transcript. Review-app names are derived from the GitHub repo slug (myrjola/petrapp), not the deployed app names — that's why prod is petra but PR apps say petrapp.

Deploys are continuous; do not run fly deploy

Deployment is automated by GitHub Actions:

• Push to main → tests run (make ci-full + make migratetest against the latest prod backup), then Docker image is built and pushed, then staging is deployed, then prod is deployed if staging passes.
• Open/sync a PR → a review app at pr-<N>-myrjola-petrapp.fly.dev is provisioned. It tears down on PR close.

So when a user asks you to "deploy" a change, the answer is virtually always: make sure CI is green, then merge the PR. Don't run fly deploy from this skill. The only legitimate exception is a rollback to a prior image when CD itself is the cause of an incident — and that needs explicit human approval each time.

The full flow lives in docs/operations.md under "CI/CD and preview environments". When walking a user through a risky change, point them at:

1. Open a PR → CI runs make migratetest against real prod data (this catches migration bugs).
2. Manually smoke-test the review app with make fly-* FLY_APP=pr-<N>-myrjola-petrapp.
3. Merge to main when happy → staging then prod auto-deploy.
4. Watch make fly-logs on staging and prod for the migration log lines.

Safety protocol

Match action to risk:

• Reads (SELECT-only, logs, profiles): run without confirmation.
• Writes against staging: confirm the SQL with the user before executing.
• Writes against prod: always confirm. Always show the user (a) the exact command you'll run, (b) the SQL contents, and (c) confirmation that the pre-write backup completed. Never bundle multiple destructive statements in one script unless the user explicitly asked for that.
• Schema migrations: prefer running them from the codebase's declarative migration path (a deploy will trigger migrateTo) rather than ad-hoc fly-sql-write. If you must use fly-sql-write for a one-shot, snapshot first (the target does this automatically) and tell the user where the snapshot lives.

If you're unsure whether a query mutates state, treat it as a write.

Mutating SQL run against prod is recorded in docs/ops-log/ as YYYY-MM-DD-<slug>.{md,sql} (conventions in docs/README.md) — it is the only durable record of manual prod surgery.

Workflow patterns

1. Run SQL against the deployed database

For any query, write it to a file first (this avoids shell-escaping issues) and route through the make target. The target handles waking and read-only enforcement.

# Read-only:
cat > /tmp/q.sql <<'SQL'
SELECT COUNT(*) AS users, MAX(created) AS most_recent FROM users;
SQL
make fly-sql-readonly SCRIPT=/tmp/q.sql FLY_APP=petra

For writes, the fly-sql-write target snapshots the DB on the machine first, then pipes the script via SSH stdin into sqlite3 — same pattern as fly-sql-readonly, so nothing is written to disk on the remote:

cat > /tmp/migration.sql <<'SQL'
UPDATE feature_flags SET enabled = 1 WHERE name = 'maintenance_mode';
SQL
# Confirm with the user before running this:
make fly-sql-write SCRIPT=/tmp/migration.sql FLY_APP=petra

The user's SQL files live in /tmp/ by convention — they're transient. Do not commit them.

fly-sql-write runs sqlite3 with PRAGMA foreign_keys = ON, so ON DELETE CASCADE fires the way the app expects. This matters for deletes: removing a parent row (e.g. a workout_sessions row) cascades to its children (exercise_slots, exercise_sets). Without the pragma — sqlite3's per-connection default is OFF — the parent vanishes but the children are silently orphaned, and the next insert that reuses the same key fails with a UNIQUE constraint failed. If you must scope a delete to children only, target the child tables explicitly; don't rely on a parent delete that can't cascade.

2. Troubleshoot issues by reading logs

make fly-logs FLY_APP=petra
make fly-logs FLY_APP=petra-staging | grep -i error

fly logs --no-tail returns a bounded snapshot. To narrow the window or filter by route, pipe to grep. When you see a stack trace or error message, look up the relevant code:

• HTTP handlers: cmd/petra/handler-*.go
• Service layer: internal/petra/service/
• DB layer: internal/petra/repository/, internal/platform/sqlitekit/

For request-timeout traces, docs/operations.md ("Flight recorder") explains how to fetch them via fly sftp get and analyze with go tool trace.

3. Investigate performance issues

The fly-pprof-* targets manage the proxy lifecycle automatically — they spawn fly proxy in the background, wait for it, capture, then tear it down on exit:

make fly-pprof-cpu FLY_APP=petra          # 30s CPU profile
make fly-pprof-goroutine FLY_APP=petra    # goroutine dump

Profiles land in pprof/ (gitignored). Open them with:

go tool pprof -top pprof/cpu-petra-<timestamp>.pb.gz
# or interactive:
go tool pprof --http=: pprof/cpu-petra-<timestamp>.pb.gz

4. Database backups before dangerous operations

make fly-sql-write always invokes make fly-backup first, so a normal write flow is already covered. Run fly-backup on its own when you want a snapshot before a sequence of operations that don't all go through fly-sql-write (e.g., a deploy that includes a migration, or before manual investigation that might lead to a write):

make fly-backup FLY_APP=petra
# → /data/snapshots/petrapp-petra-<timestamp>.sqlite3 on the Fly machine

The snapshot is taken with sqlite3 .backup, which is a single consistent file (no separate WAL). It lives on the same volume as the live database. Litestream's continuous replication is the second line of defense and runs independently.

If a write goes wrong and you need to roll back, the snapshot path will be in the output of the preceding fly-sql-write invocation. Restoring it requires shelling in and copying:

fly ssh console --app petra --user petrapp \
  -C "/bin/sh -c 'cp /data/snapshots/<snapshot>.sqlite3 /data/petrapp.sqlite3'"

Pause and confirm with the user before any restore — it overwrites the live DB.

5. Recover deleted/overwritten data via Litestream point-in-time restore

When you need data that's no longer in the live DB but is still within Litestream's retention window (litestream.yml: 168h / 7 days for daily snapshots, 24h for hourly, 1h for 5-min levels), restore a snapshot to a scratch path, query it read-only, then clean up. The make targets don't cover this — raw fly ssh console is the right tool here.

# Wake first; the litestream binary needs the machine running.
make fly-wake FLY_APP=petra

# Restore the DB as it was at a chosen UTC timestamp to /tmp/recovery.sqlite3.
# Never restore over /data/petrapp.sqlite3 — always to a scratch path under /tmp.
fly ssh console --app petra --user petrapp -C \
  "/dist/litestream restore -config /etc/litestream.yml \
     -timestamp 2026-05-01T11:00:00Z \
     -o /tmp/recovery.sqlite3 \
     /data/petrapp.sqlite3"

# Query the scratch DB read-only. Upload an extract script via sftp if needed.
fly ssh console --app petra --user petrapp -C \
  "/usr/bin/sqlite3 -readonly /tmp/recovery.sqlite3 'SELECT ...'"

# Clean up. The restored DB is petrapp-owned (since litestream ran as petrapp),
# so the cleanup command uses --user petrapp too.
fly ssh console --app petra --user petrapp -C "/bin/sh -c 'rm -f /tmp/recovery.sqlite3'"

Litestream binary lives at /dist/litestream; config at /etc/litestream.yml (set in the Dockerfile). The available subcommands are databases | info | list | ltx | register | replicate | reset | restore | start | status | stop | sync | unregister | version — there is no snapshots subcommand; use ltx to list available LTX files for the live DB.

Pick the -timestamp carefully: it must be ISO 8601 UTC, and it must be inside Litestream's retention window (older data is gone). Restore is non-destructive when -o points to a scratch path, so you can iterate freely.

Don't

• Don't run fly deploy. Deployment is CI-driven (push to main → staging → prod; PR → review app). See docs/operations.md "CI/CD and preview environments".
• Don't invoke fly commands without --app $FLY_APP (they fail with a confusing error and may silently target the wrong app if FLY_APP happens to be exported).
• Don't use make fly-sqlite3 from this skill — it's an interactive REPL meant for humans. Use fly-sql-readonly or fly-sql-write instead.
• Don't re-implement the make targets with raw fly ssh console for ops the targets already cover (fly-sql-readonly, fly-sql-write, fly-backup, fly-logs, fly-pprof-*). The targets handle waking, backup, and cleanup; reproducing those by hand is error-prone. Raw fly ssh console is the right tool for ops the targets don't cover (litestream, file inspection, ad-hoc shell) — but always prefix with make fly-wake FLY_APP=$FLY_APP && so the command doesn't time out against a cold machine.
• Don't forget --user petrapp on fly ssh console and fly ssh sftp shell — the default is root, and files written by root can't be cleaned up by the petrapp user later. The Makefile passes --user petrapp for both; if you're invoking fly ssh by hand, do the same.
• Don't paste the user's database contents into chat without confirmation — query results may contain PII.