apps/backend/src/infrastructure/database/migrations/0038_sprint_s_real_payment_provider.sqlSQL
-- Sprint S — real payment provider integration and payment transaction hardening.
alter table payment_intents
add column if not exists integration_connection_id uuid references integration_connections(id) on delete set null,
add column if not exists channel text,
add column if not exists request_idempotency_key text,
add column if not exists terminal_mapping_id uuid,
add column if not exists provider_environment text,
add column if not exists safe_payment_method_json jsonb not null default '{}'::jsonb;
alter table payment_intents drop constraint if exists payment_intents_channel_check;
alter table payment_intents add constraint payment_intents_channel_check
check(channel is null or channel in ('cash','terminal','online','external'));
alter table payment_intents drop constraint if exists payment_intents_provider_environment_check;
alter table payment_intents add constraint payment_intents_provider_environment_check
check(provider_environment is null or provider_environment in ('sandbox','production'));
create unique index if not exists idx_payment_intents_request_idempotency
on payment_intents(organization_id,request_idempotency_key)
where request_idempotency_key is not null;
-- Exactly one successful payment row per PaymentIntent.
create unique index if not exists idx_payments_one_success_per_intent
on payments(payment_intent_id)
where payment_intent_id is not null and status in ('authorized','captured','refunded');
create table if not exists payment_provider_terminal_mappings (
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,
location_id uuid not null references locations(id) on delete cascade,
provider_terminal_id text not null,
provider_location_id text,
display_name text not null,
device_id uuid,
status text not null default 'unknown',
capabilities_json jsonb not null default '{}'::jsonb,
last_seen_at timestamptz,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint payment_terminal_mapping_unique unique(integration_connection_id,provider_terminal_id),
constraint payment_terminal_status_check check(status in ('available','busy','offline','unknown','disabled'))
);
alter table payment_intents
drop constraint if exists payment_intents_terminal_mapping_fk;
alter table payment_intents
add constraint payment_intents_terminal_mapping_fk
foreign key(terminal_mapping_id) references payment_provider_terminal_mappings(id) on delete set null;
create table if not exists payment_attempts (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
payment_intent_id uuid not null references payment_intents(id) on delete cascade,
integration_connection_id uuid references integration_connections(id) on delete set null,
provider text not null,
provider_attempt_id text,
attempt_number integer not null,
channel text not null,
status text not null default 'created',
request_idempotency_key text not null,
terminal_mapping_id uuid references payment_provider_terminal_mappings(id) on delete set null,
provider_status text,
safe_result_json jsonb not null default '{}'::jsonb,
error_code text,
started_at timestamptz not null default now(),
completed_at timestamptz,
created_at timestamptz not null default now(),
constraint payment_attempt_status_check check(
status in ('created','submitted','pending','completed','failed','cancelled','status_unclear')
),
constraint payment_attempt_channel_check check(channel in ('terminal','online','external')),
constraint payment_attempt_number_unique unique(payment_intent_id,attempt_number),
constraint payment_attempt_idempotency_unique unique(integration_connection_id,request_idempotency_key)
);
create index if not exists idx_payment_attempts_intent
on payment_attempts(payment_intent_id,attempt_number desc);
create table if not exists payment_provider_inbox (
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,
provider text not null,
provider_event_id text not null,
provider_object_id text,
event_type text not null,
payload_hash text not null,
payload_json jsonb not null default '{}'::jsonb,
status text not null default 'received',
attempt_count integer not null default 0,
received_at timestamptz not null default now(),
next_attempt_at timestamptz,
processed_at timestamptz,
last_error text,
constraint payment_provider_inbox_status_check check(
status in ('received','processing','processed','failed','dead_letter','ignored')
),
constraint payment_provider_inbox_unique unique(integration_connection_id,provider_event_id)
);
create index if not exists idx_payment_provider_inbox_work
on payment_provider_inbox(status,next_attempt_at,received_at);
create table if not exists payment_integration_issues (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete set null,
integration_connection_id uuid references integration_connections(id) on delete set null,
payment_intent_id uuid references payment_intents(id) on delete set null,
refund_id uuid references refunds(id) on delete set null,
issue_type text not null,
severity text not null default 'high',
status text not null default 'open',
provider_object_id text,
summary text not null,
details_safe_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
resolved_at timestamptz,
resolved_by_user_id uuid references user_accounts(id) on delete set null,
constraint payment_integration_issue_type_check check(
issue_type in (
'unknown_provider_status','amount_mismatch','currency_mismatch','webhook_unmatched',
'duplicate_provider_object','provider_timeout','rate_limited','credential_error',
'terminal_location_mismatch','refund_unclear','provider_rejected'
)
),
constraint payment_integration_issue_status_check check(status in ('open','retrying','resolved','ignored')),
constraint payment_integration_issue_severity_check check(severity in ('medium','high','critical'))
);
create index if not exists idx_payment_integration_issues_open
on payment_integration_issues(organization_id,status,created_at desc);
create table if not exists payment_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()
);
alter table integration_connections
add column if not exists merchant_reference text,
add column if not exists environment text,
add column if not exists last_successful_payment_at timestamptz;
alter table integration_connections drop constraint if exists integration_connection_environment_check;
alter table integration_connections add constraint integration_connection_environment_check
check(environment is null or environment in ('sandbox','production'));
create unique index if not exists idx_payment_provider_callback_connection_event
on payment_provider_callbacks(provider_event_id,organization_id);
insert into permissions(permission_key,description) values
('payments.integrations.read','Read payment integration configuration and health'),
('payments.integrations.manage','Manage payment provider connections and terminals'),
('payments.issues.manage','Resolve payment integration issues')
on conflict(permission_key) do nothing;