Bite 33.2 — Backup Creation, Retention, and Verification - RodrigoMattosoSilveira/EnterpriseRemoteSystems GitHub Wiki

Bite 33.2 — Backup Creation, Retention, and Verification

Manual Verification Checklist Before Promotion

Branch: 930-bite-332-backup-creation-retention-and-verification
SHA: 05e9eff3b56c5a1308c17c131e188633e4e0e3b6

1. Purpose

These manual tests verify the operator-visible behavior introduced by Bite 33.2:

  • a live SQLite database can be snapshotted with SQLite's online .backup operation;
  • a backup is not accepted until an adjacent verified JSON manifest exists;
  • an operator independently records the backup file SHA-256 immediately after creation;
  • the manifest checksum must match that independently recorded SHA-256;
  • the same recorded SHA-256 is re-checked later to prove the retained backup has not changed;
  • the manifest contains recovery evidence but not application credentials/secrets;
  • an accepted backup can be independently re-verified later through the supported Make target;
  • verification rejects the wrong environment, a modified backup, and a missing manifest;
  • retention keeps at least the configured minimum number of verified backups and all verified backups within the configured age window;
  • retention fails closed and deletes nothing when any managed backup pair is corrupt;
  • all testing is performed against the LOCAL database and disposable files under /tmp/ers-332-manual.

Off-host replication is not part of Bite 33.2. It belongs to Bite 33.3.

2. Safety Rules

Follow these rules throughout this suite:

  1. Run every command from the ERS repository root unless a step explicitly says otherwise.
  2. Do not point any command in this suite at Test or Production.
  3. Do not use /opt/EnterpriseRemoteSystems/test or /opt/EnterpriseRemoteSystems/production.
  4. Do not edit or delete backend/data/app.db as part of these tests.
  5. All backup artifacts created by this suite must stay under:
/tmp/ers-332-manual
  1. Tests that intentionally expect a command to fail explicitly say so. A non-zero exit status in those tests is the expected PASS behavior.
  2. Keep Terminal 1 running the LOCAL backend until Test 10 is complete.

3. Automated Prerequisite

The following are prerequisites for starting manual promotion verification:

make local-check
CI

Both must already be passing for this SHA.

4. Test 01 — Establish the Exact LOCAL Source Database

Identity

No ERS login is required.

Purpose

Pin the LOCAL backend to the exact SQLite database that will be snapshotted during this suite and verify that the source database is healthy before creating backup evidence.

Terminal 1 — Confirm Branch and SHA

Run:

git branch --show-current
git rev-parse HEAD

Expected:

930-bite-332-backup-creation-retention-and-verification
05e9eff3b56c5a1308c17c131e188633e4e0e3b6

If either value differs, STOP. Do not continue this manual suite against a different baseline.

Terminal 2 — Confirm Required Local Tools

Run:

command -v sqlite3
command -v python3
command -v jq

Expected:

  • each command prints a path;
  • none of the commands returns an empty result.

If jq is missing, install it before continuing because later verification steps use it to inspect the backup manifest explicitly.

Terminal 2 — Prepare the Disposable Manual-Test Directory

Run:

rm -rf /tmp/ers-332-manual
mkdir -p /tmp/ers-332-manual/good

Confirm:

ls -ld /tmp/ers-332-manual /tmp/ers-332-manual/good

Expected: both directories exist.

Terminal 1 — Start the LOCAL Backend Against the Normal LOCAL Database

If another make local-backend process for this repository is already running, stop that process first with Ctrl+C.

Then run:

APP_ENV=development \
ERS_DATABASE_PATH="$(pwd)/backend/data/app.db" \
make local-backend

Leave Terminal 1 running.

Terminal 2 — Confirm the Backend Is Running

Run:

curl -fsS http://localhost:8080/api/v1/healthz

Expected: the command succeeds without a curl error.

Immediately run:

echo $?

Expected:

0

