This schema is designed for a high-scale technical publishing platform. It leverages PostgreSQL's robust relational features and Supabase's native security model (RLS) to handle complex access control—specifically the intersection of **tiered subscriptions** and **one-time purchases (PPV)**. --- ## 1. ENTITY RELATIONSHIP MAP ```mermaid erDiagram profiles ||--o{ articles : "authors" profiles ||--o{ subscriptions : "subscriber" profiles ||--o{ purchases : "buyer" profiles ||--o{ bookmarks : "saves" articles ||--o{ purchases : "sold as PPV" articles ||--o{ bookmarks : "bookmarked by" profiles { uuid id PK string username text bio enum tier "free | pro" } articles { uuid id PK uuid author_id FK text content enum access_level "free | pro | ppv" decimal price } ``` --- ## 2. SQL DDL SCRIPT This script sets up the core architecture, including custom types and automated timestamp management. ```sql -- 1. EXTENSIONS & TYPES create extension if not exists "uuid-ossp"; create type membership_tier as enum ('free', 'pro'); create type article_access as enum ('free', 'pro', 'ppv'); create type publish_status as enum ('draft', 'published', 'archived'); -- 2. TABLES -- Profiles (Extends Supabase Auth) create table profiles ( id uuid references auth.users on delete cascade primary key, username text unique not null, full_name text, avatar_url text, bio text, website text, tier membership_tier default 'free' not null, is_expert boolean default false, created_at timestamptz default now(), updated_at timestamptz default now() ); -- Articles create table articles ( id uuid default uuid_generate_v4() primary key, author_id uuid references profiles(id) on delete cascade not null, title text not null, slug text unique not null, excerpt text, content text, -- Use JSONB if using a block editor like TipTap status publish_status default 'draft' not null, access_level article_access default 'free' not null, price numeric(10, 2) default 0.00 check (price >= 0), created_at timestamptz default now(), updated_at timestamptz default now() ); -- Individual Purchases (Pay-Per-View) create table purchases ( id uuid default uuid_generate_v4() primary key, user_id uuid references profiles(id) on delete cascade not null, article_id uuid references articles(id) on delete cascade not null, amount_paid numeric(10, 2) not null, purchased_at timestamptz default now(), -- Ensure a user can't buy the same article twice unique(user_id, article_id) ); -- Bookmarks create table bookmarks ( user_id uuid references profiles(id) on delete cascade not null, article_id uuid references articles(id) on delete cascade not null, created_at timestamptz default now(), primary key (user_id, article_id) ); -- 3. FUNCTIONS & TRIGGERS -- Automatically update timestamps create or replace function update_updated_at_column() returns trigger as $$ begin new.updated_at = now(); return new; end; $$ language plpgsql; create trigger update_profiles_modtime before update on profiles for each row execute procedure update_updated_at_column(); create trigger update_articles_modtime before update on articles for each row execute procedure update_updated_at_column(); -- Automatically create profile on signup create or replace function public.handle_new_user() returns trigger as $$ begin insert into public.profiles (id, username, full_name, avatar_url) values (new.id, new.raw_user_meta_data->>'username', new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url'); return new; end; $$ language plpgsql security definer; create trigger on_auth_user_created after insert on auth.users for each row execute procedure public.handle_new_user(); ``` --- ## 3. RLS SECURITY POLICIES This is the most critical part: ensuring users can only see content they have paid for or have the right subscription tier for. ```sql -- Enable RLS alter table profiles enable row level security; alter table articles enable row level security; alter table purchases enable row level security; alter table bookmarks enable row level security; -- PROFILES: Public can view, only owner can edit create policy "Public profiles are viewable by everyone" on profiles for select using (true); create policy "Users can update own profile" on profiles for update using (auth.uid() = id); -- ARTICLES: The Core Logic -- 1. Anyone can see the metadata of published articles create policy "Anyone can view published article metadata" on articles for select using (status = 'published'); -- 2. Authors can do everything with their own articles create policy "Authors have full control of own articles" on articles for all using (auth.uid() = author_id); -- PURCHASES: Only the buyer can see their purchase history create policy "Users can view own purchases" on purchases for select using (auth.uid() = user_id); -- BOOKMARKS: Private to user create policy "Users can manage own bookmarks" on bookmarks for all using (auth.uid() = user_id); -- ACCESS CONTROL FUNCTION (For Article Content) -- Use this in your API calls to determine if content should be returned create or replace function can_access_article(article_id uuid) returns boolean as $$ declare v_access_level article_access; v_author_id uuid; v_user_tier membership_tier; v_has_purchased boolean; begin -- Get article details select access_level, author_id into v_access_level, v_author_id from articles where id = article_id; -- Author always has access if auth.uid() = v_author_id then return true; end if; -- Free content if v_access_level = 'free' then return true; end if; -- Pro content (Check user tier) if v_access_level = 'pro' then select tier into v_user_tier from profiles where id = auth.uid(); if v_user_tier = 'pro' then return true; end if; end if; -- PPV content (Check purchases) if v_access_level = 'ppv' then select exists(select 1 from purchases where user_id = auth.uid() and article_id = article_id) into v_has_purchased; if v_has_purchased then return true; end if; end if; return false; end; $$ language plpgsql security definer; ``` --- ## 4. IMPLEMENTATION NOTES * **Content Masking:** Do not return the `content` column in your initial "list articles" query. Only fetch the content after calling the `can_access_article` function or checking the user's status on the client side to save bandwidth and improve security. * **Stripe Integration:** Use **Supabase Edge Functions** to handle Stripe Webhooks. When a `checkout.session.completed` event occurs for a subscription, update `profiles.tier`. For a one-time payment, insert a record into the `purchases` table. * **Creator Dashboard:** To calculate revenue, query the `purchases` table: ```sql select article_id, sum(amount_paid) from purchases where article_id in (select id from articles where author_id = auth.uid()) group by article_id; ``` * **Performance:** I have included a `slug` column. Ensure you add an index on `slug` and `author_id` if you expect millions of articles, as these will be your primary lookup keys. **Would you like me to generate the Stripe Webhook logic (Edge Function) to handle these subscription and PPV updates?**