feat: Grafana-Panel Planung nach Kategorie inkl. Kreditraten (A11-T3)

Neue Tabelle planning_monthly (Monatssummen je Kategorie aus wiederkehrenden
Posten, Einmalposten und allen Kreditraten), Service planning_chart mit
refresh_planning_chart (Advisory-Lock, komplette Neuschreibung), GUI-Hooks
in index/planung_page ueber _safe_refresh, neues Barchart-Panel im
Grafana-Dashboard (Panel 5).

Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>

finance/alembic/versions/d5e8b3c7a410_planning_monthly.py

@@ -0,0 +1,30 @@
+"""planning_monthly (Grafana-Panel Planung nach Kategorie)
+
+Revision ID: d5e8b3c7a410
+Revises: c4d7e2a91b53
+Create Date: 2026-09-28
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'd5e8b3c7a410'
+down_revision: Union[str, Sequence[str], None] = 'c4d7e2a91b53'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'planning_monthly',
+        sa.Column('month', sa.Date(), primary_key=True),
+        sa.Column('category', sa.String(length=100), primary_key=True),
+        sa.Column('total', sa.Numeric(precision=12, scale=2), nullable=False),
+    )
+
+
+def downgrade() -> None:
+    op.drop_table('planning_monthly')

finance/app/models/tables.py

@@ -156,3 +156,14 @@ class ProjectionResult(Base):
     low_point_balance: Mapped[Decimal] = mapped_column(MONEY)
     below_zero_date: Mapped[date | None] = mapped_column(Date, nullable=True)
     below_threshold_date: Mapped[date | None] = mapped_column(Date, nullable=True)
+
+
+class PlanningMonthly(Base):
+    """Abgeleitete Daten für das Grafana-Panel „Planung nach Kategorie"
+    (Ausbaustufe 11): Monatssummen je Kategorie aus der Planung. Wird von
+    services/planning_chart.refresh_planning_chart komplett neu geschrieben;
+    jederzeit reproduzierbar, kein Backup-Sonderfall."""
+    __tablename__ = "planning_monthly"
+    month: Mapped[date] = mapped_column(Date, primary_key=True)
+    category: Mapped[str] = mapped_column(String(100), primary_key=True)
+    total: Mapped[Decimal] = mapped_column(MONEY)

finance/app/routers/gui.py

@@ -27,6 +27,7 @@ from app.routers.planning import recurring_suggestions as _recurring_suggestions
 from app.routers.scenarios import list_scenarios as _list_scenarios
 from app.routers.transactions import count_transactions, list_transactions
 from app.services.balances import account_balance, total_balance
+from app.services.planning_chart import refresh_planning_chart
 from app.services.projection_service import refresh_stale_projections
 from app.version import get_version
 
@@ -146,6 +147,7 @@ def _month_starts_desc(session: Session, today: date) -> list[date]:
 @router.get("/", dependencies=[Depends(gui_session)])
 def index(request: Request, session: Session = Depends(get_session)):
     _safe_refresh(session, refresh_stale_projections, date.today())
+    _safe_refresh(session, refresh_planning_chart, date.today())
     accounts = session.execute(select(Account)).scalars().all()
     balances = [(a, account_balance(session, a)) for a in accounts]
     return templates.TemplateResponse(request, "index.html", {
@@ -323,6 +325,7 @@ def hilfe_page(request: Request):
 
 @router.get("/planung", dependencies=[Depends(gui_session)])
 def planung_page(request: Request, session: Session = Depends(get_session)):
+    _safe_refresh(session, refresh_planning_chart, date.today())
     categories = session.execute(select(Category)).scalars().all()
     recurring = _list_recurring(session=session)
     planned = _list_planned(session=session)

finance/app/services/planning_chart.py

@@ -0,0 +1,66 @@
+"""Planungs-Diagramm (Grafana-Panel „Planung nach Kategorie", Ausbaustufe 11).
+
+Monatssummen je Kategorie aus der reinen Planung (ohne Szenario-Modifikatoren):
+wiederkehrende Posten, Einmalposten und die Raten ALLER Kredite (Kategorie
+„Kreditraten", Auszahlung zählt nicht). Einnahmen und Ausgaben mit Vorzeichen,
+Semantik wie v_monthly_by_category. Kreditraten brauchen die Annuitätenformel
+(engine/loans.py) - daher Python + Tabelle statt SQL-View.
+"""
+from collections import defaultdict
+from datetime import date, timedelta
+from decimal import Decimal
+
+from sqlalchemy import delete, select
+from sqlalchemy.orm import Session
+
+from app.config import get_settings
+from app.db import advisory_xact_lock
+from app.engine.loans import loan_schedule
+from app.engine.recurrence import occurrences
+from app.models.tables import (Category, Loan, PlannedItem, PlanningMonthly,
+                               RecurringItem)
+
+UNKATEGORISIERT = "unkategorisiert"
+KREDITRATEN = "Kreditraten"
+PLANNING_CHART_LOCK = 110_002
+
+
+def planning_monthly_totals(session: Session, today: date,
+                            horizon_days: int) -> dict[tuple[date, str], Decimal]:
+    """Fenster: 1. des laufenden Monats bis today + horizon_days. Schlüssel
+    (Monatserster, Kategoriename); Summen 0 entfallen."""
+    window_start = today.replace(day=1)
+    window_end = today + timedelta(days=horizon_days)
+    names = {c.id: c.name for c in session.execute(select(Category)).scalars()}
+    totals: dict[tuple[date, str], Decimal] = defaultdict(lambda: Decimal("0"))
+
+    def category(category_id: int | None) -> str:
+        return names.get(category_id, UNKATEGORISIERT) if category_id else UNKATEGORISIERT
+
+    for r in session.execute(select(RecurringItem)).scalars():
+        for d in occurrences(r.rhythm, r.due_day, window_start, window_end,
+                             r.start_date, r.end_date):
+            totals[(d.replace(day=1), category(r.category_id))] += Decimal(r.amount)
+    for p in session.execute(select(PlannedItem)).scalars():
+        if window_start <= p.due <= window_end:
+            totals[(p.due.replace(day=1), category(p.category_id))] += Decimal(p.amount)
+    for loan in session.execute(select(Loan)).scalars():
+        for inst in loan_schedule(Decimal(loan.principal), Decimal(loan.annual_rate_pct),
+                                  loan.term_months, loan.payout_date,
+                                  loan.repayment_type):
+            if window_start <= inst.due <= window_end:
+                # payment ist bereits negativ (Abfluss), wie in build_cashflows.
+                totals[(inst.due.replace(day=1), KREDITRATEN)] += inst.payment
+    return {k: v for k, v in totals.items() if v != 0}
+
+
+def refresh_planning_chart(session: Session, today: date) -> int:
+    """Schreibt planning_monthly in EINER Transaktion komplett neu (Lock
+    gegen parallele Seitenaufrufe). Rückgabe: Anzahl geschriebener Zeilen."""
+    advisory_xact_lock(session, PLANNING_CHART_LOCK)
+    totals = planning_monthly_totals(session, today, get_settings().horizon_days)
+    session.execute(delete(PlanningMonthly))
+    session.add_all(PlanningMonthly(month=m, category=c, total=v)
+                    for (m, c), v in sorted(totals.items()))
+    session.commit()
+    return len(totals)

finance/grafana/dashboards/finanzen.json

@@ -168,6 +168,28 @@
           "rawQuery": true
         }
       ]
+    },
+    {
+      "id": 5,
+      "type": "barchart",
+      "title": "Planung nach Kategorie",
+      "datasource": { "type": "postgres", "uid": "FinanzDB" },
+      "gridPos": { "h": 8, "w": 12, "x": 0, "y": 16 },
+      "fieldConfig": { "defaults": { "unit": "currencyEUR" }, "overrides": [] },
+      "options": {
+        "legend": { "displayMode": "list", "placement": "bottom" },
+        "stacking": "normal",
+        "xTickLabelRotation": 0
+      },
+      "targets": [
+        {
+          "refId": "A",
+          "datasource": { "type": "postgres", "uid": "FinanzDB" },
+          "format": "time_series",
+          "rawSql": "SELECT month AS time, category AS metric, total AS value FROM planning_monthly ORDER BY month",
+          "rawQuery": true
+        }
+      ]
     }
   ]
 }

finance/tests/test_grafana_dashboard.py

@@ -49,3 +49,23 @@ def test_time_window_covers_projection_horizon():
         f"Projektionshorizont von {horizon} Tagen nicht ab -> Projektionskurve "
         f"wird abgeschnitten"
     )
+
+
+def test_panel_planung_nach_kategorie():
+    panels = {p["title"]: p for p in _load()["panels"]}
+    p = panels["Planung nach Kategorie"]
+    assert p["type"] == "barchart"
+    assert "planning_monthly" in p["targets"][0]["rawSql"]
+    assert p["options"]["stacking"] == "normal"
+
+
+def test_panels_ueberlappen_nicht_und_ids_eindeutig():
+    panels = _load()["panels"]
+    assert len({p["id"] for p in panels}) == len(panels)
+    cells = set()
+    for p in panels:
+        g = p["gridPos"]
+        for x in range(g["x"], g["x"] + g["w"]):
+            for y in range(g["y"], g["y"] + g["h"]):
+                assert (x, y) not in cells, p["title"]
+                cells.add((x, y))

finance/tests/test_gui.py

@@ -565,3 +565,22 @@ def test_v_projection_zeigt_ist_serie_und_keine_alten_punkte():
     assert "p.day > CURRENT_DATE" in body
     names = list(VIEWS)
     assert names.index("v_balance_total") < names.index("v_projection")
+
+
+def test_planung_seite_befuellt_planungs_diagramm(client, db):
+    from app.models.tables import PlanningMonthly
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    db.add(PlannedItem(name="Kauf", amount=Decimal("-10.00"),
+                       due=date.today() + timedelta(days=3)))
+    db.commit()
+    assert client.get("/planung").status_code == 200
+    assert db.query(PlanningMonthly).count() == 1
+
+
+def test_planungs_refresh_fehler_bricht_seiten_nicht(client, monkeypatch):
+    def kaputt(session, today):
+        raise RuntimeError("absichtlich kaputt")
+    monkeypatch.setattr("app.routers.gui.refresh_planning_chart", kaputt)
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    for path in ("/", "/planung"):
+        assert client.get(path).status_code == 200, path

finance/tests/test_planning_chart.py

@@ -0,0 +1,91 @@
+from datetime import date
+from decimal import Decimal
+
+from sqlalchemy import func, select
+
+from app.models.tables import (Category, Loan, PlannedItem, PlanningMonthly,
+                               RecurringItem)
+from app.services.planning_chart import (KREDITRATEN, UNKATEGORISIERT,
+                                         planning_monthly_totals,
+                                         refresh_planning_chart)
+
+TODAY = date(2026, 9, 28)
+M = lambda y, m: date(y, m, 1)  # noqa: E731
+
+
+def _setup(db):
+    wohnen = Category(name="Wohnen")
+    vers = Category(name="Versicherung")
+    eink = Category(name="Einkommen")
+    db.add_all([wohnen, vers, eink])
+    db.flush()
+    db.add_all([
+        # monatlich, ohne Start -> 15.09., 15.10., 15.11., 15.12.
+        RecurringItem(name="Miete", amount=Decimal("-50.00"), rhythm="monthly",
+                      due_day=15, category_id=wohnen.id),
+        # quartalsweise, Phase ab 01.08. -> nur 01.11. im Fenster
+        RecurringItem(name="Police", amount=Decimal("-300.00"), rhythm="quarterly",
+                      due_day=1, start_date=date(2026, 8, 1), category_id=vers.id),
+        # jährlich ohne Kategorie, Phase Dezember -> 10.12.2026
+        RecurringItem(name="Beitrag", amount=Decimal("-120.00"), rhythm="yearly",
+                      due_day=10, start_date=date(2025, 12, 10)),
+        # endet 31.10. -> nur 01.09. und 01.10.
+        RecurringItem(name="Gehalt", amount=Decimal("1000.00"), rhythm="monthly",
+                      due_day=1, end_date=date(2026, 10, 31), category_id=eink.id),
+        # Einmalposten: innerhalb (Okt), vor dem Fenster, nach dem Fenster
+        PlannedItem(name="Reparatur", amount=Decimal("-200.00"),
+                    due=date(2026, 10, 5), category_id=wohnen.id),
+        PlannedItem(name="Vorher", amount=Decimal("-999.00"), due=date(2026, 8, 31)),
+        PlannedItem(name="Nachher", amount=Decimal("-999.00"), due=date(2027, 2, 1)),
+        # Kredit 1.200 zu 0 % auf 12 Monate, Auszahlung 10.09. -> Raten je -100
+        # am 10.10., 10.11., 10.12. (10.01.2027 liegt hinter dem Fenster).
+        Loan(name="Auto", principal=Decimal("1200.00"), annual_rate_pct=Decimal("0"),
+             term_months=12, payout_date=date(2026, 9, 10), repayment_type="annuity"),
+    ])
+    db.commit()
+
+
+def test_planung_nach_kategorie_handrechnung(db):
+    _setup(db)
+    assert planning_monthly_totals(db, TODAY, 100) == {
+        (M(2026, 9), "Wohnen"): Decimal("-50.00"),
+        (M(2026, 10), "Wohnen"): Decimal("-250.00"),
+        (M(2026, 11), "Wohnen"): Decimal("-50.00"),
+        (M(2026, 12), "Wohnen"): Decimal("-50.00"),
+        (M(2026, 11), "Versicherung"): Decimal("-300.00"),
+        (M(2026, 12), UNKATEGORISIERT): Decimal("-120.00"),
+        (M(2026, 9), "Einkommen"): Decimal("1000.00"),
+        (M(2026, 10), "Einkommen"): Decimal("1000.00"),
+        (M(2026, 10), KREDITRATEN): Decimal("-100.00"),
+        (M(2026, 11), KREDITRATEN): Decimal("-100.00"),
+        (M(2026, 12), KREDITRATEN): Decimal("-100.00"),
+    }
+
+
+def test_kredit_auszahlung_zaehlt_nicht(db):
+    _setup(db)
+    totals = planning_monthly_totals(db, TODAY, 100)
+    assert all(v < 0 for (m, c), v in totals.items() if c == KREDITRATEN)
+    assert (M(2026, 9), KREDITRATEN) not in totals
+
+
+def test_nullsummen_entfallen(db):
+    c = Category(name="Durchlauf")
+    db.add(c)
+    db.flush()
+    db.add_all([
+        PlannedItem(name="rein", amount=Decimal("10.00"), due=date(2026, 10, 1), category_id=c.id),
+        PlannedItem(name="raus", amount=Decimal("-10.00"), due=date(2026, 10, 2), category_id=c.id),
+    ])
+    db.commit()
+    assert planning_monthly_totals(db, TODAY, 100) == {}
+
+
+def test_refresh_schreibt_tabelle_idempotent(db):
+    _setup(db)
+    n1 = refresh_planning_chart(db, TODAY)
+    n2 = refresh_planning_chart(db, TODAY)
+    assert n1 == n2 > 0
+    assert db.execute(select(func.count()).select_from(PlanningMonthly)).scalar() == n2
+    row = db.get(PlanningMonthly, (M(2026, 11), "Versicherung"))
+    assert row.total == Decimal("-300.00")

finance/tests/test_views_pg.py

@@ -80,3 +80,14 @@ def test_v_projection_ist_serie_und_szenario_ab_heute(pg):
     assert all(r.day >= today for r in sc)
     assert [r.balance for r in sc[1:]] == [Decimal("5000.00")] * 5
     assert not any(r.scenario == "Ohne Punkte" for r in rows)
+
+
+def test_planning_chart_refresh_auf_postgres(pg):
+    from app.models.tables import PlannedItem, PlanningMonthly
+    from app.services.planning_chart import refresh_planning_chart
+    today = pg.execute(text("SELECT CURRENT_DATE")).scalar()
+    pg.add(PlannedItem(name="t", amount=Decimal("-5.00"), due=today + timedelta(days=2)))
+    pg.commit()
+    assert refresh_planning_chart(pg, today) == 1
+    assert refresh_planning_chart(pg, today) == 1
+    assert pg.query(PlanningMonthly).count() == 1