[I] [SIP] Proposal for Testing Alert Queries Before Saving [superset]

["janeloww (via GitHub)" <gi...@apache.org>]

janeloww opened a new issue, #45586:
URL: https://github.com/apache/superset/issues/45586

   ## [SIP] Proposal for Testing Alert Queries Before Saving
   Note: This issue is part of the 2026-2027 batch of approved UTSC issues and is already being worked on!
   
   ### Motivation
   
   Currently, Superset users cannot easily verify that an alert query works before saving the alert. Problems such as invalid SQL, insufficient database permissions, multiple rows or columns, or a non-numeric result may only arise when the scheduled alert runs, making debugging difficult.
   
   This [existing feature request](https://github.com/apache/superset/discussions/36363) proposes adding a "Test Query button" to catch this up front, and we would like to tackle this feature. Once the user creates their alert, they can click this button to run the query and report the result, allowing the user to test their alert's functionality.
   
   Alert validation is currently split between two places, which can make their rules inconsistent.
   - supertset/commands/report/base.py performs static validation when an alert is saved, checked the SQL and access permissions without actually running the query
   - superset/commands/report/alert.py performs additional checks when the alert runs, including executing the query and validating its result. 
   
   
   ### Proposed Changes
   
   1. Create superset/commands/report/alert_validation.py to consolidate the validation logic:
   
      - validate_alert_sql(): checks SQL safety and permissions without executing the query. Is used when saving an alert
      - run_alert_check(): performs the necessary validation, executes the query, checks that it returns exactly one row and one column containing a valid numeric result, and evaluates the alert condition. Used for testing and scheduled execution.
   
   Testing, saving and running alert queries, should reuse the same validation rules wherever possible, reducing inconsistencies and preventing the same logic from being implemented multiple times. 
   
   2. Add a new endpoint:  POST /api/v1/report/test_alert_query/ to test an alert using its unsaved SQL and configuration. This will call run_alert)check() with a timeout. It must not save or modify the alert, update its schedule, or send notifications.
    
   3. Add a "Test query" button under the SQL editor in AlertReportModal.tsx, with the result shown inline (not a toast). Errors show help text based on error_type, including guidance where possible.
   
   
   ### New or Changed Public Interfaces: 
   
   Add a new endpoint, POST /api/v1/report/test_alert_query/, to allow users to test an alert query before saving the alert. The endpoint accepts the alert's unsaved SQL and configuration, executes the query, and returns the result and whether the alert condition is met.
   
   Request body:
   - database: Database identifier used to execute the query
   - sql: SQL query to test
   - validator_type: Type of alert condition to evaluate
   - validator_config_json: Configuration for the selected alert condition
   - Optional Run-as fields: Specify the execution identity when supported
   - Optional report_id: Identifies an existing report when needed for permission checks or execution context.
   
   Responses:
   - 200: Returns the query result, whether the alert condition is triggered, a human-readable message, and the identity under which the query was executed
   - 403, 404, 408, 422 as shown in superset/commands/report/exceptions.py
   
   The endpoint uses the existing can_write permission on ReportSchedule. Testing does not create or modify a report schedule, change its configuration, or send notifications.
   
   Frontend: 
   - Update AlertReportModal.tsx to include a Test query button below the SQL editor. The button submits the current, unsaved alert configuration to the new endpoint. Results are displayed inline in the modal, including whether the alert condition is triggered. Validation and execution errors are displayed with contextual help text where possible.
   
   
   ### New dependencies
   
   No new dependencies.
   
   
   ### Migration Plan and Compatibility
   
   No database migration is expected because this change introduces a new API endpoint and frontend functionality rather than new persistent data.
   
   Existing alerts should continue to work. Alerts containing file-transfer statements may now be rejected when saved, aligning save-time validation with the restriction already applied during execution.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org