Skip to main content

Migration File Naming Convention

Overview​

This document defines the naming convention and sequence for multitenant migration files, following the existing pattern in the codebase.

Existing Pattern​

Current migration files follow the pattern:

YYYYMMDDHHMMSS_##_description.sql

Where:

  • YYYYMMDDHHMMSS = Timestamp (year, month, day, hour, minute, second)
  • ## = Sequential number (01, 02, 03, etc.)
  • description = Snake_case description of the migration

Current Migration Sequence​

The last migration is:

  • 20251212161646_13_create_client_communications_table.sql

Multitenant Migration Sequence​

New migrations will continue the sequence starting from 14:

Phase 1: Schema Foundation​

  1. *_14_create_tenants_table.sql

    • Creates the tenants table
    • No dependencies

  2. *_15_add_tenant_id_columns.sql

    • Adds nullable tenant_id column to profiles
    • Adds nullable tenant_id column to all business tables
    • Creates indexes on all tenant_id columns
    • Depends on: *_14_create_tenants_table.sql

Phase 2: Data Backfill​

  1. *_16_backfill_default_tenant.sql
    • Creates default tenant (Autoch.at)
    • Backfills tenant_id for all tables
    • Includes validation queries
    • Depends on: *_15_add_tenant_id_columns.sql

Phase 3: RLS Rewrite​

  1. *_17_create_tenant_helper_functions.sql

    • Creates current_tenant_id() function
    • Creates current_user_is_admin() function
    • Depends on: *_16_backfill_default_tenant.sql

  2. *_18_update_rls_policies_for_tenants.sql

    • Replaces all USING (true) policies with tenant-scoped policies
    • Updates all admin policies to be tenant-scoped
    • Depends on: *_17_create_tenant_helper_functions.sql

Phase 5: Constraints + Hardening​

  1. *_19_tighten_constraints_and_uniques.sql
    • Makes all tenant_id columns NOT NULL
    • Converts global unique constraints to tenant-scoped uniques
    • Adds composite indexes for common query patterns
    • Depends on: *_18_update_rls_policies_for_tenants.sql and Phase 4 (application changes)

File Naming Examples​

When creating these migrations, use the current timestamp. Examples:

20250115120000_14_create_tenants_table.sql
20250115120001_15_add_tenant_id_columns.sql
20250115120002_16_backfill_default_tenant.sql
20250115120003_17_create_tenant_helper_functions.sql
20250115120004_18_update_rls_policies_for_tenants.sql
20250115120005_19_tighten_constraints_and_uniques.sql

Migration File Structure​

Each migration file should follow this structure:

/*
  # Migration Title
  
  Phase: [Phase Number]
  Dependencies: [List of previous migrations]
  
  Description of what this migration does.
  
  Rollback: [Brief description of rollback steps]
*/

-- Migration SQL here

Execution Order​

Migrations must be executed in numerical order:

  1. 14 → 15 → 16 → 17 → 18 → 19

Rollback Considerations​

Each migration file should document:

  • What changes it makes
  • How to rollback those changes
  • Dependencies that must be considered

Notes​

  • Migration 19 should only be run after Phase 4 (application changes) is complete
  • Migration 16 includes validation queries that should be run and verified before proceeding
  • Migration 18 is large and may be split into multiple files if needed (e.g., 18a, 18b, etc.)

Future Migrations​

After migration 19, future migrations will continue from 20:

  • *_20_*.sql - Future enhancements
  • *_21_*.sql - Additional features
  • etc.