superset-dashboard-automation

# Superset Dashboard Developer Agent (Tableau Alternative) Transform Claude into an Apache Superset expert that creates enterprise BI dashboards automatically with production-grade DigitalOcean deployment. ## What This Skill Does **Auto-generate dashboards** - BIR compliance, finance SSC, operational analytics **Create datasets** - Optimized SQL from Supabase/Odoo **Build charts** - 20+ visualization types with best practices **Deploy to DigitalOcean** - Production-ready App Platform configuration **Apply templates** - Pre-built dashboards for common use cases **Schedule refreshes** - Automated data updates **Annual Savings: $8,400 (vs Tableau 10-user license)** ## Quick Start When asked to create dashboards: 1. **Deploy Superset**: Choose deployment method - **App Platform** (easier, $27/month): Zero maintenance - **Droplets** (flexible, $28/month): Full control 2. **Initialize database**: Via console or automated script 3. **Create dataset**: Write optimized SQL query 4. **Build charts**: Select appropriate visualization 5. **Assemble dashboard**: Layout charts with filters 6. **Apply template**: Use pre-built for common scenarios ### Deployment Quick Links - [App Platform Guide](deployment/digitalocean-spec.yaml) - Managed, zero maintenance - [Droplet Guide](deployment/droplet-guide.md) - Self-hosted, full control - [Comparison Guide](deployment/droplet-vs-appplatform.md) - Which to choose? ## DigitalOcean Deployment Options ### Option 1: App Platform (Managed, Zero Maintenance) **Best for**: Teams without DevOps, want zero maintenance [Complete guide](deployment/digitalocean-spec.yaml) ### Corrected App Spec (DO Best Practices) ```yaml name: superset region: sgp # Or your preferred region services: - name: superset image: registry_type: DOCKER_HUB registry: apache repository: superset tag: "3.1.0" # Pin to stable version (not 'latest') instance_size_slug: professional-xs # Minimum 1GB RAM instance_count: 1 http_port: 8088 envs: # Required: Database connection - key: DATABASE_URL value: postgresql://postgres:password@db.xxx.supabase.co:5432/postgres type: SECRET scope: RUN_TIME # Required: Secret key for sessions - key: SUPERSET_SECRET_KEY value: your-secret-key-here type: SECRET scope: RUN_TIME # Required: Redis for caching - key: REDIS_URL value: redis://redis-host:6379/0 type: SECRET scope: RUN_TIME # Production settings - key: SUPERSET_ENV value: production scope: RUN_TIME - key: ENABLE_PROXY_FIX value: 'True' scope: RUN_TIME - key: ALLOWED_HOSTS value: "*" # Change to your domain after setup scope: RUN_TIME - key: SUPERSET_LOAD_EXAMPLES value: 'False' scope: RUN_TIME - key: WTF_CSRF_ENABLED value: 'True' scope: RUN_TIME # CRITICAL: Persist Superset home directory volumes: - name: superset-data mount_path: /app/superset_home health_check: initial_delay_seconds: 180 period_seconds: 30 timeout_seconds: 10 failure_threshold: 3 http_path: /health domains: - domain: superset.yourdomain.com type: PRIMARY ``` ### Why Volume Persistence is Critical **Without volumes, you'll lose:** - ✗ User accounts and permissions - ✗ Custom dashboards and charts - ✗ Database connections - ✗ Uploaded files and assets - ✗ All configuration on every deploy **With volumes (`/app/superset_home`), you keep:** - ✓ All user data - ✓ Dashboard configurations - ✓ Database connections - ✓ Upload persistence - ✓ Session state ### Instance Sizing Guide | Size | RAM | vCPU | Use Case | Monthly Cost | |------|-----|------|----------|--------------| | `basic-xxs` | 512MB | Shared | ❌ Too small | $5 | | `professional-xs` | 1GB | 1 vCPU | ✅ Minimum for production | $12 | | `professional-s` | 2GB | 1 vCPU | ✅ Recommended for 10-20 users | $25 | | `professional-m` | 4GB | 2 vCPU | ✅ For 50+ users | $50 | ### Manual Initialization (DO-Aligned Approach) **❌ DO NOT use POST_DEPLOY or PRE_DEPLOY jobs** - they hang because `doctl apps console` requires interactive TTY. **✅ DO THIS instead:** ```bash # Step 1: Deploy app without init job doctl apps update <app-id> --spec superset.yaml # Step 2: Wait for deployment doctl apps get <app-id> --wait # Step 3: Open interactive console doctl apps console <app-id> superset # Step 4: Inside console, run these commands: superset db upgrade superset fab create-admin \ --username admin \ --firstname Jake \ --lastname Tolentino \ --email jgtolentino_rm@yahoo.com \ --password YourSecurePassword superset init exit ``` ### Redis Configuration Add managed Redis to your app: ```yaml databases: - name: superset-redis engine: REDIS production: true version: "7" ``` Then reference it: ```yaml - key: REDIS_URL value: ${superset-redis.REDIS_URL} scope: RUN_TIME ``` ### Option 2: Droplets (Self-Hosted, Full Control) **Best for**: DevOps teams, cost optimization, need root access **Quick Start (Automated):** ```bash # SSH into your Droplet ssh root@YOUR_DROPLET_IP # Run automated installer curl -fsSL https://raw.githubusercontent.com/your-repo/setup.sh | bash # Or manual installation: wget https://example.com/droplet-setup.sh chmod +x droplet-setup.sh ./droplet-setup.sh ``` **What's included:** - ✅ Docker + Docker Compose - ✅ Superset 3.1.0 - ✅ PostgreSQL 15 - ✅ Redis 7 - ✅ Nginx reverse proxy - ✅ Automated backups - ✅ UFW firewall - ✅ Health checks **Manual Installation:** See complete guide: [Droplet Deployment Guide](deployment/droplet-guide.md) **Docker Compose Stack:** ```yaml version: '3.8' services: superset: image: apache/superset:3.1.0 ports: - "8088:8088" environment: DATABASE_HOST: postgres REDIS_HOST: redis SUPERSET_SECRET_KEY: ${SECRET_KEY} volumes: - superset-home:/app/superset_home postgres: image: postgres:15-alpine volumes: - postgres-data:/var/lib/postgresql/data redis: image: redis:7-alpine volumes: - redis-data:/data ``` **Cost Comparison:** | Deployment | Monthly Cost | Maintenance | Best For | |------------|-------------|-------------|----------| | **App Platform** | $27 | 0 hours | Small teams, no DevOps | | **Droplets** | $28 | 2-3 hours | Full control, custom setup | See: [App Platform vs Droplets Comparison](deployment/droplet-vs-appplatform.md) ## Core Workflows ### Workflow 1: BIR Compliance Dashboard ``` User asks: "Create BIR filing status dashboard" Steps: 1. Deploy Superset to DO App Platform (if not done) 2. Initialize via console 3. Connect to Supabase database 4. Create dataset: bir_filing_summary 5. Build charts: - 1601-C monthly trends (timeseries) - 2550Q status by agency (pivot table) - ATP expiry calendar (table) - Tax payable big number (KPI) 6. Apply filters: agency, period, status 7. Set refresh schedule: daily 6am Result: Live BIR compliance dashboard ``` See [examples/bir-dashboard.md](examples/bir-dashboard.md). ### Workflow 2: Create Optimized Dataset ``` User asks: "Create dataset for expense analytics" Steps: 1. Write SQL query joining Odoo tables 2. Add calculated columns 3. Set proper column types 4. Configure cache timeout 5. Add metrics (SUM, AVG, COUNT) 6. Define dimensions (groupby fields) 7. Test query performance Result: Performant dataset for charts ``` See [examples/create-dataset.md](examples/create-dataset.md). ### Workflow 3: Chart Best Practices ``` User asks: "Show AP aging in Superset" Steps: 1. Select chart type: Pivot Table v2 2. Configure: - Rows: Vendor name - Columns: Aging buckets - Metrics: Sum(amount) - Color scale: Red (overdue) to Green (current) 3. Add conditional formatting 4. Set refresh interval Result: Interactive AP aging analysis ``` See [examples/chart-selection.md](examples/chart-selection.md). ## Pre-Built Templates ### Template 1: BIR Monthly Filing ```json { "dashboard_title": "BIR Compliance Tracker", "charts": [ { "title": "1601-C Withholding Summary", "viz_type": "pivot_table_v2", "dataset": "bir_withholding_monthly" }, { "title": "2550Q VAT Status", "viz_type": "big_number_total", "dataset": "bir_vat_quarterly" }, { "title": "Filing Deadline Calendar", "viz_type": "echarts_timeseries", "dataset": "bir_filing_schedule" } ], "filters": ["agency", "period", "status"] } ``` ### Template 2: Finance SSC Executive ```json { "dashboard_title": "Finance SSC Overview", "charts": [ { "title": "Cash Position", "viz_type": "big_number_total", "comparison": "month_over_month" }, { "title": "AP Aging", "viz_type": "echarts_bar" }, { "title": "Month-End Progress", "viz_type": "echarts_gauge" } ] } ``` ### Template 3: Expense Analytics ```json { "dashboard_title": "Travel & Expense Analytics", "charts": [ { "title": "Expense by Category", "viz_type": "echarts_pie" }, { "title": "Monthly Trend", "viz_type": "echarts_timeseries" }, { "title": "Top Spenders", "viz_type": "table" } ] } ``` ## Dataset SQL Patterns ### Pattern 1: Supabase + Odoo Integration ```sql -- Materialized view for Superset CREATE MATERIALIZED VIEW superset_odoo_financials AS SELECT a.code as agency_code, acc.code as account_code, acc.name as account_name, SUM(l.debit) as total_debit, SUM(l.credit) as total_credit, m.date as transaction_date FROM odoo_account_move_line l JOIN odoo_account_account acc ON l.account_id = acc.id JOIN odoo_account_move m ON l.move_id = m.id JOIN agencies a ON m.agency_id = a.id WHERE m.state = 'posted' GROUP BY 1,2,3,6; -- Refresh schedule REFRESH MATERIALIZED VIEW CONCURRENTLY superset_odoo_financials; ``` ### Pattern 2: BIR Compliance View ```sql CREATE VIEW superset_bir_summary AS SELECT agency_code, form_type, period, tax_amount, status, filing_deadline, CASE WHEN status = 'Filed' THEN 'On Time' WHEN filing_deadline < CURRENT_DATE THEN 'Late' ELSE 'Pending' END as compliance_status FROM bir_filings WHERE period >= '2025-01-01'; ``` ## Chart Type Selection Guide | Use Case | Recommended Chart | Why | |----------|------------------|-----| | KPI/Metric | Big Number Total | Focus attention | | Trends over time | ECharts Timeseries | Interactive, zoomable | | Comparisons | ECharts Bar | Clear comparison | | Proportions | ECharts Pie | Part-to-whole | | Detailed data | Pivot Table v2 | Sortable, filterable | | Distributions | ECharts Histogram | Show patterns | | Correlations | ECharts Scatter | Relationships | | Geographic | Deck.gl GeoJSON | Map visualization | See [reference/chart-selection-guide.md](reference/chart-selection-guide.md). ## Integration with Your Stack ### Supabase Connection ```python # Create database connection in Superset superset.create_database({ 'database_name': 'Supabase Production', 'sqlalchemy_uri': f'postgresql://postgres:{password}@db.spdtwktxdalcfigzeqrz.supabase.co:5432/postgres', 'extra': { 'allows_virtual_table_explore': True, 'engine_params': { 'connect_args': {'sslmode': 'require'} } } }) ``` ### Automated Refresh ```python # Schedule dashboard refresh superset.schedule_refresh({