Tenant Consistency Validation Queries

Overview

After backfilling tenant_id values in Phase 2, we must verify that all foreign key relationships maintain tenant consistency. A row and its parent/child rows must belong to the same tenant.

Validation Queries

Run these queries after Phase 2 backfill. All should return 0 rows (no mismatches).

1. Invoices → Clients

-- Invoices must match their client's tenant
SELECT 
  'invoices' as table_name,
  i.id as invoice_id,
  i.tenant_id as invoice_tenant_id,
  c.id as client_id,
  c.tenant_id as client_tenant_id,
  'Mismatch: invoice tenant does not match client tenant' as issue
FROM invoices i
JOIN clients c ON c.id = i.client_id
WHERE i.tenant_id IS DISTINCT FROM c.tenant_id;

2. Invoice Line Items → Invoices

-- Invoice line items must match their invoice's tenant
SELECT 
  'invoice_line_items' as table_name,
  li.id as line_item_id,
  li.tenant_id as line_item_tenant_id,
  i.id as invoice_id,
  i.tenant_id as invoice_tenant_id,
  'Mismatch: line item tenant does not match invoice tenant' as issue
FROM invoice_line_items li
JOIN invoices i ON i.id = li.invoice_id
WHERE li.tenant_id IS DISTINCT FROM i.tenant_id;

3. Quotes → Clients

-- Quotes must match their client's tenant
SELECT 
  'quotes' as table_name,
  q.id as quote_id,
  q.tenant_id as quote_tenant_id,
  c.id as client_id,
  c.tenant_id as client_tenant_id,
  'Mismatch: quote tenant does not match client tenant' as issue
FROM quotes q
JOIN clients c ON c.id = q.client_id
WHERE q.tenant_id IS DISTINCT FROM c.tenant_id;

4. Quote Line Items → Quotes

-- Quote line items must match their quote's tenant
SELECT 
  'quote_line_items' as table_name,
  qli.id as line_item_id,
  qli.tenant_id as line_item_tenant_id,
  q.id as quote_id,
  q.tenant_id as quote_tenant_id,
  'Mismatch: quote line item tenant does not match quote tenant' as issue
FROM quote_line_items qli
JOIN quotes q ON q.id = qli.quote_id
WHERE qli.tenant_id IS DISTINCT FROM q.tenant_id;

5. Projects → Clients

-- Projects must match their client's tenant
SELECT 
  'projects' as table_name,
  p.id as project_id,
  p.tenant_id as project_tenant_id,
  c.id as client_id,
  c.tenant_id as client_tenant_id,
  'Mismatch: project tenant does not match client tenant' as issue
FROM projects p
JOIN clients c ON c.id = p.client_id
WHERE p.tenant_id IS DISTINCT FROM c.tenant_id;

6. Tasks → Projects (where project_id is set)

-- Tasks must match their project's tenant (if project_id is set)
SELECT 
  'tasks' as table_name,
  t.id as task_id,
  t.tenant_id as task_tenant_id,
  p.id as project_id,
  p.tenant_id as project_tenant_id,
  'Mismatch: task tenant does not match project tenant' as issue
FROM tasks t
JOIN projects p ON p.id = t.project_id
WHERE t.tenant_id IS DISTINCT FROM p.tenant_id;

7. Tasks → Employees (where assigned_to is set)

-- Tasks must match their assigned employee's tenant (if assigned_to is set)
SELECT 
  'tasks' as table_name,
  t.id as task_id,
  t.tenant_id as task_tenant_id,
  e.id as employee_id,
  e.tenant_id as employee_tenant_id,
  'Mismatch: task tenant does not match employee tenant' as issue
FROM tasks t
JOIN employees e ON e.id = t.assigned_to
WHERE t.tenant_id IS DISTINCT FROM e.tenant_id;

8. Project Employees → Projects

-- Project employees must match their project's tenant
SELECT 
  'project_employees' as table_name,
  pe.id as project_employee_id,
  pe.tenant_id as project_employee_tenant_id,
  p.id as project_id,
  p.tenant_id as project_tenant_id,
  'Mismatch: project_employee tenant does not match project tenant' as issue
