162 lines
4.8 KiB
PL/PgSQL
162 lines
4.8 KiB
PL/PgSQL
--------------- ASSISTANTS ---------------
|
|
|
|
-- TABLE --
|
|
|
|
CREATE TABLE IF NOT EXISTS assistants (
|
|
-- ID
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- REQUIRED RELATIONSHIPS
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- OPTIONAL RELATIONSHIPS
|
|
folder_id UUID REFERENCES folders(id) ON DELETE SET NULL,
|
|
|
|
-- METADATA
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ,
|
|
|
|
--SHARING
|
|
sharing TEXT NOT NULL DEFAULT 'private',
|
|
|
|
-- REQUIRED
|
|
context_length INT NOT NULL,
|
|
description TEXT NOT NULL CHECK (char_length(description) <= 500),
|
|
embeddings_provider TEXT NOT NULL CHECK (char_length(embeddings_provider) <= 1000),
|
|
include_profile_context BOOLEAN NOT NULL,
|
|
include_workspace_instructions BOOLEAN NOT NULL,
|
|
model TEXT NOT NULL CHECK (char_length(model) <= 1000),
|
|
name TEXT NOT NULL CHECK (char_length(name) <= 100),
|
|
image_path TEXT NOT NULL CHECK (char_length(image_path) <= 1000), -- file path in assistant_images bucket
|
|
prompt TEXT NOT NULL CHECK (char_length(prompt) <= 100000),
|
|
temperature REAL NOT NULL
|
|
);
|
|
|
|
-- INDEXES --
|
|
|
|
CREATE INDEX assistants_user_id_idx ON assistants(user_id);
|
|
|
|
-- RLS --
|
|
|
|
ALTER TABLE assistants ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Allow full access to own assistants"
|
|
ON assistants
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
CREATE POLICY "Allow view access to non-private assistants"
|
|
ON assistants
|
|
FOR SELECT
|
|
USING (sharing <> 'private');
|
|
|
|
-- FUNCTIONS --
|
|
|
|
CREATE OR REPLACE FUNCTION delete_old_assistant_image()
|
|
RETURNS TRIGGER
|
|
LANGUAGE 'plpgsql'
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
status INT;
|
|
content TEXT;
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
SELECT
|
|
INTO status, content
|
|
result.status, result.content
|
|
FROM public.delete_storage_object_from_bucket('assistant_images', OLD.image_path) AS result;
|
|
IF status <> 200 THEN
|
|
RAISE WARNING 'Could not delete assistant image: % %', status, content;
|
|
END IF;
|
|
END IF;
|
|
IF TG_OP = 'DELETE' THEN
|
|
RETURN OLD;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- TRIGGERS --
|
|
|
|
CREATE TRIGGER update_assistants_updated_at
|
|
BEFORE UPDATE ON assistants
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE update_updated_at_column();
|
|
|
|
CREATE TRIGGER delete_old_assistant_image
|
|
AFTER DELETE ON assistants
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE delete_old_assistant_image();
|
|
|
|
-- STORAGE --
|
|
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('assistant_images', 'assistant_images', false);
|
|
|
|
CREATE OR REPLACE FUNCTION public.non_private_assistant_exists(p_name text)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1
|
|
FROM assistants
|
|
WHERE (id::text = (storage.filename(p_name))) AND sharing <> 'private'
|
|
);
|
|
$$;
|
|
|
|
CREATE POLICY "Allow public read access on non-private assistant images"
|
|
ON storage.objects FOR SELECT TO public
|
|
USING (bucket_id = 'assistant_images' AND public.non_private_assistant_exists(name));
|
|
|
|
CREATE POLICY "Allow insert access to own assistant images"
|
|
ON storage.objects FOR INSERT TO authenticated
|
|
WITH CHECK (bucket_id = 'assistant_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|
|
|
|
CREATE POLICY "Allow update access to own assistant images"
|
|
ON storage.objects FOR UPDATE TO authenticated
|
|
USING (bucket_id = 'assistant_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|
|
|
|
CREATE POLICY "Allow delete access to own assistant images"
|
|
ON storage.objects FOR DELETE TO authenticated
|
|
USING (bucket_id = 'assistant_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|
|
|
|
--------------- ASSISTANT WORKSPACES ---------------
|
|
|
|
-- TABLE --
|
|
|
|
CREATE TABLE IF NOT EXISTS assistant_workspaces (
|
|
-- REQUIRED RELATIONSHIPS
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
assistant_id UUID NOT NULL REFERENCES assistants(id) ON DELETE CASCADE,
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
|
|
PRIMARY KEY(assistant_id, workspace_id),
|
|
|
|
-- METADATA
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- INDEXES --
|
|
|
|
CREATE INDEX assistant_workspaces_user_id_idx ON assistant_workspaces(user_id);
|
|
CREATE INDEX assistant_workspaces_assistant_id_idx ON assistant_workspaces(assistant_id);
|
|
CREATE INDEX assistant_workspaces_workspace_id_idx ON assistant_workspaces(workspace_id);
|
|
|
|
-- RLS --
|
|
|
|
ALTER TABLE assistant_workspaces ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Allow full access to own assistant_workspaces"
|
|
ON assistant_workspaces
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
-- TRIGGERS --
|
|
|
|
CREATE TRIGGER update_assistant_workspaces_updated_at
|
|
BEFORE UPDATE ON assistant_workspaces
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE update_updated_at_column();
|