Chapter 4: Database Integration: Supabase Adapter Binding and User Profile Database Expansion

After the efforts of the previous three chapters, we now have a fully functional OAuth 2.0 dual-platform login system. Users can log in via Google or Line, and we don’t need to handle password encryption ourselves at all.

But now we face a huge problem: Where is the data stored? By default, Auth.js uses JWT mode (JSON Web Token). This means that after a user successfully logs in, Auth.js will "encrypt" the user's name, email, and profile picture URL and store them in the user's browser cookie.

This is sufficient for a simple blog, but for a SaaS knowledge payment platform like Vibe Tutor that we’re building, this is completely inadequate!

Why SaaS Must Have a Real Database?

Consider the following scenarios:

How do you know how many members your site currently has? (Cookies are stored on each user’s computer—you can’t count them.)
Xiao Ming purchased a course for NT$3,999—how do you record that he has "paid"? (If you store the payment status in the JWT, a hacker can simply tamper with the JWT payload to access the course for free.)
If Xiao Ming violates the rules and gets banned, how do you force him offline? (Once a JWT is issued, it cannot be forcibly invalidated on the server side unless it expires.)

Therefore, we must introduce a powerful relational database and use the Adapter architecture to ensure Auth.js automatically writes all registration and login data into our database!

This chapter will use the world’s most powerful Serverless database: Supabase (PostgreSQL) for practical implementation.

🗄️ Step 1: Prepare the Supabase Database Schema

For Auth.js to understand your database, your tables cannot be created arbitrarily—they must strictly adhere to the Schema specifications defined by Auth.js.

Log in to the Supabase project dashboard.
Click SQL Editor on the left and open a new Query.
Paste and execute the following PostgreSQL table creation script provided by Auth.js:

```sql -- Create the core User table (stores the "person" entity) CREATE TABLE users ( id uuid NOT NULL DEFAULT uuid_generate_v4(), name VARCHAR(255), email VARCHAR(255), "emailVerified" TIMESTAMPTZ, image TEXT,

-- You can boldly expand your own business fields here! membership_level VARCHAR(50) DEFAULT 'free', total_spent INT DEFAULT 0,

PRIMARY KEY (id) );

-- Create the Accounts relation table (stores which third-party Providers this user has bound) CREATE TABLE accounts ( id uuid NOT NULL DEFAULT uuid_generate_v4(), "userId" uuid NOT NULL, type VARCHAR(255) NOT NULL, provider VARCHAR(255) NOT NULL, "providerAccountId" VARCHAR(255) NOT NULL, refresh_token TEXT, access_token TEXT, expires_at BIGINT, token_type TEXT, scope TEXT, id_token TEXT, session_state TEXT, PRIMARY KEY (id), -- Set foreign key: if the User is deleted, their bound accounts will also be deleted CONSTRAINT fk_user FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE );

-- Create the Sessions table (stores which devices the user is currently logged in on) CREATE TABLE sessions ( id uuid NOT NULL DEFAULT uuid_generate_v4(), "sessionToken" VARCHAR(255) NOT NULL, "userId" uuid NOT NULL, expires TIMESTAMPTZ NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_user FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE );

-- Create necessary indexes for performance CREATE UNIQUE INDEX accounts_provider_provideraccountid_idx ON accounts (provider, "providerAccountId"); CREATE UNIQUE INDEX sessions_sessiontoken_idx ON sessions ("sessionToken"); CREATE UNIQUE INDEX users_email_idx ON users (email); ```

This SQL script is invaluable! Notice that we sneakily added two fields to the `users` table: `membership_level` (membership level) and `total_spent` (total spending). This is the most fascinating aspect of the Database Session architecture: you can infinitely expand fields related to your business logic.

🔌 Step 2: Install the Supabase Adapter

Return to your project terminal and install the Supabase-specific Adapter package for Auth.js, along with the official Supabase SDK:

```bash npm install @auth/supabase-adapter @supabase/supabase-js ```

After installation, ensure your `.env.local` contains the Supabase connection string. 🚨 Critical Security Warning: Since Auth.js "secretly writes" user data for us in the server background, it must bypass all of Supabase’s RLS security measures. Therefore, we must use the Service Role Key (super admin key) here—never use the `anon key`.

