Free-Text Payment Memos → Structured References

View on GitHub

What

Pull the structured tokens a downstream ledger needs — an invoice number, an order reference, a has-any-reference flag — out of free-text payment memos, where each token sits at a variable position inside an otherwise human-written sentence. Done with REGEX_EXTRACT / REGEX_MATCH, which match by shape, anywhere in the string.

Synthetic / teaching example

The data is constructed, not sourced — sample.csv is hand-written memos that pack a moving-target token into a sentence. The problem class — bank / PSP / ERP exports whose only machine-readable handle is a free-text "reference" or "memo" field — is universal; the rows are not.

Why interesting

This is the one extraction job the cheaper string tools cannot express. SPLIT_PART needs a stable delimiter at a fixed position; CONTAINS only tests presence; IN / REMAP only match whole values. When the invoice number can appear as "Payment for INV-2024-0042 thank you" in one row and "Refund INV-2023-0911 order #88 processed" in the next, only a pattern match pinned to the token's shape reaches it. The example also teaches the flip side: the Tags column is cleanly a|b|c-delimited, so its first tag is a plain SPLIT_PART — reaching for regex there would be paying the engine for a job a delimiter split already does.

The trick

(see sample.json)

1. Capture group — REGEX_EXTRACT([Memo], 'INV-([0-9]{4}-[0-9]{4})') returns just the inner YYYY-NNNN (the group), dropping the INV- literal; no match → "".
2. Different anchor — REGEX_EXTRACT([Memo], '#([0-9]+)') pulls the order number that follows a #, wherever it lands.
3. Alternation gate — REGEX_MATCH([Memo], 'INV-[0-9]{4}|#[0-9]+') answers "does this memo carry any structured reference" in a single pass; CONTAINS would need two calls and would still accept a bare INV- with no digits.
4. Cost-hierarchy contrast — SPLIT_PART([Tags], '|', 1) for the already-delimited tag. No regex on purpose.

The cost ladder is deliberate: IN/REMAP (hash) < CONTAINS/REPLACE (literal scan) < regex (pattern engine). Pick the cheapest tool that does the job; regex earns its keep only on a real pattern the others cannot phrase.

Final result

A pile of free-text memos —

T001,Payment for INV-2024-0042 thank you,priority|cleared|eu
T005,Refund INV-2023-0911 order #88 processed,low|cleared|us
T003,Card settlement no reference here,normal|pending|us

— becomes a clean, joinable reference table, each token lifted out by shape:

T001,2024-0042,,true,priority
T005,2023-0911,88,true,low
T003,,,false,normal

At full scale

Regex is the most expensive rung of the ladder, so it is worth knowing the price. On this same workload — but scaled to 1,000,000 synthetic memo rows (~62 MB) — the regex template (two REGEX_EXTRACT + one REGEX_MATCH per row) was measured against a literal-only template that produces byte-identical output by leaning on the stable INV- / # anchors (CONTAINS + nested SPLIT_PART):

Template (1M rows)	Wall	Peak RSS	Throughput
regex (2× REGEX_EXTRACT + REGEX_MATCH)	~2.4 s	~23 MB	~415k rows/s
cheap (CONTAINS + SPLIT_PART)	~1.3 s	~23 MB	~790k rows/s

So the regex path costs roughly 1.9× the wall time here — about +1 µs per row for the three pattern ops — while peak RSS is flat (the Pike-VM engine is window/arena-bounded, with no per-row growth). The takeaway matches the cost ladder above: regex is cheap enough to use freely when you need shape matching, and still worth skipping when a delimiter split or a literal CONTAINS already answers the question.

Reproduce it — the 1M-row file is generated rather than committed, so this directory ships only the six-row teaching slice:

bash make-full.sh                 # writes ./full/memos.csv (~62 MB)
bxp-cli --config full.json        # the regex template
bxp-cli --config full-cheap.json  # the literal-only template
diff full/memos.csvx full/memos-cheap.csvx && echo identical

