75 lines
3.0 KiB
SQL
75 lines
3.0 KiB
SQL
-- Migration 020: Additional constraints discovered from Django audit
|
|
-- Business logic constraints that ensure data integrity
|
|
|
|
-- ==================== SERVICE CONSTRAINTS ====================
|
|
|
|
-- One service per location per day
|
|
-- Prevents duplicate services at the same address on the same date
|
|
CREATE UNIQUE INDEX idx_service_unique_per_address_date
|
|
ON services (account_address_id, date);
|
|
|
|
-- Only one active session per service
|
|
-- A service can only have one session that hasn't ended
|
|
CREATE UNIQUE INDEX idx_service_session_unique_active
|
|
ON service_sessions (service_id) WHERE "end" IS NULL;
|
|
|
|
-- ==================== PROJECT CONSTRAINTS ====================
|
|
|
|
-- Only one active session per project
|
|
-- A project can only have one session that hasn't ended
|
|
CREATE UNIQUE INDEX idx_project_session_unique_active
|
|
ON project_sessions (project_id) WHERE "end" IS NULL;
|
|
|
|
-- ==================== NOTIFICATION CONSTRAINTS ====================
|
|
|
|
-- One delivery attempt per channel per notification
|
|
-- Prevents duplicate delivery attempts for the same notification via the same channel
|
|
CREATE UNIQUE INDEX idx_notification_delivery_unique_channel
|
|
ON notification_deliveries (notification_id, channel);
|
|
|
|
-- ==================== TEAM MEMBER CONSTRAINTS ====================
|
|
|
|
-- One team member assignment per service per profile
|
|
-- Prevents duplicate team member assignments
|
|
CREATE UNIQUE INDEX idx_service_team_member_unique
|
|
ON service_team_members (service_id, team_profile_id);
|
|
|
|
-- One team member assignment per project per profile
|
|
-- Prevents duplicate team member assignments
|
|
CREATE UNIQUE INDEX idx_project_team_member_unique
|
|
ON project_team_members (project_id, team_profile_id);
|
|
|
|
-- ==================== CONVERSATION CONSTRAINTS ====================
|
|
|
|
-- Note: conversation_participants already has UNIQUE (conversation_id, participant_type, participant_id)
|
|
-- defined in migration 014, so no additional constraint needed here.
|
|
|
|
-- ==================== INVOICE CONSTRAINTS ====================
|
|
|
|
-- One revenue association per invoice
|
|
CREATE UNIQUE INDEX idx_invoice_revenue_unique
|
|
ON invoice_revenues (invoice_id, revenue_id);
|
|
|
|
-- One project association per invoice
|
|
CREATE UNIQUE INDEX idx_invoice_project_unique
|
|
ON invoice_projects (invoice_id, project_id);
|
|
|
|
-- ==================== REPORT CONSTRAINTS ====================
|
|
|
|
-- One service association per report
|
|
CREATE UNIQUE INDEX idx_report_service_unique
|
|
ON report_services (report_id, service_id);
|
|
|
|
-- One project association per report
|
|
CREATE UNIQUE INDEX idx_report_project_unique
|
|
ON report_projects (report_id, project_id);
|
|
|
|
-- ==================== SESSION COMPLETED TASKS CONSTRAINTS ====================
|
|
|
|
-- One task completion record per session per task completion
|
|
CREATE UNIQUE INDEX idx_service_session_completed_task_unique
|
|
ON service_session_completed_tasks (session_id, task_completion_id);
|
|
|
|
CREATE UNIQUE INDEX idx_project_session_completed_task_unique
|
|
ON project_session_completed_tasks (session_id, task_completion_id);
|