Emergency Cancellation Rollback Guide¶

This guide explains how to manually roll back emergency mail cancellations using the snapshot files created by the cancel_mail_emergency.py script.

Overview¶

When the emergency cancellation script runs, it creates three snapshot files before any destructive operations:

Snapshot JSON - Full recipient data (sent and unsent)
SQL Dump - Mailpiece records for restoration
Metadata - Campaign information and summary counts

These files enable manual rollback in case of: - Partial API failures - Script crashes mid-execution - Wrong campaign selection - Database update failures - Need to reverse a cancellation

Locating Snapshot Files¶

File Location¶

Snapshot files are stored in:

out/cancellations/snapshots/

File Naming Pattern¶

campaign_<id>_<timestamp>_snapshot.json     # Recipient data
campaign_<id>_<timestamp>_mailpieces.sql    # SQL dump for mailpieces
campaign_<id>_<timestamp>_metadata.json     # Campaign metadata

Example:

campaign_123_20250115_143022_snapshot.json
campaign_123_20250115_143022_mailpieces.sql
campaign_123_20250115_143022_metadata.json

Finding Snapshots for a Campaign¶

Option 1: Check results JSON

cat out/cancellations/results/campaign_123_*_results.json | jq .snapshot_files

Option 2: List by campaign ID

ls out/cancellations/snapshots/campaign_123_*

Option 3: List recent snapshots

ls -lt out/cancellations/snapshots/ | head -20

Rollback Steps¶

Step 1: Verify Snapshot Files¶

Before starting rollback, verify all three files exist and are valid:

# Set variables for your campaign
CAMPAIGN_ID=123
TIMESTAMP=20250115_143022

# Check files exist
ls out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_*

# Verify JSON is valid
jq . out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_snapshot.json > /dev/null && echo "Valid JSON"
jq . out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_metadata.json > /dev/null && echo "Valid metadata"

# Check SQL dump content
head -20 out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_mailpieces.sql

Step 2: Review Snapshot Contents¶

Check metadata:

jq . out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_metadata.json

Expected output:

{
  "campaign_id": 123,
  "campaign_name": "Winter Sale Postcards",
  "provider": "4x6",
  "counts": {
    "sent": 100,
    "unsent": 20,
    "total": 120
  },
  "timestamp": "2025-01-15T14:30:22",
  "dry_run": false
}

Check recipient counts:

jq '.counts' out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_snapshot.json

Step 3: Restore Mailpiece Records¶

If mailpiece records were deleted, restore them from the SQL dump:

# Connect to database
psql $POSTGRES_CONNECTION_URL

# Within psql:
\i out/cancellations/snapshots/campaign_123_20250115_143022_mailpieces.sql

# Or directly:
psql $POSTGRES_CONNECTION_URL < out/cancellations/snapshots/campaign_123_20250115_143022_mailpieces.sql

Verify restoration:

-- Check mailpiece count
SELECT COUNT(*) FROM mailpieces
WHERE provider_id IN (
  -- List provider IDs from snapshot
  SELECT json->>'mailer_id'
  FROM json_array_elements('...'::json->'recipients'->'sent')
);

Step 4: Restore Recipient Status¶

Restore campaign recipient statuses using the snapshot data.

For sent recipients:

-- Restore sent recipients to 'Sent' status
UPDATE campaign_recipients
SET
  status = data.original_status,
  sent_at = data.original_sent_at::timestamp with time zone,
  updated_at = NOW()
FROM (
  VALUES
    (1, 'Sent', '2025-01-15T10:00:00'),  -- Replace with actual data from snapshot
    (2, 'Sent', '2025-01-15T10:05:00'),
    -- ... add all sent recipients from snapshot
  ) AS data(id, original_status, original_sent_at)
WHERE campaign_recipients.id = data.id;

Generate SQL from snapshot:

# Extract sent recipients and generate UPDATE statements
jq -r '.recipients.sent[] |
  "(\(.id), '\''\(.status)'\'', '\''\(.sent_at // "null")'\'')"' \
  out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_snapshot.json | \
  head -20

For unsent recipients:

-- Restore unsent recipients from Error back to original status
UPDATE campaign_recipients
SET
  status = data.original_status,
  updated_at = NOW()
FROM (
  VALUES
    (10, 'Pending'),  -- Replace with actual data from snapshot
    (11, 'Queued'),
    -- ... add all unsent recipients from snapshot
  ) AS data(id, original_status)
WHERE campaign_recipients.id = data.id;

Generate SQL from snapshot:

# Extract unsent recipients and generate UPDATE statements
jq -r '.recipients.unsent[] |
  "(\(.id), '\''\(.status)'\'')"' \
  out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_snapshot.json | \
  head -20

Step 5: Verify Restoration¶

Check recipient statuses:

-- Verify sent recipient restoration
SELECT status, COUNT(*)
FROM campaign_recipients
WHERE campaign_id = 123
  AND id IN (
    -- List IDs from snapshot sent recipients
    1, 2, 3, ...
  )
GROUP BY status;

-- Expected: All should be 'Sent' (or original status)

-- Verify unsent recipient restoration
SELECT status, COUNT(*)
FROM campaign_recipients
WHERE campaign_id = 123
  AND id IN (
    -- List IDs from snapshot unsent recipients
    10, 11, 12, ...
  )
GROUP BY status;

-- Expected: None should be 'Error'

Check mailpiece restoration:

SELECT COUNT(*) FROM mailpieces
WHERE provider_id IN (
  -- List provider IDs from snapshot
);

-- Compare count to metadata.counts.sent

