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
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
# Create a new schema file with the next numbertouch apps/web/supabase/schemas/18-notes-feature.sql
2. Apply Development Workflow
# Start Supabasepnpm supabase:web:start# Create migration from your schema filepnpm --filter web run supabase:db:diff -f notes-feature# Restart with new schemapnpm supabase:web:reset# Generate TypeScript typespnpm supabase:web:typegen
Permissions and Access Control
Adding New Permissions
Makerkit defines permissions in the public.app_permissions
enum. Add feature-specific permissions:
-- Add new permissions for your featureALTER 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;
Permission Naming Convention: Use the pattern resource.action
for consistency:
notes.create
- Create new notesnotes.manage
- Edit existing notesnotes.delete
- Delete notesnotes.share
- Share with external users
Role Assignment
Consider which roles should have which permissions by default:
-- Grant permissions to rolesINSERT 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
-- Create the main notes table with all standard fieldsCREATE 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 documentationCOMMENT 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.
-- Essential indexes for performanceCREATE 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
-- Always enable RLS (NEVER skip this!)ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;-- Revoke default permissions and grant explicitlyREVOKE 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:
-- SELECT policy: Read published notes or own private notesCREATE 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 permissionCREATE 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 editCREATE 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 requiredCREATE 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:
-- Automatically update timestampsCREATE TRIGGER notes_updated_at BEFORE UPDATE ON public.notes FOR EACH ROW EXECUTE FUNCTION kit.update_updated_at();-- Track who made changesCREATE 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)
-- Note categories with hierarchyCREATE 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 categoriesALTER TABLE public.notes ADD COLUMN category_id uuid REFERENCES public.note_categories(id) ON DELETE SET NULL;-- Index for tree operationsCREATE 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
-- External sharing tokensCREATE 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 tokensCREATE 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
-- Track note creation for usage-based billingCREATE 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 usageCREATE 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 creationCREATE 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
-- Check if account has access to advanced note featuresCREATE 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 featuresCREATE 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:
-- Require MFA for note deletionCREATE 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:
-- 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:
-- Rate limiting: max 100 notes per day per accountCREATE 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 neededEND;$$ LANGUAGE plpgsql;-- Policy to enforce rate limitingCREATE 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:
# reset the databasepnpm supabase:web:reset# Generate new typespnpm supabase:web:typegen# Verify types work in your applicationpnpm typecheck
Example Usage in Application
With your schema complete, here's how to use it in your application:
// Server component - automatically inherits RLS protectionimport { 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.
// 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!