f388d04c05
Damit ist der geplante Funktionsumfang beisammen. Phase 4 abgeschlossen - Konfigurierbare Cockpit-Kacheln; abgewählte werden nicht berechnet - Einnahmen/Ausgaben je Monat als Balkenpaare mit Saldolinie, aufgesetzt auf den Summenbericht statt auf eine eigene Abfrage - Meine Ausgaben als Ring auf der obersten Kategorieebene - Offene Vorgänge aus allen Modulen Phase 5 – Online-Banking - nemiah/php-fints als Fork auf Codeberg, psr/log direkt eingebaut: die Anwendung hängt an keinem fremden Update-Zyklus - PIN AES-256-GCM-verschlüsselt; Schlüssel aus APP_KEY oder aus data/schluessel/app_key, den der Entrypoint einmalig erzeugt - TAN-Dialog über mehrere Requests (FinTs::persist plus serialisierte Aktion in der Sitzung) – ein wartender Prozess wäre mit php-fpm der falsche Weg - Abgleich statt Direktbuchung: verbinden, übernehmen oder verwerfen - Regeln mit vier Vergleichsarten, Betragsgrenzen und Split-Schema Phase 6 – Wertpapiere - Depotbuchung und Geldbuchung als zwei Seiten eines Geschäfts; betrag ist der Bruttowert, Nebenkosten stehen daneben - Einstand nach gleitendem Durchschnitt, realisierte Gewinne, Erträge - Kursabruf über Leeway oder FMP, täglicher Lauf per Cron - QIF-Import für !Type:Invst Dabei behoben: Der Import buchte den Geldfluss jedes Wertpapiergeschäfts als Abgang – auch bei Verkäufen und Dividenden; und er erkannte das Verrechnungskonto nicht, wenn dem Konto im L-Feld eine Kategorie vorangeht (bei Verkäufen also immer). Phase 7 – Nebenmodule und Zahlungsverkehr - Buchungsvorlagen inklusive QIF-Import, Splits werden mitskaliert - Regelmäßige Buchungen mit Fälligkeitslauf, Finanzkalender, Erinnerungen - Adressen mit IBAN-Prüfung, Verträge mit Kündigungsfrist, Sparziele - Kredite mit gerechnetem Tilgungsplan - Überweisungen über FinTS; die Buchung entsteht erst nach Annahme durch die Bank. Die pain.001-Nachricht erzeugt SepaXml selbst Nebenbei: .dockerignore ergänzt (ohne sie brach der Build an data/mariadb ab), PlatzhalterController entfernt, zwei Inline-Handler beseitigt, die die Content-Security-Policy ohnehin blockierte. 888 PHP-Prüfungen und 45 E2E-Tests grün. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
248 lines
12 KiB
SQL
248 lines
12 KiB
SQL
-- Phase 7: Nebenmodule und Zahlungsverkehr.
|
||
--
|
||
-- Buchungsvorlagen, regelmäßige Buchungen, Erinnerungen, Adressen, Verträge,
|
||
-- Sparziele, Kredite und die Zahlungsaufträge des Zahlungsverkehrs.
|
||
|
||
-- Buchungsvorlagen (QIF !Type:Memorized). Sie füllen die Buchungsmaske vor;
|
||
-- die Splitzeilen stehen als JSON dabei, weil sie nur als Ganzes gebraucht
|
||
-- werden und keine Auswertung darauf läuft.
|
||
CREATE TABLE IF NOT EXISTS buchungsvorlagen (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
konto_id INT UNSIGNED NULL,
|
||
betrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
empfaenger VARCHAR(255) NOT NULL DEFAULT '',
|
||
buchungstext TEXT NULL,
|
||
nummer VARCHAR(32) NOT NULL DEFAULT '',
|
||
kategorie_id INT UNSIGNED NULL,
|
||
klasse_id INT UNSIGNED NULL,
|
||
gegenkonto_id INT UNSIGNED NULL,
|
||
splits LONGTEXT NULL,
|
||
quelle VARCHAR(16) NOT NULL DEFAULT 'manuell',
|
||
verwendet INT UNSIGNED NOT NULL DEFAULT 0,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uq_vorlage_name (benutzer_id, name),
|
||
KEY idx_vorlage_empfaenger (benutzer_id, empfaenger),
|
||
CONSTRAINT fk_vorlage_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_vorlage_konto FOREIGN KEY (konto_id)
|
||
REFERENCES konten (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_vorlage_kategorie FOREIGN KEY (kategorie_id)
|
||
REFERENCES kategorien (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_vorlage_klasse FOREIGN KEY (klasse_id)
|
||
REFERENCES klassen (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_vorlage_gegenkonto FOREIGN KEY (gegenkonto_id)
|
||
REFERENCES konten (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Regelmäßige Buchungen. „naechste_faelligkeit“ wird beim Buchen fortgeschrieben;
|
||
-- daran hängen der Fälligkeitslauf und der Finanzkalender.
|
||
CREATE TABLE IF NOT EXISTS regelmaessige_buchungen (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
konto_id INT UNSIGNED NOT NULL,
|
||
betrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
empfaenger VARCHAR(255) NOT NULL DEFAULT '',
|
||
buchungstext TEXT NULL,
|
||
kategorie_id INT UNSIGNED NULL,
|
||
klasse_id INT UNSIGNED NULL,
|
||
gegenkonto_id INT UNSIGNED NULL,
|
||
splits LONGTEXT NULL,
|
||
-- taeglich, woechentlich, zweiwoechentlich, monatlich, zweimonatlich,
|
||
-- vierteljaehrlich, halbjaehrlich, jaehrlich
|
||
intervall VARCHAR(24) NOT NULL DEFAULT 'monatlich',
|
||
-- Wie oft das Intervall übersprungen wird: 1 = jedes Mal, 2 = jedes zweite.
|
||
faktor TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
||
beginn DATE NOT NULL,
|
||
ende DATE NULL,
|
||
naechste_faelligkeit DATE NULL,
|
||
-- Automatisch buchen oder nur erinnern und auf Bestätigung warten.
|
||
automatisch TINYINT(1) NOT NULL DEFAULT 0,
|
||
-- So viele Tage im Voraus taucht sie im Kalender und in den offenen
|
||
-- Vorgängen auf.
|
||
vorlauf_tage SMALLINT UNSIGNED NOT NULL DEFAULT 7,
|
||
aktiv TINYINT(1) NOT NULL DEFAULT 1,
|
||
zuletzt_am DATE NULL,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_regelmaessig_faellig (benutzer_id, aktiv, naechste_faelligkeit),
|
||
CONSTRAINT fk_regelmaessig_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_regelmaessig_konto FOREIGN KEY (konto_id)
|
||
REFERENCES konten (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_regelmaessig_kategorie FOREIGN KEY (kategorie_id)
|
||
REFERENCES kategorien (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_regelmaessig_klasse FOREIGN KEY (klasse_id)
|
||
REFERENCES klassen (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_regelmaessig_gegenkonto FOREIGN KEY (gegenkonto_id)
|
||
REFERENCES konten (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS erinnerungen (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
text VARCHAR(255) NOT NULL,
|
||
faellig_am DATE NOT NULL,
|
||
notiz TEXT NULL,
|
||
erledigt TINYINT(1) NOT NULL DEFAULT 0,
|
||
erledigt_am DATETIME NULL,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_erinnerung_faellig (benutzer_id, erledigt, faellig_am),
|
||
CONSTRAINT fk_erinnerung_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Adressbuch. Es speist die Empfängerauswahl im Zahlungsverkehr.
|
||
CREATE TABLE IF NOT EXISTS adressen (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
zusatz VARCHAR(255) NOT NULL DEFAULT '',
|
||
strasse VARCHAR(255) NOT NULL DEFAULT '',
|
||
plz VARCHAR(16) NOT NULL DEFAULT '',
|
||
ort VARCHAR(128) NOT NULL DEFAULT '',
|
||
land VARCHAR(64) NOT NULL DEFAULT '',
|
||
iban VARCHAR(34) NOT NULL DEFAULT '',
|
||
bic VARCHAR(11) NOT NULL DEFAULT '',
|
||
email VARCHAR(190) NOT NULL DEFAULT '',
|
||
telefon VARCHAR(64) NOT NULL DEFAULT '',
|
||
notiz TEXT NULL,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uq_adresse_name (benutzer_id, name),
|
||
KEY idx_adresse_iban (benutzer_id, iban),
|
||
CONSTRAINT fk_adresse_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS vertraege (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
partner VARCHAR(255) NOT NULL DEFAULT '',
|
||
adresse_id INT UNSIGNED NULL,
|
||
vertragsnummer VARCHAR(64) NOT NULL DEFAULT '',
|
||
kategorie_id INT UNSIGNED NULL,
|
||
betrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
-- Zahlungsrhythmus wie bei den regelmäßigen Buchungen.
|
||
intervall VARCHAR(24) NOT NULL DEFAULT 'monatlich',
|
||
beginn DATE NULL,
|
||
ende DATE NULL,
|
||
kuendigungsfrist_tage SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
||
-- Ein Vertrag kann seine Zahlung selbst buchen lassen.
|
||
regelmaessige_id INT UNSIGNED NULL,
|
||
notiz TEXT NULL,
|
||
aktiv TINYINT(1) NOT NULL DEFAULT 1,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_vertrag_ende (benutzer_id, aktiv, ende),
|
||
CONSTRAINT fk_vertrag_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_vertrag_adresse FOREIGN KEY (adresse_id)
|
||
REFERENCES adressen (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_vertrag_kategorie FOREIGN KEY (kategorie_id)
|
||
REFERENCES kategorien (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_vertrag_regelmaessig FOREIGN KEY (regelmaessige_id)
|
||
REFERENCES regelmaessige_buchungen (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Sparziele: ein Betrag, ein Termin und das Konto, auf dem gespart wird. Der
|
||
-- Stand wird nicht gespeichert, sondern aus dem Konto gerechnet – sonst liefe
|
||
-- er auseinander.
|
||
CREATE TABLE IF NOT EXISTS sparziele (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
konto_id INT UNSIGNED NULL,
|
||
zielbetrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
-- Was zu Beginn schon da war und nicht zum Ziel zählt.
|
||
startbetrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
zieldatum DATE NULL,
|
||
monatsrate DECIMAL(15,2) NULL,
|
||
notiz TEXT NULL,
|
||
erreicht TINYINT(1) NOT NULL DEFAULT 0,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uq_sparziel_name (benutzer_id, name),
|
||
CONSTRAINT fk_sparziel_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_sparziel_konto FOREIGN KEY (konto_id)
|
||
REFERENCES konten (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Kredite mit Tilgungsplan. Der Plan wird gerechnet, nicht gespeichert: Er
|
||
-- ergibt sich vollständig aus Betrag, Zins, Rate und Beginn.
|
||
CREATE TABLE IF NOT EXISTS kredite (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
konto_id INT UNSIGNED NULL,
|
||
darlehensbetrag DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
zinssatz DECIMAL(6,3) NOT NULL DEFAULT 0.000,
|
||
rate DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
-- monatlich, vierteljaehrlich, halbjaehrlich, jaehrlich
|
||
rhythmus VARCHAR(24) NOT NULL DEFAULT 'monatlich',
|
||
beginn DATE NOT NULL,
|
||
zinsbindung_bis DATE NULL,
|
||
sondertilgung DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
||
notiz TEXT NULL,
|
||
aktiv TINYINT(1) NOT NULL DEFAULT 1,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uq_kredit_name (benutzer_id, name),
|
||
CONSTRAINT fk_kredit_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_kredit_konto FOREIGN KEY (konto_id)
|
||
REFERENCES konten (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- Zahlungsaufträge: Überweisungen, die über FinTS abgesendet werden. Der
|
||
-- Auftrag bleibt als Beleg stehen, auch wenn die Ausführung scheitert.
|
||
CREATE TABLE IF NOT EXISTS zahlungsauftraege (
|
||
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
benutzer_id INT UNSIGNED NOT NULL,
|
||
konto_id INT UNSIGNED NOT NULL,
|
||
zugang_id INT UNSIGNED NULL,
|
||
empfaenger VARCHAR(255) NOT NULL,
|
||
iban VARCHAR(34) NOT NULL,
|
||
bic VARCHAR(11) NOT NULL DEFAULT '',
|
||
betrag DECIMAL(15,2) NOT NULL,
|
||
waehrung CHAR(3) NOT NULL DEFAULT 'EUR',
|
||
verwendungszweck VARCHAR(500) NOT NULL DEFAULT '',
|
||
-- Leer für sofort, sonst Terminüberweisung.
|
||
ausfuehrung_am DATE NULL,
|
||
kategorie_id INT UNSIGNED NULL,
|
||
klasse_id INT UNSIGNED NULL,
|
||
-- entwurf: erfasst, noch nicht abgesendet
|
||
-- gesendet: von der Bank angenommen
|
||
-- fehler: abgelehnt, Meldung steht daneben
|
||
-- storniert: vom Nutzer zurückgezogen
|
||
status ENUM('entwurf','gesendet','fehler','storniert') NOT NULL DEFAULT 'entwurf',
|
||
meldung TEXT NULL,
|
||
gesendet_am DATETIME NULL,
|
||
buchung_id INT UNSIGNED NULL,
|
||
erstellt_am DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_auftrag_status (benutzer_id, status, ausfuehrung_am),
|
||
CONSTRAINT fk_auftrag_benutzer FOREIGN KEY (benutzer_id)
|
||
REFERENCES benutzer (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_auftrag_konto FOREIGN KEY (konto_id)
|
||
REFERENCES konten (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_auftrag_zugang FOREIGN KEY (zugang_id)
|
||
REFERENCES bank_zugaenge (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_auftrag_kategorie FOREIGN KEY (kategorie_id)
|
||
REFERENCES kategorien (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_auftrag_klasse FOREIGN KEY (klasse_id)
|
||
REFERENCES klassen (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_auftrag_buchung FOREIGN KEY (buchung_id)
|
||
REFERENCES buchungen (id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
INSERT INTO einstellungen (schluessel, wert, bereich) VALUES
|
||
('termine_vorlauf_tage', '14', 'buchhaltung'),
|
||
('termine_automatisch_buchen', '0', 'buchhaltung')
|
||
ON DUPLICATE KEY UPDATE schluessel = schluessel;
|