Self-hosted Grafana + Prometheus monitoring for the Raspberry Pi, covering:

• PortfolioPi folder — dashboards mirroring/extending its Streamlit dashboard, reading from its Supabase Postgres via a dedicated read-only grafana_reader role.
• OverwatcherPI folder — same pattern for its Supabase project.
• Pi Infrastructure folder — deep Pi system health (CPU/RAM/disk/network/PSI pressure/throttling) via node_exporter, Docker container health via cAdvisor.

Runs alongside (not replacing) both apps' existing Streamlit dashboards.

flowchart TD
    subgraph HOST["🖥️ Raspberry Pi Host"]
        VCGEN["vcgencmd get_throttled"]
        CRON["⏱️ cron (1m)\npi-throttle-exporter.sh"]
        TEXTFILE["📄 node-exporter-textfile/\npi_throttle.prom"]
        GATEWAY["🔒 pi-gateway Caddy\n[network_mode: host]\nhttp://grafana.local → :3000\n(routing only, no Basic Auth)"]
        AVAHI["avahi-alias-grafana.service\nmDNS: grafana.local"]
    end

    subgraph DOCKER["🐳 Docker Compose Stack  [monitoring-net bridge]"]
        subgraph GRAF["monitoring-grafana  [127.0.0.1:3000]"]
            GF["📊 Grafana OSS 11.4\n+ marcusolsson-treemap-panel\nNative login only\n16 dashboards / 3 folders\nplaylists · starred dashboards"]
        end

        subgraph PROM["monitoring-prometheus"]
            PR["📈 Prometheus\nscrapes: self · node-exporter · cadvisor\n30d retention"]
        end

        subgraph NODEEXP["monitoring-node-exporter  [pid: host]"]
            NE["Host metrics\nCPU/mem/disk/net/PSI pressure\n+ textfile collector"]
        end

        subgraph CADV["monitoring-cadvisor  [privileged]"]
            CA["Per-container CPU/mem/net/OOM\ncontainerd-snapshotter aware\n(cadvisor:latest)"]
        end
    end

    subgraph EXTERNAL["☁️ External"]
        PPI_DB[("🗄️ PortfolioPi Supabase\ngrafana_reader role\nBYPASSRLS")]
        OW_DB[("🗄️ OverwatcherPI Supabase\ngrafana_reader role\nBYPASSRLS")]
        LAN["Any device on the LAN"]
    end

    %% Throttle exporter chain
    CRON -->|"every 60s"| VCGEN
    VCGEN --> CRON
    CRON -->|"decoded bitmask"| TEXTFILE
    TEXTFILE -->|"--collector.textfile.directory"| NE

    %% Scrape chain
    NE -->|":9100/metrics"| PR
    CA -->|":8080/metrics"| PR
    PR -->|"promQL"| GF

    %% Postgres datasources
    GF -->|"read-only SQL"| PPI_DB
    GF -->|"read-only SQL"| OW_DB

    %% Access chain
    LAN -->|"http://grafana.local"| AVAHI
    AVAHI -->|"resolves to 192.168.1.35"| LAN
    LAN --> GATEWAY
    GATEWAY -->|"reverse_proxy 127.0.0.1:3000"| GF

    classDef container fill:#1e3a5f,stroke:#4a9eff,color:#fff
    classDef db fill:#2d4a1e,stroke:#6abf4b,color:#fff
    classDef host fill:#3a1f1f,stroke:#cc4444,color:#fff
    classDef external fill:#2d2040,stroke:#9b6eff,color:#fff
    class GRAF,PROM,NODEEXP,CADV container
    class PPI_DB,OW_DB db
    class HOST host
    class EXTERNAL external

Grafana itself is bound to 127.0.0.1:3000 and is never reachable directly from the LAN — only pi-gateway's Caddy (host networking) can reach it, and only after resolving grafana.local via the dedicated avahi-alias-grafana.service mDNS publisher. Prometheus, node-exporter, and cAdvisor are never published to the host at all — reachable only via Docker service-name DNS on monitoring-net.

