api.suitsbooks.nlGuideReference

function flow in sql

athena_reconcile_general_v13 — Simple Docs

Runs the Athena reconcile engine and creates/updates rows in public.reconciliations (and public.reconciliations_match_scores) for the best (transaction ⇄ document) matches.
You can optionally cap how many matches are finalized per run with p_chunk_limit.

Signature 

SELECT public.athena_reconcile_general_v13(
  p_weightset_id      uuid            DEFAULT NULL,
  p_athena_view       text            DEFAULT 'athena_reconcile_general_v13',
  p_date_window_days  numeric         DEFAULT 1,     -- days difference allowed between tx/doc dates
  p_min_confidence    numeric         DEFAULT 0,     -- 0–100 filter
  p_company_id        uuid            DEFAULT NULL,  -- only this company if set
  p_debug             boolean         DEFAULT false, -- timings + counts
  p_chunk_limit       integer         DEFAULT NULL   -- max matches to finalize; NULL = no cap
) RETURNS jsonb;

Parameters (plain English)

  • p_weightset_id: keep NULL (legacy placeholder).

  • p_athena_view: label stored on each reconciliation (leave default).

  • p_date_window_days: how many days the tx date can differ from the document date (±).

  • p_min_confidence: only accept matches at/above this confidence (0–100).

  • p_company_id: limit the run to a single company; NULL = all companies.

  • p_debug: if true, include timing info in the JSON.

  • p_chunk_limit: cap how many reconciliations are inserted/updated this run (after ranking). NULL = unlimited.

What it does (short)

  1. Loads unreconciled transactions and documents (optionally for one company).

  2. Generates candidates (amount / invoice / order / fuzzy merchant / customer / IBAN / BIC / date / currency).

  3. Scores candidates using your weight registry and computes confidence (0–100).

  4. Picks the best match per transaction, filters by p_min_confidence, then applies the global limit p_chunk_limit (if set).

  5. Inserts/updates public.reconciliations and writes detailed scores to public.reconciliations_match_scores.

  6. Returns a JSON summary.

Returns (example) 

{
  "reconciliations_processed": 123,
  "status": "ok",
  "message": "success",
  "processing_time": 4567,
  "timings": { "...": 123 },
  "chunk": { "limit": 250, "applied": true, "selected": 250 }
}

Example calls

Unlimited 

SELECT public.athena_reconcile_general_v13(
  p_weightset_id   => NULL,
  p_company_id     => NULL,
  p_min_confidence => 50
);

Cap to 250 (global) 

SELECT public.athena_reconcile_general_v13(
  p_weightset_id   => NULL,
  p_company_id     => NULL,
  p_min_confidence => 50,
  p_chunk_limit    => 250
);

Single company + cap to 10 

SELECT public.athena_reconcile_general_v13(
  p_company_id  => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac',
  p_chunk_limit => 10
);

reconciliations_staging_update_v1 — Simple Docs

Update one staging flag or set verified (with cascade). Returns API-shaped JSON.

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,   -- set/unset verified + cascade
  p_company_id        uuid    DEFAULT NULL,   -- passthrough (unused)
  p_organization_id   uuid    DEFAULT NULL,   -- passthrough (unused)
  p_user_id           uuid    DEFAULT NULL,   -- passthrough (unused)
  p_publish_event     boolean DEFAULT true    -- passthrough (unused)
) RETURNS jsonb;

What it does

  • If p_column is given: sets that one column on reconciliations_staging.

  • If p_verified is given:

    • Updates reconciliations_staging.verified and reconciliations.verified.

    • For linked documents_v2 / transactions: sets reconciled = p_verified and sets/clears reconciliation_id.

    • If setting true with no linked doc/tx → {"ok": false, "error": "no_linked_records"}.

Examples 

-- Toggle a flag
SELECT public.reconciliations_staging_update_v1(
  '67433724-acc3-4ddf-b838-f63736f03ef3', 'flagged', true, NULL
);

-- Unverify (cascade)
SELECT public.reconciliations_staging_update_v1(
  '67433724-acc3-4ddf-b838-f63736f03ef3', NULL, NULL, false
);

reconciliations_weight_registry_upsert_v2 — Simple Docs

Create/update a company/merchant-specific weight, or deactivate it.
Safe for frontend: cannot modify default/global registry rows.

Signature 

SELECT public.reconciliations_weight_registry_upsert_v2(
  p_weight_key   text,                      -- 'date','invoice_number','order_number','merchant_id','amount_total','currency','customer_name'
  p_weight_value numeric,                   -- >= 0
  p_company_id   uuid,                      -- required; protects global defaults
  p_merchant_id  text    DEFAULT NULL,      -- NULL = company-level weights
  p_active       boolean DEFAULT true       -- false = deactivate existing row
) RETURNS public.reconciliations_weight_registry;

What it does

  • Upsert active weight for (company, optional merchant, key).

  • If p_active = false: deactivate the existing row (error if it doesn’t exist).

  • Rejects writes to the default/global registry (where both company + merchant are NULL).

Examples 

-- Set invoice_number weight = 2 at company level
SELECT public.reconciliations_weight_registry_upsert_v2(
  p_weight_key   => 'invoice_number',
  p_weight_value => 2,
  p_company_id   => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac',
  p_merchant_id  => NULL,
  p_active       => true
);

-- Deactivate that weight
SELECT public.reconciliations_weight_registry_upsert_v2(
  p_weight_key   => 'invoice_number',
  p_weight_value => 2,
  p_company_id   => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac',
  p_merchant_id  => NULL,
  p_active       => false
);

Notes

  • athena_reconcile_general_v13 honors your weight registry overrides in this order: merchant → company → global defaults.

  • p_chunk_limit caps how many best matches get finalized this run, after sorting by confidence and your tiebreakers.

© 2025 Suitsbooks