How we use Supabase at Brick

This post is part of today's Supabase Content Storm ⛈️ in preparation for their upcoming 6th #SupaLaunchWeek.

What is Brick

Brick is a Real Estate investment calculation tool with an integrated marketplace to share and find investment objects from others to invest to. We offer calculation support for Buy & Hold, Fix & Flip and new building. There are many Excel templates on the internet, but they don't work great on mobile and are often somewhat limited. So we created Brick, a Flutter app which works on Android, iOS and Web.

What is Supabase

Supabase markets themselves as an open source Firebase alternative. They combine and extend existing open source software like PostgreSQL, GoTrue, Kong and Deno to provide a Postgres database, Authentication, Storage and Edge Functions. They offer official client libraries for JavaScript and Flutter (Dart). Learn more about them in their documentation.

Why not Firebase?

One of the first BaaS (Backend-as-a-Service) on the market and the most popular is Firebase by Google. They provide client libraries for Android, iOS and Web for a long time. Firebase offers much more individual features than Supabase, but the key features Database, Authentication and Storage are most important for the platform decision. We actually started with Firebase because it's known from previous projects and well established as BaaS, but we switched while being still in development to Supabase for a couple of reasons.

Firebase

• Query possibilities are a bit restricted. Such as, filtering one field and do an ordering on another field is not possible in Firebase's database Firestore. For Brick, this was a no-go, since we want to offer a great search experience for the marketplace. For example, you may search for a rent between 1k-3k and order your result by the range to your current user location. This brings me to another restriction: Geo queries are not well-supported by Firestore.
• This is subjective, but I much prefer SQL databases over the NoSQL in Firestore. It may be easier for newbies than SQL, but I much prefer a strict schema, where every field has a fixed type and for example can't be null. In combination with PostgreSQL being relational, I think it's much easier to handle later on.

Supabase

• Supabase builds on top of a pure PostgreSQL database, while still providing you direct access to the database itself. So you don't only get Supabase with it's integrated APIs, but also a fully featured raw SQL database. This offers many more additional possibilities.
• SQL is of course more complex than a NoSQL database, but Supabase makes most use cases painless by providing a great dashboard for e.g. security rules, table and database functions creation.
• Supabase offers many extensions to, well, extend the database with even more functionalities, which aren't part of Postgres by default. One of them is PostGis, which allows you to query geometric types. Such as filtering by the distance between two objects. Firebase offers a brand-new extension marketplace now as well, but the extensions are not as powerful as the Postgres ones.

How we use Supabase

Authentication

Each user does not only have an email and password, which are stored in the auth.users table, but also a username, full name, website, etc. The auth schema is managed by Supabase itself, so we don't add new columns into the existing auth.users table. Instead, we add a new table in the public schema called profiles. Each row is referenced to the auth.users table, thus guaranteeing data integrity. We have triggers on the auth.users table for row deletion and inserting. On insert, an empty row is inserted into the profiles table in case the profile information insert fails on the client. On deletion, we delete all associated objects and their photos and documents from storage, as described later on.

Database

One advantage of NoSQL databases is their flexibility. You don't have to carefully plan your schema, which can sometimes be very handy. At Brick, we faced such situation: On the one hand, every object has the same attributes like owner, name, created_at, type. On the other hand, each object type (Buy and Hold, Fix and Flip and new building) has their own individual fields and types.
To combine these requirements, we came up with using the JSONB type for storing flexible data. This gives us the power to store same fields in the schema and flexible fields in a column of type jsonb. Supabase just published a great blog article about this approach.

But can I use them in a query, one might ask. Of course, you can: Such as querying the rent of each object.
Let's say the data column has the following format:

{
    "rent":{
        "value": 5000
        "unit": "euro"
    }
}

We can query it in SQL via the -> and ->> operators.

select (data -> 'rent' ->> 'value')::float from object_data;

-> Extracts the JSON object field with the given key, while ->> extracts the field as TEXT.

Storage

We are using Supabase Storage currently for three things:

1. Users can upload a profile image like on almost every other social media like platform. These images are stored in a public bucket and associated with the user by their id from the auth.users table.
2. Every object does not only have a descriptive text and financial numbers, but photos as well. These are stored in a private bucket under a directory for each object.
3. In addition to photos, objects can have multiple documents. These are saved as well under the object's directory. Documents come with some additional attributes, so they are referenced in another table called object_documents referencing the object id from the previous table objects. Each document has a specific publication type. A document is either private, meaning only the object's owner can see it, public, meaning everyone can view it, or listed. This hides the document in the app, but can still be downloaded when having the correct link. Much like YouTube's unlisted video feature. Since publication_type can only have three different values, we came using the ENUM type:

CREATE TYPE public.document_publication_type AS ENUM
    ('private', 'public', 'listed');

Edge Functions

We currently don't make use of Supabase Edge Functions, but plan to do so in the future. A new feature will be generating PDFs from your object's data to take your numbers everywhere you go.

Supabase uses Deno Deploy to deploy functions to the edge in Typescript. With Deno now providing npm compatibility this will be easy to implement. No more need for Firebase Functions.

Wrap up

Beside Edge Functions, we use all core Supabase features and see how well they work together. They are all rooted in the postgres database, allowing to control and view them from the heart of Supabase: The PostgreSQL database. In Firebase, each feature works individually well too, but are not interconnected. Despite the great Dashboard, Supabase still needs SQL to be managed. Each developer has to decide themself whether they are willing to invest that extra effort or not, but for Brick this was a great decision.

In addition, Supabase is by far not finished and still massively developing. Next week Dec 12 - 16 they host their 6th #SupaLaunchWeek, where they ship new features every day. So stay tuned on what they are going to deliver.

Thanks for reading! This was my first article and I hope you enjoyed it.