Settlement reports & FinOps disbursement automation

The FinOps-facing automation layer on top of settlements. It generates a per-merchant settlement statement for each cycle, triggers the actual settlement, and delivers the statement — so Vecnet FinOps has automated settlements from day 1 with no manual spreadsheet work.

Ported from Tonder's separate usrv-batch-transaction-report service (Python, AWS Batch/Fargate). Vecnet collapses Tonder's Daily Transaction Report + Settlement Batch Report into one parametrized cycle engine (Edge Function or worker) driven by pg_cron, with the cycle type as a parameter.

Cycle model

Cycle	Cadence	Window source
T+0 (batch)	Mon & Thu (default 08:00 UTC)	the batch on-cycle dates computed by day-of-week
T+1	daily	journals dated 1 day prior
T+2	daily	journals dated 2 days prior

The cadence per merchant follows their settlement_policy (fixed_days_delay.days → 0/1/2) from the matched fee rule; a pg_cron entry exists per cadence.

Per-merchant run

Always filter journal_type = 'OUT'

In step 2, aggregate always filtering journal_type = 'OUT' — this is the single most important rule. OUT is the business side of the double entry; forgetting it doubles every amount. Sum by category: PAYMENT, DISPUTE_IN_REVIEW (active disputes — note: DISPUTE_IN_REVIEW, not DISPUTE_REVIEW), DISPUTE_WON, REFUND, VOID, WITHDRAWAL, ROLLING_RESERVE_RELEASE.

3. IVA — most comes from iva_amount on the journals. The payout fee (tarifa_liquidacion) is config-driven and has no journal record, so compute its IVA separately as payout_fee × 0.16 and add it in.
4. Trigger the settlement — hand totals to settlement orchestration, which records the settlement, computes force_decrease, and drives the §8.4 approve→confirm. All disbursement amounts are positive/absolute.
5. Deliver — upload to Supabase Storage (reports/settlement/{cycle}/{entity_id}/{date}/…xlsx) and email FinOps + the merchant; record the run in settlement_runs.

Rolling reserve — Vecnet simplification

Tonder's batch runs two rolling-reserve queries (an OLD one against the legacy mv_payment_transactions, a NEW one against the journals) and always uses the OLD result — pure migration baggage.

Greenfield: drop the dual query

Vecnet has no legacy materialized view. Use the journals directly — the ROLLING_RESERVE_RELEASE journals produced by the RR job are the single source. Drop the OLD/NEW dual-query entirely.

Run modes

Two modes (Tonder's TESTING_CONFIG):

• scheduled (default_config) — the pg_cron path, all active merchants.
• manual (manual_execution / specific_merchants) — an on-demand admin trigger for a subset.

Active-merchant selection respects an explicit allowlist when present.

Supporting tables

create table settlement_merchant_config (
  id              uuid primary key default uuid_generate_v7(),
  entity_id       text not null unique,
  language        text not null default 'es',        -- es | en (statement language)
  payout_fee      numeric(20,4) not null default 0,  -- tarifa_liquidacion (no journal; IVA computed separately)
  excluded_acquirers text[] default '{}',            -- rails to exclude from this merchant's cycle
  cadence         text not null default 'T+1',        -- T+0 | T+1 | T+2
  recipients      text[] not null default '{}',       -- statement email recipients
  active          boolean not null default true,
  created_at      timestamptz not null default now(),
  modified_at     timestamptz not null default now()
);

create table settlement_runs (
  id              uuid primary key default uuid_generate_v7(),
  entity_id       text not null,
  cycle           text not null,                       -- T+0 | T+1 | T+2
  window_from     timestamptz not null,
  window_to       timestamptz not null,
  totals          jsonb not null,                      -- per-category gross/fee/iva/net + RR + payout fee
  net_to_disburse numeric(20,4) not null,
  settlement_id   text,                                -- links to the §8.4 settlement
  statement_path  text,                                -- Supabase Storage URL
  status          text not null default 'pending',     -- pending | settled | emailed | failed
  run_mode        text not null default 'scheduled',   -- scheduled | manual
  error           text,
  created_at      timestamptz not null default now(),
  unique (entity_id, cycle, window_from)               -- idempotent per cycle window
);

The unique (entity_id, cycle, window_from) constraint makes a cycle run idempotent — a re-run of the same window won't double-disburse.

FinOps surfacing

Expose in the System Control Panel: trigger/re-run a cycle, view each merchant's run + statement + disbursement status, and catch failures (a merchant whose settlement_runs.status = 'failed'). This is the day-1 automated-settlement loop.