Supabase Tables

Populating the Supabase Postgres database with actual structure.

Now that we have a link with both Supabase and Lemon Squeezy, we can start populating our database.

Populating the Supabase database

Within Supabase, you can create custom roles. These roles can be used to make sure only users with the correct role can select, update, delete data - Authorization.

Together with tables, we can populate the first part of our database. In this population, we have also integrated relations. The reason we use relations is simple: we want to make sure that data can only be created with correct information. If you can insert a random value for user, then your database structure is incomplete. This can cause issues with rendering the data in your application later.

Downloading the correct file

Within your Supaboost-V1.0 license, you have access to the .ZIP file Supaboost-V1.0-SQL-Scripts. In this zip file, there are three text documents.

  1. Create tables and roles
  2. Create functions and triggers
  3. Create Row Level Security for Tables

This section will cover the file 1. Create tables and roles.

Creating the Tables

In the 1. Create tables and roles there will be multiple rows. Which tables are we creating?

  • Todos
    • This is basic functionality, to display the possibilities of what a Supaboost app can do
  • User
    • This table is used to create profiles of users
  • User_info
    • User_info is used to create non-changeable user information, such as company, is_active and if the user is the first within a company
  • Company
    • Changeable Company information, such as name, country, VAT identifier and Chamber of Commerce number
  • Company_info
    • Used to register subscription for a company, including status, customer_id (within Lemon Squeezy) and the variant of the subscription they are linked to
  • Members
    • Used as a link table to connect users and companies

Adding the information to Supabase

Now comes the good part, let's get our database structure in!

  1. Open Supabase
  2. Log in to your project
  3. Open the SQL Editor
  4. Then select + New query Create a new Supabase SQL query
  5. Select New blank query
  6. Open the Untitled query dropdown
  7. Select Rename query Rename the query
  8. Enter the name of the text document: 1. Create tables and roles
  9. Paste the SQL statement of the text document in the Text Editor Enter the code into the Supabase SQL editor
  10. Then select Run CTRL
  11. If everything went well you should see the result Success. No rows returned

When we now go to the table Editor, you will see that we have created 7 tables in Supabase!

Populated Supabase Database

AuthenticationFunctions and Triggers