Neon Database Backup & Restore Guide¶

Date: November 1, 2025 Status: ✅ PRODUCTION READY Phase: Phase 5 Week 1 Day 3 - Task 4

Executive Summary¶

CodeSlick uses Neon Postgres (neon.com) as its production database. This document provides comprehensive backup configuration and disaster recovery procedures to ensure data safety for customer data, team information, and PR analysis history.

Critical Data Protected: - 🏢 Teams and organization data - 👥 User accounts and authentication - 📊 PR analysis history and metrics - 💳 Stripe billing information - 🔐 GitHub installation records - 📧 Team member invitations

Neon Backup Features¶

1. Point-in-Time Restore (PITR) - AUTOMATIC¶

Neon automatically maintains a Write-Ahead-Log (WAL) history for your database, enabling restoration to any point within your configured retention window.

Key Features: - ✅ Automatic - No manual setup required - ✅ Instant restore - Completes in seconds - ✅ Precise recovery - Restore to specific timestamp or LSN - ✅ Non-destructive - Creates automatic backup before restore - ✅ Zero downtime - Applications auto-reconnect

Retention Periods by Plan: | Plan | Maximum Retention | Default | Storage Impact | |------|-------------------|---------|----------------| | Free | 6 hours or 1 GB | 6 hours | Minimal | | Launch | 7 days | 1 day | Moderate | | Scale | 14 days | 1 day | Moderate | | Enterprise | 30 days | 1 day | High |

How It Works: - Neon captures every database change as WAL records - WAL records are stored in Neon's object storage - You can "time travel" to any point within the retention window - Older records are automatically evicted based on retention policy

2. Branch-Based Backups - RECOMMENDED FOR STAGING¶

Neon's branching feature allows you to create instant database copies (branches) for testing, staging, or backup purposes.

Use Cases: - 🧪 Create test branch before risky migrations - 📸 Snapshot production data for debugging - 🔄 Create staging environments from production - 💾 Manual backup before major changes

Branch Features: - Instant creation (copy-on-write, no duplication) - Independent schemas and data - Can restore branch from parent at any time - Branches can have their own retention windows

3. Traditional pg_dump Backups - OPTIONAL¶

Standard PostgreSQL backup tools work with Neon for compliance or long-term archival needs.

When to Use: - 📜 Compliance requirements (7+ year retention) - 🏛️ Archival backups for audit purposes - 🔄 Migration to another database provider - 📦 Backup to S3, Google Cloud Storage, etc.

Not Recommended Because: - PITR is faster and more reliable - pg_dump requires manual scheduling and monitoring - Additional storage costs - More complex restore procedures

Current Configuration¶

Database Details¶

Provider: Neon.com (Serverless Postgres) Connection Type: Pooled connection via PgBouncer Environment Variables:

DATABASE_URL=postgresql://...@...-pooler.neon.tech/codeslick?sslmode=require  # Pooled (for queries)
DATABASE_URL_UNPOOLED=postgresql://...@....neon.tech/codeslick?sslmode=require  # Unpooled (for migrations)

Current Backup Status¶

To Verify Your Current Configuration:

Login to Neon Console: https://console.neon.tech
Select your CodeSlick project
Navigate to Settings > Storage
Check Instant restore section for current retention window

Expected Current State (as of Nov 2025): - ⚠️ Retention Window: 1 day (default) - ⚠️ Plan: Launch (supports up to 7 days) - ⚠️ Status: NOT OPTIMIZED for production

Recommended Configuration¶

For Production Launch (Beta + Beyond)¶

Recommended Settings: - ✅ Retention Window: 7 days (maximum for Launch plan) - ✅ Branch Strategy: Create weekly staging branch - ✅ Monitoring: Enable Vercel/Neon alerts for database issues - ✅ Testing: Monthly restore test to staging branch

Configuration Steps¶

Step 1: Increase Retention Window¶

Goal: Enable 7-day point-in-time restore capability

Login to Neon Console
URL: https://console.neon.tech
Sign in with your Neon account
Navigate to Project Settings
Click on your CodeSlick project
Go to Settings (left sidebar)
Select Storage tab
Configure Instant Restore
Find Instant restore section
Current value: Likely "1 day"
Click Edit or adjust slider
Set Retention to 7 Days
Move slider to 7 days (maximum for Launch plan)
Note: This will increase storage costs slightly (~5-10% for typical usage)
Click Save or Apply
Verify Configuration
Retention window should now show: 7 days
Estimated storage increase: ~100 MB - 1 GB (depending on daily change volume)

