What is payment reconciliation?

Payment reconciliation compares what your payment provider says it settled with what your own system recorded. Each settlement line is matched to an internal payment by the provider's transaction ID, and lines with no internal record, payments that never settled and amounts that differ are flagged as breaks for someone to investigate.

Why are webhooks not enough to keep payment records correct?

Webhooks can be delivered late, out of order, more than once or not at all if your endpoint was down past the provider's retry window. The settlement report is the provider's statement of what moved, so a daily comparison against it catches the payments your webhook handler missed or recorded wrong.

How do you make a reconciliation job safe to rerun?

Load each report keyed by a hash of the file and each line by the provider's line ID, so a second load inserts nothing. Record breaks with a unique key on kind and transaction ID, and resolve them with updates that only touch open breaks. A rerun then only adds what changed since the last run.

How long should you wait before flagging a payment that has not settled?

Use the provider's settlement delay plus a margin: if charges usually settle in two business days, flag a captured payment as missing from settlement after three or more days, and keep the window configurable per provider and currency.

Payment Reconciliation Backend for Fintech: Settlement Matching (2026)

A payments product records each charge when the provider's webhook says it succeeded. The provider then pays out the money days later, minus fees, and publishes a settlement report listing every transaction in that payout. If those two records disagree, the company's books are wrong: a webhook that never arrived means money landed for an order nobody shipped, and a charge that never settles means an order shipped for money that never arrived. Finance usually finds these in a spreadsheet at month end, weeks after the customer could still be contacted.

This guide builds the backend that finds them every morning: it fetches the settlement report, keeps the original in object storage, loads its lines without duplicates and matches them against internal payments, first as AWS pieces by hand and then in Go with Encore. Pave Bank built a licensed bank on Encore while keeping control of its own GCP environment, and Echo built its fintech Go backend on Encore deploying to its own AWS account.

The reconciliation backend at a glance

Job	Pattern	Hand-built on AWS	With Encore in your AWS account
Original reports	One object per file, versioning on	S3 bucket	Encore bucket on S3
Load once	Unique file hash and line ID	RDS for PostgreSQL	Encore database on RDS
Match and flag	Set-based SQL, unique break key	Same database	Same database
Daily run	Fetch, load and match each morning	Scheduled Lambda or ECS task	Cron job, Encore platform managed
Provider API key	Secret per environment	Secrets Manager plus IAM	Encore secret

How do you build it by hand on AWS?

Webhook handlers miss payments, record them twice, or see them late. The settlement report is the provider's statement of what moved, and the job of this backend is to fetch that file every morning, keep the original, load it once, and flag every line that does not match the ledger.

Fetch the report. Providers publish settlement data per payout or per day, for example Stripe's payout reconciliation report, which ties each payout to the transactions it settled. A scheduled task downloads yesterday's report with an API key from Secrets Manager, and retries with backoff when the provider has not published the file yet.
Keep the original. The raw file goes to a private S3 bucket with versioning enabled, keyed by the settle date and the file's SHA-256, so an overwrite or a later correction keeps both versions. When an auditor or the provider asks what the report said on a given day, the answer is the file, not a parsed copy.
Tables and idempotent load. The webhook handler already writes one payments row per captured charge, keyed by the provider's transaction ID, using INSERT ... ON CONFLICT (provider_txn_id) DO NOTHING so a duplicate webhook keeps the first capture. Loading the report records the file by its hash, so a retried task with the exact same bytes inserts nothing new. A later file that reuses a line ID with different amounts updates that line, then the next match pass re-evaluates the payment:

CREATE TABLE payments (
    provider_txn_id TEXT PRIMARY KEY,
    amount_minor    BIGINT NOT NULL,
    currency        TEXT NOT NULL,
    captured_at     TIMESTAMPTZ NOT NULL,
    settled_on      DATE
);

CREATE TABLE settlement_files (
    id          BIGSERIAL PRIMARY KEY,
    settle_date DATE NOT NULL,
    object_key  TEXT NOT NULL,
    sha256      TEXT NOT NULL UNIQUE
);

