# Backup restore checks

A local recovery drill for a service-record database and its attachment archive. The runner restores a SQLite backup into a new temporary database, verifies the restored files, then checks the application's record and attachment contract. Each run produces a machine-readable report and removes only the temporary directory it created.

The data restore runs against SQLite. The operational supplement executes local failure and recovery cases, then evaluates the original PostgreSQL recovery pipeline and remote-transfer budget as explicit theoretical models.

## Run

Python 3.10 or newer with SQLite enabled; no third-party packages. Verified on Python 3.12 and SQLite 3.53.1. Run these commands from this directory:

```sh
python restore_check.py fixtures/good --report results/manual-good.json
python -m unittest -v
python verify.py
```

`verify.py` runs the tests and the four supplied fixtures, refreshes `verification.json`, `results/summary.csv`, and `report.html`, and exits nonzero if any expected outcome differs. Open `report.html` directly in a browser. Reports require no network access.

The three failure fixtures should return exit code 1:

```sh
python restore_check.py fixtures/corrupt --report results/manual-corrupt.json
python restore_check.py fixtures/mixed-snapshot --report results/manual-mixed.json
python restore_check.py fixtures/missing-file --report results/manual-missing.json
```

## Backup format

Each bundle contains `database.sqlite`, an uncompressed `files.tar`, and `manifest.json`. The manifest binds the database and archive SHA-256 hashes to a snapshot ID, expected visit count, and exact attachment paths, sizes, and hashes. The database repeats the snapshot ID in `metadata` and attachment metadata in `documents`. The supplied fixtures contain 24 synthetic service visits and 24 text attachments. `make_fixtures.py` generates these fixtures; it overwrites only its named fixture directories.

The application probes verify SQLite integrity, foreign keys, snapshot identity, visit count, permitted visit statuses, nonempty asset names, and an exact match between database references, manifest entries, and restored files. A database restore can succeed while these probes fail; the report keeps those outcomes separate.

## Isolation and archive handling

There is no command-line option for a destination database or restore directory. The runner creates a new owned temporary directory, restores there, and deletes it on success or failure. The source database is opened read-only and copied using SQLite's backup API. Tests check that source bytes and unrelated files remain intact.

Archive members must be regular files with safe relative paths. Links, special files, duplicate paths, unlisted files, traversal, missing entries, incorrect sizes, and bad hashes are rejected. Bundles are limited to 1,000 files and 32 MiB per input or expanded archive. This runner assumes local, stable backup files while checking them. Hashes detect accidental changes; the manifest is not digitally signed and is not an authenticity guarantee.

## Verification

The automated suite exercises successful recovery, truncated and invalid databases, valid hashes from the wrong snapshot, missing files, traversal, symbolic and hard links, mismatched database references, incorrect counts, duplicate manifests, and interruption cleanup. The regression interruption hook raises inside the runner at four stages. The operational supplement separately issues real SIGKILL signals at three stages and verifies supervised cleanup. Neither removes host power.

`results/*.json` include individual check outcomes and elapsed timings. Timings are local fixture measurements, not recovery-time estimates for a larger system. The tool has no download stage because its input is an already-local backup set.

## Files

- `restore_check.py`: input validation, disposable restore, probes, cleanup, CLI.
- `make_fixtures.py`: deterministic synthetic data and three damaged variants.
- `test_restore.py`: failure-path and source-isolation tests.
- `verify.py`: reproducible verification and report generation.
- `fixtures/`: runnable example backup sets.
- `results/`, `verification.json`, `report.html`: executed results.

## Operational and theoretical completion

`python verify.py` also runs `operations.py` and requires all 11 additional expected outcomes to match. On Linux, it executes real process-kill and recovery cases against disposable resources. The readable supplement is [operations-report.html](operations-report.html); [completion.json](completion.json) includes source hashes, expected outcomes, observed results and explicit evidence types. See [OPERATIONS.md](OPERATIONS.md) for the runbook and the completed theoretical PostgreSQL review.

For CI, run `python verify.py` from this package directory on a Linux worker, fail the job on its nonzero exit, and retain `verification.json`, `completion.json` and `results/`. The command needs only the Python standard library and SQLite. It accepts no production target or secret.