> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getsabo.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Database with Supabase
> Where database types and clients live, how dashboard settings map to tables, and recommended usage patterns.
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)
```1:29:sabo/src/lib/supabase/server.ts theme={null}
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:**
```sql theme={null}
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:**
```typescript theme={null}
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:**
```sql theme={null}
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:**
```typescript theme={null}
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:**
```sql theme={null}
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:**
```typescript theme={null}
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:**
```sql theme={null}
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:
```13:36:sabo/src/lib/types/database.ts theme={null}
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
## See also
* Dashboard Settings
* Payments with Stripe
* Routing & Middleware