Database

ShipAddons uses Supabase's PostgreSQL database with Row Level Security (RLS) for data storage.

Schema Overview

The database consists of three tables:

Table	Purpose
`users`	User accounts linked to Google identity
`subscriptions`	Stripe subscription state
`audit_logs`	Activity tracking and debugging

Users Table

Stores user account information.

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

Columns

Column	Type	Description
`id`	`uuid`	Primary key, used in JWTs and RLS
`google_sub`	`text`	Google user ID from identity token
`email`	`text`	User's email address
`name`	`text`	Display name
`available_credits`	`integer`	Credit balance for usage-based features
`created_at`	`timestamptz`	Account creation timestamp
`updated_at`	`timestamptz`	Last modification timestamp

User Creation

Users are created during first authentication:

// In /api/auth/google
const { data: user } = await supabaseAdmin
  .from("users")
  .upsert(
    {
      google_sub: payload.sub,
      email: payload.email,
      name: payload.name,
    },
    { onConflict: "google_sub" }
  )
  .select()
  .single();

The upsert ensures idempotent user creation—subsequent logins update the user's name if it changed.

Subscriptions Table

Tracks Stripe subscription state.

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,
  current_period_start timestamptz,
  current_period_end timestamptz,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

Columns

Column	Type	Description
`user_id`	`uuid`	Foreign key to users table (unique constraint)
`stripe_customer_id`	`text`	Stripe customer ID (`cus_xxx`)
`stripe_subscription_id`	`text`	Stripe subscription ID (`sub_xxx`)
`status`	`text`	Subscription status (`active`, `canceled`, etc.)
`plan_id`	`text`	Package ID from `constants.ts`
`current_period_start`	`timestamptz`	Billing period start
`current_period_end`	`timestamptz`	Billing period end

Status Values

Status	Description
`active`	Subscription is paid and active
`trialing`	In trial period
`past_due`	Payment failed, grace period
`canceled`	Subscription ended
`incomplete`	Initial payment pending
`incomplete_expired`	Initial payment failed

Subscription Updates

Subscriptions are updated via Stripe webhooks:

// checkout.session.completed webhook
await supabaseAdmin.from("subscriptions").upsert({
  user_id: userId,
  stripe_customer_id: customerId,
  stripe_subscription_id: subscriptionId,
  status: subscription.status,
  plan_id: planId,
});

// customer.subscription.updated webhook
await supabaseAdmin
  .from("subscriptions")
  .update({
    status: subscription.status,
    plan_id: newPlanId,
    current_period_end: new Date(subscription.current_period_end * 1000),
  })
  .eq("stripe_subscription_id", subscription.id);

Audit Logs Table

Records user activity for debugging and analytics.

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()
);

Columns

Column	Type	Description
`user_id`	`uuid`	Foreign key to users table
`action`	`text`	Activity type (enum in application code)
`context`	`text`	Additional context (JSON string)
`timestamp`	`timestamptz`	When the action occurred

Activity Types

Defined in constants.ts:

export enum ActivityType {
  LOGIN = "LOGIN",
  CHECKOUT_STARTED = "CHECKOUT_STARTED",
  SUBSCRIPTION_CREATED = "SUBSCRIPTION_CREATED",
  SUBSCRIPTION_UPDATED = "SUBSCRIPTION_UPDATED",
  SUBSCRIPTION_CANCELED = "SUBSCRIPTION_CANCELED",
  CREDITS_USED = "CREDITS_USED",
  CREDITS_PURCHASED = "CREDITS_PURCHASED",
}

Logging Activity

await supabaseAdmin.from("audit_logs").insert({
  user_id: userId,
  action: ActivityType.SUBSCRIPTION_CREATED,
  context: JSON.stringify({ plan_id: "PRO" }),
});

Row Level Security

RLS policies ensure users can only access their own data.

Policy Definitions

-- Enable RLS
alter table public.users enable row level security;
alter table public.subscriptions enable row level security;
alter table public.audit_logs enable row level security;

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

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

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

-- Audit logs policies
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);

How RLS Works

The add-on client receives a JWT with the user's UUID as the sub claim
Client makes requests to Supabase with this JWT in the Authorization header
Supabase extracts auth.uid() from the JWT
RLS policies compare auth.uid() against row ownership columns

// This query automatically filters to the authenticated user
const { data: user } = await supabaseClient
  .from("users")
  .select("*")
  .single();
// Returns only the user's own row, or null if not found

Bypassing RLS

The service key bypasses RLS for admin operations:

// Admin operations use supabaseAdmin (service key)
const { data } = await supabaseAdmin
  .from("users")
  .select("*");
// Returns ALL users - use with caution

Only use the admin client for:

User creation during authentication
Webhook handlers
Admin dashboards

Indexes

Performance indexes are created automatically:

-- Implicit indexes from unique constraints
-- users.google_sub, users.email
-- subscriptions.stripe_customer_id, subscriptions.stripe_subscription_id

-- Explicit indexes
create index users_google_sub_idx on public.users (google_sub);
create index subscriptions_user_id_idx on public.subscriptions (user_id);
create index audit_logs_user_id_idx on public.audit_logs (user_id);
create index audit_logs_timestamp_idx on public.audit_logs (timestamp);

Updated Timestamps

Automatic updated_at triggers:

create or replace function update_updated_at_column()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger update_users_updated_at
  before update on public.users
  for each row execute function update_updated_at_column();

create trigger update_subscriptions_updated_at
  before update on public.subscriptions
  for each row execute function update_updated_at_column();

Database

On this page