> ## 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.

# Query Performance Monitoring — Integration

> Feature ID: PF-52 Status: ✅ Complete — 2026-03-05 Spec: PF-52-query-performance-monitoring.md Last Updated: 2026-03-04

**Feature ID:** PF-52\
**Status:** ✅ Complete — 2026-03-05\
**Spec:** [PF-52-query-performance-monitoring.md](../../../specs/pf/specs/PF-52-query-performance-monitoring.md)\
**Last Updated:** 2026-03-04

***

## Overview

PF-52 (Query Performance Monitoring) integrates with PF-36 (System Health Dashboard) by supplying query performance metrics and slow-query data for display on the health dashboard. Integration is **data / platform layer**: PF-52 stores logs in `pf_query_performance_logs` and exposes dashboards; PF-36 may consume aggregated metrics or link to PF-52 dashboards.

***

## Integration Points (from Spec)

| Dependency                      | Pattern         | Purpose                                                                                       |
| ------------------------------- | --------------- | --------------------------------------------------------------------------------------------- |
| PF-36 (System Health Dashboard) | Data / Platform | Query performance metrics and slow-query summary displayed on or linked from health dashboard |

***

## 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](./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](../../../specs/pf/specs/PF-52-query-performance-monitoring.md) 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](./CROSS_CORE_INTEGRATIONS.md)
* [PLATFORM\_INTEGRATION\_LAYERS.md](./PLATFORM_INTEGRATION_LAYERS.md)
* [PF-52 spec § FR-3.2 Query Sanitization](../../../specs/pf/specs/PF-52-query-performance-monitoring.md) — sanitization rules and consumer requirements
* [PF-52 Phase 5-6 Expansion](../../../specs/pf/specs/PF-52-PHASE-5-6-EXPANSION.md) — Phase 5/6 specification