Database Schema
The commerce domain uses four primary tables: orders, users_plans, payments, and shipping_notes. Each represents a distinct layer of the transaction lifecycle.
Entity Relationships
Orders
An order is the root purchase entity. It is normally created before the user pays. The subscription flow for Yuno is the exception: there, the order is created inside the recurring payment service after the user completes the Yuno checkout. The Yuno one-off (OTT) flow follows the standard sequence and creates the order before the payment call. See Yuno Checkout Flow.
Key columns:
| Column | Description |
|---|---|
uuid | Public identifier used in URLs and session storage |
gateway_type | Payment source: stripe, mercadopago, yuno, payu, manual, bulk, external, lti, saml, totalDiscountCoupon |
gateway_key | Gateway's subscription or charge identifier; set after payment completes |
type | internal, permission, report, internal_report, sale. Gates whether a payment row is ever created: sale and report types do, internal and permission do not |
status | pending, approved, paused, cancelled, refunded, expired, error, dispute_lost |
sandbox | Set at creation from the gateway environment; never changes after that |
shipping_information | JSON snapshot of carrier, address, and price for physical orders |
amount | Stored as a string in the currency's main unit (not cents). See "Amount units" below |
Rules:
- An order always has at least one UserPlan. Multiple UserPlan rows represent a multi-item cart.
sandboxis immutable after creation. A live order cannot become a sandbox order and vice versa.- Duplicate
gateway_keyentries are blocked at the model level. - Pending orders older than 6 months are automatically pruned. Associated UserPlan rows are deleted first.
Historical records may carry gateway_type = 'paypal'. PayPal is a deprecated gateway; see Deprecated gateways.
User Plans (users_plans)
A UserPlan row is one line item in an order: the binding between a user, a plan or product, and the billing terms. The table name users_plans is a legacy name; the concept is "order line item."
Key columns:
| Column | Description |
|---|---|
order_id | Parent order |
plan_id | The Plan being purchased |
issue_id | The Issue the user gets direct access to |
plan_type | single, retail, prepaid, recurring |
status | pending, approved, paused, cancelled, expired |
valid_from / valid_to | Access window; valid_to is null for perpetual access |
interval | month or annual; null for one-off types |
paused_history | JSON array of pause intervals; used to calculate expected payments |
Rules:
- By convention,
plan_type = singleandplan_type = retailare paired with anissue_idso the line item grants access to a specific piece of content. The pairing is not enforced by a database or application constraint: a derivedassigns_issueflag is true only when both conditions hold, butissue_idcan be null and no save will fail. plan_type = recurringis the only cancellable and pausable type. One-off types (single,prepaid,retail) are neither.valid_tosemantics:single: always null (access is perpetual).prepaid: fixed date set at creation.recurring: null while active; set when the subscription ends.
- Paused periods are tracked so that payment-count checks don't incorrectly flag a paused subscription as behind on payments.
Payments
Each row is an immutable financial event. Financial fields are never mutated after insert; the table is treated as an append-only ledger. See Payment Ledger Model for the full explanation.
Key columns:
| Column | Description |
|---|---|
order_id | Parent order |
user_plan_id | Associated UserPlan; null for shipping payments |
gateway_id | FK to the gateways table row, distinct from gateway_type |
gateway_type | String identifier of the payment source (mirrors the Order's value) |
gateway_transaction_id | Gateway's transaction identifier |
gateway_key | Gateway's identifier for the operation (subscription/preapproval for recurring, payment id for one-offs) |
gateway_status | Raw status string returned by the gateway, before mapping to the platform status |
status | pending, approved, cancelled, refunded, error, dispute_lost |
plan_type | Mirrors the UserPlan type; shipping for carrier-cost rows |
sale_type | retail, subscription, external, shipping |
recurring_cycle | 1-based cycle counter; null for one-off payments |
payment_payload | JSON field for additional event metadata; see the Glossary entry for the schema (refunds store the source payment id here) |
deleted_at | Soft delete timestamp; rows are soft-deleted, not hard-deleted |
Rules:
- Financial fields are not mutated after insert. The same gateway event delivered twice produces one row, not two.
statusis part of the idempotency key. Apendingand anapprovedevent for the same transaction are two separate rows.- Refunds are new rows with
status = 'refunded'; the original approved row is never modified. The id of the row being refunded lives insidepayment_payload(see the Glossary entry forpayment_payload). user_plan_id = nullwithplan_type = 'shipping'marks a carrier-cost row with no plan backing. See Shipping Payment.recurring_cycleon a shipping row is resolved by proximity to the companion product payment for the same transaction (matched by timestamp).- The table uses soft deletes. Queries that need to see deleted rows must call
withTrashed().
Amount units
Amount columns on payments are integers in the currency's subunit (cents):
amount_in_cents,tax_in_cents,gross_sale_in_cents,gross_sale_in_usd_cents,net_sale_*_in_cents,cogs_in_cents,gateway_fee_in_cents, and similar*_in_centsfields.
In contrast, orders.amount is stored as a string in the currency's main unit. When mixing the two in a calculation, convert explicitly: a 10-USD order amount is "10.00", not 1000. Zero-decimal currencies (CLP, PYG) require special handling (see How To Add Support For A New Currency).
Shipping Notes
A shipping note represents one physical shipment. Annual subscriptions generate 12 notes over 12 months; monthly subscriptions generate 1 per cycle.
Key columns:
| Column | Description |
|---|---|
order_id | Parent order |
payment_id | The shipping payment row that triggered this shipment. Nullable; rows created before the November 2025 migration may have null payment_id |
carrier_id | Selected carrier |
status | pending, order-generated, in-transit, pickup, delivered, completed, cancelled |
shipping_information | Snapshot of address and carrier details at creation time |
period_length | Total shipments for the subscription cycle: 1 (monthly) or 12 (annual) |
shipment_number | Position in the cycle: 1 to 12 |
Rules:
- Shipment #1 is created synchronously when the shipping payment is approved.
- Shipments #2–#12 for annual subscriptions are created one per month by a background job. The job checks whether the previous shipment is at least 27 days old before creating the next.
shipping_informationis a snapshot. Address changes after creation do not affect existing notes.- A notification is dispatched to the user after each note is created.
Relationship summary
| From | Cardinality | To | Notes |
|---|---|---|---|
orders | 1:N (min 1) | users_plans | One per cart line item |
orders | 1:N | payments | All payment events for this order |
orders | 1:N | shipping_notes | Physical orders only |
users_plans | N:1 | plans | Required |
users_plans | N:0..1 | issues | Required for single and retail types |
users_plans | 1:N | payments | One per billing cycle |
payments | N:0..1 | users_plans | Null for shipping payments |
payments | 1:N | shipping_notes | Shipping payments only |
shipping_notes | N:1 | shipping_carriers | Required |