# Build an app on a team Database

A team Database has an HTTP API. A browser or a server reads and writes its tables with no server code of your own.
The API follows PostgREST, so `supabase-js` works for reads and writes. A sign-in service gives your app's people
their own accounts. A live channel sends the rows again when they change.

- The database decides what a request may do. A table has no access for the web until you grant it, and row-level
  security chooses the rows.
- A request runs as a role, the role of its key or token. The roles are `anon` (a visitor), `authenticated` (a person
  who signed in) and `service_role` (your server; it passes row-level security).
- The address of everything below is `https://gizstack.com/db/TEAM/NAME`. `TEAM` is the slug of your team and `NAME`
  the name of the Database.

## Step 1: Take the keys

Open the Database page and find **Data API**. Writers of the team see three keys there.

| Key | Use it for |
| --- | --- |
| `anon` | The browser. Safe to publish: it grants nothing until you do. |
| `service` | Your server only. It reads and writes every table, past every policy. |
| signing key | Your server only, if you make your own tokens. Sign-in (Step 4) makes them for you. |

## Step 2: Give a table to the web

A new table is closed. Grant what a visitor may do, then say which rows with a policy.

```sql
CREATE TABLE notes (id bigint PRIMARY KEY, owner text, body text);
GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO authenticated;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY own ON notes
  USING (owner = current_setting('request.jwt.claim.sub', true));
```

Each claim of the token is a setting named `request.jwt.claim.NAME`. A person's id is the claim `sub`.

## Step 3: Read and write

With `supabase-js`:

```js
import { createClient } from "@supabase/supabase-js";
const db = createClient("https://gizstack.com/db/TEAM/NAME", ANON_KEY);

const { data } = await db.from("notes").select("id, body").order("id");
await db.from("notes").insert({ id: 1, owner: "me", body: "hello" });
await db.from("notes").update({ body: "changed" }).eq("id", 1);
await db.from("notes").delete().eq("id", 1);
```

With HTTP:

```sh
curl "https://gizstack.com/db/TEAM/NAME/rest/v1/notes?select=id,body&order=id" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"
```

### What the API understands

| You want | Write |
| --- | --- |
| Some columns | `select=id,body` |
| A filter | `column=op.value` with `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `like`, `ilike`, `is`, `in` (`id=in.(1,2)`) |
| Not | `body=not.like.*draft*` |
| Or, and | `or=(id.eq.1,body.eq.hello)`, `and=(...)`; they nest: `or=(a.eq.1,and(b.gt.2,c.is.null))` |
| An order | `order=id.desc.nullslast` |
| A page | `limit=10&offset=20` (at most 1000 rows) |
| One object | Header `Accept: application/vnd.pgrst.object+json` (`.single()`): the answer is 406 when the rows are not exactly one |
| A count | Header `Prefer: count=exact` (`{ count: "exact" }`): the total is in `Content-Range` |
| No rows back | Header `Prefer: return=minimal` |
| An upsert | `POST` with `Prefer: resolution=merge-duplicates` or `ignore-duplicates`, at `on_conflict=COLUMNS` (else the primary key) |
| A function | `POST /rest/v1/rpc/NAME` with the arguments as a JSON object (`.rpc()`) |

Values are always parameters of the statement. No text of a request becomes SQL.

An error has the shape `{ "code", "message", "details", "hint" }`. `code` is the SQLSTATE of the database when it
gave the error. The status is 401 for a missing or bad key, 403 when a grant or policy refuses, 406 for one object that
is not one, and 400 for the rest.

### Not supported

- Embedded resources (`select=id,author(name)`): read the tables with two requests, or make a view.
- The array and text-search filters (`cs`, `cd`, `ov`, `fts`).

## Step 4: Sign people in

The service is at `/db/TEAM/NAME/auth/v1`, with the shapes of GoTrue. People are rows of your own Database (schema
`auth`), not accounts of the console.

```js
const { data } = await db.auth.signUp({ email: "ann@example.com", password: "correct horse" });
await db.auth.signInWithPassword({ email: "ann@example.com", password: "correct horse" });
const { data: { user } } = await db.auth.getUser();
await db.auth.signOut();
```

- A session holds an access token (valid one hour) and a refresh token. Use the refresh token once: it gives a new
  pair. `supabase-js` does this for you.
- The access token is the role `authenticated` with `sub` set to the person's id, so the policy of Step 2 works with no
  more code. `supabase-js` sends it with every request after sign-in.
- A password has 8 to 128 characters. It is kept as a salted PBKDF2-SHA-256 hash.
- After 10 wrong passwords for one email in 15 minutes, the next sign-in answers 429 (`over_request_rate_limit`) until the
  window passes. A right password clears the count.
- Sign-up needs no confirmation mail. Magic links, OAuth and multi-factor sign-in are not part of the service.

## Step 5: Follow changes live

Open one WebSocket to `/db/TEAM/NAME/live`. The channel is the same one every GizWeb page uses, so one connection
carries any number of tables. Browsers cannot set headers on a WebSocket: give the key as `?apikey=`.

```js
const live = new WebSocket("wss://gizstack.com/db/TEAM/NAME/live?apikey=" + TOKEN);
live.onopen = () => live.send(JSON.stringify({
  v: "", s: [["notes?select=id,body&order=id", "", ""]],
}));
live.onmessage = (e) => {
  const m = JSON.parse(e.data);
  if (m.r) return console.error("refused", m.r);   // 401 or 403: that topic ends
  render(m.o[0].rows);                              // the rows as this person may see them
};
```

- A topic is `TABLE?QUERY`, the query of a read above. Ask for several in the list `s`.
- The first message holds the rows now. Each change to the Database sends the rows again, read again as the same
  person, so row-level security decides what each person sees.
- A change made through the Data API or the Databases page tells the channel. A write over the PostgreSQL connection
  does not yet.
- A lost socket comes back by itself in the GizWeb kernel. In your own client, open it again and send the same message.

## Where it is different from Supabase

| | GizStack |
| --- | --- |
| Address | `/db/TEAM/NAME` instead of `PROJECT.supabase.co` |
| Realtime | One WebSocket with the GizWeb messages above, not the Phoenix protocol: `.channel().on("postgres_changes")` does not connect |
| Storage and Functions | Not at this address: use GizStack Storage (S3) and apps |

See also: [Use GizDB](/docs/guides/data), [Move to GizStack](/docs/guides/migrate).