Authentication & Security

Overview

Pipeline uses Supabase Auth for authentication with cookie-based sessions, Next.js middleware for route protection, and Row-Level Security (RLS) for data isolation.

All authentication is handled server-side with secure, HTTP-only cookies. No tokens are exposed to the client.

Authentication Architecture

Auth Providers

Supabase Auth

Built-in authentication with email/password, social providers, and magic links

Supported Methods:

Email + Password (primary)
Magic Links (email-based)
OAuth providers (configurable)
Email confirmation required

Session Management

Pipeline uses @supabase/ssr for automatic cookie-based session management:

// src/lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr';
import type { Database } from '@/src/lib/types/database.types';

export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
}

The service role key bypasses RLS. Only use in trusted server-side code.

Middleware & Route Protection

Next.js Middleware

Middleware runs on every request to refresh sessions and protect routes.

import { createServerClient } from '@supabase/ssr';
import { NextResponse, type NextRequest } from 'next/server';

export async function middleware(request: NextRequest) {
  let supabaseResponse = NextResponse.next({ request });

  const supabase = createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return request.cookies.getAll();
        },
        setAll(cookiesToSet) {
          cookiesToSet.forEach(({ name, value }) =>
            request.cookies.set(name, value)
          );
          supabaseResponse = NextResponse.next({ request });
          cookiesToSet.forEach(({ name, value, options }) =>
            supabaseResponse.cookies.set(name, value, options)
          );
        },
      },
    }
  );

  // ⚠️ CRITICAL: Refresh session (don't remove!)
  const { data: { user } } = await supabase.auth.getUser();

  // Public routes (no auth required)
  const publicRoutes = [
    '/', '/login', '/signup', '/forgot-password',
    '/reset-password', '/auth/callback', '/api/auth'
  ];
  
  const pathname = request.nextUrl.pathname;
  const isPublicRoute = publicRoutes.some(route => {
    if (route === '/') return pathname === '/';
    return pathname.startsWith(route);
  });

  // Auth routes (redirect if already logged in)
  const authRoutes = ['/login', '/signup', '/forgot-password', '/reset-password'];
  const isAuthRoute = authRoutes.some(route => pathname.startsWith(route));

  // Redirect logic
  if (!user && !isPublicRoute) {
    // Not authenticated → redirect to login
    const url = request.nextUrl.clone();
    url.pathname = '/login';
    return NextResponse.redirect(url);
  }

  if (user && isAuthRoute) {
    // Authenticated → redirect to app
    const url = request.nextUrl.clone();
    url.pathname = '/tracker';
    return NextResponse.redirect(url);
  }

  return supabaseResponse;
}

export const config = {
  matcher: [
    '/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)',
  ],
};

Key Features:

Automatic session refresh on every request
Redirects unauthenticated users to /login
Redirects authenticated users away from auth pages
Runs on all routes except static assets

API Routes

import { NextRequest, NextResponse } from 'next/server';
import { createClient } from '@/src/lib/supabase/server';
import { z } from 'zod';

const LoginSchema = z.object({
  email: z.string().email('Invalid email address'),
  password: z.string().min(1, 'Password is required'),
});

export async function POST(request: NextRequest) {
  const body = await request.json();
  const { email: rawEmail, password } = LoginSchema.parse(body);
  
  // Normalize email (case-insensitive)
  const email = rawEmail.toLowerCase().trim();

  const supabase = await createClient();
  const { data, error } = await supabase.auth.signInWithPassword({
    email,
    password,
  });

  if (error) {
    if (error.message.includes('Email not confirmed')) {
      return NextResponse.json(
        { error: { code: 'EMAIL_NOT_CONFIRMED', message: 'Please confirm your email' } },
        { status: 403 }
      );
    }
    
    return NextResponse.json(
      { error: { code: 'INVALID_CREDENTIALS', message: 'Invalid email or password' } },
      { status: 401 }
    );
  }

  // ⚠️ Session cookies are set automatically by @supabase/ssr
  // Never return tokens in response body (XSS risk)
  return NextResponse.json({
    user: {
      id: data.user.id,
      email: data.user.email,
    },
  });
}

Row-Level Security (RLS)

Overview

RLS ensures users can only access their own data. Policies are enforced at the database level, making them impossible to bypass from the application layer.

RLS Enabled on All Tables

Every table has RLS enabled with policies for SELECT, INSERT, UPDATE, and DELETE operations.

Helper Functions

CREATE FUNCTION is_authenticated()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN auth.uid() IS NOT NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Jobs Table Policies

users_view_own_jobs (SELECT)

CREATE POLICY "users_view_own_jobs" ON jobs
  FOR SELECT
  USING (
    auth.uid() = user_id
    AND deleted_at IS NULL
  );

Users can view their own active jobs (not soft-deleted).

users_insert_own_jobs (INSERT)

