api.suitsbooks.nlGuideReference

Reconciliation - Helpers and Post-Processing / Staging

Helpers used after athena_reconcile_general_v1_9_i to stage, verify, toggle, and bulk‑unverify results. Includes normalization utilities.

0) Normalization Utilities

Signature 

CREATE OR REPLACE FUNCTION public.norm_code(p text)      RETURNS text IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION public.norm_core_ns(p text)   RETURNS text IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION public.norm_iban(p text)      RETURNS text IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION public.norm_text_ns(p text)   RETURNS text IMMUTABLE STRICT PARALLEL SAFE;

Purpose

  • norm_code: uppercase alnum. Remove separators. For invoice/order codes.

  • norm_core_ns: uppercase alnum. Remove punctuation and spaces and common suffixes (INC, LTD, LLC, CO, BV, GMBH, SA, PLC, etc.). For merchant core name signal.

  • norm_iban: uppercase alnum. Strip spaces and symbols. For IBAN comparisons.

  • norm_text_ns: uppercase alnum. Collapse whitespace then strip spaces. For broad name containment.

Examples 

SELECT public.norm_code('Inv-001/23');         -- 'INV00123'
SELECT public.norm_core_ns('ACME LTD.');       -- 'ACME'
SELECT public.norm_iban('IE29 aibk 9311 5212');-- 'IE29AIBK93115212'
SELECT public.norm_text_ns('John  Q.  Public');-- 'JOHNQPUBLIC'

1) reconciliations_staging_update_v2

Signature 

CREATE OR REPLACE FUNCTION public.reconciliations_staging_update_v2(
  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,
  p_organization_id   uuid    DEFAULT NULL,
  p_user_id           uuid    DEFAULT NULL,
  p_publish_event     boolean DEFAULT true
) RETURNS jsonb

Simple Lifecycle

  1. Resolve reconciliation row → (company_id, document_id, transaction_id).

  2. If p_column set → toggle that boolean on reconciliations_staging.

  3. If p_verified set → flip verified on both reconciliations and reconciliations_staging.

  4. Cascade verification to documents_v2 and transactions using reconciliation_id.

  5. Log steps and end result. Return JSON with effects.

Advanced Lifecycle

  • Logs: reconciliations_log_start/step/end with timings and context.

  • Column toggle: validates allowed set; uses dynamic SQL format(%I) to bind column name, parameterized value.

  • Verify cascade:

    • Precheck: if verifying TRUE but no linked doc/tx → error.

    • Update reconciliations.verified and reconciliations_staging.verified.

    • Update documents_v2.reconciled and transactions.reconciled. Also set/clear reconciliation_id.

  • Result JSON includes flags for which tables were affected and row counts.

  • Errors: logged and returned as {ok:false,...}.

Return shape (example) 

{
  "ok": true,
  "reconciliation_id": "2d8c...",
  "column": "flagged",
  "value": true,
  "verified": true,
  "affected": {"documents_v2": true, "transactions": true},
  "rows": {"reconciliations": 1, "staging": 1, "documents_v2": 1, "transactions": 1}
}

Example calls 

-- Toggle a flag
SELECT public.reconciliations_staging_update_v2(
  p_reconciliation_id => 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  p_column            => 'flagged',
  p_column_value      => TRUE
);

-- Verify and cascade
SELECT public.reconciliations_staging_update_v2(
  p_reconciliation_id => 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb',
  p_verified          => TRUE
);

-- Unverify and decascade
SELECT public.reconciliations_staging_update_v2(
  p_reconciliation_id => 'cccccccc-cccc-cccc-cccc-cccccccccccc',
  p_verified          => FALSE
);

2) auto_stage_reconciliations_v1

Signature 

CREATE OR REPLACE FUNCTION public.auto_stage_reconciliations_v1(
  p_reconciliation_config_id uuid
) RETURNS jsonb

Simple Lifecycle

  1. Read config by id: auto_reconcile and auto_reconcile_min_pct.

  2. Insert any reconciliations rows not yet present into reconciliations_staging.

  3. If auto‑reconcile enabled and threshold set → auto‑verify each newly staged reconciliation whose confidence >= threshold using reconciliations_staging_update_v2.

  4. Log steps and return counts.

