apps/backend/src/infrastructure/database/migrations/0045_sprint_v_completion.sqlSQL
-- Sprint V completion — concrete inbox application, outbound PO, goods receipt and retry/dead-letter.
alter table external_integration_inbox
add column if not exists retry_after_seconds integer;
create table if not exists external_integration_event_consumption (
inbox_id uuid not null references external_integration_inbox(id) on delete cascade,
handler_key text not null,
status text not null default 'processed',
created_at timestamptz not null default now(),
primary key(inbox_id,handler_key),
constraint external_integration_event_consumption_status_check check(status in ('processed','ignored','failed'))
);
create table if not exists supplier_goods_receipt_imports (
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,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
external_receipt_id text not null,
supplier_id uuid references suppliers(id) on delete set null,
purchase_order_id uuid,
status text not null default 'received',
payload_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
completed_at timestamptz,
constraint supplier_goods_receipt_import_status_check check(
status in ('received','processing','completed','conflict','failed')
),
constraint supplier_goods_receipt_import_unique unique(integration_connection_id,external_receipt_id)
);
create table if not exists external_outbound_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 external_outbound_event_consumption_status_check check(status in ('processed','skipped','failed'))
);