apps/backend/src/infrastructure/database/migrations/0050_sprint_x_completion.sqlSQL
-- Sprint X completion — auth revocation, step-up evidence, worker health and readiness evidence.
create table if not exists user_auth_security_state (
user_account_id uuid primary key references user_accounts(id) on delete cascade,
revoked_before timestamptz,
require_step_up_after timestamptz,
reason text,
updated_by_user_id uuid references user_accounts(id) on delete set null,
updated_at timestamptz not null default now()
);
create table if not exists revoked_auth_sessions (
id uuid primary key default gen_random_uuid(),
user_account_id uuid not null references user_accounts(id) on delete cascade,
session_identifier_hash text not null,
reason text not null,
revoked_by_user_id uuid references user_accounts(id) on delete set null,
revoked_at timestamptz not null default now(),
expires_at timestamptz,
constraint revoked_auth_session_hash_unique unique(session_identifier_hash)
);
create index if not exists idx_revoked_auth_sessions_user
on revoked_auth_sessions(user_account_id,revoked_at desc);
create table if not exists privileged_action_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete set null,
user_account_id uuid not null references user_accounts(id) on delete restrict,
action_key text not null,
auth_time timestamptz,
acr text,
amr_json jsonb not null default '[]'::jsonb,
correlation_id text,
result text not null,
created_at timestamptz not null default now(),
constraint privileged_action_result_check check(result in ('allowed','denied'))
);
create index if not exists idx_privileged_actions_user
on privileged_action_events(user_account_id,created_at desc);
create table if not exists worker_heartbeats (
worker_key text primary key,
status text not null,
last_started_at timestamptz,
last_success_at timestamptz,
last_failure_at timestamptz,
consecutive_failures integer not null default 0,
last_duration_ms integer,
last_error_code text,
details_safe_json jsonb not null default '{}'::jsonb,
updated_at timestamptz not null default now(),
constraint worker_heartbeat_status_check check(status in ('idle','running','healthy','degraded','failed'))
);
alter table outbox_events
add column if not exists dead_lettered_at timestamptz,
add column if not exists next_attempt_at timestamptz;
create index if not exists idx_outbox_retryable
on outbox_events(coalesce(next_attempt_at,available_at),occurred_at)
where processed_at is null and dead_lettered_at is null;
create table if not exists production_acceptance_evidence (
id uuid primary key default gen_random_uuid(),
check_key text not null,
environment text not null,
status text not null,
evidence_type text not null,
evidence_reference text,
details_safe_json jsonb not null default '{}'::jsonb,
recorded_by_user_id uuid references user_accounts(id) on delete set null,
recorded_at timestamptz not null default now(),
constraint production_acceptance_evidence_status_check check(status in ('pass','warn','fail')),
constraint production_acceptance_evidence_type_check check(
evidence_type in ('automated_test','manual_test','runbook','staging_log','trace','restore_drill','security_review','performance_run','operational_signoff')
)
);
create index if not exists idx_production_acceptance_evidence_check
on production_acceptance_evidence(environment,check_key,recorded_at desc);
insert into permissions(permission_key,description) values
('security.sessions.manage','Revoke user authentication sessions'),
('security.events.read','Read security event history'),
('production.readiness.read','Read pilot/production readiness'),
('production.readiness.manage','Record production acceptance evidence')
on conflict(permission_key) do nothing;