apps/backend/src/infrastructure/database/migrations/0049_sprint_x_foundation_security_repair.sqlSQL
-- Sprint X increment 1 — repair tenant-role foundations and add production security support state.
-- membership_roles.location_id was intended to be nullable, but the original composite PK
-- implicitly made it NOT NULL. Replace it with a surrogate PK and partial uniqueness.
alter table membership_roles drop constraint if exists membership_roles_pkey;
alter table membership_roles add column if not exists id uuid default gen_random_uuid();
update membership_roles set id=gen_random_uuid() where id is null;
alter table membership_roles alter column id set not null;
alter table membership_roles add constraint membership_roles_pkey primary key(id);
alter table membership_roles alter column location_id drop not null;
create unique index if not exists membership_roles_orgwide_unique
on membership_roles(membership_id,role_id)
where location_id is null;
create unique index if not exists membership_roles_location_unique
on membership_roles(membership_id,role_id,location_id)
where location_id is not null;
-- PostgreSQL UNIQUE with nullable organization_id allowed duplicate system role keys.
alter table roles drop constraint if exists roles_scope_key_unique;
create unique index if not exists roles_system_key_unique
on roles(role_key)
where organization_id is null;
create unique index if not exists roles_org_key_unique
on roles(organization_id,role_key)
where organization_id is not null;
-- Normalize the canonical owner key if an early installation used the legacy key.
update roles
set role_key='platform_org_owner',updated_at=now(),version=version+1
where organization_id is null
and role_key='owner'
and not exists (
select 1 from roles r2
where r2.organization_id is null and r2.role_key='platform_org_owner'
);
create table if not exists security_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete set null,
user_account_id uuid references user_accounts(id) on delete set null,
event_type text not null,
severity text not null default 'info',
source_ip_hash text,
user_agent_hash text,
correlation_id text,
details_safe_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
constraint security_event_severity_check check(severity in ('info','warning','high','critical'))
);
create index if not exists idx_security_events_created
on security_events(created_at desc);
create index if not exists idx_security_events_org
on security_events(organization_id,created_at desc);
create table if not exists production_readiness_checks (
check_key text primary key,
status text not null,
details_safe_json jsonb not null default '{}'::jsonb,
checked_at timestamptz not null default now(),
constraint production_readiness_status_check check(status in ('pass','warn','fail'))
);