Data model
All tables live in Neon Postgres, defined via Drizzle under apps/api/src/db/schema/. Every table has a uuid primary key and created_at / updated_at timestamps unless noted. Amounts are stored as bigint in minor units (pence for GBP, cent for EUR, centavos for BRL).
Entity relationships
Table detail
users
Identity, tied to a native OAuth provider. No wallet, no on-chain address, no crypto anywhere. See auth.
| Column | Type | Notes |
|---|---|---|
id | uuid | PK — internal, stable, never derived from an OAuth or Infinia identifier |
auth_provider | text | apple | google |
auth_provider_user_id | text | Apple / Google sub claim (stable per user, per provider) |
email | text nullable | May be Apple's private relay; may be null on first Apple sign-in (populated at KYC time) |
display_name | text nullable | Optional, from OAuth id_token or user input |
home_currency | text | GBP | EUR — set once at signup; determines which Infinia account is provisioned |
closed_at | timestamptz nullable | Soft-delete; row retained for regulatory retention window post-closure |
Unique: (auth_provider, auth_provider_user_id).
Sessions are stateless JWTs — no sessions table. Server-side revocation via a KV deny-list on jti is added if/when we need "log out everywhere".
kyc_verifications
One per user. Stores Infinia's account-owner reference plus the verified identity Infinia returned. Storing the identity server-side (rather than only referencing Infinia's owner id) keeps the migration door open — we can replay identity to a new vendor without re-onboarding users. This is a documented trade-off: it costs us real GDPR exposure surface. See privacy for the middle-ground option (store only queryable fields, fetch the rest from Infinia on demand).
| Column | Type | Notes |
|---|---|---|
user_id | uuid | FK → users, unique |
provider_owner_id | text | Infinia's account-owner uuid, unique |
kyc_mode | text | hosted for MVP (later external, self_declared) |
status | text | pending | completed | failed |
level | text nullable | basic | standard | enhanced — reported by Infinia after KYC completes |
verification_url | text nullable | Ephemeral URL for the hosted KYC webview; cleared after completion |
verification_url_expires_at | timestamptz nullable | Set at owner creation |
verified_identity_encrypted | bytea nullable | Identity payload from Infinia (AccountOwnerResponse.individual), encrypted at rest via pgcrypto using PII_ENCRYPTION_KEY. Decrypted only by a specific service role. See privacy. |
completed_at | timestamptz nullable |
provider_accounts
Infinia virtual accounts. One row per user per currency per provider. Cached to avoid re-provisioning and to store the funding instructions we hand back to the user (sort code + account number for GBP; IBAN + BIC for EUR).
| Column | Type | Notes |
|---|---|---|
user_id | uuid | FK → users |
provider | text | infinia (space for other providers under the migration escape hatch) |
provider_account_id | text | Infinia's account id, unique |
country | text | GB | EU | BR |
currency | text | GBP | EUR | BRL |
products | jsonb | Enabled products, e.g. ["PAYINS", "PAYOUTS", "INTERNAL_TRANSFER"] |
status | text | provisioning | active | suspended | closed |
funding_instructions | jsonb | Infinia's response — bank details for the user's home account, unused for BRL |
Unique: (user_id, provider, currency).
Lifecycle:
- Home-currency account (GBP or EUR) — created at KYC completion, before the user ever tries to deposit.
- BRL account — lazily created on first PIX. Costs ~1–2s on that one payment; cached forever after.
user_bank_details
The user's own bank account, for withdrawals. Separate from provider_accounts (which is Infinia's virtual account in the user's name).
| Column | Type | Notes |
|---|---|---|
user_id | uuid | FK → users |
currency | text | GBP | EUR |
account_holder_name | text | |
account_number_encrypted | bytea nullable | GBP only, encrypted at rest via pgcrypto |
sort_code_encrypted | bytea nullable | GBP only, encrypted |
iban_encrypted | bytea nullable | EUR only, encrypted |
bic_encrypted | bytea nullable | EUR only, encrypted |
validated_at | timestamptz nullable | Set when Infinia's Bank Account Validation confirmed the details are reachable |
validation_status | text nullable | valid | invalid — last known result from validation |
Unique: (user_id, currency).
Full values are shown in the UI only during in-app edit (with an explicit "reveal" tap); everywhere else we mask to ****1234. See privacy.
transactions
The user-facing ledger. One row per user-visible action (deposit, PIX payment, withdraw). It's a generic, fiat-first row (Bridge-era shaped, still in use) — per-flow detail lives on the linked detail records (pix_payments, or the withdraw_payout snapshot), not in per-flow columns here. Not double-entry — if we later need accounting-grade rigor, we add a ledger_entries table alongside.
| Column | Type | Notes |
|---|---|---|
user_id | uuid | FK → users |
type | text | deposit | withdrawal | … — a PIX payment is recorded as a withdrawal (ensureLedgerRow) |
status | text | pending | completed | confirmed | failed (there is no settling) |
fiat_amount | numeric nullable | Minor units — for a PIX payment, the merchant BRL in centavos |
fiat_currency | text nullable | gbp | eur | brl |
amount_usdc | numeric nullable | Legacy Bridge column; null for Infinia rows |
provider_event_id | text nullable | Idempotency key for webhook/reconcile upserts — for PIX it is "pix:<pixPaymentId>" |
withdraw_payout | jsonb nullable | Snapshot of the immutable withdraw payout inputs (withdraw only) |
failure_reason | text nullable | Raw provider error, prefixed with provider name ("infinia: DESTINATION_BLACKLISTED") |
The FX quote/transfer ids, payout id, voucher and composed rate for a PIX payment all live on the linked pix_payments row (below), not here. to_address / tx_hash / liquidation_address / bridge_transfer_id are dormant Bridge columns.
Transaction state machines
Deposit (user's bank → Infinia GBP/EUR account):
PIX payment (Infinia GBP/EUR → USDC → BRL → merchant PIX key). Infinia has no direct GBP/EUR→BRL pair, so the conversion runs as two internal-transfer legs through a per-user USDC pivot before the BRL payout. The detailed lifecycle below is pix_payments.status; the linked transactions row keeps only the coarse ledger view — syncTxToPayment holds it pending through converting/paying, then completed on success or failed on any non-success terminal (there is no settling transaction status). (Two-hop rollout is behind the USDC_ACCOUNTS_ENABLED flag.)
A definitive leg-2 failure auto-unwinds: because a point-of-sale flow can't wait on a user retry, the parked USDC is reversed back to the user's home currency and refunded — refunding (PENDING, the reverse USDC→home leg in flight) → refunded (TERMINAL, the user returned EXACTLY what they paid via home_debited, the withdrawal reversed). The unwind fires only on a definitive failure (no funds moved on leg 2); an ambiguous leg 2 stays converting and is re-driven by recovery first, so the USDC is never unwound while leg 2 might still land. payout_failed is the one remaining recoverable, user-retryable state: the BRL is parked in the user's own BRL account and POST /pix/payments/:id/retry re-issues the payout (same originId, no second FX). manual_review is a terminal, non-retryable integrity hold — leg 2 completed but delivered an unexpected amount (must NOT auto-retry, that would convert twice), or the refund couldn't return the full amount paid / the reverse leg failed (with no treasury to top up an adverse-move shortfall yet, the home amount still owed is recorded in refund_absorbed) — a human reconciles it. conversion_failed is a legacy enum value, retained only so historical rows validate; no new payment enters it. failed is terminal and only reached before any net funds move (leg-1 failure leaves GBP/EUR untouched).
Withdraw (Infinia GBP account → user's bank via FPS):
Debits (payments, withdraws) count against the user's balance from pending onwards to prevent double-spend. Only reversed on failed.
pix_payments
PIX-specific detail row, 1:1 with a transactions row of type = payment.
| Column | Type | Notes |
|---|---|---|
transaction_id | uuid | FK → transactions, unique |
user_id | uuid | Denormalised for query convenience |
status | text | PIX lifecycle: quoted | converting | paying | completed, plus the leg-2-failure refund states refunding (reverse USDC→home in flight) and refunded (user returned exactly what they paid), the recoverable payout_failed (BRL parked, payout retryable), terminal non-retryable manual_review (leg 2 completed with an unexpected amount, or a refund that couldn't complete — needs human reconciliation), terminal failed, and the legacy/unused conversion_failed. Finer-grained than the transactions row status. |
pix_key | text | |
pix_key_type | text | cpf | cnpj | email | phone | random |
recipient_name | text nullable | From PIX QR if scanned, or manually entered |
infinia_quote_id | text nullable | Two-hop: leg-1 FX quote (GBP/EUR→USDC). Flag-off single-hop: the one GBP/EUR→BRL quote. Either way, the executable quote |
infinia_transfer_id | text nullable | Two-hop: leg-1 transfer (GBP/EUR→USDC). Flag-off single-hop: the one GBP/EUR→BRL transfer |
infinia_quote_id_2 | text nullable | Leg-2 FX quote (USDC→BRL), written fresh when leg 2 is claimed |
infinia_transfer_id_2 | text nullable | Leg-2 internal-transfer id (USDC→BRL) |
usdc_account_id | text nullable | The user's USDC pivot provider_account_id (non-null ⇒ two-hop row) |
brl_account_id | text nullable | The user's BRL provider_account_id |
amount_usdc | numeric nullable | The intermediate USDC leg amount (6-decimal); reused dormant Bridge column |
source_amount | numeric nullable | The exact customer-facing source amount shown at quote time (home-currency, 2dp, spread applied); the balance check compares against this rather than the lossy amountBrl / exchange_rate reconstruction |
home_debited | numeric nullable | The exact home amount leg 1 debited (home-currency major units, 2 decimals, like amount_brl); the refund unwind is target-anchored on this so it never over-refunds |
refund_quote_id | text nullable | Leg-2-failure unwind: the reverse USDC→home FX quote (doubles as the "refund not yet claimed" marker) |
refund_transfer_id | text nullable | The reverse USDC→home internal-transfer id |
refund_absorbed | numeric nullable | Home-currency amount still OWED to the user on a manual_review refund shortfall; 0 on a clean refunded |
infinia_payout_id | text nullable | Infinia payout id |
voucher_id | text nullable | Payout voucher (receipt proof) |
pix_end_to_end_id | text nullable | From Infinia's payout.updated webhook once settled |
exchange_rate | numeric | The composed customer-facing rate shown at quote time (the GrinGo spread already applied); the only rate persisted |
expire_at | timestamptz nullable | Infinia's rate-lock expiry; execution past it returns 409 quote_expired |
Only the composed exchange_rate is stored — there are no separate spread / quote-rate / execute-rate columns. The receipt reference is the Infinia payout voucher_id.
webhook_events
Idempotency ledger for provider webhooks.
| Column | Type | Notes |
|---|---|---|
provider | text | infinia |
external_event_id | text | Infinia doesn't emit a unique id — we synthesise sha256(kind + ":" + providerRef + ":" + updatedAt) |
event_type | text | e.g. payout.updated, internal_transfer.updated, movement.created |
payload | jsonb | Full raw webhook body — contains third-party PII. Retained 90 days after processed_at (nightly purge), longer only if referenced by a still-open transaction. See privacy. |
received_at | timestamptz | |
processed_at | timestamptz nullable | Set when handler successfully finishes; a mid-processing crash lets the retry re-run |
processing_error | text nullable | Captured for observability |
Unique: (provider, external_event_id).
data_subject_requests
Formal tracking of GDPR data subject requests (access, rectification, erasure, portability, restriction, objection). Backs the DSAR process; we handle requests manually via support@gringo.pay but the table records SLA-relevant timestamps.
| Column | Type | Notes |
|---|---|---|
user_id | uuid nullable | FK → users. Nullable — identity might not yet be verified when a request arrives |
requester_email | text | Used for non-authenticated requests and for the reply-to |
type | text | access | rectification | erasure | portability | restrict | object |
status | text | open | in_progress | completed | rejected |
received_at | timestamptz | Starts the 30-day SLA clock (extendable to 90 days for complex requests) |
completed_at | timestamptz nullable | |
notes | text nullable | Free-form audit trail |
Balance computation
Balance is computed on read, not cached, from the transactions ledger. Credits (completed deposits) minus debits (payments + withdraws in pending, settling, or completed).
SELECT source_currency,
SUM(CASE WHEN type = 'deposit' AND status = 'completed'
THEN source_amount ELSE 0 END)
- SUM(CASE WHEN type IN ('payment','withdraw') AND status IN ('pending','settling','completed')
THEN source_amount ELSE 0 END) AS balance
FROM transactions
WHERE user_id = $1
GROUP BY source_currency;
At MVP scale this is trivially fast. If it ever becomes a hot query, add a materialised balances table refreshed by trigger.
Reconciled nightly against GET /v1/accounts/{id}/ from Infinia. Drift raises an alert; we investigate before shipping any change that could affect the ledger.
Retention
Every table has a documented retention policy; the privacy page is the source of truth. Summary:
| Data | Retention |
|---|---|
KYC records, transactions, pix_payments, user_bank_details | 6 years post users.closed_at (UK MLR / EU AMLD requirement) |
webhook_events | 90 days after processed_at, or until the related transaction closes |
users non-essential fields (email, display_name, home_currency) | Purged immediately on users.closed_at |
users essential fields (id, auth provider ids, closed_at) | 6 years post closure — needed to link retained records |
data_subject_requests | Kept indefinitely for audit |
A nightly retention cron enforces this — purges non-essential columns on closure, expires webhook_events, and hard-deletes the retention-bound tables at 6y+1d after closed_at.
Zod contract
Every API endpoint request/response has a Zod schema in packages/shared/src/. See API endpoints for the full list.