FROM project_employees pe
JOIN projects p ON p.id = pe.project_id
WHERE pe.tenant_id IS DISTINCT FROM p.tenant_id;

9. Project Employees → Employees

-- Project employees must match their employee's tenant
SELECT 
  'project_employees' as table_name,
  pe.id as project_employee_id,
  pe.tenant_id as project_employee_tenant_id,
  e.id as employee_id,
  e.tenant_id as employee_tenant_id,
  'Mismatch: project_employee tenant does not match employee tenant' as issue
FROM project_employees pe
JOIN employees e ON e.id = pe.employee_id
WHERE pe.tenant_id IS DISTINCT FROM e.tenant_id;

10. Payments → Invoices

-- Payments must match their invoice's tenant
SELECT 
  'payments' as table_name,
  pay.id as payment_id,
  pay.tenant_id as payment_tenant_id,
  i.id as invoice_id,
  i.tenant_id as invoice_tenant_id,
  'Mismatch: payment tenant does not match invoice tenant' as issue
FROM payments pay
JOIN invoices i ON i.id = pay.invoice_id
WHERE pay.tenant_id IS DISTINCT FROM i.tenant_id;

11. Client Communications → Clients

-- Client communications must match their client's tenant
SELECT 
  'client_communications' as table_name,
  cc.id as communication_id,
  cc.tenant_id as communication_tenant_id,
  c.id as client_id,
  c.tenant_id as client_tenant_id,
  'Mismatch: communication tenant does not match client tenant' as issue
FROM client_communications cc
JOIN clients c ON c.id = cc.client_id
WHERE cc.tenant_id IS DISTINCT FROM c.tenant_id;

12. Inventory → Products/Services

-- Inventory must match its product's tenant
SELECT 
  'inventory' as table_name,
  inv.id as inventory_id,
  inv.tenant_id as inventory_tenant_id,
  ps.id as product_id,
  ps.tenant_id as product_tenant_id,
  'Mismatch: inventory tenant does not match product tenant' as issue
FROM inventory inv
JOIN products_services ps ON ps.id = inv.product_id
WHERE inv.tenant_id IS DISTINCT FROM ps.tenant_id;

13. Inventory History → Inventory

-- Inventory history must match its inventory record's tenant
SELECT 
  'inventory_history' as table_name,
  ih.id as history_id,
  ih.tenant_id as history_tenant_id,
  inv.id as inventory_id,
  inv.tenant_id as inventory_tenant_id,
  'Mismatch: inventory_history tenant does not match inventory tenant' as issue
FROM inventory_history ih
JOIN inventory inv ON inv.id = ih.inventory_id
WHERE ih.tenant_id IS DISTINCT FROM inv.tenant_id;

14. Timesheets → Employees

-- Timesheets must match their employee's tenant
SELECT 
  'timesheets' as table_name,
  ts.id as timesheet_id,
  ts.tenant_id as timesheet_tenant_id,
  e.id as employee_id,
  e.tenant_id as employee_tenant_id,
  'Mismatch: timesheet tenant does not match employee tenant' as issue
FROM timesheets ts
JOIN employees e ON e.id = ts.employee_id
WHERE ts.tenant_id IS DISTINCT FROM e.tenant_id;

15. Employees → Profiles

-- Employees must match their profile's tenant (employees.id = profiles.id)
SELECT 
  'employees' as table_name,
  e.id as employee_id,
  e.tenant_id as employee_tenant_id,
  p.id as profile_id,
  p.tenant_id as profile_tenant_id,
  'Mismatch: employee tenant does not match profile tenant' as issue
FROM employees e
JOIN profiles p ON p.id = e.id
WHERE e.tenant_id IS DISTINCT FROM p.tenant_id;

Comprehensive Validation Query

Run this single query to check all relationships at once:

