Skip to content

Backup & Restore (D1)

This covers backing up and restoring the app's Cloudflare D1 database. D1 is the source of truth for accounts, customers, companies, encrypted QBT tokens, report configs, audit log, and notifications. KV (sessions/cache) is disposable and is not backed up — sessions simply expire.

Run all commands from the repo root with npx wrangler login already done. Each environment has its own database: qbtime_db_staging (Pages preview) and qbtime_db_prod (Pages production). Never restore one into the other.

What to back up

The whole database, as SQL. Encrypted columns (QBT tokens, client secrets, MFA secrets) are exported as their ciphertext — the DATA_ENCRYPTION_KEY is not in the database, so a backup file alone cannot decrypt them. Keep the encryption key backed up separately (e.g. a password manager); without it a restored database cannot use existing tokens.

Manual backup (export)

Export the remote database to a timestamped SQL file:

# Production
npx wrangler d1 export qbtime_db_prod --env production --remote --output "backup-prod-$(date +%Y%m%d-%H%M).sql"

# Staging
npx wrangler d1 export qbtime_db_staging --env preview --remote --output "backup-staging-$(date +%Y%m%d-%H%M).sql"

Store the file somewhere durable and access-controlled (it contains ciphertext and PII such as email addresses). Do not commit it to git — the .sql backups should be in .gitignore.

Schema-only or data-only

npx wrangler d1 export qbtime_db_prod --env production --remote --no-data   --output schema.sql   # structure only
npx wrangler d1 export qbtime_db_prod --env production --remote --no-schema --output data.sql     # rows only

Suggested cadence

  • Daily automated export of production, retained 30 days.
  • Before every production migration or deploy that changes the schema.
  • Before any bulk data operation (e.g. mass company import).

Automate by adding a step to the cron Worker or an external scheduler that runs the export command and uploads the file to object storage (e.g. R2) with a lifecycle/retention rule. (Deferred: not yet wired into qbtime-cron.)

Restore (import)

Restoring overwrites/loads rows into the target database. Restore into a fresh or intended environment — be certain which env you are targeting.

# Restore into staging (safest place to test a backup first)
npx wrangler d1 execute qbtime_db_staging --env preview --remote --file ./backup-prod-20260603-0900.sql

For a clean restore into a brand-new database, first apply migrations, then load data-only, to avoid duplicate-schema errors:

npx wrangler d1 migrations apply DB --env preview --remote     # build schema
npx wrangler d1 execute qbtime_db_staging --env preview --remote --file ./data.sql

After restore:

  1. Confirm the encryption key in that environment matches the one in force when the backup was taken — otherwise stored QBT tokens won't decrypt and every company will need to reconnect.
  2. Verify the audit-log hash chain: GET /api/v1/platform/verify-audit (DevOps).
  3. Spot-check sign-in and a manual report run.

Point-in-time / disaster recovery

D1 has Cloudflare-side Time Travel (bookmark-based restore) in addition to these SQL exports. For a catastrophic case, prefer Time Travel for the freshest state, and use the SQL exports as an offline, portable fallback. Keep both.

Verifying a backup is good

A backup you haven't restored is a guess. Quarterly, restore the latest production backup into staging (steps above) and confirm sign-in, a report run, and audit-chain verification all pass. Record the result.