Plexivo Functions

Plexivo Functions run your own server-side code beside your Plexivo Postgres: taking payments, sending emails, calling other APIs, and anything else that needs a secret. Each function knows who called it and can query your database as them, so row-level security still applies.

Set up the runtime#

Functions run in a Plexivo App Runner service of your own, isolated and asleep when idle, and billed as that service. You set it up once per database:

1. Turn on the Data API on the Connect tab. Functions use the same project endpoint and keys.
2. Open the Functions tab and copy the Plexivo Functions image shown under Set up the runtime.
3. Create a public Plexivo App Runner service from that image, on port 8000. Plexivo gives it its HTTPS address and certificate.
4. Choose it as the Service and choose Connect runtime.

Write a function#

Choose New function, give it a Name such as place-order, write it, and choose Deploy. A function is a module whose default export takes the request and a context. The examples on this page use these tables:

create table products (id bigint primary key, name text not null, price numeric(10, 2) not null);
create table orders (
  id bigint generated always as identity primary key,
  customer uuid not null,
  total numeric(10, 2) not null,
  status text not null default 'placed',
  created_at timestamptz not null default now()
);
alter table products enable row level security;
alter table orders enable row level security;
create policy products_read on products for select using (true);
create policy orders_own on orders for select to authenticated using (customer = auth.uid());
insert into products values (1, 'Assam tea', 4.50), (2, 'Mug', 8.00);

Customers can read their orders but not write them. The function prices the order as the customer, then saves it with full access:

export default async (req: Request, ctx) => {
  const { items } = await req.json();

  // As the caller: row-level security applies
  const products = await ctx.plexivo.from('products').whereIn('id', items.map((i) => i.product_id));
  const total = items.reduce((sum, i) => sum + Number(products.find((p) => p.id === i.product_id).price) * i.qty, 0);

  // With full access: past row-level security
  const order = await ctx.admin.from('orders').create({ customer: ctx.user.sub, total });

  ctx.log.info('order placed', order.id);
  return { orderId: order.id, total };
};

Return a Response, or any value to send it as JSON. Each deploy is a new version; Versions lets you Roll back to this any earlier one.

Call a function#

import { createClient } from '@plexivo/client';

const db = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await db.auth.signUp({ email, password });

const { data } = await db.functions.invoke('place-order', {
  body: { items: [{ product_id: 1, qty: 2 }, { product_id: 2, qty: 1 }] }
});
console.log(data.total);   // 17

import 'package:plexivo_client/plexivo_client.dart';

final db = PlexivoClient(dataUrl, publishableKey);
await db.signUp(email: email, password: password);

final res = await db.functions.invoke('place-order', body: {
  'items': [{'product_id': 1, 'qty': 3}],
});
print(res.data['total']);

Over HTTPS, a function is at your Project URL followed by /functions/v1/place-order, with any method and sub-path. By default, Only signed-in users (and servers) may call it over HTTP is on: visitors who have not signed in are refused with sign_in_required, and servers call it with the secret key.

Raw SQL in a function#

ctx.admin runs SQL as your server would, with parameters. Each call is one transaction, and reaches your database through its connection pooler when pooling is on:

export default async (req: Request, ctx) => {
  const since = new Date(Date.now() - 86_400_000);
  const rows = await ctx.admin.sql`
    select status, count(*)::int as orders, sum(total)::text as revenue
    from orders where created_at >= ${since} group by status`;

  // Settings and locks that must hold for several statements: one call
  await ctx.admin.sqlTransaction([
    "set local statement_timeout = '5s'",
    'select pg_advisory_xact_lock(42)',
    { sql: "update orders set status = 'expired' where status = $1 and created_at < now() - interval '7 days'", params: ['placed'] }
  ]);

  return { rows };
};

// From your server, with the secret key
const server = createClient(process.env.PLEXIVO_DATA_URL, process.env.PLEXIVO_SECRET_KEY, { persistSession: false });
const { data: report } = await server.functions.invoke('daily-report', { method: 'GET' });
console.log(report.rows);

Because each call is its own transaction on a pooled connection, nothing from one call is left for the next:

• Set options with set local inside one sqlTransaction, not with set in a call of its own.
• Use transaction-level advisory locks (pg_advisory_xact_lock), which end with the call.
• A temporary table lasts only for its call; keep the statements that use it together.
• LISTEN does not carry over between calls. For live changes, use Realtime or a trigger on row changes, below.

Triggers#

Schedule (cron, UTC)

Five-field cron in UTC, such as */15 * * * *, or @hourly, @daily, @weekly, @monthly and @yearly. Scheduled runs act as the service role. 03:30 UTC is 09:00 in India.

Run when rows change

Pick a table and insert, update or delete, up to 20 entries. ctx.event holds the new row as record and, for updates and deletes, the previous row as old_record.

Run when a user signs up

Runs for each new user, for a welcome email or a profile row.

Row changes and sign-ups are recorded in the same transaction as the change, so none is lost. Each is delivered at least once and retried with growing pauses, up to 8 attempts, until the function answers with success, so make your function safe to run twice for the same event_id.

Secrets#

Add secrets under Function secrets: a NAME in capitals and its value. Every function reads them from ctx.env, and values are never shown again. Names starting with PLEXIVO_ are reserved. Up to 100 secrets, each up to 8 KB.

Test and watch#

Test runs the function for real, As a visitor or As the service role, and shows its response and logs. Recent runs lists every run with its status and time; logs are kept for 7 days.

Limits#

• Time limit (ms): 10 seconds by default, up to 60 seconds.
• Request body: up to 6 MB.
• Code: up to 512 KB per function. Names are lowercase letters, digits and dashes, starting with a letter.
• Each run is isolated: it can reach the network, but not the file system or other processes.

Next steps#