891 lines
28 KiB
PL/PgSQL
891 lines
28 KiB
PL/PgSQL
create type "public"."conversation-type" as enum ('parametric', 'creative');
|
|
|
|
create type "public"."generation-status" as enum ('pending', 'success', 'failure');
|
|
|
|
create type "public"."mesh_file_type" as enum ('glb', 'stl', 'obj', 'fbx');
|
|
|
|
create type "public"."mesh_model_type" as enum ('quality', 'fast');
|
|
|
|
create type "public"."privacy_type" as enum ('public', 'private');
|
|
|
|
create type "public"."prompt_type" as enum ('mesh', 'image', 'chat');
|
|
|
|
create type "public"."stripe-level" as enum ('pro', 'standard');
|
|
|
|
create type "public"."subscription_level" as enum ('pro', 'standard', 'free');
|
|
|
|
|
|
create table "public"."images" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"status" public."generation-status" not null default 'pending'::public."generation-status",
|
|
"user_id" uuid not null,
|
|
"conversation_id" uuid not null,
|
|
"image_generation_call_id" text,
|
|
"prompt" jsonb not null default '{}'::jsonb
|
|
);
|
|
|
|
|
|
alter table "public"."images" enable row level security;
|
|
|
|
|
|
create table "public"."meshes" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"status" public."generation-status" not null default 'pending'::public."generation-status",
|
|
"user_id" uuid not null,
|
|
"images" uuid[],
|
|
"conversation_id" uuid not null,
|
|
"prompt" jsonb not null default '{}'::jsonb,
|
|
"file_type" public.mesh_file_type not null default 'glb'::public.mesh_file_type
|
|
);
|
|
|
|
|
|
alter table "public"."meshes" enable row level security;
|
|
|
|
|
|
create table "public"."previews" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone not null default now(),
|
|
"status" public."generation-status" not null default 'pending'::public."generation-status",
|
|
"user_id" uuid not null,
|
|
"conversation_id" uuid not null,
|
|
"mesh_id" uuid not null
|
|
);
|
|
|
|
|
|
alter table "public"."previews" enable row level security;
|
|
|
|
|
|
create table "public"."profiles" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone not null default now(),
|
|
"user_id" uuid not null,
|
|
"full_name" text not null,
|
|
"notifications_enabled" boolean not null default false,
|
|
"avatar_path" text
|
|
);
|
|
|
|
|
|
alter table "public"."profiles" enable row level security;
|
|
|
|
|
|
create table "public"."prompts" (
|
|
"id" bigint generated by default as identity not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"user_id" uuid not null,
|
|
"type" public.prompt_type not null default 'chat'::public.prompt_type
|
|
);
|
|
|
|
|
|
alter table "public"."prompts" enable row level security;
|
|
|
|
|
|
create table "public"."subscriptions" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"user_id" uuid not null,
|
|
"stripe_customer_id" text,
|
|
"stripe_subscription_id" text,
|
|
"status" text,
|
|
"created_at" timestamp with time zone default now(),
|
|
"level" public."stripe-level" not null default 'pro'::public."stripe-level"
|
|
);
|
|
|
|
|
|
alter table "public"."subscriptions" enable row level security;
|
|
|
|
|
|
create table "public"."trial_users" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"user_id" uuid not null
|
|
);
|
|
|
|
|
|
alter table "public"."trial_users" enable row level security;
|
|
|
|
alter table "public"."conversations" add column "privacy" public.privacy_type not null default 'private'::public.privacy_type;
|
|
|
|
alter table "public"."conversations" add column "settings" jsonb not null default '{}'::jsonb;
|
|
|
|
alter table "public"."conversations" add column "type" public."conversation-type" not null default 'parametric'::public."conversation-type";
|
|
|
|
alter table "public"."messages" add column "rating" smallint not null default '0'::smallint;
|
|
|
|
CREATE INDEX idx_images_image_generation_call_id ON public.images USING btree (image_generation_call_id);
|
|
|
|
CREATE INDEX idx_subscriptions_stripe_customer_id ON public.subscriptions USING btree (stripe_customer_id);
|
|
|
|
CREATE INDEX idx_subscriptions_stripe_subscription_id ON public.subscriptions USING btree (stripe_subscription_id);
|
|
|
|
CREATE INDEX idx_subscriptions_user_id ON public.subscriptions USING btree (user_id);
|
|
|
|
CREATE UNIQUE INDEX images_pkey ON public.images USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX meshes_pkey ON public.meshes USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX previews_pkey ON public.previews USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX profiles_pkey ON public.profiles USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX prompts_pkey ON public.prompts USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX subscriptions_pkey ON public.subscriptions USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX trial_users_pkey ON public.trial_users USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX trial_users_user_id_key ON public.trial_users USING btree (user_id);
|
|
|
|
alter table "public"."images" add constraint "images_pkey" PRIMARY KEY using index "images_pkey";
|
|
|
|
alter table "public"."meshes" add constraint "meshes_pkey" PRIMARY KEY using index "meshes_pkey";
|
|
|
|
alter table "public"."previews" add constraint "previews_pkey" PRIMARY KEY using index "previews_pkey";
|
|
|
|
alter table "public"."profiles" add constraint "profiles_pkey" PRIMARY KEY using index "profiles_pkey";
|
|
|
|
alter table "public"."prompts" add constraint "prompts_pkey" PRIMARY KEY using index "prompts_pkey";
|
|
|
|
alter table "public"."subscriptions" add constraint "subscriptions_pkey" PRIMARY KEY using index "subscriptions_pkey";
|
|
|
|
alter table "public"."trial_users" add constraint "trial_users_pkey" PRIMARY KEY using index "trial_users_pkey";
|
|
|
|
alter table "public"."images" add constraint "images_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."images" validate constraint "images_conversation_id_fkey";
|
|
|
|
alter table "public"."images" add constraint "images_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."images" validate constraint "images_user_id_fkey";
|
|
|
|
alter table "public"."meshes" add constraint "meshes_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."meshes" validate constraint "meshes_conversation_id_fkey";
|
|
|
|
alter table "public"."meshes" add constraint "meshes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."meshes" validate constraint "meshes_user_id_fkey";
|
|
|
|
alter table "public"."previews" add constraint "previews_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."previews" validate constraint "previews_conversation_id_fkey";
|
|
|
|
alter table "public"."previews" add constraint "previews_mesh_id_fkey" FOREIGN KEY (mesh_id) REFERENCES public.meshes(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."previews" validate constraint "previews_mesh_id_fkey";
|
|
|
|
alter table "public"."previews" add constraint "previews_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."previews" validate constraint "previews_user_id_fkey";
|
|
|
|
alter table "public"."profiles" add constraint "profiles_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."profiles" validate constraint "profiles_user_id_fkey";
|
|
|
|
alter table "public"."prompts" add constraint "prompts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."prompts" validate constraint "prompts_user_id_fkey";
|
|
|
|
alter table "public"."subscriptions" add constraint "subscriptions_status_check" CHECK ((status = ANY (ARRAY['active'::text, 'canceled'::text, 'incomplete'::text, 'incomplete_expired'::text, 'past_due'::text, 'trialing'::text, 'unpaid'::text]))) not valid;
|
|
|
|
alter table "public"."subscriptions" validate constraint "subscriptions_status_check";
|
|
|
|
alter table "public"."subscriptions" add constraint "subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) not valid;
|
|
|
|
alter table "public"."subscriptions" validate constraint "subscriptions_user_id_fkey";
|
|
|
|
alter table "public"."trial_users" add constraint "trial_users_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "public"."trial_users" validate constraint "trial_users_user_id_fkey";
|
|
|
|
alter table "public"."trial_users" add constraint "trial_users_user_id_key" UNIQUE using index "trial_users_user_id_key";
|
|
|
|
set check_function_bodies = off;
|
|
|
|
CREATE OR REPLACE FUNCTION public.handle_mesh_insert()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $function$
|
|
BEGIN
|
|
-- Insert a prompt entry immediately when a mesh is created
|
|
INSERT INTO public.prompts (user_id, type)
|
|
VALUES (NEW.user_id, 'mesh');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION public.handle_mesh_status_update()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $function$
|
|
BEGIN
|
|
-- If mesh status changed to 'failure', remove the most recent mesh prompt for this user
|
|
IF OLD.status != 'failure' AND NEW.status = 'failure' THEN
|
|
DELETE FROM public.prompts
|
|
WHERE user_id = NEW.user_id
|
|
AND type = 'mesh'
|
|
AND id = (
|
|
SELECT id FROM public.prompts
|
|
WHERE user_id = NEW.user_id
|
|
AND type = 'mesh'
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
);
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$
|
|
BEGIN
|
|
INSERT INTO public.profiles (user_id, full_name)
|
|
VALUES (
|
|
NEW.id,
|
|
COALESCE(
|
|
NEW.raw_user_meta_data->>'full_name',
|
|
split_part(NEW.email, '@', 1)
|
|
)
|
|
);
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
create type "public"."user_data" as ("hasTrialed" boolean, "sublevel" public.subscription_level, "generationsRemaining" integer);
|
|
|
|
CREATE OR REPLACE FUNCTION public.user_extradata(user_id_input uuid)
|
|
RETURNS public.user_data
|
|
LANGUAGE plpgsql
|
|
STABLE
|
|
AS $function$
|
|
DECLARE
|
|
hasTrialed boolean;
|
|
userlevel public.subscriptions.level%TYPE;
|
|
userstatus public.subscriptions.status%TYPE;
|
|
daily_generations_used integer;
|
|
monthly_generations_used integer;
|
|
ret user_data;
|
|
BEGIN
|
|
-- Get trial status
|
|
SELECT (
|
|
(SELECT count(*) FROM public.trial_users WHERE user_id = user_id_input) > 0
|
|
) INTO hasTrialed;
|
|
|
|
-- Get subscription info
|
|
SELECT STATUS, LEVEL INTO userstatus, userlevel
|
|
FROM public.subscriptions
|
|
WHERE user_id = user_id_input;
|
|
|
|
-- Get daily generation count (for free tier)
|
|
SELECT COUNT(*) INTO daily_generations_used
|
|
FROM public.prompts
|
|
WHERE user_id = user_id_input
|
|
AND type != 'image'
|
|
AND type != 'chat' -- Exclude parametric generations
|
|
AND created_at >= NOW() - INTERVAL '1 day';
|
|
|
|
-- Get monthly generation count (for standard tier)
|
|
SELECT COUNT(*) INTO monthly_generations_used
|
|
FROM public.prompts
|
|
WHERE user_id = user_id_input
|
|
AND type != 'image'
|
|
AND type != 'chat' -- Exclude parametric generations
|
|
AND created_at >= date_trunc('month', NOW());
|
|
|
|
-- Set return values
|
|
ret."hasTrialed" = hasTrialed;
|
|
|
|
-- Set subscription level
|
|
IF (userstatus = 'active') THEN
|
|
ret."sublevel" = userlevel;
|
|
ELSIF (userstatus = 'trialing') THEN
|
|
ret."sublevel" = 'pro';
|
|
ELSE
|
|
ret."sublevel" = 'free';
|
|
END IF;
|
|
|
|
-- Calculate remaining generations based on subscription level
|
|
IF ret."sublevel" = 'free' THEN
|
|
-- Free tier: 3 generations per day
|
|
ret."generationsRemaining" = GREATEST(3 - daily_generations_used, 0);
|
|
ELSIF ret."sublevel" = 'standard' THEN
|
|
-- Standard tier: 100 generations per month
|
|
ret."generationsRemaining" = GREATEST(100 - monthly_generations_used, 0);
|
|
ELSE
|
|
-- Pro or enterprise: unlimited
|
|
ret."generationsRemaining" = 999999; -- effectively unlimited
|
|
END IF;
|
|
|
|
RETURN ret;
|
|
|
|
EXCEPTION
|
|
WHEN others THEN
|
|
RAISE EXCEPTION 'An error occurred in function user_extradata(): %', SQLERRM;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
grant delete on table "public"."images" to "anon";
|
|
|
|
grant insert on table "public"."images" to "anon";
|
|
|
|
grant references on table "public"."images" to "anon";
|
|
|
|
grant select on table "public"."images" to "anon";
|
|
|
|
grant trigger on table "public"."images" to "anon";
|
|
|
|
grant truncate on table "public"."images" to "anon";
|
|
|
|
grant update on table "public"."images" to "anon";
|
|
|
|
grant delete on table "public"."images" to "authenticated";
|
|
|
|
grant insert on table "public"."images" to "authenticated";
|
|
|
|
grant references on table "public"."images" to "authenticated";
|
|
|
|
grant select on table "public"."images" to "authenticated";
|
|
|
|
grant trigger on table "public"."images" to "authenticated";
|
|
|
|
grant truncate on table "public"."images" to "authenticated";
|
|
|
|
grant update on table "public"."images" to "authenticated";
|
|
|
|
grant delete on table "public"."images" to "service_role";
|
|
|
|
grant insert on table "public"."images" to "service_role";
|
|
|
|
grant references on table "public"."images" to "service_role";
|
|
|
|
grant select on table "public"."images" to "service_role";
|
|
|
|
grant trigger on table "public"."images" to "service_role";
|
|
|
|
grant truncate on table "public"."images" to "service_role";
|
|
|
|
grant update on table "public"."images" to "service_role";
|
|
|
|
grant delete on table "public"."meshes" to "anon";
|
|
|
|
grant insert on table "public"."meshes" to "anon";
|
|
|
|
grant references on table "public"."meshes" to "anon";
|
|
|
|
grant select on table "public"."meshes" to "anon";
|
|
|
|
grant trigger on table "public"."meshes" to "anon";
|
|
|
|
grant truncate on table "public"."meshes" to "anon";
|
|
|
|
grant update on table "public"."meshes" to "anon";
|
|
|
|
grant delete on table "public"."meshes" to "authenticated";
|
|
|
|
grant insert on table "public"."meshes" to "authenticated";
|
|
|
|
grant references on table "public"."meshes" to "authenticated";
|
|
|
|
grant select on table "public"."meshes" to "authenticated";
|
|
|
|
grant trigger on table "public"."meshes" to "authenticated";
|
|
|
|
grant truncate on table "public"."meshes" to "authenticated";
|
|
|
|
grant update on table "public"."meshes" to "authenticated";
|
|
|
|
grant delete on table "public"."meshes" to "service_role";
|
|
|
|
grant insert on table "public"."meshes" to "service_role";
|
|
|
|
grant references on table "public"."meshes" to "service_role";
|
|
|
|
grant select on table "public"."meshes" to "service_role";
|
|
|
|
grant trigger on table "public"."meshes" to "service_role";
|
|
|
|
grant truncate on table "public"."meshes" to "service_role";
|
|
|
|
grant update on table "public"."meshes" to "service_role";
|
|
|
|
grant delete on table "public"."previews" to "anon";
|
|
|
|
grant insert on table "public"."previews" to "anon";
|
|
|
|
grant references on table "public"."previews" to "anon";
|
|
|
|
grant select on table "public"."previews" to "anon";
|
|
|
|
grant trigger on table "public"."previews" to "anon";
|
|
|
|
grant truncate on table "public"."previews" to "anon";
|
|
|
|
grant update on table "public"."previews" to "anon";
|
|
|
|
grant delete on table "public"."previews" to "authenticated";
|
|
|
|
grant insert on table "public"."previews" to "authenticated";
|
|
|
|
grant references on table "public"."previews" to "authenticated";
|
|
|
|
grant select on table "public"."previews" to "authenticated";
|
|
|
|
grant trigger on table "public"."previews" to "authenticated";
|
|
|
|
grant truncate on table "public"."previews" to "authenticated";
|
|
|
|
grant update on table "public"."previews" to "authenticated";
|
|
|
|
grant delete on table "public"."previews" to "service_role";
|
|
|
|
grant insert on table "public"."previews" to "service_role";
|
|
|
|
grant references on table "public"."previews" to "service_role";
|
|
|
|
grant select on table "public"."previews" to "service_role";
|
|
|
|
grant trigger on table "public"."previews" to "service_role";
|
|
|
|
grant truncate on table "public"."previews" to "service_role";
|
|
|
|
grant update on table "public"."previews" to "service_role";
|
|
|
|
grant delete on table "public"."profiles" to "anon";
|
|
|
|
grant insert on table "public"."profiles" to "anon";
|
|
|
|
grant references on table "public"."profiles" to "anon";
|
|
|
|
grant select on table "public"."profiles" to "anon";
|
|
|
|
grant trigger on table "public"."profiles" to "anon";
|
|
|
|
grant truncate on table "public"."profiles" to "anon";
|
|
|
|
grant update on table "public"."profiles" to "anon";
|
|
|
|
grant delete on table "public"."profiles" to "authenticated";
|
|
|
|
grant insert on table "public"."profiles" to "authenticated";
|
|
|
|
grant references on table "public"."profiles" to "authenticated";
|
|
|
|
grant select on table "public"."profiles" to "authenticated";
|
|
|
|
grant trigger on table "public"."profiles" to "authenticated";
|
|
|
|
grant truncate on table "public"."profiles" to "authenticated";
|
|
|
|
grant update on table "public"."profiles" to "authenticated";
|
|
|
|
grant delete on table "public"."profiles" to "service_role";
|
|
|
|
grant insert on table "public"."profiles" to "service_role";
|
|
|
|
grant references on table "public"."profiles" to "service_role";
|
|
|
|
grant select on table "public"."profiles" to "service_role";
|
|
|
|
grant trigger on table "public"."profiles" to "service_role";
|
|
|
|
grant truncate on table "public"."profiles" to "service_role";
|
|
|
|
grant update on table "public"."profiles" to "service_role";
|
|
|
|
grant delete on table "public"."prompts" to "anon";
|
|
|
|
grant insert on table "public"."prompts" to "anon";
|
|
|
|
grant references on table "public"."prompts" to "anon";
|
|
|
|
grant select on table "public"."prompts" to "anon";
|
|
|
|
grant trigger on table "public"."prompts" to "anon";
|
|
|
|
grant truncate on table "public"."prompts" to "anon";
|
|
|
|
grant update on table "public"."prompts" to "anon";
|
|
|
|
grant delete on table "public"."prompts" to "authenticated";
|
|
|
|
grant insert on table "public"."prompts" to "authenticated";
|
|
|
|
grant references on table "public"."prompts" to "authenticated";
|
|
|
|
grant select on table "public"."prompts" to "authenticated";
|
|
|
|
grant trigger on table "public"."prompts" to "authenticated";
|
|
|
|
grant truncate on table "public"."prompts" to "authenticated";
|
|
|
|
grant update on table "public"."prompts" to "authenticated";
|
|
|
|
grant delete on table "public"."prompts" to "service_role";
|
|
|
|
grant insert on table "public"."prompts" to "service_role";
|
|
|
|
grant references on table "public"."prompts" to "service_role";
|
|
|
|
grant select on table "public"."prompts" to "service_role";
|
|
|
|
grant trigger on table "public"."prompts" to "service_role";
|
|
|
|
grant truncate on table "public"."prompts" to "service_role";
|
|
|
|
grant update on table "public"."prompts" to "service_role";
|
|
|
|
grant delete on table "public"."subscriptions" to "anon";
|
|
|
|
grant insert on table "public"."subscriptions" to "anon";
|
|
|
|
grant references on table "public"."subscriptions" to "anon";
|
|
|
|
grant select on table "public"."subscriptions" to "anon";
|
|
|
|
grant trigger on table "public"."subscriptions" to "anon";
|
|
|
|
grant truncate on table "public"."subscriptions" to "anon";
|
|
|
|
grant update on table "public"."subscriptions" to "anon";
|
|
|
|
grant delete on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant insert on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant references on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant select on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant trigger on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant truncate on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant update on table "public"."subscriptions" to "authenticated";
|
|
|
|
grant delete on table "public"."subscriptions" to "service_role";
|
|
|
|
grant insert on table "public"."subscriptions" to "service_role";
|
|
|
|
grant references on table "public"."subscriptions" to "service_role";
|
|
|
|
grant select on table "public"."subscriptions" to "service_role";
|
|
|
|
grant trigger on table "public"."subscriptions" to "service_role";
|
|
|
|
grant truncate on table "public"."subscriptions" to "service_role";
|
|
|
|
grant update on table "public"."subscriptions" to "service_role";
|
|
|
|
grant delete on table "public"."trial_users" to "anon";
|
|
|
|
grant insert on table "public"."trial_users" to "anon";
|
|
|
|
grant references on table "public"."trial_users" to "anon";
|
|
|
|
grant select on table "public"."trial_users" to "anon";
|
|
|
|
grant trigger on table "public"."trial_users" to "anon";
|
|
|
|
grant truncate on table "public"."trial_users" to "anon";
|
|
|
|
grant update on table "public"."trial_users" to "anon";
|
|
|
|
grant delete on table "public"."trial_users" to "authenticated";
|
|
|
|
grant insert on table "public"."trial_users" to "authenticated";
|
|
|
|
grant references on table "public"."trial_users" to "authenticated";
|
|
|
|
grant select on table "public"."trial_users" to "authenticated";
|
|
|
|
grant trigger on table "public"."trial_users" to "authenticated";
|
|
|
|
grant truncate on table "public"."trial_users" to "authenticated";
|
|
|
|
grant update on table "public"."trial_users" to "authenticated";
|
|
|
|
grant delete on table "public"."trial_users" to "service_role";
|
|
|
|
grant insert on table "public"."trial_users" to "service_role";
|
|
|
|
grant references on table "public"."trial_users" to "service_role";
|
|
|
|
grant select on table "public"."trial_users" to "service_role";
|
|
|
|
grant trigger on table "public"."trial_users" to "service_role";
|
|
|
|
grant truncate on table "public"."trial_users" to "service_role";
|
|
|
|
grant update on table "public"."trial_users" to "service_role";
|
|
|
|
|
|
create policy "Anyone can view a public conversation"
|
|
on "public"."conversations"
|
|
as permissive
|
|
for select
|
|
to authenticated, anon
|
|
using ((privacy = 'public'::public.privacy_type));
|
|
|
|
|
|
|
|
create policy "Public conversations images"
|
|
on "public"."images"
|
|
as permissive
|
|
for select
|
|
to authenticated, anon
|
|
using ((EXISTS ( SELECT 1
|
|
FROM public.conversations
|
|
WHERE ((conversations.id = images.conversation_id) AND (conversations.privacy = 'public'::public.privacy_type)))));
|
|
|
|
|
|
|
|
create policy "User can manage their data"
|
|
on "public"."images"
|
|
as permissive
|
|
for all
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = user_id))
|
|
with check ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Everyone can view meshes associated with public conversations"
|
|
on "public"."meshes"
|
|
as permissive
|
|
for select
|
|
to authenticated, anon
|
|
using ((EXISTS ( SELECT 1
|
|
FROM public.conversations
|
|
WHERE ((conversations.id = meshes.conversation_id) AND (conversations.privacy = 'public'::public.privacy_type)))));
|
|
|
|
|
|
|
|
create policy "Users can manage their meshes"
|
|
on "public"."meshes"
|
|
as permissive
|
|
for all
|
|
to public
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Public conversations messages"
|
|
on "public"."messages"
|
|
as permissive
|
|
for select
|
|
to authenticated, anon
|
|
using ((EXISTS ( SELECT 1
|
|
FROM public.conversations
|
|
WHERE ((conversations.id = messages.conversation_id) AND (conversations.privacy = 'public'::public.privacy_type)))));
|
|
|
|
|
|
|
|
create policy "Users can manage their own previews"
|
|
on "public"."previews"
|
|
as permissive
|
|
for all
|
|
to public
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Users can manage their own profile"
|
|
on "public"."profiles"
|
|
as permissive
|
|
for all
|
|
to public
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Users can view their own prompts"
|
|
on "public"."prompts"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Service role can manage all subscriptions"
|
|
on "public"."subscriptions"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true)
|
|
with check (true);
|
|
|
|
|
|
|
|
create policy "Users can read their own subscriptions"
|
|
on "public"."subscriptions"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
|
|
create policy "Enable users to view their own data only"
|
|
on "public"."trial_users"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
CREATE TRIGGER mesh_insert_prompt_trigger AFTER INSERT ON public.meshes FOR EACH ROW EXECUTE FUNCTION public.handle_mesh_insert();
|
|
|
|
CREATE TRIGGER mesh_status_update_trigger AFTER UPDATE ON public.meshes FOR EACH ROW WHEN ((old.status IS DISTINCT FROM new.status)) EXECUTE FUNCTION public.handle_mesh_status_update();
|
|
|
|
CREATE TRIGGER update_previews_updated_at BEFORE UPDATE ON public.previews FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
|
|
|
|
|
|
create policy "Allow public read access to temp multiview images"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using ((bucket_id = 'temp-multiview'::text));
|
|
|
|
|
|
|
|
create policy "Allow service role to delete temp multiview images"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for delete
|
|
to service_role
|
|
using ((bucket_id = 'temp-multiview'::text));
|
|
|
|
|
|
|
|
create policy "Allow service role to upload temp multiview images"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for insert
|
|
to service_role
|
|
with check ((bucket_id = 'temp-multiview'::text));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder meshes_delete"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for delete
|
|
to public
|
|
using (((bucket_id = 'meshes'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder meshes_insert"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for insert
|
|
to public
|
|
with check (((bucket_id = 'meshes'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder meshes_select"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (((bucket_id = 'meshes'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder meshes_update"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for update
|
|
to public
|
|
using (((bucket_id = 'meshes'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder previews_delete"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for delete
|
|
to public
|
|
using (((bucket_id = 'previews'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder previews_insert"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for insert
|
|
to public
|
|
with check (((bucket_id = 'previews'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder previews_select"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (((bucket_id = 'previews'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Give users access to own folder previews_update"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for update
|
|
to public
|
|
using (((bucket_id = 'previews'::text) AND (( SELECT (auth.uid())::text AS uid) = (storage.foldername(name))[1])));
|
|
|
|
|
|
|
|
create policy "Public conversations allow anyone to view images_select"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for select
|
|
to anon, authenticated
|
|
using (((bucket_id = 'images'::text) AND (EXISTS ( SELECT 1
|
|
FROM public.conversations
|
|
WHERE ((conversations.privacy = 'public'::public.privacy_type) AND ((conversations.id)::text = (storage.foldername(objects.name))[2]))))));
|
|
|
|
|
|
|
|
create policy "Public conversations allow anyone to view meshes_select"
|
|
on "storage"."objects"
|
|
as permissive
|
|
for select
|
|
to anon, authenticated
|
|
using (((bucket_id = 'meshes'::text) AND (EXISTS ( SELECT 1
|
|
FROM public.conversations
|
|
WHERE ((conversations.privacy = 'public'::public.privacy_type) AND ((conversations.id)::text = (storage.foldername(objects.name))[2]))))));
|
|
|
|
|
|
|