Supabase Setup

ShipAddons uses Supabase as its database backend. This tutorial covers the complete setup process.

Why Supabase

Supabase provides:

PostgreSQL database with a generous free tier
Row Level Security (RLS) for fine-grained access control
REST API automatically generated from your schema
JWT verification that works with custom-signed tokens

The backend uses Supabase in two ways:

Admin client (service key) - For user creation and webhook handlers
User client (JWT) - For RLS-protected queries from the add-on

1. Create Project

Go to supabase.com and create a new project
Choose a region close to your users
Wait for the project to be provisioned (~2 minutes)

2. Run Database Schema

The schema creates three tables: users, subscriptions, and audit_logs.

Go to SQL Editor in your Supabase dashboard
Copy the contents of web/lib/db/supabase-schema.sql
Click Run to execute

The schema includes:

-- Users table
create table public.users (
  id uuid primary key default gen_random_uuid(),
  google_sub text unique not null,  -- Google user ID
  email text unique not null,
  name text,
  available_credits integer default 0,
  created_at timestamptz default now()
);

-- Subscriptions table
create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.users(id) unique not null,
  stripe_customer_id text unique not null,
  stripe_subscription_id text unique,
  status text,
  plan_id text
);

-- Audit logs table
create table public.audit_logs (
  id bigserial primary key,
  user_id uuid references public.users(id) not null,
  action text not null,
  context text,
  timestamp timestamptz default now()
);

3. Get API Credentials

From Project Settings > Data API and API Keys, copy these values to your .env.local:

Setting	Environment Variable	Description
Project URL	`SUPABASE_URL`	`https://xxx.supabase.co`
Publishable key	`SUPABASE_PUBLISHABLE_KEY`	Safe for client-side use
Secret key	`SUPABASE_SECRET_KEY`	Backend only, bypasses RLS

4. Configure JWT Signing

Since ShipAddons signs its own JWTs (not using Supabase Auth), you need the legacy HS256 secret.

Go to Project Settings > JWT Keys > Legacy JWT Secret
Copy the secret
Add to .env.local:

SUPABASE_JWT_SECRET=your-legacy-jwt-secret-here

This secret is used to sign JWTs that Supabase will accept for RLS verification.

RLS Strategy

Row Level Security ensures users can only access their own data.

How It Works

The backend generates a JWT with the user's UUID as the sub claim
Supabase extracts this via auth.uid() function
RLS policies compare auth.uid() against row ownership

Policies

-- Users can only read their own row
create policy "Users can read own data" on public.users
  for select using (auth.uid() = id);

-- Users can update their own row
create policy "Users can update own data" on public.users
  for update using (auth.uid() = id);

-- Users can read their own subscription
create policy "Users can read own subscription" on public.subscriptions
  for select using (auth.uid() = user_id);

-- Users can read/insert their own audit logs
create policy "Users can read own audit logs" on public.audit_logs
  for select using (auth.uid() = user_id);

create policy "Users can insert own audit logs" on public.audit_logs
  for insert with check (auth.uid() = user_id);

Service Role Access

The backend uses the service key for operations that bypass RLS:

Creating new users (during first authentication)
Updating subscriptions (from Stripe webhooks)
Admin operations

// Admin client - bypasses RLS
const { data } = await supabaseAdmin
  .from("users")
  .insert({ google_sub, email, name });

// User client - respects RLS
const { data } = await userClient.from("users").select("*").single(); // Returns only the authenticated user's row

JWT Generation

The backend generates Supabase-compatible JWTs using the HS256 algorithm:

import { SignJWT } from "jose";

export async function generateSupabaseJWT(payload: {
  sub: string;
  email: string;
}) {
  const secret = new TextEncoder().encode(process.env.SUPABASE_JWT_SECRET);

  return new SignJWT({
    sub: payload.sub,
    email: payload.email,
    role: "authenticated",
    aud: "authenticated",
  })
    .setProtectedHeader({ alg: "HS256", typ: "JWT" })
    .setIssuedAt()
    .setExpirationTime("1h")
    .sign(secret);
}

Key claims:

sub - User's UUID (used by auth.uid() in RLS)
role - Must be authenticated for RLS to work
aud - Audience claim required by Supabase

Verification

To verify your setup:

Run your backend locally: cd web && yarn dev
Open the add-on in a Google document
Complete authentication
Check Supabase dashboard:
- Table Editor > users should show a new row
- Logs > Postgres should show successful queries