Cost Impact: - Additional storage: ~$0.50 - $5/month (depending on write volume) - Worth it for production data safety

Step 2: Create Staging Branch (Optional but Recommended)¶

Goal: Maintain a staging environment for testing migrations

Navigate to Branches
In Neon Console, go to Branches tab
Current: Only main branch exists
Create Staging Branch
Click Create branch
Branch name: staging
Source: main
Type: Branch (not endpoint)
Click Create
Configure Staging Environment
Copy staging connection string
Add to Vercel as DATABASE_URL_STAGING (optional)
Use for pre-production testing

Branching Strategy:

main (production)
  ↓
staging (weekly refresh from main)
  ↓
dev (developer testing, disposable)

Step 3: Enable Monitoring Alerts (Recommended)¶

Neon Console Alerts
Go to Settings > Notifications
Enable alerts for:
- Storage limit approaching
- Compute time limit approaching
- Connection errors
Vercel Integration
If using Vercel Postgres Integration:
- Alerts automatically forwarded to project owner
- Check Vercel dashboard for database health

Restore Procedures¶

Scenario 1: Restore to Recent Point (Last 7 Days)¶

Use Case: Data deleted/corrupted, need to restore to specific time

Via Neon Console (Recommended)¶

Step-by-Step:

Identify Target Timestamp
Determine when data was last good (e.g., "Yesterday at 2:00 PM UTC")
Convert to ISO format: 2025-11-03T14:00:00Z
Login to Neon Console
URL: https://console.neon.tech
Navigate to your CodeSlick project
Navigate to Restore Page
Click Restore in left sidebar
Select branch: main (production)
Choose Restore Method
Tab: From history (already selected)
Format: Timestamp (default, easier than LSN)
Select Timestamp
Drag slider or enter timestamp: 2025-11-03T14:00:00Z
Preview shows: "Restoring to Nov 3, 2025 2:00 PM UTC"
Review Impact
Click Next
Review details:
- Branch: main
- Target: 2025-11-03T14:00:00Z
- Automatic backup: main_old_{current_timestamp} (created automatically)
All databases on branch will be restored (complete overwrite)
Execute Restore
Click Restore button
Confirmation dialog: "Are you sure?"
Click Confirm
Wait for Completion
Progress bar appears
Typically completes in: 5-30 seconds
Applications auto-reconnect when complete
Verify Data
Check CodeSlick dashboard
Verify missing data is restored
Check recent records match expected state

Automatic Safety: - Neon automatically creates backup branch: main_old_{timestamp} - Can restore from this backup if restore was incorrect - Backup branch preserved for 24 hours (then deleted to save storage)

Via Neon CLI (Advanced)¶

Prerequisites:

# Install Neon CLI
npm install -g neonctl

# Authenticate
neonctl auth

# List projects
neonctl projects list

Restore Command:

# Restore main branch to specific timestamp
neonctl branches restore main ^self@2025-11-03T14:00:00Z \
  --project-id your-project-id \
  --preserve-under-name main_old_manual_backup

# Output:
# ✔ Branch main restored to 2025-11-03T14:00:00Z
# ✔ Previous state saved to main_old_manual_backup

Scenario 2: Test Data Restore (Time Travel Query)¶

Use Case: Check if specific data existed at a past time (non-destructive)

Method: Time Travel SQL Queries

Steps:

Connect to Database
```
# Using psql
psql $DATABASE_URL
```

Query Historical Data

-- Check teams table state as of yesterday 2 PM
SELECT * FROM teams
WHERE created_at < '2025-11-03T14:00:00Z'::timestamp;

-- Use Postgres time travel extension (if enabled)
SELECT * FROM teams
AS OF SYSTEM TIME '2025-11-03T14:00:00Z';

Verify Data Without Restoring
Review results
Confirm restore point has correct data
If correct, proceed with full restore (Scenario 1)

Note: Time Travel queries work within retention window only (7 days with recommended config).

Scenario 3: Restore from Staging/Backup Branch¶

Use Case: Restore production from staging or backup branch

Steps:

