#!/usr/bin/env bash # ============================================================================== # ClassNotify P2.5 PostgreSQL Production Restore Gate & Sandbox Drill # Purpose: Automatically verifies that a backup dump can be successfully restored # into an isolated sandbox temporary DB, checks SHA256 integrity, # validates all core relational constraints, and logs drill audit metrics. # ============================================================================== set -euo pipefail BACKUP_FILE="${1:-}" DRILL_LOG="/var/log/classnotify-restore-drill.log" STATUS_DIR="/var/lib/classnotify/ops" DRILL_STATUS_FILE="${STATUS_DIR}/restore-drill-status.json" mkdir -p "${STATUS_DIR}" if [ -z "${BACKUP_FILE}" ]; then # Try reading from backup-status.json STATUS_FILE="/var/lib/classnotify/ops/backup-status.json" if [ -f "${STATUS_FILE}" ]; then BACKUP_FILE=$(grep -o '"backupFile": *"[^"]*"' "${STATUS_FILE}" | cut -d'"' -f4 || true) fi # Fallback to newest dump file in daily directory if [ -z "${BACKUP_FILE}" ] || [ ! -f "${BACKUP_FILE}" ]; then BACKUP_FILE=$(find /var/backups/classnotify/daily/ -type f -name "classnotify_*.dump" 2>/dev/null | sort -r | head -n 1 || true) fi fi if [ -z "${BACKUP_FILE}" ] || [ ! -f "${BACKUP_FILE}" ]; then echo "[RESTORE GATE] ERROR: No valid backup dump file found to test restore!" >&2 exit 1 fi START_TIME=$(date +%s%3N) FILESIZE=$(stat -c%s "${BACKUP_FILE}" 2>/dev/null || stat -f%z "${BACKUP_FILE}" 2>/dev/null || wc -c < "${BACKUP_FILE}") echo "==========================================================" echo " ClassNotify P2.5 Production Restore Gate & Drill " echo " Target Backup: ${BACKUP_FILE} " echo " File Size: ${FILESIZE} bytes " echo "==========================================================" # Step 0: SHA256 Verification if checksum file exists SHA256_FILE="${BACKUP_FILE}.sha256" if [ -f "${SHA256_FILE}" ]; then echo "[RESTORE GATE] Step 0: Verifying SHA256 integrity checksum..." EXPECTED_SHA256=$(awk '{print $1}' "${SHA256_FILE}") ACTUAL_SHA256=$(sha256sum "${BACKUP_FILE}" | awk '{print $1}') if [ "${EXPECTED_SHA256}" != "${ACTUAL_SHA256}" ]; then echo "[RESTORE GATE] ERROR: SHA256 mismatch!" >&2 echo " Expected: ${EXPECTED_SHA256}" >&2 echo " Actual: ${ACTUAL_SHA256}" >&2 exit 1 fi echo "[PASS] SHA256 Verified: ${ACTUAL_SHA256}" else ACTUAL_SHA256=$(sha256sum "${BACKUP_FILE}" | awk '{print $1}') echo "[RESTORE GATE] Step 0: Computed SHA256: ${ACTUAL_SHA256}" fi TEMP_DB="classnotify_drill_$$" PG_CMD="sudo -u postgres psql -d ${TEMP_DB} -t -A -c" cleanup() { echo "[RESTORE GATE] Cleaning up temporary sandbox database ${TEMP_DB}..." sudo -u postgres psql -c "DROP DATABASE IF EXISTS \"${TEMP_DB}\";" 2>/dev/null || true } trap cleanup EXIT echo "[RESTORE GATE] Step 1: Creating isolated sandbox database ${TEMP_DB}..." sudo -u postgres psql -c "CREATE DATABASE \"${TEMP_DB}\";" echo "[RESTORE GATE] Step 2: Executing pg_restore from ${BACKUP_FILE}..." sudo -u postgres pg_restore -d "${TEMP_DB}" --no-owner "${BACKUP_FILE}" || { RET=$? # pg_restore exit code 1 means non-fatal warnings (e.g. comments/privileges), code >= 2 is fatal error if [ "$RET" -gt 1 ]; then echo "[RESTORE GATE] ERROR: pg_restore failed with fatal exit code ${RET}" >&2 exit 1 fi } echo "[RESTORE GATE] Step 3: Verifying restored schema and data integrity..." # Check 8 core tables exist CORE_TABLES=('Tenants' 'Classrooms' 'Users' 'Devices' 'Notifications' 'NotificationReceipts' 'ActivationCodes' 'DeviceLicenses') TABLE_CHECK_SQL="SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name IN ('Tenants', 'Classrooms', 'Users', 'Devices', 'Notifications', 'NotificationReceipts', 'ActivationCodes', 'DeviceLicenses');" TABLES=$($PG_CMD "${TABLE_CHECK_SQL}") if [ "${TABLES}" -lt 8 ]; then echo "[RESTORE GATE] FAILED: Missing core tables in restored database (Found: ${TABLES}/8)" >&2 exit 1 fi echo "[PASS] Core tables restored: ${TABLES}/8 present." # Check entity counts TENANT_COUNT=$($PG_CMD "SELECT count(*) FROM \"Tenants\";") DEVICE_COUNT=$($PG_CMD "SELECT count(*) FROM \"Devices\";") USER_COUNT=$($PG_CMD "SELECT count(*) FROM \"Users\";") NOTIF_COUNT=$($PG_CMD "SELECT count(*) FROM \"Notifications\";") RCPT_COUNT=$($PG_CMD "SELECT count(*) FROM \"NotificationReceipts\";") CODE_COUNT=$($PG_CMD "SELECT count(*) FROM \"ActivationCodes\";") LICENSE_COUNT=$($PG_CMD "SELECT count(*) FROM \"DeviceLicenses\";") echo "[PASS] Restored Entity Counts: Tenants=${TENANT_COUNT}, Devices=${DEVICE_COUNT}, Users=${USER_COUNT}, ActivationCodes=${CODE_COUNT}, DeviceLicenses=${LICENSE_COUNT}, Notifications=${NOTIF_COUNT}, Receipts=${RCPT_COUNT}" # Step 4: Relational Integrity & Zero Orphan Verification ORPHAN_RCPT=$($PG_CMD "SELECT count(*) FROM \"NotificationReceipts\" r LEFT JOIN \"Notifications\" n ON r.\"NotificationId\" = n.\"Id\" WHERE n.\"Id\" IS NULL;") if [ "${ORPHAN_RCPT}" -ne 0 ]; then echo "[RESTORE GATE] FAILED: Found ${ORPHAN_RCPT} orphan notification receipts in restored DB!" >&2 exit 1 fi ORPHAN_DEV=$($PG_CMD "SELECT count(*) FROM \"Devices\" d LEFT JOIN \"Tenants\" t ON d.\"TenantId\" = t.\"Id\" WHERE t.\"Id\" IS NULL;") if [ "${ORPHAN_DEV}" -ne 0 ]; then echo "[RESTORE GATE] FAILED: Found ${ORPHAN_DEV} orphan devices without valid tenant!" >&2 exit 1 fi echo "[PASS] Referential integrity: Zero orphan receipts (${ORPHAN_RCPT}) and zero orphan devices (${ORPHAN_DEV}) verified." # Step 5: ActivationCodes schema verification (Notes column) NOTES_COL=$($PG_CMD "SELECT count(*) FROM information_schema.columns WHERE table_name = 'ActivationCodes' AND column_name = 'Notes';") if [ "${NOTES_COL}" -lt 1 ]; then echo "[RESTORE GATE] WARNING: ActivationCodes.Notes column missing in restored schema!" else echo "[PASS] ActivationCodes schema verification: Notes column verified." fi # Step 6: Traceability Verification (CorrelationId) CORR_COL=$($PG_CMD "SELECT count(*) FROM information_schema.columns WHERE table_name = 'Notifications' AND column_name = 'CorrelationId';") if [ "${CORR_COL}" -ge 1 ]; then echo "[PASS] Traceability verification: Notifications.CorrelationId schema verified." fi END_TIME=$(date +%s%3N) DURATION_MS=$((END_TIME - START_TIME)) NOW_UTC=$(date -u +"%Y-%m-%dT%H:%M:%SZ") # Write structured JSON status cat < "${DRILL_STATUS_FILE}.tmp" { "lastDrillUtc": "${NOW_UTC}", "testedBackup": "${BACKUP_FILE}", "backupSizeBytes": ${FILESIZE}, "sha256": "${ACTUAL_SHA256}", "drillDurationMs": ${DURATION_MS}, "tablesVerified": ${TABLES}, "entityCounts": { "tenants": ${TENANT_COUNT}, "devices": ${DEVICE_COUNT}, "users": ${USER_COUNT}, "activationCodes": ${CODE_COUNT}, "deviceLicenses": ${LICENSE_COUNT}, "notifications": ${NOTIF_COUNT}, "receipts": ${RCPT_COUNT} }, "referentialIntegrity": "ZeroOrphansVerified", "status": "PASSED" } EOF mv "${DRILL_STATUS_FILE}.tmp" "${DRILL_STATUS_FILE}" chmod 644 "${DRILL_STATUS_FILE}" # Append to drill audit log echo "[${NOW_UTC}] RESTORE DRILL PASSED | Target: $(basename "${BACKUP_FILE}") | Size: ${FILESIZE}B | SHA256: ${ACTUAL_SHA256:0:12}.. | Duration: ${DURATION_MS}ms | Entities: T:${TENANT_COUNT} D:${DEVICE_COUNT} U:${USER_COUNT} C:${CODE_COUNT} L:${LICENSE_COUNT} N:${NOTIF_COUNT} R:${RCPT_COUNT}" >> "${DRILL_LOG}" echo "==========================================================" echo " RESTORE GATE RESULT: PASSED (Database is 100% restorable)" echo " Drill Duration: ${DURATION_MS}ms" echo "==========================================================" exit 0