samraHanif0340 opened a new issue, #33012: URL: https://github.com/apache/superset/issues/33012
## [SIP] Proposal for Introducing Saved Queries in Alerts ### Proposal Summary This proposal introduces an option for users to select a pre-existing saved query when creating or editing an alert in Apache Superset. By decoupling the SQL definition from the alert configuration, users will have a more streamlined experience for updating SQL queries without having to modify the alert settings repeatedly. ### Motivation **Problem Statement** Currently, Superset allows users to include SQL queries directly in an alert’s configuration. When changes to an SQL query are necessary, users must: - Go to SQL Lab and modify or test the query, - Navigate back to the Alerts interface, - Update the SQL in the alert configuration manually. This back-and-forth is inefficient and can be error-prone, particularly in environments where queries often evolve over time. **Rationale** By providing the ability to reference a saved query (which itself can be updated independently), we can: - Reduce redundant steps for editing SQL queries in alerts. - Minimise errors or oversights caused by manual copying and pasting. - Promote reusability and consistency of SQL statements across different alerts. ### Proposed Change and New or Changed Public Interfaces **Alerts UI:** 1. Add a **checkbox** or toggle to use an existing saved query instead of manually entering SQL. 2. If checked, a dropdown of the **user’s saved queries** appears, allowing them to select the query to be used for the alert. **API Endpoints:** 1. [`api/v1/reports`]: Extend the existing reports API to accept and store `query_id` alongside the current SQL field (for backward compatibility). 2. [`api/v1/saved_queries`]: Retrieve the user’s saved queries and populate the selection dropdown. Potentially reuse the existing endpoint to fetch a list of saved queries by user ownership. **Alert Execution:** **AlertCommand** in `alert.py`: Modify the execution logic to fetch the SQL from the chosen saved_query if a `query_id` is present. Then proceed with the usual alert evaluation and notifications. **Database Schema Changes:** `reports_schedule`: A new column `query_id` (nullable) that references the relevant saved query’s ID. **User Experience** **Create/Edit Alert:** Option A: Enter a new SQL query (as it is today). Option B: Choose “Attach Saved Query” and select from a list of owned (or otherwise permitted) saved queries. **Editing a Saved Query:** Users can revise or refine the query in SQL Lab, and those changes automatically propagate to any alerts referencing that saved query. **Wireframes** <img width="479" alt="Image" src="https://github.com/user-attachments/assets/9d025a6e-5cee-46fc-a8f9-15658e7534dd"; /> <img width="1056" alt="Image" src="https://github.com/user-attachments/assets/5765e50f-aea6-4d98-811f-f40b9f0b8ea7"; /> ### Migration Plan and Compatibility A new database migration script to: 1. Add the `query_id` column to `reports_schedule`. **Backward Compatibility:** - Alerts without a query_id will continue to behave exactly as before, reading SQL from the existing column. - Users can gradually transition to using saved queries by updating alerts. ### Future Plan **Deprecation of Embedded Queries:** In the future, the project might consider deprecating inline SQL for alerts in favor of strictly referencing saved queries. This, however, should be subject to feedback from the community and a formal deprecation notice. **Enhanced Permissions:** Granular permissions could be introduced to manage which roles can attach or modify saved queries used by alerts. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
