pguerrerox
5508e15da1
add Stripe checkout, portal, webhook ingestion, and idempotent event persistence add billing lifecycle state (grace/sync/timeline/admin visibility) and stronger entitlement handling add analytics event tracking and admin summary APIs plus account/pricing UI integration
48 lines
2.4 KiB
SQL
48 lines
2.4 KiB
SQL
alter table public.workspace_billing_accounts
|
|
add column if not exists grace_period_ends_at timestamptz,
|
|
add column if not exists pending_plan_code text,
|
|
add column if not exists pending_plan_effective_at timestamptz,
|
|
add column if not exists billing_sync_status text not null default 'ok' check (billing_sync_status in ('ok', 'stale', 'error')),
|
|
add column if not exists last_stripe_sync_at timestamptz;
|
|
|
|
create table if not exists public.workspace_billing_timeline_events (
|
|
id uuid primary key default gen_random_uuid(),
|
|
workspace_id uuid not null references public.workspaces (id) on delete cascade,
|
|
billing_account_id uuid references public.workspace_billing_accounts (id) on delete set null,
|
|
event_type text not null check (
|
|
event_type in (
|
|
'checkout_completed',
|
|
'subscription_created',
|
|
'subscription_updated',
|
|
'subscription_deleted',
|
|
'invoice_paid',
|
|
'invoice_payment_failed',
|
|
'portal_returned',
|
|
'checkout_returned',
|
|
'addon_purchased',
|
|
'billing_status_changed',
|
|
'plan_change_scheduled'
|
|
)
|
|
),
|
|
source text not null check (source in ('stripe', 'app', 'system')),
|
|
payload_json jsonb not null default '{}'::jsonb,
|
|
external_event_id text,
|
|
external_customer_ref text,
|
|
external_subscription_ref text,
|
|
occurred_at timestamptz not null default now(),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists workspace_billing_accounts_pending_plan_idx on public.workspace_billing_accounts (pending_plan_code);
|
|
create index if not exists workspace_billing_accounts_sync_status_idx on public.workspace_billing_accounts (billing_sync_status);
|
|
create index if not exists workspace_billing_timeline_events_workspace_id_idx on public.workspace_billing_timeline_events (workspace_id, occurred_at desc);
|
|
create index if not exists workspace_billing_timeline_events_event_type_idx on public.workspace_billing_timeline_events (event_type);
|
|
create index if not exists workspace_billing_timeline_events_subscription_ref_idx on public.workspace_billing_timeline_events (external_subscription_ref);
|
|
|
|
drop trigger if exists set_workspace_billing_timeline_events_updated_at on public.workspace_billing_timeline_events;
|
|
create trigger set_workspace_billing_timeline_events_updated_at
|
|
before update on public.workspace_billing_timeline_events
|
|
for each row
|
|
execute function public.set_updated_at();
|