How to create new migrations and update the database schema in your Next.js Supabase application

Learn how to create new migrations and update the database schema in your Next.js Supabase application

Steps to create a new migration

Learn how to create new migrations and update the database schema in your Next.js Supabase application

1

Planning Your Schema Extension

2

Creating Schema Files

3

Permissions and Access Control

4

Building Tables with RLS

5

Advanced Patterns

6

Testing and Deployment

This guide walks you through extending Makerkit's database schema with new tables and features. We'll use a comprehensive example that demonstrates best practices, security patterns, and integration with Makerkit's multi-tenant architecture.

Planning Your Schema Extension

Before writing any SQL, it's crucial to understand how your new features fit into Makerkit's multi-tenant architecture.

Decision Framework

Step 1: Determine Data Ownership Ask yourself: "Who owns this data - individual users or accounts?"

  • User-owned data: Personal preferences, activity logs, user settings
  • Account-owned data: Business content, shared resources, collaborative features

Step 2: Define Access Patterns

  • Public within account: All team members can access
  • Private within account: Only creator + specific permissions
  • Admin-only: Requires special permissions or super admin access

Step 3: Consider Integration Points

  • Does this feature affect billing? (usage tracking, feature gates)
  • Does it need notifications? (in-app alerts, email triggers)
  • Should it have audit trails? (compliance, change tracking)

Creating Schema Files

Makerkit organizes database schema in numbered files for proper ordering. Follow this workflow:

1. Create Your Schema File

bash
# Create a new schema file with the next number
touch apps/web/supabase/schemas/18-notes-feature.sql

2. Apply Development Workflow

bash
# Start Supabase
pnpm supabase:web:start
# Create migration from your schema file
pnpm --filter web run supabase:db:diff -f notes-feature
# Restart with new schema
pnpm supabase:web:reset
# Generate TypeScript types
pnpm supabase:web:typegen

Permissions and Access Control

Adding New Permissions

Makerkit defines permissions in the public.app_permissions enum. Add feature-specific permissions:

sql
-- Add new permissions for your feature
ALTER TYPE public.app_permissions ADD VALUE 'notes.create';
ALTER TYPE public.app_permissions ADD VALUE 'notes.manage';
ALTER TYPE public.app_permissions ADD VALUE 'notes.delete';
COMMIT;

NB: the Supabase diff function does not support adding new permissions to enum types. Please add the new permissions manually instead of using the diff function.

Permission Naming Convention: Use the pattern resource.action for consistency:

  • notes.create - Create new notes
  • notes.manage - Edit existing notes
  • notes.delete - Delete notes
  • notes.share - Share with external users

Role Assignment

Consider which roles should have which permissions by default:

sql
-- Grant permissions to roles
INSERT INTO public.role_permissions (role, permission) VALUES
  ('owner', 'notes.create'),
  ('owner', 'notes.manage'),
  ('owner', 'notes.delete'),
  ('owner', 'notes.share'),
  ('member', 'notes.create'),
  ('member', 'notes.manage');

Building Tables with RLS

Let's create a comprehensive notes feature that demonstrates various patterns and best practices.

Core Notes Table

sql
-- Create the main notes table with all standard fields
CREATE TABLE IF NOT EXISTS public.notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  title varchar(500) NOT NULL,
  content text,
  is_published boolean NOT NULL DEFAULT false,
  tags text[] DEFAULT '{}',
  metadata jsonb DEFAULT '{}',
  
  -- Audit fields (always include these)
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES auth.users(id),
  updated_by uuid REFERENCES auth.users(id),
  
  -- Data integrity constraints
  CONSTRAINT notes_title_length CHECK (length(title) >= 1),
  CONSTRAINT notes_account_required CHECK (account_id IS NOT NULL)
);
-- Add helpful comments for documentation
COMMENT ON TABLE public.notes IS 'User-generated notes with sharing capabilities';
COMMENT ON COLUMN public.notes.account_id IS 'Account that owns this note (multi-tenant isolation)';
COMMENT ON COLUMN public.notes.is_published IS 'Whether note is visible to all account members';
COMMENT ON COLUMN public.notes.tags IS 'Searchable tags for categorization';
COMMENT ON COLUMN public.notes.metadata IS 'Flexible metadata (view preferences, etc.)';

