# Baselyra — complete documentation

Source: https://baselyra.sarimtools.com/docs/
Generated: 2026-08-24
Licence: Apache-2.0

This file is the entire Baselyra documentation as plain text, in reading order, so a language model can ingest the site in one fetch. Each page is delimited by a rule and carries its canonical URL.

Contents:
 1. Baselyra documentation — Documentation for Baselyra, a self-hosted backend-as-a-service: Postgres with row level security, an auto-generated REST API, auth, storage, realtime and an admin Studio in two containers.
 2. Getting started — What Baselyra is, how a request becomes a Postgres query under row level security, and the order to read these pages in.
 3. Quickstart — From an empty machine to an authenticated API with row level security in about ten minutes, with every request and response shown.
 4. Installation — Install Baselyra with Docker Compose or straight on a host with Node 22 and Postgres 17, and understand what the boot sequence does.
 5. Configuration — Every environment variable Baselyra reads, its default, what it changes, and what happens when it is left unset.
 6. The database — What lives in each schema, how a table becomes an API endpoint, which types cross the wire as strings, and how to evolve the schema safely.
 7. REST API — The auto-generated PostgREST-compatible API over every table, view and function in public: reading, writing, upserts, RPC, headers and errors.
 8. Filtering and paging — Every filter operator the REST API accepts, the value grammar, nested logic trees, ordering, paging and the exact errors a malformed filter produces.
 9. Row level security — The authorisation model: how a request becomes a Postgres role, the helper functions that exist today, five complete policy sets, and what happens when RLS is left off.
10. Auth — Sessions, refresh-token rotation and theft detection, email flows, metadata, admin user management, and the line between Studio accounts and application users.
11. Third-party sign-in — OAuth sign-in with Google, GitHub, LinkedIn, Facebook, Instagram, TikTok and Envato: configuring a provider, the round trip, identity linking and the redirect rules.
12. Phone one-time codes — Sign in with a six-digit SMS code: choosing a sender among Twilio, Vonage, MessageBird, Amazon SNS, Plivo or your own webhook, and the limits that protect your balance.
13. Storage — Buckets on local disk with MIME allowlists and size limits, uploads and downloads with Range and ETag support, signed URLs, and RLS on storage.objects.
14. Realtime — One WebSocket carrying database change feeds, broadcast and presence, with every changed row re-read as each subscriber before it is delivered.
15. AI assistant — The optional DeepSeek routes: natural language to SQL, query explanation, ask-your-database, and a chat relay your app's users can call without the key reaching a browser.
16. Importing — One-click import from Supabase, Postgres, Appwrite, Firebase or a pg_dump file — what each source carries, which passwords survive, and how a run behaves.
17. Client libraries — Which client is official, what the plain-HTTP contract is for every other language, the shared error envelope, and which key belongs where.
18. JavaScript client — The complete reference for @baselyra/client: createClient, the query builder, auth, storage, realtime and AI, with no dependencies.
19. Next.js — Working App Router setup: the browser client, three server clients, sessions in server components, sign-in, and a route handler using the service key.
20. React — A Vite setup, an auth hook, a protected route, a live list, and the remount trap that makes every realtime message arrive twice.
21. Flutter and Dart — A complete Dart client for Baselyra over package:http and web_socket_channel — auth with refresh, queries, storage and realtime.
22. PHP — A complete PHP client for Baselyra over ext-curl with no dependencies: sign-in, queries with counts, writes and RPC.
23. Python — A complete Python client for Baselyra over requests: auth, queries, upserts, a paginating generator, uploads and signed URLs.
24. The Studio — The admin console shipped at /: data grid, SQL editor, users, files, realtime inspector, API reference, email templates, AI panels and the audit log.
25. VS Code extension — Browse the schema, run SQL with diagnostics on the offending token, generate TypeScript types, manage files and watch realtime events without leaving the editor.
26. Self-hosting — Running Baselyra in production: reverse proxies, TLS, email, backups and restores, upgrades, sizing and day-to-day operations.
27. Security — What Baselyra defends and how, what the SQL editor can reach, where each credential lives — and, stated plainly, what is not protected.
28. Troubleshooting — The failures people actually hit — an empty result, a silent realtime feed, a missing email, a Studio that will not open — with the cause and the fix for each.
29. FAQ — Straight answers to the questions people ask first about Baselyra: how it compares, what it does not do, and the decisions that surprise people.

==============================================================================
# Baselyra documentation
URL: https://baselyra.sarimtools.com/docs/
==============================================================================

# Baselyra documentation

A self-hosted backend-as-a-service: Postgres with row level
security, an auto-generated REST API over your tables, authentication, file storage,
realtime and an admin Studio — running as one Node process next to one Postgres
container, in a few hundred megabytes of RAM.

Get started

Row level security
What is not built

## The path

Six pages, in this order. It takes about an hour end to end, and step 2
is the one that stops a public table being world-writable.

-
Install and make the first query

Clone, run ./scripts/setup.sh, docker compose up -d.
Two containers start, migrations apply, and the Studio is on
http://127.0.0.1:3130. Then create a table, mint the keys and read a row.

-
Turn row level security on

Authorisation is Postgres', not JavaScript's. Every request runs as
anon, authenticated or service_role with the
verified JWT claims published to the session, and policies decide the rest. A table in
public with RLS off is readable and writable by anyone holding the anon
key — which is a public key that ships in your frontend.

-
Learn the REST surface

Filters, ordering, pagination, one level of embedded resources, upserts and RPC over
every table, view and function in public. A PostgREST-compatible subset,
so existing client patterns transfer.

-
Add users

Email and password, magic links, one-time codes, recovery and email change, plus
OAuth sign-in and phone codes over
SMS. Refresh tokens rotate and reuse is detected. Read the part about Studio
accounts and application users being two unrelated populations.

-
Store files

Buckets on local disk with MIME allowlists, per-bucket size limits, signed URLs and
Range requests. Policies on storage.objects guard files exactly as they
guard a table.

-
Put it behind a proxy and back it up

nginx or Apache, TLS, SMTP, pg_dump plus the storage volume, and the
security checklist to run before you point a domain at it.

## How the pieces fit

One process serves every prefix. It holds two Postgres connection pools: one to the
project database your application lives in, and one to the control database that holds
Studio accounts and the audit log. Postgres has no cross-database queries without FDW,
so a project connection — even one holding the service key, which bypasses RLS entirely —
cannot read a Studio password hash.

[diagram: A browser calling the Baselyra process over HTTPS with a JWT. The process serves the auth, REST, storage, realtime, admin and AI prefixes, opens a Postgres transaction that sets the request role, and writes uploaded files to a storage volume on disk.]

The whole deployment. Two containers. Every user-facing query runs
inside a transaction that sets the request role and publishes the verified JWT claims as
request.jwt.claims, so the same policies apply through REST, storage and
realtime alike.

## The Studio

An admin console ships inside the server and is served at /: a data grid with
inline editing, a SQL editor with schema-aware autocompletion, user management, a file
browser, a live realtime inspector, a per-table API reference, an email template editor
and the audit log. It signs in against control.platform_users, which lives in
a different database from your data.

[diagram: A mockup of the Baselyra Studio: a 240 pixel sidebar listing Overview, Database, SQL, Auth, Storage, Realtime, API, AI, Email and Settings, with the Database page showing a data grid of the public.posts table.]

Studio, Database page. 240px sidebar, 48px top bar, 32px rows,
monospace for every identifier and value. The SQL editor runs against the project
database only, so select * from control.platform_users fails with
relation does not exist — which is the correct answer.

## Read by topic

### Core

Row level security
The model, the helper functions, and five complete policy sets you can paste.

REST API
Filters, ordering, embeds, upserts, RPC and the error shape.

Auth
Sessions, refresh rotation, email flows, and operators versus users.

Third-party sign-in
Google, GitHub, LinkedIn, Facebook, Instagram, TikTok, Envato.

Phone and SMS
One-time codes over Twilio, Vonage, MessageBird, SNS, Plivo or your own webhook.

Storage
Buckets, uploads, signed URLs, per-user folders.

Realtime
Change feeds, broadcast, presence, and the proxy rule.

### Clients and operations

JavaScript client
Zero dependencies, unchanged in browsers, Node, Deno, Bun and React Native.

Framework integrations
Next.js, Vite, Flutter, Angular, PHP, Python, Expo.

Self-hosting
Proxies, TLS, backups, upgrades, sizing, security checklist.

Webhooks and jobs
Signed HTTP calls on row changes, and cron jobs run in-process.

Importing
Supabase, Postgres, Appwrite, Firebase, pg_dump — and which passwords survive.

## In one request

The anon key is public and meant to ship in your frontend. It says only which Postgres
role the request runs as; the policies decide what that role may read and write.

JavaScript
curl
SQL policy

```ts
import { createClient } from '@baselyra/client';

const bl = createClient('https://api.example.com', ANON_KEY);

const { data, error } = await bl
  .from('posts')
  .select('id, title, author:users(name)')
  .eq('published', true)
  .order('created_at', { ascending: false })
  .limit(20);
```

```bash
curl 'https://api.example.com/rest/v1/posts?select=id,title&published=eq.true' \
  -H "apikey: $ANON_KEY"
```

```sql
alter table public.posts enable row level security;

create policy posts_select_published on public.posts
  for select to anon, authenticated
  using (published);
```

DANGER:
A table in public without row level security is readable and writable by
anyone who has the anon key, and the anon key is in your frontend bundle. Enable RLS on
every table you create, in the same breath as creating it.
Read that page before you ship.

## What Baselyra does not do

Stated here rather than discovered on day two. In
progress means work has started and nothing is shippable yet — plan as though it
does not exist. The full list is on What is not built.

Not available | What you have instead | Status |

Edge functions | Postgres functions over /rest/v1/rpc/:fn | In progress |

S3 or object storage | Files on the local disk of the host running the app | In progress |

Image transformation | Files come back exactly as uploaded | In progress |

A project switcher | control.projects holds one row and every lookup resolves through it, but nothing creates a second | In progress |

A Studio page for webhooks and jobs | Both are configured over /admin/v1/hooks; the console has no screen for them yet | In progress |

Apple sign-in | Seven other OAuth providers, or email and phone | Not built |

Replication and failover | One Postgres; pg_dump plus the storage volume | Not built |

## Questions

### Is Baselyra a Supabase fork?

No. It is a separate implementation that reuses two of Supabase's public interfaces so
client code transfers: a PostgREST-compatible subset for the REST API, and row level
security as the authorisation model. The server is one Fastify process with eight runtime
dependencies — fastify, five Fastify plugins, pg and
nodemailer. No ORM, no query builder, no Redis, no message broker, no sidecar.

### Does Baselyra support Google or GitHub sign-in?

Yes. OAuth ships for Google, GitHub, LinkedIn, Facebook,
Instagram, TikTok and Envato: you add a client id and secret, and the provider writes a
row in auth.identities against the same auth.users record an
email sign-up would have created. Phone one-time codes go
out over Twilio, Vonage, MessageBird, Amazon SNS, Plivo or a webhook you host. There is
no Apple sign-in.

### How much memory does Baselyra need?

Roughly 400–600 MB idle across both containers. 1 GB of RAM is enough to start;
2 GB is comfortable. Sizing is on Self-hosting.

### Can I move an existing project onto Baselyra?

Tables, rows and users import from Supabase, plain Postgres, Appwrite, Firebase or a
pg_dump file. Password hashes survive from Supabase, Postgres and bcrypt SQL
dumps, so those users never have to reset. Appwrite and Firebase use hash schemes Baselyra
cannot verify, so those users need a password reset —
Importing says exactly which, and what to send them.

## For language models

The whole documentation is published as plain text so a model can ingest it in one fetch,
following the llms.txt convention.

- /llms.txt — every page with a one-line summary.

- /llms-full.txt — the entire documentation as one plain-text file.

Every page is a real file with no client-side routing, so any crawler reading HTML gets
the same content a browser does.

==============================================================================
# Getting started
URL: https://baselyra.sarimtools.com/docs/getting-started.html
==============================================================================

# Getting started

Baselyra is a self-hosted backend that runs as two containers: one Node process and one Postgres. This page is the orientation — what the pieces are, which one decides who may read a row, and where to go next. If you would rather type than read, go straight to the quickstart.

## What you get

One Node 22 process serves every API prefix and the admin Studio. It talks to one
Postgres 17 server, which holds two databases: the project database with your
tables, and the control database with Baselyra's own operating data.

[diagram: One Node process serving six URL prefixes talks to one Postgres server holding a project database and a separate control database.]

The whole deployment. Two containers. The split between the two databases is a security boundary, not a convention — Postgres has no cross-database queries without FDW.

Prefix | What it is |

/rest/v1 | An auto-generated REST API over every table, view and function in public. Read from the catalog on demand, so a table created a second ago is queryable now. |

/auth/v1 | Sign-up, sign-in, refresh-token rotation, magic links, one-time codes, phone codes over SMS and third-party sign-in. |

/storage/v1 | Buckets on local disk, with metadata in storage.objects so the same policies guard files as guard tables. |

/realtime/v1 | One WebSocket carrying database change feeds, broadcast and presence. |

/admin/v1 | What the Studio talks to: schema, SQL, users, settings, keys, imports, webhooks and scheduled jobs. |

/ai/v1 | Optional DeepSeek routes. Every one answers 503 until DEEPSEEK_API_KEY is set. |

/health | Liveness plus whether both databases answer SELECT 1. |

/ | The compiled Studio, served last so it never shadows an API prefix. |

## Postgres decides, not JavaScript

This is the single idea the rest of the product is built on. Baselyra performs
no authorisation in JavaScript. No route handler checks ownership and no
query is narrowed for security reasons in the server process. Every request that
touches your data runs inside a transaction that has switched to the caller's
Postgres role and published their verified JWT claims to the session; row level
security policies do the rest.

[diagram: A request carries a key and a token, the server verifies the signature, opens a transaction that sets the Postgres role and publishes the JWT claims, and the policy decides which rows come back.]

The request path. The same five steps run for REST, storage and realtime, which is why one policy governs all three.

What the caller sent | Postgres role | RLS |

Nothing, or the anon key | anon | Enforced |

Authorization: Bearer <user access token> | authenticated | Enforced, and auth.uid() is that user |

The service key | service_role | Bypassed — the role has BYPASSRLS |

A Studio session on /admin/v1 | service_role | Bypassed |

The anon key is public and is meant to be. It is not a password; it only names
the role a request runs as. The service key is the opposite: it reads and writes
every row in the project database, so it belongs on a server and nowhere else.

DANGER:
A new table in public receives default grants for anon and
authenticated. Until row level security is enabled and a policy exists, that table
is readable, writable and deletable by anyone holding your anon key — which is a
public string in your frontend. Two statements make it safe. Read
Row level security before you ship anything.

## Two identities that are not the same

Baselyra keeps operators and application users in two unrelated tables in two
unrelated databases. Conflating them is the mistake this project most wants you
not to make.

| Studio account | Application user |

Who | You, and whoever else operates the instance | The end users of the app you build |

Table | control.platform_users | auth.users |

Database | baselyra_control | baselyra (the project database) |

Signs in at | POST /admin/v1/login | POST /auth/v1/token |

Token carries | role: service_role, typ: "platform" | role: authenticated, sub, app_metadata |

Can open the Studio | Yes | Never |

Visible to /rest/v1 | No — it is in another database | Only through your own policies |

Consequences worth knowing on day one:

- No row in auth.users can ever open the Studio, however it is configured.
Studio tokens carry a typ: "platform" claim inside the signature, and exactly
one endpoint mints it.

- select * from control.platform_users in the SQL editor fails with
relation does not exist. That is the correct outcome: the hashes are in a
database that connection cannot address.

