File size: 3,274 Bytes
6782be3 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | -- Videoscriber: Full schema migration for new Supabase project
-- Run this in Supabase Dashboard > SQL Editor
-- 1. Extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- 2. Profiles table
CREATE TABLE IF NOT EXISTS public.profiles (
id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
email text NOT NULL UNIQUE,
full_name text NOT NULL,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);
-- 3. Transcriptions table
CREATE TABLE IF NOT EXISTS public.transcriptions (
id bigserial PRIMARY KEY,
user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
source_url text NOT NULL,
source_hash text NOT NULL,
transcript text,
created_at timestamptz DEFAULT now(),
UNIQUE (user_id, source_hash)
);
-- 4. Enable RLS on both tables
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.transcriptions ENABLE ROW LEVEL SECURITY;
-- 5. RLS policies for profiles (owner-only)
CREATE POLICY "Users can view own profile"
ON public.profiles FOR SELECT
USING (auth.uid() = id);
CREATE POLICY "Users can insert own profile"
ON public.profiles FOR INSERT
WITH CHECK (auth.uid() = id);
CREATE POLICY "Users can update own profile"
ON public.profiles FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);
-- 6. RLS policies for transcriptions (owner-only)
CREATE POLICY "Users can view own transcriptions"
ON public.transcriptions FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own transcriptions"
ON public.transcriptions FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own transcriptions"
ON public.transcriptions FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own transcriptions"
ON public.transcriptions FOR DELETE
USING (auth.uid() = user_id);
-- 7. Auto-update updated_at on profiles
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_profiles_updated_at
BEFORE UPDATE ON public.profiles
FOR EACH ROW
EXECUTE FUNCTION public.handle_updated_at();
-- 8. Auto-create profile on user signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (id, email, full_name)
VALUES (
NEW.id,
NEW.email,
COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name', '')
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
-- 9. Enforce max 5 transcriptions per user
CREATE OR REPLACE FUNCTION public.enforce_transcription_limit()
RETURNS TRIGGER AS $$
DECLARE
current_count integer;
BEGIN
SELECT COUNT(*) INTO current_count
FROM public.transcriptions
WHERE user_id = NEW.user_id;
IF current_count >= 5 THEN
RAISE EXCEPTION 'Transcription limit reached: max 5 per user';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER enforce_transcription_limit
BEFORE INSERT ON public.transcriptions
FOR EACH ROW
EXECUTE FUNCTION public.enforce_transcription_limit();
|