feat(engine): column and table profiles, drill-through, privacy guard and exact runs #2

2026-10-13T21:26:37Z

KofTwentyTwo commented

2026-10-13 21:26:37 +00:00

(Migrated from github.com)

Summary

Phase (b), engine part, of QRun-IO/qqq docs/DATA-ANALYSIS.md (§2, §3.7, §4, §8.2): the analysis engine behind the upcoming /qqq/v1/qbit/data-analysis endpoints and the Next profile panel.

Typed results (result/), frontend-agnostic:
- Metric (key, label, value, valueType, formatted, drill, suppressed);
- Drill (table plus QQueryFilter);
- Chart as data (kind, alternatives, axes, series of points with drills);
- ColumnProfile, TableProfile, DistinctValuesPage, DuplicatesResult, run status and the request types.
Profiles (engine/):
- Column profile, per spec §4.1: counts, nulls and blanks, distinct, uniqueness, top 20 with PVS/FK labels translated as the user.
- By field type:
  - numeric: min, max, mean, stddev, percentiles, a 20-bin p1–p99 histogram with below and above bins, zeros, negatives, Tukey outliers;
  - dates: range, auto-bucketed volume, day-of-week and hour patterns, gaps;
  - text: lengths and shape patterns;
  - booleans: true, false and null counts.
- Chart choice with alternatives.
- Table profile with a completeness heatmap, constant and empty flags, and candidate keys (singles and pairs); paged distinct values and duplicates.
Drill-through: every number carries the request filter AND its condition. Bins are half-open with the last bin closed, date ranges are in the session zone, and the original filter is never mutated.
Security (AnalysisSecurity):
- requires the table enabled in config, <table>.analyze plus table READ, the personalized table, field security locks, and analyzable or excluded fields;
- every query runs as the user with PermissionEnforcement.ENFORCE;
- errors never reveal data.
Privacy guard: the per-table minimum group size merges small groups into "Other (k groups below N)" with no drill, and suppresses extremes and percentiles under N and outlier listings whenever N > 0. It runs before caching.
Config: code defaults plus the data_analysis_table_config row, memoized for 60 s.
Runs:
- data version = count + max(modify date or primary key);
- estimate-first above sample_threshold_rows;
- async exact runs carry the user's session and are stored in data_analysis_run as COMPLETE or FAILED;
- the cache is keyed by user, request hash and data version, with a TTL.
DataAnalysisService: the single facade for endpoints and the assistant, registered by the producer.

