Merge remote-tracking branch 'origin/feature/profile-endpoints' into feature/pivot

POST /tables/{table}/pivot (extension 1.1.0): up to 3 row and 2 column
dimensions with date buckets or nice numeric bins, fields from to-one
exposed joins, count/sum/avg/min/max/distinct/percentile measures,
totals, Other-merging by top-N, the privacy guard and a drill per cell.
FieldAnalysisAccessResolver maps fields to FULL / EXACT_MATCH_ONLY /
NONE (core secured-data tiers when present, FieldSecurityLock otherwise);
only FULL fields are analyzable, drills never name others, and exact-run
cache keys include the user's field-access fingerprint.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

CHANGELOG.md

@@ -6,7 +6,13 @@ Following [Keep a Changelog](https://keepachangelog.com/en/1.1.0/) and [Semantic
 
 ### Added
 
-- `data-analysis` UI API extension 1.0.0: seven session-bound profile, capability, distinct-value, duplicate and run endpoints, typed schemas and a checked OpenAPI fragment.
+- Phase c pivot backend and `POST /qbit/data-analysis/tables/{table}/pivot`, extension version 1.1.0, typed schemas and regenerated OpenAPI fragment.
+- Row/column dimensions, calendar/numeric buckets, seven measure operators, sparse cells, exact Other recomputation, totals, privacy suppression and count-equivalent drills.
+- Exposed-join dimensions and measures with READ/FULL checks; filter-only drills for to-one paths and explicit null-drill warnings for to-many paths whose joined-row counts can exceed record counts.
+- Configurable field-access resolver, cached reflective core tier bridge with legacy fallback, FULL-only capabilities/selections/labels, generated-drill protection and field-access cache fingerprints.
+- Shared memory/H2/PostgreSQL pivot contracts, HTTP drills and errors, reflective resolver/fallback and tier-change cache tests.
+
+- Session-bound profile, capability, distinct-value, duplicate and run endpoints, typed schemas and a checked OpenAPI fragment (introduced in extension 1.0.0).
 - Core table-feature permission registration and capability-directed aggregate pushdown, with native sampling and portable streamed fallbacks.
 - HTTP authorization, drill-to-core-count round trips, async polling, served OpenAPI and assistant discovery tests; H2 streamed and native-compatibility contract variants.
 

README.md

@@ -138,7 +138,7 @@ Count drills combine an untouched copy of the request filter with their producin
 
 ## HTTP endpoints
 
-Extension `data-analysis` version **1.0.0**, prefix `/qbit/data-analysis` inside `/qqq/v1`:
+Extension `data-analysis` version **1.1.0**, prefix `/qbit/data-analysis` inside `/qqq/v1`:
 
 | Method | Path after `/qqq/v1` | Body or purpose |
 | --- | --- | --- |
@@ -147,12 +147,13 @@ Extension `data-analysis` version **1.0.0**, prefix `/qbit/data-analysis` inside
 | POST | `/qbit/data-analysis/tables/{table}/distinct-values` | `{fieldName, filter, page, pageSize, search}` |
 | POST | `/qbit/data-analysis/tables/{table}/table-profile` | `{filter, fields}` |
 | POST | `/qbit/data-analysis/tables/{table}/duplicates` | `{fields, filter, limit}`; default limit 50, maximum 500 |
+| POST | `/qbit/data-analysis/tables/{table}/pivot` | `{rows, columns, values, filter, showTotals, limitGroups}`; 1–3 row dimensions, 0–2 column dimensions, 1–6 measures |
 | POST | `/qbit/data-analysis/runs` | `{operation: COLUMN_PROFILE or TABLE_PROFILE, tableName, request}` returns `{runId, status}` |
 | GET | `/qbit/data-analysis/runs/{runId}` | Status, timestamps, error, typed `columnProfile` or `tableProfile` |
 
 Every operation uses the request session through `DataAnalysisService`. Capabilities returns 200 with `canAnalyze: false` and no fields when denied. Other permission denials return 403, bad bodies or unavailable fields return 400, and unknown tables return 404. Core middleware supplies plain messages in its standard `{error: message}` JSON envelope. Run rows remain owner-locked.
 
-Requests and drills reuse core's `QueryFilter` JSON shape and schema reference. Versioned `DataAnalysis...V1` schemas cover profiles, metrics, drills, charts, series, points, field summaries, distinct and duplicate results, capabilities and runs. The [OpenAPI fragment](src/main/resources/openapi/qqq-v1/data-analysis.yaml) is checked against regeneration. The deployed `/qqq/v1/openapi.json` and `UiApiInvoker.listOperations()` include all seven operations.
+Requests and drills reuse core's `QueryFilter` JSON shape and schema reference. Versioned `DataAnalysis...V1` schemas cover profiles, metrics, drills, charts, series, points, field summaries, distinct and duplicate results, capabilities and runs. The [OpenAPI fragment](src/main/resources/openapi/qqq-v1/data-analysis.yaml) is checked against regeneration. The deployed `/qqq/v1/openapi.json` and `UiApiInvoker.listOperations()` include all eight operations.
 
 Regenerate deliberately after an API change:
 
@@ -160,6 +161,20 @@ Regenerate deliberately after an API change:
 ./mvnw -o -q test -Dtest=DataAnalysisApiTest#contract -Dopenapi.write=true -Dqqq.snapshot.version=4.1.0-DATAANALYSIS-SNAPSHOT
 ```
 
+## Pivot backend and field access
+
+`DataAnalysisService.pivot(PivotRequest)` returns sparse cells indexed into row/column header tuples, aligned totals, typed/formatted metrics, warnings and `asOf`. `COUNT` counts records (including null measure values); other operators require a field. `PERCENTILE` uses continuous interpolation with a fraction from 0 to 1. Calendar buckets use the session timezone. Numeric bins reuse profile nice-edge selection when explicit bounds are omitted; the last interior bin includes its maximum.
+
+The planner first requests count-ordered top-N groups for every dimension (default 50, maximum 200), then dimension counts for privacy/cardinality, one grouped matrix and one aggregate per totals level. Additional aggregates recompute merged Other cells/totals: averages, distinct counts and percentiles cannot safely be combined from displayed cells. Ordinary Other predicates exclude kept values and handle null/empty strings separately. Boolean, temporal and bucketed Other groups use equivalent membership/range predicates for core HTTP compatibility. Cyclic date predicates enumerate observed values through core grouping; memory numeric boundaries reuse the profiles' decimal-scale compatibility helper. Native pivots are exact; bounded core streamed fallback marks partial results as estimates and disables their drills.
+
+`joinTable.field` resolves only through an unambiguous exposed join path. Every table in that path requires READ and the selected field must be FULL; core enforces joins and record locks. The installed data-analysis snapshot does not contain the related-record picker path API, so this implementation uses `getExposedJoins()` and `QueryJoin`. Paths whose every hop is MANY_TO_ONE or ONE_TO_ONE in the analyzed direction support ordinary filter-only drills: core infers the joins from qualified criteria. To-many paths can multiply a base record, so their pivots retain null drills and warn that counts include one row per related table record.
+
+`min_group_size` suppresses rare dimension groups and rare row/column tuples; every measure of a small cell or total is suppressed, including COUNT, with no drill. Totals include the full authorized population. As with existing profiles, separate aggregate calls are not a transactional snapshot.
+
+Hosts can supply `withFieldAnalysisAccessResolver(() -> resolver)`. FULL fields alone appear in capabilities and may be selected. EXACT_MATCH_ONLY and NONE get the same 400 as an unknown field; user-authored filters are passed through for core enforcement. Generated drills using a restricted field (including representative primary keys) are disabled. Related PVS display labels also require FULL access. Exact-run cache identities hash the sorted FULL-field list, the optional core access fingerprint, and related label access.
+
+The default resolver discovers the optional core `fieldaccess.FieldAccessHelper` class and its static methods once. FULL maps to FULL, REVEALABLE to EXACT_MATCH_ONLY, MASKED/HIDDEN to NONE. Without that class, legacy field locks supply FULL/NONE. Replace the reflective calls when the core package ships. Reflection tests use a local stand-in helper rather than shadowing a future real core class at its FQN; the installed snapshot exercises the actual fallback selection. Exact-run persistence continues to support profiles only; pivot results are synchronous.
+
 ## Backend selection
 
 `DataAnalysisService` selects `CoreAnalysisBackend` when the source backend advertises aggregate capabilities, including memory. Supported standard deviation, percentiles, null/blank counts, numeric/date buckets and length statistics execute through core aggregates as the current session. Unsupported primitives delegate to `StandInAnalysisBackend`. Patterns and derived length histograms remain streamed; representative distinct drills and cyclic-date membership use bounded scans.
@@ -180,7 +195,7 @@ Data versions use filtered COUNT plus MAX of a readable modification-date-like f
 4. Review per-table include/exclude sets, limits and minimum group sizes before enabling analysis. Integrate Saved Analyses and Watches with host ESB/Quick Search when those optional host features are installed; this scaffold does not add those dependencies.
 5. Use the Analyses home as the owner of analysis data. Other dashboards should link there until saved-analysis widgets are implemented.
 
-Phase **b** profiles, drill-through, runs/cache and HTTP endpoints are implemented. Remaining work: **b** analysis UI; **c** pivots; **d** comparisons and relationships; **e** watch evaluation and alerts; **f** saved-analysis execution, sharing, stars integration, schedules, chart widgets, full home and exports. The current home summarizes stored rows; the host facade executes profiles. The home does not send deliveries.
+Phase **b** profiles, drill-through, runs/cache and HTTP endpoints, and phase **c** pivot backend are implemented. Remaining work: **b** analysis UI; **c** pivot UI; **d** comparisons and relationships; **e** watch evaluation and alerts; **f** saved-analysis execution, sharing, stars integration, schedules, chart widgets, full home and exports. The current home summarizes stored rows; the host facade executes profiles. The home does not send deliveries.
 
 ## Build
 

src/main/java/com/kingsrook/qbits/dataanalysis/DataAnalysisQBitConfig.java

@@ -26,6 +26,9 @@ import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
+import java.util.function.Supplier;
+import com.kingsrook.qbits.dataanalysis.engine.CoreTierFieldAccessResolver;
+import com.kingsrook.qbits.dataanalysis.engine.FieldAnalysisAccessResolver;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
 import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitConfig;
 
@@ -35,6 +38,7 @@ import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitConfig;
  *******************************************************************************/
 public class DataAnalysisQBitConfig implements QBitConfig
 {
+   private Supplier<FieldAnalysisAccessResolver> fieldAnalysisAccessResolver = CoreTierFieldAccessResolver::defaultResolver;
    private String backendName;
    private String schedulerName;
    private String appName = "dataAnalysis";
@@ -387,4 +391,21 @@ public class DataAnalysisQBitConfig implements QBitConfig
       this.tableDefaults = tableDefaults;
       return (this);
    }
+
+   /*******************************************************************************
+    ** Host override for session-aware field analysis access.
+    *******************************************************************************/
+   public Supplier<FieldAnalysisAccessResolver> getFieldAnalysisAccessResolver()
+   {
+      return (fieldAnalysisAccessResolver);
+   }
+
+   /*******************************************************************************
+    ** Configure an instance supplier; each service resolves one policy instance.
+    *******************************************************************************/
+   public DataAnalysisQBitConfig withFieldAnalysisAccessResolver(Supplier<FieldAnalysisAccessResolver> resolver)
+   {
+      this.fieldAnalysisAccessResolver = java.util.Objects.requireNonNull(resolver);
+      return (this);
+   }
 }

src/main/java/com/kingsrook/qbits/dataanalysis/DataAnalysisService.java

@@ -21,14 +21,21 @@
 package com.kingsrook.qbits.dataanalysis;
 
 import java.util.List;
+import java.util.Map;
+import java.util.Set;
 import java.util.concurrent.Executor;
 import com.kingsrook.qbits.dataanalysis.engine.AnalysisBackend;
+import com.kingsrook.qbits.dataanalysis.engine.AnalysisDrillGuard;
+import com.kingsrook.qbits.dataanalysis.engine.AnalysisLabels;
 import com.kingsrook.qbits.dataanalysis.engine.AnalysisSecurity;
 import com.kingsrook.qbits.dataanalysis.engine.ColumnProfiler;
 import com.kingsrook.qbits.dataanalysis.engine.CoreAnalysisBackend;
 import com.kingsrook.qbits.dataanalysis.engine.DistinctValues;
+import com.kingsrook.qbits.dataanalysis.engine.DrillFilters;
 import com.kingsrook.qbits.dataanalysis.engine.DuplicateFinder;
 import com.kingsrook.qbits.dataanalysis.engine.EffectiveConfigResolver;
+import com.kingsrook.qbits.dataanalysis.engine.FieldAnalysisAccessResolver;
+import com.kingsrook.qbits.dataanalysis.engine.PivotExplorer;
 import com.kingsrook.qbits.dataanalysis.engine.StandInAnalysisBackend;
 import com.kingsrook.qbits.dataanalysis.engine.TableProfiler;
 import com.kingsrook.qbits.dataanalysis.result.AnalysisRequest;
@@ -39,6 +46,8 @@ import com.kingsrook.qbits.dataanalysis.result.DistinctValuesPage;
 import com.kingsrook.qbits.dataanalysis.result.DistinctValuesRequest;
 import com.kingsrook.qbits.dataanalysis.result.DuplicatesRequest;
 import com.kingsrook.qbits.dataanalysis.result.DuplicatesResult;
+import com.kingsrook.qbits.dataanalysis.result.PivotRequest;
+import com.kingsrook.qbits.dataanalysis.result.PivotResult;
 import com.kingsrook.qbits.dataanalysis.result.RunStatus;
 import com.kingsrook.qbits.dataanalysis.result.TableProfile;
 import com.kingsrook.qbits.dataanalysis.result.TableProfileRequest;
@@ -53,6 +62,7 @@ public class DataAnalysisService
 {
    private final EffectiveConfigResolver configs;
    private final AnalysisSecurity security;
+   private final FieldAnalysisAccessResolver fieldAccess;
    private final AnalysisRunService runs;
 
    /*******************************************************************************
@@ -71,7 +81,8 @@ public class DataAnalysisService
    public DataAnalysisService(DataAnalysisQBitConfig config, Executor executor)
    {
       configs = new EffectiveConfigResolver(config);
-      security = new AnalysisSecurity(configs);
+      fieldAccess = config.getFieldAnalysisAccessResolver().get();
+      security = new AnalysisSecurity(configs, fieldAccess);
       runs = new AnalysisRunService(this, executor);
    }
 
@@ -116,7 +127,7 @@ public class DataAnalysisService
    public DistinctValuesPage distinctValues(DistinctValuesRequest request) throws QException
    {
       validate(request);
-      return (new DistinctValues(backend(request.getTableName())).page(request, security.field(request.getTableName(), request.getFieldName()), configs.resolve(request.getTableName())));
+      return (protect(new DistinctValues(backend(request.getTableName()), fieldAccess).page(request, security.field(request.getTableName(), request.getFieldName()), configs.resolve(request.getTableName())), request));
    }
 
 
@@ -127,7 +138,7 @@ public class DataAnalysisService
    public DuplicatesResult duplicates(DuplicatesRequest request) throws QException
    {
       validate(request);
-      return (new DuplicateFinder(backend(request.getTableName())).find(request, configs.resolve(request.getTableName())));
+      return (protect(new DuplicateFinder(backend(request.getTableName())).find(request, configs.resolve(request.getTableName())), request));
    }
 
 
@@ -219,7 +230,7 @@ public class DataAnalysisService
    public ColumnProfile compute(ColumnProfileRequest request, AnalysisBackend backend, AnalysisBackend.Scope scope) throws QException
    {
       validate(request);
-      return (new ColumnProfiler(backend).profile(request, security.field(request.getTableName(), request.getFieldName()), scope, configs.resolve(request.getTableName())));
+      return (protect(new ColumnProfiler(backend, fieldAccess).profile(request, security.field(request.getTableName(), request.getFieldName()), scope, configs.resolve(request.getTableName())), request));
    }
 
 
@@ -232,6 +243,41 @@ public class DataAnalysisService
       validate(request);
       List<QFieldMetaData> fields = security.analyzableFields(request.getTableName()).stream()
          .filter(f -> request.getFields() == null || request.getFields().contains(f.getName())).toList();
-      return (new TableProfiler(backend).profile(request, fields, scope));
+      return (protect(new TableProfiler(backend).profile(request, fields, scope), request));
+   }
+
+   /*******************************************************************************
+    ** Field policy identity is re-evaluated before cache reads and writes.
+    *******************************************************************************/
+   public String fieldAccessFingerprint(String table) throws QException
+   {
+      return (security.accessFingerprint(table));
+   }
+
+   /*******************************************************************************
+    ** Execute a pivot as the current user through core aggregates.
+    *******************************************************************************/
+   public PivotResult pivot(PivotRequest request) throws QException
+   {
+      validate(request);
+      return (new PivotExplorer(security, configs.resolve(request.getTableName())).execute(request));
+   }
+
+   /*******************************************************************************
+    ** Generated primary-key drills also obey the configured field-access seam.
+    *******************************************************************************/
+   private <T> T protect(T result, AnalysisRequest request) throws QException
+   {
+      new AnalysisDrillGuard(Set.copyOf(capabilities(request.getTableName()).getFields()),
+         DrillFilters.restoreTypes(request.getTableName(), request.getFilter())).apply(result);
+      return (result);
+   }
+
+   /*******************************************************************************
+    ** Include resolved tiers of related label fields in cache identity.
+    *******************************************************************************/
+   public Map<String, Object> fieldAccessPolicy(String table) throws QException
+   {
+      return (AnalysisLabels.policy(table, fieldAccess));
    }
 }

src/main/java/com/kingsrook/qbits/dataanalysis/api/DataAnalysisApi.java

@@ -30,6 +30,7 @@ import com.kingsrook.qbits.dataanalysis.result.ChartKind;
 import com.kingsrook.qbits.dataanalysis.result.ColumnProfileRequest;
 import com.kingsrook.qbits.dataanalysis.result.DistinctValuesRequest;
 import com.kingsrook.qbits.dataanalysis.result.DuplicatesRequest;
+import com.kingsrook.qbits.dataanalysis.result.PivotRequest;
 import com.kingsrook.qbits.dataanalysis.result.TableProfileRequest;
 import com.kingsrook.qqq.backend.core.context.QContext;
 import com.kingsrook.qqq.backend.core.exceptions.QBadRequestException;
@@ -70,10 +71,10 @@ public class DataAnalysisApi
     *******************************************************************************/
    public static MiddlewareSpecExtension extension()
    {
-      return (new MiddlewareSpecExtension().withName("data-analysis").withSemanticVersion("1.0.0")
-         .withDescription("Profiles, distinct values, duplicate groups and exact analysis runs as the signed-in user.")
+      return (new MiddlewareSpecExtension().withName("data-analysis").withSemanticVersion("1.1.0")
+         .withDescription("Profiles, pivots, distinct values, duplicate groups and exact analysis runs as the signed-in user.")
          .withSpec(CapabilitiesSpec.class).withSpec(ColumnProfileSpec.class).withSpec(DistinctValuesSpec.class)
-         .withSpec(TableProfileSpec.class).withSpec(DuplicatesSpec.class).withSpec(StartRunSpec.class).withSpec(GetRunSpec.class));
+         .withSpec(PivotSpec.class).withSpec(TableProfileSpec.class).withSpec(DuplicatesSpec.class).withSpec(StartRunSpec.class).withSpec(GetRunSpec.class));
    }
 
    /*******************************************************************************
@@ -184,7 +185,7 @@ public class DataAnalysisApi
             throw new IllegalArgumentException("exact must be a boolean.");
          }
          QQueryFilter filter = body.get("filter") == null ? null : JsonUtils.toObject(JsonUtils.toJson(body.get("filter")), QQueryFilter.class);
-         validateFilter(filter, readable);
+         validateFilter(filter);
          List<String> fields = null;
          if(body.get("fields") != null)
          {
@@ -224,7 +225,7 @@ public class DataAnalysisApi
    /*******************************************************************************
     ** Bad filter fields fail before any aggregate or cached result is consulted.
     *******************************************************************************/
-   private static void validateFilter(QQueryFilter filter, List<String> readable)
+   private static void validateFilter(QQueryFilter filter)
    {
       if(filter == null)
       {
@@ -232,13 +233,12 @@ public class DataAnalysisApi
       }
       for(var criterion : filter.getCriteria())
       {
-         if(criterion.getOperator() == null || !readable.contains(criterion.getFieldName())
-            || criterion.getOtherFieldName() != null && !readable.contains(criterion.getOtherFieldName()))
+         if(criterion.getOperator() == null || criterion.getFieldName() == null)
          {
             throw new IllegalArgumentException("Choose readable filter fields and a valid operator.");
          }
       }
-      filter.getSubFilters().forEach(sub -> validateFilter(sub, readable));
+      filter.getSubFilters().forEach(sub -> validateFilter(sub));
    }
 
    /*******************************************************************************
@@ -504,4 +504,77 @@ public class DataAnalysisApi
       };
       return (result);
    }
+
+   /*******************************************************************************
+    ** Group records by row and column dimensions.
+    *******************************************************************************/
+   public static class PivotSpec extends ProfileSpec
+   {
+      /*******************************************************************************
+       ** Publish assistant-friendly grouping and measure capabilities.
+       *******************************************************************************/
+      public PivotSpec()
+      {
+         super("/tables/{table}/pivot", HttpMethod.POST, "Explore a pivot table",
+            "Group records by up to 3 row and 2 column fields (with date or numeric buckets) and compute counts, sums, averages, min/max, distinct counts or percentiles per cell; every cell has a drill-through filter.", "PivotRequest", "Pivot");
+      }
+
+      /*******************************************************************************
+       ** Strict body validation precedes the session-bound engine.
+       *******************************************************************************/
+      @Override
+      protected Object handle(JsonInput input) throws Exception
+      {
+         String tableName = input.getPathParam("table");
+         table(tableName);
+         DataAnalysisRuntime.service().capabilities(tableName);
+         PivotRequest pivot;
+         try
+         {
+            Map<String, Object> body = input.getBody();
+            integer(body, "limitGroups", 50, 1, 200);
+            if(body.containsKey("showTotals") && !(body.get("showTotals") instanceof Boolean))
+            {
+               throw new IllegalArgumentException("showTotals must be a boolean.");
+            }
+            for(String axis : List.of("rows", "columns"))
+            {
+               if(body.containsKey(axis))
+               {
+                  if(!(body.get(axis) instanceof List<?> dimensions))
+                  {
+                     throw new IllegalArgumentException("Dimensions must be arrays.");
+                  }
+                  for(Object dimension : dimensions)
+                  {
+                     if(!(dimension instanceof Map<?, ?> map) || !(map.get("field") instanceof String))
+                     {
+                        throw new IllegalArgumentException("Each dimension requires a field name.");
+                     }
+                     if(map.get("numericBins") instanceof Map<?, ?> bins)
+                     {
+                        Object count = bins.get("count");
+                        if(count != null && (!(count instanceof Number number) || number.doubleValue() != number.intValue()))
+                        {
+                           throw new IllegalArgumentException("Bin count must be an integer.");
+                        }
+                     }
+                  }
+               }
+            }
+            if(!(body.get("values") instanceof List<?> values) || values.stream().anyMatch(v -> !(v instanceof Map<?, ?> m)
+               || !(m.get("operator") instanceof String) || m.get("field") != null && !(m.get("field") instanceof String)))
+            {
+               throw new IllegalArgumentException("Measures require an operator and optional field name.");
+            }
+            pivot = JsonUtils.toObject(JsonUtils.toJson(body), PivotRequest.class).withTableName(tableName);
+            validateFilter(pivot.getFilter());
+         }
+         catch(Exception e)
+         {
+            throw new QBadRequestException("Invalid pivot request.");
+         }
+         return (transport(DataAnalysisRuntime.service().pivot(pivot), DataAnalysisSchemas.DataAnalysisPivotV1.class));
+      }
+   }
 }

src/main/java/com/kingsrook/qbits/dataanalysis/api/DataAnalysisSchemas.java

@@ -106,6 +106,14 @@ public class DataAnalysisSchemas
       result.put("DataAnalysisLimitsV1", new DataAnalysisLimitsV1().toSchema());
       result.put("DataAnalysisStartRunRequestV1", new DataAnalysisStartRunRequestV1().toSchema());
       result.put("DataAnalysisRunStartedV1", new DataAnalysisRunStartedV1().toSchema());
+      result.put("DataAnalysisPivotRequestV1", new DataAnalysisPivotRequestV1().toSchema());
+      result.put("DataAnalysisPivotDimensionV1", new DataAnalysisPivotDimensionV1().toSchema());
+      result.put("DataAnalysisPivotNumericBinsV1", new DataAnalysisPivotNumericBinsV1().toSchema());
+      result.put("DataAnalysisPivotMeasureV1", new DataAnalysisPivotMeasureV1().toSchema());
+      result.put("DataAnalysisPivotHeaderV1", new DataAnalysisPivotHeaderV1().toSchema());
+      result.put("DataAnalysisPivotMeasureInfoV1", new DataAnalysisPivotMeasureInfoV1().toSchema());
+      result.put("DataAnalysisPivotCellV1", new DataAnalysisPivotCellV1().toSchema());
+      result.put("DataAnalysisPivotV1", new DataAnalysisPivotV1().toSchema());
       return (result);
    }
 
@@ -1146,4 +1154,281 @@ public class DataAnalysisSchemas
          return (new Schema().withType(Type.OBJECT).withNullable(true).withProperties(properties));
       }
    }
+
+   /*******************************************************************************
+    ** Nested arrays share the same explicit schema construction.
+    *******************************************************************************/
+   private static Schema array(Schema items)
+   {
+      return (new Schema().withType(Type.ARRAY).withItems(items));
+   }
+
+   /*******************************************************************************
+    ** PivotRequest version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotRequestV1 implements ToSchema
+   {
+      @OpenAPIDescription("User query filter, passed through for core enforcement.")
+      public QQueryFilter filter;
+
+      @OpenAPIDescription("One to three row dimensions.")
+      public List<DataAnalysisPivotDimensionV1> rows;
+
+      @OpenAPIDescription("Zero to two column dimensions; defaults to empty.")
+      public List<DataAnalysisPivotDimensionV1> columns;
+
+      @OpenAPIDescription("One to six measures.")
+      public List<DataAnalysisPivotMeasureV1> values;
+
+      @OpenAPIDescription("Include row, column and grand totals; defaults to true.")
+      public Boolean showTotals;
+
+      @OpenAPIDescription("Kept groups per dimension; default 50, maximum 200.")
+      public Integer limitGroups;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("filter", ref("QueryFilter").withDescription("User query filter, passed through for core enforcement."));
+         properties.put("rows", array(ref("DataAnalysisPivotDimensionV1")).withDescription("One to three row dimensions."));
+         properties.put("columns", array(ref("DataAnalysisPivotDimensionV1")).withDescription("Zero to two column dimensions; defaults to empty."));
+         properties.put("values", array(ref("DataAnalysisPivotMeasureV1")).withDescription("One to six measures."));
+         properties.put("showTotals", new Schema().withType(Type.BOOLEAN).withDescription("Include row, column and grand totals; defaults to true."));
+         properties.put("limitGroups", new Schema().withType(Type.INTEGER).withDescription("Kept groups per dimension; default 50, maximum 200."));
+         return (new RequiredSchema(List.of("rows", "values")).withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotDimension version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotDimensionV1 implements ToSchema
+   {
+      @OpenAPIDescription("FULL field name, optionally joinTable.field through an exposed join.")
+      public String field;
+
+      @OpenAPIDescription("Optional calendar or cyclic bucket in the session timezone.")
+      public String dateBucket;
+
+      @OpenAPIDescription("Optional equal-width numeric bins; mutually exclusive with dateBucket.")
+      public DataAnalysisPivotNumericBinsV1 numericBins;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("field", new Schema().withType(Type.STRING).withDescription("FULL field name, optionally joinTable.field through an exposed join."));
+         properties.put("dateBucket", new Schema().withType(Type.STRING).withEnumValues(List.of("YEAR", "QUARTER", "MONTH", "WEEK", "DAY", "HOUR", "DAY_OF_WEEK", "HOUR_OF_DAY")).withDescription("Optional calendar or cyclic bucket in the session timezone."));
+         properties.put("numericBins", ref("DataAnalysisPivotNumericBinsV1").withDescription("Optional equal-width numeric bins; mutually exclusive with dateBucket."));
+         return (new RequiredSchema(List.of("field")).withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotNumericBins version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotNumericBinsV1 implements ToSchema
+   {
+      @OpenAPIDescription("Number of bins, 1–200; defaults to 20.")
+      public Integer count;
+
+      @OpenAPIDescription("Optional explicit lower bound; otherwise a nice enclosing edge.")
+      public BigDecimal min;
+
+      @OpenAPIDescription("Optional explicit upper bound; final interior bin includes this value.")
+      public BigDecimal max;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("count", new Schema().withType(Type.INTEGER).withDescription("Number of bins, 1–200; defaults to 20."));
+         properties.put("min", new Schema().withType(Type.NUMBER).withDescription("Optional explicit lower bound; otherwise a nice enclosing edge."));
+         properties.put("max", new Schema().withType(Type.NUMBER).withDescription("Optional explicit upper bound; final interior bin includes this value."));
+         return (new Schema().withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotMeasure version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotMeasureV1 implements ToSchema
+   {
+      @OpenAPIDescription("Aggregate operator. COUNT counts records, including null values.")
+      public String operator;
+
+      @OpenAPIDescription("FULL field to aggregate; optional for COUNT.")
+      public String field;
+
+      @OpenAPIDescription("PERCENTILE only: percentile_cont fraction from 0 to 1.")
+      public BigDecimal percentile;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("operator", new Schema().withType(Type.STRING).withEnumValues(List.of("COUNT", "SUM", "AVG", "MIN", "MAX", "COUNT_DISTINCT", "PERCENTILE")).withDescription("Aggregate operator. COUNT counts records, including null values."));
+         properties.put("field", new Schema().withType(Type.STRING).withDescription("FULL field to aggregate; optional for COUNT."));
+         properties.put("percentile", new Schema().withType(Type.NUMBER).withDescription("PERCENTILE only: percentile_cont fraction from 0 to 1."));
+         return (new RequiredSchema(List.of("operator")).withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotHeader version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotHeaderV1 implements ToSchema
+   {
+      @OpenAPIDescription("Typed group key; null for a blank group or Other.")
+      public Serializable value;
+
+      @OpenAPIDescription("Display label, including Other (k groups).")
+      public String label;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("value", new Schema().withNullable(true).withDescription("Typed group key; null for a blank group or Other."));
+         properties.put("label", new Schema().withType(Type.STRING).withDescription("Display label, including Other (k groups)."));
+         return (new Schema().withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotMeasureInfo version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotMeasureInfoV1 implements ToSchema
+   {
+      @OpenAPIDescription("Stable measure key by request position.")
+      public String key;
+
+      @OpenAPIDescription("Display label.")
+      public String label;
+
+      @OpenAPIDescription("Semantic metric type.")
+      public ValueType valueType;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("key", new Schema().withType(Type.STRING).withDescription("Stable measure key by request position."));
+         properties.put("label", new Schema().withType(Type.STRING).withDescription("Display label."));
+         properties.put("valueType", new Schema().withType(Type.STRING).withEnumValues(java.util.Arrays.stream(ValueType.values()).map(Enum::name).toList()).withDescription("Semantic metric type."));
+         return (new Schema().withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** PivotCell version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotCellV1 implements ToSchema
+   {
+      @OpenAPIDescription("Zero-based index in rowHeaders.")
+      public Integer rowIndex;
+
+      @OpenAPIDescription("Zero-based index in columnHeaders.")
+      public Integer columnIndex;
+
+      @OpenAPIDescription("Metrics in requested measure order; suppressed metrics have no value or drill.")
+      public List<DataAnalysisMetricV1> values;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("rowIndex", new Schema().withType(Type.INTEGER).withDescription("Zero-based index in rowHeaders."));
+         properties.put("columnIndex", new Schema().withType(Type.INTEGER).withDescription("Zero-based index in columnHeaders."));
+         properties.put("values", array(ref("DataAnalysisMetricV1")).withDescription("Metrics in requested measure order; suppressed metrics have no value or drill."));
+         return (new Schema().withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
+   /*******************************************************************************
+    ** Pivot version 1 transport contract.
+    *******************************************************************************/
+   public static class DataAnalysisPivotV1 implements ToSchema
+   {
+      @OpenAPIDescription("Row header tuples in cell-index order.")
+      public List<List<DataAnalysisPivotHeaderV1>> rowHeaders;
+
+      @OpenAPIDescription("Column header tuples; an empty tuple represents no column dimensions.")
+      public List<List<DataAnalysisPivotHeaderV1>> columnHeaders;
+
+      @OpenAPIDescription("Measure metadata in request order.")
+      public List<DataAnalysisPivotMeasureInfoV1> measures;
+
+      @OpenAPIDescription("Sparse cells; absent combinations are not materialized.")
+      public List<DataAnalysisPivotCellV1> cells;
+
+      @OpenAPIDescription("Metric tuples aligned with rowHeaders; empty when totals disabled.")
+      public List<List<DataAnalysisMetricV1>> rowTotals;
+
+      @OpenAPIDescription("Metric tuples aligned with columnHeaders; empty when totals disabled.")
+      public List<List<DataAnalysisMetricV1>> columnTotals;
+
+      @OpenAPIDescription("Grand-total metrics; empty when totals disabled.")
+      public List<DataAnalysisMetricV1> grandTotals;
+
+      @OpenAPIDescription("Row groups were merged into Other.")
+      public Boolean truncatedRows;
+
+      @OpenAPIDescription("Column groups were merged into Other.")
+      public Boolean truncatedColumns;
+
+      @OpenAPIDescription("True when bounded streamed fallback produced a partial result.")
+      public Boolean estimate;
+
+      @OpenAPIDescription("Privacy, partial-result and joined-drill limitations.")
+      public List<String> warnings;
+
+      @OpenAPIDescription("UTC timestamp of computation.")
+      public String asOf;
+
+      /*******************************************************************************
+       ** Explicit references preserve the standalone extension contract.
+       *******************************************************************************/
+      @Override
+      public Schema toSchema()
+      {
+         Map<String, Schema> properties = new LinkedHashMap<>();
+         properties.put("rowHeaders", array(array(ref("DataAnalysisPivotHeaderV1"))).withDescription("Row header tuples in cell-index order."));
+         properties.put("columnHeaders", array(array(ref("DataAnalysisPivotHeaderV1"))).withDescription("Column header tuples; an empty tuple represents no column dimensions."));
+         properties.put("measures", array(ref("DataAnalysisPivotMeasureInfoV1")).withDescription("Measure metadata in request order."));
+         properties.put("cells", array(ref("DataAnalysisPivotCellV1")).withDescription("Sparse cells; absent combinations are not materialized."));
+         properties.put("rowTotals", array(array(ref("DataAnalysisMetricV1"))).withDescription("Metric tuples aligned with rowHeaders; empty when totals disabled."));
+         properties.put("columnTotals", array(array(ref("DataAnalysisMetricV1"))).withDescription("Metric tuples aligned with columnHeaders; empty when totals disabled."));
+         properties.put("grandTotals", array(ref("DataAnalysisMetricV1")).withDescription("Grand-total metrics; empty when totals disabled."));
+         properties.put("truncatedRows", new Schema().withType(Type.BOOLEAN).withDescription("Row groups were merged into Other."));
+         properties.put("truncatedColumns", new Schema().withType(Type.BOOLEAN).withDescription("Column groups were merged into Other."));
+         properties.put("estimate", new Schema().withType(Type.BOOLEAN).withDescription("True when bounded streamed fallback produced a partial result."));
+         properties.put("warnings", array(new Schema().withType(Type.STRING)).withDescription("Privacy, partial-result and joined-drill limitations."));
+         properties.put("asOf", new Schema().withType(Type.STRING).withDescription("UTC timestamp of computation."));
+         return (new Schema().withType(Type.OBJECT).withProperties(properties));
+      }
+   }
+
 }

src/main/java/com/kingsrook/qbits/dataanalysis/engine/AnalysisDrillGuard.java

@@ -0,0 +1,168 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+import java.util.Set;
+import com.kingsrook.qbits.dataanalysis.result.CandidateKey;
+import com.kingsrook.qbits.dataanalysis.result.Chart;
+import com.kingsrook.qbits.dataanalysis.result.ColumnProfile;
+import com.kingsrook.qbits.dataanalysis.result.DateProfile;
+import com.kingsrook.qbits.dataanalysis.result.DistinctValuesPage;
+import com.kingsrook.qbits.dataanalysis.result.Drill;
+import com.kingsrook.qbits.dataanalysis.result.DuplicatesResult;
+import com.kingsrook.qbits.dataanalysis.result.FieldSummary;
+import com.kingsrook.qbits.dataanalysis.result.Metric;
+import com.kingsrook.qbits.dataanalysis.result.NumericProfile;
+import com.kingsrook.qbits.dataanalysis.result.Point;
+import com.kingsrook.qbits.dataanalysis.result.Series;
+import com.kingsrook.qbits.dataanalysis.result.TableProfile;
+import com.kingsrook.qbits.dataanalysis.result.TextProfile;
+import com.kingsrook.qbits.dataanalysis.result.ValueCount;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.utils.JsonUtils;
+
+/*******************************************************************************
+ ** Final defense for profile-generated drills, including representative primary
+ ** keys and prefix bounds. User-authored predicates remain intact; unsafe generated
+ ** drills are disabled rather than broadened by removing their conditions.
+ *******************************************************************************/
+public class AnalysisDrillGuard
+{
+   private final Set<String> fields;
+   private final String original;
+
+   /*******************************************************************************
+    ** Resolve FULL fields once per completed result.
+    *******************************************************************************/
+   public AnalysisDrillGuard(Set<String> fields, QQueryFilter original)
+   {
+      this.fields = fields;
+      this.original = JsonUtils.toJson(DrillFilters.copy(original));
+   }
+
+   /*******************************************************************************
+    ** Visit only typed result containers, never the caller's query.
+    *******************************************************************************/
+   public void apply(Object value)
+   {
+      if(value instanceof Iterable<?> list)
+      {
+         list.forEach(this::apply);
+      }
+      else if(value instanceof Metric metric)
+      {
+         metric.withDrill(permitted(metric.getDrill()));
+      }
+      else if(value instanceof ValueCount count)
+      {
+         count.withDrill(permitted(count.getDrill()));
+      }
+      else if(value instanceof Point point)
+      {
+         point.withDrill(permitted(point.getDrill()));
+      }
+      else if(value instanceof CandidateKey key)
+      {
+         key.withDrill(permitted(key.getDrill()));
+      }
+      else if(value instanceof ColumnProfile profile)
+      {
+         apply(profile.getCounts());
+         apply(profile.getTopValues());
+         apply(profile.getBooleans());
+         apply(profile.getNumeric());
+         apply(profile.getDates());
+         apply(profile.getText());
+         apply(profile.getChart());
+      }
+      else if(value instanceof NumericProfile profile)
+      {
+         apply(profile.getMetrics());
+         apply(profile.getHistogram());
+         apply(profile.getOutliers());
+      }
+      else if(value instanceof DateProfile profile)
+      {
+         apply(profile.getMetrics());
+         apply(profile.getBuckets());
+         apply(profile.getGaps());
+         apply(profile.getDayOfWeek());
+         apply(profile.getHourOfDay());
+      }
+      else if(value instanceof TextProfile profile)
+      {
+         apply(profile.getMetrics());
+         apply(profile.getHistogram());
+         apply(profile.getPatterns());
+      }
+      else if(value instanceof TableProfile profile)
+      {
+         apply(profile.getRowCount());
+         apply(profile.getFields());
+         apply(profile.getCandidateKeys());
+         apply(profile.getHeatmap());
+      }
+      else if(value instanceof FieldSummary field)
+      {
+         apply(field.getNulls());
+         apply(field.getBlanks());
+      }
+      else if(value instanceof Chart chart)
+      {
+         apply(chart.getSeries());
+      }
+      else if(value instanceof Series series)
+      {
+         apply(series.getPoints());
+      }
+      else if(value instanceof DistinctValuesPage page)
+      {
+         apply(page.getValues());
+      }
+      else if(value instanceof DuplicatesResult result)
+      {
+         apply(result.getGroups());
+      }
+   }
+
+   /*******************************************************************************
+    ** Preserve count equivalence by removing the entire unsafe drill.
+    *******************************************************************************/
+   private Drill permitted(Drill drill)
+   {
+      return (drill == null || safe(drill.getFilter()) ? drill : null);
+   }
+
+   /*******************************************************************************
+    ** An exact copy of a user subtree is exempt from analysis field restrictions.
+    *******************************************************************************/
+   private boolean safe(QQueryFilter filter)
+   {
+      if(original.equals(JsonUtils.toJson(DrillFilters.copy(filter))))
+      {
+         return (true);
+      }
+      return (filter.getCriteria().stream().allMatch(c -> fields.contains(c.getFieldName())
+         && (c.getOtherFieldName() == null || fields.contains(c.getOtherFieldName()))
+         && (c.getFieldFunction() == null || fields.contains(c.getFieldFunction().getFieldName())))
+         && filter.getSubFilters().stream().allMatch(this::safe));
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/engine/AnalysisLabels.java

@@ -25,7 +25,6 @@ import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Set;
 import com.kingsrook.qqq.backend.core.actions.metadata.personalization.TableMetaDataPersonalizerAction;
-import com.kingsrook.qqq.backend.core.actions.permissions.FieldSecurityLockHelper;
 import com.kingsrook.qqq.backend.core.actions.permissions.PermissionEnforcement;
 import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
 import com.kingsrook.qqq.backend.core.actions.values.QPossibleValueTranslator;
@@ -64,7 +63,7 @@ public final class AnalysisLabels
     *******************************************************************************/
    public static String translate(QFieldMetaData field, Serializable value) throws QException
    {
-      return (translate(field, value, new LinkedHashSet<>()));
+      return (translate(field, value, CoreTierFieldAccessResolver.defaultResolver()));
    }
 
 
@@ -72,7 +71,15 @@ public final class AnalysisLabels
    /*******************************************************************************
     ** Resolve nested PVS label fields without invoking core's trusted lookup path.
     *******************************************************************************/
-   private static String translate(QFieldMetaData field, Serializable value, Set<String> visited) throws QException
+   public static String translate(QFieldMetaData field, Serializable value, FieldAnalysisAccessResolver access) throws QException
+   {
+      return (translate(field, value, new LinkedHashSet<>(), access));
+   }
+
+   /*******************************************************************************
+    ** Resolve nested labels only through FULL fields under the host policy.
+    *******************************************************************************/
+   private static String translate(QFieldMetaData field, Serializable value, Set<String> visited, FieldAnalysisAccessResolver access) throws QException
    {
       if(value == null)
       {
@@ -98,7 +105,7 @@ public final class AnalysisLabels
          List<String> labelFields = original.getRecordLabelFields() == null ? List.of() : original.getRecordLabelFields();
          for(String labelField : labelFields)
          {
-            if(!table.getFields().containsKey(labelField) || !FieldSecurityLockHelper.isFieldAccessible(table.getField(labelField)))
+            if(!table.getFields().containsKey(labelField) || access.resolve(table, table.getField(labelField)) != FieldAnalysisAccess.FULL)
             {
                return (fallback);
             }
@@ -119,7 +126,7 @@ public final class AnalysisLabels
             QFieldMetaData labelMetadata = table.getField(labelField);
             if(labelMetadata.getPossibleValueSourceName() != null)
             {
-               record.withDisplayValue(labelField, translate(labelMetadata, record.getValue(labelField), visited));
+               record.withDisplayValue(labelField, translate(labelMetadata, record.getValue(labelField), visited, access));
             }
          }
          String label = QValueFormatter.formatRecordLabel(table, record);
@@ -144,6 +151,14 @@ public final class AnalysisLabels
     ** host changing field/record locks cannot release older translated labels.
     *******************************************************************************/
    public static java.util.Map<String, Object> policy(String tableName) throws QException
+   {
+      return (policy(tableName, CoreTierFieldAccessResolver.defaultResolver()));
+   }
+
+   /*******************************************************************************
+    ** Include host-resolved tiers of related labels in the exact-run identity.
+    *******************************************************************************/
+   public static java.util.Map<String, Object> policy(String tableName, FieldAnalysisAccessResolver access) throws QException
    {
       java.util.Map<String, Object> result = new java.util.TreeMap<>();
       java.util.ArrayDeque<String> pending = new java.util.ArrayDeque<>();
@@ -160,6 +175,7 @@ public final class AnalysisLabels
          QTableMetaData table = TableMetaDataPersonalizerAction.execute(input, true);
          java.util.Map<String, Object> entry = new java.util.TreeMap<>();
          entry.put("fields", table.getFields());
+         entry.put("fullFields", table.getFields().values().stream().filter(f -> access.resolve(table, f) == FieldAnalysisAccess.FULL).map(QFieldMetaData::getName).sorted().toList());
          entry.put("locks", table.getRecordSecurityLocks());
          entry.put("labelFields", table.getRecordLabelFields());
          entry.put("labelFormat", table.getRecordLabelFormat());

src/main/java/com/kingsrook/qbits/dataanalysis/engine/AnalysisSecurity.java

@@ -20,19 +20,21 @@
 
 package com.kingsrook.qbits.dataanalysis.engine;
 
+import java.io.Serializable;
 import java.util.List;
 import com.kingsrook.qbits.dataanalysis.permissions.DataAnalysisPermissions;
 import com.kingsrook.qqq.backend.core.actions.metadata.personalization.TableMetaDataPersonalizerAction;
-import com.kingsrook.qqq.backend.core.actions.permissions.FieldSecurityLockHelper;
 import com.kingsrook.qqq.backend.core.actions.permissions.PermissionEnforcement;
 import com.kingsrook.qqq.backend.core.actions.permissions.PermissionsHelper;
 import com.kingsrook.qqq.backend.core.actions.permissions.TablePermissionSubType;
 import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QBadRequestException;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
 import com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException;
 import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
 import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import com.kingsrook.qqq.backend.core.utils.JsonUtils;
 
 /*******************************************************************************
  ** Fail closed before any source-table computation, including cache retrieval.
@@ -40,17 +42,27 @@ import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
 public class AnalysisSecurity
 {
    private final EffectiveConfigResolver configs;
+   private final FieldAnalysisAccessResolver fieldAccess;
 
    /*******************************************************************************
     ** Use the runtime's configuration policy.
     *******************************************************************************/
    public AnalysisSecurity(EffectiveConfigResolver configs)
    {
-      this.configs = configs;
+      this(configs, CoreTierFieldAccessResolver.defaultResolver());
    }
 
 
 
+   /*******************************************************************************
+    ** Bind the host field policy.
+    *******************************************************************************/
+   public AnalysisSecurity(EffectiveConfigResolver configs, FieldAnalysisAccessResolver fieldAccess)
+   {
+      this.configs = configs;
+      this.fieldAccess = fieldAccess;
+   }
+
    /*******************************************************************************
     ** Explicit enforcement is independent of the instance's default switch.
     *******************************************************************************/
@@ -76,7 +88,7 @@ public class AnalysisSecurity
    {
       QTableMetaData table = table(name);
       EffectiveConfig c = configs.resolve(name);
-      return (table.getFields().values().stream().filter(FieldSecurityLockHelper::isFieldAccessible)
+      return (table.getFields().values().stream().filter(f -> fieldAccess.resolve(table, f) == FieldAnalysisAccess.FULL)
          .filter(f -> c.getAnalyzableFields().isEmpty() || c.getAnalyzableFields().contains(f.getName()))
          .filter(f -> !c.getExcludedFields().contains(f.getName())).toList());
    }
@@ -89,6 +101,50 @@ public class AnalysisSecurity
    public QFieldMetaData field(String table, String field) throws QException
    {
       return (analyzableFields(table).stream().filter(f -> f.getName().equals(field)).findFirst()
-         .orElseThrow(() -> new QPermissionDeniedException("Analysis is unavailable for this field.")));
+         .orElseThrow(() -> new QBadRequestException("Unknown analysis field.")));
+   }
+
+   /*******************************************************************************
+    ** Core fingerprint plus the sorted FULL-field list, hashed by the run identity.
+    *******************************************************************************/
+   public String accessFingerprint(String table) throws QException
+   {
+      return (fieldAccess.getAccessFingerprint() + ":" + JsonUtils.toJson(analyzableFields(table).stream().map(QFieldMetaData::getName).sorted().toList()));
+   }
+
+   /*******************************************************************************
+    ** Check a joined field without requiring the related table analyze permission.
+    *******************************************************************************/
+   public QFieldMetaData joinedField(String tableName, String fieldName) throws QException
+   {
+      QTableMetaData table = joinedTable(tableName);
+      QFieldMetaData field = table.getFields().get(fieldName);
+      if(field == null || fieldAccess.resolve(table, field) != FieldAnalysisAccess.FULL)
+      {
+         throw new QBadRequestException("Unknown analysis field.");
+      }
+      return (field);
+   }
+
+   /*******************************************************************************
+    ** Display labels use the same policy as the selected pivot field.
+    *******************************************************************************/
+   public String label(QFieldMetaData field, Serializable value) throws QException
+   {
+      return (AnalysisLabels.translate(field, value, fieldAccess));
+   }
+
+   /*******************************************************************************
+    ** Each intermediate exposed-join table requires READ, without analyze rights.
+    *******************************************************************************/
+   public QTableMetaData joinedTable(String tableName) throws QException
+   {
+      QueryInput input = new QueryInput(tableName);
+      input.setPermissionEnforcement(PermissionEnforcement.ENFORCE);
+      if(QContext.getQInstance().getTable(tableName) == null || !PermissionsHelper.hasTablePermission(input, tableName, TablePermissionSubType.READ))
+      {
+         throw new QBadRequestException("Unknown analysis field.");
+      }
+      return (TableMetaDataPersonalizerAction.execute(input, true));
    }
 }

src/main/java/com/kingsrook/qbits/dataanalysis/engine/ColumnProfiler.java

@@ -48,13 +48,23 @@ import com.kingsrook.qqq.backend.core.utils.ValueUtils;
 public class ColumnProfiler
 {
    private final AnalysisBackend backend;
+   private final FieldAnalysisAccessResolver fieldAccess;
 
    /*******************************************************************************
     ** The backend can be replaced when core's new operators ship.
     *******************************************************************************/
    public ColumnProfiler(AnalysisBackend backend)
+   {
+      this(backend, CoreTierFieldAccessResolver.defaultResolver());
+   }
+
+   /*******************************************************************************
+    ** Bind the host field policy for possible-value labels.
+    *******************************************************************************/
+   public ColumnProfiler(AnalysisBackend backend, FieldAnalysisAccessResolver fieldAccess)
    {
       this.backend = backend;
+      this.fieldAccess = fieldAccess;
    }
 
 
@@ -82,7 +92,7 @@ public class ColumnProfiler
       }
       result.getCounts().add(ProfileSupport.count("distinct", counts.distinct(), table, drillFilter, counts.distinctFilter()));
       result.getCounts().add(ProfileSupport.metric("uniqueness", counts.uniqueness(), ValueType.PERCENT));
-      result.withTopValues(ProfileSupport.values(table, drillFilter, field, backend.topValues(table, filter, name, 20, 0, null), rows));
+      result.withTopValues(ProfileSupport.values(table, drillFilter, field, backend.topValues(table, filter, name, 20, 0, null), rows, fieldAccess));
       QFieldType type = field.getType();
       if(type == QFieldType.INTEGER || type == QFieldType.LONG || type == QFieldType.DECIMAL)
       {

src/main/java/com/kingsrook/qbits/dataanalysis/engine/CoreTierFieldAccessResolver.java

@@ -0,0 +1,118 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+import java.lang.reflect.Method;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+
+/*******************************************************************************
+ ** Optional core bridge: replace with direct calls once QQQ ships the fieldaccess package.
+ ** Class and method discovery happen once; invocation failures fail closed.
+ *******************************************************************************/
+public class CoreTierFieldAccessResolver implements FieldAnalysisAccessResolver
+{
+   private static final CoreTierFieldAccessResolver DEFAULT = discover();
+   private final Method tier;
+   private final Method fingerprint;
+
+   /*******************************************************************************
+    ** Bind discovered static methods once.
+    *******************************************************************************/
+   public CoreTierFieldAccessResolver(Class<?> helper) throws ReflectiveOperationException
+   {
+      tier = helper.getMethod("getTier", QTableMetaData.class, String.class);
+      Method optional;
+      try
+      {
+         optional = helper.getMethod("getAccessFingerprint");
+      }
+      catch(NoSuchMethodException e)
+      {
+         optional = null;
+      }
+      fingerprint = optional;
+   }
+
+   /*******************************************************************************
+    ** Resolve the optional class once per class loader.
+    *******************************************************************************/
+   private static CoreTierFieldAccessResolver discover()
+   {
+      try
+      {
+         return (new CoreTierFieldAccessResolver(Class.forName("com.kingsrook.qqq.backend.core.actions.permissions.fieldaccess.FieldAccessHelper")));
+      }
+      catch(ClassNotFoundException e)
+      {
+         return (null);
+      }
+      catch(ReflectiveOperationException e)
+      {
+         throw new ExceptionInInitializerError(e);
+      }
+   }
+
+   /*******************************************************************************
+    ** Use legacy locks only when the core helper class is absent.
+    *******************************************************************************/
+   public static FieldAnalysisAccessResolver defaultResolver()
+   {
+      return (DEFAULT == null ? new FieldSecurityLockFieldAccessResolver() : DEFAULT);
+   }
+
+   /*******************************************************************************
+    ** Unknown tiers and invocation failures never grant analysis rights.
+    *******************************************************************************/
+   @Override
+   public FieldAnalysisAccess resolve(QTableMetaData table, QFieldMetaData field)
+   {
+      try
+      {
+         return (switch(String.valueOf(tier.invoke(null, table, field.getName())))
+         {
+            case "FULL" -> FieldAnalysisAccess.FULL;
+            case "REVEALABLE" -> FieldAnalysisAccess.EXACT_MATCH_ONLY;
+            default -> FieldAnalysisAccess.NONE;
+         });
+      }
+      catch(ReflectiveOperationException e)
+      {
+         return (FieldAnalysisAccess.NONE);
+      }
+   }
+
+   /*******************************************************************************
+    ** A broken fingerprint prevents cache reuse rather than returning stale data.
+    *******************************************************************************/
+   @Override
+   public String getAccessFingerprint()
+   {
+      try
+      {
+         return (fingerprint == null ? "" : (String) fingerprint.invoke(null));
+      }
+      catch(ReflectiveOperationException e)
+      {
+         throw new IllegalStateException("Could not resolve field access fingerprint.", e);
+      }
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/engine/DistinctValues.java

@@ -34,13 +34,23 @@ import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
 public class DistinctValues
 {
    private final AnalysisBackend backend;
+   private final FieldAnalysisAccessResolver fieldAccess;
 
    /*******************************************************************************
     ** Bind primitive operations.
     *******************************************************************************/
    public DistinctValues(AnalysisBackend backend)
+   {
+      this(backend, CoreTierFieldAccessResolver.defaultResolver());
+   }
+
+   /*******************************************************************************
+    ** Bind the host field policy for possible-value labels.
+    *******************************************************************************/
+   public DistinctValues(AnalysisBackend backend, FieldAnalysisAccessResolver fieldAccess)
    {
       this.backend = backend;
+      this.fieldAccess = fieldAccess;
    }
 
 
@@ -60,7 +70,7 @@ public class DistinctValues
       boolean wholeListing = config.getMinGroupSize() > 0 || request.getSearch() != null && !request.getSearch().isBlank();
       int fetch = wholeListing ? Math.max(1, config.getMaxRows()) : request.getPageSize() + 1;
       List<ValueCount> values = ProfileSupport.values(request.getTableName(), scope.filter(), field,
-         backend.topValues(request.getTableName(), scope.filter(), field.getName(), fetch, wholeListing ? 0 : offset, request.getSearch()), scope.rowsScanned());
+         backend.topValues(request.getTableName(), scope.filter(), field.getName(), fetch, wholeListing ? 0 : offset, request.getSearch()), scope.rowsScanned(), fieldAccess);
       long totalDistinct = wholeListing ? values.stream().filter(v -> v.getValue() != null).count()
          : backend.fieldCounts(request.getTableName(), scope.filter(), List.of(field.getName())).get(field.getName()).distinct();
       values = new MinGroupSizeGuard(config.getMinGroupSize()).groups(values);

src/main/java/com/kingsrook/qbits/dataanalysis/engine/FieldAnalysisAccess.java

@@ -0,0 +1,29 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+/*******************************************************************************
+ ** Analysis access is stricter than the ability to submit an exact query.
+ *******************************************************************************/
+public enum FieldAnalysisAccess
+{
+   FULL, EXACT_MATCH_ONLY, NONE
+}

src/main/java/com/kingsrook/qbits/dataanalysis/engine/FieldAnalysisAccessResolver.java

@@ -0,0 +1,43 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+
+/*******************************************************************************
+ ** Resolve the current user's analysis rights without exposing protected values.
+ *******************************************************************************/
+public interface FieldAnalysisAccessResolver
+{
+   /*******************************************************************************
+    ** Only FULL fields may contribute generated analysis predicates.
+    *******************************************************************************/
+   FieldAnalysisAccess resolve(QTableMetaData table, QFieldMetaData field);
+
+   /*******************************************************************************
+    ** Optional core policy fingerprint supplements the FULL-field list hash.
+    *******************************************************************************/
+   default String getAccessFingerprint()
+   {
+      return ("");
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/engine/FieldSecurityLockFieldAccessResolver.java

@@ -0,0 +1,42 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+import com.kingsrook.qqq.backend.core.actions.permissions.FieldSecurityLockHelper;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+
+/*******************************************************************************
+ ** Legacy lock adapter. The core tier API (qbit-secured-data) will map REVEALABLE
+ ** to EXACT_MATCH_ONLY, MASKED/HIDDEN without filter rights to NONE, and
+ ** MASKED/HIDDEN with filter rights to FULL.
+ *******************************************************************************/
+public class FieldSecurityLockFieldAccessResolver implements FieldAnalysisAccessResolver
+{
+   /*******************************************************************************
+    ** Keep the legacy lock enforcement when core tiers are not installed.
+    *******************************************************************************/
+   @Override
+   public FieldAnalysisAccess resolve(QTableMetaData table, QFieldMetaData field)
+   {
+      return (FieldSecurityLockHelper.isFieldAccessible(field) ? FieldAnalysisAccess.FULL : FieldAnalysisAccess.NONE);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/engine/MinGroupSizeGuard.java

@@ -143,4 +143,16 @@ public class MinGroupSizeGuard
          profile.getText().withHistogram(groups(profile.getText().getHistogram())).withPatterns(groups(profile.getText().getPatterns()));
       }
    }
+
+   /*******************************************************************************
+    ** Small pivot cells suppress every measure, including count, with no drill.
+    *******************************************************************************/
+   public void pivotMetrics(List<Metric> metrics, long rows)
+   {
+      if(rows < minimum)
+      {
+         metrics.forEach(m -> m.withValue(null).withFormatted(null).withDrill(null).withSuppressed(true)
+            .withSuppressedReason("Hidden: fewer than " + minimum + " records"));
+      }
+   }
 }

src/main/java/com/kingsrook/qbits/dataanalysis/engine/NiceBins.java

@@ -33,6 +33,14 @@ public record NiceBins(BigDecimal low, BigDecimal high, int count)
     ** Select the closest 10..25-bin enclosure from 1, 2, 2.5 and 5 times powers of ten.
     *******************************************************************************/
    public static NiceBins covering(BigDecimal low, BigDecimal high)
+   {
+      return (covering(low, high, 20));
+   }
+
+   /*******************************************************************************
+    ** Choose readable enclosing edges for a requested pivot bin count.
+    *******************************************************************************/
+   public static NiceBins covering(BigDecimal low, BigDecimal high, int target)
    {
       BigDecimal span = high.subtract(low);
       if(span.signum() == 0)
@@ -41,7 +49,7 @@ public record NiceBins(BigDecimal low, BigDecimal high, int count)
          low = low.subtract(span.divide(BigDecimal.valueOf(2)));
          high = high.add(span.divide(BigDecimal.valueOf(2)));
       }
-      int exponent = span.precision() - span.scale() - 2;
+      int exponent = span.precision() - span.scale() - Integer.toString(target).length();
       NiceBins best = null;
       for(int power = exponent - 2; power <= exponent + 2; power++)
       {
@@ -51,7 +59,7 @@ public record NiceBins(BigDecimal low, BigDecimal high, int count)
             BigDecimal first = low.divide(step, 0, RoundingMode.FLOOR).multiply(step);
             BigDecimal last = high.divide(step, 0, RoundingMode.CEILING).multiply(step);
             int count = last.subtract(first).divide(step).intValueExact();
-            if(count >= 10 && count <= 25 && (best == null || Math.abs(count - 20) < Math.abs(best.count() - 20)))
+            if(count >= Math.max(1, target / 2) && count <= Math.max(2, target * 5 / 4) && (best == null || Math.abs(count - target) < Math.abs(best.count() - target)))
             {
                best = new NiceBins(first, last, count);
             }

src/main/java/com/kingsrook/qbits/dataanalysis/engine/PivotExplorer.java

@@ -0,0 +1,607 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.engine;
+
+import java.io.Serializable;
+import java.math.BigDecimal;
+import java.math.MathContext;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
+import com.kingsrook.qbits.dataanalysis.result.Metric;
+import com.kingsrook.qbits.dataanalysis.result.PivotCell;
+import com.kingsrook.qbits.dataanalysis.result.PivotDimension;
+import com.kingsrook.qbits.dataanalysis.result.PivotHeader;
+import com.kingsrook.qbits.dataanalysis.result.PivotMeasure;
+import com.kingsrook.qbits.dataanalysis.result.PivotMeasureInfo;
+import com.kingsrook.qbits.dataanalysis.result.PivotRequest;
+import com.kingsrook.qbits.dataanalysis.result.PivotResult;
+import com.kingsrook.qbits.dataanalysis.result.ValueType;
+import com.kingsrook.qqq.backend.core.actions.permissions.PermissionEnforcement;
+import com.kingsrook.qqq.backend.core.actions.tables.AggregateAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QBadRequestException;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.Aggregate;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.AggregateInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.AggregateOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.AggregateResult;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.DateBucket;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.DateBucketUnit;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.GroupBy;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.NumericBucket;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.QFilterOrderByAggregate;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryJoin;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinType;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.ExposedJoin;
+import com.kingsrook.qqq.backend.core.utils.ValueUtils;
+import com.kingsrook.qqq.backend.core.utils.aggregates.AggregateAnalysis;
+
+/*******************************************************************************
+ ** Request-local pivot planner. Core executes every aggregate with ENFORCE.
+ ** Merged cells are re-aggregated so non-additive measures remain exact.
+ *******************************************************************************/
+public class PivotExplorer
+{
+   private final AnalysisSecurity security;
+   private final EffectiveConfig config;
+   private final List<QueryJoin> joins = new ArrayList<>();
+   private final LinkedHashSet<String> toManyTables = new LinkedHashSet<>();
+   private final LinkedHashSet<String> multiHopTables = new LinkedHashSet<>();
+   private PivotRequest request;
+   private Aggregate count;
+   private final List<Aggregate> measures = new ArrayList<>();
+   private final List<PivotMeasureInfo> measureInfo = new ArrayList<>();
+   private boolean partial;
+
+   /*******************************************************************************
+    ** Bind the current user's policy and bounded streamed fallback.
+    *******************************************************************************/
+   public PivotExplorer(AnalysisSecurity security, EffectiveConfig config)
+   {
+      this.security = security;
+      this.config = config;
+   }
+
+   /*******************************************************************************
+    ** One slot is a visible group or the exact complement of the kept groups.
+    *******************************************************************************/
+   private record Slot(Serializable value, String label, QQueryFilter condition, boolean other)
+   {
+   }
+
+   /*******************************************************************************
+    ** Dimension state owns predicates, so no generated filter names hidden fields.
+    *******************************************************************************/
+   private record Dimension(GroupBy group, QFieldMetaData field, Map<Serializable, Slot> kept, Map<Serializable, Long> counts, Slot other)
+   {
+      /*******************************************************************************
+       ** Return the kept group, Other, or null for a privacy-suppressed group.
+       *******************************************************************************/
+      Slot slot(Serializable value, int minimum)
+      {
+         return (counts.getOrDefault(value, 0L) < minimum ? null : kept.getOrDefault(value, other));
+      }
+   }
+
+   /*******************************************************************************
+    ** Validate the complete plan before any source computation.
+    *******************************************************************************/
+   public PivotResult execute(PivotRequest request) throws QException
+   {
+      this.request = request;
+      validate();
+      String primaryKey = QContext.getQInstance().getTable(request.getTableName()).getPrimaryKeyField();
+      count = new Aggregate(primaryKey, AggregateOperator.COUNT).withFieldType(QFieldType.INTEGER);
+      List<PivotDimension> requested = new ArrayList<>(request.getRows());
+      requested.addAll(request.getColumns());
+      List<QFieldMetaData> fields = new ArrayList<>();
+      for(PivotDimension dimension : requested)
+      {
+         fields.add(resolve(dimension.getField()));
+      }
+      for(int i = 0; i < request.getValues().size(); i++)
+      {
+         prepareMeasure(request.getValues().get(i), i);
+      }
+      List<Dimension> dimensions = new ArrayList<>();
+      for(int i = 0; i < requested.size(); i++)
+      {
+         dimensions.add(dimension(requested.get(i), fields.get(i)));
+      }
+      int rowCount = request.getRows().size();
+      List<Dimension> rows = dimensions.subList(0, rowCount);
+      List<Dimension> columns = dimensions.subList(rowCount, dimensions.size());
+      PivotResult result = new PivotResult().withAsOf(Instant.now()).withMeasures(measureInfo)
+         .withTruncatedRows(rows.stream().anyMatch(d -> d.other() != null))
+         .withTruncatedColumns(columns.stream().anyMatch(d -> d.other() != null));
+      Map<List<Slot>, List<Metric>> matrix = level(dimensions);
+      Map<List<Slot>, List<Metric>> rowTotals = request.getShowTotals() || config.getMinGroupSize() > 0 ? level(rows) : Map.of();
+      Map<List<Slot>, List<Metric>> columnTotals = request.getShowTotals() || config.getMinGroupSize() > 0 ? level(columns) : Map.of();
+      List<List<Slot>> rowKeys = new ArrayList<>();
+      List<List<Slot>> columnKeys = new ArrayList<>();
+      for(var entry : matrix.entrySet())
+      {
+         List<Slot> row = List.copyOf(entry.getKey().subList(0, rowCount));
+         List<Slot> column = List.copyOf(entry.getKey().subList(rowCount, dimensions.size()));
+         if(config.getMinGroupSize() > 0 && (rowTotals.getOrDefault(row, List.of()).stream().allMatch(m -> m.getSuppressed())
+            || columnTotals.getOrDefault(column, List.of()).stream().allMatch(m -> m.getSuppressed())))
+         {
+            continue;
+         }
+         if(!rowKeys.contains(row))
+         {
+            rowKeys.add(row);
+         }
+         if(!columnKeys.contains(column))
+         {
+            columnKeys.add(column);
+         }
+         result.getCells().add(new PivotCell().withRowIndex(rowKeys.indexOf(row)).withColumnIndex(columnKeys.indexOf(column)).withValues(entry.getValue()));
+      }
+      result.withRowHeaders(headers(rowKeys)).withColumnHeaders(headers(columnKeys));
+      if(request.getShowTotals())
+      {
+         rowKeys.forEach(key -> result.getRowTotals().add(rowTotals.getOrDefault(key, List.of())));
+         columnKeys.forEach(key -> result.getColumnTotals().add(columnTotals.getOrDefault(key, List.of())));
+         result.withGrandTotals(level(List.of()).getOrDefault(List.of(), List.of()));
+      }
+      for(String tableName : toManyTables)
+      {
+         result.getWarnings().add("Counts include one row per related " + QContext.getQInstance().getTable(tableName).getLabel()
+            + "; drill-through is not available for this dimension.");
+      }
+      for(String tableName : multiHopTables)
+      {
+         result.getWarnings().add("Drill-through is not available for fields reached through more than one join (" + QContext.getQInstance().getTable(tableName).getLabel() + ").");
+      }
+      if(config.getMinGroupSize() > 0)
+      {
+         result.getWarnings().add("Groups below " + config.getMinGroupSize() + " records are omitted; small cells and totals are suppressed.");
+      }
+      if(partial)
+      {
+         result.withEstimate(true);
+         result.getWarnings().add("Partial pivot: the streamed row limit was reached. Drills are disabled because aggregates may cover different prefixes.");
+         result.getCells().forEach(c -> disableDrills(c.getValues()));
+         result.getRowTotals().forEach(this::disableDrills);
+         result.getColumnTotals().forEach(this::disableDrills);
+         disableDrills(result.getGrandTotals());
+      }
+      return (result);
+   }
+
+   /*******************************************************************************
+    ** Reject invalid shapes and types before aggregate execution.
+    *******************************************************************************/
+   private void validate() throws QException
+   {
+      if(request.getRows() == null || request.getRows().isEmpty() || request.getRows().size() > 3
+         || request.getColumns() == null || request.getColumns().size() > 2 || request.getValues() == null
+         || request.getValues().isEmpty() || request.getValues().size() > 6 || request.getShowTotals() == null
+         || request.getLimitGroups() == null || request.getLimitGroups() < 1 || request.getLimitGroups() > 200)
+      {
+         throw new QBadRequestException("Choose 1�3 row dimensions, 0�2 column dimensions, 1�6 measures and a group limit from 1 to 200.");
+      }
+      List<PivotDimension> dimensions = new ArrayList<>(request.getRows());
+      dimensions.addAll(request.getColumns());
+      LinkedHashSet<String> names = new LinkedHashSet<>();
+      for(PivotDimension dimension : dimensions)
+      {
+         if(dimension == null || dimension.getField() == null || !names.add(dimension.getField()))
+         {
+            throw new QBadRequestException("Choose distinct dimension fields.");
+         }
+         QFieldMetaData field = resolve(dimension.getField());
+         var bins = dimension.getNumericBins();
+         if(dimension.getDateBucket() != null && (bins != null || field.getType() != QFieldType.DATE && field.getType() != QFieldType.DATE_TIME)
+            || bins != null && (!numeric(field) || bins.getCount() == null || bins.getCount() < 1 || bins.getCount() > 200
+               || bins.getMin() != null && bins.getMax() != null && bins.getMin().compareTo(bins.getMax()) >= 0))
+         {
+            throw new QBadRequestException("Buckets must match the field type; numeric bins require 1�200 bins and min < max.");
+         }
+      }
+   }
+
+   /*******************************************************************************
+    ** Only exposed, unambiguous paths may introduce a joined field.
+    ** The installed snapshot has no related-record picker join-path helper.
+    *******************************************************************************/
+   private QFieldMetaData resolve(String name) throws QException
+   {
+      if(name == null || !name.contains("."))
+      {
+         return (security.field(request.getTableName(), name));
+      }
+      String[] parts = name.split("\\.", -1);
+      var table = QContext.getQInstance().getTable(request.getTableName());
+      List<ExposedJoin> exposed = table.getExposedJoins() == null ? List.of() : table.getExposedJoins();
+      List<ExposedJoin> paths = exposed.stream().filter(j -> j.getJoinTable().equals(parts[0])).toList();
+      if(parts.length != 2 || paths.size() != 1 || paths.getFirst().getJoinPath() == null || paths.getFirst().getJoinPath().isEmpty())
+      {
+         throw new QBadRequestException("Unknown analysis field.");
+      }
+      String current = table.getName();
+      boolean toOne = true;
+      for(String joinName : paths.getFirst().getJoinPath())
+      {
+         var metadata = QContext.getQInstance().getJoin(joinName);
+         if(metadata == null || !current.equals(metadata.getLeftTable()) && !current.equals(metadata.getRightTable()))
+         {
+            throw new QBadRequestException("Unknown analysis field.");
+         }
+         String next = current.equals(metadata.getLeftTable()) ? metadata.getRightTable() : metadata.getLeftTable();
+         JoinType type = metadata.getType();
+         if(type != null && current.equals(metadata.getRightTable()))
+         {
+            type = type.flip();
+         }
+         toOne &= type == JoinType.MANY_TO_ONE || type == JoinType.ONE_TO_ONE;
+         security.joinedTable(next);
+         QueryJoin existing = joins.stream().filter(j -> next.equals(j.getJoinTable())).findFirst().orElse(null);
+         if(existing == null)
+         {
+            joins.add(new QueryJoin(current, next).withJoinMetaData(metadata));
+         }
+         else if(!existing.getJoinMetaData().getName().equals(joinName))
+         {
+            throw new QBadRequestException("Ambiguous analysis join path.");
+         }
+         current = next;
+      }
+      if(!current.equals(parts[0]))
+      {
+         throw new QBadRequestException("Unknown analysis field.");
+      }
+      if(!toOne)
+      {
+         toManyTables.add(current);
+      }
+      else if(paths.getFirst().getJoinPath().size() > 1)
+      {
+         ///////////////////////////////////////////////////////////////////////////////
+         // counts are per record, but core's implicit joins for a multi-hop filter     //
+         // do not reproduce them on every backend yet (memory counts differ), so no   //
+         // drill until that is fixed in core                                          //
+         ///////////////////////////////////////////////////////////////////////////////
+         multiHopTables.add(current);
+      }
+      return (security.joinedField(parts[0], parts[1]));
+   }
+
+   /*******************************************************************************
+    ** Count records even when a supplied COUNT field is nullable.
+    *******************************************************************************/
+   private void prepareMeasure(PivotMeasure measure, int index) throws QException
+   {
+      if(measure == null || measure.getOperator() == null)
+      {
+         throw new QBadRequestException("Choose a measure operator.");
+      }
+      QFieldMetaData field = measure.getField() == null && measure.getOperator() == PivotMeasure.Operator.COUNT ? null : resolve(measure.getField());
+      PivotMeasure.Operator operator = measure.getOperator();
+      if((operator == PivotMeasure.Operator.SUM || operator == PivotMeasure.Operator.AVG || operator == PivotMeasure.Operator.PERCENTILE) && !numeric(field)
+         || operator == PivotMeasure.Operator.PERCENTILE && (measure.getPercentile() == null || measure.getPercentile().signum() < 0 || measure.getPercentile().compareTo(BigDecimal.ONE) > 0)
+         || operator != PivotMeasure.Operator.PERCENTILE && measure.getPercentile() != null)
+      {
+         throw new QBadRequestException("Numeric measures require a numeric field; percentile must be between 0 and 1.");
+      }
+      Aggregate aggregate = operator == PivotMeasure.Operator.COUNT ? count : new Aggregate(measure.getField(),
+         operator == PivotMeasure.Operator.PERCENTILE ? AggregateOperator.PERCENTILE_CONT : AggregateOperator.valueOf(operator.name()));
+      if(operator == PivotMeasure.Operator.PERCENTILE)
+      {
+         aggregate.withPercentile(measure.getPercentile());
+      }
+      if(operator == PivotMeasure.Operator.COUNT_DISTINCT)
+      {
+         aggregate.withFieldType(QFieldType.INTEGER);
+      }
+      measures.add(aggregate);
+      ValueType type = operator == PivotMeasure.Operator.COUNT || operator == PivotMeasure.Operator.COUNT_DISTINCT ? ValueType.COUNT
+         : numeric(field) ? ValueType.DECIMAL : field.getType() == QFieldType.DATE ? ValueType.DATE : field.getType() == QFieldType.DATE_TIME ? ValueType.DATE_TIME : ValueType.TEXT;
+      measureInfo.add(new PivotMeasureInfo().withKey("value" + index).withLabel(operator.name() + (field == null ? " records" : " " + field.getLabel())).withValueType(type));
+   }
+
+   /*******************************************************************************
+    ** Numeric buckets and moments accept the same numeric QQQ field types.
+    *******************************************************************************/
+   private static boolean numeric(QFieldMetaData field)
+   {
+      return (field != null && (field.getType() == QFieldType.INTEGER || field.getType() == QFieldType.LONG || field.getType() == QFieldType.DECIMAL));
+   }
+
+   /*******************************************************************************
+    ** Select top groups first, then collect cardinality and privacy counts.
+    *******************************************************************************/
+   private Dimension dimension(PivotDimension requested, QFieldMetaData field) throws QException
+   {
+      GroupBy group = new GroupBy(field.getType(), requested.getField());
+      if(requested.getDateBucket() != null)
+      {
+         group.withBucket(new DateBucket(requested.getDateBucket()));
+      }
+      if(requested.getNumericBins() != null)
+      {
+         var bins = requested.getNumericBins();
+         BigDecimal low = bins.getMin();
+         BigDecimal high = bins.getMax();
+         if(low == null || high == null)
+         {
+            Aggregate min = new Aggregate(requested.getField(), AggregateOperator.MIN);
+            Aggregate max = new Aggregate(requested.getField(), AggregateOperator.MAX);
+            var extrema = aggregate(List.of(), request.getFilter(), List.of(min, max), null);
+            Serializable first = extrema.isEmpty() ? null : extrema.getFirst().getAggregateValues().get(min);
+            Serializable last = extrema.isEmpty() ? null : extrema.getFirst().getAggregateValues().get(max);
+            NiceBins nice = NiceBins.covering(first == null ? BigDecimal.ZERO : new BigDecimal(first.toString()), last == null ? BigDecimal.ONE : new BigDecimal(last.toString()), bins.getCount());
+            low = low == null ? nice.low() : low;
+            high = high == null ? nice.high() : high;
+         }
+         if(low.compareTo(high) >= 0)
+         {
+            throw new QBadRequestException("Numeric bin min must be below max.");
+         }
+         group.withBucket(new NumericBucket(low, high, bins.getCount()));
+      }
+      List<AggregateResult> top = aggregate(List.of(group), request.getFilter(), List.of(count), request.getLimitGroups());
+      List<AggregateResult> all = aggregate(List.of(group), request.getFilter(), List.of(count), null);
+      Map<Serializable, Long> counts = new LinkedHashMap<>();
+      all.forEach(r -> counts.put(r.getGroupByValues().get(group), count(r)));
+      Map<Serializable, Slot> kept = new LinkedHashMap<>();
+      for(AggregateResult row : top)
+      {
+         Serializable value = row.getGroupByValues().get(group);
+         if(count(row) >= config.getMinGroupSize())
+         {
+            kept.put(value, new Slot(value, label(group, field, value), condition(group, field, value), false));
+         }
+      }
+      List<Serializable> remaining = counts.keySet().stream().filter(v -> !kept.containsKey(v) && counts.get(v) >= config.getMinGroupSize()).toList();
+      Slot other = null;
+      if(!remaining.isEmpty())
+      {
+         QQueryFilter predicate;
+         if(group.getBucket() == null && field.getType() != QFieldType.BOOLEAN && config.getMinGroupSize() == 0 && !kept.containsKey("") && kept.keySet().stream().noneMatch(v -> v instanceof Instant || v instanceof LocalDate))
+         {
+            List<Serializable> values = kept.keySet().stream().filter(Objects::nonNull).toList();
+            predicate = values.isEmpty() ? new QQueryFilter() : new QQueryFilter().withCriteria(group.getFieldName(), QCriteriaOperator.NOT_IN, values);
+            predicate = DrillFilters.and(predicate, DrillFilters.nonNull(group.getFieldName(), field.getType().isStringLike()));
+            if(!kept.containsKey(null))
+            {
+               predicate = new QQueryFilter().withBooleanOperator(QQueryFilter.BooleanOperator.OR).withSubFilter(predicate).withSubFilter(DrillFilters.nulls(group.getFieldName(), field.getType().isStringLike()));
+            }
+            if(field.getType().isStringLike())
+            {
+               predicate = new QQueryFilter().withBooleanOperator(QQueryFilter.BooleanOperator.OR).withSubFilter(predicate).withSubFilter(DrillFilters.value(group.getFieldName(), "", true));
+            }
+         }
+         else
+         {
+            predicate = new QQueryFilter().withBooleanOperator(QQueryFilter.BooleanOperator.OR);
+            for(Serializable value : remaining)
+            {
+               predicate.withSubFilter(condition(group, field, value));
+            }
+         }
+         other = new Slot(null, "Other (" + remaining.size() + " groups)", predicate, true);
+      }
+      return (new Dimension(group, field, kept, counts, other));
+   }
+
+   /*******************************************************************************
+    ** Native group keys are displayed as labels without changing filter operands.
+    *******************************************************************************/
+   private String label(GroupBy group, QFieldMetaData field, Serializable value) throws QException
+   {
+      if(value == null)
+      {
+         return ("(Blank)");
+      }
+      if(group.getBucket() instanceof DateBucket date)
+      {
+         return (AnalysisFormatting.dateBucket(value, AnalysisBackend.DateUnit.valueOf(date.getUnit().name()), ValueUtils.getSessionOrInstanceZoneId()));
+      }
+      if(group.getBucket() instanceof NumericBucket bins)
+      {
+         BigDecimal[] edges = edges(bins, ((Number) value).intValue());
+         return (AnalysisFormatting.interval(field, edges[0], edges[1]));
+      }
+      return (security.label(field, value));
+   }
+
+   /*******************************************************************************
+    ** Match core's final closed numeric bin and exterior bins.
+    *******************************************************************************/
+   private static BigDecimal[] edges(NumericBucket bins, int index)
+   {
+      BigDecimal width = bins.getMax().subtract(bins.getMin()).divide(BigDecimal.valueOf(bins.getCount()), MathContext.DECIMAL128);
+      return (new BigDecimal[] { index == 0 ? null : index > bins.getCount() ? bins.getMax() : bins.getMin().add(width.multiply(BigDecimal.valueOf(index - 1))),
+         index == 0 ? bins.getMin() : index > bins.getCount() ? null : index == bins.getCount() ? bins.getMax() : bins.getMin().add(width.multiply(BigDecimal.valueOf(index))) });
+   }
+
+   /*******************************************************************************
+    ** Calendar and numeric predicates use core bucket boundaries in the session zone.
+    *******************************************************************************/
+   private QQueryFilter condition(GroupBy group, QFieldMetaData field, Serializable value) throws QException
+   {
+      String name = group.getFieldName();
+      if(value == null || group.getBucket() == null)
+      {
+         return (DrillFilters.value(name, value, field.getType().isStringLike()));
+      }
+      if(group.getBucket() instanceof NumericBucket bins)
+      {
+         int index = ((Number) value).intValue();
+         BigDecimal[] edges = edges(bins, index);
+         if(StandInAnalysisBackend.isMemory(request.getTableName()) && index <= bins.getCount())
+         {
+            GroupBy raw = new GroupBy(field.getType(), name);
+            List<BigDecimal> members = aggregate(List.of(raw), request.getFilter(), List.of(count), null).stream()
+               .map(r -> r.getGroupByValues().get(raw)).filter(Objects::nonNull).map(v -> new BigDecimal(v.toString())).toList();
+            return (new StandInAnalysisBackend(config).numericRange(request.getTableName(), name, members, edges[0], edges[1], index == bins.getCount()));
+         }
+         return (index > bins.getCount() ? DrillFilters.condition(name, QCriteriaOperator.GREATER_THAN, bins.getMax())
+            : DrillFilters.range(name, edges[0], edges[1], index == bins.getCount()));
+      }
+      DateBucketUnit unit = ((DateBucket) group.getBucket()).getUnit();
+      if(unit == DateBucketUnit.HOUR)
+      {
+         Instant start = ValueUtils.getValueAsInstant(value);
+         return (DrillFilters.range(name, start, start.plusSeconds(3600), false));
+      }
+      if(unit == DateBucketUnit.DAY_OF_WEEK || unit == DateBucketUnit.HOUR_OF_DAY)
+      {
+         GroupBy raw = new GroupBy(field.getType(), name);
+         List<Serializable> members = aggregate(List.of(raw), request.getFilter(), List.of(count), null).stream().map(r -> r.getGroupByValues().get(raw))
+            .filter(v -> Objects.equals(AggregateAnalysis.bucket(v, group.getBucket(), ValueUtils.getSessionOrInstanceZoneId()), value)).toList();
+         return (DrillFilters.in(name, members));
+      }
+      LocalDate start = ValueUtils.getValueAsLocalDate(value);
+      LocalDate end = switch(unit)
+      {
+         case YEAR -> start.plusYears(1);
+         case QUARTER -> start.plusMonths(3);
+         case MONTH -> start.plusMonths(1);
+         case WEEK -> start.plusWeeks(1);
+         default -> start.plusDays(1);
+      };
+      return (DrillFilters.dateRange(name, start, end, field.getType() == QFieldType.DATE_TIME, ValueUtils.getSessionOrInstanceZoneId()));
+   }
+
+   /*******************************************************************************
+    ** A level is the full matrix, row totals, column totals or grand totals.
+    *******************************************************************************/
+   private Map<List<Slot>, List<Metric>> level(List<Dimension> dimensions) throws QException
+   {
+      List<Aggregate> requested = new ArrayList<>(measures);
+      if(!requested.contains(count))
+      {
+         requested.add(count);
+      }
+      Map<List<Slot>, List<AggregateResult>> grouped = new LinkedHashMap<>();
+      List<AggregateResult> aggregates = aggregate(dimensions.stream().map(Dimension::group).toList(), request.getFilter(), requested, null);
+      if(dimensions.isEmpty() && aggregates.isEmpty())
+      {
+         AggregateResult empty = new AggregateResult();
+         requested.forEach(a -> empty.withAggregateValue(a, a.getOperator() == AggregateOperator.COUNT || a.getOperator() == AggregateOperator.COUNT_DISTINCT ? 0L : null));
+         aggregates = List.of(empty);
+      }
+      for(AggregateResult row : aggregates)
+      {
+         List<Slot> key = new ArrayList<>();
+         for(Dimension dimension : dimensions)
+         {
+            key.add(dimension.slot(row.getGroupByValues().get(dimension.group()), config.getMinGroupSize()));
+         }
+         if(!key.contains(null))
+         {
+            grouped.computeIfAbsent(key, ignored -> new ArrayList<>()).add(row);
+         }
+      }
+      Map<List<Slot>, List<Metric>> result = new LinkedHashMap<>();
+      for(var entry : grouped.entrySet())
+      {
+         QQueryFilter predicate = new QQueryFilter();
+         entry.getKey().forEach(slot -> predicate.withSubFilter(slot.condition()));
+         AggregateResult row = entry.getValue().getFirst();
+         if(entry.getKey().stream().anyMatch(Slot::other))
+         {
+            var merged = aggregate(List.of(), DrillFilters.and(request.getFilter(), predicate), requested, null);
+            if(merged.isEmpty())
+            {
+               continue;
+            }
+            row = merged.getFirst();
+         }
+         List<Metric> values = new ArrayList<>();
+         for(int i = 0; i < measures.size(); i++)
+         {
+            PivotMeasureInfo info = measureInfo.get(i);
+            Serializable value = row.getAggregateValues().get(measures.get(i));
+            Metric metric = new Metric().withKey(info.getKey()).withLabel(info.getLabel()).withValueType(info.getValueType()).withValue(value)
+               .withFormatted(AnalysisFormatting.scalar(info.getKey(), value, info.getValueType()));
+            if(toManyTables.isEmpty() && multiHopTables.isEmpty())
+            {
+               metric.withDrill(DrillFilters.drill(request.getTableName(), request.getFilter(), predicate));
+            }
+            values.add(metric);
+         }
+         new MinGroupSizeGuard(config.getMinGroupSize()).pivotMetrics(values, count(row));
+         result.put(entry.getKey(), values);
+      }
+      return (result);
+   }
+
+   /*******************************************************************************
+    ** Count is always present, even when the user selects only non-count measures.
+    *******************************************************************************/
+   private long count(AggregateResult row)
+   {
+      Object value = row.getAggregateValues().get(count);
+      return (value == null ? 0 : ((Number) value).longValue());
+   }
+
+   /*******************************************************************************
+    ** Core owns SQL pushdown, joins, record locks, field locks and bounded fallback.
+    *******************************************************************************/
+   private List<AggregateResult> aggregate(List<GroupBy> groups, QQueryFilter filter, List<Aggregate> aggregates, Integer limit) throws QException
+   {
+      QQueryFilter query = DrillFilters.restoreTypes(request.getTableName(), filter);
+      if(limit != null)
+      {
+         query.withOrderBy(new QFilterOrderByAggregate(count, false));
+      }
+      AggregateInput input = new AggregateInput(request.getTableName()).withFilter(query).withQueryJoins(joins.stream().map(QueryJoin::clone).toList())
+         .withAllowStreamedFallback(true).withStreamedRowLimit(config.getMaxRows()).withLimit(limit);
+      groups.forEach(input::withGroupBy);
+      new LinkedHashSet<>(aggregates).forEach(input::withAggregate);
+      input.setPermissionEnforcement(PermissionEnforcement.ENFORCE);
+      var output = new AggregateAction().execute(input);
+      partial |= output.getTruncated();
+      return (limit == null ? output.getResults() : output.getResults().stream().sorted(java.util.Comparator.comparingLong(this::count).reversed()
+         .thenComparing(r -> String.valueOf(r.getGroupByValues().values()))).limit(limit).toList());
+   }
+
+   /*******************************************************************************
+    ** Header tuples retain typed values; Other is distinguished by its label.
+    *******************************************************************************/
+   private static List<List<PivotHeader>> headers(List<List<Slot>> keys)
+   {
+      return (keys.stream().map(key -> key.stream().map(slot -> new PivotHeader().withValue(slot.value()).withLabel(slot.label())).toList()).toList());
+   }
+
+   /*******************************************************************************
+    ** Partial numbers must never claim an exact drill equivalence.
+    *******************************************************************************/
+   private void disableDrills(List<Metric> metrics)
+   {
+      metrics.forEach(m -> m.withDrill(null).withEstimate(true));
+   }
+}
+

src/main/java/com/kingsrook/qbits/dataanalysis/engine/ProfileSupport.java

@@ -108,6 +108,15 @@ public final class ProfileSupport
     ** PVS translations run as the current user; drill values always remain ids.
     *******************************************************************************/
    public static List<ValueCount> values(String table, QQueryFilter filter, QFieldMetaData field, List<AnalysisBackend.GroupCount> groups, long total) throws QException
+   {
+      return (values(table, filter, field, groups, total, CoreTierFieldAccessResolver.defaultResolver()));
+   }
+
+   /*******************************************************************************
+    ** Host field policy applies to related display labels as well as source values.
+    *******************************************************************************/
+   public static List<ValueCount> values(String table, QQueryFilter filter, QFieldMetaData field, List<AnalysisBackend.GroupCount> groups, long total,
+      FieldAnalysisAccessResolver access) throws QException
    {
       List<ValueCount> result = new ArrayList<>();
       for(AnalysisBackend.GroupCount group : groups)
@@ -115,7 +124,7 @@ public final class ProfileSupport
          String label = group.label();
          if(field != null && field.getPossibleValueSourceName() != null && group.value() != null)
          {
-            String translated = AnalysisLabels.translate(field, group.value());
+            String translated = AnalysisLabels.translate(field, group.value(), access);
             if(translated != null)
             {
                label = translated;

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotCell.java

@@ -0,0 +1,89 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import java.util.ArrayList;
+import java.util.List;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotCell contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotCell implements Serializable
+{
+   private Integer rowIndex;
+   private Integer columnIndex;
+   private List<Metric> values = new ArrayList<>();
+
+
+   /*******************************************************************************
+    ** Getter for rowIndex.
+    *******************************************************************************/
+   public Integer getRowIndex()
+   {
+      return (rowIndex);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for rowIndex.
+    *******************************************************************************/
+   public PivotCell withRowIndex(Integer rowIndex)
+   {
+      this.rowIndex = rowIndex;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for columnIndex.
+    *******************************************************************************/
+   public Integer getColumnIndex()
+   {
+      return (columnIndex);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for columnIndex.
+    *******************************************************************************/
+   public PivotCell withColumnIndex(Integer columnIndex)
+   {
+      this.columnIndex = columnIndex;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for values.
+    *******************************************************************************/
+   public List<Metric> getValues()
+   {
+      return (values);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for values.
+    *******************************************************************************/
+   public PivotCell withValues(List<Metric> values)
+   {
+      this.values = values;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotDimension.java

@@ -0,0 +1,88 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.DateBucketUnit;
+
+/*******************************************************************************
+ ** Typed PivotDimension contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotDimension implements Serializable
+{
+   private String field;
+   private DateBucketUnit dateBucket;
+   private PivotNumericBins numericBins;
+
+
+   /*******************************************************************************
+    ** Getter for field.
+    *******************************************************************************/
+   public String getField()
+   {
+      return (field);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for field.
+    *******************************************************************************/
+   public PivotDimension withField(String field)
+   {
+      this.field = field;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for dateBucket.
+    *******************************************************************************/
+   public DateBucketUnit getDateBucket()
+   {
+      return (dateBucket);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for dateBucket.
+    *******************************************************************************/
+   public PivotDimension withDateBucket(DateBucketUnit dateBucket)
+   {
+      this.dateBucket = dateBucket;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for numericBins.
+    *******************************************************************************/
+   public PivotNumericBins getNumericBins()
+   {
+      return (numericBins);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for numericBins.
+    *******************************************************************************/
+   public PivotDimension withNumericBins(PivotNumericBins numericBins)
+   {
+      this.numericBins = numericBins;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotHeader.java

@@ -0,0 +1,69 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotHeader contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotHeader implements Serializable
+{
+   private Serializable value;
+   private String label;
+
+
+   /*******************************************************************************
+    ** Getter for value.
+    *******************************************************************************/
+   public Serializable getValue()
+   {
+      return (value);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for value.
+    *******************************************************************************/
+   public PivotHeader withValue(Serializable value)
+   {
+      this.value = value;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for label.
+    *******************************************************************************/
+   public String getLabel()
+   {
+      return (label);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for label.
+    *******************************************************************************/
+   public PivotHeader withLabel(String label)
+   {
+      this.label = label;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotMeasure.java

@@ -0,0 +1,95 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import java.math.BigDecimal;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotMeasure contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotMeasure implements Serializable
+{
+   private Operator operator;
+   private String field;
+   private BigDecimal percentile;
+
+   /*******************************************************************************
+    ** Supported pivot measures. COUNT counts records, regardless of field nulls.
+    *******************************************************************************/
+   public enum Operator
+   {
+      COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT, PERCENTILE
+   }
+
+   /*******************************************************************************
+    ** Getter for operator.
+    *******************************************************************************/
+   public Operator getOperator()
+   {
+      return (operator);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for operator.
+    *******************************************************************************/
+   public PivotMeasure withOperator(Operator operator)
+   {
+      this.operator = operator;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for field.
+    *******************************************************************************/
+   public String getField()
+   {
+      return (field);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for field.
+    *******************************************************************************/
+   public PivotMeasure withField(String field)
+   {
+      this.field = field;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for percentile.
+    *******************************************************************************/
+   public BigDecimal getPercentile()
+   {
+      return (percentile);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for percentile.
+    *******************************************************************************/
+   public PivotMeasure withPercentile(BigDecimal percentile)
+   {
+      this.percentile = percentile;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotMeasureInfo.java

@@ -0,0 +1,87 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotMeasureInfo contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotMeasureInfo implements Serializable
+{
+   private String key;
+   private String label;
+   private ValueType valueType;
+
+
+   /*******************************************************************************
+    ** Getter for key.
+    *******************************************************************************/
+   public String getKey()
+   {
+      return (key);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for key.
+    *******************************************************************************/
+   public PivotMeasureInfo withKey(String key)
+   {
+      this.key = key;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for label.
+    *******************************************************************************/
+   public String getLabel()
+   {
+      return (label);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for label.
+    *******************************************************************************/
+   public PivotMeasureInfo withLabel(String label)
+   {
+      this.label = label;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for valueType.
+    *******************************************************************************/
+   public ValueType getValueType()
+   {
+      return (valueType);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for valueType.
+    *******************************************************************************/
+   public PivotMeasureInfo withValueType(ValueType valueType)
+   {
+      this.valueType = valueType;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotNumericBins.java

@@ -0,0 +1,88 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import java.math.BigDecimal;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotNumericBins contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotNumericBins implements Serializable
+{
+   private Integer count = 20;
+   private BigDecimal min;
+   private BigDecimal max;
+
+
+   /*******************************************************************************
+    ** Getter for count.
+    *******************************************************************************/
+   public Integer getCount()
+   {
+      return (count);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for count.
+    *******************************************************************************/
+   public PivotNumericBins withCount(Integer count)
+   {
+      this.count = count;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for min.
+    *******************************************************************************/
+   public BigDecimal getMin()
+   {
+      return (min);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for min.
+    *******************************************************************************/
+   public PivotNumericBins withMin(BigDecimal min)
+   {
+      this.min = min;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for max.
+    *******************************************************************************/
+   public BigDecimal getMax()
+   {
+      return (max);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for max.
+    *******************************************************************************/
+   public PivotNumericBins withMax(BigDecimal max)
+   {
+      this.max = max;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotRequest.java

@@ -0,0 +1,182 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.util.ArrayList;
+import java.util.List;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+
+/*******************************************************************************
+ ** Typed PivotRequest contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotRequest implements AnalysisRequest
+{
+   private String tableName;
+   private QQueryFilter filter;
+   private Boolean exact = true;
+   private List<PivotDimension> rows = new ArrayList<>();
+   private List<PivotDimension> columns = new ArrayList<>();
+   private List<PivotMeasure> values = new ArrayList<>();
+   private Boolean showTotals = true;
+   private Integer limitGroups = 50;
+
+
+   /*******************************************************************************
+    ** Getter for tableName.
+    *******************************************************************************/
+   @Override
+   public String getTableName()
+   {
+      return (tableName);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for tableName.
+    *******************************************************************************/
+   public PivotRequest withTableName(String tableName)
+   {
+      this.tableName = tableName;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for filter.
+    *******************************************************************************/
+   @Override
+   public QQueryFilter getFilter()
+   {
+      return (filter);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for filter.
+    *******************************************************************************/
+   public PivotRequest withFilter(QQueryFilter filter)
+   {
+      this.filter = filter;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for exact.
+    *******************************************************************************/
+   @Override
+   public Boolean getExact()
+   {
+      return (exact);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for exact.
+    *******************************************************************************/
+   public PivotRequest withExact(Boolean exact)
+   {
+      this.exact = exact;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for rows.
+    *******************************************************************************/
+   public List<PivotDimension> getRows()
+   {
+      return (rows);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for rows.
+    *******************************************************************************/
+   public PivotRequest withRows(List<PivotDimension> rows)
+   {
+      this.rows = rows;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for columns.
+    *******************************************************************************/
+   public List<PivotDimension> getColumns()
+   {
+      return (columns);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for columns.
+    *******************************************************************************/
+   public PivotRequest withColumns(List<PivotDimension> columns)
+   {
+      this.columns = columns;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for values.
+    *******************************************************************************/
+   public List<PivotMeasure> getValues()
+   {
+      return (values);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for values.
+    *******************************************************************************/
+   public PivotRequest withValues(List<PivotMeasure> values)
+   {
+      this.values = values;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for showTotals.
+    *******************************************************************************/
+   public Boolean getShowTotals()
+   {
+      return (showTotals);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for showTotals.
+    *******************************************************************************/
+   public PivotRequest withShowTotals(Boolean showTotals)
+   {
+      this.showTotals = showTotals;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for limitGroups.
+    *******************************************************************************/
+   public Integer getLimitGroups()
+   {
+      return (limitGroups);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for limitGroups.
+    *******************************************************************************/
+   public PivotRequest withLimitGroups(Integer limitGroups)
+   {
+      this.limitGroups = limitGroups;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/result/PivotResult.java

@@ -0,0 +1,252 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis.result;
+
+import java.io.Serializable;
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+
+/*******************************************************************************
+ ** Typed PivotResult contract.
+ *******************************************************************************/
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+public class PivotResult implements Serializable
+{
+   private List<List<PivotHeader>> rowHeaders = new ArrayList<>();
+   private List<List<PivotHeader>> columnHeaders = new ArrayList<>();
+   private List<PivotMeasureInfo> measures = new ArrayList<>();
+   private List<PivotCell> cells = new ArrayList<>();
+   private List<List<Metric>> rowTotals = new ArrayList<>();
+   private List<List<Metric>> columnTotals = new ArrayList<>();
+   private List<Metric> grandTotals = new ArrayList<>();
+   private Boolean truncatedRows = false;
+   private Boolean truncatedColumns = false;
+   private Boolean estimate = false;
+   private List<String> warnings = new ArrayList<>();
+   private Instant asOf;
+
+
+   /*******************************************************************************
+    ** Getter for rowHeaders.
+    *******************************************************************************/
+   public List<List<PivotHeader>> getRowHeaders()
+   {
+      return (rowHeaders);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for rowHeaders.
+    *******************************************************************************/
+   public PivotResult withRowHeaders(List<List<PivotHeader>> rowHeaders)
+   {
+      this.rowHeaders = rowHeaders;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for columnHeaders.
+    *******************************************************************************/
+   public List<List<PivotHeader>> getColumnHeaders()
+   {
+      return (columnHeaders);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for columnHeaders.
+    *******************************************************************************/
+   public PivotResult withColumnHeaders(List<List<PivotHeader>> columnHeaders)
+   {
+      this.columnHeaders = columnHeaders;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for measures.
+    *******************************************************************************/
+   public List<PivotMeasureInfo> getMeasures()
+   {
+      return (measures);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for measures.
+    *******************************************************************************/
+   public PivotResult withMeasures(List<PivotMeasureInfo> measures)
+   {
+      this.measures = measures;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for cells.
+    *******************************************************************************/
+   public List<PivotCell> getCells()
+   {
+      return (cells);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for cells.
+    *******************************************************************************/
+   public PivotResult withCells(List<PivotCell> cells)
+   {
+      this.cells = cells;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for rowTotals.
+    *******************************************************************************/
+   public List<List<Metric>> getRowTotals()
+   {
+      return (rowTotals);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for rowTotals.
+    *******************************************************************************/
+   public PivotResult withRowTotals(List<List<Metric>> rowTotals)
+   {
+      this.rowTotals = rowTotals;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for columnTotals.
+    *******************************************************************************/
+   public List<List<Metric>> getColumnTotals()
+   {
+      return (columnTotals);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for columnTotals.
+    *******************************************************************************/
+   public PivotResult withColumnTotals(List<List<Metric>> columnTotals)
+   {
+      this.columnTotals = columnTotals;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for grandTotals.
+    *******************************************************************************/
+   public List<Metric> getGrandTotals()
+   {
+      return (grandTotals);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for grandTotals.
+    *******************************************************************************/
+   public PivotResult withGrandTotals(List<Metric> grandTotals)
+   {
+      this.grandTotals = grandTotals;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for truncatedRows.
+    *******************************************************************************/
+   public Boolean getTruncatedRows()
+   {
+      return (truncatedRows);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for truncatedRows.
+    *******************************************************************************/
+   public PivotResult withTruncatedRows(Boolean truncatedRows)
+   {
+      this.truncatedRows = truncatedRows;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for truncatedColumns.
+    *******************************************************************************/
+   public Boolean getTruncatedColumns()
+   {
+      return (truncatedColumns);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for truncatedColumns.
+    *******************************************************************************/
+   public PivotResult withTruncatedColumns(Boolean truncatedColumns)
+   {
+      this.truncatedColumns = truncatedColumns;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for estimate.
+    *******************************************************************************/
+   public Boolean getEstimate()
+   {
+      return (estimate);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for estimate.
+    *******************************************************************************/
+   public PivotResult withEstimate(Boolean estimate)
+   {
+      this.estimate = estimate;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for warnings.
+    *******************************************************************************/
+   public List<String> getWarnings()
+   {
+      return (warnings);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for warnings.
+    *******************************************************************************/
+   public PivotResult withWarnings(List<String> warnings)
+   {
+      this.warnings = warnings;
+      return (this);
+   }
+
+   /*******************************************************************************
+    ** Getter for asOf.
+    *******************************************************************************/
+   public Instant getAsOf()
+   {
+      return (asOf);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for asOf.
+    *******************************************************************************/
+   public PivotResult withAsOf(Instant asOf)
+   {
+      this.asOf = asOf;
+      return (this);
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/run/AnalysisRunService.java

@@ -441,7 +441,8 @@ public class AnalysisRunService
       identity.put("zone", ValueUtils.getSessionOrInstanceZoneId().getId());
       identity.put("config", service.getConfigResolver().resolve(request.getTableName()));
       identity.put("fields", service.capabilities(request.getTableName()).getFields().stream().sorted().toList());
-      identity.put("policy", com.kingsrook.qbits.dataanalysis.engine.AnalysisLabels.policy(request.getTableName()));
+      identity.put("fieldAccess", digest(service.fieldAccessFingerprint(request.getTableName())));
+      identity.put("policy", service.fieldAccessPolicy(request.getTableName()));
       identity.put("backendVariants", QContext.getQSession().getBackendVariants());
       try
       {

src/main/resources/openapi/qqq-v1/data-analysis.yaml

@@ -638,6 +638,206 @@ components:
           nullable: true
           type: "array"
       type: "object"
+    DataAnalysisPivotCellV1:
+      properties:
+        rowIndex:
+          description: "Zero-based index in rowHeaders."
+          type: "integer"
+        columnIndex:
+          description: "Zero-based index in columnHeaders."
+          type: "integer"
+        values:
+          description: "Metrics in requested measure order; suppressed metrics have\
+            \ no value or drill."
+          items:
+            $ref: "#/components/schemas/DataAnalysisMetricV1"
+          type: "array"
+      type: "object"
+    DataAnalysisPivotDimensionV1:
+      properties:
+        field:
+          description: "FULL field name, optionally joinTable.field through an exposed\
+            \ join."
+          type: "string"
+        dateBucket:
+          description: "Optional calendar or cyclic bucket in the session timezone."
+          enum:
+          - "YEAR"
+          - "QUARTER"
+          - "MONTH"
+          - "WEEK"
+          - "DAY"
+          - "HOUR"
+          - "DAY_OF_WEEK"
+          - "HOUR_OF_DAY"
+          type: "string"
+        numericBins:
+          $ref: "#/components/schemas/DataAnalysisPivotNumericBinsV1"
+          description: "Optional equal-width numeric bins; mutually exclusive with\
+            \ dateBucket."
+      required:
+      - "field"
+      type: "object"
+    DataAnalysisPivotHeaderV1:
+      properties:
+        value:
+          description: "Typed group key; null for a blank group or Other."
+          nullable: true
+        label:
+          description: "Display label, including Other (k groups)."
+          type: "string"
+      type: "object"
+    DataAnalysisPivotMeasureInfoV1:
+      properties:
+        key:
+          description: "Stable measure key by request position."
+          type: "string"
+        label:
+          description: "Display label."
+          type: "string"
+        valueType:
+          description: "Semantic metric type."
+          enum:
+          - "COUNT"
+          - "DECIMAL"
+          - "PERCENT"
+          - "DATE"
+          - "DATE_TIME"
+          - "DURATION"
+          - "TEXT"
+          type: "string"
+      type: "object"
+    DataAnalysisPivotMeasureV1:
+      properties:
+        operator:
+          description: "Aggregate operator. COUNT counts records, including null values."
+          enum:
+          - "COUNT"
+          - "SUM"
+          - "AVG"
+          - "MIN"
+          - "MAX"
+          - "COUNT_DISTINCT"
+          - "PERCENTILE"
+          type: "string"
+        field:
+          description: "FULL field to aggregate; optional for COUNT."
+          type: "string"
+        percentile:
+          description: "PERCENTILE only: percentile_cont fraction from 0 to 1."
+          type: "number"
+      required:
+      - "operator"
+      type: "object"
+    DataAnalysisPivotNumericBinsV1:
+      properties:
+        count:
+          description: "Number of bins, 1–200; defaults to 20."
+          type: "integer"
+        min:
+          description: "Optional explicit lower bound; otherwise a nice enclosing\
+            \ edge."
+          type: "number"
+        max:
+          description: "Optional explicit upper bound; final interior bin includes\
+            \ this value."
+          type: "number"
+      type: "object"
+    DataAnalysisPivotRequestV1:
+      properties:
+        filter:
+          $ref: "#/components/schemas/QueryFilter"
+          description: "User query filter, passed through for core enforcement."
+        rows:
+          description: "One to three row dimensions."
+          items:
+            $ref: "#/components/schemas/DataAnalysisPivotDimensionV1"
+          type: "array"
+        columns:
+          description: "Zero to two column dimensions; defaults to empty."
+          items:
+            $ref: "#/components/schemas/DataAnalysisPivotDimensionV1"
+          type: "array"
+        values:
+          description: "One to six measures."
+          items:
+            $ref: "#/components/schemas/DataAnalysisPivotMeasureV1"
+          type: "array"
+        showTotals:
+          description: "Include row, column and grand totals; defaults to true."
+          type: "boolean"
+        limitGroups:
+          description: "Kept groups per dimension; default 50, maximum 200."
+          type: "integer"
+      required:
+      - "rows"
+      - "values"
+      type: "object"
+    DataAnalysisPivotV1:
+      properties:
+        rowHeaders:
+          description: "Row header tuples in cell-index order."
+          items:
+            items:
+              $ref: "#/components/schemas/DataAnalysisPivotHeaderV1"
+            type: "array"
+          type: "array"
+        columnHeaders:
+          description: "Column header tuples; an empty tuple represents no column\
+            \ dimensions."
+          items:
+            items:
+              $ref: "#/components/schemas/DataAnalysisPivotHeaderV1"
+            type: "array"
+          type: "array"
+        measures:
+          description: "Measure metadata in request order."
+          items:
+            $ref: "#/components/schemas/DataAnalysisPivotMeasureInfoV1"
+          type: "array"
+        cells:
+          description: "Sparse cells; absent combinations are not materialized."
+          items:
+            $ref: "#/components/schemas/DataAnalysisPivotCellV1"
+          type: "array"
+        rowTotals:
+          description: "Metric tuples aligned with rowHeaders; empty when totals disabled."
+          items:
+            items:
+              $ref: "#/components/schemas/DataAnalysisMetricV1"
+            type: "array"
+          type: "array"
+        columnTotals:
+          description: "Metric tuples aligned with columnHeaders; empty when totals\
+            \ disabled."
+          items:
+            items:
+              $ref: "#/components/schemas/DataAnalysisMetricV1"
+            type: "array"
+          type: "array"
+        grandTotals:
+          description: "Grand-total metrics; empty when totals disabled."
+          items:
+            $ref: "#/components/schemas/DataAnalysisMetricV1"
+          type: "array"
+        truncatedRows:
+          description: "Row groups were merged into Other."
+          type: "boolean"
+        truncatedColumns:
+          description: "Column groups were merged into Other."
+          type: "boolean"
+        estimate:
+          description: "True when bounded streamed fallback produced a partial result."
+          type: "boolean"
+        warnings:
+          description: "Privacy, partial-result and joined-drill limitations."
+          items:
+            type: "string"
+          type: "array"
+        asOf:
+          description: "UTC timestamp of computation."
+          type: "string"
+      type: "object"
     DataAnalysisPointV1:
       nullable: true
       properties:
@@ -966,10 +1166,10 @@ components:
 info:
   contact:
     email: "contact@kingsrook.com"
-  description: "Profiles, distinct values, duplicate groups and exact analysis runs\
-    \ as the signed-in user."
+  description: "Profiles, pivots, distinct values, duplicate groups and exact analysis\
+    \ runs as the signed-in user."
   title: "QQQ Middleware API extension: data-analysis"
-  version: "1.0.0"
+  version: "1.1.0"
 openapi: "3.0.3"
 paths:
   /qqq/v1/qbit/data-analysis/tables/{table}/capabilities:
@@ -1051,6 +1251,35 @@ paths:
       - sessionUuidCookie:
         - "N/A"
       summary: "List distinct column values"
+  /qqq/v1/qbit/data-analysis/tables/{table}/pivot:
+    post:
+      description: "Group records by up to 3 row and 2 column fields (with date or\
+        \ numeric buckets) and compute counts, sums, averages, min/max, distinct counts\
+        \ or percentiles per cell; every cell has a drill-through filter."
+      parameters:
+      - description: "QQQ table name to analyze."
+        in: "path"
+        name: "table"
+        required: true
+        schema:
+          type: "string"
+      requestBody:
+        content:
+          application/json:
+            schema:
+              $ref: "#/components/schemas/DataAnalysisPivotRequestV1"
+        required: true
+      responses:
+        200:
+          content:
+            application/json:
+              schema:
+                $ref: "#/components/schemas/DataAnalysisPivotV1"
+          description: "Successful Response"
+      security:
+      - sessionUuidCookie:
+        - "N/A"
+      summary: "Explore a pivot table"
   /qqq/v1/qbit/data-analysis/tables/{table}/table-profile:
     post:
       description: "Profile readable fields under a filter for completeness, uniqueness,\

src/test/java/com/kingsrook/qbits/dataanalysis/CoreAnalysisBackendTest.java

@@ -202,4 +202,28 @@ class CoreAnalysisBackendTest extends EngineFixture
          });
       }
    }
+
+   /*******************************************************************************
+    ** Core fallback limits never produce a falsely exact pivot drill.
+    *******************************************************************************/
+   @Test
+   void partialPivotDisablesDrills() throws Exception
+   {
+      capabilities(Set.of());
+      config.withDefaultMaxRows(3);
+      service.getConfigResolver().invalidate();
+      var request = new com.kingsrook.qbits.dataanalysis.result.PivotRequest().withTableName("orders")
+         .withRows(java.util.List.of(new com.kingsrook.qbits.dataanalysis.result.PivotDimension().withField("created")
+            .withDateBucket(com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.DateBucketUnit.DAY)))
+         .withValues(java.util.List.of(new com.kingsrook.qbits.dataanalysis.result.PivotMeasure().withField("amount")
+            .withOperator(com.kingsrook.qbits.dataanalysis.result.PivotMeasure.Operator.PERCENTILE).withPercentile(new java.math.BigDecimal("0.5"))));
+      var result = service.pivot(request);
+      assertThat(result.getEstimate()).isTrue();
+      assertThat(result.getWarnings()).anyMatch(w -> w.startsWith("Partial pivot"));
+      assertThat(result.getCells()).isNotEmpty().allSatisfy(c -> assertThat(c.getValues()).allSatisfy(m ->
+      {
+         assertThat(m.getDrill()).isNull();
+         assertThat(m.getEstimate()).isTrue();
+      }));
+   }
 }

src/test/java/com/kingsrook/qbits/dataanalysis/DataAnalysisApiTest.java

@@ -169,7 +169,7 @@ class DataAnalysisApiTest extends EngineFixture
          assertThat(denied.getStatus()).isEqualTo(403);
          assertThat(run.path(operation.equals("COLUMN_PROFILE") ? "columnProfile" : "tableProfile").isObject()).isTrue();
       }
-      assertThat(invoker.listOperations().stream().filter(o -> o.pathTemplate().startsWith(DataAnalysisApi.PREFIX)).toList()).hasSize(7);
+      assertThat(invoker.listOperations().stream().filter(o -> o.pathTemplate().startsWith(DataAnalysisApi.PREFIX)).toList()).hasSize(8);
       var client = java.net.http.HttpClient.newHttpClient();
       var response = client.send(java.net.http.HttpRequest.newBuilder(java.net.URI.create("http://127.0.0.1:" + server.getBoundPort() + "/qqq/v1/openapi.json")).build(), java.net.http.HttpResponse.BodyHandlers.ofString());
       assertThat(response.statusCode()).isEqualTo(200);
@@ -255,7 +255,7 @@ class DataAnalysisApiTest extends EngineFixture
       call(HttpMethod.POST, "/runs", Map.of("operation", "PIVOT"), 400);
       call(HttpMethod.POST, "/runs", com.fasterxml.jackson.databind.node.NullNode.getInstance(), 400);
       call(HttpMethod.POST, "/tables/{table}/column-profile", Map.of("fieldName", "amount", "filter",
-         Map.of("criteria", List.of(Map.of("fieldName", "secret", "operator", "EQUALS", "values", List.of("hidden"))))), 400);
+         Map.of("criteria", List.of(Map.of("fieldName", "secret", "operator", "EQUALS", "values", List.of("hidden"))))), 403);
       var absent = invoker.invoke(new UiApiRequest().withMethod(HttpMethod.GET).withPath(DataAnalysisApi.PREFIX + "/tables/{table}/capabilities")
          .withPathParam("table", "missing").withSessionUUID(caller).withUserTimezone("America/Chicago"));
       assertThat(absent.getStatus()).isEqualTo(404);
@@ -269,7 +269,7 @@ class DataAnalysisApiTest extends EngineFixture
    }
 
    /*******************************************************************************
-    ** Generated fragment is committed verbatim and has only the seven operations.
+    ** Generated fragment is committed verbatim and has only the eight operations.
     *******************************************************************************/
    @Test
    void contract() throws Exception
@@ -286,5 +286,38 @@ class DataAnalysisApiTest extends EngineFixture
       assertThat(Files.exists(path)).as("Regenerate with ./mvnw -o test -Dtest=DataAnalysisApiTest#contract -Dopenapi.write=true -Dqqq.snapshot.version=4.1.0-DATAANALYSIS-SNAPSHOT").isTrue();
       assertThat(Files.readString(path).replace("\r\n", "\n")).as("OpenAPI changed: regenerate with -Dopenapi.write=true").isEqualTo(yaml.replace("\r\n", "\n"));
    }
+   @Test
+   void pivotHttp() throws Exception
+   {
+      start();
+      var body = Map.of("rows", List.of(Map.of("field", "status")), "columns", List.of(Map.of("field", "customer")),
+         "values", List.of(Map.of("operator", "COUNT")));
+      JsonNode result = call(HttpMethod.POST, "/tables/{table}/pivot", body, 200);
+      assertThat(result.path("cells").size()).isEqualTo(4);
+      checkDrills(result);
+      checkDrills(call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", "paid")),
+         "values", List.of(Map.of("operator", "COUNT")), "limitGroups", 1), 200));
+      for(String field : List.of("note", "amount", "quantity"))
+      {
+         checkDrills(call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", field)),
+            "values", List.of(Map.of("operator", "COUNT")), "limitGroups", 1), 200));
+      }
+      for(String unit : List.of("DAY", "HOUR", "DAY_OF_WEEK", "HOUR_OF_DAY"))
+      {
+         checkDrills(call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", "created", "dateBucket", unit)),
+            "values", List.of(Map.of("operator", "COUNT"))), 200));
+      }
+      checkDrills(call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", "amount", "numericBins", Map.of("count", 10))),
+         "values", List.of(Map.of("operator", "COUNT"))), 200));
+      for(String field : List.of("secret", "missing"))
+      {
+         call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", field)), "values", List.of(Map.of("operator", "COUNT"))), 400);
+         call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", "status")), "values", List.of(Map.of("operator", "MIN", "field", field))), 400);
+      }
+      call(HttpMethod.POST, "/tables/{table}/pivot", Map.of(), 400);
+      call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", "status", "values", List.of(Map.of("operator", "COUNT"))), 400);
+      call(HttpMethod.POST, "/tables/{table}/pivot", Map.of("rows", List.of(Map.of("field", "status")), "values", List.of(Map.of("operator", "COUNT")), "limitGroups", 1.5), 400);
+      caller = "denied";
+      call(HttpMethod.POST, "/tables/{table}/pivot", body, 403);
+   }
 }
-

src/test/java/com/kingsrook/qbits/dataanalysis/EngineContractTest.java

@@ -61,7 +61,7 @@ import static org.assertj.core.api.Assertions.within;
 /*******************************************************************************
  ** Shared backend acceptance tests, including a generic drill equivalence walker.
  *******************************************************************************/
-abstract class EngineContractTest extends EngineFixture
+abstract class EngineContractTest extends PivotContractTest
 {
    /*******************************************************************************
     ** Every emitted key across all profile kinds has a label, on every backend.
@@ -249,7 +249,7 @@ abstract class EngineContractTest extends EngineFixture
    void permissionChecks() throws Exception
    {
       assertThat(service.capabilities("orders").getFields()).doesNotContain("secret");
-      assertThatThrownBy(() -> service.columnProfile(column("secret"))).isInstanceOf(QPermissionDeniedException.class);
+      assertThatThrownBy(() -> service.columnProfile(column("secret"))).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
       QContext.init(instance, TestSupport.user("alice").withPermission("orders.analyze"));
       assertThatThrownBy(() -> service.columnProfile(column("amount"))).isInstanceOf(QPermissionDeniedException.class);
       QContext.init(instance, TestSupport.user("alice").withPermission("orders.read"));
@@ -258,7 +258,7 @@ abstract class EngineContractTest extends EngineFixture
       config.withTableDefaults(Map.of("orders", new TableAnalysisDefaults().withExcludedFields(Set.of("amount"))));
       service.getConfigResolver().invalidate();
       assertThat(service.capabilities("orders").getFields()).doesNotContain("amount");
-      assertThatThrownBy(() -> service.columnProfile(column("amount"))).isInstanceOf(QPermissionDeniedException.class);
+      assertThatThrownBy(() -> service.columnProfile(column("amount"))).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
       assertThatThrownBy(() -> service.capabilities("missing")).isInstanceOf(QPermissionDeniedException.class);
    }
 

src/test/java/com/kingsrook/qbits/dataanalysis/FieldAnalysisAccessTest.java

@@ -0,0 +1,244 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis;
+
+import java.util.List;
+import java.util.concurrent.atomic.AtomicReference;
+import com.kingsrook.qbits.dataanalysis.engine.CoreTierFieldAccessResolver;
+import com.kingsrook.qbits.dataanalysis.engine.DrillFilters;
+import com.kingsrook.qbits.dataanalysis.engine.FieldAnalysisAccess;
+import com.kingsrook.qbits.dataanalysis.engine.FieldAnalysisAccessResolver;
+import com.kingsrook.qbits.dataanalysis.engine.FieldSecurityLockFieldAccessResolver;
+import com.kingsrook.qbits.dataanalysis.result.DistinctValuesRequest;
+import com.kingsrook.qbits.dataanalysis.result.ValueCount;
+import com.kingsrook.qbits.dataanalysis.result.ColumnProfile;
+import com.kingsrook.qbits.dataanalysis.result.ColumnProfileRequest;
+import com.kingsrook.qbits.dataanalysis.result.Metric;
+import com.kingsrook.qbits.dataanalysis.result.PivotCell;
+import com.kingsrook.qbits.dataanalysis.result.PivotDimension;
+import com.kingsrook.qbits.dataanalysis.result.PivotMeasure;
+import com.kingsrook.qbits.dataanalysis.result.PivotRequest;
+import com.kingsrook.qbits.dataanalysis.result.PivotResult;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.utils.JsonUtils;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+import static org.assertj.core.api.Assertions.within;
+
+/*******************************************************************************
+ ** Exercise reflection without shadowing a future real core package on test classpaths.
+ *******************************************************************************/
+class FieldAnalysisAccessTest extends EngineFixture
+{
+   /*******************************************************************************
+    ** Test helper for field-access behavior.
+    *******************************************************************************/
+   public static class TierApi
+   {
+      static String tier = "FULL";
+      static boolean fail;
+      /*******************************************************************************
+       ** Verify or support the pivot and field-access contract.
+       *******************************************************************************/
+      public static String getTier(QTableMetaData table, String field)
+      {
+         if(fail)
+         {
+            throw new IllegalStateException("unavailable");
+         }
+         return (tier);
+      }
+      /*******************************************************************************
+       ** Verify or support the pivot and field-access contract.
+       *******************************************************************************/
+      public static String getAccessFingerprint()
+      {
+         if(fail)
+         {
+            throw new IllegalStateException("unavailable");
+         }
+         return ("policy-v1");
+      }
+   }
+
+   /*******************************************************************************
+    ** Test helper for field-access behavior.
+    *******************************************************************************/
+   public static class TierOnlyApi
+   {
+      /*******************************************************************************
+       ** Verify or support the pivot and field-access contract.
+       *******************************************************************************/
+      public static String getTier(QTableMetaData table, String field)
+      {
+         return ("FULL");
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void legacyFallbackAndReflectiveTierMapping() throws Exception
+   {
+      var table = instance.getTable("orders");
+      var fallback = new FieldSecurityLockFieldAccessResolver();
+      assertThat(fallback.resolve(table, table.getField("amount"))).isEqualTo(FieldAnalysisAccess.FULL);
+      assertThat(fallback.resolve(table, table.getField("secret"))).isEqualTo(FieldAnalysisAccess.NONE);
+      // The snapshot has no tier package. Test the actual default selection.
+      assertThat(CoreTierFieldAccessResolver.defaultResolver()).isInstanceOf(FieldSecurityLockFieldAccessResolver.class);
+      var resolver = new CoreTierFieldAccessResolver(TierApi.class);
+      try
+      {
+         for(String tier : List.of("FULL", "REVEALABLE", "MASKED", "HIDDEN", "UNKNOWN"))
+         {
+            TierApi.tier = tier;
+            assertThat(resolver.resolve(table, table.getField("amount"))).isEqualTo(tier.equals("FULL") ? FieldAnalysisAccess.FULL : tier.equals("REVEALABLE") ? FieldAnalysisAccess.EXACT_MATCH_ONLY : FieldAnalysisAccess.NONE);
+         }
+         assertThat(resolver.getAccessFingerprint()).isEqualTo("policy-v1");
+         TierApi.fail = true;
+         assertThat(resolver.resolve(table, table.getField("amount"))).isEqualTo(FieldAnalysisAccess.NONE);
+         assertThatThrownBy(resolver::getAccessFingerprint).isInstanceOf(IllegalStateException.class);
+         assertThat(new CoreTierFieldAccessResolver(TierOnlyApi.class).getAccessFingerprint()).isEmpty();
+      }
+      finally
+      {
+         TierApi.fail = false;
+         TierApi.tier = "FULL";
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void customPolicyAndUserFilterPassThrough() throws Exception
+   {
+      config.withFieldAnalysisAccessResolver(() -> (table, field) -> field.getName().equals("note") ? FieldAnalysisAccess.NONE
+         : field.getName().equals("paid") ? FieldAnalysisAccess.EXACT_MATCH_ONLY : new FieldSecurityLockFieldAccessResolver().resolve(table, field));
+      service = new DataAnalysisService(config, Runnable::run);
+      assertThat(service.capabilities("orders").getFields()).doesNotContain("note", "paid", "secret");
+      for(String field : List.of("note", "paid"))
+      {
+         assertThatThrownBy(() -> service.columnProfile(new ColumnProfileRequest().withTableName("orders").withFieldName(field)))
+            .isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+         assertThatThrownBy(() -> service.pivot(new PivotRequest().withTableName("orders").withRows(List.of(new PivotDimension().withField(field)))
+            .withValues(List.of(new PivotMeasure().withOperator(PivotMeasure.Operator.COUNT)))))
+            .isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+         assertThatThrownBy(() -> service.pivot(new PivotRequest().withTableName("orders").withRows(List.of(new PivotDimension().withField("status")))
+            .withValues(List.of(new PivotMeasure().withOperator(PivotMeasure.Operator.MIN).withField(field)))))
+            .isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+      }
+      var request = new PivotRequest().withTableName("orders").withRows(List.of(new PivotDimension().withField("status")))
+         .withValues(List.of(new PivotMeasure().withOperator(PivotMeasure.Operator.COUNT)));
+      assertThat(JsonUtils.toJson(service.pivot(request))).doesNotContain("\"fieldName\":\"note\"", "\"fieldName\":\"paid\"");
+      var ownFilter = DrillFilters.condition("paid", QCriteriaOperator.EQUALS, true);
+      String before = JsonUtils.toJson(ownFilter);
+      PivotResult result = service.pivot(request.withFilter(ownFilter));
+      assertThat(JsonUtils.toJson(ownFilter)).isEqualTo(before);
+      assertThat(((Number)result.getGrandTotals().getFirst().getValue()).intValue()).isEqualTo(6);
+      for(PivotCell cell : result.getCells())
+      {
+         var drill = cell.getValues().getFirst().getDrill().getFilter();
+         assertThat(JsonUtils.toJson(drill.getSubFilters().getFirst())).isEqualTo(before);
+         assertThat(JsonUtils.toJson(drill.getSubFilters().get(1))).doesNotContain("paid", "note");
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void cacheMissesOnFieldTierAndCoreFingerprintChanges() throws Exception
+   {
+      AtomicReference<FieldAnalysisAccess> tier = new AtomicReference<>(FieldAnalysisAccess.FULL);
+      AtomicReference<String> fingerprint = new AtomicReference<>("first");
+      config.withFieldAnalysisAccessResolver(() -> new FieldAnalysisAccessResolver()
+      {
+         /*******************************************************************************
+          ** Verify or support the pivot and field-access contract.
+          *******************************************************************************/
+         @Override
+         public FieldAnalysisAccess resolve(QTableMetaData table, QFieldMetaData field)
+         {
+            return (field.getName().equals("quantity") ? tier.get() : new FieldSecurityLockFieldAccessResolver().resolve(table, field));
+         }
+         /*******************************************************************************
+          ** Verify or support the pivot and field-access contract.
+          *******************************************************************************/
+         @Override
+         public String getAccessFingerprint()
+         {
+            return (fingerprint.get());
+         }
+      });
+      service = new DataAnalysisService(config, Runnable::run);
+      ColumnProfileRequest request = new ColumnProfileRequest().withTableName("orders").withFieldName("amount").withExact(true);
+      String first = service.startExactRun(request);
+      assertThat(service.startExactRun(request)).isEqualTo(first);
+      tier.set(FieldAnalysisAccess.EXACT_MATCH_ONLY);
+      String second = service.startExactRun(request);
+      assertThat(second).isNotEqualTo(first);
+      assertThatThrownBy(() -> service.getRun(first)).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException.class);
+      fingerprint.set("second");
+      assertThat(service.startExactRun(request)).isNotEqualTo(second);
+   }
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void generatedRepresentativeDrillsCannotUseRestrictedPrimaryKey() throws Exception
+   {
+      config.withFieldAnalysisAccessResolver(() -> (table, field) -> field.getName().equals("id") ? FieldAnalysisAccess.NONE
+         : new FieldSecurityLockFieldAccessResolver().resolve(table, field));
+      service = new DataAnalysisService(config, Runnable::run);
+      ColumnProfile profile = service.columnProfile(new ColumnProfileRequest().withTableName("orders").withFieldName("status").withExact(true));
+      Metric distinct = profile.getCounts().stream().filter(m -> m.getKey().equals("distinct")).findFirst().orElseThrow();
+      assertThat(distinct.getDrill()).isNull();
+      assertThat(profile.getCounts().getFirst().getDrill()).isNotNull();
+      var ownFilter = DrillFilters.condition("id", QCriteriaOperator.EQUALS, 1);
+      profile = service.columnProfile(new ColumnProfileRequest().withTableName("orders").withFieldName("status").withFilter(ownFilter).withExact(true));
+      assertThat(profile.getCounts().getFirst().getDrill()).isNotNull();
+      assertThat(JsonUtils.toJson(profile.getCounts().getFirst().getDrill())).contains("id");
+   }
+   /*******************************************************************************
+    ** Related label tiers must neither leak labels nor reuse a stale cache entry.
+    *******************************************************************************/
+   @Test
+   void relatedLabelTierChangesInvalidateCache() throws Exception
+   {
+      AtomicReference<FieldAnalysisAccess> tier = new AtomicReference<>(FieldAnalysisAccess.FULL);
+      config.withFieldAnalysisAccessResolver(() -> (table, field) -> table.getName().equals("customer") && field.getName().equals("name")
+         ? tier.get() : new FieldSecurityLockFieldAccessResolver().resolve(table, field));
+      service = new DataAnalysisService(config, Runnable::run);
+      ColumnProfileRequest request = new ColumnProfileRequest().withTableName("orders").withFieldName("customer").withExact(true);
+      String first = service.startExactRun(request);
+      assertThat(service.columnProfile(request).getTopValues()).extracting(ValueCount::getLabel).contains("Alice Co");
+      tier.set(FieldAnalysisAccess.NONE);
+      assertThat(service.startExactRun(request)).isNotEqualTo(first);
+      assertThat(service.columnProfile(request).getTopValues()).extracting(ValueCount::getLabel).containsExactly("1", "2");
+      assertThat(service.distinctValues(new DistinctValuesRequest().withTableName("orders").withFieldName("customer")).getValues()).extracting(ValueCount::getLabel).containsExactly("1", "2");
+   }
+}

src/test/java/com/kingsrook/qbits/dataanalysis/PivotContractTest.java

@@ -0,0 +1,350 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dataanalysis;
+
+import java.math.BigDecimal;
+import java.util.List;
+import java.util.ArrayList;
+import com.kingsrook.qbits.dataanalysis.result.Drill;
+import com.kingsrook.qbits.dataanalysis.result.Metric;
+import com.kingsrook.qbits.dataanalysis.result.PivotCell;
+import com.kingsrook.qbits.dataanalysis.result.PivotDimension;
+import com.kingsrook.qbits.dataanalysis.result.PivotHeader;
+import com.kingsrook.qbits.dataanalysis.result.PivotMeasure;
+import com.kingsrook.qbits.dataanalysis.result.PivotNumericBins;
+import com.kingsrook.qbits.dataanalysis.result.PivotRequest;
+import com.kingsrook.qbits.dataanalysis.result.PivotResult;
+import com.kingsrook.qbits.dataanalysis.result.ValueType;
+import com.kingsrook.qbits.dataanalysis.engine.DrillFilters;
+import com.kingsrook.qqq.backend.core.actions.permissions.PermissionEnforcement;
+import com.kingsrook.qqq.backend.core.actions.tables.CountAction;
+import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.actions.tables.count.CountInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.aggregate.DateBucketUnit;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinOn;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinType;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.ExposedJoin;
+import com.kingsrook.qqq.backend.core.utils.JsonUtils;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+import static org.assertj.core.api.Assertions.within;
+
+/*******************************************************************************
+ ** Pivot contract inherited by every existing backend test configuration.
+ *******************************************************************************/
+abstract class PivotContractTest extends EngineFixture
+{
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   protected PivotDimension dimension(String field)
+   {
+      return (new PivotDimension().withField(field));
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   protected PivotMeasure measure(PivotMeasure.Operator operator, String field)
+   {
+      return (new PivotMeasure().withOperator(operator).withField(field));
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   protected PivotRequest pivot(List<String> rows, List<String> columns)
+   {
+      return (new PivotRequest().withTableName("orders").withRows(rows.stream().map(this::dimension).toList())
+         .withColumns(columns.stream().map(this::dimension).toList()).withValues(List.of(measure(PivotMeasure.Operator.COUNT, null))));
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   private void equalNumber(Metric metric, String value)
+   {
+      assertThat(new BigDecimal(metric.getValue().toString())).isEqualByComparingTo(value);
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   protected void countDrills(PivotResult result) throws Exception
+   {
+      List<List<Metric>> groups = new ArrayList<>(result.getRowTotals());
+      groups.addAll(result.getColumnTotals());
+      groups.add(result.getGrandTotals());
+      result.getCells().forEach(c -> groups.add(c.getValues()));
+      for(List<Metric> metrics : groups)
+      {
+         for(Metric metric : metrics)
+         {
+            if(metric.getKey().equals("value0") && !metric.getSuppressed())
+            {
+               assertThat(metric.getDrill()).isNotNull();
+               Drill drill = JsonUtils.toObject(JsonUtils.toJson(metric.getDrill()), Drill.class);
+               CountInput input = new CountInput(drill.getTableName()).withFilter(drill.getFilter());
+               input.setPermissionEnforcement(PermissionEnforcement.ENFORCE);
+               assertThat(new CountAction().execute(input).getCount()).as(JsonUtils.toJson(drill)).isEqualTo(((Number) metric.getValue()).intValue());
+               QueryInput query = new QueryInput(drill.getTableName()).withFilter(drill.getFilter());
+               query.setPermissionEnforcement(PermissionEnforcement.ENFORCE);
+               assertThat(new QueryAction().execute(query).getRecords()).as(JsonUtils.toJson(drill)).hasSize(((Number) metric.getValue()).intValue());
+            }
+         }
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotShapesAndTotals() throws Exception
+   {
+      for(PivotRequest request : List.of(pivot(List.of("status"), List.of("customer")), pivot(List.of("status", "paid"), List.of("customer")),
+         pivot(List.of("status"), List.of("customer", "paid"))))
+      {
+         PivotResult result = service.pivot(request);
+         equalNumber(result.getGrandTotals().getFirst(), "12");
+         assertThat(result.getCells().stream().mapToLong(c -> ((Number)c.getValues().getFirst().getValue()).longValue()).sum()).isEqualTo(12);
+         countDrills(result);
+      }
+      PivotResult result = service.pivot(pivot(List.of("status"), List.of("customer")));
+      assertThat(result.getCells()).hasSize(4);
+      assertThat(result.getCells()).allSatisfy(c -> assertThat(((Number)c.getValues().getFirst().getValue()).longValue()).isIn(2L, 4L));
+      assertThat(result.getRowTotals()).allSatisfy(m -> equalNumber(m.getFirst(), "6"));
+      result = service.pivot(pivot(List.of("status"), List.of()).withShowTotals(false));
+      assertThat(result.getGrandTotals()).isEmpty();
+      assertThat(result.getRowTotals()).isEmpty();
+      assertThat(result.getColumnHeaders()).containsExactly(List.of());
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotOtherAndNonAdditiveMeasures() throws Exception
+   {
+      PivotRequest request = pivot(List.of("quantity"), List.of("status")).withLimitGroups(1).withValues(List.of(
+         measure(PivotMeasure.Operator.COUNT, null), measure(PivotMeasure.Operator.SUM, "amount"), measure(PivotMeasure.Operator.AVG, "amount"),
+         measure(PivotMeasure.Operator.COUNT_DISTINCT, "amount"), measure(PivotMeasure.Operator.PERCENTILE, "amount").withPercentile(new BigDecimal("0.5"))));
+      PivotResult result = service.pivot(request);
+      assertThat(result.getTruncatedRows()).isTrue();
+      assertThat(result.getTruncatedColumns()).isTrue();
+      assertThat(result.getRowHeaders().stream().flatMap(List::stream).map(PivotHeader::getLabel)).anyMatch(l -> l.equals("Other (5 groups)"));
+      equalNumber(result.getGrandTotals().get(1), "1170");
+      equalNumber(result.getGrandTotals().get(3), "8");
+      equalNumber(result.getGrandTotals().get(4), "20");
+      countDrills(result);
+      for(PivotCell cell : result.getCells())
+      {
+         var input = new com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput("orders").withFilter(cell.getValues().getFirst().getDrill().getFilter());
+         input.setPermissionEnforcement(PermissionEnforcement.ENFORCE);
+         var numbers = new com.kingsrook.qqq.backend.core.actions.tables.QueryAction().execute(input).getRecords().stream().map(r -> r.getValueBigDecimal("amount"))
+            .filter(java.util.Objects::nonNull).sorted().toList();
+         BigDecimal sum = numbers.stream().reduce(BigDecimal.ZERO, BigDecimal::add);
+         equalNumber(cell.getValues().get(1), sum.toString());
+         assertThat(new BigDecimal(cell.getValues().get(2).getValue().toString()).doubleValue()).isCloseTo(sum.doubleValue() / numbers.size(), within(0.000000001));
+         equalNumber(cell.getValues().get(3), Long.toString(numbers.stream().map(BigDecimal::stripTrailingZeros).distinct().count()));
+         BigDecimal median = numbers.size() % 2 == 1 ? numbers.get(numbers.size() / 2) : numbers.get(numbers.size() / 2 - 1).add(numbers.get(numbers.size() / 2)).divide(BigDecimal.valueOf(2));
+         equalNumber(cell.getValues().get(4), median.toString());
+      }
+      for(String field : List.of("paid", "note", "amount"))
+      {
+         countDrills(service.pivot(pivot(List.of(field), List.of()).withLimitGroups(1)));
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotBuckets() throws Exception
+   {
+      for(DateBucketUnit unit : DateBucketUnit.values())
+      {
+         PivotRequest request = pivot(List.of("created"), List.of("status")).withRows(List.of(dimension("created").withDateBucket(unit)));
+         PivotResult result = service.pivot(request);
+         equalNumber(result.getGrandTotals().getFirst(), "12");
+         countDrills(result);
+      }
+      PivotResult dates = service.pivot(pivot(List.of("order_date"), List.of("status")).withRows(List.of(dimension("order_date").withDateBucket(DateBucketUnit.DAY))));
+      assertThat(dates.getCells()).hasSize(4);
+      countDrills(dates);
+      for(PivotNumericBins bins : List.of(new PivotNumericBins().withCount(4).withMin(BigDecimal.ZERO).withMax(BigDecimal.valueOf(40)), new PivotNumericBins().withCount(10)))
+      {
+         countDrills(service.pivot(pivot(List.of("amount"), List.of("status")).withRows(List.of(dimension("amount").withNumericBins(bins)))));
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotPrivacyAndUserFilter() throws Exception
+   {
+      config.withDefaultMinGroupSize(3);
+      service.getConfigResolver().invalidate();
+      PivotResult result = service.pivot(pivot(List.of("status"), List.of("customer")).withValues(List.of(measure(PivotMeasure.Operator.COUNT, null), measure(PivotMeasure.Operator.SUM, "amount"))));
+      assertThat(result.getCells().stream().filter(c -> c.getValues().getFirst().getSuppressed())).hasSize(2);
+      result.getCells().stream().filter(c -> c.getValues().getFirst().getSuppressed()).forEach(c -> assertThat(c.getValues()).allSatisfy(m ->
+      {
+         assertThat(m.getValue()).isNull();
+         assertThat(m.getDrill()).isNull();
+      }));
+      assertThat(service.pivot(pivot(List.of("quantity"), List.of())).getRowHeaders()).isEmpty();
+      config.withDefaultMinGroupSize(0);
+      service.getConfigResolver().invalidate();
+      var filter = DrillFilters.condition("customer", QCriteriaOperator.EQUALS, 1);
+      String before = JsonUtils.toJson(filter);
+      result = service.pivot(pivot(List.of("status"), List.of()).withFilter(filter));
+      equalNumber(result.getGrandTotals().getFirst(), "8");
+      assertThat(JsonUtils.toJson(filter)).isEqualTo(before);
+      countDrills(result);
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotJoinedDimensionAndPermissions() throws Exception
+   {
+      instance.addJoin(new QJoinMetaData().withName("ordersCustomer").withLeftTable("orders").withRightTable("customer")
+         .withType(JoinType.MANY_TO_ONE).withJoinOn(new JoinOn("customer", "id")));
+      instance.getTable("orders").withExposedJoin(new ExposedJoin().withJoinTable("customer").withJoinPath(List.of("ordersCustomer")));
+      PivotResult result = service.pivot(pivot(List.of("customer.name"), List.of("status")));
+      assertThat(result.getRowHeaders().stream().flatMap(List::stream).map(PivotHeader::getValue)).containsExactlyInAnyOrder("Alice Co", "Bob Co");
+      equalNumber(result.getGrandTotals().getFirst(), "12");
+      assertThat(result.getWarnings()).isEmpty();
+      assertThat(result.getCells()).allSatisfy(c -> assertThat(JsonUtils.toJson(c.getValues().getFirst().getDrill())).contains("customer.name"));
+      countDrills(result);
+      countDrills(service.pivot(pivot(List.of("customer.name"), List.of("status")).withLimitGroups(1)
+         .withFilter(DrillFilters.condition("paid", QCriteriaOperator.EQUALS, true))));
+      instance.getJoins().put("ordersCustomer", instance.getJoin("ordersCustomer").flip());
+      countDrills(service.pivot(pivot(List.of("customer.name"), List.of("status"))));
+      QContext.getQSession().getPermissions().remove("customer.read");
+      assertThatThrownBy(() -> service.pivot(pivot(List.of("customer.name"), List.of()))).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+   }
+
+   /*******************************************************************************
+    ** Reversing a many-to-one join multiplies records and disables every drill.
+    *******************************************************************************/
+   @Test
+   void pivotToManyDimension() throws Exception
+   {
+      instance.addJoin(new QJoinMetaData().withName("ordersCustomer").withLeftTable("orders").withRightTable("customer")
+         .withType(JoinType.MANY_TO_ONE).withJoinOn(new JoinOn("customer", "id")));
+      instance.getTable("customer").withExposedJoin(new ExposedJoin().withJoinTable("orders").withJoinPath(List.of("ordersCustomer")));
+      QContext.getQSession().getPermissions().add("customer.analyze");
+      for(int direction = 0; direction < 2; direction++)
+      {
+         PivotResult result = service.pivot(pivot(List.of("orders.status"), List.of()).withTableName("customer"));
+         equalNumber(result.getGrandTotals().getFirst(), "12");
+         assertThat(result.getWarnings()).containsExactly("Counts include one row per related " + instance.getTable("orders").getLabel()
+            + "; drill-through is not available for this dimension.");
+         assertThat(result.getCells()).allSatisfy(c -> assertThat(c.getValues()).allSatisfy(m -> assertThat(m.getDrill()).isNull()));
+         assertThat(result.getRowTotals()).allSatisfy(metrics -> assertThat(metrics).allSatisfy(m -> assertThat(m.getDrill()).isNull()));
+         assertThat(result.getColumnTotals()).allSatisfy(metrics -> assertThat(metrics).allSatisfy(m -> assertThat(m.getDrill()).isNull()));
+         assertThat(result.getGrandTotals()).allSatisfy(m -> assertThat(m.getDrill()).isNull());
+         instance.getJoins().put("ordersCustomer", instance.getJoin("ordersCustomer").flip());
+      }
+   }
+
+   /*******************************************************************************
+    ** Every hop must be to-one, including hops preceding a one-to-one target.
+    *******************************************************************************/
+   @Test
+   void pivotMultiHopDimension() throws Exception
+   {
+      instance.addTable(new com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData().withName("region").withLabel("Region")
+         .withBackendName(instance.getTable("customer").getBackendName()).withPrimaryKeyField("id")
+         .withField(new com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData("id", com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType.INTEGER))
+         .withField(new com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData("name", com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType.STRING)));
+      if(connection != null)
+      {
+         try(var statement = connection.createStatement())
+         {
+            statement.execute("DROP TABLE IF EXISTS region");
+            statement.execute("CREATE TABLE region (id INTEGER PRIMARY KEY, name VARCHAR(100))");
+         }
+      }
+      new com.kingsrook.qqq.backend.core.instances.QInstanceEnricher(instance).enrich();
+      TestSupport.insert("region", new com.kingsrook.qqq.backend.core.model.data.QRecord().withValue("id", 1).withValue("name", "East"));
+      TestSupport.insert("region", new com.kingsrook.qqq.backend.core.model.data.QRecord().withValue("id", 2).withValue("name", "West"));
+      instance.addJoin(new QJoinMetaData().withName("ordersCustomer").withLeftTable("orders").withRightTable("customer")
+         .withType(JoinType.MANY_TO_ONE).withJoinOn(new JoinOn("customer", "id")));
+      instance.addJoin(new QJoinMetaData().withName("customerRegion").withLeftTable("region").withRightTable("customer")
+         .withType(JoinType.ONE_TO_ONE).withJoinOn(new JoinOn("id", "id")));
+      instance.getTable("orders").withExposedJoin(new ExposedJoin().withJoinTable("region").withJoinPath(List.of("ordersCustomer", "customerRegion")));
+      PivotResult result = service.pivot(pivot(List.of("region.name"), List.of("status")));
+      assertThat(result.getWarnings()).containsExactly("Drill-through is not available for fields reached through more than one join (Region).");
+      assertThat(result.getCells()).allSatisfy(c -> assertThat(c.getValues().getFirst().getDrill()).isNull());
+      instance.getJoin("ordersCustomer").withType(JoinType.MANY_TO_MANY);
+      result = service.pivot(pivot(List.of("region.name"), List.of("status")));
+      assertThat(result.getWarnings()).containsExactly("Counts include one row per related Region; drill-through is not available for this dimension.");
+      assertThat(result.getCells()).allSatisfy(c -> assertThat(c.getValues().getFirst().getDrill()).isNull());
+   }
+
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotValidation() throws Exception
+   {
+      for(String field : List.of("secret", "missing", "missing.field"))
+      {
+         assertThatThrownBy(() -> service.pivot(pivot(List.of(field), List.of()))).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+         assertThatThrownBy(() -> service.pivot(pivot(List.of("status"), List.of()).withValues(List.of(measure(PivotMeasure.Operator.MIN, field)))))
+            .isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+      }
+      for(PivotRequest bad : List.of(pivot(List.of(), List.of()), pivot(List.of("status"), List.of()).withLimitGroups(201),
+         pivot(List.of("status"), List.of("status")), pivot(List.of("status"), List.of()).withRows(List.of(dimension("status").withDateBucket(DateBucketUnit.YEAR))),
+         pivot(List.of("status"), List.of()).withValues(List.of(measure(PivotMeasure.Operator.PERCENTILE, "amount").withPercentile(BigDecimal.TEN)))))
+      {
+         assertThatThrownBy(() -> service.pivot(bad)).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QBadRequestException.class);
+      }
+   }
+   /*******************************************************************************
+    ** Verify or support the pivot and field-access contract.
+    *******************************************************************************/
+   @Test
+   void pivotTypedExtremesAndEmptyScope() throws Exception
+   {
+      PivotResult result = service.pivot(pivot(List.of("status"), List.of()).withValues(List.of(measure(PivotMeasure.Operator.COUNT, "amount"),
+         measure(PivotMeasure.Operator.MIN, "order_date"), measure(PivotMeasure.Operator.MAX, "created"), measure(PivotMeasure.Operator.MIN, "amount"),
+         measure(PivotMeasure.Operator.MAX, "amount"))));
+      equalNumber(result.getGrandTotals().getFirst(), "12");
+      equalNumber(result.getGrandTotals().get(3), "-10");
+      equalNumber(result.getGrandTotals().get(4), "1000");
+      assertThat(result.getGrandTotals().get(1).getValueType()).isEqualTo(ValueType.DATE);
+      assertThat(result.getGrandTotals().get(2).getValueType()).isEqualTo(ValueType.DATE_TIME);
+      countDrills(result);
+      result = service.pivot(pivot(List.of("status"), List.of()).withFilter(DrillFilters.condition("id", QCriteriaOperator.EQUALS, -1)));
+      assertThat(result.getCells()).isEmpty();
+      equalNumber(result.getGrandTotals().getFirst(), "0");
+      countDrills(result);
+   }
+}