Skip to main content

Unique Constraint Migration Strategy

Overview​

Current global unique constraints must be converted to tenant-scoped uniques to allow multiple tenants to use the same values (e.g., invoice numbers, quote numbers, SKUs).

Migration Strategy​

The general pattern is:

  1. Drop the existing global unique constraint/index
  2. Create a new composite unique constraint/index on (tenant_id, original_column)

Tables Requiring Unique Constraint Migration​

1. invoices.invoice_number​

Current State:

invoice_number text UNIQUE NOT NULL

Migration:

-- Drop existing unique constraint
ALTER TABLE invoices DROP CONSTRAINT IF EXISTS invoices_invoice_number_key;

-- Create tenant-scoped unique index
CREATE UNIQUE INDEX IF NOT EXISTS uniq_invoices_tenant_invoice_number 
  ON invoices(tenant_id, invoice_number);

Rationale: Multiple tenants should be able to use the same invoice numbers (e.g., both can have "INV-00001").

2. quotes.quote_number​

Current State:

quote_number text UNIQUE NOT NULL

Migration:

-- Drop existing unique constraint
ALTER TABLE quotes DROP CONSTRAINT IF EXISTS quotes_quote_number_key;

-- Create tenant-scoped unique index
CREATE UNIQUE INDEX IF NOT EXISTS uniq_quotes_tenant_quote_number 
  ON quotes(tenant_id, quote_number);

Rationale: Multiple tenants should be able to use the same quote numbers (e.g., both can have "QUO-00001").

3. products_services.sku​

Current State:

-- Partial unique index (only where sku IS NOT NULL)
CREATE UNIQUE INDEX idx_products_services_sku ON products_services(sku) WHERE sku IS NOT NULL;

Migration:

-- Drop existing partial unique index
DROP INDEX IF EXISTS idx_products_services_sku;

-- Create tenant-scoped partial unique index
CREATE UNIQUE INDEX IF NOT EXISTS uniq_products_services_tenant_sku 
  ON products_services(tenant_id, sku) WHERE sku IS NOT NULL;

Rationale: SKUs should be unique within a tenant, but different tenants can use the same SKU values.

Tables Keeping Global Uniques​

1. employees.email​

Current State:

email text UNIQUE NOT NULL

Decision: Keep global unique

Rationale:

  • Email addresses are user identity, not tenant-specific business data
  • A user's email should be globally unique across the system
  • The tenant_id column will still be added for tenant isolation, but email uniqueness remains global

No migration needed - keep existing unique constraint.

2. profiles.email​

Current State:

email text UNIQUE NOT NULL

Decision: Keep global unique

Rationale:

  • Email is the authentication identity
  • Must remain globally unique for auth system integrity
  • tenant_id will be added for tenant isolation, but email uniqueness is global

No migration needed - keep existing unique constraint.

3. api_keys.key_hash​

Current State:

key_hash text NOT NULL UNIQUE

Decision: Keep global unique

Rationale:

  • API key hashes must be globally unique for security
  • Cannot allow duplicate key hashes across tenants
  • tenant_id will be added for tenant isolation, but key_hash uniqueness is global

No migration needed - keep existing unique constraint.

4. inventory.product_id​

Current State:

product_id uuid NOT NULL UNIQUE REFERENCES products_services(id)

Decision: Keep as-is (1:1 relationship)

Rationale:

  • This is a 1:1 relationship constraint, not a business uniqueness constraint
  • One inventory record per product is correct
  • tenant_id will be added, but the unique constraint on product_id remains

No migration needed - keep existing unique constraint.

5. project_employees​

Current State:

UNIQUE(project_id, employee_id)

Decision: Keep as composite unique

Rationale:

  • This ensures an employee can only be assigned once per project
  • The composite unique is correct as-is
  • tenant_id will be added for tenant isolation, but the composite unique remains

No migration needed - keep existing unique constraint.

Migration Execution Order​

  1. Phase 1: Add nullable tenant_id columns (no constraint changes yet)
  2. Phase 2: Backfill all tenant_id values
  3. Phase 5:
    • Make tenant_id NOT NULL
    • Drop global unique constraints
    • Create tenant-scoped unique indexes

Validation Queries​

After migration, verify tenant-scoped uniqueness:

-- Verify no duplicate invoice numbers within tenants
SELECT tenant_id, invoice_number, COUNT(*) 
FROM invoices 
GROUP BY tenant_id, invoice_number 
HAVING COUNT(*) > 1;

-- Verify no duplicate quote numbers within tenants
SELECT tenant_id, quote_number, COUNT(*) 
FROM quotes 
GROUP BY tenant_id, quote_number 
HAVING COUNT(*) > 1;

-- Verify no duplicate SKUs within tenants (where SKU is not null)
SELECT tenant_id, sku, COUNT(*) 
FROM products_services 
WHERE sku IS NOT NULL
GROUP BY tenant_id, sku 
HAVING COUNT(*) > 1;

All queries should return 0 rows.

Rollback Considerations​

If rollback is needed:

  1. Drop tenant-scoped unique indexes
  2. Recreate global unique constraints
  3. Note: This will fail if multiple tenants have duplicate values, so rollback should only occur before Phase 2 backfill is complete