Usage-based restrictions for SaaS subscription tiers

Usage-based restrictions are used to differentiate the value provided by each subscription tier. In this video, Jon Meyers uses a combination of PostgreSQL Functions, Triggers and RLS policies to ensure a user is only able to do the number of things their subscription tier allows!
Full playlist: • Fastest way to build a...
Additionally, he uses the Supabase CLI to create a shadow database, diff schemas and automatically generate a Postgres migration file, using a git-like "push and pull" workflow to keep multiple instances of Supabase in sync. This allows new features to be collaboratively developed, and safely rolled out to production, without breaking your SaaS product! 🚀
00:00 Usage-based restrictions for subscription tiers in SaaS product
04:21 Generate Postgres migration file with Supabase CLI
09:17 Creating the table we want usage-based restrictions for
12:12 Writing database access rules with Row Level Security (RLS) policies
17:13 PostgreSQL Functions and Triggers to implement business rules
31:35 Push local schema changes to prod using Postgres migrations
💻 Videos to watch next:
▶ Fastest way to build a SaaS in 2024 - Next.js, Supabase and Stripe: • Fastest way to build a...
▶ Fastest way to get started with Next.js and Supabase in 2024: • FASTEST way to get sta...
▶ Implement Authorization using Row Level Security with Supabase: • Implement Authorizatio...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: github.com/supabase
💬 Discord: discord.supabase.com/
🐦 Twitter: / supabase
📸 Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@yarapolana]

this is exactly what I was looking for, understanding the db side of subscriptions, with rules and such. Thank you so much for the video.

[@JonMeyers]

Awesome! Glad you enjoyed it! 🙌

[@nicken86]

So thankful for this playlist! Any chance you could walk through how to handle if a user downgrades from a higher level subscription to a lower one, and how to handle that with the rules setup?

[@ewwitsantonio]

Supa helpful tutorials! Thanks for creating these videos. :)

[@JonMeyers]

Thanks for watching! Glad you’re enjoying them 🙌

[@jrdnrc]

jon the legend did it again

[@JonMeyers]

Thanks Jordi! Glad you enjoyed it! 🙌

[@selambengp]

What a lovely reminder of the db password 😂❤

[@JonMeyers]

I wish projects had analytics on number of times the database password had been reset! Much faster than a password manager! 😂

[@codewrangler]

Yet another great video Jon! The way you walked us through the function logic was fantastic. Question: does the raise exception message get passed back to the supabase query on the client side or just in the PG backend?

[@JonMeyers]

Thanks! Glad you enjoyed it! Yep, the exception gets returned as an ‘error’ in supabase-js 👍

[@R0cky0]

Great tutorial Jon. A couple of questions: 1. did you generate the random text IDs with prefix for product/sub/price_* through JS? does Supabase have such function other than generating uuid? 2. Would do a quick walkthrough on how to apply the expiration date rule and possibly with a notification before the plan expires? Many Thanks! 🙏

[@JonMeyers]

Thanks for watching! Glad you enjoyed it! 🙌 1. These were created in the Stripe dashboard, triggering webhook events that are sent to Supabase 👍 2. That’s a great idea! Will add it to the list!

[@Oreoezi]

Why can't we use the RLS insert policy instead of a trigger to make sure a user cannot insert a row if they exceed the limit?

[@JonMeyers]

You can enforce this with RLS but it is better practice to separate your security and business logic. Security-wise you want the user to be authenticated to insert, and only insert a row that belongs to them. Enforcing the limits of each subscription tier is business logic. Security -> RLS Business Logic -> Trigger But you can implement it however you prefer in your app 👍

[@StefanRows]

For those where pnpm supabase:pull doesn't work: npx supabase db pull Also, npx supabase migration up works. No idea why the pnpm commands fail for me (latest version on everything)

[@JonMeyers]

Good call! I think I tinkered with the package.json scripts to record the video - need to push those up to the template 👍

[@damianocellai1783]

Hello, what happen if i go to a plan that let me create a 5 notes and later i downgrade? They let me downgrade? How handle this with Stripe, Next and Supabase? Thanks a lot for your content

[@JonMeyers]

Currently, this would allow you to keep the notes, you just couldn’t add any new notes until you upgrade again. But you handle this anyway you prefer. Could add an “active” column to each of the notes and automatically deactivate the ones above 5 if they downgrade their subscriptions

[@rtorcato]

if the user goes pro adds more notes then the limit then cancels their pro plan they still get to keep more notes allowed.

[@JonMeyers]

Yep, correct! Currently, they would keep the extra notes, but you can handle this in your app any way you want 👍 One option would be add a column for “is_active” to the notes table. You could automatically set the notes above 5 to inactive when the user downgrades their subscription. Or set up a grace period of 7 days for them to delete the additional notes or upgrade their subscription

[@Tanner-cz4bd]

Can we get a full next js tutorial building a high quality CRUD app

[@JonMeyers]

Will add it to the list 👍

[@Tanner-cz4bd]

@@JonMeyers thanks

[@yarapolana]

Can we get another remix auth tutorial with @supabase/ssr?

[@JonMeyers]

Working on it! 👍