supabase_schema.sql

-- ================================ IMPORTANT! ========================================= 

-- First,you MUST SET UP CLERK WITH SUPABASE
-- 1) Go to clerk dashboard then integrations then enable integration with supabase 
-- 2) Click Manage Integration and fill all fields needed then copy the domain url.
-- 2) Go to supabase dashboard provider third party and select Clerk and paste the domain url there.

-- ===================================================================================== 

-- TABLE CREATION 

CREATE TABLE IF NOT EXISTS "public"."comments" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "comment" "text" NOT NULL,
    "user_id" "text" NOT NULL,
    "recipe_id" "uuid" NOT NULL
);

CREATE TABLE IF NOT EXISTS "public"."recipes" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "name" character varying NOT NULL,
    "ingredients" "text"[],
    "instructions" "text" NOT NULL,
    "user_id" "text" NOT NULL
);

CREATE TABLE IF NOT EXISTS "public"."recipes_unlocked" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    "recipe_id" "uuid" NOT NULL,
    "user_id" "text" NOT NULL
);


ALTER TABLE ONLY "public"."comments"
    ADD CONSTRAINT "comments_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."recipes"
    ADD CONSTRAINT "recipes_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."recipes_unlocked"
    ADD CONSTRAINT "recipes_unlocked_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."comments"
    ADD CONSTRAINT "comments_recipe_id_fkey" FOREIGN KEY ("recipe_id") REFERENCES "public"."recipes"("id") ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY "public"."recipes_unlocked"
    ADD CONSTRAINT "recipes_unlocked_recipe_id_fkey" FOREIGN KEY ("recipe_id") REFERENCES "public"."recipes"("id") ON UPDATE CASCADE ON DELETE CASCADE;


-- POLICIES

CREATE POLICY "All users can see the recipes" ON "public"."recipes" FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create/modify only own comments" ON "public"."comments" TO "authenticated" USING ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id")) WITH CHECK ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id"));

CREATE POLICY "Authenticated users can create/modify only own recipes" ON "public"."recipes" TO "authenticated" USING ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id")) WITH CHECK ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id"));

CREATE POLICY "Authenticated users can see all comments" ON "public"."comments" FOR SELECT TO "authenticated" USING ((( SELECT "auth"."jwt"() AS "jwt") IS NOT NULL));

CREATE POLICY "Users can view/modify own unlocked recipes" ON "public"."recipes_unlocked" TO "authenticated" USING ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id")) WITH CHECK ((( SELECT ("auth"."jwt"() ->> 'sub'::"text")) = "user_id"));


-- ENABLE ROW LEVEL SECURITY (RLS)

ALTER TABLE "public"."comments" ENABLE ROW LEVEL SECURITY;

ALTER TABLE "public"."recipes" ENABLE ROW LEVEL SECURITY;

ALTER TABLE "public"."recipes_unlocked" ENABLE ROW LEVEL SECURITY;