Merge pull request 'fix(filters): relative dates in an analysis filter no longer answer HTTP 500' (#44) from fix/relative-date-filters into develop

Co-Authored-By: Claude Fable 5.2 <noreply@anthropic.com>

.circleci/config.yml

@@ -1,60 +1,14 @@
 version: 2.1
-
+setup: true
 orbs:
-   qqq-orb: kingsrook/qqq-orb@0.6.9
-
-commands:
-   configure-package-install:
-      steps:
-         - run:
-              name: Keep package installation unattended
-              command: |
-                 # These build machines are discarded after the job.
-                 sudo install -d /etc/needrestart/conf.d
-                 printf '%s\n' '$nrconf{restart} = "l";' | sudo tee /etc/needrestart/conf.d/99-qqq-ci.conf >/dev/null
-
+  qqq-orb: kingsrook/qqq-orb@0.8
 workflows:
-   test_only:
-      jobs:
-         - qqq-orb/mvn_test_only:
-              pre-steps:
-                 - configure-package-install
-              context: [qqq-maven-registry-credentials]
-              filters:
-                 branches:
-                    ignore: /(develop|release\/.*)/
-
-   publish_snapshot:
-      jobs:
-         - qqq-orb/mvn_publish:
-              pre-steps:
-                 - configure-package-install
-              context: [qqq-maven-registry-credentials]
-              branch_type: snapshot
-              filters:
-                 branches:
-                    only: [develop]
-
-   publish_release_candidate:
-      jobs:
-         - qqq-orb/mvn_publish:
-              pre-steps:
-                 - configure-package-install
-              context: [qqq-maven-registry-credentials]
-              branch_type: release_candidate
-              filters:
-                 branches:
-                    only: [/release\/.*/]
-
-   publish_release:
-      jobs:
-         - qqq-orb/mvn_publish:
-              pre-steps:
-                 - configure-package-install
-              context: [qqq-maven-registry-credentials]
-              branch_type: release
-              filters:
-                 branches:
-                    ignore: /.*/
-                 tags:
-                    only: [/^v\d+\.\d+\.\d+$/]
+  setup:
+    jobs:
+    - qqq-orb/generate_pipeline:
+        orb_version: '0.8'
+        filters:
+          branches:
+            ignore: automation/qbit-rc-pending
+          tags:
+            only: /^v\d+\.\d+\.\d+$/

.gitea/workflows/build.yaml

@@ -0,0 +1,7 @@
+name: build
+on:
+  push:
+jobs:
+  maven:
+    uses: self:KofTwentyTwo/ci-workflows/.gitea/workflows/maven.yaml@v1
+    secrets: inherit

.github/workflows/codeql.yml

@@ -0,0 +1,23 @@
+name: CodeQL
+'on':
+  push:
+    branches:
+    - main
+    - develop
+    - release/**
+    - hotfix/**
+  pull_request: {}
+  schedule:
+  - cron: 41 16 * * 0
+  workflow_dispatch: {}
+permissions:
+  contents: read
+  actions: read
+  security-events: write
+  packages: read
+concurrency:
+  group: codeql-${{ github.event.pull_request.number || github.ref }}
+  cancel-in-progress: ${{ github.event_name == 'pull_request' }}
+jobs:
+  analyze:
+    uses: QRun-IO/qqq-orb/.github/workflows/qbit-codeql.yml@c288a8864fe9054f68410e1d78f5863aea4c4d22

.github/workflows/release-rc.yml

@@ -1,26 +1,74 @@
-# Cut the next release candidate (QRun-IO/qqq docs/QRUN-QBIT-REPO-STANDARD.md, C6):
-# run this workflow by hand, or merge a develop PR labelled `release:rc`. It merges develop into
-# the release line and the release/* push runs CircleCI publish_release_candidate.
 name: Release candidate
-
-on:
+'on':
   workflow_dispatch:
     inputs:
       dry_run:
-        description: Print the plan without merging or creating branches
+        description: Plan a cut without changing release state
         type: boolean
         default: false
+      finalize:
+        description: Retry finalization of an existing cut; do not cut another RC
+        type: boolean
+        default: false
+      recover:
+        description: Recover the same published RC after a deployment timeout; never
+          reupload
+        type: boolean
+        default: false
+      circle_build:
+        description: Original Circle publisher build number (recovery only)
+        type: string
+        default: ''
+      merge_sha:
+        description: Existing cut merge_sha (finalize retry only)
+        type: string
+        default: ''
+      bump_sha:
+        description: Existing cut bump_sha (finalize retry only)
+        type: string
+        default: ''
+      branch:
+        description: Existing cut branch (finalize retry only)
+        type: string
+        default: ''
+      version:
+        description: Existing cut version (finalize retry only)
+        type: string
+        default: ''
   pull_request:
-    types: [closed]
-    branches: [develop]
-
+    types:
+    - closed
+    branches:
+    - develop
+  repository_dispatch:
+    types:
+    - qbit-rc-published
 permissions:
   contents: write
-
+  actions: read
+  checks: read
 jobs:
   release-rc:
-    if: github.event_name == 'workflow_dispatch' || (github.event.pull_request.merged == true && contains(github.event.pull_request.labels.*.name, 'release:rc'))
-    uses: QRun-IO/.github/.github/workflows/qbit-release-rc.yml@main
-    secrets: inherit
+    if: github.event_name == 'workflow_dispatch' || github.event_name == 'repository_dispatch'
+      || (github.event_name == 'pull_request' && github.event.pull_request.merged
+      == true && github.event.pull_request.base.ref == 'develop' && contains(github.event.pull_request.labels.*.name,
+      'release:rc'))
+    uses: QRun-IO/qqq-orb/.github/workflows/qbit-release-rc.yml@cc30f599e63c8e41629c99d23395bac1adb8bc44
     with:
-      dry_run: ${{ github.event_name == 'workflow_dispatch' && inputs.dry_run }}
+      dry_run: ${{ github.event_name == 'workflow_dispatch' && inputs.dry_run || false
+        }}
+      finalize: ${{ github.event_name == 'repository_dispatch' || (github.event_name
+        == 'workflow_dispatch' && inputs.finalize) }}
+      recover: ${{ github.event_name == 'workflow_dispatch' && inputs.recover || false
+        }}
+      circle_build: ${{ github.event_name == 'workflow_dispatch' && inputs.circle_build
+        || '' }}
+      merge_sha: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.merge_sha
+        || inputs.merge_sha || '' }}
+      bump_sha: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.bump_sha
+        || inputs.bump_sha || '' }}
+      branch: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.branch
+        || inputs.branch || '' }}
+      version: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.version
+        || inputs.version || '' }}
+    secrets: inherit

.qqq-ci.yml

@@ -0,0 +1,16 @@
+profile: qbit
+orb_version: '0.8'
+default_branch: develop
+java_version: '21'
+browsers: true
+api_checks: false
+docs_only: false
+release_on_main: false
+release_tag_policy: stable
+test_branch_policy: non-release
+publish:
+  snapshot: true
+  release_candidate: true
+  release: true
+  asciidoc: false
+release_completion: true

CHANGELOG.md

@@ -4,9 +4,17 @@ Following [Keep a Changelog](https://keepachangelog.com/en/1.1.0/) and [Semantic
 
 ## [Unreleased]
 
+### Changed
+
+- Reconciled legacy release-branch history with the current Gitea CI while retaining the develop SNAPSHOT revision, so normal RC cuts can merge cleanly.
+
 ### Fixed
 
+- Fixed HTTP 500 ("Unsupported class ...NowWithOffset for converting to Instant") from every analysis whose filter holds a relative date (a filter expression such as "now minus one day" or "this month") on a date or date-time field: the pivot, profiles, comparisons and the stand-in backend converted filter values to the field's type before querying and did not expect an expression. Expressions are now left for core to evaluate when the query runs, so "printed in the last day" works from the Analyses screens, over the UI API (`POST /qbit/data-analysis/tables/{table}/pivot`) and in a saved or scheduled analysis, where it means the last day on every run. Drill-through filters carry the expression.
+- Reject saved analyses with a NULL or false shared flag and no read owner in the generated schema. Existing hosts must apply the documented constraint correction through a new migration.
+- Scheduled analyses and watches work under OAuth2. When the host declares the `data-analysis` service principal (QQQ service identities, `DataAnalysisQBitConfig.withServicePrincipal`), schedule and watch inventory runs as that principal, and owner and recipient runs go on their behalf through it: their current access intersected with the principal's. The self-made `data-analysis-scheduler` session was rejected under OAuth2 (`Invalid session in request` on every `data_analysis_run_schedules` run). With no principal declared, behavior is unchanged. Needs the QQQ release with service identities (qqq#1255, #1256).
 - QBit metadata now reports the Maven artifact version, including release versions, from a filtered build resource.
+- The Data Analysis home no longer shows the engine Setup card or "Unavailable: READ permission required." shells. Setup is admin-only (hidden from everyone else) and off the home; hosts place `DataAnalysisQBitProducer.SYSTEM_WIDGETS` on their Admin area. Home widgets the user cannot read are omitted from metadata, and unreadable groups are left out of the overview.
 
 ### Added
 

README.md

@@ -100,6 +100,14 @@ ALTER TABLE data_analysis_schedule ALTER COLUMN last_status TYPE TEXT;
 
 The metadata-generated create script includes these fields for new installations; it does not migrate existing databases. Fix older placeholder definitions (such as `{}` for a column profile) to valid typed requests. Edit existing enabled schedules through core CRUD to validate recipients and initialize `next_run_at`.
 
+Installations created with the earlier generated constraint also need a new host migration after ensuring the read-owner projection is populated. SQL CHECK constraints accept UNKNOWN, so `shared = TRUE` allowed a null shared flag and null read owner. Only explicit sharing may omit the read owner:
+
+```sql
+ALTER TABLE data_analysis_saved
+   DROP CONSTRAINT IF EXISTS ck_data_analysis_saved_read_owner,
+   ADD CONSTRAINT ck_data_analysis_saved_read_owner CHECK (shared IS TRUE OR read_owner_user_id IS NOT NULL);
+```
+
 Minimum group size defaults to 0 (off). Hosts should set it for personal-data tables. Groups, bins and distinct listings below N are merged into an Other row with no value or drill. Min/max, percentiles, mean and standard deviation are suppressed when the assessed population is below N. Outlier listings and their count's drill are removed whenever the guard is enabled. The guard runs before chart construction and before `result_json` is stored.
 
 Source fields must be present in personalized metadata, pass `FieldSecurityLockHelper`, and pass the configured allow/exclude lists. FK labels are queried with enforcement before core PVS formatting; unreadable related records or label fields fall back to the source id. This avoids RC.5's ordinary table-PVS translator's trusted permission bypass.

pom.xml

@@ -18,7 +18,8 @@
 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 
    <properties>
-      <revision>0.1.0-SNAPSHOT</revision>
+      <qqq.version>4.1.0-RC.26</qqq.version> <!-- feeds qbit-build-parent's qqq-bom-pom import -->
+      <revision>0.3.0-SNAPSHOT</revision>
       <maven.compiler.source>21</maven.compiler.source>
       <maven.compiler.target>21</maven.compiler.target>
       <jacoco.version>0.8.15</jacoco.version>
@@ -27,7 +28,7 @@
    <parent>
       <groupId>com.kingsrook</groupId>
       <artifactId>qbit-build-parent</artifactId>
-      <version>2.0.0</version>
+      <version>2.2.1</version>
    </parent>
 
    <groupId>com.kingsrook.qbits</groupId>
@@ -243,7 +244,7 @@
             <activeByDefault>true</activeByDefault>
          </activation>
          <properties>
-            <qqq.snapshot.version>4.1.0-RC.15</qqq.snapshot.version>
+            <qqq.snapshot.version>4.1.0-RC.26</qqq.snapshot.version>
          </properties>
          <dependencyManagement>
             <dependencies>

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

@@ -427,6 +427,28 @@ public class DataAnalysisQBitConfig implements QBitConfig
       return (this);
    }
 
+   private String servicePrincipal = "data-analysis";
+
+   /*******************************************************************************
+    ** The service principal schedules and watches run as when the host declares
+    ** it (QQQ service identities): inventory as the principal, owner and
+    ** recipient runs on their behalf through it. Undeclared: the previous local
+    ** sessions.
+    *******************************************************************************/
+   public String getServicePrincipal()
+   {
+      return (servicePrincipal);
+   }
+
+   /*******************************************************************************
+    ** Fluent setter for servicePrincipal (default data-analysis).
+    *******************************************************************************/
+   public DataAnalysisQBitConfig withServicePrincipal(String servicePrincipal)
+   {
+      this.servicePrincipal = servicePrincipal;
+      return (this);
+   }
+
    private String messagingProviderName;
    private String fromAddress;
    private transient java.util.function.Function<String, String> recipientAddressSupplier;

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

@@ -23,8 +23,10 @@ package com.kingsrook.qbits.dataanalysis;
 
 import java.io.IOException;
 import java.io.InputStream;
+import java.util.List;
 import java.util.Properties;
 import com.kingsrook.qbits.dataanalysis.api.DataAnalysisApi;
+import com.kingsrook.qbits.dataanalysis.app.DataAnalysisHome;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
 import com.kingsrook.qqq.backend.core.model.metadata.MetaDataProducerMultiOutput;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
@@ -45,6 +47,13 @@ public class DataAnalysisQBitProducer implements QBitMetaDataProducer<DataAnalys
    public static final String ARTIFACT_ID = "qbit-data-analysis";
    public static final String VERSION = loadVersion();
 
+   /////////////////////////////////////////////////////////////////////////////////
+   // Widgets that show engine configuration. They are registered with the qbit   //
+   // (admin-only, hidden from everyone else) but kept off the business home; the //
+   // host places them on its Admin area, e.g. withWidgets(SYSTEM_WIDGETS).       //
+   /////////////////////////////////////////////////////////////////////////////////
+   public static final List<String> SYSTEM_WIDGETS = List.of(DataAnalysisHome.SETUP);
+
    private DataAnalysisQBitConfig qBitConfig;
    private String namespace;
 

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

@@ -63,7 +63,7 @@ public final class DataAnalysisSchema
             // a null read-owner projection means "shared": make the database refuse it //
             // on a private row, so a row written outside QQQ fails closed, not open    //
             ///////////////////////////////////////////////////////////////////////////////
-            columns.add("   CONSTRAINT ck_data_analysis_saved_read_owner CHECK (shared = TRUE OR read_owner_user_id IS NOT NULL)");
+            columns.add("   CONSTRAINT ck_data_analysis_saved_read_owner CHECK (shared IS TRUE OR read_owner_user_id IS NOT NULL)");
          }
          statements.add("CREATE TABLE IF NOT EXISTS " + table.getName() + " (\n" + String.join(",\n", columns) + "\n)");
          if(table.getUniqueKeys() != null)

