57 lines
2.2 KiB
SQL
57 lines
2.2 KiB
SQL
-- Task templates
|
|
CREATE TABLE app.task_template (
|
|
template_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
category app.task_category NOT NULL DEFAULT 'operations',
|
|
is_claimable BOOLEAN NOT NULL DEFAULT false,
|
|
default_location TEXT,
|
|
estimated_minutes INT,
|
|
recurrence_cron TEXT, -- cron expression, e.g. '0 8 * * 1,3,5'
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Task instances
|
|
CREATE TABLE app.task_instance (
|
|
task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id UUID REFERENCES app.task_template(template_id),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
category app.task_category NOT NULL DEFAULT 'operations',
|
|
location TEXT,
|
|
linked_retreat_id UUID,
|
|
linked_stay_id UUID REFERENCES app.stay(stay_id),
|
|
scheduled_start_at TIMESTAMPTZ,
|
|
scheduled_end_at TIMESTAMPTZ,
|
|
status app.task_status NOT NULL DEFAULT 'planned',
|
|
created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_task_instance_status ON app.task_instance(status);
|
|
CREATE INDEX idx_task_instance_category ON app.task_instance(category);
|
|
CREATE INDEX idx_task_instance_template ON app.task_instance(template_id);
|
|
|
|
-- Task assignments
|
|
CREATE TABLE app.task_assignment (
|
|
assignment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
task_instance_id UUID NOT NULL REFERENCES app.task_instance(task_id),
|
|
assigned_user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
assigned_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
status app.task_assignment_status NOT NULL DEFAULT 'proposed',
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_task_assignment_task ON app.task_assignment(task_instance_id);
|
|
CREATE INDEX idx_task_assignment_user ON app.task_assignment(assigned_user_id);
|
|
|
|
SELECT app.enable_audit('task_template', 'template_id');
|
|
SELECT app.enable_audit('task_instance', 'task_id');
|
|
SELECT app.enable_audit('task_assignment', 'assignment_id');
|