Terminal 2 — Confirm Source Database Integrity

Run:

sqlite3 backend/data/app.db 'PRAGMA integrity_check;'

Expected:

ok

Then run:

sqlite3 backend/data/app.db 'PRAGMA foreign_key_check;'

Expected: no rows and no output.

Confirm the latest migration:

sqlite3 backend/data/app.db \
  'SELECT filename FROM schema_migrations ORDER BY filename DESC LIMIT 1;'

Expected:

000077_journey_bonus_award_approval.up.sql

PASS Criteria

PASS only if:

  • branch and SHA exactly match this suite;
  • the backend is running against backend/data/app.db;
  • health check succeeds;
  • PRAGMA integrity_check returns ok;
  • PRAGMA foreign_key_check returns no rows;
  • latest migration is 000077_journey_bonus_award_approval.up.sql.

Promotion Blocker

Do not promote if the source LOCAL database is already corrupt, has foreign-key violations, or is not at the expected migration level.

5. Test 02 — Create a Live SQLite Backup and Verified Manifest

Identity

No ERS login is required.

Prerequisite

Terminal 1 from Test 01 must still be running the LOCAL backend against:

backend/data/app.db

Terminal 2 — Create a Transactionally Consistent SQLite Snapshot

Run exactly:

GOOD_DIR=/tmp/ers-332-manual/good
GOOD_BACKUP="$GOOD_DIR/app-$(date -u +%Y%m%dT%H%M%SZ).db"

sqlite3 backend/data/app.db ".backup '$GOOD_BACKUP'"

printf '%s\n' "$GOOD_BACKUP" > /tmp/ers-332-manual/GOOD_BACKUP_PATH

Confirm the backup exists and is non-empty:

ls -lh "$GOOD_BACKUP"
test -s "$GOOD_BACKUP" && echo 'PASS: backup file is non-empty'

Expected final line:

PASS: backup file is non-empty

Record an Independent SHA-256 for Later Verification

Run:

GOOD_BACKUP_SHA256="$(shasum -a 256 "$GOOD_BACKUP" | awk '{print $1}')"

printf '%s\n' "$GOOD_BACKUP_SHA256" \
  > /tmp/ers-332-manual/GOOD_BACKUP_SHA256

printf 'Recorded backup SHA-256: %s\n' "$GOOD_BACKUP_SHA256"

Expected:

  • one 64-character hexadecimal SHA-256 value is printed;
  • /tmp/ers-332-manual/GOOD_BACKUP_SHA256 contains exactly that value.

Confirm:

cat /tmp/ers-332-manual/GOOD_BACKUP_SHA256

Record this value in your test notes:

Known-good backup SHA-256:

This checksum is the operator-observed reference value used in later tests.

Create the 33.2 Manifest

Run:

python3 scripts/ers-backup.py create-manifest \
  --backup "$GOOD_BACKUP" \
  --environment development \
  --source-container local-manual-test \
  --source-database-path "$(pwd)/backend/data/app.db"

Expected output includes all of the following:

Verified backup manifest written to
Backup SHA-256:
Latest schema migration: 000077_journey_bonus_award_approval.up.sql

Define the manifest path:

GOOD_MANIFEST="${GOOD_BACKUP}.manifest.json"

Confirm both files exist:

test -s "$GOOD_BACKUP" && echo 'PASS: backup exists'
test -s "$GOOD_MANIFEST" && echo 'PASS: manifest exists'

Expected:

PASS: backup exists
PASS: manifest exists

Verify the Snapshot Itself

Run:

sqlite3 "$GOOD_BACKUP" 'PRAGMA integrity_check;'

Expected:

ok

Then:

sqlite3 "$GOOD_BACKUP" 'PRAGMA foreign_key_check;'

Expected: no rows and no output.

PASS Criteria

