Ingestion & analytics

transaction_events (the inbox) and transactions (the attempt-level analytics store).

transaction_events (ingestion inbox)

Use pgmq for the inbox (FIFO-ish, visibility timeouts, DLQ semantics) or a plain table:

create table transaction_events (
  id            uuid primary key default uuid_generate_v7(),
  correlation_id text not null,
  entity_id     text not null,
  acquirer      text not null,                       -- tonder | menta
  raw_payload   jsonb not null,                      -- normalized rail notification
  status        text not null default 'pending',     -- pending | processing | processed | failed
  attempts      int not null default 0,
  created_at    timestamptz not null default now(),
  processed_at  timestamptz,
  unique (correlation_id, acquirer)                  -- dedup at ingest
);

The worker pulls with ... where status='pending' for update skip locked limit N, takes a pg_advisory_xact_lock(hashtext(entity_id)) to preserve per-entity ordering, runs the orchestrator transaction, and marks processed.

transactions (attempt-level analytics store)

Why this table exists

The journals ledger only records successful, captured financial events. But most dashboard metrics — acceptance rate, decline reasons, status distribution, 3DS, BIN/issuer performance, Guardian block rate — need every attempt, including declined, failed, pending, and expired, which never become journals. So Vecnet persists every rail notification (all outcomes) here. journals = money; transactions = attempts. Both are joined on correlation_id.

create table transactions (
  id               uuid primary key default uuid_generate_v7(),
  correlation_id   text not null,
  entity_id        text not null,
  acquirer         text not null,                 -- tonder | menta
  payment_intent_id text,                          -- dedup key: one intent counted once across retries
  status           text not null,                 -- success | declined | failed | pending | expired
  transaction_type text not null,                 -- PAYMENT | REFUND | DISPUTE | WITHDRAWAL | PAYOUT | VOID
  is_apm           boolean not null default false,-- APM vs card (acceptance vs conversion — never blended)
  payment_method   text,                          -- card | spei | oxxopay | mercadopago | ...
  amount           numeric(20,4) not null,
  currency_code    text not null,
  card_brand       text,                          -- Visa | Mastercard | Amex
  bin              text,                          -- card BIN (suspicious-BIN analysis)
  issuing_bank     text,                          -- top/worst issuing banks
  issuing_country  text,                          -- intl acceptance / geographic risk
  is_international  boolean,
  customer_ref     text,                          -- HASHED customer email/id (FTD, active payers) — never raw PII
  is_first_success boolean,                        -- first-ever success for this customer (FTD flag)
  decline_code     text,                          -- top decline reasons
  threeds_outcome  text,                          -- frictionless | challenge | abandoned | success | na
  guardian_decision text,                          -- allowed | blocked | na
  attempt_count    int not null default 1,        -- retries collapsed under one intent
  occurred_at      timestamptz not null,          -- rail event time
  created_at       timestamptz not null default now(),
  unique (correlation_id, acquirer)
);
create index on transactions (entity_id, occurred_at);
create index on transactions (entity_id, status);
create index on transactions (entity_id, customer_ref);
create index on transactions (bin);

• Dedup is structural: group by payment_intent_id so retries count once.
• PII: customer_ref is a hash/token, never a raw email — matters doubly in GovTech.
• The orchestrator upserts this row for every event; it creates journals only for the successful financial subset.

Two data sources, one correlation thread

A declined or expired notification produces a transactions row and no journal. This split is the foundation of the metric catalog.