Verify and roll back

After running the client-assignment migration, walk through the verification queries on this page. If verification fails, follow the rollback procedure.

For every migrated table:

1
2
3
4

SELECT count(*) AS total,
       count(*) FILTER (WHERE organization_id IS NOT NULL) AS with_org,
       count(*) FILTER (WHERE organization_id IS NULL) AS without_org
FROM <schema>.<table>;

• without_org should be 0. This applies after you have backfilled the client tables with the template on this page. Straight after the v2.0.0 upgrade itself, CYPEX’s own tables legitimately still hold NULL rows — those stay visible to the Default Organization by design.
• total should equal the pre-migration row count.

For each organization:

1
2
3
4

SELECT organization_id, count(*)
FROM <schema>.<table>
GROUP BY organization_id
ORDER BY count DESC;

Compare against the expected distribution. If a known client should own all rows in a table and the distribution shows multiple organizations, the backfill assigned rows to the wrong organization.

1
2
3

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM <schema>.<table>
WHERE <some-column> = <some-value>;

EXPLAIN does not print policy names — RLS shows up as an added predicate. Look for the organization_id comparison served as an Index Cond on idx_<schema>_<table>_organization_id rather than a bare Filter; a plain filter means the predicate is not being pushed down and queries will be slow on large tables. Confirm the policy itself separately:

1

SELECT policyname FROM pg_policies WHERE tablename = '<table>';

Client tables created by the template use <schema>_<table>_user_access. CYPEX’s own tables use shorter names without the schema prefix or the t_ prefix — for example file_user_access on cypex.t_file.

Confirm the claims flow that the new RLS policies rely on. Log into the CYPEX GUI as a non-admin user mapped to one organization:

• Confirm previously-visible data for that organization is still visible.
• Confirm data belonging to another organization is not visible (the UI should hide it; direct API calls should return 403).
• Confirm a write attempt to a row in the user’s own organization succeeds.

If visibility or writes do not match organization membership, check the claims the session is presenting on request.jwt.claims — org_id and organization_ids are what the policies read.

1
2
3
4
5

SELECT created_at, user_id, action, entity_type, entity_name,
       context ->> 'reason' AS reason
FROM cypex_log.t_permission_audit_log
ORDER BY created_at DESC
LIMIT 50;

You should see entries from the smoke test. There is no boolean allowed column: a denial is recorded through the action value and a reason inside the context payload, such as org_out_of_scope or org_admin_cannot_access_internal_schemas. Compare before_state and after_state to see what a permission change actually did.

If verification fails, you have two options.

Restore the database from the pre-migration pg_dump and revert the backend binary. This is the cleanest rollback path.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Stop the backend
docker compose stop backend

# Drop and recreate the database
dropdb cypex
createdb cypex

# Restore from backup
pg_restore --no-owner --no-privileges -d cypex cypex-pre-orgs.dump

# Restart the backend on the previous version
docker compose up -d backend

This loses any data written during the migration window. The window should be empty (CYPEX in maintenance mode), so this is normally acceptable.

If you cannot restore from backup, attempt a partial rollback:

1. Disable RLS on every table that had it enabled:

   1	ALTER TABLE <schema>.<table> DISABLE ROW LEVEL SECURITY;

2. Drop the policies:

   1	DROP POLICY <schema>_<table>_user_access ON <schema>.<table>;

3. Drop the organization_id columns:

   1	ALTER TABLE <schema>.<table> DROP COLUMN organization_id;

4. Drop the indexes:

   1	DROP INDEX idx_<schema>_<table>_organization_id;

5. Restart the backend on the v2.0.0 binary, but in maintenance mode (no user traffic). The v2.0.0 backend reads organization_id, so it will fail until you also revert the backend.

Partial rollback is fragile. Use it only when restoring from backup is not possible (for example, because other systems have written to the database during the window).

When verification passes:

• Remove the maintenance banner.
• Notify users that the platform is back online.
• File a post-incident review if anything unexpected happened during the window.

When verification fails:

• Keep the maintenance banner.
• Notify the on-call rotation.
• Decide between Option A (restore) and Option B (partial rollback).
• File a post-incident review.

• Assign clients to organizations
• Enable RLS
• Prepare the existing database
• v2.0.0 rollback constraints