PASS only if:

  • SQLite .backup succeeds while the LOCAL backend remains running;
  • the backup file is non-empty;
  • an independent SHA-256 is recorded in /tmp/ers-332-manual/GOOD_BACKUP_SHA256;
  • the recorded SHA-256 is exactly 64 hexadecimal characters;
  • manifest creation succeeds;
  • the adjacent .manifest.json file exists;
  • backup integrity is ok;
  • foreign-key check is empty;
  • the manifest reports latest migration 000077_journey_bonus_award_approval.up.sql.

Promotion Blocker

Do not promote if a backup can be created but cannot be verified into a managed backup/manifest pair.

6. Test 03 — Inspect Manifest Evidence Explicitly

Identity

No ERS login is required.

Terminal 2 — Recover the Good Backup Paths

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
GOOD_MANIFEST="${GOOD_BACKUP}.manifest.json"

Display the Manifest

Run:

jq . "$GOOD_MANIFEST"

Visually confirm the manifest is readable JSON.

Assert Required Manifest Fields

Run exactly:

jq -e \
  --arg dbpath "$(pwd)/backend/data/app.db" \
  --arg filename "$(basename "$GOOD_BACKUP")" '
    .format_version == 1 and
    .status == "verified" and
    .environment == "development" and
    .source.container == "local-manual-test" and
    .source.database_path == $dbpath and
    .backup.file == $filename and
    .backup.size_bytes > 0 and
    (.backup.sha256 | length) == 64 and
    .verification.integrity_check == "ok" and
    .verification.foreign_key_check == "ok" and
    .verification.schema_migration_count > 0 and
    .verification.latest_schema_migration == "000077_journey_bonus_award_approval.up.sql" and
    (.verification.required_tables | index("schema_migrations") != null) and
    (.verification.required_tables | index("tenants") != null) and
    (.verification.required_tables | index("auth_user_accounts") != null) and
    (.verification.required_tables | index("authz_actors") != null) and
    (.verification.required_tables | index("collaborator_journeys") != null) and
    (.verification.required_tables | index("ledger_entries") != null) and
    (.verification.required_tables | index("work_periods") != null)
  ' "$GOOD_MANIFEST"

Expected:

true

Immediately run:

echo $?

Expected:

0

Compare the Manifest SHA-256 with the Independently Recorded SHA-256

Run:

RECORDED_SHA256="$(cat /tmp/ers-332-manual/GOOD_BACKUP_SHA256)"
MANIFEST_SHA256="$(jq -r '.backup.sha256' "$GOOD_MANIFEST")"

printf 'Recorded SHA-256: %s\n' "$RECORDED_SHA256"
printf 'Manifest SHA-256: %s\n' "$MANIFEST_SHA256"

test "$RECORDED_SHA256" = "$MANIFEST_SHA256" && \
  echo 'PASS: manifest checksum matches independently recorded backup checksum'

Expected final line:

PASS: manifest checksum matches independently recorded backup checksum

If that PASS line is not printed, STOP. Do not continue the suite.

Confirm No Credential/Secret Fields Are Present

Run:

if grep -Eqi \
  'password|jwt_secret|openai_api_key|ledger_correction_key|ledger_settlement_key' \
  "$GOOD_MANIFEST"; then
  echo 'FAIL: manifest contains a credential/secret field'
  exit 1
else
  echo 'PASS: manifest contains no credential/secret fields'
fi

Expected:

PASS: manifest contains no credential/secret fields

PASS Criteria

PASS only if:

  • manifest is valid JSON;
  • status is verified;
  • environment is development;
  • source path is the exact LOCAL database used in Test 01;
  • size and SHA evidence are present;
  • the manifest SHA-256 exactly matches the independently recorded checksum from Test 02;
  • integrity and foreign-key evidence are ok;
  • migration evidence is present;
  • required ERS table evidence is present;
  • no password/secret fields are present.

Promotion Blocker

Do not promote if the manifest lacks recovery evidence or contains application credentials/secrets.

