User-owned app databases

Some apps are better when the user owns their data — a notes app, a journal, a personal tracker. PerSQL’s database scope lets your app provision a private database in the user’s own PerSQL account instead of yours. You pay for the storage and queries; the user owns the data, can inspect or export it any time, and can disconnect your app to cut off access. No backend, no sync server.

It’s OAuth 2.1 Authorization Code + PKCE — the same flow as “Sign in with PerSQL” — with one extra scope.

1. Register your app

In the console: OAuth apps → New app. Choose Public (PKCE, no secret) for a mobile or single-page app. Add an HTTPS redirect URI (for native apps, an App Link / Universal Link or the Expo AuthSession proxy; http://localhost is allowed for local development).

You get a client_id like psqlrp_s00_…. Keep your namespace’s prepaid balance funded — that’s the balance the app’s usage draws from (see Funding below).

2. Send the user through the flow

The SDK does the OAuth 2.1 + PKCE handshake for you — no need to hand-roll the challenge. Request the scope database (add openid for an ID token in the same round-trip).

import { PerSQL } from "@persql/sdk";

const req = await PerSQL.beginConnect({
  clientId: CLIENT_ID,
  redirectUri: REDIRECT_URI,
  scope: "openid database",
});
// Open req.url in a browser / in-app browser / Expo AuthSession.
// Keep req.codeVerifier and req.state until the redirect comes back.

The user signs in to PerSQL (GitHub, Google, or email — one screen, and they can sign up right there) and sees a consent screen explaining that your app gets a private database in their account and nothing else.

Web Crypto powers the PKCE step. It’s built in everywhere except bare React Native — there, import "react-native-get-random-values" and a Web Crypto SHA-256 shim, or run step 3 on your server.

3. Exchange the code for a database token

When the redirect returns, verify state matches, then exchange the code:

const persql = await PerSQL.completeConnect({
  clientId: CLIENT_ID,
  redirectUri: REDIRECT_URI,
  code,                          // from the redirect query string
  codeVerifier: req.codeVerifier,
});

persql.grant.database;   // "ns-slug/notes", scoped to this user's database
persql.grant.accessToken; // a psql_live_… token — store it in the keychain
persql.grant.idToken;     // present only if you asked for `openid`

The token reaches exactly one database — this user’s — so a stolen token’s blast radius is the same as a session cookie, not your whole account. On a server-side (confidential) client, pass clientSecret too.

4. Use it

completeConnect hands back a ready client:

const db = persql.database(persql.grant.database);

await db.query(
  "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT, updated_at TEXT)"
);
await db.query("INSERT INTO notes (body, updated_at) VALUES (?, ?)", [
  "first note",
  new Date().toISOString(),
]);

On a later launch you already have the stored token — skip the OAuth dance and go straight to the data client:

const db = new PerSQL({ token: storedToken, baseURL: "https://api.persql.com" })
  .database(storedDatabaseRef);

The token is admin-scoped to that one database, so your app manages its own schema and migrations — but it can’t touch anything else in the user’s account.

Re-running the flow for the same user reuses the same database (their data persists) and rotates the token — so a reinstall just re-authorizes and finds everything where it was. The (user, app) pair is the durable link.

Funding: billing follows the caller

Billing routes by who makes the call, not by where the database lives:

• Your app’s queries and the database’s storage bill your namespace’s prepaid balance while the app is connected — so a notes user (~10 MB, a few thousand reads/writes a month) costs you a few cents per month. Keep your balance funded in the console; if it runs dry, your app’s calls get a 402.
• The user’s own direct queries (opening the database in the PerSQL console or with their own token) bill their balance, not yours.
• On disconnect, storage reverts to the user — you stop paying for a database you no longer have access to, and their data stays put.

The database lives in the user’s own namespace the whole time; you never hold their data, only a token scoped to that one database.

What the user sees

Under Connected apps in the console, the user sees every app holding a database in their account, can open it to inspect or export exactly what you stored, and can disconnect your app — which revokes its token but keeps their data, restored if they reconnect.

Cloud-first by design

This is a hosted database the app reaches over HTTPS — which is the point. For a notes app, a journal, a tracker, the data is durable from the first write: it survives a lost or wiped device, restores on reinstall, and the user can open it from anywhere. On-device-only storage is the liability you’re removing, not a feature you’re giving up.

A couple of practical notes:

• Redirect URIs are HTTPS or loopback only. Custom URL schemes are rejected (they’re phishable on Android). Use an App Link / Universal Link, or the Expo AuthSession proxy.
• The user controls access, you hold a scoped token. The data lives in the user’s account; they can inspect, export, or disconnect at any time. You manage the schema and queries — so design for that, the same as any server-backed app.

Next step: db.describe() to hand your agent the schema, or wire the same token into db.asTools() so an assistant can query the user’s data with their permission.