Database Overview

This page provides an overview of the database architecture, focusing on the integration of Drizzle ORM with Supabase Postgres. It covers the database client setup, schema definition, and the structure of the data model as implemented in the codebase.

Database Client Setup

The application connects to a Supabase Postgres instance using the postgres-js client, managed by Drizzle ORM. The database client is initialized in src/lib/db/index.ts.

import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import * as schema from "./schema"

const client = postgres(process.env.DATABASE_URL!, { prepare: false })
export const db = drizzle(client, { schema })

The DATABASE_URL environment variable is used for connection, and prepare: false disables prepared statements to avoid potential caching issues in serverless environments. The db object is exported as a Drizzle ORM instance with the schema applied, enabling type-safe queries across the application.

Schema Definition

The database schema is defined in src/lib/db/schema.ts using Drizzle ORM's pgTable and column types such as uuid, text, timestamp, boolean, and integer. All tables use UUID primary keys with defaultRandom() for unguessable IDs. Timestamps use defaultNow() for automatic population of createdAt and updatedAt fields.

Foreign key relationships are declared with references() and consistently use onDelete: "cascade" to ensure referential integrity. This design supports data privacy requirements like the LGPD "right to be forgotten" — deleting a professional triggers deletion of all related records.

Schema Structure

The schema consists of ten core tables centered around the professionals table.

professionals

Represents a professional user with business and payment configuration.

• id: UUID primary key.
• userId: Supabase auth ID, stored as text, unique.
• name: Full name of the professional, required.
• slug: Unique URL path for public profile.
• phone: Contact phone number, required.
• email: Contact email, optional.
• profession: Professional category (e.g., psychologist, nutritionist), required.
• bio: Professional biography, optional.
• avatarUrl: URL to profile image, optional.
• sessionDuration, sessionPrice: Integer values in minutes and cents.
• plan: Subscription tier (free, pro, premium).
• paymentType, paymentPercentage, pixKeyType, pixKey: PIX payment configuration.
• stripeCustomerId, stripeSubscriptionId: Stripe identifiers.
• trialEndsAt: Timestamp for Pro trial expiration.
• welcomeMessage: Custom message sent to new clients, optional.
• autoConfirm: Boolean for automatic appointment confirmation.
• active: Boolean flag for account status.

availability

Defines recurring time slots for a professional.

• professionalId: Foreign key to professionals.
• dayOfWeek: Integer (0–6).
• startTime, endTime: Text in "HH:mm" format.
• Indexed on professionalId.

clients

Stores client information scoped to a professional.

• userId: Optional Supabase ID (nullable text).
• professionalId: Required foreign key.
• Indexed on professionalId and userId.

clientNotes

Private notes by professionals on clients.

• professionalId, clientId: Foreign keys.
• content: Free-form text.
• createdAt, updatedAt: Timestamps.

recurringSchedules

Captures recurring appointment patterns.

• professionalId, clientId: Foreign keys.
• frequency: weekly, biweekly, twice_weekly.
• dayOfWeek, secondDayOfWeek: Day integers.
• time: Text in "HH:mm".
• active: Boolean toggle.
• createdAt: Timestamp of record creation.

appointments

Main booking record with lifecycle and payment state.

• professionalId, clientId: Foreign keys.
• recurringScheduleId: Optional link to recurrence pattern.
• startsAt, endsAt: Appointment timestamps.
• status: Lifecycle state (pending_confirmation, confirmed, etc.).
• paymentStatus: (none, authorized, captured, refunded).
• stripePaymentIntentId: Stripe Payment Intent identifier, optional.
• reminderSent24h, reminderSent1h: Booleans to prevent duplicate emails.
• notes: Internal notes for the appointment, optional.
• Indexed on professionalId, clientId, startsAt, and status.

messages

In-product chat between professional and client.

• sender: professional or client.
• type: text, link, pdf.
• content: Message text.
• fileUrl: URL to attached file (e.g., PDF), optional.
• readAt: Timestamp, null until read.
• Composite index on (professionalId, clientId).

follows

Social follow relationship (client → professional).

• userId: Supabase ID.
• professionalId: Foreign key.
• Indexed on both fields.

reviews

Client feedback on completed appointments.

• appointmentId: Unique foreign key (one review per appointment).
• rating: Integer 1–5.
• comment: Optional text.
• Indexed on professionalId.

notifications

In-app notifications.

• recipientId: Supabase user ID.
• recipientType: professional or client.
• type: Categorization (e.g., booking_confirmed, new_message), example values: booking_confirmed, new_message, payment_captured.
• title: Notification headline, required.
• body: Notification content, required.
• read: Boolean status.
• Indexed on recipientId.

Drizzle Configuration

Drizzle Kit is configured in drizzle.config.ts for schema management and migration generation.

import type { Config } from "drizzle-kit"
import { config } from "dotenv"

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

export default {
  schema: "./src/lib/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config

This configuration points to the schema file, sets the migration output directory, specifies PostgreSQL as the dialect, and uses DATABASE_URL for connection credentials during introspection and migration operations.

Why this shape

The schema is designed as a clean, relational model with Drizzle ORM enabling full TypeScript type safety. The use of onDelete: "cascade" ensures data consistency and simplifies deletion workflows, particularly for compliance with data privacy regulations. All business logic, derived data, and access control are intentionally kept in the application layer, avoiding database-level complexity such as views, triggers, or RLS policies.

Known gaps

The current design does not use Row Level Security (RLS) in Supabase Postgres. Access control is entirely application-managed via Drizzle queries that scope data by professionalId or userId. This centralizes logic in TypeScript but shifts the burden of enforcing data isolation entirely to the application code, increasing the risk of accidental data leaks if query construction is inconsistent. For example, a missing where clause on professionalId could expose another professional's appointments.