Migration Workflow — From Spec to Deployment

alter table hr_credentials enable row level security;

create policy hr_credentials_tenant_isolation
  on hr_credentials
  for all
  using (org_id = (select auth_org_id()))
  with check (org_id = (select auth_org_id()));

create trigger hr_credentials_audit
  after insert or update or delete on hr_credentials
  for each row execute function pf_log_phi_access();

npm run db:schemas:diff -- -f add_hr_credentials

npm run validate-migration -- --latest

npm run supabase:db:reset   # destroys local DB; reapplies all migrations cleanly
npm run generate-types       # regenerates src/integrations/supabase/types.ts

import { describe, it, expect } from 'vitest';
import { setupRlsTest } from '../helpers';

describe('hr_credentials RLS', () => {
  it('blocks cross-tenant reads', async () => {
    const { tenantA, tenantB } = await setupRlsTest();
    await tenantA.from('hr_credentials').insert({ /* ... */ });
    const { data } = await tenantB.from('hr_credentials').select();
    expect(data).toHaveLength(0);
  });
});

git add supabase/schemas/hr/employees.sql supabase/migrations/<ts>_add_hr_credentials.sql src/integrations/supabase/types.ts tests/rls/hr/credentials.rls.test.ts
git commit -m "hr: add credentials table with tenant + role RLS"
git push -u origin <branch>