7. Test 04 — Independently Re-Verify the Retained Backup Through the Supported Make Target

Identity

No ERS login is required.

Terminal 2

Recover the known-good backup path and independently recorded checksum:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
RECORDED_SHA256="$(cat /tmp/ers-332-manual/GOOD_BACKUP_SHA256)"

Before invoking ERS verification, independently recompute the backup checksum:

CURRENT_SHA256="$(shasum -a 256 "$GOOD_BACKUP" | awk '{print $1}')"

printf 'Recorded SHA-256: %s\n' "$RECORDED_SHA256"
printf 'Current SHA-256:  %s\n' "$CURRENT_SHA256"

test "$RECORDED_SHA256" = "$CURRENT_SHA256" && \
  echo 'PASS: retained backup checksum is unchanged before independent verification'

Expected final line:

PASS: retained backup checksum is unchanged before independent verification

Now run the supported ERS verification target:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$GOOD_BACKUP"

Expected output includes:

Backup verification passed:
Environment: development
SHA-256:
Latest schema migration: 000077_journey_bonus_award_approval.up.sql

Immediately run:

echo $?

Expected:

0

PASS Criteria

PASS only if:

  • the backup SHA-256 recomputed immediately before verification exactly matches the checksum recorded in Test 02;
  • the independently invoked Make target reopens and verifies the retained backup/manifest pair successfully.

Promotion Blocker

Do not promote if a newly accepted backup cannot later be re-verified independently.

8. Test 05 — Wrong Environment Is Rejected

Identity

No ERS login is required.

Purpose

Prove that a Development backup cannot be presented as Production recovery evidence.

Terminal 2

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"

make server-backup-verify \
  ENV=production \
  BACKUP_FILE="$GOOD_BACKUP"

STATUS=$?
printf 'verification exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes:

Backup verification error: backup environment mismatch: expected production, manifest declares development

Expected status is non-zero. With the current implementation, Make normally reports status 2.

Now prove the original backup is still valid:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$GOOD_BACKUP"

Expected: verification passes.

PASS Criteria

PASS only if:

  • Production verification rejects the Development backup;
  • the error explicitly identifies the environment mismatch;
  • the backup and manifest are not modified;
  • Development verification still succeeds afterward.

Promotion Blocker

Do not promote if backup evidence from one environment can be accepted as another environment's backup.

9. Test 06 — Tampered Backup Is Rejected Without Damaging the Good Backup

Identity

No ERS login is required.

Terminal 2 — Create a Disposable Tampered Copy

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
GOOD_MANIFEST="${GOOD_BACKUP}.manifest.json"
TAMPER_DIR=/tmp/ers-332-manual/tampered

rm -rf "$TAMPER_DIR"
mkdir -p "$TAMPER_DIR"

TAMPER_BACKUP="$TAMPER_DIR/$(basename "$GOOD_BACKUP")"
TAMPER_MANIFEST="${TAMPER_BACKUP}.manifest.json"

cp "$GOOD_BACKUP" "$TAMPER_BACKUP"
cp "$GOOD_MANIFEST" "$TAMPER_MANIFEST"

printf '\nTAMPER\n' >> "$TAMPER_BACKUP"

Attempt Verification

Run:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$TAMPER_BACKUP"

STATUS=$?
printf 'tampered verification exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes either a backup size mismatch or SHA-256 mismatch. With this exact test, size mismatch should be detected first.

Expected status: non-zero.

Confirm the Original Good Backup Still Verifies

Run:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$GOOD_BACKUP"

Expected: verification passes.

PASS Criteria

PASS only if:

  • the tampered copy is rejected;
  • the error identifies changed backup evidence;
  • the original good backup is unaffected and still verifies.

Promotion Blocker

Do not promote if a modified backup can still pass verification.

10. Test 07 — Missing Manifest Is Rejected

Identity

No ERS login is required.

