Double-Entry Payment Ledger Backend on AWS

// Hand-rolled: reject unbalanced legs, then insert entry + legs in one tx.
func post(ctx context.Context, db *sql.DB, key, memo string, legs []Leg) (int64, error) {
	var sum int64
	for _, leg := range legs {
		sum += leg.Amount
	}
	if sum != 0 {
		return 0, fmt.Errorf("legs must sum to zero")
	}
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()
	// SELECT existing by idempotency_key, else INSERT entry + legs, COMMIT.
	return 0, tx.Commit()
}

-- ledger/migrations/1_create_ledger.up.sql
CREATE TABLE accounts (
    id         BIGSERIAL PRIMARY KEY,
    code       TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    currency   TEXT NOT NULL CHECK (char_length(currency) = 3),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE journal_entries (
    id              BIGSERIAL PRIMARY KEY,
    idempotency_key TEXT NOT NULL UNIQUE,
    memo            TEXT NOT NULL DEFAULT '',
    posted_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE journal_legs (
    id         BIGSERIAL PRIMARY KEY,
    entry_id   BIGINT NOT NULL REFERENCES journal_entries(id),
    account_id BIGINT NOT NULL REFERENCES accounts(id),
    -- Positive amount = debit, negative = credit, in minor units.
    amount     BIGINT NOT NULL,
    currency   TEXT NOT NULL CHECK (char_length(currency) = 3)
);

CREATE INDEX journal_legs_account_id_idx ON journal_legs (account_id);

// ledger/ledger.go
package ledger

import (
	"context"
	"errors"
	"time"

	"encore.dev/beta/auth"
	"encore.dev/beta/errs"
	"encore.dev/cron"
	"encore.dev/pubsub"
	"encore.dev/rlog"
	"encore.dev/storage/sqldb"
)

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

// Optional: call a processor for payouts. Values are not stored in the repo.
var secrets struct {
	StripeSecretKey string
	AuthToken       string // local/dev bearer; replace with IdP verification in production
}

type Leg struct {
	AccountCode string `json:"account_code"`
	Amount      int64  `json:"amount"` // minor units; debit > 0, credit < 0
	Currency    string `json:"currency"`
}

type PostParams struct {
	IdempotencyKey string `json:"idempotency_key"`
	Memo           string `json:"memo"`
	Legs           []Leg  `json:"legs"`
}

type PostResponse struct {
	EntryID int64 `json:"entry_id"`
}

type LedgerPosted struct {
	EntryID        int64     `json:"entry_id"`
	IdempotencyKey string    `json:"idempotency_key"`
	PostedAt       time.Time `json:"posted_at"`
}

var Posted = pubsub.NewTopic[*LedgerPosted]("ledger-posted", pubsub.TopicConfig{
	DeliveryGuarantee: pubsub.AtLeastOnce,
})

//encore:authhandler
func AuthHandler(ctx context.Context, token string) (auth.UID, error) {
	// Local/dev: compare to a secret. In production, verify a JWT from your IdP.
	if token == "" || token != secrets.AuthToken {
		return "", &errs.Error{Code: errs.Unauthenticated, Message: "invalid token"}
	}
	return auth.UID("teller"), nil
}

// Post writes a balanced entry or returns the existing one for the same key.
//
//encore:api auth method=POST path=/ledger/entries
func Post(ctx context.Context, p *PostParams) (*PostResponse, error) {
	if len(p.Legs) < 2 {
		return nil, &errs.Error{Code: errs.InvalidArgument, Message: "need at least two legs"}
	}

	var sum int64
	currency := p.Legs[0].Currency
	for _, leg := range p.Legs {
		if leg.Currency != currency {
			return nil, &errs.Error{Code: errs.InvalidArgument, Message: "mixed currencies"}
		}
		sum += leg.Amount
	}
	if sum != 0 {
		return nil, &errs.Error{Code: errs.InvalidArgument, Message: "legs must sum to zero"}
	}

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

	var existing int64
	err = tx.QueryRow(ctx, `
		SELECT id FROM journal_entries WHERE idempotency_key = $1
	`, p.IdempotencyKey).Scan(&existing)
	if err == nil {
		return &PostResponse{EntryID: existing}, nil
	}
	if !errors.Is(err, sqldb.ErrNoRows) {
		return nil, err
	}

	var entryID int64
	if err := tx.QueryRow(ctx, `
		INSERT INTO journal_entries (idempotency_key, memo)
		VALUES ($1, $2) RETURNING id
	`, p.IdempotencyKey, p.Memo).Scan(&entryID); err != nil {
		return nil, err
	}

	for _, leg := range p.Legs {
		var accountID int64
		var accountCurrency string
		if err := tx.QueryRow(ctx, `
			SELECT id, currency FROM accounts WHERE code = $1
		`, leg.AccountCode).Scan(&accountID, &accountCurrency); err != nil {
			return nil, &errs.Error{Code: errs.NotFound, Message: "unknown account " + leg.AccountCode}
		}
		if accountCurrency != leg.Currency {
			return nil, &errs.Error{Code: errs.InvalidArgument, Message: "currency mismatch for " + leg.AccountCode}
		}
		if _, err := tx.Exec(ctx, `
			INSERT INTO journal_legs (entry_id, account_id, amount, currency)
			VALUES ($1, $2, $3, $4)
		`, entryID, accountID, leg.Amount, leg.Currency); err != nil {
			return nil, err
		}
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}

	if _, err := Posted.Publish(ctx, &LedgerPosted{
		EntryID:        entryID,
		IdempotencyKey: p.IdempotencyKey,
		PostedAt:       time.Now().UTC(),
	}); err != nil {
		// Entry is committed; returning an error would confuse the client.
		// A follow-up outbox or retry of the publish is the durable fix.
		rlog.Error("publish ledger-posted failed", "entry_id", entryID, "err", err)
	}

	_ = secrets.StripeSecretKey // available for payouts that reference this entry
	return &PostResponse{EntryID: entryID}, nil
}

type BalanceResponse struct {
	AccountCode string `json:"account_code"`
	Balance     int64  `json:"balance"`
	Currency    string `json:"currency"`
}

//encore:api auth method=GET path=/ledger/accounts/:code/balance
func Balance(ctx context.Context, code string) (*BalanceResponse, error) {
	var out BalanceResponse
	err := db.QueryRow(ctx, `
		SELECT a.code, COALESCE(SUM(l.amount), 0), a.currency
		FROM accounts a
		LEFT JOIN journal_legs l ON l.account_id = a.id AND l.currency = a.currency
		WHERE a.code = $1
		GROUP BY a.code, a.currency
	`, code).Scan(&out.AccountCode, &out.Balance, &out.Currency)
	if err != nil {
		return nil, &errs.Error{Code: errs.NotFound, Message: "account not found"}
	}
	return &out, nil
}

// Daily check that every journal entry's legs still sum to zero.
var _ = cron.NewJob("reconcile-ledger", cron.JobConfig{
	Title:    "Reconcile ledger entries",
	Schedule: "0 3 * * *",
	Endpoint: Reconcile,
})

//encore:api private
func Reconcile(ctx context.Context) error {
	rows, err := db.Query(ctx, `
		SELECT entry_id, SUM(amount) AS s
		FROM journal_legs
		GROUP BY entry_id
		HAVING SUM(amount) <> 0
	`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var entryID, sum int64
		if err := rows.Scan(&entryID, &sum); err != nil {
			return err
		}
		rlog.Error("unbalanced entry", "entry_id", entryID, "sum", sum)
	}
	return rows.Err()
}