apps/backend/src/infrastructure/database/migrations/0036_sprint_r_reservation_provider_integration.sqlSQL
-- Sprint R — real reservation provider integration.
alter table integration_connections
add column if not exists credential_reference text,
add column if not exists sync_direction text not null default 'inbound_only',
add column if not exists last_successful_inbound_sync_at timestamptz,
add column if not exists last_successful_outbound_sync_at timestamptz,
add column if not exists webhook_last_seen_at timestamptz,
add column if not exists last_error_at timestamptz,
add column if not exists last_error_code text;
alter table integration_connections drop constraint if exists integration_connection_status_check;
alter table integration_connections add constraint integration_connection_status_check check(
status in ('draft','connecting','connected','degraded','error','revoked','disabled','active')
);
alter table integration_connections add constraint integration_connection_sync_direction_check check(
sync_direction in ('inbound_only','outbound_only','bidirectional')
);
create table if not exists reservation_provider_venue_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_venue_id text not null,
provider_venue_name text,
capacity_authority text not null default 'shared_manual',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint reservation_provider_venue_mapping_unique unique(integration_connection_id,provider_venue_id),
constraint reservation_provider_location_mapping_unique unique(integration_connection_id,location_id),
constraint reservation_capacity_authority_check check(
capacity_authority in ('core','provider','shared_manual')
)
);
create table if not exists reservation_provider_state_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,
provider_state text not null,
canonical_state text not null,
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint reservation_provider_state_mapping_unique unique(integration_connection_id,provider_state),
constraint reservation_provider_canonical_state_check check(
canonical_state in ('pending','confirmed','arrived','seated','completed','cancelled','no_show')
)
);
create table if not exists reservation_provider_sync_cursors (
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,
resource_type text not null,
cursor_json jsonb not null default '{}'::jsonb,
last_synced_at timestamptz,
updated_at timestamptz not null default now(),
constraint reservation_provider_cursor_unique unique(integration_connection_id,resource_type)
);
create table if not exists reservation_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,
provider_reservation_id text,
event_type text not null,
event_source text not null,
provider_version text,
provider_updated_at timestamptz,
payload_json jsonb not null default '{}'::jsonb,
payload_hash text not null,
status text not null default 'received',
attempt_count integer not null default 0,
received_at timestamptz not null default now(),
processed_at timestamptz,
last_error text,
constraint reservation_provider_inbox_source_check check(event_source in ('webhook','poll','manual')),
constraint reservation_provider_inbox_status_check check(
status in ('received','processing','processed','duplicate','stale','conflict','failed','dead_letter')
)
);
create unique index if not exists idx_reservation_provider_inbox_event_unique
on reservation_provider_inbox(integration_connection_id,provider_event_id)
where provider_event_id is not null;
create index if not exists idx_reservation_provider_inbox_queue
on reservation_provider_inbox(status,received_at);
create table if not exists reservation_provider_outbox (
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,
reservation_id uuid not null references reservations(id) on delete cascade,
command_type text not null,
idempotency_key text not null,
payload_json jsonb not null default '{}'::jsonb,
status text not null default 'queued',
attempt_count integer not null default 0,
next_attempt_at timestamptz,
source_provider text,
origin_event_id uuid,
sync_generation bigint not null default 1,
created_at timestamptz not null default now(),
processed_at timestamptz,
last_error text,
constraint reservation_provider_outbox_command_check check(command_type in ('create','update','cancel','confirm')),
constraint reservation_provider_outbox_status_check check(status in ('queued','processing','completed','failed','dead_letter')),
constraint reservation_provider_outbox_idempotency_unique unique(integration_connection_id,idempotency_key)
);
create index if not exists idx_reservation_provider_outbox_queue
on reservation_provider_outbox(status,next_attempt_at,created_at);
create table if not exists reservation_provider_snapshots (
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,
reservation_id uuid references reservations(id) on delete cascade,
provider_reservation_id text not null,
provider_state text,
provider_version text,
provider_updated_at timestamptz,
provider_owned_json jsonb not null default '{}'::jsonb,
raw_safe_json jsonb not null default '{}'::jsonb,
applied_at timestamptz not null default now()
);
create index if not exists idx_reservation_provider_snapshots_external
on reservation_provider_snapshots(integration_connection_id,provider_reservation_id,applied_at desc);
create table if not exists integration_issues (
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,
entity_type text not null,
entity_id uuid,
external_id text,
issue_type text not null,
severity text not null default 'warning',
status text not null default 'open',
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 integration_issue_type_check check(issue_type in (
'unknown_state','mapping_missing','conflict','provider_rejected','authentication_failed',
'rate_limited','payload_invalid','stale_update','manual_review'
)),
constraint integration_issue_severity_check check(severity in ('info','warning','high','critical')),
constraint integration_issue_status_check check(status in ('open','retrying','resolved','ignored'))
);
create index if not exists idx_integration_issues_connection_open
on integration_issues(integration_connection_id,status,created_at desc);
-- Provider version metadata belongs to the integration edge, not canonical Reservation.
create table if not exists reservation_provider_external_versions (
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
provider_reservation_id text not null,
provider_version text,
provider_updated_at timestamptz,
last_inbox_id uuid references reservation_provider_inbox(id) on delete set null,
updated_at timestamptz not null default now(),
primary key(integration_connection_id,provider_reservation_id)
);
-- Correct external reference uniqueness to the canonical per-connection model.
alter table external_references drop constraint if exists external_reference_unique;
create unique index if not exists idx_external_reference_connection_unique
on external_references(integration_connection_id,provider,entity_type,external_id)
where integration_connection_id is not null;
insert into permissions(permission_key,description) values
('integrations.reservations.read','Read reservation provider integrations'),
('integrations.reservations.manage','Manage reservation provider integrations'),
('integrations.reservations.sync','Run reservation provider synchronization'),
('integrations.issues.read','Read integration issues'),
('integrations.issues.manage','Resolve integration issues')
on conflict(permission_key) do nothing;