apps/backend/src/infrastructure/database/migrations/0039_sprint_s_completion.sqlSQL
-- Sprint S completion — reconciliation invariants, references, rate-limit health and refund idempotency.
alter table refunds
add column if not exists request_idempotency_key text,
add column if not exists integration_connection_id uuid references integration_connections(id) on delete set null,
add column if not exists provider_status text,
add column if not exists uncertain_since timestamptz;
create unique index if not exists idx_refunds_request_idempotency
on refunds(organization_id,request_idempotency_key)
where request_idempotency_key is not null;
create table if not exists payment_issue_resolutions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
payment_integration_issue_id uuid not null references payment_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 payment_issue_resolution_action_check check(
action in ('refresh_provider','mark_investigated','ignore')
)
);
create index if not exists idx_payment_issue_resolutions_issue
on payment_issue_resolutions(payment_integration_issue_id,created_at desc);
create table if not exists payment_provider_rate_limit_events (
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,
operation text not null,
retry_after_seconds integer,
occurred_at timestamptz not null default now()
);
create index if not exists idx_payment_provider_rate_limit_events_connection
on payment_provider_rate_limit_events(integration_connection_id,occurred_at desc);
create unique index if not exists idx_external_reference_payment_provider_object
on external_references(integration_connection_id,provider,entity_type,external_id)
where integration_connection_id is not null
and entity_type in ('payment_intent','refund');
insert into permissions(permission_key,description) values
('payments.terminal.cancel','Cancel a pending terminal action'),
('payments.issues.read','Read payment integration issues')
on conflict(permission_key) do nothing;