Advanced Lifecycle

  • Threshold normalization: values <=1 treated as fractions → percent.

  • Staging insert uses INSERT ... SELECT with NOT EXISTS and returns ids to avoid temp tables.

  • Auto‑verify loops over inserted ids only, not historical data.

  • Logs both staging and auto‑verify steps with counts and timing.

Return shape 

{
  "status":"ok",
  "message":"success",
  "processing_time": 142,
  "reconciliations_staged": 27,
  "auto_verified": 9,
  "auto_reconcile": true,
  "auto_reconcile_min_pct": 95
}

Example calls 

-- Stage new reconciliations for the profile and auto‑verify above threshold
SELECT public.auto_stage_reconciliations_v1('3dae610e-2326-46b8-ba18-85f54961e4ca');

-- Inspect staging results
SELECT s.*
FROM public.reconciliations_staging s
ORDER BY s.created_at DESC
LIMIT 50;

3) reconciliations_unverify_bulk_v1

Signature 

CREATE OR REPLACE FUNCTION public.reconciliations_unverify_bulk_v1(
  p_reconciliation_ids uuid[]      DEFAULT NULL,
  p_company_id         uuid        DEFAULT NULL,
  p_since              timestamptz DEFAULT NULL,
  p_limit              int         DEFAULT NULL,
  p_publish_event      boolean     DEFAULT true
) RETURNS jsonb

Simple Lifecycle

  1. Build a target list of currently verified reconciliations using filters.

  2. Iterate and call reconciliations_staging_update_v2(..., p_verified => FALSE) for each target.

  3. Log totals. Return summary JSON.

Advanced Lifecycle

  • Filters: explicit id array, company id, created_at lower bound, optional limit.

  • Ordering: newest first.

  • Robust loop with per‑item exception handling; counts successes and errors.

  • End log reflects attempted, unverified, and errors.

Return shape 

{ "ok": true, "attempted": 42, "unverified": 42, "errors": 0 }

Example calls 

-- Unverify a set of ids
SELECT public.reconciliations_unverify_bulk_v1(
  p_reconciliation_ids => ARRAY[
    'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid,
    'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'::uuid
  ]
);

-- Unverify last 7 days for a company, max 100
SELECT public.reconciliations_unverify_bulk_v1(
  p_company_id => '11111111-1111-1111-1111-111111111111',
  p_since      => now() - interval '7 days',
  p_limit      => 100
);

4) Operational Flow (End‑to‑End)

Simple

  1. Run athena_reconcile_general_v1_9_i(company_id) → inserts into reconciliations.

  2. Run auto_stage_reconciliations_v1(config_id) → fills staging and optional auto‑verify.

  3. Use reconciliations_staging_update_v2 for manual verify/unverify or flagging.

  4. If needed, bulk rollback with reconciliations_unverify_bulk_v1.

Advanced

  • Tune reconciliations_config for: min_confidence, amount_tolerance, date_window_days, auto_reconcile, auto_reconcile_min_pct.

  • Audit via reconciliations_logs using run_id from each helper.

  • Downstream UI reads reconciliations_staging booleans booked|archived|flagged and verified for workflow states.

5) Troubleshooting

  • invalid_column: only booked|archived|flagged allowed.

  • no_linked_records on verify: reconciliation missing document_id and transaction_id.

  • staging_row_not_found: run auto_stage_reconciliations_v1 first to populate staging.

  • Nothing to update: pass p_column or p_verified.

  • Auto‑verify not triggering: ensure auto_reconcile=true and auto_reconcile_min_pct set (>0).

6) Monitoring snippets 

-- Last run logs for auto‑stage
SELECT * FROM public.reconciliations_logs
WHERE function_version = 'auto_stage_reconciliations_v1'
ORDER BY time DESC LIMIT 10;

-- Verify cascades vs. decascades
SELECT time, action, target, status, rows_affected, context
FROM public.reconciliations_logs
WHERE function_version = 'reconciliations_staging_update_v2'
ORDER BY time DESC LIMIT 20;

© 2025 Suitsbooks