A backend with pgapi: project, tables, RLS and supabase-js

pgapi is the multi-project, Supabase-compatible backend platform we run on our own server. Every project gets its own PostgreSQL database, authentication, REST and GraphQL API, file storage, realtime channels and an Edge Functions layer. This post shows how we set up a new application’s backend from start to finish.

Short answer: create the project, write the schema as migration files, add row level security (RLS) policies to every table, generate TypeScript types and connect the app with the official supabase-js client. The application code is written as if it talked to Supabase; only the project URL changes.

1. Create the project

A project is created from the panel or the command line. Our example project id is magaza:

pgapi create magaza Magaza_app https://magaza.com
pgapi init-project magaza

init-project drops a supabase/ folder, an example env file and project docs into the repository. The project’s API lives at https://pgapi.venus.tr/magaza.

2. Write the schema as migrations

Database changes are made with SQL files under version control, never by hand. A file under supabase/migrations/:

create table public.products (
  id bigint generated always as identity primary key,
  name text not null,
  price numeric(10,2) not null check (price >= 0),
  owner_id uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

alter table public.products enable row level security;

create policy "Anyone can read products"
  on public.products for select
  using (true);

create policy "Owners insert their products"
  on public.products for insert
  with check (owner_id = auth.uid());

create policy "Owners update their products"
  on public.products for update
  using (owner_id = auth.uid());

Then:

pgapi migrate magaza
pgapi types magaza > src/types/database.ts

RLS is not negotiable for us: every table in the public schema is protected by explicit policies. A table without a policy returns no rows to the anonymous key.

3. Connect from the app

The browser or mobile app only knows the project URL and the anonymous key:

import { createClient } from '@supabase/supabase-js';
import type { Database } from './types/database';

const supabase = createClient<Database>('https://pgapi.venus.tr/magaza', import.meta.env.PUBLIC_ANON_KEY);

await supabase.auth.signInWithPassword({ email, password });
const { data } = await supabase.from('products').select('id, name, price');

Uploads, realtime subscriptions and function calls use the same client:

await supabase.storage.from('images').upload(`${user.id}/cover.png`, file);

supabase
  .channel('orders')
  .on('postgres_changes', { event: 'INSERT', schema: 'public', table: 'orders' }, console.log)
  .subscribe();

await supabase.functions.invoke('checkout', { body: { cartId } });

4. What stays on the server

The service_role key, the JWT secret and the database URL live only on the server side, in environment variables. Code shipped to the browser contains only the anonymous key; RLS policies decide what it may do. .env files never enter the repository.

5. Scheduled jobs and webhooks

A nightly report, a renewal reminder or a notification to an outside service does not need a separate server. With pg_cron and pg_net it is written inside the database:

select cron.schedule('nightly-report', '0 3 * * *', $$
  select net.http_post(
    url := 'https://pgapi.venus.tr/magaza/functions/v1/nightly-report',
    headers := '{"Content-Type": "application/json"}'::jsonb
  );
$$);

Summary

On pgapi, an application’s backend is built from migration files, RLS policies and the official Supabase client. The application code stays portable, and behind the infrastructure stands the team that built it and watches it. See our backend platform page for details.