db-snapshot

<critical_rules>

1. NEVER drop, truncate, or modify the running DB. This agent only reads. If you somehow find yourself about to run DROP, TRUNCATE, DELETE, UPDATE, or INSERT against the live DB, stop.
2. NEVER overwrite an existing snapshot file. If your generated filename collides, append a counter suffix and try again. The caller's previous snapshot is sacred.
3. Always dump the dev DB, not the test DB. Test DBs get wiped by the test runner between runs and aren't worth snapshotting.
4. Dump structure + data by default. No --no-data. Use a transactionally-consistent dump option (--single-transaction for MySQL InnoDB, --no-lock care for big tables).
5. Snapshots go under backups/db/ at the repo root. Create the directory if missing. This path MUST be gitignored — confirm it is before writing, and never commit a .sql dump to git.
6. Verify the dump succeeded by checking exit code AND that the file is non-empty (>1KB). A 0-byte dump means the engine refused; report that as failure, do not return a bogus path.
7. Refuse to dump if the DB is unreachable. Don't write a 0-byte file and pretend everything is fine. </critical_rules>

Step 1 — Sanity-check the environment.

docker-compose ps <DB_SERVICE> --format "table {{.Service}}\t{{.State}}"

If the DB container is not running, stop and report — do not attempt the dump.

Step 2 — Confirm the snapshot directory exists and is gitignored.

mkdir -p backups/db
grep -qE '^backups/?$|^backups/db/?$' .gitignore || echo "WARNING: backups/ may not be gitignored"

If the warning fires, mention it in the final reply — don't silently proceed.

Step 3 — Build the filename.

backups/db/<DB_NAME>_<UTC-timestamp>_<context>.sql

• <UTC-timestamp>: date -u +%Y%m%d_%H%M%S
• <context>: short kebab-case label the caller passed (e.g. pre-migrate-fresh, pre-flows-rebuild). If no context given, use manual.

Check that the file does NOT already exist. If it does (rare — same-second collision), append _1, _2, ... until unique.

Step 4 — Dump (MySQL example).

docker exec <DB_CONTAINER> sh -c '
  mysqldump \
    --single-transaction \
    --quick \
    --routines \
    --triggers \
    --skip-lock-tables \
    --default-character-set=utf8mb4 \
    -u root -p"$MYSQL_ROOT_PASSWORD" \
    <DB_NAME>
' > "$DUMP_PATH" 2>/tmp/db-snapshot.err

Notes:

• --single-transaction gives a consistent snapshot of InnoDB tables without locking writers.
• The root password is available inside the container as an env var (set by docker-compose). Use it inline via sh -c to avoid leaking it into shell history.
• Redirect stderr to a temp file so you can inspect failures without polluting the dump file.

Step 5 — Verify.

[ -s "$DUMP_PATH" ] && wc -c < "$DUMP_PATH"

File must exist AND be > 1024 bytes (a meaningful schema is at least that). If smaller, treat as failed, delete the partial file, and report.