# Backup and restore verification

> **2026-07-19 19:32 UTC.** Full backup → verify → restore → start-the-app rehearsal of the
> `deploy/deploy.sh` backup path, exercised end to end against MariaDB 11.
>
> **This is a rehearsal, not a verification of production data.** See *Limitations* — that section is
> the most important part of this document, and the checklist at the end is what actually closes the
> gap.

## Scope — read this first

| | |
|---|---|
| **What was verified** | The backup *mechanism*: the real `backup_database` function from `deploy.sh`, against a real MariaDB 11 database carrying the real 42-table schema, restored into a separate clean MariaDB 11 container, with the real application started against the result |
| **What was NOT verified** | **Your production database.** No production host was contacted, no production credentials were used, and no production data was read, copied or restored |
| **Why** | The machine this ran on has no route to production: no `/opt/job-tracker`, no `DATABASE_PROVIDER` or `JOBTRACKER_CONNECTION_STRING` in its `.env`, and the local stack runs SQLite. The production host, user and key are CI secrets (`PROD_HOST`, `PROD_USER`, `PROD_SSH_KEY`) that are not available here |

The commands below are the ones to run against production. They are recorded so the owner can execute
the same sequence with production values substituted.

## 1. Backup configuration

Read from `deploy/deploy.sh` and `docker-compose.yml`:

- `deploy.sh` loads `/opt/job-tracker/shared/.env` into its own shell before any decision.
- `validate_deploy_config` runs **before** the backup, and therefore before any build, stop or replace.
  It requires `DATABASE_PROVIDER` (no default), `JOBTRACKER_CONNECTION_STRING` when the provider is
  MariaDB, plus `AI_SERVICE_TOKEN` and `AUTH_JWT_KEY`.
- Backups land in `/opt/job-tracker/backups` (override with `BACKUP_DIR`), UTC-timestamped, gzipped.
  Nothing is ever overwritten and nothing is ever auto-deleted.
- The password travels via `MYSQL_PWD`, never on the command line.

## 2. Provider resolution

`DATABASE_PROVIDER=mariadb` selected the MariaDB dump path. Confirmed by the script's own output:

```
Deployment configuration validated (database provider: mariadb).
Backing up MariaDB database 'jobtracker' on <host>:<port> to <dir>/jobtracker-jobtracker-20260719T193022Z.sql.gz
Backup verified: <dir>/jobtracker-jobtracker-20260719T193022Z.sql.gz (8.0K)
```

**The filename is the check that matters.** `jobtracker-<database>-<stamp>.sql.gz` means the MariaDB
path ran. A `jobtracker-sqlite-<stamp>.tar.gz` on a MariaDB host would mean the environment is wrong.

## 3. Commands used

Schema built by the real application, not by hand — `Database.Migrate()` plus the startup reconciler
against an empty MariaDB, producing 42 tables. Representative rows were then seeded across users,
companies, applications, career profile and CV variants.

```bash
# Backup — the real function from deploy.sh, not a hand-written dump
DATABASE_PROVIDER=mariadb \
JOBTRACKER_CONNECTION_STRING='Server=<host>;Port=<port>;Database=jobtracker;User Id=<user>;Password=<password>;' \
BACKUP_DIR=/opt/job-tracker/backups \
  deploy/deploy.sh          # takes the backup first and aborts the deploy if it fails

# Integrity and content
gzip -t  "$BACKUP"                                  # valid archive
gzip -dc "$BACKUP" | grep -c 'CREATE TABLE'         # schema present
gzip -dc "$BACKUP" | tail -1                        # "-- Dump completed on ..." trailer

# Restore into a SEPARATE, empty database — never over a live one
gzip -dc "$BACKUP" | MYSQL_PWD='<password>' mariadb --host=<host> --user=<user> jobtracker
```

Passwords are supplied via `MYSQL_PWD` so they never reach the process list or the shell history.

## 4. Backup verification result

| Check | Result |
|---|---|
| Correct backup type | ✅ `.sql.gz` MariaDB dump, not a volume archive |
| File integrity | ✅ `gzip -t` passed |
| Schema markers | ✅ 42 `CREATE TABLE` statements |
| Dump trailer | ✅ `-- Dump completed on 2026-07-19 19:30:23` — proves the dump was not truncated |
| Expected tables | ✅ `AspNetUsers`, `Companies`, `JobApplications`, `CareerProfiles`, `CvVariants`, `JobEvents`, `ApplicationChecklistItems`, `AiInteractions` all present |
| Data present | ✅ 7 `INSERT INTO` statements |

## 5. Restore result

Restored into a **separate, empty** MariaDB 11 container (0 tables before, 42 after).

| Entity | Source | Restored | |
|---|---|---|---|
| Users | 1 | 1 | ✅ |
| Companies | 2 | 2 | ✅ |
| Applications | 2 | 2 | ✅ |
| Career profiles | 1 | 1 | ✅ |
| CV variants | 1 | 1 | ✅ |

Beyond counts:

- **All 42 tables compared** — table lists identical, and **every table's row count matched**.
- **Content survived, not just cardinality** — the restored applications resolve their company
  foreign keys (`Senior Backend Engineer @ Northwind Consulting`), the CV variant kept its name and
  `IsPublic` flag, and the career profile JSON still contained its languages.
- **The application starts against the restored database.** `/health` returned
  `{"status":"ok","version":"restore-rehearsal"}`, the schema stayed at 42 tables (the reconciler
  correctly found nothing to do) and rows were preserved. A restore that produces a database the app
  cannot boot against is not a restore.

## Limitations

Read these before treating the deployment as backed up.

1. **No production data was touched.** Everything above ran against a locally built MariaDB with
   seeded rows. It proves the mechanism; it proves nothing about your database.
2. **The seeded dataset is tiny** (7 rows). It does not exercise dump duration, disk headroom, lock
   behaviour under load, or timeout limits on a real dataset. A production database large enough to
   make `mariadb-dump` slow could behave differently.
3. **Character-set and collation fidelity was not stress-tested.** The seeded data was mostly ASCII.
   Real CV content contains non-ASCII text — Norwegian `æøå`, accents, CJK. The dump defaults should
   handle this, but it was not proven here.
4. **No restore was performed over a populated database.** The destination was empty. Restoring over
   an existing database is a different operation with different failure modes.
5. **`deploy.sh` was exercised up to and including the backup**, not through the build and container
   replacement, which would have required a full deployment.
6. **The `mariadb-dump` client came from a container** (`mariadb:11`). If the production host has its
   own client installed, `deploy.sh` uses that instead, and version differences are possible.

## What the owner still needs to do

This is the checklist that turns a rehearsal into a verified backup.

- [ ] **Run one backup by hand against production** and confirm the filename is
      `jobtracker-<database>-<stamp>.sql.gz`.
- [ ] **Check the dump size is plausible** for the amount of data you have. A suspiciously small file
      is the signal worth catching.
- [ ] **Restore it into a scratch database** — never over the live one — and confirm row counts for
      `AspNetUsers`, `JobApplications`, `Companies` and `CareerProfiles` match production.
- [ ] **Check non-ASCII text survived.** Open one CV or career profile containing `æ`, `ø` or `å` in
      the restored copy and confirm it is not mangled. This is the most likely silent failure.
- [ ] **Confirm `/opt/job-tracker/backups` has disk headroom** for several dumps.
- [ ] **Note how long the dump takes.** It runs before every deploy and blocks it.

## Cleanup

Both MariaDB containers and all rehearsal files were removed. No test artefacts remain, and the
existing local `jobtracker-backend-1` / `jobtracker-frontend-1` stack was left untouched.
