Commerce tables

Reference

This page documents the commerce tables MaShop creates in your own Supabase project: orders, customers, coupons, shipping, and payment providers and transactions. Every table here lives in the public schema of the database MaShop provisions under your account. You own the rows; MaShop never reads them. For the catalog side (products, variants, categories, inventory) see Catalog tables. For the security model see Row Level Security and RPCs.

How money and ownership work

Every monetary column is a bigint of minor units (cents), never a float. A price of 19.99 USD is stored as 1999. The orders.currency and payment_transactions.currency columns default to 'USD' and hold an ISO 4217 code. Render cents to a display string in your own code; the database stays integer-exact.

Every commerce table has Row Level Security enabled. None of them are readable by the anonymous storefront role. Only signed-in staff reach these rows: any staff role can read, and the owner, admin, and editor roles can write. Two tables tighten writes to owner and admin only: payment_providers and shipping_carriers, because they hold encrypted credentials.

customers

One row per buyer. The email column is citext and UNIQUE, so a buyer is deduplicated case-insensitively. Addresses are stored as jsonb blobs (billing_address, shipping_address), each defaulting to {}.

Column	Type	Notes
id	uuid	Primary key, gen_random_uuid()
email	citext	NOT NULL, UNIQUE
full_name	text	Nullable
phone	text	Nullable
billing_address	jsonb	Defaults to {}
shipping_address	jsonb	Defaults to {}
accepts_marketing	boolean	Defaults to false
total_spent_cents	bigint	Aggregate, kept fresh by an RPC
orders_count	integer	Aggregate, kept fresh by an RPC
created_at	timestamptz	Defaults to now()

The two aggregate columns are not maintained by triggers. They are recomputed on demand by the recompute_customer_aggregates(p_customer_id uuid) RPC, which sums total_cents across the customer's orders whose status is paid or fulfilled and writes back both total_spent_cents and orders_count. The backoffice calls it through supabase.rpc('recompute_customer_aggregates',...). The RPC requires at least the viewer role.

orders

One row per order. Each order carries its own money breakdown in cents, a status, a snapshot of both addresses, and an optional link to a customer.

Column	Type	Notes
id	uuid	Primary key
number	text	NOT NULL, UNIQUE, auto-generated
customer_id	uuid	FK to customers, ON DELETE SET NULL
status	order_status	Enum, defaults to pending
subtotal_cents	bigint	Line items before tax, shipping, discount
tax_cents	bigint
shipping_cents	bigint
discount_cents	bigint
total_cents	bigint	Final amount
currency	text	Defaults to 'USD'
payment_provider	text	Slug of the provider used, nullable
payment_ref	text	Provider-side reference, nullable
shipping_address	jsonb	Snapshot, defaults to {}
billing_address	jsonb	Snapshot, defaults to {}
notes	text	Nullable
created_at	timestamptz	Defaults to now()

The number column has a default that calls generate_order_number(). That function reads the order_number_prefix key from the settings table, falls back to MR- when the key is absent, and appends a zero-padded counter from a sequence. The first order is MR-0001 unless you change the prefix setting.

order_items

Line items belong to an order through order_id with ON DELETE CASCADE: deleting an order deletes its items. Each item snapshots the SKU, name, and unit price at purchase time, so later edits to the product never rewrite history.

Column	Type	Notes
order_id	uuid	FK to orders, cascade on delete
product_id	uuid	FK to products, ON DELETE RESTRICT
variant_id	uuid	FK to product_variants, restrict, nullable
sku_snapshot	text	SKU at purchase time
name_snapshot	text	NOT NULL
price_cents_snapshot	bigint	Unit price at purchase time
quantity	integer	CHECK (quantity >= 0)
tax_cents	bigint
discount_cents	bigint

The ON DELETE RESTRICT on product_id means the database refuses to delete a product that any order item references. That protects order history from dangling links.

The order state machine

The order_status enum has five values: pending, paid, fulfilled, refunded, cancelled. Status changes do not happen with a plain UPDATE. They go through the transition_order_status(p_order_id uuid, p_to_status order_status, p_reason text) RPC, which the backoffice calls via supabase.rpc('transition_order_status',...). The RPC locks the order row FOR UPDATE, checks the move against a fixed table of legal transitions, applies it, and writes an audit row.

The legal moves are:

• pending can go to paid or cancelled
• paid can go to fulfilled, refunded, or cancelled
• fulfilled can go to refunded
• refunded is terminal
• cancelled is terminal

Any other move fails. If you call the RPC with an illegal target the database raises illegal transition pending -> fulfilled (with your actual from and to values) under SQLSTATE check_violation. If the order id does not exist it raises order <id> not found under no_data_found. The RPC requires the editor role; a viewer hits forbidden: requires editor role.

// templates/backend/src/lib/orders/mutations.ts
const { data, error } = await supabase.rpc('transition_order_status', {
 p_order_id: orderId,
 p_to_status: 'paid',
 p_reason: 'stripe webhook',
});

