Why Simply Accepting Backup Success Is Not Enough
Many SaaS founders and product owners assume a backup job completing without error means the system can be recovered fully. A successful job status reports completion of that job's configured scope. It does not by itself establish that its output is complete, readable, decryptable or compatible with the application, or that a customer can finish a workflow. Restored data may miss linked records, file attachments, or permissions, causing workflows to fail.
Restore in Isolation: Minimising Risk While Testing
The first step is to identify a separate database and object-storage destination, verify its host, project and account IDs, and restrict access to the recovery team. Block outbound live payments, email, SMS, webhooks and scheduled workers; use provider sandboxes or controlled stubs for workflow testing. Merely copying production configuration into staging can send real notifications or charge a customer. Record the isolation checks before allowing the restore job to write.
Match the application revision, database version, extensions, schema migrations and relevant configuration to the backup. Keep an inventory of roles, authentication dependencies, encryption keys and object backups held outside the database. Record vault references and key availability without placing secret values in the test report. Restored customer data remains sensitive and needs restricted access and a cleanup plan.
Validate Linked Records and Referential Integrity
SaaS applications often have complex relational data. After restore, verify that linked records across tables are intact. For example, orders should link to valid customers, and comments to their parent posts. Referential integrity checks in PostgreSQL help but do not guarantee workflow correctness. Manual or automated queries should confirm expected relationships.
Confirm File Attachments and External Storage
Many SaaS apps store files externally (e.g., user uploads) linked to database records. Database references are not a substitute for attachment bytes. Record the object-storage backup or versioned object manifest that supplies each referenced file, including its key, size, checksum where available and backup time. Compare the database and object recovery cutoffs: a database row restored to a later time than its attachment backup can point to a file that was never captured. Test file access in the isolated environment, ensuring signed URLs or permissions work correctly. For example, Supabase storage policies and signed URLs require validation to confirm users can retrieve files post-restore.
Execute the Key User Journey End-to-End
Identify the critical user workflow that defines your SaaS value , such as creating an invoice, processing payment, and generating a report. After restore, perform this journey against both existing restored records and newly created test records. A fresh invoice can succeed while an older customer's attachment or identity is missing. Use a gateway sandbox and controlled notification destination, and record which external behaviours were simulated. Those results establish sandbox behaviour, not live payment or email delivery. This practical test reveals hidden restore issues.
Proposed Workflow-Based Restore Test Template
| Step | Description | Expected Result | Status |
|---|---|---|---|
| 1 | Restore database dump into isolated environment | Database restores without errors | |
| 2 | Verify user roles and permissions exist | Roles match production setup | |
| 3 | Validate linked records integrity via queries | All foreign keys valid | |
| 4 | Restore and verify file attachments in storage | Files accessible with correct permissions | |
| 5 | Perform key user workflow (e.g., create and process order) | Workflow completes successfully | |
| 6 | Check logs and error reports for anomalies | No unexpected errors |
Use this checklist to document tests and outcomes, enabling repeatable verification.
Worked Example: SaaS Invoice Workflow Restore Test
Suppose your SaaS handles invoicing. After restoring the backup:
- Confirm customers, invoices, and payment tables are restored with correct links.
- Verify invoice PDF files are accessible via signed URLs.
- Log in as a test user and create a new invoice.
- Process a sandbox payment through the approved test gateway; block production credentials and outbound live charging.
- Generate and download the invoice report. If the checks pass, record that the identified restore supported this specific workflow in the stated test environment. Keep untested integrations, historical data ranges and live delivery requirements as separate unresolved items.
Edge Cases and Considerations
- Partial restores: Some backups may exclude large file storage; test if workflows tolerate missing files.
- Permissions drift: Roles or policies might differ between environments; ensure they are synced.
- Time-limited signed URLs: Confirm expiry and access behave as expected after restore.
- External integrations: Restore tests should consider downstream systems if critical.
Tools and Resources
Use PostgreSQL's row-level security policies to mimic production access controls during restore verification. Supabase storage access control guides help validate file permissions. For SaaS development best practices, see SaaS development. For understanding user journeys, consult the User Journey glossary. The Discovery Phase checklist supports planning restore tests. For platform choices, review CMS vs Custom Development.
Recommendation
Do not treat backup job success as proof of recoverability. Implement a workflow-based restore test in an isolated environment routinely. This provides evidence about the tested recovery scope and exposes missing dependencies before an incident. A planned test is not a completed recovery, and one passing journey does not establish every tenant or integration.
If your business needs help designing or automating these restore tests, or integrating them into your SaaS development lifecycle, get in touch with our expert team at SaaS development.
Automating a bounded restore acceptance test
Automation is useful when it refuses the wrong destination and retains evidence of failures. PostgreSQL describes SQL dumps, filesystem backups and continuous archiving as different recovery approaches. Identify the actual format and procedure before selecting a restore tool; a command copied from an example may not suit the backup. PostgreSQL backup approaches
Use a restore-job contract rather than a generic destructive command. An operator should be able to see what will be overwritten, which artifact will be used and which outbound systems are disabled. Keep the report out of public CI artifacts and restrict runner credentials to the isolated destination.
| Checkpoint | Record before proceeding | Expected evidence | Stop condition |
|---|---|---|---|
| Destination | Host, project ID, database name, storage account and permitted operator | Explicit match to the isolated test inventory | Production or unknown destination |
| Backup | Artifact ID, checksum, timestamp, format and retention location | Readable artifact matched to the recovery plan | Missing, corrupt or ambiguous artifact |
| Compatibility | App commit, database version, extensions and migration level | Required components available | Unsupported format or missing component |
| Dependencies | Roles, identities, object manifest, keys and provider sandbox configuration | Required dependencies identified and recoverable | Required file, role or key unavailable |
| Isolation | Disabled live notifications, payments, webhooks and workers | Controlled destinations and sandbox credentials | A live side effect remains possible |
| Restore | Command output, errors, start and finish times | Procedure finishes with failures retained | An error is ignored or hidden |
| Acceptance | Existing-record journey, new-record journey and negative access tests | Recorded observed outcomes | Any agreed requirement fails |
Do not silently create missing roles or seed replacement records and then mark the original backup as complete. Record what was absent, how it was repaired and whether the repair is a documented recovery dependency. Repeat the workflow after the repair and preserve both results. Fixtures can exercise new behaviour, but cannot prove that an older customer record survived.
Measure elapsed recovery time from the agreed start point to accepted service readiness. Record the latest recoverable data timestamp and excluded data so the owner can compare observed recovery with their chosen objectives. A fast database import does not include file restoration, authentication repair, provider checks or owner acceptance, and does not establish compliance with a contractual recovery target.
Hypothetical Case Study: Restoring and Testing a SaaS Project Management App
Scenario
Your SaaS product manages projects, tasks, and file attachments. The critical workflow is creating a project, adding tasks, uploading task attachments, and generating a status report.
Restore Test Plan
| Step | Action | Expected Result | Recorded Field | Recovery Step if Failed |
|---|---|---|---|---|
| 1 | Restore database dump into isolated environment | Database restores without errors | Restore log | Re-run restore, check dump integrity |
| 2 | Verify roles and authenticated membership | Required roles, grants and memberships match the documented configuration | Role and policy comparison | Record any missing dependency; restore through the approved procedure and retest |
| 3 | Check referential integrity: tasks linked to projects | All tasks have valid project_id | Foreign key violation count | Fix data inconsistencies or backup process |
| 4 | Verify file attachments in Supabase storage | Files accessible via signed URLs | File access success/failure count | Restore missing files from secondary backup |
| 5 | Open an existing project, read its attachment, then create a test project, task, attachment and report | Both restored and new-record journeys meet the agreed requirements | Record IDs, responses and redacted workflow evidence | Diagnose missing state, repair through the recovery procedure and repeat acceptance |
| 6 | Review logs for errors | No unexpected errors | Log error count | Investigate and resolve errors |
Acceptance Criteria
- No SQL errors during restore
- All user roles present
- No broken foreign keys
- All files accessible with correct permissions
- Key user journey completes successfully
- No errors in logs
Failure Tests
- Attempt to create a task without a project: should be rejected to confirm referential integrity
- Generate new signed links under restored permissions, test unauthorised requests and document expiry/cache behaviour instead of treating old signed links as recovery dependencies
- Run workflow with a user lacking permissions: should fail appropriately
Recovery Steps
- Record missing roles or permissions, recover them through the documented dependency procedure, and repeat access and workflow tests without erasing the original failure
- If files are missing, restore from file storage backups
- If workflows fail, analyze error logs and fix data or code issues
This is a hypothetical test plan, not a claim that a restore has run or passed. Fill the observed-result fields during execution and state the workflow, tenants and data period actually covered.
For further guidance on integrating these tests into your SaaS development lifecycle, review our SaaS development resources.
Documentation boundaries for this decision
A database recovery procedure needs explicit checks for ownership, grants and row policies. PostgreSQL notes that table owners normally bypass RLS and superusers or roles with bypass privileges can do so. A test run solely as an administrator can therefore miss customer access defects. Use representative application identities and verify both permitted and denied operations. PostgreSQL row security
Supabase Storage uses policies on storage objects for access control, while a service key can bypass those controls. Check the restored storage rules using ordinary authorized users, including a user from another tenant and a user whose membership was removed. Confirm actual bytes can be downloaded, rather than accepting a metadata row or an administrator's successful read as sufficient. Supabase Storage access control
Supabase supports authenticated downloads and signed download URLs. Issue fresh links from the restored environment under the intended permissions and keep the signing configuration out of public logs. Recovery should not depend on replaying previously issued signed tokens. Supabase authenticated and signed downloads
For Supabase Smart CDN, a cached response using a signed token can remain available after token expiry until its cache lifetime ends. Expiry and cache lifetime are separate controls; deletion invalidation also has propagation limits. Test the configured behaviour and record it accurately. Neither a URL expiry nor restored metadata revokes files someone already downloaded. Supabase Smart CDN caching
Frequently asked questions
How often should we perform restore verification tests?
Choose a cadence based on data value, recovery objectives and the rate of system changes. A quarterly exercise is an illustrative starting point, not a universal requirement. Retest after material changes to schema, storage, permissions, encryption or the recovery procedure.
Can we automate workflow-based restore tests?
Yes, automated test scripts can simulate user journeys and verify data integrity post-restore, reducing manual effort and improving reliability.
What if file storage is on a separate system from the database?
You must back up and restore file storage separately and validate file availability and permissions during restore tests.
How do signed URLs affect restore testing?
Signed URLs often have expiry times and depend on keys. Generate new URLs from the restored environment and verify authorization and the configured expiry/cache behaviour. Include key availability and rotation in the recovery plan; an old signed link is not evidence that the restored user can obtain a new permitted link.