api.suitsbooks.nlGuideReference

reconciliations_staging_update_v1

One function to update staging flags (booked | archived | flagged) and/or verified status with the proper cascade to related tables. Returns a JSON you can pass straight back to clients.

What it does

  • Toggle one of booked | archived | flagged on public.reconciliations_staging.

  • Set/unset verified:

    • Updates reconciliations_staging.verified

    • Updates reconciliations.verified

    • Cascades to:

      • documents_v2.reconciled + documents_v2.reconciliation_id

      • transactions.reconciled + transactions.reconciliation_id

  • Prevents verifying true if there are no linked document_id and transaction_id.

Signature 

SELECT public.reconciliations_staging_update_v1(
  p_reconciliation_id uuid,
  p_column            text    DEFAULT NULL,   -- 'booked' | 'archived' | 'flagged'
  p_column_value      boolean DEFAULT NULL,
  p_verified          boolean DEFAULT NULL,
  p_company_id        uuid    DEFAULT NULL,   -- optional passthrough (unused)
  p_organization_id   uuid    DEFAULT NULL,   -- optional passthrough (unused)
  p_user_id           uuid    DEFAULT NULL,   -- optional passthrough (unused)
  p_publish_event     boolean DEFAULT true    -- optional passthrough (unused)
) RETURNS jsonb;

Quick examples

1) Toggle a staging flag 

-- Set 'flagged' = true on staging
select public.reconciliations_staging_update_v1(
  '00000000-0000-0000-0000-000000000000',
  'flagged',
  true,
  null
);

Response

{
  "ok": true,
  "reconciliation_id": "00000000-0000-0000-0000-000000000000",
  "column": "flagged",
  "value": true
}

2) Verify with cascade 

-- Set verified = true and cascade reconciled/reconciliation_id to linked rows
select public.reconciliations_staging_update_v1(
  '00000000-0000-0000-0000-000000000000',
  null,
  null,
  true
);

Response

{
  "ok": true,
  "verified": true,
  "affected": {
    "documents_v2": true,
    "transactions": true
  }
}

3) Do both in one call 

-- Flag and verify together
select public.reconciliations_staging_update_v1(
  '00000000-0000-0000-0000-000000000000',
  'booked',
  true,
  true
);

Behavior details

  • Column update: only one of booked | archived | flagged per call.

  • Verify:

    • true requires at least one of reconciliations.document_id or reconciliations.transaction_id.

    • Cascades:

      • documents_v2: reconciled = p_verified, reconciliation_id = (p_verified ? id : null)

      • transactions: same as above

    • Writes verified to both reconciliations_staging and reconciliations.

Errors

  • {"ok": false, "error": "not_found"} — reconciliation row doesn’t exist.

  • {"ok": false, "error": "invalid_column", "allowed": ["booked","archived","flagged"]} — bad p_column.

  • {"ok": false, "error": "missing_value"} — p_column given without p_column_value.

  • {"ok": false, "error": "no_linked_records"} — verifying true but no linked doc/tx.

  • {"ok": false, "error": "no_op", "message": "nothing to update"} — neither p_column nor p_verified provided.

  • {"ok": false, "error": "internal", "message": "..."} — any other failure (includes staging row missing).

Notes

  • p_company_id, p_organization_id, p_user_id, p_publish_event are present for parity/auditing; they’re not used internally.

  • Designed to mirror existing REST PATCH behavior and JSON shapes.

© 2025 Suitsbooks