payments — Phase 5

Schema locked 2026-06-10. 8 tables, 112 cols: stripe_connect_account (14), payment_intent (25), payment_refund (15), payout (13), dispute (14), payment_method (13), stripe_event_log (8), stripe_event_dead_letter (10).

Tenant merchant payment processing via Stripe Connect — the tenant's customers pay the tenant (POS card sales, customer A/R payments). NOT Vrida's SaaS subscription billing (tenant pays Vrida) — that is platform. The two never touch. See PROJECT_DECISIONS "Payments Module Scope (Decided 2026-06-10)".

Design rules:

• Idempotency (both directions): stripe_event_log dedups incoming Stripe webhooks by event.id (global unique, not tenant-scoped — Stripe event IDs are globally unique). payment_intent.idempotency_key dedups outgoing charge requests so retries hit Stripe once.
• Offline lifecycle: payment_intent.origin (online/offline) + status tracks the queued-→-executing-→-succeeded/failed arc. An offline POS sale creates a queued intent; on reconnect PaymentsService executes; a failed offline charge (declined after customer left) surfaces via status = 'failed' + failure_reason + status writeback to pos.sale_payment.status.
• Many-intents-to-one-source: partial / multi-tender payments mean multiple payment_intent rows reference the same source record (e.g. three card swipes on one sale each create one payment_intent, all with source_ref = pos.sale_payment.id). The index on (source_module, source_ref) supports this fan-in query.
• Status writeback: on Stripe webhook, PaymentsService updates its payment_intent AND writes status back to pos.sale_payment.status / billing.ar_payment.status. Service-layer only; not FK-enforced.
• Connect gate: stripe_connect_account.charges_enabled must be true before a tenant can take live payments; PaymentsService checks this before every charge.
• Card vault: Stripe is the card vault. payment_method stores a Stripe PM reference + display metadata only (last4, brand). No raw card data ever enters Vrida.

Standard column exception: stripe_event_log is an insert-once, status-updated webhook idempotency log; stripe_event_dead_letter is an insert-once, updated-in-place dead-letter queue. Both carry id, nullable tenant_id, and created_at only — no updated_at / deleted_at. tenant_id is nullable because webhook events arrive at the Stripe account level; tenant_id is resolved from the Connect account and may be null briefly during resolution. These tables are written by the webhook handler via service_role, not by tenant users. stripe_event_log: status (received→processed/ignored) and processed_at (NULL→set) mutate post-insert; the row is inserted once (global unique on stripe_event_id prevents re-insert), then updated with processing outcome. This is the standard webhook-idempotency pattern: dedup on insert, record outcome on update. stripe_event_dead_letter: one row per failed event, updated in place across retries (retry_count, last_retry_at, resolved_at all mutate). Neither table has a deleted_at — events are never deleted.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced (nullable on event log tables — see note above)
payment_intent.customer_id	crm.customer	Locked — enforced (nullable; anonymous POS sales)
payment_intent.site_id	multi_loc.site	Locked — enforced (nullable)
payment_method.customer_id	crm.customer	Locked — enforced
payment_refund.refunded_by_user_id	identity.identity_user	Locked — enforced (nullable)
payment_refund.payment_intent_id, dispute.payment_intent_id	payments.payment_intent	Intra-schema — enforced
payment_intent.source_ref (source_module='pos')	pos.sale_payment.id	SEAM CLOSED 2026-06-10 — sale_payment.stripe_payment_intent_id (text) was a forward-ref; PaymentsService now creates payment_intent and writes status back to sale_payment.status. Polymorphic UUID; cross-schema; not an enforced FK.
payment_intent.source_ref (source_module='billing')	billing.ar_payment.id	SEAM CLOSED 2026-06-10 — ar_payment.stripe_payment_intent_id (text) was a forward-ref; same writeback pattern. Cross-schema; not an enforced FK.
billing.ap_payment.stripe_payment_intent_id	—	NOT CLOSED — vendor A/P payments are manual (check/ACH). That seam stays a nullable text column; Payments does not execute A/P.
payment_refund.source_refund_ref	pos.sale_refund (POS origin) or billing record (Billing origin)	Polymorphic UUID; not enforced FK (cross-schema). Same pattern as payment_intent.source_ref. Documents the originating refund request for traceback.
Stripe-side IDs (stripe_payment_intent_id, stripe_refund_id, stripe_payout_id, etc.)	Stripe (external)	Stored as text — Stripe is system of record; no FK possible