Terminal 2 — Create a Backup Copy Without Its Manifest

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
MISSING_DIR=/tmp/ers-332-manual/missing-manifest

rm -rf "$MISSING_DIR"
mkdir -p "$MISSING_DIR"

MISSING_BACKUP="$MISSING_DIR/$(basename "$GOOD_BACKUP")"
cp "$GOOD_BACKUP" "$MISSING_BACKUP"

Confirm the database exists and the manifest does not:

test -s "$MISSING_BACKUP" && echo 'PASS: backup copy exists'
test ! -e "${MISSING_BACKUP}.manifest.json" && echo 'PASS: manifest is absent'

Expected:

PASS: backup copy exists
PASS: manifest is absent

Attempt Verification

Run:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$MISSING_BACKUP"

STATUS=$?
printf 'missing-manifest verification exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes:

backup manifest does not exist

Expected status: non-zero.

PASS Criteria

PASS only if a .db file without its adjacent manifest is rejected as verified recovery evidence.

Promotion Blocker

Do not promote if file existence alone is accepted as a verified ERS backup.

11. Test 08 — Retention Keeps Minimum Count AND All Backups Within the Age Window

Identity

No ERS login is required.

Purpose

Prove this exact policy:

Keep at least the newest 2 verified backups and keep every verified backup created during the last 30 days.

For this test, five managed backups will be created:

old60     60 days old  -> should be removed
old45     45 days old  -> should be removed
recent10  10 days old  -> should be kept because it is within 30 days
recent5    5 days old  -> should be kept
recent1    1 day old   -> should be kept

The minimum count is 2, but the final retained count must be 3 because the 10-day-old backup is also inside the 30-day window.

Terminal 2 — Prepare a Disposable Server Root

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
RET_SERVER_ROOT=/tmp/ers-332-manual/retention-server
RET_DIR="$RET_SERVER_ROOT/development/backups"

rm -rf "$RET_SERVER_ROOT"
mkdir -p "$RET_DIR"

Create Five Verified Managed Backup Pairs

Copy and run this entire block exactly:

python3 - "$GOOD_BACKUP" "$RET_DIR" "$(pwd)" <<'PY'
from datetime import datetime, timedelta, timezone
from pathlib import Path
import shutil
import subprocess
import sys

source = Path(sys.argv[1])
out = Path(sys.argv[2])
repo = Path(sys.argv[3])

specs = [
    ("old60", 60),
    ("old45", 45),
    ("recent10", 10),
    ("recent5", 5),
    ("recent1", 1),
]

now = datetime.now(timezone.utc).replace(microsecond=0)
for label, days_ago in specs:
    backup = out / f"app-{label}.db"
    shutil.copy2(source, backup)
    created_at = (now - timedelta(days=days_ago)).isoformat().replace("+00:00", "Z")
    subprocess.check_call([
        sys.executable,
        str(repo / "scripts" / "ers-backup.py"),
        "create-manifest",
        "--backup", str(backup),
        "--environment", "development",
        "--source-container", "local-retention-test",
        "--source-database-path", str(repo / "backend" / "data" / "app.db"),
        "--created-at", created_at,
    ])
PY

Confirm exactly ten files exist: five databases plus five manifests.

find "$RET_DIR" -maxdepth 1 -type f -print | sort
find "$RET_DIR" -maxdepth 1 -type f | wc -l

Expected count:

10

Apply Retention Through the Supported Make Target

Run:

make server-backup-retention \
  ENV=development \
  SERVER_ROOT="$RET_SERVER_ROOT" \
  SERVER_BACKUP_RETENTION_COUNT=2 \
  SERVER_BACKUP_RETENTION_DAYS=30

Expected output includes removal of:

app-old60.db
app-old45.db

Expected summary is equivalent to:

Retention: kept 3 verified backup(s); removed 2; minimum-count=2; max-age-days=30.

Verify Exact Retention Result

Run:

