Assign clients to organizations

This page explains how to use the client-add-organization-id SQL template to backfill the organization_id column on existing application-schema tables and assign clients to organizations.

Warning

Rehearse this on a staging copy before running it in production. This migration has failed in the field: the failure modes and the safeguards that prevent them are listed under Failure modes below.

Download the template:

client-add-organization-id-template.sql

It is client responsibility — CYPEX does not run it automatically. Each deployment customizes the template for its own schemas and tables.

The template:

• Adds organization_id (BIGINT) columns to client application-schema tables.
• Backfills existing rows to a configurable default organization.
• Replaces the migration literal DEFAULT with cypex.current_organization_id() so later inserts follow the session org.
• Enables Row-Level Security (ENABLE + FORCE).
• Attaches a baseline policy with matching USING and WITH CHECK.
• Creates a per-table index on organization_id and runs ANALYZE.
• Fails loud on the first table error (set continue_on_error := true only for inventory runs).

1
2
3

curl -LO https://cypex.app/downloads/client-add-organization-id-template.sql
mv client-add-organization-id-template.sql \
   migrations/YYYYMMDDHHMMSS-add-organization-id.sql

Use a timestamped name in your CYPEX migrations directory that fits your migration runner’s naming convention. If you already have the file from the documentation site, copy it into migrations/ instead of curling.

The template is a DO $$ … $$ block. Edit these sections before running:

Default organization — resolved at the top of the block:

1
2
3
4
5
6

SELECT id INTO default_org_id
FROM cypex.t_organization
WHERE organization_domain = 'default'
  AND is_active = true
ORDER BY id ASC
LIMIT 1;

Change the WHERE clause if existing rows should land on a different organization.

Schema filter — which schemas the loop visits:

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

FOR schema_rec IN
  SELECT nspname
  FROM pg_namespace
  WHERE nspname NOT LIKE 'pg_%'
    AND nspname NOT LIKE 'cypex%'
    AND nspname NOT LIKE 'sso_gateway%'
    AND nspname != 'information_schema'
    AND nspname != 'public'
    -- ADD YOUR SCHEMA FILTERS HERE:
    -- AND nspname IN ('your_schema_1', 'your_schema_2')
  ORDER BY nspname
LOOP

Table filter — which tables inside each schema are migrated:

1
2
3
4

AND tablename NOT LIKE '_%'
AND tablename NOT LIKE 'pg_%'
-- ADD YOUR TABLE FILTERS HERE:
-- AND tablename NOT IN ('excluded_table_1', 'excluded_table_2')

Edit them to match your deployment. Do not run the unmodified template — it is a starting point, not a one-size-fits-all script.

Apply the customized template to a non-production replica and verify:

• Every expected table now has an organization_id column.
• The RLS policy exists and matches the schema/table names.
• Row counts match before and after the migration.
• The backend integration tests pass.

Once staging has passed:

• Take a fresh backup.
• Schedule the maintenance window.
• Run the migration inside a transaction.
• Verify row counts.
• Cut traffic only after verification.

There is no separate dry-run flag: rehearse on a staging replica, then run the customized script in production.

The automated path in the template applies changes per table in this order:

1. Add the organization_id column (NOT NULL DEFAULT <org>), or finish a partial prior run (backfill NULLs, SET NOT NULL, add FK if missing).
2. Replace the literal backfill DEFAULT with cypex.current_organization_id().
3. Enable RLS (ENABLE + FORCE).
4. Drop any existing policy (idempotency), then create the org policy with matching USING and WITH CHECK.
5. Create the index on organization_id and ANALYZE the table.

Because the column is added with a DEFAULT, existing rows are tagged as the column is created — there is no separate backfill UPDATE in Option 1 for brand-new columns. Re-runs that find an existing nullable column do run an explicit UPDATE for NULL rows.

If you use Option 2 (manual / per-table) or write a custom backfill, prefer this safer order so RLS does not hide rows mid-migration:

1. Add the organization_id column (nullable).
2. Backfill the column for existing rows (batched if large).
3. Add the index (CREATE INDEX CONCURRENTLY outside a transaction).
4. SET NOT NULL, add the FK, set DEFAULT cypex.current_organization_id().
5. Enable RLS and attach the policy (USING + WITH CHECK).
6. Verify row counts.

The reason: if you enable RLS before the backfill, the backfill query itself may be subject to the policy and could miss rows.

If you need to move rows between organizations after the initial assignment, see Enable RLS for the SET LOCAL pattern.

These are the failure modes seen in real runs:

• Cross-schema policy names. The template names policies <schema>_<table>_user_access so the same table name in two schemas does not collide in ops views.
• Long-running migration. A multi-million-row table can exceed statement timeouts when adding a NOT NULL DEFAULT column. Increase the timeout for the migration session and prefer Option 2 with batched updates (WHERE id BETWEEN ... AND ...) plus CREATE INDEX CONCURRENTLY.
• Literal DEFAULT left behind. If you customize the script and skip the SET DEFAULT cypex.current_organization_id() step, later inserts that omit organization_id silently land in the migration org.

After running the template:

1

SELECT count(*) FROM <schema>.<table> WHERE organization_id IS NULL;

Should be 0 for every migrated table. If any rows are still NULL, either the migration did not complete or the template’s filter excluded those rows.

• Prepare the existing database
• Enable RLS
• Verify and roll back
• Failure modes — read before production rollout.