-- Comprehensive tenant consistency check
WITH mismatches AS (
  -- Invoices → Clients
  SELECT 'invoices→clients' as relationship, COUNT(*) as count
  FROM invoices i JOIN clients c ON c.id = i.client_id
  WHERE i.tenant_id IS DISTINCT FROM c.tenant_id
  
  UNION ALL
  
  -- Invoice Line Items → Invoices
  SELECT 'invoice_line_items→invoices', COUNT(*)
  FROM invoice_line_items li JOIN invoices i ON i.id = li.invoice_id
  WHERE li.tenant_id IS DISTINCT FROM i.tenant_id
  
  UNION ALL
  
  -- Quotes → Clients
  SELECT 'quotes→clients', COUNT(*)
  FROM quotes q JOIN clients c ON c.id = q.client_id
  WHERE q.tenant_id IS DISTINCT FROM c.tenant_id
  
  UNION ALL
  
  -- Quote Line Items → Quotes
  SELECT 'quote_line_items→quotes', COUNT(*)
  FROM quote_line_items qli JOIN quotes q ON q.id = qli.quote_id
  WHERE qli.tenant_id IS DISTINCT FROM q.tenant_id
  
  UNION ALL
  
  -- Projects → Clients
  SELECT 'projects→clients', COUNT(*)
  FROM projects p JOIN clients c ON c.id = p.client_id
  WHERE p.tenant_id IS DISTINCT FROM c.tenant_id
  
  UNION ALL
  
  -- Tasks → Projects
  SELECT 'tasks→projects', COUNT(*)
  FROM tasks t JOIN projects p ON p.id = t.project_id
  WHERE t.tenant_id IS DISTINCT FROM p.tenant_id
  
  UNION ALL
  
  -- Tasks → Employees
  SELECT 'tasks→employees', COUNT(*)
  FROM tasks t JOIN employees e ON e.id = t.assigned_to
  WHERE t.tenant_id IS DISTINCT FROM e.tenant_id
  
  UNION ALL
  
  -- Project Employees → Projects
  SELECT 'project_employees→projects', COUNT(*)
  FROM project_employees pe JOIN projects p ON p.id = pe.project_id
  WHERE pe.tenant_id IS DISTINCT FROM p.tenant_id
  
  UNION ALL
  
  -- Project Employees → Employees
  SELECT 'project_employees→employees', COUNT(*)
  FROM project_employees pe JOIN employees e ON e.id = pe.employee_id
  WHERE pe.tenant_id IS DISTINCT FROM e.tenant_id
  
  UNION ALL
  
  -- Payments → Invoices
  SELECT 'payments→invoices', COUNT(*)
  FROM payments pay JOIN invoices i ON i.id = pay.invoice_id
  WHERE pay.tenant_id IS DISTINCT FROM i.tenant_id
  
  UNION ALL
  
  -- Client Communications → Clients
  SELECT 'client_communications→clients', COUNT(*)
  FROM client_communications cc JOIN clients c ON c.id = cc.client_id
  WHERE cc.tenant_id IS DISTINCT FROM c.tenant_id
  
  UNION ALL
  
  -- Inventory → Products/Services
  SELECT 'inventory→products_services', COUNT(*)
  FROM inventory inv JOIN products_services ps ON ps.id = inv.product_id
  WHERE inv.tenant_id IS DISTINCT FROM ps.tenant_id
  
  UNION ALL
  
  -- Inventory History → Inventory
  SELECT 'inventory_history→inventory', COUNT(*)
  FROM inventory_history ih JOIN inventory inv ON inv.id = ih.inventory_id
  WHERE ih.tenant_id IS DISTINCT FROM inv.tenant_id
  
  UNION ALL
  
  -- Timesheets → Employees
  SELECT 'timesheets→employees', COUNT(*)
  FROM timesheets ts JOIN employees e ON e.id = ts.employee_id
  WHERE ts.tenant_id IS DISTINCT FROM e.tenant_id
  
  UNION ALL
  
  -- Employees → Profiles
  SELECT 'employees→profiles', COUNT(*)
  FROM employees e JOIN profiles p ON p.id = e.id
  WHERE e.tenant_id IS DISTINCT FROM p.tenant_id
)
SELECT 
  relationship,
  count,
  CASE WHEN count > 0 THEN 'FAIL' ELSE 'PASS' END as status