CREATE TABLE settlement_lines (
    line_id         TEXT PRIMARY KEY,
    file_id         BIGINT NOT NULL REFERENCES settlement_files (id),
    provider_txn_id TEXT NOT NULL,
    type            TEXT NOT NULL,
    gross_minor     BIGINT NOT NULL,
    fee_minor       BIGINT NOT NULL,
    currency        TEXT NOT NULL,
    settle_date     DATE NOT NULL
);

CREATE TABLE breaks (
    kind            TEXT NOT NULL,
    provider_txn_id TEXT NOT NULL,
    resolved_at     TIMESTAMPTZ,
    UNIQUE (kind, provider_txn_id)
);

INSERT INTO settlement_files (settle_date, object_key, sha256)
VALUES ($1, $2, $3)
ON CONFLICT (sha256) DO NOTHING;

A late webhook after the report has already been loaded is picked up on the next match run. The webhook route should verify the provider's HMAC signature over the raw body before inserting a payment, so a forged or replayed event never reaches the ledger.

Match in one transaction. SQL joins settlement charge lines to payments on the provider's transaction ID, never on amount and time, fills in settled_on when the amounts agree, and writes three kinds of break: a settlement line with no payment, an amount or currency mismatch, and a payment old enough that it should have settled but has no line. Each insert uses ON CONFLICT DO NOTHING so a second run does not open a break that is already open. Breaks close automatically once the payment has settled_on set, for example after a late webhook or the next day's report:

UPDATE payments p SET settled_on = l.settle_date
FROM settlement_lines l
WHERE l.type = 'charge' AND l.provider_txn_id = p.provider_txn_id
  AND l.gross_minor = p.amount_minor AND l.currency = p.currency
  AND p.settled_on IS NULL;

Access and review. The task role needs the secret, the bucket and a network path to RDS, and the ops screen that lists open breaks needs read access to the same database. Two overlapping morning runs should claim the day's work with FOR UPDATE SKIP LOCKED on the settlement file they are about to match, so they cannot flag and resolve the same breaks at once.

How do you build it with Encore?

Encore turns infrastructure declared in Go or TypeScript code into resources in your own cloud account. This service declares the reports bucket, the database, the provider API key and the daily schedule, and a deploy creates S3, RDS and Secrets Manager entries in your AWS account, or the GCP equivalents, as the AWS infrastructure docs list.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account. — Encore provisions the infrastructure your application needs in your own AWS or GCP account.

Set up the app

Get started

brew install encoredev/tap/encore &&

encore app create

Copy

Run in your terminal to get started locally.

$ encore app create payments --example=hello-world$ cd payments && mkdir -p reconcile/migrations$ encore secret set --type dev,local ProviderAPIKey

The migration creates the tables used above, with amounts in minor units, such as cents, to avoid floating point:

-- reconcile/migrations/1_reconcile.up.sql
-- Written by the payment webhook handler: one row per captured charge.
CREATE TABLE payments (
    provider_txn_id TEXT PRIMARY KEY,
    amount_minor    BIGINT NOT NULL,
    currency        TEXT NOT NULL,
    captured_at     TIMESTAMPTZ NOT NULL,
    settled_on      DATE
);

