Mattsson
8a6ce7093e
* feat: implement skattekonto drift detection and alerting - Add skattekonto drift computation logic to compare Skatteverket's saldo with GL 1630 sum. - Implement alerting mechanism for significant drift changes, with throttling to prevent alert spamming. - Introduce database functions to sum GL 1630 entries and list unbooked skattekonto rows. feat: create own account transfer detection - Develop logic to detect transfers between a company's own cash accounts based on counterparty IBAN. - Implement tests to validate detection logic under various scenarios, including matching and non-matching IBANs. feat: establish cash accounts as a first-class entity - Create cash_accounts table to manage routable cash accounts, replacing ad-hoc JSONB structures. - Implement functions for listing, upserting, and managing cash accounts, including primary account designation. feat: enhance GL line reconciliation functionality - Modify get_unlinked_1930_lines RPC to accept any account number for reconciliation, improving flexibility for different currencies. - Update related functions to ensure compatibility with the new cash_accounts structure. feat: capture counterparty IBAN in transactions - Add counterparty_iban column to transactions table to facilitate intra-account transfer detection. - Create index for efficient lookups based on counterparty IBAN. * feat: Enhance cash account handling and reconciliation processes - Updated reconciliation routes to enforce cash account validation for all account numbers, including '1930'. - Improved error handling for unknown cash accounts in reconciliation status and unmatched entries routes. - Changed CashAccountSelector to use sessionStorage instead of localStorage for better data privacy. - Fixed mapping for employer payroll taxes to route to the correct account (2730 instead of 2731). - Added safety checks for company IDs in the guessCounterAccount function to prevent injection vulnerabilities. - Introduced atomic RPC for setting primary cash accounts to avoid intermediate states during updates. - Seeded default cash accounts for new companies to ensure reconciliation routes are accessible from day one. - Updated email notifications for drift detection to avoid exposing sensitive financial data. - Enhanced bank reconciliation logic to handle multi-currency transactions correctly. - Renamed and updated tests to reflect changes in the underlying RPCs and ensure accurate coverage. - Migrated existing cash account rules to correct mappings in compliance with Swedish accounting standards.
24 lines
1.1 KiB
SQL
24 lines
1.1 KiB
SQL
-- Migration: capture counterparty IBAN on transactions for transfer-pairing.
|
|
--
|
|
-- Why: PSD2 returns the creditor (outflow) or debtor (inflow) account IBAN on
|
|
-- every booked transaction, but gnubok hasn't persisted it. Without an IBAN
|
|
-- column, intra-account transfers ("move money from SEK 1930 to EUR 1932")
|
|
-- can't be auto-detected and end up double-categorized.
|
|
--
|
|
-- The new column is nullable: SIE imports, manual entries, and pre-existing
|
|
-- rows have no IBAN. counterparty_account stays as a fallback for Bankgiro /
|
|
-- Plusgiro identifiers when IBAN is absent (Swedish domestic transfers).
|
|
--
|
|
-- The partial index supports the own-account detector's primary lookup:
|
|
-- SELECT ... FROM transactions WHERE company_id = ? AND counterparty_iban = ?
|
|
|
|
ALTER TABLE public.transactions
|
|
ADD COLUMN IF NOT EXISTS counterparty_iban TEXT,
|
|
ADD COLUMN IF NOT EXISTS counterparty_account TEXT;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_counterparty_iban
|
|
ON public.transactions (company_id, counterparty_iban)
|
|
WHERE counterparty_iban IS NOT NULL;
|
|
|
|
NOTIFY pgrst, 'reload schema';
|