Skip to main content

Migration Sequence Document

Overview​

This document outlines the detailed step-by-step migration order for implementing multitenancy. The sequence is designed to ensure zero downtime and data integrity.

Critical Principles​

  1. Add nullable columns first - Allows gradual migration without breaking existing code
  2. Backfill before constraints - Ensure all data has tenant_id before making it NOT NULL
  3. RLS changes after backfill - Policies can safely reference tenant_id after data is populated
  4. Application changes after RLS - Frontend must be ready to provide tenant_id when RLS enforces it
  5. Tighten constraints last - Make tenant_id NOT NULL and add tenant-scoped uniques only after everything else is working

Phase 1: Schema Foundation (Add Nullable Columns)​

Step 1.1: Create tenants table​

Migration File: *_14_create_tenants_table.sql

CREATE TABLE IF NOT EXISTS tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  created_at timestamptz DEFAULT now()
);

Rollback: DROP TABLE IF EXISTS tenants CASCADE;

Step 1.2: Add tenant_id to profiles​

Migration File: *_15_add_tenant_id_columns.sql (part 1)

ALTER TABLE profiles
  ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id);

CREATE INDEX IF NOT EXISTS idx_profiles_tenant_id ON profiles(tenant_id);

Rollback:

DROP INDEX IF EXISTS idx_profiles_tenant_id;
ALTER TABLE profiles DROP COLUMN IF EXISTS tenant_id;

Step 1.3: Add tenant_id to all business tables​

Migration File: *_15_add_tenant_id_columns.sql (part 2)

Order: Start with parent tables, then child tables to maintain referential integrity

Parent Tables (no tenant-scoped FKs):

  1. clients
  2. products_services

Child Tables (have tenant-scoped FKs): 3. inventory (references products_services) 4. inventory_history (references inventory) 5. employees (references profiles) 6. timesheets (references employees/profiles) 7. invoices (references clients) 8. invoice_line_items (references invoices) 9. quotes (references clients) 10. quote_line_items (references quotes) 11. projects (references clients) 12. tasks (references projects, employees) 13. project_employees (references projects, employees) 14. payments (references invoices) 15. client_communications (references clients) 16. activity_logs (references profiles) 17. api_keys (references profiles) 18. webhooks (references profiles) 19. webhook_logs (references webhooks)

Pattern for each table:

ALTER TABLE <table_name>
  ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id);

CREATE INDEX IF NOT EXISTS idx_<table_name>_tenant_id ON <table_name>(tenant_id);

Rollback: Drop indexes and columns in reverse order.

Phase 2: Data Backfill​

Step 2.1: Create default tenant​

Migration File: *_16_backfill_default_tenant.sql (part 1)

INSERT INTO tenants (name)
VALUES ('Autoch.at')
ON CONFLICT (name) DO NOTHING;

Rollback: DELETE FROM tenants WHERE name = 'Autoch.at'; (only if no data has been backfilled)

Step 2.2: Backfill profiles.tenant_id​

Migration File: *_16_backfill_default_tenant.sql (part 2)

UPDATE profiles
SET tenant_id = (SELECT id FROM tenants WHERE name = 'Autoch.at')
WHERE tenant_id IS NULL;

Rollback: UPDATE profiles SET tenant_id = NULL;

Step 2.3: Backfill all business table tenant_id columns​

Migration File: *_16_backfill_default_tenant.sql (part 3)

Order: Same as Step 1.3 (parent tables first, then child tables)

Pattern for each table:

UPDATE <table_name>
SET tenant_id = (SELECT id FROM tenants WHERE name = 'Autoch.at')
WHERE tenant_id IS NULL;

Rollback: UPDATE <table_name> SET tenant_id = NULL; (for each table)

Step 2.4: Run consistency checks​

Migration File: *_16_backfill_default_tenant.sql (part 4 - validation)

See 06-tenant-consistency-validation.md for validation queries.

Action if mismatches found: Fix data before proceeding to Phase 3.

Phase 3: RLS Rewrite​

Step 3.1: Create helper functions​

Migration File: *_17_create_tenant_helper_functions.sql

CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS uuid
LANGUAGE sql
STABLE
AS $$
  SELECT tenant_id FROM profiles WHERE id = auth.uid()
$$;

CREATE OR REPLACE FUNCTION current_user_is_admin()
RETURNS boolean
LANGUAGE sql
STABLE
AS $$
  SELECT EXISTS(
    SELECT 1 FROM profiles
    WHERE id = auth.uid()
      AND role = 'admin'
  )
$$;

Rollback:

DROP FUNCTION IF EXISTS current_tenant_id();
DROP FUNCTION IF EXISTS current_user_is_admin();

