Skip to main content

​
Overview

This runbook defines backup and restore procedures for PensionPortal.ai in compliance with DORA Art. 12 (ICT Business Continuity Policy) and GDPR Art. 32 (security of processing). It covers the Neon PostgreSQL database, Vercel Blob document storage, application code, and environment configuration.
PensionPortal.ai stores GDPR-protected personal data including encrypted PPS numbers. Any backup containing member data is subject to GDPR data protection requirements. Encrypted backup files must be stored securely and access must be logged.

​
1. Backup Architecture Overview

ComponentBackup MethodManaged ByNotes
Production DatabaseContinuous WAL archiving + automated snapshotsNeonPoint-in-time recovery available
Document StorageRedundant object storageVercel (Blob)Managed — no manual backup required
Application CodeGit version controlGitHubEvery commit is recoverable
Environment ConfigurationManual — password managerEngineering teamMust be backed up manually (see Section 6)

​
2. RTO/RPO Targets (DORA Art. 12 Compliance)

These are conservative targets designed to be achievable even under adverse conditions. Neon’s point-in-time recovery typically allows an RPO of approximately 5 minutes on the Pro plan. Under normal conditions, recovery will be significantly faster than the RTO targets below.
TierSystemRPO (Recovery Point Objective)RTO (Recovery Time Objective)
Tier 1Production Database24 hours4 hours
Tier 2Document Storage (Vercel Blob)24 hours8 hours
Tier 3Application Code (GitHub)0 — every commit preserved1 hour
Tier 4Environment ConfigurationPer last manual backup2 hours
Tier 4 (Environment Configuration) is the most vulnerable component. If PPS_ENCRYPTION_KEY is lost and no backup exists, all encrypted PPS numbers become permanently unrecoverable. Run the configuration backup procedure (Section 6) monthly at minimum, and immediately after any secret rotation.

​
3. Neon PostgreSQL Backup Details

Neon provides continuous backup without any manual configuration required.
PropertyFree PlanPro PlanRecommendation
Backup typeContinuous WAL archivingContinuous WAL archiving—
Snapshot frequencyAutomaticAutomatic—
Retention period7 days30 daysUse Pro plan for production
Point-in-time recoveryUp to 7 daysUp to 30 days—
Restore interfaceNeon ConsoleNeon Console—
To review current backup status:
  1. Log in to Neon Console
  2. Select the PensionPortal.ai project
  3. Navigate to Branches → select the production branch
  4. The branch details panel shows the earliest available restore point
The Free plan’s 7-day retention window does not satisfy DORA Art. 12 requirements for a production pension platform. The production Neon project must be on the Pro plan.

​
4. Manual Database Backup

Use this procedure when a point-in-time export is needed — for example, before a major migration, or for an audit/legal hold. 
# Requires pg_dump installed locally and DATABASE_URL_UNPOOLED from Vercel env
# Use the unpooled connection string — pg_dump is not compatible with PgBouncer

# Export full database as a custom-format dump
pg_dump $DATABASE_URL_UNPOOLED \
  --format=custom \
  --no-owner \
  --no-acl \
  --file="pensionportal-backup-$(date +%Y%m%d-%H%M%S).dump"

# Verify the dump is valid
pg_restore --list pensionportal-backup-*.dump | head -20
Encrypt the dump before storing it: 
# Encrypt using GPG symmetric AES-256
gpg --symmetric \
    --cipher-algo AES256 \
    --output "pensionportal-backup-$(date +%Y%m%d-%H%M%S).dump.gpg" \
    pensionportal-backup-*.dump

# Delete the unencrypted dump
rm pensionportal-backup-*.dump

# Store the .gpg file in secure offline storage
# Document the GPG passphrase in the company password manager
The unencrypted dump file contains all member PII including encrypted PPS numbers. Delete it immediately after encrypting. Never store unencrypted database dumps on local machines, shared drives, or cloud storage without encryption.

​
5. Database Restore Procedure

Use this for most recovery scenarios. Neon creates a new branch at the restore point, allowing verification before switching production traffic. Step 1 — Open the restore interface:
  1. Log in to Neon Console
  2. Navigate to Project → Branches
  3. Select the main (production) branch
  4. Click Restore in the branch details panel
Step 2 — Select the restore point:
  • Choose by timestamp (e.g., “restore to 2 hours ago”)
  • Or select a named snapshot if one exists
Step 3 — Create restore branch:
  • Neon creates a new branch at the selected point in time
  • Note the new branch’s connection string
Step 4 — Verify data integrity on the restore branch: 
# Connect to the restored branch (using its specific connection string)
psql $RESTORE_BRANCH_CONNECTION_STRING

# Spot-check critical tables
SELECT COUNT(*) FROM members;
SELECT COUNT(*) FROM schemes;
SELECT COUNT(*) FROM audit_logs;

