Part of the Sanchayam series.

Invite-Only Signup

Sanchayam is a personal finance tool. There is no public registration. New users are added by invitation from an existing user. The first user is created via a /setup endpoint that is only active when the database has no users.

An invitation is a row in the invitations table:

CREATE TABLE invitations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  token_hash  VARCHAR NOT NULL UNIQUE,
  label       VARCHAR,
  email       VARCHAR,
  expires_at  TIMESTAMP NOT NULL,
  used_at     TIMESTAMP,
  created_by  UUID NOT NULL REFERENCES users(id),
  created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

The token itself is never stored - only its SHA-256 hash. The invitation link sent by email contains the raw token. When the signup form is submitted, the backend hashes the submitted token and looks up the hash. If the row exists, is not yet used, and has not expired, signup proceeds and used_at is set. The raw token cannot be reconstructed from the hash, so a database leak does not expose valid invitation links.

email is optional on the invitation. If set, the signup form pre-fills it and the backend validates that the submitted email matches. If not set, any email can use the link.

OTP Password Reset

Password reset uses a one-time PIN sent to the user’s email, not a reset link. Links can be forwarded. A numeric OTP with a short expiry and an attempt limit cannot.

The flow:

  1. User submits their email to POST /auth/forgot-password
  2. Backend generates a 6-digit OTP, hashes it, stores the hash with a 10-minute expiry in password_reset_otps
  3. OTP is emailed. The raw OTP is not stored anywhere
  4. User submits email + OTP to POST /auth/verify-otp
  5. Backend hashes the submitted OTP and checks against the stored hash
  6. On match, issues a short-lived reset token (separate from the session JWT) and marks the OTP as used
  7. User submits new password + reset token to POST /auth/reset-password

Rate Limiting OTP Attempts

Two separate rate limit tables guard the OTP flow:

-- migration 002
CREATE TABLE forgot_password_log (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email        VARCHAR NOT NULL,
  attempted_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE forgot_password_lockouts (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      VARCHAR NOT NULL UNIQUE,
  locked_until TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- migration 003
CREATE TABLE otp_verify_log (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email        VARCHAR NOT NULL,
  attempted_at TIMESTAMP NOT NULL DEFAULT NOW()
);

forgot_password_log tracks how many reset requests an email has made in a window. Too many triggers a lockout row. otp_verify_log tracks how many OTP verification attempts an email has made - too many wrong guesses locks the account out of further attempts. Both tables are purged nightly after 90 days.

Sessions and Access Tokens

On login, the backend issues two tokens:

  • Access token: short-lived JWT (15 minutes), signed with JWT_SECRET, returned in the response body. Stored in memory on the frontend.
  • Refresh token: long-lived (30 days), signed with a separate secret, set as an httpOnly cookie. The raw token is also stored hashed in the sessions table.

The access token carries the user’s ID and a standard iat claim. The force_logout table is checked on every authenticated request:

CREATE TABLE force_logout (
  user_id       UUID PRIMARY KEY REFERENCES users(id),
  logged_out_at TIMESTAMP NOT NULL DEFAULT NOW()
);

If a row exists for the user and token.iat < logged_out_at, the token is rejected even if it has not expired. This invalidates all access tokens for a user across all devices instantly - no need to wait for the 15-minute expiry.

Refresh Token Rotation with Reuse Detection

When the access token expires, the frontend sends the refresh token cookie to POST /auth/refresh. The backend:

  1. Verifies the JWT signature
  2. Hashes the token and looks it up in sessions
  3. Checks the hash against used_refresh_tokens
CREATE TABLE used_refresh_tokens (
  token_hash VARCHAR   PRIMARY KEY,
  used_at    TIMESTAMP NOT NULL DEFAULT NOW()
);

If the hash appears in used_refresh_tokens, the token has already been rotated. This means the refresh token was used twice - either a replay attack or a stolen token. The response is immediate force logout: a row is inserted into force_logout with the current timestamp, invalidating every session for this user.

If the token is valid and not reused:

  1. The old session row is deleted
  2. A new refresh token is generated and a new session row is inserted
  3. The old token hash is inserted into used_refresh_tokens
  4. New access token and new refresh cookie are returned

Every refresh cycle produces a completely new token pair. A stolen refresh token can only be used once - the moment it is used, the legitimate user’s next refresh attempt will detect the collision and force a logout.

used_refresh_tokens is purged nightly after 60 days. Tokens expire in 30 days, so any token older than 60 days cannot be valid regardless.

Next: FX Service: USD Pivot, Two-Layer Cache, Stale-While-Revalidate