FROM mismatches
ORDER BY count DESC, relationship;

Fixing Mismatches

If any mismatches are found, fix them by updating the child table's tenant_id to match the parent:

-- Example: Fix invoice tenant_id to match client
UPDATE invoices i
SET tenant_id = (
  SELECT tenant_id FROM clients c WHERE c.id = i.client_id
)
WHERE i.tenant_id IS DISTINCT FROM (
  SELECT tenant_id FROM clients c WHERE c.id = i.client_id
);

Apply similar patterns for other relationships.

NULL tenant_id Check

Also verify no NULL tenant_id values remain after backfill:

-- Check for NULL tenant_id in all tenant-scoped tables
SELECT 'profiles' as table_name, COUNT(*) as null_count
FROM profiles WHERE tenant_id IS NULL
UNION ALL
SELECT 'clients', COUNT(*) FROM clients WHERE tenant_id IS NULL
UNION ALL
SELECT 'products_services', COUNT(*) FROM products_services WHERE tenant_id IS NULL
UNION ALL
SELECT 'inventory', COUNT(*) FROM inventory WHERE tenant_id IS NULL
UNION ALL
SELECT 'inventory_history', COUNT(*) FROM inventory_history WHERE tenant_id IS NULL
UNION ALL
SELECT 'employees', COUNT(*) FROM employees WHERE tenant_id IS NULL
UNION ALL
SELECT 'timesheets', COUNT(*) FROM timesheets WHERE tenant_id IS NULL
UNION ALL
SELECT 'invoices', COUNT(*) FROM invoices WHERE tenant_id IS NULL
UNION ALL
SELECT 'invoice_line_items', COUNT(*) FROM invoice_line_items WHERE tenant_id IS NULL
UNION ALL
SELECT 'quotes', COUNT(*) FROM quotes WHERE tenant_id IS NULL
UNION ALL
SELECT 'quote_line_items', COUNT(*) FROM quote_line_items WHERE tenant_id IS NULL
UNION ALL
SELECT 'projects', COUNT(*) FROM projects WHERE tenant_id IS NULL
UNION ALL
SELECT 'tasks', COUNT(*) FROM tasks WHERE tenant_id IS NULL
UNION ALL
SELECT 'project_employees', COUNT(*) FROM project_employees WHERE tenant_id IS NULL
UNION ALL
SELECT 'payments', COUNT(*) FROM payments WHERE tenant_id IS NULL
UNION ALL
SELECT 'client_communications', COUNT(*) FROM client_communications WHERE tenant_id IS NULL
UNION ALL
SELECT 'activity_logs', COUNT(*) FROM activity_logs WHERE tenant_id IS NULL
UNION ALL
SELECT 'api_keys', COUNT(*) FROM api_keys WHERE tenant_id IS NULL
UNION ALL
SELECT 'webhooks', COUNT(*) FROM webhooks WHERE tenant_id IS NULL
UNION ALL
SELECT 'webhook_logs', COUNT(*) FROM webhook_logs WHERE tenant_id IS NULL;

All counts should be 0.

Overview​

Validation Queries​

1. Invoices → Clients​

2. Invoice Line Items → Invoices​

3. Quotes → Clients​

4. Quote Line Items → Quotes​

5. Projects → Clients​

6. Tasks → Projects (where project_id is set)​

7. Tasks → Employees (where assigned_to is set)​

8. Project Employees → Projects​

9. Project Employees → Employees​

10. Payments → Invoices​

11. Client Communications → Clients​

12. Inventory → Products/Services​

13. Inventory History → Inventory​

14. Timesheets → Employees​

15. Employees → Profiles​

Comprehensive Validation Query​

Fixing Mismatches​

NULL tenant_id Check​