Database functions (RPCs)

Reference

Your generated backoffice ships with a set of PostgreSQL functions that the app calls through Supabase's supabase.rpc(...). Each one wraps a privileged commerce operation so it runs as one atomic step and applies its own role check. This page lists every function, its arguments, what it does, the role it requires, and the exact error it raises when a precondition fails. All of these live in your own Supabase project, in the code you own.

Why these are functions and not table writes

Two reasons drive every RPC here: atomicity and role checks.

• Atomicity. A stock change is two writes (the running total and the audit row). An order status change is three (lock the row, flip the status, log the transition). Doing them as separate client calls leaves a window where one lands and the other does not. Each RPC runs the whole sequence inside one transaction, so it either all happens or none of it does.
• Role checks. Every function except the order-number generator starts with PERFORM public.require_staff_role(...). Because the functions are SECURITY DEFINER, they run with the table owner's rights, so the role check is the gate, not the row-level security policy.

require_staff_role: the gate every RPC shares

require_staff_role(min_role public.staff_role DEFAULT 'viewer') is the shared role check. It reads the caller's role from staff_roles using auth.uid(), compares it against the minimum you pass in, and either returns nothing or raises an exception. It is defined in migration 20260516000001_init_universal_schema.sql.

The four staff roles rank as integers:

Role	Rank
viewer	1
editor	2
admin	3
owner	4

The check passes when the caller's rank is greater than or equal to the required rank. It fails in two ways:

• The caller has no row in staff_roles: it raises forbidden: not staff with SQLSTATE insufficient_privilege.
• The caller's role ranks below the minimum: it raises forbidden: requires <role> role with the same SQLSTATE.

adjust_stock: atomic inventory change with an audit trail

adjust_stock(p_variant_id uuid, p_location_id uuid, p_delta integer, p_reason text, p_ref_id uuid DEFAULT NULL) moves stock by a signed delta and logs the movement in one call. It is defined in migration 20260516000002_inventory_adjust_rpc.sql and returns the updated inventory_stock row.

It does three things in order:

1. Calls require_staff_role('editor').
2. Upserts inventory_stock for the variant and location, clamping the new quantity at zero so a large negative delta can never push the count below zero (GREATEST(0, quantity + p_delta)).
3. Inserts a row into inventory_movements recording the delta, the reason, and the optional reference id.

If you pass a zero delta it raises delta must be non-zero with SQLSTATE check_violation and writes nothing.

You reach this from the workspace: the Inventory page opens a stock adjustment sheet that posts to the /api/inventory/adjust route, which calls the RPC. The route also validates with Zod first, so a zero delta is rejected at the route with a 400 before it ever reaches the function.

SELECT * FROM adjust_stock(p_variant_id:= '...'::uuid,
 p_location_id:= '...'::uuid,
 p_delta:= -3,
 p_reason:= 'sold'
);

transition_order_status: the order state machine

transition_order_status(p_order_id uuid, p_to_status public.order_status, p_reason text DEFAULT NULL) moves an order to a new status, but only along a legal path, and records the move. It is defined in migration 20260516000007_commerce_rpcs.sql and returns the updated orders row.

The sequence:

1. Calls require_staff_role('editor').
2. Locks the order row with SELECT... FOR UPDATE so two concurrent transitions cannot interleave. If no order matches, it raises order <id> not found with SQLSTATE no_data_found.
3. Checks the move against the legal-transition table below. An illegal move raises illegal transition <from> -> <to> with SQLSTATE check_violation.
4. Updates the status and inserts a row into order_status_transitions with the from-status, to-status, reason, and the acting user.

The allowed transitions are:

From status	Allowed next status
pending	paid, cancelled
paid	fulfilled, refunded, cancelled
fulfilled	refunded
refunded	none (terminal)
cancelled	none (terminal)

