mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
admin
5aef967b74
Root cause of 'syncs stop at 5/31': the nightly buildium-gl-sync writes journal entries (so reports stay current) but never created the matching bank-register transactions for import-mode companies (gl_auto_post=false). Those registers were materialized once at import, so the reconciliation/register views froze ~5/31 while the GL kept advancing. - Add accounting.transactions.journal_entry_line_id (FK + unique index) to link register rows to their source GL line, making materialization idempotent - buildium-gl-sync now materializes a register transaction for each bank line it inserts, for import-mode companies only (bank debit -> deposit/credit, bank credit -> withdrawal/debit; category/coa from the single offset account), upserting on journal_entry_line_id so re-runs never double-insert - One-time backfill (run against prod) filled the existing gap: 231 register rows across Bridgewater/Casuarina/Village Grove/Bent Oak, skipping 3 near-miss rows that share a day with an existing register row (incl. a Casuarina transfer) for manual review gl_managed companies are unaffected — their register drives the GL via post_transaction_gl, not the other way around. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
13 lines
723 B
SQL
13 lines
723 B
SQL
-- Link bank-register transactions back to the GL line that produced them, so
|
|
-- the Buildium GL pull can materialize register rows idempotently for
|
|
-- import-mode companies (gl_auto_post = false). NULL = manually-entered or
|
|
-- legacy register row with no GL source. NULLs are distinct, so many unlinked
|
|
-- rows coexist; the unique index only prevents materializing the same GL line
|
|
-- into the register twice (and is usable as an ON CONFLICT target).
|
|
alter table accounting.transactions
|
|
add column if not exists journal_entry_line_id uuid
|
|
references accounting.journal_entry_lines(id) on delete set null;
|
|
|
|
create unique index if not exists transactions_jel_id_uidx
|
|
on accounting.transactions(journal_entry_line_id);
|