# Coursepedia Backup and Restore Guide

## What must be backed up

Back up PostgreSQL, uploaded course media, certificate assets and records, payment and order
records, email templates, and environment configuration. PostgreSQL contains the certificate,
payment, enrollment, learning-progress, email-template, and audit data; these records must be
restored together to preserve their relationships.

Never place backups in the application release directory. Store encrypted copies in a private
backup account with access restricted to authorised administrators.

## Recommended schedule

- PostgreSQL: encrypted full backup every night, retained for 35 days.
- PostgreSQL point-in-time recovery logs: continuous, retained for at least 7 days.
- Uploaded media: daily incremental copy and weekly integrity check.
- Monthly archive: one encrypted database and media backup retained for 12 months.
- Before deployment or migration: a labelled database and media snapshot.
- Restore drill: at least once every quarter.

## PostgreSQL backup

Run from a protected operations host. Supply credentials through the environment or a password
file, not directly in command history.

```powershell
pg_dump --format=custom --no-owner --no-privileges --file coursepedia-YYYY-MM-DD.dump $env:DATABASE_URL
```

Verify that the dump can be listed:

```powershell
pg_restore --list coursepedia-YYYY-MM-DD.dump
```

The database backup includes certificates, payments, orders, email templates, and their audit
history. A separate export of these tables may be retained for compliance, but it is not a
replacement for a full relational backup.

## Uploaded media backup

For local persistent storage, snapshot the directory configured by `STORAGE_PATH`. For S3-compatible
storage, enable bucket versioning and lifecycle retention, then replicate to a second private bucket
or account. Do not make private learner attachments publicly listable.

Record the database backup timestamp and media snapshot version together in the backup manifest.

## Environment configuration

Keep production secrets in the hosting platform's secret manager. Export an encrypted configuration
inventory containing variable names and ownership, not plaintext values. Document who can rotate the
database, session, payment, SMTP, OAuth, storage, and monitoring credentials.

## Restore process

1. Create an isolated recovery environment with no public traffic or live webhooks.
2. Restore PostgreSQL into an empty database:

```powershell
createdb coursepedia_restore
pg_restore --clean --if-exists --no-owner --dbname coursepedia_restore coursepedia-YYYY-MM-DD.dump
```

3. Restore the matching media snapshot to the configured storage location.
4. Configure temporary recovery secrets and disable payment/email delivery.
5. Run `pnpm db:reconcile` and `pnpm check`.
6. Verify login, enrollment, lesson progress, payments, certificates, email templates, and audit logs.
7. Verify several old and new certificate codes and their QR links.
8. Record counts and evidence, then obtain approval before switching production traffic.

Never restore a production backup over the live database without an approved change window and a
fresh pre-restore snapshot.

## Backup monitoring

Alert administrators when a scheduled backup is missing, unusually small, cannot be decrypted, or
fails an automated restore check. Log backup metadata and verification results, but never log
database passwords, secret values, private tokens, or card information.