• marcusolsson-treemap-panel — Grafana-Labs-maintained, free, self-hosted-compatible (signed, signatureType: grafana). Installed via GF_INSTALL_PLUGINS in docker-compose.yml. Used for PortfolioPi's stock and sector allocation panels (a 26-category pie chart was hard to read; treemap handles that shape natively).

Reachable at http://grafana.local via the existing pi-gateway Caddy (added as a routing-only entry — no Basic Auth layer; Grafana's own native login is the sole credential check). Grafana itself is bound to 127.0.0.1:3000 on the Pi and is never reachable directly from the LAN.

• Dashboard JSON files under grafana/provisioning/dashboards-json/<folder>/ are auto-reloaded within 30s of being added/edited — no restart needed.
• grafana/provisioning/datasources/datasources.yml and grafana/provisioning/dashboards/dashboards.yml are only read at startup — changing either requires docker compose restart grafana.
• prometheus/prometheus.yml requires docker compose restart prometheus.

Dashboards are authored live in the Grafana UI (folders are provisioned read-only via allowUiUpdates: false, so UI edits revert on the next 30s poll unless exported). Workflow: edit in the UI → Dashboard settings → JSON Model → copy → paste into grafana/provisioning/dashboards-json/<folder>/NN-name.json (strip the numeric id field, keep uid stable) → git commit.

Infrastructure stood up 2026-07-11, dashboards authored and fully verified 2026-07-12:

• All 4 containers (grafana, prometheus, node-exporter, cadvisor) up and healthy.
• All 3 Grafana datasources (PortfolioPi Postgres, OverwatcherPI Postgres, Prometheus) provisioned and passing their health check.
• Prometheus scraping node-exporter and cadvisor successfully (both up).
• grafana.local resolvable from any device on the LAN via a dedicated avahi-alias-grafana.service (mirrors the existing overwatchpi.local/portfoliopi.local aliases). Security verified from a separate LAN device: 192.168.1.35:3000 direct connection refused; http://grafana.local (via pi-gateway Caddy) resolves correctly to Grafana's login.
• 16 dashboards live across 3 folders, all hand-authored as JSON (not built via the UI) and verified by actually executing every panel's query against live Postgres/Prometheus data — not just checking the JSON loads:
  • Pi Infrastructure (2): System Health (CPU/memory/disk/network/temp/uptime via node-exporter), Docker Container Health (per-container CPU/memory/network/restarts/uptime via cAdvisor, $container variable).
  • PortfolioPi (6): Portfolio Snapshot & Allocation, Watchlist & Signals, Historical Charts ($stock variable, native candlestick panel), System & Session Status, Action Plan, Portfolio Analyser.
  • OverwatcherPI (8): Live Overview, Device History, Device Detail ($mac variable), Security Posture, Trends, Threat Map (Lite), Timeline, System Alerts ($category variable).
• Deferred/approximated panels (per the original scope decision) are documented inline via each panel's Grafana description tooltip, not silently omitted.

Bugs found and fixed during this pass:

• Original compose file bind-mounted dashboards-json as a second volume inside the already read-only provisioning mount — Docker can't create a mountpoint there. Fixed by nesting dashboards-json/ under provisioning/ so one mount covers both.
• cAdvisor v0.49.1 couldn't resolve any container name/image/labels at all — this host runs Docker's containerd-snapshotter storage mode, and v0.49.1 only understands the classic overlay2 graphdriver layout. Bumped to cadvisor:latest (v0.55.1) and pointed the extra rootfs mount at the real live path (docker-data/rootfs/overlayfs, not docker-data/image/overlayfs/layerdb).
• Both Supabase projects have Row-Level Security enabled on every table. grafana_reader's GRANT SELECT succeeded but RLS silently returned zero rows for everything — no error, just empty dashboards. Fixed with ALTER ROLE grafana_reader BYPASSRLS on both projects (safe: the role has no write grants at all). See sql/create_grafana_reader_roles.sql.
• One panel (OverwatcherPI Security Posture → "Events by Severity") nested $__timeFilter() inside a to_timestamp() call, which Grafana's macro parser mis-parenthesized. Fixed by using the manual to_timestamp(col) BETWEEN $__timeFrom() AND $__timeTo() pattern used everywhere else in these dashboards (required throughout OverwatcherPI since its timestamp columns are epoch double precision, not native timestamptz).

