Backup and restore

A QueryProxy backup has two halves that are useless apart: the data (the application database and the result files) and the APP_KEY that decrypts the stored connection credentials, chat secrets, masking keys and TLS certificates inside it. Back up both, and keep them in different places.

What to back up

The data volume (/var/www/html/storage/app) holds the SQLite database, the generated key file and the result files. The TLS files under storage/app/tls are written at run time from encrypted values in the database, so they come back with the database.

The backup holds the key next to the data it protects when the key lives on the volume. Write the archive readable by its owner only (umask 077) and store it as carefully as the key itself. Better, supply APP_KEY from outside the volume; queryproxy:doctor warns when the key file shares a volume with the SQLite database.

Copy a SQLite database

The simplest backup is the one in the README: stop the container and archive the volume. Nothing is writing while the container is stopped, so the copy is consistent.

To copy the database while the container runs, use SQLite’s own .backup command from a machine that has the sqlite3 command-line tool (the QueryProxy image does not ship it) and can read the volume:

sqlite3 /path/to/database.sqlite ".backup '/backups/queryproxy-$(date +%F).sqlite'"

.backup uses SQLite’s online backup mechanism. SQLite documents that other connections may keep writing during an online backup, and that a write from another process usually makes the copy restart; under constant heavy writing a copy may not finish. A QueryProxy install writes little, so this rarely matters, but check that the command exits with status 0. Do not copy database.sqlite with cp or tar while QueryProxy runs: the file may be mid-write, and its journal or WAL side files (-journal, -wal, -shm, the last two once Litestream has switched the database to WAL) can hold changes the main file does not.

For MySQL, MariaDB and PostgreSQL, use the database’s own tools (mysqldump, pg_dump, or snapshots), as you would for any application database.

Continuous replication with Litestream

If you want a restore point that is minutes old instead of a nightly copy, Litestream replicates a SQLite database to object storage such as S3. It is not part of QueryProxy and the QueryProxy test suite does not exercise it, so rehearse a restore in a staging copy before you rely on it.

Litestream works only with SQLite’s WAL journaling mode and switches the database to it if the application has not. It takes short write locks when it checkpoints. Run one Litestream process per database and replicate to a path no other installation uses: its documentation warns that several applications replicating into the same bucket and path can leave you unable to restore.

A minimal configuration:

dbs:
  - path: /var/www/html/storage/app/database.sqlite
    replica:
      type: s3
      bucket: my-queryproxy-backups
      path: queryproxy
      region: eu-central-1

Litestream reads AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY from the environment. Run it as a sidecar that mounts the same data volume. Its restore command takes the replica URL and an output path:

litestream restore -o /restore/database.sqlite s3://my-queryproxy-backups/queryproxy

Litestream replicates the database only. The APP_KEY and the result files are not in it.

The APP_KEY

Treat the key as the most valuable secret of the install. It encrypts every target database credential, the chat signing secrets, the per-team masking keys and the stored TLS certificates. A lost key cannot be recovered, and a container started without its original key generates a new one and cannot read any of it.

An escrow procedure that works:

1. Generate it once, explicitly, and keep it out of the data volume: docker run --rm queryproxy/queryproxy php artisan key:generate --show.
2. Store it in a secret manager or a sealed envelope that a second person can open: at least two custodians, in two places, neither of them the backup storage.
3. Supply it with APP_KEY or APP_KEY_FILE (a Docker or Kubernetes secret), see Configuration.
4. Record rotations. When you rotate, move the old key into APP_PREVIOUS_KEYS (comma-separated) so older ciphertext still decrypts, and store both. A backup taken before a rotation needs the key that was current then.
5. Test it. After every rotation or restore, queryproxy:doctor reports whether the key can decrypt stored credentials (check app_key).

Restore

1. Stop the container and remove it (docker stop queryproxy && docker rm queryproxy).

2. Put the data back. For an archive made as in the README:

   [!WARNING] The second command deletes everything in the queryproxy-data volume before it extracts the backup. Check that the archive is the one you want first.

   docker run --rm -v queryproxy-data:/data -v "$PWD":/backup alpine \
     sh -c 'find /data -mindepth 1 -delete \
       && tar xzf /backup/queryproxy-data.tgz -C /data'

   For SQLite, first stop the Litestream sidecar if you run one: a running sidecar could replicate the restored file over the existing replica. Then delete database.sqlite-wal, database.sqlite-shm and database.sqlite-journal next to the old database; SQLite would apply a leftover WAL to the restored file and corrupt it. After that, place a .backup copy at the DB_DATABASE path as database.sqlite on the volume, or run litestream restore -o <path> to a path where no file exists yet (remove the old database file first). For MySQL, MariaDB and PostgreSQL, restore the database with its own tools.

3. Start the same version the backup came from, with the same environment file and the same APP_KEY (plus APP_PREVIOUS_KEYS if you rotated).

4. Check the result:

   docker exec queryproxy php artisan queryproxy:doctor
   docker exec queryproxy php artisan audit:verify

   app_key must be ok and audit:verify must report an intact chain. A break in a restored copy means the copy itself is damaged (for example a plain file copy taken while QueryProxy was writing); try the previous backup. See Audit chain and SIEM export.

5. Only then upgrade, if that was the plan.

A restored install may hold pending requests whose chat buttons belong to messages that are hours or days old. The buttons of such a message can have expired; the reminder posts a fresh card for a request that is still pending. The requests themselves never expire.

Next steps

• Upgrading and rolling back
• Troubleshooting
• Threat model