Skip to main content

​
Database Schema

Garmint uses Supabase (PostgreSQL) with a structured schema for auth, billing, projects, and production.

​
Migrations

Migrations live in supabase/migrations/. Apply them with: 
npx supabase db push
# or
npx supabase migration up

​
Core Tables

​
profiles

User profiles, created automatically on signup: 
create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email citext unique,
  display_name text,
  avatar_url text,
  role text not null default 'member', -- 'member' | 'admin' | 'operator'
  plan_override text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Auto-create profile on signup
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

​
projects

Workspace containers for threads and generations: 
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  name text not null,
  slug text not null,
  description text,
  created_at timestamptz default now(),
  updated_at timestamptz default now(),
  unique(user_id, slug)
);

​
threads

Chat conversation containers: 
create table public.threads (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  project_id uuid references public.projects(id) on delete set null,
  title text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

​
generations

AI-generated design images: 
create table public.generations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  project_id uuid references public.projects(id) on delete set null,
  thread_id uuid references public.threads(id) on delete set null,
  prompt text,
  image_url text,
  provider text,    -- 'replicate' | 'fal'
  model text,       -- 'nano-banana' | 'nano-banana-pro'
  status text default 'pending',
  created_at timestamptz default now()
);

​
Billing Tables

​
billing_plans

Available subscription tiers: 
create table public.billing_plans (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,       -- 'free', 'pro', 'studio'
  name text not null,              -- 'Free', 'Pro', 'Studio'
  billing_model text not null,     -- 'subscription' | 'payg'
  description text,
  monthly_tokens integer default 0,
  rollover boolean default false,
  price_monthly integer default 0, -- in cents
  price_per_token numeric(10,4) default 0,
  is_active boolean default true,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

​
subscriptions

User subscription records: 
create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete cascade,
  plan_id uuid references public.billing_plans(id),
  status text default 'trialing', -- 'trialing' | 'active' | 'past_due' | 'canceled'
  current_period_start timestamptz default now(),
  current_period_end timestamptz,
  cancel_at_period_end boolean default false,
  external_reference text,        -- Stripe subscription ID
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

​
token_grants

Token allocations from subscriptions or purchases: 
create table public.token_grants (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete cascade,
  subscription_id uuid references public.subscriptions(id),
  source text not null,  -- 'system' | 'plan' | 'payg_purchase' | 'manual_adjustment'
  tokens integer not null,
  expires_at timestamptz,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

​
usage_ledgers

Token consumption records: 
create table public.usage_ledgers (
  id bigserial primary key,
  user_id uuid references public.profiles(id) on delete cascade,
  subscription_id uuid references public.subscriptions(id),
  event_type text not null, -- 'ai_generation' | 'mockup' | 'upload' | 'shopify_call'
  tokens integer not null,
  model text,
  notes text,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

​
Token Balance View

Calculates remaining tokens per user: 
create view public.user_token_balances as
with grants as (
  select user_id, coalesce(sum(tokens), 0) as tokens_available
  from public.token_grants
  where expires_at is null or expires_at > now()
  group by user_id
),
usage as (
  select user_id, coalesce(sum(tokens), 0) as tokens_spent
  from public.usage_ledgers
  group by user_id
)
select
  p.id as user_id,
  coalesce(g.tokens_available, 0) as tokens_available,
  coalesce(u.tokens_spent, 0) as tokens_spent,
  greatest(g.tokens_available - u.tokens_spent, 0) as tokens_remaining
from public.profiles p
left join grants g on g.user_id = p.id
left join usage u on u.user_id = p.id;

​
Production Tables

​
production_orders

Orders for physical garment production: 
create table public.production_orders (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  project_id uuid references public.projects(id),
  reference text unique,
  status text default 'draft',
  notes text,
  metadata jsonb default '{}',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

​
production_line_items

Order line items (size/quantity): 
create table public.production_line_items (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references public.production_orders(id) on delete cascade,
  size text not null,
  quantity integer default 1,
  technique text default 'dtg',
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

​
fulfillment_events

Order status history: 
create table public.fulfillment_events (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references public.production_orders(id) on delete cascade,
  status text not null,
  message text,
  metadata jsonb default '{}',
  occurred_at timestamptz default now()
);

​
Row Level Security

All tables have RLS enabled. Key policies: 
-- Users can only see their own data
create policy "Users view own profile"
  on public.profiles for select
  using (auth.uid() = id);

create policy "Users view own projects"
  on public.projects for select
  using (auth.uid() = user_id);

create policy "Users view own generations"
  on public.generations for select
  using (auth.uid() = user_id);

-- Service role bypasses RLS for server operations
create policy "Service role manages all"
  on public.profiles for all
  using (auth.role() = 'service_role');

​
Indexes

Key indexes for performance: 
-- Fast user lookups
create index idx_projects_user_id on public.projects(user_id);
create index idx_threads_user_id on public.threads(user_id);
create index idx_generations_user_id on public.generations(user_id);

-- Fast project filtering
create index idx_threads_project_id on public.threads(project_id);
create index idx_generations_project_id on public.generations(project_id);

-- Order lookups
create index idx_production_orders_user_id on public.production_orders(user_id);
create index idx_production_orders_status on public.production_orders(status);

​
Seeding

Initial billing plans are seeded via supabase/seed.sql: 
insert into public.billing_plans (slug, name, billing_model, monthly_tokens, price_monthly)
values
  ('free', 'Free', 'subscription', 25, 0),
  ('pro', 'Pro', 'subscription', 500, 2900),
  ('studio', 'Studio', 'subscription', 2000, 9900);

​
Type Generation

Generate TypeScript types from your schema: 
npx supabase gen types typescript --local > types/supabase.ts
This creates typed interfaces for all tables used throughout the app.