apps/backend/src/infrastructure/database/migrations/0034_sprint_q_public_booking_wordpress.sqlSQL
-- Sprint Q — public booking and WordPress integration.
create table if not exists public_location_profiles (
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,
public_location_id uuid not null default gen_random_uuid(),
public_name text not null,
address_json jsonb not null default '{}'::jsonb,
contact_json jsonb not null default '{}'::jsonb,
booking_policy_summary_de text,
booking_policy_summary_en text,
is_published boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint public_location_profiles_location_unique unique(location_id),
constraint public_location_profiles_public_id_unique unique(public_location_id)
);
create table if not exists public_booking_configs (
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,
is_enabled boolean not null default false,
default_duration_minutes integer not null default 120,
slot_interval_minutes integer not null default 30,
minimum_party_size integer not null default 1,
maximum_party_size integer not null default 12,
minimum_lead_time_minutes integer not null default 60,
maximum_advance_days integer not null default 90,
confirmation_mode text not null default 'request',
cancellation_cutoff_minutes integer,
guest_note_enabled boolean not null default true,
dietary_note_enabled boolean not null default true,
require_email boolean not null default true,
require_phone boolean not null default false,
widget_theme_json jsonb not null default '{}'::jsonb,
allowed_origins jsonb not null default '[]'::jsonb,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint public_booking_config_location_unique unique(location_id),
constraint public_booking_confirmation_mode_check check(confirmation_mode in ('instant','request')),
constraint public_booking_slot_interval_check check(slot_interval_minutes in (15,30,60)),
constraint public_booking_party_check check(minimum_party_size > 0 and maximum_party_size >= minimum_party_size),
constraint public_booking_duration_check check(default_duration_minutes between 15 and 480)
);
create table if not exists public_booking_windows (
id uuid primary key default gen_random_uuid(),
public_booking_config_id uuid not null references public_booking_configs(id) on delete cascade,
weekday smallint not null,
start_local time not null,
end_local time not null,
is_active boolean not null default true,
constraint public_booking_window_weekday_check check(weekday between 0 and 6),
constraint public_booking_window_time_check check(end_local > start_local)
);
create index if not exists idx_public_booking_windows_config_day
on public_booking_windows(public_booking_config_id,weekday,is_active);
create table if not exists public_booking_tokens (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
reservation_id uuid not null references reservations(id) on delete cascade,
token_hash text not null,
expires_at timestamptz not null,
revoked_at timestamptz,
created_at timestamptz not null default now(),
constraint public_booking_token_hash_unique unique(token_hash)
);
create index if not exists idx_public_booking_tokens_reservation
on public_booking_tokens(reservation_id);
create table if not exists public_booking_idempotency (
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,
idempotency_key text not null,
request_hash text not null,
reservation_id uuid references reservations(id) on delete set null,
response_json jsonb,
status text not null default 'processing',
created_at timestamptz not null default now(),
completed_at timestamptz,
constraint public_booking_idempotency_unique unique(location_id,idempotency_key),
constraint public_booking_idempotency_status_check check(status in ('processing','completed','failed'))
);
create table if not exists public_rate_limit_buckets (
bucket_key text primary key,
request_count integer not null default 0,
window_started_at timestamptz not null,
updated_at timestamptz not null default now()
);
create table if not exists integration_connections (
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,
category text not null,
provider text not null,
display_name text not null,
status text not null default 'active',
credentials_json jsonb not null default '{}'::jsonb,
config_json jsonb not null default '{}'::jsonb,
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
revoked_at timestamptz,
version bigint not null default 1,
constraint integration_connection_status_check check(status in ('active','disabled','error','revoked'))
);
create index if not exists idx_integration_connections_org_provider
on integration_connections(organization_id,provider,status);
create table if not exists integration_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_connection_id uuid references integration_connections(id) on delete set null,
direction text not null,
provider text not null,
event_type text not null,
external_event_id text,
payload_hash text,
status text not null default 'received',
request_id text,
occurred_at timestamptz not null default now(),
processed_at timestamptz,
error text,
constraint integration_event_direction_check check(direction in ('inbound','outbound')),
constraint integration_event_status_check check(status in ('received','processed','duplicate','rejected','failed'))
);
create unique index if not exists idx_integration_event_external_unique
on integration_events(integration_connection_id,external_event_id)
where external_event_id is not null;
create table if not exists external_references (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
provider text not null,
entity_type text not null,
entity_id uuid not null,
external_id text not null,
integration_connection_id uuid references integration_connections(id) on delete set null,
created_at timestamptz not null default now(),
constraint external_reference_unique unique(provider,entity_type,external_id)
);
create index if not exists idx_external_reference_entity
on external_references(organization_id,entity_type,entity_id);
create table if not exists wordpress_form_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,
source_form_id text,
source_provider text not null default 'generic',
field_map_json jsonb not null,
confirmation_mode text,
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint wordpress_form_provider_check check(
source_provider in ('elementor','wpforms','gravityforms','contactform7','generic')
),
constraint wordpress_mapping_confirmation_check check(
confirmation_mode is null or confirmation_mode in ('instant','request')
)
);
create index if not exists idx_wordpress_form_mappings_connection
on wordpress_form_mappings(integration_connection_id,is_active);
create table if not exists wordpress_webhook_nonces (
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
nonce text not null,
used_at timestamptz not null default now(),
primary key(integration_connection_id,nonce)
);
create table if not exists public_booking_metrics_daily (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
business_date date not null,
availability_requests integer not null default 0,
booking_attempts integer not null default 0,
booking_successes integer not null default 0,
slot_conflicts integer not null default 0,
rate_limited integer not null default 0,
primary key(location_id,business_date)
);
insert into permissions(permission_key,description) values
('public_booking.settings.manage','Manage public booking settings'),
('integrations.wordpress.read','Read WordPress integration settings'),
('integrations.wordpress.manage','Manage WordPress integration settings'),
('public_booking.create','Create reservations through trusted integration'),
('public_booking.read_config','Read public booking configuration through trusted integration')
on conflict(permission_key) do nothing;