athena_reconcile_v10

What it does (in plain English)

Matches unreconciled transactions to unreconciled documents_v2 for the same company and writes results to:

• reconciliations (one best document per transaction) - FIXME: Will change going forward

• reconciliations_match_scores (why it matched: per-signal scores/flags)

It uses a simple weighted scoring system you control via reconciliations_weight_registry.
Fuzzy logic is applied for merchant names and customer names.

Inputs (function params)

• p_weightset_id uuid — ignored (kept for signature compatibility).

• p_athena_view text — label stored in reconciliations.athena_view. Default: function name.

• p_date_window_days numeric — allowed date difference (± days). Default: 1.

• p_min_confidence numeric — only insert if confidence ≥ this. Default: 0.

• p_company_id uuid — filter to a single company (much faster). Default: NULL (all).

• p_debug boolean — include stage timings in the JSON result. Default: false.

Returns: jsonb with keys:

• reconciliations_processed (int)

• status (ok/error)

• message (text)

• processing_time (ms)

• timings (jsonb; present when p_debug=true)

Signals used to match

From transactions (tx) vs documents_v2 (doc):

Normalization helpers (see below) strip noise and standardize fields.

Weights & confidence

Effective weights are resolved per candidate with fallback:

merchant-specific  →  company-level  →  global base
(_reg_merchant)       (_reg_company)    (_reg_base)

Supported weight keys in reconciliations_weight_registry.weight_key:

'date','invoice_number','order_number','merchant_id',
'amount_total','currency','customer_name'

Confidence (%) = 100 × (sum of weights for matched signals) ÷ (sum of all applicable weights).
Only rows with confidence ≥ p_min_confidence are eligible.
Tie-breakers for selecting the single best doc per tx:

1. Higher confidence

2. “All but currency” met (numerator ≥ total − currency_weight)

3. Smaller date difference

4. Lower document_id (as a deterministic final tie)

What tables it reads

• public.transactions (unreconciled only)

• public.documents_v2 (unreconciled only)

• public.reconciliations_weight_registry (for weights)

What tables it writes

• public.reconciliations

  • Inserts one row per (best transaction, document) if not already present.

  • Sets: athena_view, confidence, company_id, transaction_id, document_id, fiscal_year, quarter, month.

  • weightset_id is written as NULL (not used anymore).

• public.reconciliations_match_scores

  • Upserts by reconciliation_id with per-signal flags and weights:
    date_match/invoice_match/order_match/merchant_match/amount_match/currency_match/customer_match
    and *_weight, *_score, plus numerator, total_weight, confidence.

The function is idempotent w.r.t. duplicates: it NOT EXISTS before inserting into reconciliations, and ON CONFLICT updates the score row.

Helper functions (quick reference)

• norm_core_ns(text) → text
  Company/merchant-style name normalizer.
  Steps: UPPER → strip non [A-Z0-9 ] → collapse spaces → drop legal suffixes
  (INC|LTD|LLC|CO|COMPANY|CORP|BV|GMBH|SA|PLC|...) → remove spaces.

• norm_code(text) → text
  Code normalizer for invoice/order.
  Steps: UPPER → keep only [A-Z0-9] → empty → NULL.

• norm_text_ns(text) → text
  Person/customer text normalizer.
  Steps: UPPER → strip non [A-Z0-9 ] → collapse spaces → remove spaces.

• parse_doc_date(text, bigint) → bigint (unix seconds)
  If p_unix provided, returns it. Else parses:
  13-digit ms epoch, 10-digit s epoch, ISO YYYY-MM-DD[...], YYYY-Mon-DD, YYYY-Month-DD, DD/MM/YYYY, MM/DD/YYYY.

All helpers are IMMUTABLE (SQL) or STABLE (PL/pgSQL) and safe to index/inline.

Weight registry usage

Table: public.reconciliations_weight_registry

• Scope is determined by (company_id, merchant_id):

  • NULL, NULL → global base

  • company_id, NULL → company-level

  • company_id, 'some merchant key' → merchant-specific

Example upserts

-- Global defaults
INSERT INTO public.reconciliations_weight_registry(company_id, merchant_id, weight_key, weight_value, active)
VALUES (NULL, NULL, 'amount_total', 1), (NULL, NULL, 'merchant_id', 1),
       (NULL, NULL, 'currency', 0.5),  (NULL, NULL, 'date', 0.5),
       (NULL, NULL, 'invoice_number', 1.1), (NULL, NULL, 'order_number', 1.1),
       (NULL, NULL, 'customer_name', 0.5)
ON CONFLICT (company_id, merchant_id, weight_key)
DO UPDATE SET weight_value = EXCLUDED.weight_value, active = TRUE;

-- Company override
INSERT INTO public.reconciliations_weight_registry(company_id, merchant_id, weight_key, weight_value)
VALUES ('<company_uuid>'::uuid, NULL, 'customer_name', 0.8)
ON CONFLICT (company_id, merchant_id, weight_key)
DO UPDATE SET weight_value = EXCLUDED.weight_value, active = TRUE;

-- Merchant override (per company)
INSERT INTO public.reconciliations_weight_registry(company_id, merchant_id, weight_key, weight_value)
VALUES ('<company_uuid>'::uuid, 'DISCORD', 'merchant_id', 1.5)
ON CONFLICT (company_id, merchant_id, weight_key)
DO UPDATE SET weight_value = EXCLUDED.weight_value, active = TRUE;

(If you created a dedicated upsert function earlier, you can use that instead.)

How to run it

-- All companies, default window (±1 day), no confidence floor
SELECT public.athena_reconcile_general_v10();

-- All companies, default window (±1 day), 40% confidence floor
SELECT public.athena_reconcile_general_v10(p_min_confidence   => 40);

-- One company (faster), 2-day window, require ≥ 60% confidence
SELECT public.athena_reconcile_general_v10(
  p_date_window_days => 2,
  p_min_confidence   => 40,
  p_company_id       => '<company_uuid>'::uuid
);

-- With profiling output
SELECT public.athena_reconcile_general_v10(p_debug => true);

Temporary objects it creates (and auto-drops at session end)

_reg_base, _reg_company, _reg_merchant, _tx, _docs,
_cand_amount/_cand_invoice/_cand_order/_cand_merchant/_cand_customer/_cand_date/_cand_currency,
_cand, _rw, _scored, _conf, _best.
Each has targeted indexes to speed joins.

Notes & gotchas

• Works only on rows where reconciled=false in both sources.

• Merchant/customer fuzzy matching depends on normalized text; unexpected punctuation/casing is handled by the helpers.

• If you store document_date_unix, it will be used directly (fastest path).

• weightset_id is written as NULL (legacy field kept for compatibility).