Authentication Prerequisites

Everything you need to set up before building the authentication system. This guide combines all database tables, secrets configuration, and security controls from every authentication tutorial into a single reference.

Overview

The authentication system consists of five core workflows:

Workflow	Endpoint	Purpose
Registration	`POST /register`	Create new user accounts with email verification
Email Verification	`POST /verify`	Verify user emails via hashed token comparison
Login	`POST /login`	Authenticate users, issue JWT tokens, support OTP/TOTP
Forgotten Password	`POST /forgotten-password`	Request reset links and reset passwords
Change Password	`POST /change-password`	Authenticated password change with JWT

Each workflow shares the same users table and follows the same security patterns: schema validation, email normalization, hashed tokens, rate limiting, and generic error messages.

Database Tables

Table: `users`

The central table used by all five workflows. Every column listed below is required across the full system.

Column	Type	Used By	Description
`email`	text	All workflows	User's email address (unique). Always stored lowercase and trimmed.
`password`	text	Register, Login, Forgotten Password, Change Password	Bcrypt hash of the user's password. Never store plaintext.
`name`	text	Register, Login	User's display name.
`verified`	text	Register, Verify, Login	`"yes"` or `"no"`. Login rejects unverified accounts.
`verification_token`	text	Register, Verify	SHA-256 hash of the raw verification token sent in the email. Cleared after successful verification.
`token_expires_at`	datetime	Register, Verify	When the verification token expires (24 hours after registration). Cleared after verification.
`verified_at`	datetime	Verify	Timestamp of when the account was verified. Set during verification.
`otp_enabled`	text	Login	`"yes"` or empty. When enabled, login returns an OTP challenge instead of a JWT.
`totp_enabled`	text	Login	`"yes"` or empty. When enabled, login returns a TOTP challenge instead of a JWT.
`reset_token`	text	Forgotten Password	SHA-256 hash of the password reset token. Cleared after successful reset.
`reset_token_expires_at`	datetime	Forgotten Password	When the reset token expires (1 hour after request). Cleared after reset.

Why each column exists

