Supabase analytics with Jitsu

Supabase is the open-source Firebase alternative. Unlike Firebase which went with NoSQL key-value storage model, they use PostgresSQL as a storage layer. Key-value storages are great, but SQL gives users a more powerful way to aggregate data and build reports.

However, Supabase lacks one important feature of Firebase - analytics. Firebase users can send events to linked Google Analytics account or to BigQuery table with JS API. Supabase do not have this yet. But luckily, we build Jitsu, an open-source data collection platform. Among other databases, Jitsu supports Postgres, which is a Supabase backbone.

In this article we're going to show how Jitsu can be used along with Supabase to collect web-application events to PostgresSQL.

Setting up Supabase#

You will need a Github account. Signup with it at app.supabase.com and create a new project

Make sure you saved a password somewhere. This password will be used later for Jitsu configuration.

Go to Settings → Database → Connection Info, copy Postgres hostname and save it

Copy Hostname

Setting up Jitsu#

Signup#

Signup at cloud.jitsu.com/register We recommend using GitHub.

Connect Jitsu to Supabase Postgres#

Go through onboarding process and type in your name and company name. On Destination Setup step, select Postgres SQL

Then set database host to the value copied from Supabase settings. Use saved Supabase password, use postgres as username and database name

Add Jitsu SDK to your application#

Use yarn:

yarn add @jitsu/sdk-js

...or npm:

npm install --save @jitsu/sdk-js

Start tracking events#

You can use vanilla Javascript for sending events:

import { jitsuClient } from "@jitsu/sdk-js"
//init
const jitsu = jitsuClient({
  key: "<JITSU KEY>",
  tracking_host: "https://t.jitsu.com",
})
//identify user
jitsu.id({
  email: getEmail(),
  internal_id: getId(),
})
//track page views
jitsu.track("app_page")

//track other events such as conversion
jitsu.track("conversion")

To get a <JITSU KEY> go to Events API section and copy Client Secret

Or get advantage of react hooks by implementing useJitsu(). Check out our @jitsucom/jitsu-react-example repository

Querying events#

The data will be recorded to the table called events (you can change the name of the table in Jitsu settings, it's event possible to put events to different tables based on event content).

Here's an example that displays the last tracked event with user location:

select
  _timestamp, page_title,
  location_country, location_city, location_zip,
  user_email
from events order by _timestamp desc limit 1;

Next steps#

Once the data is events are recorded into a database, we recommend using following tools to build a full-stack open-source data pipeline:

• DBT is great for data modelling: building views and transforming. By the way, Jitsu is integrated with DBT Cloud
• Metabase is a great open-source visualization tool. It works smoothly with postgres & DBT. Another great open-source visualization tools are Apache Superset and Redash