Database with Supabase

Sabo uses Supabase for authentication and database access. This page shows where the clients and types live, how dashboard pages map to tables, and patterns to read/write data safely.

Where things live

Server client: sabo/src/lib/supabase/server.ts (SSR/server actions; cookies wired)
Browser client: sabo/src/lib/supabase/client.ts (client components; limited usage)
Types: sabo/src/lib/types/database.ts (UserProfile, UserSubscription, PaymentHistory)

import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function createClient() {
  const cookieStore = await cookies()
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
    {
      cookies: {
        getAll() { return cookieStore.getAll() },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // Server Component context: safe to ignore if middleware refreshes sessions
          }
        },
      },
    }
  )
}

Database Schema

Sabo’s database includes four main tables with Row Level Security (RLS) enabled:

1. user_profiles

Stores user profile information, preferences, and notification settings. Schema:

CREATE TABLE public.user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE,
  full_name TEXT,
  profile_image_url TEXT,
  bio TEXT,
  website TEXT,
  location TEXT,
  phone TEXT,
  birth_date DATE,
  language TEXT DEFAULT 'en',
  timezone TEXT DEFAULT 'UTC',
  is_private BOOLEAN DEFAULT false,
  email_notifications BOOLEAN DEFAULT true,
  marketing_emails BOOLEAN DEFAULT false,
  push_notifications TEXT DEFAULT 'mentions',
  communication_emails BOOLEAN DEFAULT false,
  social_emails BOOLEAN DEFAULT true,
  security_emails BOOLEAN DEFAULT true,
  mobile_notifications_different BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (user_id)
);

Key Fields:

user_id - Foreign key to auth.users, unique (one profile per user)
username - Unique username, nullable
push_notifications - Values: "all", "mentions", "none"
updated_at - Automatically updated via trigger

RLS Policies:

Users can SELECT/INSERT/UPDATE/DELETE their own profile only
Policy checks: auth.uid() = user_id

TypeScript Interface:

export interface UserProfile {
  id: string;
  user_id: string;
  username: string | null;
  full_name: string | null;
  profile_image_url: string | null;
  bio: string | null;
  website: string | null;
  location: string | null;
  phone: string | null;
  birth_date: string | null; // ISO date string
  language: string;
  timezone: string;
  is_private: boolean;
  email_notifications: boolean;
  marketing_emails: boolean;
  push_notifications: string; // "all" | "mentions" | "none"
  communication_emails: boolean;
  social_emails: boolean;
  security_emails: boolean;
  mobile_notifications_different: boolean;
  created_at: string; // ISO timestamp
  updated_at: string; // ISO timestamp
}

2. user_subscriptions

Stores Stripe subscription data for billing management. Schema:

CREATE TABLE public.user_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT UNIQUE,
  stripe_price_id TEXT,
  plan_name TEXT,
  status TEXT,
  billing_cycle TEXT,
  trial_start TIMESTAMPTZ,
  trial_end TIMESTAMPTZ,
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN DEFAULT false,
  cancel_at TIMESTAMPTZ,
  canceled_at TIMESTAMPTZ,
  cancellation_reason TEXT,
  cancellation_feedback TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (user_id)
);

Key Fields:

user_id - One subscription per user
stripe_customer_id - Stripe Customer ID (e.g., cus_...)
stripe_subscription_id - Stripe Subscription ID (e.g., sub_...), unique
stripe_price_id - Stripe Price ID (e.g., price_...)
status - Subscription status: "active", "trialing", "past_due", "canceled", etc.
billing_cycle - "monthly" or "yearly"
cancel_at_period_end - If true, subscription cancels at period end
canceled_at - When cancellation was requested
cancel_at - Future date when subscription will actually end

RLS Policies:

Users can SELECT their own subscription only
No INSERT/UPDATE/DELETE policies (webhooks use service client to bypass RLS)

TypeScript Interface:

export interface UserSubscription {
  id: string;
  user_id: string;
  stripe_customer_id: string | null;
  stripe_subscription_id: string | null;
  stripe_price_id: string | null;
  plan_name: string | null;
  status: string | null;
  billing_cycle: string | null;
  trial_start: string | null; // ISO timestamp
  trial_end: string | null;
  current_period_start: string | null;
  current_period_end: string | null;
  cancel_at_period_end: boolean;
  canceled_at: string | null; // When cancellation was requested
  cancel_at: string | null; // When subscription will end
  cancellation_reason: string | null;
  cancellation_feedback: string | null;
  created_at: string;
  updated_at: string;
}

3. payment_history

Stores payment records for invoices and transactions. Schema:

CREATE TABLE public.payment_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  stripe_subscription_id TEXT,
  stripe_payment_intent_id TEXT,
  amount INTEGER NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL,
  description TEXT,
  invoice_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Key Fields:

user_id - Foreign key to auth.users
stripe_subscription_id - Associated subscription (nullable for one-time payments)
stripe_payment_intent_id - Stripe Payment Intent ID (e.g., pi_...)
amount - Amount in cents (e.g., 1200 = $12.00)
currency - ISO currency code (e.g., "usd", "eur")
status - Payment status: "succeeded", "failed", "pending", etc.
invoice_url - Stripe-hosted invoice URL

RLS Policies:

Users can SELECT their own payment history only
No INSERT/UPDATE/DELETE policies (webhooks use service client)

TypeScript Interface:

export interface PaymentHistory {
  id: string;
  user_id: string;
  stripe_subscription_id: string | null;
  stripe_payment_intent_id: string | null;
  amount: number; // Amount in cents
  currency: string;
  status: string;
  description: string | null;
  invoice_url: string | null;
  created_at: string; // ISO timestamp
}

4. stripe_products

Stores Stripe product catalog (optional, for dynamic pricing displays). Schema:

CREATE TABLE public.stripe_products (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

RLS Policies:

Anyone (authenticated) can SELECT active products
No INSERT/UPDATE/DELETE policies (managed via webhooks or admin)

Tables and Settings Mapping

The dashboard settings pages map to these tables:

Settings Page	Table	Key Fields
General Settings	`user_profiles`	`language`, `timezone`
Account Settings	`user_profiles`	`full_name`, `username`, `bio`, `website`, `phone`, `birth_date`
Billing Settings	`user_subscriptions`	`plan_name`, `status`, `current_period_end`
Billing Settings	`payment_history`	`amount`, `status`, `invoice_url`, `created_at`
Notification Settings	`user_profiles`	`email_notifications`, `push_notifications`, `communication_emails`, `social_emails`, `security_emails`

See the type definitions:

export interface UserProfile {
  id: string;
  user_id: string;
  username: string | null;
  full_name: string | null;
  profile_image_url: string | null;
  bio: string | null;
  website: string | null;
  language: string;
  timezone: string;
  email_notifications: boolean;
  marketing_emails: boolean;
  // ...
}

Settings implementation: General/Notifications update UserProfile; Account uses supabase.auth.updateUser(); Billing reads UserSubscription/PaymentHistory and links to Stripe Portal.

Recommended patterns

Prefer the server client (createClient) in Server Components and server actions for reads/writes.
Revalidate affected paths after writes (e.g., revalidatePath('/dashboard/settings/general')).
Keep types centralized in database.ts, and import them where needed for safety.
Handle errors explicitly; return predictable shapes to the UI.

Future work (optional)

Migrations and type generation from Supabase schema
RLS policies and role-based access
Seed scripts and local development workflow

​Where things live

​Database Schema

​1. user_profiles

​2. user_subscriptions

​3. payment_history

​4. stripe_products

​Tables and Settings Mapping

​Recommended patterns

​Future work (optional)

​See also

Where things live

Database Schema

1. user_profiles

2. user_subscriptions

3. payment_history

4. stripe_products

Tables and Settings Mapping

Recommended patterns

Future work (optional)

See also