Step 3.2: Replace RLS policies​

Migration File: *_18_update_rls_policies_for_tenants.sql

Order:

  1. Replace read policies first (SELECT)
  2. Then replace write policies (INSERT, UPDATE, DELETE)

Tables to update (in order):

  1. profiles (critical - affects all other policies)
  2. clients
  3. products_services
  4. inventory
  5. inventory_history
  6. employees
  7. timesheets
  8. invoices
  9. invoice_line_items
  10. quotes
  11. quote_line_items
  12. projects
  13. tasks
  14. project_employees
  15. payments
  16. client_communications
  17. activity_logs
  18. api_keys
  19. webhooks
  20. webhook_logs

Pattern: See 07-rls-policy-migration-strategy.md

Rollback: Restore original policies from migration files (backup before changes)

Phase 4: Application Changes​

Note: This phase involves TypeScript/React code changes, not SQL migrations.

Step 4.1: Update TypeScript types​

  • Add tenant_id: string to Profile interface
  • Update AuthContext to expose tenantId

Step 4.2: Update service layer​

  • All INSERT operations must include tenant_id
  • All SELECT operations should filter by tenant_id (optional but recommended)

Files to update:

  • src/types/index.ts
  • src/context/AuthContext.tsx
  • src/services/*.ts (all service files)

Phase 5: Constraints + Hardening​

Step 5.1: Make tenant_id NOT NULL​

Migration File: *_19_tighten_constraints_and_uniques.sql (part 1)

Order: Same as Step 1.3

Pattern for each table:

ALTER TABLE <table_name> 
  ALTER COLUMN tenant_id SET NOT NULL;

Rollback:

ALTER TABLE <table_name> 
  ALTER COLUMN tenant_id DROP NOT NULL;

Step 5.2: Convert global uniques to tenant-scoped uniques​

Migration File: *_19_tighten_constraints_and_uniques.sql (part 2)

See 04-unique-constraint-migration.md for details.

Rollback: Drop tenant-scoped uniques and recreate global uniques (only safe before Phase 2 backfill)

Step 5.3: Add composite indexes​

Migration File: *_19_tighten_constraints_and_uniques.sql (part 3)

See 03-index-planning.md for composite index specifications.

Rollback: DROP INDEX IF EXISTS <index_name>;

Migration File Sequence​

Following existing pattern: YYYYMMDDHHMMSS_##_description.sql

  1. *_14_create_tenants_table.sql
  2. *_15_add_tenant_id_columns.sql
  3. *_16_backfill_default_tenant.sql
  4. *_17_create_tenant_helper_functions.sql
  5. *_18_update_rls_policies_for_tenants.sql
  6. *_19_tighten_constraints_and_uniques.sql

Testing Strategy​

After Phase 1​

  • Verify all tenant_id columns exist and are nullable
  • Verify all indexes are created

After Phase 2​

  • Verify all rows have tenant_id set
  • Run consistency validation queries (should return 0 mismatches)
  • Verify no NULL tenant_id values remain

After Phase 3​

  • Test tenant isolation: Create two tenants, verify users can only see their tenant's data
  • Test admin permissions within tenant scope
  • Test that USING (true) policies no longer allow cross-tenant access

After Phase 4​

  • Test all CRUD operations include tenant_id
  • Test that reads are tenant-scoped
  • Verify application still functions correctly

After Phase 5​

  • Verify all tenant_id columns are NOT NULL
  • Verify tenant-scoped uniques work (try inserting duplicate invoice_number in same tenant, should fail)
  • Verify tenant-scoped uniques allow duplicates across tenants (try same invoice_number in different tenants, should succeed)
  • Test query performance with composite indexes

Rollback Strategy​

Full Rollback (Before Phase 2)​

  • Drop all tenant_id columns and indexes
  • Drop tenants table
  • Restore original RLS policies

Partial Rollback (After Phase 2, Before Phase 3)​

  • Keep tenant_id columns but set to NULL
  • Drop tenants table (if no other tenants exist)
  • Restore original RLS policies

Partial Rollback (After Phase 3, Before Phase 5)​

  • Revert RLS policies to original
  • Keep tenant_id columns nullable
  • Application must handle NULL tenant_id gracefully

No Rollback (After Phase 5)​

  • Once constraints are tightened, rollback becomes very difficult
  • Focus on forward fixes rather than rollback

Critical Checkpoints​

  1. Before Phase 2: Backup production database
  2. After Phase 2: Verify 100% of rows have tenant_id set
  3. After Phase 3: Test tenant isolation thoroughly in staging
  4. Before Phase 5: Ensure application is fully updated and tested