apps/backend/src/infrastructure/database/migrations/0004_sprint_b_membership_employee_lifecycle.sqlSQL
-- Sprint B — membership lifecycle, location scopes, role assignment, last-owner safety.
alter table memberships
add column if not exists invited_at timestamptz,
add column if not exists activated_at timestamptz,
add column if not exists suspended_at timestamptz,
add column if not exists revoked_at timestamptz;
alter table employees
add column if not exists started_at date,
add column if not exists ended_at date;
create table if not exists membership_location_scopes (
membership_id uuid not null references memberships(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
created_at timestamptz not null default now(),
primary key (membership_id, location_id)
);
create index if not exists idx_membership_location_scopes_location
on membership_location_scopes(location_id);
create or replace function enforce_membership_location_scope_same_org()
returns trigger
language plpgsql
as $$
declare
membership_org uuid;
location_org uuid;
begin
select organization_id into membership_org
from memberships where id = new.membership_id;
select organization_id into location_org
from locations where id = new.location_id;
if membership_org is null or location_org is null or membership_org <> location_org then
raise exception 'membership location scope must stay within organization'
using errcode = '23514';
end if;
return new;
end;
$$;
drop trigger if exists trg_membership_location_scope_same_org
on membership_location_scopes;
create trigger trg_membership_location_scope_same_org
before insert or update
on membership_location_scopes
for each row
execute function enforce_membership_location_scope_same_org();
insert into permissions(permission_key, description) values
('memberships.read', 'Read memberships'),
('memberships.invite', 'Invite memberships'),
('memberships.suspend', 'Suspend memberships'),
('memberships.revoke', 'Revoke memberships'),
('memberships.activate', 'Activate memberships'),
('employees.create', 'Create employees'),
('employees.update', 'Update employees'),
('employees.terminate', 'Terminate employees'),
('roles.assign', 'Assign roles'),
('roles.unassign', 'Remove role assignments'),
('location_scopes.manage', 'Manage membership location scopes')
on conflict (permission_key) do nothing;