145 lines
4.3 KiB
PL/PgSQL
145 lines
4.3 KiB
PL/PgSQL
CREATE SCHEMA IF NOT EXISTS auth;
|
|
|
|
CREATE TABLE IF NOT EXISTS auth.users (
|
|
id uuid PRIMARY KEY,
|
|
email text NOT NULL UNIQUE,
|
|
encrypted_password text NOT NULL,
|
|
raw_user_meta_data jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.users (
|
|
id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
display_name text NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth.sessions (
|
|
id uuid PRIMARY KEY,
|
|
session_token text NOT NULL UNIQUE,
|
|
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
expires_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS auth_sessions_user_id_idx ON auth.sessions(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.tablos (
|
|
id uuid PRIMARY KEY,
|
|
owner_id uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
color text NOT NULL,
|
|
status text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS tablos_owner_created_idx
|
|
ON public.tablos (owner_id, created_at DESC)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS public.tasks (
|
|
id uuid PRIMARY KEY,
|
|
tablo_id uuid NOT NULL REFERENCES public.tablos(id) ON DELETE CASCADE,
|
|
owner_id uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
|
title text NOT NULL,
|
|
description text NOT NULL DEFAULT '',
|
|
status text NOT NULL CHECK (status IN ('todo', 'in_progress', 'in_review', 'done')),
|
|
assignee_id uuid NULL REFERENCES public.users(id) ON DELETE SET NULL,
|
|
is_etape boolean NOT NULL DEFAULT false,
|
|
parent_task_id uuid NULL REFERENCES public.tasks(id) ON DELETE SET NULL,
|
|
due_date date NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz NULL,
|
|
CHECK (NOT is_etape OR parent_task_id IS NULL)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS tasks_owner_tablo_deleted_idx
|
|
ON public.tasks (owner_id, tablo_id, deleted_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS tasks_assignee_deleted_idx
|
|
ON public.tasks (assignee_id, deleted_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS tasks_tablo_is_etape_deleted_idx
|
|
ON public.tasks (tablo_id, is_etape, deleted_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS tasks_parent_task_id_idx
|
|
ON public.tasks (parent_task_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS tasks_tablo_due_date_idx
|
|
ON public.tasks (tablo_id, due_date);
|
|
|
|
CREATE OR REPLACE FUNCTION public.validate_task_parent() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
parent_record public.tasks%ROWTYPE;
|
|
BEGIN
|
|
IF NEW.parent_task_id IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF NEW.is_etape THEN
|
|
RAISE EXCEPTION 'Etapes cannot have parent tasks';
|
|
END IF;
|
|
|
|
SELECT *
|
|
INTO parent_record
|
|
FROM public.tasks
|
|
WHERE id = NEW.parent_task_id;
|
|
|
|
IF NOT FOUND OR parent_record.deleted_at IS NOT NULL THEN
|
|
RAISE EXCEPTION 'Parent task is invalid';
|
|
END IF;
|
|
|
|
IF parent_record.is_etape IS NOT TRUE THEN
|
|
RAISE EXCEPTION 'Parent task must be an etape';
|
|
END IF;
|
|
|
|
IF parent_record.owner_id <> NEW.owner_id THEN
|
|
RAISE EXCEPTION 'Parent task owner must match child owner';
|
|
END IF;
|
|
|
|
IF parent_record.tablo_id <> NEW.tablo_id THEN
|
|
RAISE EXCEPTION 'Parent task tablo must match child tablo';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS tasks_validate_parent_trigger ON public.tasks;
|
|
CREATE TRIGGER tasks_validate_parent_trigger
|
|
BEFORE INSERT OR UPDATE ON public.tasks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.validate_task_parent();
|
|
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.users (id, email, created_at, updated_at, display_name)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
NEW.created_at,
|
|
NEW.updated_at,
|
|
COALESCE(NEW.raw_user_meta_data ->> 'display_name', split_part(NEW.email, '@', 1))
|
|
);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.handle_new_user();
|