# Patterns

Canonical recipes for apps built on `@supabase/lite`. Pair with [LIMITATIONS.md](https://github.com/supabase-community/lite/blob/HEAD/LIMITATIONS.md) (what to avoid) and [STATUS.md](https://github.com/supabase-community/lite/blob/HEAD/STATUS.md) (full reference).

This file is the authoritative source — the bundled [`supalite` skill](https://github.com/supabase-community/lite/blob/HEAD/skills/supalite/SKILL.md) points agents here, so updates land for every consumer on the next `npm install`.

## Per-user multi-tenant ("each user sees only their own X")

Most-common shape. Works the same on SQLite, PGlite, and Postgres.

Schema:

```sql
create table <thing> (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  -- domain columns
  created_at timestamptz not null default now()
);

alter table <thing> enable row level security;

create policy "select own" on <thing> for select to authenticated using (auth.uid() = user_id);
create policy "insert own" on <thing> for insert to authenticated with check (auth.uid() = user_id);
create policy "update own" on <thing> for update to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "delete own" on <thing> for delete to authenticated using (auth.uid() = user_id);
```

The client supplies `user_id` on insert (SQLite RLS evaluates `WITH CHECK` against supplied values; there is no `DEFAULT auth.uid()` on SQLite — see [LIMITATIONS.md](https://github.com/supabase-community/lite/blob/HEAD/LIMITATIONS.md#sql--ddl-sqlite-path)):

```ts
const { data: { session } } = await supabase.auth.getSession();
await supabase.from("<thing>").insert({
  user_id: session.user.id,
  // ...
});
```

## Verifying RLS policies locally

Local admin mode is on by default (`lite dev`, `lite start`, Vite dev server), and a request with **no** credential runs as `service_role` — so a bare `curl` sees every row and proves nothing about your policies. Always test with a credential; those requests are never elevated and behave exactly as they will in production.

```bash
# anon: what a logged-out visitor sees
curl -H "apikey: $SUPABASE_PUBLISHABLE_KEY" \
  "http://localhost:54321/rest/v1/<thing>?select=*"

# authenticated: the apikey is required IN ADDITION to the user JWT
JWT=$(curl -s -H "apikey: $SUPABASE_PUBLISHABLE_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"email":"a@b.co","password":"secret123"}' \
  "http://localhost:54321/auth/v1/token?grant_type=password" | jq -r .access_token)

curl -H "apikey: $SUPABASE_PUBLISHABLE_KEY" -H "Authorization: Bearer $JWT" \
  "http://localhost:54321/rest/v1/<thing>?select=*"
```

`Authorization` alone is a 401 — opaque keys are only read from `apikey`, matching upstream. supabase-js sends both automatically, so app code needs no special handling. To take admin mode out of the picture entirely, start with `--no-admin` (or `supalite({ admin: false })`) and every request will require a key.

## Filtering an embedded resource (SQLite path)

Dotted-path filters (`.eq('rel.col', v)`) are not supported on SQLite. Two options:

```ts
// Not supported on SQLite:
// supabase.from("trips").select("*, days(*)").eq("days.city", "Paris")

// Option A — filter on FK column after pre-resolving ids:
const { data: dayMatches } = await supabase.from("days").select("trip_id").eq("city", "Paris");
const tripIds = dayMatches.map(d => d.trip_id);
const { data } = await supabase.from("trips").select("*, days(*)").in("id", tripIds);

// Option B — flatten the query and group in JS.
```

Native on Postgres / PGlite.

## Custom server logic without `rpc()`

`rpc()` is not implemented on the SQLite path. Three options, in order of preference:

1. Express the logic as a SQL view or trigger in `schemas/schema.sql`.
2. Switch the driver to `pglite` or `postgres` in `supabase/config.toml`. `rpc()` works there.
3. Run a regular server endpoint (Hono / Express / Next route handler / Vite middleware) and call it directly from the client.

## Vite + supalite cold start

The canonical Vite recipe:

1. `bun add @supabase/lite @supabase/supabase-js`
2. `vite.config.ts`:
   ```ts
   import { defineConfig } from "vite";
   import { supalite } from "@supabase/lite/vite";
   export default defineConfig({ plugins: [supalite()] });
   ```
3. `bunx lite init` to scaffold `supabase/`.
4. Write Postgres DDL in `supabase/schemas/schema.sql` (RLS enabled).
5. `src/lib/supabase.ts`:
   ```ts
   import { createClient } from "@supabase/supabase-js";
   export const supabase = createClient(window.location.origin, "<sb_publishable_...>");
   ```
6. `bun run dev`.

Same-process, same origin, hot-reload on schema changes. Do **not** run `lite dev` alongside.

## `updated_at` timestamps via trigger

```sql
create or replace function set_updated_at() returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger set_<thing>_updated_at
before update on <thing>
for each row execute function set_updated_at();
```

The translator inlines `NEW.updated_at = now()` into a SQLite `CREATE TRIGGER` body. See [STATUS.md#plpgsql-trigger-functions](https://github.com/supabase-community/lite/blob/HEAD/STATUS.md#plpgsql-trigger-functions) for the supported subset.

## Profiles row on signup (`handle_new_user`)

```sql
create or replace function handle_new_user() returns trigger language plpgsql as $$
begin
  insert into public.profiles (id, email) values (new.id, new.email);
  return new;
end;
$$;

create trigger on_auth_user_created
after insert on auth.users
for each row execute function handle_new_user();
```

Supported on all backends.