test ! -e "$RET_DIR/app-old60.db" && \
test ! -e "$RET_DIR/app-old60.db.manifest.json" && \
test ! -e "$RET_DIR/app-old45.db" && \
test ! -e "$RET_DIR/app-old45.db.manifest.json" && \
echo 'PASS: backups outside both retention protections were removed'

Expected:

PASS: backups outside both retention protections were removed

Then run:

test -s "$RET_DIR/app-recent10.db" && \
test -s "$RET_DIR/app-recent10.db.manifest.json" && \
test -s "$RET_DIR/app-recent5.db" && \
test -s "$RET_DIR/app-recent5.db.manifest.json" && \
test -s "$RET_DIR/app-recent1.db" && \
test -s "$RET_DIR/app-recent1.db.manifest.json" && \
echo 'PASS: every backup inside the 30-day window was retained'

Expected:

PASS: every backup inside the 30-day window was retained

Finally:

find "$RET_DIR" -maxdepth 1 -type f | wc -l

Expected:

6

That means exactly three managed backup pairs remain.

PASS Criteria

PASS only if:

  • old60 and old45 pairs are removed;
  • recent10, recent5, and recent1 pairs remain;
  • exactly three verified backup pairs remain;
  • retention therefore implements minimum count OR age window, not merely “keep the newest 2.”

Promotion Blocker

Do not promote if retention removes a verified backup that is still inside the configured age window or violates the minimum-count rule.

12. Test 09 — Retention Fails Closed and Deletes Nothing When Any Managed Pair Is Corrupt

Identity

No ERS login is required.

Purpose

Prove that retention verifies all managed pairs before deleting anything.

Three old backups will be created. Retention would normally delete two of them under count=1 and days=1. One pair will then be corrupted. The correct result is:

verification failure
+ zero deletions

Terminal 2 — Prepare the Fail-Closed Directory

Run:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
FAIL_SERVER_ROOT=/tmp/ers-332-manual/failclosed-server
FAIL_DIR="$FAIL_SERVER_ROOT/development/backups"

rm -rf "$FAIL_SERVER_ROOT"
mkdir -p "$FAIL_DIR"

Create Three Verified Old Backup Pairs

Run this block exactly:

python3 - "$GOOD_BACKUP" "$FAIL_DIR" "$(pwd)" <<'PY'
from datetime import datetime, timedelta, timezone
from pathlib import Path
import shutil
import subprocess
import sys

source = Path(sys.argv[1])
out = Path(sys.argv[2])
repo = Path(sys.argv[3])

specs = [
    ("old90", 90),
    ("old80", 80),
    ("old70", 70),
]

now = datetime.now(timezone.utc).replace(microsecond=0)
for label, days_ago in specs:
    backup = out / f"app-{label}.db"
    shutil.copy2(source, backup)
    created_at = (now - timedelta(days=days_ago)).isoformat().replace("+00:00", "Z")
    subprocess.check_call([
        sys.executable,
        str(repo / "scripts" / "ers-backup.py"),
        "create-manifest",
        "--backup", str(backup),
        "--environment", "development",
        "--source-container", "local-failclosed-test",
        "--source-database-path", str(repo / "backend" / "data" / "app.db"),
        "--created-at", created_at,
    ])
PY

Confirm six files exist:

find "$FAIL_DIR" -maxdepth 1 -type f | wc -l

Expected:

6

Corrupt One Managed Backup After Its Manifest Has Been Created

Run:

printf '\nCORRUPTED AFTER VERIFICATION\n' >> "$FAIL_DIR/app-old80.db"

Record the complete file list before retention:

find "$FAIL_DIR" -maxdepth 1 -type f -exec basename {} \; | sort \
  > /tmp/ers-332-manual/failclosed-before.txt

cat /tmp/ers-332-manual/failclosed-before.txt

Expected: six filenames are listed.

Run Retention

Run:

