leopost/docs/DATABASE.md

Database Schema - Leopost

Overview

Leopost uses Supabase (PostgreSQL) with Row Level Security for multi-tenant data isolation.

Tables

plans

Subscription plan definitions.

Column	Type	Description
id	UUID	Primary key
name	TEXT	Unique identifier: 'free', 'creator', 'pro'
display_name	TEXT	English display name
display_name_it	TEXT	Italian display name
price_monthly	INTEGER	Price in cents (0, 1900, 4900)
features	JSONB	Feature limits and flags
created_at	TIMESTAMPTZ	Creation timestamp

Features JSONB structure:

{
  "posts_per_month": 10,
  "ai_models": ["gpt-4o-mini"],
  "social_accounts": 1,
  "image_generation": false,
  "automation": false
}

Plan tiers:

Plan	Price	Posts/Month	AI Models	Social Accounts	Images	Automation
Free	0	10	gpt-4o-mini	1	No	No
Creator	19 EUR	50	gpt-4o-mini, gpt-4o, claude-3-5-sonnet	3	Yes	Manual
Pro	49 EUR	200	All models + claude-opus-4	10	Yes	Full

profiles

User profiles with tenant isolation.

Column	Type	Description
id	UUID	Primary key, references auth.users
tenant_id	UUID	Tenant isolation key (auto-generated)
plan_id	UUID	References plans.id, defaults to 'free'
email	TEXT	User email
full_name	TEXT	Optional display name
avatar_url	TEXT	Optional avatar URL
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Indexes:

• idx_profiles_tenant_id - For tenant-scoped queries
• idx_profiles_plan_id - For plan-based queries
• idx_profiles_email - For email lookups

Row Level Security

CRITICAL: RLS is enabled on all tables. Never bypass RLS in client code.

plans

• SELECT: Everyone (authenticated + anon) can read

profiles

• SELECT: Users can only read their own profile
• UPDATE: Users can only update their own profile
• INSERT: System creates via trigger on signup

Performance note: All policies use (SELECT auth.uid()) subquery pattern for 99% performance improvement over bare auth.uid() calls.

Helper Functions

get_user_plan_features()

Returns JSONB of current user's plan features. Use for limit checking.

const { data } = await supabase.rpc('get_user_plan_features')
// Returns: { posts_per_month: 10, ai_models: [...], ... }

get_user_plan_name()

Returns TEXT of current user's plan name ('free', 'creator', 'pro').

const { data } = await supabase.rpc('get_user_plan_name')
// Returns: 'free' | 'creator' | 'pro'

Triggers

on_auth_user_created

Automatically creates a profile when a new user signs up via Supabase Auth.

• Sets tenant_id to new UUID (multi-tenant isolation)
• Sets plan_id to 'free' plan
• Copies email, full_name, avatar_url from auth metadata

profiles_updated_at

Automatically updates updated_at timestamp on profile changes.

Running Migrations

Option 1: Supabase Dashboard

1. Go to SQL Editor
2. Paste migration content from supabase/migrations/001_initial_auth_setup.sql
3. Run

Option 2: Supabase CLI

supabase db push

Option 3: Direct connection

psql $DATABASE_URL -f supabase/migrations/001_initial_auth_setup.sql

Security Notes

• Never use service_role key in client code
• Always verify RLS is enabled after schema changes
• Use Supabase Security Advisor in dashboard before production
• tenant_id is in profiles table, not JWT (simpler approach for v1)
• All helper functions use SECURITY DEFINER to bypass RLS when appropriate

Related Files

• Migration: supabase/migrations/001_initial_auth_setup.sql
• Seed: supabase/seed.sql