> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Migration Rollback Strategy

> This document provides guidelines and procedures for safely reverting database migrations, with a focus on RLS (Row Level Security) policy changes. Following t…

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-11\
> **Status:** Active

## Overview

This document provides guidelines and procedures for safely reverting database migrations, with a focus on RLS (Row Level Security) policy changes. Following these procedures ensures minimal downtime and prevents data exposure during rollback operations.

***

## Table of Contents

1. [Pre-Migration Checklist](#pre-migration-checklist)
2. [RLS Policy Rollback Procedures](#rls-policy-rollback-procedures)
3. [Emergency Rollback Steps](#emergency-rollback-steps)
4. [Policy State Capture Scripts](#policy-state-capture-scripts)
5. [Testing Rollback](#testing-rollback)
6. [Rollback Templates](#rollback-templates)

***

## Pre-Migration Checklist

Before applying any migration that modifies RLS policies:

### 1. Document Current State

```sql theme={null}
-- Capture all existing policies for affected tables
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual AS using_expression,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename IN ('table1', 'table2') -- List affected tables
ORDER BY tablename, policyname;
```

### 2. Create Rollback SQL

For every `CREATE POLICY`, prepare the inverse `DROP POLICY`:

```sql theme={null}
-- Migration
CREATE POLICY "new_policy" ON table_name FOR SELECT USING (...);

-- Rollback
DROP POLICY IF EXISTS "new_policy" ON table_name;
```

### 3. Test in Staging

* [ ] Apply migration to staging environment
* [ ] Run RLS test suite
* [ ] Verify no recursion risks
* [ ] Confirm multi-tenant isolation
* [ ] Test all CRUD operations

### 4. Backup Policy Definitions

Save the output of the state capture script to a file before migration:

```bash theme={null}
# Save current state
psql -c "SELECT * FROM get_all_rls_policies()" > pre_migration_policies.sql
```

***

## RLS Policy Rollback Procedures

### Principle: Never Modify Policies In-Place

Always use the pattern:

1. `DROP POLICY IF EXISTS`
2. `CREATE POLICY` with new definition

This ensures atomic changes and clean rollback.

### Standard Rollback Pattern

```sql theme={null}
-- Step 1: Drop the problematic policy
DROP POLICY IF EXISTS "problematic_policy" ON affected_table;

-- Step 2: Recreate the original policy
CREATE POLICY "original_policy" ON affected_table
  FOR SELECT
  USING (original_using_expression);
```

### Rolling Back Multiple Policies

Wrap in a transaction for atomicity:

```sql theme={null}
BEGIN;

-- Drop new policies
DROP POLICY IF EXISTS "new_policy_1" ON table1;
DROP POLICY IF EXISTS "new_policy_2" ON table2;

-- Recreate original policies
CREATE POLICY "original_policy_1" ON table1 FOR SELECT USING (...);
CREATE POLICY "original_policy_2" ON table2 FOR SELECT USING (...);

COMMIT;
```

***

## Emergency Rollback Steps

### Severity Levels

| Level        | Symptoms                                         | Response Time           |
| ------------ | ------------------------------------------------ | ----------------------- |
| **Critical** | Data exposure across tenants, auth bypass        | Immediate (\< 5 min)    |
| **High**     | Users locked out, broken CRUD operations         | \< 15 min               |
| **Medium**   | Performance degradation, partial feature failure | \< 1 hour               |
| **Low**      | Minor inconsistencies, non-critical features     | Next maintenance window |

### Critical Emergency Procedure

1. **Identify the Problem**
   ```sql theme={null}
   -- Check for recursion risks
   SELECT * FROM get_rls_recursion_risks();

   -- Check for cross-tenant data leaks
   SELECT * FROM verify_tenant_isolation();
   ```

2. **Disable Problematic Policies Immediately**
   ```sql theme={null}
   -- Temporarily make table admin-only
   DROP POLICY IF EXISTS "problematic_policy" ON affected_table;

   CREATE POLICY "emergency_lockdown" ON affected_table
     FOR ALL
     USING (pf_is_platform_admin(auth.uid()));
   ```

3. **Notify Stakeholders**
   * Alert development team
   * Document incident timeline
   * Prepare communication for affected users

4. **Apply Proper Rollback**
   * Execute pre-prepared rollback SQL
   * Verify with test suite
   * Monitor for 30 minutes

5. **Post-Incident Review**
   * Document root cause
   * Update rollback procedures if needed
   * Add regression tests

***

## Policy State Capture Scripts

### Capture All Policies

```sql theme={null}
-- Create a function to capture current RLS state
CREATE OR REPLACE FUNCTION get_all_rls_policies()
RETURNS TABLE (
  schema_name text,
  table_name text,
  policy_name text,
  policy_cmd text,
  policy_roles text[],
  using_expr text,
  with_check_expr text,
  create_statement text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN QUERY
  SELECT 
    p.schemaname::text,
    p.tablename::text,
    p.policyname::text,
    p.cmd::text,
    p.roles::text[],
    p.qual::text,
    p.with_check::text,
    format(
      'CREATE POLICY %I ON %I.%I FOR %s TO %s USING (%s)%s;',
      p.policyname,
      p.schemaname,
      p.tablename,
      p.cmd,
      array_to_string(p.roles, ', '),
      COALESCE(p.qual::text, 'true'),
      CASE WHEN p.with_check IS NOT NULL 
           THEN format(' WITH CHECK (%s)', p.with_check::text)
           ELSE '' END
    )
  FROM pg_policies p
  WHERE p.schemaname = 'public'
  ORDER BY p.tablename, p.policyname;
END;
$$;
```

### Capture Policies for Specific Tables

```sql theme={null}
-- Get policies for specific tables as rollback SQL
SELECT format(
  E'-- Rollback for %I\nDROP POLICY IF EXISTS %I ON %I;\nCREATE POLICY %I ON %I\n  FOR %s\n  USING (%s)%s;\n',
  tablename,
  policyname,
  tablename,
  policyname,
  tablename,
  cmd,
  COALESCE(qual::text, 'true'),
  CASE WHEN with_check IS NOT NULL 
       THEN format(E'\n  WITH CHECK (%s)', with_check::text)
       ELSE '' END
) AS rollback_sql
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename = 'your_table_name'
ORDER BY policyname;
```

### Generate DROP Statements for New Policies

```sql theme={null}
-- Generate DROP statements for policies created after a specific date
-- (Requires tracking policy creation dates separately)
SELECT format('DROP POLICY IF EXISTS %I ON %I;', policyname, tablename)
FROM pg_policies
WHERE schemaname = 'public'
  AND policyname LIKE 'new_prefix_%';
```

***

## Testing Rollback

### Verification Queries

After rollback, run these checks:

```sql theme={null}
-- 1. Verify no recursion risks
SELECT * FROM get_rls_recursion_risks();
-- Expected: 0 rows

-- 2. Check policy counts per table
SELECT tablename, COUNT(*) as policy_count
FROM pg_policies 
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY policy_count;

-- 3. Verify UPDATE policies have WITH CHECK
SELECT tablename, policyname
FROM pg_policies
WHERE schemaname = 'public'
  AND cmd = 'UPDATE'
  AND with_check IS NULL;
-- Expected: 0 rows (or known exceptions)

-- 4. Test multi-tenant isolation
-- Run as org1_user, should not see org2 data
SELECT COUNT(*) FROM your_table WHERE organization_id = 'org2_id';
-- Expected: 0
```

### RLS Test Suite

Run the full RLS test suite after rollback:

```bash theme={null}
# Run RLS-specific tests
npm run test -- --grep "RLS"

# Run multi-tenant isolation tests
npm run test -- tests/rls/
```

***

## Rollback Templates

### Template A: Single Policy Rollback

```sql theme={null}
-- Rollback: [Policy Name] on [Table Name]
-- Date: YYYY-MM-DD
-- Reason: [Brief explanation]

BEGIN;

-- Remove problematic policy
DROP POLICY IF EXISTS "new_policy_name" ON table_name;

-- Restore original policy
CREATE POLICY "original_policy_name" ON table_name
  FOR [SELECT|INSERT|UPDATE|DELETE|ALL]
  USING (original_using_expression)
  WITH CHECK (original_with_check_expression);

COMMIT;

-- Verification
SELECT * FROM pg_policies WHERE tablename = 'table_name';
```

### Template B: Multi-Table Rollback

```sql theme={null}
-- Rollback: Migration YYYYMMDD_description
-- Date: YYYY-MM-DD
-- Affected Tables: table1, table2, table3
-- Reason: [Brief explanation]

BEGIN;

-- ============================================
-- Table 1: table1
-- ============================================
DROP POLICY IF EXISTS "new_table1_select" ON table1;
DROP POLICY IF EXISTS "new_table1_insert" ON table1;
DROP POLICY IF EXISTS "new_table1_update" ON table1;
DROP POLICY IF EXISTS "new_table1_delete" ON table1;

CREATE POLICY "old_table1_select" ON table1 FOR SELECT USING (...);
CREATE POLICY "old_table1_insert" ON table1 FOR INSERT WITH CHECK (...);
CREATE POLICY "old_table1_update" ON table1 FOR UPDATE USING (...) WITH CHECK (...);
CREATE POLICY "old_table1_delete" ON table1 FOR DELETE USING (...);

-- ============================================
-- Table 2: table2
-- ============================================
-- ... repeat pattern ...

COMMIT;

-- Verification
SELECT tablename, COUNT(*) 
FROM pg_policies 
WHERE tablename IN ('table1', 'table2', 'table3')
GROUP BY tablename;
```

### Template C: Helper Function Rollback

```sql theme={null}
-- Rollback: Helper Function [function_name]
-- Date: YYYY-MM-DD
-- Reason: [Brief explanation]

-- Step 1: Drop dependent policies first
DROP POLICY IF EXISTS "policy_using_function" ON affected_table;

-- Step 2: Drop new function
DROP FUNCTION IF EXISTS new_function_name(args);

-- Step 3: Recreate original function
CREATE OR REPLACE FUNCTION original_function_name(args)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  -- Original function body
$$;

-- Step 4: Recreate policies using original function
CREATE POLICY "policy_using_function" ON affected_table
  FOR SELECT
  USING (original_function_name(auth.uid()));
```

***

## Best Practices Summary

1. **Always prepare rollback SQL before migration**
2. **Test in staging first**
3. **Use transactions for atomic rollbacks**
4. **Never modify policies in-place - always DROP + CREATE**
5. **Document every rollback with date and reason**
6. **Run verification queries after every rollback**
7. **Keep rollback SQL files version-controlled**
8. **Set up monitoring alerts for RLS failures**

***

## Related Documents

* [RLS Patterns Reference](/database/RLS_PATTERNS)
* [RLS Policy Audit](https://github.com/Encore-OS/encoreos/blob/development/docs/database/RLS_POLICY_AUDIT.md)
* [Database Development Guide](/development/DATABASE_DEVELOPMENT_GUIDE)

***

## Changelog

| Version | Date       | Changes                   |
| ------- | ---------- | ------------------------- |
| 1.0.0   | 2026-01-11 | Initial document creation |