Navigate to Branches
Neon Console > Branches
Select main (production)
Choose Restore Source
Click Restore (or use dropdown: Reset from parent)
Tab: From another branch
Select Source Branch
Branch: staging (or main_old_... backup branch)
Option: Restore from latest data (head) - CHECKED
- If you want historical data from branch, UNCHECK and select timestamp
Review and Confirm
Source: staging (or backup branch)
Target: main
Click Restore
Wait for Completion
Typically 5-30 seconds
Automatic backup created: main_old_{timestamp}
Verify Data
Check production environment
Verify data matches staging state

Scenario 4: Complete Database Loss (Worst Case)¶

Use Case: Project deleted, branch corrupted beyond repair

Recovery Options:

Option A: Restore from Another Branch (If Available)¶

Follow Scenario 3 above
If you maintained weekly staging branch, restore from there
Max data loss: 1 week (since last staging refresh)

Option B: Contact Neon Support (Enterprise SLA)¶

Email: support@neon.com
Neon maintains internal backups for disaster recovery
Enterprise plans have guaranteed recovery SLA
Free/Launch plans: Best effort, no guarantee

Option C: Restore from pg_dump Backup (If You Created One)¶

Not recommended as primary strategy
Only if you set up automated pg_dump backups to S3/GCS
Requires manual restore procedure

Prevention: - ✅ Keep 7-day retention window - ✅ Maintain staging branch (weekly refresh) - ✅ Test restores monthly - ✅ Monitor Neon alerts

Testing Restores¶

Monthly Restore Test Procedure¶

Goal: Verify backup system works before emergency

Frequency: Monthly (1^st day of each month)

Procedure:

Create Test Branch

# Via CLI
neonctl branches create test_restore --parent main

# Or via Console:
# Branches > Create branch > Name: test_restore > Source: main

Verify Test Branch Data

# Connect to test branch
psql postgresql://...@...-pooler.neon.tech/test_restore

# Verify data
SELECT COUNT(*) FROM teams;
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM analysis_records;

