rdmsandClaude Opus 5.5
ffd7647c5f
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
1596 lines
71 KiB
Markdown
1596 lines
71 KiB
Markdown
# Ausbaustufe 11: Szenario-Löschen, Grafana-Korrekturen, Tabellensortierung — Implementation Plan
|
||
|
||
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
|
||
|
||
**Goal:** Szenarien per GUI löschbar; „Szenario-Vergleich" zeigt vor heute
|
||
nur den echten Ist-Saldo (Serie „System-Ist") und veraltete Szenarien werden
|
||
automatisch neu gerechnet; neues Grafana-Panel „Planung nach Kategorie";
|
||
alle Tabellen per Spaltenkopf sortierbar (Buchungen serverseitig).
|
||
|
||
**Architecture:** (1) Reiner Template-Zusatz + Namens-Reservierung in der
|
||
Szenario-API. (2) `v_projection` wird zur UNION aus Ist-Serie, Szenario-
|
||
Startpunkt (heute) und Projektionspunkten *nach* heute; GUI-Seitenaufrufe
|
||
rechnen veraltete Projektionen neu (`refresh_stale_projections`). (3) Neue
|
||
abgeleitete Tabelle `planning_monthly`, bei GUI-Aufrufen komplett neu
|
||
geschrieben aus RecurringItems, PlannedItems und Tilgungsplänen aller Kredite.
|
||
(4) Ein generisches `static/sort.js` (htmx.onLoad) für alle Tabellen;
|
||
`/buchungen` sortiert serverseitig über `sort`/`dir`.
|
||
|
||
**Tech Stack:** FastAPI, SQLAlchemy 2.0, Alembic, Jinja2, htmx, Postgres 17
|
||
(Prod) / SQLite (Tests), Grafana 12 (provisioniertes Dashboard), Vanilla-JS
|
||
(ES5-kompatibel, keine Build-Kette).
|
||
|
||
**Spec:** `docs/superpowers/specs/2026-09-28-ausbaustufe-11-design.md`
|
||
|
||
## Global Constraints
|
||
|
||
- **DATENSCHUTZ:** `tests/fixtures/*` = echte Kontodaten, nie committen/zitieren. Tests nur mit künstlichen Werten; keine Live-Beträge/-Namen in Commits, Ledger, Chat.
|
||
- **Geld = `decimal.Decimal`** (DB `Numeric(12,2)` = `MONEY`), keine float-Arithmetik für Beträge (Ausnahme: reine Sortier-Schlüssel im Browser-JS).
|
||
- **Deutsch** für GUI-Texte, Fehlermeldungen, Kommentare, Commit-Messages; Datumsformat TT.MM.JJJJ.
|
||
- **UX-Regel:** Bedienelemente sichtbar, Sperrzustände per `disabled`; Fehler inline bzw. über den bestehenden `htmx:responseError`-Alert.
|
||
- **Reservierter Serienname:** `System-Ist` (Konstante `RESERVED_SCENARIO_NAME` in `finance/app/models/views.py`); API lehnt ihn für Szenarien mit 422 ab (Vergleich `strip().casefold()`), Meldung „Der Name »System-Ist« ist für die Ist-Kurve reserviert".
|
||
- **Test-Suite grün:** `cd finance && .venv/bin/python -m pytest -q` — Basis 216 passed; jede Task erhöht die Zahl.
|
||
- **Fable-Testagent-Gate je Task vor dem Commit** (CLAUDE.md). Ledger-Eintrag in `.superpowers/sdd/progress.md` pro Task (Commit-Range + Fable-Befund).
|
||
- **Code domain-neutral** (nur `fb.example.de` in Code/Tests, falls überhaupt nötig).
|
||
- **Release am Ende als v0.11.0** (`finance/VERSION`), Redeploy via `./create_pod_finance.sh`.
|
||
- **Wegwerf-Postgres für Postgres-only-Prüfungen:** Image `docker.io/library/postgres:17.10` (wie Pod), DB-Name MUSS auf `_test` enden (Test-Guard), niemals gegen `finance_pod`-DB.
|
||
|
||
## Review Focus
|
||
|
||
1. **Leerwerte beim Sortieren** (`–`, leere Zellen, z.B. Spalte „Start"/„Ende"/„Kategorie") müssen in *beiden* Richtungen am Ende stehen, nicht bei „absteigend" oben — Test in Task 4 (`sort.test.js`: „Leerwerte immer am Ende").
|
||
2. **Offene Bearbeiten-Zeile / Tilgungsplan-Zeile beim Sortieren:** Zusatzzeilen (`*-edit-*`, Tilgungsplan-`<details>`-Zeile bei Krediten) müssen bei ihrer Hauptzeile bleiben, unabhängig davon ob gerade sichtbar — Tests in Task 4 (`groupRows` + DOM-Test + Template-Test auf `data-sort-follow`).
|
||
3. **Gesamt-Zeile in `/salden`** darf beim Sortieren nicht zwischen die Konten rutschen — Task 4 verschiebt sie nach `<tfoot>` (Template-Test + DOM-Test „tfoot bleibt unberührt").
|
||
4. **Manipulierte/ungültige URL-Parameter `sort`/`dir` auf `/buchungen`** dürfen weder 422 noch 500 liefern; Filter + Sortierung + Seitenwechsel müssen sich gegenseitig erhalten — Tests in Task 5.
|
||
5. **Szenario, das nie wieder neu gerechnet wird** (Nutzer öffnet nur Grafana direkt): dessen alte Punkte ≤ heute dürfen trotzdem nicht erscheinen — Postgres-Test in Task 2 (`test_views_pg.py`, veraltete Punkte vor heute unsichtbar).
|
||
|
||
---
|
||
|
||
### Task 1: Szenario löschen (GUI) + reservierter Name „System-Ist"
|
||
|
||
**Files:**
|
||
- Modify: `finance/app/models/views.py` (Konstante oben einfügen)
|
||
- Modify: `finance/app/routers/scenarios.py` (`create_scenario`, `patch_scenario`, neuer Helper)
|
||
- Modify: `finance/app/templates/szenarien.html` (Löschen-Button im Szenario-Kopf)
|
||
- Test: `finance/tests/test_crud_api.py`, `finance/tests/test_gui.py`
|
||
|
||
**Interfaces:**
|
||
- Produces: `app.models.views.RESERVED_SCENARIO_NAME: str = "System-Ist"` (Task 2 verwendet sie in der View-SQL).
|
||
|
||
- [x] **Step 1: Failing Tests schreiben**
|
||
|
||
An `finance/tests/test_crud_api.py` anhängen (dort existiert bereits `H = {"Authorization": "Bearer test-key"}`):
|
||
|
||
```python
|
||
import pytest
|
||
|
||
|
||
@pytest.mark.parametrize("name", ["System-Ist", "system-ist", " System-Ist ", "SYSTEM-IST"])
|
||
def test_szenario_name_system_ist_ist_reserviert_post(client, name):
|
||
r = client.post("/api/scenarios", json={"name": name}, headers=H)
|
||
assert r.status_code == 422
|
||
assert "reserviert" in r.json()["detail"]
|
||
|
||
|
||
def test_szenario_name_system_ist_ist_reserviert_patch(client):
|
||
sid = client.post("/api/scenarios", json={"name": "Normal"}, headers=H).json()["id"]
|
||
r = client.patch(f"/api/scenarios/{sid}", json={"name": "System-Ist"}, headers=H)
|
||
assert r.status_code == 422
|
||
assert "reserviert" in r.json()["detail"]
|
||
# Andere Felder ohne Namensänderung bleiben erlaubt.
|
||
r = client.patch(f"/api/scenarios/{sid}", json={"description": "x"}, headers=H)
|
||
assert r.status_code == 200
|
||
|
||
|
||
def test_szenario_normale_namen_weiter_erlaubt(client):
|
||
assert client.post("/api/scenarios", json={"name": "Ist-Zustand plus"},
|
||
headers=H).status_code == 201
|
||
```
|
||
|
||
Hinweis: falls `import pytest` in der Datei schon vorhanden ist, nicht doppelt importieren.
|
||
|
||
An `finance/tests/test_gui.py` anhängen:
|
||
|
||
```python
|
||
def test_szenarien_seite_hat_loeschen_button(client, db):
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
sc = Scenario(name="Wegwerf", description="", include_recurring=True,
|
||
include_planned=True)
|
||
db.add(sc)
|
||
db.commit()
|
||
r = client.get("/szenarien")
|
||
assert f'hx-delete="/api/scenarios/{sc.id}"' in r.text
|
||
assert "Szenario „Wegwerf“ mit allen Einträgen wirklich löschen?" in r.text
|
||
|
||
|
||
def test_szenario_loeschen_roundtrip_gui(client, db):
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
sc = Scenario(name="Wegwerf-Roundtrip", description="", include_recurring=True,
|
||
include_planned=True)
|
||
db.add(sc)
|
||
db.commit()
|
||
assert client.delete(f"/api/scenarios/{sc.id}", headers=H).status_code == 204
|
||
assert "Wegwerf-Roundtrip" not in client.get("/szenarien").text
|
||
```
|
||
|
||
- [x] **Step 2: Tests laufen lassen — müssen fehlschlagen**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q tests/test_crud_api.py tests/test_gui.py -k "reserviert or loeschen or normale_namen"`
|
||
Expected: FAIL (POST liefert 201 statt 422; Button fehlt). `test_szenario_loeschen_roundtrip_gui` und `test_szenario_normale_namen_weiter_erlaubt` dürfen schon grün sein.
|
||
|
||
- [x] **Step 3: Konstante in `views.py`**
|
||
|
||
In `finance/app/models/views.py` direkt nach den Imports:
|
||
|
||
```python
|
||
# Serienname der Ist-Kurve im Grafana-Panel „Szenario-Vergleich" (v_projection).
|
||
# Für Szenarien reserviert (routers/scenarios.py lehnt ihn ab), damit kein
|
||
# Szenario mit der Ist-Serie verschmelzen kann (Ausbaustufe 11).
|
||
RESERVED_SCENARIO_NAME = "System-Ist"
|
||
```
|
||
|
||
- [x] **Step 4: Namensprüfung in `routers/scenarios.py`**
|
||
|
||
Import ergänzen: `from app.models.views import RESERVED_SCENARIO_NAME`
|
||
|
||
Helper nach `_get_scenario` einfügen:
|
||
|
||
```python
|
||
def _check_reserved_name(name: str | None) -> None:
|
||
"""Der Serienname der Ist-Kurve (v_projection) ist für Szenarien tabu."""
|
||
if name is not None and (name.strip().casefold()
|
||
== RESERVED_SCENARIO_NAME.casefold()):
|
||
raise HTTPException(
|
||
422, f"Der Name »{RESERVED_SCENARIO_NAME}« ist für die Ist-Kurve reserviert")
|
||
```
|
||
|
||
In `create_scenario` als erste Zeile: `_check_reserved_name(data.name)`.
|
||
In `patch_scenario` direkt nach `fields = data.model_dump(exclude_unset=True)`:
|
||
`_check_reserved_name(fields.get("name"))`.
|
||
|
||
- [x] **Step 5: Löschen-Button in `szenarien.html`**
|
||
|
||
Im `<p id="sc-row-{{ sc.id }}">` direkt nach dem „Bearbeiten"-Button einfügen. Bewusst ein `<button>` mit htmx-Attributen, **kein** `<form>` — ein `<form>` innerhalb von `<p>` ist ungültiges HTML (der Parser schließt das `<p>` davor, der Button bliebe dann beim Bearbeiten sichtbar):
|
||
|
||
```html
|
||
<button type="button" hx-delete="/api/scenarios/{{ sc.id }}" hx-swap="none"
|
||
hx-confirm="Szenario „{{ sc.name }}“ mit allen Einträgen wirklich löschen?"
|
||
hx-on::after-request="if(event.detail.successful){window.location.reload()}">Löschen</button>
|
||
```
|
||
|
||
- [x] **Step 6: Tests grün + Gesamtsuite**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q`
|
||
Expected: alle grün, 216 + 8 = 224 passed.
|
||
|
||
- [x] **Step 7: Fable-Gate** — Faktencheck: Button im gerenderten HTML korrekt verschachtelt (nicht in `<form>`/außerhalb `<p>`), API-422 für alle Schreibvarianten, bestehende Löschlogik unverändert (Abräumung abhängiger Zeilen), Name-Konstante nur an einer Stelle definiert.
|
||
|
||
- [x] **Step 8: Commit**
|
||
|
||
```bash
|
||
git add finance/app/models/views.py finance/app/routers/scenarios.py \
|
||
finance/app/templates/szenarien.html finance/tests/test_crud_api.py finance/tests/test_gui.py
|
||
git commit -m "feat: Szenario per GUI löschen, Name System-Ist reserviert (A11-T1)"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 2: Szenario-Vergleich — Ist-Serie + automatisches Neurechnen
|
||
|
||
**Files:**
|
||
- Modify: `finance/app/db.py` (Helper `advisory_xact_lock`)
|
||
- Modify: `finance/app/models/views.py` (`v_projection`)
|
||
- Modify: `finance/app/services/projection_service.py` (`refresh_stale_projections`)
|
||
- Modify: `finance/app/routers/gui.py` (`_safe_refresh`, Aufrufe in `index`, `szenarien_page`)
|
||
- Create: `finance/tests/test_projection_refresh.py`
|
||
- Create: `finance/tests/test_views_pg.py` (nur mit `FB_TEST_PG_URL`, sonst skip)
|
||
- Test: `finance/tests/test_gui.py`
|
||
|
||
**Interfaces:**
|
||
- Consumes: `RESERVED_SCENARIO_NAME` (Task 1).
|
||
- Produces:
|
||
- `app.db.advisory_xact_lock(session: Session, key: int) -> None` (Task 3 nutzt es).
|
||
- `app.services.projection_service.refresh_stale_projections(session: Session, today: date) -> int`.
|
||
- `app.routers.gui._safe_refresh(session: Session, refresh: Callable[[Session, date], object], today: date) -> None` (Task 3 nutzt es).
|
||
|
||
- [x] **Step 1: Failing Service-Tests** — `finance/tests/test_projection_refresh.py`:
|
||
|
||
```python
|
||
from datetime import date, timedelta
|
||
|
||
from sqlalchemy import func, select
|
||
|
||
from app.config import get_settings
|
||
from app.models.tables import ProjectionPoint, ProjectionResult, Scenario
|
||
from app.services.projection_service import refresh_stale_projections, run_projection
|
||
|
||
TODAY = date(2026, 9, 28)
|
||
|
||
|
||
def _scenario(db, name):
|
||
sc = Scenario(name=name, description="", include_recurring=False,
|
||
include_planned=False)
|
||
db.add(sc)
|
||
db.commit()
|
||
return sc
|
||
|
||
|
||
def _first_day(db, sid):
|
||
return db.execute(select(func.min(ProjectionPoint.day))
|
||
.where(ProjectionPoint.scenario_id == sid)).scalar()
|
||
|
||
|
||
def _count(db, sid):
|
||
return db.execute(select(func.count()).select_from(ProjectionPoint)
|
||
.where(ProjectionPoint.scenario_id == sid)).scalar()
|
||
|
||
|
||
def test_veraltetes_szenario_wird_ab_heute_neu_gerechnet(db):
|
||
sc = _scenario(db, "Alt")
|
||
run_projection(db, sc, 10, TODAY - timedelta(days=5))
|
||
assert _first_day(db, sc.id) == TODAY - timedelta(days=4)
|
||
assert refresh_stale_projections(db, TODAY) == 1
|
||
assert _first_day(db, sc.id) == TODAY + timedelta(days=1)
|
||
assert _count(db, sc.id) == get_settings().horizon_days
|
||
|
||
|
||
def test_gestern_gerechnet_gilt_als_veraltet(db):
|
||
sc = _scenario(db, "Gestern")
|
||
run_projection(db, sc, 10, TODAY - timedelta(days=1))
|
||
assert refresh_stale_projections(db, TODAY) == 1
|
||
|
||
|
||
def test_heute_gerechnet_bleibt_unveraendert(db):
|
||
sc = _scenario(db, "Heute")
|
||
run_projection(db, sc, 10, TODAY)
|
||
before = db.get(ProjectionResult, sc.id).computed_at
|
||
assert refresh_stale_projections(db, TODAY) == 0
|
||
assert _count(db, sc.id) == 10
|
||
assert db.get(ProjectionResult, sc.id).computed_at == before
|
||
|
||
|
||
def test_nie_gerechnetes_szenario_bleibt_ohne_ergebnis(db):
|
||
sc = _scenario(db, "Nie")
|
||
assert refresh_stale_projections(db, TODAY) == 0
|
||
assert db.get(ProjectionResult, sc.id) is None
|
||
assert _count(db, sc.id) == 0
|
||
|
||
|
||
def test_refresh_ist_idempotent(db):
|
||
sc = _scenario(db, "Zweimal")
|
||
run_projection(db, sc, 10, TODAY - timedelta(days=3))
|
||
assert refresh_stale_projections(db, TODAY) == 1
|
||
assert refresh_stale_projections(db, TODAY) == 0
|
||
assert _count(db, sc.id) == get_settings().horizon_days
|
||
```
|
||
|
||
- [x] **Step 2: Failing GUI-Tests + View-Strukturtest** — an `finance/tests/test_gui.py` anhängen:
|
||
|
||
```python
|
||
def test_szenarien_seite_rechnet_veraltete_projektion_neu(client, db):
|
||
from sqlalchemy import func, select
|
||
from app.models.tables import ProjectionPoint
|
||
from app.services.projection_service import run_projection
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
sc = Scenario(name="Veraltet", description="", include_recurring=False,
|
||
include_planned=False)
|
||
db.add(sc)
|
||
db.commit()
|
||
run_projection(db, sc, 10, date.today() - timedelta(days=3))
|
||
assert client.get("/szenarien").status_code == 200
|
||
first = db.execute(select(func.min(ProjectionPoint.day))
|
||
.where(ProjectionPoint.scenario_id == sc.id)).scalar()
|
||
assert first == date.today() + timedelta(days=1)
|
||
|
||
|
||
def test_refresh_fehler_bricht_seiten_nicht(client, monkeypatch):
|
||
def kaputt(session, today):
|
||
raise RuntimeError("absichtlich kaputt")
|
||
monkeypatch.setattr("app.routers.gui.refresh_stale_projections", kaputt)
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
for path in ("/", "/szenarien"):
|
||
assert client.get(path).status_code == 200, path
|
||
|
||
|
||
def test_v_projection_zeigt_ist_serie_und_keine_alten_punkte():
|
||
from app.models.views import RESERVED_SCENARIO_NAME, VIEWS
|
||
body = VIEWS["v_projection"]
|
||
assert f"'{RESERVED_SCENARIO_NAME}'" in body
|
||
assert "p.day > CURRENT_DATE" in body
|
||
names = list(VIEWS)
|
||
assert names.index("v_balance_total") < names.index("v_projection")
|
||
```
|
||
|
||
- [x] **Step 3: Postgres-View-Test anlegen** — `finance/tests/test_views_pg.py` (läuft nur, wenn `FB_TEST_PG_URL` auf eine Wegwerf-DB mit Namen `*_test` zeigt; sonst skip):
|
||
|
||
```python
|
||
"""Postgres-only-Prüfung der Grafana-Views (Ausbaustufe 11).
|
||
|
||
Läuft nur mit FB_TEST_PG_URL (Wegwerf-Postgres, DB-Name muss auf '_test'
|
||
enden - Schutz gegen versehentliche Ausführung gegen die Produktiv-DB).
|
||
Beispiel:
|
||
podman run -d --rm --name a11-pg -e POSTGRES_PASSWORD=wegwerf \
|
||
-e POSTGRES_DB=fb_test -p 127.0.0.1:55432:5432 docker.io/library/postgres:17.10
|
||
FB_TEST_PG_URL=postgresql+psycopg://postgres:wegwerf@127.0.0.1:55432/fb_test \
|
||
.venv/bin/python -m pytest -q tests/test_views_pg.py
|
||
"""
|
||
import os
|
||
from datetime import timedelta
|
||
from decimal import Decimal
|
||
|
||
import pytest
|
||
from sqlalchemy import create_engine, text
|
||
from sqlalchemy.engine import make_url
|
||
from sqlalchemy.orm import Session
|
||
|
||
from app.db import Base
|
||
import app.models # noqa: F401
|
||
from app.models.tables import (Account, ProjectionPoint, ProjectionResult,
|
||
Scenario, Transaction)
|
||
from app.models.views import RESERVED_SCENARIO_NAME, VIEWS, create_views
|
||
|
||
PG_URL = os.environ.get("FB_TEST_PG_URL")
|
||
pytestmark = pytest.mark.skipif(not PG_URL, reason="FB_TEST_PG_URL nicht gesetzt")
|
||
|
||
|
||
def _reset(engine):
|
||
with engine.begin() as conn:
|
||
for name in VIEWS:
|
||
conn.execute(text(f"DROP VIEW IF EXISTS {name} CASCADE"))
|
||
Base.metadata.drop_all(engine)
|
||
|
||
|
||
@pytest.fixture
|
||
def pg():
|
||
assert make_url(PG_URL).database.endswith("_test"), "nur Wegwerf-DB *_test!"
|
||
engine = create_engine(PG_URL)
|
||
_reset(engine)
|
||
Base.metadata.create_all(engine)
|
||
create_views(engine)
|
||
with Session(engine) as session:
|
||
yield session
|
||
_reset(engine)
|
||
engine.dispose()
|
||
|
||
|
||
def test_v_projection_ist_serie_und_szenario_ab_heute(pg):
|
||
today = pg.execute(text("SELECT CURRENT_DATE")).scalar()
|
||
acc = Account(bank="t", iban="DE-PG-1", name="Testkonto", type="giro",
|
||
anchor_date=today - timedelta(days=10),
|
||
anchor_balance=Decimal("1000.00"))
|
||
pg.add(acc)
|
||
pg.flush()
|
||
pg.add(Transaction(account_id=acc.id, booking_date=today - timedelta(days=2),
|
||
amount=Decimal("-100.00"), purpose="t", counterparty="t",
|
||
status="confirmed", dedup_hash="pg-1"))
|
||
stale = Scenario(name="Veraltet", description="")
|
||
empty = Scenario(name="Ohne Punkte", description="")
|
||
pg.add_all([stale, empty])
|
||
pg.flush()
|
||
# Veraltete Momentaufnahme: Punkte von heute-5 bis heute+5.
|
||
for off in range(-5, 6):
|
||
pg.add(ProjectionPoint(scenario_id=stale.id, day=today + timedelta(days=off),
|
||
balance=Decimal("5000.00")))
|
||
pg.add(ProjectionResult(scenario_id=stale.id, computed_at=today,
|
||
low_point_date=today, low_point_balance=Decimal("0")))
|
||
pg.commit()
|
||
|
||
rows = pg.execute(text(
|
||
"SELECT scenario, day, balance FROM v_projection ORDER BY scenario, day")).all()
|
||
ist = [r for r in rows if r.scenario == RESERVED_SCENARIO_NAME]
|
||
sc = [r for r in rows if r.scenario == "Veraltet"]
|
||
assert max(r.day for r in ist) == today
|
||
assert [r.balance for r in ist if r.day == today] == [Decimal("900.00")]
|
||
# Szenario startet heute am Ist-Saldo, keine alten Punkte davor.
|
||
assert sc[0].day == today and sc[0].balance == Decimal("900.00")
|
||
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)
|
||
```
|
||
|
||
- [x] **Step 4: Tests laufen lassen — müssen fehlschlagen**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q tests/test_projection_refresh.py tests/test_gui.py tests/test_views_pg.py`
|
||
Expected: FAIL (ImportError `refresh_stale_projections`; View-Test: `System-Ist` fehlt); `test_views_pg.py` skipped.
|
||
|
||
- [x] **Step 5: `advisory_xact_lock` in `finance/app/db.py`**
|
||
|
||
Import `from sqlalchemy import create_engine, text` und anhängen:
|
||
|
||
```python
|
||
def advisory_xact_lock(session: Session, key: int) -> None:
|
||
"""Serialisiert konkurrierende Schreiber abgeleiteter Daten (Projektionen,
|
||
Planungs-Diagramm) über einen Postgres-Transaktions-Advisory-Lock. Der
|
||
Lock endet mit dem nächsten commit/rollback. Auf SQLite (Tests) No-Op."""
|
||
if session.get_bind().dialect.name == "postgresql":
|
||
session.execute(text("SELECT pg_advisory_xact_lock(:k)"), {"k": key})
|
||
```
|
||
|
||
- [x] **Step 6: `refresh_stale_projections` in `projection_service.py`**
|
||
|
||
Imports ergänzen: `from sqlalchemy import delete, func, select` und `from app.db import advisory_xact_lock`. Anhängen:
|
||
|
||
```python
|
||
# Advisory-Lock-Schlüssel (beliebig, aber projektweit eindeutig).
|
||
STALE_PROJECTION_LOCK = 110_001
|
||
|
||
|
||
def refresh_stale_projections(session: Session, today: date) -> int:
|
||
"""Rechnet jedes bereits durchgerechnete Szenario neu, dessen Projektion
|
||
vor `today` beginnt (erster gespeicherter Punkt <= today, d.h. Rechen-Start
|
||
< today). Maßstab ist der Projektionsbeginn, nicht computed_at (UTC-
|
||
Zeitstempel, würde um Mitternacht falsch schneiden). Nie gerechnete
|
||
Szenarien bleiben unberührt. Je Szenario: Lock nehmen, Staleness NEU
|
||
prüfen (ein paralleler Seitenaufruf kann schon gerechnet haben), rechnen;
|
||
run_projection committet und gibt damit den Lock frei.
|
||
Rückgabe: Anzahl neu gerechneter Szenarien."""
|
||
horizon = get_settings().horizon_days
|
||
ids = list(session.execute(select(ProjectionResult.scenario_id)).scalars())
|
||
refreshed = 0
|
||
for sid in ids:
|
||
advisory_xact_lock(session, STALE_PROJECTION_LOCK)
|
||
first = session.execute(select(func.min(ProjectionPoint.day))
|
||
.where(ProjectionPoint.scenario_id == sid)).scalar()
|
||
scenario = session.get(Scenario, sid)
|
||
if scenario is None or first is None or first > today:
|
||
session.commit() # Lock freigeben
|
||
continue
|
||
run_projection(session, scenario, horizon, today)
|
||
refreshed += 1
|
||
return refreshed
|
||
```
|
||
|
||
- [x] **Step 7: `v_projection` in `views.py` ersetzen**
|
||
|
||
```python
|
||
# Szenario-Vergleich (Ausbaustufe 11): Ist-Gesamtsaldo bis heute als eigene
|
||
# Serie RESERVED_SCENARIO_NAME; jede Szenario-Kurve beginnt heute am
|
||
# Ist-Saldo und zeigt danach nur Projektionspunkte NACH heute. Punkte
|
||
# <= heute einer älteren Berechnung (Momentaufnahme des letzten
|
||
# „Durchrechnen") erscheinen damit nie - auch wenn ein Szenario noch nicht
|
||
# neu gerechnet wurde. Muss in VIEWS NACH v_balance_total stehen.
|
||
"v_projection": f"""
|
||
SELECT '{RESERVED_SCENARIO_NAME}'::text AS scenario, t.day, t.balance
|
||
FROM v_balance_total t
|
||
UNION ALL
|
||
SELECT s.name::text, t.day, t.balance
|
||
FROM scenarios s
|
||
JOIN v_balance_total t ON t.day = CURRENT_DATE
|
||
WHERE EXISTS (SELECT 1 FROM projection_points p0
|
||
WHERE p0.scenario_id = s.id)
|
||
UNION ALL
|
||
SELECT s.name::text, p.day, p.balance
|
||
FROM projection_points p JOIN scenarios s ON s.id = p.scenario_id
|
||
WHERE p.day > CURRENT_DATE""",
|
||
```
|
||
|
||
Hinweis: die Spalte `scenario` wechselt von `varchar(200)` auf `text` → `CREATE OR REPLACE` schlägt fehl, `create_views` fällt automatisch auf DROP+CREATE zurück (vorhandenes Verhalten, kein Code nötig).
|
||
|
||
- [x] **Step 8: GUI-Hooks in `routers/gui.py`**
|
||
|
||
Imports: `import logging`, `from typing import Callable`, `from app.services.projection_service import refresh_stale_projections`. Nach `router = APIRouter()`:
|
||
|
||
```python
|
||
log = logging.getLogger(__name__)
|
||
|
||
|
||
def _safe_refresh(session: Session, refresh: Callable[[Session, date], object],
|
||
today: date) -> None:
|
||
"""Frischt abgeleitete Daten (Projektionen, Planungs-Diagramm) vor dem
|
||
Rendern auf. Ein Fehler darf die Seite nie brechen: rollback, Warnung ins
|
||
Log, die Seite rendert mit dem vorhandenen Stand."""
|
||
try:
|
||
refresh(session, today)
|
||
except Exception:
|
||
session.rollback()
|
||
log.warning("Auffrischen fehlgeschlagen: %s",
|
||
getattr(refresh, "__name__", refresh), exc_info=True)
|
||
```
|
||
|
||
In `index` als erste Zeile und in `szenarien_page` als erste Zeile:
|
||
|
||
```python
|
||
_safe_refresh(session, refresh_stale_projections, date.today())
|
||
```
|
||
|
||
(Der Name `refresh_stale_projections` wird zur Laufzeit im Modul nachgeschlagen — deshalb greift `monkeypatch.setattr("app.routers.gui.refresh_stale_projections", ...)`.)
|
||
|
||
- [x] **Step 9: Tests grün + Gesamtsuite**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q`
|
||
Expected: 224 + 8 = 232 passed, 1 skipped (`test_views_pg.py`).
|
||
|
||
- [x] **Step 10: Fable-Gate** — (a) Wegwerf-Postgres starten (Kommando im Docstring von `test_views_pg.py`), `FB_TEST_PG_URL=... pytest tests/test_views_pg.py` → passed; Container danach entfernen. (b) Race-Probe auf derselben Wegwerf-DB: zwei Threads rufen `refresh_stale_projections` gleichzeitig für ein veraltetes Szenario → danach genau `horizon_days` Punkte (keine Duplikate). (c) Code-Review Lock-Semantik (Lock endet mit commit in `run_projection`).
|
||
|
||
- [x] **Step 11: Commit**
|
||
|
||
```bash
|
||
git add finance/app/db.py finance/app/models/views.py finance/app/services/projection_service.py \
|
||
finance/app/routers/gui.py finance/tests/test_projection_refresh.py \
|
||
finance/tests/test_views_pg.py finance/tests/test_gui.py
|
||
git commit -m "fix: Szenario-Vergleich zeigt vor heute Ist-Saldo, veraltete Szenarien werden neu gerechnet (A11-T2)"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 3: Grafana-Panel „Planung nach Kategorie"
|
||
|
||
**Files:**
|
||
- Modify: `finance/app/models/tables.py` (Klasse `PlanningMonthly`)
|
||
- Create: `finance/alembic/versions/d5e8b3c7a410_planning_monthly.py`
|
||
- Create: `finance/app/services/planning_chart.py`
|
||
- Modify: `finance/app/routers/gui.py` (Aufrufe in `index`, `planung_page`)
|
||
- Modify: `finance/grafana/dashboards/finanzen.json` (Panel 5)
|
||
- Create: `finance/tests/test_planning_chart.py`
|
||
- Test: `finance/tests/test_grafana_dashboard.py`, `finance/tests/test_gui.py`, `finance/tests/test_views_pg.py`
|
||
|
||
**Interfaces:**
|
||
- Consumes: `advisory_xact_lock`, `_safe_refresh` (Task 2); `occurrences` (`engine/recurrence.py`), `loan_schedule` (`engine/loans.py`).
|
||
- Produces:
|
||
- `app.models.tables.PlanningMonthly` (Tabelle `planning_monthly`: `month: date` PK, `category: str` PK, `total: Decimal`).
|
||
- `app.services.planning_chart.planning_monthly_totals(session, today: date, horizon_days: int) -> dict[tuple[date, str], Decimal]`
|
||
- `app.services.planning_chart.refresh_planning_chart(session, today: date) -> int` (Anzahl geschriebener Zeilen).
|
||
|
||
- [x] **Step 1: Failing Tests** — `finance/tests/test_planning_chart.py` (Handrechnung; `today=28.09.2026`, `horizon_days=100` → Fenster 01.09.2026 … 06.01.2027):
|
||
|
||
```python
|
||
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")
|
||
```
|
||
|
||
Hinweis: `refresh_planning_chart` nutzt `get_settings().horizon_days` (Default 548) — deshalb prüft der letzte Test nur Idempotenz und eine eindeutig im Fenster liegende Zeile.
|
||
|
||
GUI + Dashboard, an `finance/tests/test_gui.py` anhängen:
|
||
|
||
```python
|
||
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
|
||
```
|
||
|
||
An `finance/tests/test_grafana_dashboard.py` anhängen:
|
||
|
||
```python
|
||
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))
|
||
```
|
||
|
||
An `finance/tests/test_views_pg.py` anhängen (Postgres-Idempotenz inkl. Lock-Pfad):
|
||
|
||
```python
|
||
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
|
||
```
|
||
|
||
- [x] **Step 2: Tests laufen lassen — müssen fehlschlagen**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q tests/test_planning_chart.py tests/test_gui.py tests/test_grafana_dashboard.py`
|
||
Expected: FAIL (ImportError `PlanningMonthly`/`planning_chart`, Panel fehlt).
|
||
|
||
- [x] **Step 3: Modell** — an `finance/app/models/tables.py` anhängen:
|
||
|
||
```python
|
||
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)
|
||
```
|
||
|
||
- [x] **Step 4: Migration** — `finance/alembic/versions/d5e8b3c7a410_planning_monthly.py`:
|
||
|
||
```python
|
||
"""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')
|
||
```
|
||
|
||
- [x] **Step 5: Service** — `finance/app/services/planning_chart.py`:
|
||
|
||
```python
|
||
"""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)
|
||
```
|
||
|
||
- [x] **Step 6: GUI-Hooks** — in `routers/gui.py` Import `from app.services.planning_chart import refresh_planning_chart`. In `index` (nach dem Projektions-Refresh aus Task 2) und als erste Zeile in `planung_page`:
|
||
|
||
```python
|
||
_safe_refresh(session, refresh_planning_chart, date.today())
|
||
```
|
||
|
||
- [x] **Step 7: Grafana-Panel** — in `finance/grafana/dashboards/finanzen.json` nach Panel 4 als fünftes Element von `panels` einfügen (2 Leerzeichen Einrückung wie bestehend):
|
||
|
||
```json
|
||
{
|
||
"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
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
Danach `python3 -m json.tool finance/grafana/dashboards/finanzen.json >/dev/null` (Syntax-Check).
|
||
|
||
- [x] **Step 8: Tests grün + Gesamtsuite**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q`
|
||
Expected: 232 + 8 = 240 passed, 2 skipped (`test_views_pg.py`).
|
||
|
||
- [x] **Step 9: Fable-Gate** — (a) Wegwerf-Postgres: `alembic upgrade head` → `alembic downgrade -1` → `alembic upgrade head` gegen `FB_DATABASE_URL` der Wegwerf-DB; Tabelle/PK per `\d planning_monthly` prüfen. (b) `FB_TEST_PG_URL=... pytest tests/test_views_pg.py` → 2 passed. (c) Unabhängige Handrechnung eines zweiten Szenarios (z.B. endfälliger Kredit `bullet` mit Zins) gegen `planning_monthly_totals`. (d) Datenschutz-Grep: keine Echtdaten in neuen Tests.
|
||
|
||
- [x] **Step 10: Commit**
|
||
|
||
```bash
|
||
git add finance/app/models/tables.py finance/alembic/versions/d5e8b3c7a410_planning_monthly.py \
|
||
finance/app/services/planning_chart.py finance/app/routers/gui.py \
|
||
finance/grafana/dashboards/finanzen.json finance/tests/test_planning_chart.py \
|
||
finance/tests/test_gui.py finance/tests/test_grafana_dashboard.py finance/tests/test_views_pg.py
|
||
git commit -m "feat: Grafana-Panel Planung nach Kategorie inkl. Kreditraten (A11-T3)"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 4: Clientseitige Tabellensortierung (`sort.js`)
|
||
|
||
**Files:**
|
||
- Create: `finance/app/static/sort.js`
|
||
- Create: `finance/tests/js/sort.test.js` (Unit, `node --test`)
|
||
- Create: `finance/tests/js/sort.dom.test.js` (DOM, braucht `jsdom`, sonst skip)
|
||
- Create: `finance/tests/test_sort_js.py` (pytest-Wrapper, skip ohne `node`)
|
||
- Create: `finance/tests/test_sort_templates.py`
|
||
- Modify: `finance/app/templates/base.html` (Script einbinden)
|
||
- Modify: `finance/app/static/style.css` (`.sort-btn`)
|
||
- Modify: `finance/app/templates/planning.html`, `szenarien.html`, `admin.html`, `index.html`, `salden.html`, `_import_list.html`, `transactions.html` (Attribute, s.u.)
|
||
|
||
**Interfaces:**
|
||
- Produces (JS, `window.FBSort` bzw. `module.exports`): `sortKey(raw: string, fromDataSort: bool) -> {kind:'date'|'number'|'text', value, text} | null`, `columnKind(keys) -> 'date'|'number'|'text'`, `sortItems(items: [{key, index, ...}], dir: 'asc'|'desc') -> items`, `groupRows(rows, isFollow) -> rows[][]`, `sortTable(table, colIndex, dir)`, `initTable(table)`, `init(root)`.
|
||
- Template-Konventionen (Task 5 nutzt `data-sort-mode="server"`): `tr[data-sort-follow]` hängt an der Vorzeile; `td[data-sort]` überschreibt den Sortierwert (Zahlen Punkt-dezimal, Datum ISO); `th[data-nosort]` bekommt keinen Button; `table[data-sort-mode="server"]` wird übersprungen.
|
||
|
||
- [x] **Step 1: Failing JS-Unit-Tests** — `finance/tests/js/sort.test.js`:
|
||
|
||
```js
|
||
'use strict';
|
||
const test = require('node:test');
|
||
const assert = require('node:assert');
|
||
const S = require('../../app/static/sort.js');
|
||
|
||
const items = (vals, ds) => vals.map((v, i) => ({ key: S.sortKey(v, !!ds), index: i, label: v }));
|
||
const labels = (arr) => arr.map((i) => i.label);
|
||
|
||
test('Datum TT.MM.JJJJ', () => {
|
||
const k = S.sortKey('05.03.2026', false);
|
||
assert.equal(k.kind, 'date');
|
||
assert.equal(k.value, 20260305);
|
||
});
|
||
|
||
test('deutsche Beträge und Zahlen', () => {
|
||
assert.equal(S.sortKey('-1.234,56 €', false).value, -1234.56);
|
||
assert.equal(S.sortKey('5,00 %', false).value, 5);
|
||
assert.equal(S.sortKey('15', false).kind, 'number');
|
||
assert.equal(S.sortKey('1.234', false).value, 1234);
|
||
});
|
||
|
||
test('data-sort: Punkt-Dezimal und ISO-Datum', () => {
|
||
assert.equal(S.sortKey('-1234.56', true).value, -1234.56);
|
||
assert.equal(S.sortKey('2026-06-30', true).kind, 'text');
|
||
});
|
||
|
||
test('Leerwerte sind null', () => {
|
||
assert.equal(S.sortKey('', false), null);
|
||
assert.equal(S.sortKey(' ', false), null);
|
||
assert.equal(S.sortKey('–', false), null);
|
||
assert.equal(S.sortKey(undefined, false), null);
|
||
});
|
||
|
||
test('Zahlen numerisch statt alphabetisch', () => {
|
||
assert.deepEqual(labels(S.sortItems(items(['10,00', '9,00', '-100,00']), 'asc')),
|
||
['-100,00', '9,00', '10,00']);
|
||
assert.deepEqual(labels(S.sortItems(items(['10,00', '9,00', '-100,00']), 'desc')),
|
||
['10,00', '9,00', '-100,00']);
|
||
});
|
||
|
||
test('Datum chronologisch', () => {
|
||
assert.deepEqual(labels(S.sortItems(items(['01.02.2026', '15.01.2026', '31.12.2025']), 'asc')),
|
||
['31.12.2025', '15.01.2026', '01.02.2026']);
|
||
});
|
||
|
||
test('Leerwerte immer am Ende, beide Richtungen', () => {
|
||
const v = ['01.02.2026', '–', '15.01.2026', ''];
|
||
assert.deepEqual(labels(S.sortItems(items(v), 'asc')), ['15.01.2026', '01.02.2026', '–', '']);
|
||
assert.deepEqual(labels(S.sortItems(items(v), 'desc')), ['01.02.2026', '15.01.2026', '–', '']);
|
||
});
|
||
|
||
test('gemischte Typen -> Text', () => {
|
||
assert.equal(S.columnKind([S.sortKey('10,00'), S.sortKey('abc'), null]), 'text');
|
||
assert.equal(S.columnKind([S.sortKey('10,00'), null]), 'number');
|
||
});
|
||
|
||
test('Text deutsch (Umlaute, Groß/klein)', () => {
|
||
assert.deepEqual(labels(S.sortItems(items(['Zahnarzt', 'Ärztin', 'miete']), 'asc')),
|
||
['Ärztin', 'miete', 'Zahnarzt']);
|
||
});
|
||
|
||
test('stabil bei gleichen Werten', () => {
|
||
const it = [{ key: S.sortKey('5'), index: 0, label: 'a' }, { key: S.sortKey('5'), index: 1, label: 'b' }];
|
||
assert.deepEqual(labels(S.sortItems(it, 'desc')), ['a', 'b']);
|
||
});
|
||
|
||
test('groupRows: Folgezeilen hängen an der Vorzeile', () => {
|
||
const rows = [{ id: 'a' }, { id: 'a-edit', f: true }, { id: 'a-plan', f: true },
|
||
{ id: 'b' }, { id: 'b-edit', f: true }];
|
||
assert.deepEqual(S.groupRows(rows, (r) => !!r.f).map((g) => g.map((r) => r.id)),
|
||
[['a', 'a-edit', 'a-plan'], ['b', 'b-edit']]);
|
||
});
|
||
```
|
||
|
||
- [x] **Step 2: DOM-Test (jsdom, optional)** — `finance/tests/js/sort.dom.test.js`:
|
||
|
||
```js
|
||
'use strict';
|
||
const test = require('node:test');
|
||
const assert = require('node:assert');
|
||
const fs = require('node:fs');
|
||
const path = require('node:path');
|
||
|
||
let JSDOM = null;
|
||
try { ({ JSDOM } = require('jsdom')); } catch (e) { /* jsdom fehlt -> skip */ }
|
||
const SRC = fs.readFileSync(path.join(__dirname, '../../app/static/sort.js'), 'utf8');
|
||
|
||
function load(html) {
|
||
const dom = new JSDOM(`<!doctype html><body>${html}</body>`, { runScripts: 'outside-only' });
|
||
dom.window.eval(SRC);
|
||
dom.window.FBSort.init(dom.window.document.body);
|
||
return dom.window.document;
|
||
}
|
||
|
||
const TABLE = `
|
||
<table id="t"><thead><tr><th>Name</th><th>Betrag</th><th>Aktion</th><th></th></tr></thead>
|
||
<tbody>
|
||
<tr id="r1"><td>B</td><td>10,00 €</td><td>x</td><td></td></tr>
|
||
<tr id="r1-edit" hidden data-sort-follow><td colspan="4">edit1</td></tr>
|
||
<tr id="r2"><td>A</td><td>-5,00 €</td><td>x</td><td></td></tr>
|
||
<tr id="r2-edit" hidden data-sort-follow><td colspan="4">edit2</td></tr>
|
||
<tr id="r3"><td>C</td><td>–</td><td>x</td><td></td></tr>
|
||
</tbody>
|
||
<tfoot><tr id="sum"><td>Gesamt</td><td>5,00 €</td><td></td><td></td></tr></tfoot>
|
||
</table>
|
||
<table data-sort-mode="server" id="s"><thead><tr><th>Datum</th></tr></thead><tbody></tbody></table>`;
|
||
|
||
const ids = (doc) => [...doc.querySelectorAll('#t tbody tr')].map((r) => r.id);
|
||
|
||
test('Buttons nur an Spalten mit Text, nicht an server-Tabellen', { skip: !JSDOM }, () => {
|
||
const doc = load(TABLE.replace('<th>Aktion</th>', '<th data-nosort>Aktion</th>'));
|
||
assert.equal(doc.querySelectorAll('#t .sort-btn').length, 2);
|
||
assert.equal(doc.querySelectorAll('#s .sort-btn').length, 0);
|
||
assert.equal(doc.querySelector('#t .sort-btn').textContent, '⇅');
|
||
});
|
||
|
||
test('Klick sortiert auf/ab, Folgezeilen wandern mit, Leerwert am Ende, tfoot bleibt', { skip: !JSDOM }, () => {
|
||
const doc = load(TABLE);
|
||
const btn = doc.querySelectorAll('#t .sort-btn')[1]; // Betrag
|
||
btn.click();
|
||
assert.equal(btn.textContent, '▲');
|
||
assert.deepEqual(ids(doc), ['r2', 'r2-edit', 'r1', 'r1-edit', 'r3']);
|
||
btn.click();
|
||
assert.equal(btn.textContent, '▼');
|
||
assert.deepEqual(ids(doc), ['r1', 'r1-edit', 'r2', 'r2-edit', 'r3']);
|
||
assert.equal(doc.querySelector('#t tfoot tr').id, 'sum');
|
||
const nameBtn = doc.querySelectorAll('#t .sort-btn')[0];
|
||
nameBtn.click();
|
||
assert.equal(btn.textContent, '⇅');
|
||
assert.deepEqual(ids(doc), ['r2', 'r2-edit', 'r1', 'r1-edit', 'r3']);
|
||
});
|
||
|
||
test('init zweimal erzeugt keine doppelten Buttons', { skip: !JSDOM }, () => {
|
||
const doc = load(TABLE);
|
||
doc.defaultView.FBSort.init(doc.body);
|
||
assert.equal(doc.querySelectorAll('#t .sort-btn').length, 3);
|
||
});
|
||
```
|
||
|
||
- [x] **Step 3: pytest-Wrapper** — `finance/tests/test_sort_js.py`:
|
||
|
||
```python
|
||
"""Führt die JS-Tests von static/sort.js aus, falls `node` installiert ist.
|
||
Auf dem Host fehlt node derzeit - dann skip; das Fable-Gate führt die Tests
|
||
per Podman aus (siehe Plan A11 Task 4, Fable-Gate)."""
|
||
import shutil
|
||
import subprocess
|
||
from pathlib import Path
|
||
|
||
import pytest
|
||
|
||
ROOT = Path(__file__).resolve().parent.parent
|
||
NODE = shutil.which("node")
|
||
|
||
|
||
@pytest.mark.skipif(NODE is None, reason="node nicht installiert")
|
||
def test_sort_js():
|
||
r = subprocess.run([NODE, "--test", str(ROOT / "tests" / "js")],
|
||
capture_output=True, text=True)
|
||
assert r.returncode == 0, r.stdout + r.stderr
|
||
```
|
||
|
||
- [x] **Step 4: Failing Template-Tests** — `finance/tests/test_sort_templates.py`:
|
||
|
||
```python
|
||
import re
|
||
from datetime import date
|
||
from decimal import Decimal
|
||
|
||
from app.models.tables import (Account, Category, Loan, PlannedItem,
|
||
RecurringItem, Scenario, ScenarioModifier,
|
||
ScenarioPlannedItem)
|
||
|
||
|
||
def _login(client):
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
|
||
|
||
def _follow_ids(html):
|
||
return re.findall(r'<tr id="([a-z]+-(?:edit|plan)-\d+)"[^>]*data-sort-follow', html)
|
||
|
||
|
||
def test_base_bindet_sort_js_ein(client):
|
||
_login(client)
|
||
assert '<script src="/static/sort.js"></script>' in client.get("/").text
|
||
|
||
|
||
def test_planung_folgezeilen_markiert(client, db):
|
||
_login(client)
|
||
c = Category(name="K")
|
||
db.add(c)
|
||
db.flush()
|
||
db.add_all([
|
||
RecurringItem(name="R", amount=Decimal("-1.00"), rhythm="monthly", due_day=1, category_id=c.id),
|
||
PlannedItem(name="P", amount=Decimal("-1.00"), due=date(2030, 1, 1)),
|
||
Loan(name="L", principal=Decimal("100.00"), annual_rate_pct=Decimal("1"),
|
||
term_months=2, payout_date=date(2030, 1, 1), repayment_type="annuity"),
|
||
])
|
||
db.commit()
|
||
kinds = sorted(i.split("-")[0] + "-" + i.split("-")[1] for i in _follow_ids(client.get("/planung").text))
|
||
assert kinds == ["loan-edit", "loan-plan", "pln-edit", "rec-edit"]
|
||
|
||
|
||
def test_szenarien_und_admin_folgezeilen_markiert(client, db):
|
||
_login(client)
|
||
c = Category(name="K")
|
||
sc = Scenario(name="S", description="")
|
||
db.add_all([c, sc])
|
||
db.flush()
|
||
db.add_all([
|
||
ScenarioModifier(scenario_id=sc.id, target_type="category", target_id=c.id,
|
||
kind="percent", value=Decimal("10")),
|
||
ScenarioPlannedItem(scenario_id=sc.id, name="E", amount=Decimal("-1.00"), due=date(2030, 1, 1)),
|
||
])
|
||
db.commit()
|
||
assert sorted(i.rsplit("-", 1)[0] for i in _follow_ids(client.get("/szenarien").text)) == ["mod-edit", "spi-edit"]
|
||
assert [i.rsplit("-", 1)[0] for i in _follow_ids(client.get("/admin").text)] == ["cat-edit"]
|
||
|
||
|
||
def test_salden_gesamtzeile_im_tfoot(client, db):
|
||
_login(client)
|
||
db.add(Account(bank="t", iban="DE-S-1", name="Konto", type="giro"))
|
||
db.commit()
|
||
html = client.get("/salden").text
|
||
assert re.search(r'<tfoot>\s*<tr class="total-row">', html)
|
||
|
||
|
||
def test_index_aktionsspalten_ohne_sortierung(client):
|
||
_login(client)
|
||
html = client.get("/").text
|
||
assert "<th data-nosort>Umbenennen</th>" in html
|
||
assert "<th data-nosort>Anker pflegen</th>" in html
|
||
|
||
|
||
def test_buchungen_tabelle_ist_serverseitig(client):
|
||
_login(client)
|
||
assert 'data-sort-mode="server"' in client.get("/buchungen").text
|
||
```
|
||
|
||
- [x] **Step 5: Tests laufen lassen — müssen fehlschlagen**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q tests/test_sort_templates.py tests/test_sort_js.py`
|
||
Expected: FAIL in `test_sort_templates.py`; `test_sort_js.py` skipped (kein node).
|
||
|
||
- [x] **Step 6: `finance/app/static/sort.js` implementieren**
|
||
|
||
```js
|
||
/* Tabellensortierung per Spaltenkopf (Ausbaustufe 11).
|
||
*
|
||
* Jede Tabelle mit <thead> bekommt je Spaltenkopf mit Text einen Button
|
||
* (⇅ unsortiert, ▲ aufsteigend, ▼ absteigend). Ausnahmen: th[data-nosort],
|
||
* table[data-sort-mode="server"] (z.B. /buchungen, sortiert serverseitig).
|
||
* Sortierwert: td[data-sort] (Zahl Punkt-dezimal / ISO-Datum) oder der
|
||
* angezeigte Text (Datum TT.MM.JJJJ, deutscher Betrag 1.234,56, sonst Text).
|
||
* Leere Werte und „–" stehen in beiden Richtungen am Ende. Zeilen mit
|
||
* data-sort-follow (Bearbeiten-/Tilgungsplan-Zeilen) wandern mit ihrer
|
||
* Vorzeile. Nur <tbody> wird sortiert, <tfoot> bleibt unberührt.
|
||
* Initialisierung über htmx.onLoad - greift auch bei nachgeladenen Fragmenten.
|
||
*/
|
||
(function () {
|
||
'use strict';
|
||
|
||
var DE_DATE = /^(\d{2})\.(\d{2})\.(\d{4})$/;
|
||
var DE_NUMBER = /^-?(\d{1,3}(\.\d{3})+|\d+)(,\d+)?$/;
|
||
var PLAIN_NUMBER = /^-?\d+(\.\d+)?$/;
|
||
var ICON = { none: '⇅', asc: '▲', desc: '▼' };
|
||
var collator = new Intl.Collator('de', { sensitivity: 'base', numeric: true });
|
||
|
||
function sortKey(raw, fromDataSort) {
|
||
var text = String(raw == null ? '' : raw).trim();
|
||
if (text === '' || text === '–' || text === '-') { return null; }
|
||
if (fromDataSort && PLAIN_NUMBER.test(text)) {
|
||
return { kind: 'number', value: parseFloat(text), text: text };
|
||
}
|
||
var m = DE_DATE.exec(text);
|
||
if (m) { return { kind: 'date', value: parseInt(m[3] + m[2] + m[1], 10), text: text }; }
|
||
var num = text.replace(/\s*[€%]$/, '').replace(/\s/g, '');
|
||
if (DE_NUMBER.test(num)) {
|
||
return { kind: 'number', value: parseFloat(num.replace(/\./g, '').replace(',', '.')), text: text };
|
||
}
|
||
return { kind: 'text', value: text, text: text };
|
||
}
|
||
|
||
function columnKind(keys) {
|
||
var kind = null;
|
||
for (var i = 0; i < keys.length; i++) {
|
||
if (!keys[i]) { continue; }
|
||
if (kind === null) { kind = keys[i].kind; } else if (kind !== keys[i].kind) { return 'text'; }
|
||
}
|
||
return kind || 'text';
|
||
}
|
||
|
||
function sortItems(items, dir) {
|
||
var kind = columnKind(items.map(function (it) { return it.key; }));
|
||
var sign = dir === 'desc' ? -1 : 1;
|
||
return items.slice().sort(function (a, b) {
|
||
if (!a.key || !b.key) {
|
||
if (a.key) { return -1; }
|
||
if (b.key) { return 1; }
|
||
return a.index - b.index;
|
||
}
|
||
var c = kind === 'text' ? collator.compare(a.key.text, b.key.text)
|
||
: a.key.value - b.key.value;
|
||
return c !== 0 ? sign * c : a.index - b.index;
|
||
});
|
||
}
|
||
|
||
function groupRows(rows, isFollow) {
|
||
var groups = [];
|
||
rows.forEach(function (row) {
|
||
if (isFollow(row) && groups.length) { groups[groups.length - 1].push(row); }
|
||
else { groups.push([row]); }
|
||
});
|
||
return groups;
|
||
}
|
||
|
||
function sortTable(table, col, dir) {
|
||
var tbody = table.tBodies[0];
|
||
if (!tbody) { return; }
|
||
var groups = groupRows(Array.prototype.slice.call(tbody.rows), function (r) {
|
||
return r.hasAttribute('data-sort-follow');
|
||
});
|
||
var items = groups.map(function (g, i) {
|
||
var cell = g[0].cells[col];
|
||
var key = null;
|
||
if (cell && cell.colSpan === 1) {
|
||
key = cell.hasAttribute('data-sort')
|
||
? sortKey(cell.getAttribute('data-sort'), true)
|
||
: sortKey(cell.textContent, false);
|
||
}
|
||
return { key: key, index: i, rows: g };
|
||
});
|
||
sortItems(items, dir).forEach(function (it) {
|
||
it.rows.forEach(function (r) { tbody.appendChild(r); });
|
||
});
|
||
}
|
||
|
||
function initTable(table) {
|
||
if (table.hasAttribute('data-sort-init') || !table.tHead || !table.tHead.rows.length
|
||
|| table.getAttribute('data-sort-mode') === 'server') { return; }
|
||
table.setAttribute('data-sort-init', '');
|
||
var doc = table.ownerDocument;
|
||
Array.prototype.forEach.call(table.tHead.rows[0].cells, function (th) {
|
||
var label = th.textContent.trim();
|
||
if (!label || th.hasAttribute('data-nosort')) { return; }
|
||
var btn = doc.createElement('button');
|
||
btn.type = 'button';
|
||
btn.className = 'sort-btn';
|
||
btn.textContent = ICON.none;
|
||
btn.setAttribute('aria-label', 'Nach ' + label + ' sortieren');
|
||
btn.addEventListener('click', function () {
|
||
var dir = btn.getAttribute('data-dir') === 'asc' ? 'desc' : 'asc';
|
||
Array.prototype.forEach.call(table.tHead.querySelectorAll('.sort-btn'), function (b) {
|
||
b.removeAttribute('data-dir');
|
||
b.classList.remove('active');
|
||
b.textContent = ICON.none;
|
||
});
|
||
btn.setAttribute('data-dir', dir);
|
||
btn.classList.add('active');
|
||
btn.textContent = ICON[dir];
|
||
sortTable(table, th.cellIndex, dir);
|
||
});
|
||
th.appendChild(doc.createTextNode(' '));
|
||
th.appendChild(btn);
|
||
});
|
||
}
|
||
|
||
function init(root) {
|
||
if (!root || !root.querySelectorAll) { return; }
|
||
if (root.tagName === 'TABLE') { initTable(root); }
|
||
Array.prototype.forEach.call(root.querySelectorAll('table'), initTable);
|
||
}
|
||
|
||
var api = { sortKey: sortKey, columnKind: columnKind, sortItems: sortItems,
|
||
groupRows: groupRows, sortTable: sortTable, initTable: initTable, init: init };
|
||
if (typeof module !== 'undefined' && module.exports) { module.exports = api; }
|
||
if (typeof window !== 'undefined') {
|
||
window.FBSort = api;
|
||
if (window.htmx) { window.htmx.onLoad(init); }
|
||
}
|
||
})();
|
||
```
|
||
|
||
- [x] **Step 7: Einbinden + CSS**
|
||
|
||
`base.html`: direkt nach `<script src="/static/htmx.min.js"></script>`:
|
||
```html
|
||
<script src="/static/sort.js"></script>
|
||
```
|
||
(`htmx.onLoad` feuert nach DOMContentLoaded für `body` und danach für jedes per htmx eingesetzte Fragment.)
|
||
|
||
`style.css` anhängen:
|
||
```css
|
||
/* Sortier-Buttons in Spaltenköpfen (Ausbaustufe 11, static/sort.js). */
|
||
.sort-btn {
|
||
border: none;
|
||
background: none;
|
||
padding: 0 0.2rem;
|
||
margin: 0;
|
||
font-size: 0.8em;
|
||
color: #888;
|
||
cursor: pointer;
|
||
text-decoration: none;
|
||
}
|
||
.sort-btn.active {
|
||
color: inherit;
|
||
font-weight: bold;
|
||
}
|
||
```
|
||
|
||
- [x] **Step 8: Template-Attribute setzen** (jede Stelle einzeln):
|
||
|
||
`planning.html`:
|
||
- `<tr id="rec-edit-{{ r.id }}" hidden>` → `<tr id="rec-edit-{{ r.id }}" hidden data-sort-follow>`
|
||
- `<tr id="pln-edit-{{ p.id }}" hidden>` → `... hidden data-sort-follow>`
|
||
- `<tr id="loan-edit-{{ l.id }}" hidden>` → `... hidden data-sort-follow>`
|
||
- Tilgungsplan-Zeile (das `<tr>` direkt vor `<td colspan="7">` mit `<details ontoggle=...loadLoanSchedule`) → `<tr id="loan-plan-{{ l.id }}" data-sort-follow>`
|
||
- Wiederkehrend/Einmal/Vorschläge-Betrag-`<td>`: `data-sort="{{ r.amount }}"` / `data-sort="{{ p.amount }}"` / `data-sort="{{ s.amount }}"` ergänzen (Vorschlags-Zelle enthält den Hinweistext).
|
||
- Kredite: Summe-`<td data-sort="{{ l.principal }}">`, Laufzeit-`<td data-sort="{{ l.term_months }}">`.
|
||
|
||
`szenarien.html`: `mod-edit-…` und `spi-edit-…` `<tr>` jeweils `data-sort-follow` ergänzen.
|
||
|
||
`admin.html`: `<tr id="cat-edit-{{ c.id }}" hidden data-sort-follow>`.
|
||
|
||
`index.html`: `<th data-nosort>Umbenennen</th><th data-nosort>Anker pflegen</th>`; Anker-`<td>` → `<td data-sort="{{ account.anchor_date.isoformat() if account.anchor_date is not none else '' }}">`; Saldo-`<td ... data-sort="{{ balance }}">`.
|
||
|
||
`salden.html`: die `<tr class="total-row">…</tr>` aus `<tbody>` herausnehmen und nach `</tbody>` in `<tfoot>…</tfoot>` setzen (Inhalt unverändert). Monats-`<td>` → `<td data-sort="{{ row['month'].isoformat() }}">`.
|
||
|
||
`_import_list.html`: Zeitraum-`<td>` → `<td data-sort="{{ s.period_start.isoformat() if s.period_start else '' }}">`.
|
||
|
||
`transactions.html`: `<table>` → `<table data-sort-mode="server">` (Links folgen in Task 5).
|
||
|
||
Tilgungsplan (per JS erzeugt) bleibt bewusst unsortiert (kein htmx-Swap → kein `onLoad`).
|
||
|
||
- [x] **Step 9: Tests grün + Gesamtsuite**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q`
|
||
Expected: 240 + 6 = 246 passed, 3 skipped.
|
||
|
||
- [x] **Step 10: Fable-Gate** — JS-Tests per Podman (Host hat kein node):
|
||
```bash
|
||
cd finance && podman run --rm -v "$PWD":/w:ro -w /w docker.io/library/node:22-alpine \
|
||
sh -c 'npm i --silent --no-save --prefix /tmp/j jsdom >/dev/null 2>&1; NODE_PATH=/tmp/j/node_modules node --test tests/js/'
|
||
```
|
||
Erwartet: alle Unit- und DOM-Tests grün (DOM-Tests NICHT geskippt). Zusätzlich jede Tabelle der GUI (lokaler uvicorn gegen Test-/SQLite-DB oder gerenderter HTML-Dump) auf korrekte `data-sort`/`data-nosort`/`data-sort-follow` durchsehen; Salden-Gesamtzeile im `tfoot`.
|
||
|
||
- [x] **Step 11: Commit**
|
||
|
||
```bash
|
||
git add finance/app/static/sort.js finance/app/static/style.css finance/app/templates/ \
|
||
finance/tests/js finance/tests/test_sort_js.py finance/tests/test_sort_templates.py
|
||
git commit -m "feat: Tabellen per Spaltenkopf sortierbar (A11-T4)"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 5: Serverseitige Sortierung `/buchungen`
|
||
|
||
**Files:**
|
||
- Modify: `finance/app/routers/transactions.py` (`SORT_COLUMNS`, `normalize_sort`, `_apply_sort`, `list_transactions`)
|
||
- Modify: `finance/app/routers/gui.py` (`buchungen`)
|
||
- Modify: `finance/app/templates/transactions.html` (Sortier-Links, Pager)
|
||
- Create: `finance/tests/test_transactions_sort.py`
|
||
|
||
**Interfaces:**
|
||
- Consumes: `data-sort-mode="server"` an der Buchungstabelle (Task 4); CSS `.sort-btn` (Task 4).
|
||
- Produces: `app.routers.transactions.normalize_sort(sort: str | None, dir: str | None) -> tuple[str, str]`; `list_transactions(..., sort: str = "datum", dir: str = "asc", ...)`. Erlaubte Schlüssel: `datum, konto, zweck, empfaenger, betrag, kategorie`.
|
||
|
||
- [x] **Step 1: Failing Tests** — `finance/tests/test_transactions_sort.py`:
|
||
|
||
```python
|
||
from datetime import date, timedelta
|
||
from decimal import Decimal
|
||
|
||
import pytest
|
||
|
||
from app.models.tables import Account, Category, Transaction
|
||
|
||
H = {"Authorization": "Bearer test-key"}
|
||
|
||
|
||
def _tx(db, acc, day, amount, purpose="Zweck", counterparty="X", category=None, n=[0]):
|
||
n[0] += 1
|
||
db.add(Transaction(account_id=acc.id, booking_date=day, amount=Decimal(amount),
|
||
purpose=purpose, counterparty=counterparty, status="confirmed",
|
||
category_id=category.id if category else None,
|
||
dedup_hash=f"sort-{n[0]}"))
|
||
|
||
|
||
@pytest.fixture
|
||
def small(db):
|
||
zeta = Account(bank="t", iban="DE-SO-1", name="Zeta", type="giro")
|
||
alpha = Account(bank="t", iban="DE-SO-2", name="alpha", type="giro")
|
||
kat = Category(name="Miete")
|
||
db.add_all([zeta, alpha, kat])
|
||
db.flush()
|
||
_tx(db, zeta, date(2026, 1, 3), "-5.00", "b-Zweck", "Bert", kat)
|
||
_tx(db, alpha, date(2026, 1, 1), "20.00", "A-Zweck", "anna", None)
|
||
_tx(db, zeta, date(2026, 1, 2), "-30.00", "c-Zweck", "Carl", kat)
|
||
db.commit()
|
||
|
||
|
||
def _amounts(client, qs):
|
||
r = client.get(f"/api/transactions?{qs}", headers=H)
|
||
assert r.status_code == 200
|
||
return [Decimal(t["amount"]) for t in r.json()]
|
||
|
||
|
||
def test_standard_bleibt_datum_aufsteigend(client, small):
|
||
assert _amounts(client, "") == [Decimal("20.00"), Decimal("-30.00"), Decimal("-5.00")]
|
||
|
||
|
||
@pytest.mark.parametrize("sort,dir,expected", [
|
||
("betrag", "asc", ["-30.00", "-5.00", "20.00"]),
|
||
("betrag", "desc", ["20.00", "-5.00", "-30.00"]),
|
||
("datum", "desc", ["-5.00", "-30.00", "20.00"]),
|
||
("konto", "asc", ["20.00", "-5.00", "-30.00"]), # alpha vor Zeta (case-insensitiv), Gleichstand -> id
|
||
("zweck", "asc", ["20.00", "-5.00", "-30.00"]), # A-, b-, c-Zweck
|
||
("empfaenger", "desc", ["-30.00", "-5.00", "20.00"]), # Carl, Bert, anna
|
||
("kategorie", "asc", ["-5.00", "-30.00", "20.00"]), # Miete (id-Reihenfolge), unkategorisiert zuletzt
|
||
("kategorie", "desc", ["-30.00", "-5.00", "20.00"]), # unkategorisiert auch absteigend zuletzt
|
||
])
|
||
def test_sortierung_je_spalte(client, small, sort, dir, expected):
|
||
assert _amounts(client, f"sort={sort}&dir={dir}") == [Decimal(e) for e in expected]
|
||
|
||
|
||
def test_ungueltige_werte_fallen_auf_standard(client, small):
|
||
assert _amounts(client, "sort=foo;drop&dir=hoch") == _amounts(client, "")
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
assert client.get("/buchungen?sort=foo&dir=bar").status_code == 200
|
||
|
||
|
||
@pytest.fixture
|
||
def many(db):
|
||
acc = Account(bank="t", iban="DE-SO-3", name="Viel", type="giro")
|
||
db.add(acc)
|
||
db.flush()
|
||
for i in range(1, 61): # Beträge i,37; Datum verwürfelt
|
||
_tx(db, acc, date(2026, 1, 1) + timedelta(days=(i * 7) % 60), f"{i}.37", f"Miete {i}")
|
||
db.commit()
|
||
|
||
|
||
def test_sortierung_gilt_ueber_alle_seiten(client, many):
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
p1 = client.get("/buchungen?sort=betrag&dir=desc").text
|
||
p2 = client.get("/buchungen?sort=betrag&dir=desc&page=2").text
|
||
assert ">60,37</td>" in p1 and ">1,37</td>" not in p1
|
||
assert ">1,37</td>" in p2 and ">60,37</td>" not in p2
|
||
|
||
|
||
def test_links_erhalten_filter_und_sortierung(client, many):
|
||
client.post("/login", data={"username": "admin", "password": "geheim"})
|
||
html = client.get("/buchungen?q=Miete&sort=betrag&dir=asc").text
|
||
# Aktiver Spaltenkopf kehrt die Richtung um, übernimmt den Filter, ohne page.
|
||
assert 'href="/buchungen?q=Miete&status=confirmed&sort=betrag&dir=desc"' in html
|
||
# Inaktive Spalte startet aufsteigend.
|
||
assert 'href="/buchungen?q=Miete&status=confirmed&sort=datum&dir=asc"' in html
|
||
# Pager behält Filter + Sortierung.
|
||
assert "q=Miete&status=confirmed&sort=betrag&dir=asc&page=2" in html
|
||
assert "▲" in html
|
||
```
|
||
|
||
Hinweis zur Link-Reihenfolge: die Parameter-Reihenfolge ergibt sich aus dem `filter_params`-Dict (account_id, date_from, date_to, category_id, q, status); leere Werte entfallen — wie beim bestehenden Pager.
|
||
|
||
- [x] **Step 2: Tests laufen lassen — müssen fehlschlagen**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q tests/test_transactions_sort.py`
|
||
Expected: FAIL (Sortierung wird ignoriert, Links fehlen). `test_standard_bleibt_datum_aufsteigend` darf grün sein.
|
||
|
||
- [x] **Step 3: Sortierlogik in `routers/transactions.py`** (vor `list_transactions` einfügen):
|
||
|
||
```python
|
||
# Serverseitige Sortierung der Buchungsliste (Ausbaustufe 11): Whitelist
|
||
# URL-Schlüssel -> Sortierausdruck. Textspalten über lower(), damit SQLite
|
||
# (Tests) und Postgres gleich (case-insensitiv) sortieren.
|
||
SORT_COLUMNS = {
|
||
"datum": Transaction.booking_date,
|
||
"konto": func.lower(Account.name),
|
||
"zweck": func.lower(Transaction.purpose),
|
||
"empfaenger": func.lower(Transaction.counterparty),
|
||
"betrag": Transaction.amount,
|
||
"kategorie": func.lower(Category.name),
|
||
}
|
||
|
||
|
||
def normalize_sort(sort: str | None, dir: str | None) -> tuple[str, str]:
|
||
"""Unbekannte Werte fallen still auf den Standard (datum/asc) zurück -
|
||
kein 422 für manipulierte oder veraltete Links."""
|
||
return (sort if sort in SORT_COLUMNS else "datum",
|
||
dir if dir in ("asc", "desc") else "asc")
|
||
|
||
|
||
def _apply_sort(stmt, sort: str | None, dir: str | None):
|
||
sort, dir = normalize_sort(sort, dir)
|
||
if sort == "konto":
|
||
stmt = stmt.outerjoin(Account, Account.id == Transaction.account_id)
|
||
elif sort == "kategorie":
|
||
stmt = stmt.outerjoin(Category, Category.id == Transaction.category_id)
|
||
col = SORT_COLUMNS[sort]
|
||
if dir == "desc":
|
||
return stmt.order_by(col.desc().nulls_last(), Transaction.id.desc())
|
||
return stmt.order_by(col.asc().nulls_last(), Transaction.id.asc())
|
||
```
|
||
|
||
In `list_transactions` Parameter `sort: str = "datum", dir: str = "asc",` vor `limit` ergänzen und die `order_by`-Zeile ersetzen durch:
|
||
|
||
```python
|
||
stmt = _apply_sort(stmt, sort, dir).offset(offset).limit(limit)
|
||
```
|
||
|
||
(Standard `datum/asc` ergibt `booking_date ASC NULLS LAST, id ASC` = bisherige Reihenfolge.)
|
||
|
||
- [x] **Step 4: `gui.buchungen`** — Import `normalize_sort` ergänzen (`from app.routers.transactions import count_transactions, list_transactions, normalize_sort`). Parameter `sort: str = "datum", dir: str = "asc",` nach `page` ergänzen; nach `page = max(1, page)`: `sort, dir = normalize_sort(sort, dir)`; an `list_transactions(...)` zusätzlich `sort=sort, dir=dir,` übergeben; im Template-Kontext `"sort": sort, "dir": dir,` ergänzen.
|
||
|
||
- [x] **Step 5: Template `transactions.html`**
|
||
|
||
Vor `<table data-sort-mode="server">` einfügen (ersetzt das bisherige `pager_params` im Pager):
|
||
|
||
```jinja
|
||
{% set filter_params = {
|
||
'account_id': filters.account_id,
|
||
'date_from': filters.date_from.isoformat() if filters.date_from else None,
|
||
'date_to': filters.date_to.isoformat() if filters.date_to else None,
|
||
'category_id': filters.category_id,
|
||
'q': filters.q,
|
||
'status': filters.status,
|
||
} %}
|
||
{% macro sort_th(key, label, params, cur_sort, cur_dir) -%}
|
||
{%- set active = cur_sort == key -%}
|
||
<th>{{ label }} <a class="sort-btn{{ ' active' if active else '' }}" aria-label="Nach {{ label }} sortieren"
|
||
href="/buchungen?{% for k, v in params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}sort={{ key }}&dir={{ 'desc' if active and cur_dir == 'asc' else 'asc' }}">{{ ('▲' if cur_dir == 'asc' else '▼') if active else '⇅' }}</a></th>
|
||
{%- endmacro %}
|
||
```
|
||
|
||
Kopfzeile ersetzen:
|
||
|
||
```jinja
|
||
<tr>
|
||
{{ sort_th('datum', 'Datum', filter_params, sort, dir) }}{{ sort_th('konto', 'Konto', filter_params, sort, dir) }}
|
||
{{ sort_th('zweck', 'Verwendungszweck', filter_params, sort, dir) }}{{ sort_th('empfaenger', 'Empfänger/Zahler', filter_params, sort, dir) }}
|
||
{{ sort_th('betrag', 'Betrag (€)', filter_params, sort, dir) }}{{ sort_th('kategorie', 'Kategorie', filter_params, sort, dir) }}<th></th>
|
||
</tr>
|
||
```
|
||
|
||
Pager: den `{% set pager_params = … %}`-Block entfernen; beide Pager-Links auf `filter_params` umstellen und vor `page=` `sort={{ sort }}&dir={{ dir }}&` einfügen, z.B.:
|
||
|
||
```jinja
|
||
<a href="/buchungen?{% for k, v in filter_params.items() %}{% if v %}{{ k }}={{ v|urlencode }}&{% endif %}{% endfor %}sort={{ sort }}&dir={{ dir }}&page={{ page - 1 }}">Zurück</a>
|
||
```
|
||
|
||
(`Weiter` analog mit `page + 1`.)
|
||
|
||
- [x] **Step 6: Tests grün + Gesamtsuite**
|
||
|
||
Run: `cd finance && .venv/bin/python -m pytest -q`
|
||
Expected: 246 + 12 = 258 passed, 3 skipped. Bestehende Pager-Tests (`test_gui.py` „Weiter", `?page=2`) weiterhin grün.
|
||
|
||
- [x] **Step 7: Fable-Gate** — Live-Approximation gegen Wegwerf-Postgres (Collation `en_US.utf8` wie Prod): `FB_DATABASE_URL` auf Wegwerf-DB, `alembic upgrade head`, künstliche Buchungen, `uvicorn` lokal, jede Spalte auf/ab per curl inkl. Seitengrenze und Filter; manipulierte Parameter (`sort=%27;--`, `dir=`) → 200.
|
||
|
||
- [x] **Step 8: Commit**
|
||
|
||
```bash
|
||
git add finance/app/routers/transactions.py finance/app/routers/gui.py \
|
||
finance/app/templates/transactions.html finance/tests/test_transactions_sort.py
|
||
git commit -m "feat: Buchungen serverseitig nach Spalte sortierbar (A11-T5)"
|
||
```
|
||
|
||
---
|
||
|
||
### Task 6: Doku + Release v0.11.0 + Live-Smoke
|
||
|
||
**Files:**
|
||
- Modify: `docs/ARCHITEKTUR.md`
|
||
- Modify: `finance/app/templates/hilfe.html`
|
||
- Modify: `finance/VERSION`
|
||
- Modify: `docs/superpowers/plans/2026-09-28-ausbaustufe-11.md` (Haken)
|
||
- Modify: `.superpowers/sdd/progress.md`
|
||
|
||
**Interfaces:**
|
||
- Consumes: alles aus Task 1–5.
|
||
|
||
- [x] **Step 1: `docs/ARCHITEKTUR.md`** — Komponenten-Diagramm: `S_projection` um `refresh_stale_projections` ergänzen; neuen Knoten `S_planning_chart["planning_chart.py<br/>refresh_planning_chart"]` mit Kanten von `gui.py` und zur DB; `M_views` unverändert benannt, im Abschnitt 4 (Zentrale Konzepte) kurz: `v_projection` = System-Ist + Szenarien ab heute, reservierter Name; Tabelle `planning_monthly` (abgeleitet, bei GUI-Aufruf neu geschrieben, Advisory-Locks 110001/110002); `static/sort.js` + serverseitige Buchungs-Sortierung. Jeder im Diagramm genannte Name muss real im Code existieren (CLAUDE.md-Regel).
|
||
|
||
- [x] **Step 2: `hilfe.html`** — im Konzepte-Abschnitt: Szenarien-Absatz um „Löschen" und automatisches Neurechnen veralteter Szenarien ergänzen; Grafana-Absatz um „System-Ist"-Kurve und Panel „Planung nach Kategorie"; ein Satz zur Sortierung per Spaltenkopf (⇅/▲/▼; Buchungen über alle Seiten).
|
||
|
||
- [x] **Step 3: Version** — `finance/VERSION`: `0.10.0` → `0.11.0`. Suite: `cd finance && .venv/bin/python -m pytest -q` → 258 passed, 3 skipped (bzw. aktueller Stand, alle grün).
|
||
|
||
- [x] **Step 4: Commit Release**
|
||
|
||
```bash
|
||
git add docs/ARCHITEKTUR.md finance/app/templates/hilfe.html finance/VERSION
|
||
git commit -m "chore: Release v0.11.0 (Szenario-Löschen, Grafana-Korrekturen, Tabellensortierung)"
|
||
```
|
||
|
||
- [x] **Step 5: Backup + Redeploy** — `./backup_finance_pod.sh` (vor Migration), dann `./create_pod_finance.sh`. Prüfen: `curl -s 127.0.0.1:8096/api/version` → `0.11.0`; `alembic current` im API-Container → `d5e8b3c7a410`.
|
||
|
||
- [x] **Step 6: Live-Smoke** (Details nur Chat/Scratchpad, DATENSCHUTZ):
|
||
- Seiten `/`, `/buchungen`, `/salden`, `/import`, `/planung`, `/szenarien`, `/admin`, `/hilfe` → 200.
|
||
- Nach Aufruf von `/`: alle bereits gerechneten Live-Szenarien haben ersten Projektionspunkt = morgen (SQL `min(day)` je Szenario, ohne Beträge ausgeben).
|
||
- `v_projection`: Serie `System-Ist` endet heute; keine Szenario-Zeile < heute.
|
||
- `planning_monthly` nicht leer, enthält Kategorie `Kreditraten` genau dann, wenn Kredite existieren.
|
||
- Grafana-API `/api/dashboards/uid/finanzen` → 5 Panels.
|
||
- Wegwerf-Szenario `SMOKE-A11` anlegen → per API (bzw. Button-Ziel) löschen → 204, keine Waisen in `projection_points`/`scenario_modifiers`.
|
||
- `POST /api/scenarios {"name":"System-Ist"}` → 422.
|
||
- `/buchungen?sort=betrag&dir=desc` → 200, erste Zeile = betragsgrößte Buchung (nur prüfen, nicht zitieren).
|
||
- Bestandsdaten unverändert: Anzahl confirmed Buchungen, Konten, Kategorien, Szenarien wie vor dem Deploy.
|
||
|
||
- [x] **Step 7: Fable-Release-Gate** (Whole-Branch `dd6110b..HEAD`) + Ledger-Eintrag „AUSBAUSTUFE 11 ABGESCHLOSSEN" in `.superpowers/sdd/progress.md`; Plan-Haken setzen; commit `docs: A11 Ledger + Plan-Haken`.
|