apps/backend/src/infrastructure/database/migrations/0037_sprint_r_completion.sqlSQL
-- Sprint R completion — event routing, retry health and manual reconciliation.
alter table reservation_provider_inbox
add column if not exists next_attempt_at timestamptz,
add column if not exists retry_after_seconds integer;
alter table integration_connections
add column if not exists provider_rate_limited_until timestamptz,
add column if not exists provider_consecutive_failures integer not null default 0,
add column if not exists credential_expires_at timestamptz,
add column if not exists last_health_check_at timestamptz,
add column if not exists last_health_ok boolean;
create table if not exists reservation_provider_event_consumption (
outbox_event_id uuid not null references outbox_events(id) on delete cascade,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
status text not null default 'processed',
created_at timestamptz not null default now(),
primary key(outbox_event_id,integration_connection_id),
constraint reservation_provider_event_consumption_status_check
check(status in ('processed','skipped','failed'))
);
create table if not exists integration_issue_resolutions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_issue_id uuid not null references integration_issues(id) on delete cascade,
action text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
note text,
result_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
constraint integration_issue_resolution_action_check check(
action in ('retry_provider','use_provider','keep_local','ignore')
)
);
create index if not exists idx_integration_issue_resolutions_issue
on integration_issue_resolutions(integration_issue_id,created_at desc);
create table if not exists reservation_provider_health_history (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
ok boolean not null,
status_code integer,
code text,
latency_ms integer,
checked_at timestamptz not null default now()
);
create index if not exists idx_reservation_provider_health_connection
on reservation_provider_health_history(integration_connection_id,checked_at desc);