Documentation Index
Fetch the complete documentation index at: https://mintlify.com/ashcroft08/provesa-web/llms.txt
Use this file to discover all available pages before exploring further.
The authentication system uses four core tables to manage users, sessions, OAuth accounts, and email verification. All tables use Drizzle ORM with PostgreSQL.
User Table
Stores user profile information and credentials.
Primary key. Unique identifier for the user.
User’s email address. Must be unique.Constraints:
.unique() - No duplicate emails allowed
emailVerified
boolean
default:false
required
Whether the user’s email has been verified.
URL to user’s profile image/avatar.
When the user account was created.Default: defaultNow()
Last time the user record was updated.Default: defaultNow()
Auto-update: Updates automatically on any change
Relationships
- sessions - One-to-many relationship with
session table
- accounts - One-to-many relationship with
account table
Example Schema Usage
import { user } from './schemas/auth.schema';
import { db } from './db';
// Create a new user
await db.insert(user).values({
id: 'usr_abc123',
name: 'Juan Pérez',
email: 'juan@example.com',
emailVerified: false
});
// Query user by email
const result = await db.select().from(user).where(eq(user.email, 'juan@example.com'));
Session Table
Manages active user sessions with token-based authentication.
Primary key. Unique session identifier.
When this session expires and should be invalidated.
Session token used for authentication.Constraints:
.unique() - Each token must be unique
When the session was created.Default: defaultNow()
Last activity timestamp for this session.Auto-update: Updates on any change
IP address from which the session was created.
Browser/client user agent string.
Foreign key reference to the user who owns this session.Constraints:
- References
user.id
onDelete: 'cascade' - Deletes session when user is deleted
Indexes:
session_userId_idx - Indexed for fast user session lookups
Relationships
- user - Many-to-one relationship with
user table
Example Usage
import { session } from './schemas/auth.schema';
import { db } from './db';
// Create a new session
await db.insert(session).values({
id: 'sess_xyz789',
userId: 'usr_abc123',
token: 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...',
expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000), // 7 days
ipAddress: '192.168.1.1',
userAgent: 'Mozilla/5.0...'
});
// Find all sessions for a user
const userSessions = await db
.select()
.from(session)
.where(eq(session.userId, 'usr_abc123'));
Account Table
Stores OAuth provider accounts and credentials linked to users.
Primary key. Unique account identifier.
Account ID from the OAuth provider.
OAuth provider identifier (e.g., ‘google’, ‘github’, ‘credentials’).
Foreign key to the user who owns this account.Constraints:
- References
user.id
onDelete: 'cascade' - Deletes account when user is deleted
Indexes:
account_userId_idx - Indexed for performance
OAuth access token from the provider.
OAuth refresh token for renewing access tokens.
When the access token expires.
When the refresh token expires.
OAuth scopes granted for this account.
Hashed password for credentials-based authentication.Always hash passwords before storing. Never store plaintext passwords.
When the account was linked.Default: defaultNow()
Last time the account was updated.Auto-update: Updates automatically
Relationships
- user - Many-to-one relationship with
user table
Example Usage
import { account } from './schemas/auth.schema';
import { db } from './db';
// Link Google OAuth account
await db.insert(account).values({
id: 'acc_google_123',
userId: 'usr_abc123',
providerId: 'google',
accountId: '1234567890',
accessToken: 'ya29.a0AfH6SMBx...',
refreshToken: '1//0gHw...',
accessTokenExpiresAt: new Date(Date.now() + 3600 * 1000),
scope: 'openid email profile'
});
// Find all accounts for a user
const userAccounts = await db
.select()
.from(account)
.where(eq(account.userId, 'usr_abc123'));
Verification Table
Stores verification tokens for email verification, password resets, and other verification flows.
Primary key. Unique verification record identifier.
What is being verified (usually an email address).Indexes:
verification_identifier_idx - Indexed for fast lookups
The verification token/code.
When this verification token expires.
When the verification was created.Default: defaultNow()
Last time the verification record was updated.Default: defaultNow()
Auto-update: Updates automatically
Example Usage
import { verification } from './schemas/auth.schema';
import { db } from './db';
import { randomBytes } from 'crypto';
// Create email verification token
const token = randomBytes(32).toString('hex');
await db.insert(verification).values({
id: 'ver_123',
identifier: 'user@example.com',
value: token,
expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000) // 24 hours
});
// Verify token
const result = await db
.select()
.from(verification)
.where(
and(
eq(verification.identifier, 'user@example.com'),
eq(verification.value, token),
gt(verification.expiresAt, new Date())
)
);
if (result.length > 0) {
// Token is valid
await db.delete(verification).where(eq(verification.id, result[0].id));
}
Database Migration
To create these tables in your database:
# Generate migration
npm run db:generate
# Apply migration
npm run db:migrate
Migration SQL Example
CREATE TABLE "user" (
"id" text PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"email" text NOT NULL UNIQUE,
"email_verified" boolean DEFAULT false NOT NULL,
"image" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
CREATE TABLE "session" (
"id" text PRIMARY KEY NOT NULL,
"expires_at" timestamp NOT NULL,
"token" text NOT NULL UNIQUE,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp NOT NULL,
"ip_address" text,
"user_agent" text,
"user_id" text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE
);
CREATE INDEX "session_userId_idx" ON "session"("user_id");
CREATE TABLE "account" (
"id" text PRIMARY KEY NOT NULL,
"account_id" text NOT NULL,
"provider_id" text NOT NULL,
"user_id" text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE,
"access_token" text,
"refresh_token" text,
"id_token" text,
"access_token_expires_at" timestamp,
"refresh_token_expires_at" timestamp,
"scope" text,
"password" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp NOT NULL
);
CREATE INDEX "account_userId_idx" ON "account"("user_id");
CREATE TABLE "verification" (
"id" text PRIMARY KEY NOT NULL,
"identifier" text NOT NULL,
"value" text NOT NULL,
"expires_at" timestamp NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
CREATE INDEX "verification_identifier_idx" ON "verification"("identifier");
Security Best Practices
Important Security Considerations:
- Always hash passwords before storing in the
account.password field
- Use secure random token generation for sessions and verification
- Implement token expiration checks before validating
- Clear expired sessions and verification tokens regularly
- Use HTTPS for all authentication endpoints
- Implement rate limiting on login attempts
