Session auth

PerSQL ships first-class auth without you running a session server. Define a users table on your database, add an auth_signup and auth_login endpoint, and gate mutations with auth: "session".

The users table contract

Your table must have at least:

Column	Type	Notes
(primary key)	INTEGER / TEXT	Surfaced as $user_id in session-bound endpoints
email (or whatever you name in authConfig.emailColumn)	TEXT	Must be unique
password_hash	TEXT	Exactly this name. Stores pbkdf2_sha256$<iters>$<salt_b64>$<hash_b64>

Migration sketch:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Signup

{
  "slug": "signup",
  "name": "Sign up",
  "kind": "auth_signup",
  "method": "POST",
  "sql": "INSERT INTO users (email, password_hash) VALUES (?, ?) RETURNING id",
  "input": [
    { "name": "email", "type": "email", "required": true },
    { "name": "password", "type": "text", "required": true, "maxLength": 200 }
  ],
  "output": "session",
  "authConfig": {
    "usersTable": "users",
    "emailColumn": "email",
    "passwordColumn": "password_hash",
    "sessionTtlSec": 86400
  }
}

The runtime:

1. Validates the input.
2. Hashes password with PBKDF2-SHA256 (210k iterations, 16-byte salt).
3. Binds email and the hash into the INSERT.
4. Reads the new row’s PK from RETURNING.
5. Mints an HS256 JWT with claims { sub: <pk>, email, iat, exp }.
6. Returns { token, expiresAt }.

The signing key is per-database, generated lazily on first auth call, and stored in D1 (database.session_signing_key_b64).

Login

{
  "slug": "login",
  "name": "Log in",
  "kind": "auth_login",
  "method": "POST",
  "sql": "SELECT id, password_hash FROM users WHERE email = ?",
  "input": [
    { "name": "email", "type": "email", "required": true },
    { "name": "password", "type": "text", "required": true }
  ],
  "output": "session",
  "authConfig": {
    "usersTable": "users",
    "emailColumn": "email",
    "passwordColumn": "password_hash"
  }
}

Runtime: looks up by email, compares the password against password_hash (constant-time), mints a fresh JWT.

sessionTtlSec defaults to 24 hours; max 7 days.

Using the session

The client sends the token on subsequent calls:

POST /tasks/create
Authorization: Bearer <jwt>
Content-Type: application/json

{ "title": "buy milk" }

Endpoints with auth: "session":

• Reject the call with 401 if the bearer is missing, malformed, expired, or signed with a different key.
• Populate $user_id, $user_email, $session_iat from the verified claims before binding ?.

So the mutation doesn’t need to trust client input for ownership:

{
  "kind": "mutation",
  "auth": "session",
  "sql": "UPDATE tasks SET title = ? WHERE id = ? AND user_id = ?",
  "input": [
    { "name": "title", "type": "text", "required": true },
    { "name": "task_id", "type": "integer", "required": true },
    { "name": "$user_id", "type": "integer" }
  ],
  "output": "rows_written"
}

Password hashing

pbkdf2_sha256$210000$<salt_b64>$<hash_b64> — same format Django / passlib use, so existing tooling works for offline migration. The 210k-iter cost is set in @persql/endpoint-runtime and applied to every signup.

When to roll your own

The built-in auth covers email + password. For OAuth, magic links, or multi-factor, write a server that owns the auth ceremony and hits PerSQL’s /v1 API for the data side — then mint your own JWT or issue a session cookie from your own app.