Backup and recovery

Current Status

Production backup infrastructure has not been activated. This document specifies the required backup scope, retention policy, recovery procedure, and encryption-key dependency for Vendale. All steps below are pre-activation requirements; none have been executed in a live environment.

---

1. Backup Scope

Items not required to be backed up separately (recoverable from code):

• Application source code (version controlled in git).
• Database schema (recovered via alembic upgrade head against a blank database).
• Tauri installer artifacts (rebuilt from tagged commit).

---

2. Encryption Key Dependency

WHATSAPP_CREDENTIAL_ENCRYPTION_KEY is a 64-character hex AES-256 key stored in the backend environment only. It encrypts all WhatsApp access tokens in the whatsapp_connections table using AES-256-GCM.

If this key is lost or rotated without re-encryption, all stored WhatsApp access tokens become permanently unreadable. Operators must re-enter access tokens for every WhatsApp connection.

Required storage policy:

• Store the key in the primary secrets manager.
• Store a recovery copy in an offline or isolated secondary location (e.g., hardware security module, printed sealed copy in a physical safe).
• Never store the key in version control, application logs, or database rows.
• Never pass the key as a command-line argument.
• Rotation procedure: decrypt all connection tokens with the old key, re-encrypt with the new key, update the env var, restart the backend. This requires a brief maintenance window and must be tested in a staging environment first.

---

3. PostgreSQL Backup Procedure

3.1 Full Dump (Manual)

export PGPASSWORD=<production-password>

pg_dump \
  --host=<production-host> \
  --port=5432 \
  --username=<production-user> \
  --format=custom \
  --compress=9 \
  --file="vendale_$(date +%Y%m%d_%H%M%S).pgdump" \
  vendale

Store the dump in the object storage backup bucket or off-site storage. Verify the dump is readable before declaring backup complete:

pg_restore --list vendale_<timestamp>.pgdump | head -20

3.2 Automated Snapshot (Recommended)

Configure your hosting provider's automated PostgreSQL snapshot:

• AWS RDS / Aurora: Enable automated backups with 7–30 day retention; enable continuous WAL for point-in-time recovery.
• Supabase: Enable daily backups (Pro plan) or pg_basebackup for self-hosted.
• DigitalOcean Managed PG: Enable automated daily backups.

3.3 WAL Archiving (Point-In-Time Recovery)

For workloads with frequent writes (broadcasts, media ingestion):

# postgresql.conf additions
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://<backup-bucket>/wal/%f'

Verify archiving is active:

SELECT pg_walfile_name(pg_current_wal_lsn()), archived_count FROM pg_stat_archiver;

---

4. Object Storage Backup

4.1 Bucket Versioning

Enable versioning on the production media bucket to allow recovery of accidentally deleted or overwritten objects. Vendale never overwrites objects under the same key (tenant-scoped opaque keys are content-addressed), but accidental operator deletion or storage-side corruption can be recovered via versioning.

# AWS S3
aws s3api put-bucket-versioning \
  --bucket vendale-production \
  --versioning-configuration Status=Enabled

# MinIO
mc version enable myminio/vendale-production

4.2 Cross-Region Replication (Recommended)

Configure cross-region replication for disaster recovery:

# AWS S3 — set up replication rule in the source bucket
# Target: s3://<backup-region-bucket>
# Replication time: 15 minutes SLA

4.3 Backup Verification

Monthly: randomly select 5 media asset storage keys from media_assets where download_state = 'downloaded' and verify each object exists in the production bucket.

from app.services.media_storage import ProductionObjectMediaStorage
# Verify exists for a sample of storage_key values

---

5. Recovery Procedure

5.1 Database Restore From Full Dump

# 1. Create a blank target database (if starting fresh)
createdb --host=<host> --username=<user> vendale

# 2. Restore
pg_restore \
  --host=<host> \
  --port=5432 \
  --username=<user> \
  --dbname=vendale \
  --no-owner \
  --no-privileges \
  vendale_<timestamp>.pgdump

# 3. Verify the restored schema matches the expected Alembic head
export DATABASE_URL="postgresql+asyncpg://<user>:<pass>@<host>:5432/thread_crm"
python -m alembic current
# Must match the production migration head (e.g., f9a1b2c3d4e5)

5.2 Database Restore From Point-In-Time

Using PostgreSQL WAL recovery to restore to a specific timestamp:

# recovery.conf (PostgreSQL 12 and earlier) or postgresql.conf (13+)
restore_command = 'aws s3 cp s3://<backup-bucket>/wal/%f %p'
recovery_target_time = '2026-06-20 14:30:00+00'
recovery_target_action = promote

After recovery, confirm alembic current matches expected head before starting the application.

5.3 Object Storage Restore

For accidentally deleted or corrupted objects, restore from versioned backup:

# AWS S3 — restore a specific version of an object
aws s3api copy-object \
  --bucket vendale-production \
  --copy-source "vendale-production/<storage-key>?versionId=<version-id>" \
  --key <storage-key>

For unrecoverable media bytes (no version, no replica), preserve the media_assets row and mark download_state = 'unavailable' rather than deleting the message history.

5.4 Full Application Recovery Sequence

Execute in this order after restoring database and object storage:

1. Restore PostgreSQL — full dump or point-in-time as above.
2. Restore object storage — verify critical media objects are accessible.
3. Restore environment configuration — load from secrets manager; never use development values.
4. Confirm WHATSAPP_CREDENTIAL_ENCRYPTION_KEY — must match the value used when tokens were stored.
5. Apply Alembic migrations if needed — python -m alembic upgrade head.
6. Start backend — verify /health and /ready return 200.
7. Start workers — whatsapp_media_worker and whatsapp_broadcast_worker.
8. Verify webhook endpoint — confirm Meta can reach /webhooks/meta/whatsapp and the challenge passes.
9. Send a controlled test message — confirm outbound WhatsApp provider path is live.
10. Verify a broadcast — confirm worker claims jobs and sends template messages.

---

6. Disaster Recovery Targets

The following targets apply once production is activated. They are targets, not guarantees, and require the infrastructure described above to be in place.

---

7. Webhook Replay

Webhook deliveries and events are idempotent. The meta_webhook_deliveries table uses payload_hash (SHA-256 of raw JSON) as a deduplication key. Replayed provider events:

• Produce a second meta_webhook_deliveries row with skipped=true and no duplicate message/status processing.
• Do not duplicate messages, conversations, or customer identities.
• Do not regress delivery statuses when the same WAMID is delivered again with the same status.

If recovery requires replaying a range of Meta webhook events:

1. Re-deliver the raw payloads to POST /webhooks/meta/whatsapp with valid X-Hub-Signature-256 headers.
2. The backend will skip already-processed deliveries and process only new ones.
3. Monitor meta_webhook_deliveries.skipped and meta_webhook_events.skipped columns to confirm deduplication.

---

8. Backup Testing Schedule