#429 App: Scheduled SQL Reporter (sql_reporter)

## Overview Run database queries on schedule and email formatted results as reports. ## App Metadata - **App Name:** sql_reporter - **Publisher:** platform - **Version:** 1.0.0 - **Priority:** Medium - **Complexity:** Medium - **Phase:** 2 (Core business value) ## Actions ### 1. run_query **Description:** Execute SQL query against configured database **Parameters:** - query (str, required): SQL query to execute (SELECT only) - database_alias (str, default='default'): Database connection alias - timeout_seconds (int, default=60): Query timeout - max_rows (int, default=10000): Maximum rows to return - parameters (dict, optional): Query parameters for prepared statements **Returns:** { "success": true, "row_count": 150, "columns": ["id", "name", "value"], "rows": [[1, "a", 100], ...], "execution_time_ms": 250, "truncated": false } ### 2. format_results **Description:** Convert query results to formatted output **Parameters:** - results (dict, required): Query results from run_query - format (str, default='html_table'): Output format (html_table|csv|json|markdown) - title (str, optional): Report title - include_summary (bool, default=True): Include row count and execution stats **Returns:** {"formatted": "...", "format": "html_table", "size_bytes": 5000} ### 3. email_report **Description:** Email formatted report **Parameters:** - formatted_report (str, required): Formatted report content - recipient_email (str, required): Destination email - subject (str, required): Email subject - format (str, default='html'): Email format - attach_csv (bool, default=False): Attach raw data as CSV - results (dict, optional): Raw results for CSV attachment **Returns:** {"sent": true, "recipient": "...", "attachment_size": 1024} ### 4. run_and_email **Description:** Convenience action - run query and email in one step **Parameters:** - query (str, required): SQL query - recipient_email (str, required): Destination email - subject (str, required): Email subject - database_alias (str, default='default'): Database alias - format (str, default='html_table'): Report format - attach_csv (bool, default=True): Include CSV attachment **Returns:** Combined results ## Example Workflow - Daily Sales Report builder = WorkflowBuilder(name='daily_sales_report', version='1.0.0') builder.task( task_id='run_report', function='apps.platform.sql_reporter.run_and_email', kwargs={ 'query': ''' SELECT date, product, SUM(amount) as total FROM sales WHERE date = CURRENT_DATE - INTERVAL '1 day' GROUP BY date, product ORDER BY total DESC ''', 'recipient_email': 'finance@example.com', 'subject': 'Daily Sales Report - {{today}}', 'attach_csv': True } ) ## Security Considerations - ONLY allow SELECT queries (parse and validate) - Use prepared statements for parameters - Limit query execution time - Restrict accessible databases via configuration - Audit log all queries ## Implementation Notes - Use psycopg for PostgreSQL (primary) - Consider supporting MySQL via separate container - Query validation: reject INSERT/UPDATE/DELETE/DROP/TRUNCATE - HTML table styling with inline CSS for email compatibility - Large result sets: paginate or attach as file ## Configuration Schema { "databases": { "default": { "type": "postgresql", "host": "configured_via_secrets", "port": 5432, "database": "reporting_db" } }, "max_query_timeout": 300, "max_rows": 50000, "allowed_schemas": ["public", "reporting"] } ## Secrets Config { "db_default_connection": "vault:tenant/databases:reporting", "db_analytics_connection": "vault:tenant/databases:analytics" } ## Dependencies - psycopg[binary] for PostgreSQL - Container with DB client for other databases - Email sending (reuse SMTP config) - CSV module (stdlib)