SUPABASE_SETUP.md

Supabase Setup Guide

Note: For a complete, ready-to-run migration script, see migrations/001_initial_schema.sql

The migration script includes all tables, policies, functions, and triggers in a single file that can be run directly in the Supabase SQL Editor.

Quick Setup

1. Run the migration script: migrations/001_initial_schema.sql in your Supabase SQL Editor
2. Create the storage bucket manually (see instructions below)

Database Schema (Manual Setup)

If you prefer to set up manually, here are the individual SQL commands:

1. Create users table (extends auth.users)

-- Create users table to store points, streak, and last upload date
CREATE TABLE public.users (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  email TEXT,
  points INTEGER DEFAULT 0,
  streak INTEGER DEFAULT 0,
  last_upload_date DATE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- Enable Row Level Security
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- Create policy to allow users to read their own data
CREATE POLICY "Users can view own data" ON public.users
  FOR SELECT USING (auth.uid() = id);

-- Create policy to allow users to update their own data
CREATE POLICY "Users can update own data" ON public.users
  FOR UPDATE USING (auth.uid() = id);

-- Create policy to allow reading all users for leaderboard
CREATE POLICY "Anyone can view users for leaderboard" ON public.users
  FOR SELECT USING (true);

-- Create function to automatically create user profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, email, points, streak, last_upload_date)
  VALUES (NEW.id, NEW.email, 0, 0, NULL);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger to call function on new user signup
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

2. Create submissions table

-- Create submissions table
CREATE TABLE public.submissions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  image_url TEXT NOT NULL,
  score INTEGER NOT NULL,
  date DATE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  UNIQUE(user_id, date)
);

-- Enable Row Level Security
ALTER TABLE public.submissions ENABLE ROW LEVEL SECURITY;

-- Create policy to allow users to view their own submissions
CREATE POLICY "Users can view own submissions" ON public.submissions
  FOR SELECT USING (auth.uid() = user_id);

-- Create policy to allow users to insert their own submissions
CREATE POLICY "Users can insert own submissions" ON public.submissions
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Create index for faster queries
CREATE INDEX idx_submissions_user_date ON public.submissions(user_id, date);
CREATE INDEX idx_submissions_date ON public.submissions(date);

4. Create Storage Bucket

1. Go to Storage in Supabase dashboard
2. Create a new bucket named screenshots
3. Make it public (or configure policies as needed)
4. Add policy to allow authenticated users to upload:

-- Allow authenticated users to upload files
CREATE POLICY "Authenticated users can upload screenshots"
ON storage.objects FOR INSERT
TO authenticated
WITH CHECK (bucket_id = 'screenshots');

-- Allow authenticated users to read their own files
CREATE POLICY "Users can read own screenshots"
ON storage.objects FOR SELECT
TO authenticated
USING (bucket_id = 'screenshots');

Environment Variables

Copy .env.local.example to .env.local and fill in your credentials:

• NEXT_PUBLIC_SUPABASE_URL: Your Supabase project URL
• NEXT_PUBLIC_SUPABASE_ANON_KEY: Your Supabase anon/public key
• OPENAI_API_KEY: Your OpenAI API key
• NEXT_PUBLIC_SITE_URL: Your site URL (use http://localhost:3000 for development)