Payments data model

Every table money touches, what each meaningful column is for, and which record is written at which stage. Measured against live Dev on 2026-08-17.

Relationships

⚠ Three FK behaviours are deliberate and are not interchangeable.payments.order_id is RESTRICT: an order with money against it cannot be deleted. order_items.order_id is CASCADE: a line has no meaning without its order. orders.buyer_user_id is SET NULL and nullable: an anonymous buyer is first-class, and deleting a consumer account must not delete the merchant's sales record.

orders — 21 columns

The merchant's sales ledger. Two independent status axes, which is the single most important thing to understand about this table: an order can be ready and unpaid, or completed and partly_paid. Collapsing them into one status would make "the idol is carved but not paid for" unrepresentable, and that is the normal Ganapati case.

Column	Purpose
id	uuid, and this is what Razorpay carries as reference_id on the payment link
workspace_id	the merchant. Every RLS policy scopes on it
reference	what the buyer quotes at the counter. Random, never a sequence — a per-workspace sequence lets a competitor measure a rival's volume by ordering twice and subtracting. Crockford base32 omitting I/L/O/U so it is unambiguous read aloud. Unique per workspace
buyer_user_id	nullable. Null = anonymous, set = a registered consumer. Not an addition: an append-only table cannot gain an identity column cheaply
buyer_name · buyer_phone · buyer_email · buyer_note	buyer_phone is NOT NULL: an order with no contact route is not an order, because a stall that cannot reach the buyer cannot say the idol is ready
source	public_card | manual — did it arrive from the card or the counter
status	pending → confirmed → ready → completed, or cancelled. orders_cancelled_has_reason forces a reason
payment_status	unpaid | partly_paid | paid | refunded | failed. ⚠ A cache, derived from the ledger, never set from an event
subtotal_minor · tax_minor · total_minor	integer paise. orders_total_is_subtotal_plus_tax CHECKs the identity. ⚠ tax_minor is currently always 0 — see below
tax_treatment	supplier_not_registered | inclusive | exclusive | exempt | not_computed
collect_on	the agreed collection date, nullable
cancelled_reason	free text, so a row written by support may hold a sentence rather than one of our codes

⚠ tax_minor is hardcoded 0, and the blocker is not the CHECK constraint.place-public-order mints the Razorpay link on subtotalMinor and inserts payments.amount_minor = subtotalMinor, while derivePaymentStatus compares captured against orders.total_minor. They coincide only because tax is zero. The moment it is non-zero, every fully-paid order derives as partly_paid and the Route transfer is computed on the wrong base. That coupling is the real blocker on inclusive tax, and it is why the inputs were snapshotted before the decomposition was attempted.

order_items — 16 columns

Column	Purpose
order_id	CASCADE
item_id · variant_id	SET NULL, and nullable by design: a stall sells things not in the catalogue
name_snapshot	the name at sale time. A join to catalog_items would restate a past sale under today's name
unit · quantity	numeric(12,3) — 0.5 kg of sweets is an ordinary sale
unit_price_minor · line_total_minor	both computed server-side, never accepted from the client
position	assigned server-side so ordering cannot be steered by a caller
hsn_sac	snapshotted GST classification. NULL means the merchant had not classified the item, which is distinguishable from zero-rated
tax_rate_bp	snapshotted rate in basis points. ⚠ Existed since 2026-08-08 and was written by nothing until 2026-08-17
price_includes_tax	whether unit_price_minor was tax-inclusive at sale time

⚠ The three tax fields are the only genuinely lost-forever facts in the whole payment surface. Every provider figure is recoverable from payment_events.payload; no payload anywhere contains our tax fields. They live only in catalog_items, which is merchant-mutable, so the classification at the instant of sale dies silently the first time a vendor edits the item.

payments — 21 columns

The money ledger. One row per attempt, not one per order: a retried link and a counter top-up are both ordinary.

