In this article, you will learn how to build an application with supabase as a firebase alternative in order to build Sticar.

To keep the project simple, we'll develop a web application using NextJS. Additionaly, by leveraging FCM(Firebase Cloud Message) we'll deliver notifications directly to the user without requiring them to install any app.

Requirements :

Create a supabase project

Create a Next.js app

Use the create-next-app command and the with-supabase template, to create a Next.js app pre-configured with:



npx create-next-app -e with-supabase

Declare Supabase Environment Variables

Rename .env.example to .env and populate with your
project's URL and Anon Key.



NEXT_PUBLIC_SUPABASE_URL=your-project-url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
DATABASE_URL=your-database-url-used-by-drizzle-orm

Start the app

Using npm run dev will trigger a script to start the development server.
Authenticate with the mail that you registered on supabase and you should be able to receive an email, confirm the account and sign in !

Creating our first SQL Table

Instead of creating an SQL table with Supabase - SQL Editor, we'll be using Drizzle ORM. It's an easy way to generate and migrate your sql schema.

Drizzle Kit

Drizzle Kit — is a CLI companion for automatic SQL migrations generation and rapid prototyping.

Conceptually it’s very simple, you just declare a Drizzle ORM TypeScript schema and generate an SQL migration from it.

Stop the server and install the module using yarn add -D drizzle-kit

Moreover, we'll need to install drizzle-orm, postgres and dotenv package as well by using yarn add drizzle-orm dotenv postgres.

Create drizzle.config.ts at the root of the project folder. This file will be used by drizzle-kit to generate the migrations based on our schema files.



import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env.local" });

export default defineConfig({
  schema: "./utils/drizzle/schema.ts",
  out: "./supabase/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Next step is to create under utils folder a new folder called drizzle with a file named schema.ts.

In the schema.ts file we will export the schemas and be consumed by the drizzle-kit.

For this example we will have 3 tables :

QRCodes
- The user will be able to generate an unique QR Code and link it to an asset.
Assets
- The user can define the type of the asset (vehicle, bag, property)
Messages
- We will store the message received from sender and link it to the receiver.

Asset Management The schema includes a table for assets (Assets), which represent physical items like vehicles, properties, or bags that users want to link with QR codes.

Asset Types: The application supports different asset categories such as vehicles, properties, and bags. This is done using an enumeration (enum), ensuring only predefined types can be assigned to an asset.
Assets Table: Each asset has a unique ID (id), belongs to a user (userId), and can be associated with a QR code (qrCodeId). The table also stores a description (assetDescription) of the asset and a creation timestamp (createdAt).
This structure allows the system to track which assets belong to which users, and to link them to QR codes.

QR Codes Management QR codes are crucial for this project as they allow users to scan and interact with the asset owner without intermediaries.

QR Code Table: Each QR code has a unique identifier (id) and is linked to an asset (assetId). The table tracks whether the QR code is currently active (isActive), when it was assigned to the asset (assignedAt), and the time it was last scanned (lastScannedAt).
By managing these QR codes independently, the system can store and update QR codes without deleting them, even if they are no longer in active use. This is essential for printed codes that cannot be easily replaced.
QR Code Status: The QR codes have statuses such as active or inactive, helping to indicate whether the code is currently usable or has been replaced.

User Messaging System The schema also includes a messaging feature, allowing users to communicate directly with the owner of the asset after scanning a QR code.

Messages Table: When a user scans a QR code and sends a message, it’s logged in the Messages table. Each message includes the sender’s user ID (senderUserId), the receiver’s user ID (receiverUserId), the asset it relates to (assetId), and the content of the message (messageContent). The table also records when the message was created (createdAt).

This system facilitates direct communication between the scanning user and the asset owner, which is essential for scenarios like road emergencies or parking situations.

Schema File



import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  pgEnum,
  serial,
  integer,
} from "drizzle-orm/pg-core";

export enum AssetType {
  VEHICLE = "VEHICLE",
  PROPERTY = "PROPERTY",
  BAG = "BAG",
}

export const assetTypeEnum = pgEnum("asset_type", [
  AssetType.BAG,
  AssetType.PROPERTY,
  AssetType.VEHICLE,
]);

export const Assets = pgTable("asset", {
  id: serial("id").primaryKey(),
  userId: uuid("user_id").notNull(),
  assetType: assetTypeEnum("asset_type").notNull(),
  assetDescription: text("asset_description"),
  qrCodeId: integer("qr_code_id").references(() => QRCodes.id),
  createdAt: timestamp("created_at").defaultNow(),
});

export enum QRStatusType {
  ACTIVE = "ACTIVE",
  INACTIVE = "INACTIVE",
}

export const qrStatusTypeEnum = pgEnum("asset_type", [
  AssetType.BAG,
  AssetType.PROPERTY,
  AssetType.VEHICLE,
]);

export const QRCodes = pgTable("qrcode", {
  id: serial("id").primaryKey(),
  userId: uuid("user_id").notNull(),
  isActive: boolean("is_active"),
  assetId: integer("asset_id").references(() => Assets.id),
  assignedAt: timestamp("assigned_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
  lastScannedAt: timestamp("last_scanned_at").defaultNow(),
});

export const Messages = pgTable("message", {
  id: serial("id").primaryKey(),
  senderUserId: uuid("sender_user_id"),
  receiverUserId: uuid("receiver_user_id").notNull(),
  assetId: integer("asset_id").references(() => Assets.id),
  messageContent: varchar("message_content", {
    length: 255,
  }),
  createdAt: timestamp("created_at").defaultNow(),
});

Generate the sql migration using :
npx drizzle-kit generate

Make sure you have DATABASE_URL updated inside your environment file and execute npx drizzle-kit migrate.

You can now check Supabase - Table Editor to view the tables created.

Conclusion

In this part of the article, we've explored how to set up the backend for our app using Supabase, focusing on the database structure, asset management, QR code handling, and messaging. With the Supabase setup in place, you now have the foundation to manage user assets, QR codes, and communication between users.

In the next part (Part 2) of this series, we'll dive into the Next.js frontend and build out the user interface. We’ll cover how to connect the backend with the UI, manage real-time updates, and handle QR code scanning and messaging seamlessly.

Stay tuned!

Building a QR-Driven Driver Communication App with Supabase: A Step-by-Step Guide to Clone Sticar - Part 1