make server-backup-retention \
  ENV=development \
  SERVER_ROOT="$FAIL_SERVER_ROOT" \
  SERVER_BACKUP_RETENTION_COUNT=1 \
  SERVER_BACKUP_RETENTION_DAYS=1

STATUS=$?
printf 'fail-closed retention exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes a backup verification error for app-old80.db, normally a size mismatch.

Expected status: non-zero.

Prove That Nothing Was Deleted

Run:

find "$FAIL_DIR" -maxdepth 1 -type f -exec basename {} \; | sort \
  > /tmp/ers-332-manual/failclosed-after.txt

diff -u \
  /tmp/ers-332-manual/failclosed-before.txt \
  /tmp/ers-332-manual/failclosed-after.txt

Expected: diff prints no output.

Immediately run:

echo $?

Expected:

0

Also verify the file count is still six:

find "$FAIL_DIR" -maxdepth 1 -type f | wc -l

Expected:

6

PASS Criteria

PASS only if:

  • retention detects the corrupted managed pair;
  • retention exits non-zero;
  • no managed .db file is deleted;
  • no managed manifest is deleted;
  • the before/after filename lists are identical.

Promotion Blocker

Do not promote if retention deletes any valid historical backup before discovering that another managed pair is corrupt.

13. Test 10 — Retention Cannot Be Configured to Keep Zero Backups

Identity

No ERS login is required.

Terminal 2

Use the already-created retention server root:

RET_SERVER_ROOT=/tmp/ers-332-manual/retention-server

Run:

make server-backup-retention \
  ENV=development \
  SERVER_ROOT="$RET_SERVER_ROOT" \
  SERVER_BACKUP_RETENTION_COUNT=0 \
  SERVER_BACKUP_RETENTION_DAYS=30

STATUS=$?
printf 'zero-count retention exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes:

retention count must be an integer >= 1

Expected status: non-zero.

Now test zero days:

make server-backup-retention \
  ENV=development \
  SERVER_ROOT="$RET_SERVER_ROOT" \
  SERVER_BACKUP_RETENTION_COUNT=2 \
  SERVER_BACKUP_RETENTION_DAYS=0

STATUS=$?
printf 'zero-days retention exit status: %s\n' "$STATUS"

This command is expected to fail intentionally.

Expected output includes:

retention days must be an integer >= 1

Expected status: non-zero.

PASS Criteria

PASS only if both zero-valued retention settings are rejected.

Promotion Blocker

Do not promote if automatic retention can be configured with a minimum count of zero or an age window of zero days.

14. Test 11 — Final Good Backup Still Verifies After All Negative Tests

Identity

No ERS login is required.

Terminal 2

Recover the known-good backup and original checksum:

GOOD_BACKUP="$(cat /tmp/ers-332-manual/GOOD_BACKUP_PATH)"
RECORDED_SHA256="$(cat /tmp/ers-332-manual/GOOD_BACKUP_SHA256)"

Recompute the checksum after all negative tests:

FINAL_SHA256="$(shasum -a 256 "$GOOD_BACKUP" | awk '{print $1}')"

printf 'Original recorded SHA-256: %s\n' "$RECORDED_SHA256"
printf 'Final SHA-256:             %s\n' "$FINAL_SHA256"

test "$RECORDED_SHA256" = "$FINAL_SHA256" && \
  echo 'PASS: known-good backup checksum is unchanged after all negative tests'

Expected final line:

PASS: known-good backup checksum is unchanged after all negative tests

Then perform the final supported verification:

make server-backup-verify \
  ENV=development \
  BACKUP_FILE="$GOOD_BACKUP"

Expected output includes:

Backup verification passed:
Environment: development
Latest schema migration: 000077_journey_bonus_award_approval.up.sql

Immediately run:

echo $?

Expected:

0

PASS Criteria

