Add a Database Table

What you are building

A new Postgres table in your Supabase project, with RLS enabled and no policies (the deny-all posture). The TypeScript types are regenerated to include the new table, and access is exposed through a Server Action that uses the typed createAdminClient().

Step 1: add the SQL

Append the CREATE TABLE statement to supabase/schema.sql. The pattern enforced across the existing tables:

-- Notes table: per-user free-form notes
CREATE TABLE public.notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Enable RLS. NO policies. anon key has zero access.
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- Index the foreign key
CREATE INDEX idx_notes_user_id ON public.notes(user_id);

Three properties matter here:

• ON DELETE CASCADE on the user FK. When a user is deleted from auth.users, their notes go with them. No orphan rows, no manual cleanup.
• ALTER TABLE ... ENABLE ROW LEVEL SECURITY without any CREATE POLICY. This is the deny-all posture: the anon key cannot read or write the table at all. Server Actions using createAdminClient() bypass RLS and get full access. This is what makes the SecureStartKit data access pattern work.
• Index the foreign key. Postgres does NOT auto-index foreign keys. Without idx_notes_user_id, a query filtering by user_id does a sequential scan and slows down as the table grows.

Step 2: run the migration

Open the Supabase SQL Editor (Dashboard > SQL Editor > New query), paste the SQL, and run it. Confirm the table exists under Database > Tables.

For repeatable migrations across environments, you can also use the Supabase CLI's migration system. The default template uses the single schema.sql file approach because it is simpler to reason about for solo developers; the CLI migration system is optional.

Step 3: regenerate TypeScript types

The typed Supabase client (createAdminClient<Database>()) is parameterized by Database types generated from your actual schema. Regenerate after every table change:

npx supabase gen types typescript --project-id YOUR_PROJECT_REF > lib/supabase/database.types.ts

Find your project ref under Supabase Dashboard > Settings > General. After this, admin.from('notes') is fully typed; misspelling a column name fails the TypeScript build.

Step 4: expose access through a Server Action

The new table is unreachable from the browser by design. Add a Server Action that wraps each operation with validation + identity + authorization (the add a Server Action recipe walks through the canonical pattern):

// actions/notes.ts
'use server'

import { z } from 'zod'
import { createAdminClient, getUser } from '@/lib/supabase/server'

const noteSchema = z.object({
  title: z.string().min(1).max(200),
  body: z.string().max(10000).optional(),
})

export async function createNote(data: z.infer<typeof noteSchema>) {
  const parsed = noteSchema.safeParse(data)
  if (!parsed.success) {
    return { error: 'Invalid input' }
  }

  const user = await getUser()
  if (!user) {
    return { error: 'Not authenticated' }
  }

  const admin = createAdminClient()
  const { data: note, error } = await admin
    .from('notes')
    .insert({
      user_id: user.id,
      title: parsed.data.title,
      body: parsed.data.body,
    })
    .select()
    .single()

  if (error) {
    console.error('Failed to create note:', error)
    return { error: 'Failed to create note' }
  }

  return { data: note }
}

export async function getMyNotes() {
  const user = await getUser()
  if (!user) {
    return { error: 'Not authenticated' }
  }

  const admin = createAdminClient()
  const { data, error } = await admin
    .from('notes')
    .select('*')
    .eq('user_id', user.id)
    .order('created_at', { ascending: false })

  if (error) return { error: 'Failed to load notes' }
  return { data }
}

The pattern repeats: validate, read identity from session, scope every query by user.id. Even though createAdminClient bypasses RLS, your Server Action enforces the same ownership check in code. RLS is the safety net; the Server Action is the primary authorization layer.

When to add an RLS policy

If you want any data access outside the Server Action layer (e.g., a Supabase Realtime subscription that filters by user_id automatically, or a Postgres function callable from the client), add explicit RLS policies. The deny-all default is the secure baseline; you OPT IN to specific access patterns, you do not opt out.

-- Allow authenticated users to read their own notes
CREATE POLICY "Users read own notes" ON public.notes
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

For the full RLS pattern catalog, see Supabase RLS policies that actually work. For multi-tenant patterns where rows are scoped by tenant_id from JWT claims, see multi-tenancy and RBAC in Supabase.

Common mistakes

• Forgetting ENABLE ROW LEVEL SECURITY. A new table without RLS enabled is readable by the anon key by default. Always enable RLS, even if you do not write policies.
• Not regenerating types. Without fresh types, TypeScript treats the new table as unknown and you lose every type-safety guarantee for queries against it.
• Skipping the FK index. Sequential scans on a growing user-scoped table is one of the easiest performance bugs to ship and one of the hardest to diagnose later.
• Trusting RLS instead of Server-Action checks. Use both. RLS is the fail-closed safety net; the Server Action authorization check is the primary defense and the layer that returns useful error messages.