The same table is mirrored in TypeScript at src/lib/orders/state-machine.ts as a canTransition helper. That helper is a client-side short-circuit that produces a friendlier message and saves a roundtrip on obviously illegal requests; the function in the database is the source of truth. You reach the RPC through the /api/orders/[id]/transition route from the order detail page.

recompute_customer_aggregates: refresh spend and order count

recompute_customer_aggregates(p_customer_id uuid) rebuilds a customer's total_spent_cents and orders_count from the canonical orders table, so the stored aggregates never drift. It is defined in migration 20260516000007_commerce_rpcs.sql and returns the updated customers row.

It calls require_staff_role('viewer'), then sums total_cents and counts orders for that customer where the status is paid or fulfilled, and writes both numbers back. This is the one mutating RPC gated at viewer rather than editor, because it only restates derived totals from data that already exists. You reach it through the /api/customers/[id] route.

moderate_review: approve, spam, or delete in one entrypoint

moderate_review(p_review_id uuid, p_action text) is the single gated action for review moderation. It is defined in migration 20260625000000_backoffice_rebuild_phase0.sql and returns the affected reviews row.

It calls require_staff_role('editor'), then branches on the action:

• delete removes the row.
• approve sets the status to published.
• spam sets the status to spam.

Any other value raises unknown moderation action <action> with SQLSTATE check_violation. If the id matches no review, it raises review <id> not found with SQLSTATE no_data_found. You reach it from the Reviews page, where each card's approve, spam, and delete buttons call a server action that invokes the RPC.

Credential functions: encrypted, admin-only, never read in the browser

Two pairs of functions store secrets as pgcrypto-encrypted JSONB so they never sit in plaintext. All four require require_staff_role('admin'). The encryption key is read from the Postgres setting app.settings.encryption_key, with a development-only fallback sentinel when that setting is absent.

Function	Purpose	Migration
set_payment_provider_credentials(p_slug text, p_credentials jsonb)	Encrypts a payment provider's credentials and stores them on the matching payment_providers row.	20260516000007
get_payment_provider_credentials(p_slug text)	Decrypts and returns a provider's credentials, or {} when none are set.	20260516000007
set_gsc_credentials(p_credentials jsonb)	Encrypts and upserts the Search Console refresh token into gsc_connection.	20260625000000
get_gsc_credentials()	Decrypts and returns the stored Search Console refresh token, or {} when none is set.	20260625000000

The payment setters are called when you save provider keys from the Settings page; the getters are called server-side from the payment webhook routes. The Search Console pair is called from the integration's OAuth callback. The wrappers in src/lib/payments/encrypt.ts import server-only, so a credential read can never run in the browser.

generate_order_number: the order-number default

generate_order_number() produces the human-readable order number used as the default for orders.number. It reads an order_number_prefix from the settings table (falling back to MR-), takes the next value from a sequence, and pads it to four digits, yielding numbers like MR-0001. It is defined in migration 20260516000001_init_universal_schema.sql.

Unlike the others, this function is not SECURITY DEFINER and has no role check. It never runs as a standalone call from the app; it fires automatically as a column default when an order row is inserted.

Functions that exist but the backoffice does not call

redeem_coupon(p_coupon_id uuid, p_order_id uuid, p_customer_id uuid DEFAULT NULL) is defined in migration 20260516000007_commerce_rpcs.sql. It validates a coupon (active, within its window, under its usage cap), increments used_count, and logs a coupon_redemptions row, all atomically and gated at editor.

The shipped backoffice does not call this function. Coupon checks inside the dashboard run through the pure TypeScript validator canRedeem in src/lib/coupons/validate.ts, and the backoffice only reads coupon_redemptions for its discount KPIs. redeem_coupon exists for the storefront checkout the AI generates against this database, where a redemption actually happens. Treat it as available to wire, not as an active backoffice control.

Where to go next

• For the tables these functions read and write, see Commerce tables and Catalog tables.
• For the row-level security policies that gate direct table access, see Row Level Security.
• For how your generated app connects to Supabase, see Supabase in your code.