apps/backend/src/infrastructure/database/migrations/0046_sprint_w_saas_entitlements_billing.sqlSQL
-- Sprint W increment 1 — SaaS plans, entitlements, usage and billing provider foundations.
create table if not exists saas_features (
feature_key text primary key,
display_name text not null,
entitlement_type text not null,
description text,
dependency_json jsonb not null default '[]'::jsonb,
created_at timestamptz not null default now(),
constraint saas_feature_entitlement_type_check
check(entitlement_type in ('boolean','numeric_limit','enum'))
);
create table if not exists saas_plans (
id uuid primary key default gen_random_uuid(),
code text not null unique,
name text not null,
description text,
status text not null default 'draft',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint saas_plan_status_check check(status in ('draft','active','retired'))
);
create table if not exists saas_plan_versions (
id uuid primary key default gen_random_uuid(),
plan_id uuid not null references saas_plans(id) on delete cascade,
version_number integer not null,
currency_code text not null default 'EUR',
billing_interval text not null,
base_price_minor bigint not null default 0,
provider_price_id text,
effective_from timestamptz not null default now(),
effective_until timestamptz,
status text not null default 'draft',
created_at timestamptz not null default now(),
constraint saas_plan_version_unique unique(plan_id,version_number),
constraint saas_plan_version_interval_check check(billing_interval in ('monthly','annual')),
constraint saas_plan_version_status_check check(status in ('draft','active','retired'))
);
create table if not exists saas_plan_entitlements (
plan_version_id uuid not null references saas_plan_versions(id) on delete cascade,
feature_key text not null references saas_features(feature_key) on delete cascade,
entitlement_type text not null,
boolean_value boolean,
numeric_value bigint,
string_value text,
created_at timestamptz not null default now(),
primary key(plan_version_id,feature_key),
constraint plan_entitlement_type_check check(entitlement_type in ('boolean','numeric_limit','enum'))
);
create table if not exists billing_accounts (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null unique references organizations(id) on delete cascade,
provider text not null,
provider_customer_id text,
billing_email text,
company_name text,
address_json jsonb not null default '{}'::jsonb,
tax_id text,
currency_code text not null default 'EUR',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table if not exists organization_subscriptions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
plan_version_id uuid not null references saas_plan_versions(id) on delete restrict,
billing_account_id uuid references billing_accounts(id) on delete set null,
status text not null,
started_at timestamptz not null default now(),
trial_ends_at timestamptz,
current_period_start timestamptz,
current_period_end timestamptz,
cancel_at_period_end boolean not null default false,
cancelled_at timestamptz,
grace_ends_at timestamptz,
provider_subscription_id text,
provider_status text,
provider_updated_at timestamptz,
scheduled_plan_version_id uuid references saas_plan_versions(id) on delete set null,
scheduled_change_at timestamptz,
version bigint not null default 1,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint organization_subscription_status_check check(
status in ('trialing','active','past_due','grace_period','suspended','cancelled','expired')
)
);
create unique index if not exists idx_organization_subscription_current
on organization_subscriptions(organization_id)
where status in ('trialing','active','past_due','grace_period','suspended');
create index if not exists idx_organization_subscriptions_status
on organization_subscriptions(organization_id,status);
create table if not exists subscription_addons (
id uuid primary key default gen_random_uuid(),
subscription_id uuid not null references organization_subscriptions(id) on delete cascade,
addon_code text not null,
feature_key text references saas_features(feature_key) on delete set null,
quantity integer not null default 1,
entitlement_type text,
boolean_value boolean,
numeric_value bigint,
string_value text,
status text not null default 'active',
starts_at timestamptz not null default now(),
ends_at timestamptz,
provider_reference text,
created_at timestamptz not null default now(),
constraint subscription_addon_status_check check(status in ('active','scheduled','expired','cancelled'))
);
create table if not exists entitlement_overrides (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
feature_key text not null references saas_features(feature_key) on delete cascade,
entitlement_type text not null,
boolean_value boolean,
numeric_value bigint,
string_value text,
reason text not null,
starts_at timestamptz not null default now(),
ends_at timestamptz,
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
revoked_at timestamptz,
constraint entitlement_override_type_check check(entitlement_type in ('boolean','numeric_limit','enum'))
);
create index if not exists idx_entitlement_overrides_active
on entitlement_overrides(organization_id,feature_key,starts_at,ends_at)
where revoked_at is null;
create table if not exists organization_entitlement_projection (
organization_id uuid not null references organizations(id) on delete cascade,
feature_key text not null references saas_features(feature_key) on delete cascade,
effective_type text not null,
boolean_value boolean,
numeric_value bigint,
string_value text,
source text not null,
valid_from timestamptz not null,
valid_until timestamptz,
calculated_at timestamptz not null default now(),
over_limit boolean not null default false,
current_usage bigint,
primary key(organization_id,feature_key),
constraint organization_entitlement_projection_type_check check(effective_type in ('boolean','numeric_limit','enum')),
constraint organization_entitlement_projection_source_check check(source in ('plan','add_on','manual_override','trial','suspension_policy'))
);
create table if not exists usage_meters (
organization_id uuid not null references organizations(id) on delete cascade,
metric_key text not null,
current_value bigint not null default 0,
period_start timestamptz,
period_end timestamptz,
updated_at timestamptz not null default now(),
primary key(organization_id,metric_key)
);
create table if not exists usage_snapshots (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
metric_key text not null,
value bigint not null,
captured_at timestamptz not null default now()
);
create table if not exists billing_provider_events (
id uuid primary key default gen_random_uuid(),
provider text not null,
provider_event_id text not null,
organization_id uuid references organizations(id) on delete cascade,
billing_account_id uuid references billing_accounts(id) on delete set null,
event_type text not null,
provider_created_at timestamptz,
payload_hash text,
payload_json jsonb not null default '{}'::jsonb,
status text not null default 'received',
attempt_count integer not null default 0,
next_attempt_at timestamptz,
received_at timestamptz not null default now(),
processed_at timestamptz,
last_error text,
constraint billing_provider_event_unique unique(provider,provider_event_id),
constraint billing_provider_event_status_check check(
status in ('received','processing','processed','ignored','failed','dead_letter')
)
);
create index if not exists idx_billing_provider_events_work
on billing_provider_events(status,next_attempt_at,received_at);
create table if not exists billing_invoice_cache (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
billing_account_id uuid not null references billing_accounts(id) on delete cascade,
provider text not null,
provider_invoice_id text not null,
invoice_number text,
period_start timestamptz,
period_end timestamptz,
amount_due_minor bigint,
amount_paid_minor bigint,
currency_code text,
status text,
hosted_invoice_url text,
invoice_pdf_url text,
provider_created_at timestamptz,
updated_at timestamptz not null default now(),
constraint billing_invoice_cache_unique unique(provider,provider_invoice_id)
);
create table if not exists subscription_change_requests (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
subscription_id uuid not null references organization_subscriptions(id) on delete cascade,
from_plan_version_id uuid not null references saas_plan_versions(id) on delete restrict,
to_plan_version_id uuid not null references saas_plan_versions(id) on delete restrict,
change_type text not null,
effective_at timestamptz,
impact_json jsonb not null default '{}'::jsonb,
status text not null default 'requested',
requested_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
completed_at timestamptz,
constraint subscription_change_type_check check(change_type in ('upgrade','downgrade')),
constraint subscription_change_status_check check(status in ('requested','provider_pending','scheduled','completed','failed','cancelled'))
);
create table if not exists commercial_audit_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete cascade,
event_type text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
details_safe_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now()
);
insert into permissions(permission_key,description) values
('commercial.subscription.read','Read SaaS subscription'),
('commercial.subscription.manage','Manage SaaS subscription'),
('commercial.billing.read','Read SaaS billing invoices'),
('commercial.entitlements.read','Read organization entitlements and usage'),
('platform.plans.manage','Manage SaaS plans and plan versions'),
('platform.entitlements.override','Grant temporary entitlement overrides')
on conflict(permission_key) do nothing;