Column	Purpose
order_id	RESTRICT
workspace_id	denormalised from the order deliberately: every policy filters by workspace, and an order never changes workspace, so this is a fixed copy of an immutable fact
provider	razorpay | cash | upi_manual. The two offline values exist because advances are taken in cash and tracked in a notebook, and a payments table that cannot record cash records none of it
provider_payment_id	Razorpay's pay_…. UNIQUE with provider — the replay key. Nullable because the row is created before the provider is called
provider_order_id	Razorpay's order_…
provider_link_id	our plink_…, written at checkout. The only key present on the row before any payment happens
amount_minor	> 0
commission_rate_bp	snapshotted at settlement, so a rate change cannot rewrite history
commission_minor · vendor_minor	payments_split_adds_up: amount = commission + vendor, exactly
refunded_minor	cumulative, <= amount_minor
status	created | authorized | captured | failed | partly_refunded | refunded
failure_reason · captured_at	payments_captured_is_complete forces captured_at and a pay_… on a captured Razorpay row
provider_fee_minor	what Razorpay charged us, inclusive of its GST. Borne by Digious, never the merchant, and not returned on a refund
provider_fee_tax_minor	the GST within the fee. ⚠ Not an expense — a reclaimable input tax credit, an asset
provider_transfer_id	Route's trf_…. Answers "did the vendor actually get paid"

⚠ payments_offline_has_no_commission forces commission_rate_bp = 0 and commission_minor = 0 for cash and UPI. We take no cut of money that never passed through us.

payment_events — 9 columns

Append-only, and the reason most defects here are recoverable rather than fatal.

Column	Purpose
provider + provider_event_id	UNIQUE. A redelivery becomes a constraint violation rather than a second payment
event_type	payment_link.paid | payment.captured | order.paid | payment.failed | refund.processed, plus anything else recorded and ignored
payload	the verbatim body, written BEFORE processing. If processing throws, the row survives intact and is replayable
payment_id	FK SET NULL, so the ledger can be joined to the money it describes
processed_at	set only on success, so a dead letter stays visible as unprocessed
process_error	set on a permanent business failure. Indexed WHERE process_error IS NOT NULL
received_at	arrival time, which is how the event-ordering race was measured

payout_accounts — 13 columns

Column	Purpose
workspace_id	CASCADE
provider_account_id	Razorpay's acc_…. UNIQUE with provider
account_status	created | suspended — Razorpay's view of the account
activation_status	requested | needs_clarification | under_review | activated | suspended — Razorpay's view of whether it can receive money
account_last4 · ifsc	format-CHECKed. ⚠ The full bank account number is deliberately NOT stored

⚠ Two status fields, and only one of them gates a transfer. activation_status = 'activated' is the product gate. An account can exist (account_status = 'created') and still be unable to receive money, and conflating the two would mint payment links that Route cannot settle.

RLS is enabled with no policy, deliberately, matching payment_events: this is platform operations data, the only writer is the service role, and an absent policy looks like a gap to the next reader who will otherwise "fix" it.

workspace_subscriptions and platform_plans

Covered on Subscription payments. workspace_subscriptions is one row per workspace, plan_key FK to platform_plans(key), status in ('active','ended').

workspace_tax_identity_history — 7 columns

The merchant's GSTIN and state code over time, written by the workspaces_record_tax_identity trigger using is distinct from so a no-op update does not create a row. Time-keyed rather than a mutable column because an invoice issued in August must reflect the GSTIN that was in force in August, not today's.

What is written at each stage

Stage	orders	order_items	payments	payment_events
Public order placed	INSERT pending / unpaid	INSERT lines + tax snapshot	—	—
Payment link minted	—	—	INSERT created, split snapshotted, provider_link_id	—
Any webhook arrives	—	—	—	INSERT verbatim, before processing
order.paid correlates	UPDATE payment_status (derived)	—	UPDATE captured, pay_id, captured_at	UPDATE processed_at, payment_id
payment_link.paid (stale)	—	—	UPDATE fee, tax, provider_order_id	UPDATE processed_at, payment_id
payment.captured first	—	—	—	UPDATE process_error (dead letter, noise)
Refund processed	UPDATE payment_status	—	UPDATE refunded_minor (monotonic) + status	UPDATE processed_at, payment_id
Counter payment	UPDATE payment_status	—	INSERT captured, zero commission	—
Order completed	UPDATE status = completed	—	INSERT settlement row if a balance was due	—
Order cancelled	UPDATE cancelled, reason	—	UPDATE refunded_minor if a refund was agreed	—

Razorpay identifiers we store

Razorpay id	Column	Written when
plink_… payment link	payments.provider_link_id	at checkout, by place-public-order
pay_… payment	payments.provider_payment_id	on first correlating webhook
order_… order	payments.provider_order_id	on a webhook carrying it
acc_… linked account	payout_accounts.provider_account_id	when the owner onboards a vendor
trf_… Route transfer	payments.provider_transfer_id	⚠ nothing writes it yet — needs transfer.processed
event id	payment_events.provider_event_id	on every webhook, as the replay key
rfnd_… refund	⚠ not stored — only the cumulative amount is	—