Test Point-in-Time Restore
Console: Restore > test_restore branch
Target: 24 hours ago
Execute restore
Verify data from yesterday is present
Document Results
Date: [Today's date]
Restore time: [e.g., 15 seconds]
Data verified: [e.g., "All tables present, 10 teams, 25 users"]
Status: ✅ PASS or ❌ FAIL

Delete Test Branch

# Clean up
neonctl branches delete test_restore --project-id your-project-id

Log Template:

Monthly Restore Test - November 2025
Date: 2025-11-01
Tester: Vitor
Branch: test_restore
Restore Target: 2025-10-31T12:00:00Z (24h ago)
Restore Time: 18 seconds
Data Verified:
  - Teams: 3 present ✅
  - Users: 12 present ✅
  - Analysis records: 47 present ✅
Status: ✅ PASS

Next Test: 2025-12-01

Emergency Recovery¶

Emergency Contacts¶

Neon Support: - Email: support@neon.com - Console: Help icon (chat bubble) in bottom-right - Status Page: https://neonstatus.com - Response Time: - Free/Launch: 24-48 hours (best effort) - Scale/Enterprise: <4 hours (SLA)

CodeSlick Internal: - Database owner: Vitor - Vercel project owner: Vitor - Emergency access: Documented in 1Password/vault

Emergency Scenarios & Actions¶

1. Accidental Data Deletion (Last 15 Minutes)¶

Action: Immediate PITR restore - Time to Restore: 5 minutes - Data Loss: Zero (if within retention window) - Procedure: Follow Scenario 1 (Restore to Recent Point)

2. Bad Migration (Schema Corruption)¶

Action: Rollback to pre-migration state - Time to Restore: 5 minutes - Data Loss: Transactions during migration (usually <1 minute) - Procedure: 1. Identify migration start time 2. Restore to 1 minute before migration 3. Review and fix migration script 4. Re-run corrected migration

Prevention: Always test migrations on staging branch first

3. Ransomware / Data Corruption¶

Action: Restore to last known good state - Time to Restore: 10 minutes - Data Loss: Depends on detection time - Procedure: 1. Identify when corruption started (check logs) 2. Restore to timestamp before corruption 3. Change all database passwords 4. Review access logs for breach source 5. Contact Neon Support for security review

4. Project Deleted / Catastrophic Loss¶

Action: Contact Neon Support immediately - Time to Restore: 2-24 hours (depends on Neon response) - Data Loss: Potentially significant if no staging branch - Procedure: 1. Email support@neon.com with project ID 2. Explain situation (accidental deletion) 3. Request disaster recovery from Neon internal backups 4. Wait for Neon support response

Prevention: - Enable branch protection on main - Require MFA for Neon account - Maintain weekly staging branch

Best Practices¶

Configuration Best Practices¶

✅ DO: - Set retention window to maximum for your plan (7 days for Launch) - Create staging branch for testing migrations - Enable Neon email/Slack alerts for storage/compute limits - Document your project ID and database credentials securely - Test restores monthly (1^st day of month) - Keep staging branch refreshed weekly

❌ DON'T: - Set retention to 0 days (disables PITR completely) - Delete staging branch (keep for emergency recovery) - Ignore Neon storage alerts (can cause data loss if quota exceeded) - Share database credentials in plain text - Skip monthly restore tests

Migration Best Practices¶

✅ Before Migration: 1. Note current timestamp: date -u +"%Y-%m-%dT%H:%M:%SZ" 2. Create staging branch from main 3. Test migration on staging first 4. Verify staging data integrity 5. Run migration on production

✅ After Migration: 1. Verify production data 2. Monitor for errors (next 1 hour) 3. Delete staging branch after 24 hours (if all good)

❌ Never: - Run untested migrations on production - Skip verification steps - Delete old branches immediately (keep 24h for rollback)

Security Best Practices¶

✅ DO: - Enable MFA on Neon account - Use environment variables for DATABASE_URL (never hardcode) - Rotate database passwords quarterly - Use pooled connection (PgBouncer) for queries - Use unpooled connection only for migrations - Restrict Neon project access to necessary team members

❌ DON'T: - Commit DATABASE_URL to git - Share credentials via email/Slack - Use same password for multiple environments - Give production access to all developers

Production Readiness Checklist¶

Pre-Beta Launch Checklist¶

Use this checklist before recruiting beta testers (Phase 5 Week 2).

Database Configuration¶

Neon retention window set to 7 days
Staging branch created from main
Storage alerts enabled in Neon Console
Database credentials documented securely (1Password/vault)
Vercel environment variables verified (DATABASE_URL, DATABASE_URL_UNPOOLED)

Backup Verification¶

Performed test restore successfully (documented results)
Verified data integrity after test restore
Confirmed automatic backup branch created (main_old_...)
Documented current project ID and branch names

Security¶

Neon account has MFA enabled
Database password rotated in last 30 days
Access restricted to authorized team members only
.env.local in .gitignore (DATABASE_URL not committed)

Documentation¶

This guide (NEON_BACKUP_RESTORE_GUIDE.md) created
Emergency contacts documented
Monthly restore test calendar reminder set
Team members trained on restore procedures

Monitoring¶

Neon email alerts enabled
Vercel database monitoring active (if using Vercel Postgres integration)
Backup status check in weekly team review

Summary¶

Key Takeaways¶

Neon PITR is Automatic: No manual backup jobs required
7 Days Recommended: Set retention to maximum for your plan (7 days for Launch)
Test Monthly: Verify restores work before you need them
Staging Branch: Create weekly refresh for safety net
Recovery is Fast: Most restores complete in 5-30 seconds

Quick Reference Commands¶

Check Retention Window:

# Via Neon Console
Settings > Storage > Instant restore

Restore to Timestamp (CLI):

neonctl branches restore main ^self@2025-11-03T14:00:00Z --preserve-under-name main_backup

Create Staging Branch (CLI):

neonctl branches create staging --parent main

Test Connection:

psql $DATABASE_URL -c "SELECT version();"

Additional Resources¶

Neon Documentation: - Point-in-Time Restore: https://neon.com/docs/introduction/point-in-time-restore - Branch Restore: https://neon.com/docs/guides/branch-restore - Backups Overview: https://neon.com/docs/manage/backups

Neon Console: - Login: https://console.neon.tech - Status Page: https://neonstatus.com

Support: - Email: support@neon.com - Console: Help chat (bottom-right icon)

Document Status: ✅ COMPLETE Next Review: Monthly (1^st day of each month) Owner: Vitor Created: November 1, 2025 Phase: Phase 5 Week 1 Day 3 - Task 4 Complete