ftb-project-management/docs/performance-hot-queries.md

V2.6 Hot Query Budget And Index Audit

This document records the V2.6 query budget for the large-data harness and the index contracts that keep hot APIs on partition keys.

Commands

Offline contract audit:

node scripts/explain-hot-queries.mjs --dry-run

Database explain audit:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/ftb_pm pnpm perf:explain

Strict plan mode is available for seeded medium/large databases:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/ftb_pm pnpm perf:explain -- --strict-plan

--strict-plan fails on sequential scans. It is useful after the medium fixture is seeded and analyzed, but not required for empty or tiny local databases where PostgreSQL may choose a sequential scan correctly.

Budgets

Area	Query Shape	Budget
health/version	no database query	HTTP p95 <= 500ms
Requirement pool	requirements.product_id + optional filters/search, cursor, created_at sort	HTTP p95 <= 1000ms
Version detail	root version plus child rows by version_id	HTTP p95 <= 1500ms
Workspace	owner/assignee unfinished rows	HTTP p95 <= 1200ms
Xiaobao warnings	non-on_track summaries by score	HTTP p95 <= 1000ms
Xiaobao dirty queue	dirty=true summaries ordered by updated_at	background batch <= 100 rows
Audit search adapter	V2.5 audit table pending; ai_logs is the current AI audit surface	explain-only contract

Required Index Contracts

V2.6 keeps the existing partition prefixes:

• Requirement pool queries must include productId; requirements is hash-partitioned by product_id.
• Version detail child queries must include versionId; dev_tasks, test_cases, and bugs are hash-partitioned by version_id.
• Append evidence tables stay range-partitioned by created_at; background workers must still filter by version_id, user_id, or date before scanning.

Added in migration 20260708030000_v26_hot_query_indexes:

Index	Purpose
projects_product_created_at_idx	product-scoped project list
versions_product_created_at_idx	product-scoped version list
versions_product_project_created_at_idx	project-scoped version list
version_plans_owner_open_due_idx	workspace plan queue
dev_tasks_assignee_open_priority_idx	workspace dev task queue
test_cases_assignee_open_priority_idx	workspace test case queue
bugs_version_status_priority_updated_at_idx	version detail bug ordering
bugs_assignee_open_priority_idx	workspace bug queue
test_cases_version_round_status_updated_at_desc_idx	version detail test case ordering
xiaobao_risk_summaries_warning_score_idx	manager Xiaobao warning list
xiaobao_risk_summaries_dirty_updated_at_idx	background Xiaobao dirty summary queue
work_activities_version_occurred_at_idx	Xiaobao evidence recompute

Existing V2.2 indexes remain part of the contract, including requirement pool indexes, version child indexes, workspace partial indexes, task worklog date indexes, Xiaobao snapshot/insight indexes, and ai_logs operation/status indexes.

Audit Adapter Note

The V2.5 RBAC/audit contract is not present in this branch. V2.6 therefore documents audit.searchAdapter as an adapter target instead of inventing a temporary audit table. When audit lands, the expected query shape should be:

SELECT *
FROM audit_events
WHERE product_id = $1
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT 100;

Expected future index: (product_id, created_at DESC) plus actor/resource indexes required by the audit module. Until then, ai_logs_operation_created_at_idx and ai_logs_status_created_at_idx cover AI operation audit searches only.