payments — module #19, the money-MOVEMENT layer
9 tables, 159 columns — schema-locked 2026-07-07. payments executes money movement via Stripe Connect — billing (module #18) records what's owed/settled; payments executes the actual charge/refund/payout. v1 had a real, locked Payments module (8 tables / 112 cols, self-verified, locked 2026-06-10) — all 8 preserved, plus 1 NEW table (terminal_reader). Depends on platform, identity (actor), crm (customer), multi_loc (site), shared (currency), pos (sale_payment, register — the Terminal seam), orders (order_payment), billing (ar_payment).
PROJECT_DECISIONS entry: #33.
Global rules for this schema:
- The money boundary, designed once.
billingRECORDS what's owed/settled;paymentsEXECUTES the actual Stripe charge/refund/payout;platformis Vrida's own unrelated SaaS-subscription revenue (a direct Stripe integration, structurally unrelated to Connect — never conflated).platform.subscription.status's column comment was corrected at this build to name a distinct Vrida-billing service, notpayments.PaymentsService. payment_intent.source_refis polymorphic — plainuuid, NO single-table FK. Points DOWN at whichever row triggered the charge:pos.sale_payment.id,orders.order_payment.id, orbilling.ar_payment.id. Those tables' existingstripe_payment_intent_idTEXT columns stay passive mirrors, populated (not FK'd) oncePaymentsServiceexists — a text Stripe ID string cannot FK to a UUID PK. Validated bychk_payment_intent_source_pair(below).chk_payment_intent_source_pair— a genuine FIX, not a v1-preserved feature. v1'spayment_intenthad two separate enum CHECKs (source_module,source_type) but never a combined pairing CHECK —source_module='pos'+source_type='ar_payment'was silently insertable. Caught by independent adversarial verification during the design-phase pass (not build time). Mirrorstax.tax_calculation/billing.ar_charge's own source-pair CHECK exactly.source_module/source_typealso widened from v1's 2-way pair to a 3-way pair addingorders/order_payment(v1 predatesorders.order_payment);'sale'renamed to'sale_payment'for accuracy.- The idempotency / double-charge guard, live-tested.
payment_intent's two partial uniques ((tenant_id, idempotency_key)and(stripe_payment_intent_id), bothWHERE ... IS NOT NULL) are single-nullable-column with matching WHERE clauses — checked specifically against the NULL-distinctness bug class that hitbilling.ar_chargethe same day, and confirmed clean (no second nullable column lurking in the tuple).source_refis deliberately NOT unique (multi-tender/partial-payment/retry scenarios legitimately create multiple intents against one source row) — this is v1's own decision, re-confirmed, not a gap. stripe_event_log's global (non-tenant-scoped) unique onstripe_event_id— the ONE deliberate deviation in this codebase from "all uniques are tenant-scoped." Stripeevent.idvalues are globally unique across all Stripe accounts;tenant_idis nullable, resolved post-insert from the Connect account. Preserved verbatim from v1.terminal_readeris NEW — closesOPEN_ITEMSrow 185 (pos.register's v1 hardware-pairing config had no v2 equivalent). Lives here, not onpos.register, since a physical Stripe Terminal reader is inherently a Payments/Stripe concern. CarriesUNIQUE (stripe_reader_id) WHERE deleted_at IS NULL, matching every other Stripe-ID column in this module (a fix caught by adversarial verification — the original design draft omitted it).- 5 hardcoded
'USD'currency defaults removed (global-from-day-1, applied retroactively to a v1 design that predates that convention). automation_sourcedefault split, precisely scoped —'system'(no human decision point in the row's creation — a pure external-system/webhook artifact):stripe_connect_account,payout,dispute.'human'(the row is created as the direct, immediate consequence of a staff/customer action, even though the row can be flagged by an agent afterward):payment_intent,payment_refund,payment_method,terminal_reader. Extendstax.tax_calculation's precedent to 3 more tables (4 total in the codebase).- The never-move-money boundary. No agent may ever execute a charge or refund. Charge execution is system/human-triggered only. Refund execution requires human approval when agent-drafted (
review_status='pending'gatesPaymentsServicefrom ever submitting to Stripe). Agents only ever flag (fraud/anomaly onpayment_intent, reconciliation breaks onpayout, chargeback/evidence-drafting ondispute) — never move money themselves.
Cross-Phase Foreign Keys (payments)
| Column | Target | Notes |
|---|---|---|
*.tenant_id (all 9) |
platform.tenant |
NOT NULL except stripe_event_log/stripe_event_dead_letter (nullable — see below) |
stripe_event_log.tenant_id, stripe_event_dead_letter.tenant_id |
platform.tenant |
nullable — webhook events arrive at the Stripe account level, tenant resolved post-insert |
payment_intent.customer_id, payment_method.customer_id |
crm.customer |
nullable on payment_intent (anonymous POS sales), NOT NULL on payment_method |
payment_intent.site_id, terminal_reader.site_id |
multi_loc.site |
nullable on payment_intent, NOT NULL on terminal_reader |
payment_intent.source_ref |
pos.sale_payment.id / orders.order_payment.id / billing.ar_payment.id |
polymorphic, plain uuid, no FK — validated by chk_payment_intent_source_pair |
payment_refund.payment_intent_id, dispute.payment_intent_id |
payments.payment_intent |
NOT NULL on payment_refund, nullable on dispute |
payment_refund.source_refund_ref |
pos.sale_refund or a billing record |
polymorphic, plain uuid, no FK — traceback only |
terminal_reader.register_id |
pos.register |
nullable — a reader may not always map 1:1 to a specific register |
*.currency_code / default_currency |
shared.currency.iso_code |
char(3), NOT NULL, no default (global from day 1) |
*_actor_id (various) |
identity.actor |
nullable |
billing.ar_payment.stripe_payment_intent_id, billing.ap_payment.stripe_payment_intent_id, pos.sale_payment.stripe_payment_intent_id, orders.order_payment.stripe_payment_intent_id |
— (text mirror, no FK) | populated by PaymentsService, never FK'd — a Stripe ID string cannot FK to a UUID PK |
payments.stripe_connect_account (16 cols, LIGHT)
Per-tenant Stripe Connect account. One row per tenant; tracks onboarding state and the capability flags gating live payment processing.
Tenant-scoped. RLS enabled —
stripe_connect_account_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (4): chk_stripe_connect_account_type; chk_stripe_connect_account_onboarding_status; chk_stripe_connect_account_automation_source; chk_stripe_connect_account_enabled_requires_complete (NEW, Remediation Phase 1).
Indexes: PK; tenant_id; (tenant_id) UNIQUE WHERE not deleted; stripe_account_id UNIQUE WHERE not deleted.
payments.payment_intent (33 cols, FULL)
Central ledger for every incoming charge. source_module/.source_type 3-way widened; chk_payment_intent_source_pair is the adversarial-caught fix (see global rules above).
Tenant-scoped. RLS enabled —
payment_intent_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (13): chk_payment_intent_source_module; chk_payment_intent_source_type; chk_payment_intent_source_pair (NEW fix); chk_payment_intent_amount_positive; chk_payment_intent_total_charged; chk_payment_intent_refunded_le_total; chk_payment_intent_payment_method_type; chk_payment_intent_status; chk_payment_intent_origin; chk_payment_intent_automation_source; chk_payment_intent_review_status; chk_payment_intent_reviewer_not_creator; chk_payment_intent_processor (NEW, Remediation Phase 2).
Indexes: PK; tenant_id; (tenant_id, source_module, source_ref) fan-in; customer_id; status partial (active/needs-attention queue); origin='offline' partial; review_status='pending' partial; stripe_payment_intent_id UNIQUE WHERE not null/not deleted; (tenant_id, idempotency_key) UNIQUE WHERE not null/not deleted — the double-charge guard, live-tested clean of the NULL-distinctness bug class.
payments.payment_refund (21 cols, FULL)
A refund may be partial or full. The clearest C8 boundary in this module — automation_source defaults 'human' (a refund is always a deliberate decision), review_status gates whether an agent-drafted refund may be executed.
Tenant-scoped. RLS enabled —
payment_refund_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (6): chk_payment_refund_amount_positive; chk_payment_refund_reason; chk_payment_refund_status; chk_payment_refund_automation_source; chk_payment_refund_review_status; chk_payment_refund_reviewer_not_creator.
Indexes: PK; tenant_id; payment_intent_id; status; review_status='pending' partial; stripe_refund_id UNIQUE WHERE not null/not deleted; (tenant_id, idempotency_key) UNIQUE WHERE not null/not deleted.
payments.payout (20 cols, FULL)
Thin reference — Stripe is the ledger of what's in each payout. automation_source defaults 'system' — Stripe decides timing/amount, no human decision point in the row's creation. The review seam is the reconciliation-flagging surface.
Tenant-scoped. RLS enabled —
payout_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (5): chk_payout_status; chk_payout_method; chk_payout_automation_source; chk_payout_review_status; chk_payout_reviewer_not_creator.
Indexes: PK; tenant_id; status; arrival_date; review_status='pending' partial; stripe_payout_id UNIQUE WHERE not deleted.
payments.dispute (21 cols, FULL)
Chargeback lifecycle tracking. automation_source defaults 'system' — the customer's bank initiates a chargeback, not Vrida. The review seam is the evidence-drafting surface.
Tenant-scoped. RLS enabled —
dispute_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (5): chk_dispute_status; chk_dispute_automation_source; chk_dispute_review_status; chk_dispute_reviewer_not_creator; chk_dispute_terminal_requires_evidence (NEW, Remediation Phase 1).
Indexes: PK; tenant_id; payment_intent_id; status partial (open disputes); evidence_due_by partial; review_status='pending' partial; stripe_dispute_id UNIQUE WHERE not deleted.
payments.payment_method (15 cols, LIGHT)
Saved card-on-file. Stripe is the card vault — only a PM reference + display-safe metadata (last4, brand) ever enters Vrida (PCI-DSS boundary, v1's own guard, preserved). No review seam.
Tenant-scoped. RLS enabled —
payment_method_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (2): chk_payment_method_type; chk_payment_method_automation_source.
Indexes: PK; tenant_id; customer_id; customer_id UNIQUE WHERE is_default=true/not deleted; stripe_payment_method_id UNIQUE WHERE not deleted.
payments.stripe_event_log (8 cols, ZERO, insert-once/status-updated)
Webhook idempotency log. Insert-once, status-updated — NOT append-only (status/processed_at mutate post-insert). No updated_at/deleted_at at all.
Mixed-scope RLS (nullable
tenant_id). RLS enabled —stripe_event_log_tenant_isolation. No soft delete.
CHECK constraints (1): chk_stripe_event_log_status.
Indexes: PK; tenant_id; event_type; stripe_event_id UNIQUE, global — NOT partial, NOT tenant-scoped (the one deliberate exception in this codebase).
payments.stripe_event_dead_letter (10 cols, ZERO, insert-once/updated-in-place)
Events that failed processing after retries. Insert-once, updated in place — retry_count/last_retry_at/resolved_at all mutate across retries. No updated_at/deleted_at at all.
Mixed-scope RLS (nullable
tenant_id). RLS enabled —stripe_event_dead_letter_tenant_isolation. No soft delete.
Indexes: PK; tenant_id; stripe_event_id; resolved_at partial (open queue); stripe_event_id UNIQUE WHERE resolved_at IS NULL — at most one OPEN dead-letter per event, preventing the double-drive bug.
payments.terminal_reader (15 cols, LIGHT, NEW)
Closes OPEN_ITEMS row 185. A physical Stripe Terminal reader — Payments-owned, referenced by site_id and an optional register_id, no reciprocal column on pos.register.
Tenant-scoped. RLS enabled —
terminal_reader_tenant_isolation. Soft delete:deleted_at.updated_at: trigger-maintained.
CHECK constraints (2): chk_terminal_reader_status; chk_terminal_reader_automation_source.
Indexes: PK; tenant_id; (tenant_id, site_id); stripe_reader_id UNIQUE WHERE not deleted — the adversarial NOTE #9 fix, matching every other Stripe-ID column in this module.
The seams
The source seam. payment_intent.source_ref → pos.sale_payment.id / orders.order_payment.id / billing.ar_payment.id — polymorphic, no FK, validated by chk_payment_intent_source_pair. Live-tested: resolves via JOIN to a real pos.sale_payment row; all 3 valid pairs succeed; the invalid pos+ar_payment combo is rejected.
The idempotency / double-charge guard. Live-tested: a duplicate (tenant_id, idempotency_key) is rejected; a duplicate stripe_payment_intent_id is rejected; two intents sharing the same source_ref with NULL idempotency_key (legitimate multi-tender) are NOT falsely deduped.
The Terminal seam. terminal_reader.register_id → pos.register.id — live-tested, resolves correctly.
The refund needs-approval seam. Live-tested: an agent-drafted refund (automation_source='agent', review_status='pending') records correctly with decision_provenance resolving to identity.actor; PaymentsService's binding contract (not yet built) must never submit to Stripe while review_status='pending'.
Design Patterns Summary
Column-count reconciliation
| Table | Cols |
|---|---|
stripe_connect_account |
16 |
payment_intent |
33 |
payment_refund |
21 |
payout |
20 |
dispute |
21 |
payment_method |
15 |
stripe_event_log |
8 |
stripe_event_dead_letter |
10 |
terminal_reader |
15 |
| Total | 159 |
Verified live: 9 tables, 159 columns (158→159 after Remediation Phase 2, below), 38 CHECK + 32 FK constraints (35→37 after Remediation Phase 1, 37→38 after Remediation Phase 2), RLS on all 9; set_updated_at fires on 7 tables (all except the 2 webhook tables, which have no updated_at column at all).
Remediation Phase 1 (2026-07-08)
A cross-cutting senior-architect review of the money-movement layer found two fail-open gaps, closed here with new CHECK constraints (no column/table count change — still 9 tables / 158 cols):
payments.disputegainedchk_dispute_terminal_requires_evidence— live-verified shape:status IN ('won','lost','closed')requiresresolved_at,evidence_submitted_at, andpayment_intent_idall NOT NULL. Closes a gap where a dispute could reach a terminal state with no resolution timestamp, no evidence trail, and no linked charge.payments.stripe_connect_accountgainedchk_stripe_connect_account_enabled_requires_complete— live-verified shape:charges_enabled OR payouts_enabledrequiresonboarding_status = 'complete'. Closes a gap where a Connect account could be flagged as able to move money while onboarding was still incomplete.
Both are additive CHECKs only — no RLS, GRANT, or trigger changes to this module in this pass. Full cross-module remediation record: PROJECT_DECISIONS #37.
Remediation Phase 2 (2026-07-08)
A cross-cutting pass adding a minimal-now de-primitivization anchor ahead of a future multi-processor world:
payments.payment_intentgainedprocessor(text, NOT NULL, DEFAULT'stripe',chk_payment_intent_processorCHECK constrained to('stripe')only for now) — Item 7 of the plan. Deliberately NOT the full vendor abstraction; the 19 otherstripe_*columns across 6 modules, 2 table renames, and this CHECK's own widening are deferred until a second payment processor is actually integrated (seeOPEN_ITEMS.md).payments.stripe_event_logandpayments.stripe_event_dead_letterwere considered for Item 6's UUIDv7 PK widening (id DEFAULT gen_random_uuid()→platform.uuid_generate_v7()) but explicitly EXCLUDED — both mutate fields in place post-insert (stripe_event_log.status/processed_at;stripe_event_dead_letter.retry_count/status/processed_at) despite lackingupdated_at, so neither is genuinely append-only and neither qualifies for the time-ordered-PK rationale.payments.payment_intent.idis correctly UNCHANGED (stillgen_random_uuid()) — it's a mutable ledger (status/review_statustransition post-insert), not an append-only table, so it was never a candidate for the UUIDv7 widening either.- Column count: 158 → 159 (the only column-count change in this phase;
+1onpayment_intent, from 32 to 33 cols). No table added or removed.
Full cross-module remediation record: PROJECT_DECISIONS #38.
Service layer
No PaymentsService yet — schema-only this pass.
JSONB columns
stripe_connect_account.requirements (Stripe's outstanding-requirements object); stripe_event_log.payload/stripe_event_dead_letter.payload (full Stripe event body, retained for reprocessing/debugging); decision_provenance (project-wide convention, all 4 FULL tables).
Open items carried forward (see OPEN_ITEMS.md)
No PaymentsService yet; the status-writeback to pos.sale_payment.status/orders.order_payment.status/billing.ar_payment.status is documented, not built; card-expiry alert job/index (v1's own deferred item); billing.ap_payment.stripe_payment_intent_id stays permanently unwired (deliberate v1 scope boundary, re-confirmed).