wlfb
cf9282c716
F1: GUI-Login-Hash gegen $-Korruption gehaertet. .env-Werte einfach-gequotet
geschrieben + Sanity-Check nach Sourcing. Zusaetzlich systemd-Ebene:
podman escapt $->$$ in Environment=, systemd 252 kollabiert das nicht ->
sed-Post-Processing der Unit-Dateien. Live-.env: GUI-Passwort rotiert.
F2: Explizite Dependent-Deletes gegen FK-500 (delete_scenario/loan), 409 bei
bestaetigtem Import-Delete. Test-Engine erzwingt PRAGMA foreign_keys=ON.
F3: Enum/Range-Validierung (Literal + Field ge/le) fuer Recurring, Loan,
Modifier inkl. PATCH-Pfade.
F4: Confirm nur fuer draft-Statements (sonst 409) + Dedup-Re-Check gegen
bestaetigte Buchungen vor dem Promoten.
F5: v_balance_history/v_balance_total addieren pro Konto den opening_balance
des fruehesten bestaetigten Statements (approved deviation vom Plan-SQL).
F6: patch_scenario Namenskollision -> 409; create_transaction unbekanntes
Konto -> 404 (statt 500).
F7: Generierte systemd-Unit-Dateien chmod 600 (enthalten Env-Secrets).
F8: Globaler htmx:responseError-Handler in base.html.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
59 lines
2.7 KiB
Python
59 lines
2.7 KiB
Python
from sqlalchemy import text
|
|
from sqlalchemy.engine import Engine
|
|
|
|
# Per-account starting balance: the opening_balance of each account's earliest
|
|
# confirmed statement (if any). Used to seed the cumulative sums below so the
|
|
# Grafana balance charts show the real account balance, not just the running
|
|
# sum of imported transactions (which would start at zero and ignore the
|
|
# balance that existed before the first imported statement).
|
|
_ACCOUNT_BASE = """
|
|
SELECT DISTINCT ON (account_id) account_id, opening_balance
|
|
FROM statements
|
|
WHERE status = 'confirmed' AND opening_balance IS NOT NULL
|
|
ORDER BY account_id, period_start NULLS LAST, id
|
|
"""
|
|
|
|
VIEWS: dict[str, str] = {
|
|
"v_balance_history": f"""
|
|
SELECT t.booking_date AS day, a.name AS account,
|
|
COALESCE(b.opening_balance, 0)
|
|
+ SUM(SUM(t.amount)) OVER (PARTITION BY a.id
|
|
ORDER BY t.booking_date) AS balance
|
|
FROM transactions t JOIN accounts a ON a.id = t.account_id
|
|
LEFT JOIN ({_ACCOUNT_BASE}) b ON b.account_id = a.id
|
|
WHERE t.status = 'confirmed'
|
|
GROUP BY a.id, a.name, t.booking_date, b.opening_balance""",
|
|
"v_balance_total": f"""
|
|
SELECT booking_date AS day,
|
|
(SELECT COALESCE(SUM(opening_balance), 0)
|
|
FROM ({_ACCOUNT_BASE}) s)
|
|
+ SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
|
|
FROM transactions WHERE status = 'confirmed' GROUP BY booking_date""",
|
|
"v_monthly_by_category": """
|
|
SELECT date_trunc('month', t.booking_date) AS month,
|
|
COALESCE(c.name, 'unkategorisiert') AS category,
|
|
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""",
|
|
}
|
|
|
|
|
|
def create_views(engine: Engine) -> None:
|
|
if engine.dialect.name != "postgresql":
|
|
return
|
|
with engine.begin() as conn:
|
|
for name, body in VIEWS.items():
|
|
# CREATE OR REPLACE VIEW fails if the output column set changed
|
|
# (name/type/order). Fall back to DROP + CREATE in that case so a
|
|
# redeploy always applies the current definition.
|
|
try:
|
|
with conn.begin_nested():
|
|
conn.execute(
|
|
text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
|
|
except Exception:
|
|
conn.execute(text(f"DROP VIEW IF EXISTS {name} CASCADE"))
|
|
conn.execute(text(f"CREATE VIEW {name} AS {body}"))
|