athena_reconcile_general_v1_9_i (v1.9i)

Signature

CREATE OR REPLACE FUNCTION public.athena_reconcile_general_v1_9_i (
  p_company_id uuid,
  p_reconciliation_config_id uuid DEFAULT '3dae610e-2326-46b8-ba18-85f54961e4ca'
) RETURNS jsonb

Requires

• Tables: public.documents_v2, public.transactions, public.reconciliations, public.reconciliations_match_scores, public.reconciliations_logs, public.reconciliations_config, public.reconciliations_weight_registry.

• Helper functions: public.norm_code(text), public.norm_core_ns(text), public.norm_text_ns(text), public.norm_iban(text).

1) Simple Lifecycle

1. Load config and weights.

2. Build document scope and transaction scope.

3. Generate candidate pairs by heuristic joins.

4. Compute match signals (amount, invoice, order, date, currency, merchant, customer).

5. Score signals with weights; apply directional denominator and tolerance.

6. Filter by min confidence; pick top per transaction then top per document.

7. Insert reconciliations and per-signal scores; log run summary.

8. Return run stats as JSON.

Directional matching

• Outgoing payments → merchant-side evidence counts.

• Incoming payments → customer-side evidence counts.

• Symmetric mode exists in code but v_directional_matching = TRUE is set.

2) Advanced Lifecycle

2.1 Config

• From reconciliations_config using p_reconciliation_config_id.

• Keys: amount_tolerance, date_window_days, min_confidence
allow_doc_duplicates (unused for now)
  log_activity (unused for now)

• Tolerance: if >=1 → interpret as percent (e.g. 2 → 2%). Else already a fraction.

• min_confidence normalized to percent [0–100].

2.2 Weights

• Pulled from reconciliations_weight_registry for keys:
  amount_total, invoice_number, order_number, date, currency, merchant_id, customer_name.

• Company-specific entries override global. If none found, defaults to 1 each.

• v_weights_total_possible used only for logging; dynamic denominator is recalculated later.

2.3 Scopes

• document_scope: unreconciled documents for company; normalizes fields:

  • invoice_number_norm, order_id_norm via norm_code.

  • document_date_unix parsed from multiple formats.

  • doc_merchant_ns from merchant names; doc_customer_ns from customer info; doc_iban_ns with O→0 then norm_iban.

  • doc_dedup_key uses dedup_serialized else document_id.

• transactions_scope: recent unreconciled transactions (limit v_max_transactions_to_scan = 20000), normalizes:

  • Extracts implicit invoice and order ids from texts.

  • Determines direction flags tx_is_in, tx_is_out.

  • tx_merchant_ns, tx_customer_ns; IBANs: tx_iban_beneficiary_ns, tx_iban_counterparty_ns with O→0 before normalization.

  • effective_amount resolved from debited/credited/amount.

2.4 Candidate generation

Heuristics join each transaction to documents of same company using any of:

• Exact invoice or order id match.

• Currency + merchant_id or currency + consumer name match (directional guarded).

• Name-similarity NS match within date window (merchant for outgoing, customer for incoming).

• IBAN equality within date window (counterparty for outgoing, beneficiary for incoming).

• Amount equality or tolerance by direction.

2.5 Scoring

• Compute booleans per signal.

• Amount scoring: exact → full weight; tolerance → linear decay toward the tolerance bound.

• Date scoring: full weight if within grace 30 days; then linear decay to the window bound.

• Other signals: binary weight on/off.

• Applicability: merchant and customer signals only counted if their side has usable data and matches the direction.

• Dynamic denominator: sum of weights for all applicable signals only.

• Confidence: round( numerator / dynamic_denominator * 100, 4 ).

2.6 Ranking

• Filter: at least 1 signal and confidence >= min_confidence.

• Rank per transaction by (confidence desc, numerator desc, matched_signals desc, document_id) → keep row 1.

• Then rank per document dedup key with tie-breakers also using nearest date; keep row 1.

2.7 Insert

• Insert winners into public.reconciliations with a JSON summary snapshot containing:

  • matched signals, amounts, normalized ids, currencies, merchant/customer ids and names,

  • unix dates, IBANs, and direction flags.

• Insert per-signal rows into public.reconciliations_match_scores.

• Write reconciliations_logs entries for run_start, run_end, and any run_error.

2.8 Errors

• Catches all exceptions, logs diagnostic fields from GET STACKED DIAGNOSTICS, returns JSON {msg:'error', ...}.

3) Directional rules

• tx_is_out → prioritize merchant evidence:

  • merchant_id, merchant NS containment, or tx_iban_counterparty == doc_iban.

• tx_is_in → prioritize customer evidence:

  • customer NS containment, or tx_iban_beneficiary == doc_iban.

• Amount comparison follows direction: debited for out, credited for in.

• If v_directional_matching flipped to FALSE then both sides are symmetric.

4) Normalization

• norm_code strips casing and separators for invoice/order-like tokens.

• norm_core_ns and norm_text_ns slim and unify names for fuzzy containments.

• norm_iban uppercases, strips spaces; pre-fix converts letter O to zero 0.

