Prepare the existing database

Before you start assigning clients to organizations, walk through this preparation checklist. The procedures on this page are the same shape as the v2.0.0 pre-upgrade checklist, but focused on the post-upgrade multi-tenant setup rather than the upgrade itself.

The procedures here assume the v2.0.0 migrations have been applied. Verify:

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

SELECT EXISTS (
  SELECT 1 FROM information_schema.tables
  WHERE table_schema = 'cypex'
    AND table_name = 't_organization'
) AS has_organization_table;

SELECT EXISTS (
  SELECT 1 FROM information_schema.columns
  WHERE table_schema = 'cypex'
    AND table_name = 't_ui'
    AND column_name = 'organization_id'
) AS has_org_id_on_t_ui;

Both queries should return t. If either returns f, finish the v2.0.0 upgrade first.

• Full logical backup with pg_dump:

  ```bash
  pg_dump --no-owner --no-privileges -Fc -f cypex-pre-orgs.dump cypex
  ```
  

• Filesystem snapshot of the PostgreSQL data directory, if your infrastructure supports it.

The migration maps each client’s existing data into one organization. To plan the mapping, list:

• Every application-schema table (per client, if multi-schema):

  1 2 3 4 5

  SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'cypex', 'cypex_log') AND schemaname NOT LIKE 'pg_%' ORDER BY schemaname, tablename;

• Every existing CYPEX module:

  1	SELECT id, module_name, schema_name FROM cypex.t_module;

• Every existing role that may need to be scoped to a non-default organization:

  1	SELECT rolname FROM pg_roles WHERE rolcanlogin;

There are three common patterns:

One organization per client. Existing data is split across organizations. This requires backfilling organization_id from a client identifier column on every row.

One organization per business unit (e.g. Finance, HR, Operations). The split is logical rather than per-client.

One organization per environment. Existing data is left on the Default Organization in production and copied to per-environment organizations for dev/staging.

Pick the pattern before you start the migration; the SQL differs.

Even with the v2.0.0 upgrade done, splitting existing data into multiple organizations is disruptive:

• The organization_id column is added to client application-schema tables.
• RLS is enabled on those tables.
• Backfill queries run for as long as it takes to update every row.

For most deployments, this is a multi-hour operation on large databases. Plan accordingly.

Replicate production to staging and rehearse the full procedure:

1. Apply the customized client-add-organization-id SQL template (see Assign clients to organizations).

2. Run the verification queries from Verify and roll back.

3. Confirm session / RLS behavior on staging: log in as a non-admin user mapped to one organization and verify that row visibility matches the org_id claim the new RLS policies read.

4. Time the operation. Extrapolate to production scale.

5. Note any failure modes for the production run.

Read Rollback constraints before the window, not after. Define which failure modes cause you to roll back versus fix forward, and who has the authority to call it.

• DBA sign-off
• Tech lead sign-off
• Operations sign-off

When every box is checked, proceed to Enable RLS.