Building Production-Grade Web Applications with Supabase: A comprehensive guide to database design

✍ Scribed by David Lorenz

• Publisher: Packt Publishing Pvt Ltd
• Year: 2024
• Tongue: English
• Leaves: 534
• Category: Library

✦ Synopsis

Craft resilient web applications with Supabase by leveraging advanced features such as authentication, data and user management, and seamless AI integration using its powerful Postgres infrastructure

Key Features
Learn how to integrate Supabase and Next.js to create powerful and scalable web apps
Explore real-world scenarios with a multi-tenant ticket system
Master real-time data handling, secure file storage, and application security enhancement, while discovering the full potential of the database beyond holding data
Purchase of the print or Kindle book includes a free PDF eBook
Book Description
Discover the powerful capabilities of Supabase, the cutting-edge, open-source platform flipping the script on backend architecture. Guided by David Lorenz, a battle-tested software architect with over two decades of development experience, this book will transform the way you approach your projects and make you a Supabase expert.

In this comprehensive guide, you'll build a secure, production-grade multi-tenant ticket system, seamlessly integrated with Next.js. You’ll build essential skills for effective data manipulation, authentication, and file storage, as well as master Supabase's advanced capabilities including automating tasks with cron scheduling, performing similarity searches with artificial intelligence, testing your database, and leveraging real-time updates.

By the end of the book, you'll have a deeper understanding of the platform and be able to confidently utilize Supabase in your own web applications, all thanks to David's excellent expertise.

What you will learn
Explore essential features for effective web app development
Handle user registration, login/logout processes, and user metadata
Navigate multi-tenant applications and understand the potential pitfalls and best practices
Discover how to implement real-time functionality
Find out how to upload, download, and manipulate files
Explore preventive measures against data manipulation and security breaches, ensuring robust web app security
Increase efficiency and streamline task automation through personalized email communication, webhooks, and cron jobs

Who this book is for
This book is for developers looking for a hassle-free, universal solution to building robust apps using Supabase and its integration libraries. While a basic understanding of JavaScript is useful, it’s not essential as the book focuses on Supabase for creating high-performance web apps using Next.js. Experienced professionals from non-JavaScript backgrounds will find this book useful. Familiarity with Postgres, although helpful, is not mandatory as the book explains all the SQL statements used.

✦ Table of Contents