See sql/create_grafana_reader_roles.sql for the read-only role DDL (including the required RLS bypass) applied to both Supabase projects.

scripts/pi-throttle-exporter.sh decodes vcgencmd get_throttled's bitmask into a Prometheus textfile metric (pi_throttle_status{condition=...,when="now"|"occurred"}) every 60s via a host cron job. This is Raspberry-Pi-specific under-voltage/frequency-cap/thermal-limit signal that node_exporter's generic node_cooling_device_* collector does not cover. Install with scripts/install-throttle-cron.sh (run once, directly on the Pi — vcgencmd needs host access, so this does not run in a container). node-exporter is configured with --collector.textfile.directory=/textfile-collector, bind-mounted from ./node-exporter-textfile/ on the host.

Second pass focused on filling gaps found by cross-referencing every dashboard's queries against the full Postgres schemas and node_exporter's metric catalog, plus pulling in one new panel plugin. Every new/changed panel was verified the same way as v1: executed directly against live data via /api/ds/query / /api/v1/query, not just JSON-validated.

Pi Infrastructure — System Health grew from 13 to 40 panels. Added: PSI pressure (CPU/memory/IO stall time), CPU depth (active governor, frequency min/max bounds, context switches, interrupts, process run/block queue), memory depth (OOM kill counter, major page faults), disk depth (I/O utilization %, open file descriptors), network depth (softnet drops, link-flap counter, conntrack table usage), clock drift (NTP offset), entropy pool, and the new Pi throttle-status panels. All of this was data node-exporter was already scraping every 30s but nothing had ever plotted — zero new exporters needed except the throttle script above.

Docker Container Health: added per-container OOM-kill visibility (container_oom_events_total). A per-container disk-usage panel was also attempted (container_fs_usage_bytes) but confirmed via a live query to only report root-cgroup-level usage under this host's containerd-snapshotter setup — no per-container label is ever populated for it — so it was left out rather than shipped showing misleading data.

PortfolioPi: "Current Allocation by Stock" upgraded from piechart to the new treemap plugin. New "Current Allocation by Sector" treemap (stock_metadata.sector — unused table, currently empty so it shows one "Unknown" bucket today, ready to break down for real the moment that table is populated). New Backfill Requests and Ticker Mapping Health tables (both previously-unused tables). Portfolio Value Over Time now plots total_pnl directly instead of leaving that column unused. Portfolio Analyser gained a per-stock trend-strength table (stage_history.sma_150/slope).

OverwatcherPI: Security Posture gained a Cooldown Detail row showing which metric/vendor/IP is on cooldown (previously only ever counted). Live Overview's Recent Jobs table now shows target, requester, duration, and result summary (4 more of the jobs table's columns). Device History's Bluetooth table gained RSSI min/max/avg computed from the previously-unused rssi_history JSON column — a native Table sparkline was considered but ruled out after confirming live that Postgres arrays come back as plain strings through Grafana's datasource, not a true array field. New reference-data panels for oui_mappings (shown as a loaded-count, not a ~40k-row dump), deferred_scans, and monitored_hosts.

Not pursued, on purpose: a real geo-enriched Threat Map. Grafana's Geomap panel is core (no plugin needed), but dns_queries.src_ip is the querying device's LAN IP — not geolocatable. A genuine version needs to resolve query_name to an external IP and geo-enrich that, which is new app-level data collection (a GeoIP lookup step), not a Grafana change. Flagged as a distinct future project rather than built against the wrong IP.