72 lines
2.8 KiB
PL/PgSQL
72 lines
2.8 KiB
PL/PgSQL
-- Mall Software Subscription Tables
|
|
-- PostgreSQL DDL; adjust schema name as needed (default public)
|
|
|
|
-- Plans
|
|
create table if not exists ml_subscription_plans (
|
|
id uuid primary key default gen_random_uuid(),
|
|
plan_code text not null unique,
|
|
name text not null,
|
|
description text,
|
|
features jsonb,
|
|
price numeric(12,2) not null,
|
|
currency text default 'CNY',
|
|
billing_period text not null check (billing_period in ('monthly','yearly')),
|
|
trial_days int default 0,
|
|
is_active boolean default true,
|
|
sort_order int default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_ml_subscription_plans_active on ml_subscription_plans(is_active) where is_active = true;
|
|
create index if not exists idx_ml_subscription_plans_sort on ml_subscription_plans(sort_order);
|
|
|
|
-- User Subscriptions
|
|
create table if not exists ml_user_subscriptions (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null,
|
|
plan_id uuid not null references ml_subscription_plans(id) on delete restrict,
|
|
status text not null default 'active' check (status in ('trial','active','past_due','canceled','expired')),
|
|
start_date timestamptz not null default now(),
|
|
end_date timestamptz,
|
|
next_billing_date timestamptz,
|
|
auto_renew boolean not null default true,
|
|
cancel_at_period_end boolean not null default false,
|
|
metadata jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_ml_user_subscriptions_user on ml_user_subscriptions(user_id);
|
|
create index if not exists idx_ml_user_subscriptions_plan on ml_user_subscriptions(plan_id);
|
|
create index if not exists idx_ml_user_subscriptions_status on ml_user_subscriptions(status);
|
|
|
|
-- updated_at trigger helper (idempotent)
|
|
create or replace function public.set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $fn$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$fn$;
|
|
|
|
-- Recreate triggers safely
|
|
drop trigger if exists trg_ml_subscription_plans_updated on ml_subscription_plans;
|
|
create trigger trg_ml_subscription_plans_updated
|
|
before update on ml_subscription_plans
|
|
for each row execute function public.set_updated_at();
|
|
|
|
drop trigger if exists trg_ml_user_subscriptions_updated on ml_user_subscriptions;
|
|
create trigger trg_ml_user_subscriptions_updated
|
|
before update on ml_user_subscriptions
|
|
for each row execute function public.set_updated_at();
|
|
|
|
-- Optional: basic RLS scaffolding (customize policies per project standards)
|
|
-- alter table ml_user_subscriptions enable row level security;
|
|
-- create policy rls_ml_user_subscriptions_owner on ml_user_subscriptions
|
|
-- using (user_id::text = current_setting('app.user_id', true));
|
|
|
|
-- Done
|