Daily Ledger Flow

Daily XLSX report generation and balance snapshots per business account.

---

Overview

Attribute	Value
Trigger	CloudWatch cron, daily at 10:00 UTC
Orchestrator handler	src/handler/dailyLedgerCronHandler.ts
Orchestrator service	src/service/accounting/DailyLedgerOrchestratorService.ts
Processor handler	src/handler/dailyLedgerProcessorHandler.ts (async Lambda invoke)
Processor service	src/service/accounting/DailyLedgerProcessorService.ts

The cron identifies all business accounts that have the DAILY_BALANCES module enabled, resolves the previous day's window in each business's timezone, and invokes a processor Lambda per account. Each processor generates an XLSX report, stores it in S3, and saves a DynamoDB balance snapshot.

---

Flow Diagram

---

Processor Event Structure (IDailyLedgerProcessorEvent)

Field	Type	Required	Description
entity_id	string	yes	Business ID
account_id	string	yes	Payment account UUID
account_code	string	yes	Account code (e.g. BUSINESS_PAYABLE)
currency_code	string	yes	ISO currency
timezone	string	yes	Business timezone (IANA)
date_local	string	yes	Report date (ISO, local)
start_utc	string	yes	Day start UTC (ISO datetime)
end_utc	string	yes	Day end UTC (ISO datetime)
date_field	string	no	Journal field to filter by date (default: created_at)
withdrawal_account_id	string	no	Withdrawal account UUID (if separate)

---

Previous Day Window

The orchestrator resolves the previous day in each business's timezone:

timezone    = business.timezone OR "America/Mexico_City"
yesterday   = (cron_time - 1 day) in business timezone → date only
start_utc   = yesterday 00:00:00 in business timezone → UTC
end_utc     = yesterday 23:59:59 in business timezone → UTC

The cron runs at 10:00 UTC, so when the processor runs, the previous day is fully closed in most timezones.

---

Journal Query (MongoDB)

getJournalsWithLedgerEntries() aggregates financesJournals:

[
  { $match: {
      entity_id: entity_id,
      account_id: account_id,         // payment or withdrawal account
      [date_field]: { $gte: start_utc, $lte: end_utc },
      category: { $in: categoryFilter },
      settlement_id: { $in: [null, ""] }  // unsettled only
  }},
  { $lookup: {
      from: "financesLedgerEntries",
      localField: "id",
      foreignField: "journal_id",
      as: "ledger_entries"
  }},
  { $addFields: { balance: { $toDouble: "$balance" } }}
]

date_field defaults to "created_at". Override allows reprocessing with a different timestamp column (e.g. process_date).

---

Balance Calculation

Opening Balance

Fetched from DynamoDB BalanceSnapshotsTable:

• PK = ACC#{account_id}
• SK begins_with P#DAILY#S#
• Filter: SK < P#DAILY#S#{startEpoch} (snapshot before the period)
• Returns the most recent snapshot's closing balance (or 0 if none)

Closing Balance

closing = opening + sum(net movements from period journals)

Net movement per journal = sum of debit/credit entries for that account.

---

Totals Calculation

calculatePaymentTotals() and calculateWithdrawalTotals() reduce journals:

Output field	Description
total_gross_amount	Sum of all gross amounts
total_net_amount	Sum of all net amounts
total_fee_amount	Sum of all fees
total_iva_amount	Sum of all IVA
totals_by_category	Breakdown per category: amount totals (total_payment, total_topup, etc.) AND per-category counts (payment_count, topup_count, etc.). Example: { total_payment: 1553083.9, payment_count: 8509, total_dispute_in_review: 70190, dispute_in_review_count: 25, total_topup: 0, topup_count: 0, total_withdrawal: 0, withdrawal_count: 0 }
total_journals_count	Number of journals in the period (not ledger entries). Stored in the balance snapshot.
balance_changes	Net balance change per category

Entry and journal ordering:

• Journals in the Excel report are sorted by the minimum seq of their entries (not by created_at). This ensures the balance_before/after chain displays correctly.
• Entries within a journal are sorted by seq first, created_at as tiebreaker.

---

Excel Report

Generated with ExcelJS. Structure:

Sheet	Content
Summary	Opening balance, totals by category, closing balance, period metadata
Transactions	One row per journal: ID, date, category, amounts, description

Formatting:

• Business logo embedded (read from filesystem at startup)
• Column widths auto-sized
• Number formats: currency (2 decimal places)
• Header row: bold + background color
• Category rows: alternating shade

---

S3 Storage

Key: reports/daily-ledger/{entity_id}/{date_local}/{entity_id}-{date_local}.xlsx
Bucket: FINANCES_FILES_BUCKET

The processor returns s3_path_url (full S3 URL) in its response.

---

DynamoDB Snapshot

saveDailyBalanceSnapshot() stores a snapshot record:

Attribute	Value
PK	ACC#{account_id}
SK	P#DAILY#S#{startEpochMs}
entity_id	Business ID
account_code	Account code
currency_code	ISO currency
date_local	ISO date string (local)
timezone	Business timezone
opening	Opening balance
closing	Closing balance
totals	Full IDailyLedgerTotals object
path_url	S3 file URL
created_at	Epoch ms

The SK pattern P#DAILY#S#{epoch} allows querying previous snapshots using DynamoDB begins_with on the SK.

---

Non-Obvious Behaviors

Behavior	Detail
Generates PREVIOUS day report	The 10:00 UTC cron generates the report for yesterday in the business timezone, not today.
Opening balance from DynamoDB	Opening comes from the last stored snapshot (SK < today's epoch). First-time run with no prior snapshot uses 0.
Unsettled journals only	Journals with a settlement_id are excluded — they are accounted for in the settlement flow, not in the daily ledger.
date_field is configurable	Defaults to created_at. Can be overridden to process_date for reprocessing or historical backfills.
Withdrawal account is optional	Some businesses have a single payable account; others have a separate withdrawal account. The orchestrator includes withdrawal_account_id only when it exists.
DAILY_BALANCES module flag	Controlled per entity via FinancesConfigService. If disabled, the account is skipped entirely.
Concurrency 5	Orchestrator limits Lambda invocations to 5 concurrent per run to avoid overwhelming MongoDB with simultaneous queries.
Logo from filesystem	The processor reads a logo image from /tmp or an asset directory. If not found, the Excel report is generated without a logo (no error).
PNG dimension parsing	readPngDimensions() reads the PNG binary header to determine image size for ExcelJS embedding — no external library dependency.
Error isolation	Each account processor runs independently. If one fails, the orchestrator records the error and continues with the remaining accounts.