bd3e1074a8
Phase 01: Foundation & Auth - 6 plans in 4 execution waves - Wave 1: Project setup (01) + Database schema (02) [parallel] - Wave 2: Email/password auth (03) + Google OAuth (04) [parallel] - Wave 3: Middleware & route protection (05) - Wave 4: Subscription management UI (06) Requirements covered: AUTH-01, AUTH-02, AUTH-03 Ready for execution Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
451 lines
14 KiB
Markdown
451 lines
14 KiB
Markdown
---
|
|
phase: 01-foundation-auth
|
|
plan: 02
|
|
type: execute
|
|
wave: 1
|
|
depends_on: []
|
|
files_modified:
|
|
- supabase/migrations/001_initial_auth_setup.sql
|
|
- supabase/seed.sql
|
|
- docs/DATABASE.md
|
|
autonomous: true
|
|
|
|
must_haves:
|
|
truths:
|
|
- "Plans table exists with Free, Creator, Pro entries"
|
|
- "Profiles table creates automatically on user signup"
|
|
- "RLS policies prevent cross-tenant data access"
|
|
- "User cannot see other users' profiles"
|
|
artifacts:
|
|
- path: "supabase/migrations/001_initial_auth_setup.sql"
|
|
provides: "Database schema and RLS policies"
|
|
contains: "CREATE TABLE plans"
|
|
- path: "docs/DATABASE.md"
|
|
provides: "Schema documentation"
|
|
contains: "plans"
|
|
key_links:
|
|
- from: "profiles table"
|
|
to: "auth.users"
|
|
via: "foreign key + trigger"
|
|
pattern: "REFERENCES auth.users"
|
|
- from: "profiles table"
|
|
to: "plans table"
|
|
via: "plan_id foreign key"
|
|
pattern: "REFERENCES plans"
|
|
---
|
|
|
|
<objective>
|
|
Create the database schema for multi-tenant authentication with subscription plans and Row Level Security policies.
|
|
|
|
Purpose: Establish secure data foundation with tenant isolation from day 1 - this is CRITICAL for security and cannot be retrofitted.
|
|
|
|
Output: SQL migration ready to execute in Supabase, with plans table, profiles table, RLS policies, and auto-profile trigger.
|
|
</objective>
|
|
|
|
<execution_context>
|
|
@C:\Users\miche\.claude/get-shit-done/workflows/execute-plan.md
|
|
@C:\Users\miche\.claude/get-shit-done/templates/summary.md
|
|
</execution_context>
|
|
|
|
<context>
|
|
@.planning/PROJECT.md
|
|
@.planning/ROADMAP.md
|
|
@.planning/phases/01-foundation-auth/01-RESEARCH.md
|
|
</context>
|
|
|
|
<tasks>
|
|
|
|
<task type="auto">
|
|
<name>Task 1: Create database migration with plans and profiles</name>
|
|
<files>
|
|
supabase/migrations/001_initial_auth_setup.sql
|
|
</files>
|
|
<action>
|
|
Create supabase/migrations/ directory if not exists.
|
|
|
|
Create migration file with complete auth schema:
|
|
|
|
```sql
|
|
-- Migration: 001_initial_auth_setup.sql
|
|
-- Purpose: Create plans, profiles tables with RLS for multi-tenant auth
|
|
|
|
-- ============================================
|
|
-- PLANS TABLE
|
|
-- ============================================
|
|
|
|
CREATE TABLE public.plans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL UNIQUE CHECK (name IN ('free', 'creator', 'pro')),
|
|
display_name TEXT NOT NULL,
|
|
display_name_it TEXT NOT NULL, -- Italian display name
|
|
price_monthly INTEGER NOT NULL CHECK (price_monthly >= 0), -- cents
|
|
features JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Insert default plans
|
|
INSERT INTO public.plans (name, display_name, display_name_it, price_monthly, features) VALUES
|
|
('free', 'Free', 'Gratuito', 0, '{
|
|
"posts_per_month": 10,
|
|
"ai_models": ["gpt-4o-mini"],
|
|
"social_accounts": 1,
|
|
"image_generation": false,
|
|
"automation": false
|
|
}'),
|
|
('creator', 'Creator', 'Creator', 1900, '{
|
|
"posts_per_month": 50,
|
|
"ai_models": ["gpt-4o-mini", "gpt-4o", "claude-3-5-sonnet"],
|
|
"social_accounts": 3,
|
|
"image_generation": true,
|
|
"automation": "manual"
|
|
}'),
|
|
('pro', 'Pro', 'Pro', 4900, '{
|
|
"posts_per_month": 200,
|
|
"ai_models": ["gpt-4o-mini", "gpt-4o", "claude-3-5-sonnet", "claude-opus-4"],
|
|
"social_accounts": 10,
|
|
"image_generation": true,
|
|
"automation": "full"
|
|
}');
|
|
|
|
-- ============================================
|
|
-- PROFILES TABLE
|
|
-- ============================================
|
|
|
|
CREATE TABLE public.profiles (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
tenant_id UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
plan_id UUID REFERENCES public.plans(id) NOT NULL DEFAULT (SELECT id FROM public.plans WHERE name = 'free'),
|
|
email TEXT NOT NULL,
|
|
full_name TEXT,
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Performance indexes
|
|
CREATE INDEX idx_profiles_tenant_id ON public.profiles(tenant_id);
|
|
CREATE INDEX idx_profiles_plan_id ON public.profiles(plan_id);
|
|
CREATE INDEX idx_profiles_email ON public.profiles(email);
|
|
|
|
-- ============================================
|
|
-- ROW LEVEL SECURITY
|
|
-- ============================================
|
|
|
|
-- Enable RLS on all tables (CRITICAL - never skip this)
|
|
ALTER TABLE public.plans ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Plans: Everyone can read (public info)
|
|
CREATE POLICY "Plans are viewable by everyone"
|
|
ON public.plans FOR SELECT
|
|
TO authenticated, anon
|
|
USING (true);
|
|
|
|
-- Profiles: Users can only read their own profile
|
|
-- IMPORTANT: Use (SELECT auth.uid()) for 99% performance improvement
|
|
CREATE POLICY "Users can read own profile"
|
|
ON public.profiles FOR SELECT
|
|
TO authenticated
|
|
USING ((SELECT auth.uid()) = id);
|
|
|
|
-- Profiles: Users can update their own profile
|
|
CREATE POLICY "Users can update own profile"
|
|
ON public.profiles FOR UPDATE
|
|
TO authenticated
|
|
USING ((SELECT auth.uid()) = id)
|
|
WITH CHECK ((SELECT auth.uid()) = id);
|
|
|
|
-- Profiles: System can insert (via trigger)
|
|
-- Note: INSERT policy needed because trigger runs as SECURITY DEFINER
|
|
CREATE POLICY "System can insert profiles"
|
|
ON public.profiles FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK ((SELECT auth.uid()) = id);
|
|
|
|
-- ============================================
|
|
-- AUTO-CREATE PROFILE TRIGGER
|
|
-- ============================================
|
|
|
|
-- Function to create profile on user signup
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, email, tenant_id, full_name, avatar_url)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
gen_random_uuid(), -- Each user gets unique tenant_id
|
|
COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name'),
|
|
NEW.raw_user_meta_data->>'avatar_url'
|
|
);
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Trigger on auth.users insert
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.handle_new_user();
|
|
|
|
-- ============================================
|
|
-- HELPER FUNCTIONS
|
|
-- ============================================
|
|
|
|
-- Function to get current user's plan features (for API limit checking)
|
|
CREATE OR REPLACE FUNCTION public.get_user_plan_features()
|
|
RETURNS JSONB
|
|
LANGUAGE SQL STABLE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT p.features
|
|
FROM public.plans p
|
|
INNER JOIN public.profiles pr ON pr.plan_id = p.id
|
|
WHERE pr.id = (SELECT auth.uid());
|
|
$$;
|
|
|
|
-- Function to get current user's plan name
|
|
CREATE OR REPLACE FUNCTION public.get_user_plan_name()
|
|
RETURNS TEXT
|
|
LANGUAGE SQL STABLE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT p.name
|
|
FROM public.plans p
|
|
INNER JOIN public.profiles pr ON pr.plan_id = p.id
|
|
WHERE pr.id = (SELECT auth.uid());
|
|
$$;
|
|
|
|
-- Function to update profile's updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION public.update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER profiles_updated_at
|
|
BEFORE UPDATE ON public.profiles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
-- ============================================
|
|
-- GRANTS
|
|
-- ============================================
|
|
|
|
-- Grant usage to authenticated users
|
|
GRANT USAGE ON SCHEMA public TO authenticated;
|
|
GRANT SELECT ON public.plans TO authenticated;
|
|
GRANT SELECT, UPDATE ON public.profiles TO authenticated;
|
|
GRANT EXECUTE ON FUNCTION public.get_user_plan_features() TO authenticated;
|
|
GRANT EXECUTE ON FUNCTION public.get_user_plan_name() TO authenticated;
|
|
```
|
|
|
|
CRITICAL NOTES from RESEARCH.md:
|
|
- RLS MUST be enabled on EVERY table (CVE-2025-48757 exposed 170+ apps without this)
|
|
- Use (SELECT auth.uid()) not bare auth.uid() for 99% performance improvement
|
|
- Both SELECT and INSERT policies needed for profiles (PostgreSQL returns inserted rows)
|
|
- SECURITY DEFINER on functions to bypass RLS when needed
|
|
</action>
|
|
<verify>
|
|
- File exists at supabase/migrations/001_initial_auth_setup.sql
|
|
- SQL syntax is valid (no obvious errors)
|
|
- All three plans are inserted (free, creator, pro)
|
|
- RLS is enabled on both tables
|
|
- Trigger function exists for auto-profile creation
|
|
</verify>
|
|
<done>
|
|
Complete database migration ready for Supabase execution.
|
|
</done>
|
|
</task>
|
|
|
|
<task type="auto">
|
|
<name>Task 2: Create seed file for development</name>
|
|
<files>
|
|
supabase/seed.sql
|
|
</files>
|
|
<action>
|
|
Create seed file for development/testing (optional data beyond migration):
|
|
|
|
```sql
|
|
-- Seed file for development
|
|
-- Note: Plans are already seeded in migration
|
|
-- This file is for additional test data if needed
|
|
|
|
-- Verify plans exist
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM public.plans WHERE name = 'free') THEN
|
|
RAISE EXCEPTION 'Plans not found - run migration first';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Log seed completion
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE 'Seed completed. Plans available: free, creator, pro';
|
|
END $$;
|
|
```
|
|
|
|
This seed file is minimal because:
|
|
- Plans are created in migration (should always exist)
|
|
- Profiles are created automatically via trigger
|
|
- Test users should be created through the app flow
|
|
</action>
|
|
<verify>
|
|
File exists at supabase/seed.sql
|
|
</verify>
|
|
<done>
|
|
Seed file created for development verification.
|
|
</done>
|
|
</task>
|
|
|
|
<task type="auto">
|
|
<name>Task 3: Document database schema</name>
|
|
<files>
|
|
docs/DATABASE.md
|
|
</files>
|
|
<action>
|
|
Create docs/ directory if not exists.
|
|
|
|
Create DATABASE.md documenting the schema:
|
|
|
|
```markdown
|
|
# 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:**
|
|
```json
|
|
{
|
|
"posts_per_month": 10,
|
|
"ai_models": ["gpt-4o-mini"],
|
|
"social_accounts": 1,
|
|
"image_generation": false,
|
|
"automation": false
|
|
}
|
|
```
|
|
|
|
### 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 |
|
|
|
|
## 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
|
|
|
|
## Helper Functions
|
|
|
|
### get_user_plan_features()
|
|
Returns JSONB of current user's plan features. Use for limit checking.
|
|
|
|
```typescript
|
|
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').
|
|
|
|
## 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
|
|
|
|
## Running Migrations
|
|
|
|
Option 1: Supabase Dashboard
|
|
1. Go to SQL Editor
|
|
2. Paste migration content
|
|
3. Run
|
|
|
|
Option 2: Supabase CLI
|
|
```bash
|
|
supabase db push
|
|
```
|
|
|
|
## 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)
|
|
```
|
|
</action>
|
|
<verify>
|
|
- docs/DATABASE.md exists
|
|
- Documents both tables
|
|
- Includes RLS policies
|
|
- Includes helper functions
|
|
</verify>
|
|
<done>
|
|
Database schema documented for team reference.
|
|
</done>
|
|
</task>
|
|
|
|
</tasks>
|
|
|
|
<verification>
|
|
After all tasks complete:
|
|
1. Migration file exists and contains valid SQL
|
|
2. Plans table has 3 entries (free, creator, pro)
|
|
3. Profiles table has RLS policies
|
|
4. Trigger creates profile on user signup
|
|
5. Helper functions exist for plan checking
|
|
6. Documentation is complete
|
|
</verification>
|
|
|
|
<success_criteria>
|
|
- SQL migration ready to execute in Supabase
|
|
- RLS enabled on ALL tables (security critical)
|
|
- Auto-profile creation via trigger
|
|
- Plan features stored as JSONB for flexibility
|
|
- Helper functions for limit checking
|
|
- Schema documented in docs/DATABASE.md
|
|
</success_criteria>
|
|
|
|
<output>
|
|
After completion, create `.planning/phases/01-foundation-auth/01-02-SUMMARY.md`
|
|
</output>
|