Pi Recon

At low transaction volumes, reconciliation is done manually. A finance analyst exports a report from the payment system and compares it, row by row, against the provider's settlement file. This works — badly — at low volumes. At any meaningful scale, it fails for several reasons.

It is slow. A team of analysts working on a large batch can take days to clear a month-end reconciliation. During that time, discrepancies are invisible, chargebacks may go unnoticed, and financial reporting is delayed.

It is error-prone. Human eyes miss things. When comparing two datasets with slight differences in formatting, date representation, and field naming conventions, errors in one direction are indistinguishable from errors in the other direction without careful cross-checking.

It does not scale. Hiring more analysts is the only way to increase throughput. As transaction volumes grow, the cost of reconciliation grows linearly — and the error rate grows too, because the same analysts are now under more pressure.

The first automation attempt most businesses make is building a set of rules: if the transaction ID matches and the amount matches, it is reconciled. These rules resolve the straightforward cases quickly, which is a significant improvement. But rule systems break down at the edges.

Real-world reconciliation data is imperfect. Provider settlement files have date format inconsistencies. Currency conversions introduce fractional rounding differences. Time zones shift dates by a day. Provider IDs are represented as names in one system and as numeric codes in another. A rigid rule system that requires exact matches will reject a large proportion of transactions that are genuinely the same payment, simply because one field is formatted slightly differently.

The alternative — relaxing the rules to be more permissive — introduces false positives: matches that look correct on the rule criteria but are actually different transactions. Both outcomes are costly.

Recon combines a structured, tiered rule engine with an AI matching layer. The rule engine handles the clear cases fast, deterministically, and with full auditability. The AI engine handles the ambiguous residual cases using weighted field scoring and an LLM that produces a human-readable explanation for every decision.

Every outcome is recorded with a tier, a rule label, an engine identifier, and a confidence score. Operations teams and finance analysts can see exactly how every transaction was classified and why, without any manual comparison required.

The rule engine evaluates every incoming transaction through a strict, ordered hierarchy of five matching rules. Rules are applied in priority order — a transaction is assigned to the highest tier it qualifies for.

Tier 1 — Perfect match:
Transaction ID matches exactly. Amount matches exactly. Currency matches exactly. Date matches exactly (same calendar day). This is the strongest possible match. Tier 1 transactions are written to recon_internal_transactions with recon_status = matched and a confidence score of 100.

Tier 2 — Date-tolerant match:
Transaction ID matches exactly. Amount matches exactly. Currency matches. Date is within ±30 days of the reference. This handles date discrepancies caused by cut-off timing, time zone differences between provider and platform, or batch processing delays. Written to recon_internal_transactions.

Tier 3 — Amount-tolerant match:
Transaction ID matches. Currency matches. Amount is within 1% of the reference amount. Date is the same day or within ±30 days. This handles minor rounding differences — the most common source of which is currency conversion at slightly different exchange rates. Written to recon_internal_transactions.

Tier 4 — Weak match, staged:
Transaction ID matches. Currency matches. Amount is within 2% of the reference. Date is the same day or within ±30 days. This covers larger rounding or fee deduction discrepancies. Because the confidence is lower, Tier 4 transactions are written to recon_staging_raw_transactions for further review by the AI engine.

Tier 5 — ID not found:
The transaction ID does not exist in the reference ledger at all. This could indicate a transaction that was processed by the acquirer but not recorded internally, a data entry error, or a genuinely fraudulent charge. Written to recon_staging_raw_transactions.

Uncategorized — Provider or method mismatch:
The transaction ID exists in the reference ledger, but the provider or payment method recorded in the incoming file does not match the reference. This typically indicates a data integrity issue — a transaction being attributed to the wrong provider. Written to recon_staging_raw_transactions.

A critical design decision of the rule engine is that provider identity and payment method are prerequisites for rule evaluation, not a tier in themselves. If the provider or payment method does not match the reference, the transaction is uncategorized regardless of how well the other fields match. This prevents misleading matches across different providers for the same transaction ID.

Transactions that the rule engine could not resolve cleanly — Tier 4, Tier 5, and Uncategorized — are processed by the AI engine as a second pass.

The AI engine uses a weighted field scoring model across 10 dimensions:

For each staged transaction, the AI engine fetches all reference transactions within a ±30-day window, scores each against the staged record using these weights, and selects the best candidate using a hierarchical field-match selection algorithm. The algorithm starts with the highest-weight field (transaction ID) and progressively narrows the candidate set — if multiple candidates still remain after all fields are evaluated, the one with the highest score, smallest amount difference, and smallest time difference wins.

Once the best candidate is identified, the AI engine calls Claude (claude-3-5-haiku) to generate a brief, human-readable explanation of the match decision. The explanation cites the specific fields that matched, the fields that did not, the amount difference if any, and the overall confidence score. This explanation is stored in match_description on the staging record and is available for audit and review.

The rule engine accepts transaction data in four different input formats, handling whichever the calling system provides:

CSV upload (base64-encoded): The client sends the CSV file content as a base64 string in the JSON request body, along with a filename. The rule engine decodes the CSV, writes it to S3 with AES-256 encryption, then processes each row.

