SQL migrations

Run these SQL blocks in your Supabase SQL Editor (SQL Editor → New query). Run them in order. Each block is safe to re-run — it uses IF NOT EXISTS throughout.

ℹ️

Your app's schemaThis page covers the platform schema (for the validation and report pipeline). Your generated app has its own schema — check your project.md for the data model, and ask your AI IDE to generate those migrations for you.

1. Profiles table

Extends Supabase's built-in auth.users with app-specific user data.

sql

CREATE TABLE IF NOT EXISTS profiles (
  id          UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email       TEXT,
  full_name   TEXT,
  avatar_url  TEXT,
  plan        TEXT NOT NULL DEFAULT 'free',  -- 'free' | 'pro'
  credits     INT  NOT NULL DEFAULT 3,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

2. Reports table

Stores each validation run — the query, status, and scraped post count.

sql

CREATE TABLE IF NOT EXISTS reports (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  query        TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending',
  -- 'pending' | 'scraping' | 'analyzing' | 'complete' | 'error'
  post_count   INT  DEFAULT 0,
  subreddits   TEXT[] DEFAULT '{}',
  error_msg    TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE reports DISABLE ROW LEVEL SECURITY;

3. Scraped posts table

sql

CREATE TABLE IF NOT EXISTS scraped_posts (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id    UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
  post_id      TEXT,
  subreddit    TEXT,
  title        TEXT,
  body         TEXT,
  url          TEXT,
  score        INT  DEFAULT 0,
  num_comments INT  DEFAULT 0,
  top_comments JSONB DEFAULT '[]',
  created_utc  TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE scraped_posts DISABLE ROW LEVEL SECURITY;

4. Report analysis table

sql

CREATE TABLE IF NOT EXISTS report_analysis (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id           UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
  market_signal_score INT  DEFAULT 0,
  sentiment_positive  INT  DEFAULT 0,
  sentiment_neutral   INT  DEFAULT 0,
  sentiment_negative  INT  DEFAULT 0,
  pain_points         JSONB DEFAULT '[]',
  wtp_signals         JSONB DEFAULT '[]',
  key_themes          JSONB DEFAULT '[]',
  subreddit_breakdown JSONB DEFAULT '[]',
  executive_summary   TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE report_analysis DISABLE ROW LEVEL SECURITY;

5. App ideas table

sql

CREATE TABLE IF NOT EXISTS app_ideas (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id        UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
  title            TEXT NOT NULL,
  description      TEXT,
  target_user      TEXT,
  revenue_model    TEXT,
  opportunity_score INT DEFAULT 0,
  mvp_features     JSONB DEFAULT '[]',
  evidence_quotes  JSONB DEFAULT '[]',
  complexity       TEXT, -- 'low' | 'medium' | 'high'
  rank             INT  DEFAULT 0,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE app_ideas DISABLE ROW LEVEL SECURITY;

6. Generated specs table

Stores Claude Code packages — the generated zip and download metadata.

sql

CREATE TABLE IF NOT EXISTS generated_specs (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id        UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  idea_id        UUID REFERENCES app_ideas(id) ON DELETE SET NULL,
  report_id      UUID REFERENCES reports(id) ON DELETE SET NULL,
  spec_markdown  TEXT,
  wizard_answers JSONB NOT NULL DEFAULT '{}',
  skill_count    INT  DEFAULT 0,
  file_path      TEXT,
  download_url   TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE generated_specs DISABLE ROW LEVEL SECURITY;

⚠️

RLS is disabled on these tablesThese tables use the service role key server-side, so RLS is disabled. Your own app's tables should use proper RLS policies — see the Supabase RLS docs.

Verify everything ran

Run this in the SQL Editor to confirm all tables exist:

sql

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

You should see: app_ideas, generated_specs, profiles,report_analysis, reports, scraped_posts.

← PreviousSupabase setup Next →FAQ