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.
57 lines
2.1 KiB
PL/PgSQL
57 lines
2.1 KiB
PL/PgSQL
-- Migration: atomic set_cash_account_primary RPC
|
|
--
|
|
-- Why this exists: lib/cash-accounts/service.ts#setPrimary() previously did two
|
|
-- separate UPDATEs (clear old primary, then set new primary). Between the two
|
|
-- round-trips no row carries is_primary = true. If skattekonto-booking runs
|
|
-- in that window and encounters a rule whose counter_account is '__PRIMARY_SEK__',
|
|
-- getPrimary() returns null and the booking either falls back to '1930'
|
|
-- (potentially wrong) or fails outright.
|
|
--
|
|
-- This RPC wraps both updates in a single BEGIN ... COMMIT so the
|
|
-- intermediate "no primary" state is invisible to concurrent readers.
|
|
-- SECURITY INVOKER so RLS still scopes which rows the caller may touch.
|
|
--
|
|
-- Compliance: addresses the P1 "Non-atomic setPrimary" review finding.
|
|
|
|
CREATE OR REPLACE FUNCTION public.set_cash_account_primary(
|
|
p_company_id uuid,
|
|
p_cash_account_id uuid
|
|
)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
-- Guard: refuse to set primary on an account that doesn't exist or that
|
|
-- belongs to a different company. RLS covers the second case but we want a
|
|
-- clean error rather than a no-op silent UPDATE.
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM public.cash_accounts
|
|
WHERE id = p_cash_account_id AND company_id = p_company_id
|
|
) THEN
|
|
RAISE EXCEPTION 'cash_account not found for company';
|
|
END IF;
|
|
|
|
-- Both updates execute in the same statement-level transaction. The partial
|
|
-- unique index idx_cash_accounts_one_primary_per_company is deferred to
|
|
-- transaction commit only if explicitly deferred; UPDATE order matters and
|
|
-- the index is non-deferrable today. Postgres still evaluates uniqueness
|
|
-- at statement boundaries within the function body, so we clear first.
|
|
UPDATE public.cash_accounts
|
|
SET is_primary = false
|
|
WHERE company_id = p_company_id
|
|
AND is_primary = true
|
|
AND id <> p_cash_account_id;
|
|
|
|
UPDATE public.cash_accounts
|
|
SET is_primary = true
|
|
WHERE company_id = p_company_id
|
|
AND id = p_cash_account_id;
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION public.set_cash_account_primary(uuid, uuid) TO authenticated;
|
|
|
|
NOTIFY pgrst, 'reload schema';
|