Tutorial: a store with Stripe or Razorpay

create table products (
  id bigint generated always as identity primary key,
  name text not null,
  price_minor int not null check (price_minor > 0),   -- cents or paise
  currency text not null default 'usd'                 -- 'inr' for Razorpay
);
create table orders (
  id bigint generated always as identity primary key,
  owner_id uuid not null,
  product_id bigint not null references products,
  amount_minor int not null,
  currency text not null,
  status text not null default 'pending' check (status in ('pending', 'paid')),
  provider_ref text unique,                              -- Stripe session id or Razorpay order id
  paid_at timestamptz,
  created_at timestamptz not null default now()
);

alter table products enable row level security;
alter table products force row level security;
alter table orders enable row level security;
alter table orders force row level security;

create policy "anyone reads products" on products for select using (true);
create policy "customers read own orders" on orders for select using (owner_id = auth.uid());
-- No insert or update policies: only your functions (with a server key) create and change orders.

// Runs SQL with the server key. Values always go in params ($1, $2, …), never in the SQL text.
export async function sql(statement, params = []) {
  const res = await fetch(`${process.env.SOVARIA_URL}/api/v1/sql/query`, {
    method: 'POST',
    headers: { apikey: process.env.SOVARIA_SERVICE_ROLE_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({ sql: statement, params }),
  })
  if (!res.ok) throw new Error(`SQL failed: ${res.status} ${await res.text()}`)
  const { columns, rows } = await res.json()
  return rows.map((row) => Object.fromEntries(columns.map((c, i) => [c, row[i]])))
}

import { sql } from './db.mjs'

export async function handler(event, context) {
  const userId = context.caller.user_id
  if (!userId) return { error: 'sign in first' }

  const [product] = await sql('select id, name, price_minor, currency from products where id = $1', [event.product_id])
  if (!product) return { error: 'no such product' }

  const [order] = await sql(
    'insert into orders (owner_id, product_id, amount_minor, currency) values ($1, $2, $3, $4) returning id',
    [userId, product.id, product.price_minor, product.currency],
  )

  const form = new URLSearchParams({
    mode: 'payment',
    success_url: 'https://shop.example.com/orders?paid=1',
    cancel_url: 'https://shop.example.com/cart',
    client_reference_id: String(order.id),
    'metadata[order_id]': String(order.id),
    'line_items[0][quantity]': '1',
    'line_items[0][price_data][currency]': product.currency,
    'line_items[0][price_data][unit_amount]': String(product.price_minor),
    'line_items[0][price_data][product_data][name]': product.name,
  })
  const res = await fetch('https://api.stripe.com/v1/checkout/sessions', {
    method: 'POST',
    headers: { Authorization: `Bearer ${process.env.STRIPE_SECRET_KEY}`, 'Content-Type': 'application/x-www-form-urlencoded' },
    body: form,
  })
  const session = await res.json()
  if (!res.ok) return { error: session.error?.message ?? 'Stripe refused the session' }

  await sql('update orders set provider_ref = $1 where id = $2', [session.id, order.id])
  return { url: session.url }
}

sov fn deploy ./create-checkout --name create-checkout
# Signed-in users only, and at most 20 checkouts a minute
sov fn policy-set create-checkout '{"invoke_permission":"authenticated","rate_limit_per_minute":20}'

import { sql } from './db.mjs'

// Don't trust the body: fetch the event back from Stripe by id and act on Stripe's copy.
export async function handler(body) {
  if (typeof body?.id !== 'string' || !body.id.startsWith('evt_')) return { ignored: true }
  const res = await fetch(`https://api.stripe.com/v1/events/${encodeURIComponent(body.id)}`, {
    headers: { Authorization: `Bearer ${process.env.STRIPE_SECRET_KEY}` },
  })
  if (!res.ok) return { ignored: true }      // not a real event of this account
  const event = await res.json()

  if (event.type === 'checkout.session.completed' && event.data.object.payment_status === 'paid') {
    const session = event.data.object
    // Idempotent: a repeated event matches no pending order and changes nothing.
    await sql(
      "update orders set status = 'paid', paid_at = now() where provider_ref = $1 and status = 'pending' and amount_minor = $2",
      [session.id, session.amount_total],
    )
  }
  return { received: true }
}

const { url, error } = await sovaria.functions.call<{ url?: string; error?: string }>('create-checkout', { product_id: 7 })
if (url) location.href = url        // Stripe Checkout, then back to /orders?paid=1