Hugging Face's logo

Spaces:
simikkk
/
EtsyListingGenerator
Paused

App Files Community
EtsyListingGenerator / supabase_schema.sql
simikkk's picture
simikkk
Upload 36 files
16e1aa7 verified
Raw History Blame Contribute Delete
6.15 kB
-- Etsy Listing Optimizer - Supabase (Postgres) schema
-- Run this in the Supabase SQL editor for your project (see DEPLOY.md).
-- Assumes Supabase Auth is enabled; auth.users is Supabase's built-in table.
-- ---------------------------------------------------------------------------
-- profiles: one row per auth user, holds subscription/tier state
-- ---------------------------------------------------------------------------
create table if not exists public.profiles (
user_id uuid primary key references auth.users (id) on delete cascade,
email text not null,
tier text not null default 'free' check (tier in ('free', 'starter', 'pro', 'business')),
stripe_customer_id text,
stripe_subscription_id text,
stripe_subscription_status text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table public.profiles enable row level security;
create policy "profiles_select_own" on public.profiles
for select using (auth.uid() = user_id);
create policy "profiles_update_own" on public.profiles
for update using (auth.uid() = user_id);
-- Inserts happen via a service-role backend call on signup, not directly by
-- the client, so no client-facing insert policy is defined here.
-- ---------------------------------------------------------------------------
-- generation_counters: one row per user per calendar month
-- ---------------------------------------------------------------------------
create table if not exists public.generation_counters (
user_id uuid not null references auth.users (id) on delete cascade,
year_month text not null, -- e.g. '2026-07'
count integer not null default 0,
updated_at timestamptz not null default now(),
primary key (user_id, year_month)
);
alter table public.generation_counters enable row level security;
create policy "counters_select_own" on public.generation_counters
for select using (auth.uid() = user_id);
-- Writes to counters go through the backend (service role) so quota can't
-- be tampered with by the client; no client insert/update policy.
-- ---------------------------------------------------------------------------
-- brand_voice_profiles
-- ---------------------------------------------------------------------------
create table if not exists public.brand_voice_profiles (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users (id) on delete cascade,
name text not null,
tone text,
target_audience text,
favorite_words text,
created_at timestamptz not null default now()
);
alter table public.brand_voice_profiles enable row level security;
create policy "brand_voice_select_own" on public.brand_voice_profiles
for select using (auth.uid() = user_id);
create policy "brand_voice_insert_own" on public.brand_voice_profiles
for insert with check (auth.uid() = user_id);
create policy "brand_voice_update_own" on public.brand_voice_profiles
for update using (auth.uid() = user_id);
create policy "brand_voice_delete_own" on public.brand_voice_profiles
for delete using (auth.uid() = user_id);
-- ---------------------------------------------------------------------------
-- listings: generation history
-- ---------------------------------------------------------------------------
create table if not exists public.listings (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users (id) on delete cascade,
brand_voice_profile_id uuid references public.brand_voice_profiles (id) on delete set null,
product_description text not null,
target_keywords text,
titles jsonb not null,
tags jsonb not null,
description text not null,
category_hints jsonb,
provider_used text,
created_at timestamptz not null default now()
);
alter table public.listings enable row level security;
create policy "listings_select_own" on public.listings
for select using (auth.uid() = user_id);
create policy "listings_insert_own" on public.listings
for insert with check (auth.uid() = user_id);
create policy "listings_delete_own" on public.listings
for delete using (auth.uid() = user_id);
create index if not exists listings_user_created_idx on public.listings (user_id, created_at desc);
-- ---------------------------------------------------------------------------
-- shops: for Business tier "multiple shops/clients"
-- ---------------------------------------------------------------------------
create table if not exists public.shops (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users (id) on delete cascade,
name text not null,
created_at timestamptz not null default now()
);
alter table public.shops enable row level security;
create policy "shops_select_own" on public.shops
for select using (auth.uid() = user_id);
create policy "shops_insert_own" on public.shops
for insert with check (auth.uid() = user_id);
create policy "shops_delete_own" on public.shops
for delete using (auth.uid() = user_id);
-- ---------------------------------------------------------------------------
-- helper: touch updated_at on profiles
-- ---------------------------------------------------------------------------
create or replace function public.set_updated_at()
returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
drop trigger if exists profiles_set_updated_at on public.profiles;
create trigger profiles_set_updated_at
before update on public.profiles
for each row execute function public.set_updated_at();
-- ---------------------------------------------------------------------------
-- Reminder (see DEPLOY.md):
-- - Enable "Leaked password protection" under Auth > Policies in the Supabase
-- dashboard - this is a project setting, not a SQL statement.
-- - Provision this project in an EU region (e.g. Frankfurt) at creation time
-- for GDPR data residency - also a dashboard setting, not SQL.
-- ---------------------------------------------------------------------------