fix: lueckenlose taegliche Saldo-Reihen fuer Grafana

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

finance/app/models/views.py

@@ -26,22 +26,83 @@ _ACCOUNT_OFFSET = """
     WHERE a.anchor_date IS NOT NULL AND a.anchor_balance IS NOT NULL
 """
 
+
+# Per-account earliest day the daily series needs to start at: the earlier of
+# the account's first confirmed booking and its anchor_date (an anchor can
+# predate the first booking, e.g. an anchor set before any import). Accounts
+# with neither an anchor nor any confirmed booking have no meaningful start
+# and are excluded (nothing to plot).
+_ACCOUNT_START = """
+    SELECT a.id AS account_id, a.name AS account,
+           LEAST(
+               COALESCE(a.anchor_date, ft.first_date),
+               COALESCE(ft.first_date, a.anchor_date)
+           ) AS start_date
+    FROM accounts a
+    LEFT JOIN (
+        SELECT account_id, MIN(booking_date) AS first_date
+        FROM transactions WHERE status = 'confirmed'
+        GROUP BY account_id
+    ) ft ON ft.account_id = a.id
+    WHERE a.anchor_date IS NOT NULL OR ft.first_date IS NOT NULL
+"""
+
 VIEWS: dict[str, str] = {
+    # Daily (gap-free) balance per account: one row per calendar day from the
+    # account's start date (see _ACCOUNT_START) through CURRENT_DATE. The
+    # cumulative SUM ... OVER (... ROWS UNBOUNDED PRECEDING) carries the
+    # previous day's balance forward on days without any confirmed booking,
+    # matching services.balances.account_balance's per-Tagesende semantics
+    # (base + cumulated amounts up to and including that day).
     "v_balance_history": f"""
-        SELECT t.booking_date AS day, a.name AS account,
+        WITH offsets AS ({_ACCOUNT_OFFSET}),
-               COALESCE(b.base, 0)
+        bounds AS ({_ACCOUNT_START}),
-                 + SUM(SUM(t.amount)) OVER (PARTITION BY a.id
+        days AS (
-                                            ORDER BY t.booking_date) AS balance
+            SELECT b.account_id, b.account, gs.day::date AS day
-        FROM transactions t JOIN accounts a ON a.id = t.account_id
+            FROM bounds b
-        LEFT JOIN ({_ACCOUNT_OFFSET}) b ON b.account_id = a.id
+            CROSS JOIN LATERAL generate_series(
-        WHERE t.status = 'confirmed'
+                b.start_date, CURRENT_DATE, interval '1 day') AS gs(day)
-        GROUP BY a.id, a.name, t.booking_date, b.base""",
+        ),
+        daily_tx AS (
+            SELECT account_id, booking_date AS day, SUM(amount) AS day_amount
+            FROM transactions WHERE status = 'confirmed'
+            GROUP BY account_id, booking_date
+        )
+        SELECT d.day, d.account,
+               COALESCE(o.base, 0)
+                 + SUM(COALESCE(dt.day_amount, 0)) OVER (
+                       PARTITION BY d.account_id ORDER BY d.day
+                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
+        FROM days d
+        LEFT JOIN daily_tx dt ON dt.account_id = d.account_id AND dt.day = d.day
+        LEFT JOIN offsets o ON o.account_id = d.account_id""",
+    # Daily (gap-free) total balance across all accounts: same carry-forward
+    # logic as v_balance_history, but summed globally (equivalent to summing
+    # each account's own base + cumulated amounts, since a cumulative sum of
+    # a union of per-day amounts equals the sum of per-account cumulative
+    # sums). The day series spans from the earliest account start date
+    # through CURRENT_DATE.
     "v_balance_total": f"""
-        SELECT booking_date AS day,
+        WITH offsets AS ({_ACCOUNT_OFFSET}),
-               (SELECT COALESCE(SUM(base), 0)
+        bounds AS ({_ACCOUNT_START}),
-                FROM ({_ACCOUNT_OFFSET}) s)
+        days AS (
-                 + SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
+            SELECT gs.day::date AS day
-        FROM transactions WHERE status = 'confirmed' GROUP BY booking_date""",
+            FROM (SELECT MIN(start_date) AS start_date FROM bounds) b
+            CROSS JOIN LATERAL generate_series(
+                b.start_date, CURRENT_DATE, interval '1 day') AS gs(day)
+        ),
+        daily_tx AS (
+            SELECT booking_date AS day, SUM(amount) AS day_amount
+            FROM transactions WHERE status = 'confirmed'
+            GROUP BY booking_date
+        )
+        SELECT d.day,
+               (SELECT COALESCE(SUM(base), 0) FROM offsets)
+                 + SUM(COALESCE(dt.day_amount, 0)) OVER (
+                       ORDER BY d.day
+                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
+        FROM days d
+        LEFT JOIN daily_tx dt ON dt.day = d.day""",
     "v_monthly_by_category": """
         SELECT date_trunc('month', t.booking_date) AS month,
                COALESCE(c.name, 'unkategorisiert') AS category,

finance/grafana/dashboards/finanzen.json

@@ -26,7 +26,9 @@
           "custom": {
             "drawStyle": "line",
             "lineWidth": 1,
-            "fillOpacity": 0
+            "fillOpacity": 0,
+            "lineInterpolation": "stepAfter",
+            "spanNulls": true
           },
           "unit": "currencyEUR"
         },
@@ -63,7 +65,9 @@
           "custom": {
             "drawStyle": "line",
             "lineWidth": 2,
-            "fillOpacity": 10
+            "fillOpacity": 10,
+            "lineInterpolation": "stepAfter",
+            "spanNulls": true
           },
           "unit": "currencyEUR"
         },