fix: Szenario-Vergleich zeigt vor heute Ist-Saldo, veraltete Szenarien werden neu gerechnet (A11-T2)
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
This commit is contained in:
Claude Opus 5.5
+9
-1
@@ -1,4 +1,4 @@
|
||||
from sqlalchemy import create_engine
|
||||
from sqlalchemy import create_engine, text
|
||||
from sqlalchemy.orm import DeclarativeBase, Session
|
||||
|
||||
from app.config import get_settings
|
||||
@@ -21,3 +21,11 @@ def get_engine():
|
||||
def get_session():
|
||||
with Session(get_engine()) as session:
|
||||
yield session
|
||||
|
||||
|
||||
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})
|
||||
|
||||
@@ -114,9 +114,25 @@ VIEWS: dict[str, str] = {
|
||||
SUM(t.amount) AS total
|
||||
FROM transactions t LEFT JOIN categories c ON c.id = t.category_id
|
||||
WHERE t.status = 'confirmed' GROUP BY 1, 2""",
|
||||
"v_projection": """
|
||||
SELECT s.name AS scenario, p.day, p.balance
|
||||
FROM projection_points p JOIN scenarios s ON s.id = p.scenario_id""",
|
||||
# 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""",
|
||||
}
|
||||
|
||||
|
||||
|
||||
@@ -1,5 +1,7 @@
|
||||
import logging
|
||||
from datetime import date, timedelta
|
||||
from decimal import Decimal
|
||||
from typing import Callable
|
||||
|
||||
from fastapi import APIRouter, Depends, HTTPException, Request
|
||||
from fastapi.templating import Jinja2Templates
|
||||
@@ -25,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.projection_service import refresh_stale_projections
|
||||
from app.version import get_version
|
||||
|
||||
PAGE_SIZE = 50
|
||||
@@ -51,6 +54,21 @@ templates.env.filters["eur"] = eur
|
||||
templates.env.filters["de_label"] = de_label
|
||||
router = APIRouter()
|
||||
|
||||
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)
|
||||
|
||||
|
||||
def gui_session(request: Request):
|
||||
if not session_valid(request.cookies.get(COOKIE)):
|
||||
@@ -127,6 +145,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())
|
||||
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 +342,7 @@ def planung_page(request: Request, session: Session = Depends(get_session)):
|
||||
|
||||
@router.get("/szenarien", dependencies=[Depends(gui_session)])
|
||||
def szenarien_page(request: Request, session: Session = Depends(get_session)):
|
||||
_safe_refresh(session, refresh_stale_projections, date.today())
|
||||
categories = session.execute(select(Category)).scalars().all()
|
||||
recurring = _list_recurring(session=session)
|
||||
loans = _list_loans(session=session)
|
||||
|
||||
@@ -2,10 +2,11 @@ from datetime import date, timedelta
|
||||
from datetime import datetime, timezone
|
||||
from decimal import Decimal
|
||||
|
||||
from sqlalchemy import delete, select
|
||||
from sqlalchemy import delete, func, 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.projection import project
|
||||
from app.engine.scenario import (PlainModifier, PlainPlanned, PlainRecurring,
|
||||
@@ -63,3 +64,32 @@ def run_projection(session: Session, scenario: Scenario, horizon_days: int,
|
||||
session.add(result)
|
||||
session.commit()
|
||||
return result
|
||||
|
||||
|
||||
# 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
|
||||
|
||||
@@ -531,3 +531,37 @@ def test_szenario_loeschen_roundtrip_gui(client, db):
|
||||
db.commit()
|
||||
assert client.delete(f"/api/scenarios/{sc.id}", headers=H).status_code == 204
|
||||
assert "Wegwerf-Roundtrip" not in client.get("/szenarien").text
|
||||
|
||||
|
||||
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")
|
||||
|
||||
@@ -0,0 +1,66 @@
|
||||
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
|
||||
@@ -0,0 +1,82 @@
|
||||
"""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)
|
||||
Reference in New Issue
Block a user