IFM Unreconciled Cleanup (Pre 2026 02 28)

IFM Unreconciled Cleanup (Pre-2026-02-28)

Date: 2026-04-07 Project: zlokhayitthdzitjysht Parent org: InFocus Ministries (4762f260-4c90-4265-b362-2d86a7f3e6be) Run label: ifm_unreconciled_pre_2026_02_28_2026_04_07

Scope Used

Deleted only journal_entry_lines that matched all of:

• Organization in IFM org tree (parent + descendants)
• Account linked in bank_accounts for that tree
• transaction_date < '2026-02-28'
• reconciliation_status = 'unreconciled' (with NULL treated as unreconciled)
• Not voided and not void reversal

No bank_transactions rows matched this scope.

Pre-Delete Report

• bank_transactions: 0 rows
• journal_entry_lines (reconciliation account scope): 85 rows
• Account impacted: 1000 / IFM Checking Peoples Bank (b4fb3e0c-cb6c-4daa-9abc-fc8184e24553)
• Date range: 2025-12-08 to 2026-02-27
• Net amount (debit - credit): -5186.61

Cache / Backup

Created persistent cache table:

• public.reconciliation_cleanup_cache

Cached rows for this run:

• 85 rows (source_table = 'journal_entry_lines')
• run_label = 'ifm_unreconciled_pre_2026_02_28_2026_04_07'

Each cached row stores full original line JSON in row_data.

Delete Result

• Candidates found: 85
• Cached: 85
• Deleted: 85

Post-Delete Verification

• Remaining matching unreconciled journal_entry_lines: 0
• Remaining matching unreconciled bank_transactions: 0
• Cached rows for this run: 85

Restore SQL (if backfill needed)

Use this to reinsert deleted rows from cache:

INSERT INTO public.journal_entry_lines
SELECT *
FROM jsonb_populate_recordset(
  NULL::public.journal_entry_lines,
  (
    SELECT jsonb_agg(c.row_data ORDER BY c.id)
    FROM public.reconciliation_cleanup_cache c
    WHERE c.run_label = 'ifm_unreconciled_pre_2026_02_28_2026_04_07'
      AND c.source_table = 'journal_entry_lines'
  )
);

Quick Audit Queries

SELECT source_table, COUNT(*) AS rows
FROM public.reconciliation_cleanup_cache
WHERE run_label = 'ifm_unreconciled_pre_2026_02_28_2026_04_07'
GROUP BY 1;

SELECT
  MIN((row_data->>'transaction_date')::date) AS min_date,
  MAX((row_data->>'transaction_date')::date) AS max_date,
  ROUND(SUM(COALESCE((row_data->>'debit')::numeric,0) - COALESCE((row_data->>'credit')::numeric,0))::numeric,2) AS net_amount
FROM public.reconciliation_cleanup_cache
WHERE run_label = 'ifm_unreconciled_pre_2026_02_28_2026_04_07'
  AND source_table = 'journal_entry_lines';

Synced from IFMmvp-Frontend documentation: pages/accounting/IFM-UNRECONCILED-PRE-2026-02-28-CLEANUP-2026-04-07.md