stacked area graphs / stacked line graphs

[81reap]

What are you building with SQLPage ?

I export my NixOS machine's logs + metrics to parquet using a custom python collector. I use SQLPage + DuckDB in my NixOS config to power dashboards that show me relevant logs + metrics.

What is your problem ? A description of the problem, not the solution you are proposing.

I would like to have the ability to create stacked area graphs or stacked line graphs.

What are you currently doing ? Since your solution is not implemented in SQLPage currently, what are you doing instead ?

Currently for component power draw I am just using a regular line graph, but it makes more sense to be able to see how each component adds up to total power draw.

---

Describe the solution you'd like

I want to be able to create stacked area graphs / stacked line graphs.

Describe alternatives you've considered

Not sure there is an alternative. For now I just use a regular line graph.

Additional context

example graphs :: https://datavizcatalogue.com/methods/stacked_area_graph.html

[81reap]

I had Claude whip up a patch I could apply. this is by no means properly bar raised to be PRed into the codebase but it seems to get close to the general idea. sharing to help maintainers :)

select 'chart' as component, 'area' as type, true as stacked,
  'Power' as title, 'watts' as ytitle, true as time, 400 as ymax,
  'blue' as color, 'teal' as color,
  350 as yline, '350 W budget' as yline_label;

with span as (select now() at time zone 'UTC' - interval 6 hour as since)
select ts as x, round(pkg_watts, 1) as y, 'CPU' as series
from cpu_power, span where ts > since
union all
select ts, gpu_power_w, 'GPU' || gpu_index
from gpu_samples, span where ts > since
order by series, x;

--- a/sqlpage/apexcharts.js
+++ b/sqlpage/apexcharts.js
@@ -136,6 +136,31 @@
 
     const chart_type = data.type || "line";
     const options = {
+      // Reference lines are annotations, not series: a series would be stacked
+      // in a stacked chart and filled in an area chart, so it could not mark a
+      // constant. They are drawn only inside the y-axis range, so a line above
+      // the data needs ymax set to keep it on screen.
+      annotations: {
+        yaxis: data.yline.map((y, i) => {
+          // Each entry is merged over ApexCharts' yAxisAnnotation defaults, so
+          // the label key is omitted rather than left undefined: undefined
+          // would replace the default label object instead of falling back to
+          // it.
+          const line = {
+            y: Number(y),
+            borderColor: colorNames.red,
+            strokeDashArray: 4,
+          };
+          const text = data.yline_label[i];
+          if (text)
+            line.label = {
+              text,
+              borderColor: colorNames.red,
+              style: { color: "#fff", background: colorNames.red },
+            };
+          return line;
+        }),
+      },
       chart: {
         type: chart_type,
         fontFamily: "inherit",
--- a/sqlpage/templates/chart.handlebars
+++ b/sqlpage/templates/chart.handlebars
@@ -37,6 +37,8 @@
     "stacked": {{stringify stacked}},
     "height": {{stringify (default height 250)}},
     "colors": {{stringify (to_array color)}},
+    "yline": {{stringify (to_array yline)}},
+    "yline_label": {{stringify (to_array yline_label)}},
     "points": [
     {{~#each_row~}}
         {{~#if (gt @row_index 0)}},{{/if~}}