api.suitsbooks.nlGuideReference

reconciliations_weight_registry_upsert

What it does
Creates or updates a single weight in public.reconciliations_weight_registry.
If a row with the same (company_id, merchant_id, weight_key) already exists, it updates weight_value and active; otherwise it inserts a new row. Returns the final row.

Table it touches

  • Writes/reads: public.reconciliations_weight_registry

    • Key columns: id (identity PK), company_id uuid, merchant_id text, weight_key text, weight_value numeric, active boolean

    • Unique key: (company_id, merchant_id, weight_key)

  • No other tables affected.

Scope model (how rows are used by reconciliation)

  • company_id = NULL and merchant_id = NULL → Global default

  • company_id = <uuid> and merchant_id = NULL → Company-level

  • company_id = <uuid> and merchant_id = '<name/id>' → Merchant-level (highest precedence)

Signature

reconciliations_weight_registry_upsert(
  p_weight_key   text,         -- required
  p_weight_value numeric,      -- required (>= 0)
  p_company_id   uuid    DEFAULT NULL,
  p_merchant_id  text    DEFAULT NULL,
  p_active       boolean DEFAULT true
) RETURNS public.reconciliations_weight_registry

Accepted p_weight_key values

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

Validation & behavior

  • p_weight_value must be non-negative.

  • Empty p_merchant_id ('') is treated as NULL (company scope).

  • Unknown p_weight_key → error.

  • Idempotent: calling again with same key/scope updates the row.

Common examples

Global defaults

-- Set a global default for invoice number weight
SELECT reconciliations_weight_registry_upsert('invoice_number', 1.2);

-- Set a global customer_name weight
SELECT reconciliations_weight_registry_upsert('customer_name', 0.5);

Company-level

-- Company-specific amount weight
SELECT reconciliations_weight_registry_upsert(
  p_weight_key   => 'amount_total',
  p_weight_value => 1.0,
  p_company_id   => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac'
);

Merchant-level (within a company)

-- Heavier weight for Discord merchant matching at this company
SELECT reconciliations_weight_registry_upsert(
  p_weight_key   => 'merchant_id',
  p_weight_value => 1.5,
  p_company_id   => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac',
  p_merchant_id  => 'DISCORD'
);

Deactivate a rule (without deleting)

SELECT reconciliations_weight_registry_upsert(
  p_weight_key   => 'order_number',
  p_weight_value => 0,  -- value can be anything; 'active' controls usage
  p_company_id   => 'ded3d471-ddaf-4cc1-8d42-16346c05d5ac',
  p_active       => false
);

What it returns

  • The full row (all columns) from public.reconciliations_weight_registry after insert/update.

Errors you might see

  • 22023: invalid p_weight_key (not in the allowed list).

  • 22003: p_weight_value is NULL or negative.

Tips

  • Use merchant-level rows sparingly; they override company/global values.

  • If you want to “turn off” a signal for a scope, either set a low weight (e.g., 0) or set active=false.

  • Keep a small set of global defaults, then layer company/merchant specifics only when needed.

© 2025 Suitsbooks