Scheduled Query Plugin

A powerful FastPluggy plugin for scheduling and executing SQL queries on a recurring basis with comprehensive monitoring, result history tracking, and Prometheus metrics integration.

Overview

The Scheduled Query plugin allows you to: - Schedule SQL queries to run automatically using CRON syntax - Monitor query execution with detailed history tracking - Export query results as Prometheus metrics - View results in multiple formats (table, counter, metric, raw) - Set up query execution intervals with safety controls - Track execution duration and status

Features

Core Features

✅ CRON-based Scheduling: Use standard CRON syntax for flexible scheduling
✅ Automatic Execution: Background task worker executes queries every 60 seconds
✅ Query Safety: Built-in keyword filtering to prevent dangerous operations
✅ History Tracking: Complete execution history with timestamps, duration, and results
✅ Multiple Result Formats: Auto-detect or force render types (table, counter, metric, raw)
✅ Error Handling: Comprehensive error tracking and logging
✅ Real-time Execution: Run queries immediately via the "Run Now" button

Monitoring & Metrics

📊 Prometheus Integration: Export query results as Prometheus Gauge metrics
📈 Execution Dashboard: Visual dashboard showing all queries and their status
⏱️ Performance Tracking: Monitor query execution time in milliseconds
📋 Execution History: Paginated history with filtering by status

UI Features

🎨 Result Widgets: Customizable widgets for displaying query results
🔍 Advanced Filtering: Filter execution history by date range and status
✏️ Edit Interface: Easy-to-use forms for creating and editing queries
📊 Dashboard View: Overview of all scheduled queries with real-time status

Installation

Using pip

pip install fastpluggy-scheduled-query

From source

cd fastpluggy_plugin/scheduled_query
pip install -e .

Requirements

Python >= 3.10
fastpluggy-ui-tools
fastpluggy-tasks-worker >= 0.3.0
loguru
prometheus_client

The plugin will be automatically discovered by FastPluggy through the entry point system.

Configuration

The plugin can be configured through environment variables or the FastPluggy configuration system:

Available Settings

class ScheduledQuerySettings(BaseDatabaseSettings):
    # Enable notifications when queries run
    notification_on_run: bool = False

    # Comma-separated list of forbidden SQL keywords
    forbidden_keywords: str = "drop,delete,truncate,alter"

    # Minimum interval between query executions (seconds)
    interval: int = 30

    # Enable execution history tracking
    enable_history: bool = True

    # Maximum number of history records to keep (-1 = unlimited)
    limit_history: int = -1

    # Enable Prometheus metrics export
    prometheus_enabled: bool = True

Environment Variables

You can override these settings using environment variables with the prefix SCHEDULED_QUERY_:

export SCHEDULED_QUERY_FORBIDDEN_KEYWORDS="drop,delete,truncate,alter,update"
export SCHEDULED_QUERY_INTERVAL=60
export SCHEDULED_QUERY_ENABLE_HISTORY=true
export SCHEDULED_QUERY_PROMETHEUS_ENABLED=true

Usage

Creating Scheduled Queries

Via Web Interface

Navigate to Scheduled Query → Scheduled Queries in the menu
Click the Edit button for an existing query or create a new one
Fill in the form:
Name: Descriptive name for the query
Query: SQL query to execute (SELECT queries recommended)
CRON Schedule: CRON expression (e.g., 0 6 * * * for daily at 6 AM)
Enabled: Toggle to enable/disable execution
Render Type: Choose how to display results (auto, table, counter, metric, raw)

Programmatically

from fastpluggy.core.database import session_scope
from fastpluggy_plugin.scheduled_query.src.models import ScheduledQuery
from datetime import datetime

with session_scope() as db:
    query = ScheduledQuery(
        name="Daily User Count",
        query="SELECT COUNT(*) as total_users FROM users WHERE is_active = true",
        cron_schedule="0 6 * * *",  # Daily at 6 AM
        enabled=True,
        render_type="counter"
    )
    db.add(query)
    db.commit()

Managing Queries

Enable/Disable Queries

Edit the query through the web interface and toggle the "Enabled" checkbox.

Run Query Immediately

Click the Run Now button next to any query in the list view to execute it immediately, bypassing the schedule.

View Execution History

Navigate to Scheduled Query → Dashboard to see: - Latest query results - Execution history with pagination - Performance metrics - Error messages for failed executions

Viewing Results

Dashboard View

The dashboard (/scheduled_query/dashboard) provides: - List of all scheduled queries - Latest execution results - Interactive history viewer with filters - Real-time status updates

Result Widgets

Visit /scheduled_query/results-widgets to see queries displayed as widgets with different rendering modes:

