Add structured DB query duration metrics and slow-query SIEM integration

[Jagadeeshftw]

Description

PostgreSQL query latency is not tracked in the Prometheus metrics exposed by GET /metrics. SREs cannot build query-latency SLOs or alert on p99 regressions. Additionally, the slow-query log entries emitted by src/db/pool.ts are not in a format suitable for ingestion by common SIEM tools (Splunk, Datadog, Elastic). Structured DB metrics and SIEM-compatible log format must be added.

Requirements and context

• Register a fluxora_db_query_duration_seconds histogram with repository and operation labels in src/metrics/dbMetrics.ts
• Instrument every method in src/db/repositories/streamRepository.ts with histogram observations
• Ensure slow-query log entries include a log_type: slow_query field and follow the OCSF schema for compatibility with SIEM ingestion pipelines
• Must be secure, tested, and documented
• Should be efficient and easy to review

Suggested execution

Fork the repo and create a branch

git checkout -b feature/db-query-duration-metrics

Implement changes

• Update/Write: src/metrics/dbMetrics.ts — register fluxora_db_query_duration_seconds histogram
• Update/Write: src/db/repositories/streamRepository.ts — wrap each method with histogram timing
• Update/Write: src/config/logger.ts — add log_type and OCSF fields to slow-query log entries
• Write comprehensive tests: tests/metrics/dbMetrics.test.ts
• Add documentation: docs/observability.md — document DB metrics, labels, and SIEM log format
• Include clear code comments and types
• Validate security assumptions

Test and commit

• Run tests: pnpm test (or pnpm test:coverage)
• Cover edge cases: successful query records histogram, failed query records histogram with error label, SIEM fields present in slow-query log, no PII in log output
• Include test output and security notes

Example commit message

feat: add DB query duration histogram and SIEM-compatible slow-query logs

Guidelines

• Minimum 95 percent test coverage
• Clear documentation
• Timeframe: 96 hours