src/main/java/com/kingsrook/qbits/dataanalysis/app/DataAnalysisHome.java

@@ -28,14 +28,17 @@ import java.util.List;
 import java.util.Map;
 import java.util.Objects;
 import com.kingsrook.qbits.dataanalysis.DataAnalysisQBitConfig;
+import com.kingsrook.qbits.dataanalysis.DataAnalysisQBitProducer;
 import com.kingsrook.qbits.dataanalysis.DataAnalysisRuntime;
 import com.kingsrook.qbits.dataanalysis.engine.DrillFilters;
 import com.kingsrook.qbits.dataanalysis.model.DataAnalysisTables;
+import com.kingsrook.qbits.dataanalysis.permissions.DataAnalysisPermissions;
 import com.kingsrook.qbits.dataanalysis.saved.SavedAnalyses;
 import com.kingsrook.qqq.backend.core.actions.dashboard.widgets.AbstractWidgetRenderer;
 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.actions.permissions.UseOtherPermissionNameCustomPermissionChecker;
 import com.kingsrook.qqq.backend.core.actions.tables.CountAction;
 import com.kingsrook.qqq.backend.core.context.QContext;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
@@ -54,6 +57,7 @@ import com.kingsrook.qqq.backend.core.model.metadata.dashboard.QWidgetMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppSection;
 import com.kingsrook.qqq.backend.core.model.metadata.layout.QIcon;
