68 lines
3.0 KiB
SQL
68 lines
3.0 KiB
SQL
-- Migration 024: Rename scope templates to service_scope_templates and create project_scope_templates
|
|
-- This provides consistent naming: service_scope_templates vs project_scope_templates
|
|
-- Note: Service tasks have frequency, project tasks do not
|
|
|
|
-- ==================== RENAME SERVICE SCOPE TEMPLATES ====================
|
|
|
|
-- Rename tables for consistency
|
|
ALTER TABLE scope_templates RENAME TO service_scope_templates;
|
|
ALTER TABLE scope_template_areas RENAME TO service_scope_template_areas;
|
|
ALTER TABLE scope_template_tasks RENAME TO service_scope_template_tasks;
|
|
|
|
-- Rename indexes
|
|
ALTER INDEX idx_scope_templates_active RENAME TO idx_service_scope_templates_active;
|
|
ALTER INDEX idx_scope_template_areas_template RENAME TO idx_service_scope_template_areas_template;
|
|
ALTER INDEX idx_scope_template_tasks_area RENAME TO idx_service_scope_template_tasks_area;
|
|
|
|
-- ==================== CREATE PROJECT SCOPE TEMPLATES ====================
|
|
|
|
-- Reusable project scope template
|
|
CREATE TABLE project_scope_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
-- Category within a project scope template (equivalent to Area for services)
|
|
CREATE TABLE project_scope_template_categories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
template_id UUID NOT NULL REFERENCES project_scope_templates(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
"order" INTEGER NOT NULL CHECK ("order" >= 0)
|
|
);
|
|
|
|
-- Task within a project scope template category
|
|
-- Note: No frequency field - project tasks are one-time
|
|
CREATE TABLE project_scope_template_tasks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
category_id UUID NOT NULL REFERENCES project_scope_template_categories(id) ON DELETE CASCADE,
|
|
|
|
-- Three descriptions for different audiences
|
|
scope_description TEXT NOT NULL, -- Customer-facing
|
|
checklist_description TEXT NOT NULL, -- QA/punchlist format
|
|
session_description TEXT NOT NULL, -- Team member work instructions
|
|
|
|
"order" INTEGER NOT NULL CHECK ("order" >= 0),
|
|
estimated_minutes INTEGER CHECK (estimated_minutes >= 0)
|
|
);
|
|
|
|
-- Indexes for project scope templates
|
|
CREATE INDEX idx_project_scope_templates_active ON project_scope_templates(is_active);
|
|
CREATE INDEX idx_project_scope_template_categories_template ON project_scope_template_categories(template_id);
|
|
CREATE INDEX idx_project_scope_template_tasks_category ON project_scope_template_tasks(category_id);
|
|
|
|
-- Note: Data migration from Django tables removed as they don't exist in production.
|
|
-- If needed locally, run INSERT statements manually from core_projectscopetemplate,
|
|
-- core_projectareatemplate, and core_projecttasktemplate tables.
|