apps/backend/src/infrastructure/database/migrations/0017_sprint_h_completion.sqlSQL
-- Sprint H completion — provider callbacks/refunds/split metadata.
create table if not exists payment_provider_callbacks (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
provider_key text not null,
provider_event_id text not null,
provider_reference text,
event_type text not null,
payload jsonb not null default '{}'::jsonb,
received_at timestamptz not null default now(),
processed_at timestamptz,
processing_error text,
constraint payment_provider_callbacks_unique
unique(provider_key, provider_event_id)
);
create table if not exists refunds (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
payment_id uuid not null references payments(id) on delete restrict,
amount_minor bigint not null,
currency_code char(3) not null,
status text not null default 'requested',
reason text not null,
provider_key text,
provider_reference text,
requested_at timestamptz not null default now(),
completed_at timestamptz,
created_by uuid references user_accounts(id) on delete set null,
constraint refunds_amount_positive check(amount_minor > 0),
constraint refunds_status_check check(status in ('requested','pending','completed','failed','uncertain'))
);
create index if not exists idx_refunds_payment
on refunds(payment_id, requested_at);