Skip to main content

Index Planning for Multitenant Migration

Overview​

Every table that receives a tenant_id column requires an index on that column for query performance. Additionally, composite indexes on common query patterns will improve tenant-scoped queries.

Required tenant_id Indexes​

All tables receiving tenant_id must have a basic index on that column:

  1. profiles → idx_profiles_tenant_id
  2. clients → idx_clients_tenant_id
  3. products_services → idx_products_services_tenant_id
  4. inventory → idx_inventory_tenant_id
  5. inventory_history → idx_inventory_history_tenant_id
  6. employees → idx_employees_tenant_id
    • Note: employees.id references profiles.id, so employees.tenant_id should match profiles.tenant_id
  7. timesheets → idx_timesheets_tenant_id
  8. invoices → idx_invoices_tenant_id
  9. invoice_line_items → idx_invoice_line_items_tenant_id
  10. quotes → idx_quotes_tenant_id
  11. quote_line_items → idx_quote_line_items_tenant_id
  12. projects → idx_projects_tenant_id
  13. tasks → idx_tasks_tenant_id
  14. project_employees → idx_project_employees_tenant_id
  15. payments → idx_payments_tenant_id
  16. client_communications → idx_client_communications_tenant_id
  17. activity_logs → idx_activity_logs_tenant_id
  18. api_keys → idx_api_keys_tenant_id
  19. webhooks → idx_webhooks_tenant_id
  20. webhook_logs → idx_webhook_logs_tenant_id

Composite Index Strategy​

Composite indexes on (tenant_id, ...) improve performance for common tenant-scoped queries:

Clients Table​

CREATE INDEX idx_clients_tenant_company_name ON clients(tenant_id, company_name);

Use Case: Tenant-scoped client searches and alphabetical listings

Invoices Table​

CREATE INDEX idx_invoices_tenant_status ON invoices(tenant_id, status);
CREATE INDEX idx_invoices_tenant_client ON invoices(tenant_id, client_id);

Use Cases:

  • Filtering invoices by status within a tenant
  • Listing all invoices for a specific client within a tenant

Quotes Table​

CREATE INDEX idx_quotes_tenant_status ON quotes(tenant_id, status);
CREATE INDEX idx_quotes_tenant_client ON quotes(tenant_id, client_id);

Use Cases:

  • Filtering quotes by status within a tenant
  • Listing all quotes for a specific client within a tenant

Products/Services Table​

CREATE INDEX idx_products_services_tenant_type ON products_services(tenant_id, type);

Use Case: Filtering products vs services within a tenant

Projects Table​

CREATE INDEX idx_projects_tenant_client ON projects(tenant_id, client_id);
CREATE INDEX idx_projects_tenant_status ON projects(tenant_id, status);

Use Cases:

  • Listing all projects for a specific client within a tenant
  • Filtering projects by status within a tenant

Tasks Table​

CREATE INDEX idx_tasks_tenant_assigned ON tasks(tenant_id, assigned_to);
CREATE INDEX idx_tasks_tenant_project ON tasks(tenant_id, project_id) WHERE project_id IS NOT NULL;

Use Cases:

  • Listing tasks assigned to an employee within a tenant
  • Listing tasks for a project within a tenant

Timesheets Table​

CREATE INDEX idx_timesheets_tenant_employee_date ON timesheets(tenant_id, employee_id, date);

Use Case: Querying timesheets for a specific employee within a date range, scoped to tenant

Payments Table​

CREATE INDEX idx_payments_tenant_invoice ON payments(tenant_id, invoice_id);
CREATE INDEX idx_payments_tenant_date ON payments(tenant_id, payment_date);

Use Cases:

  • Listing payments for a specific invoice within a tenant
  • Filtering payments by date within a tenant

Client Communications Table​

CREATE INDEX idx_client_communications_tenant_client ON client_communications(tenant_id, client_id);
CREATE INDEX idx_client_communications_tenant_date ON client_communications(tenant_id, communication_date);

Use Cases:

  • Listing communications for a specific client within a tenant
  • Filtering communications by date within a tenant

Index Creation Order​

  1. Phase 1: Create basic tenant_id indexes when adding the column (nullable phase)
  2. Phase 5: Add composite indexes after constraints are tightened (NOT NULL phase)

Performance Considerations​

  • All tenant-scoped queries should start with tenant_id in the WHERE clause
  • Composite indexes are most effective when the leftmost column (tenant_id) is used in the WHERE clause
  • Indexes on tenant_id alone are sufficient for basic tenant isolation queries
  • Composite indexes should be added based on actual query patterns observed in production

Migration SQL Examples​

Basic tenant_id Index​

CREATE INDEX IF NOT EXISTS idx_clients_tenant_id ON clients(tenant_id);

Composite Index​

CREATE INDEX IF NOT EXISTS idx_invoices_tenant_status ON invoices(tenant_id, status);

Partial Index (for nullable foreign keys)​

CREATE INDEX IF NOT EXISTS idx_tasks_tenant_project ON tasks(tenant_id, project_id) WHERE project_id IS NOT NULL;