Backup & Restore Runbook

Overview

This runbook defines backup and restore procedures for PensionsPortal.ie 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.

PensionsPortal.ie 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

Component	Backup Method	Managed By	Notes
Production Database	Continuous WAL archiving + automated snapshots	Neon	Point-in-time recovery available
Document Storage	Redundant object storage	Vercel (Blob)	Managed — no manual backup required
Application Code	Git version control	GitHub	Every commit is recoverable
Environment Configuration	Manual — password manager	Engineering team	Must 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.

Tier	System	RPO (Recovery Point Objective)	RTO (Recovery Time Objective)
Tier 1	Production Database	24 hours	4 hours
Tier 2	Document Storage (Vercel Blob)	24 hours	8 hours
Tier 3	Application Code (GitHub)	0 — every commit preserved	1 hour
Tier 4	Environment Configuration	Per last manual backup	2 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.

Property	Free Plan	Pro Plan	Recommendation
Backup type	Continuous WAL archiving	Continuous WAL archiving	—
Snapshot frequency	Automatic	Automatic	—
Retention period	7 days	30 days	Use Pro plan for production
Point-in-time recovery	Up to 7 days	Up to 30 days	—
Restore interface	Neon Console	Neon Console	—

To review current backup status:

Log in to Neon Console
Select the PensionsPortal.ie project
Navigate to Branches → select the production branch
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="pensionsportal-backup-$(date +%Y%m%d-%H%M%S).dump"

# Verify the dump is valid
pg_restore --list pensionsportal-backup-*.dump | head -20

Encrypt the dump before storing it:

# Encrypt using GPG symmetric AES-256
gpg --symmetric \
    --cipher-algo AES256 \
    --output "pensionsportal-backup-$(date +%Y%m%d-%H%M%S).dump.gpg" \
    pensionsportal-backup-*.dump

# Delete the unencrypted dump
rm pensionsportal-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

Option A — Neon Console Restore (Recommended)

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:

Log in to Neon Console
Navigate to Project → Branches
Select the main (production) branch
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:

In Vercel Dashboard → Project → Settings → Environment Variables
Update DATABASE_URL and DATABASE_URL_UNPOOLED to the restored branch URLs
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 pensionsportal-backup-YYYYMMDD.dump.gpg \
    --output pensionsportal-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 \
  pensionsportal-backup-YYYYMMDD.dump

# Delete the decrypted dump file immediately after restore
rm pensionsportal-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:

Open the company password manager (1Password / Bitwarden)
Locate the “PensionsPortal.ie Production Secrets” vault entry
Verify each of the following secrets is present, up to date, and matches what is deployed:

Variable	Criticality	Notes
`PPS_ENCRYPTION_KEY`	CRITICAL	Loss = permanent loss of all PPS data
`AUTH_SECRET`	High	Loss requires forced logout of all users
`DATABASE_URL`	High	Pooled connection string
`DATABASE_URL_UNPOOLED`	High	Direct connection string (needed for migrations)
`ANTHROPIC_API_KEY`	Medium	AI compliance assistant
`RESEND_API_KEY`	Medium	Email delivery
`BLOB_READ_WRITE_TOKEN`	Medium	Document storage

After verifying, record the backup date in the password manager entry notes
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.

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)

Getting Started

Annual Compliance Statement

Own-Risk Assessment

Key Function Holders

Written Policies

Supervisory Readiness

OMA Transition

AI Assistant

Compliance Calendar

Overview

1. Backup Architecture Overview

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

3. Neon PostgreSQL Backup Details

4. Manual Database Backup

5. Database Restore Procedure

Option A — Neon Console Restore (Recommended)

Option B — Manual Restore from pg_dump

6. Configuration Backup (Manual — Run Monthly)

​Overview

​1. Backup Architecture Overview

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

​3. Neon PostgreSQL Backup Details

​4. Manual Database Backup

​5. Database Restore Procedure

​Option A — Neon Console Restore (Recommended)

​Option B — Manual Restore from pg_dump

​6. Configuration Backup (Manual — Run Monthly)

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

Overview

1. Backup Architecture Overview

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

3. Neon PostgreSQL Backup Details

4. Manual Database Backup

5. Database Restore Procedure

Option A — Neon Console Restore (Recommended)

Option B — Manual Restore from pg_dump

6. Configuration Backup (Manual — Run Monthly)

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