6e5244b612
- 00-schema.sql: Schema creation with grants - 01-enums.sql: 8 enums (goal_type, metric_type, period_type, etc.) - 02-tables.sql: 4 tables (definitions, assignments, progress_log, milestone_notifications) - 04-rls.sql: 16 RLS policies for tenant isolation - 05-indexes.sql: Performance indexes Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
110 lines
3.9 KiB
SQL
110 lines
3.9 KiB
SQL
-- ============================================
|
|
-- SAAS-022: Goals Indexes
|
|
-- Performance optimization
|
|
-- ============================================
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- Definitions Indexes
|
|
-- ─────────────────────────────────────────────
|
|
|
|
-- Tenant + status (most common query)
|
|
CREATE INDEX idx_definitions_tenant_status
|
|
ON goals.definitions (tenant_id, status);
|
|
|
|
-- Period queries
|
|
CREATE INDEX idx_definitions_tenant_period
|
|
ON goals.definitions (tenant_id, period, starts_at, ends_at);
|
|
|
|
-- Date range queries
|
|
CREATE INDEX idx_definitions_dates
|
|
ON goals.definitions (starts_at, ends_at);
|
|
|
|
-- Category filtering
|
|
CREATE INDEX idx_definitions_category
|
|
ON goals.definitions (tenant_id, category)
|
|
WHERE category IS NOT NULL;
|
|
|
|
-- Active goals (partial index)
|
|
CREATE INDEX idx_definitions_active
|
|
ON goals.definitions (tenant_id, starts_at, ends_at)
|
|
WHERE status = 'active';
|
|
|
|
-- Created by user
|
|
CREATE INDEX idx_definitions_created_by
|
|
ON goals.definitions (created_by)
|
|
WHERE created_by IS NOT NULL;
|
|
|
|
-- Tags (GIN for JSONB array)
|
|
CREATE INDEX idx_definitions_tags
|
|
ON goals.definitions USING GIN (tags);
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- Assignments Indexes
|
|
-- ─────────────────────────────────────────────
|
|
|
|
-- Tenant + status (most common query)
|
|
CREATE INDEX idx_assignments_tenant_status
|
|
ON goals.assignments (tenant_id, status);
|
|
|
|
-- Definition lookup
|
|
CREATE INDEX idx_assignments_definition
|
|
ON goals.assignments (definition_id);
|
|
|
|
-- User assignments (most common filter)
|
|
CREATE INDEX idx_assignments_user
|
|
ON goals.assignments (user_id, status)
|
|
WHERE user_id IS NOT NULL;
|
|
|
|
-- Team assignments
|
|
CREATE INDEX idx_assignments_team
|
|
ON goals.assignments (team_id, status)
|
|
WHERE team_id IS NOT NULL;
|
|
|
|
-- Active assignments (partial index)
|
|
CREATE INDEX idx_assignments_active
|
|
ON goals.assignments (tenant_id, user_id)
|
|
WHERE status = 'active';
|
|
|
|
-- Progress percentage (for reports)
|
|
CREATE INDEX idx_assignments_progress
|
|
ON goals.assignments (tenant_id, progress_percentage);
|
|
|
|
-- Achieved goals
|
|
CREATE INDEX idx_assignments_achieved
|
|
ON goals.assignments (achieved_at)
|
|
WHERE status = 'achieved';
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- Progress Log Indexes
|
|
-- ─────────────────────────────────────────────
|
|
|
|
-- Assignment history
|
|
CREATE INDEX idx_progress_log_assignment
|
|
ON goals.progress_log (assignment_id, logged_at DESC);
|
|
|
|
-- Tenant + date range
|
|
CREATE INDEX idx_progress_log_tenant_date
|
|
ON goals.progress_log (tenant_id, logged_at);
|
|
|
|
-- Source reference (for deduplication)
|
|
CREATE INDEX idx_progress_log_source_ref
|
|
ON goals.progress_log (source_reference)
|
|
WHERE source_reference IS NOT NULL;
|
|
|
|
-- By user who logged
|
|
CREATE INDEX idx_progress_log_logged_by
|
|
ON goals.progress_log (logged_by)
|
|
WHERE logged_by IS NOT NULL;
|
|
|
|
-- ─────────────────────────────────────────────
|
|
-- Milestone Notifications Indexes
|
|
-- ─────────────────────────────────────────────
|
|
|
|
-- Assignment milestones
|
|
CREATE INDEX idx_milestone_notifications_assignment
|
|
ON goals.milestone_notifications (assignment_id);
|
|
|
|
-- Tenant notifications
|
|
CREATE INDEX idx_milestone_notifications_tenant
|
|
ON goals.milestone_notifications (tenant_id, notified_at);
|