+import com.kingsrook.qqq.backend.core.model.metadata.permissions.DenyBehavior;
 import com.kingsrook.qqq.backend.core.model.metadata.permissions.PermissionLevel;
 import com.kingsrook.qqq.backend.core.model.metadata.permissions.QPermissionRules;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
@@ -82,7 +86,39 @@ public class DataAnalysisHome extends AbstractWidgetRenderer
     *******************************************************************************/
    public static List<QWidgetMetaData> widgets()
    {
-      return (List.of(widget(SETUP, "Setup", WidgetType.TABLE), widget(), widget(STARRED, "Starred analyses", WidgetType.TABLE), widget(FIRING, "Firing watches", WidgetType.TABLE), widget(RECENT, "My recent runs", WidgetType.TABLE)));
+      return (List.of(setupWidget(), widget().withPermissionRules(readRules(DataAnalysisTables.SAVED)),
+         widget(STARRED, "Starred analyses", WidgetType.TABLE).withPermissionRules(readRules(DataAnalysisTables.SAVED)),
+         widget(FIRING, "Firing watches", WidgetType.TABLE).withPermissionRules(readRules(DataAnalysisTables.WATCH)),
+         widget(RECENT, "My recent runs", WidgetType.TABLE).withPermissionRules(readRules(DataAnalysisTables.RUN))));
+   }
+
+   /*******************************************************************************
+    ** Names of the widgets placed on the business home. Setup is system content and
+    ** is deliberately absent; hosts place {@link DataAnalysisQBitProducer#SYSTEM_WIDGETS}
+    ** on their Admin area instead.
+    *******************************************************************************/
+   public static List<String> homeWidgetNames()
+   {
+      return (widgets().stream().map(QWidgetMetaData::getName).filter(name -> !DataAnalysisQBitProducer.SYSTEM_WIDGETS.contains(name)).toList());
+   }
+
+   /*******************************************************************************
+    ** The Setup card shows engine configuration, so only Data Analysis admins get it;
+    ** everyone else has it omitted from metadata.
+    *******************************************************************************/
+   private static QWidgetMetaData setupWidget()
+   {
+      return (widget(SETUP, "Setup", WidgetType.TABLE).withPermissionRules(new QPermissionRules().withLevel(PermissionLevel.HAS_ACCESS_PERMISSION)
+         .withDenyBehavior(DenyBehavior.HIDDEN).withCustomPermissionChecker(UseOtherPermissionNameCustomPermissionChecker.build(DataAnalysisPermissions.ADMIN))));
+   }
+
+   /*******************************************************************************
+    ** A data widget is omitted from metadata for users who cannot read its table.
+    *******************************************************************************/
+   private static QPermissionRules readRules(String table)
+   {
+      return (new QPermissionRules().withLevel(PermissionLevel.HAS_ACCESS_PERMISSION).withDenyBehavior(DenyBehavior.HIDDEN)
+         .withCustomPermissionChecker(TableReadWidgetPermissionChecker.build(table)));
    }
 
    /*******************************************************************************
@@ -101,7 +137,7 @@ public class DataAnalysisHome extends AbstractWidgetRenderer
    public static QAppMetaData app(DataAnalysisQBitConfig config, List<QTableMetaData> tables)
    {
       QAppMetaData app = new QAppMetaData().withName(config.getAppName()).withLabel(config.getAppLabel())
-         .withIcon(new QIcon("insights")).withWidgets(widgets().stream().map(QWidgetMetaData::getName).toList())
+         .withIcon(new QIcon("insights")).withWidgets(homeWidgetNames())
          .withPermissionRules(new QPermissionRules().withLevel(PermissionLevel.NOT_PROTECTED))
          .withSection(new QAppSection().withName("analyses").withLabel("Analyses").withIcon(new QIcon("insights")).withTables(DataAnalysisTables.NAV_TABLES));
       app.setParentAppName(config.getParentAppName());
@@ -197,46 +233,45 @@ public class DataAnalysisHome extends AbstractWidgetRenderer
    }
 
    /*******************************************************************************
-    ** One standard group per subject, with real counts or an unavailable explanation.
+    ** One standard group per subject the viewer can read; unreadable subjects are left out.
     *******************************************************************************/
    public static MultiStatisticsData statistics(Instant now) throws QException
    {
       List<MultiStatisticsData.StatisticsGroupData> groups = new ArrayList<>();
-      var saved = group("Saved analyses", DataAnalysisTables.SAVED);
       if(canRead(DataAnalysisTables.SAVED))
       {
+         var saved = group("Saved analyses");
          List<SavedAnalyses.Entry> entries = DataAnalysisRuntime.saved().list();
          String user = SavedAnalyses.user();
          saved.withStatisticList(List.of(stat("Mine", (int) entries.stream().filter(e -> e.owner().equals(user)).count(), DataAnalysisTables.SAVED),
             stat("Shared", (int) entries.stream().filter(e -> Boolean.TRUE.equals(e.shared())).count(), DataAnalysisTables.SAVED),
             stat("Starred", (int) entries.stream().filter(SavedAnalyses.Entry::starred).count(), DataAnalysisTables.SAVED)));
+         groups.add(saved);
       }
-      groups.add(saved);
-      var watches = group("Watches", DataAnalysisTables.WATCH);
       if(canRead(DataAnalysisTables.WATCH))
       {
+         var watches = group("Watches");
          watches.withStatisticList(List.of(stat("Enabled", count(DataAnalysisTables.WATCH, equal("enabled", true)), DataAnalysisTables.WATCH),
             stat("Firing", count(DataAnalysisTables.WATCH, equal("state", "FIRING")), DataAnalysisTables.WATCH)));
+         groups.add(watches);
       }
-      groups.add(watches);
-      var runs = group("My runs", DataAnalysisTables.RUN);
       if(canRead(DataAnalysisTables.RUN))
       {
+         var runs = group("My runs");
          runs.withStatisticList(List.of(stat("Last 7 days", count(DataAnalysisTables.RUN, equal("user_id", SavedAnalyses.user())
             .withCriteria("started_at", QCriteriaOperator.GREATER_THAN_OR_EQUALS, now.minus(7, ChronoUnit.DAYS))
             .withCriteria("started_at", QCriteriaOperator.LESS_THAN_OR_EQUALS, now)), DataAnalysisTables.RUN)));
+         groups.add(runs);
       }
-      groups.add(runs);
       return (new MultiStatisticsData("Analyses", groups));
    }
 
    /*******************************************************************************
-    ** Empty/denied data are different messages, not synthetic zeroes.
+    ** A group is only built for a readable subject, so its subheader is always true.
     *******************************************************************************/
