Refresh staging from production

npx dbdock copydb "$PROD_URL" "$STAGING_URL"

0 3 * * *  /app/scripts/refresh-staging.sh >> /var/log/refresh.log 2>&1

#!/usr/bin/env bash
set -euo pipefail

export PROD_URL="${PROD_URL}"
export STAGING_URL="${STAGING_URL}"

echo "[$(date -u +%FT%TZ)] Starting staging refresh"

# Drop and recreate the staging database to guarantee a clean slate
psql "$STAGING_URL" -c "DROP DATABASE IF EXISTS myapp_staging;"
psql "$STAGING_URL" -c "CREATE DATABASE myapp_staging;"

# Copy production into the fresh staging database
npx --yes dbdock copydb "$PROD_URL" "$STAGING_URL" --yes

echo "[$(date -u +%FT%TZ)] Refresh complete"

# 1. Copy prod to a "clean room" staging database
npx dbdock copydb "$PROD_URL" "$CLEAN_ROOM_URL"

# 2. Scrub the clean room
psql "$CLEAN_ROOM_URL" <<'SQL'
  UPDATE users SET
    email = 'user' || id || '@example.invalid',
    phone = NULL,
    ssn = NULL;
  UPDATE payment_methods SET
    card_last4 = '0000';
  DELETE FROM audit_logs WHERE created_at < now() - interval '90 days';
SQL

# 3. Copy the scrubbed clean room to the developer-facing staging
npx dbdock copydb "$CLEAN_ROOM_URL" "$STAGING_URL"

# 4. Clean up the clean room
psql "$CLEAN_ROOM_URL" -c "DROP DATABASE clean_room;"

npx dbdock copydb --schema-only "$PROD_URL" "$STAGING_URL"

-- In staging
SELECT max(updated_at) FROM events;

# Pull prod to local once
npx dbdock copydb "$PROD_URL" "postgresql://localhost:5432/myapp"

# Later, refresh from staging instead of prod (less load on prod)
npx dbdock copydb "$STAGING_URL" "postgresql://localhost:5432/myapp"

npx dbdock copydb --driver "$NEON_URL" "$STAGING_URL"