# Isis Database Backup and Restore Runbook

Last Updated: 2026-02-22  
Scope: Isis production data stores
(`PostgreSQL + model/output metadata pointers`)  
Primary Owners: `@oshun/devops`, `@oshun/isis-team`

## Purpose

Provide deterministic operational procedures for:

1. Scheduled and on-demand backups of Isis database state.
2. Point-in-time and snapshot-based restores for Isis incidents.
3. Recovery objective tracking (RTO/RPO) for Isis data stores.

This runbook covers the Isis persistence layer used by:

- `apps/isis/generation-api`
- `apps/isis/workflow-registry`
- `apps/isis/output-registry`
- `libs/isis/database/prisma/schema.prisma`

## Recovery Objectives (Source of Truth)

| Data Store                                         | RPO Target | RTO Target | Notes                                                                                                |
| -------------------------------------------------- | ---------- | ---------- | ---------------------------------------------------------------------------------------------------- |
| Isis PostgreSQL metadata (`generation_jobs`, etc.) | 15 minutes | 60 minutes | Primary incident objective. Restore to isolated instance first, then validate/cut over.              |
| Model/output object artifacts (referenced by DB)   | 60 minutes | 4 hours    | Artifact stores are restored after metadata plane is healthy; prioritize metadata consistency first. |

RTO is measured from incident declaration to successful restore validation and
service readiness (`GET /ready` green for all Isis APIs).

## Prerequisites

- AWS CLI authenticated for production account.
- `psql` and `jq` installed for validation checks.
- Access to:
  - RDS instance identifier (`ISIS_DB_INSTANCE_ID`)
  - optional cluster identifier (`ISIS_DB_CLUSTER_ID`) when using Aurora
  - backup bucket for logical dumps (`ISIS_DB_BACKUP_BUCKET`)
  - restore target security groups and subnet group.
- Emergency comms channel and incident ticket opened.

## Backup Runbook

### A. Daily Baseline Snapshot Verification

1. Confirm automated backups and retention window:

```bash
aws rds describe-db-instances \
  --db-instance-identifier "$ISIS_DB_INSTANCE_ID" \
  --query 'DBInstances[0].{BackupRetentionPeriod:BackupRetentionPeriod,LatestRestorableTime:LatestRestorableTime,StorageEncrypted:StorageEncrypted}'
```

2. Verify latest snapshot status:

```bash
aws rds describe-db-snapshots \
  --db-instance-identifier "$ISIS_DB_INSTANCE_ID" \
  --snapshot-type automated \
  --max-items 5 \
  --query 'DBSnapshots[].{Id:DBSnapshotIdentifier,Status:Status,CreateTime:SnapshotCreateTime}'
```

### B. On-Demand Manual Snapshot (Pre-Change / Pre-Incident)

Create manual snapshot before risky operations (schema migrations, cutovers,
rollback drills):

```bash
SNAPSHOT_ID="isis-prod-manual-$(date -u +%Y%m%dt%H%M%SZ)"
aws rds create-db-snapshot \
  --db-instance-identifier "$ISIS_DB_INSTANCE_ID" \
  --db-snapshot-identifier "$SNAPSHOT_ID" \
  --tags "Key=service,Value=isis" "Key=purpose,Value=pre-change"
```

Track snapshot until `available`:

```bash
aws rds wait db-snapshot-available --db-snapshot-identifier "$SNAPSHOT_ID"
```

### C. Logical Backup (Schema-Scoped)

Run a logical backup for table-level rollback coverage:

```bash
BACKUP_FILE="isis-db-$(date -u +%Y%m%dt%H%M%SZ).dump"
pg_dump "$ISIS_DATABASE_URL" \
  --format=custom \
  --no-owner \
  --no-acl \
  --file "/tmp/${BACKUP_FILE}"

pg_restore --list "/tmp/${BACKUP_FILE}" >"/tmp/${BACKUP_FILE}.toc"
aws s3 cp "/tmp/${BACKUP_FILE}" "s3://${ISIS_DB_BACKUP_BUCKET}/logical/${BACKUP_FILE}"
aws s3 cp "/tmp/${BACKUP_FILE}.toc" "s3://${ISIS_DB_BACKUP_BUCKET}/logical/${BACKUP_FILE}.toc"
```

### D. Backup Health Gate

Fail backup step if any condition occurs:

- latest automated backup is older than 24h
- manual snapshot fails to reach `available`
- logical dump upload fails or `pg_restore --list` fails.

## Restore Runbook

### A. Restore Strategy Decision

1. Use point-in-time restore (PITR) for corruption/user error inside retention
   window.
2. Use manual snapshot restore for planned rollback points.
3. Use logical restore for targeted table/object recovery.

### B. Restore to Isolated Target First

PITR command example:

```bash
RESTORE_INSTANCE_ID="isis-prod-restore-$(date -u +%Y%m%dt%H%M%SZ)"
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier "$ISIS_DB_INSTANCE_ID" \
  --target-db-instance-identifier "$RESTORE_INSTANCE_ID" \
  --use-latest-restorable-time \
  --db-subnet-group-name "$ISIS_DB_SUBNET_GROUP" \
  --vpc-security-group-ids "$ISIS_DB_SECURITY_GROUP"
```

Manual snapshot restore command example:

```bash
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier "$RESTORE_INSTANCE_ID" \
  --db-snapshot-identifier "$SNAPSHOT_ID" \
  --db-subnet-group-name "$ISIS_DB_SUBNET_GROUP" \
  --vpc-security-group-ids "$ISIS_DB_SECURITY_GROUP"
```

Wait for readiness:

```bash
aws rds wait db-instance-available --db-instance-identifier "$RESTORE_INSTANCE_ID"
```

### C. Post-Restore Validation

Run Isis restore verification report:

```bash
RESTORE_DATABASE_URL="postgres://..." \
MIN_GENERATION_JOBS=1 \
MIN_WORKFLOWS=1 \
MIN_OUTPUTS=0 \
MIN_MODELS=0 \
STRICT=true \
scripts/isis/verify_database_restore.sh
```

Expected output: JSON report in `tmp/isis-dr/` with `pass=true`.

### D. Cutover Sequence

1. Freeze new writes by setting maintenance mode / draining traffic.
2. Update secret/connection endpoint to restored instance.
3. Roll Isis services in order:
   - `workflow-registry`
   - `output-registry`
   - `generation-api`
4. Validate:
   - `GET /health` and `GET /ready` for each service
   - critical UI/API smoke flows (`jobs`, `workflows`, `outputs`, `models`)
5. Resume traffic after validation.

### E. Post-Incident Actions

- Capture achieved RTO and estimated RPO in incident timeline.
- Store restore verification report artifact.
- File follow-up tasks for any missed objective.

## RTO/RPO Reporting Checklist

Record all values in incident retro:

- `restore_start_time`
- `restore_validation_pass_time`
- `rto_minutes`
- `restore_point_timestamp`
- `estimated_rpo_minutes`
- `objective_met` (`true/false`)

## Operational Cadence

- Backup verification: daily.
- Manual snapshot drill: weekly pre-change or pre-release.
- Full restore drill: at least once per quarter (or after major schema
  migrations).

## Related Artifacts

- `scripts/isis/verify_database_restore.sh`
- `docs/domains/isis/database.md`
- `docs/domains/isis/remediation/ISIS_ROLLBACK_CRITERIA.md`
- `docs/domains/isis/remediation/ISIS_GAMEDAY_CUTOVER_PLAN.md`
