114 lines
4.7 KiB
SQL
114 lines
4.7 KiB
SQL
-- Migration 019: Create Punchlist tables
|
|
-- Customer-facing snapshot generated from completed sessions
|
|
-- Uses checklist_description field for punchlist-formatted task text
|
|
|
|
-- ==================== SERVICE PUNCHLISTS ====================
|
|
|
|
-- Service punchlist (generated from completed session)
|
|
CREATE TABLE service_punchlists (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
session_id UUID NOT NULL REFERENCES service_sessions(id) ON DELETE CASCADE,
|
|
|
|
-- Denormalized for easy access/reporting
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
account_address_id UUID NOT NULL REFERENCES account_addresses(id) ON DELETE CASCADE,
|
|
date DATE NOT NULL,
|
|
|
|
-- PDF/export tracking
|
|
pdf_url VARCHAR(500),
|
|
exported_at TIMESTAMPTZ,
|
|
|
|
notes TEXT
|
|
);
|
|
|
|
-- Individual task entries on the service punchlist (snapshot of scope tasks)
|
|
CREATE TABLE service_punchlist_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
punchlist_id UUID NOT NULL REFERENCES service_punchlists(id) ON DELETE CASCADE,
|
|
task_id UUID REFERENCES service_scope_tasks(id) ON DELETE SET NULL, -- nullable if task deleted
|
|
|
|
-- Snapshot of task at time of punchlist creation
|
|
checklist_description TEXT NOT NULL,
|
|
"order" INTEGER NOT NULL CHECK ("order" >= 0),
|
|
|
|
-- Completion status
|
|
is_completed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
completed_at TIMESTAMPTZ,
|
|
completed_by_id UUID REFERENCES team_profiles(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- ==================== PROJECT PUNCHLISTS ====================
|
|
|
|
-- Project punchlist (generated from completed session)
|
|
CREATE TABLE project_punchlists (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
session_id UUID NOT NULL REFERENCES project_sessions(id) ON DELETE CASCADE,
|
|
|
|
-- Denormalized for easy access/reporting
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
date DATE NOT NULL,
|
|
|
|
-- PDF/export tracking
|
|
pdf_url VARCHAR(500),
|
|
exported_at TIMESTAMPTZ,
|
|
|
|
notes TEXT
|
|
);
|
|
|
|
-- Individual task entries on the project punchlist (snapshot of scope tasks)
|
|
CREATE TABLE project_punchlist_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
punchlist_id UUID NOT NULL REFERENCES project_punchlists(id) ON DELETE CASCADE,
|
|
task_id UUID REFERENCES project_scope_tasks(id) ON DELETE SET NULL, -- nullable if task deleted
|
|
|
|
-- Snapshot of task at time of punchlist creation
|
|
checklist_description TEXT NOT NULL,
|
|
"order" INTEGER NOT NULL CHECK ("order" >= 0),
|
|
|
|
-- Completion status
|
|
is_completed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
completed_at TIMESTAMPTZ,
|
|
completed_by_id UUID REFERENCES team_profiles(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- ==================== INDEXES ====================
|
|
|
|
-- Service punchlist queries
|
|
CREATE INDEX idx_service_punchlists_session ON service_punchlists(session_id);
|
|
CREATE INDEX idx_service_punchlists_customer ON service_punchlists(customer_id);
|
|
CREATE INDEX idx_service_punchlists_account ON service_punchlists(account_id);
|
|
CREATE INDEX idx_service_punchlists_address ON service_punchlists(account_address_id);
|
|
CREATE INDEX idx_service_punchlists_date ON service_punchlists(date DESC);
|
|
|
|
-- Service punchlist items
|
|
CREATE INDEX idx_service_punchlist_items_punchlist ON service_punchlist_items(punchlist_id);
|
|
CREATE INDEX idx_service_punchlist_items_order ON service_punchlist_items(punchlist_id, "order");
|
|
CREATE INDEX idx_service_punchlist_items_completed ON service_punchlist_items(is_completed);
|
|
|
|
-- Project punchlist queries
|
|
CREATE INDEX idx_project_punchlists_session ON project_punchlists(session_id);
|
|
CREATE INDEX idx_project_punchlists_customer ON project_punchlists(customer_id);
|
|
CREATE INDEX idx_project_punchlists_project ON project_punchlists(project_id);
|
|
CREATE INDEX idx_project_punchlists_date ON project_punchlists(date DESC);
|
|
|
|
-- Project punchlist items
|
|
CREATE INDEX idx_project_punchlist_items_punchlist ON project_punchlist_items(punchlist_id);
|
|
CREATE INDEX idx_project_punchlist_items_order ON project_punchlist_items(punchlist_id, "order");
|
|
CREATE INDEX idx_project_punchlist_items_completed ON project_punchlist_items(is_completed);
|
|
|
|
-- Unique constraint: one punchlist per session
|
|
CREATE UNIQUE INDEX idx_service_punchlist_unique_session ON service_punchlists(session_id);
|
|
CREATE UNIQUE INDEX idx_project_punchlist_unique_session ON project_punchlists(session_id);
|