CREATE TABLE settlement_files (
    id          BIGSERIAL PRIMARY KEY,
    settle_date DATE NOT NULL,
    object_key  TEXT NOT NULL,
    sha256      TEXT NOT NULL UNIQUE,
    loaded_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE settlement_lines (
    line_id         TEXT PRIMARY KEY,
    file_id         BIGINT NOT NULL REFERENCES settlement_files (id),
    provider_txn_id TEXT NOT NULL,
    type            TEXT NOT NULL,
    gross_minor     BIGINT NOT NULL,
    fee_minor       BIGINT NOT NULL,
    currency        TEXT NOT NULL,
    settle_date     DATE NOT NULL
);
CREATE INDEX settlement_lines_txn ON settlement_lines (provider_txn_id);

CREATE TABLE breaks (
    id              BIGSERIAL PRIMARY KEY,
    kind            TEXT NOT NULL
                    CHECK (kind IN ('missing_in_ledger', 'amount_mismatch', 'missing_in_settlement')),
    provider_txn_id TEXT NOT NULL,
    found_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    resolved_at     TIMESTAMPTZ,
    UNIQUE (kind, provider_txn_id)
);

Load the settlement report

Load fetches one day's report, writes the original to the bucket under a key that includes its hash, then records the file and its lines. A corrected file gets a new hash and a new key, so both versions stay in the bucket. When that file reuses a known line ID with a different amount, currency or type, the insert updates the existing line and clears settled_on on the matching payment so the next Run can rematch it:

// reconcile/ingest.go
package reconcile

import (
	"bytes"
	"context"
	"crypto/sha256"
	"encoding/csv"
	"encoding/hex"
	"errors"
	"fmt"
	"strconv"
	"time"

	"encore.dev/beta/errs"
	"encore.dev/storage/objects"
	"encore.dev/storage/sqldb"
)

var db = sqldb.NewDatabase("reconcile", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

// Original settlement reports, kept as received.
var Reports = objects.NewBucket("settlement-reports", objects.BucketConfig{
	Versioned: true,
})

var secrets struct {
	ProviderAPIKey string // reporting API key for the payment provider
}

type LoadResponse struct {
	Key    string `json:"key"`
	Lines  int    `json:"lines"`
	Loaded bool   `json:"loaded"` // false if this exact file was loaded before
}

// Load fetches the settlement report for one day, stores the original
// and loads its lines. Loading the same file again is a no-op.
//
//encore:api private method=POST path=/settlements/:date
func Load(ctx context.Context, date string) (*LoadResponse, error) {
	if _, err := time.Parse("2006-01-02", date); err != nil {
		return nil, &errs.Error{Code: errs.InvalidArgument, Message: "date must be YYYY-MM-DD"}
	}
	body, err := fetchReport(ctx, secrets.ProviderAPIKey, date)
	if err != nil {
		return nil, err
	}
	rows, err := csv.NewReader(bytes.NewReader(body)).ReadAll()
	if err != nil || len(rows) == 0 {
		return nil, &errs.Error{Code: errs.InvalidArgument, Message: "report is not valid CSV"}
	}

	sum := sha256.Sum256(body)
	digest := hex.EncodeToString(sum[:])
	key := fmt.Sprintf("%s/%s.csv", date, digest[:16])

	// Keep the original before touching the database.
	w := Reports.Upload(ctx, key, objects.WithUploadAttrs(objects.UploadAttrs{
		ContentType: "text/csv",
	}))
	if _, err := w.Write(body); err != nil {
		w.Abort(err)
		return nil, err
	}
	if err := w.Close(); err != nil {
		return nil, err
	}

	tx, err := db.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var fileID int64
	err = tx.QueryRow(ctx, `
		INSERT INTO settlement_files (settle_date, object_key, sha256)
		VALUES ($1, $2, $3)
		ON CONFLICT (sha256) DO NOTHING
		RETURNING id
	`, date, key, digest).Scan(&fileID)
	if errors.Is(err, sqldb.ErrNoRows) {
		return &LoadResponse{Key: key, Lines: len(rows) - 1, Loaded: false}, nil
	} else if err != nil {
		return nil, err
	}

	// Columns: line_id, provider_txn_id, type, gross_minor, fee_minor, currency
	for _, r := range rows[1:] {
		if len(r) != 6 {
			return nil, &errs.Error{Code: errs.InvalidArgument, Message: "unexpected column count"}
		}
		gross, err1 := strconv.ParseInt(r[3], 10, 64)
		fee, err2 := strconv.ParseInt(r[4], 10, 64)
		if err1 != nil || err2 != nil {
			return nil, &errs.Error{Code: errs.InvalidArgument, Message: "invalid amount in line " + r[0]}
		}
		var previous string
		prevErr := tx.QueryRow(ctx, `
			SELECT provider_txn_id FROM settlement_lines WHERE line_id = $1
		`, r[0]).Scan(&previous)
		if prevErr != nil && !errors.Is(prevErr, sqldb.ErrNoRows) {
			return nil, prevErr
		}
		if _, err := tx.Exec(ctx, `
			INSERT INTO settlement_lines
			    (line_id, file_id, provider_txn_id, type, gross_minor, fee_minor, currency, settle_date)
			VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
			ON CONFLICT (line_id) DO UPDATE SET
			    file_id = EXCLUDED.file_id,
			    provider_txn_id = EXCLUDED.provider_txn_id,
			    type = EXCLUDED.type,
			    gross_minor = EXCLUDED.gross_minor,
			    fee_minor = EXCLUDED.fee_minor,
			    currency = EXCLUDED.currency,
			    settle_date = EXCLUDED.settle_date
		`, r[0], fileID, r[1], r[2], gross, fee, r[5], date); err != nil {
			return nil, err
		}
		if previous != "" && previous != r[1] {
			if _, err := tx.Exec(ctx, `
				UPDATE payments SET settled_on = NULL
				WHERE provider_txn_id = $1 AND settled_on IS NOT NULL
			`, previous); err != nil {
				return nil, err
			}
		}
	}
	if _, err := tx.Exec(ctx, `
		UPDATE payments p SET settled_on = NULL
		FROM settlement_lines l
		WHERE l.file_id = $1 AND p.provider_txn_id = l.provider_txn_id
		  AND p.settled_on IS NOT NULL
	`, fileID); err != nil {
		return nil, err
	}
	// A correction for the same day replaces the previous file's lines.
	// Payments that were only evidenced by an omitted line are unset so
	// the next match can flag them as missing in settlement.
	if _, err := tx.Exec(ctx, `
		UPDATE payments p SET settled_on = NULL
		FROM settlement_lines l
		WHERE l.settle_date = $1 AND l.file_id <> $2
		  AND p.provider_txn_id = l.provider_txn_id
		  AND p.settled_on IS NOT NULL
	`, date, fileID); err != nil {
		return nil, err
	}
	if _, err := tx.Exec(ctx, `
		DELETE FROM settlement_lines
		WHERE settle_date = $1 AND file_id <> $2
	`, date, fileID); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return &LoadResponse{Key: key, Lines: len(rows) - 1, Loaded: true}, nil
}

// fetchReport stands in for your provider's settlement report API.
func fetchReport(ctx context.Context, apiKey, date string) ([]byte, error) {
	if apiKey == "" {
		return nil, errors.New("provider API key not set")
	}
	return []byte("line_id,provider_txn_id,type,gross_minor,fee_minor,currency\n" +
		"ln_1,ch_1001,charge,4999,175,EUR\n"), nil
}

The object storage docs cover Upload and the Versioned option. Per the secrets docs, the key's value stays out of the repository, so a coding agent working on the parser sees the secret's name and never its value.

Match and flag breaks

Run applies the set-based match described above, in one transaction, including the ON CONFLICT DO NOTHING inserts against UNIQUE (kind, provider_txn_id):

// reconcile/match.go
package reconcile

import (
	"context"
	"time"

	"encore.dev/cron"
	"encore.dev/rlog"
)

// Load yesterday's report and match it, every morning at 07:00 UTC.
var _ = cron.NewJob("daily-reconciliation", cron.JobConfig{
	Title:    "Load settlements and match against payments",
	Schedule: "0 7 * * *",
	Endpoint: Daily,
})

//encore:api private
func Daily(ctx context.Context) error {
	yesterday := time.Now().UTC().AddDate(0, 0, -1).Format("2006-01-02")
	if _, err := Load(ctx, yesterday); err != nil {
		return err
	}
	_, err := Run(ctx)
	return err
}

type RunResponse struct {
	NewBreaks int64 `json:"new_breaks"`
	Resolved  int64 `json:"resolved"`
}

// Run matches the last 30 days of settlement lines against payments.
// Every statement is safe to repeat, so a rerun only adds what changed.
//
//encore:api private method=POST path=/reconcile/run
func Run(ctx context.Context) (*RunResponse, error) {
	tx, err := db.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var resp RunResponse
	steps := []struct {
		name    string
		sql     string
		counter *int64
	}{
		{"mark settled", `
			UPDATE payments p SET settled_on = l.settle_date
			FROM settlement_lines l
			WHERE l.type = 'charge' AND l.provider_txn_id = p.provider_txn_id
			  AND l.gross_minor = p.amount_minor AND l.currency = p.currency
			  AND p.settled_on IS NULL`, nil},
		{"missing in ledger", `
			INSERT INTO breaks (kind, provider_txn_id)
			SELECT 'missing_in_ledger', l.provider_txn_id
			FROM settlement_lines l
			LEFT JOIN payments p ON p.provider_txn_id = l.provider_txn_id
			WHERE l.type = 'charge' AND p.provider_txn_id IS NULL
			  AND l.settle_date >= current_date - 30
			ON CONFLICT (kind, provider_txn_id) DO NOTHING`, &resp.NewBreaks},
		{"amount mismatch", `
			INSERT INTO breaks (kind, provider_txn_id)
			SELECT 'amount_mismatch', l.provider_txn_id
			FROM settlement_lines l
			JOIN payments p ON p.provider_txn_id = l.provider_txn_id
			WHERE l.type = 'charge'
			  AND (l.gross_minor <> p.amount_minor OR l.currency <> p.currency)
			  AND l.settle_date >= current_date - 30
			ON CONFLICT (kind, provider_txn_id) DO NOTHING`, &resp.NewBreaks},
		{"missing in settlement", `
			INSERT INTO breaks (kind, provider_txn_id)
			SELECT 'missing_in_settlement', p.provider_txn_id
			FROM payments p
			WHERE p.settled_on IS NULL
			  AND p.captured_at < now() - interval '3 days'
			  AND p.captured_at >= now() - interval '30 days'
			  AND NOT EXISTS (
			      SELECT 1 FROM settlement_lines l
			      WHERE l.provider_txn_id = p.provider_txn_id)
			ON CONFLICT (kind, provider_txn_id) DO NOTHING`, &resp.NewBreaks},
		{"resolve", `
			UPDATE breaks b SET resolved_at = now()
			FROM payments p
			WHERE b.resolved_at IS NULL AND p.provider_txn_id = b.provider_txn_id
			  AND p.settled_on IS NOT NULL`, &resp.Resolved},
	}
	for _, s := range steps {
		res, err := tx.Exec(ctx, s.sql)
		if err != nil {
			return nil, err
		}
		if s.counter != nil {
			*s.counter += res.RowsAffected()
		}
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	rlog.Info("reconciliation done", "new_breaks", resp.NewBreaks, "resolved", resp.Resolved)
	return &resp, nil
}

Tested against four payments and three settlement lines, the first run flagged an amount mismatch, a line with no payment and a five-day-old payment with no line, and a second run added nothing. After a late webhook and the missing charge in the next report, the following run resolved both, leaving the amount mismatch open for review.

Fees do not cause breaks here: the match compares gross_minor with the captured amount and stores the fee separately. Comparing net amounts per payout against the bank statement is a later step on the same tables.

The database docs cover migrations, and the cron job docs cover UTC schedules. Cron jobs do not run locally or in preview environments, so call Load and Run by hand under encore run. In deployed environments the daily job is Encore platform managed.

When a coding agent later adds a second provider, it adds a provider column and a second fetcher, runs Load and Run locally against sample files, and the deploy applies the migration.

Best fit: Fintech and payments teams in their own AWS or GCP account who want settlement files, matching rules and the daily schedule in one Go service that finance and engineering can both read.

Things to decide early

Matching key. Match on the provider's transaction ID, never on amount and time. If your webhook handler does not store that ID on every payment today, fix that first.
Settlement delay. The three-day window in missing_in_settlement should follow your provider's payout schedule per currency, since some settle in one business day and others take a week or more.
Refunds, disputes and fees. The example only matches charge lines, while refunds, chargebacks and fee adjustments each need their own match rule against the records that created them.
Resolution trail. Breaks closed by hand need a reason and the person who closed them. Store those as columns or a separate table, so the history survives the next run.
Retention. Settlement reports are accounting records, so keep the bucket's originals for as long as your finance team and regulators require, and add the cleanup rule only after that is agreed.

Payment Reconciliation Backend: Match Settlements Against Your Ledger