Building Production-Grade Web Applications 
with Supabase
Foreword
Contributors
About the author
About the reviewers
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Join Us on Discord
Share Your Thoughts
Download a free PDF copy of this book
Part 1:Creating the Foundations of the Ticket System App
1
Unveiling the Inner Workings of Supabase and Introducing the Book’s Project
Technical requirements (and some preamble)
Understanding why Supabase is the stack you want
Demystifying the inner workings of Supabase with Postgres
Access logic within a route
Access logic as a central service
How Supabase handles access control
How the access system works under the hood
Supabase Studio – the convenient web dashboard
Supabase Auth (GoTrue) – the authentication handler
PostgREST – a REST and GraphQL API for your database
Realtime – elevating the user experience
Storage – simple and scalable object storage
Image Proxy – helping to transform images on the fly
Edge Functions – completing the optimization stack
pg-meta – an internal helper service for the database
Kong – the overarching service orchestrator
Introducing the production-grade ticket system project
Summary
2
Setting Up Supabase with Next.js
Technical requirements
Getting ready with Next.js
Installing the Supabase CLI
Running your first Supabase instance on your machine
Initializing a new local Supabase instance
Starting your first Supabase instance
Managing multiple local Supabase instances
Option 1 – the start-stop technique
Option 2 – change ports
Connecting to Supabase with the Supabase 
JavaScript client
Initializing and testing the base Supabase JavaScript client within Next.js
Understanding the base Supabase client
Using the Supabase client with Pages Router and App Router
Connecting directly to the database
Using Supabase with TypeScript
Connecting Supabase to other frameworks
Nuxt 3
Python
Summary
3
Creating the Ticket Management Pages, Layout, and Components
Technical requirements
Setting up Pico.css with Next.js
Building the login form
Visualizing the Ticket Management UI
Creating a shared UI layout with navigation elements
Designing the Ticket List page
Constructing the Ticket Details page
Adding the comments section to the ticket details
Implementing a page to create a new ticket
Implementing a user overview
Enhancing the navigation component
Summary
Part 2: Adding Multi-Tenancy and Learning RLS
4
Adding Authentication and Application Protection
Technical requirements
Adding authentication protection with Supabase
Creating users
Preparing the middleware for authentication
Implementing the login functionality in our app
Protecting access to the Ticket Management system
Adding a log out button
Logging out using the frontend
Logging out using the backend
Understanding server authentication
Enhancing the password login
Authenticating with magic links
Sending magic links with signInWithOtp() on the frontend
Why I usually don’t use signInWithOtp()
Understanding a server-only magic link flow
Implementing a server-only magic link flow with custom email content
Adding password recovery
Learning about the Site URL and redirect URLs
How to configure site and redirect URLs
Optional knowledge: adapting built-in templates
Summary
5
Crafting Multi-Tenancy through Database and App Design
Technical requirements
What kind of multi-tenancy do we need?
Designing the database for multi-tenancy
Planning our database
Creating the tenants table
Designing the users table
Designing the permission structure
Committing your database state (if you don’t seed it, you lose it)
Making our Next.js application tenant-aware
Enhancing the middleware to safeguard dynamic routes
Fixing all static routes in the application
Making the login tenant-based
Summary
6
Enforcing Tenant Permissions with RLS and Handling Tenant Domains
Technical requirements
Learning to work with RLS
Fetching tenant data with the restrictive Supabase client
Defining RLS policies to access tenants based on permissions
Creating a permission-based RLS policy
Understanding and solving RLS implications
Shrinking RLS policies based on the implications
Learning about RLS implications
Minimizing RLS complexity with custom claims
Extending app_metadata with tenant permissions
Keeping custom claims in sync with the table data
Making the authentication process tenant-based
Preventing password login on a foreign tenant
Preventing the magic link login for foreign tenants
Rejecting to visit invalid and forbidden tenant URLs when signed in
Matching a tenant per domain instead of per path
Adding custom domains via the hosts file
Mapping domains in our application
Bringing back localhost with mapped domains
Summary
7
Adding Tenant-Based Signups, including Google Login
Technical requirements
Understanding the impact of disabling signups
Disabling signups generally
Disabling specific signup methods
Implementing the registration page
Processing the registration with a Route Handler
Reading and validating the form data
Rejecting registration
Handling account creation
Adding the service user and permission rows
Sending the activation email
Redirecting the user to a success page
Enabling OAuth/Sign-in with Google
Obtaining Google OAuth credentials
Configuring our Supabase instance with the OAuth credentials
Adding a “Sign in with Google” option triggering the OAuth process
Solving the crypto/HTTPS security problem
Building a verification route to finalize the registration
Dealing with invalid user registration
Summary
Part 3: Managing Tickets and Interactions
8
Implementing Dynamic Ticket Management
Technical requirements
Creating the tickets table in the database
Creating tickets and using triggers
Implementing the ticket creation logic
Using triggers to derive and set the user ID
Improving loading behavior after adding a ticket
Enforcing checks on the database columns
Viewing the ticket details
Caching the author’s name with a trigger
Improving the date and status view
Listing and filtering tickets
Enabling paging
Sorting tickets
Creating a ticket filter
Deleting tickets
Summary
9
Creating a User List with RPCs and Setting Ticket Assignees
Technical requirements
Adding a user list with an RPC
Ensuring there are enough users to test
Enhancing the table structure
Fetching the users with an RPC
Using the function with an RPC
Allowing the setting and editing of an assignee to a ticket
Adding assignee columns in the tickets table
Creating the trigger function to cache the name
Adding an assignee at ticket creation
Showing the assignee in the details
Updating the assignee
Summary
10
Enhancing Interactivity with Realtime Comments
Technical requirements
Creating the comments table
Adding a trigger to set the tenant automatically
Adding and optimizing RLS policies
Creating RLS helper functions
Creating the policies
Implementing comment creation
Listing existing comments from the server
Implementing Realtime comments
Enabling Realtime and subscribing to it
Updating the UI with Realtime data
Triggering impersonated real-time updates with the Table Editor
Embracing additional Realtime insights and learning about potential pitfalls
Summary
11
Adding, Securing, and Serving File Uploads with Supabase Storage
Technical requirements
Creating and understanding Storage buckets
Examining public buckets
Exploring files within a bucket programmatically
Learning how a basic RLS policy can be added to your bucket
Understanding private buckets and revising our bucket choice
Choosing a private or a public bucket?
Enabling the addition of comments with file attachments
Preparing the UI with file upload possibility
Uploading files to storage
Connecting uploaded files with the written comment
Showing the connected files
Serving image attachments directly in the UI
Using Image Transformations
Building a pseudo-CDN for private buckets
Using the pseudo-CDN inside our UI
Writing RLS policies directly on buckets and objects table
Diving into advanced storage restrictions
Summary
Part 4: Diving Deeper into Security and Advanced Features
12
Avoiding Unwanted Data Manipulation and Undisclosed Exposures
Technical requirements
Understanding PostgREST’s OpenAPI Schema exposure
Preventing schema exposure
Removing schemas from usage via API
Specifically exposing a schema to the API
Being careful with current_user usage and understanding auth.role()
Generating new Anonymous Keys, Service Role Keys, and database passwords
Benefiting from Supabase Vault
Creating secrets in the Vault and reading them
Using the secret in the business logic/within your application
Utilizing silent resets to avoid data manipulation
Enabling column-level security/working with roles
Understanding security on views and manually created tables
Changing the max_rows configuration
Understanding safe-guarded API updates or deletion
Adding middleware inside Postgres for each API request
Adding middleware for PostgREST
Using the Security Advisor
Allowing a listing of IPs for database connections
Enforcing SSL on direct database connections
Summary
13
Adding Supabase Superpowers and Reviewing Production Hardening Tips
Technical requirements
Making sense of search_path
Comprehending search path in Postgres
Grasping the importance of extra_search_path
Familiarizing yourself with database extensions
Installing an extension in the default extensions schema
Installing extensions in their own schema
Using the programmatic installation of extensions versus using the UI
Adding an AI-based semantic ticket search
Deciding on an embeddings provider
Creating the embeddings column in the table
Creating embeddings with OpenAI
Comparing embeddings to find matching search results
Using anonymous sign-ins
Transforming external APIs into tables with foreign data wrappers
Using webhooks
Creating webhooks with dynamic URLs per environment
Understanding Edge Functions
Understanding when to use Edge Functions
Creating an Edge Function that runs for new rows
Triggering the Edge Function
Using cronjobs to notify about due tickets
Using pg_jsonschema for JSON data integrity
Testing the database with pgTAP
Setting the auth.storageKey to avoid migration problems
Extending supabase.ts with custom typings
Improving RLS and query performance
Identifying database performance problems and bloat
Working with complex table joins
Reviewing the underestimated benefit of using an external database client
Understanding migrations
Utilizing database branching
Disabling GraphQL or PostgREST (if you don’t need it)
Using a dead-end built-in mailing setup
Retrieving table data with the REST API and cURL
Summary
Index
Why subscribe?
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book