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.

Table of Contents

• Overview
• Features
• Installation
• Configuration
• Usage
• Creating Scheduled Queries
• Managing Queries
• Viewing Results
• API Endpoints
• Web Interface
• Data Models
• Prometheus Metrics
• Security
• Examples
• Dependencies

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

1. Navigate to Scheduled Query → Scheduled Queries in the menu
2. Click the Edit button for an existing query or create a new one
3. 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

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

1. Use SELECT queries: Prefer read-only queries to avoid data modification
2. Limit result size: Large result sets can impact performance
3. Test queries manually: Verify queries before scheduling
4. Monitor execution time: Set appropriate CRON schedules based on query duration
5. 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

1. Task Scheduler: Runs every 60 seconds via collect_execute_scheduled_query
2. Query Selection: Fetches all enabled queries from the database
3. Safety Check: Validates query against forbidden keywords
4. Execution: Runs query and measures duration
5. Result Storage: Saves results to the database and history table
6. Metrics Update: Updates Prometheus metrics if configured
7. 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

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

History Not Being Saved

1. Verify enable_history is set to true in settings
2. Check database permissions
3. Review logs for database errors

Prometheus Metrics Not Updating

1. Ensure prometheus_enabled is set to true
2. Verify grafana_metric_config is properly configured
3. Check that query returns numeric values
4. 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