apps/backend/src/infrastructure/database/migrations/0048_sprint_w_completion.sqlSQL
-- Sprint W completion — plan administration, reconciliation, scheduled changes and commercial safety.
alter table organization_subscriptions
add column if not exists provider_price_id text,
add column if not exists last_reconciled_at timestamptz,
add column if not exists reconciliation_error text;
alter table billing_accounts
add column if not exists last_reconciled_at timestamptz,
add column if not exists reconciliation_error text;
create table if not exists commercial_reconciliation_runs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
billing_account_id uuid references billing_accounts(id) on delete set null,
status text not null default 'running',
started_at timestamptz not null default now(),
completed_at timestamptz,
details_safe_json jsonb not null default '{}'::jsonb,
last_error text,
constraint commercial_reconciliation_run_status_check
check(status in ('running','completed','failed'))
);
create table if not exists commercial_limit_denials (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
feature_key text not null,
current_usage bigint not null,
limit_value bigint not null,
resource_type text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now()
);
create table if not exists saas_plan_dependency_validation (
id uuid primary key default gen_random_uuid(),
plan_version_id uuid not null references saas_plan_versions(id) on delete cascade,
valid boolean not null,
issues_json jsonb not null default '[]'::jsonb,
validated_at timestamptz not null default now()
);
insert into saas_plans(id,code,name,description,status)
values
('10000000-0000-0000-0000-000000000001','starter','Starter','Core gastronomy operations for a single location.','active'),
('10000000-0000-0000-0000-000000000002','professional','Professional','Full operations, reporting and integrations.','active'),
('10000000-0000-0000-0000-000000000003','multi_location','Multi Location','Advanced multi-location operations and integrations.','active')
on conflict(code) do nothing;
insert into saas_plan_versions(
id,plan_id,version_number,currency_code,billing_interval,base_price_minor,
provider_price_id,effective_from,status
) values
('11000000-0000-0000-0000-000000000001','10000000-0000-0000-0000-000000000001',1,'EUR','monthly',9900,null,now(),'active'),
('11000000-0000-0000-0000-000000000002','10000000-0000-0000-0000-000000000002',1,'EUR','monthly',19900,null,now(),'active'),
('11000000-0000-0000-0000-000000000003','10000000-0000-0000-0000-000000000003',1,'EUR','monthly',39900,null,now(),'active')
on conflict(plan_id,version_number) do nothing;
-- Baseline local/demo entitlements. Provider price IDs intentionally remain null until deployment config.
insert into saas_plan_entitlements(plan_version_id,feature_key,entitlement_type,boolean_value,numeric_value)
select '11000000-0000-0000-0000-000000000001',feature_key,entitlement_type,
case when entitlement_type='boolean' then feature_key in (
'core_service','reservations','kds','operations','haccp','reporting','public_booking'
) else null end,
case feature_key
when 'max_locations' then 1
when 'max_active_users' then 10
when 'max_wordpress_connections' then 0
when 'max_reservation_integrations' then 0
when 'max_payment_connections' then 1
when 'max_pms_connections' then 0
else null end
from saas_features
on conflict(plan_version_id,feature_key) do nothing;
insert into saas_plan_entitlements(plan_version_id,feature_key,entitlement_type,boolean_value,numeric_value)
select '11000000-0000-0000-0000-000000000002',feature_key,entitlement_type,
case when entitlement_type='boolean' then feature_key in (
'core_service','reservations','kds','inventory','purchasing','costing','operations',
'haccp','reporting','public_booking','wordpress','reservation_integrations',
'payment_integrations','fiscalization','accounting','supplier_integrations','advanced_offline'
) else null end,
case feature_key
when 'max_locations' then 1
when 'max_active_users' then 40
when 'max_wordpress_connections' then 3
when 'max_reservation_integrations' then 3
when 'max_payment_connections' then 3
when 'max_pms_connections' then 0
else null end
from saas_features
on conflict(plan_version_id,feature_key) do nothing;
insert into saas_plan_entitlements(plan_version_id,feature_key,entitlement_type,boolean_value,numeric_value)
select '11000000-0000-0000-0000-000000000003',feature_key,entitlement_type,
case when entitlement_type='boolean' then true else null end,
case feature_key
when 'max_locations' then 25
when 'max_active_users' then 500
when 'max_wordpress_connections' then 25
when 'max_reservation_integrations' then 25
when 'max_payment_connections' then 25
when 'max_pms_connections' then 25
else null end
from saas_features
on conflict(plan_version_id,feature_key) do nothing;