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,12 +1,12 @@
 version: 2.1
 setup: true
 orbs:
-  qqq-orb: kingsrook/qqq-orb@0.7.12
+  qqq-orb: kingsrook/qqq-orb@0.8
 workflows:
   setup:
     jobs:
     - qqq-orb/generate_pipeline:
-        orb_version: 0.7.12
+        orb_version: '0.8'
         filters:
           branches:
             ignore: automation/qbit-rc-pending

.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

.qqq-ci.yml

@@ -1,5 +1,5 @@
 profile: qbit
-orb_version: 0.7.12
+orb_version: '0.8'
 default_branch: develop
 java_version: '21'
 browsers: true

CHANGELOG.md

@@ -4,8 +4,14 @@ 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.

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.19</qqq.snapshot.version>
+            <qqq.snapshot.version>4.1.0-RC.26</qqq.snapshot.version>
          </properties>
          <dependencyManagement>
             <dependencies>

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/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/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/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.
     *******************************************************************************/