S3 URI: The client provides the S3 URI of a file already in object storage (s3://bucket/path/to/file.csv). The rule engine reads directly from S3 and processes the file.

Bucket and key: The client provides the S3 bucket name and key separately. Equivalent to the S3 URI mode.

Single record: The client provides a single transaction as a JSON object with at minimum a transaction ID. The rule engine processes and categorises it immediately and returns the result inline.

The S3 event trigger is also supported — the rule engine can be invoked directly by an S3 ObjectCreated event when a file lands in the designated bucket, enabling fully automated reconciliation without any API call from the client system. EventBridge, SNS, and SQS fan-out patterns are all supported as event sources.

After processing a CSV batch — whether uploaded directly or read from S3 — the rule engine automatically triggers the AI engine as a fire-and-forget call. The client does not need to call the AI engine separately. The AI engine runs asynchronously in the background and updates staged records without blocking the rule engine's response.

This design ensures that the reconciliation pipeline is fully self-contained from the client's perspective. The client submits a file, the rule engine responds immediately with a summary of how many rows were processed and how many succeeded, and the AI engine continues working in the background on the harder cases.

Every reconciled record is stored with a numeric confidence score:

• Score 100 — Tier 1, 2, or 3 matches (rule engine, strong deterministic match)
• Score 60 — Tier 4, Tier 5, or Uncategorized (written to staging, pending AI resolution)
• Score 0–100 (variable) — AI engine matches; the score is the weighted field match score out of 100

The confidence score gives downstream systems and operations teams a way to prioritise review. A batch with 4,700 records at confidence 100 and 40 records with a confidence score below 50 tells the finance team exactly where to focus their attention.

Both the rule engine and the AI engine normalise provider identifiers automatically. Providers may arrive in the incoming file as numeric IDs or as name strings. The engines resolve numeric IDs to canonical provider names via the payment_providers table and normalise all provider names to lowercase for consistent comparison. Resolved names are cached within the Lambda execution environment to avoid repeated database lookups for the same provider within a single batch.

Real settlement files are not clean. Recon is built to handle common data quality problems without failing:

Missing transaction IDs — if a row in the CSV has no ID field, the rule engine generates a deterministic SHA-1 hash from the available fields (client ID, checkout ID, amount, currency, date, filename, row index) and uses that as the transaction identifier. The row is processed and stored normally.

Ambiguous date formats — the rule engine accepts dates in ISO 8601, epoch timestamps (seconds and milliseconds), and a range of common day-month-year formats. Timezone-naive timestamps are treated as UTC.

Gzip-compressed CSV files — files with a .gz extension or Content-Encoding: gzip are decompressed transparently before processing.

CSV encoding — UTF-8 with BOM (utf-8-sig) is supported, handling files exported from Excel without manual pre-processing.

Duplicate rows — the database insert uses ON CONFLICT DO NOTHING, so submitting the same file twice will not create duplicate records.

The rule engine API is protected by Cognito-based JWT authorisation for authenticated client requests. The AI engine is protected by API key authentication scoped to the recon product. Requests without valid credentials are rejected before any processing begins. S3-triggered invocations bypass the authentication layer, as they originate from an AWS-internal event source.

All reconciliation processing is scoped to the authenticated client. Client IDs extracted from JWT claims are used to scope S3 key prefixes, so files from one client are stored in a separate path from files from another client. The database records include the client ID, allowing queries to be scoped without risk of cross-client data exposure.

All API communication is over encrypted connections. Database connections enforce SSL in transit. S3 objects are stored with AES-256 server-side encryption (ServerSideEncryption: AES256). The AI engine's Claude API call is made over HTTPS.

Database credentials, the Claude API key, and the Recon internal API key are all retrieved at Lambda startup from a centralised secrets management service. They are held in memory only, never logged, and never returned in error responses or API payloads.

Both the rule engine and the AI engine produce structured log entries for every significant event:

Rule engine logs:

• Every invocation (request received, auth result, ingestion mode detected)
• Every file uploaded to S3 (bucket, key, client ID)
• Every batch processed (row count, success count, error count, AI engine trigger status)
• Every single-record categorisation (tier, rule applied)
• AI engine trigger sent confirmation
• Every unhandled exception with full traceback

AI engine logs:

• Every invocation
• Candidate fetch results (count, time window, staging record details)
• Sample of top candidates evaluated (with scores and field matches)
• Best candidate selected (with score, field match breakdown, amount and time differences)
• Claude request and response (model, client ID, token counts)
• Each batch record result (matched, score, candidate ID, reason)
• Persist success or failure for each record

The AI engine has a configurable debug log mode (AI_ENGINE_DEBUG environment variable, default: enabled). When debug mode is on, the engine logs detailed intermediate state at each step: the candidates fetched, the scoring applied to each candidate, the hierarchical selection process, the Claude request payload, and the final decision. This gives full visibility into why a specific staging record was matched to a specific reference transaction.

Every Claude API call made by the AI engine is logged to a dedicated LLM cost tracking endpoint with input tokens, output tokens, model name, and client ID. This provides per-client AI cost attribution and allows monitoring of LLM usage trends over time.

The rule engine returns clear error responses for known failure modes:

• Missing input fields (missing_filename, missing_client_id, missing_input)
• Configuration problems (missing_bucket)
• Internal errors with a traceback in logs

The AI engine validates required table environment variables at startup and returns a configuration error if they are missing, rather than producing cryptic SQL errors at runtime.