183 lines
12 KiB
Markdown
183 lines
12 KiB
Markdown
---
|
||
title: Payment
|
||
tags: [data-model, postgresql, drizzle]
|
||
aliases: [Payment Record, Escrow, IPayment]
|
||
---
|
||
|
||
# Payment
|
||
|
||
> **Last updated:** 2026-06-06 — MongoDB fully removed; PostgreSQL + Drizzle ORM is the only database layer as of backend v2.9.12.
|
||
|
||
Records every monetary movement in the marketplace: buyer pay-ins, seller payouts, and refunds. The model is centered on Request Network pay-in, in-house checkout metadata, on-chain transaction verification, escrow state, and provider request IDs. The `provider` and `direction` discriminators let one table hold incoming buyer payments, outgoing seller releases, refunds, and legacy/other provider records.
|
||
|
||
> [!note] Runtime store
|
||
> The `Payment` record is stored exclusively in PostgreSQL (`payments` table). Mongoose and MongoDB have been completely removed from the backend as of v2.9.12. The repository factory returns Drizzle repos only. `MONGO_URI` / `MONGODB_URI` / `MONGO_CONNECT_MODE` env vars are obsolete; `PG_URL` is required.
|
||
|
||
> [!note] Source
|
||
> `backend/src/repositories/drizzle/DrizzlePaymentRepo.ts` — Drizzle repository implementation
|
||
> `backend/src/db/schema/` — Drizzle schema definitions
|
||
|
||
> [!note] IDs
|
||
> All primary keys are PostgreSQL UUIDs (`.id` field, string). The legacy MongoDB ObjectId is preserved as `legacy_object_id` for historical lookups only. Marketplace FKs (e.g. `sellerId`) reference `user.pgId` (UUID), not the legacy `_id`.
|
||
|
||
> [!note] `provider` values
|
||
> The backend accepts `request.network`, `amn.scanner`, `shkeeper`, `escrow`, and `other`. `amn.scanner` is the in-house scanner provider used for direct on-chain monitoring. `escrow` is used for internal escrow-native flows. Older docs and some frontend types may still mention historical values such as `test` or `decentralized`; treat those as legacy until their active routes are audited.
|
||
|
||
> [!note] `confirmed` vs `completed` — stats parity
|
||
> Payment stats count both **`confirmed`** and **`completed`** as successful.
|
||
|
||
> [!warning] `SIM_` payment-hash bypass — security concern
|
||
> In both `payment/paymentRoutes.ts` and `marketplace/routes.ts`, a `paymentHash` that starts with `SIM_` (or a short `0x...` hash under 64 chars) is treated as a simulated transaction and **skips on-chain verification entirely** (`isVerified = true`). There is **no environment guard** (e.g. no `NODE_ENV !== 'production'` check) around this branch, so the bypass is reachable in production. ⚠️ A caller can mark a payment verified without any real on-chain settlement.
|
||
|
||
## PostgreSQL schema (Drizzle)
|
||
|
||
| Field | Type | Required | Default | Validation | Index | Description |
|
||
| --- | --- | --- | --- | --- | --- | --- |
|
||
| `id` | UUID (string) | yes | gen_random_uuid() | — | yes (PK) | Primary key. |
|
||
| `purchaseRequestId` | UUID or String | yes | — | — | yes (compound, partial) | Linked [[PurchaseRequest]] id (or template id). |
|
||
| `sellerOfferId` | UUID or String | yes | — | — | — | Linked [[SellerOffer]] id (or template offer ref). |
|
||
| `buyerId` | UUID → [[User]] | yes | — | — | yes (compound) | Buyer paying. |
|
||
| `sellerId` | UUID or String | yes | — | — | yes (compound) | Seller receiving (or template seller). References `user.pgId`. |
|
||
| `amount` | String (decimal) | yes | — | decimal string | — | Settlement amount as a decimal string (e.g. `"12.50"`). |
|
||
| `provider` | String | no | `request.network` | enum: `request.network` / `amn.scanner` / `shkeeper` / `escrow` / `other` | yes (compound, partial) | Payment processor. `amn.scanner` is the in-house scanner pay-in rail. |
|
||
| `direction` | String | no | `in` | enum: `in` / `out` / `refund` | yes (compound, partial) | Flow direction. |
|
||
| `blockchain.network` | String | no | — | — | — | Network identifier. |
|
||
| `blockchain.transactionHash` | String | no | — | — | yes (sparse) | On-chain tx hash. |
|
||
| `blockchain.blockchain` | String | no | — | enum: `ethereum` / `polygon` / `bsc` / `avalanche` / `solana` / `optimism` / `arbitrum` / `base` / `gnosis` | — | Chain. |
|
||
| `blockchain.token` | String | no | — | — | — | Token symbol. |
|
||
| `blockchain.sender` | String | no | — | — | — | Source address. |
|
||
| `blockchain.receiver` | String | no | — | — | — | Destination address. |
|
||
| `blockchain.confirmedAt` | Date | no | — | — | — | When tx confirmed. |
|
||
| `blockchain.confirmations` | Number | no | `0` | — | — | Accepted confirmation count. For settled webhooks this is capped at the effective per-chain threshold rather than an endlessly increasing live block count; payment screens render settled values with a `+` suffix. |
|
||
| `blockchain.blockNumber` | Number | no | — | — | — | Block number of the confirmed transaction. |
|
||
| `blockchain.gasUsed` | Number | no | — | — | — | Gas units consumed by the transaction. |
|
||
| `blockchain.isSimulated` | Boolean | no | — | — | — | True when the payment was created via the `SIM_` hash bypass (no real on-chain tx). |
|
||
| `status` | String | no | `pending` | enum: `pending` / `processing` / `confirmed` / `completed` / `failed` / `cancelled` / `refunded` | yes (compound) | Lifecycle status. Both `confirmed` and `completed` are counted as successful in payment stats. |
|
||
| `escrowState` | String | no | — | enum: `funded` / `releasable` / `released` / `refunded` / `releasing` / `failed` / `cancelled` / `partial` | — | Escrow lifecycle. Note the intermediate states `releasable` (delivery confirmed, ready to pay out) and `releasing` (payout in flight) between `funded` and `released`. |
|
||
| `providerPaymentId` | String | no | — | — | yes (sparse) | External provider id for idempotency. |
|
||
| `metadata.userAgent` | String | no | — | — | — | Browser UA. |
|
||
| `metadata.ipAddress` | String | no | — | — | — | Client IP. |
|
||
| `metadata.walletType` | String | no | — | — | — | Wallet category. |
|
||
| `metadata.paymentMethod` | String | no | — | — | — | Payment method label. |
|
||
| `metadata.shkeeperUrl` | String | no | — | — | — | Invoice URL. |
|
||
| `metadata.shkeeperInvoiceId` | String | no | — | — | — | Invoice id. |
|
||
| `metadata.shkeeperData` | JSONB | no | — | — | — | Raw provider payload. |
|
||
| `metadata.shkeeperStatus` | String | no | — | — | — | Provider status string. |
|
||
| `metadata.balanceFiat` | String | no | — | — | — | Fiat-equivalent balance. |
|
||
| `metadata.balanceCrypto` | String | no | — | — | — | Crypto balance. |
|
||
| `metadata.cryptoName` | String | no | — | — | — | Crypto label. |
|
||
| `metadata.walletAddress` | String | no | — | — | — | Wallet address. |
|
||
| `metadata.shkeeperTaskId` | String | no | — | — | — | Payout task id. |
|
||
| `metadata.requestNetworkRequestId` | String | no | — | — | — | Request Network request id. |
|
||
| `metadata.requestNetworkPaymentReference` | String | no | — | — | — | Request Network payment reference. |
|
||
| `metadata.requestNetworkSecurePaymentUrl` | String | no | — | — | — | Request Network secure payment URL. |
|
||
| `metadata.requestNetworkData` | JSONB | no | — | — | — | Raw Request Network payload. |
|
||
| `metadata.transactionSafety` | JSONB | no | — | — | — | Last Transaction Safety Provider decision, checks, evidence, and blocker reason. |
|
||
| `metadata.derivedDestination` | JSONB | no | — | — | — | Snapshot of per-payment derived destination address/path/index/chain. |
|
||
| `metadata.lastWebhookAt` | Date | no | — | — | — | Last webhook timestamp. |
|
||
| `metadata.webhookPayload` | JSONB | no | — | — | — | Last webhook body. |
|
||
| `metadata.createdVia` | String | no | — | — | — | Origin marker. |
|
||
| `metadata.payoutType` | String | no | — | — | — | Payout sub-type. |
|
||
| `metadata.error` | String | no | — | — | — | Last error message. |
|
||
| `metadata.failedAt` | Date | no | — | — | — | When it failed. |
|
||
| `quote.quoteId` | String | no | — | — | — | `payment_quotes.id` (UUID) when a Postgres quote row exists. |
|
||
| `quote.pricingCurrency` | String | no | — | — | — | Seller offer currency used for the quote. |
|
||
| `quote.offerAmount` | String | no | — | decimal string | — | Seller obligation in `pricingCurrency`. |
|
||
| `quote.invoiceUSD` | String | no | — | decimal string | — | `offerAmount × fxRate` at quote time. |
|
||
| `quote.fxRate` | String | no | — | decimal string | — | Pricing currency to USD rate. |
|
||
| `quote.fxSource` | String | no | — | — | — | FX provider id. |
|
||
| `quote.tokenPriceUsd` | String | no | — | decimal string | — | Settlement token USD price used for depeg protection. |
|
||
| `quote.depegSource` | String | no | — | — | — | Depeg/token-price provider id. |
|
||
| `quote.rawSettleAmount` | String | no | — | decimal string | — | Exact `invoiceUSD / tokenPriceUsd` before rounding. |
|
||
| `quote.settleAmount` | String | no | — | decimal string | — | Final token amount after seller-protective rounding. |
|
||
| `quote.roundingBps` | Number | no | — | integer bps | — | Upward rounding applied. |
|
||
| `quote.depegAdjustmentBps` | Number | no | — | integer bps | — | Absolute deviation from USD peg. |
|
||
| `quote.token` | String | no | — | — | — | Settlement token symbol. |
|
||
| `quote.chainId` | Number | no | — | — | — | Settlement chain id. |
|
||
| `quote.fetchedAt` | Date | no | — | — | — | Oracle rate timestamp. |
|
||
| `quote.expiresAt` | Date | no | — | — | — | Quote expiry. |
|
||
| `createdAt` | Date | auto | now() | — | yes (compound) | Row creation timestamp. |
|
||
| `processedAt` | Date | no | — | — | — | When processing started. |
|
||
| `completedAt` | Date | no | — | — | — | When fully settled. |
|
||
| `notes` | String | no | — | — | — | Free-form notes. |
|
||
| `updatedAt` | Date | auto | — | — | — | Last update timestamp. |
|
||
| `legacy_object_id` | String | no | — | — | yes (sparse) | Original MongoDB ObjectId preserved for historical lookups during migration window. |
|
||
|
||
## Virtuals / Computed
|
||
|
||
| Field | Returns | Description |
|
||
| --- | --- | --- |
|
||
| `paymentRef` | `PAY-<LAST_8_OF_ID_UPPERCASE>` | Derived from UUID `id`. Included in API responses. |
|
||
|
||
## Indexes
|
||
|
||
PostgreSQL indexes on the `payments` table:
|
||
|
||
- `{ status, createdAt DESC }` — admin queues.
|
||
- `{ buyerId, status }` — buyer dashboard.
|
||
- `{ sellerId, status }` — seller dashboard.
|
||
- `{ blockchain.transactionHash }` (sparse) — webhook lookup by hash.
|
||
- `{ providerPaymentId }` (sparse) — provider idempotency.
|
||
- `{ buyerId, purchaseRequestId, provider, direction }` (unique partial: `provider = 'shkeeper' AND direction = 'in' AND status = 'pending'`, name `uniq_pending_shkeeper_by_buyer_session`) — guards against duplicate pending invoices.
|
||
|
||
## Postgres Quote Table
|
||
|
||
Oracle quotes are stored in `payment_quotes`, a 1:1 child table keyed by `payment_id → payments.id`. Amount/rate columns use `numeric(38,18)`. The `payments.legacy_object_id` column supports lookups that originate from legacy references during the migration window.
|
||
|
||
## Relationships
|
||
|
||
- **References**: [[User]] (`buyerId`, `sellerId` via `pgId`), [[PurchaseRequest]] (`purchaseRequestId`), [[SellerOffer]] (`sellerOfferId`).
|
||
- **Referenced by**: Indirectly through [[PurchaseRequest]] status transitions and [[Dispute]] resolution amounts; no table holds a direct foreign key back to `payments`.
|
||
|
||
## State Transitions
|
||
|
||
Payment status:
|
||
|
||
```mermaid
|
||
stateDiagram-v2
|
||
[*] --> pending
|
||
pending --> processing : webhook received
|
||
processing --> confirmed : tx confirmed
|
||
confirmed --> completed : escrow released / payout done
|
||
pending --> cancelled : buyer aborts
|
||
processing --> failed : provider error
|
||
completed --> refunded : dispute resolved
|
||
failed --> [*]
|
||
cancelled --> [*]
|
||
completed --> [*]
|
||
refunded --> [*]
|
||
```
|
||
|
||
Escrow state (for `direction: 'in'`):
|
||
|
||
```mermaid
|
||
stateDiagram-v2
|
||
[*] --> funded : buyer pays
|
||
funded --> releasable : delivery confirmed
|
||
releasable --> releasing : payout initiated
|
||
releasing --> released : payout completed
|
||
funded --> refunded : dispute refund
|
||
releasing --> failed : payout error
|
||
released --> [*]
|
||
refunded --> [*]
|
||
failed --> [*]
|
||
```
|
||
|
||
## Common Queries
|
||
|
||
```ts
|
||
// Buyer history (Drizzle)
|
||
db.select().from(payments).where(and(eq(payments.buyerId, buyerId), eq(payments.direction, 'in'))).orderBy(desc(payments.createdAt));
|
||
|
||
// Seller payouts
|
||
db.select().from(payments).where(and(eq(payments.sellerId, sellerId), eq(payments.direction, 'out'), eq(payments.status, 'completed')));
|
||
|
||
// Webhook lookup
|
||
db.select().from(payments).where(eq(payments.providerPaymentId, providerPaymentId));
|
||
|
||
// Pending escrows ready for release
|
||
db.select().from(payments).where(and(eq(payments.direction, 'in'), eq(payments.escrowState, 'releasable')));
|
||
```
|
||
|
||
Related: [[PurchaseRequest]], [[SellerOffer]], [[User]], [[Dispute]].
|