mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
52 lines
2.4 KiB
SQL
52 lines
2.4 KiB
SQL
|
|
-- Add missing columns to tasks table
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS client_id uuid REFERENCES public.associations(id);
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS parent_task_id uuid REFERENCES public.tasks(id) ON DELETE CASCADE;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS template_id uuid;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS completed_at timestamptz;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS client_specific boolean DEFAULT false;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS recurring boolean DEFAULT false;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS recurring_interval text;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS recurring_next_date date;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS property_id uuid;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS task_sequence integer;
|
|
ALTER TABLE public.tasks ADD COLUMN IF NOT EXISTS days_until_due integer;
|
|
|
|
-- Create workflow_templates table
|
|
CREATE TABLE IF NOT EXISTS public.workflow_templates (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
description text,
|
|
client_id uuid REFERENCES public.associations(id),
|
|
created_by uuid,
|
|
created_at timestamptz DEFAULT now(),
|
|
updated_at timestamptz DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE public.workflow_templates ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Authenticated users can manage workflow_templates"
|
|
ON public.workflow_templates FOR ALL TO authenticated USING (true) WITH CHECK (true);
|
|
|
|
-- Create workflow_template_tasks table
|
|
CREATE TABLE IF NOT EXISTS public.workflow_template_tasks (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id uuid REFERENCES public.workflow_templates(id) ON DELETE CASCADE NOT NULL,
|
|
task_name text NOT NULL,
|
|
task_description text DEFAULT '',
|
|
days_until_due integer DEFAULT 0,
|
|
task_sequence integer DEFAULT 1,
|
|
priority text DEFAULT 'medium',
|
|
assigned_to text,
|
|
created_at timestamptz DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE public.workflow_template_tasks ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Authenticated users can manage workflow_template_tasks"
|
|
ON public.workflow_template_tasks FOR ALL TO authenticated USING (true) WITH CHECK (true);
|
|
|
|
-- Update template_id FK now that workflow_templates exists
|
|
ALTER TABLE public.tasks DROP CONSTRAINT IF EXISTS tasks_template_id_fkey;
|
|
ALTER TABLE public.tasks ADD CONSTRAINT tasks_template_id_fkey FOREIGN KEY (template_id) REFERENCES public.workflow_templates(id);
|