api.suitsbooks.nlGuideReference

stage_reconciliations_v2

Purpose
Copy newly created rows from public.reconciliations into public.reconciliations_staging once, avoiding duplicates. Returns a JSONB summary.

What it writes/reads

  • Reads: public.reconciliations

    • Must contain: reconciliation_id, company_id

  • Writes: public.reconciliations_staging

    • Must contain (at minimum): reconciliation_id, company_id

    • The function inserts (reconciliation_id, company_id) pairs. It skips any reconciliation_id that already exists in staging.

Signature

stage_reconciliations_v2() RETURNS jsonb

Behavior

  • Inserts all missing reconciliations into the staging table.

  • Uses a WHERE NOT EXISTS guard to prevent duplicates.

  • Wraps execution in a try/catch; on error you get {"status":"error", "message":"<pg error>"}.

Return shape (example)

{
  "reconciliations_staged": 24,
  "status": "ok",
  "message": "success",
  "processing_time": 37
}

  • reconciliations_staged: number of rows inserted this run

  • processing_time: milliseconds

Typical usage

-- Run it ad-hoc
SELECT public.stage_reconciliations_v2();

-- As part of a larger pipeline
WITH run AS (
  SELECT public.athena_reconcile_general_v10(p_company_id => '...') AS reconcile
)
SELECT public.stage_reconciliations_v2();

Idempotency

  • Safe to call repeatedly—existing reconciliation_ids in staging are skipped.

Common pitfalls

  • Missing columns in reconciliations_staging. Ensure it has:

    reconciliation_id uuid PRIMARY KEY /* or UNIQUE */,
company_id        uuid

  • Permission issues: caller needs INSERT on reconciliations_staging and SELECT on reconciliations.

Ideas to extend (optional)

  • Add a company filter variant:

    ... WHERE r.company_id = $1 AND NOT EXISTS ( ... )

  • Add an index/PK on reconciliation_id in staging for faster duplicate checks.

© 2025 Suitsbooks