Data & Auth Integration Patterns¶

Architecture Overview¶

• Auth.js (NextAuth v5): Session-based authentication and tenant-aware identity, configured with the Credentials provider (additional OAuth providers may be added later).
• Kysely: Type-safe SQL query builder for all server-side data access. Targets PostgreSQL in production and SQLite locally — write portable SQL.
• Zod: Schema validation at every API and server-action boundary; same schemas drive OpenAPI generation.
• bcryptjs: Password hashing for the Credentials provider.
• Server-only: Auth.js, Kysely, and the database driver MUST stay on the server. Never import them from a 'use client' module.

Environment Variables¶

Server-Side Variables (Private)¶

Auth.js and database variables are server-only and MUST NOT use the NEXT_PUBLIC_ prefix:

# Auth.js core
AUTH_SECRET=replace-with-32-byte-random-string
AUTH_URL=https://example.com
AUTH_TRUST_HOST=true

# OAuth provider keys (only set the providers you have enabled)
AUTH_GOOGLE_ID=your_google_client_id
AUTH_GOOGLE_SECRET=your_google_client_secret

# Kysely / database
DATABASE_URL=postgres://user:password@host:5432/lms

For local development with SQLite, point DATABASE_URL at a file path (e.g. sqlite:./data/dev.sqlite).

Client-Side Variables (Public)¶

There are no client-visible auth or database variables. Anything sensitive must remain server-only.

Authentication Patterns¶

Auth.js Configuration¶

• Configuration lives in src/auth.ts and exports auth, signIn, signOut, and handlers (the v5 idiom).
• src/app/api/auth/\[...nextauth\]/route.ts re-exports handlers.GET and handlers.POST.
• Session strategy is JWT. Augment the session in the jwt/session callbacks to include userId, role, and tenantId.

Server-Side Session Access¶

• In server components, route handlers, and server actions, call await auth() to retrieve the current session.
• Treat auth() as the single source of truth for "is this request authenticated?". Do not parse cookies manually.
• Return Response 401 / redirect('/login') when no session is present on a protected surface.

Client-Side Session Access¶

• Use useSession() (from next-auth/react) only when the UI needs reactive session state (e.g. avatar, sign-out button).
• For initial render of protected pages, prefer server components using auth() and pass the resulting session down.

Session Claims vs Database Records¶

Session Claims (JWT)¶

• Used for fast role checks at the edge of every request.
• Stored in the encrypted JWT cookie issued by Auth.js.
• Updated via the jwt callback. Keep the payload small.

Database Records (Kysely)¶

• Source of truth for user data and role assignments.
• users table holds id, email, password_hash, role, tenant_id, created_at, etc.
• Privileged actions (role promotions by tenant_admin/super_admin, course-admin assignments, billing, deletions, impersonation) MUST re-read the database — never trust the JWT alone.

Consistency¶

• When a role changes, update the database row first, then refresh the session (Auth.js issues a new JWT on next sign-in or via the update() helper).
• Do not store ephemeral state in the JWT — it is only refreshed on session events.

Database Authorization¶

There is no declarative database rule engine. Authorization is enforced in application code:

// Example: protected route handler
import { auth } from "@/auth";
import { db } from "@/db";
import { z } from "zod";

const enrollSchema = z.object({
  courseSlug: z.string().min(1),
});

export async function POST(request: Request) {
  const session = await auth();
  if (!session?.user?.id) {
    return new Response("Unauthorized", { status: 401 });
  }

  const parsed = enrollSchema.safeParse(await request.json());
  if (!parsed.success) {
    return new Response("Bad Request", { status: 400 });
  }

  const enrollment = await db
    .insertInto("enrollments")
    .values({
      user_id: session.user.id,
      tenant_id: session.user.tenantId,
      course_slug: parsed.data.courseSlug,
      status: "enrolled",
    })
    .returningAll()
    .executeTakeFirstOrThrow();

  return Response.json(enrollment);
}

Every multi-tenant query MUST scope by tenant_id; every per-user query MUST scope by user_id.

Admin Routes¶

import { auth } from "@/auth";

export async function GET() {
  const session = await auth();
  const role = session?.user?.role;
  if (role !== "tenant_admin" && role !== "super_admin") {
    return new Response("Forbidden", { status: 403 });
  }
  // ...tenant_admin / super_admin logic. For per-course endpoints,
  // also accept `course_admin` after verifying the course slug is in
  // `session.user.assignedCourses`. See user-management.md.
}

Schema & Migrations¶

• The Kysely Database type lives in src/db/types.ts and is the only handwritten source of truth for table shapes. There is no codegen.
• Migrations live in src/db/migrations/ and run via tsx scripts (e.g. npm run db:migrate).
• Use Kysely's Migrator API. Each migration must implement both up and down.
• After changing a table, update src/db/types.ts in the same commit.

Error Handling¶

Authentication Errors¶

• Auth.js redirects to the configured pages.error route on failure. Surface a friendly message and a retry path; never leak provider error codes.
• Treat expired sessions as anonymous — middleware (proxy.ts) redirects to /login?callbackUrl=....

Database Errors¶

• Wrap Kysely calls with try/catch in route handlers and server actions; map known errors (unique constraint, foreign key) to user-friendly messages.
• Never surface raw SQL or stack traces to the client.
• Log the original error server-side with enough context (route, user id, tenant id) to debug.

User Management¶

Role Assignment¶

• New users default to the student role on first sign-in (or on invitation acceptance, the role from the invitations row).
• tenant_admin users promote others within { student, course_admin, tenant_admin } for their own tenant; super_admin can act across tenants. Promotion writes the new role via Kysely, bumps users.token_version to invalidate outstanding JWTs, and triggers a session refresh on the affected user's next request.
• course_admin scope is assigned per-course via course_admin_assignments; managing assignments is a tenant_admin/super_admin action.

User Lifecycle¶

• On first sign-in, insert a row into users if one does not already exist.
• On profile changes, update the users row. The next session refresh propagates the change to the JWT.
• On deletion, cascade through enrollments, quiz_attempts, etc., or soft-delete by toggling a deleted_at column — pick one strategy per table and document it in src/db/types.ts.

Performance Optimization¶

Caching¶

• Server components cache naturally through Next.js fetch caching; for Kysely queries, use unstable_cache or React's cache() helper for stable inputs.
• Index frequently filtered columns (user_id, tenant_id, course_slug).
• Avoid N+1 queries — prefer a single join over a loop of .executeTakeFirst() calls.

Query Optimization¶

• Use select() to fetch only the columns you need.
• Use limit() + cursor-based pagination for large lists.
• Profile with EXPLAIN in PostgreSQL when a query is slow.

Security Best Practices¶

• Never expose AUTH_SECRET, OAuth client secrets, or DATABASE_URL to the client bundle.
• Always validate user permissions server-side, even after a session check.
• Hash credentials passwords with bcryptjs (cost factor ≥ 12) before insert.
• Rotate AUTH_SECRET on a defined cadence; document the rotation process.
• Sanitize all user inputs with Zod schemas before any Kysely insert/update.