5) Configuration map

Performance knobs

• v_max_transactions_to_scan default 20000.

• v_date_full_weight_grace_days default 30.

6) Signals and weights

7) Return value

{
  "msg": "success",
  "time_ms": 1234,
  "top_per_tx": 8,
  "top_per_doc": 6,
  "passed_confidence": 6
}

On error, includes fields: error, sqlstate.

8) Example calls

8.1 Default config

SELECT public.athena_reconcile_general_v1_9_i('11111111-111111-1111-1111-1111111111');

Expected: JSON with counts. Inserts appear in public.reconciliations and public.reconciliations_match_scores. Logs in public.reconciliations_logs.

8.2 Custom config

SELECT public.athena_reconcile_general_v1_9_i(
  '11111111-1111-1111-1111-111111111111',
  '3dae610e-2326-46b8-ba18-85f54961e4ca'  -- your tuned profile
);

8.3 Inspect winners

SELECT r.reconciliation_id, r.transaction_id, r.document_id, r.confidence, r.summary
FROM public.reconciliations r
WHERE r.engine_version = '1.9i'
ORDER BY r.confidence DESC
LIMIT 20;

8.4 Inspect match components

SELECT *
FROM public.reconciliations_match_scores s
JOIN public.reconciliations r USING (reconciliation_id)
WHERE r.engine_version = '1.9i'
ORDER BY s.confidence DESC
LIMIT 20;

8.5 Logs for a run

SELECT time, action, target, status, message, context
FROM public.reconciliations_logs
WHERE run_id = (
  SELECT run_id FROM public.reconciliations_logs
  WHERE action = 'run_start'
  ORDER BY time DESC
  LIMIT 1
);

9) Worked examples (synthetic)

Example A — Outgoing with merchant + amount tolerance

• Transaction: debited=100.00, currency EUR, tx_is_out=true, counterparty IBAN matches doc IBAN, date diff 40 days, tolerance 2%.

• Document: total 101.00 EUR, merchant NS equals, date set.

• Signals: amount tolerance, currency, merchant, date (decayed), possibly invoice/order if present.

• Confidence ≈ (amount_score + currency + merchant + date_decayed) / (sum applicable weights).

• Iban is a fallback / second indicator for merchant or customer depending on direction

• Inserted if ≥ min_confidence.

Example B — Incoming with customer + beneficiary IBAN

• Transaction: credited=500.00, tx_is_in=true, beneficiary IBAN equals doc IBAN; name containment.

• Iban is a fallback / second indicator for merchant or customer depending on direction

• Document: total 500.00 EUR.

• Signals: amount exact, currency, customer, date.

10) Troubleshooting

• No candidates: verify documents_v2 and transactions have reconciled IS NOT TRUE and currencies set.

• Low confidence: raise weights for discriminating signals, widen date_window_days, or relax min_confidence.

• Wrong direction: confirm debited/credited/money_in/money_out population and tx_is_in/tx_is_out derivation.

• IBAN mismatches: ensure upstream O→0 fix is not reintroduced; confirm norm_iban behavior.

• Invoice aliasing: confirm norm_code strips separators consistently for both doc and tx.

11) Version notes

• v1.9i

  • Fix alias bug for invoice_match.

  • Include *_customer_ns in applicability for customer-side.

  • Keep directional denominator logic.

  • IBAN O→0 normalization.

  • Rich summary payload on insert.

12) Operational checklist

1. Ensure config row exists and weights registry populated or let defaults apply.

2. Verify helper functions present and deterministic.

3. Run function for a single company_id.

4. Audit reconciliations and reconciliations_match_scores.

5. Monitor reconciliations_logs for timings and errors.

13) Field glossary

• *_ns: normalized string for containment checks.

• dynamic_total_weight: denominator used in confidence after applying applicability and direction.

• total_numerator: sum of realized scores across signals.

• signals_matched_count: count of booleans true before thresholds.

• doc_dedup_key: groups variants of the same document for ranking.

14) Safety and idempotency

• Protects against duplicates by checking existing reconciliations on transaction_id and document_id before insert.

• Uses engine_version to segment audits by release.

15) Schema expectations (minimal)

-- reconciliations(company_id uuid, transaction_id uuid, document_id uuid,
-- confidence numeric, time bigint, engine_version text, fiscal_year int,
-- quarter int, month text, summary text)

-- reconciliations_match_scores(reconciliation_id uuid, confidence numeric,
-- numerator numeric, total_weight numeric, ... per-signal booleans/weights/scores ...)

-- reconciliations_logs(run_id uuid, function_version text, action text,
-- target text, status text, message text, company_id uuid,
-- transaction_id uuid, document_id uuid, reconciliation_id uuid,
-- rows_affected int, confidence numeric, step_ms bigint,
-- processing_ms bigint, context jsonb)

16) Maintenance notes

• Keep weight keys consistent with code. Unknown keys are ignored.

• Adjust v_max_transactions_to_scan for large tenants.

• To trial symmetric matching, toggle v_directional_matching to FALSE and re-run on a sample cohort.