```env

Add these two to .env.local

NEXT_PUBLIC_SUPABASE_URL="https://xxxxx.supabase.co"

Note! This key must NOT have the NEXT_PUBLIC_ prefix, or it will leak to the frontend!

SUPABASE_SERVICE_ROLE_KEY="eyJhbGciOiJIUz...this is a long super key" ```

⚙️ Step 3: Activate the Adapter Engine in auth.ts

Now, we’ll extensively rewrite `src/auth.ts` to switch from the ethereal Cookie mode to the hardcore Database mode.

Open `src/auth.ts`:

```typescript import NextAuth from "next-auth" import GoogleProvider from "next-auth/providers/google" import LineProvider from "next-auth/providers/line" import { SupabaseAdapter } from "@auth/supabase-adapter"

export const { handlers, signIn, signOut, auth } = NextAuth({ providers: [ GoogleProvider({ clientId: process.env.AUTH_GOOGLE_ID, clientSecret: process.env.AUTH_GOOGLE_SECRET, }), LineProvider({ clientId: process.env.AUTH_LINE_ID, clientSecret: process.env.AUTH_LINE_SECRET, }), ],

// 🚀 Key Setting 1: Mount the Adapter adapter: SupabaseAdapter({ url: process.env.NEXT_PUBLIC_SUPABASE_URL!, secret: process.env.SUPABASE_SERVICE_ROLE_KEY!, }),

// 🚀 Key Setting 2: Force Switch Session Strategy session: { // When you set an Adapter, the default is actually "database". // But explicitly writing "database" helps the team understand the architecture. strategy: "database", // Set how long before login expires (here set to 30 days) maxAge: 30 * 24 * 60 * 60, },

// 🚀 Key Setting 3: Callbacks (Interceptors) callbacks: { // When you call auth() or useSession() on the frontend, this callback is triggered // We can use this opportunity to pass additional database fields (e.g., membership_level) to the frontend! async session({ session, user }) { if (session.user) { // The user object is the real data directly selected from the database! session.user.id = user.id; // If you've expanded type definitions, you can even do this: // session.user.membership_level = user.membership_level; } return session; } } }) ```

🎇 Step 4: The Moment of Truth

You’ve set everything up. Now it’s time to reap the rewards.

Ensure the development server is running (`npm run dev`).
Go to `http://localhost:3000/api/auth/signin`.
Use a Google account you’ve never logged in with before and click login.
After successful login, immediately go to the Supabase backend Dashboard and open Table Editor.

Prepare to be amazed:

Open the `users` table: You’ll see the name, email, and high-resolution profile picture URL of the user who just logged in! You didn’t write a single `INSERT INTO users` SQL statement!
Open the `accounts` table: You’ll see a related record showing that this user logged in via `google`, and the `access_token` provided by Google is perfectly stored.
Open the `sessions` table: You’ll see a random `sessionToken` and expiration time.

This is the beauty of architecture. If you, as an admin, manually delete that record in the `sessions` table in the Supabase backend, within seconds, that user’s browser state will instantly change to "not logged in." This is why large-scale commercial systems must use Database Session—you have absolute control!

In the next chapter, we’ll reach the climax of this course: how to use this powerful Auth.js to intercept non-paying "freeloaders" on the server side and build an airtight, commercial-grade security defense system!

Summary

The Supabase PostgreSQL adapter replaces cookie-based sessions with persistent database sessions. This enables sessions to survive server restarts, work across multiple servers, and be shared between applications.

Key takeaways:

Database sessions persist across restarts (unless you use cookie sessions)
The adapter stores sessions, accounts, and users in PostgreSQL tables
JWT is simpler but cannot be revoked server-side
Database sessions can be listed, inspected, and revoked
Supabase provides managed PostgreSQL with Row Level Security
Use database sessions for production applications
Use JWT sessions for serverless deployments or simple apps

What's Next: User Sessions in Practice

The next chapter covers practical session management — protecting routes, accessing session data, and handling session expiry.