27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
|
-- +goose Up
|
||
|
|
-- Hot-path indexes for capabilities resolution + support queues (admin / staff / user).
|
||
|
|
|
||
|
|
-- Active plan lookup: CapabilitiesForCompany / CreditsOverview
|
||
|
|
-- WHERE company_id = ? AND is_active = true ORDER BY created_at DESC LIMIT 1
|
||
|
|
CREATE INDEX IF NOT EXISTS company_plans_company_active_created_idx
|
||
|
|
ON company_plans (company_id, created_at DESC)
|
||
|
|
WHERE is_active = true;
|
||
|
|
|
||
|
|
-- Admin queue: status filter + activity sort (COALESCE last_message / updated)
|
||
|
|
CREATE INDEX IF NOT EXISTS support_tickets_status_activity_idx
|
||
|
|
ON support_tickets (status, (COALESCE(last_message_at, updated_at)) DESC);
|
||
|
|
|
||
|
|
-- Staff inbox: assigned tickets by activity
|
||
|
|
CREATE INDEX IF NOT EXISTS support_tickets_assignee_activity_idx
|
||
|
|
ON support_tickets (assignee_admin_user_id, status, (COALESCE(last_message_at, updated_at)) DESC)
|
||
|
|
WHERE assignee_admin_user_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- User ticket list: company + creator + activity
|
||
|
|
CREATE INDEX IF NOT EXISTS support_tickets_user_activity_idx
|
||
|
|
ON support_tickets (company_id, created_by_user_id, (COALESCE(last_message_at, updated_at)) DESC);
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
DROP INDEX IF EXISTS support_tickets_user_activity_idx;
|
||
|
|
DROP INDEX IF EXISTS support_tickets_assignee_activity_idx;
|
||
|
|
DROP INDEX IF EXISTS support_tickets_status_activity_idx;
|
||
|
|
DROP INDEX IF EXISTS company_plans_company_active_created_idx;
|