- There is no is_admin column and no auth.is_admin() function. Both were
removed. An application's own admin role lives in app_metadata and is read in a
policy as (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'.

## The shortest real example

A table, a policy, a user, a query. Every line of this works against a fresh
install.

```sql
create table public.posts (
  id         bigint generated always as identity primary key,
  author     uuid not null default auth.uid() references auth.users(id) on delete cascade,
  title      text not null,
  published  boolean not null default false,
  created_at timestamptz not null default now()
);

alter table public.posts enable row level security;

create policy posts_read on public.posts
  for select to anon, authenticated
  using (published or author = (select auth.uid()));

create policy posts_write_own on public.posts
  for insert to authenticated
  with check (author = (select auth.uid()));
```

```bash
curl -s "$URL/rest/v1/posts?select=id,title&published=eq.true" -H "apikey: $ANON_KEY"
```

```json
[{"id":1,"title":"Hello"}]
```

Nobody sent an author. The column defaults to auth.uid(), and the policy's
with check would refuse anything else — ownership is established by the database,
not by trusting the client.

## What Baselyra does not do

Stated plainly, because finding out on day two is worse.

Not built | What you have instead |

Edge functions In progress | Postgres functions called over /rest/v1/rpc/:fn |

Replication, read replicas, failover In progress | One Postgres. Backups are pg_dump plus the storage volume. |

An S3 or object-storage backend In progress | Files on the local disk of the host running the app |

Image transformation In progress | Files come back exactly as they were uploaded |

A project switcher In progress | control.projects holds one row that every lookup resolves through, but nothing creates a second |

Apple sign-in | Google, GitHub, LinkedIn, Facebook, Instagram, TikTok and Envato — see Third-party sign-in |

Two-factor authentication on Studio accounts | A password, rate limiting, and an audit log |

Database webhooks and cron-scheduled jobs are built — they run in-process with
their queue in the project database, under /admin/v1/hooks/* — but the Studio has
no page for either yet.

## Read in this order

QuickstartTen minutes from an empty machine to a working authenticated API.
Row level securityThe page to read properly. Five complete policy sets.
REST APIThe auto-generated API over every table and view.
AuthSessions, refresh rotation, email flows, metadata.
Self-hostingTLS, reverse proxies, backups, upgrades.
FAQThe questions people actually ask first.

==============================================================================
# Quickstart
URL: https://baselyra.sarimtools.com/docs/quickstart.html
==============================================================================

# Quickstart

Everything on this page is copy-pasteable and every response is the real one. At the end you will have an instance running, a table with policies on it, an application user, and a query that returns different rows depending on who asks.

BEFORE YOU START:
You need Docker and Docker Compose. Nothing else — Node, Postgres and the toolchain live inside the images. If you would rather run it directly, see Installation.

- Clone and generate secrets

```bash
git clone https://github.com/baselyra/baselyra.git
cd baselyra
./scripts/setup.sh
```

setup.sh refuses to run when .env already exists, so it can never overwrite
your secrets. It copies .env.example to .env, replaces JWT_SECRET,
POSTGRES_PASSWORD and BASELYRA_ADMIN_PASSWORD with fresh random values,
chmod 600s the file, and prints the admin credentials once.

```bash
Wrote .env

  Admin email     : admin@example.com
  Admin password  : k3Qm9xTdL2wvB8pR

Save that password now — it is only stored hashed after the first boot.
```

COPY IT NOW:
After the first boot only the hash exists. If you lose it, clear BASELYRA_ADMIN_PASSWORD, set a new one, and restart — or reset it directly in control.platform_users.

- Set the two URLs

Open .env and set at least these. On a laptop, http://127.0.0.1:3130 for both
is fine.

```bash
BASELYRA_PUBLIC_URL=https://api.example.com   # where this instance is reachable
BASELYRA_SITE_URL=https://app.example.com     # where your frontend lives
```

BASELYRA_PUBLIC_URL goes into signed storage URLs and email links.
BASELYRA_SITE_URL is where GET /auth/v1/verify sends a user after they click a
confirmation link — and it is always that value, never anything from the request,
which is what keeps the endpoint from being an open redirect.

- Start it

```bash
docker compose up -d --build
docker compose logs -f app
```

The app container waits for Postgres, creates both databases if they are absent,
applies db/project/*.sql and db/control/*.sql in filename order to their own
database, creates the first Studio account, and starts listening. You are looking
for [migrate] done and Baselyra listening on 0.0.0.0:3000.

```bash
curl -s http://127.0.0.1:3130/health
```

```json
{"status":"ok","service":"baselyra","version":"0.1.0","database":"up","time":"2026-08-24T09:14:02.481Z"}
```

Then run the end-to-end check, which exercises auth, REST, storage, realtime, the
admin API and the Studio the way a real client would, and deletes everything it
created on the way out.

```bash
./scripts/smoke.sh http://127.0.0.1:3130 admin@example.com 'k3Qm9xTdL2wvB8pR'
```

- Sign in to the Studio and take your keys

Open http://127.0.0.1:3130 and sign in with the
printed credentials. The Overview page carries a Connect panel with your
project URL and anon key. From the command line:

```bash
ADMIN_TOKEN=$(curl -s http://127.0.0.1:3130/admin/v1/login \
  -H 'content-type: application/json' \
  -d '{"email":"admin@example.com","password":"k3Qm9xTdL2wvB8pR"}' \
  | sed -n 's/.*"access_token":"\([^"]*\)".*/\1/p')

curl -s http://127.0.0.1:3130/admin/v1/keys -H "authorization: Bearer $ADMIN_TOKEN"
```

```json
{"anonKey":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYW5vbiIsImlzcyI6ImJhc2VseXJhIiwiaWF0IjoxNzcxOTk1MjQyLCJleHAiOjIwODc1NzEyNDJ9.tS0…",
 "serviceKey":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic2VydmljZV9yb2xlIiwiaXNzIjoiYmFzZWx5cmEiLCJpYXQiOjE3NzE5OTUyNDIsImV4cCI6MjA4NzU3MTI0Mn0.Qa4…"}
```

Both are signed with JWT_SECRET and last ten years. The anon key ships in your
frontend; the service key never leaves a server. Rotating JWT_SECRET
invalidates both and every session at once.

- Create a table — and turn RLS on in the same breath

Studio → SQL (or Cmd + K, type "sql"). The editor
defaults to read-only; flip the segmented control beside Run to
Write, which tints itself with the warning colour because that is the mode
that can drop things. Then Cmd + Enter.

```sql
create table public.posts (
  id         bigint generated always as identity primary key,
  author     uuid not null default auth.uid() references auth.users(id) on delete cascade,
  title      text not null check (length(title) between 1 and 200),
  body       text not null default '',
  published  boolean not null default false,
  created_at timestamptz not null default now()
);

create index posts_author_idx on public.posts (author);

alter table public.posts enable row level security;

create policy posts_read_published on public.posts
  for select to anon, authenticated
  using (published or author = (select auth.uid()));

create policy posts_insert_own on public.posts
  for insert to authenticated
  with check (author = (select auth.uid()));

create policy posts_update_own on public.posts
  for update to authenticated
  using (author = (select auth.uid()))
  with check (author = (select auth.uid()));

create policy posts_delete_own on public.posts
  for delete to authenticated
  using (author = (select auth.uid()));
```

DANGER:
Between create table and alter table … enable row level security, that table is world-writable to anyone holding your public anon key. Run them together, in one editor submission, every time.

- Create an application user

```bash
curl -s http://127.0.0.1:3130/auth/v1/signup \
  -H 'content-type: application/json' \
  -d '{"email":"ada@example.com","password":"correct-horse-battery","data":{"name":"Ada"}}'
```

```json
{"user":{"id":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","aud":"authenticated","role":"authenticated",
  "email":"ada@example.com","phone":null,"email_confirmed_at":null,"phone_confirmed_at":null,
  "confirmed_at":null,"last_sign_in_at":null,"banned_until":null,
  "app_metadata":{"provider":"email","providers":["email"]},
  "user_metadata":{"name":"Ada"},"created_at":"2026-08-24T09:20:11.004Z",
  "updated_at":"2026-08-24T09:20:11.004Z"},
 "session":null}
```

session is null because AUTH_CONFIRM_EMAIL defaults to true. With no
SMTP configured the confirmation email is printed to the log instead of sent:

```bash
docker compose logs app | grep '\[mail\]'
```

```bash
[mail] SMTP not configured, would have sent to
[mail]   subject: Confirm your email
[mail]   link:    http://127.0.0.1:3130/auth/v1/verify?token=8Yb…&type=confirmation
```

Open that link, or set AUTH_CONFIRM_EMAIL=false while developing. Configure SMTP
before you go live — see Email.

- Sign in and use the API as that user

```bash
curl -s 'http://127.0.0.1:3130/auth/v1/token?grant_type=password' \
  -H 'content-type: application/json' \
  -d '{"email":"ada@example.com","password":"correct-horse-battery"}'
```

```json
{"access_token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9…",
 "token_type":"bearer",
 "expires_in":3600,
 "expires_at":1771998842,
 "refresh_token":"o3Hn7Vb2XkQ0…",
 "user":{"id":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","email":"ada@example.com", "…":"…"}}
```

Write a post as Ada. Note that author is never sent.

```bash
curl -s http://127.0.0.1:3130/rest/v1/posts \
  -H "apikey: $ANON_KEY" \
  -H "authorization: Bearer $ADA_TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"title":"Hello","body":"First post","published":true}'
```

```json
[{"id":1,"author":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","title":"Hello",
  "body":"First post","published":true,"created_at":"2026-08-24T09:24:55.117Z"}]
```

Now read it anonymously. The same URL, no bearer token, and the policy decides.

```bash
curl -s "http://127.0.0.1:3130/rest/v1/posts?select=id,title&order=created_at.desc" \
  -H "apikey: $ANON_KEY"
```

```json
[{"id":1,"title":"Hello"}]
```

Add an unpublished draft as Ada and the anonymous request still returns only the
published row — with no filter, no where clause, and no code of yours involved.
That is row level security working.

- Talk to it from an application

```bash
npm install @baselyra/client
```

```ts
import { createClient } from '@baselyra/client';

const bl = createClient(
  import.meta.env.VITE_BASELYRA_URL,
  import.meta.env.VITE_BASELYRA_ANON_KEY,
);

await bl.auth.signInWithPassword({ email, password });

const { data, error } = await bl
  .from('posts')
  .select('id, title, created_at')
  .eq('published', true)
  .order('created_at', { ascending: false })
  .limit(20);

if (error) console.error(error.code, error.message);
```

Nothing throws: every call resolves to { data, error }. Framework setups are in
Next.js, React,
Flutter, PHP and
Python.

- Turn on realtime

Change feeds are opt-in per table, because a NOTIFY trigger on a hot table you
are not watching is pure cost.

```sql
select baselyra.enable_realtime('public.posts');
```

```ts
const channel = bl.channel('public:posts');
channel.on('postgres_changes',
  { event: 'INSERT', schema: 'public', table: 'posts', filter: 'published=eq.true' },
  ({ new: row }) => console.log('new post', row));
await channel.subscribe();
```

Each changed row is re-read as your role before it is sent, so a subscription can
never show more than a select would. Details and the reverse-proxy rules realtime
needs are in Realtime.

- Store a file

```ts
await bl.storage.from('avatars').upload(user.id + '/me.png', file);
const { data } = bl.storage.from('avatars').getPublicUrl(user.id + '/me.png');
```

Two buckets are seeded: avatars (public, images only, 5 MB limit) and
uploads (private, no limits). Creating buckets needs the service key or the
Studio. See Storage.

## What to do before you ship

- Every table in public has RLS on and at least one policy —
the audit query.

- SMTP is configured, so recovery emails leave the machine instead of landing in
your log file.

- CORS_ORIGINS names your origins rather than *.

- BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD are cleared from
.env after your first sign-in.

- The service key appears in no client bundle. Grep your frontend for it.

The full list is in the hardening
checklist.

## If something went wrong

Symptom | Cause |

database not ready repeating in the logs | Postgres is still initialising. scripts/migrate.js retries thirty times at two seconds. Past that, check docker compose logs db for a volume permission problem. |

Sign-in returns email_not_confirmed | Working as configured. Open the link from the log, or set AUTH_CONFIRM_EMAIL=false for local development. |

A table with rows returns [] | RLS is on and no policy admits your role. This is the correct default — add a policy. |

Realtime connects but never delivers | The table has no trigger, or a proxy is eating the Upgrade header. See Troubleshooting. |

More in Troubleshooting.

==============================================================================
# Installation
URL: https://baselyra.sarimtools.com/docs/installation.html
==============================================================================

# Installation

Baselyra ships as two containers and a .env file. This page covers what the images contain, what happens on every boot, how to run it without Docker, and how to verify the install before you trust it.

## Requirements

| Needs |

With Docker | Docker and Docker Compose. Nothing else. |

Without Docker | Node 22 or newer and Postgres 17. The database role must be allowed to CREATE DATABASE, CREATE ROLE (two of them with BYPASSRLS) and CREATE EXTENSION. |

Memory | 1 GB is enough to start. 2 GB is comfortable for production. |

Disk | The Postgres volume plus whatever you put in STORAGE_ROOT. |

At idle expect roughly 250–400 MB for Postgres with the shipped
shared_buffers=256MB, and 80–150 MB for the Node process. Under load the Node side
grows with concurrent uploads and open WebSockets; Postgres grows with
work_mem times concurrent sorts.

## With Docker Compose

```bash
git clone https://github.com/baselyra/baselyra.git /opt/baselyra
cd /opt/baselyra
./scripts/setup.sh          # writes .env with fresh secrets, prints the admin password once
$EDITOR .env                # set BASELYRA_PUBLIC_URL, BASELYRA_SITE_URL, SMTP, CORS_ORIGINS
docker compose up -d --build
docker compose logs -f app
```

The shipped docker-compose.yml is two services and two named volumes.

```bash
services:
  db:
    image: postgres:17-alpine
    environment:
      POSTGRES_INITDB_ARGS: "--locale-provider=icu --icu-locale=und-x-icu --encoding=UTF8"
    command: [postgres, -c, max_connections=200, -c, shared_buffers=256MB, -c, work_mem=8MB]
    volumes: [db-data:/var/lib/postgresql/data]
    # No published port: only the app container reaches Postgres.

  app:
    image: baselyra:latest
    env_file: .env
    environment:
      DATABASE_URL: postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@db:5432/${POSTGRES_DB}
      PORT: 3000
    ports: ["127.0.0.1:${BASELYRA_PORT:-3130}:3000"]
    volumes: [storage-data:/var/lib/baselyra/storage]
```

TWO THINGS WORTH NOTICING:
The app is published on 127.0.0.1 only, and Postgres publishes no port at all.
That is deliberate: trustProxy is on, so a directly reachable port would let a client
spoof X-Forwarded-For and defeat every per-IP rate limit. Terminate TLS in a reverse
proxy — see Self-hosting.

The collation is pinned with --locale-provider=icu so ORDER BY does not
change under a locale upgrade of the base image.

### What is in the image

Three build stages, so neither toolchain reaches the runtime layer. What ships is
production node_modules, the compiled server in dist/, the compiled Studio in
studio/, the SQL in db/ and the scripts.

Detail | Value |

Base | node:22-alpine, plus tini and postgresql17-client |

User | Runs as uid 10001, not root |

Entrypoint | tini, so signals reach Node and a SIGTERM drains cleanly |

Command | node scripts/migrate.js && node dist/index.js |

Healthcheck | GET /health every 30s after a 20s start period |

Runtime dependencies | Eight npm packages: fastify, five Fastify plugins, pg and nodemailer |

## What happens on every boot

scripts/migrate.js runs before the server, on every start, and every step in it
is a no-op the second time.

- Wait for Postgres
Thirty attempts at two-second intervals, logging database not ready with the driver's error code each time.

- Create the databases if they are absent
CREATE DATABASE cannot run inside a transaction and cannot run from a connection to the database being created, so it is issued from a connection to postgres, the maintenance database every server ships with.

- Create and pin the SQL console role
baselyra_sql is created NOLOGIN and then forced nosuperuser nocreatedb nocreaterole noreplication nologin inherit bypassrls on every boot, so a hand-run alter role baselyra_sql superuser survives exactly until the next restart. See The SQL editor.

- Apply the migrations
db/control/*.sql to the control database and db/project/*.sql to every registered project database, in filename order, each file inside one transaction. Each database tracks what it has applied in its own migrations table with a checksum. A file whose checksum changed is re-run — every file in db/ is written to be idempotent, and that is the intended way to evolve the schema.

- Move an older instance's operating data
An instance created before the control/project split keeps platform_users, audit_log, import_runs and request_stats in the project database. They are copied across in pages of 5000 rows preserving ids, hashes, roles and timestamps, and the originals are dropped only once a row-count comparison confirms the copy. If the counts disagree it aborts loudly and drops nothing.

- Create the first Studio account
From BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD. On a re-run the account is promoted to owner but its password is never overwritten, so changing it in the Studio survives a restart.

A successful run ends with [migrate] done, and then
Baselyra listening on 0.0.0.0:3000.

CAREFUL:
Clear BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD from .env once you have signed in. Left in place they are a second known credential — and if you later change your address in the Studio, a restart creates a second owner account from the stale value.

## Verify the install

```bash
curl -s http://127.0.0.1:3130/health
```

```json
{"status":"ok","service":"baselyra","version":"0.1.0","database":"up","time":"2026-08-24T09:14:02.481Z"}
```

database is up only when both pools answer SELECT 1. Without the
control database nobody can open the Studio, so a half-up instance reports itself
down.

Then the end-to-end check, which drives auth, REST, storage, realtime, the admin
API and the Studio the way a real client would, and removes everything it created:

```bash
./scripts/smoke.sh http://127.0.0.1:3130 admin@example.com 'the-printed-password'
```

## Reaching Postgres directly

```bash
docker compose exec db psql -U baselyra -d baselyra          # your project
docker compose exec db psql -U baselyra -d baselyra_control  # Studio accounts, audit log
```

Two databases on one server. DATABASE_URL names the first;
CONTROL_DATABASE_URL is optional and defaults to the same server with the database
name swapped to baselyra_control. Pointing both at one database is
refused at boot — that would put Studio password hashes back inside the
database the SQL editor can read.

## Without Docker

Build both halves, then assemble the runtime layout. The server serves the Studio
from ../studio relative to dist/, so the built frontend has to sit next to
dist/ — which is exactly what the Dockerfile does.

```bash
npm ci && npm run build                 # -> dist/
(cd studio && npm ci && npm run build)  # -> studio/dist/

install -d /opt/baselyra
cp -r dist package.json package-lock.json db scripts /opt/baselyra/
cp -r studio/dist /opt/baselyra/studio
(cd /opt/baselyra && npm ci --omit=dev)
```

DANGER:
Do not point the server at the repository checkout. <repo>/studio is the Vite source there, and its index.html loads /src/main.tsx, which does not exist in a production build. You get a blank console and no error that says why.

```bash
cd /opt/baselyra
DATABASE_URL=postgres://… JWT_SECRET=… node scripts/migrate.js
DATABASE_URL=postgres://… JWT_SECRET=… node dist/index.js
```

Run it under systemd with Restart=always, an EnvironmentFile, and a
dedicated user that owns STORAGE_ROOT.

## Upgrading

```bash
cd /opt/baselyra
./scripts/backup.sh
git pull
docker compose up -d --build
docker compose logs -f app
./scripts/smoke.sh http://127.0.0.1:3130 admin@example.com 'password'
```

Migrations run automatically at boot, transactionally, with checksums. Rolling
back means restoring the backup: there are no down-migrations. Backups, restores
and the reverse proxy are covered in Self-hosting.

## Install problems

Symptom | Cause and fix |

The app container restarts in a loop | Usually a missing required variable — DATABASE_URL or JWT_SECRET — or a failed migration, which prints the file and the character position. docker compose logs app. |

CONTROL_DATABASE_URL must name a different database than DATABASE_URL | Both URLs point at the same database. Unset CONTROL_DATABASE_URL and let it default. |

the SQL console role baselyra_sql does not exist | A migration directory was applied without scripts/migrate.js — a role belongs to the cluster rather than to one database, so migrate.js creates it once before applying the files. Run node scripts/migrate.js. |

baselyra_sql is a superuser: the SQL console would be a shell on this host | Someone granted it. alter role baselyra_sql nosuperuser and restart. The migration aborts on purpose rather than starting an instance whose admin console is remote code execution. |

database not ready thirty times, then exit | Postgres never came up. docker compose logs db, usually a volume permission problem. |

Studio loads as a blank page | The server is pointed at the Vite source rather than a built Studio. See above. |

==============================================================================
# Configuration
URL: https://baselyra.sarimtools.com/docs/configuration.html
==============================================================================

# Configuration

All runtime configuration comes from the environment; nothing is read from disk at runtime, so the same image runs anywhere with a different .env. Two variables are required — DATABASE_URL and JWT_SECRET — and the process refuses to start without them.

## Required

Variable | Notes |

DATABASE_URL | The project database: your tables, auth, storage, and this project's Baselyra configuration. This is what /rest/v1 exposes. |

JWT_SECRET | HS256 secret. Signs access tokens, the anon key, the service key, Studio tokens and signed storage URLs. At least 32 random bytes; scripts/setup.sh generates one. |

DANGER:
JWT_SECRET is the root of almost everything. Anyone holding it can mint a service key, forge any access token and sign any storage URL. Rotating it invalidates every session, both project keys and every Studio token at once — a deliberate act, not a routine one.

## Instance

Variable | Default | Effect |

NODE_ENV | production | Anything but production turns per-request logging on. |

PORT | 3000 | Port inside the container. Compose maps it with BASELYRA_PORT. |

HOST | 0.0.0.0 | Bind address. |

BASELYRA_PUBLIC_URL | http://localhost:3000 | This instance's public origin. Goes into email links, signed storage URLs and the OAuth callback URL. A trailing slash is stripped. |

BASELYRA_SITE_URL | http://localhost:3000 | Your frontend. Where GET /auth/v1/verify redirects, and the default OAuth redirect target. Always this value, never anything from the request — which is what keeps those routes from being open redirects. |

BASELYRA_VERSION | 0.1.0 | Reported by /health. The Docker build sets it. |

CORS_ORIGINS | * | Comma-separated allowed origins. * means any page on the internet may call the API with a token the browser holds. Name your origins in production. |

LOG_LEVEL | info | Fastify log level. authorization, apikey and cookie headers are redacted at every level. |

## Databases

Variable | Default | Effect |

CONTROL_DATABASE_URL | DATABASE_URL with the database name swapped for baselyra_control | The control database: Studio accounts, audit log, import history, request metering, the project registry. Set it only to put the control database on another server. Pointing it at the same database as DATABASE_URL is refused at boot. |

DATABASE_POOL_MAX | 12 | Connections per pool, per app process. Stay well under Postgres' max_connections. |

DATABASE_STATEMENT_TIMEOUT_MS | 15000 | Kills a runaway API query. Exceeding it is a 408 statement_timeout. Bulk import copies raise it to unlimited for the duration of a table. |

### Replica settings

Four variables exist and are read, and the registry will open a pool per URL and
probe it every DATABASE_REPLICA_HEALTH_MS with
pg_is_in_recovery() and pg_last_xact_replay_timestamp().

CAREFUL:
No request path routes reads to a replica today. Every query the shipped routes
make goes to the primary. These variables are plumbing for work in progress
In progress, not a feature — and Baselyra does not set up, monitor or fail over
replication in any case. Setting them changes nothing you can observe.

Variable | Default |

DATABASE_REPLICA_URLS | empty — comma separated |

DATABASE_REPLICA_MAX_LAG_MS | 5000 |

DATABASE_REPLICA_HEALTH_MS | 10000 |

DATABASE_READ_AFTER_WRITE_MS | 10000 |

### Project settings

Every lookup of "which database is this project" reads control.projects rather
than a constant, so a switcher later is a change to one resolver. Today that table
holds a single row and nothing creates a second In progress.

Variable | Default | Effect |

PROJECT_DEFAULT_SLUG | default | The project a request that names none is answered from. |

PROJECT_DATABASE_PREFIX | baselyra_ | Prefix for a created project's database name. |

PROJECT_MAX_POOLS | 8 | Idle project pools held open at once; past this the least recently used is closed. |

## Tokens and sessions

Variable | Default | Effect |

JWT_ACCESS_TTL | 3600 | Access token lifetime in seconds. An access token cannot be revoked before it expires, so keep this short — that is what the refresh token is for. |

JWT_REFRESH_TTL | 2592000 | Refresh token lifetime in seconds (30 days). |

JWT_ISSUER | baselyra | The iss claim. |

## Auth behaviour

Variable | Default | Effect |

AUTH_CONFIRM_EMAIL | true | Require a confirmed address before the first sign-in. With it on, POST /auth/v1/signup answers with session: null. |

AUTH_ALLOW_SIGNUPS | true | When false, /auth/v1/signup answers 403, third-party sign-in refuses to create a new account, and a phone code is only sent to a number that already has one. |

AUTH_MIN_PASSWORD_LENGTH | 8 | The floor. There is no composition rule — length is the property that matters. The ceiling is 256 bytes, because scrypt is linear in input length. |

AUTH_MAX_ATTEMPTS | 8 | Failed sign-ins per email+IP inside the window before a 429 with Retry-After. |

AUTH_ATTEMPT_WINDOW | 900 | That window, in seconds. Nothing is locked permanently, so nobody can lock a competitor out of their own account. |

AUTH_OTP_TTL | 600 | One-time code lifetime in seconds, for both email and phone codes. |

## Third-party sign-in

A provider is offered only when both halves are present. Half-configured is
indistinguishable from not configured, and a sign-in button that cannot work is
worse than no button.

```bash
BASELYRA_OAUTH_GOOGLE_CLIENT_ID=…
BASELYRA_OAUTH_GOOGLE_CLIENT_SECRET=…
BASELYRA_OAUTH_GITHUB_CLIENT_ID=…
BASELYRA_OAUTH_GITHUB_CLIENT_SECRET=…
```

The provider id is uppercased into the variable name:
google, github, linkedin, facebook, instagram,
tiktok, envato.

Variable | Default | Effect |

BASELYRA_OAUTH_REDIRECT_ALLOWLIST | empty | Extra targets a sign-in may return the browser to, on top of BASELYRA_SITE_URL. Comma-separated absolute http(s) URLs; a path on one narrows the allowance to that path and below. |

Full flow, callback URL and identity linking rules:
Third-party sign-in.

## Storage

Variable | Default | Effect |

STORAGE_ROOT | /var/lib/baselyra/storage | One directory per bucket. A Docker volume in the shipped compose file. Back it up or lose it. |

STORAGE_MAX_FILE_BYTES | 52428800 | Global upload cap (50 MB), and the Fastify body limit. A bucket's own file_size_limit can be lower. Keep your proxy's body limit above this. |

## Email

Variable | Default | Effect |

SMTP_HOST | empty | Empty means nothing is sent — the message, including confirmation and recovery links, is printed to the app log. Right for a laptop, never right in production. |

SMTP_PORT | 587 | |

SMTP_SECURE | false | true only for implicit TLS on port 465. |

SMTP_USER / SMTP_PASS | empty | |

SMTP_FROM | Baselyra <no-reply@localhost> | |

DETAIL:
A configured relay that rejects a message raises an error. Sending is only skipped when SMTP_HOST is empty — there is no silent fallback to the log once you have configured a relay.

## Phone and SMS

With SMS_PROVIDER unset the code is printed to the log instead of sent, exactly
as email is. A configured provider that refuses a message still throws.

Variable | Default | Effect |

SMS_PROVIDER | empty | One of twilio, vonage, messagebird, sns, plivo, webhook. |

SMS_DEFAULT_COUNTRY | empty | Country calling code for a number typed without one, e.g. 1 or 44. Unset means a number must arrive in international form to be accepted at all. |

SMS_OTP_COOLDOWN | 60 | Seconds before the same number may be sent another code. |

SMS_MAX_PER_NUMBER_PER_HOUR | 5 | Hard cap per number per hour. Every message is a charge on your account and the endpoint that causes one is unauthenticated, so this ceiling is what stops a stranger spending your money. |

SMS_FROM | empty | Sender id or number, where the provider needs one. |

Per-provider credentials — TWILIO_ACCOUNT_SID, VONAGE_API_KEY,
PLIVO_AUTH_ID and the rest — are listed on
Phone one-time codes.

## Realtime

Variable | Default | Effect |

REALTIME_MAX_CHANNELS | 100 | Channels one socket may hold. |

REALTIME_HEARTBEAT_MS | 30000 | Server ping interval. A socket that misses two consecutive pongs is terminated. |

## AI

Entirely optional. Without a key every /ai/v1 route answers 503 ai_disabled,
the Studio hides its AI panels, and nothing else changes.

Variable | Default | Effect |

DEEPSEEK_API_KEY | empty | The key stays on the server and never appears in a response. |

DEEPSEEK_BASE_URL | https://api.deepseek.com | Any OpenAI-compatible /chat/completions endpoint, so a local Ollama or vLLM behind an OpenAI shim works. |

DEEPSEEK_MODEL | deepseek-chat | |

DEEPSEEK_MAX_TOKENS | 2048 | Hard ceiling per completion. A caller may ask for fewer, never more. |

## Bootstrap and compose-only

Variable | Read by | Effect |

BASELYRA_ADMIN_EMAIL | scripts/migrate.js | Creates the first Studio account in control.platform_users — never in your project's auth.users. |

BASELYRA_ADMIN_PASSWORD | scripts/migrate.js | Its password on first boot only. A re-run promotes the account to owner but never overwrites the password. |

BASELYRA_PORT | docker-compose.yml | Host port, bound to 127.0.0.1. Default 3130. |

POSTGRES_USER / POSTGRES_PASSWORD / POSTGRES_DB | docker-compose.yml | Compose composes DATABASE_URL from them. |

CAREFUL:
Clear both admin variables after the first sign-in. Left in place they are a second known credential, and a restart after you change your address in the Studio creates a second owner account from the stale value.

## A production .env

```bash
# instance
BASELYRA_PUBLIC_URL=https://api.example.com
BASELYRA_SITE_URL=https://app.example.com
BASELYRA_PORT=3130
CORS_ORIGINS=https://app.example.com
LOG_LEVEL=info

# secrets — generated by ./scripts/setup.sh
JWT_SECRET=…48 random bytes…
POSTGRES_USER=baselyra
POSTGRES_PASSWORD=…32 random bytes…
POSTGRES_DB=baselyra

# auth
AUTH_CONFIRM_EMAIL=true
AUTH_ALLOW_SIGNUPS=true
AUTH_MIN_PASSWORD_LENGTH=8

# email — without this, recovery links land in your log file
SMTP_HOST=smtp.example.com
SMTP_PORT=587
SMTP_SECURE=false
SMTP_USER=apikey
SMTP_PASS=…
SMTP_FROM="Acme "

# storage
STORAGE_ROOT=/var/lib/baselyra/storage
STORAGE_MAX_FILE_BYTES=52428800

# cleared after the first sign-in
BASELYRA_ADMIN_EMAIL=
BASELYRA_ADMIN_PASSWORD=
```

## Changing a value

```bash
$EDITOR .env
docker compose up -d          # recreates the app container with the new environment
```

Nothing is read from disk at runtime, so every change needs a restart. Changing
POSTGRES_PASSWORD also means changing it in Postgres
(alter role baselyra password '…') and restarting both containers.

==============================================================================
# The database
URL: https://baselyra.sarimtools.com/docs/database.html
==============================================================================

# The database

Your data is in Postgres tables you can psql into, not behind an abstraction. This page is the map: which schema holds what, what happens to a table the moment you create it, and the handful of Postgres details that change how Baselyra behaves.

## Two databases, five schemas

Database | Schema | Holds | Reachable from |

baselyra | public | Your tables, views and functions | /rest/v1, the Studio, the SQL editor |

baselyra | auth | Your application's users, sessions, identities, one-time tokens | Policies and the SQL editor. Never /rest/v1. |

baselyra | storage | Buckets and object metadata | Policies and the SQL editor. Never /rest/v1. |

baselyra | baselyra | This project's configuration: settings, email templates, realtime tables, webhooks, jobs, the migration ledger | The Studio's purpose-built pages. Excluded from /admin/v1/schema entirely. |

baselyra_control | control | Studio accounts, audit log, import history, request metering, the project registry | Baselyra's own code, through a separate pool. Nothing else. |

DETAIL:
Only public is exposed through /rest/v1. That is by construction rather than by a blocklist: the route resolves snap.table('public', name) and nothing else, so GET /rest/v1/users looks for public.users and answers 404 when you meant auth.users.

GET /admin/v1/schema groups what it returns: user schemas
(public and anything you created) first and expanded, system
(auth, storage) collapsed, and baselyra, pg_catalog,
information_schema and pg_toast excluded from the response. auth and
storage stay visible because writing a policy against auth.users or
storage.objects is a legitimate thing to do.

## Creating a table

A table is an API endpoint the moment it exists. There is no generate step, no
deploy, and no registration — the catalog is read on demand.

DANGER:
db/project/001_schema.sql declares
alter default privileges in schema public grant select, insert, update, delete on
tables to anon, authenticated. That is what makes a new table work over
/rest/v1 with no grant step. It also means a new table with RLS off is
readable, writable and deletable by anyone holding your public anon key. Enable
row level security in the same submission as the create table, every time.

```sql
create table public.invoices (
  id          uuid primary key default gen_random_uuid(),
  owner       uuid not null default auth.uid() references auth.users(id) on delete cascade,
  number      text not null unique,
  amount      numeric(12,2) not null check (amount >= 0),
  currency    text not null default 'GBP' check (length(currency) = 3),
  paid_at     timestamptz,
  metadata    jsonb not null default '{}'::jsonb,
  created_at  timestamptz not null default now()
);

-- A policy is a WHERE clause on every row of every query. Index what it filters on.
create index invoices_owner_idx on public.invoices (owner);

alter table public.invoices enable row level security;

create policy invoices_own on public.invoices
  for all to authenticated
  using (owner = (select auth.uid()))
  with check (owner = (select auth.uid()));
```

```bash
curl -s "$URL/rest/v1/invoices?select=number,amount&order=created_at.desc&limit=2" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"
```

```json
[{"number":"INV-0042","amount":"1250.00"},
 {"number":"INV-0041","amount":"90.00"}]
```

### The catalog snapshot

The REST layer reads columns, primary keys, foreign keys and functions out of
pg_catalog and caches the result for 60 seconds. DDL performed through
/admin/v1 or the Studio invalidates it immediately; DDL run straight from
psql is visible to the API within a minute.

CAREFUL:
"I created the table and REST says 404" is almost always this. Wait a minute, or run the statement through the Studio's SQL editor, which invalidates the cache for you.

## Types on the wire

Baselyra installs identity parsers for two Postgres oids so JSON stays exact
rather than lossy:

Postgres type | JSON | Why |

bigint / int8 | string | JavaScript numbers lose precision above 253 |

numeric | string | A float cannot represent 1250.00 exactly, and money must not round |

timestamptz | ISO 8601 string | |

jsonb / json | the value itself | An object or array in a write body is serialised as JSON rather than as a Postgres array literal |

uuid | string | |

text[] and other arrays | JSON array | |

The generated TypeScript types from the
VS Code extension mirror what the JSON
carries, not what Postgres stores, for exactly this reason.

## Views

Views are exposed over /rest/v1 the same as tables. There is one thing you
must do to every one of them.

DANGER:
In Postgres a view runs with its owner's privileges by default, and the table
owner is exempt from RLS because Baselyra enables it without FORCE. A view over
an RLS-protected table therefore returns every row to whoever can select from
the view — and /rest/v1 exposes views.

```sql
create view public.recent_invoices with (security_invoker = true) as
  select id, number, amount, created_at
    from public.invoices
   order by created_at desc;

-- retrofit an existing one
alter view public.recent_invoices set (security_invoker = true);
```

Audit them:

```sql
select c.relname as view,
       coalesce((select option_value from pg_options_to_table(c.reloptions)
                  where option_name = 'security_invoker'), 'false') as security_invoker
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
 where n.nspname = 'public' and c.relkind = 'v'
 order by 2, 1;
```

## Functions

Every function in public with prokind = 'f' is callable at
POST /rest/v1/rpc/:name. Arguments are passed by name, resolved against
pg_proc, and always bound as parameters.

```sql
create or replace function public.invoice_totals(since timestamptz default now() - interval '30 days')
returns table (currency text, total numeric)
language sql stable
as $$
  select i.currency, sum(i.amount)
    from public.invoices i
   where i.created_at >= since
   group by 1
   order by 2 desc;
$$;
```

```bash
curl -s -X POST "$URL/rest/v1/rpc/invoice_totals" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -d '{"since":"2026-08-01T00:00:00Z"}'
```

```json
[{"currency":"GBP","total":"4310.00"},{"currency":"EUR","total":"220.00"}]
```

An ordinary function is security invoker, so it is still subject to RLS. A
security definer function runs as its owner and bypasses RLS on everything it
touches — which is exactly what you want for a membership helper inside a policy,
and exactly what you do not want for a function you expose over RPC. Pin
search_path on anything you do mark definer.

## The auth schema

These are your application's users. They are not Studio operators — those live in
another database entirely. Write policies against them freely; do not expose them
over /rest/v1, because you cannot.

Table | What it holds |

auth.users | One row per application user: id, email (citext, so equality is case-insensitive), phone, encrypted_password, email_confirmed_at, phone_confirmed_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, banned_until |

auth.sessions | One row per refresh token, with parent_id pointing at the session it replaced, plus user_agent, ip, revoked_at, expires_at |

auth.identities | One row per linked provider account, unique on (provider, provider_id) |

auth.one_time_tokens | SHA-256 digests of confirmation, recovery, email-change, magic-link and OTP tokens. Never the token itself. |

auth.attempts | Failed sign-ins and SMS sends, for the throttles |

auth.oauth_states | One row per third-party sign-in in flight, for ten minutes |

Row level security is on for all of them. auth.users, auth.sessions and
auth.identities carry own-row policies for authenticated;
auth.one_time_tokens, auth.attempts and auth.oauth_states have
no policy at all, and that absence is the denial.

DETAIL:
There is no is_admin column and no auth.is_admin() function — 001_schema.sql
actively drops both if an older instance still has them. An application's own admin
role belongs in app_metadata. A BEFORE UPDATE trigger silently restores
banned_until for anyone who is not service_role, so a user cannot unban
themselves even if a policy lets them update their own row.

## The storage schema

Table | What it holds |

storage.buckets | id, name, public, file_size_limit, allowed_mime_types, owner |

storage.objects | bucket_id, name, owner, size, mime_type, checksum, metadata, unique on (bucket_id, name) |

Because the metadata is a table with RLS on it, who may read or write a file is
an ordinary policy. See Storage and
the per-user folder example.

## The baselyra schema

This project's own configuration. The Studio has purpose-built pages for most of
it, and /admin/v1/schema never returns it.

Table | What it holds |

baselyra.settings | Key/value instance settings |

baselyra.email_templates | The six templates, as finished HTML |

baselyra.realtime_tables | Which tables have the NOTIFY trigger |

baselyra.webhooks / baselyra.webhook_deliveries | Database webhooks and their delivery queue |

baselyra.scheduled_jobs / baselyra.job_runs | Cron-scheduled jobs and their history |

baselyra.migrations | This database's migration ledger, with checksums |

Two functions matter to you:

```sql
select baselyra.enable_realtime('public.invoices');
select baselyra.disable_realtime('public.invoices');
```

Both are security definer so the Studio can toggle a table it does not own,
and EXECUTE is restricted to service_role — a project user cannot attach a
trigger to an arbitrary table.

## DDL from the admin API

The Studio's table and policy forms are these four routes. They run as
baselyra_sql, exactly as the SQL editor does, so a create policy typed into
the editor and one built by the form reach the database with identical powers.

Route | Body |

POST /admin/v1/tables | {schema, name, columns} |

DELETE /admin/v1/tables/:schema/:table?cascade=true | Also deletes the baselyra.realtime_tables row |

POST /admin/v1/policies | {schema, table, name, command, roles, using, check} |

DELETE /admin/v1/policies/:schema/:table/:name | |

NOTE:
There is no ALTER POLICY. Editing a policy means dropping and recreating it, or running an alter policy statement in the SQL editor. Every one of these routes writes an audit row to control.audit_log and invalidates the REST catalog.

## Running SQL

```bash
curl -s -X POST "$URL/admin/v1/sql" \
  -H "authorization: Bearer $ADMIN_TOKEN" -H 'content-type: application/json' \
  -d '{"query":"select count(*) from public.invoices where paid_at is null","read_only":true}'
```

```json
{"columns":[{"name":"count","type":"int8"}],
 "rows":[{"count":"3"}],
 "rowCount":1,
 "durationMs":4,
 "command":"SELECT"}
```

Three things about this endpoint are worth knowing before you use it:

- It runs as baselyra_sql, a NOLOGIN role with BYPASSRLS and every
object right the owner has — and no access to the host. See
The SQL editor.

- The audit row is written before the statement runs, on a separate
connection, so a statement that fails or rolls back is still recorded. It contains
the statement text and its bound parameters: do not type a password into the
editor and expect it to be forgotten.

- Passing params forces the extended protocol, which carries exactly one
statement. Without them the simple protocol accepts a whole script.

## Evolving the schema

Two honest options. Baselyra does not ship a migration tool for your tables.

Approach | How |

Add files to db/project/ | They are applied in filename order on every boot, each inside one transaction, tracked by checksum in baselyra.migrations. Write them idempotently — an edited file is re-run deliberately, which is the intended way to evolve the schema. |

Run the statement in the SQL editor | Fine for development. It is audited, and it invalidates the catalog immediately. It leaves no record your next deployment can replay, so promote anything permanent into a file. |

CAREFUL:
A file whose checksum changes is re-run, not skipped. That is why every shipped migration is guarded with IF NOT EXISTS, CREATE OR REPLACE or a DO block that reads the catalogs first. An unguarded create table in an edited file fails the migration and the container restarts into the same failure.

## Things that go wrong

What you see | Why | Fix |

404 undefined_table for a table you can see in psql | It is not in public, or the catalog snapshot is up to a minute stale | Move it to public, or wait, or run the DDL through the Studio |

[] from a table with rows | RLS is on and no policy admits your role | Add a policy — Row level security |

403 insufficient_privilege | The role lacks table grants. This is a grant problem, not a policy problem: an RLS denial is an empty result, never a 403. | grant select, insert, update, delete on public.t to anon, authenticated |

A view returns rows the underlying policy hides | The view is not security_invoker | alter view … set (security_invoker = true) |

408 statement_timeout | Over DATABASE_STATEMENT_TIMEOUT_MS (15s) | Index the columns your filters and policies use, or raise the variable |

numeric arrives as a string in JavaScript | Deliberate — a float would round money | Parse it where you need arithmetic |

==============================================================================
# REST API
URL: https://baselyra.sarimtools.com/docs/rest-api.html
==============================================================================

# REST API

Every table, view and function in the public schema is an HTTP endpoint at /rest/v1. Nothing is generated or deployed — the API is read from the catalog on demand, so a table created a second ago is queryable now. The dialect is a PostgREST-compatible subset, so client patterns transfer directly.

## The endpoints

Route | Does |

GET /rest/v1/:table | Read rows, with filters, ordering, paging and embeds |

POST /rest/v1/:table | Insert one row or many, optionally as an upsert |

PATCH /rest/v1/:table | Update the rows the filters match — a filter is required |

DELETE /rest/v1/:table | Delete the rows the filters match — a filter is required |

POST /rest/v1/rpc/:function | Call a Postgres function with named arguments |

## Authenticating a request

```bash
apikey:                           # which Postgres role the request runs as
authorization: Bearer    # who the user is
```

Either header alone works, and the bearer token wins when both are present. With
no credentials at all the request runs as anon. What happens next is decided by
row level security, not by this API.

NOTE:
An RLS denial is not an error. It is an empty array on read and zero rows affected on write. If you expected rows and got none, check your policies before you check your filters.

## Reading

```bash
curl -s "$URL/rest/v1/articles?select=id,title,published_at&published_at=not.is.null&order=published_at.desc&limit=3" \
  -H "apikey: $ANON_KEY" -i
```

```bash
HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
content-range: 0-2/*

[{"id":9,"title":"Indexes for policies","published_at":"2026-08-20T08:00:00.000Z"},
 {"id":7,"title":"Reading a query plan","published_at":"2026-08-14T08:00:00.000Z"},
 {"id":4,"title":"Two databases","published_at":"2026-08-02T08:00:00.000Z"}]
```

The whole filter grammar — every operator, the value rules, logic trees, ordering
and paging — has its own page: Filtering and paging.
What follows here is the shape of the API around it.

### Choosing columns

Form | Meaning |

select=* | Every column. The default. |

select=id,title | Those columns |

select=name:title | Alias — the JSON key is name |

select=id::text | Cast to a single-word type name |

select=*,author:users(id,name) | One level of embedded resource |

select=*,users!articles_author_fkey(name) | Disambiguate when two foreign keys point at the same table |

CAREFUL:
A cast must be a single-word type name matching [A-Za-z_][A-Za-z0-9_]*. select=amount::numeric works; select=x::double precision is a 400 with a message saying so, rather than a Postgres syntax error.

### Embedded resources

Embedding follows a foreign key in either direction. A many-to-one embed produces
an object; a one-to-many embed produces an array. Each embed compiles to one
LEFT JOIN LATERAL producing a single JSON column, so nested objects arrive in
the same round trip and no join fan-out has to be undone in JavaScript.

```bash
curl -s "$URL/rest/v1/articles?select=id,title,author:users(id,name),comments(id,body)&id=eq.9" \
  -H "apikey: $ANON_KEY"
```

```json
[{"id":9,
  "title":"Indexes for policies",
  "author":{"id":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","name":"Ada"},
  "comments":[{"id":31,"body":"Useful, thanks"},{"id":33,"body":"Also index the FK"}]}]
```

Only one level is supported. a(b(c)) is a deliberate 400, because the
second level is where the query plan stops being predictable.

```json
{"error":{"code":"bad_request",
  "message":"only one level of embedded resources is supported (\"comments(author(name))\")",
  "details":null}}
```

Two foreign keys to the same table are ambiguous, and the error names your way
out:

```json
{"error":{"code":"bad_request",
  "message":"the relationship between articles and users is ambiguous; disambiguate with users! — candidates: articles_author_fkey, articles_editor_fkey",
  "details":null}}
```

An embedded resource is a table like any other, so its own policies apply.
An embed of a table your role cannot read yields null or [], not an error.

### One row instead of an array

```bash
curl -s "$URL/rest/v1/articles?id=eq.9" \
  -H "apikey: $ANON_KEY" \
  -H 'accept: application/vnd.pgrst.object+json'
```

Returns the object itself. A result that did not contain exactly one row is a
406:

```json
{"error":{"code":"not_acceptable",
  "message":"JSON object requested, but 0 rows were returned","details":null}}
```

In the client that is .single(), or .maybeSingle() to allow zero.

## Writing

### Insert

```bash
curl -s -X POST "$URL/rest/v1/articles" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"title":"Hello","slug":"hello"}' -i
```

```bash
HTTP/1.1 201 Created
location: /rest/v1/articles?id=eq.12
content-type: application/json; charset=utf-8

[{"id":12,"author":"6c1f5c62-…","slug":"hello","title":"Hello","body":"","published_at":null,
  "created_at":"2026-08-24T10:02:41.882Z"}]
```

The body is an object or an array of objects. Without
Prefer: return=representation the response is 201 with an empty body and just
the Location header — which is what you want for a bulk insert, so the rows do
not cross the wire twice.

A bulk insert takes the union of the keys across all rows; a row missing one of
them gets that column's DEFAULT.

```bash
-d '[{"title":"One","slug":"one"},{"title":"Two","slug":"two","body":"…"}]'
```

### Upsert

Header or parameter | Meaning |

Prefer: resolution=merge-duplicates | ON CONFLICT … DO UPDATE |

Prefer: resolution=ignore-duplicates | ON CONFLICT … DO NOTHING |

x-upsert: true | The same as merge-duplicates |

?on_conflict=slug | The conflict target. Defaults to the primary key. |

```bash
curl -s -X POST "$URL/rest/v1/articles?on_conflict=slug" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: resolution=merge-duplicates,return=representation' \
  -d '{"slug":"hello","title":"Hello again"}'
```

A table with no primary key and no on_conflict is a 400 that says exactly
that: upsert needs a conflict target: give the table a primary key or pass the
on_conflict parameter.

### Update

```bash
curl -s -X PATCH "$URL/rest/v1/articles?id=eq.12" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"title":"Edited"}'
```

Without Prefer: return=representation the response is 204 with no body.

### Delete

```bash
curl -s -X DELETE "$URL/rest/v1/articles?id=eq.12" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"
```

### A filter is required on writes

```bash
curl -s -X DELETE "$URL/rest/v1/articles" -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"
```

```json
{"error":{"code":"bad_request",
  "message":"a filter is required for update/delete",
  "details":{"hint":"add a filter such as ?id=eq.1, or send \"Prefer: unsafe-mutation\" to delete every row"}}}
```

DANGER:
An unfiltered PATCH or DELETE rewrites or empties the whole table, and it is the single most common self-inflicted disaster with an API of this shape: one forgotten query parameter in client code and the table is gone. RLS does not save you — the policy allows those rows, the client simply asked for all of them. If you really mean every row, send Prefer: unsafe-mutation.

## Calling functions

```bash
curl -s -X POST "$URL/rest/v1/rpc/search_articles" \
  -H "apikey: $ANON_KEY" -H 'content-type: application/json' \
  -d '{"term":"postgres","max_results":5}'
```

```sql
create or replace function public.search_articles(term text, max_results int default 20)
returns setof public.articles
language sql stable
as $$
  select * from public.articles
   where to_tsvector('english', title || ' ' || body) @@ websearch_to_tsquery('english', term)
   limit max_results;
$$;
```

The function returns | The response is |

A set of composite rows | A JSON array of objects |

A set of scalars | A JSON array of values |

A single composite | A JSON object |

A single scalar | A bare JSON value — 42, "ok", null |

void | 204 with no body |

Overloads are resolved by the argument names you supply. When none matches, the
error lists the signatures that exist:

```json
{"error":{"code":"bad_request",
  "message":"no overload of public.search_articles accepts (q) — signatures: search_articles(term, max_results)",
  "details":null}}
```

CAREFUL:
Filters and paging do not apply to an RPC result — narrow inside the function instead. The JS client refuses the chain rather than silently ignoring it: rpc() results cannot be narrowed with filters.

## Request and response headers

Header | Direction | Meaning |

apikey | request | The project key. Names the Postgres role. |

authorization: Bearer … | request | The user's access token. Wins over apikey. |

accept: application/vnd.pgrst.object+json | request | Return one object, or 406 |

prefer: return=representation | request | Send the affected rows back |

prefer: count=exact | request | Fill in the total in content-range |

prefer: resolution=merge-duplicates | request | Upsert |

prefer: unsafe-mutation | request | Allow an unfiltered write |

range: items=0-19 | request | Paging, when limit/offset are absent |

x-upsert: true | request | The same as resolution=merge-duplicates |

content-range | response | 0-19/*, or 0-19/347 when a count was asked for |

location | response | On 201, the filter URL of the new row |

CORS exposes content-range and x-total-count, and allows
authorization, apikey, content-type, prefer, range,
x-client-info and x-upsert. A custom header your app invents will be
blocked by the browser until you add it to that list in src/index.ts.

## Errors

Every error has the same shape, from every prefix of the API:

```json
{"error":{"code":"unique_violation",
  "message":"duplicate key value violates unique constraint \"articles_slug_key\"",
  "details":{"detail":"Key (slug)=(hello) already exists.","hint":null,"sqlstate":"23505"}}}
```

HTTP | code | Cause |

400 | bad_request | Malformed filter, unknown column, unknown operator, missing filter on a write |

400 | not_null_violation | A required column was absent — SQLSTATE 23502 |

400 | check_violation | A CHECK constraint refused the row — 23514 |

400 | invalid_text_representation | A value could not be cast to the column's type — 22P02 |

400 | string_too_long | 22001 |

400 | undefined_column | 42703 |

400 | undefined_function | 42883 |

400 | raise_exception | A raise exception in a trigger or function — P0001 |

401 | unauthorized | Malformed, expired or wrongly signed token |

403 | forbidden / insufficient_privilege | The role lacks table grants — 42501. Not an RLS denial. |

404 | not_found / undefined_table | No such table or view in public — 42P01 |

406 | not_acceptable | A singular response was requested and the row count was not 1 |

408 | statement_timeout | Over DATABASE_STATEMENT_TIMEOUT_MS — 57014 |

409 | unique_violation / foreign_key_violation | 23505 / 23503 |

413 | payload_too_large | Over the body limit |

429 | rate_limited | 300 requests per minute per IP across the whole API |

500 | internal_error | Anything unmapped |

## How a request is compiled

Worth knowing, because it explains why some things are a 400 rather than a
surprising query plan.

- A value from the request never reaches the SQL text. Exactly three kinds
of token are interpolated: operators looked up in a fixed table, identifiers copied
out of the catalog (not out of the request), and cast type names that matched a
strict regex. Everything else is a bound parameter.

- An unknown column is resolved against the catalog and refused with a 400
before anything reaches the planner.

- limit defaults to 1000 and is clamped to 10000, so an
unbounded select cannot exhaust memory.

- Filter nesting is capped at 20 levels, so a hostile
or=(or=(or=(…))) cannot exhaust the stack.

- Prefer: count=exact runs a second aggregate in the same transaction, so
the total is the number of rows your policies allow, not the table's raw row count.

## In the client

```ts
const { data, error, count } = await bl
  .from('articles')
  .select('id, title, author:users(name)', { count: 'exact' })
  .eq('published', true)
  .order('created_at', { ascending: false })
  .range(0, 19);

await bl.from('articles').insert({ title: 'Hello' }).select().single();
await bl.from('articles').upsert(rows, { onConflict: 'slug' });
await bl.from('articles').update({ title: 'x' }).eq('id', 12);
await bl.from('articles').delete().eq('id', 12);
await bl.rpc('search_articles', { term: 'postgres' });
```

Full reference: the JavaScript client.

## Performance notes

- A filter is a WHERE clause and a policy is another one. Index both.

- Prefer: count=exact costs a second aggregate over the same predicate. Leave
it off for infinite scroll; use it for page numbers.

- An embedded resource is a LATERAL subquery per row of the outer result.
Index the foreign key column.

- The catalog snapshot is cached for 60 seconds and invalidated immediately by
DDL through /admin/v1.

==============================================================================
# Filtering and paging
URL: https://baselyra.sarimtools.com/docs/filtering.html
==============================================================================

# Filtering and paging

A filter is a query parameter of the form column=operator.value. This page is the complete reference for that grammar — every operator, how values are quoted, how or groups nest, how paging works, and what each mistake looks like when it comes back as a 400.

## The shape of a filter

```bash
GET /rest/v1/articles?status=eq.open&score=gte.10&order=created_at.desc&limit=20
```

Every query parameter that is not a reserved name is a filter. Filters on
separate parameters are ANDed. The same parameter may appear more than once,
and those are ANDed too — ?score=gte.10&score=lt.100 is a range.

Six names are reserved and never treated as filters:

```bash
select   order   limit   offset   on_conflict   columns
```

CAREFUL:
A column actually named select or order cannot be filtered through the query string. Rename it, or reach it through an RPC function.

## Operators

Operator | SQL | Example |

eq | = | status=eq.open |

neq | <> | status=neq.archived |

gt gte lt lte | > >= < <= | score=gte.10 |

like | LIKE, with * as the wildcard | title=like.*sql* |

ilike | ILIKE | title=ilike.*SQL* |

match | ~ (POSIX regex) | slug=match.^post- |

imatch | ~* | slug=imatch.^POST- |

in | = ANY(…) | id=in.(1,2,3) |

is | IS | deleted_at=is.null |

isdistinct | IS DISTINCT FROM | a=isdistinct.b |

fts | @@ to_tsquery() | body=fts(english).cat & dog |

plfts | @@ plainto_tsquery() | body=plfts.cats and dogs |

phfts | @@ phraseto_tsquery() | body=phfts(english).black cat |

wfts | @@ websearch_to_tsquery() | body=wfts(english).cats -dogs |

cs | @> contains | tags=cs.{sql,db} |

cd | <@ contained by | tags=cd.{sql,db,ops} |

ov | && overlaps | period=ov.[2026-01-01,2026-02-01) |

sl | << strictly left of | period=sl.[2026-03-01,2026-04-01) |

sr | >> strictly right of | period=sr.[2026-01-01,2026-02-01) |

nxr | &< does not extend to the right of | period=nxr.[2026-01-01,2026-02-01) |

nxl | &> does not extend to the left of | period=nxl.[2026-01-01,2026-02-01) |

adj | -|- is adjacent to | period=adj.[2026-02-01,2026-03-01) |

### Negation

Prefix any operator with not.:

```bash
?status=not.eq.archived
?tags=not.cs.{draft}
?deleted_at=not.is.null
```

It compiles to NOT (…) around the whole condition, which is not the same as
the inverse operator when NULLs are involved. status=neq.archived excludes rows
where status is NULL; status=not.eq.archived also excludes them, because
NOT (NULL = 'archived') is NULL and NULL is not true. To include NULLs, ask for
them: ?or=(status.neq.archived,status.is.null).

### Testing for null

```bash
?deleted_at=is.null        # IS NULL       — correct
?published=is.true         # IS TRUE
?verified=is.false         # IS FALSE
?flag=is.unknown           # IS UNKNOWN
?deleted_at=eq.null        # = NULL        — never true, for anything
```

is accepts exactly those four keywords; anything else is a 400:

```json
{"error":{"code":"bad_request","message":"\"is\" expects null, true, false or unknown, got \"NULL()\"","details":null}}
```

## The value grammar

You write | The server sends to Postgres |

eq.42 | the text 42, coerced by Postgres from the column type |

eq.null | SQL NULL |

eq."null" | the four-character string null |

eq."Smith, J." | the string Smith, J. — quoting is what keeps the comma out of the grammar |

eq."say \\"hi\\"" | the string say "hi" — a backslash escapes a quote or a backslash |

cs.{sql,db} | the Postgres array literal {sql,db}, verbatim |

like.*sql* | %sql% — * is rewritten to % for like and ilike only |

Range, array and jsonb operators — cs, cd, ov, sl, sr,
nxr, nxl, adj — take a Postgres literal on the right and are bound
verbatim, because stripping quotes from {"a":1} or
[2026-01-01,2026-02-01) would corrupt it.

DETAIL:
Every value is a bound parameter. There is no string interpolation of values anywhere in the query compiler, and column names are resolved against the catalog before they are quoted — so an unknown column is a 400 rather than anything reaching the planner.

### URL encoding

The value lives in a query string, so &, #, + and % must be
percent-encoded. A literal + is especially worth remembering: in a query
string it decodes to a space.

```bash
# wrong: the phone number becomes " 14155552671"
curl "$URL/rest/v1/contacts?phone=eq.+14155552671" -H "apikey: $ANON_KEY"

# right
curl --get "$URL/rest/v1/contacts" --data-urlencode 'phone=eq.+14155552671' -H "apikey: $ANON_KEY"
```

## Logic trees

Top-level filters are ANDed. For anything else there is or= and and=, and
they nest.

```bash
?or=(status.eq.draft,and(views.gte.100,pinned.is.true))
?and=(score.gte.10,score.lt.100)
?not.and=(archived.is.true,owner.eq.me)
?not.or=(status.eq.spam,status.eq.deleted)
```

Which compiles to:

```sql
("t"."status" = $1 OR ("t"."views" >= $2 AND "t"."pinned" IS TRUE))
```

Inside a group the value ends at the first top-level , or ), so a value
containing either must be double-quoted:

```bash
?or=(name.eq."Smith, J.",name.eq."O'Neill")
```

A column whose own name contains a dot or a comma is quoted the same way:

```bash
?or=("weird.name".eq.1,other.eq.2)
```

Nesting is capped at 20 levels, so a hostile
or=(or=(or=(…))) cannot exhaust the stack. Beyond that:

```json
{"error":{"code":"bad_request","message":"filter nesting is too deep at position 41 of \"…\"","details":null}}
```

## Full-text search

The four full-text operators take an optional text search configuration in
parentheses. Without one, Postgres uses default_text_search_config.

```sql
alter table public.articles
  add column search tsvector
  generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) stored;

create index articles_search_idx on public.articles using gin (search);
```

```bash
curl --get "$URL/rest/v1/articles" \
  --data-urlencode 'search=wfts(english).postgres -mysql' \
  --data-urlencode 'select=id,title' \
  -H "apikey: $ANON_KEY"
```

```json
[{"id":9,"title":"Indexes for policies"},{"id":4,"title":"Two databases"}]
```

Operator | Parser | Good for |

wfts | websearch_to_tsquery | A search box. Understands quotes, or and -, and never raises on odd input. |

plfts | plainto_tsquery | Plain words, all ANDed |

phfts | phraseto_tsquery | An exact phrase |

fts | to_tsquery | Raw tsquery syntax — cat & !dog. Malformed input raises. |

The configuration name must match [A-Za-z_][A-Za-z0-9_]*; anything else is a
400 invalid text search configuration rather than a Postgres error.

CAREFUL:
Prefer wfts for anything a user types. fts passes the string to to_tsquery, which raises a syntax error on unbalanced input — that reaches your user as a 500-shaped failure for typing an odd character.

## Ordering

```bash
?order=created_at.desc
?order=priority.desc.nullslast,created_at.asc
```

Modifiers: asc, desc, nullsfirst, nullslast. Terms are applied
in the order written. Anything else is a 400:

```json
{"error":{"code":"bad_request","message":"unknown order modifier \"descending\" — use asc, desc, nullsfirst or nullslast","details":null}}
```

TIP:
Sorting on a nullable column without nullsfirst/nullslast uses Postgres' default, which is NULLS LAST for ASC and NULLS FIRST for DESC. Say which you mean when it matters.

## Paging

```bash
?limit=20&offset=40
```

Or the Range header, which is what .range(from, to) in the client sends:

```bash
curl -s "$URL/rest/v1/articles?order=created_at.desc" \
  -H "apikey: $ANON_KEY" \
  -H 'range: items=40-59' -i
```

```bash
HTTP/1.1 200 OK
content-range: 40-59/*
```

Rule | Value |

Default limit | 1000 |

Maximum limit | 10000 — an unbounded select must not be able to exhaust memory |

Precedence | An explicit limit/offset wins over a Range header |

Range is inclusive | items=0-19 is the first twenty rows |

Content-Range is on every read. The total is * unless you ask for it:

```bash
curl -s "$URL/rest/v1/articles?limit=20" -H "apikey: $ANON_KEY" -H 'prefer: count=exact' -i
```

```bash
content-range: 0-19/347
```

The count runs in the same transaction as the page, so 347 is the number of
rows your policies allow, not the table's raw row count. It costs a second
aggregate over the same predicate: leave it off for infinite scroll, use it for
page numbers.

### Keyset paging

OFFSET makes Postgres walk and discard every skipped row, so page 500 is
slower than page 1 and rows shift under a concurrent insert. For a long list,
filter on the last row you saw instead:

```bash
# page 1
?select=id,title,created_at&order=created_at.desc,id.desc&limit=20

# page 2 — everything strictly older than the last row of page 1
?select=id,title,created_at&order=created_at.desc,id.desc&limit=20&created_at=lt.2026-08-14T08:00:00Z
```

Add id as the tiebreaker so two rows with the same timestamp cannot both be
skipped or both be repeated.

## Filters on writes

PATCH and DELETE use the same grammar to choose their rows, and
at least one filter is required.

```bash
curl -s -X PATCH "$URL/rest/v1/articles?status=eq.draft&created_at=lt.2026-01-01" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -d '{"status":"archived"}'
```

DANGER:
An unfiltered PATCH or DELETE touches every row your policies allow — which for a service key is every row in the table. The server refuses it by default; Prefer: unsafe-mutation is the deliberate opt-out.

## What a bad filter looks like

Request | Response |

?titel=eq.x | 400 bad_request — unknown column "titel" on public.articles |

?title=contains.x | 400 bad_request — unknown operator "contains" |

?id=in.1,2,3 | 400 bad_request — the "in" operator takes a parenthesised list, got "1,2,3" |

?or=(a.eq.1 | 400 bad_request — unbalanced parentheses: missing ")" at position 6 |

?or=() | 400 bad_request — empty logical group |

?limit=-5 | 400 bad_request — limit must be a non-negative integer, got "-5" |

Range: bytes=0-19 | 400 bad_request — malformed Range header … expected items=<start>-<end> |

Range: items=20-5 | 400 bad_request — Range end 5 is before its start 20 |

?age=eq.old on an integer | 400 invalid_text_representation — SQLSTATE 22P02 |

NOTE:
None of these is an RLS problem. A policy denial is an empty array with a 200, never a 400 and never a 403.

## In the client

```ts
const { data, error, count } = await bl
  .from('articles')
  .select('id,title,tags', { count: 'exact' })
  .eq('published', true)
  .neq('kind', 'draft')
  .gte('score', 10)
  .ilike('title', '*sql*')
  .is('deleted_at', null)
  .in('category', ['db', 'ops'])
  .contains('tags', ['sql'])
  .not('state', 'eq', 'archived')
  .or('status.eq.draft,and(views.gte.100,pinned.is.true)')
  .textSearch('search', 'postgres -mysql', { type: 'websearch', config: 'english' })
  .order('created_at', { ascending: false, nullsFirst: false })
  .range(0, 19);
```

Every builder method maps one-to-one onto the grammar above, and
.filter(column, operator, value) reaches any operator without a named method.
To see the URL a chain would send without sending it:

```ts
console.log(bl.from('articles').select('id').eq('published', true).limit(2).build());
// { method: 'GET', path: '/rest/v1/articles?select=id&published=eq.true&limit=2', headers: {}, body: undefined }
```

==============================================================================
# Row level security
URL: https://baselyra.sarimtools.com/docs/rls.html
==============================================================================

# Row level security

This is the page to read properly. Everything else in Baselyra is convenience; this is the part that decides whether your data is safe. Baselyra performs no authorisation in JavaScript — Postgres decides, every time, for every path, and REST, storage, realtime and the client SDK all end at the same policies.

## The model

Every request that touches your data goes through one function, asRole(), which
opens a transaction and does three things before running anything:

```sql
BEGIN;
SELECT set_config('role', 'authenticated', true);                  -- or 'anon', or 'service_role'
SELECT set_config('request.jwt.claims', '{"sub":"6c1f…","role":"authenticated", …}', true);
SELECT set_config('request.jwt.claim.sub',  '6c1f…', true);
SELECT set_config('request.jwt.claim.role', 'authenticated', true);
-- your query runs here
COMMIT;
```

Both settings are LOCAL, so the commit restores the pooled connection. There is
no path by which one request's identity survives into the next.

What the caller sent | Postgres role | RLS |

Nothing, or the anon key | anon | Enforced |

Authorization: Bearer <user access token> | authenticated | Enforced |

The service key | service_role | Bypassed — BYPASSRLS |

A Studio session on /admin/v1 | service_role | Bypassed |

The three roles are NOLOGIN NOINHERIT, so membership never leaks privileges
implicitly: a connection only gets one by an explicit SET ROLE, which is what
asRole() does.

The anon key is public and is meant to be public. It is not a password; it only
names the role a request runs as. The service key is the opposite: it bypasses
every policy on the instance, so it belongs on a server and nowhere else — never in
a bundle, a mobile binary, or a NEXT_PUBLIC_* variable.

### Grants and policies are two different gates

[diagram: A request passes two independent gates: the table grant, which answers 403 when it is missing, and the row level security policy, which simply returns fewer rows.]

Two gates, two failure shapes. Knowing which one you hit tells you which thing to fix.

### Helper functions

These read request.jwt.claims and are executable by anon,
authenticated and service_role. They never raise — a policy that errors
turns a denied row into a failed query, and an anonymous request legitimately has
no claims at all.

Function | Returns | Notes |

auth.uid() | uuid | The signed-in user's id, NULL for anon. The sub claim is matched against a uuid regex before the cast, so a malformed one is NULL rather than an error inside your policy. |

auth.role() | text | anon, authenticated or service_role. Falls back to the request.jwt.claim.role GUC, then to anon. |

auth.email() | text | The email claim, NULL if absent. A phone-only user has none. |

auth.jwt() | jsonb | The whole verified claim set, including app_metadata and user_metadata. |

DANGER:
auth.is_admin() no longer exists, and neither does the
auth.users.is_admin column — db/project/001_schema.sql actively drops both. They
belonged to the old design where operators lived in auth.users. A policy that
still calls auth.is_admin() fails with function auth.is_admin() does not
exist, which makes the whole query fail rather than merely deny it.

### An application admin role

If your application needs its own admin role, keep it in the user's
app_metadata — a field only the service key can write — and read it from the
claims:

```sql
create policy invoices_admin_read on public.invoices
  for select to authenticated
  using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin');
```

```bash
curl -s -X PUT "$URL/auth/v1/admin/users/$USER_ID" \
  -H "authorization: Bearer $SERVICE_KEY" \
  -H 'content-type: application/json' \
  -d '{"app_metadata":{"role":"admin","plan":"pro"}}'
```

The claim is inside the signature, so it takes effect on that user's next sign-in
or token refresh — within an hour at the default TTL, or immediately if the client
calls bl.auth.refreshSession().

DANGER:
Never make an authorisation decision from user_metadata. The user writes
that field themselves through PUT /auth/v1/user. It is a display name and a theme
preference, not a claim. app_metadata is writable only with the service key —
that difference is the entire security property.

## What happens if you leave RLS off

This is the failure mode that matters, so it gets its own section.

db/project/001_schema.sql grants table privileges in public to anon
and authenticated — for existing tables, and, through ALTER DEFAULT
PRIVILEGES, for every table created later:

```sql
alter default privileges in schema public
  grant select, insert, update, delete on tables to anon, authenticated;
```

That is what makes a table work over /rest/v1 the moment you create it, with no
grant step. It also means:

DANGER:
A new table in public with RLS off is readable, writable and deletable by
anyone holding your anon key — which is a public string in your frontend. Not
"readable by logged-in users". By anyone, on the internet, with curl.

Reproduce it in ten seconds on a fresh install, and then never forget it:

```bash
curl -s -X DELETE "$URL/rest/v1/unprotected?id=gt.0" -H "apikey: $ANON_KEY"
```

So a new table is exactly two statements from safe:

```sql
alter table public.thing enable row level security;
-- then at least one policy, or nothing can read it at all
```

With RLS enabled and no policies, the table denies everything to
anon and authenticated — which is the correct, safe default. An empty result
from REST usually means "RLS is on and no policy admits you", not "no rows".

NOTE:
The same default grants apply to a table an import
creates, which is why the importer enables row level security on each target table
before a single row is inserted, and never turns it off.

### Audit what you have

```sql
select c.relname                        as table,
       c.relrowsecurity                 as rls_enabled,
       count(p.polname)                 as policies
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  left join pg_catalog.pg_policy p on p.polrelid = c.oid
 where n.nspname = 'public' and c.relkind in ('r', 'p')
 group by 1, 2
 order by rls_enabled, 1;
```

```bash
      table       | rls_enabled | policies
------------------+-------------+----------
 signup_leads     | f           |        0     Anything with rls_enabled = false is world-writable. Anything with RLS on and
zero policies is inert. The Studio's Database page shows the same thing per table,
and a table with RLS off is badged; so does the
VS Code extension, in the warning colour.

### Two ways to bypass your own policies

Views. A view runs with its owner's privileges by default, and the table
owner is exempt from RLS — Baselyra enables RLS without FORCE, which is what
lets the auth module manage sessions and tokens on the owner connection. A view
over an RLS-protected table therefore returns every row to whoever can select from
the view, and /rest/v1 exposes views.

```sql
create view public.recent_notes with (security_invoker = true) as
select id, title, created_at from public.notes order by created_at desc;

-- retrofit
alter view public.recent_notes set (security_invoker = true);
```

security definer functions. A function marked security definer runs
as its owner and bypasses RLS on everything it touches. That is exactly what you
want for the membership helpers below, and exactly what you do not want for a
function you expose over /rest/v1/rpc/. Default to security invoker and pin
search_path on anything you do mark definer.

## Writing policies

A policy has a name, a command, a list of roles, and one or two expressions:

- using (…) — which existing rows this command may see or touch
      (SELECT, UPDATE, DELETE).

- with check (…) — which rows may result from a write
      (INSERT, UPDATE).

[diagram: Permissive policies for a command are ORed together and widen access; restrictive policies are ANDed on top and narrow it; a table with row level security on and no policy at all admits nothing.]

How the policies on a table combine. Permissive policies OR; a restrictive policy ANDs on top of all of them.

Two habits worth adopting from the first policy you write:

```sql
-- 1. Index every column a policy filters on. A policy is a WHERE clause that
-- runs on every row of every query against that table.
create index notes_user_id_idx on public.notes (user_id);

-- 2. Wrap the helper in a scalar subquery so the planner evaluates it once per
-- statement instead of once per row.
using (user_id = (select auth.uid()))
```

The five sets below are complete and copy-pasteable. Run them in the Studio SQL
editor with the mode set to Write.

## 1. Private per-user data

A table only its owner can see. The default shape for notes, settings, documents,
anything personal.

```sql
create table public.notes (
id uuid primary key default gen_random_uuid(),
user_id uuid not null default auth.uid()
references auth.users(id) on delete cascade,
title text not null check (length(title) between 1 and 200),
body text not null default '',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

create index notes_user_id_idx on public.notes (user_id);

alter table public.notes enable row level security;

create policy notes_select_own on public.notes
for select to authenticated
using (user_id = (select auth.uid()));

create policy notes_insert_own on public.notes
for insert to authenticated
with check (user_id = (select auth.uid()));

create policy notes_update_own on public.notes
for update to authenticated
using (user_id = (select auth.uid()))
with check (user_id = (select auth.uid()));

create policy notes_delete_own on public.notes
for delete to authenticated
using (user_id = (select auth.uid()));
```

Why it is safe:

- No policy names anon, so anonymous callers get nothing — not an error, an
      empty result, which is also what you want, because existence is information.

- user_id defaults to auth.uid(), so clients never send it, and the
      with check refuses it if they try to send someone else's.

- The update policy repeats the condition in with check. Without that, a
      user could UPDATE … SET user_id = <someone else> and hand their row away.
      Always write both.

```ts
await bl.from('notes').insert({ title: 'Groceries' }); // user_id fills itself
const { data } = await bl.from('notes').select('*'); // only yours, no filter needed
```

And the proof, from the shell, with two different users' tokens against one URL:

```bash
curl -s "$URL/rest/v1/notes?select=id,title" -H "apikey: $ANON_KEY" -H "authorization: Bearer $ADA"
[{"id":"3f2a…","title":"Groceries"}]

curl -s "$URL/rest/v1/notes?select=id,title" -H "apikey: $ANON_KEY" -H "authorization: Bearer $GRACE"
[]
```

## 2. Public read, owner write

The blog shape: everyone reads what is published, only the author writes, and the
author can also see their own drafts.

```sql
create table public.articles (
id bigint generated always as identity primary key,
author uuid not null default auth.uid()
references auth.users(id) on delete cascade,
slug text not null unique,
title text not null,
body text not null default '',
published_at timestamptz,
created_at timestamptz not null default now()
);

create index articles_author_idx on public.articles (author);
create index articles_published_idx on public.articles (published_at desc)
where published_at is not null;

alter table public.articles enable row level security;

-- Anyone, signed in or not, sees published articles.
create policy articles_select_published on public.articles
for select to anon, authenticated
using (published_at is not null and published_at Moderators without a schema change, using the claim idiom:

```sql
create policy articles_moderate on public.articles
  for all to authenticated
  using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'moderator')
  with check ((auth.jwt() -> 'app_metadata' ->> 'role') = 'moderator');
```

CAREFUL:
for all is four policies in one — select, insert, update and
delete — and its using expression is used as the with check when you do
not write one. That is convenient here and dangerous elsewhere: on a table where
reading and writing have different rules, write the four separately.

## 3. Team membership

The multi-tenant shape, and the one where people most often write an infinite
loop by accident.

```sql
create table public.teams (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_by uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

create table public.team_members (
  team_id   uuid not null references public.teams(id) on delete cascade,
  user_id   uuid not null references auth.users(id) on delete cascade,
  role      text not null default 'member'
            check (role in ('owner', 'admin', 'member')),
  joined_at timestamptz not null default now(),
  primary key (team_id, user_id)
);

create index team_members_user_idx on public.team_members (user_id);

create table public.projects (
  id         bigint generated always as identity primary key,
  team_id    uuid not null references public.teams(id) on delete cascade,
  name       text not null,
  created_at timestamptz not null default now()
);

create index projects_team_idx on public.projects (team_id);
```

### The recursion trap

The obvious policy on team_members is:

```sql
-- DO NOT DO THIS
create policy members_read on public.team_members
  for select to authenticated
  using (team_id in (select team_id from public.team_members where user_id = auth.uid()));
```

Reading team_members invokes the policy, which reads team_members, which
invokes the policy:

```json
{"error":{"code":"internal_error",
  "message":"infinite recursion detected in policy for relation \"team_members\"","details":null}}
```

Every query against the table fails — including the ones that were working before
you added it. The fix is one security definer function: it runs as its owner, so
the policy does not re-enter, and search_path is pinned so nothing a caller
creates can resolve ahead of the objects it means to touch.

```sql
create or replace function public.team_role(target uuid) returns text
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select m.role
    from public.team_members m
   where m.team_id = target
     and m.user_id = auth.uid();
$$;

create or replace function public.is_team_member(target uuid) returns boolean
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select exists (
    select 1 from public.team_members m
     where m.team_id = target and m.user_id = auth.uid()
  );
$$;

revoke all on function public.team_role(uuid), public.is_team_member(uuid) from public;
grant execute on function public.team_role(uuid), public.is_team_member(uuid)
  to anon, authenticated;
```

CAREFUL:
Both are reachable at POST /rest/v1/rpc/is_team_member because every function in public is. That is fine here — they answer only about the caller's own membership, take a team id the caller already knows, and leak nothing. Check that property before you mark any function security definer.

### The policies

```sql
alter table public.teams        enable row level security;
alter table public.team_members enable row level security;
alter table public.projects     enable row level security;

-- Teams: members see their teams; admins and owners rename them; owners delete.
create policy teams_select_member on public.teams
  for select to authenticated
  using (public.is_team_member(id));

create policy teams_insert_any on public.teams
  for insert to authenticated
  with check (created_by = (select auth.uid()));

create policy teams_update_admin on public.teams
  for update to authenticated
  using (public.team_role(id) in ('owner', 'admin'))
  with check (public.team_role(id) in ('owner', 'admin'));

create policy teams_delete_owner on public.teams
  for delete to authenticated
  using (public.team_role(id) = 'owner');

-- Membership: everyone in a team sees the roster; admins and owners change it.
create policy members_select on public.team_members
  for select to authenticated
  using (public.is_team_member(team_id));

create policy members_write_admin on public.team_members
  for insert to authenticated
  with check (public.team_role(team_id) in ('owner', 'admin'));

create policy members_update_admin on public.team_members
  for update to authenticated
  using (public.team_role(team_id) in ('owner', 'admin'))
  with check (public.team_role(team_id) in ('owner', 'admin'));

-- Leaving is your own business; removing someone else needs a role.
create policy members_delete on public.team_members
  for delete to authenticated
  using (user_id = (select auth.uid())
         or public.team_role(team_id) in ('owner', 'admin'));

-- Everything owned by a team inherits the team's membership rule.
create policy projects_all_member on public.projects
  for all to authenticated
  using (public.is_team_member(team_id))
  with check (public.is_team_member(team_id));
```

### The chicken and egg

Creating a team leaves you outside it: you are not a member yet, so
members_write_admin refuses to let you add yourself. Solve it in the database,
where the rule cannot be forgotten by a client.

```sql
create or replace function public.add_team_creator() returns trigger
language plpgsql security definer
set search_path = pg_catalog, public
as $$
begin
  insert into public.team_members (team_id, user_id, role)
  values (new.id, new.created_by, 'owner')
  on conflict do nothing;
  return new;
end;
$$;

create trigger teams_add_creator
  after insert on public.teams
  for each row execute function public.add_team_creator();
```

```ts
const { data: team } = await bl.from('teams').insert({ name: 'Acme' }).select().single();
// You are already the owner — the trigger did it inside the same transaction.
await bl.from('projects').insert({ team_id: team.id, name: 'Website' });
```

## 4. A chat application

Room members read that room's messages. Nobody else can, including through a
realtime subscription.

```sql
create table public.rooms (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  is_public  boolean not null default false,
  created_by uuid not null default auth.uid() references auth.users(id),
  created_at timestamptz not null default now()
);

create table public.room_members (
  room_id   uuid not null references public.rooms(id) on delete cascade,
  user_id   uuid not null references auth.users(id) on delete cascade,
  joined_at timestamptz not null default now(),
  primary key (room_id, user_id)
);

create index room_members_user_idx on public.room_members (user_id);

create table public.messages (
  id         bigint generated always as identity primary key,
  room_id    uuid not null references public.rooms(id) on delete cascade,
  author     uuid not null default auth.uid()
             references auth.users(id) on delete cascade,
  body       text not null check (length(body) between 1 and 4000),
  created_at timestamptz not null default now()
);

-- The index the read policy and the message list both need.
create index messages_room_created_idx on public.messages (room_id, created_at desc);

create or replace function public.in_room(target uuid) returns boolean
language sql stable security definer
set search_path = pg_catalog, public
as $$
  select exists (
    select 1 from public.room_members m
     where m.room_id = target and m.user_id = auth.uid()
  );
$$;

revoke all on function public.in_room(uuid) from public;
grant execute on function public.in_room(uuid) to anon, authenticated;

alter table public.rooms        enable row level security;
alter table public.room_members enable row level security;
alter table public.messages     enable row level security;

-- Rooms: public ones are discoverable; private ones only to their members.
create policy rooms_select on public.rooms
  for select to anon, authenticated
  using (is_public or public.in_room(id));

create policy rooms_insert on public.rooms
  for insert to authenticated
  with check (created_by = (select auth.uid()));

-- Membership: members see the roster; you may join a public room yourself and
-- leave any room. Adding someone else to a private room is the creator's job.
create policy room_members_select on public.room_members
  for select to authenticated
  using (public.in_room(room_id));

create policy room_members_join on public.room_members
  for insert to authenticated
  with check (
    user_id = (select auth.uid())
    and exists (select 1 from public.rooms r where r.id = room_id and r.is_public)
  );

create policy room_members_leave on public.room_members
  for delete to authenticated
  using (user_id = (select auth.uid()));

-- Messages: read what your rooms contain, write as yourself into a room you are
-- in, edit and delete only your own.
create policy messages_select_member on public.messages
  for select to authenticated
  using (public.in_room(room_id));

create policy messages_insert_member on public.messages
  for insert to authenticated
  with check (author = (select auth.uid()) and public.in_room(room_id));

create policy messages_update_own on public.messages
  for update to authenticated
  using (author = (select auth.uid()))
  with check (author = (select auth.uid()));

create policy messages_delete_own on public.messages
  for delete to authenticated
  using (author = (select auth.uid()));
```

Turn on the change feed:

```sql
select baselyra.enable_realtime('public.messages');
```

```ts
const channel = bl.channel('public:messages');
channel.on('postgres_changes',
  { event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.' + roomId },
  ({ new: row }) => append(row));
await channel.subscribe();
```

The filter is a convenience, not a boundary. Before any change is
delivered, the server re-reads the row as the subscriber's own Postgres role;
messages_select_member decides, exactly as it does for a select. Remove the
filter and a non-member still receives nothing. A subscription can never show more
than a query would.

DANGER:
One exception, and it is worth knowing: a DELETE event is not
RLS-checked, because the row no longer exists to be re-read. Deletes go to every
subscriber of that table whose filter matches, carrying the old row. If a table's
contents are confidential, soft-delete instead
(update … set deleted_at = now()), which goes through the normal check. See
Realtime.

## 5. A per-user storage folder

Files are rows in storage.objects, so the same policy machinery applies. The
convention is a key prefixed with the owner's id:
user-files/<uid>/report.pdf.

Create the bucket with the service key or from the Studio — bucket rows are
service-only by design, because storage.buckets has no INSERT,
UPDATE or DELETE policy at all:

```sql
insert into storage.buckets (id, name, "public", file_size_limit)
values ('user-files', 'user-files', false, 26214400)
on conflict (id) do nothing;
```

db/project/002_rls.sql ships four permissive default policies on
storage.objects: read if the bucket is public, you own the object, or the owner
listed you in metadata.shared_with; write only if you own it. Those already stop
one user reading another's file. They do not stop a user
uploading into another user's folder, because the uploader would still be the
owner.

Add a restrictive policy, which ANDs with the defaults and is a no-op for every
other bucket:

```sql
create policy user_files_own_folder on storage.objects
  as restrictive
  for all to anon, authenticated
  using (
    bucket_id <> 'user-files'
    or split_part(name, '/', 1) = (select auth.uid())::text
  )
  with check (
    bucket_id <> 'user-files'
    or split_part(name, '/', 1) = (select auth.uid())::text
  );
```

For anon, auth.uid() is NULL, the comparison is NULL, the
restrictive policy fails, and the bucket is closed to anonymous callers entirely.

```ts
const path = user.id + '/' + file.name;
await bl.storage.from('user-files').upload(path, file);
await bl.storage.from('user-files').list(user.id + '/');
const { data } = await bl.storage.from('user-files').createSignedUrl(path, 3600);
```

Trying to write into someone else's folder is refused by the policy, and the
storage route reports that as a 403 rather than a silent no-op:

```json
{"error":{"code":"forbidden","message":"Not allowed to write this object","details":null}}
```

### Sharing one file with one person

The default read policy already honours metadata.shared_with, an array of user
ids:

```sql
update storage.objects
   set metadata = jsonb_set(metadata, '{shared_with}', '["6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11"]'::jsonb)
 where bucket_id = 'user-files' and name = '3f2a…/report.pdf';
```

Or hand out a signed URL, which needs no account at all. A signed URL is an HMAC
over bucket, key and expiry — treat it as a bearer credential and keep the expiry
short.

### Replacing the defaults instead

If the shipped rules do not suit you, drop them and write your own — nothing in
Baselyra depends on their names:

```sql
drop policy objects_select on storage.objects;
drop policy objects_insert on storage.objects;
drop policy objects_update on storage.objects;
drop policy objects_delete on storage.objects;
```

CAREFUL:
Do this in one migration together with your replacements. Between the drop and the create, every bucket denies everything, including your public ones.

## Testing a policy without a client

Impersonate a role in the SQL editor. Roll it back so the pooled connection is not
left holding a role:

```sql
begin;
  select set_config('role', 'authenticated', true);
  select set_config('request.jwt.claims',
    '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated","email":"ada@example.com",
      "app_metadata":{"role":"admin"}}',
    true);

  select * from public.notes;              -- exactly what that user would see over REST
  insert into public.notes (title) values ('probe');   -- and what they may write
rollback;
```

DANGER:
The SQL editor itself runs with BYPASSRLS, so a table that looks fine in the Studio grid may still be denying every request from your app. The block above is how you check what your users actually see. Do not skip it because the grid looked right.

To check the anonymous case, set the role to anon and the claims to the empty
string:

```sql
begin;
  select set_config('role', 'anon', true);
  select set_config('request.jwt.claims', '', true);
  select * from public.articles;           -- what a logged-out visitor sees
rollback;
```

## Performance

Habit | Why |

Index every column a policy filters on | A policy is a WHERE clause evaluated for every row the query would otherwise touch. An unindexed user_id turns every read into a sequential scan. |

Wrap helpers in (select …) | using (user_id = (select auth.uid())) lets the planner treat the call as a one-time filter instead of re-evaluating it per row. |

Prefer a security definer helper over a correlated subquery | One indexed lookup per statement beats a subquery per row — and it is also what avoids the recursion trap. |

Watch EXPLAIN under the right role | A plan taken as the owner does not include the policy. Wrap explain (analyze, buffers) in the impersonation block above. |

## Failure modes

What you see | What it means | What to do |

200 with [] from a table you know has rows | RLS is on and no policy admits your role. The commonest of all. | Run the audit query, then the impersonation block |

403 insufficient_privilege | The grant is missing, not the policy. SQLSTATE 42501. | grant select, insert, update, delete on public.t to anon, authenticated |

500 — infinite recursion detected in policy for relation … | A policy on a table reads that same table | Move the lookup into a security definer function |

500 — function auth.is_admin() does not exist | A policy carried over from an older instance or an import | Replace it with (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' |

An insert succeeds but the row is invisible afterwards | The with check allowed it and the using of the select policy does not | Make the two agree, or add Prefer: return=representation and read what came back |

A user can move their row to another owner | The update policy has a using and no with check | Add the with check |

Everything works in the Studio and nothing works in the app | The Studio bypasses RLS | Test with a real user token, or with the impersonation block |

A view returns rows the underlying policy hides | The view is not security_invoker | alter view … set (security_invoker = true) |

Realtime delivers deletes for rows a user cannot read | Deletes are not RLS-checked — the row is gone | Soft-delete, or do not enable realtime on that table |

## Checklist before you ship

- Every table in public has relrowsecurity = true.

- Every table with RLS on has at least one policy, or is deliberately inert.

- Every update policy has a with check, not just a using.

- Every column a policy filters on is indexed.

- Every view in public is security_invoker = true.

- No policy reads user_metadata — users write that field themselves.

- The service key is not in any client bundle, mobile app or public env var.

- You have run the impersonation block for at least one policy per table.

- Any realtime-enabled table whose rows are confidential uses soft deletes.

==============================================================================
# Auth
URL: https://baselyra.sarimtools.com/docs/auth.html
==============================================================================

# Auth

Baselyra has two completely separate identity systems, and understanding the split is the first thing to do. The rest of this page is about the second one: /auth/v1, the API your application calls.

## Two populations, one instance

| Studio account | Application user |

Who | You, and whoever else operates the instance | The end users of the app you build |

Table | control.platform_users | auth.users |

Database | baselyra_control | baselyra — the project database |

Signs in at | POST /admin/v1/login | POST /auth/v1/token |

Token carries | role: service_role, typ: "platform", pr: owner|admin|viewer | role: authenticated, sub, app_metadata, user_metadata |

Can open the Studio | Yes | Never |

Visible to /rest/v1 | No — it is in another database | Only through your own policies |

Password | Independent | Independent |

auth.users is your product's data. A chat app's members. A shop's
customers. Its row count is a number you show a customer. An operator account
sitting in it is a bug, not a convenience.

This is exactly the relationship a Supabase dashboard login has to a Supabase
project's users: unrelated accounts, unrelated passwords, unrelated tables. The
Studio side is documented on The Studio.

DETAIL:
There is no is_admin column and no auth.is_admin() function; both were
removed and 001_schema.sql drops them from an older instance. Your application's
own admin role goes in app_metadata and is read in a policy as
(auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'.

## Sessions and tokens

Signing in returns two tokens with very different natures.

```json
{"access_token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9…",
 "token_type":"bearer",
 "expires_in":3600,
 "expires_at":1771998842,
 "refresh_token":"o3Hn7Vb2XkQ0…",
 "user":{"id":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11",
         "aud":"authenticated","role":"authenticated",
         "email":"ada@example.com","phone":null,
         "email_confirmed_at":"2026-08-24T09:22:00.000Z","phone_confirmed_at":null,
         "confirmed_at":"2026-08-24T09:22:00.000Z",
         "last_sign_in_at":"2026-08-24T09:24:02.117Z","banned_until":null,
         "app_metadata":{"provider":"email","providers":["email"]},
         "user_metadata":{"name":"Ada"},
         "created_at":"2026-08-24T09:20:11.004Z","updated_at":"2026-08-24T09:24:02.117Z"}}
```

The access token is a JWT signed with JWT_SECRET. Its claims are
published to Postgres on every request as request.jwt.claims, which is what
auth.uid(), auth.role(), auth.email() and auth.jwt() read:

```json
{"sub":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11",
 "role":"authenticated",
 "aud":"authenticated",
 "email":"ada@example.com",
 "session_id":"a91c…",
 "app_metadata":{"provider":"email","providers":["email"]},
 "user_metadata":{"name":"Ada"},
 "iss":"baselyra","iat":1771995242,"exp":1771998842}
```

It is valid for JWT_ACCESS_TTL seconds (3600 by default) and
cannot be revoked before it expires. Keep the TTL short; that is what the
refresh token is for.

The refresh token is 48 opaque random bytes in auth.sessions, valid for
JWT_REFRESH_TTL (30 days). The user object never includes
encrypted_password, on any route — every field is picked by name rather than
spread, so no column added to auth.users later can leak by accident.

### Rotation and theft detection

Every refresh consumes its token and issues a new one, linked to the old by
parent_id. Presenting a refresh token that was already spent is either a stolen
copy being replayed or a legitimate client replaying an old one — and the server
cannot tell which, so it assumes the worst.

DANGER:
The entire rotation chain is revoked: every ancestor and every descendant
session of the reused token, walked as an undirected graph so a corrupted cycle
terminates rather than hangs. The response is a 401.

```json
{"error":{"code":"unauthorized","message":"This refresh token has already been used","details":null}}
```

The user signs in again; an attacker holding a stolen token gets nothing. If you
see users being signed out unexpectedly, look for a client that retries a refresh
after a network error without storing the new token — that is the same shape as
theft from the server's point of view.

What the server finds | Outcome |

No session for that token | 401 — Invalid or expired refresh token |

A session with revoked_at set | 401 — the whole family is revoked |

A session past expires_at | 401 — Invalid or expired refresh token |

A live session | A new access token and a new refresh token |

### Throttling

Two independent layers, protecting two different things.

Layer | Limit | Protects |

Per IP, on the expensive routes | 10/min /signup; 30/min /token and /verify; 20/min /authorize; 5/min the email and OTP flows | The process |

Per account, in auth.attempts | AUTH_MAX_ATTEMPTS (8) failures per email+IP inside AUTH_ATTEMPT_WINDOW (900s), plus wider counters on the email alone and the IP alone | One user's password from being guessed from many addresses |

```bash
HTTP/1.1 429 Too Many Requests
retry-after: 274

{"error":{"code":"rate_limited","message":"Too many attempts. Try again in 274 seconds.","details":null}}
```

Nothing is locked permanently, so nobody can lock a competitor out of their own
account. Timing is equalised: the password is hashed before the account lookup,
so "no such user" and "wrong password" cost the same scrypt derivation.

## Endpoints

Route | Body | Notes |

POST /auth/v1/signup | {email, password, data?} | data becomes user_metadata |

POST /auth/v1/token?grant_type=password | {email, password} | |

POST /auth/v1/token?grant_type=refresh_token | {refresh_token} | Rotates |

POST /auth/v1/logout?scope=local|global | — | Auth required. 204. |

GET /auth/v1/user | — | Auth required |

PUT /auth/v1/user | {email?, password?, data?} | Auth required |

POST /auth/v1/recover | {email} | Always 200 |

POST /auth/v1/magiclink | {email} | Always 200 |

POST /auth/v1/otp | {email} or {phone} | One route, two channels — see Phone codes |

POST /auth/v1/resend | {type, email} | type: signup, confirmation, magiclink, recovery, otp |

POST /auth/v1/verify | {type, token, password?, email?, phone?} | The programmatic redemption |

GET /auth/v1/verify?type=&token= | — | What the link in an email hits |

GET /auth/v1/providers | — | The third-party providers configured on this instance |

GET /auth/v1/authorize?provider= | — | Starts a third-party sign-in |

GET /auth/v1/callback | — | Where the provider returns |

/recover, /magiclink, /otp and /resend always answer
200 with the same body whether or not the address exists, and take the same time
either way — a 350 ms floor absorbs the difference a database round trip would
otherwise reveal.

```json
{"message":"If an account exists for that address, an email is on its way."}
```

### Sign up

```bash
curl -s -X POST "$URL/auth/v1/signup" -H 'content-type: application/json' \
  -d '{"email":"ada@example.com","password":"correct-horse-battery","data":{"name":"Ada"}}'
```

With AUTH_CONFIRM_EMAIL=true (the default) the response carries
session: null and the account cannot sign in until the address is confirmed.

CAREFUL:
Signing up with an address that already exists also answers 200, with a
plausible but fabricated user object and no session. That is deliberate: the
endpoint must not become a way to test whether an address is registered. The real
owner of the address gets an email — a fresh confirmation link if their account was
never confirmed, or a "someone signed up with your address" notice pointing at the
sign-in page if it was, so no credential is minted for a stranger. Do not treat the
returned id as real until the user has actually signed in.

With AUTH_CONFIRM_EMAIL=false there is no answer that both succeeds and stays
silent, so the honest one is used: 409 conflict, A user with this email address
already exists.

### Sign in and sign out

```bash
curl -s -X POST "$URL/auth/v1/token?grant_type=password" \
  -H 'content-type: application/json' \
  -d '{"email":"ada@example.com","password":"wrong"}'
```

```json
{"error":{"code":"invalid_credentials","message":"Invalid login credentials","details":null}}
```

The same error for a wrong password and an unknown address. Two other outcomes
are distinguishable, and both are checked after the password so they cannot be
used as an enumeration oracle by someone who does not know it:

```json
{"error":{"code":"user_banned","message":"This account is temporarily suspended",
  "details":{"banned_until":"2026-09-01T00:00:00.000Z"}}}

{"error":{"code":"email_not_confirmed","message":"Confirm your email address before signing in","details":null}}
```

?scope=local (the default) revokes the current session; ?scope=global
revokes every session that user has — the "sign out everywhere" button. Neither
invalidates an already-issued access token before it expires.

## Email flows

Six templates, editable in Studio → Email with a live preview and a test
send. Substitutions are {{ .ConfirmationURL }}, {{ .Token }},
{{ .Email }}, {{ .NewEmail }} and {{ .SiteURL }}.

Template | Sent when | Token lifetime |

confirmation | Sign-up, and resend | 24 hours |

invite | An invitation | 24 hours |

magiclink | POST /auth/v1/magiclink | 1 hour |

recovery | POST /auth/v1/recover | 1 hour |

email_change | PUT /auth/v1/user with a new email | 24 hours |

otp | POST /auth/v1/otp with an email | AUTH_OTP_TTL (600s) |

Only a SHA-256 digest of each token is stored, in
auth.one_time_tokens, and each is single-use — the redeeming UPDATE
re-checks used_at IS NULL under the row lock, so of two concurrent redemptions
exactly one wins. A six-digit OTP is additionally hashed scoped to the address or
number it was sent to: twenty bits of entropy matched against every pending code
in the table at once would be far weaker than matched against one account's.

### The two verify routes

GET /auth/v1/verify is what the link in an email hits. It redeems the token and
redirects to BASELYRA_SITE_URL with the result in the URL fragment:

```bash
HTTP/1.1 303 See Other
location: https://app.example.com/#access_token=eyJ…&refresh_token=o3Hn…&expires_in=3600&token_type=bearer&type=magiclink
```

A fragment never reaches a server log or a Referer header, which a query
string would. The destination is always BASELYRA_SITE_URL, never anything
from the request — that is what keeps this from being an open redirect. A failure
comes back the same way:

```bash
location: https://app.example.com/#error=unauthorized&error_description=This%20link%20or%20code%20is%20invalid%2C%20expired%20or%20already%20used
```

Read it on the landing page and hand it to the client:

```ts
const params = new URLSearchParams(location.hash.slice(1));
const access_token = params.get('access_token');
if (access_token) {
  bl.auth.setSession({
    access_token,
    refresh_token: params.get('refresh_token'),
    expires_in: Number(params.get('expires_in')),
    expires_at: Math.floor(Date.now() / 1000) + Number(params.get('expires_in')),
    token_type: 'bearer',
    user: null,
  });
  history.replaceState(null, '', location.pathname);   // keep the tokens out of the back button
  await bl.auth.getUser();
}
```

POST /auth/v1/verify is the programmatic twin, for a six-digit code typed into
your own form:

```ts
await bl.auth.verifyOtp({ email, token: '123456', type: 'otp' });
await bl.auth.verifyOtp({ token: linkToken, type: 'recovery', password: 'a-new-one' });
```

### When SMTP is not configured

With SMTP_HOST empty, nothing is sent and the message — including the link — is
printed to the app log:

```bash
[mail] SMTP not configured, would have sent to
[mail]   subject: Confirm your email address
[mail]   link:    http://127.0.0.1:3130/auth/v1/verify?token=8Yb…&type=confirmation
```

DANGER:
That is right for a laptop and exactly wrong in production: your users cannot confirm an address or reset a password, and working recovery links accumulate in your log file. A configured relay that rejects a message raises an error — sending is only skipped when SMTP_HOST is empty.

## Metadata

| Written by | Use for |

user_metadata | The user, via PUT /auth/v1/user | Display name, avatar URL, preferences |

app_metadata | The service key only | Roles, plan, entitlements — anything a policy reads |

DANGER:
Never make an authorisation decision from user_metadata. A user can set it to anything they like, including {"role":"admin"}.

```bash
curl -s -X PUT "$URL/auth/v1/admin/users/$USER_ID" \
  -H "authorization: Bearer $SERVICE_KEY" -H 'content-type: application/json' \
  -d '{"app_metadata":{"role":"admin","plan":"pro"}}'
```

The change appears in that user's claims on their next sign-in or token refresh —
within an hour at the default TTL. To apply it immediately, have the client call
bl.auth.refreshSession().

## Managing users from a server

These require the service key, never a user token. Studio → Auth is a
UI over the same routes.

Route | Body |

GET /auth/v1/admin/users?page=&per_page=&search= | search matches an email substring or an exact id |

POST /auth/v1/admin/users | {email, password, email_confirm?, data?} → 201 |

PUT /auth/v1/admin/users/:id | {email?, password?, data?, app_metadata?, banned_until?} |

DELETE /auth/v1/admin/users/:id | 204 |

```bash
curl -s "$URL/auth/v1/admin/users?per_page=2&search=ada" -H "authorization: Bearer $SERVICE_KEY"
```

```json
{"users":[{"id":"6c1f5c62-…","email":"ada@example.com","app_metadata":{"provider":"email","providers":["email"]},
           "user_metadata":{"name":"Ada"},"banned_until":null,"…":"…"}],
 "total":1,"page":1,"per_page":2}
```

Banning is banned_until. A BEFORE UPDATE trigger restores that column for
anyone who is not service_role, so a user cannot unban themselves even if a
policy would otherwise let them update their own row — WITH CHECK sees only the
proposed row and cannot express "this column may not change", so the trigger does
it, silently, turning a self-unban into a no-op rather than an error.

## Passwords

Hashed with scrypt from node:crypto — memory-hard, in the standard library, no
native build step. The stored value carries its own parameters
(scrypt$N$r$p$salt$hash, N=16384 r=8 p=1), so the cost can be raised later
without invalidating existing hashes.

A second format is recognised but never produced: bcrypt$<hash>, written by
the import engine. Verification asks Postgres
(select $1 = crypt($2, $1) via pgcrypto, which is already installed), and on the
first successful sign-in the row is quietly re-hashed to scrypt. bcrypt is not
reimplemented anywhere in this codebase. That is why users migrated from Supabase
keep their passwords and why the bcrypt rows retire themselves.

Rule | Value |

Minimum length | AUTH_MIN_PASSWORD_LENGTH, 8 by default |

Maximum length | 256 bytes — scrypt is linear in input length, so an unbounded password is a CPU amplifier |

Composition rules | None. Length is the property that matters. |

Unusable password | A marker that parses as no scheme at all. Written by imports that could not carry a hash. It is rejected after burning an equivalent scrypt, so the timing does not distinguish those accounts. |

## In the client

```ts
await bl.auth.signUp({ email, password, data: { name: 'Ada' } });
await bl.auth.signInWithPassword({ email, password });
await bl.auth.signInWithOtp({ email, type: 'magiclink' });   // or type: 'otp'
await bl.auth.verifyOtp({ email, token: '123456', type: 'otp' });
await bl.auth.resetPasswordForEmail(email);
await bl.auth.updateUser({ password: 'new-one' });
await bl.auth.refreshSession();
await bl.auth.signOut({ scope: 'global' });

const { data: { subscription } } = bl.auth.onAuthStateChange((event, session) => {
  setUser(session?.user ?? null);   // SIGNED_IN | SIGNED_OUT | TOKEN_REFRESHED | USER_UPDATED
});
```

The session is persisted in localStorage where it exists and in memory where it
does not, so server rendering and React Native are safe by default. The access
token is renewed a minute before it expires, and the renewed token is re-attached
to the realtime socket automatically. A refresh that fails with a 4xx clears the
session and emits SIGNED_OUT; anything else is treated as the network and retried
in ten seconds.

## Configuration

Variable | Default | Effect |

AUTH_CONFIRM_EMAIL | true | Require confirmation before the first sign-in |

AUTH_ALLOW_SIGNUPS | true | Allow POST /auth/v1/signup |

AUTH_MIN_PASSWORD_LENGTH | 8 | |

AUTH_MAX_ATTEMPTS | 8 | Failed sign-ins per account+IP before lockout |

AUTH_ATTEMPT_WINDOW | 900 | That window, in seconds |

AUTH_OTP_TTL | 600 | One-time code lifetime |

JWT_ACCESS_TTL | 3600 | Access token lifetime |

JWT_REFRESH_TTL | 2592000 | Refresh token lifetime |

BASELYRA_SITE_URL | — | Where GET /auth/v1/verify redirects |

## Failure modes

What you see | Why | Fix |

email_not_confirmed right after signup | Working as configured | Open the link from the log, or set AUTH_CONFIRM_EMAIL=false in development |

Signup returns a user you cannot sign in as | The address already exists and the response is a decoy | Sign in instead, or use password recovery |

Everything is 401 after a redeploy | JWT_SECRET changed — a new .env, or setup.sh run again on a fresh clone | Restore the old secret, or accept that everyone signs in again and re-issue the anon key |

Users are signed out at random | A client retries a refresh without storing the rotated token, which the server reads as reuse | Store every rotated refresh token; the SDK does this for you |

401 exactly one hour into a session | The access token expired and nothing refreshed it | Use the SDK, or call /token?grant_type=refresh_token yourself |

A policy sees no app_metadata change | The claim is inside the signature and the old token is still valid | bl.auth.refreshSession(), or wait out the TTL |

No email ever arrives | SMTP_HOST is empty, so it went to the log | Configure SMTP — Troubleshooting |

A second owner account appears after a restart | BASELYRA_ADMIN_EMAIL was left in .env after you changed your address | Clear both admin variables |

==============================================================================
# Third-party sign-in
URL: https://baselyra.sarimtools.com/docs/oauth.html
==============================================================================

# Third-party sign-in

Baselyra signs users in with seven providers. A provider is offered only when both halves of its credential are configured, the whole round trip is server-side, and the session comes back to your site in a URL fragment. There is no Apple sign-in.

## The providers

Provider | id | PKCE | Scope requested |

Google | google | Yes (S256) | openid email profile |

GitHub | github | No | read:user user:email |

LinkedIn | linkedin | No | openid profile email |

Facebook | facebook | Yes (S256) | public_profile,email |

Instagram | instagram | No | instagram_business_basic |

TikTok | tiktok | Yes (S256) | user.info.basic |

Envato | envato | No | none — the app's permissions are set at Envato |

NOTE:
There is no Apple sign-in, and no way to add a provider from configuration: each one is a descriptor in src/auth/oauth/providers.ts, because the differences between them are not cosmetic. TikTok spells the public credential client_key and nests the user under data.user; Facebook and Instagram return nothing without a fields list; GitHub's /user carries the public email, which is usually null, so the address comes from a second call to /user/emails; Envato has no OIDC endpoint at all.

## Configuring a provider

- Register the application with the provider

The redirect URI to give them is always this, and it is built from
BASELYRA_PUBLIC_URL:

```bash
https://api.example.com/auth/v1/callback
```

One callback URL for every provider. If BASELYRA_PUBLIC_URL is wrong, the
provider will refuse the redirect and you will see the provider's own error page,
not one of ours.

- Put both halves in the environment

```bash
BASELYRA_OAUTH_GOOGLE_CLIENT_ID=1234-abc.apps.googleusercontent.com
BASELYRA_OAUTH_GOOGLE_CLIENT_SECRET=GOCSPX-…
```

The variable name is BASELYRA_OAUTH_ + the provider id uppercased +
_CLIENT_ID / _CLIENT_SECRET. A provider is offered only when both are
non-empty: half-configured is indistinguishable from not configured, and a
sign-in button that cannot work is worse than no button.

- Allow the redirect targets

```bash
BASELYRA_SITE_URL=https://app.example.com
BASELYRA_OAUTH_REDIRECT_ALLOWLIST=https://staging.example.com,http://localhost:5173
```

BASELYRA_SITE_URL is always allowed. Each extra entry allows an origin; adding
a path narrows the allowance to that path and below. A redirect_to that is not
on the list is refused at /authorize and bounced nowhere — the whole point of
the list is that an unlisted target never receives a redirect from this server.

- Restart and check

```bash
docker compose up -d
curl -s "$URL/auth/v1/providers"
```

```json
{"providers":[
  {"id":"google","name":"Google","url":"https://api.example.com/auth/v1/authorize?provider=google"},
  {"id":"github","name":"GitHub","url":"https://api.example.com/auth/v1/authorize?provider=github"}]}
```

Render your buttons from this response and they all work by construction.

## The round trip

[diagram: The OAuth round trip: the browser asks Baselyra to start, Baselyra stores a single-use state and PKCE verifier and redirects to the provider, the provider redirects back with a code, Baselyra claims the state with a delete, exchanges the code, reads the profile and redirects to the site with the session in the URL fragment.]

Nine steps, none of which the browser can forge. The state value and the PKCE verifier never leave the server, and the state row is claimed with a DELETE so a replay finds nothing.

Start it by sending the browser to /authorize. It is a full-page navigation,
not fetch — the provider has to be able to render its own consent screen.

```ts
const params = new URLSearchParams({
  provider: 'google',
  redirect_to: window.location.origin + '/auth/callback',
});
window.location.href = BASELYRA_URL + '/auth/v1/authorize?' + params;
```

The session arrives on your page as a URL fragment, exactly as an email link's
does:

```bash
https://app.example.com/auth/callback#access_token=eyJ…&refresh_token=o3Hn…&expires_in=3600&expires_at=1771998842&token_type=bearer&provider=google
```

```ts
const p = new URLSearchParams(location.hash.slice(1));
if (p.get('error')) {
  showError(p.get('error_description') ?? p.get('error'));
} else if (p.get('access_token')) {
  bl.auth.setSession({
    access_token: p.get('access_token'),
    refresh_token: p.get('refresh_token'),
    expires_in: Number(p.get('expires_in')),
    expires_at: Number(p.get('expires_at')),
    token_type: 'bearer',
    user: null,
  });
  history.replaceState(null, '', location.pathname);
  await bl.auth.getUser();
}
```

DETAIL:
The fragment rather than the query string, because a fragment is never sent to a server: the tokens stay out of access logs, out of the Referer header of whatever the landing page loads next, and out of every proxy in between. Call history.replaceState so they are not in the back button either.

### What the server does with the state

- auth.oauth_states holds one row per sign-in in flight: the state, the
provider, the PKCE verifier where the provider supports it, and the
already-validated redirect_to. It lives ten minutes.

- The table has RLS on and no policy, and anon and
authenticated are revoked from it outright. A row here is a live credential;
being able to read one from /rest/v1 would let any anonymous caller complete
somebody else's sign-in.

- The callback claims the row with DELETE … RETURNING, so a replayed state
finds nothing however fast it comes back — single use is a property of the
statement, not of a later check that a second request could race past. The value
is then compared again in constant time.

- The redirect_to is re-validated against the allow-list after the round
trip: if the list shrank while the person was at the provider, the old target is no
longer honoured.

- The insert sweeps expired rows in the same statement, so the table stays
bounded without a background job.

## Identity linking

Every third-party account becomes a row in auth.identities, unique on
(provider, provider_id). What happens on a sign-in depends on what already
exists:

Situation | Outcome |

An identity row already matches this provider account | Sign in as that user. Checked first, so a returning user is recognised even after they change their address at the provider. |

No identity, and no account with that email | Create a new user. Refused with 403 when AUTH_ALLOW_SIGNUPS=false. |

No identity, an account with that email, and the provider says the address is verified | Link the provider to the existing account. |

No identity, an account with that email, and the provider does not verify it | Blocked, with 409 conflict. |

```json
{"error":{"code":"conflict",
  "message":"That email address already belongs to an account and this provider does not verify it. Sign in with your password first, then connect this provider.",
  "details":null}}
```

DANGER:
That last rule is the account-takeover defence. A provider that lets a user claim
an arbitrary unverified address could otherwise be used to take over any account
by signing up at the provider with the victim's email. Only email_verified
from the provider — not merely the presence of an address — may attach a sign-in to
an existing account.

A successful sign-in also refreshes what the provider knows: the identity row's
identity_data is updated, last_sign_in_at is stamped, and
app_metadata.providers accumulates the provider id, so a user who has both a
password and Google reads as {"provider":"email","providers":["email","google"]}.
A ban is checked after the account is resolved, so it holds however the person
reaches the door.

## Policies and OAuth users

Nothing about a third-party user is special in a policy. They are a row in
auth.users with auth.uid(), an email, and metadata:

```sql
-- Which providers this user has, from inside a policy
select auth.jwt() -> 'app_metadata' -> 'providers';

-- Everything they have linked
select provider, provider_id, identity_data ->> 'name'
  from auth.identities
 where user_id = auth.uid();
```

auth.identities carries a select policy for the row's own owner, so a signed-in
user can list their own linked accounts and nobody else's.

## Failure modes

What you see | Why | Fix |

400 bad_request — redirect_to is not an allowed redirect target | The target is not BASELYRA_SITE_URL and not on the allow-list | Add its origin to BASELYRA_OAUTH_REDIRECT_ALLOWLIST |

The provider shows redirect_uri_mismatch | BASELYRA_PUBLIC_URL does not match what you registered | Register <BASELYRA_PUBLIC_URL>/auth/v1/callback exactly, scheme and all |

#error=bad_request — That sign-in provider is not enabled on this instance | One half of the credential pair is missing or misspelled | Set both _CLIENT_ID and _CLIENT_SECRET and restart |

#error=bad_request — This sign-in has expired or was already completed | More than ten minutes at the provider, the back button after a completed sign-in, or a replayed state | Start again |

#error=oauth_denied | The person cancelled at the provider | Nothing — show your sign-in page again |

#error=conflict on a first sign-in | The email belongs to an existing account and this provider does not verify addresses | Sign in with the password, then link |

A provider is missing from /auth/v1/providers | Its credentials are not both set in the running process | docker compose up -d after editing .env — nothing is read from disk at runtime |

Sign-in works, then the user has no email | The provider did not release one — Instagram and TikTok often do not | Prompt for an address after the first sign-in; email is nullable |

==============================================================================
# Phone one-time codes
URL: https://baselyra.sarimtools.com/docs/phone-otp.html
==============================================================================

# Phone one-time codes

A phone number gets a six-digit code and trades it for the same session any other auth flow issues. The number is normalised to E.164 before anything else happens, only a digest of the code is stored, and every send is capped — because unlike an email, each message costs you money.

## The two requests

```bash
curl -s -X POST "$URL/auth/v1/otp" \
  -H 'content-type: application/json' \
  -d '{"phone":"+14155552671"}'
```

```json
{"message":"If that number can receive messages, a code is on its way."}
```

The same answer for a first send, a resend inside the cooldown, a banned account
and a number nobody has ever used. The account is created on the first request,
so there is nothing to reveal about who is registered.

```bash
curl -s -X POST "$URL/auth/v1/verify" \
  -H 'content-type: application/json' \
  -d '{"phone":"+14155552671","token":"418302"}'
```

```json
{"user":{"id":"b21e…","phone":"+14155552671","email":null,
         "phone_confirmed_at":"2026-08-24T11:02:44.881Z","confirmed_at":"2026-08-24T11:02:44.881Z",
         "app_metadata":{"provider":"phone","providers":["phone"]},"user_metadata":{}, "…":"…"},
 "session":{"access_token":"eyJ…","token_type":"bearer","expires_in":3600,
            "expires_at":1772002964,"refresh_token":"pQ9…","user":{"…":"…"}}}
```

Redeeming the code is what proves the handset is theirs, which is exactly what
phone_confirmed_at records. The session is identical to a password session in
every other way — same claims, same rotation, same policies.

```ts
// The SDK's signInWithOtp is email-only; a phone code is two plain calls.
await fetch(BASELYRA_URL + '/auth/v1/otp', {
  method: 'POST',
  headers: { 'content-type': 'application/json', apikey: ANON_KEY },
  body: JSON.stringify({ phone }),
});

const res = await fetch(BASELYRA_URL + '/auth/v1/verify', {
  method: 'POST',
  headers: { 'content-type': 'application/json', apikey: ANON_KEY },
  body: JSON.stringify({ phone, token: code }),
});
const { session } = await res.json();
bl.auth.setSession(session);
```

## Numbers are normalised first

Every number is converted to E.164 — a plus, a country code that never starts
with zero, at most fifteen digits — before it reaches the database or causes a
message. Only the normalised form is ever stored, because
+1 415 555 2671 and (415) 555-2671 are one handset and would otherwise be two
accounts.

Typed | With SMS_DEFAULT_COUNTRY=1 | Why |

+14155552671 | +14155552671 | Already international; untouched |

(415) 555-2671 | +14155552671 | The default country code is prefixed |

0415 555 2671 | +14155552671 | One leading trunk zero is dropped |

001 415 555 2671 | +14155552671 | 00 is the international access prefix |

+1 415 555 2671 ext 4 | rejected | Silently turning it into a number ending in 4 would text a stranger |

+44 20 +7946 0018 | rejected | A plus after the first character is two numbers, not a formatting quirk |

4155552671 with no default country | rejected | There is nothing to resolve it against |

CAREFUL:
The single dropped trunk zero is the near-universal rule, not the universal one — Italy keeps its leading zero. If your users are there, have clients send +39… directly, which every branch leaves untouched.

```json
{"error":{"code":"bad_request",
  "message":"A valid phone number in international format is required","details":null}}
```

A CHECK constraint on auth.users.phone enforces the same shape at the
database level, so a hand-written UPDATE or an admin edit cannot reintroduce a
badly formatted number. It is added NOT VALID so the migration cannot fail on
rows an earlier build wrote; promote it once you have cleaned those up:

```sql
alter table auth.users validate constraint users_phone_e164;
```

## Choosing a sender

Set SMS_PROVIDER to one of these and give it the credentials it needs.

SMS_PROVIDER | Needs |

twilio | TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, SMS_FROM |

vonage | VONAGE_API_KEY, VONAGE_API_SECRET, SMS_FROM |

messagebird | MESSAGEBIRD_ACCESS_KEY, SMS_FROM |

sns | AWS_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, optionally AWS_SESSION_TOKEN |

plivo | PLIVO_AUTH_ID, PLIVO_AUTH_TOKEN, SMS_FROM |

webhook | SMS_WEBHOOK_URL, optionally SMS_WEBHOOK_SECRET |

```bash
SMS_PROVIDER=twilio
TWILIO_ACCOUNT_SID=AC…
TWILIO_AUTH_TOKEN=…
SMS_FROM=+15005550006
SMS_DEFAULT_COUNTRY=1
```

An unknown id is refused at send time with the list of the ones that exist, and
the Studio's readiness check reports a provider missing a credential by name
before you trust the flow:

```json
{"ok":false,"error":"twilio needs TWILIO_AUTH_TOKEN, SMS_FROM to be set"}
```

### With no provider configured

With SMS_PROVIDER unset the code is printed to the log instead of sent, exactly
as email is, so you can finish a sign-in on a laptop:

```bash
[sms] no SMS provider configured, would have sent to +14155552671
[sms]   | 418302 is your verification code. It expires in 10 minutes.
```

That happens only when SMS_PROVIDER is unset. A configured provider that
refuses a message throws.

### The webhook sender

For a carrier Baselyra does not speak, or an internal gateway. Baselyra
POSTs JSON to SMS_WEBHOOK_URL; you deliver it however you like.

```bash
SMS_PROVIDER=webhook
SMS_WEBHOOK_URL=https://sms.internal.example.com/send
SMS_WEBHOOK_SECRET=…
```

## The limits that protect your balance

DANGER:
POST /auth/v1/otp is unauthenticated and every call it accepts spends
your money. A limit on failures caps nothing here, because the expensive
outcome is a successful send. Three ceilings apply.

Limit | Default | Variable |

Per IP, per minute, on /otp | 5 | fixed |

Cooldown before the same number gets another code | 60 s | SMS_OTP_COOLDOWN |

Hard cap per number per hour | 5 | SMS_MAX_PER_NUMBER_PER_HOUR |

Failed verifications per number+IP | AUTH_MAX_ATTEMPTS (8) per AUTH_ATTEMPT_WINDOW (900 s) | |

Inside the cooldown the caller gets the same answer as a real send, minus the
message: a resend that failed loudly would tell an attacker the first one arrived.
Past the hourly cap the answer changes, because there is nothing to protect any
more — the number is already known to be in use by whoever is pressing the button:

```json
{"error":{"code":"rate_limited",
  "message":"Too many codes have been sent to this number. Try again later.","details":null}}
```

Sends are counted in auth.attempts under an sms:-prefixed identifier, so
they share the table and its sweeper with failed sign-ins without spending a user's
failure budget on messages they successfully received. The counter is incremented
before the send, because a message the provider accepted and then failed to
deliver has still been paid for.

## How the code itself is handled

- Six digits from crypto.randomInt — uniform and CSPRNG-backed, because this
string is a credential.

- Only a SHA-256 digest is stored, in auth.one_time_tokens, hashed
with the number as its scope. Without that scope, six digits could be matched
against every pending code in the table at once instead of against one account's.

- Issuing a new code retires the outstanding one, so the oldest code anybody
received does not stay live for its full lifetime.

- Comparison is timingSafeEqual over the digests. Twenty bits of entropy is
little enough that an early-returning === would leak a usable prefix.

- Redemption is single-use by construction: the UPDATE re-checks
used_at IS NULL under the row lock, so of two concurrent redemptions exactly
one gets the row.

- A failed verification is recorded against the number, and a successful one
clears the counter.

```json
{"error":{"code":"unauthorized","message":"This code is invalid, expired or already used","details":null}}
```

One message for a wrong code, an expired code, a spent code and a number with no
outstanding code at all.

## Phone users in policies

A phone-only user has email = null, so auth.email() is NULL for them.
A policy written as using (owner_email = auth.email()) silently admits nothing
for every phone user on the instance. Key on auth.uid().

```sql
-- fine for everyone
using (user_id = (select auth.uid()))

-- silently empty for phone-only users
using (owner_email = auth.email())
```

The phone number is also in the claims, so it is readable in a policy if you need
it:

```sql
using (phone = (auth.jwt() ->> 'phone'))
```

## Failure modes

What you see | Why | Fix |

400 — A valid phone number in international format is required | The number would not normalise | Send E.164, or set SMS_DEFAULT_COUNTRY |

200 and no message | Inside the 60-second cooldown, or no provider configured | Wait, or check the log for the printed code |

429 rate_limited | Past SMS_MAX_PER_NUMBER_PER_HOUR | Wait an hour, or raise it knowingly |

Codes stop arriving for one number only | The hourly cap is per number | It resets on a rolling hour |

Unknown SMS_PROVIDER "twillio" | A typo | One of twilio, vonage, messagebird, sns, plivo, webhook |

A user has two accounts for one phone | Rows written before the E.164 constraint existed | Normalise them, merge, then validate constraint users_phone_e164 |

200 and no code, for a number that never gets one | AUTH_ALLOW_SIGNUPS=false and that number has no account. The endpoint still answers identically rather than revealing which numbers exist. | Create the user with the service key first, or allow signups |

==============================================================================
# Storage
URL: https://baselyra.sarimtools.com/docs/storage.html
==============================================================================

# Storage

Files live on the local disk under STORAGE_ROOT, one directory per bucket. Their metadata lives in storage.objects, and that table has row level security — so who may read, write and delete a file is an ordinary Postgres policy, written exactly like the ones governing a table.

CAREFUL:
There is no S3 backend and no external object store In progress. Whatever host runs
the app holds the bytes, on a Docker volume by default. Back that volume up or lose
it — a database dump alone restores an instance whose storage.objects rows point
at files that are gone, which looks like a working restore right up until someone
opens an image. There is also no image transformation: files come back exactly as
they were uploaded.

## Buckets

Two are seeded on first boot:

Bucket | Public | Limit | MIME allowlist |

avatars | yes | 5 MB | image/png, image/jpeg, image/gif, image/webp, image/avif |

uploads | no | none | none |

DETAIL:
image/svg+xml is deliberately absent from the public bucket. An SVG served inline from your own origin is stored XSS.

Route | Body |

GET /storage/v1/bucket | Every bucket the caller's policies allow |

POST /storage/v1/bucket | {id, name?, public?, file_size_limit?, allowed_mime_types?} → 201 |

GET /storage/v1/bucket/:id | |

PUT /storage/v1/bucket/:id | The same fields, all optional |

DELETE /storage/v1/bucket/:id | 409 while the bucket still holds objects |

```bash
curl -s -X POST "$URL/storage/v1/bucket" \
  -H "authorization: Bearer $SERVICE_KEY" -H 'content-type: application/json' \
  -d '{"id":"invoices","public":false,"file_size_limit":10485760,
       "allowed_mime_types":["application/pdf"]}'
```

```json
{"id":"invoices","name":"invoices","public":false,"file_size_limit":10485760,
 "allowed_mime_types":["application/pdf"],"owner":null,
 "created_at":"2026-08-24T11:40:02.117Z","updated_at":"2026-08-24T11:40:02.117Z"}
```

Creating, reconfiguring and deleting buckets requires the service key or the
Studio. db/project/002_rls.sql gives storage.buckets no INSERT,
UPDATE or DELETE policy at all, and that absence is the denial — a browser
holding the anon key cannot create a bucket however it asks.

A bucket id is 1–63 characters, starts with a letter or digit, and contains only
letters, digits, dot, dash or underscore. Seven names are reserved because the
object routes claim those first path segments: public, list, move,
copy, sign, info, upload.

Deleting a non-empty bucket is refused. The ON DELETE CASCADE would have taken
every file with it and said nothing:

```json
{"error":{"code":"conflict","message":"Bucket invoices still contains objects","details":null}}
```

## Objects

Route | Does |

POST /storage/v1/object/:bucket/* | Upload. Refuses to overwrite unless x-upsert: true. |

PUT /storage/v1/object/:bucket/* | Upload, always overwriting |

GET /storage/v1/object/:bucket/* | Download, RLS checked |

DELETE /storage/v1/object/:bucket/* | Delete |

GET /storage/v1/object/public/:bucket/* | Public buckets only, no credentials |

POST /storage/v1/object/list/:bucket | {prefix?, limit?, offset?, sortBy?} |

POST /storage/v1/object/move | {bucketId, sourceKey, destinationKey} |

POST /storage/v1/object/copy | {bucketId, sourceKey, destinationKey} |

POST /storage/v1/object/sign/:bucket/* | {expiresIn} → a signed URL |

GET /storage/v1/object/sign/:bucket/*?token=… | Download by signature, no credentials |

### Uploading

curl, raw body
curl, multipart
JavaScript

```bash
curl -s -X POST "$URL/storage/v1/object/avatars/$USER_ID/me.png" \
  -H "authorization: Bearer $TOKEN" \
  -H 'content-type: image/png' \
  --data-binary @me.png
```

```bash
curl -s -X POST "$URL/storage/v1/object/avatars/$USER_ID/me.png" \
  -H "authorization: Bearer $TOKEN" \
  -F "file=@me.png;type=image/png"
```

```ts
await bl.storage.from('avatars').upload(user.id + '/me.png', file, { upsert: true });
```

```json
{"Id":"a1e3c0f2-6d5b-4d02-9d33-7b2a8f1c0e44",
 "Key":"avatars/6c1f5c62-…/me.png",
 "size":40213,
 "checksum":"9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08",
 "mimeType":"image/png"}
```

The checksum is SHA-256 of the bytes and becomes the object's ETag.

DETAIL:
The metadata row is written before the bytes, because the row is the
authorisation checkpoint — RLS accepts or refuses it — and a dangling row is
something an operator can find and delete, while an orphaned file is invisible to
every API. The bytes themselves go to a temporary sibling and are renamed into
place, so a failed or interrupted upload never leaves a partial file at the real
key.

Uploads are capped twice: by STORAGE_MAX_FILE_BYTES globally (50 MB) and by the
bucket's own file_size_limit.

```json
{"error":{"code":"payload_too_large","message":"Object exceeds the 5242880 byte limit for this bucket","details":null}}
{"error":{"code":"bad_request","message":"Bucket avatars does not accept application/pdf","details":null}}
{"error":{"code":"forbidden","message":"Not allowed to write this object","details":null}}
```

### Object keys

Up to 1024 characters. Forward slashes are folder separators — there are no real
directories in the metadata, only a prefix convention. Rejected outright:
.. segments, absolute paths, backslashes, control characters, and anything that
resolves outside its bucket directory. The check runs on every percent-decoded form
of the key and is then re-asserted against the resolved absolute path as a last
line.

### Downloading

```bash
curl -s "$URL/storage/v1/object/uploads/reports/q1.pdf" \
  -H "authorization: Bearer $TOKEN" -o q1.pdf -D -
```

```bash
HTTP/1.1 200 OK
accept-ranges: bytes
content-type: application/pdf
content-length: 184320
etag: "9f86d081884c…"
last-modified: Sun, 24 Aug 2026 11:44:10 GMT
content-disposition: inline; filename*=UTF-8''q1.pdf
cache-control: private, no-store
```

Feature | Behaviour |

If-None-Match | A matching ETag answers 304 with no body. * always matches. |

Range: bytes=0-1023 | 206 with content-range. A multi-range request, or a unit that is not bytes, is answered in full. |

An unsatisfiable range | 416 with content-range: bytes */184320 |

?download | Switches content-disposition from inline to attachment |

Cache headers | A public object is public, max-age=31536000, immutable; a private one is private, no-store, so it can never rest in a shared cache |

NOTE:
An object hidden by RLS is reported as 404, not 403 — whether a file exists is itself information the policy withheld.

### Public URLs

For a bucket with public = true:

```bash
https://api.example.com/storage/v1/object/public/avatars/6c1f5c62-…/me.png
```

No auth header, and the client constructs it without a request. The route checks
the bucket's public flag explicitly as well as reading through anon, so a
policy edit can never quietly open it. On a private bucket it is a 404:

```json
{"error":{"code":"not_found","message":"Bucket invoices is not public","details":null}}
```

DANGER:
Public means public. Any object in a public bucket is readable by URL, with no token, forever, by anyone who learns the path. A hard-to-guess key is not access control.

### Signed URLs

```bash
curl -s -X POST "$URL/storage/v1/object/sign/invoices/2026/march.pdf" \
  -H "authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{"expiresIn":900}'
```

```json
{"signedURL":"/storage/v1/object/sign/invoices/2026/march.pdf?token=1774000000.9c2f…",
 "url":"https://api.example.com/storage/v1/object/sign/invoices/2026/march.pdf?token=1774000000.9c2f…",
 "expiresAt":1774000000}
```

expiresIn is between 1 second and 7 days. The token is an HMAC-SHA256 over
bucket, key and expiry using JWT_SECRET.

Minting is itself an authorised read: only someone whose policies let them
see the object can mint a URL for it. Redemption is not — the signature is the
authorisation, which is the entire point of handing one out. Treat a signed URL as
a bearer credential and keep the expiry short.

TIP:
The client builds the absolute URL from the origin it was configured with, not from the server's idea of its own public URL. Behind a tunnel or a staging proxy the two differ, and the client's is the one the browser can reach.

### Listing

```bash
curl -s -X POST "$URL/storage/v1/object/list/uploads" \
  -H "authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{"prefix":"teams/acme/","limit":100,"sortBy":{"column":"name","order":"asc"}}'
```

```json
[{"name":"contracts","id":null,"size":null,"mime_type":null,"checksum":null,
  "metadata":null,"created_at":null,"updated_at":null},
 {"name":"logo.png","id":"3c9a…","size":8210,"mime_type":"image/png",
  "checksum":"b1946ac9…","metadata":{},"created_at":"2026-08-20T09:00:00.000Z",
  "updated_at":"2026-08-20T09:00:00.000Z"}]
```

The result is folder-grouped, like a file browser: an entry with id: null and
size: null is a folder — a distinct next path segment — and anything else is an
object. Sortable columns: name, size, created_at, updated_at,
mime_type. limit is 1–1000. The listing runs through RLS, so it shows only
what the caller may see.

### Move and copy

```bash
curl -s -X POST "$URL/storage/v1/object/move" \
  -H "authorization: Bearer $TOKEN" -H 'content-type: application/json' \
  -d '{"bucketId":"uploads","sourceKey":"draft.pdf","destinationKey":"archive/2026/draft.pdf"}'
```

```json
{"message":"Successfully moved","Key":"uploads/archive/2026/draft.pdf"}
```

Both are transactional: the row change and the filesystem operation happen inside
one transaction, so if the rename fails the row change rolls back and the object
stays exactly where it was. Both are within one bucket — there is no cross-bucket
move.

## Policies

db/project/002_rls.sql ships four permissive defaults on
storage.objects:

Policy | Rule |

objects_select | the bucket is public, or you own the object, or the owner listed you in metadata.shared_with |

objects_insert | owner = auth.uid() |

objects_update | owner = auth.uid() |

objects_delete | owner = auth.uid() |

owner is set from the caller's user id at upload. A Studio operator uploading
through the console leaves owner null — a platform account is not a row in
auth.users, and writing its id into a column that references auth.users
would violate the foreign key.

A permissive policy only ever widens access, so narrowing needs
as restrictive:

```sql
create policy user_files_own_folder on storage.objects
  as restrictive
  for all to anon, authenticated
  using (bucket_id <> 'user-files'
         or split_part(name, '/', 1) = (select auth.uid())::text)
  with check (bucket_id <> 'user-files'
              or split_part(name, '/', 1) = (select auth.uid())::text);
```

The full worked example, including why the defaults alone do not stop a user
uploading into someone else's folder, is
policy set 5.

Sharing one object with one person, without a new policy:

```sql
update storage.objects
   set metadata = jsonb_set(metadata, '{shared_with}', '["6c1f5c62-…"]'::jsonb)
 where bucket_id = 'uploads' and name = 'reports/q1.pdf';
```

## In the client

```ts
const bucket = bl.storage.from('avatars');

await bucket.upload(user.id + '/me.png', file);
await bucket.update(user.id + '/me.png', file);                     // overwrite
await bucket.list(user.id + '/', { limit: 100, sortBy: { column: 'name', order: 'asc' } });
await bucket.move('me.png', 'archive/me.png');
await bucket.copy('me.png', 'archive/me.png');
await bucket.remove([user.id + '/me.png']);

const { data: blob } = await bucket.download(user.id + '/me.png');  // data is a Blob
const { data } = await bucket.createSignedUrl(user.id + '/me.png', 3600);
bucket.getPublicUrl(user.id + '/me.png');                           // no request, public buckets only

await bl.storage.listBuckets();
await bl.storage.createBucket('invoices', { public: false });       // service key
```

Uploads accept a browser File or Blob, a Node Buffer, an
ArrayBuffer, a string or FormData. remove() stops at the first failure
and reports what was already deleted, so the caller knows the operation was
partial.

## Operational notes

- Delete removes the row first. Once the row is gone the object is
invisible to every API, so an unlink that fails leaves a harmless orphan rather
than a live row pointing at nothing. A file already missing is still a success.

- There are no quotas. Per-bucket size limits cap a single file, not the
total. Nothing stops a bucket filling the disk — watch the volume.

- Deleting a bucket directory happens after the rows are gone, and also
sweeps temporary files left by uploads that died mid-stream.

- Keep your reverse proxy's body limit above STORAGE_MAX_FILE_BYTES, or
large uploads fail at the proxy with the proxy's own error page.

## Failure modes

What you see | Why | Fix |

404 on an object you can see in psql | RLS hid it. Missing and forbidden are deliberately the same answer. | Check the policies on storage.objects |

403 forbidden — Not allowed to write this object | The insert policy refused the metadata row | Usually a key that does not start with your user id under a restrictive folder policy |

413 from your proxy, not from Baselyra | The proxy's body limit is below STORAGE_MAX_FILE_BYTES | client_max_body_size 100m, or LimitRequestBody 0 on Apache |

A .json or .txt upload arrives re-serialised | It does not — the storage plugin drops the JSON and text parsers so those bodies are stored byte for byte | Nothing |

A public URL returns 404 Bucket … is not public | The bucket's public flag is false | PUT /storage/v1/bucket/:id with {"public":true}, or use a signed URL |

A signed URL stops working early | expiresIn elapsed, or JWT_SECRET was rotated | Mint a new one |

409 deleting a bucket | It still holds objects | Empty it first — the refusal is the guard against a mistyped id |

Files gone after a restore | The storage volume was not in the backup | Back up STORAGE_ROOT alongside the database — Backups |

==============================================================================
# Realtime
URL: https://baselyra.sarimtools.com/docs/realtime.html
==============================================================================

# Realtime

One WebSocket endpoint carries three independent things: row-level change feeds from tables you opt in, ephemeral broadcast between clients, and presence. Change feeds are authorised the hard way — each changed row is re-read as the subscriber's own Postgres role before delivery, so a policy that hides the row hides the event.

## Connecting

```bash
ws://localhost:3130/realtime/v1?apikey=
wss://api.example.com/realtime/v1?apikey=
```

Browsers cannot set headers on a WebSocket handshake, which is why the query
parameter exists. Non-browser clients may send Authorization: Bearer … or
apikey: headers instead, and a header wins when both are present. A connection
with no credentials at all is legitimate and runs as anon.

DETAIL:
The connection's Postgres role is decided during the handshake and never revisited. A renewed access token only takes effect on a new socket — which is why setAuth() in the client closes and reopens, and why the automatic re-subscribe makes that invisible to your application.

## Enabling a table

Change feeds are opt-in per table, because a NOTIFY trigger on a hot table
nobody is watching is pure cost.

```sql
select baselyra.enable_realtime('public.messages');
select baselyra.disable_realtime('public.messages');
```

Or Studio → Realtime, which lists enabled tables, the live socket count and
an event inspector. This attaches an
AFTER INSERT OR UPDATE OR DELETE trigger that emits on
LISTEN baselyra_realtime. The function is security definer and executable
only by service_role, so a project user cannot attach triggers to arbitrary
tables.

CAREFUL:
A subscription to a table with no trigger succeeds and then emits nothing, forever, with no error anywhere. If a feed is silent, check this first — select * from baselyra.realtime_tables;.

## Channels

Name | Meaning |

schema:table, e.g. public:messages | A database change feed |

anything else, e.g. room:42 | Broadcast and presence only |

Only a name matching identifier:identifier produces postgres_changes.
Everything else is a pure in-memory channel. A socket may hold up to
REALTIME_MAX_CHANNELS channels, 100 by default.

## How authorisation works

[diagram: A row change fires a trigger that sends a notification carrying only the primary key; the server re-reads the row once per distinct subscriber identity under that subscriber's own role, so a policy that hides the row drops the event for that subscriber alone.]

The fan-out, and the re-check inside it. The notification carries the primary key, never the row body — the row that ships is the one that subscriber's own role was allowed to read.

There is no policy evaluation in JavaScript anywhere in the realtime path.
A subscription can never show more than a select would.

Visibility decisions are cached for three seconds per socket identity and
row, so a busy table does not become one query per subscriber per change — short
enough that a revoked grant stops mattering quickly. A re-read that fails is
treated as "not visible": leaking on error would defeat the point of re-reading at
all.

### The one exception: DELETE

DANGER:
DELETE events are not RLS-checked. A deleted row cannot be re-read — it is
gone — so a delete is delivered to every subscriber of that table whose filter
matches, carrying the old row from the trigger.

Two ways around it, if a table's contents are confidential:

- Soft delete. update … set deleted_at = now() instead of
delete. The update goes through the normal re-read, and your read policy can
exclude it.

- Tombstones. Have a trigger write the deleted row's key to an
RLS-protected table and enable realtime on that instead.

Inserts and updates have no such caveat.

## The wire protocol

If you are using the JS client, skip to the client
section — it does all of this for you.

Client → server:

```json
{"type":"subscribe","channel":"public:messages","filter":"room_id=eq.42"}
{"type":"unsubscribe","channel":"public:messages"}
{"type":"broadcast","channel":"room:42","event":"typing","payload":{"name":"Ada"}}
{"type":"presence","channel":"room:42","state":{"name":"Ada"}}
{"type":"ping"}
```

Server → client:

```json
{"type":"subscribed","channel":"public:messages"}
{"type":"postgres_changes","channel":"public:messages","event":"INSERT",
 "new":{"id":901,"room_id":42,"author":"6c1f…","body":"hello","created_at":"2026-08-24T12:00:01.004Z"},
 "old":null}
{"type":"broadcast","channel":"room:42","event":"typing","payload":{"name":"Ada"}}
{"type":"presence_state","channel":"room:42","state":{"e7c9…":{"name":"Ada"}}}
{"type":"error","message":"subscribe to room:42 before broadcasting on it"}
{"type":"pong"}
```

Re-subscribing to a channel replaces its filter rather than erroring, so a
client can change a filter without a round trip through unsubscribe. A broadcast
is not echoed to its sender, and you must be subscribed to a channel to broadcast
on it or track presence there — which is what makes
REALTIME_MAX_CHANNELS the real cap on how many channels one socket can reach.

An UPDATE carries both: new is the row as re-read under your role, and
old is the trigger's copy of the row before the change.

## Filters

```bash
room_id=eq.42
status=in.(open,pending)
status=in.(open,"needs review")
deleted_at=is.null
```

Operators: eq, neq, gt, gte, lt, lte, in,
is. One filter per subscription, on one unquoted column. is accepts
null, true, false or unknown. Inside an in list, a value
containing a comma must be double-quoted — silently splitting one would drop rows a
client expected to match.

DANGER:
A filter is a convenience, not a security boundary. It exists to spare your client rows it did not ask for. Removing it never reveals a row your policies hide.

DETAIL:
One socket holds one server-side filter per table. When two channels in the same client watch the same table with different filters, the client subscribes to everything and narrows each binding locally — the alternative is one of them silently seeing nothing.

## Large rows

pg_notify hard-fails above 8000 bytes, and that failure would abort the
writing transaction — your insert would fail because someone was listening. So a
payload over about 7.5 KB degrades instead: the notification is sent with the
primary key and truncated: true in place of the row bodies. Since the server
re-reads the row anyway for INSERT and UPDATE, subscribers still get the
full current row. A truncated DELETE is dropped, because there is nothing left to
read and nothing to send.

## Broadcast and presence

Neither touches the database, so neither is subject to RLS — a client that can
join a channel can send on it and see who else is there.

DANGER:
Do not put anything confidential in a broadcast payload or a presence state. Use a table, and let a policy decide.

Presence state is capped at 4096 bytes per member. Joining, updating or leaving
re-broadcasts presence_state to the whole channel, and a disconnect removes the
member automatically. Members are keyed by connection id, so one person with two
tabs is two members.

## Connection management

Behaviour | Detail |

Server heartbeat | A ping every REALTIME_HEARTBEAT_MS (30 s). A socket that misses two consecutive pongs is terminated — after a laptop sleeps a connection is dead but still reports itself OPEN, and without this the app receives nothing, forever, while looking perfectly healthy. |

Client heartbeat | The SDK sends its own ping every 25 s and closes the socket if the pong never comes. |

Slow consumers | A socket whose send buffer exceeds 1 MB is dropped. It is not going to catch up on a busy table, and buffering for it costs the whole process memory. |

Listener reconnect | The server's own LISTEN connection reconnects with exponential backoff and full jitter, 500 ms up to 30 s, if Postgres restarts. |

Client reconnect | Exponential backoff with jitter up to 30 s, then every channel is re-subscribed and queued frames are flushed. Up to 200 frames are queued while offline. |

Frame size | Capped at 1 MB. |

## In the client

```ts
const channel = bl.channel('room:' + roomId);

channel
  .on('postgres_changes',
      { event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.' + roomId },
      ({ new: row }) => append(row))
  .on('broadcast', { event: 'typing' }, ({ payload }) => showTyping(payload))
  .on('presence', { event: 'sync' }, ({ state }) => setHere(Object.values(state)));

const status = await channel.subscribe();   // SUBSCRIBED | CHANNEL_ERROR | TIMED_OUT | CLOSED
if (status === 'SUBSCRIBED') await channel.track({ name: 'Ada' });

await channel.send({ type: 'broadcast', event: 'typing', payload: { name: 'Ada' } });
await bl.removeChannel(channel);
```

One socket carries every channel. A channel's own name gets broadcast and
presence; each postgres_changes binding additionally joins the server channel
named schema:table.

CAREFUL:
In React, create the channel inside the effect and remove it in the cleanup. A remount otherwise stacks a second set of callbacks on the same channel object and every message arrives twice. A complete chat component is on the JavaScript client page.

```ts
// Surface protocol errors — a bad filter, a channel limit — instead of losing them.
const off = bl.realtime.onError((message) => console.warn('realtime:', message));
```

## Behind a reverse proxy

DANGER:
This is the single most common self-hosting failure for a product of this shape:
every other route works, realtime silently does nothing, and there is no error
anywhere. It is always the same cause — the proxy did not forward the
Upgrade handshake, so the WebSocket was proxied as plain HTTP.

nginx
Apache

```bash
map $http_upgrade $connection_upgrade {
    default upgrade;
    ''      close;
}

location / {
    proxy_pass http://127.0.0.1:3130;
    proxy_http_version 1.1;                    # 1.0 cannot upgrade
    proxy_set_header Upgrade    $http_upgrade;
    proxy_set_header Connection $connection_upgrade;
    proxy_read_timeout 300s;                   # or idle sockets are cut
    proxy_buffering off;
}
```

```bash
RewriteEngine On
RewriteCond %{HTTP:Upgrade} =websocket [NC]
RewriteRule ^/?(.*) ws://127.0.0.1:3130/$1 [P,L]

ProxyPass        / http://127.0.0.1:3130/
ProxyPassReverse / http://127.0.0.1:3130/
ProxyTimeout 300
```

The rewrite must come before the catch-all ProxyPass, and the modules
must be loaded — nginx builds proxy_wstunnel in; Apache needs
a2enmod proxy_wstunnel rewrite. Both full vhosts are in deploy/.

Check it from the command line. A 101 is the whole test:

```bash
curl -i -N \
  -H 'Connection: Upgrade' -H 'Upgrade: websocket' \
  -H 'Sec-WebSocket-Version: 13' -H 'Sec-WebSocket-Key: dGhlIHNhbXBsZSBub25jZQ==' \
  "https://api.example.com/realtime/v1?apikey=$ANON_KEY"
```

```bash
HTTP/1.1 101 Switching Protocols
upgrade: websocket
connection: Upgrade
```

HTTP/1.1 101 Switching Protocols means the path is clear. A 200 with HTML
is the proxy answering instead of upgrading.

NOTE:
Cloudflare proxies WebSockets on all plans, but its 100-second idle timeout is shorter than a long heartbeat interval would be. Leave the heartbeat at 30 s and it stays open.

## Configuration

Variable | Default | Effect |

REALTIME_MAX_CHANNELS | 100 | Channels one socket may hold |

REALTIME_HEARTBEAT_MS | 30000 | Server ping interval |

## Failure modes

What you see | Why | Fix |

subscribed, then nothing, ever | The table has no NOTIFY trigger | select baselyra.enable_realtime('public.your_table') |

Works locally, dead in production | The proxy is not forwarding Upgrade | The curl test above; fix the vhost |

Events for some rows only | RLS hid the rest, or your filter excluded them | Test the policy with the impersonation block |

The socket closes with 1008 | The token was rejected: expired, or JWT_SECRET changed | Refresh the session; the SDK reconnects with the new token |

Events stop after a few minutes | A proxy idle timeout below the heartbeat interval | proxy_read_timeout 300s / ProxyTimeout 300 |

Every message arrives twice in React | The channel outlived a remount | Create it inside the effect, remove it in the cleanup |

A subscriber sees deletes it should not | Deletes are not RLS-checked | Soft-delete instead |

a socket may hold at most 100 channels | A channel per row, or a leak | One channel per table with a filter, or raise REALTIME_MAX_CHANNELS |

The whole feed goes quiet after a database restart | The listener reconnects with backoff — up to 30 s | Wait; realtime: listening for postgres changes appears in the log when it is back |

==============================================================================
# AI assistant
URL: https://baselyra.sarimtools.com/docs/ai.html
==============================================================================

# AI assistant

Baselyra can talk to DeepSeek, or to anything that speaks the same /chat/completions shape. Two very different trust levels live behind /ai/v1: operator tooling that sees your real schema, and a plain relay any signed-in user may call. All of it is optional — without a key every route answers 503 and nothing else changes.

## Enabling it

```bash
DEEPSEEK_API_KEY=sk-…
DEEPSEEK_BASE_URL=https://api.deepseek.com
DEEPSEEK_MODEL=deepseek-chat
DEEPSEEK_MAX_TOKENS=2048
```

```bash
curl -s "$URL/ai/v1/status"
```

```json
{"enabled":true,"model":"deepseek-chat"}
```

The key stays on the server. It is never sent to a browser and never appears in a
response. DEEPSEEK_BASE_URL is any OpenAI-compatible /chat/completions
endpoint, so a local Ollama or vLLM behind an OpenAI shim works if you point it
there and set DEEPSEEK_MODEL to match.

NOTE:
GET /ai/v1/status is public and needs no credential, so a frontend can hide its AI affordances rather than render a control that would answer 503. That is exactly what the Studio does.

## The routes

Route | Body | Who |

GET /ai/v1/status | — | Anyone |

POST /ai/v1/sql | {prompt} | A Studio token or the service key |

POST /ai/v1/explain | {sql} | A Studio token or the service key |

POST /ai/v1/ask | {question} | A Studio token or the service key |

POST /ai/v1/chat | {messages, system?, stream?} | Any signed-in application user |

Bodies are capped at 256 KiB, a prompt at 8000 characters, and a conversation at
50 messages.

### POST /ai/v1/sql

Generates a statement against your real schema. It is not run. It comes back
into the SQL editor for you to read and execute.

```bash
curl -s -X POST "$URL/ai/v1/sql" \
  -H "authorization: Bearer $ADMIN_TOKEN" -H 'content-type: application/json' \
  -d '{"prompt":"top 10 authors by published article count in the last 30 days"}'
```

```json
{"sql":"SELECT u.id, u.email, count(*) AS articles FROM public.articles a JOIN auth.users u ON u.id = a.author WHERE a.published_at > now() - interval '30 days' GROUP BY 1, 2 ORDER BY articles DESC LIMIT 10",
 "explanation":"Counts articles published in the last month per author and returns the ten highest."}
```

The schema is rendered into the system prompt and the model is told to use only
names that exist, to qualify every table, and to emit one statement with no
trailing semicolon. A model that answers in prose instead of SQL gets its prose
back as explanation with an empty sql — a wrong-shaped answer is still an
answer, and showing it beats a 502.

### POST /ai/v1/explain

```bash
curl -s -X POST "$URL/ai/v1/explain" \
  -H "authorization: Bearer $ADMIN_TOKEN" -H 'content-type: application/json' \
  -d '{"sql":"delete from auth.sessions where expires_at Two short paragraphs at most: what the statement returns or changes, which tables
it touches, and anything expensive or destructive.

### POST /ai/v1/ask

The only route that runs model-written SQL. It drafts a query, executes it, and
answers the question from the rows.

```bash
curl -s -X POST "$URL/ai/v1/ask" \
-H "authorization: Bearer $ADMIN_TOKEN" -H 'content-type: application/json' \
-d '{"question":"how many users signed up last week?"}'
```

```json
{"answer":"412 users signed up between 14 and 20 August.",
"sql":"SELECT count(*) FROM auth.users WHERE created_at >= date_trunc('week', now() - interval '1 week') AND created_at If the model answers in prose rather than SQL — usually because the question was
not about the data — the prose comes back as answer and no query runs.

How the query is contained. Four independent guards, because one
hallucinated DROP TABLE is enough:

- SET TRANSACTION READ ONLY
Every write and every DDL fails inside Postgres. Nothing relies on the server recognising a dangerous statement.

- A local statement_timeout of 10 seconds
A cartesian join cannot pin a core.

- The statement is wrapped
select * from (<statement>) as baselyra_ask limit 200. That bounds memory and, as a bonus, turns a multi-statement reply into a syntax error rather than a second query.

- The transaction is rolled back either way
Nothing survives, even in principle.

DANGER:
The query runs as the owner, not as anon — this is operator tooling and
it reads every schema. The rows are then sent back to the model to phrase the
answer, so treat /ask as sending a bounded sample of your data to DeepSeek.
That is inherent to the feature. It is also the one path where SQL nobody wrote
reaches the database on the server's own connection: a read-only transaction stops
writes, but it does not stop pg_read_file() if DATABASE_URL is a superuser.
If that trade is wrong for you, leave DEEPSEEK_API_KEY unset.

### POST /ai/v1/chat

A plain relay for your application's users, so an app built on Baselyra can offer
its own assistant without standing up a second backend just to hold an API key. It
requires a signed-in user token, not the anon key.

```ts
const { data, error } = await bl.ai.chat(
  [{ role: 'user', content: 'Summarise this order for me: ' + JSON.stringify(order) }],
  { system: 'You are a helpful support assistant for Acme. Be brief.' },
);
if (error) return show(error.message);
console.log(data.content);
```

Streaming, over server-sent events:

```bash
curl -N -X POST "$URL/ai/v1/chat" \
  -H "authorization: Bearer $USER_TOKEN" -H 'content-type: application/json' \
  -d '{"messages":[{"role":"user","content":"hi"}],"stream":true}'
```

```bash
data: {"delta":"Hel"}
data: {"delta":"lo!"}
data: [DONE]
```

An error inside a stream arrives as a data: frame carrying the usual error
object, because the status line has already been sent:

```bash
data: {"error":{"code":"ai_upstream","message":"DeepSeek returned 500","details":null}}
```

DANGER:
This route has no memory and no tools. It does not see your schema, cannot
query the database, and does not know who the user is beyond the fact that they are
signed in. Conversation history is whatever you send in messages. Do not build
an authorisation decision on anything it says, and do not put data in the prompt
that the signed-in user is not already allowed to read — you are the one choosing
what goes in.

## Cost control

Control | Value |

/ai/v1/chat rate limit | 20 requests per minute per user — keyed on the user id, not the IP, so a mobile carrier NAT does not share one budget across thousands of people |

DEEPSEEK_MAX_TOKENS | A hard ceiling per completion. A caller may ask for fewer, never more. |

Client disconnect | Aborts the upstream call, so you stop paying for tokens the moment the user closes the tab |

Upstream timeout | 60 seconds, with one retry, and only for 429 and 5xx |

There is no per-user or per-instance spend cap beyond these. If you expose
/ai/v1/chat to the public, watch your DeepSeek dashboard.

## Privacy

Be clear-eyed about what leaves the machine.

Route | Sent upstream |

/sql | Your schema — table, column and type names. No row data. |

/explain | The statement you pasted. No row data. |

/ask | Your schema, the question, and up to 200 result rows. |

/chat | Exactly the messages your app sends. |

Nothing is sent when DEEPSEEK_API_KEY is unset, which is the default. The AI
routes themselves are not audited; a query you then run in the SQL editor is, like
any other statement.

## Errors

Status | code | Meaning |

503 | ai_disabled | DEEPSEEK_API_KEY is not set |

400 | bad_request | A malformed body — a missing prompt, a message over 8000 characters, an unknown role |

401 | unauthorized | /chat without a signed-in user |

403 | forbidden | /sql, /explain or /ask without a Studio token or the service key |

429 | ai_rate_limited | DeepSeek's own rate limit, or Baselyra's per-user cap |

502 | ai_unauthorized | DeepSeek rejected the key |

502 | ai_upstream | DeepSeek returned an unexpected status |

504 | ai_timeout | No response within 60 seconds |

504 | ai_unreachable | A network failure reaching DeepSeek |

## In the Studio

Studio → AI carries the ask-your-database panel and SQL generation; the SQL
editor offers "explain this" on the current statement. Every panel disappears when
/ai/v1/status reports enabled: false, rather than showing a control that would
return 503.

## Failure modes

What you see | Why | Fix |

503 ai_disabled | No key in the running process | Set DEEPSEEK_API_KEY and docker compose up -d |

A stream that arrives all at once at the end | A proxy is buffering | proxy_buffering off on nginx. The app already sends X-Accel-Buffering: no, but an explicit proxy_buffering on in your config wins. |

/ask returns an answer and no sql | The model answered in prose | Ask a question about the data, or use /sql |

/ask errors with a SQLSTATE and a sql in details | The generated statement did not run — usually an invented column | Read the statement; it is in the error |

Generated SQL names a table that does not exist | The schema in the prompt is a snapshot; a very new table may be missing | Re-run after the catalog refreshes |

/chat is 401 with a working anon key | The relay needs a user token, not the project key | Sign the user in first |

==============================================================================
# Importing
URL: https://baselyra.sarimtools.com/docs/importing.html
==============================================================================

# Importing

Studio → Import connects to another backend, shows you what is in it, and copies what you select while streaming progress. The headline is passwords: Supabase and Postgres bcrypt hashes survive the move, so your users do not have to reset anything. Every other source is honest about what it cannot carry.

## What each source brings

Source | Tables | Users | Passwords | Files | Policies |

Supabase | with types, keys and indexes | auth.users | preserved bcrypt | with project URL + service key | translated where possible |

Postgres | with types, keys and indexes | if auth.users exists | preserved if bcrypt | n/a | translated where possible |

Appwrite | collections → tables | yes | lost argon2 | yes | none to translate |

Firebase | collections → tables, sampled | yes | lost keyed scrypt | with storageBucket | none to translate |

SQL dump | CREATE TABLE + COPY/INSERT | if auth.users is in the dump | preserved if bcrypt | n/a | none to translate |

## Passwords, exactly

This is the part people get burned by, so here it is per source.

### Supabase and Postgres — preserved

Supabase stores a bcrypt hash in auth.users.encrypted_password. Baselyra copies
it verbatim behind a bcrypt$ marker. When such a user signs in,
verification recognises the marker and asks Postgres to check it —
select $1 = crypt($2, $1), using pgcrypto, which is already installed. bcrypt is
not reimplemented anywhere in this codebase. On the first successful
sign-in the row is quietly re-hashed to scrypt, so the bcrypt rows retire
themselves as your users come back.

Nobody has to reset a password. Nobody is emailed. The migration is invisible.

### Appwrite and Firebase — lost

Appwrite's Users API never returns a password hash, and it hashes with
argon2 by default. Firebase uses a modified scrypt keyed with a per-project signer
key held by Google — even with the hash you cannot verify a password without that
key.

Imported accounts are created with an unusable password marker. They exist, their
email and metadata are intact, and they cannot sign in until they go through
password recovery. The inspect step says so in warnings before you start, and
the run repeats it. Plan for it:

```bash
# after the import, for each imported address
curl -s -X POST "$URL/auth/v1/recover" -H 'content-type: application/json' \
  -d '{"email":"'"$EMAIL"'"}'
```

DETAIL:
"Unusable" means encrypted_password is set to a marker that parses as no scheme at all. Verification rejects it after burning an equivalent scrypt derivation, so the timing does not distinguish those accounts from any other, and recovery is the only way in.

### SQL dump — preserved if bcrypt

If the dump contains auth.users, each row's hash is classified.
$2a$/$2b$ values are bcrypt and are preserved exactly as above.
$argon2… and anything unrecognised become unusable passwords, and the inspect
step warns.

## Source configuration

Every route takes { source, config }.

supabase / postgres
appwrite
firebase
sqldump

```json
{"source":"supabase",
 "config":{
   "connectionString":"postgresql://postgres:PASSWORD@db.abcdefgh.supabase.co:5432/postgres",
   "restUrl":"https://abcdefgh.supabase.co",
   "serviceKey":"eyJ…"}}
```

connectionString (aliases url, databaseUrl) is required and must start
with postgres:// or postgresql://. Use the direct connection string, not
the transaction-mode pooler — a server-side cursor needs a session.

restUrl and serviceKey are optional and only used to download storage
objects; without them, bucket and object metadata is imported but the file bytes
are not, and the run says so. sslmode=require in the URL relaxes certificate
verification, which is what a self-hosted Supabase behind its own certificate needs;
verify-ca and verify-full are left strict.

```json
{"source":"appwrite",
 "config":{
   "endpoint":"https://cloud.appwrite.io/v1",
   "projectId":"…",
   "apiKey":"…"}}
```

The API key needs read scopes for databases, users and storage.
Collections become tables, attributes become columns, and the three Appwrite system
fields become id, created_at and updated_at. An attribute type Baselyra
does not recognise is imported as text rather than dropped.

```json
{"source":"firebase",
 "config":{
   "projectId":"my-project",
   "databaseId":"(default)",
   "accessToken":"ya29.…",
   "storageBucket":"my-project.appspot.com"}}
```

```bash
gcloud config set project my-project
gcloud auth print-access-token
```

Firestore has no schema, so column types are inferred from a sample of each
collection. A field that is an integer in half the documents and a string in the
other half is widened to jsonb; maps, arrays and geopoints are jsonb too. A
field that appears only outside the sample will not have a column. Check the
inspect result before you run, and expect to tidy types afterwards. Without
storageBucket, Cloud Storage is not part of the import.

```json
{"source":"sqldump","config":{"sql":""}}
```

Plain-text pg_dump output only. A custom-format archive — it starts with
PGDMP — is refused with an explanation: re-export with
pg_dump --format=plain, or restore it somewhere and import from the database
directly. Both COPY … FROM stdin blocks and INSERT statements are read.

## The API

Route | Does |

POST /admin/v1/import/test | {source, config} → {ok, detail, version?} |

POST /admin/v1/import/inspect | {source, config} → the inspect result |

POST /admin/v1/import/run | {source, config, selection} → text/event-stream |

GET /admin/v1/import/history | ?limit= → past runs |

All four require a Studio token or the service key, and all four require the
import.run capability — which owner and admin hold and viewer does
not. A connection string is a credential, so even reading the history is part of
running an import rather than a read.

Start with test. It is cheap and reports the source's version:

```bash
curl -s -X POST "$URL/admin/v1/import/test" \
  -H "authorization: Bearer $ADMIN_TOKEN" -H 'content-type: application/json' \
  -d '{"source":"supabase","config":{"connectionString":"postgresql://…"}}'
```

```json
{"ok":true,"detail":"connected","version":"PostgreSQL 15.6"}
```

Then inspect:

```json
{"tables":[{"key":"public.posts","schema":"public","name":"posts","rowEstimate":48213,
            "columns":[{"name":"id","type":"bigint","nullable":false,"default":null,"autoIncrement":true}],
            "primaryKey":["id"],"foreignKeys":[],"indexes":[],"hasRls":true,
            "policies":["posts_read_published"]}],
 "users":{"count":1204,"hashAlgorithm":"bcrypt"},
 "buckets":[{"id":"avatars","public":true,"objectCount":900,"totalBytes":41231884}],
 "warnings":["public.audit_log has no primary key and will be imported without one"]}
```

selection names exactly what to copy. tables holds the key values from
the inspect result, and at least one of tables, users or buckets must be
non-empty.

```json
{"mode":"create",
 "schema":"public",
 "tables":["public.posts","public.comments"],
 "users":true,
 "buckets":["avatars"],
 "objects":true}
```

mode | Behaviour |

create (default) | Refuses to overwrite a target table that already holds rows, naming the table in the error |

replace | Truncates the target first |

DANGER:
The refusal is the guard between "import into an empty database" and "silently destroy the data already here". Prefer create and deselect what you did not mean to import.

## How a run behaves

[diagram: The import pipeline: a source driver is inspected, then the run creates every table, copies rows one table per transaction, adds constraints and indexes at the end, copies users and finally streams objects to disk, emitting server-sent progress events throughout.]

The pipeline. Schema first, then rows, then constraints — creating every table before any data means a child can load before its parent, and adding keys at the end validates each once over a finished table instead of row by row.

- One transaction per table. A cancellation or a bad row leaves that
table empty rather than half-filled. Tables already finished stay finished.

- Streamed, never buffered. Postgres sources read through a server-side
cursor and insert in batches of 1000 rows — fewer if the table is wide enough that
1000 rows would exceed the protocol's 65535 bind-parameter limit. A ten-million-row
table does not materialise in memory. The bulk copy also raises
statement_timeout to unlimited for the duration of a table, because a bulk
load is meant to take longer than an API request.

- Files three at a time, streamed source → disk. A file that fails is
recorded in failedObjects and the run continues; one unreadable object does
not end a migration of ten thousand.

- Closing the connection cancels. The in-flight table's transaction rolls
back and nothing further starts. The run is recorded as cancelled.

- Users are inserted on conflict do nothing. An account that already
exists here is left exactly as it is.

- Non-uuid user ids get new ones. auth.users.id is a uuid; a source
that numbers users differently gets a fresh id, with the original kept in
app_metadata.provider_id so the two can still be matched up.

- Nothing logs a secret. Connection strings, API keys and service keys
are redacted from the audit entry, the control.import_runs row, the progress
stream and every error message — both by key name and by pattern-matching
credential shapes inside free text, because a driver error quoting the connection
string it failed on is the usual way one escapes.

## Progress

```bash
data: {"phase":"connect","item":"source","done":0,"total":0,"rowsCopied":0,"warnings":[]}
data: {"phase":"schema","item":"posts","done":1,"total":4,"rowsCopied":0,"warnings":[]}
data: {"phase":"rows","item":"posts","done":1,"total":4,"rowsCopied":48000,"warnings":[]}
: heartbeat
data: {"phase":"constraints","item":"posts","done":1,"total":4,"rowsCopied":91234,"warnings":[]}
data: {"phase":"users","item":"auth.users","done":1204,"total":1204,"rowsCopied":91234,"warnings":[]}
data: {"phase":"done","item":"succeeded","done":1,"total":1,"rowsCopied":91234,
       "summary":{"tables":[{"name":"public.posts","rows":48000}],"rowsCopied":91234,
                  "users":1204,"objects":900,"failedObjects":[],"warnings":[]}}
```

Phases run in this order: connect, schema, rows, constraints,
users, objects, then done — or error. A comment heartbeat every 15
seconds keeps the pipe warm, because a proxy with a 300-second timeout would
otherwise cut a long table copy.

Every run also writes a control.import_runs row, readable through
GET /admin/v1/import/history and shown in the Studio.

## Imported tables arrive with RLS on

ALTER DEFAULT PRIVILEGES grants DML on every new table in public to
anon and authenticated, so a table is world-readable from the moment
CREATE TABLE returns. The importer therefore enables row level security on each
target table before a single row is inserted, and never turns it off.

For Postgres and Supabase sources it then tries to recreate the source's
policies:

Case | Outcome |

A policy that translates | Created as it was |

A policy naming a role that does not exist here | Skipped, with a warning |

A policy that fails to create | Skipped, with a warning |

The common failure is a helper the source had and this instance does not.
auth.is_admin() is the usual one; replace it with
(auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'.

DANGER:
If nothing translated, the table ends with RLS on and no policy at all, which
denies everyone except the service key, and the summary says so per table. That is
the deliberate failure direction: a table nobody can read is a bug you find in a
minute, and a table everybody can write is a breach you find later. Read every
warnings entry after a run and write the missing policies before you point
traffic at the new instance.

## What does not come across

- Triggers, functions, views, extensions, and sequences' current values.
Tables, columns, defaults, primary keys, foreign keys and indexes only.

- Supabase edge functions, storage image transformations, OAuth identities,
webhooks and cron jobs. The first three do not exist here at all.

- Appwrite functions, teams and messaging.

- Firebase security rules, Cloud Functions and the Realtime Database —
Firestore only.

## After the import

- Check the policies on every imported table
RLS is already on; what may be missing are the policies. Run the audit query — a table with RLS on and zero policies is invisible to your app until you write one. Nothing else matters until this is done.

- Read warnings and failedObjects in the summary

- If passwords were lost, send recovery emails
Before you switch traffic over, not after.

- Re-check inferred types on a Firestore import

- Enable realtime on the tables that need it

```sql
select baselyra.enable_realtime('public.messages');
```

- Re-point your client at the new URL and anon key

## Failure modes

What you see | Why | Fix |

Could not read the source: … | Credentials or the network | Run test first — it is cheap and reports the source's version |

A table is missing from inspect | Internal schemas are excluded, and views are not imported | Materialise the view, or import the underlying tables |

… already holds N rows | mode is create and the target is not empty | Deselect that table, or switch to replace |

The stream stops with no done event | A proxy timeout below the 15-second heartbeat | Raise proxy_read_timeout / ProxyTimeout — the shipped vhosts use 300s |

Rows copied but REST answers 404 for the table | The catalog cache. A run invalidates it on success. | Give it a minute |

Imported users cannot sign in | Their hashes could not be carried | Password recovery — the inspect warnings said so before the run |

A pooler connection string times out mid-copy | A server-side cursor needs a session, and the transaction-mode pooler does not give it one | Use the direct connection string |

403 on /admin/v1/import/* | The Studio account is a viewer | Import needs import.run, which owner and admin have |

==============================================================================
# Client libraries
URL: https://baselyra.sarimtools.com/docs/sdk.html
==============================================================================

# Client libraries

Baselyra ships one official client, for JavaScript and TypeScript. Everything else is plain HTTP with JSON and three headers, which is why the Dart, PHP and Python pages here are complete hand-rolled clients rather than a promise of an SDK that does not exist.

## What exists

Language | Status |

JavaScript / TypeScript | Official — @baselyra/client, zero dependencies. Browsers, Node 22, Deno, Bun, React Native. |

Dart / Flutter | A documented, complete plain-HTTP client to copy into your project |

PHP | A documented, complete plain-HTTP client |

Python | A documented, complete plain-HTTP client |

Anything else | Three headers and JSON. See below. |

NOTE:
The VS Code extension generates a Database type for the JS client from your live schema. There is no code generation for the other languages.

## Which key goes where

Key | Role | Where it may appear |

anon key | anon | Anywhere. A frontend bundle, a mobile binary, a public repository. It is meant to be public — row level security is what protects the data. |

service key | service_role, BYPASSRLS | Server-side only. Never in a bundle, never in a NEXT_PUBLIC_* / VITE_* / EXPO_PUBLIC_* variable, never in a mobile app. |

DANGER:
The service key reads and writes every row in every table, ignoring every policy. Treat it like a database superuser password, because that is what it is. If one leaks: rotate JWT_SECRET, restart, and re-issue the anon key to your clients. There is no per-key revocation list.

## The HTTP contract

Three headers cover every language.

```bash
apikey:                           # which Postgres role the request runs as
authorization: Bearer    # who the user is; wins over apikey
content-type: application/json
```

You want | Do |

Sign in | POST /auth/v1/token?grant_type=password with {email, password} |

Renew a session | POST /auth/v1/token?grant_type=refresh_token with {refresh_token} |

Read rows | GET /rest/v1/<table>?<filters> |

Write rows | POST / PATCH / DELETE /rest/v1/<table> |

Call a function | POST /rest/v1/rpc/<name> with the named arguments |

Upload a file | POST /storage/v1/object/<bucket>/<key> |

Watch changes | WebSocket /realtime/v1?apikey=<token> |

### The error envelope

Every failure, from every prefix, has the same shape. Write one decoder and reuse
it.

```json
{"error":{"code":"unique_violation",
  "message":"duplicate key value violates unique constraint \"articles_slug_key\"",
  "details":{"detail":"Key (slug)=(hello) already exists.","hint":null,"sqlstate":"23505"}}}
```

Field | Meaning |

code | A stable string — bad_request, unauthorized, unique_violation, statement_timeout. Branch on this, not on the message. |

message | Human-readable. Safe to show a developer; think before showing a user. |

details | null, or an object. For a Postgres error it carries detail, hint and sqlstate. |

CAREFUL:
A row hidden by row level security is not an error. It is a 200 with an empty array, or zero rows affected. Client code that treats "no rows" as a failure will be wrong in both directions.

### Things every client should do

- Store the rotated refresh token. Every refresh consumes its token and
returns a new one. Replaying a spent token revokes the entire chain, and the user
is signed out. This is the single most common bug in a hand-rolled client.

- Refresh before the access token expires, not after a 401. An hour
is the default lifetime, and a failure-driven refresh means every call site has to
know how to replay itself.

- Send a filter on every update and delete. The server refuses an
unfiltered one, and that refusal is a feature — but a client that constructs the
URL by hand should assert it too.

- Read content-range for the total when you asked for
Prefer: count=exact.

- Percent-encode filter values. A literal + in a query string
decodes to a space.

## The JavaScript client

```bash
npm install @baselyra/client
```

```ts
import { createClient } from '@baselyra/client';

const bl = createClient('https://api.example.com', ANON_KEY);

const { data, error } = await bl.from('posts').select('id,title').eq('published', true);
```

Zero dependencies: fetch and WebSocket are the only platform features it
uses, so the same build runs in browsers, Node 22, Deno, Bun and React Native with
no polyfill and no bundler shim. The whole reference is on
the JavaScript client page.

## Framework guides

JavaScript / TypeScriptThe official client, method by method.
Next.jsApp Router, server components, route handlers, the three server clients.
ReactVite setup, an auth hook, a live list, and the remount trap.
Flutter / DartA complete client over package:http and web_socket_channel.
PHPA complete client over ext-curl, with no dependencies.
PythonA complete client over requests, with a paginating generator.

## Anything else

Sign in, read, write, subscribe. That is the whole protocol.

```bash
# 1. sign in
curl -s -X POST "$URL/auth/v1/token?grant_type=password" \
  -H 'content-type: application/json' \
  -d '{"email":"ada@example.com","password":"…"}'

# 2. read as that user — RLS decides what comes back
curl -s "$URL/rest/v1/notes?select=id,title&order=created_at.desc" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $ACCESS_TOKEN"

# 3. write
curl -s -X POST "$URL/rest/v1/notes" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $ACCESS_TOKEN" \
  -H 'content-type: application/json' -H 'Prefer: return=representation' \
  -d '{"title":"From anywhere"}'

# 4. watch
websocat "wss://api.example.com/realtime/v1?apikey=$ACCESS_TOKEN"
{"type":"subscribe","channel":"public:notes"}
```

The full query language is on Filtering and paging,
sessions on Auth, files on Storage
and the socket protocol on Realtime.

==============================================================================
# JavaScript client
URL: https://baselyra.sarimtools.com/docs/javascript.html
==============================================================================

# JavaScript client

The official client is five source files and no dependencies. Every call resolves to { data, error } — nothing throws — and one WebSocket carries every channel. This page is the whole surface.

## Install

```bash
npm install @baselyra/client
```

| |

Dependencies | None |

Runtimes | Browsers, Node 18+ (22 for the built-in WebSocket), Deno, Bun, React Native |

Module format | ESM, with types |

Licence | Apache-2.0 |

## createClient

```ts
import { createClient } from '@baselyra/client';

export const bl = createClient(
  import.meta.env.VITE_BASELYRA_URL,
  import.meta.env.VITE_BASELYRA_ANON_KEY,
);
```

Hold one instance for the lifetime of the app: it owns the session, the
refresh timer and the single WebSocket every channel shares. It throws — the only
thing here that does — when the URL or the key is missing, because that is a
programming error rather than a runtime condition.

```ts
createClient(url, anonKey, {
  auth: {
    persistSession: true,             // default
    autoRefreshToken: true,           // default
    storageKey: 'baselyra.auth.session',
    storage: myStorageAdapter,        // getItem / setItem / removeItem, synchronous
  },
  global: {
    headers: { 'x-client-info': 'acme-web/1.4.0' },
    fetch: myFetch,                   // a Node agent, a test double, a retrying wrapper
  },
  realtime: {
    enabled: true,                    // false never opens the socket
    heartbeatIntervalMs: 25_000,
    maxReconnectDelayMs: 30_000,
    WebSocket: MyWebSocket,
  },
});
```

DETAIL:
Session storage is deliberately synchronous, so a request made on the first render always carries the right token. It falls back to memory rather than throwing when localStorage is missing (SSR, React Native) or present but refusing writes (a Safari private window, some corporate policies).

## Nothing throws

```ts
const { data, error, count, status } = await bl.from('posts').select('*');
if (error) {
  if (error.isNetworkError) return showOffline();   // status === 0
  return showMessage(error.message);                // error.code, error.status, error.details
}
render(data);
```

An HTTP error is not a rejected promise, and neither is a dead network. That is
deliberate: a rejecting data call forces try/catch around every line that
touches the database, and the one you forget in a React event handler becomes an
unhandled rejection instead of a message next to the form field.

Field | Meaning |

data | The parsed body, or null when the call failed or returned nothing |

error | A BaselyraError with code, status, details and isNetworkError |

count | The total from content-range, only when you asked for it |

status / statusText | The HTTP status, or 0 for a network failure |

error.code | When |

network_error | The request never reached the server — DNS, CORS, offline |

aborted | An AbortSignal fired |

no_session | refreshSession() with nobody signed in |

too_many_rows | maybeSingle() and more than one row came back |

anything else | The server's own code, verbatim |

Two things still throw, both programming errors: createClient without a URL or
key, and a filter on an rpc() result the server cannot honour.

## Queries

The builder is a thenable, so a chain runs when you await it — there is no
terminal .execute() to remember, and awaiting the same chain twice sends one
request.

```ts
const { data, error, count } = await bl
  .from('posts')
  .select('id,title,tags,author:users(id,name)', { count: 'exact' })
  .eq('published', true)
  .neq('kind', 'draft')
  .gt('score', 10).gte('score', 10).lt('score', 99).lte('score', 99)
  .like('title', '*sql*').ilike('title', '*SQL*')     // * is the wildcard
  .is('deleted_at', null)                             // the only correct null test
  .in('id', [1, 2, 3])
  .contains('tags', ['sql'])                          // @>
  .containedBy('tags', ['sql', 'db'])                 //

Method | Does |

.select(columns, { count, head }) | Choose columns. After a write it asks for the affected rows back. head: true sends HEAD, so a count crosses the wire without rows. |

.insert(values, { count }) | One row or an array. Nothing comes back unless .select() is chained. |

.upsert(values, { onConflict, ignoreDuplicates, count }) | Insert, or update the row holding the conflicting key |

.update(values, { count }) | Needs a filter, or .unsafeMutation() |

.delete({ count }) | Needs a filter, or .unsafeMutation() |

.filter(column, op, value) | Any operator the server knows, including ones without a named method |

.single() | Expect exactly one row; data is the row. 0 or 2+ is a 406. |

.maybeSingle() | Expect zero or one; data is the row or null |

.csv() | Render the rows as RFC 4180 CSV, client-side |

.abortSignal(signal) | Cancel; the result is an error with code aborted |

.build() | The request this chain would send, without sending it |

```ts
await bl.from('posts').insert({ title: 'Hello' }).select().single();
await bl.from('posts').upsert(rows, { onConflict: 'slug' });
await bl.from('posts').update({ title: 'x' }).eq('id', 1);
await bl.from('posts').delete().eq('id', 1);
await bl.from('posts').delete().unsafeMutation(); // every row. Deliberate.
await bl.rpc('search_posts', { term: 'sql' });
```

CAREFUL:
rpc() results cannot be narrowed with filters, ordering or paging — narrow inside the SQL function instead. The client throws rather than silently dropping the chain.

### Typed rows

```ts
import type { Database } from './database.types';

const bl = createClient(url, ANON_KEY);
const { data } = await bl.from('posts').select('*'); // data: Post[] | null
```

The type only needs the shape
{ public: { Tables: { posts: { Row: Post } } } }. Without it, rows are open
records and everything still works. The
VS Code extension writes that file from
your live schema.

## Auth

```ts
await bl.auth.signUp({ email, password, data: { name: 'Ada' } });
await bl.auth.signInWithPassword({ email, password });
await bl.auth.signInWithOtp({ email, type: 'magiclink' }); // or type: 'otp'
await bl.auth.verifyOtp({ email, token: '123456', type: 'otp' });
await bl.auth.resetPasswordForEmail(email);
await bl.auth.resend({ type: 'confirmation', email });
await bl.auth.updateUser({ password: 'a-new-one', data: { theme: 'dark' } });
await bl.auth.refreshSession();
await bl.auth.signOut({ scope: 'global' });

const { data: { session } } = await bl.auth.getSession(); // refreshes first if expired
const { data: { user } } = await bl.auth.getUser(); // asks the server
bl.auth.accessToken; // the raw token, no round trip
bl.auth.setSession(session); // adopt one from elsewhere
bl.auth.stopAutoRefresh(); // let a Node process exit
```

```ts
const { data: { subscription } } = bl.auth.onAuthStateChange((event, session) => {
setUser(session?.user ?? null);
});
// SIGNED_IN | SIGNED_OUT | TOKEN_REFRESHED | USER_UPDATED
return () => subscription.unsubscribe();
```

The access token is renewed a minute before it expires, and the renewed token is
re-attached to the realtime socket automatically. A refresh that fails with a
4xx clears the session and emits SIGNED_OUT — the token is gone for good,
rotated or revoked. Anything else is treated as the network and retried in ten
seconds.

NOTE:
The exported User type still declares an is_admin field. The server never sends it, and there is no such column — read an application role from user.app_metadata instead, and remember that only the service key can write that.

signInWithOtp is email-only. A phone code is two plain fetch calls; see
Phone one-time codes.

## Storage

```ts
const bucket = bl.storage.from('avatars');

await bucket.upload('me.png', file, { upsert: true, contentType: 'image/png' });
await bucket.update('me.png', file); // always overwrites
await bucket.list('teams/', { limit: 100, sortBy: { column: 'name', order: 'asc' } });
await bucket.move('me.png', 'archive/me.png');
await bucket.copy('me.png', 'archive/me.png');
await bucket.remove(['me.png', 'old.png']);

const { data: blob } = await bucket.download('me.png'); // data is a Blob
const { data: signed } = await bucket.createSignedUrl('me.png', 3600);
bucket.getPublicUrl('me.png'); // no request; public buckets only

await bl.storage.listBuckets();
await bl.storage.getBucket('avatars');
await bl.storage.createBucket('invoices', { public: false }); // service key
await bl.storage.updateBucket('invoices', { file_size_limit: 10485760 });
await bl.storage.deleteBucket('invoices'); // refused while not empty
```

Uploads accept a browser File/Blob, a Node Buffer, an
ArrayBuffer, a string or FormData. The bytes are sent as the raw request
body, so nothing is copied through a multipart encoder. remove() stops at the
first failure and returns what was already deleted, so a partial result is visible
rather than silent.

## Realtime

```ts
const channel = bl.channel('room:' + roomId);

channel
.on('postgres_changes',
{ event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.' + roomId },
({ new: row }) => append(row))
.on('broadcast', { event: 'typing' }, ({ payload }) => showTyping(payload))
.on('presence', { event: 'sync' }, ({ state }) => setHere(Object.values(state)));

const status = await channel.subscribe(); // SUBSCRIBED | CHANNEL_ERROR | TIMED_OUT | CLOSED
await channel.track({ name: 'Ada' });
await channel.send({ type: 'broadcast', event: 'typing', payload: { name: 'Ada' } });
channel.presence(); // everyone here, keyed by connection id

bl.getChannels();
await bl.removeChannel(channel);
await bl.removeAllChannels();

const off = bl.realtime.onError((message) => console.warn('realtime:', message));
```

send() and track() resolve to 'ok' or 'buffered' — the socket may
be down, in which case up to 200 frames are queued and flushed on reconnect. A
broadcast is not echoed to its sender, so update your own UI locally.

### A complete chat component

```ts
import { useEffect, useState } from 'react';
import { bl } from './lib/baselyra';

type Message = { id: number; body: string; author: string };

export function Chat({ roomId, name }: { roomId: number; name: string }) {
const [messages, setMessages] = useState([]);
const [typing, setTyping] = useState([]);
const [here, setHere] = useState([]);
const [draft, setDraft] = useState('');
const topic = 'room:' + roomId;

useEffect(() => {
// Created inside the effect: the cleanup drops it, so a remount binds a
// fresh channel instead of stacking a second set of callbacks on the old one.
const channel = bl.channel(topic);

bl.from('messages').select('id,body,author')
.eq('room_id', roomId).order('created_at').limit(50)
.then(({ data }) => setMessages(data ?? []));

channel
.on('postgres_changes',
{ event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.' + roomId },
({ new: row }) => setMessages((prev) => (row ? [...prev, row] : prev)))
.on('broadcast', { event: 'typing' }, ({ payload }) => {
const who = (payload as { name: string }).name;
setTyping((prev) => [...new Set([...prev, who])]);
setTimeout(() => setTyping((prev) => prev.filter((n) => n !== who)), 2000);
})
.on('presence', { event: 'sync' }, ({ state }) =>
setHere(Object.values(state).map((m) => (m as { name: string }).name)));

channel.subscribe().then((status) => {
if (status === 'SUBSCRIBED') void channel.track({ name });
});

return () => { void bl.removeChannel(channel); };
}, [topic, name]);

async function send(event: React.FormEvent) {
event.preventDefault();
const body = draft.trim();
if (!body) return;
setDraft('');
// The INSERT comes back over the channel above, so nothing is appended here.
const { error } = await bl.from('messages').insert({ room_id: roomId, body, author: name });
if (error) alert(error.message);
}

return (

{here.length} here{typing.length > 0 && ' · ' + typing.join(', ') + ' typing…'}

{messages.map((m) =>
- {m.author} {m.body})}

{
setDraft(e.target.value);
void bl.channel(topic).send({ type: 'broadcast', event: 'typing', payload: { name } });
}} />

);
}
```

The table needs its trigger and its policies first —
select baselyra.enable_realtime('public.messages'), and
policy set 4.

## AI

```ts
const { data, error } = await bl.ai.chat(
[{ role: 'user', content: 'Summarise this order.' }],
{ system: 'You are a support assistant for Acme.', signal: controller.signal },
);
console.log(data?.content);

const { data: status } = await bl.ai.status(); // { enabled, model }
```

The relay needs a signed-in user token, not the anon key, and answers 503
when no key is configured on the server. Streaming is not in the client; use
fetch against /ai/v1/chat with stream: true and read the SSE frames.

## Node scripts and tests

```ts
import { createClient } from '@baselyra/client';

const bl = createClient(process.env.BASELYRA_URL, process.env.BASELYRA_SERVICE_KEY, {
auth: { persistSession: false, autoRefreshToken: false },
realtime: { enabled: false },
});

const { data } = await bl.from('profiles').select('id,email').eq('plan', 'free');

await bl.dispose(); // release the refresh timer and the socket, or the process hangs
```

Passing the service key as the key runs every request as service_role, which
bypasses RLS entirely — right for a batch job, never for anything a browser
downloads.

## Exports

Export | What it is |

createClient, BaselyraClient | The client |

BaselyraError, Http | The error class and the fetch wrapper |

QueryBuilder, AuthClient, StorageClient, BucketApi | The pieces, for extending or testing |

RealtimeClient, RealtimeChannel |  |

backoffDelay(attempt, maxMs, random?) | The reconnect delay, exported because a backoff nobody can test is a backoff nobody trusts |

matchesFilter(filter, row) | The client-side filter check — presentation, never authorisation |

Types | Session, User, BaselyraResponse, AuthResponse, ClientOptions, Database helpers, PostgresChangesPayload, and the rest |

## Failure modes

What you see | Why | Fix |

error.status === 0, code network_error | CORS, DNS, or offline | Check CORS_ORIGINS on the server; * is the default but a production instance should name your origins |

A Node script never exits | The refresh timer and the WebSocket are alive | await bl.dispose() |

Every message arrives twice in React | A channel outlived a remount | Create it inside the effect, remove it in the cleanup |

No global WebSocket | Node before 22, or a runtime without one | Pass one as { realtime: { WebSocket } }, or disable realtime |

No global fetch | Node before 18 | Pass one as { global: { fetch } } |

A session vanishes on reload in a Safari private window | localStorage threw, so the client fell back to memory | Expected. Supply a storage adapter if you need something else. |

The user is signed out at random | A rotated refresh token was not stored — usually a second client instance | Hold exactly one createClient instance |

rpc() results cannot be narrowed… | A filter was chained onto rpc() | Filter inside the SQL function |

==============================================================================
# Next.js
URL: https://baselyra.sarimtools.com/docs/nextjs.html
==============================================================================

# Next.js

Baselyra has no cookie-based session helper, and that shapes how a Next.js app is built on it. This page shows the two patterns that work, the three clients worth having, and the one variable name that must never carry the service key.

## Environment

```bash
NEXT_PUBLIC_BASELYRA_URL=https://api.example.com
NEXT_PUBLIC_BASELYRA_ANON_KEY=eyJhbGciOiJIUzI1NiJ9…
BASELYRA_SERVICE_KEY=eyJhbGciOiJIUzI1NiJ9…      # no NEXT_PUBLIC_ prefix, ever
```

DANGER:
Anything prefixed NEXT_PUBLIC_ is inlined into the client bundle at build time. Putting the service key there ships a credential that bypasses every policy to every visitor, permanently, in a file they can read. The anon key belongs there; the service key never does.

## The browser client

```ts
'use client';

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

export const bl = createClient(
  process.env.NEXT_PUBLIC_BASELYRA_URL!,
  process.env.NEXT_PUBLIC_BASELYRA_ANON_KEY!,
);
```

One instance for the app's lifetime: it owns the session, its refresh timer and
the single WebSocket every channel shares.

## The server clients

```ts
import 'server-only';
import { createClient } from '@baselyra/client';

const URL = process.env.NEXT_PUBLIC_BASELYRA_URL!;
const ANON = process.env.NEXT_PUBLIC_BASELYRA_ANON_KEY!;

/**
 * Anonymous reads on the server — public content, sitemaps, OG images.
 * Sees exactly what a logged-out visitor sees, because it runs as anon.
 */
export const blPublic = createClient(URL, ANON, {
  auth: { persistSession: false, autoRefreshToken: false },
  realtime: { enabled: false },
});

/**
 * A per-request client acting as one signed-in user. RLS still applies, which
 * is the point: server code gets the user's own view, not everyone's.
 */
export function blAsUser(accessToken: string) {
  return createClient(URL, ANON, {
    auth: { persistSession: false, autoRefreshToken: false },
    realtime: { enabled: false },
    global: {
      fetch: (input, init = {}) => {
        const headers = new Headers(init.headers);
        headers.set('authorization', 'Bearer ' + accessToken);
        return fetch(input, { ...init, headers });
      },
    },
  });
}

/** Bypasses every policy. Webhooks, cron, admin tooling. Nothing user-facing. */
export function blAdmin() {
  const key = process.env.BASELYRA_SERVICE_KEY;
  if (!key) throw new Error('BASELYRA_SERVICE_KEY is not set');
  return createClient(URL, key, {
    auth: { persistSession: false, autoRefreshToken: false },
    realtime: { enabled: false },
  });
}
```

TIP:
import 'server-only' turns an accidental import from a client component into a build error rather than a leaked key. It costs one line.

Both server clients turn realtime and session persistence off. A server has no
browser to persist to, and a WebSocket per request would leak sockets.

## Sessions and server components

The client keeps the session in localStorage, so a server component
cannot see who is signed in. That is a real constraint, not an oversight: there
is no cookie-based session helper. Two patterns work.

### A. Client-side auth, server-side public data

Server components render public content with blPublic; anything user-specific
is a client component using bl. Simplest, and right for most apps.

```ts
import { blPublic } from '@/lib/baselyra-server';

export const revalidate = 60;

export default async function Home() {
  const { data: posts } = await blPublic
    .from('posts')
    .select('id,title,slug')
    .not('published_at', 'is', null)
    .order('published_at', { ascending: false })
    .limit(20);

  return {posts?.map((p) =>
- {p.title})}
;
}
```

### B. Mirror the token into a cookie

When you need user-scoped server rendering. Write the token from the client on
every auth change, read it in a server component, and hand it to blAsUser.

```ts
'use client';
import { useEffect } from 'react';
import { bl } from '@/lib/baselyra';

export function AuthSync({ children }: { children: React.ReactNode }) {
  useEffect(() => {
    const { data: { subscription } } = bl.auth.onAuthStateChange((_event, session) => {
      // Session-scoped, no Max-Age: it dies with the browser session.
      document.cookie = session
        ? 'bl-token=' + session.access_token + '; Path=/; SameSite=Lax; Secure'
        : 'bl-token=; Path=/; Max-Age=0; SameSite=Lax; Secure';
    });
    return () => subscription.unsubscribe();
  }, []);
  return <>{children};
}
```

```ts
import { cookies } from 'next/headers';
import { redirect } from 'next/navigation';
import { blAsUser } from '@/lib/baselyra-server';

export default async function Dashboard() {
  const token = (await cookies()).get('bl-token')?.value;
  if (!token) redirect('/login');

  const bl = blAsUser(token);
  const { data: notes, error } = await bl
    .from('notes')
    .select('id, title, created_at')
    .order('created_at', { ascending: false });

  if (error) {
    if (error.status === 401) redirect('/login');   // the hour is up
    throw new Error(error.message);
  }

  return {notes?.map((n) =>
- {n.title})}
;
}
```

CAREFUL:
The cookie is a convenience for reading, not a trust boundary. The token is verified by Baselyra on every request and RLS decides — but the cookie is readable by any script on the origin, exactly as localStorage is, and it now travels on every request to your own server too. Keep SameSite=Lax and Secure, and never treat its presence as proof of anything.

The access token expires in an hour, so treat a 401 from a server component
as "sign in again" rather than as a bug.

## Sign in

```ts
'use client';
import { useState } from 'react';
import { useRouter } from 'next/navigation';
import { bl } from '@/lib/baselyra';

export default function LoginForm() {
  const router = useRouter();
  const [error, setError] = useState(null);
  const [busy, setBusy] = useState(false);

  async function onSubmit(e: React.FormEvent) {
    e.preventDefault();
    setBusy(true);
    setError(null);
    const form = new FormData(e.currentTarget);
    const { error } = await bl.auth.signInWithPassword({
      email: String(form.get('email')),
      password: String(form.get('password')),
    });
    setBusy(false);
    if (error) return setError(error.message);
    router.refresh();
    router.push('/dashboard');
  }

  return (

      {busy ? 'Signing in…' : 'Sign in'}
      {error && {error}
}

  );
}
```

DETAIL:
A wrong password and an unknown address both return invalid_credentials with the same message, on purpose. Show it verbatim rather than inventing "no such user".

## A route handler with the service key

```ts
import { NextResponse } from 'next/server';
import { blAdmin } from '@/lib/baselyra-server';

export async function POST(request: Request) {
  const event = await request.json();
  // Verify the provider's signature here before trusting anything.

  const bl = blAdmin();
  const { error } = await bl
    .from('subscriptions')
    .upsert({ user_id: event.data.user_id, status: event.data.status }, { onConflict: 'user_id' });

  if (error) return NextResponse.json({ error: error.message }, { status: 500 });
  return NextResponse.json({ ok: true });
}
```

DANGER:
Never construct blAdmin() in a server action reachable from an untrusted form without checking who is calling. A server action is an HTTP endpoint like any other, and the service key inside it ignores every policy you wrote.

## Realtime in a client component

```ts
'use client';
import { useEffect, useState } from 'react';
import { bl } from '@/lib/baselyra';

export function LiveNotes({ initial }: { initial: Note[] }) {
  const [notes, setNotes] = useState(initial);

  useEffect(() => {
    const channel = bl.channel('public:notes');
    channel.on('postgres_changes',
      { event: 'INSERT', schema: 'public', table: 'notes' },
      ({ new: row }) => setNotes((prev) => (row ? [row, ...prev] : prev)));
    void channel.subscribe();
    return () => { void bl.removeChannel(channel); };
  }, []);

  return {notes.map((n) =>
- {n.title})}
;
}
```

Render the first page on the server and hand it in as initial, then let the
channel keep it fresh. The table needs
select baselyra.enable_realtime('public.notes') and a read policy.

## Failure modes

What you see | Why | Fix |

process.env.BASELYRA_SERVICE_KEY is undefined in the browser | Working as designed — it has no NEXT_PUBLIC_ prefix | Use it only in server code |

A server component renders nothing for a signed-in user | It ran as anon; the session lives in localStorage | Pattern B, or move that part to a client component |

401 from a server component after an hour | The mirrored token expired | Redirect to sign-in; the browser client has already refreshed |

Realtime never connects in the App Router | The channel was created in a server component | It must be inside a 'use client' component's effect |

CORS errors in the browser only | CORS_ORIGINS does not name your site | Set it in .env on the server and restart |

Two clients, two sessions, random sign-outs | A second createClient call somewhere | Export one instance and import it |

==============================================================================
# React
URL: https://baselyra.sarimtools.com/docs/react.html
==============================================================================

# React

Nothing about React needs a Baselyra adapter — the client is a plain object with promises and callbacks. What is worth writing down is where to create it, how to track the session, and the one effect mistake everybody makes with realtime.

## Setup

```bash
npm install @baselyra/client
```

```bash
VITE_BASELYRA_URL=https://api.example.com
VITE_BASELYRA_ANON_KEY=eyJhbGciOiJIUzI1NiJ9…
```

```ts
import { createClient } from '@baselyra/client';

export const bl = createClient(
  import.meta.env.VITE_BASELYRA_URL,
  import.meta.env.VITE_BASELYRA_ANON_KEY,
);
```

DANGER:
Vite inlines every VITE_* variable into the bundle. The anon key belongs there — it is public by design. The service key must never be one, and there is no server in a Vite app to put it on.

Export one instance and import it everywhere. Two instances means two sessions,
two refresh timers racing to rotate the same refresh token, and users signed out at
random.

## An auth hook

```ts
import { useEffect, useState } from 'react';
import type { User } from '@baselyra/client';
import { bl } from './baselyra';

export function useAuth() {
  const [user, setUser] = useState(null);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    bl.auth.getSession().then(({ data }) => {
      setUser(data.session?.user ?? null);
      setLoading(false);
    });

    const { data: { subscription } } = bl.auth.onAuthStateChange((_event, session) => {
      setUser(session?.user ?? null);
    });
    return () => subscription.unsubscribe();
  }, []);

  return { user, loading };
}
```

getSession() refreshes first if the stored token has already expired, so it
never hands you a token the server will reject. The listener then covers sign-in,
sign-out, background renewal and profile updates.

## A protected route

```ts
import { Navigate } from 'react-router-dom';
import { useAuth } from './lib/useAuth';

export function RequireAuth({ children }: { children: React.ReactNode }) {
  const { user, loading } = useAuth();
  if (loading) return Loading…
;
  if (!user) return ;
  return <>{children};
}
```

CAREFUL:
This is navigation, not security. Hiding a route hides a route; the data is protected by row level security on the server, and a user who edits your bundle still gets exactly the rows their policies allow. Never let a client-side guard be the only thing between someone and a table.

## Reading data

```ts
import { useEffect, useState } from 'react';
import { bl } from './lib/baselyra';

export function Notes() {
  const [notes, setNotes] = useState([]);
  const [error, setError] = useState(null);

  useEffect(() => {
    const controller = new AbortController();

    bl.from('notes')
      .select('id,title,created_at')
      .order('created_at', { ascending: false })
      .abortSignal(controller.signal)
      .then(({ data, error }) => {
        if (error) {
          if (error.code === 'aborted') return;     // the component went away
          return setError(error.message);
        }
        setNotes(data ?? []);
      });

    return () => controller.abort();
  }, []);

  if (error) return {error}
;
  return {notes.map((n) =>
- {n.title})}
;
}
```

An empty list here usually is not a bug: it means row level security admits no
rows for this caller. Check the policy before you check the query.

## A list that stays live

```ts
useEffect(() => {
  bl.from('notes').select('id,title').order('created_at', { ascending: false })
    .then(({ data }) => setNotes(data ?? []));

  // Created inside the effect so the cleanup drops it.
  const channel = bl.channel('public:notes');
  channel.on('postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'notes' },
    ({ new: row }) => setNotes((prev) => (row ? [row, ...prev] : prev)));
  void channel.subscribe();

  return () => { void bl.removeChannel(channel); };
}, []);
```

DANGER:
The remount trap. bl.channel(name) returns the same channel object
for the same name. Create it outside an effect, or forget the cleanup, and a
remount — which React 18's Strict Mode does deliberately in development — binds a
second set of callbacks to the same channel. Every message then arrives twice, and
it looks like a server bug. Create inside, remove in the cleanup, always.

## Writing, and optimistic updates

```ts
async function addNote(title: string) {
  const { data, error } = await bl.from('notes').insert({ title }).select().single();
  if (error) return setError(error.message);
  // With realtime on this table the INSERT comes back over the channel too —
  // key the list by id so the two do not double up.
  setNotes((prev) => (prev.some((n) => n.id === data.id) ? prev : [data, ...prev]));
}
```

Do not send user_id or author from the client. Give the column a
default auth.uid() and let the policy's with check refuse anything else —
ownership established by the database cannot be forgotten by a component.

## Uploading a file

```ts
async function onPick(e: React.ChangeEvent) {
  const file = e.target.files?.[0];
  if (!file || !user) return;

  const path = user.id + '/' + file.name;
  const { error } = await bl.storage.from('avatars').upload(path, file, { upsert: true });
  if (error) return setError(error.message);

  const { data } = bl.storage.from('avatars').getPublicUrl(path);
  setAvatarUrl(data.publicUrl + '?v=' + Date.now());   // bust the immutable cache
}
```

A public object is served immutable with a year's max-age, so a re-upload at
the same key needs a cache-busting query parameter or the browser keeps showing the
old image.

## Failure modes

What you see | Why | Fix |

Every realtime message twice | The channel outlived a remount, or Strict Mode double-invoked the effect | Create inside the effect, removeChannel in the cleanup |

[] from a table with rows | RLS admits nothing for this role | Test the policy |

Users signed out at random | Two client instances rotating the same refresh token | One createClient, exported |

error.code === 'aborted' in the console | A component unmounted mid-request | Ignore that code; it is not a failure |

A re-uploaded image does not change | The public URL is cached immutable | Add a version query parameter |

CORS errors from the browser | CORS_ORIGINS does not name your dev origin | Add http://localhost:5173 on the server |

==============================================================================
# Flutter and Dart
URL: https://baselyra.sarimtools.com/docs/flutter.html
==============================================================================

# Flutter and Dart

There is no official Dart client. The API is plain HTTP, so package:http plus package:web_socket_channel for realtime is the whole story. What follows is a complete client you can paste into a project and extend.

## Dependencies

```bash
dependencies:
  http: ^1.2.0
  web_socket_channel: ^3.0.0
  shared_preferences: ^2.3.0
```

DANGER:
Never ship the service key in a Flutter app. The binary is downloadable and the strings inside it are readable — an APK is a zip file. The anon key is the one that belongs in a mobile app; row level security is what protects the data.

## The client

```dart
import 'dart:async';
import 'dart:convert';
import 'package:http/http.dart' as http;
import 'package:shared_preferences/shared_preferences.dart';
import 'package:web_socket_channel/web_socket_channel.dart';

class BaselyraException implements Exception {
  BaselyraException(this.code, this.message, this.status);
  final String code;
  final String message;
  final int status;
  @override
  String toString() => 'Baselyra $status $code: $message';
}

class Baselyra {
  Baselyra({required this.url, required this.anonKey});

  final String url;
  final String anonKey;
  String? _accessToken;
  String? _refreshToken;

  Map get _headers => {
        'apikey': anonKey,
        'authorization': 'Bearer ${_accessToken ?? anonKey}',
        'content-type': 'application/json',
      };

  dynamic _decode(http.Response res) {
    final body = res.body.isEmpty ? null : jsonDecode(res.body);
    if (res.statusCode >= 400) {
      final error = (body is Map && body['error'] is Map) ? body['error'] as Map : const {};
      throw BaselyraException(
        (error['code'] ?? 'http_error').toString(),
        (error['message'] ?? res.reasonPhrase ?? 'Request failed').toString(),
        res.statusCode,
      );
    }
    return body;
  }

  // ---- auth ----

  Future signIn(String email, String password) async {
    final res = await http.post(
      Uri.parse('$url/auth/v1/token?grant_type=password'),
      headers: _headers,
      body: jsonEncode({'email': email, 'password': password}),
    );
    await _store(_decode(res) as Map);
  }

  Future signUp(String email, String password, {Map? data}) async {
    final res = await http.post(
      Uri.parse('$url/auth/v1/signup'),
      headers: _headers,
      body: jsonEncode({'email': email, 'password': password, if (data != null) 'data': data}),
    );
    final body = _decode(res) as Map;
    // Null when the instance confirms email addresses — the user must click the link.
    if (body['session'] != null) await _store(body['session'] as Map);
  }

  Future signOut() async {
    await http.post(Uri.parse('$url/auth/v1/logout'), headers: _headers);
    _accessToken = null;
    _refreshToken = null;
    final prefs = await SharedPreferences.getInstance();
    await prefs.remove('bl_refresh_token');
  }

  /// Access tokens last an hour; call this at startup to trade the stored
  /// refresh token for a fresh pair. A refresh token is single-use, so the new
  /// one MUST be stored or the next launch signs the user out.
  Future restore() async {
    final prefs = await SharedPreferences.getInstance();
    final stored = prefs.getString('bl_refresh_token');
    if (stored == null) return false;
    final res = await http.post(
      Uri.parse('$url/auth/v1/token?grant_type=refresh_token'),
      headers: {'apikey': anonKey, 'content-type': 'application/json'},
      body: jsonEncode({'refresh_token': stored}),
    );
    if (res.statusCode >= 400) {
      await prefs.remove('bl_refresh_token');
      return false;
    }
    await _store(jsonDecode(res.body) as Map);
    return true;
  }

  Future _store(Map session) async {
    _accessToken = session['access_token'] as String?;
    _refreshToken = session['refresh_token'] as String?;
    final prefs = await SharedPreferences.getInstance();
    if (_refreshToken != null) await prefs.setString('bl_refresh_token', _refreshToken!);
  }

  // ---- data ----

  Future
- > select(String table, {Map query = const {}}) async {
    final uri = Uri.parse('$url/rest/v1/$table').replace(queryParameters: query);
    return _decode(await http.get(uri, headers: _headers)) as List;
  }

  Future
- > insert(String table, Map row) async {
    final res = await http.post(
      Uri.parse('$url/rest/v1/$table'),
      headers: {..._headers, 'Prefer': 'return=representation'},
      body: jsonEncode(row),
    );
    return _decode(res) as List;
  }

  Future
- > update(
      String table, Map filters, Map patch) async {
    if (filters.isEmpty) throw ArgumentError('a filter is required: an unfiltered PATCH rewrites the table');
    final uri = Uri.parse('$url/rest/v1/$table').replace(queryParameters: filters);
    final res = await http.patch(
      uri,
      headers: {..._headers, 'Prefer': 'return=representation'},
      body: jsonEncode(patch),
    );
    return _decode(res) as List;
  }

  /// The server refuses an unfiltered delete, so `filters` must not be empty.
  Future delete(String table, Map filters) async {
    if (filters.isEmpty) throw ArgumentError('a filter is required: an unfiltered DELETE empties the table');
    final uri = Uri.parse('$url/rest/v1/$table').replace(queryParameters: filters);
    _decode(await http.delete(uri, headers: _headers));
  }

  Future rpc(String fn, Map args) async {
    final res = await http.post(
      Uri.parse('$url/rest/v1/rpc/$fn'),
      headers: _headers,
      body: jsonEncode(args),
    );
    return _decode(res);
  }

  // ---- storage ----

  Future> upload(
      String bucket, String key, List bytes, String mime) async {
    final request = http.MultipartRequest('POST', Uri.parse('$url/storage/v1/object/$bucket/$key'))
      ..headers['authorization'] = 'Bearer ${_accessToken ?? anonKey}'
      ..files.add(http.MultipartFile.fromBytes('file', bytes, filename: key.split('/').last));
    final res = await http.Response.fromStream(await request.send());
    return _decode(res) as Map;
  }

  String publicUrl(String bucket, String key) => '$url/storage/v1/object/public/$bucket/$key';

  Future signedUrl(String bucket, String key, {int expiresIn = 3600}) async {
    final res = await http.post(
      Uri.parse('$url/storage/v1/object/sign/$bucket/$key'),
      headers: _headers,
      body: jsonEncode({'expiresIn': expiresIn}),
    );
    return (_decode(res) as Map)['url'] as String;
  }

  // ---- realtime ----

  /// One socket, one channel. Cancel the returned subscription on dispose.
  Stream> subscribe(String channel, {String? filter}) {
    final wsUrl = url.replaceFirst(RegExp(r'^http'), 'ws');
    final socket = WebSocketChannel.connect(
      Uri.parse('$wsUrl/realtime/v1?apikey=${_accessToken ?? anonKey}'),
    );
    socket.sink.add(jsonEncode({
      'type': 'subscribe',
      'channel': channel,
      if (filter != null) 'filter': filter,
    }));
    return socket.stream
        .map((event) => jsonDecode(event as String) as Map)
        .where((frame) => frame['type'] == 'postgres_changes' || frame['type'] == 'broadcast');
  }
}
```

## Using it

```dart
final bl = Baselyra(url: 'https://api.example.com', anonKey: '');

await bl.restore();                                   // at startup
await bl.signIn('ada@example.com', 'correct-horse-battery');

final notes = await bl.select('notes', query: {
  'select': 'id,title',
  'order': 'created_at.desc',
  'limit': '20',
});

await bl.insert('notes', {'title': 'From Flutter'});

final sub = bl.subscribe('public:notes', filter: 'archived=is.false').listen((frame) {
  debugPrint('${frame['event']} ${frame['new']}');
});
// later: await sub.cancel();
```

## Things worth knowing on mobile

- Store the rotated refresh token. Every refresh returns a new one and
retires the old. Replaying a spent token revokes the whole chain and the user
is signed out — which is exactly what an app that forgets to save the new value
does on its second launch.

- Refresh at startup, not on a 401. A stored token is usually stale by the
next launch, and a failure-driven refresh means every call site has to know how to
replay itself.

- The OS suspends sockets in the background. Re-subscribe and refetch on
resume rather than trusting the change feed to have caught everything while the app
was asleep.

- Filters are query parameters, so Uri.replace(queryParameters:)
does the encoding for you. A literal + in a value must be encoded or it
decodes to a space.

- Consider flutter_secure_storage instead of SharedPreferences for the
refresh token — it is a 30-day credential.

## Failure modes

What you see | Why | Fix |

Signed out on the second launch | The rotated refresh token was not stored | Store the new one on every refresh |

400 invalid_credentials for a real account | Same message for a wrong password and an unknown address | Show it verbatim |

signUp returns no session | AUTH_CONFIRM_EMAIL is on | The user has to click the link first |

An empty list where the Studio shows rows | RLS admits nothing for this role | Write a policy |

The realtime stream never emits | The table has no trigger, or a proxy ate the Upgrade header | select baselyra.enable_realtime('public.notes'), then the 101 test |

ArgumentError: a filter is required | The guard above fired | Pass a filter — the server would have refused it anyway |

==============================================================================
# PHP
URL: https://baselyra.sarimtools.com/docs/php.html
==============================================================================

# PHP

No dependencies beyond ext-curl and ext-json. This client acts either as a signed-in visitor — forward their token and row level security applies — or as a trusted server holding the service key.

## The client

```php
accessToken = $token;
    }

    /** @return array{0: mixed, 1: array} decoded body and response headers */
    private function request(string $method, string $path, mixed $body = null, array $extraHeaders = []): array
    {
        $headers = array_merge([
            'apikey: ' . $this->key,
            'authorization: Bearer ' . ($this->accessToken ?? $this->key),
            'content-type: application/json',
        ], $extraHeaders);

        $ch = curl_init($this->url . $path);
        curl_setopt_array($ch, [
            CURLOPT_CUSTOMREQUEST  => $method,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_HTTPHEADER     => $headers,
            CURLOPT_TIMEOUT        => 30,
            CURLOPT_HEADER         => true,
        ]);
        if ($body !== null) {
            curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($body, JSON_THROW_ON_ERROR));
        }

        $raw = curl_exec($ch);
        if ($raw === false) {
            $message = curl_error($ch);
            curl_close($ch);
            throw new BaselyraError('network_error', $message, 0);
        }

        $status     = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        $headerSize = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        curl_close($ch);

        $responseHeaders = [];
        foreach (explode("\r\n", substr($raw, 0, $headerSize)) as $line) {
            if (str_contains($line, ':')) {
                [$name, $value] = explode(':', $line, 2);
                $responseHeaders[strtolower(trim($name))] = trim($value);
            }
        }

        $payload = substr($raw, $headerSize);
        $decoded = $payload === '' ? null : json_decode($payload, true);

        if ($status >= 400) {
            $error = is_array($decoded) && isset($decoded['error']) ? $decoded['error'] : [];
            throw new BaselyraError(
                (string) ($error['code'] ?? 'http_error'),
                (string) ($error['message'] ?? 'Request failed'),
                $status,
            );
        }

        return [$decoded, $responseHeaders];
    }

    public function signIn(string $email, string $password): array
    {
        [$session] = $this->request('POST', '/auth/v1/token?grant_type=password', [
            'email' => $email, 'password' => $password,
        ]);
        $this->accessToken = $session['access_token'];
        return $session;
    }

    /** @param array $query e.g. ['select' => 'id,title', 'published' => 'eq.true'] */
    public function select(string $table, array $query = []): array
    {
        [$rows] = $this->request('GET', '/rest/v1/' . rawurlencode($table) . '?' . http_build_query($query));
        return $rows ?? [];
    }

    /** Total row count for a filter, without fetching the rows. */
    public function count(string $table, array $query = []): int
    {
        $query['limit'] = '1';
        [, $headers] = $this->request(
            'GET',
            '/rest/v1/' . rawurlencode($table) . '?' . http_build_query($query),
            null,
            ['Prefer: count=exact'],
        );
        $range = $headers['content-range'] ?? '*/0';
        return (int) substr($range, strpos($range, '/') + 1);
    }

    public function insert(string $table, array $row): array
    {
        [$rows] = $this->request('POST', '/rest/v1/' . rawurlencode($table), $row, ['Prefer: return=representation']);
        return $rows[0] ?? [];
    }

    /** @param array $filters required — the server refuses an unfiltered write */
    public function update(string $table, array $filters, array $patch): array
    {
        if ($filters === []) {
            throw new InvalidArgumentException('a filter is required: an unfiltered PATCH rewrites the table');
        }
        [$rows] = $this->request(
            'PATCH',
            '/rest/v1/' . rawurlencode($table) . '?' . http_build_query($filters),
            $patch,
            ['Prefer: return=representation'],
        );
        return $rows ?? [];
    }

    public function delete(string $table, array $filters): void
    {
        if ($filters === []) {
            throw new InvalidArgumentException('a filter is required: an unfiltered DELETE empties the table');
        }
        $this->request('DELETE', '/rest/v1/' . rawurlencode($table) . '?' . http_build_query($filters));
    }

    public function rpc(string $fn, array $args = []): mixed
    {
        [$result] = $this->request('POST', '/rest/v1/rpc/' . rawurlencode($fn), $args);
        return $result;
    }
}
```

## Using it

```php
setAccessToken($_SESSION['bl_access_token'] ?? null);

$articles = $bl->select('articles', [
    'select'       => 'id,title,published_at',
    'published_at' => 'not.is.null',
    'order'        => 'published_at.desc',
    'limit'        => '10',
]);

foreach ($articles as $article) {
    echo htmlspecialchars($article['title']), "\n";
}

$total = $bl->count('articles', ['published_at' => 'not.is.null']);
echo "$total published\n";
```

### Handling the error

```php
try {
    $bl->insert('articles', ['title' => 'Hello', 'slug' => 'hello']);
} catch (BaselyraError $e) {
    if ($e->errorCode === 'unique_violation') {
        // a duplicate slug — show it next to the field
    } elseif ($e->status === 401) {
        // the access token expired; refresh or send them to sign in
    } else {
        error_log("baselyra {$e->status} {$e->errorCode}: {$e->getMessage()}");
    }
}
```

Branch on errorCode, never on the message. The full list is on
REST API.

## Keys and sessions

DANGER:
Keep the service key in an environment variable read by PHP-FPM, never in a file under the document root and never in a template. A misconfigured web server serving Baselyra.php as text is a bad day; serving a file with a service key in it is a much worse one.

Acting as | Construct with | And |

A visitor | the anon key | setAccessToken($visitorToken) — RLS applies to their own rows |

Nobody | the anon key | no access token — the request runs as anon |

The server | the service key | never set an access token; every policy is bypassed |

Storing the whole session in $_SESSION is fine. Store the
refresh token too, and when a request comes back 401, exchange it once
and retry:

```php
/** Add to the class. A refresh token is single-use: store BOTH values it returns. */
public function refresh(string $refreshToken): array
{
    [$session] = $this->request('POST', '/auth/v1/token?grant_type=refresh_token', [
        'refresh_token' => $refreshToken,
    ]);
    $this->accessToken = $session['access_token'];
    return $session;
}
```

```php
try {
    $rows = $bl->select('notes', ['select' => 'id,title']);
} catch (BaselyraError $e) {
    if ($e->status !== 401) throw $e;
    $session = $bl->refresh($_SESSION['bl_refresh_token']);
    $_SESSION['bl_access_token']  = $session['access_token'];
    $_SESSION['bl_refresh_token'] = $session['refresh_token'];   // the old one is now spent
    $rows = $bl->select('notes', ['select' => 'id,title']);
}
```

## Failure modes

What you see | Why | Fix |

network_error with an empty message | curl could not connect — TLS, DNS, or a firewall | curl -v the same URL from the same host |

401 an hour after sign-in | The access token expired | Refresh with the stored refresh token and store both new values |

[] where the Studio shows rows | RLS, or you forgot setAccessToken | Check both, in that order |

A filter value with + matches nothing | http_build_query encodes it correctly; a hand-built string does not | Use http_build_query |

409 unique_violation | A constraint refused the row | Read details.detail, which names the key |

InvalidArgumentException: a filter is required | The guard above fired | Pass a filter — the server would have refused anyway |

==============================================================================
# Python
URL: https://baselyra.sarimtools.com/docs/python.html
==============================================================================

# Python

One dependency, requests. This client is what a batch job, an ETL script or a Django view needs: it acts either as a signed-in user, with row level security applying, or as a server holding the service key.

## The client

```bash
pip install requests
```

```python
"""Minimal Baselyra client. Nothing here is Baselyra-specific beyond the paths."""
from __future__ import annotations

from typing import Any
import requests

class BaselyraError(Exception):
    def __init__(self, code: str, message: str, status: int) -> None:
        super().__init__(f"{status} {code}: {message}")
        self.code = code
        self.message = message
        self.status = status

class Baselyra:
    def __init__(self, url: str, key: str, access_token: str | None = None) -> None:
        self.url = url.rstrip("/")
        self.key = key
        self.access_token = access_token
        self.session = requests.Session()

    @property
    def _headers(self) -> dict[str, str]:
        return {
            "apikey": self.key,
            "authorization": f"Bearer {self.access_token or self.key}",
            "content-type": "application/json",
        }

    def _request(self, method: str, path: str, *, json: Any = None,
                 params: dict[str, str] | None = None,
                 headers: dict[str, str] | None = None) -> requests.Response:
        res = self.session.request(
            method,
            f"{self.url}{path}",
            json=json,
            params=params,
            headers={**self._headers, **(headers or {})},
            timeout=30,
        )
        if res.status_code >= 400:
            try:
                error = res.json().get("error", {})
            except ValueError:
                error = {}
            raise BaselyraError(
                error.get("code", "http_error"),
                error.get("message", res.reason),
                res.status_code,
            )
        return res

    # ---- auth ----

    def sign_in(self, email: str, password: str) -> dict[str, Any]:
        session = self._request(
            "POST", "/auth/v1/token", params={"grant_type": "password"},
            json={"email": email, "password": password},
        ).json()
        self.access_token = session["access_token"]
        return session

    def refresh(self, refresh_token: str) -> dict[str, Any]:
        """A refresh token is single-use: store BOTH values it returns."""
        session = self._request(
            "POST", "/auth/v1/token", params={"grant_type": "refresh_token"},
            json={"refresh_token": refresh_token},
        ).json()
        self.access_token = session["access_token"]
        return session

    # ---- data ----

    def select(self, table: str, **params: str) -> list[dict[str, Any]]:
        return self._request("GET", f"/rest/v1/{table}", params=params).json()

    def count(self, table: str, **params: str) -> int:
        res = self._request(
            "GET", f"/rest/v1/{table}", params={**params, "limit": "1"},
            headers={"Prefer": "count=exact"},
        )
        total = res.headers.get("content-range", "*/0").split("/")[-1]
        return 0 if total == "*" else int(total)

    def insert(self, table: str, row: dict[str, Any] | list[dict[str, Any]]) -> list[dict[str, Any]]:
        return self._request(
            "POST", f"/rest/v1/{table}", json=row,
            headers={"Prefer": "return=representation"},
        ).json()

    def upsert(self, table: str, rows: list[dict[str, Any]], on_conflict: str) -> list[dict[str, Any]]:
        return self._request(
            "POST", f"/rest/v1/{table}", json=rows,
            params={"on_conflict": on_conflict},
            headers={"Prefer": "return=representation,resolution=merge-duplicates"},
        ).json()

    def update(self, table: str, filters: dict[str, str], patch: dict[str, Any]) -> list[dict[str, Any]]:
        if not filters:
            raise ValueError("a filter is required: an unfiltered PATCH rewrites the table")
        return self._request(
            "PATCH", f"/rest/v1/{table}", params=filters, json=patch,
            headers={"Prefer": "return=representation"},
        ).json()

    def delete(self, table: str, filters: dict[str, str]) -> None:
        if not filters:
            raise ValueError("a filter is required: an unfiltered DELETE empties the table")
        self._request("DELETE", f"/rest/v1/{table}", params=filters)

    def rpc(self, fn: str, **args: Any) -> Any:
        return self._request("POST", f"/rest/v1/rpc/{fn}", json=args).json()

    def paginate(self, table: str, page_size: int = 1000, **params: str):
        """Yield every row a filter matches, one page at a time."""
        offset = 0
        while True:
            page = self.select(table, limit=str(page_size), offset=str(offset), **params)
            if not page:
                return
            yield from page
            if len(page)  dict[str, Any]:
        res = self.session.post(
            f"{self.url}/storage/v1/object/{bucket}/{key}",
            data=data,
            headers={
                "authorization": f"Bearer {self.access_token or self.key}",
                "content-type": content_type,
            },
            timeout=120,
        )
        res.raise_for_status()
        return res.json()

    def signed_url(self, bucket: str, key: str, expires_in: int = 3600) -> str:
        return self._request(
            "POST", f"/storage/v1/object/sign/{bucket}/{key}",
            json={"expiresIn": expires_in},
        ).json()["url"]
```

## A server-side batch job

```python
import os
from baselyra import Baselyra

# The service key bypasses RLS, so this sees everything.
bl = Baselyra("https://api.example.com", os.environ["BASELYRA_SERVICE_KEY"])

for user in bl.paginate("profiles", select="id,email", plan="eq.free"):
    print(user["email"])

bl.upsert(
    "metrics",
    [{"day": "2026-08-23", "signups": 412}],
    on_conflict="day",
)
```

DANGER:
The service key ignores every policy on every table. Keep it in the environment, never in the repository, and never in a script that also runs somewhere a user can reach. A user-scoped script uses the anon key and the user's own token instead.

```python
bl = Baselyra("https://api.example.com", ANON_KEY)
bl.sign_in("ada@example.com", "correct-horse-battery")
print(bl.select("notes", select="id,title"))     # only Ada's, decided by RLS
```

## Paging and counting

```python
total = bl.count("orders", status="eq.paid")
print(f"{total} paid orders")

# limit is clamped to 10000 by the server, so page rather than asking for everything
for order in bl.paginate("orders", page_size=1000, status="eq.paid", order="created_at.asc"):
    process(order)
```

TIP:
OFFSET makes Postgres walk and discard every skipped row, so a very long table is faster with keyset paging: order by created_at,id and filter on the last row you saw. See Keyset paging.

## Calling a function

```python
rows = bl.rpc("search_articles", term="postgres", max_results=5)
```

Arguments are passed by name and always bound as parameters. Filters do not apply
to an RPC result — narrow inside the function.

## Numbers arrive as strings

```python
from decimal import Decimal

row = bl.select("invoices", select="amount", id="eq.1")[0]
row["amount"]                 # '1250.00' — a str, not a float
Decimal(row["amount"])        # what to do with it
```

bigint and numeric cross the wire as strings so JSON stays exact: a float
cannot represent 1250.00, and money must not round. Parse with
Decimal or int where you need arithmetic.

## Failure modes

What you see | Why | Fix |

BaselyraError: 401 unauthorized | The access token expired, or the key is wrong | Refresh, and store both returned tokens |

[] from a table with rows | RLS admits nothing for this role | Use the service key for a batch job, or write a policy |

ValueError: a filter is required | The guard above fired | Pass a filter — the server refuses an unfiltered write anyway |

408 statement_timeout | Over DATABASE_STATEMENT_TIMEOUT_MS (15s) | Index the filtered columns, or page in smaller batches |

numeric compares wrong | It is a string | Decimal(value) |

429 rate_limited from a loop | 300 requests per minute per IP | Batch with insert([…]) and upsert rather than one call per row |

A + in a filter value matches nothing | It decoded to a space | requests encodes params correctly — do not hand-build the query string |

==============================================================================
# The Studio
URL: https://baselyra.sarimtools.com/docs/studio.html
==============================================================================

# The Studio

The Studio is compiled into the server and served at /. It talks to /admin/v1 and nothing else — every screen here is a view over an API you can call yourself. Its accounts live in the control database and have nothing to do with your application's users.

## Signing in

Open the instance root and sign in with the credentials scripts/setup.sh
printed on the first boot. That account lives in control.platform_users, in the
control database, and is created by scripts/migrate.js from
BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD.

DANGER:
No row in auth.users can ever open the Studio, however it is
configured. A Studio token carries a typ: "platform" claim inside its
signature and exactly one endpoint mints it. If you are trying to sign in with an
application user's email and password, that is why it fails — see
Troubleshooting.

POST /admin/v1/login is capped at five attempts per minute per IP: it is the
one route on the instance that turns a password into a token holding
service_role, and it is reachable without any credential.

## The database page

[diagram: The Studio's Database page: a 240 pixel sidebar of sections, a top bar with a breadcrumb and a run action, a list of tables each showing whether row level security is on, and a data grid of 32 pixel rows with monospace values.]

Database → public.invoices. The RLS state of every table is shown in the list, because a table with row level security off is the one mistake that ships a world-writable API.

Schemas are grouped the way the server groups them: your schemas first and
expanded, auth and storage collapsed under System, and baselyra,
pg_catalog, information_schema and pg_toast not returned at all. Every
table carries a planner row estimate and its RLS state.

CAREFUL:
The row grid runs as service_role, which bypasses row level security. That is deliberate — an RLS-filtered admin grid would quietly lie about what a table contains — but it means a table that looks fine here may be denying every request from your app. Check with the impersonation block, not with the grid.

## The pages

Page | What it does | API behind it |

Overview | Instance health, counts, largest tables, recent audit entries, and the Connect panel with your URL and anon key | /admin/v1/overview, /usage, /keys |

Database | Table list with RLS state, data grid, row editor, column and policy editing | /admin/v1/schema, /tables/…/rows, /tables, /policies |

SQL | Editor with schema-aware autocompletion, a results grid, saved snippets, and a read-only/write toggle | /admin/v1/sql |

Auth | Application users: list, search, create, edit, ban, delete | /auth/v1/admin/users |

Storage | Buckets, a file browser, upload, preview and signed URLs | /storage/v1/* |

Realtime | Which tables are enabled, the live socket count, and an event inspector over a WebSocket | /admin/v1/realtime |

API | A generated reference per table, the project keys, and copyable snippets | /admin/v1/schema, /keys |

AI | Ask-your-database and SQL generation. Hidden entirely when no key is configured. | /ai/v1/* |

Email | The six templates, with a live preview and a test send | /admin/v1/email-templates/* |

Settings | Instance settings, keys, the team, and the audit log | /admin/v1/settings, /team, /roles, /logs |

Cmd + K opens a command palette that jumps between pages,
tables and actions. Below the md breakpoint the sidebar becomes a drawer and
every table scrolls inside its own container.

### The SQL editor

The editor defaults to read-only. Flipping the segmented control beside
Run to Write tints it with the warning colour, because that is the
mode that can drop things. Cmd + Enter runs the selection, or
the whole statement when nothing is selected.

```json
{"columns":[{"name":"count","type":"int8"}],
 "rows":[{"count":"3"}],
 "rowCount":1,
 "durationMs":4,
 "command":"SELECT"}
```

Three properties are worth knowing before you use it:

- It runs as baselyra_sql, not as the server's own login role. That role
keeps every object right the owner has — including reading every password hash in
auth.users — and loses access to the host. See
The SQL editor.

- The audit row is written before the statement runs, on a separate
connection, so a statement that fails or rolls back is still recorded. It contains
the statement text: do not type a password into the editor and expect it to be
forgotten.

- A viewer may run read-only statements and nothing else. The write half of
that decision is made from the request body, at the one point that can see it.

## Studio accounts

Route | Capability |

POST /admin/v1/login | — (rate limited to 5/min per IP) |

GET /admin/v1/me · PUT /admin/v1/me | any signed-in account |

GET /admin/v1/team | team.read |

POST PUT DELETE /admin/v1/team | team.manage |

GET /admin/v1/roles | team.read |

POST PUT DELETE /admin/v1/roles | team.manage |

Changing your own email or password requires current_password. You cannot
delete your own account, and you cannot delete or demote the last owner — an
instance with no owner cannot be recovered from the Studio at all, only by an
INSERT into control.platform_users from psql against the control
database.

### Roles and capabilities

Three roles ship, and they are rows rather than constants — an owner can add
more with their own capability set.

Role | Holds |

owner | Every capability, including team.manage |

admin | Everything except managing accounts and roles |

viewer | Every read capability, including keys.read. Every mutating route answers 403. |

A capability is checked per route, against the pattern the route was registered
with, using the list inside the token's signature:

```bash
database.read   database.write   database.ddl
sql.read        sql.write
auth.read       auth.write
storage.read    storage.write
realtime.read   realtime.write
import.run
settings.read   settings.write   keys.read
email.read      email.write
logs.read
team.read       team.manage
```

DETAIL:
A route that is missing from that map requires team.manage, the highest capability there is. A permission system leaks the day someone adds a route and forgets the map; defaulting to the highest makes that a 403 the author hits on their first request, which is the cheapest possible way to find out.

DANGER:
GET /admin/v1/keys returns the service key to a Studio session — that is
what the Connect panel is for. viewer holds keys.read, so any Studio account
is effectively a service-key holder. Give Studio accounts only to people you would
give the database password to.

### Tokens are stateless

A Studio token lasts one hour and nothing is stored server-side. No route
re-checks the account row, so:

- Signing out records an audit entry and nothing more.

- Deleting an operator's row stops them signing in again; a token already
issued keeps working until it expires, at most an hour later.

- To cut every Studio token off at once, rotate JWT_SECRET — which also
invalidates both project keys and every application session.

The service key is a second way in and always has been: it is the instance's own
credential, it predates any Studio account, and /admin/v1 accepts it so scripts
and first-boot recovery keep working. It has no /me, because it is not a person.

## What the Studio does not have

Missing | Do it here instead |

A page for database webhooks | /admin/v1/hooks/webhooks — the routes exist, the UI does not yet In progress |

A page for scheduled jobs | /admin/v1/hooks/jobs, same In progress |

ALTER POLICY | /admin/v1 creates and drops policies; editing one is a drop and a create, or an alter policy in the SQL editor |

A project switcher | control.projects holds one row that every lookup resolves through, but nothing creates a second In progress |

Two-factor authentication | Not built. Rate limiting and the audit log are what there is. |

## The audit log

Every Studio sign-in, SQL execution, DDL statement, policy change, settings write
and import is written to control.audit_log — in the control database, so
the SQL editor cannot read it and an operator cannot quietly edit their own trail.

```bash
curl -s "$URL/admin/v1/logs?limit=100" -H "authorization: Bearer $ADMIN_TOKEN"
```

Read it under Studio → Settings. Import runs redact as they go: connection
strings, passwords and API keys are replaced in the audit row, in the
control.import_runs summary, in the progress stream and in error messages.

## Failure modes

What you see | Why | Fix |

The Studio loads as a blank page | The server is pointed at the Vite source rather than a built Studio | Put studio/dist next to dist/ — Installation |

Sign-in fails with an application user's credentials | Studio accounts are in another database entirely | Use the credentials setup.sh printed |

Every request is 401 a moment after signing in | JWT_SECRET changed since the token was issued | Sign in again |

403 on a page that used to work | The account is a viewer, or a custom role is missing a capability | GET /admin/v1/roles shows the matrix |

select * from control.platform_users fails | Correct and deliberate — it is in another database | Nothing. That is the boundary. |

The AI pages are missing | /ai/v1/status reports enabled: false | Set DEEPSEEK_API_KEY and restart |

A new table does not appear | The schema response is cached briefly; DDL through the Studio invalidates it | Refresh |

==============================================================================
# VS Code extension
URL: https://baselyra.sarimtools.com/docs/vscode.html
==============================================================================

# VS Code extension

The extension talks to a live instance over /admin/v1, /auth/v1 and /storage/v1 — the same API the Studio uses. There is no local index, no cached copy of your database and no service in between. It holds a service key, so read the first section before you connect.

## Read this before you connect

DANGER:
This extension holds a Baselyra service key. The service key runs every
request as service_role, which bypasses row level security on every table in the
project. It can read and write auth.users and storage.objects, and it can run
arbitrary SQL. It is the most dangerous credential Baselyra issues. Connect to
instances you administer; if you are only exploring, point it at staging.

What the extension does with it:

- Stores it in VS Code SecretStorage — the macOS Keychain, libsecret on
Linux, the Windows Credential Manager. Never in settings.json, never in a
workspace file, never in globalState, and never in a file it writes.

- Sends it only as an apikey / Authorization header to the instance URL
you entered.

- Never logs it. Every string on its way to the output channel, a notification or
an error message passes through a redaction step first — at every log level,
including debug.

- Never puts it in a webview. The SQL results panel receives its data over
postMessage after loading, so no server value and no credential is ever part
of that page's HTML.

- Keeps it out of the realtime URL. The WebSocket handshake is made by hand so
the credential travels in an Authorization header instead of the
?apikey= query string a browser would be forced to use — a query string ends
up in every reverse proxy access log on the way.

There is deliberately no command that reveals the service key.
Baselyra: Copy Anon Key copies the anon key, which is public and meant to
ship in your frontend.

## Install and build

```bash
cd vscode
npm install
npm run compile      # or npm run watch
npm test             # the wire-codec and type-mapping checks
```

Then press F5 to launch an Extension Development Host, or package it
with npx @vscode/vsce package. No runtime dependencies; two dev dependencies,
@types/vscode and typescript.

## Connecting

Run Baselyra: Connect to Instance. You are asked for the origin, shown the
warning above, and asked for the service key. Before anything is stored, two checks
run:

Check | What it proves |

GET /health | The URL is a Baselyra, and its database is up |

GET /admin/v1/keys | The key opens the admin API |

The anon key passes the first and fails the second with a 401, so pasting the
wrong one tells you immediately instead of half-working.

Several named profiles are supported. The status bar shows the active one; click
it to switch, add or verify. Baselyra: Disconnect forgets the key but keeps
the profile, so reconnecting is one command and one paste.

## Database explorer

Schemas grouped the way the server groups them — yours first and expanded,
auth and storage collapsed under a lock, baselyra and the catalogs not
returned at all. Tables carry a planner row estimate and an RLS indicator: a
table with row level security off is flagged in the description and drawn in the
warning colour, because that is the one mistake that ships a world-writable API.

Expand a table for its columns with types, primary keys, foreign keys and
defaults, and a Policies node listing every policy with its USING and
WITH CHECK expressions in the tooltip. Right-click for:

Command | Does |

View Rows | Opens the rows as a read-only JSON document |

Copy REST URL | The /rest/v1 URL with every column selected. Only public is exposed through REST, so it says so rather than handing you a URL that 404s. |

Generate Select Statement | A select … from … limit 100 at the cursor of the SQL file you are in, or a new one |

Edit Policies | Lists, creates through a guided flow, drops, or opens a definition as an alter policy statement |

Enable or Disable Realtime on Table | Toggles the NOTIFY trigger |

Drop Table | A modal naming the row count and size, then asks you to type the table's name. CASCADE is a separate button. |

## SQL

Cmd + Enter in a .sql file runs the selection, or the
whole file when nothing is selected. Results open beside the editor in a sortable
grid with CSV and JSON export.

A failure becomes a diagnostic on the offending token. Baselyra returns the
Postgres position with its errors, and the extension maps that offset back
through your selection into a range in the document, so the squiggle lands on the
word Postgres actually objected to. The SQLSTATE is the diagnostic's code and the
hint is in the message.

CAREFUL:
Everything you run through POST /admin/v1/sql is written to the instance's audit log before it executes, including the statement text. That is the server's behaviour, not the extension's, and it is worth knowing before you paste a credential into a query.

## Type generation

Baselyra: Generate TypeScript Types reads the live schema and writes a
Database type into the workspace, in the shape @baselyra/client expects.

```ts
import { createClient } from '@baselyra/client';
import type { Database } from './database.types';

const bl = createClient(url, ANON_KEY);
const { data } = await bl.from('posts').select('*');   // data: Post[] | null
```

Row, Insert and Update are emitted per table, with Insert marking
a column optional when it has a default, is an identity column, or is nullable.
Views get a Row. Only public is emitted, because only public is
reachable through /rest/v1.

DETAIL:
The types mirror what the JSON carries, not what Postgres stores: bigint and numeric are string, because Baselyra installs identity parsers for those oids so JSON stays exact; timestamps are ISO string; jsonb is Json.

Set baselyra.generateTypesOnSave to regenerate after saving a .sql file.
When the live schema drifts from what was generated, the extension offers to
regenerate — once per distinct schema, so declining is not asked again for the same
change.

## Storage and users

Storage shows buckets with their public/private flag, size limit and MIME
allowlist, browsed one prefix at a time the way the server lists them. Upload files
from the workspace — the content type is derived from the extension, which is what
the bucket's MIME allowlist is checked against — download, delete with a
confirmation, and copy a public or signed URL. Asking for a public URL on a private
bucket offers a signed one instead of handing you a link that 404s.

Users lists rows of auth.users — the application users of the app you
are building — with confirmation state, ban state and last sign-in, searchable by
email substring or exact id, paged with an explicit "load more". Copy an id, open
the full JSON, or delete with a confirmation.

NOTE:
Studio operators are not here and cannot be: they live in control.platform_users, in a different database that this connection cannot reach at all.

## Realtime inspector

Baselyra: Subscribe to Table picks a table — realtime-enabled ones first —
and streams postgres_changes, broadcast and presence frames into the
Baselyra Realtime output channel, with a status bar indicator and a live event
count. If the table has no NOTIFY trigger it says so and offers to add one,
because a subscription to a table without one silently emits nothing.

An optional filter (room_id=eq.42) is offered — a filter is a convenience, not
a security boundary. DELETE events are annotated in the log: a deleted row cannot
be re-read, so deletes are the one event delivered without an RLS re-check.

## Settings

Setting | Default | Effect |

baselyra.url | "" | Default URL offered when creating a profile |

baselyra.generateTypesOnSave | false | Regenerate types after saving a .sql file |

baselyra.typesPath | src/database.types.ts | Where the Database type is written |

baselyra.requestTimeout | 30000 | Milliseconds before a request is aborted |

baselyra.logLevel | info | off, error, info, debug |

baselyra.rowPageSize | 100 | Rows fetched when opening a table |

baselyra.signedUrlExpiry | 3600 | Seconds a signed storage URL stays valid |

There is no key setting, on purpose.

## Snippets

One snippet file, contributed to TypeScript, JavaScript, Dart and PHP, with
prefixes namespaced by dialect so the wrong language never wins a completion.

Prefix | For |

bl-client bl-select bl-embed bl-single bl-insert bl-update bl-upsert bl-delete bl-rpc | @baselyra/client queries |

bl-signin bl-signup bl-magiclink bl-authstate bl-signout | Auth |

bl-channel bl-presence | Realtime |

bl-upload bl-signedurl bl-publicurl bl-list | Storage |

bl-ai | The DeepSeek relay |

bl-policy | The four policies that make a table per-user private |

bl-dart-* · bl-php-* | The hand-rolled Dart and PHP clients |

## What it does not do

- No ALTER POLICY. /admin/v1 creates and drops policies; it has
no update.

- No table or column editing. Creating a table, adding a column and
altering a type are SQL, in the SQL editor. The extension will not grow a schema
designer that generates DDL you cannot see.

- No row editing. Rows open as a read-only JSON document. Writing a row
is an update you can read before you run it.

- No user creation or editing. The Users view lists, searches and deletes.

- One project per profile, because Baselyra has one project per
instance In progress.

- Realtime broadcast and presence are read-only here. The inspector
watches; it does not send.

## Failure modes

What you see | Why | Fix |

401 when verifying a new profile | You pasted the anon key, not the service key | Studio → Settings, or GET /admin/v1/keys |

/health passes and the second check fails | The URL is right and the credential is not | Same as above |

A table is missing from the tree | It is in a hidden schema, or the schema response is stale | Baselyra: Refresh |

Generated types do not match the API | The schema changed since generation | Regenerate; the extension offers to when it notices |

The realtime inspector shows nothing | The table has no trigger, or a proxy ate the Upgrade header | Accept the offer to add the trigger; then the 101 test |

A public URL command offers a signed one instead | That bucket is private | Expected — a public URL would 404 |

==============================================================================
# Self-hosting
URL: https://baselyra.sarimtools.com/docs/self-hosting.html
==============================================================================

# Self-hosting

Two containers, one .env, one reverse proxy. Installation covers getting it running; this page is everything after that — the proxy rule realtime depends on, TLS, what to back up, how to restore it, and what to watch.

## Sizing

Instance | Works? | Notes |

1 vCPU / 1 GB | Yes | Fine for development and a small production app. Add swap. |

2 vCPU / 2 GB | Comfortable | The sensible default for production. |

4 vCPU / 4 GB+ | Room to grow | Raise shared_buffers and DATABASE_POOL_MAX. |

At idle, expect roughly 250–400 MB for Postgres with the shipped
shared_buffers=256MB, and 80–150 MB for the Node process. Under load the Node
side grows with concurrent uploads and open WebSockets; Postgres grows with
work_mem times concurrent sorts. Disk is the Postgres volume plus whatever you
store in STORAGE_ROOT — both are Docker named volumes by default,
baselyra_db-data and baselyra_storage-data.

## Reverse proxy

Both shipped vhosts are in deploy/. Replace BASELYRA_DOMAIN and
BASELYRA_PORT in whichever you use.

DANGER:
The one rule. Baselyra's realtime endpoint is a WebSocket. A proxy that does
not forward the Upgrade handshake proxies it as plain HTTP, the handshake never
completes, and realtime silently does nothing while every other route works
perfectly. There is no error in any log. This is the most common self-hosting
complaint for a product of this shape, and it is always this.

nginx
Apache

```bash
map $http_upgrade $connection_upgrade {
    default upgrade;
    ''      close;
}

server {
    listen 80;
    server_name api.example.com;
    location /.well-known/acme-challenge/ { root /var/www/letsencrypt; }
    location / { return 301 https://$host$request_uri; }
}

server {
    listen 443 ssl;
    http2 on;
    server_name api.example.com;

    ssl_certificate     /etc/letsencrypt/live/api.example.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/api.example.com/privkey.pem;

    # Keep above STORAGE_MAX_FILE_BYTES or large uploads fail at the proxy.
    client_max_body_size 100m;
    proxy_read_timeout 300s;

    location / {
        proxy_pass http://127.0.0.1:3130;
        proxy_http_version 1.1;                       # 1.0 cannot upgrade
        # On every location, not just /realtime, so the upgrade works whatever
        # path a future version listens on.
        proxy_set_header Upgrade    $http_upgrade;
        proxy_set_header Connection $connection_upgrade;
        proxy_set_header Host              $host;
        proxy_set_header X-Real-IP         $remote_addr;
        proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_buffering off;                          # SSE arrives as it is produced
    }
}
```

```bash
sudo cp deploy/nginx-baselyra.conf /etc/nginx/sites-available/baselyra
sudo ln -s /etc/nginx/sites-available/baselyra /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
```

```bash

    ServerName api.example.com

    ProxyPreserveHost On
    RequestHeader set X-Forwarded-Proto "https"
    ProxyTimeout 300

    # MUST come before the catch-all ProxyPass below, or /realtime/v1 is
    # proxied as plain HTTP and the WebSocket upgrade never completes.
    RewriteEngine On
    RewriteCond %{HTTP:Upgrade} =websocket [NC]
    RewriteRule ^/?(.*) ws://127.0.0.1:3130/$1 [P,L]

    ProxyPass        / http://127.0.0.1:3130/
    ProxyPassReverse / http://127.0.0.1:3130/

    LimitRequestBody 0          # the app enforces its own upload limit

    SSLEngine on
    SSLCertificateFile    /etc/letsencrypt/live/api.example.com/fullchain.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/api.example.com/privkey.pem

```

```bash
sudo a2enmod proxy proxy_http proxy_wstunnel rewrite headers ssl
sudo cp deploy/apache-baselyra.conf /etc/apache2/sites-available/baselyra.conf
sudo a2ensite baselyra
sudo apachectl configtest && sudo systemctl reload apache2
```

### Verify the upgrade

```bash
curl -i -N \
  -H 'Connection: Upgrade' -H 'Upgrade: websocket' \
  -H 'Sec-WebSocket-Version: 13' -H 'Sec-WebSocket-Key: dGhlIHNhbXBsZSBub25jZQ==' \
  "https://api.example.com/realtime/v1?apikey=$ANON_KEY"
```

HTTP/1.1 101 Switching Protocols is a pass. Anything else — a 200 with
HTML, a 404, a 502 — means the proxy answered instead of upgrading.
Recheck the module list and the rule order.

### Two more proxy settings that matter

Setting | Why |

proxy_buffering off / no buffering on Apache | Two endpoints stream: the AI chat stream and the import progress stream. With buffering on, both arrive only once finished. The app already sends X-Accel-Buffering: no, but an explicit proxy_buffering on in your config wins. |

A body limit above STORAGE_MAX_FILE_BYTES | Otherwise a large upload fails at the proxy, with the proxy's own error page rather than Baselyra's 413. |

### Client IPs

The app runs with trustProxy on and reads X-Forwarded-For. Rate limiting
and auth.sessions.ip depend on it, so make sure your proxy sets it — both
shipped configs do.

DANGER:
Nothing untrusted may reach port 3130 directly. With trustProxy on, a client that can talk to the app port can spoof X-Forwarded-For and defeat every per-IP limit on the instance. The shipped compose file binds the port to 127.0.0.1 for exactly this reason. Keep it that way.

## TLS with certbot

```bash
sudo apt install certbot
sudo mkdir -p /var/www/letsencrypt
sudo certbot certonly --webroot -w /var/www/letsencrypt -d api.example.com
```

Both shipped vhosts already serve /.well-known/acme-challenge/ from
/var/www/letsencrypt on port 80 without redirecting it, which is what webroot
issuance needs.

```bash
# /etc/letsencrypt/renewal-hooks/deploy/reload-proxy.sh
#!/bin/sh
systemctl reload nginx      # or apache2
```

```bash
sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/reload-proxy.sh
sudo certbot renew --dry-run
```

After issuing, set BASELYRA_PUBLIC_URL=https://api.example.com and
docker compose up -d. Signed URLs and email links use that value; leaving it on
http:// produces mixed-content failures in the browser and links that do not
work.

## Email

DANGER:
With SMTP_HOST empty, nothing is sent — confirmation and recovery links
are printed to the app log instead. That is right for a laptop and never right in
production: your users cannot confirm an address or reset a password, and working
credentials accumulate in your log file.

```bash
SMTP_HOST=smtp.example.com
SMTP_PORT=587
SMTP_SECURE=false          # true only for implicit TLS on 465
SMTP_USER=…
SMTP_PASS=…
SMTP_FROM="Acme "
```

Test from Studio → Email → Send test, which uses your real templates
and reports the SMTP error verbatim if there is one. A relay that rejects a message
raises an error rather than falling back to the log — only an empty
SMTP_HOST skips sending.

## Backups

Three things, and the third is the cheap one.

- The project database (baselyra)
Your tables, your users, your bucket and object metadata. Losing it loses the product.

- The storage volume
The actual file bytes. The dump alone restores an instance whose storage.objects rows point at files that are gone — which looks like a working restore right up until someone opens an image.

- The control database (baselyra_control)
Studio accounts, the audit log, import history, request metering. Losing it costs you your Studio logins and your operator history, not your application: a restart with BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD set recreates an owner account.

```bash
BASELYRA_BACKUP_DIR=/var/backups/baselyra \
BASELYRA_BACKUP_KEEP_DAYS=14 \
  ./scripts/backup.sh
```

One run writes three files sharing one UTC stamp: db-<stamp>.dump,
control-<stamp>.dump and storage-<stamp>.tar.gz. Restore reads them as a
set, so keep them together.

Detail | Why |

pg_dump -Fc | The custom format: compressed, and restorable table-by-table with pg_restore, which a plain SQL file cannot do |

A tar.gz of the storage volume | Read through a throwaway container, so it does not matter where Docker put the volume |

Each file is written as .part and renamed on success | A half-written archive is never mistaken for a backup |

A zero-byte dump of either database fails the run loudly | The classic silent backup failure |

Files older than KEEP_DAYS are pruned | |

```bash
15 2 * * * cd /opt/baselyra && ./scripts/backup.sh >> /var/log/baselyra-backup.log 2>&1
```

CAREFUL:
Copy the directory off the machine. A backup on the same disk as the database is not a backup.

### Restore

```bash
./scripts/restore.sh /var/backups/baselyra/db-20260823T020000Z.dump \
                     /var/backups/baselyra/storage-20260823T020000Z.tar.gz
```

DANGER:
restore.sh is destructive: it stops the app, drops and recreates the databases it is restoring, replaces the storage volume, and starts the app. It asks you to type the project database name to confirm, and it refuses to drop anything until pg_restore --list has read every archive it was handed — a truncated dump passes a size check and then fails halfway, by which point the live database is already gone.

The control dump is found beside the project dump by name —
db-<stamp>.dump becomes control-<stamp>.dump — or passed as a third
argument. Both databases are then replaced together, which is what you want:
Studio accounts, the audit log and the project registry come back to the same
moment as the data they describe.

A backup taken before the control/project split has no control dump, and it is
still a legitimate restore: the Studio accounts are inside the project dump, in its
baselyra schema. restore.sh recognises that, restores only the project
database, and leaves the live control database alone — dropping it would throw away
accounts the dump predates — and the upgrade in scripts/migrate.js moves those
accounts across on the next boot. The one case it refuses is the ambiguous one: no
control dump beside the project dump and no Studio accounts inside it either, which
would leave an instance nobody can sign in to.

CAREFUL:
Rehearse a restore once on a scratch machine. A backup you have never restored is a hypothesis.

## Upgrading

```bash
cd /opt/baselyra
./scripts/backup.sh
git pull
docker compose up -d --build
docker compose logs -f app
./scripts/smoke.sh http://127.0.0.1:3130 admin@example.com 'password'
```

Migrations run automatically at boot, in filename order, each file inside one
transaction, tracked with a checksum in each database's own migrations table. A
migration that fails halfway leaves nothing behind, which is the only way an
unattended restart loop stays safe. Rolling back means restoring the backup —
there are no down-migrations.

Upgrading from a single-database instance is handled automatically and is safe to
run twice; the detail is in
the boot sequence.

## Operations

### Health

```bash
curl -s https://api.example.com/health
```

```json
{"status":"ok","service":"baselyra","version":"0.1.0","database":"up","time":"2026-08-24T09:14:02.481Z"}
```

database is up only when both pools answer. The container has its own
HEALTHCHECK hitting the same route, so docker compose ps shows
(healthy) or not.

### Logs

```bash
docker compose logs -f app
docker compose logs -f db
```

JSON lines from Fastify, rotated by Docker at 10 MB × 3 files.
Authorization, apikey and Cookie headers are redacted before anything is
written. LOG_LEVEL=debug for more; per-request logging is off in production by
default.

### The audit log

Every Studio sign-in, SQL execution, DDL statement, policy change and import is
written to control.audit_log — in the control database, so the SQL editor cannot
read it and an operator cannot quietly edit their own trail.

```bash
curl -s "$URL/admin/v1/logs?limit=100" -H "authorization: Bearer $ADMIN_TOKEN"
```

### Rotating secrets

DANGER:
Changing JWT_SECRET invalidates every access token, every refresh token, both project keys and every Studio session at once. Everyone signs in again and every client needs the new anon key. Do it deliberately — and it is the only way to revoke a leaked service key, because there is no per-key revocation list.

The Postgres password is in .env; changing it means changing it in Postgres
too (alter role baselyra password '…') and restarting both containers.

### Tuning

```bash
command:
  - postgres
  - -c
  - max_connections=200
  - -c
  - shared_buffers=256MB     # roughly 25% of RAM
  - -c
  - work_mem=8MB
```

```bash
DATABASE_POOL_MAX=12                 # per app process; stay well under max_connections
DATABASE_STATEMENT_TIMEOUT_MS=15000  # kills a runaway API query
STORAGE_MAX_FILE_BYTES=52428800      # keep the proxy's body limit above this
```

NOTE:
Baselyra does not set up, monitor or fail over replication, and no request path routes reads to a replica today In progress. One Postgres, backed up.

## Security checklist

- CORS_ORIGINS names your origins, not *.

- Port 3130 is bound to 127.0.0.1 and firewalled; Postgres publishes no port.

- .env is chmod 600 and not in version control.

- The service key is in no client bundle or public env var.

- Every table in public has RLS on and at least one policy —
the audit query.

- TLS is on and BASELYRA_PUBLIC_URL is https://.

- SMTP is configured, so recovery emails leave the machine.

- Backups run nightly, land off the machine, and have been restored once.

- GET /admin/v1/team lists only people who still work here.

- DATABASE_URL does not authenticate as a Postgres superuser — see
the hardening checklist, which
explains what the SQL editor can reach and why this one matters most.

## Failure modes

What you see | Why | Fix |

The app restarts in a loop | A missing required variable, or a failed migration | docker compose logs app — it prints the file and the character position |

Realtime dead in production, fine locally | The proxy is not forwarding Upgrade | The 101 test above |

Uploads fail at a few megabytes | The proxy's body limit is below STORAGE_MAX_FILE_BYTES | client_max_body_size, or LimitRequestBody 0 |

Streaming endpoints deliver everything at the end | Proxy buffering | proxy_buffering off |

Every request is 401 after a redeploy | JWT_SECRET changed — a new .env, or setup.sh on a fresh clone | Restore the old value, or re-issue keys |

Disk full | There are no storage quotas; a bucket can fill the disk | docker system df -v, then prune or move STORAGE_ROOT |

Rate limits trip for everyone at once | The app port is reachable directly and X-Forwarded-For is being spoofed, or every client shares one NAT | Bind to 127.0.0.1 and firewall the port |

More, with the exact messages: Troubleshooting.

==============================================================================
# Security
URL: https://baselyra.sarimtools.com/docs/security.html
==============================================================================

# Security

Read this alongside Row level security: that page is the authorisation mechanism, and this one is the context around it. The last section lists what Baselyra does not defend, because a security page that only lists strengths is marketing.

## The short version

- Postgres decides who may read a row. The server never filters for security.

- Two databases: the thing that grants access to the console does not live inside
the thing the console administers.

- The service key is a permanent superuser over your data. It belongs on a
server, never in a browser, a mobile app, or a repository.

- The SQL editor runs as a role that cannot reach the host. Your
DATABASE_URL login role probably still can —
see below.

## Trust boundaries

### Two databases

| Project database (baselyra) | Control database (baselyra_control) |

Holds | auth, storage, your public tables, this project's config | Studio accounts, audit log, import history, request metering, the project registry |

Reachable from | /rest/v1, /auth/v1, /storage/v1, /realtime/v1, the Studio's browser and SQL editor | Baselyra's own code, through a separate pool |

The service key can read it | Yes, entirely | No |

Postgres has no cross-database queries without FDW, so this is a boundary rather
than a convention: select * from control.platform_users in the SQL editor fails
with relation does not exist, and it would fail identically for anyone holding
the service key. The Studio's password hashes are not protected by a grant that
could be widened by mistake — they are in a database that connection cannot address
at all.

DETAIL:
GET /admin/v1/schema never returns the baselyra schema either: that is this project's own configuration, and the Studio has purpose-built pages for every table in it. auth and storage stay visible, grouped as System, because writing policies against auth.users is a legitimate thing to do.

### Three request roles

Every request that touches your data runs through asRole(), which opens a
transaction, switches the Postgres role, and publishes the caller's verified claims
as request.jwt.claims. Both settings are LOCAL, so the commit restores the
pooled connection and no identity survives into the next request.

Caller sent | Postgres role | RLS |

nothing, or the anon key | anon | enforced |

a user's access token | authenticated | enforced, and auth.uid() is that user |

the service key | service_role | bypassed (BYPASSRLS) |

An application-level admin is a claim, not a column:
(auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'. There is no
is_admin flag and no auth.is_admin() function — an earlier build had both,
and they conflated your customers with your operators.

### How the JWT is verified

HS256 on node:crypto, about forty lines. What matters is done explicitly:

- The algorithm is pinned. alg: "none" and any asymmetric algorithm
are rejected outright, which is what stops the classic algorithm-confusion forgery.

- The signature is compared in constant time, after a length check.

- exp and nbf are enforced, and an unknown role claim is refused.

- Nothing is taken from an unverified payload except the ref claim, and that
is used for exactly one thing: choosing which project's secret verifyJwt is
handed. A forged ref therefore selects a secret the token was not signed with,
and verification fails before a row is read.

## Keys, tokens and hashes

Credential | Stored or signed how | Lifetime | Revoked by |

anon key | JWT, HS256, JWT_SECRET | 10 years | rotating JWT_SECRET |

service key | JWT, HS256, JWT_SECRET | 10 years | rotating JWT_SECRET |

access token | JWT, HS256, JWT_SECRET | JWT_ACCESS_TTL (1h) | expiry only |

refresh token | 48 random bytes, stored as-is in auth.sessions | JWT_REFRESH_TTL (30d) | logout, rotation, or revoking the session row |

Studio token | JWT with typ: "platform" | 1 hour | expiry only |

email / recovery / OTP token | SHA-256 digest in auth.one_time_tokens, scoped to the address or number | per flow | single use |

Studio and user passwords | scrypt$N$r$p$salt$hash, N=16384 r=8 p=1 | — | — |

imported passwords | bcrypt$… verbatim, verified through pgcrypto and re-hashed to scrypt on first sign-in | — | — |

signed storage URL | HMAC-SHA256 over bucket, key and expiry, JWT_SECRET | as requested, 1s–7d | expiry only |

webhook delivery | HMAC-SHA256 over <timestamp>.<body>, per-webhook secret | — | rotating the secret |

Passwords are hashed, never encrypted-and-decryptable, and each hash carries the
parameters it was made with, so the cost can be raised later without invalidating
anything. A failed sign-in burns the same scrypt work as a successful one, so
timing does not reveal whether an address is registered.

DANGER:
JWT_SECRET is the root of almost all of this. Anyone holding it can mint a
service key, forge any access token, and sign any storage URL. It lives in
.env, in plaintext, chmod 600 — that is the honest state of secret storage
here.

### Where the service key must never appear

- Not in a browser bundle, a React/Vue/Svelte env var, a mobile app, or a desktop
app. Anything shipped to a user's device is public.

- Not in a repository, a CI log, a screenshot of the Studio, or a support ticket.

- Not in a VITE_, NEXT_PUBLIC_, EXPO_PUBLIC_ or equivalent
variable — those are compiled into the client by design.

- Not in a URL query string, where it lands in proxy and browser history logs.

The anon key is the one clients get. It is public by design and worthless without
policies that let it read something; it is also project-scoped, so presenting it to
another project on the same instance fails the signature check before a row is
read. If a service key leaks: rotate JWT_SECRET, restart, and re-issue the anon
key to your clients.

## RLS is the authorisation mechanism

No route handler checks ownership. There is no WHERE user_id = … added by the
server for security reasons. If a policy is wrong, the database says no; if a
policy is missing, the database says nothing at all.

DANGER:
A table in public with RLS off is readable and writable by anon — that
is, by anyone on the internet holding your public anon key — because the schema's
default grants give anon and authenticated DML and RLS is the only thing that
narrows it. This is the single most likely way to expose data with Baselyra.
The audit query lists every table in that
state.

Two things bypass policies on purpose: service_role, which has
BYPASSRLS, and the table owner, because RLS is enabled but not FORCEd —
that exemption is what lets the auth module manage sessions and tokens on the owner
connection. ALTER TABLE … FORCE ROW LEVEL SECURITY if you want the owner subject
to its own policies too.

Realtime obeys the same rule the hard way: before a change is delivered, the row
is re-read as that subscriber's role and dropped if RLS hides it. Broadcast and
presence channels never touch the database, so anything you put in them is visible
to every subscriber of that channel.

## The SQL editor

POST /admin/v1/sql and the Studio's table and policy endpoints run the
operator's statement as baselyra_sql, a NOLOGIN role the migrations create,
reached by a SET LOCAL ROLE inside the transaction and undone by the commit.

Before that change they ran on the server's own connection, as the role in
DATABASE_URL — which in the shipped docker-compose.yml is
POSTGRES_USER, the initdb superuser. That made the admin console a remote shell:
COPY … FROM PROGRAM runs a command as the postgres user and
pg_read_file() reads any file it can open. Neither is a bug in Postgres — they
are superuser features.

Statement | As baselyra_sql | Why |

copy t from program 'sh -c …' | refused | needs pg_execute_server_program or superuser |

select pg_read_file('/etc/shadow') | refused | needs pg_read_server_files or superuser |

select lo_export(…, '/some/path') | refused | needs pg_write_server_files or superuser |

alter system set … | refused | superuser, or a GRANT … ON PARAMETER that is never issued |

create extension plpython3u, file_fdw | refused | untrusted extensions are superuser-only |

create extension pgcrypto | allowed | trusted extension, plus CREATE on the database |

drop table public.posts | allowed | ownership rights, granted deliberately |

alter table auth.users … | allowed | same |

select encrypted_password from auth.users | allowed | BYPASSRLS, and the console is for this |

select * from control.platform_users | fails | different database |

The role deliberately keeps everything in the second half of that table. An admin
console is meant to be powerful inside its own database, and every one of those is
something the operator could do from psql anyway. What it loses is access to
the host, which was never part of the job.

DETAIL:
There is no statement blocklist, and adding one would be a mistake. A filter
that catches DROP TABLE but not a do $$ … $$ block assembling the same string
is a Postgres parser written badly; its only real effect is the confidence to expose
the endpoint more widely. The role is enforced by Postgres against the current user,
which no amount of string manipulation in the request body can change. The last
block of db/project/006_sql_role.sql re-checks the invariant on every boot: if
baselyra_sql is ever a superuser, or inherits one of the host-access roles, the
migration aborts and the container does not start.

DANGER:
What this does not fix. If DATABASE_URL authenticates as a superuser — the
default in the shipped compose file — then a Studio operator who deliberately types
RESET ROLE; before their statement is a superuser again, because the session's
authenticated user still is one. SET ROLE narrows what a statement does by
default; it is not a jail. Closing that means not being a superuser in the first
place: see the checklist below.

POST /ai/v1/ask is the one path left where SQL the operator did not write
reaches the database on the server's own connection. It is admin-only, wrapped in a
READ ONLY transaction with a statement timeout, and rolled back either way — but
a read-only transaction does not stop pg_read_file(). Leave
DEEPSEEK_API_KEY unset if that trade is wrong for you.

## Rate limiting and lockout

Layer | Limit |

Globally | 300 requests per minute per IP across the whole API, answering 429 |

POST /admin/v1/login | 5 per minute — the one route that turns a password into a token holding service_role, callable with no credential |

/auth/v1/signup | 10 per minute |

/auth/v1/token, /verify | 30 per minute |

The email and OTP flows | 5 per minute |

Sign-in attempts | auth.attempts records every failure; three counters are read from one scan — email+IP (AUTH_MAX_ATTEMPTS, 8), the email alone (×4) and the IP alone (×10) |

SMS sends | A 60-second cooldown per number and a hard cap of 5 per number per hour, because every message is a charge on the operator's account |

The email+IP pair is the documented key; the wider two exist because rotating
either half bypasses it, and they sit far enough above the threshold that a shared
office NAT does not trip them. The reply is a 429 with the seconds to wait;
nothing is locked permanently, so no one can lock a competitor out of their own
account.

Enumeration: /signup, /recover, /magiclink, /otp and
/resend return the same status, the same body and comparable timing whether or
not the address exists — a 350 ms response floor absorbs the difference a database
round trip would otherwise reveal.

## What is logged, and what is redacted

- control.audit_log records every Studio action: actor, action, target, IP,
timestamp and a meta object. It is in the control database, so the SQL editor
cannot read or edit it.

- The SQL editor writes its audit row before the statement runs and on a
separate connection, so a statement that fails or rolls back is still recorded.
That row contains the statement text and its bound parameters: do not type a
password or an API key into the editor and expect it to be forgotten.

- Import runs redact as they go — connection strings, passwords and API keys are
replaced in the audit row, in the control.import_runs summary, in the progress
stream and in error messages, by key name and by pattern, because a driver
error quoting the connection string it failed on is the usual way one escapes.

- Request metering (control.request_stats) stores a route pattern, a method, a
status class and timings. No bodies, no query strings, no identities.

- Application logs are Fastify's. Request bodies are not logged;
authorization, apikey and cookie headers are redacted at every level.

- Emails are printed to the log instead of sent when SMTP_HOST is empty —
which means recovery links in your log file.

## Hardening checklist

- DATABASE_URL does not authenticate as a Postgres superuser

This is the one change that turns the SQL editor's role switch from a sensible
default into an actual boundary.

```sql
create role baselyra_app login password '…' createrole createdb;
alter database baselyra          owner to baselyra_app;
alter database baselyra_control  owner to baselyra_app;
```

The existing objects still belong to the old role, and the tidiest way to hand
them over is a restore: take a backup, then pg_restore --no-owner each dump into
an empty database connected as baselyra_app, which makes it the owner of
everything it creates. Point DATABASE_URL at it and restart;
scripts/migrate.js re-applies the grants for the new owner. Keep the superuser
credentials for the day you need them, out of .env.

- JWT_SECRET is at least 32 random bytes and was never committed anywhere
scripts/setup.sh generates one.

- .env is chmod 600 and outside version control

- CORS_ORIGINS names your origins
* means any page on the internet can call the API with a user's token in the browser that holds it.

- The app port is bound to 127.0.0.1, TLS terminates in your proxy, and BASELYRA_PUBLIC_URL is https://

- Postgres publishes no port

- Every table in public has RLS enabled and at least one policy

- BASELYRA_ADMIN_EMAIL and BASELYRA_ADMIN_PASSWORD are cleared from .env
After the first sign-in. Left in place they are a second known credential.

- GET /admin/v1/team lists only people who still work here, and viewers are viewers
Remember that viewer holds keys.read, so every Studio account can read the service key.

- The service key appears in no client bundle
Grep your frontend for it.

- Backups run, land off the machine, and have been restored once
Including the control database, or nobody can sign in to the restored instance.

- SMTP is configured
So recovery emails leave the machine instead of landing in the log.

- docker compose pull && docker compose up -d --build on a schedule
The Postgres and Node base images are where most CVEs will reach you.

## What is not protected

Stated plainly.

Gap | Detail |

A superuser DATABASE_URL | Leaves the SQL editor one RESET ROLE from the host. This is the default in the shipped compose file. |

Studio tokens cannot be revoked | They are stateless and last an hour. Signing out writes an audit row; deleting a team member stops the next login. A stolen token works until it expires. |

Refresh tokens are stored as issued | Not hashed. Whoever reads auth.sessions — a dump, a backup, the service key — can resume those sessions until they expire or rotate. One-time email tokens are stored as SHA-256 digests; refresh tokens are not. |

The Studio keeps its token in localStorage | No cookie means no CSRF surface, and it also means any script that runs on the Studio's origin can read it. Do not serve untrusted content from that origin. |

GET /admin/v1/keys returns the service key | To any Studio session, including a viewer. That is what the Connect panel is for, and it means a Studio account is effectively a service-key holder. |

The Studio's row grid runs as service_role | An RLS-filtered admin grid would quietly lie about what a table contains, so it does not filter. |

No two-factor authentication | On Studio accounts, and no password policy beyond a minimum length. |

Nothing is encrypted at rest | Not the database, not the storage volume, not .env. Use full-disk encryption on the host if you need it. |

Public buckets are public | Any object in one is readable by URL with no token, forever, by anyone who learns the path. |

Storage paths are checked, quotas are not | .. and absolute paths are refused; there is no per-bucket total size limit, so a bucket can fill the disk. |

Outbound requests are yours to trust | Webhook targets are checked against private address ranges before delivery and refused unless you opt out deliberately; OAuth providers, SMTP and DeepSeek are whatever you configured. |

No WAF, no bot detection, no DDoS protection | The rate limits above are per-IP counters in one process, not a defence against a botnet. Put a CDN or a proxy in front if that is your threat model. |

A compromised host is a total loss | The database password, JWT_SECRET and every uploaded file are on it. Nothing here is designed to survive root on the VPS. |

## Reporting something

Found something worse than the above? Do not open a public issue with a working
exploit in it. Send it to whoever runs the instance you are looking at, and to the
maintainers privately.

==============================================================================
# Troubleshooting
URL: https://baselyra.sarimtools.com/docs/troubleshooting.html
==============================================================================

# Troubleshooting

Every entry here is a real symptom with a real cause. Start with the four at the top: between them they account for most of the time anyone loses with Baselyra.

## My query returns no rows

You call a table you can see in the Studio, and get this:

```bash
curl -s "$URL/rest/v1/notes?select=id,title" -H "apikey: $ANON_KEY"
```

```json
[]
```

Almost always row level security, and it is working correctly. A policy
denial is not an error — it is an empty array with a 200, because whether a row
exists is itself information a policy can withhold.

- Check whether the table has policies at all

```sql
select c.relname as table,
       c.relrowsecurity as rls_enabled,
       count(p.polname) as policies
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  left join pg_catalog.pg_policy p on p.polrelid = c.oid
 where n.nspname = 'public' and c.relkind in ('r','p')
 group by 1, 2
 order by rls_enabled, 1;
```

RLS on and zero policies means the table denies everything to anon and
authenticated. That is the safe default, not a bug — write a policy.

- Check which role you were

An apikey alone is anon. A policy written for select to authenticated
admits nothing to an anonymous caller. Send the user's access token as
authorization: Bearer … as well.

- Reproduce it as that user, in SQL

```sql
begin;
  select set_config('role', 'authenticated', true);
  select set_config('request.jwt.claims',
    '{"sub":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","role":"authenticated"}', true);
  select * from public.notes;      -- exactly what that user sees over REST
rollback;
```

The Studio's grid runs with BYPASSRLS, so it shows the whole table whatever
your policies say. This block is the only honest test.

If instead you see | It is not RLS |

403 insufficient_privilege | A missing table grant. grant select, insert, update, delete on public.t to anon, authenticated; |

404 undefined_table | The table is not in public, or the catalog snapshot is up to a minute stale |

400 unknown column | A typo in select or a filter |

## Realtime connects but nothing ever arrives

The socket opens, subscribe() resolves SUBSCRIBED, and no event ever
comes. There are exactly two causes.

### 1. The table has no trigger

Change feeds are opt-in per table.

```sql
select * from baselyra.realtime_tables;
select baselyra.enable_realtime('public.messages');
```

### 2. The proxy is eating the Upgrade header

DANGER:
This is the single most common self-hosting failure for a product of this shape.
The proxy did not forward the Upgrade handshake, so the WebSocket was proxied as
plain HTTP: every other route works perfectly, realtime does nothing, and there
is no error in any log.

One command settles it. A 101 is the whole test:

```bash
curl -i -N \
  -H 'Connection: Upgrade' -H 'Upgrade: websocket' \
  -H 'Sec-WebSocket-Version: 13' -H 'Sec-WebSocket-Key: dGhlIHNhbXBsZSBub25jZQ==' \
  "https://api.example.com/realtime/v1?apikey=$ANON_KEY"
```

Response | Means |

HTTP/1.1 101 Switching Protocols | The path is clear |

200 with HTML | The proxy answered instead of upgrading |

404 or 502 | The proxy is not routing that path to Baselyra at all |

The working configurations are in deploy/. On Apache the rewrite
must come before the catch-all ProxyPass, or the WebSocket is proxied as
plain HTTP:

```bash
RewriteEngine On
RewriteCond %{HTTP:Upgrade} =websocket [NC]
RewriteRule ^/?(.*) ws://127.0.0.1:3130/$1 [P,L]

ProxyPass        / http://127.0.0.1:3130/
ProxyPassReverse / http://127.0.0.1:3130/
ProxyTimeout 300
```

```bash
sudo a2enmod proxy proxy_http proxy_wstunnel rewrite headers ssl
```

On nginx, proxy_http_version 1.1 and both Upgrade headers, on every
location:

```bash
map $http_upgrade $connection_upgrade { default upgrade; '' close; }

location / {
    proxy_pass http://127.0.0.1:3130;
    proxy_http_version 1.1;
    proxy_set_header Upgrade    $http_upgrade;
    proxy_set_header Connection $connection_upgrade;
    proxy_read_timeout 300s;
    proxy_buffering off;
}
```

Related symptom | Cause |

Events stop after a few minutes | A proxy idle timeout below the heartbeat interval. Raise proxy_read_timeout / ProxyTimeout to 300s. |

Events for some rows only | RLS hid the rest, or your filter excluded them. Not a transport problem. |

Deletes arrive that a user should not see | Deletes are the one event not RLS-checked — the row is gone and cannot be re-read. Soft-delete instead. |

The socket closes with 1008 | The token was rejected: expired, or JWT_SECRET changed. |

Every message arrives twice in React | A channel outlived a remount. Create it inside the effect and remove it in the cleanup. |

## No email ever arrives

Sign-up succeeds, the confirmation never comes, and nothing looks broken.

DANGER:
With SMTP_HOST empty — which is the default — nothing is sent. The message, including the link, is printed to the app log instead. That is deliberate for a laptop and exactly wrong in production.

```bash
docker compose logs app | grep '\[mail\]'
```

```bash
[mail] SMTP not configured, would have sent to
[mail]   subject: Confirm your email address
[mail]   link:    http://127.0.0.1:3130/auth/v1/verify?token=8Yb…&type=confirmation
```

Open that link in development. For production, configure a relay and restart:

```bash
SMTP_HOST=smtp.example.com
SMTP_PORT=587
SMTP_SECURE=false          # true only for implicit TLS on 465
SMTP_USER=…
SMTP_PASS=…
SMTP_FROM="Acme "
```

```bash
docker compose up -d
```

Then Studio → Email → Send test, which uses your real templates and
reports the SMTP error verbatim.

Symptom | Cause |

SMTP is configured and still nothing | A configured relay that rejects a message raises an error rather than falling back to the log. Look for [auth] … email failed in the log. |

Mail sends but lands in spam | SPF, DKIM and DMARC for the SMTP_FROM domain. Nothing Baselyra can do. |

/recover answers 200 for an address that does not exist | Deliberate. Those endpoints answer identically either way so they cannot be used to enumerate accounts. |

Nothing is sent on a re-signup for a confirmed address | A confirmation link would be a working credential minted on an unauthenticated request. The address owner gets a notice pointing at sign-in instead. |

## The Studio will not let me in

You created a user, the sign-up worked, and /login refuses those credentials.

DANGER:
A Studio account is not an application user. They are two unrelated tables in
two unrelated databases. No row in auth.users can ever open the Studio, however
it is configured — Studio tokens carry a typ: "platform" claim inside their
signature, and exactly one endpoint mints it. There is no flag to set and no column
to flip.

| Studio account | Application user |

Table | control.platform_users | auth.users |

Database | baselyra_control | baselyra |

Created by | scripts/migrate.js from BASELYRA_ADMIN_EMAIL/PASSWORD, or by an owner | POST /auth/v1/signup, or the service key |

Signs in at | POST /admin/v1/login | POST /auth/v1/token |

Use the credentials ./scripts/setup.sh printed on the first boot. If they are
lost:

```bash
# put a new bootstrap password in .env and restart — migrate.js only sets the
# password when it CREATES the account, so first remove or rename the old row.
docker compose exec db psql -U baselyra -d baselyra_control \
  -c "select id, email, role_slug from control.platform_users;"
```

Then either delete that row and restart with BASELYRA_ADMIN_EMAIL and
BASELYRA_ADMIN_PASSWORD set, or ask another owner to reset it from
Studio → Settings → Team.

Symptom | Cause |

select * from control.platform_users fails in the SQL editor | Correct and deliberate. It is in another database, and Postgres has no cross-database queries without FDW. |

429 on the login page | POST /admin/v1/login is capped at 5 per minute per IP. Wait. |

Signed in, then every request is 401 | JWT_SECRET changed since the token was issued, or the hour is up. |

403 on a page that used to work | The account is a viewer, or a custom role is missing a capability. GET /admin/v1/roles shows the matrix. |

A second owner appears after a restart | BASELYRA_ADMIN_EMAIL was left in .env after you changed your address in the Studio. |

The Studio is a blank page | The server is pointed at the Vite source rather than a built Studio. |

## An import says a table already exists

```json
{"error":{"code":"import_rejected",
  "message":"public.posts already holds 4812 rows — deselect it, or choose replace mode",
  "details":null}}
```

This is the guard, not a fault. mode: "create" — the default — refuses to
write into a target table that already holds rows, and names the table.

You want | Do |

To keep what is there | Deselect that table from selection.tables and run the rest |

To overwrite it | Set "mode": "replace", which truncates the target first |

To import into a clean namespace | Set "schema": "imported" and copy across afterwards |

DANGER:
replace truncates. The refusal is the only thing standing between "import into an empty database" and "silently destroy the data already here" — prefer create and deselect.

Other import failures | Cause and fix |

Could not read the source: … | Credentials or the network. Run POST /admin/v1/import/test first — it is cheap and reports the source's version. |

The stream stops with no done event | A proxy timeout below the 15-second heartbeat. Raise proxy_read_timeout / ProxyTimeout. |

Rows copied, but the API answers 404 for the table | The catalog cache. A run invalidates it on success; otherwise it refreshes within a minute. |

Imported users cannot sign in | Appwrite and Firebase hashes cannot be carried. Send recovery emails — the inspect warnings said so before the run. |

A table imported with RLS on and no policies | The source's policies did not translate. Deliberate: a table nobody can read is a bug you find in a minute. Read the warnings and write the policies. |

A pooler connection string times out mid-copy | A server-side cursor needs a session. Use the direct connection string, not the transaction-mode pooler. |

403 on /admin/v1/import/* | The Studio account is a viewer; import needs import.run. |

## The instance will not start

Log line | Cause and fix |

Missing required env var: DATABASE_URL | Or JWT_SECRET. Both are required; the process refuses to start without them. |

CONTROL_DATABASE_URL must name a different database than DATABASE_URL | Both point at one database. Unset CONTROL_DATABASE_URL and let it default. |

[migrate] … database not ready, thirty times | Postgres never came up. docker compose logs db — usually a volume permission problem. |

the SQL console role baselyra_sql does not exist | The migrations were applied without scripts/migrate.js, which creates that role once before applying the directory. Run node scripts/migrate.js. |

baselyra_sql is a superuser: the SQL console would be a shell on this host | Someone granted it. alter role baselyra_sql nosuperuser and restart. The migration aborts on purpose. |

A migration fails with a file and a character position | Read the statement at that position. Every file in db/ is idempotent; an edited file is re-run, so an unguarded create table fails on the second boot. |

## HTTP status quick reference

Status | Most likely cause |

400 | A malformed filter, an unknown column or operator, a constraint violation, or a write with no filter |

401 | The token expired (one hour), JWT_SECRET changed, or a refresh token was replayed and the whole chain was revoked |

403 | A missing table grant, a Studio capability you do not hold, or a storage policy refusing a write. Never an RLS row denial. |

404 | No such table in public, a stale catalog snapshot, a storage object your policies hide, or a public URL on a private bucket |

406 | A singular response was asked for and the row count was not 1 |

408 | Over DATABASE_STATEMENT_TIMEOUT_MS (15s). Index the columns your filters and policies use. |

409 | A unique or foreign key violation, or deleting a bucket that still holds objects |

413 | Over STORAGE_MAX_FILE_BYTES or the bucket limit — or your proxy's body limit, in which case the error page is the proxy's |

429 | 300 requests per minute per IP globally, or a per-account sign-in lockout with Retry-After |

503 | An /ai/v1 route with no DEEPSEEK_API_KEY |

## Things that look broken and are not

Behaviour | Why |

bigint and numeric arrive as strings | Deliberate — a JavaScript number cannot hold either exactly, and money must not round |

Signing up with an existing address returns a 200 and a user you cannot sign in as | A decoy, so the endpoint cannot be used to test whether an address is registered. The real owner gets an email. |

/recover and /magiclink always succeed | Same reason, plus a 350 ms response floor so timing does not reveal it either |

A public image does not change after a re-upload | Public objects are served immutable with a year's max-age. Add a cache-busting query parameter. |

An unfiltered DELETE is refused | One forgotten query parameter otherwise empties the table, and RLS does not save you. Prefer: unsafe-mutation is the opt-out. |

A viewer cannot read the import history | A connection string is a credential, so the whole import area needs import.run |

The realtime feed goes quiet for up to 30 seconds after a database restart | The listener reconnects with exponential backoff and jitter |

## Collecting evidence

```bash
curl -s https://api.example.com/health                  # is it up, are both databases up
docker compose ps                                       # healthy?
docker compose logs --tail=200 app
docker compose logs --tail=200 db
docker compose exec db psql -U baselyra -d baselyra -c '\dt public.*'
docker compose exec db psql -U baselyra -d baselyra_control -c 'select count(*) from control.platform_users;'
```

Then the end-to-end check, which drives auth, REST, storage, realtime, the admin
API and the Studio the way a real client would:

```bash
./scripts/smoke.sh http://127.0.0.1:3130 admin@example.com 'password'
```

CAREFUL:
Redact before you paste anything into an issue. A log line can carry a connection string, and the audit log carries the text of every statement run in the SQL editor.

==============================================================================
# FAQ
URL: https://baselyra.sarimtools.com/docs/faq.html
==============================================================================

# FAQ

Short answers, with a link to the page that goes into it properly. If your question is a symptom rather than a question, Troubleshooting is the other page.

## What it is

### Is Baselyra a Supabase fork?

No. It shares the idea — Postgres with row level security behind an
auto-generated REST API — and the REST dialect is a PostgREST-compatible subset so
client patterns transfer. None of the code is shared. Baselyra is one Node process
next to one Postgres container, with eight runtime npm dependencies and no Kong,
GoTrue, PostgREST, Realtime, Storage, imgproxy or Vector.

### How does it compare to Supabase and Appwrite?

| Baselyra | Supabase (self-hosted) | Appwrite (self-hosted) |

Containers | 2 | ~12–15 | ~10–20 |

Idle memory | ~400–600 MB | ~2–4 GB | ~2–3 GB |

Fits a 1 GB VPS | Yes | No | No |

Database | Postgres 17 | Postgres | MariaDB (+ Redis) |

Authorisation model | Postgres RLS | Postgres RLS | Per-document permission strings |

Upgrade | docker compose pull && up -d | Coordinated across services | Coordinated across services |

Where they win, plainly: Supabase has edge functions, read replicas, connection
pooling at scale, branching and a hosted tier. Appwrite has server-side functions
in many runtimes, messaging and a mature mobile SDK story. Baselyra has none of
that. It has the parts most small projects actually use, in a footprint one person
can run and reason about, and the source of one process to read when something goes
wrong. If you need what they have, they are the honest recommendation.

### Is it production ready?

It runs in production, it has an end-to-end smoke test, and the failure modes are
documented rather than hidden — including
the ones that are not fixed. What
it does not have is replication, failover, or an S3 backend: one Postgres, one
disk, and backups. Decide with that in front of you.

### What licence?

Apache-2.0, with no contributor licence agreement, no open-core split and no
paid tier. The whole thing is in the repository.

### Is there telemetry?

No. Baselyra makes no outbound request you did not configure. Request metering is
counted into the control database on your own machine and never leaves it. The only
outbound calls the code can make at all are to your SMTP relay, your SMS provider,
an OAuth provider you configured, a webhook target you created, and DeepSeek if you
set a key.

## Features

### Does Baselyra support Google or GitHub sign-in?

Yes. Google, GitHub, LinkedIn, Facebook, Instagram, TikTok and Envato are built
in; a provider appears only when both its client id and client secret are
configured. There is no Apple sign-in. Phone one-time codes over SMS are also built
in, with Twilio, Vonage, MessageBird, Amazon SNS, Plivo or a generic webhook
sender. See Third-party sign-in and
Phone codes.

### Does Baselyra have edge functions?

No. Postgres functions called over POST /rest/v1/rpc/:fn are what there is,
and they run as the caller so row level security still applies. Edge functions are
in progress and not shipped In progress.

### Are webhooks and scheduled jobs built?

Yes — database webhooks fired by a row change, signed with an HMAC and retried
with backoff, and cron-scheduled jobs, both running in-process with their queue in
the project database under /admin/v1/hooks/*. The Studio has no page for
either yet, so they are configured through the API.

### Can I store files on S3?

No In progress. Files live on the local disk of the host running the app, under
STORAGE_ROOT, with their metadata in storage.objects so the same policies
guard them as guard a table. Back that volume up alongside the database. There is
also no image transformation: files come back exactly as they were uploaded.

### Can I run more than one project on one instance?

Not today. control.projects holds a single row that every project lookup
resolves through, so adding a switcher later is a change to one resolver, but
nothing creates a second project and the Studio has no switcher In progress. Running
two projects means running two stacks.

### Does it do read replicas?

No. Configuration exists for listing standbys and the registry will probe their
lag, but no request path routes reads to a replica today In progress — every
query goes to the primary. Baselyra does not set up, monitor or fail over
replication in any case.

### How much memory does Baselyra need?

A 1 GB VPS is enough to start, and 2 GB is comfortable for production. At idle
expect roughly 250 to 400 MB for Postgres with the shipped shared_buffers of
256 MB, and 80 to 150 MB for the Node process. Under load the Node side grows with
concurrent uploads and open WebSockets.

## Security and keys

### Is the anon key a secret?

No. It is public and meant to ship in your frontend. It only names which Postgres
role a request runs as; the policies decide what that role may read and write. The
service key is the opposite — it bypasses every policy and belongs on a server
only, never in a bundle, a mobile app or a NEXT_PUBLIC_* variable.

### Why does my query return an empty array?

Almost always row level security, and it is working correctly. A policy denial is
a 200 with an empty array, never a 403, because whether a row exists is
itself information a policy can withhold. A table with RLS enabled and no policies
denies everything to anon and authenticated, which is the safe default.
The three checks settle
it in a minute.

### What happens if I forget to turn RLS on?

DANGER:
That table is readable, writable and deletable by anyone holding your anon key — a public string in your frontend. New tables in public receive default grants for anon and authenticated, and row level security is the only thing that narrows them. Run the audit query now.

### How do I give a user an admin role?

Put it in their app_metadata, which only the service key can write, and read
it in a policy. There is no is_admin column and no auth.is_admin()
function — both were removed, because they conflated your customers with your
operators.

```sql
using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin')
```

### Can a user of my app get into the Studio?

No, and there is no configuration that would let them. Studio accounts live in
control.platform_users, in a different database, and their tokens carry a
typ: "platform" claim inside the signature that exactly one endpoint mints.
Postgres has no cross-database queries without FDW, so a project connection cannot
even read a Studio password hash while holding the service key.

### What happens if I rotate JWT_SECRET?

Every access token, every refresh token, both project keys and every Studio
session become invalid at once. Everyone signs in again and every client needs the
new anon key. It is also the only way to revoke a leaked service key, because there
is no per-key revocation list — so it is a deliberate act, not a routine one.

## Using it

### Can I move an existing project onto Baselyra?

Yes, from Supabase, plain Postgres, Appwrite, Firebase or a pg_dump file.
Supabase and Postgres bcrypt password hashes come across intact, so those users
never have to reset a password. Appwrite hashes with argon2 and Firebase uses a
keyed scrypt, so accounts from those two arrive with an unusable password and must
go through recovery. See Importing.

### Can I just use psql?

Yes, and you should when it is the right tool. It is your Postgres: the tables are
ordinary tables, the policies are ordinary policies, and nothing in Baselyra
requires you to go through its API. Two things to remember — DDL run outside
/admin/v1 is invisible to the REST catalog for up to a minute, and the
baselyra and control schemas are Baselyra's own bookkeeping rather than
yours.

### Do I need the JavaScript client?

No. It is a convenience over an API that is plain HTTP with JSON and three
headers. There are complete hand-rolled clients for Dart,
PHP and Python in these docs, and
anything that can make an HTTP request can talk to Baselyra.

### Why do bigint and numeric come back as strings?

Because a JavaScript number cannot hold either exactly. Baselyra installs
identity parsers for those two Postgres oids so the JSON is exact — a
numeric(12,2) of 1250.00 stays "1250.00" rather than becoming a float
that might not. Parse it where you need arithmetic.

### Why is an unfiltered DELETE refused?

Because one forgotten query parameter in client code otherwise empties the table,
and row level security does not save you — the policy allows those rows, the client
simply asked for all of them. If you really mean every row, send
Prefer: unsafe-mutation, or call .unsafeMutation() in the client.

### How do I do a join?

Embed one level of related resource through a foreign key, or write a Postgres
function and call it over RPC when you need more:

```bash
?select=id,title,author:users(id,name),comments(id,body)
```

Only one level is supported, deliberately — the second is where the query plan
stops being predictable. Embedded
resources.

### Can I use my own Postgres?

Yes. Point DATABASE_URL at it. The role needs to be able to
CREATE DATABASE (Baselyra creates the control database), CREATE ROLE — two
of them with BYPASSRLS — and CREATE EXTENSION for pgcrypto,
citext and pg_trgm. Ideally it is not a superuser; see
the hardening checklist.

### How do I get help?

Read the error's code first — it is stable and every page here lists the ones
it can produce. Then Troubleshooting. Then the
repository's issues. Redact before you paste: a log line can carry a connection
string, and the audit log carries the text of every statement run in the SQL
editor.