PASS only if:

  • the final SHA-256 exactly matches the checksum recorded immediately after backup creation in Test 02;
  • the original accepted backup still passes make server-backup-verify;
  • wrong-environment, tamper, missing-manifest, and retention failure testing did not modify the known-good backup.

Promotion Blocker

Do not promote if negative verification/retention operations modify a previously accepted good backup.

15. Test 12 — Re-Run the Bite 33.2 Automated Gate After Manual Verification

Identity

No ERS login is required.

Terminal 2

Run:

make backup-creation-retention-verification-check

Expected final result:

Bite 33.2 backup creation, retention, and verification verified.

Then run:

make local-check

Expected: make local-check passes completely.

CI for this exact SHA must also pass before promotion.

PASS Criteria

PASS only if:

  • make backup-creation-retention-verification-check passes;
  • make local-check passes;
  • CI passes.

Promotion Blocker

Any local or CI regression is a promotion blocker.

16. Cleanup

After every manual test has passed, stop the LOCAL backend in Terminal 1 with:

Ctrl+C

Then remove only the disposable manual-test directory:

rm -rf /tmp/ers-332-manual

Do not delete:

backend/data/app.db

Confirm the normal LOCAL database still exists:

ls -lh backend/data/app.db

17. Promotion Gate

Bite 33.2 is ready for promotion only when every item below is true:

  • Branch is 930-bite-332-backup-creation-retention-and-verification.
  • SHA is 05e9eff3b56c5a1308c17c131e188633e4e0e3b6.
  • LOCAL source database passes SQLite integrity and foreign-key checks.
  • A live SQLite .backup snapshot can be created while the LOCAL backend remains running.
  • The backup SHA-256 is independently recorded immediately after creation.
  • A verified adjacent JSON manifest is created for the backup.
  • Manifest records environment, source path, size, SHA-256, integrity, migration, required-table, and record-count evidence.
  • The manifest SHA-256 exactly matches the independently recorded backup checksum.
  • Manifest does not contain credential/secret fields.
  • make server-backup-verify ENV=development BACKUP_FILE=<good-backup> succeeds.
  • The same Development backup is rejected when verified as Production.
  • A tampered backup is rejected.
  • A backup without its manifest is rejected.
  • Retention removes only backups outside both the minimum-count and age-window protections.
  • Retention keeps all verified backups still inside the configured age window.
  • Retention fails closed when any managed backup pair is corrupt.
  • Fail-closed retention deletes nothing.
  • Retention count 0 is rejected.
  • Retention days 0 is rejected.
  • The original known-good backup SHA-256 is unchanged after all negative tests.
  • The original known-good backup still verifies after all negative tests.
  • make backup-creation-retention-verification-check passes.
  • make local-check passes.
  • CI passes for this exact SHA.

18. Promotion-Blocking Defects

Do not promote Bite 33.2 if any of the following occurs:

  • SQLite backup cannot be created consistently while the source database is live.
  • A backup is accepted without a valid adjacent manifest.
  • No independent SHA-256 is recorded for the known-good backup at creation time.
  • The manifest SHA-256 does not match the independently recorded checksum.
  • Manifest verification does not validate size/SHA-256.
  • Manifest omits migration or required-table evidence.
  • Manifest contains application passwords, JWT secrets, OpenAI keys, ledger keys, or similar credentials.
  • A Development backup verifies successfully as Production.
  • A modified backup verifies successfully.
  • A backup with a missing manifest verifies successfully.
  • Retention removes a backup that is still within the configured age window.
  • Retention keeps fewer than the configured minimum count when more valid backups exist.
  • Retention begins deleting backups before discovering another managed pair is corrupt.
  • Corrupt managed backup evidence is silently ignored by retention.
  • Retention permits a minimum count of zero.
  • The known-good backup SHA-256 changes after any negative verification/retention test.
  • Negative verification/retention tests damage the known-good backup.
  • make backup-creation-retention-verification-check fails.
  • make local-check fails.
  • CI fails.