Database

Supabase PostgreSQL schema, tables, RLS policies, triggers, and migration patterns.

Overview

ScaleRocket uses a database to store users, subscriptions, and credits. The schema and migration approach differ depending on your backend.

ScaleRocket uses Supabase (PostgreSQL) as its database. The schema lives in supabase/migrations/ and is version-controlled. Every table has Row Level Security (RLS) enabled by default.

ScaleRocket uses Convex as its database. The schema is defined in TypeScript at convex/schema.ts and is automatically synced when you run npx convex dev. Convex handles access control through function-level authorization.

Tables

profiles

Created automatically when a user signs up via a database trigger.

Column	Type	Description
`id`	`uuid`	References `auth.users.id`
`email`	`text`	User email
`full_name`	`text`	Display name
`avatar_url`	`text`	Profile picture URL
`created_at`	`timestamptz`	Account creation date
`updated_at`	`timestamptz`	Last profile update

subscriptions

Tracks Stripe subscription state for each user.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	References `profiles.id`
`stripe_customer_id`	`text`	Stripe customer ID
`stripe_subscription_id`	`text`	Stripe subscription ID
`plan_id`	`text`	Plan identifier
`status`	`text`	`active`, `canceled`, `past_due`, etc.
`current_period_end`	`timestamptz`	When the current billing period ends
`created_at`	`timestamptz`	Subscription creation date
`updated_at`	`timestamptz`	Last update

credits

Stores the credit balance for each user.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	References `profiles.id`
`balance`	`integer`	Current credit balance
`monthly_allowance`	`integer`	Credits granted per billing cycle
`last_reset_at`	`timestamptz`	Last monthly reset date
`updated_at`	`timestamptz`	Last modification

Schema Definition

The schema is defined through SQL migration files in supabase/migrations/. Each migration is a .sql file that runs sequentially.

The schema is defined in TypeScript at convex/schema.ts:

// convex/schema.ts
import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  profiles: defineTable({
    userId: v.string(),
    email: v.string(),
    fullName: v.optional(v.string()),
    avatarUrl: v.optional(v.string()),
  }).index("by_userId", ["userId"]),

  subscriptions: defineTable({
    userId: v.string(),
    stripeCustomerId: v.string(),
    stripeSubscriptionId: v.string(),
    planId: v.string(),
    status: v.string(),
    currentPeriodEnd: v.number(),
  }).index("by_userId", ["userId"])
    .index("by_stripeCustomerId", ["stripeCustomerId"]),

  credits: defineTable({
    userId: v.string(),
    balance: v.number(),
    monthlyAllowance: v.number(),
    lastResetAt: v.number(),
  }).index("by_userId", ["userId"]),
});

Schema changes are applied automatically when you run npx convex dev.

Row Level Security (RLS)

Every table has RLS enabled. Users can only access their own data:

-- profiles: users can read and update their own profile
CREATE POLICY "Users can view own profile"
  ON profiles FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id);

-- subscriptions: users can only read their own subscription
CREATE POLICY "Users can view own subscription"
  ON subscriptions FOR SELECT
  USING (auth.uid() = user_id);

-- credits: users can only read their own credits
CREATE POLICY "Users can view own credits"
  ON credits FOR SELECT
  USING (auth.uid() = user_id);

Service role (used by Edge Functions and admin panel) bypasses RLS entirely.

Convex does not use RLS. Instead, access control is enforced at the function level. Each query or mutation verifies the user's identity:

// convex/profiles.ts
import { query } from "./_generated/server";
import { getAuthUserId } from "@convex-dev/auth/server";

export const getProfile = query({
  handler: async (ctx) => {
    const userId = await getAuthUserId(ctx);
    if (!userId) throw new Error("Unauthorized");

    return await ctx.db
      .query("profiles")
      .withIndex("by_userId", (q) => q.eq("userId", userId))
      .unique();
  },
});

Triggers

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

In Convex, profile creation is handled in the auth callback or a mutation triggered after signup:

// convex/users.ts
import { internalMutation } from "./_generated/server";
import { v } from "convex/values";

export const createProfile = internalMutation({
  args: { userId: v.string(), email: v.string(), fullName: v.optional(v.string()) },
  handler: async (ctx, args) => {
    await ctx.db.insert("profiles", {
      userId: args.userId,
      email: args.email,
      fullName: args.fullName,
    });
  },
});

Auto-update `updated_at`

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Applied to all tables
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Indexes

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_stripe_customer_id ON subscriptions(stripe_customer_id);
CREATE INDEX idx_credits_user_id ON credits(user_id);

Adding New Tables

Create a new migration:

pnpm supabase migration new add_my_table

Edit the generated file in supabase/migrations/:

CREATE TABLE my_table (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  content text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can CRUD own rows"
  ON my_table FOR ALL
  USING (auth.uid() = user_id);

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON my_table
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Apply the migration:

pnpm supabase db push

Add the table to convex/schema.ts:

// convex/schema.ts
export default defineSchema({
  // ... existing tables
  myTable: defineTable({
    userId: v.string(),
    content: v.optional(v.string()),
  }).index("by_userId", ["userId"]),
});

The schema is applied automatically when you run:

npx convex dev

No migration files needed -- Convex handles schema changes automatically.

Type Generation

Generate TypeScript types from your database schema:

pnpm supabase gen types typescript --local > packages/types/src/database.ts

This updates the Database type used across all apps. Run this after every migration.

Convex generates TypeScript types automatically from convex/schema.ts. Types are available via the _generated directory and are updated in real-time when you run npx convex dev.

Done reading? Mark this page as complete.

Database

On this page