Performance Indexes

Consider creating indexes for your query patterns if you are scaling to a large number of records.

sql
-- Essential indexes for performance
CREATE INDEX idx_notes_account_id ON public.notes(account_id);
CREATE INDEX idx_notes_created_at ON public.notes(created_at DESC);
CREATE INDEX idx_notes_account_created ON public.notes(account_id, created_at DESC);
CREATE INDEX idx_notes_published ON public.notes(account_id, is_published) WHERE is_published = true;
CREATE INDEX idx_notes_tags ON public.notes USING gin(tags);

Security Setup

sql
-- Always enable RLS (NEVER skip this!)
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
-- Revoke default permissions and grant explicitly
REVOKE ALL ON public.notes FROM authenticated, service_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.notes TO authenticated, service_role;

RLS Policies

Create comprehensive policies that handle both personal and team accounts:

sql
-- SELECT policy: Read published notes or own private notes
CREATE POLICY "notes_select" ON public.notes
  FOR SELECT TO authenticated
  USING (
    -- Personal account: direct ownership
    account_id = (SELECT auth.uid())
    OR
    -- Team account: member can read published notes
    (public.has_role_on_account(account_id) AND is_published = true)
    OR
    -- Team account: creator can read their own drafts
    (public.has_role_on_account(account_id) AND created_by = auth.uid())
    OR
    -- Team account: users with manage permission can read all
    public.has_permission(auth.uid(), account_id, 'notes.manage')
  );
-- INSERT policy: Must have create permission
CREATE POLICY "notes_insert" ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK (
    -- Personal account: direct ownership
    account_id = (SELECT auth.uid())
    OR
    -- Team account: must have create permission
    public.has_permission(auth.uid(), account_id, 'notes.create')
  );
-- UPDATE policy: Owner or manager can edit
CREATE POLICY "notes_update" ON public.notes
  FOR UPDATE TO authenticated
  USING (
    -- Personal account: direct ownership
    account_id = (SELECT auth.uid())
    OR
    -- Team account: creator can edit their own
    (public.has_role_on_account(account_id) AND created_by = auth.uid())
    OR
    -- Team account: users with manage permission
    public.has_permission(auth.uid(), account_id, 'notes.manage')
  )
  WITH CHECK (
    -- Same conditions for updates
    account_id = (SELECT auth.uid())
    OR
    (public.has_role_on_account(account_id) AND created_by = auth.uid())
    OR
    public.has_permission(auth.uid(), account_id, 'notes.manage')
  );
-- DELETE policy: Stricter permissions required
CREATE POLICY "notes_delete" ON public.notes
  FOR DELETE TO authenticated
  USING (
    -- Personal account: direct ownership
    account_id = (SELECT auth.uid())
    OR
    -- Team account: creator can delete own notes
    (public.has_role_on_account(account_id) AND created_by = auth.uid())
    OR
    -- Team account: users with delete permission
    public.has_permission(auth.uid(), account_id, 'notes.delete')
  );

Automatic Triggers

Add triggers for common patterns:

sql
-- Automatically update timestamps
CREATE TRIGGER notes_updated_at
  BEFORE UPDATE ON public.notes
  FOR EACH ROW
  EXECUTE FUNCTION kit.update_updated_at();
-- Track who made changes
CREATE TRIGGER notes_track_changes
  BEFORE INSERT OR UPDATE ON public.notes
  FOR EACH ROW
  EXECUTE FUNCTION kit.track_user_changes();

Advanced Patterns

1. Hierarchical Notes (Categories)