---

payments.stripe_connect_account — 14 cols

Per-tenant Stripe Connect account. One row per tenant; tracks onboarding state and the capability flags that gate live payment processing. charges_enabled = true is the minimum bar for taking payments.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
stripe_account_id	text	NOT NULL	—	Stripe Connect account ID (e.g. acct_...). Stripe is system of record.
account_type	text	NOT NULL	'express'	CHECK IN ('express','standard','custom')
onboarding_status	text	NOT NULL	'pending'	CHECK IN ('pending','in_progress','complete','restricted')
charges_enabled	boolean	NOT NULL	false	Mirrored from Stripe; true = tenant can take live payments
payouts_enabled	boolean	NOT NULL	false	Mirrored from Stripe; true = automatic payouts to bank are active
details_submitted	boolean	NOT NULL	false	Whether the onboarding form has been submitted to Stripe
default_currency	char(3)	NOT NULL	'USD'	ISO 4217 — the account's settlement currency
requirements	JSONB	nullable	—	Stripe's outstanding requirements object; updated on account.updated webhook
connected_at	timestamptz	nullable	—	When charges_enabled first became true

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — one Connect account per tenant
• UNIQUE on (stripe_account_id) WHERE deleted_at IS NULL

---

payments.payment_intent — 25 cols

Central ledger for every incoming charge — the authoritative Vrida-side record of a Stripe PaymentIntent. Links to its source (pos.sale_payment or billing.ar_payment) via source_module / source_type / source_ref. Tracks the full offline-to-online lifecycle.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
stripe_payment_intent_id	text	nullable	—	Stripe's pi_... ID. NULL when intent is queued (offline sale created before execution).
source_module	text	NOT NULL	—	CHECK IN ('pos','billing')
source_type	text	NOT NULL	—	CHECK IN ('sale','ar_payment') — 'sale' = pos.sale_payment; 'ar_payment' = billing.ar_payment
source_ref	UUID	NOT NULL	—	pos.sale_payment.id when source_module = 'pos'; billing.ar_payment.id when source_module = 'billing'. Polymorphic; not enforced FK (cross-schema).
customer_id	UUID	nullable	—	FK → crm.customer; NULL for anonymous POS sales
site_id	UUID	nullable	—	FK → multi_loc.site; NULL for non-POS-initiated charges
amount_cents	bigint	NOT NULL	—	Charge amount excluding tip
tip_amount_cents	bigint	NOT NULL	0	Terminal tip portion. Card-reader tip added at the till; mirrors pos.sale.tip_amount_cents for the card tender.
application_fee_amount_cents	bigint	NOT NULL	0	Vrida's platform fee on this charge (Connect application fee)
total_charged_cents	bigint	NOT NULL	—	amount_cents + tip_amount_cents. CHECK: total_charged_cents = amount_cents + tip_amount_cents.
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
payment_method_type	text	nullable	—	CHECK (payment_method_type IS NULL OR payment_method_type IN ('card_present','card','ach','other'))
status	text	NOT NULL	'queued'	CHECK IN ('queued','executing','requires_action','succeeded','failed','cancelled','refunded','partially_refunded')
origin	text	NOT NULL	'online'	CHECK IN ('online','offline') — set at creation; immutable. Offline intents begin queued; executed on reconnect.
idempotency_key	text	nullable	—	Outgoing-charge dedup key passed to Stripe; stable across retries so Stripe charges once.
failure_reason	text	nullable	—	Terminal failure message (e.g. 'card_declined'). Most significant when origin = 'offline' and the customer has already left.
refunded_amount_cents	bigint	NOT NULL	0	Maintained cache: sum of payment_refund.amount_cents for this intent. CHECK: refunded_amount_cents <= total_charged_cents.
stripe_charge_id	text	nullable	—	Stripe ch_... charge ID from the succeeded PaymentIntent
captured_at	timestamptz	nullable	—	When the charge was captured / confirmed
synced_at	timestamptz	nullable	—	When an offline intent reached Stripe for execution

Table-level CHECKs:

• CHECK (amount_cents > 0) — zero-cent intents are nonsensical
• CHECK (total_charged_cents = amount_cents + tip_amount_cents)
• CHECK (refunded_amount_cents <= total_charged_cents)

Indexes:

• PK on id
• on (tenant_id)
• on (source_module, source_ref) — many-intents-to-one-source fan-in query
• on (customer_id)
• on (status) WHERE status IN ('queued','executing','requires_action','failed') — active / needs-attention queue
• on (origin) WHERE origin = 'offline' — offline charge monitoring
• UNIQUE on (stripe_payment_intent_id) WHERE stripe_payment_intent_id IS NOT NULL AND deleted_at IS NULL
• UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL — outgoing-charge idempotency

---

payments.payment_refund — 15 cols

Payments owns refund execution against Stripe. A refund may be partial (less than the intent total) or full. POS sale_refund and Billing records reference this row for the Stripe execution details; they do not call Stripe directly.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
payment_intent_id	UUID	NOT NULL	—	FK → payments.payment_intent — the charge being refunded
stripe_refund_id	text	nullable	—	Stripe re_... ID; NULL until refund is submitted to Stripe
amount_cents	bigint	NOT NULL	—	Refund amount; must be ≤ intent's total_charged_cents − already_refunded. Enforced at service layer.
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
reason	text	nullable	—	CHECK (reason IS NULL OR reason IN ('requested_by_customer','duplicate','fraudulent','other'))
status	text	NOT NULL	'pending'	CHECK IN ('pending','succeeded','failed','cancelled')
source_refund_ref	UUID	nullable	—	The originating pos.sale_refund.id or billing record for traceback. Plain UUID; polymorphic; not an enforced FK.
idempotency_key	text	nullable	—	Outgoing-refund dedup key passed to Stripe
refunded_by_user_id	UUID	nullable	—	FK → identity.identity_user — who initiated the refund
failure_reason	text	nullable	—	Stripe failure code on status = 'failed'

Indexes:

• PK on id
• on (tenant_id)
• on (payment_intent_id)
• on (status)
• UNIQUE on (stripe_refund_id) WHERE stripe_refund_id IS NOT NULL AND deleted_at IS NULL
• UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL — outgoing-refund dedup; prevents duplicate refund rows corrupting payment_intent.refunded_amount_cents

---

payments.payout — 13 cols

Records Stripe-to-tenant-bank payouts. Thin reference — "when did I get paid." Not a replica of Stripe's payout internals; Stripe is the ledger of what's in each payout.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
stripe_payout_id	text	NOT NULL	—	Stripe po_... ID
amount_cents	bigint	NOT NULL	—	Payout amount in settlement currency
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
status	text	NOT NULL	'pending'	CHECK IN ('pending','in_transit','paid','failed','cancelled')
arrival_date	date	nullable	—	Expected or actual bank arrival date
method	text	nullable	—	CHECK (method IS NULL OR method IN ('standard','instant'))
description	text	nullable	—	Stripe's payout description (often auto-generated)
failure_reason	text	nullable	—	Stripe failure code on status = 'failed'

Indexes:

• PK on id
• on (tenant_id)
• on (status)
• on (arrival_date)
• UNIQUE on (stripe_payout_id) WHERE deleted_at IS NULL

---

payments.dispute — 14 cols

Chargeback lifecycle tracking. Surfaces open disputes for tenant response and evidence submission. Stripe drives the dispute timeline; this table gives the tenant visibility and tracks evidence submission.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
payment_intent_id	UUID	nullable	—	FK → payments.payment_intent — the disputed charge; nullable for disputes Vrida cannot match to a local intent
stripe_dispute_id	text	NOT NULL	—	Stripe dp_... ID
amount_cents	bigint	NOT NULL	—	Disputed amount
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
reason	text	nullable	—	Stripe's dispute reason (e.g. 'fraudulent', 'product_not_received')
status	text	NOT NULL	'needs_response'	CHECK IN ('warning_needs_response','needs_response','under_review','won','lost','closed')
evidence_due_by	timestamptz	nullable	—	Stripe's evidence submission deadline; monitor for alerts
evidence_submitted_at	timestamptz	nullable	—	When the tenant submitted evidence
resolved_at	timestamptz	nullable	—	When the dispute reached a terminal state (won/lost/closed)

Indexes:

• PK on id
• on (tenant_id)
• on (payment_intent_id)
• on (status) WHERE status IN ('warning_needs_response','needs_response','under_review') — open disputes requiring attention
• on (evidence_due_by) WHERE evidence_due_by IS NOT NULL — deadline sweep
• UNIQUE on (stripe_dispute_id) WHERE deleted_at IS NULL