CREATE POLICY "users_insert_own_jobs" ON jobs
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Users can insert jobs where they are the owner.

users_update_own_jobs (UPDATE)

CREATE POLICY "users_update_own_jobs" ON jobs
  FOR UPDATE
  USING (
    auth.uid() = user_id
    AND deleted_at IS NULL
  );

Users can update their own active jobs.

users_delete_own_jobs (DELETE)

CREATE POLICY "users_delete_own_jobs" ON jobs
  FOR DELETE
  USING (
    auth.uid() = user_id
    AND deleted_at IS NULL
  );

Users can soft-delete their own active jobs.

DELETE triggers the soft delete function, setting deleted_at = NOW() instead of hard-deleting.

service_role_bypass_jobs (ALL)

CREATE POLICY "service_role_bypass_jobs" ON jobs
  FOR ALL
  USING (is_service_role())
  WITH CHECK (is_service_role());

Service role key bypasses RLS for system operations (scrapers, AI scoring, analytics).

Events Table Policies

Events are append-only. Users can SELECT and INSERT their own events, but cannot UPDATE or DELETE.

users_view_own_events (SELECT)

CREATE POLICY "users_view_own_events" ON events
  FOR SELECT
  USING (auth.uid() = user_id);

users_insert_own_events (INSERT)

CREATE POLICY "users_insert_own_events" ON events
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

service_role_bypass_events (ALL)

CREATE POLICY "service_role_bypass_events" ON events
  FOR ALL
  USING (is_service_role())
  WITH CHECK (is_service_role());

Security Best Practices

Never Expose Service Role Key

The SUPABASE_SERVICE_ROLE_KEY bypasses RLS. Only use server-side with the server-only package.

HTTP-Only Cookies

Session tokens are stored in HTTP-only cookies, inaccessible to JavaScript (prevents XSS).

Rate Limiting

API routes implement rate limiting (5 req/min for auth endpoints).

Input Validation

All inputs validated with Zod schemas before database operations.

Authentication Flow Diagram

RLS Testing

Manual Testing

-- Login as User A
SET request.jwt.claim.sub = 'user-a-uuid';

-- Try to access User B's job
SELECT * FROM jobs WHERE id = 'user-b-job-uuid';
-- Result: 0 rows (blocked by RLS)

-- Try to update User B's job
UPDATE jobs SET status = 'rejected' WHERE id = 'user-b-job-uuid';
-- Result: 0 rows affected (blocked by RLS)

Security Checklist

RLS Enabled

✅ All tables have ALTER TABLE ... ENABLE ROW LEVEL SECURITY

Policies Defined

✅ Every table has SELECT, INSERT, UPDATE policies (DELETE where appropriate)

Service Role Protected

✅ SUPABASE_SERVICE_ROLE_KEY only in .env and server-side code with server-only package

Middleware Active

✅ Session refresh on every request, route protection enforced

HTTPS URLs Only

✅ Database constraint: CHECK (job_url IS NULL OR job_url LIKE 'https://%')

Input Validation

✅ All API routes validate input with Zod schemas

Next Steps

Database Schema

Review complete table structures and constraints

Deployment Guide

Deploy with RLS enabled to production

Get Started

Core Features

Guides

Architecture

Authentication & Security

Overview

Authentication Architecture

Auth Providers

Supabase Auth

Session Management

Middleware & Route Protection

Next.js Middleware

API Routes

Row-Level Security (RLS)

Overview

RLS Enabled on All Tables

Helper Functions

Jobs Table Policies

Events Table Policies

Security Best Practices

Never Expose Service Role Key

HTTP-Only Cookies

Rate Limiting

Input Validation

Authentication Flow Diagram

RLS Testing

Manual Testing

Security Checklist

Next Steps

Database Schema

Deployment Guide

Build docs developers (and LLMs) love

Get Started

Core Features

Guides

Architecture

​Overview

​Authentication Architecture

​Auth Providers

Supabase Auth

​Session Management

​Cookie-Based Sessions

​Middleware & Route Protection

​Next.js Middleware

​API Routes

​Login Endpoint

​Row-Level Security (RLS)

​Overview

RLS Enabled on All Tables

​Helper Functions

​Jobs Table Policies

​Events Table Policies

​Security Best Practices

Never Expose Service Role Key

HTTP-Only Cookies

Rate Limiting

Input Validation

​Authentication Flow Diagram

​RLS Testing

​Manual Testing

​Security Checklist

​Next Steps

Database Schema

Deployment Guide

Build docs developers (and LLMs) love

Overview

Authentication Architecture

Auth Providers

Session Management

Cookie-Based Sessions

Middleware & Route Protection

Next.js Middleware

API Routes

Login Endpoint

Row-Level Security (RLS)

Overview

Helper Functions

Jobs Table Policies

Events Table Policies

Security Best Practices

Authentication Flow Diagram

RLS Testing

Manual Testing

Security Checklist

Next Steps