sql
-- Note categories with hierarchy
CREATE TABLE IF NOT EXISTS public.note_categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  parent_id uuid REFERENCES public.note_categories(id) ON DELETE CASCADE,
  name varchar(255) NOT NULL,
  color varchar(7), -- hex color codes
  path ltree, -- efficient tree operations
  
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES auth.users(id),
  
  -- Ensure hierarchy stays within account
  CONSTRAINT categories_same_account CHECK (
    parent_id IS NULL OR 
    (SELECT account_id FROM public.note_categories WHERE id = parent_id) = account_id
  ),
  
  -- Prevent circular references
  CONSTRAINT categories_no_self_parent CHECK (id != parent_id)
);
-- Link notes to categories
ALTER TABLE public.notes ADD COLUMN category_id uuid REFERENCES public.note_categories(id) ON DELETE SET NULL;
-- Index for tree operations
CREATE INDEX idx_note_categories_path ON public.note_categories USING gist(path);
CREATE INDEX idx_note_categories_account ON public.note_categories(account_id, parent_id);

2. Note Sharing and Collaboration

sql
-- External sharing tokens
CREATE TABLE IF NOT EXISTS public.note_shares (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  note_id uuid NOT NULL REFERENCES public.notes(id) ON DELETE CASCADE,
  share_token varchar(64) NOT NULL UNIQUE,
  expires_at timestamptz,
  password_hash varchar(255), -- optional password protection
  view_count integer DEFAULT 0,
  max_views integer, -- optional view limit
  
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES auth.users(id),
  
  -- Ensure token uniqueness
  CONSTRAINT share_token_format CHECK (share_token ~ '^[a-zA-Z0-9_-]{32,64}$')
);
-- Function to generate secure share tokens
CREATE OR REPLACE FUNCTION generate_note_share_token()
RETURNS varchar(64) AS $$
BEGIN
  RETURN encode(gen_random_bytes(32), 'base64url');
END;
$$ LANGUAGE plpgsql;

3. Usage Tracking for Billing

sql
-- Track note creation for usage-based billing
CREATE TABLE IF NOT EXISTS public.note_usage_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  action varchar(50) NOT NULL, -- 'create', 'share', 'export'
  note_count integer DEFAULT 1,
  date date DEFAULT CURRENT_DATE,
  
  -- Daily aggregation
  UNIQUE(account_id, action, date)
);
-- Function to track note usage
CREATE OR REPLACE FUNCTION track_note_usage(
  target_account_id uuid,
  usage_action varchar(50)
) RETURNS void AS $$
BEGIN
  INSERT INTO public.note_usage_logs (account_id, action, note_count)
  VALUES (target_account_id, usage_action, 1)
  ON CONFLICT (account_id, action, date)
  DO UPDATE SET note_count = note_usage_logs.note_count + 1;
END;
$$ LANGUAGE plpgsql;
-- Trigger to track note creation
CREATE OR REPLACE FUNCTION trigger_track_note_creation()
RETURNS trigger AS $$
BEGIN
  PERFORM track_note_usage(NEW.account_id, 'create');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER notes_track_creation
  AFTER INSERT ON public.notes
  FOR EACH ROW
  EXECUTE FUNCTION trigger_track_note_creation();

4. Feature Access Control

sql
-- Check if account has access to advanced note features
CREATE OR REPLACE FUNCTION has_advanced_notes_access(target_account_id uuid)
RETURNS boolean AS $$
DECLARE
  has_access boolean := false;
BEGIN
  -- Check active subscription with advanced features
  SELECT EXISTS(
    SELECT 1
    FROM public.subscriptions s
    JOIN public.subscription_items si ON s.id = si.subscription_id
    WHERE s.account_id = target_account_id
      AND s.status = 'active'
      AND si.price_id IN ('price_pro_plan', 'price_enterprise_plan')
  ) INTO has_access;
  
  RETURN has_access;
