Query Performance Monitoring — Integration

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

​

Overview

​

Integration Points (from Spec)

​

Platform / Data Integration

• Data source: pf_query_performance_logs (RLS: platform admins only). Queries may aggregate by time range, module, or slow-query count.
• Platform layer: If PF-36 embeds a widget, use shared dashboard components and data hooks from platform. No event contract required; read from same table with platform-admin context.
• Documentation: CROSS_CORE_INTEGRATIONS.md — PF-52 → PF-36 entry added.

​

Query text sanitization and PHI/PII safety (required)

• Application-layer sanitization (required): Per PF-52 FR-3.2, query text MUST be sanitized before storage. Implementers MUST apply redaction at the application layer (e.g. in the layer that inserts into pf_query_performance_logs) for email addresses, phone numbers, SSNs, common credential/token patterns, and SQL comments. See PF-52 spec § FR-3.2 for the full sanitization rules.
• Optional DB trigger fallback: The trigger trg_sanitize_pf_query_performance_logs invokes sanitize_query_text() as a fallback; it must not be relied on as the only sanitization. Application-layer redaction remains the primary requirement.
• Consumers use redacted data only: Raw query text is never stored; only redacted_query_text and query_fingerprint are persisted. PF-36 and all other consumers MUST use redacted_query_text for UI, exports, and dashboards.
• PHI/PII safety note: Raw SQL can contain patient names, SSNs, diagnoses, MRNs, and other PHI/PII. These MUST be redacted before storage or display. Failure to sanitize can result in PHI exposure in logs and dashboards. See PF-52 FR-3.2 for patterns and testing requirements.

​

Implementation Notes

• PF-52 dashboards are standalone pages; PF-36 may link to them or show a summary (e.g. slow query count, p95 execution time). Exact widget design is defined in PF-36 or a joint follow-up.
• No event publishing from PF-52; integration is read-only from PF-36 side.

​

Slow-query logging: current state and options

• logSlowQuery (platform): The utility in src/platform/query-performance/log-slow-query.ts sanitizes and inserts into pf_query_performance_logs. From the client, direct insert is blocked by RLS (table is service_role INSERT only). The function is intended for use from an edge function or RPC that has elevated privileges, not from the browser.
• Primary source for slow-query visibility today: Use Supabase Dashboard (Logs / Database) and Postgres pg_stat_statements (or Supabase’s built-in query insights) to identify slow queries until application-level logging is wired.
• Optional future implementation: (A) Client-side timing: wrap Supabase client or a thin query helper to measure request duration; when executionTimeMs >= threshold, call an edge function (or RPC) that inserts into pf_query_performance_logs (fire-and-forget). (B) Ensure the edge path uses the existing sanitization (e.g. sanitizeQueryText) before insert so only redacted_query_text and query_fingerprint are stored.

​

Phase 5–6 Expansion (2026-03-14)

• Phase 5 — Automatic Optimization & Advanced Profiling:
  • Recommendation engine (recommendation-engine.ts): heuristic index suggestions from slow-query fingerprint patterns. Outputs DDL; Apply gated by pf.query_performance.apply_recommendation permission.
  • Execution plan capture (useQueryPlan hook): reads custom_fields.query_plan and wait_events from log rows. Capture is best-effort at logging time.
  • Feature flag: query_performance_recommendations, query_performance_capture_plan in pf_module_settings.
• Phase 6 — Pattern Analysis & Anomaly Detection:
  • Pattern aggregation (useQueryPatterns hook, aggregatePatterns): groups logs by fingerprint, computes p95/p99/max/frequency.
  • Anomaly rules (anomaly-rules.ts): new-pattern detection and execution-time spike detection (configurable threshold).
  • Feature flags: query_performance_pattern_analysis, query_performance_anomaly_new_pattern_alert, query_performance_anomaly_spike_alert, query_performance_anomaly_spike_threshold in pf_module_settings.
  • Anomaly alerts are off by default; all UI gated by flags.

​

Related Docs

• CROSS_CORE_INTEGRATIONS.md
• PLATFORM_INTEGRATION_LAYERS.md
• PF-52 spec § FR-3.2 Query Sanitization — sanitization rules and consumer requirements
• PF-52 Phase 5-6 Expansion — Phase 5/6 specification