The ledger records the lifecycle of submitted money movements, regardless of source. Financial facts are immutable; only status can change, with trigger-written history:
Invoice and payout statuses remain user-facing projections. The ledger is the durable record used for reconciliation, balances, transaction history, and future reporting.
The implementation uses ledger for financial facts and current status, and
append-only ledger_history for initial status and every status change. It is not a full
double-entry accounting system. A later posting/account model may project from
these entries if formal accounting requirements demand it.
Financial facts are immutable. No ledger entry is deleted, and its kind, amount, parties, source, and references cannot be changed. Corrections are new entries that reference the original entry.
Provider acceptance creates a Pending entry, in the same transaction that records submission. Queued intent and payouts awaiting bank setup create no ledger entry. Settlement changes status to Completed for either collection or payout. Failed and Canceled submitted movements remain visible without contributing to settled balances. An ambiguous submission has no entry until the existing retry/reconciliation path recovers a confirmed provider reference; it must not invent movement or a new payment.
ledger
- id text primary key
- kind text not null
- amount bigint not null
- status text not null
- invoice_id text null references invoice(id)
- vendor_id text null references vendor(id)
- customer_id text null references customer(id)
- source text not null
- source_reference text not null
- idempotency_key text not null unique
- reversal_of_id text null references ledger(id)
- memo text null
- occurred_at timestamptz not null
- created_at timestamptz not null
amount is signed and stored in cents. Positive amounts move money into Tendr.me; negative
amounts move money out. Zero is invalid except if a future, explicitly defined
entry kind requires it.
Kind is a caller-defined string throughout the request, outbox payload, and ledger. There is no enum, registry, or database allowlist. New handlers supply their own nonblank kind without changing Core. Source is likewise a provider-owned string.
kind says what the event is. Existing conventions include (not an exhaustive list):
InvoicePayment
InvoiceCredit
ProcessingFee
VendorPayout
ReferralBonus
CapitalContribution
Refund
Adjustment
Reversal
Balance queries return settled Amount (Completed only) and separate
PendingAmount, within the requested account/date/kind scope. Failed/Canceled
entries contribute to neither. Listing entries includes every status.
A reversal is a new entry with the opposite amount and reversal_of_id set to
the original entry. The original entry remains unchanged. A refund,
return, or chargeback is likewise a new entry, not a mutation of an original
payment.
source identifies the origin of a financial event. It is a nonblank,
case-sensitive string supplied by IGateway.Source for provider transactions.
An independent gateway supplies its own stable name without changing Core or
the database schema. These are examples, not a predefined list:
Stripe
ModT
Manual
System
source_reference is the stable source event identifier: a Stripe charge ID,
a ModT payment-order ID, a bank wire reference, or an internal record ID for a
system-generated reversal.
Callers supply a stable, generated 10-character lowercase alphanumeric
idempotency_key, validated by UniqueId. They retain it across retries rather
than generating a fresh key for each insert attempt. A duplicate key
returns the already-recorded entry rather than inserting another one. It must
match the source, source reference, kind, amount, invoice/vendor/customer scope, reversal reference, memo, and
event timestamp; conflicting facts throw instead of silently succeeding.
Timestamp comparisons use PostgreSQL's microsecond precision.
Payment reconciliation uses the persisted outbox operation ID as this ledger key:
one movement lifecycle per charge/payout operation. This is distinct from the
provider request's idempotency key. Other callers must persist their generated
ledger keys before retryable execution; multiple movements need separate keys.
A unique index on (source, source_reference, kind) independently prevents
the same provider movement from being recorded twice under different keys.
Replaying creation returns the entry's current status without resetting it or adding history. Status changes preserve the original submission timestamp and facts.
ILedgerStore owns storage, idempotency-key validation, and the small set of
ledger-specific invariants. A separate ledger service is not needed yet.
public interface ILedgerStore
{
Task<ILedgerEntry> CreateAsync(LedgerEntryCreateOptions options);
Task<ILedgerEntry?> GetByIdAsync(string id);
Task<LedgerPage> ListAsync(LedgerQuery query);
Task<LedgerBalance> GetBalanceAsync(LedgerBalanceQuery query);
Task SetStatusAsync(string idempotencyKey, PaymentStatus status, string eventId);
Task<IReadOnlyList<LedgerHistory>> GetHistoryAsync(string id);
}
CreateAsync inserts a financial entry with an explicit status using the shared patch
writer's insert-only path, never an upsert that updates existing facts. It participates in a
caller's transaction when present, or commit their own transaction otherwise.
Returned records are hydrated from PostgreSQL, including database-generated
created_at timestamps.
Migration 020 corrects the old timezone-stripping created_at defaults. Its
Prod-only seed also repairs historical values with set-based predicates, under
the owner's confirmation that production application sessions used
America/Los_Angeles and no manual corrections were made. Keep writers stopped
and retain the pre-cutover backup. The migration must not be run as standalone SQL.
Normal vendor, customer, and vendor/customer-link inserts used the default;
the two vendors explicitly timestamped in 001_Seed.sql are excluded. ACH rows
before March 12, 2026 UTC are repaired. The reviewed inventory contains no ACH
rows in the March 12-17 deployment transition window; the migration rejects any
such rows rather than guessing which application version created them. Later
ACH rows, invoices, card accounts, and ledger rows remain unchanged.
The correction uses explicit Los Angeles/UTC conversions, not a fixed offset.
Spring-forward timestamps with an ambiguous inverse fail review. Migration
version tracking prevents a second application, and production rollback is
blocked to preserve that protection. Development history is deliberately not
changed: it used a different timezone and already has individual repairs.
The repair is a marked section of 020_Seed.Prod.sql; the backfill generator
preserves it in production migration output. Schema, ledger backfill, timestamp
repair, and the single 020 version record commit or roll back together.
Owner column permissions reject accidental changes to financial facts, deletes, and truncates. The database rejects zero amounts, invalid statuses, and invalid idempotency-key formats. The store rejects blank kinds, but accepts caller-defined values. The store additionally requires a reversal to negate an existing settled entry's amount in the same invoice/vendor/customer scope.
ListAsync supports UI transaction history with vendor, customer, invoice,
kind, date range, and paging filters. Its page contains ILedgerEntry records
directly. GetBalanceAsync returns settled and pending totals in cents for the requested scope.
List pages and their counts share one database snapshot and use event time plus
ID for stable ordering (newest first by default, OldestFirst for catch-up).
Null kind filters mean unrestricted; empty filters match
nothing. Date bounds are inclusive and filter the financial entry's event time.
dotnet run --project Cli -- ledger balance --env Development
This read-only command uses ILedgerStore.GetBankBalanceAsync to report:
The query includes BankDeposit entries from all sources and non-collection debits
regardless of their caller-defined kind. Reversals follow the original movement's
scope. InvoicePayment entries and their reversals are excluded to avoid counting
processor-held collections again as bank cash. Pending incoming funds and
Failed/Canceled movements do not contribute. All figures use one database snapshot.
This is a ledger-based USD estimate, not a live bank balance or profit calculation.
Unsubmitted payout jobs are excluded. No operating reserve threshold is enforced.
Only database configuration is required, not payment gateways or Worker.
Production's migration baseline is 019. Consolidated 020_WorkProcessingAndLedger.cs (version
202610280020) creates the final ledger, lifecycle/permissions, invoice, and work
schema directly, then applies embedded 020_Seed.Prod.sql through the shared seed
loader for ENVIRONMENT=Prod.
Schema, data, and migration version commit atomically. There is no provider allowlist.
Previously migrated development databases with the retired 020-024 chain need a
separately reviewed rebaseline or disposable rebuild; no local
data is automatically erased. Queue/outbox infrastructure,
local payment handlers, and file-backed gateway simulation use separate work.outbox and
work.job_queue tables. Settlement tracking lives on each payment's outbox row;
invoice fields are projections. Invoice and RT payouts share IPayoutService,
the same execution handler, polling, and ledger recording; see Local durable work.
API charges and CLI payouts now always enqueue through the shared payment
pipeline. The worker owns ledger/projection updates; there is no synchronous
bypass mode. The API Function App now hosts the shared engine via Azure timers
(work every five seconds, recent payment status every five seconds and older Pending
payments every five minutes, deposits every four
hours). V1 rollout still requires production
configuration/provisioning review, execution of consolidated 020 with refreshed historical data,
and deployment verification. The local migration rehearsal against a production
snapshot passed on October 28; see the rehearsal results below.
Incoming bank-deposit discovery and outbox recording are implemented; the initial
deposit history must be backfilled before discovery can run.
Submission receipt emails are implemented. Automatic failure recovery, failure
emails, webhooks, and durable email delivery are deferred, not v1 prerequisites.
Updated October 29, 2026. Implementation is complete for the pure RT calculator
and simplified three-tier/three-year defaults (with LiveForever retained), shared
worker/timer routing and checkpoints, daily return discovery, bank-deposit polling,
buffered work.log traces, and Error/Critical email alerts. Transaction email
examples have been reviewed and updated: vendor invoice totals/contact links,
invoice payout links, and RT subject/formatting. Site links now use relative paths
or the current origin. These changes are committed. Focused tests passed; a final
full-suite run against the combined release changes remains outstanding.
The CLI maintenance commands from main have been brought onto the ledger branch:
vendor rail sets the preferred rail (including 0/auto); invoice pay includes
the old status-reset use case as a confirmed, deduplicated retry through the
normal payout queue. It preserves failed/canceled attempts and does not recharge
the customer. RT retries and recovery of completed-then-returned payouts are not
covered by this command. Local Development's outbox uniqueness index has been
refreshed to match the updated migration 020; no production data has changed.
Failed/canceled charges and payouts, including RT, and returned payouts now emit
post-commit operator Error alerts through ErrorAlerts. Email delivery remains
best-effort; the durable event and ledger retain the outcome even if email fails.
work.log, as noted below.WorkTraces, and production Error/Critical email delivery. Confirm the
deployed appsettings and Azure environment overrides agree.020_Seed.Prod.sql using the generator's --format migration output,
rebuild, and apply 020_WorkProcessingAndLedger (version 202610280020) from
production's 019 baseline. Deploy the matching application with workers held
disabled until the schema and seed are in place. Production execution remains pending.work.log, and post-migration balances. Enable production money
movement in a controlled manner and observe the normal payment/payout path.
Local rehearsal does not validate Azure hosting or provider connectivity.--max 1
and observe each payout before continuing. Use the existing normal work pipeline.Deferred: PostgreSQL version upgrade, ACH pending-payout UX, required-float business rules, per-invoice payout disposition (below), automatic failure recovery, customer/vendor failure emails, webhooks, and durable email delivery.
Cover invoice creation, customer payment, asynchronous settlement, vendor payout, RT payouts, ledger/status history, parent-linked work, and receipt/alert delivery. Use designated test participants and accounts, explicit operator approval and capped amounts for real money movement, and stable request identities so retries cannot duplicate charges or payouts. Poll with bounded waits; report pending bank settlement separately from failure, and retain invoice/work/ledger/provider IDs for investigation. Do not force statuses or delete financial history for cleanup. This is implementation follow-up, not an assertion that production smoke coverage already exists or authorization to run live financial tests.
Agreed design; not implemented and not a current go-live requirement. Persist on
invoice, separately from payment_status and payout_status:
payout_disposition: Automatic (default), Held, or Retained.payout_disposition_reason: required explanation for Held/Retained.payout_disposition_changed_at: timestamp of the change.payout_disposition_changed_by: operator identity.Automatic uses normal payout processing. Held temporarily withholds the payout, which remains owed. Retained explicitly records funds kept as float with no payout expected. Disposition describes intent; payout status describes actual processing. Do not fabricate a completed payout or ledger cash movement to represent retention.
Provide an operator CLI control usable before customer payment. Automatic scheduling
and explicit invoice pay must respect disposition. Already-queued work needs a
race-safe submission guard; changing the invoice flag alone cannot stop it. Reject
changes once submission has started rather than implying an external transfer was canceled.
Historical retained-float entries currently have explanatory collection-ledger memos and preserved Completed payout status. When this feature is implemented, explicitly classify those approved invoices as Retained and reconcile the misleading payout status without changing cash history. This note does not change the current backfill or schema. Whether disposition also suppresses RT payouts remains an explicit unresolved decision.
Passed using an actual production baseline-019 dump, restored into file-backed
PgLite on 127.0.0.1:10074, database prod_tb. Source reads and pg_dump shared
one exported, repeatable-read, read-only snapshot. All 8 source tables (312 rows)
restored with matching counts. Local ownership was assigned to prod_tb_user,
without superuser or CREATEROLE; source ACLs were not imported.
The actual Migrations entry point ran with ENVIRONMENT=Prod and an explicit
local ConnectionStrings:Migrations command-line override. Production credentials
and configuration were not changed. Migration 020 and its embedded production seed
committed together. Verification confirmed:
No pending migrations.This rehearsal predates the addition of the diagnostic work.log table to
unreleased migration 020. The persistent copy has not been updated with that table;
the updated migration and log writes are covered by disposable-database tests.
Private dump, logs, and verification output are under
~/.tendrme/rehearsal-ledger-20261028-10074/; the persistent cluster is its pglite/
subdirectory. This copy contains production data and must not be committed.
Startup remains through the standard script:
PGLITE_WAIT=1 PGLITE_PORT=10074 \
PGLITE_DATA_DIR="$HOME/.tendrme/rehearsal-ledger-20261028-10074/pglite" \
PGLITE_LOG_FILE="$HOME/.tendrme/rehearsal-ledger-20261028-10074/pglite.log" \
./scripts/ensure-pglite.sh
PGLITE_WAIT=1 keeps a newly started server attached to the calling session;
the normal default still detaches. Existing healthy servers are reused.
Production was PostgreSQL 16.15; PgLite was PostgreSQL 18.3. This is not an
engine-version-identical or Azure deployment rehearsal. As data grows, use an
isolated server-side clone/restored backup, ideally matching production's engine,
instead of downloading a full local copy. Final cutover still requires a fresh
backfill generation and bank reconciliation.
The optional tendr ledger balance convenience command implements the bank-balance
accounting rule; it is not a release blocker. Timers (including daily payout-return
discovery), receipt emails, bank-deposit polling, and RT catch-up
are implemented. After deployment/backfill, use catch-up list and dry-run before
queueing one invoice at a time.
Unreleased migrations 020-024 are flattened into migration 020, starting from production's 019 baseline. It embeds the reviewed, deterministic one-time ledger backfill from provider history, including ModT history. Migrations must not call Stripe, ModT, or any other external API at runtime.
Before refreshing its embedded data:
The ad hoc backfill generator reads production's
019 invoices and provider history without database writes, then emits reviewed SQL
for the updated schema. Rerun it with --format migration immediately before cutover,
review and replace 020_Seed.Prod.sql, then rebuild. Generation is read-only;
running migration 020 applies schema and data together. Unresolved evidence blocks SQL
generation rather than inventing financial history.
Scheduled status checking queries known provider payment IDs and durably queues deduplicated terminal observations through the same publication entry point intended for future authenticated webhooks. It does not write financial state. The worker's reconciliation handler updates ledger status and the invoice; other event handlers schedule invoice and RT payouts. Those changes and event completion share one transaction. Duplicate observations cannot repeat these effects. Polling will remain active as the backstop for missed webhooks; ambiguous submissions without a saved provider reference still require provider-specific recovery.
Stripe-to-Tendr bank settlement is a separate reconciliation flow, not
covered by polling known customer charges or vendor payouts. BankDepositPoller
discovers posted bank credits at ModT every four hours, starting one day before the
latest Completed BankDeposit ledger date, regardless of source. It queues observations;
BankDepositHandler records them through the normal transaction machinery. A bank
receipt moves existing Stripe-held funds; it must not count as new revenue or
trigger another invoice/RT payout. The bank description's Originator field
identifies the source (STRIPE normalizes to Stripe; other values retain their
original spelling), while the ModT transaction id is the ledger's
source_reference. Reporting provider, bank reference and metadata remain in the
outbox payload; no association to an individual Stripe payout is guessed.
See the incoming-transfer design.
ledger_history uses (id, version) as its primary key: id references ledger.id,
and version starts at 1 for each entry and increments only on a status change.
The ledger row's update lock serializes version allocation. There is no independent
history-row ID or global sequence. The table stores previous/new status, database timestamp, originating
outbox event ID, and database actor. Inserts record the original operation ID. Status
updates carry the observation event ID using transaction-local context; direct admin
SQL without that context records a null event ID, not a fabricated cause. Repeated
unchanged status creates no history. History rolls back with the ledger transaction.
Terminal statuses cannot transition again; refunds/reversals are compensating entries.
The application keeps its existing non-superuser database-owner login. The migration sets permissions immediately after creating the ledger/history and trigger, in the same transaction: SELECT/INSERT on ledger, UPDATE(status) only, and SELECT/INSERT on history. It revokes table-wide mutation privileges and checks that inherited grants do not bypass those restrictions. No table-grant loop or permission maintenance is required for unrelated application tables, including future tables created by the owner.
This protects against accidental DML, not a malicious owner. The owner can explicitly restore privileges, change schema, or disable triggers. Superusers bypass these checks and must not be the production application login.
The history trigger runs with the caller's permissions, with a pinned search path and qualified table names. History INSERT is intentionally allowed for the existing owner; UPDATE, DELETE, and TRUNCATE are denied. Application code leaves history insertion to the trigger, but the database does not prohibit direct history inserts. No extra role, CREATEROLE privilege, admin provisioning, or connection-string change is required. Migration and rollback run as the existing database owner.
Local API/CLI/Worker and migrations use dev_tb_user; Worker reads the selected
Bill database config. The former dev_tb_runtime login and broad-grant provisioning
scripts are retired. Production still requires review of ledger permissions and the
migration, but no switch to a separate application login.