END;
$$ LANGUAGE plpgsql;
-- Restrictive policy for advanced features
CREATE POLICY "notes_advanced_features" ON public.notes
  AS RESTRICTIVE
  FOR ALL TO authenticated
  USING (
    -- Basic features always allowed
    is_published = true
    OR category_id IS NULL
    OR tags = '{}'
    OR 
    -- Advanced features require subscription
    has_advanced_notes_access(account_id)
  );

Security Enhancements

MFA Compliance

For sensitive note operations, enforce MFA:

sql
-- Require MFA for note deletion
CREATE POLICY "notes_delete_mfa" ON public.notes
  AS RESTRICTIVE
  FOR DELETE TO authenticated
  USING (public.is_mfa_compliant());

Super Admin Access

Allow super admins to access all notes for support purposes:

sql
-- Super admin read access (for support)
CREATE POLICY "notes_super_admin_access" ON public.notes
  FOR SELECT TO authenticated
  USING (public.is_super_admin());

Rate Limiting

Implement basic rate limiting for note creation:

sql
-- Rate limiting: max 100 notes per day per account
CREATE OR REPLACE FUNCTION check_note_creation_limit(target_account_id uuid)
RETURNS boolean AS $$
DECLARE
  daily_count integer;
BEGIN
  SELECT COALESCE(note_count, 0) INTO daily_count
  FROM public.note_usage_logs
  WHERE account_id = target_account_id
    AND action = 'create'
    AND date = CURRENT_DATE;
    
  RETURN daily_count < 100; -- Adjust limit as needed
END;
$$ LANGUAGE plpgsql;
-- Policy to enforce rate limiting
CREATE POLICY "notes_rate_limit" ON public.notes
  AS RESTRICTIVE
  FOR INSERT TO authenticated
  WITH CHECK (check_note_creation_limit(account_id));

Type Generation

After schema changes, always update TypeScript types:

bash
# reset the database
pnpm supabase:web:reset
# Generate new types
pnpm supabase:web:typegen
# Verify types work in your application
pnpm typecheck

Example Usage in Application

With your schema complete, here's how to use it in your application:

typescript
// Server component - automatically inherits RLS protection
import { getSupabaseServerClient } from '@kit/supabase/server-client';
async function NotesPage({ params }: { params: { account: string } }) {
  const client = getSupabaseServerClient();
  
  // RLS automatically filters to accessible notes
  const { data: notes } = await client
    .from('notes')
    .select(`
      *,
      category:note_categories(name, color),
      creator:created_by(name, avatar_url)
    `)
    .eq('account_id', params.account)
    .order('created_at', { ascending: false });
  return <NotesList notes={notes} />;
}

From the client component, you can use the useQuery hook to fetch the notes.

typescript
// Client component with real-time updates
'use client';
import { useSupabase } from '@kit/supabase/hooks/use-supabase';
function useNotes(accountId: string) {
  const supabase = useSupabase();
  
  return useQuery({
    queryKey: ['notes', accountId],
    queryFn: async () => {
      const { data } = await supabase
        .from('notes')
        .select('*, category:note_categories(name)')
        .eq('account_id', accountId);
      return data;
    }
  });
}

Summary

You've now created a comprehensive notes feature that demonstrates:

✅ Proper multi-tenancy with account-based data isolation ✅ Granular permissions using Makerkit's role system
✅ Advanced features like categories, sharing, and usage tracking ✅ Security best practices with comprehensive RLS policies ✅ Performance optimization with proper indexing ✅ Integration patterns with billing and feature gates

This pattern can be adapted for any feature in your SaaS application. Remember to always:

  • Start with proper planning and data ownership decisions
  • Enable RLS and create comprehensive policies
  • Add appropriate indexes for your query patterns
  • Test thoroughly before deploying
  • Update TypeScript types after schema changes

Your database schema is now production-ready and follows Makerkit's security and architecture best practices!