email - The unique identifier for every user. Normalized to lowercase to prevent duplicate accounts from case variations (User@Example.com vs user@example.com).
password - Stored as a bcrypt hash (cost factor 10, ~80-100ms to verify). Bcrypt's intentional slowness makes brute-force attacks computationally expensive.
name - Display name returned in login responses. Not used for authentication.
verified - Gates login access. Users cannot log in until verified = "yes". This prevents account takeover via unverified email addresses.
verification_token - Only the SHA-256 hash is stored. The raw token is sent in the verification email. If the database is compromised, attackers can't use hashed tokens to verify accounts.
token_expires_at - 24-hour window. Long enough for users to check email, short enough to limit exposure if the email is compromised later.
verified_at - Audit trail. Useful for analytics and support investigations.
otp_enabled / totp_enabled - Multi-factor authentication flags. The login workflow checks these before issuing a JWT and redirects to the appropriate verification flow.
reset_token - Same SHA-256 hashing pattern as verification_token. Cleared after use to prevent token reuse.
reset_token_expires_at - 1-hour window (shorter than verification's 24h because reset tokens are higher risk - they grant password change access).

Table: `verification_attempts`

Tracks rate limiting for the email verification workflow. Prevents brute-force token guessing.

Column	Type	Description
`email`	text	The email being verified (unique key for upsert)
`attempt_count`	number	Number of verification attempts in the current 15-minute window
`last_attempt_at`	datetime	Timestamp of the most recent attempt

Rate limit: 5 attempts per 15 minutes per email. Counter resets on successful verification.

Table: `login_attempts`

Tracks brute-force protection for the login workflow. Locks accounts after repeated failures.

Column	Type	Description
`email`	text	The email that was attempted (unique key for upsert)
`attempts`	text	Number of consecutive failed attempts (stored as string)
`locked_until`	text	Millisecond timestamp when lockout expires (empty = not locked)
`last_attempt`	text	Millisecond timestamp of last failed attempt

Lockout: 5 failed attempts → 15-minute lockout. Counter resets on successful login.

Table: `password_reset_attempts`

Tracks rate limiting for the forgotten password workflow. Separate limits for request and reset actions.

Column	Type	Description
`email`	text	The email requesting reset (unique key for upsert)
`attempt_count`	number	Number of attempts in the current 15-minute window
`last_attempt_at`	datetime	Timestamp of the most recent attempt

Rate limits: 3 requests per 15 minutes (request action), 5 attempts per 15 minutes (reset action). Counter resets on successful password reset.

Vault Secrets

`my_jwt_secret`

The Login and Change Password workflows require a JWT signing key stored in the Vault.

Key	Used By
`my_jwt_secret`	Login, Change Password

Setup:

Go to Settings → Vault in your workspace
Click + Add Secret
Set the key name to my_jwt_secret and the value to a strong random string
In node fields, reference it using {{secrets.my_jwt_secret}}
In Code Execution nodes, access it via variables.secrets.my_jwt_secret

The JWT is signed with this secret using HS256. Tokens include sub (email), iss ("ubex"), iat, and exp (1 hour) claims.

Password Requirements

All workflows that accept passwords (Register, Forgotten Password reset, Change Password) enforce the same strength rules:

Rule	Regex Check
Minimum 8 characters	`password.length < 8`
At least one uppercase letter	`/[A-Z]/`
At least one lowercase letter	`/[a-z]/`
At least one number	`/[0-9]/`
At least one special character	`/[!@#$%^&*()_+-=[]{};':"\

Token Hashing Pattern

Every token in the system follows the same pattern:

Generate a raw token (UUID with dashes removed)
Hash it with SHA-256 before storing in the database
Send the raw token to the user (via email link)
When the user submits the token, hash it again and compare against the stored hash

This means a database breach never exposes usable tokens. The same principle applies to both verification tokens and password reset tokens.

Rate Limiting Summary

Workflow	Global Limit	Per-Email Limit	Lockout
Registration	10/min	—	—
Email Verification	10/min	5 per 15 min	—
Login	30/min	—	5 failures → 15 min lock
Forgotten Password (request)	10/min	3 per 15 min	—
Forgotten Password (reset)	10/min	5 per 15 min	—
Change Password	10/min	—	—

Combined Security Checklist

Input Validation

Control	Register	Verify	Login	Forgotten Password	Change Password
POST only (no GET)	✅	✅	✅	✅	✅
Schema validation (Data Validator)	✅	✅	✅	✅	✅
Email format validation (regex)	✅	✅	✅	✅	—
Email normalization (lowercase, trim)	✅	✅	✅	✅	—
Token format validation	—	✅	—	—	—

Password Security

Control	Register	Verify	Login	Forgotten Password	Change Password
Password strength enforcement	✅	—	—	✅	✅
Password hashed with bcrypt	✅	—	—	✅	✅
Password verified with bcrypt	—	—	✅	—	✅
Same password prevention	—	—	—	—	✅
Current password required	—	—	—	—	✅

Token Security

Control	Register	Verify	Login	Forgotten Password	Change Password
Token hashed (SHA-256) before storage	✅	✅	—	✅	—
Token expiration enforced	✅	✅	—	✅	—
Token cleared on success	—	✅	—	✅	—
JWT with expiry (1 hour)	—	—	✅	—	—
JWT claims (sub, iss, iat, exp)	—	—	✅	—	—
JWT authentication required	—	—	—	—	✅

Brute-Force Protection

Control	Register	Verify	Login	Forgotten Password	Change Password
Global rate limiting	✅	✅	✅	✅	✅
Per-email rate limiting	—	✅	—	✅	—
Account lockout	—	—	✅	—	—
Rate limit counter reset on success	—	✅	✅	✅	—

Data Protection

Control	Register	Verify	Login	Forgotten Password	Change Password
Generic error messages (no enumeration)	✅	✅	✅	✅	✅
Duplicate email check	✅	—	—	—	—
Already-verified guard	—	✅	—	—	—
Triple WHERE on update	—	✅	—	✅	—
Audit logging	—	✅	—	✅	—

Infrastructure

Control	Register	Verify	Login	Forgotten Password	Change Password
CORS configuration	✅	✅	✅	✅	✅
Security headers (HSTS, X-Frame-Options)	✅	✅	—	—	—
Reduced timeout	✅	✅	—	✅	✅
No real secrets in tutorial JSON	✅	✅	✅	✅	✅

Authentication Prerequisites

Authentication Prerequisites

Overview

Database Tables

Table: users

Why each column exists

Table: verification_attempts

Table: login_attempts

Table: password_reset_attempts

Vault Secrets

my_jwt_secret

Password Requirements

Token Hashing Pattern

Rate Limiting Summary

Combined Security Checklist

Input Validation

Password Security

Token Security

Brute-Force Protection

Data Protection

Infrastructure

Table: `users`

Table: `verification_attempts`

Table: `login_attempts`

Table: `password_reset_attempts`

`my_jwt_secret`