The diff is not decoration: a timing comparison between two templates only means anything once they are proven to produce the same answer.

Methodology: best of five interleaved runs, bxp-cli built ReleaseFast, BXP_METRICS=1 self-reported wall + peak RSS. Absolute milliseconds vary by machine; the ratio and the flat-RSS shape are the portable takeaways.

Note what the cheap template needs and the teaching slice deliberately withholds: stable literal anchors. make-full.sh emits memos that always spell INV- and #, which is what lets CONTAINS + SPLIT_PART reach the tokens at all. Free text in the wild does not promise that — which is the reason the example itself uses regex.

Sample data

Run it with bxp-cli --config ./sample.json --template freeform_payment_memos:

sample.json (config)sample.csvsample.csvx (result)

{
  // Teaching example (advanced) — synthetic data. Pull structured tokens out of
  // FREE-TEXT payment memos, where the token a downstream system needs (an
  // invoice number, an order reference) sits at a *variable* position inside an
  // otherwise human-written sentence. This is the one job the cheaper string
  // tools cannot express: SPLIT_PART needs a stable delimiter+position, CONTAINS
  // only tests presence, IN/REMAP only match whole values. REGEX_EXTRACT /
  // REGEX_MATCH match by *shape*, anywhere in the string.
  //
  // The deliberate contrast: the `Tags` column IS cleanly delimited ("a|b|c"),
  // so its first tag is pulled with SPLIT_PART — NOT regex. Reach for the regex
  // engine only when a cheaper tool cannot do the job (IN/REMAP < CONTAINS/
  // REPLACE < regex); paying for it on already-delimited data is waste.
  conversion_templates: {
    freeform_payment_memos: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",

      input_schema: {
        $txn_id:  "[TxnId]",

        // 1 — capture group: pull the invoice number out of the memo wherever
        // it sits. The group (...) returns just the inner YYYY-NNNN, dropping
        // the "INV-" literal. No match → "".
        $invoice: "REGEX_EXTRACT([Memo], 'INV-([0-9]{4}-[0-9]{4})')",

        // 2 — capture group with a different anchor: the order ref follows a
        // '#'. Position varies row to row, so SPLIT_PART cannot reach it.
        $order:   "REGEX_EXTRACT([Memo], '#([0-9]+)')",

        // 3 — alternation gate: "does this memo carry ANY structured reference"
        // — either an INV-YYYY code OR a #NNNN order — answered in one pass.
        // CONTAINS would need two calls and still match a bare "INV-" with no
        // digits; the pattern requires the digits.
        $has_ref: "REGEX_MATCH([Memo], 'INV-[0-9]{4}|#[0-9]+')",

        // 4 — the cheap-tool contrast: Tags is "a|b|c", cleanly delimited, so
        // the first tag is a plain SPLIT_PART. No regex — that would be paying
        // the engine for a job a delimiter split already does.
        $priority: "SPLIT_PART([Tags], '|', 1)"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        txn_id:    "$txn_id",
        invoice:   "$invoice",
        order_ref: "$order",
        has_ref:   "$has_ref",
        priority:  "$priority"
      }
    }
  }
}

TxnId,Memo,Tags
T001,Payment for INV-2024-0042 thank you,priority|cleared|eu
T002,SEPA credit ref order #12345 from ACME,normal|cleared|eu
T003,Card settlement no reference here,normal|pending|us
T004,Monthly wire transfer to supplier,priority|pending|eu
T005,Refund INV-2023-0911 order #88 processed,low|cleared|us
T006,Misc cash adjustment,low|cleared|eu

txn_id,invoice,order_ref,has_ref,priority
T001,2024-0042,,true,priority
T002,,12345,true,normal
T003,,,false,normal
T004,,,false,priority
T005,2023-0911,88,true,low
T006,,,false,low

Scale files (the 1M-row cost comparison): make-full.sh · full.json · full-cheap.json.