# Verify most recent records match expected state
SELECT id, updated_at FROM members ORDER BY updated_at DESC LIMIT 5;
Step 5 — Switch application to the restored branch:
  1. In Vercel Dashboard → Project → Settings → Environment Variables
  2. Update DATABASE_URL and DATABASE_URL_UNPOOLED to the restored branch URLs
  3. Redeploy the application
Step 6 — Confirm application operates correctly:
  • Application loads and authentication works
  • BrokerAdmin can view schemes and members
  • PPS decryption works for a test member (confirms encryption key matches)
  • Audit logs are being written
Step 7 — Cleanup:
  • Once satisfied, delete the old production branch in Neon Console
  • Promote the restored branch to be the new production branch (or rename it)

​
Option B — Manual Restore from pg_dump

Use this if a custom dump was taken (Section 4) and needs to be restored. 
# Decrypt the backup first
gpg --decrypt pensionportal-backup-YYYYMMDD.dump.gpg \
    --output pensionportal-backup-YYYYMMDD.dump

# Restore to the target database
# WARNING: --clean will DROP and recreate all objects before restoring
pg_restore \
  --dbname=$DATABASE_URL_UNPOOLED \
  --clean \
  --if-exists \
  --no-owner \
  --no-acl \
  --verbose \
  pensionportal-backup-YYYYMMDD.dump

# Delete the decrypted dump file immediately after restore
rm pensionportal-backup-YYYYMMDD.dump
pg_restore --clean is destructive — it drops all existing objects before restoring. Only use this against a dedicated restore target or in a confirmed disaster recovery scenario where the current data is already lost or corrupted.
Verify the restore: 
psql $DATABASE_URL_UNPOOLED -c "SELECT COUNT(*) FROM members;"
psql $DATABASE_URL_UNPOOLED -c "SELECT COUNT(*) FROM schemes;"
psql $DATABASE_URL_UNPOOLED -c "\dt"  # List all tables

​
6. Configuration Backup (Manual — Run Monthly)

Vercel environment variable names (not their secret values) can be viewed in the dashboard. Secret values are never retrievable from Vercel after entry — they must be backed up externally. Procedure — run on the first business day of each month:
  1. Open the company password manager (1Password / Bitwarden)
  2. Locate the “PensionPortal.ai Production Secrets” vault entry
  3. Verify each of the following secrets is present, up to date, and matches what is deployed:
VariableCriticalityNotes
PPS_ENCRYPTION_KEYCRITICALLoss = permanent loss of all PPS data
AUTH_SECRETHighLoss requires forced logout of all users
DATABASE_URLHighPooled connection string
DATABASE_URL_UNPOOLEDHighDirect connection string (needed for migrations)
ANTHROPIC_API_KEYMediumAI compliance assistant
RESEND_API_KEYMediumEmail delivery
BLOB_READ_WRITE_TOKENMediumDocument storage
  1. After verifying, record the backup date in the password manager entry notes
  2. If any secret has been rotated since the last backup, update the password manager entry immediately
The configuration backup procedure should also be run immediately after any secret rotation (e.g., after an incident requiring key rotation). Do not wait for the monthly cycle in those cases.

​
7. Data Anonymisation Procedure (GDPR Art. 17 — Right to Erasure)

When a data subject (pension scheme member) submits a valid erasure request and no legal hold applies (note: pension records may be subject to a 7-year statutory retention period — verify with legal before proceeding): 
-- Step 1: Verify no legal hold applies
-- Check scheme compliance records for statutory retention requirements
SELECT s.name, s.type, e.name AS employer
FROM members m
JOIN schemes s ON s.id = m.scheme_id
JOIN employers e ON e.id = s.employer_id
WHERE m.id = $memberId;

-- Step 2: Anonymise the member record
-- Preserves referential integrity and audit trail structure
-- but removes all personal identifiers
UPDATE members SET
  first_name            = 'DELETED',
  last_name             = 'DELETED',
  email                 = NULL,
  phone                 = NULL,
  pps_number_encrypted  = NULL,
  date_of_birth         = NULL,
  address_line1         = NULL,
  address_line2         = NULL,
  address_city          = NULL,
  address_eircode       = NULL,
  updated_at            = NOW()
WHERE id = $memberId;

-- Step 3: Log the erasure in the audit trail
INSERT INTO audit_logs (
  actor_id,
  action,
  entity_type,
  entity_id,
  notes,
  timestamp
) VALUES (
  $adminUserId,
  'GDPR_ERASURE',
  'member',
  $memberId,
  'GDPR Art. 17 erasure request — all PII removed. Structural record retained for scheme integrity.',
  NOW()
);
Irish pension scheme records may be subject to a statutory 7-year retention period under the Pensions Act 1990 (as amended). Do not process an erasure request for a member of an active scheme, or a member whose records are within the retention window, without written legal sign-off. The DPO must approve all GDPR Art. 17 erasure requests for pension scheme members.
After anonymisation:
  • Confirm the member record no longer displays any PII in the portal
  • Confirm the audit log entry is present
  • Notify the data subject that their erasure request has been fulfilled
  • File correspondence in the DPO records (GDPR Art. 30)