---

payments.payment_method — 13 cols

Saved card-on-file. Light — Stripe is the card vault. Vrida stores a Stripe PM reference and display metadata only. Used for recurring A/R payments, customer-facing saved cards, and returning customer checkout acceleration. No raw card data ever enters Vrida (last4 and brand are display-safe non-sensitive values).

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
customer_id	UUID	NOT NULL	—	FK → crm.customer
stripe_payment_method_id	text	NOT NULL	—	Stripe pm_... ID — the actual card lives at Stripe
type	text	NOT NULL	'card'	CHECK IN ('card','ach','other')
brand	text	nullable	—	Display-only: visa, mastercard, amex, etc. (from Stripe)
last4	text	nullable	—	Display-only: last 4 digits (from Stripe; safe to store)
exp_month	integer	nullable	—	Expiry month for display and expiry alerts
exp_year	integer	nullable	—	Expiry year for display and expiry alerts
is_default	boolean	NOT NULL	false	Whether this is the customer's default payment method for this tenant

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• UNIQUE on (customer_id) WHERE is_default = true AND deleted_at IS NULL — one default per customer
• UNIQUE on (stripe_payment_method_id) WHERE deleted_at IS NULL

---

payments.stripe_event_log — 8 cols

Webhook idempotency log. Every inbound Stripe webhook is inserted here by event.id before processing. Prevents double-processing of retried webhooks. Insert-once, status-updated — no updated_at / deleted_at. The row is inserted once (global UNIQUE on stripe_event_id prevents re-insert); status and processed_at are then updated to record processing outcome (see standard column exception note above).

Access model: written by the webhook handler via service_role, not by tenant users. RLS tenant isolation applies where tenant_id IS NOT NULL; service_role bypasses RLS for the insert step (tenant_id resolved from Connect account post-insert).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant; nullable — events arrive at the Stripe account level; tenant resolved from Connect account; may be NULL briefly after insert
created_at	timestamptz	NOT NULL	now()
stripe_event_id	text	NOT NULL	—	Stripe's evt_... event ID — the global dedup key
event_type	text	NOT NULL	—	e.g. 'payment_intent.succeeded', 'charge.dispute.created'
processed_at	timestamptz	nullable	—	When processing completed; NULL = not yet processed
status	text	NOT NULL	'received'	CHECK IN ('received','processed','ignored')
payload	JSONB	nullable	—	Full Stripe event body; retained for reprocessing and debugging

Indexes:

• PK on id
• on (tenant_id)
• on (event_type)
• UNIQUE on (stripe_event_id) — global dedup; NOT partial, NOT tenant-scoped: Stripe event.id values are globally unique across all accounts; this unique must be unconditional

---

payments.stripe_event_dead_letter — 10 cols

Events that failed processing after retries. Manual investigation and re-drive surface. Insert-once, updated in place — one row per failed event; retry_count, last_retry_at, and resolved_at mutate across retries. No updated_at / deleted_at.

Access model: same as stripe_event_log — service_role writer; tenant_id nullable.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant; nullable for same reason as stripe_event_log
created_at	timestamptz	NOT NULL	now()
stripe_event_id	text	NOT NULL	—	Stripe evt_... event ID
event_type	text	NOT NULL	—	Stripe event type
error	text	NOT NULL	—	Processing failure message or exception
retry_count	integer	NOT NULL	0	How many processing attempts have been made
last_retry_at	timestamptz	nullable	—	Timestamp of the most recent failed attempt
resolved_at	timestamptz	nullable	—	When the dead-letter was re-driven successfully or manually dismissed
payload	JSONB	nullable	—	Full Stripe event body

Indexes:

• PK on id
• on (tenant_id)
• on (stripe_event_id)
• on (resolved_at) WHERE resolved_at IS NULL — open / unresolved dead-letter queue
• UNIQUE on (stripe_event_id) WHERE resolved_at IS NULL — at most one OPEN dead-letter per event; prevents the double-drive bug (two open rows → event reprocessed twice on manual re-drive)

---

Column counts: stripe_connect_account(14) + payment_intent(25) + payment_refund(15) + payout(13) + dispute(14) + payment_method(13) + stripe_event_log(8) + stripe_event_dead_letter(10) = 112

Deferred items:

• payment_method (exp_year, exp_month) expiry index — add when card-expiry alert job is written.

---