Auto: Automatically detects the best format
Table: Displays results in a table format
Counter/Metric: Shows numeric results as large numbers
Raw: Displays raw result text

API Endpoints

All API endpoints require authentication.

Run Query Now

GET /api/run-now/{query_id}

Executes a scheduled query immediately and redirects to the queries list.

Parameters: - query_id (int): ID of the query to execute

Response: Redirect to scheduled queries list (303)

Get Execution History

GET /api/execution-history/{query_id}

Retrieves paginated execution history for a specific query.

Parameters: - query_id (int): ID of the query - page (int, optional): Page number (default: 1) - limit (int, optional): Records per page (default: 50, max: 200) - status_filter (str, optional): Filter by status: "all", "success", "failed", "timeout"

Response:

{
  "data": [
    {
      "id": 1,
      "executed_at": "2025-11-05 12:04:30",
      "duration_ms": 245,
      "result": "[(87, datetime.date(2025, 11, 05))]",
      "result_key": ["count", "date"],
      "status": "success",
      "error_message": null,
      "grafana_metrics_snapshot": null
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 50,
    "total_count": 150,
    "total_pages": 3,
    "has_next": true,
    "has_prev": false
  },
  "query_info": {
    "id": 1,
    "name": "Daily User Activity"
  }
}

Get Query Info

GET /api/scheduled-queries/{query_id}

Returns detailed information about a specific scheduled query.

Response:

{
  "id": 1,
  "name": "Daily User Count",
  "query": "SELECT COUNT(*) FROM users",
  "cron_schedule": "0 6 * * *",
  "last_executed": "2025-11-05 06:00:15",
  "enabled": true,
  "grafana_metric_config": null
}

Web Interface

Routes

Route	Menu Label	Description
`/scheduled_query/`	Scheduled Queries	List and manage all scheduled queries
`/scheduled_query/dashboard`	Dashboard	View query results and execution history
`/scheduled_query/results-widgets`	Results Widgets	Display query results as widgets
`/scheduled_query/edit/{query_id}`	-	Edit form for a specific query

All routes require authentication.

Data Models

ScheduledQuery

Stores query definitions and configuration.

class ScheduledQuery(Base):
    __tablename__ = 'fp_scheduled_queries'

    id: int                          # Primary key
    name: str                        # Query name
    query: str                       # SQL query text
    cron_schedule: str              # CRON expression
    last_executed: datetime         # Last execution timestamp
    grafana_metric_config: dict     # Prometheus metric configuration
    enabled: bool                   # Enable/disable flag
    last_result: str                # Latest query result
    last_result_key: list           # Column names from result
    render_type: str                # Display format: auto/table/counter/metric/raw

ScheduledQueryResultHistory

Tracks execution history for each query.

class ScheduledQueryResultHistory(Base):
    __tablename__ = 'fp_scheduled_query_results'

    id: int                         # Primary key
    scheduled_query_id: int         # Foreign key to ScheduledQuery
    executed_at: datetime           # Execution timestamp
    duration_ms: int                # Execution duration in milliseconds
    result: str                     # Query result
    result_key: list                # Column names
    status: str                     # 'success', 'failed', 'timeout'
    error_message: str              # Error details if failed
    grafana_metrics_snapshot: dict  # Metrics snapshot

Prometheus Metrics

When enabled, the plugin can export query results as Prometheus Gauge metrics.

Configuration

Add Grafana metric configuration to a scheduled query:

query.grafana_metric_config = {
    "metric_name": "user_count_total",
    "labels": {
        "environment": "production",
        "service": "api"
    }
}

Requirements

Query must return a numeric value
The result is evaluated and the first numeric value is extracted
Metrics are updated after each successful query execution

Example

# Query that counts active users
query = ScheduledQuery(
    name="Active Users Metric",
    query="SELECT COUNT(*) FROM users WHERE is_active = true",
    cron_schedule="*/5 * * * *",  # Every 5 minutes
    enabled=True,
    grafana_metric_config={
        "metric_name": "active_users_total",
        "labels": {"type": "active"}
    }
)

This will create a Prometheus metric accessible at /metrics:

# HELP active_users_total Metric for query SELECT COUNT(*) FROM users WHERE is_active = true
# TYPE active_users_total gauge
active_users_total 594.0

Security

Query Safety

The plugin includes built-in protection against dangerous SQL operations:

Forbidden Keywords: Configurable list of keywords that are blocked (default: drop, delete, truncate, alter)
Keyword Detection: Case-insensitive scanning of query text
Rejection Logging: Rejected queries are logged with the reason

Authentication

All endpoints and routes require authentication through FastPluggy's authentication system.

Best Practices

Use SELECT queries: Prefer read-only queries to avoid data modification
Limit result size: Large result sets can impact performance
Test queries manually: Verify queries before scheduling
Monitor execution time: Set appropriate CRON schedules based on query duration
Review history regularly: Check for failed executions and errors

Examples

Example 1: Daily Active Users

ScheduledQuery(
    name="Daily Active Users",
    query="SELECT COUNT(*) as active_users FROM users WHERE last_login >= CURDATE()",
    cron_schedule="0 8 * * *",  # Daily at 8 AM
    enabled=True,
    render_type="counter"
)

Example 2: Order Status Summary

ScheduledQuery(
    name="Order Status Summary",
    query="""
        SELECT status, COUNT(*) as count, SUM(total_amount) as revenue
        FROM orders
        WHERE created_at >= CURDATE() - INTERVAL 7 DAY
        GROUP BY status
    """,
    cron_schedule="0 */4 * * *",  # Every 4 hours
    enabled=True,
    render_type="table"
)

Example 3: System Health Check

ScheduledQuery(
    name="Database Connection Pool",
    query="SHOW STATUS LIKE 'Threads_connected'",
    cron_schedule="*/5 * * * *",  # Every 5 minutes
    enabled=True,
    render_type="metric",
    grafana_metric_config={
        "metric_name": "mysql_threads_connected",
        "labels": {"instance": "primary"}
    }
)

Creating Test Data

Use the included example script to create test queries:

cd /path/to/fastpluggy
python fastpluggy_plugin/scheduled_query/examples/create_test_data.py

Options: - --cleanup: Remove all test data - --recreate: Clean up and recreate test data

Dependencies

The plugin depends on the following FastPluggy plugins and libraries:

Plugin Dependencies

tasks_worker (>= 0.2.0): Background task execution
ui_tools (>= 0.0.4): UI components and widgets

Python Dependencies

loguru: Logging framework
prometheus_client: Prometheus metrics export
sqlalchemy: Database ORM
fastapi: Web framework

Architecture

Task Execution Flow

Task Scheduler: Runs every 60 seconds via collect_execute_scheduled_query
Query Selection: Fetches all enabled queries from the database
Safety Check: Validates query against forbidden keywords
Execution: Runs query and measures duration
Result Storage: Saves results to the database and history table
Metrics Update: Updates Prometheus metrics if configured
Interval Wait: Sleeps for configured interval before next query

Result Rendering

The plugin supports multiple render types for displaying results:

auto: Automatically detects the best format based on result structure
table: Forces table display for all results
counter/metric: Displays numeric results as large formatted numbers
raw: Shows raw text output

Troubleshooting

Query Not Executing

Check if the query is enabled in the database
Verify the tasks_worker plugin is running
Check logs for error messages
Ensure the query doesn't contain forbidden keywords

History Not Being Saved

Verify enable_history is set to true in settings
Check database permissions
Review logs for database errors

Prometheus Metrics Not Updating

Ensure prometheus_enabled is set to true
Verify grafana_metric_config is properly configured
Check that query returns numeric values
Review logs for metric update errors

Contributing

Contributions are welcome! Please ensure: - Code follows the existing style - New features include tests - Documentation is updated - Changes are backward compatible

License

This plugin is part of the FastPluggy framework.

Support

For issues and questions: - Check the FastPluggy documentation - Review the example scripts in the examples/ directory - Check the logs in /var/log/fastpluggy/ or configured log directory

Version: 0.1.48
Last Updated: November 2025
Maintained by: FastPluggy Team

Scheduled Query Plugin

Table of Contents

Overview

Features

Core Features

Monitoring & Metrics

UI Features

Installation

Using pip

From source

Requirements

Configuration

Available Settings

Environment Variables

Usage

Creating Scheduled Queries

Via Web Interface

Programmatically

Managing Queries

Enable/Disable Queries

Run Query Immediately

View Execution History

Viewing Results

Dashboard View

Result Widgets

API Endpoints

Run Query Now

Get Execution History

Get Query Info

Web Interface

Routes

Data Models

ScheduledQuery

ScheduledQueryResultHistory

Prometheus Metrics

Configuration

Requirements

Example

Security

Query Safety

Authentication

Best Practices

Examples

Example 1: Daily Active Users

Example 2: Order Status Summary

Example 3: System Health Check

Creating Test Data

Dependencies

Plugin Dependencies

Python Dependencies

Architecture

Task Execution Flow

Result Rendering

Troubleshooting

Query Not Executing

History Not Being Saved

Prometheus Metrics Not Updating

Contributing

License

Support