FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

--Plans define limits (NULL = unlimited)
create table plan_tiers (
  plan_id          uuid primary key default gen_random_uuid(),
  name             text unique not null,
  -- NULL means unlimited
  srt_exports_limit integer check (srt_exports_limit is null or srt_exports_limit >= 0)
);

--Users reference a plan; billing windows live here
create table users (
  user_id   uuid primary key default gen_random_uuid(),
  plan_id   uuid not null references plan_tiers(plan_id),
  -- the current quota window for the user
  period_start timestamptz not null,
  period_end   timestamptz not null,
  check (period_end > period_start)
);

create index on users(plan_id);

--Usage is counted, not decremented
create table user_monthly_usage (
  usage_id   uuid primary key default gen_random_uuid(),
  user_id    uuid not null references users(user_id),
  period_start timestamptz not null,
  period_end   timestamptz not null,
  srt_exports_used integer not null default 0,
  unique (user_id, period_start, period_end)  -- one row per window
);

--A helper view with "remaining" and an allow/deny flag
create view v_user_srt_quota as
select
  u.user_id,
  u.period_start,
  u.period_end,
  pt.srt_exports_limit,
  coalesce(umu.srt_exports_used, 0) as used,
  case
    when pt.srt_exports_limit is null then null  -- unlimited: “remaining” is conceptually ∞
    else greatest(pt.srt_exports_limit - coalesce(umu.srt_exports_used,0), 0)
  end as remaining,
  case
    when pt.srt_exports_limit is null then true
    else coalesce(umu.srt_exports_used,0) < pt.srt_exports_limit
  end as can_download
from users u
join plan_tiers pt on pt.plan_id = u.plan_id
left join user_monthly_usage umu
  on umu.user_id = u.user_id
 and umu.period_start = u.period_start
 and umu.period_end = u.period_end;

-- 1. Plan tiers
insert into plan_tiers (plan_id, name, srt_exports_limit)
values
  ('11111111-1111-1111-1111-111111111111', 'Free', 1),
  ('22222222-2222-2222-2222-222222222222', 'Pro', 10),
  ('33333333-3333-3333-3333-333333333333', 'Unlimited', null);

-- 2. Users
insert into users (user_id, plan_id, period_start, period_end)
values
  ('aaaaaaa1-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month'),
  ('aaaaaaa2-0000-0000-0000-000000000002', '22222222-2222-2222-2222-222222222222',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month'),
  ('aaaaaaa3-0000-0000-0000-000000000003', '33333333-3333-3333-3333-333333333333',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month');

-- 3. Usage so far this month
insert into user_monthly_usage (usage_id, user_id, period_start, period_end, srt_exports_used)
values
  ('bbbbbbb1-0000-0000-0000-000000000001',
   'aaaaaaa1-0000-0000-0000-000000000001',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month',
   1),  -- free user already used their single download

('bbbbbbb2-0000-0000-0000-000000000002',
   'aaaaaaa2-0000-0000-0000-000000000002',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month',
   3),  -- pro user has used 3 of 10

('bbbbbbb3-0000-0000-0000-000000000003',
   'aaaaaaa3-0000-0000-0000-000000000003',
   date_trunc('month', now()), date_trunc('month', now()) + interval '1 month',
   25); -- unlimited user, arbitrary count

select * from v_user_srt_quota;

user_id	period_start	period_end	srt_exports_limit	used	remaining	can_download
aaaaaaa1-0000-0000-0000-000000000001	2025-10-01 00:00:00+00	2025-11-01 00:00:00+00	1	1	0	f
aaaaaaa2-0000-0000-0000-000000000002	2025-10-01 00:00:00+00	2025-11-01 00:00:00+00	10	3	7	t
aaaaaaa3-0000-0000-0000-000000000003	2025-10-01 00:00:00+00	2025-11-01 00:00:00+00		25		t

CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE VIEW
INSERT 0 3
INSERT 0 3
INSERT 0 3