feat: foundation (schema, Postgres collector, delta snapshots, rollup and retention) #3

2026-10-15T16:23:21Z

KofTwentyTwo commented

2026-10-15 16:23:21 +00:00

(Migrated from github.com)

Phase A of docs/SPEC.md.

17 db_perf_ tables (entities, metadata, possible values, child lists, joins), Liquibase changelog for PostgreSQL and H2 with FKs, indexes and 14 seeded suggestion rules (insert-only).
Permissions db_performance.view|view_live_text|explain|explain_analyze|manage_findings|manage_settings; settings and rules editable only with manage_settings; other tables read-only (qbit writes as system user); settings and rules hidden from the assistant (AiExposure.NONE).
DbStatsProvider SPI, PostgresStatsProvider (pg_stat_statements with reset info and per-entry stats_since, pg_stat_database, tables, indexes, sessions with blocking pids, connections) and an unsupported-vendor provider.
SqlNormalizer (literals, comments, IN/VALUES lists, $n and ? give the same fingerprint).
Delta snapshots with reset detection (calls down, stats_since change, pg_stat_statements_info.stats_reset), baselining of first observations, top-N per snapshot with exact share of DB time, hourly table/index deltas, notable activity (fingerprinted, never raw text), connection samples, 24h statement aggregates.
Daily rollups (idempotent) and retention (30 days detail, 365 days daily, configurable) as scheduled durable-eligible processes; Collect now table action.

Tests: 21 (H2 and PostgreSQL 17 Testcontainers), checkstyle clean, JaCoCo gate met. Implementation by Codex, reviewed by Claude (fixed 24h aggregates decaying for idle statements, header, FK test).

🤖 Generated with Claude Code

Phase A of docs/SPEC.md. - 17 `db_perf_` tables (entities, metadata, possible values, child lists, joins), Liquibase changelog for PostgreSQL and H2 with FKs, indexes and 14 seeded suggestion rules (insert-only). - Permissions `db_performance.view|view_live_text|explain|explain_analyze|manage_findings|manage_settings`; settings and rules editable only with manage_settings; other tables read-only (qbit writes as system user); settings and rules hidden from the assistant (AiExposure.NONE). - `DbStatsProvider` SPI, `PostgresStatsProvider` (pg_stat_statements with reset info and per-entry stats_since, pg_stat_database, tables, indexes, sessions with blocking pids, connections) and an unsupported-vendor provider. - `SqlNormalizer` (literals, comments, IN/VALUES lists, `$n` and `?` give the same fingerprint). - Delta snapshots with reset detection (calls down, stats_since change, pg_stat_statements_info.stats_reset), baselining of first observations, top-N per snapshot with exact share of DB time, hourly table/index deltas, notable activity (fingerprinted, never raw text), connection samples, 24h statement aggregates. - Daily rollups (idempotent) and retention (30 days detail, 365 days daily, configurable) as scheduled durable-eligible processes; Collect now table action. Tests: 21 (H2 and PostgreSQL 17 Testcontainers), checkstyle clean, JaCoCo gate met. Implementation by Codex, reviewed by Claude (fixed 24h aggregates decaying for idle statements, header, FK test). 🤖 Generated with [Claude Code](https://claude.com/claude-code)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: QRun-IO/qbit-db-performance#3