Stand-in (say so in review): RC.5 core lacks the §3 operators (qqq#1139, aimed at RC.6).

AnalysisBackend is the port.
StandInAnalysisBackend pushes down COUNT, COUNT_DISTINCT, MIN, MAX, AVG, SUM, group-by top-N and null/blank counts. It streams percentiles, stddev, histograms, date buckets, lengths and patterns with the max_rows limit and honest truncated / rowsScanned.
Estimates use the first sample_rows rows by primary key. True sampling arrives with core.
A CoreAnalysisBackend will push everything down once #1139 ships in an RC.

Also works around an RC.5 RDBMS reader quirk: COUNT on a boolean column was read as BOOLEAN, so counts are now typed INTEGER explicitly.

Test plan

./mvnw verify locally with Docker: 70 tests, including 20 against Postgres 17 (Testcontainers) through qqq-backend-module-postgres. Checkstyle clean; JaCoCo above 97% instructions and 100% classes.
One contract suite runs on memory, H2 and Postgres: exact profile numbers on a fixture with nulls, blanks, zeros, negatives, outliers, a date gap, PVS and FK fields
Drill equivalence: every count, top value, bin, bucket, outlier count, duplicate group, null/blank and candidate-key result equals COUNT(drill.filter)
Security:
- no READ, or no .analyze: denied;
- field security lock: refused and not listed;
- record locks restrict every count and drill;
- excluded_fields is honoured.
Privacy guard: merges, suppression, hidden outliers; the cached result_json has no unmerged groups
Estimate vs exact: cache hit, and a cache miss after the data version changes
Config resolution, date auto-bucket rules, shape function, Tukey fences

🤖 Generated with Claude Code

## Summary Phase (b), engine part, of QRun-IO/qqq `docs/DATA-ANALYSIS.md` (§2, §3.7, §4, §8.2): the analysis engine behind the upcoming `/qqq/v1/qbit/data-analysis` endpoints and the Next profile panel. - **Typed results (`result/`)**, frontend-agnostic: - `Metric` (key, label, value, valueType, formatted, drill, suppressed); - `Drill` (table plus QQueryFilter); - `Chart` as data (kind, alternatives, axes, series of points with drills); - `ColumnProfile`, `TableProfile`, `DistinctValuesPage`, `DuplicatesResult`, run status and the request types. - **Profiles (`engine/`):** - Column profile, per spec §4.1: counts, nulls and blanks, distinct, uniqueness, top 20 with PVS/FK labels translated as the user. - By field type: - numeric: min, max, mean, stddev, percentiles, a 20-bin p1–p99 histogram with below and above bins, zeros, negatives, Tukey outliers; - dates: range, auto-bucketed volume, day-of-week and hour patterns, gaps; - text: lengths and shape patterns; - booleans: true, false and null counts. - Chart choice with alternatives. - Table profile with a completeness heatmap, constant and empty flags, and candidate keys (singles and pairs); paged distinct values and duplicates. - **Drill-through:** every number carries the request filter AND its condition. Bins are half-open with the last bin closed, date ranges are in the session zone, and the original filter is never mutated. - **Security (`AnalysisSecurity`):** - requires the table enabled in config, `<table>.analyze` plus table READ, the personalized table, field security locks, and analyzable or excluded fields; - every query runs as the user with `PermissionEnforcement.ENFORCE`; - errors never reveal data. - **Privacy guard:** the per-table minimum group size merges small groups into "Other (k groups below N)" with no drill, and suppresses extremes and percentiles under N and outlier listings whenever N > 0. It runs before caching. - **Config:** code defaults plus the `data_analysis_table_config` row, memoized for 60 s. - **Runs:** - data version = count + max(modify date or primary key); - estimate-first above `sample_threshold_rows`; - async exact runs carry the user's session and are stored in `data_analysis_run` as COMPLETE or FAILED; - the cache is keyed by user, request hash and data version, with a TTL. - **`DataAnalysisService`:** the single facade for endpoints and the assistant, registered by the producer. **Stand-in (say so in review):** RC.5 core lacks the §3 operators (qqq#1139, aimed at RC.6). - `AnalysisBackend` is the port. - `StandInAnalysisBackend` pushes down COUNT, COUNT_DISTINCT, MIN, MAX, AVG, SUM, group-by top-N and null/blank counts. It streams percentiles, stddev, histograms, date buckets, lengths and patterns with the `max_rows` limit and honest `truncated` / `rowsScanned`. - Estimates use the first `sample_rows` rows by primary key. True sampling arrives with core. - A `CoreAnalysisBackend` will push everything down once #1139 ships in an RC. Also works around an RC.5 RDBMS reader quirk: COUNT on a boolean column was read as BOOLEAN, so counts are now typed INTEGER explicitly. ## Test plan - [x] `./mvnw verify` locally with Docker: **70 tests**, including 20 against Postgres 17 (Testcontainers) through `qqq-backend-module-postgres`. Checkstyle clean; JaCoCo above 97% instructions and 100% classes. - [x] One contract suite runs on memory, H2 and Postgres: exact profile numbers on a fixture with nulls, blanks, zeros, negatives, outliers, a date gap, PVS and FK fields - [x] **Drill equivalence:** every count, top value, bin, bucket, outlier count, duplicate group, null/blank and candidate-key result equals `COUNT(drill.filter)` - [x] **Security:** - no READ, or no `.analyze`: denied; - field security lock: refused and not listed; - record locks restrict every count and drill; - `excluded_fields` is honoured. - [x] **Privacy guard:** merges, suppression, hidden outliers; the cached `result_json` has no unmerged groups - [x] **Estimate vs exact:** cache hit, and a cache miss after the data version changes - [x] Config resolution, date auto-bucket rules, shape function, Tukey fences 🤖 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-data-analysis#2