How to Add a Digital Asset Marketplace to Your App — Copy-Paste Prompts Inside

1-- Seller Stripe Connect accounts
2create table public.sellers (
3  id uuid primary key default gen_random_uuid(),
4  user_id uuid references auth.users(id) on delete cascade not null unique,
5  stripe_account_id text,
6  payout_enabled boolean not null default false,
7  created_at timestamptz not null default now()
8);
9
10alter table public.sellers enable row level security;
11
12create policy "Sellers manage own record"
13  on public.sellers for all
14  using (auth.uid() = user_id)
15  with check (auth.uid() = user_id);
16
17-- Digital assets
18create table public.assets (
19  id uuid primary key default gen_random_uuid(),
20  seller_id uuid references public.sellers(id) on delete cascade not null,
21  title text not null,
22  description text not null,
23  price_cents int not null check (price_cents >= 50),
24  currency text not null default 'usd',
25  file_path text not null,       -- private Supabase Storage path
26  preview_url text not null,     -- public watermarked preview
27  license_type text not null check (license_type in ('personal','commercial','extended')),
28  tags text[] not null default '{}',
29  download_count int not null default 0,
30  rating_avg numeric(3,2),
31  is_published boolean not null default true,
32  created_at timestamptz not null default now()
33);
34
35alter table public.assets enable row level security;
36
37create policy "Anyone can browse published assets"
38  on public.assets for select
39  using (is_published = true);
40
41create policy "Sellers manage own assets"
42  on public.assets for all
43  using (
44    seller_id in (
45      select id from public.sellers where user_id = auth.uid()
46    )
47  );
48
49create index assets_search_idx on public.assets using gin(to_tsvector('english', title || ' ' || description));
50create index assets_tags_idx on public.assets using gin(tags);
51
52-- Purchases (grant download access)
53create table public.purchases (
54  id uuid primary key default gen_random_uuid(),
55  buyer_id uuid references auth.users(id) on delete set null,
56  asset_id uuid references public.assets(id) on delete restrict not null,
57  stripe_payment_intent_id text not null unique,
58  amount_paid int not null,
59  downloaded_at timestamptz,
60  download_count int not null default 0,
61  created_at timestamptz not null default now()
62);
63
64alter table public.purchases enable row level security;
65
66create policy "Buyers see own purchases"
67  on public.purchases for select
68  using (auth.uid() = buyer_id);
69
70create policy "Sellers see purchases of their assets"
71  on public.purchases for select
72  using (
73    asset_id in (
74      select a.id from public.assets a
75      join public.sellers s on s.id = a.seller_id
76      where s.user_id = auth.uid()
77    )
78  );
79
80-- Reviews (buyer-written, post-purchase)
81create table public.reviews (
82  id uuid primary key default gen_random_uuid(),
83  buyer_id uuid references auth.users(id) on delete set null not null,
84  asset_id uuid references public.assets(id) on delete cascade not null,
85  rating int not null check (rating between 1 and 5),
86  body text,
87  created_at timestamptz not null default now(),
88  constraint reviews_unique_buyer_asset unique (buyer_id, asset_id)
89);
90
91alter table public.reviews enable row level security;
92
93create policy "Buyers write own reviews"
94  on public.reviews for insert
95  with check (auth.uid() = buyer_id);
96
97create policy "Anyone reads reviews"
98  on public.reviews for select using (true);
99
100-- Atomic download count increment (call via supabase.rpc)
101create or replace function increment_download_count(asset_id uuid)
102returns void language sql security definer as $$
103  update public.assets set download_count = download_count + 1 where id = asset_id;
104$$;