Compare with snapshot counts:

# Get expected counts from metadata
jq '.counts' out/cancellations/snapshots/campaign_${CAMPAIGN_ID}_${TIMESTAMP}_metadata.json

Advanced Scenarios¶

Partial Rollback (Only Recipients)¶

If mailpieces were not deleted (using --keep-mailpieces flag), only restore recipient statuses:

-- Check if mailpieces exist
SELECT COUNT(*) FROM mailpieces WHERE provider_id = '<any_mailer_id_from_snapshot>';

-- If mailpieces exist, skip Step 3 and proceed directly to Step 4

Rollback After Partial Failure¶

If the script partially completed:

Check the results JSON to see which cancellations succeeded:

jq '.successful | length' out/cancellations/results/campaign_123_*_results.json
jq '.failed | length' out/cancellations/results/campaign_123_*_results.json

Only restore recipients that were successfully cancelled:

# Get list of successfully cancelled provider IDs
jq -r '.successful[].provider_id' out/cancellations/results/campaign_123_*_results.json

Filter snapshot data to only those provider IDs when restoring

Dry-Run Snapshot Validation¶

If testing with dry-run mode:

# Snapshots are prefixed with DRY_RUN_
ls out/cancellations/snapshots/DRY_RUN_campaign_*

# These can be used to validate rollback procedures without affecting real data

Helper Scripts¶

Generate Restoration SQL¶

Create a Python script to generate SQL from snapshot JSON:

import json
import sys

# Read snapshot file
with open(sys.argv[1], 'r') as f:
    snapshot = json.load(f)

# Generate UPDATE statements for sent recipients
print("-- Restore sent recipients")
print("UPDATE campaign_recipients SET")
print("  status = data.original_status,")
print("  sent_at = data.original_sent_at::timestamp with time zone,")
print("  updated_at = NOW()")
print("FROM (VALUES")

sent = snapshot['recipients']['sent']
for i, recipient in enumerate(sent):
    comma = ',' if i < len(sent) - 1 else ''
    sent_at = recipient.get('sent_at', 'null')
    print(f"  ({recipient['id']}, '{recipient['status']}', '{sent_at}'){comma}")

print(") AS data(id, original_status, original_sent_at)")
print("WHERE campaign_recipients.id = data.id;")
print()

# Generate UPDATE statements for unsent recipients
print("-- Restore unsent recipients")
print("UPDATE campaign_recipients SET")
print("  status = data.original_status,")
print("  updated_at = NOW()")
print("FROM (VALUES")

unsent = snapshot['recipients']['unsent']
for i, recipient in enumerate(unsent):
    comma = ',' if i < len(unsent) - 1 else ''
    print(f"  ({recipient['id']}, '{recipient['status']}'){comma}")

print(") AS data(id, original_status)")
print("WHERE campaign_recipients.id = data.id;")

Usage:

python generate_rollback_sql.py out/cancellations/snapshots/campaign_123_*_snapshot.json > rollback.sql
psql $POSTGRES_CONNECTION_URL < rollback.sql

Troubleshooting¶

Snapshot Files Not Found¶

Cause: Script may have failed before snapshot creation or files were deleted

Solution: - Check if operation reached snapshot creation phase in logs - Look for DRY_RUN_ prefixed files if testing - Contact engineering team if files are missing

SQL Dump Restoration Fails¶

Cause: Duplicate key violations if mailpieces weren't actually deleted

Resolution:

-- Check if mailpieces still exist
SELECT COUNT(*) FROM mailpieces WHERE provider_id = '<provider_id_from_snapshot>';

-- If they exist, skip mailpiece restoration

Recipient IDs Not Found¶

Cause: Recipients may have been deleted or ID mismatches

Resolution:

-- Verify recipients exist
SELECT id, status, campaign_id
FROM campaign_recipients
WHERE id IN (<ids_from_snapshot>)
LIMIT 10;

-- Check for campaign_id mismatch
SELECT id, campaign_id
FROM campaign_recipients
WHERE id IN (<ids_from_snapshot>)
  AND campaign_id != <expected_campaign_id>;

Post-Rollback Actions¶

After successful rollback:

Verify counts match:

SELECT
  campaign_id,
  status,
  COUNT(*) as count
FROM campaign_recipients
WHERE campaign_id = 123
GROUP BY campaign_id, status;

Document rollback in results:

echo '{"rollback_performed": true, "rollback_timestamp": "'$(date -Iseconds)'", "snapshot_used": "campaign_123_20250115_143022"}' \
  > out/cancellations/results/campaign_123_rollback.json

Notify team:
Update relevant tickets/incidents
Document why rollback was necessary
Review what went wrong to prevent future issues

Prevention¶

To avoid needing rollbacks:

Always use dry-run first:

python -m app.scripts.cancel_mail_emergency --org-id 123 --dry-run

Double-check campaign ID:

SELECT id, campaign_name, organization_id, status
FROM campaigns
WHERE id = <campaign_id>;

Review recipient counts:

SELECT
  COUNT(*) as total_recipients,
  COUNT(*) FILTER (WHERE mailer_id IS NOT NULL) as sent_recipients,
  COUNT(*) FILTER (WHERE mailer_id IS NULL) as unsent_recipients
FROM campaign_recipients
WHERE campaign_id = <campaign_id>;

Test on non-production first if possible

Support¶

If you encounter issues during rollback:

Preserve snapshot files - do not delete or modify them
Document the state - run verification queries and save output
Contact engineering team with:
Campaign ID
Snapshot file paths
Error messages
Results JSON (if available)
Current recipient status counts