-   private static MultiStatisticsData.StatisticsGroupData group(String label, String table)
+   private static MultiStatisticsData.StatisticsGroupData group(String label)
    {
-      return (new MultiStatisticsData.StatisticsGroupData().withHeader(label).withStatisticList(List.of())
-         .withSubheader(canRead(table) ? "Visible to you" : "Unavailable: READ permission required."));
+      return (new MultiStatisticsData.StatisticsGroupData().withHeader(label).withStatisticList(List.of()).withSubheader("Visible to you"));
    }
 
    /*******************************************************************************
@@ -257,7 +292,7 @@ public class DataAnalysisHome extends AbstractWidgetRenderer
       {
          columns.add(new TableData.Column(i == 0 ? "html" : "text", headers[i], "c" + i, null, "left"));
       }
-      return (new TableData(label, columns, new ArrayList<>()).withNoRowsFoundHTML(canRead(table) ? empty : "Unavailable: READ permission required.")
+      return (new TableData(label, columns, new ArrayList<>()).withNoRowsFoundHTML(empty)
          .withLinkText("View all").withLinkURL(QContext.getQInstance().getTablePath(table)).withRowsPerPage(10));
    }
 

src/main/java/com/kingsrook/qbits/dataanalysis/app/TableReadWidgetPermissionChecker.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.app;
+
+
+import java.io.Serializable;
+import java.util.Map;
+import com.kingsrook.qqq.backend.core.actions.permissions.CustomPermissionChecker;
+import com.kingsrook.qqq.backend.core.actions.permissions.PermissionsHelper;
+import com.kingsrook.qqq.backend.core.actions.permissions.TablePermissionSubType;
+import com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException;
+import com.kingsrook.qqq.backend.core.model.actions.AbstractActionInput;
+import com.kingsrook.qqq.backend.core.model.actions.AbstractTableActionInput;
+import com.kingsrook.qqq.backend.core.model.metadata.code.InitializableViaCodeReference;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReferenceWithProperties;
+import com.kingsrook.qqq.backend.core.model.metadata.permissions.MetaDataWithPermissionRules;
+import com.kingsrook.qqq.backend.core.utils.ValueUtils;
+import com.kingsrook.qqq.backend.core.utils.collections.MapBuilder;
+
+
+/*******************************************************************************
+ ** A widget is available only to sessions that can READ the table it summarizes.
+ ** Contributes no catalog entry of its own: the table's READ permission is the
+ ** permission.
+ *******************************************************************************/
+public class TableReadWidgetPermissionChecker implements CustomPermissionChecker, InitializableViaCodeReference
+{
+   private static final String TABLE_NAME_PROPERTY = "tableName";
+
+   private String tableName;
+
+
+
+   /*******************************************************************************
+    ** Code reference for the given table.
+    *******************************************************************************/
+   public static QCodeReferenceWithProperties build(String tableName)
+   {
+      return (new QCodeReferenceWithProperties(TableReadWidgetPermissionChecker.class, MapBuilder.of(TABLE_NAME_PROPERTY, tableName)));
+   }
+
+
+
+   /*******************************************************************************
+    ** Read the table name from the code reference properties.
+    *******************************************************************************/
+   @Override
+   public void initialize(QCodeReference codeReference)
+   {
+      if(codeReference instanceof QCodeReferenceWithProperties withProperties)
+      {
+         Map<String, Serializable> properties = withProperties.getProperties();
+         tableName = ValueUtils.getValueAsString(properties.get(TABLE_NAME_PROPERTY));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Deny unless the session may READ the table.
+    *******************************************************************************/
+   @Override
+   public void checkPermissionsThrowing(AbstractActionInput actionInput, MetaDataWithPermissionRules metaDataWithPermissionRules) throws QPermissionDeniedException
+   {
+      AbstractTableActionInput tableInput = new AbstractTableActionInput();
+      tableInput.setTableName(tableName);
+      PermissionsHelper.checkTablePermissionThrowing(tableInput, TablePermissionSubType.READ);
+   }
+}

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

@@ -302,9 +302,19 @@ public final class DrillFilters
 
    /*******************************************************************************
     ** String decimal operands preserve exact scale and precision across JSON round trips.
+    ** Filter expressions (relative dates) are left for core to evaluate.
     *******************************************************************************/
    private static Serializable restoreValue(com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType type, Serializable value)
    {
+      if(value instanceof com.kingsrook.qqq.backend.core.model.actions.tables.query.expressions.AbstractFilterExpression<?>)
+      {
+         ///////////////////////////////////////////////////////////////////////////
+         // a relative date ("now minus one day", "this month") is not a value to  //
+         // convert: core evaluates it when the query runs, so a saved or          //
+         // scheduled analysis means "the last day" on every run                   //
+         ///////////////////////////////////////////////////////////////////////////
+         return (value);
+      }
       if(type == com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType.DECIMAL)
       {
          return (value == null ? null : value.toString());

src/main/java/com/kingsrook/qbits/dataanalysis/schedule/AnalysisSessions.java

@@ -0,0 +1,90 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  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.schedule;
+
+
+import java.util.function.Supplier;
+import com.kingsrook.qbits.dataanalysis.DataAnalysisQBitConfig;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.modules.authentication.QAuthenticationModuleDispatcher;
+import com.kingsrook.qqq.backend.core.serviceidentity.ServiceIdentities;
+
+
+/*******************************************************************************
+ ** Sessions for scheduled analysis work. With the host's data-analysis service
+ ** principal declared (QQQ service identities): inventory runs as the
+ ** principal, and owner or recipient runs go on their behalf through it (their
+ ** current access intersected with the principal's). Without it: the previous
+ ** local sessions (single-node default unchanged). Never a system session.
+ *******************************************************************************/
+public final class AnalysisSessions
+{
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private AnalysisSessions()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** The session for schedule/watch inventory and status updates.
+    *******************************************************************************/
+   public static QSession maintenance(DataAnalysisQBitConfig config, Supplier<QSession> legacy) throws QException
+   {
+      if(usesPrincipal(config))
+      {
+         return (ServiceIdentities.session(QContext.getQInstance(), config.getServicePrincipal()));
+      }
+      return (legacy.get());
+   }
+
+
+
+   /*******************************************************************************
+    ** The session for running as a user (owner or recipient).
+    *******************************************************************************/
+   public static QSession forUser(DataAnalysisQBitConfig config, String userId) throws QException
+   {
+      if(config.getOwnerSessionSupplier() != null)
+      {
+         return (config.getOwnerSessionSupplier().apply(userId));
+      }
+      if(usesPrincipal(config))
+      {
+         return (ServiceIdentities.sessionOnBehalfOf(QContext.getQInstance(), config.getServicePrincipal(), userId));
+      }
+      return (new QAuthenticationModuleDispatcher().getQModule(QContext.getQInstance().getAuthentication()).createAutomatedSessionForUser(QContext.getQInstance(), userId));
+   }
+
+
+
+   /*******************************************************************************
+    ** Whether the host declared the configured principal.
+    *******************************************************************************/
+   private static boolean usesPrincipal(DataAnalysisQBitConfig config)
+   {
+      return (config.getServicePrincipal() != null && ServiceIdentities.isDeclared(QContext.getQInstance(), config.getServicePrincipal()));
+   }
+}

src/main/java/com/kingsrook/qbits/dataanalysis/schedule/ScheduleRunner.java

@@ -58,7 +58,6 @@ import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
 import com.kingsrook.qqq.backend.core.model.data.QRecord;
 import com.kingsrook.qqq.backend.core.model.session.QSession;
 import com.kingsrook.qqq.backend.core.model.session.QUser;
-import com.kingsrook.qqq.backend.core.modules.authentication.QAuthenticationModuleDispatcher;
 
 /*******************************************************************************
  ** Scheduled work uses owner and recipient sessions; maintenance only inventories rows.
@@ -83,13 +82,17 @@ public class ScheduleRunner
     *******************************************************************************/
    public synchronized Integer runDue(Instant now) throws QException
    {
-      QSession maintenance = new QSession().withUser(new QUser().withIdReference("data-analysis-scheduler"))
-         .withPermissions("data_analysis.saved.read", "data_analysis.saved.edit")
-         .withSecurityKeyValue(DataAnalysisPermissions.ADMIN, true);
-      if(QContext.getQSession().getBackendVariants() != null)
+      QSession maintenance = AnalysisSessions.maintenance(config, () ->
       {
-         maintenance.setBackendVariants(new java.util.HashMap<>(QContext.getQSession().getBackendVariants()));
-      }
+         QSession legacy = new QSession().withUser(new QUser().withIdReference("data-analysis-scheduler"))
+            .withPermissions("data_analysis.saved.read", "data_analysis.saved.edit")
+            .withSecurityKeyValue(DataAnalysisPermissions.ADMIN, true);
+         if(QContext.getQSession().getBackendVariants() != null)
+         {
+            legacy.setBackendVariants(new java.util.HashMap<>(QContext.getQSession().getBackendVariants()));
+         }
+         return (legacy);
+      });
       int[] count = {0};
       QContext.withTemporaryContext(new CapturedContext(QContext.getQInstance(), maintenance), () ->
       {
@@ -161,9 +164,7 @@ public class ScheduleRunner
       QSession session;
       try
       {
-         session = config.getOwnerSessionSupplier() == null
-            ? new QAuthenticationModuleDispatcher().getQModule(QContext.getQInstance().getAuthentication()).createAutomatedSessionForUser(QContext.getQInstance(), id)
-            : config.getOwnerSessionSupplier().apply(id);
+         session = AnalysisSessions.forUser(config, id);
       }
       catch(Exception e)
       {

src/main/java/com/kingsrook/qbits/dataanalysis/watch/WatchEvaluator.java

@@ -49,7 +49,6 @@ import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
 import com.kingsrook.qqq.backend.core.model.data.QRecord;
 import com.kingsrook.qqq.backend.core.model.session.QSession;
 import com.kingsrook.qqq.backend.core.model.session.QUser;
-import com.kingsrook.qqq.backend.core.modules.authentication.QAuthenticationModuleDispatcher;
 
 /*******************************************************************************
  ** Transition watches through the core alerts SPI, computing exclusively as owner.
@@ -94,13 +93,17 @@ public class WatchEvaluator
     *******************************************************************************/
    public synchronized Integer evaluateEnabled(Instant now) throws QException
    {
-      QSession maintenance = new QSession().withUser(new QUser().withIdReference("data-analysis-scheduler"))
-         .withPermissions("data_analysis.watch.read", "data_analysis.watch.edit")
-         .withSecurityKeyValue(DataAnalysisPermissions.ADMIN, true);
-      if(QContext.getQSession().getBackendVariants() != null)
+      QSession maintenance = com.kingsrook.qbits.dataanalysis.schedule.AnalysisSessions.maintenance(config, () ->
       {
-         maintenance.setBackendVariants(new java.util.HashMap<>(QContext.getQSession().getBackendVariants()));
-      }
+         QSession legacy = new QSession().withUser(new QUser().withIdReference("data-analysis-scheduler"))
+            .withPermissions("data_analysis.watch.read", "data_analysis.watch.edit")
+            .withSecurityKeyValue(DataAnalysisPermissions.ADMIN, true);
+         if(QContext.getQSession().getBackendVariants() != null)
+         {
+            legacy.setBackendVariants(new java.util.HashMap<>(QContext.getQSession().getBackendVariants()));
+         }
+         return (legacy);
+      });
       int[] count = {0};
       QContext.withTemporaryContext(new CapturedContext(QContext.getQInstance(), maintenance), () ->
       {
@@ -128,9 +131,7 @@ public class WatchEvaluator
       QSession session;
       try
       {
-         session = config.getOwnerSessionSupplier() == null
-            ? new QAuthenticationModuleDispatcher().getQModule(QContext.getQInstance().getAuthentication()).createAutomatedSessionForUser(QContext.getQInstance(), owner)
-            : config.getOwnerSessionSupplier().apply(owner);
+         session = com.kingsrook.qbits.dataanalysis.schedule.AnalysisSessions.forUser(config, owner);
       }
       catch(Exception e)
       {

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

@@ -1753,6 +1753,14 @@ components:
           - "TRUE"
           - "FALSE"
           type: "string"
+        options:
+          description: "Options for how the criteria is applied, e.g. CASE_INSENSITIVE\
+            \ (string operators match regardless of case).  Since 1.9.0."
+          items:
+            enum:
+            - "CASE_INSENSITIVE"
+            type: "string"
+          type: "array"
         values:
           description: "Values to apply via the operator to the field"
           items:

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

@@ -25,6 +25,9 @@ import java.time.temporal.ChronoUnit;
 import java.util.List;
 import com.kingsrook.qbits.dataanalysis.app.DataAnalysisHome;
 import com.kingsrook.qbits.dataanalysis.model.DataAnalysisTables;
+import com.kingsrook.qbits.dataanalysis.permissions.DataAnalysisPermissions;
+import com.kingsrook.qqq.backend.core.actions.permissions.PermissionsHelper;
+import com.kingsrook.qqq.backend.core.model.actions.AbstractActionInput;
 import com.kingsrook.qqq.backend.core.context.QContext;
 import com.kingsrook.qqq.backend.core.model.actions.widgets.RenderWidgetInput;
 import com.kingsrook.qqq.backend.core.model.dashboard.widgets.MultiStatisticsData;
@@ -69,12 +72,43 @@ class DataAnalysisHomeTest
          }
       }
       QContext.init(instance, TestSupport.user("alice"));
-      assertThat(DataAnalysisHome.statistics(Instant.now()).getStatisticsGroupData()).allSatisfy(g ->
+      assertThat(DataAnalysisHome.statistics(Instant.now()).getStatisticsGroupData()).isEmpty();
+      assertThat(((TableData) DataAnalysisHome.render(DataAnalysisHome.FIRING, Instant.now())).getNoRowsFoundHTML()).doesNotContain("Unavailable");
+   }
+
+   @Test
+   void setupIsOffTheBusinessHomeAndAdminOnly() throws Exception
+   {
+      var instance = TestSupport.instance();
+      assertThat(DataAnalysisHome.homeWidgetNames()).doesNotContain(DataAnalysisHome.SETUP).containsExactly(DataAnalysisHome.HOME_WIDGET,
+         DataAnalysisHome.STARRED, DataAnalysisHome.FIRING, DataAnalysisHome.RECENT);
+      assertThat(instance.getApp("dataAnalysis").getWidgets()).doesNotContain(DataAnalysisHome.SETUP);
+      assertThat(DataAnalysisQBitProducer.SYSTEM_WIDGETS).containsExactly(DataAnalysisHome.SETUP);
+      assertThat(instance.getWidgets()).containsKey(DataAnalysisHome.SETUP);
+
+      QContext.init(instance, TestSupport.admin());
+      assertThat(PermissionsHelper.hasWidgetPermission(new AbstractActionInput(), DataAnalysisHome.SETUP)).isTrue();
+      assertThat(DataAnalysisHome.setup().getRows()).hasSize(5);
+      assertThat(new DataAnalysisHome().render(new RenderWidgetInput().withWidgetMetaData(instance.getWidget(DataAnalysisHome.SETUP))).getWidgetData()).isInstanceOf(TableData.class);
+
+      QContext.init(instance, TestSupport.user("alice").withPermissions(DataAnalysisPermissions.SAVED_READ));
+      assertThat(PermissionsHelper.hasWidgetPermission(new AbstractActionInput(), DataAnalysisHome.SETUP)).isFalse();
+   }
+
+   @Test
+   void savedReadOnlyUserGetsNoLockedShells() throws Exception
+   {
+      var instance = TestSupport.instance();
+      QContext.init(instance, TestSupport.user("alice").withPermissions(DataAnalysisPermissions.SAVED_READ));
+      List<String> visible = DataAnalysisHome.homeWidgetNames().stream().filter(name -> PermissionsHelper.hasWidgetPermission(new AbstractActionInput(), name)).toList();
+      assertThat(visible).containsExactly(DataAnalysisHome.HOME_WIDGET, DataAnalysisHome.STARRED);
+      for(String name : visible)
       {
-         assertThat(g.getStatisticList()).isEmpty();
-         assertThat(g.getSubheader()).contains("Unavailable");
-      });
-      assertThat(((TableData) DataAnalysisHome.render(DataAnalysisHome.FIRING, Instant.now())).getNoRowsFoundHTML()).contains("Unavailable");
+         var data = DataAnalysisHome.render(name, Instant.now());
+         assertThat(com.kingsrook.qqq.backend.core.utils.JsonUtils.toJson(data)).doesNotContain("Unavailable").doesNotContain("permission required");
+      }
+      var groups = DataAnalysisHome.statistics(Instant.now()).getStatisticsGroupData();
+      assertThat(groups).extracting(g -> g.getHeader()).containsExactly("Saved analyses");
    }
 
    @Test

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

@@ -213,7 +213,7 @@ class DataAnalysisMetadataTest
          "modify_date TIMESTAMP WITHOUT TIME ZONE", "rows_scanned BIGINT", "result_json TEXT", "shared BOOLEAN", "day INTEGER",
          "owner_user_id VARCHAR(250) NOT NULL", "date_field VARCHAR(250) NOT NULL", "last_value NUMERIC", "last_message TEXT", "CREATE UNIQUE INDEX IF NOT EXISTS uq_data_analysis_table_config_1 ON data_analysis_table_config (table_name)",
          "CREATE UNIQUE INDEX IF NOT EXISTS uq_data_analysis_saved_star_1 ON data_analysis_saved_star (saved_id, user_id)",
-         "CHECK (shared = TRUE OR read_owner_user_id IS NOT NULL)");
+         "CHECK (shared IS TRUE OR read_owner_user_id IS NOT NULL)");
       assertThat(ddl).endsWith(";\n");
    }
 }

src/test/java/com/kingsrook/qbits/dataanalysis/DataAnalysisSchemaTest.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;
+
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.UUID;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Execute generated saved-analysis DDL on H2 in PostgreSQL mode, including SQL's three-valued
+ ** CHECK semantics. These direct writes intentionally bypass SavedCustomizer.
+ *******************************************************************************/
+class DataAnalysisSchemaTest
+{
+   /*******************************************************************************
+    ** A null read owner grants shared reads, so only an explicit true shared flag
+    ** may omit it. Null and omitted flags must fail closed like false.
+    *******************************************************************************/
+   @Test
+   void savedAnalysisRequiresReadOwnerUnlessExplicitlyShared() throws Exception
+   {
+      try(Connection connection = DriverManager.getConnection("jdbc:h2:mem:saved_schema_" + UUID.randomUUID() + ";MODE=PostgreSQL;DATABASE_TO_UPPER=FALSE");
+         var statement = connection.createStatement())
+      {
+         for(String sql : DataAnalysisSchema.postgresScript(new DataAnalysisQBitConfig().withBackendName("database")).split(";"))
+         {
+            if(sql.stripLeading().startsWith("CREATE TABLE IF NOT EXISTS data_analysis_saved ("))
+            {
+               statement.execute(sql);
+            }
+         }
+
+         assertThatThrownBy(() -> statement.executeUpdate("INSERT INTO data_analysis_saved (name, table_name, kind, definition_json, owner_user_id) VALUES ('omitted flag', 'test_table', 'PIVOT', '{}', 'alice')"))
+            .isInstanceOf(SQLException.class).hasMessageContaining("ck_data_analysis_saved_read_owner");
+         assertThatThrownBy(() -> insert(connection, null, null)).isInstanceOf(SQLException.class).hasMessageContaining("ck_data_analysis_saved_read_owner");
+         assertThatThrownBy(() -> insert(connection, false, null)).isInstanceOf(SQLException.class).hasMessageContaining("ck_data_analysis_saved_read_owner");
+
+         insert(connection, true, null);
+         insert(connection, false, "alice");
+         insert(connection, null, "alice");
+         try(var rows = statement.executeQuery("SELECT COUNT(*) FROM data_analysis_saved"))
+         {
+            assertThat(rows.next()).isTrue();
+            assertThat(rows.getInt(1)).isEqualTo(3);
+         }
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Nullable values are bound explicitly so no Java boolean conversion can hide
+    ** the SQL null case that caused the original constraint to admit private rows.
+    *******************************************************************************/
+   private static void insert(Connection connection, Boolean shared, String readOwner) throws SQLException
+   {
+      try(var statement = connection.prepareStatement("INSERT INTO data_analysis_saved (name, table_name, kind, definition_json, owner_user_id, shared, read_owner_user_id) VALUES ('schema test', 'test_table', 'PIVOT', '{}', 'alice', ?, ?)"))
+      {
+         statement.setObject(1, shared);
+         statement.setString(2, readOwner);
+         statement.executeUpdate();
+      }
+   }
+}

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

@@ -118,6 +118,28 @@ abstract class PivotContractTest extends EngineFixture
       }
    }
 
+   /*******************************************************************************
+    ** A relative date in the filter ("created in the last N days") is evaluated
+    ** by core when the pivot runs, also after the JSON round trip of an HTTP
+    ** request or a saved definition, and the drills carry it.
+    *******************************************************************************/
+   @Test
+   void pivotWithRelativeDateFilter() throws Exception
+   {
+      PivotRequest everything = pivot(List.of("status"), List.of()).withFilter(new com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter()
+         .withCriteria("created", QCriteriaOperator.GREATER_THAN_OR_EQUALS, com.kingsrook.qqq.backend.core.model.actions.tables.query.expressions.NowWithOffset.minus(36500, java.time.temporal.ChronoUnit.DAYS)));
+      for(PivotRequest request : List.of(everything, JsonUtils.toObject(JsonUtils.toJson(everything), PivotRequest.class)))
+      {
+         PivotResult result = service.pivot(request);
+         equalNumber(result.getGrandTotals().getFirst(), "12");
+         countDrills(result);
+      }
+
+      PivotRequest lastDay = pivot(List.of("status"), List.of()).withFilter(new com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter()
+         .withCriteria("created", QCriteriaOperator.GREATER_THAN_OR_EQUALS, com.kingsrook.qqq.backend.core.model.actions.tables.query.expressions.NowWithOffset.minus(1, java.time.temporal.ChronoUnit.DAYS)));
+      assertThat(service.pivot(JsonUtils.toObject(JsonUtils.toJson(lastDay), PivotRequest.class)).getCells()).isEmpty();
+   }
+
    /*******************************************************************************
     ** Verify or support the pivot and field-access contract.
     *******************************************************************************/

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

@@ -26,6 +26,7 @@ import java.util.ArrayList;
 import java.util.List;
 import java.util.Map;
 import com.kingsrook.qbits.dataanalysis.model.DataAnalysisTables;
+import com.kingsrook.qbits.dataanalysis.permissions.DataAnalysisPermissions;
 import com.kingsrook.qbits.dataanalysis.processes.ScheduleActions;
 import com.kingsrook.qbits.dataanalysis.saved.AnalysisExport;
 import com.kingsrook.qbits.dataanalysis.saved.SavedDefinition;
@@ -47,6 +48,8 @@ import com.kingsrook.qqq.backend.core.model.metadata.messaging.QMessagingProvide
 import com.kingsrook.qqq.backend.core.model.session.QSession;
 import com.kingsrook.qqq.backend.core.modules.messaging.MessagingProviderInterface;
 import com.kingsrook.qqq.backend.core.modules.messaging.QMessagingProviderDispatcher;
+import com.kingsrook.qqq.backend.core.serviceidentity.QServicePrincipalMetaData;
+import com.kingsrook.qqq.backend.core.serviceidentity.ServiceIdentitiesMetaData;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
 import static org.assertj.core.api.Assertions.assertThat;
@@ -160,6 +163,27 @@ class SchedulesTest extends EngineFixture
       assertThat(WatchesTest.Sink.raised).isEmpty();
    }
 
+   /*******************************************************************************
+    ** With the host's data-analysis principal declared, schedule inventory runs
+    ** as the principal (its grants decide - a principal without them finds
+    ** nothing due), never as a self-made session.
+    *******************************************************************************/
+   @Test
+   void inventoryRunsAsTheDeclaredPrincipal() throws Exception
+   {
+      schedule("PIVOT", Map.of("rows", List.of(Map.of("field", "status")), "values", List.of(Map.of("operator", "COUNT"))), "CSV");
+      config.withServicePrincipal("analysis-bare");
+      ServiceIdentitiesMetaData.declare(instance, new QServicePrincipalMetaData().withName("analysis-bare"));
+      assertThatThrownBy(() -> runner.runDue(NOW)).isInstanceOf(QException.class);
+      assertThat(Mail.sent).isEmpty();
+
+      ServiceIdentitiesMetaData.declare(instance, new QServicePrincipalMetaData().withName("data-analysis")
+         .withLocalPermissions("data_analysis.saved.read", "data_analysis.saved.edit").withLocalSecurityKeyValue(DataAnalysisPermissions.ADMIN, true));
+      config.withServicePrincipal("data-analysis");
+      assertThat(runner.runDue(NOW)).isEqualTo(1);
+      assertThat(Mail.sent).hasSize(2);
+   }
+
    @Test
    void providerFailureAndMissingOwnerRaiseVisibleAlerts() throws Exception
    {