Article SAAS - back-end integration

The front-end functionality of my world simplest SAAS project is now complete enough that I want to start adding some real back-end services. This is so that I can firstly add functional authentication and persistence, and eventually add web hook integration to a payment provider.

I am going to use Supabase for the back-end for this project.  It is a great, well-documented set of services that has a fully functional free usage tier.  It is a one-stop shop for auth, persistence, web hooks and many other features. It is based on open source products, it supports both client-only and multi-tier architectures and it supports a local development model where you can build your solution on a local instance of the Supabase stack. What is not to like?

Initial Setup

To get started, I have created a free tier account and a Supabase project for the article site.

I then followed this guide  to setting up a local version of the Supabase stack and linking it to my project.

With the Getting Started steps complete, I have a local installed version of the Supabase environment that I can start using:

Database Schema

Another really great feature of Supabase is that the locally stack still includes the full Studio UI for managing the project.  This means that I can quickly set up my initial database schema:

Supabase is designed primarily for client-only applications where the browser hosted application is interacting directly with the persistence layer. To enable this to occur securely, Supabase supports row-level access policies for its database tables.  These policies define which operations and which rows uses can perform on each table.   These policies often require tuning as you develop and test the solution, but it is a good idea to set up some initial policies when you define your tables.  

With the initial database schema defined, we can create a migration that exports the postgres-sql that can be used to recreate or publish this schema later

>npx supabase migration new create_initial_schema

will create a new  migration file: 

>npm supabase db diff --schema public

will give a dump of the SQL required to migrate our database is its current status.

using a text editor to copy this sql to a migration file will preserve this state change and enable us to scrip the rebuild of the database in the future.

User Accounts

Creating user accounts is a little trickier. The Supabase authentication service uses its own database schema to manage user authentication. This table stores the 'master record' for each user identity, but is purely concerned with authentication.

We also want to store own own details for our user in the user_accounts table, and we want to make sure that  a user_accounts  record is created whenever a new user signs up.  

Also, by default Supabase doesn't give you a user interface for setting or resetting a user's password. This is probably to reduce security risks. 

Eventually we will manage the account creation through the user sign-up process on our site, but in the mean time we can inject some test users via SQL.

This Gist (not mine - I found it with a google search ) shows how to inject users into the Supabase auth schema.

Once we have done that, we can run a simple SQL statement to populate our user_accounts table based on the currently defined users

Once those scripts are executed, we have some test account to play with

To make this a repeatable process, we can add this SQL to the database seeding script supabase/seed.sql

This will ensure that these accounts are created whenever we reset the database, and gives us a repeatable way of setting our test database to a known state.

Conclusion

With the above steps complete, I now have a scaffolded back end that I can start integrating into the site. The next step will be to wire up our services layer to Supabase, then create the user interface for signing up and logging in  users.