Supabase

Setup

1. Create a project at supabase.com (free tier includes 500MB PostgreSQL)
2. Go to Project Settings - API
3. Copy the Project URL, publishable key, and secret key
4. Open SQL Editor in your Supabase dashboard
5. Paste and run the contents of lib/db/schema.sql
6. Add the three env vars to .env.local

Environment variables

NEXT_PUBLIC_SUPABASE_URL=https://xxxxxxxxxxxx.supabase.co
NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY=sb_publishable_xxxxxxxxxxxx
SUPABASE_SECRET_KEY=sb_secret_xxxxxxxxxxxx

Run the schema first

Run lib/db/schema.sql before you test any auth flow. If the schema has not been applied, registration, OTP lookup, and password reset will fail against missing tables or indexes even when your keys are correct.

-- Users table
create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null unique,
  password text not null,
  email_verified boolean not null default false,
  role text not null default 'user' check (role in ('user', 'admin')),
  image text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- OTPs table
create table if not exists otps (
  id uuid primary key default gen_random_uuid(),
  email text not null,
  hashed_otp text not null,
  purpose text not null check (purpose in ('email-verification', 'password-reset')),
  expires_at timestamptz not null,
  used boolean not null default false,
  created_at timestamptz not null default now()
);

create index if not exists idx_otps_email_purpose on otps (email, purpose);
create index if not exists idx_otps_expires_at on otps (expires_at);

-- Optional: pg_cron cleanup (requires pg_cron extension enabled in Supabase dashboard)
-- select cron.schedule('delete-expired-otps', '*/15 * * * *',
--   'delete from otps where expires_at < now()');

Auth flow smoke test

1. Register a new user from /register.
2. Verify the OTP from the email you receive.
3. Log in with the verified account.
4. Request a reset from /forgot-password.
5. Reset the password from /reset-password.
6. Log in again with the updated password.

That sequence was validated successfully after migrating the scaffold to the new publishable and secret key system.

Troubleshooting

relation "users" does not exist or relation "otps" does not exist

You did not run lib/db/schema.sql in the same Supabase project your app is pointing at. Open SQL Editor, run the schema, and retry the flow.

Missing Supabase environment variables or invalid API key errors

Check that your env file uses NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY and SUPABASE_SECRET_KEY exactly. Legacy anon and service role variable names do not match the current code.

Credentials look right but auth still hits the wrong project

Project URL and keys must come from the same Supabase project. A mismatched URL and key pair can produce confusing symptoms like empty reads, auth failures, or missing-table errors against a different environment.

// Finding a valid OTP record
const { data: record } = await supabase
  .from('otps')
  .select('id, hashed_otp')
  .eq('email', email)
  .eq('purpose', 'email-verification')
  .eq('used', false)
  .gt('expires_at', new Date().toISOString())
  .single()