The same transition table is mirrored in TypeScript at src/lib/orders/state-machine.ts through canTransition(from, to), nextLegalStatuses(from), and isTerminal(status), so the UI can grey out illegal buttons before it ever calls the database.

order_status_transitions

Every successful status change appends one row here: the order id, from_status, to_status, an optional reason, the acting user (actor_id), and occurred_at. This is the order audit trail. Any staff role can read it; rows are written only by the transition RPC, never directly.

coupons

One row per discount code. The code column is citext and UNIQUE, so codes match case-insensitively. The type column is the coupon_type enum with four values: percent, fixed, free_shipping, and bxgy (buy X get Y).

Column	Type	Notes
code	citext	NOT NULL, UNIQUE
type	coupon_type	Defaults to percent
value	numeric	Percent points or fixed cents, per type
min_subtotal_cents	bigint	Minimum cart subtotal to qualify
max_uses	integer	Nullable; null means unlimited
used_count	integer	Defaults to 0
applies_to	jsonb	Product, category, collection scoping
starts_at	timestamptz	Nullable window start
ends_at	timestamptz	Nullable window end
is_active	boolean	Defaults to true

The backoffice validates a coupon against a cart with the pure function canRedeem(coupon, cart, ctx) in src/lib/coupons/validate.ts. It returns either a computed discountCents or a structured failure reason: coupon_inactive, not_yet_started, expired, max_uses_reached, subtotal_below_minimum, or no_eligible_products. These mirror the checks of the order state machine in spirit: validate first, mutate second.

coupon_redemptions

One row per use of a coupon: coupon_id (cascade on delete), order_id (cascade on delete), an optional customer_id (ON DELETE SET NULL), and redeemed_at. This is the ledger behind a coupon's used_count.

Shipping

Shipping is two tables. A zone groups countries; a method is a rate inside a zone.

shipping_zones

Column	Type	Notes
name	text	NOT NULL
countries	text[]	ISO country codes, defaults to empty array
regions	jsonb	Finer scoping, defaults to {}

shipping_methods

Each method belongs to a zone (zone_id, cascade on delete) and has a rate type. The shipping_rate_type enum is flat, weight, or free_threshold.

Column	Type	Notes
zone_id	uuid	FK to shipping_zones, cascade
name	text	NOT NULL
type	shipping_rate_type	Defaults to flat
rate_cents	bigint	Charge in cents
free_threshold_cents	bigint	Nullable; used by free_threshold type
transit_days_min	integer	Nullable
transit_days_max	integer	Nullable

Payment providers and transactions

You choose which payment provider your store uses. MaShop never sits in the money path and never sees the funds. The backoffice stores one row per configured provider and one row per money movement.

payment_providers

One row per provider you enable, keyed by a unique slug. The provider's API keys live in credentials_encrypted as a pgcrypto-encrypted jsonb blob, never as plaintext columns.

Column	Type	Notes
slug	text	NOT NULL, UNIQUE
display_name	text	NOT NULL
mode	text	CHECK (mode IN ('test', 'live'))
credentials_encrypted	jsonb	pgcrypto cipher, defaults to {}
is_enabled	boolean	Defaults to false
position	integer	Display order

Credentials never travel through PostgREST as readable JSON. They are written and read only through two SECURITY DEFINER RPCs: set_payment_provider_credentials(p_slug, p_credentials) encrypts with pgp_sym_encrypt and stores the base64 cipher; get_payment_provider_credentials(p_slug) decrypts and returns the JSON. Both require the admin role, matching the owner/admin-only write policy on the table. The encryption key comes from the app.settings.encryption_key database setting.

payment_transactions

One row per money movement against an order (order_id, cascade on delete). The type is the payment_tx_type enum (authorization, capture, refund, void) and the status is the payment_tx_status enum (pending, succeeded, failed, cancelled).

Column	Type	Notes
order_id	uuid	FK to orders, cascade
provider_slug	text	NOT NULL
type	payment_tx_type	Defaults to authorization
status	payment_tx_status	Defaults to pending
amount_cents	bigint	Gross amount
fee_cents	bigint	Provider fee; net = amount_cents - fee_cents
currency	text	Defaults to 'USD'
provider_ref	text	Provider-side id, nullable
raw_response	jsonb	Full provider payload, defaults to {}
created_at	timestamptz	Defaults to now()

Net revenue is always derived as amount_cents - fee_cents and never stored as its own column. Your payment webhook populates fee_cents from the provider's balance payload.

Who can read and write

All commerce tables enforce RLS through the staff role model. Reads require any staff role. Writes require owner, admin, or editor on most tables; payment_providers writes require owner or admin. The role check itself lives in require_staff_role(min_role), which raises forbidden: not staff when the caller has no role and forbidden: requires editor role (with the needed role name) when the caller is under-privileged. None of these tables grant the anonymous role any access, so your public storefront cannot read orders, customers, or payment rows. See Row Level Security for the full policy set and RPCs for every SECURITY DEFINER function.