Database Migrations

Database migrations in the Umami project are a critical process for evolving the database schema in a consistent and version-controlled manner. The system utilizes two distinct databases: a primary PostgreSQL database for application data (users, websites, etc.) and a ClickHouse database for high-volume analytics event data. Consequently, migration strategies are managed separately for each database.

PostgreSQL migrations are handled by Prisma, a modern ORM for Node.js and TypeScript. The schema is defined declaratively in prisma/schema.prisma, and migrations are generated as SQL files. ClickHouse migrations are managed through raw SQL scripts located in the db/clickhouse/migrations/ directory, which are applied manually or through a custom process. The application automates the PostgreSQL migration process during build and startup phases to ensure the database is always up-to-date.

Migration Architecture

The project employs a dual-database architecture, necessitating two separate migration workflows.

• PostgreSQL (Prisma): Used for core application models like users, teams, and websites. Prisma provides a robust framework for schema definition, migration generation, and application.
• ClickHouse: Used for storing and querying large volumes of analytics events. Migrations are handled with plain SQL scripts, reflecting a more manual but flexible approach suitable for a columnar analytics database.

Key Scripts

The package.json file defines several scripts that orchestrate the database migration and management process.

Sources: package.json:12-25

PostgreSQL Migrations with Prisma

Prisma is the primary tool for managing the PostgreSQL database schema. The workflow is centered around the prisma/schema.prisma file, which acts as the single source of truth for the database models.

Sources: prisma/schema.prisma

Schema Definition

The prisma/schema.prisma file defines all models, their fields, types, and relations. For example, the Link and Pixel models are defined as follows:

model Link {
  id        String    @id @unique @map("link_id") @db.Uuid
  name      String    @db.VarChar(100)
  url       String    @db.VarChar(500)
  slug      String    @unique() @db.VarChar(100)
  userId    String?   @map("user_id") @db.Uuid
  teamId    String?   @map("team_id") @db.Uuid
  // ... other fields
  user User? @relation("user", fields: [userId], references: [id])
  team Team? @relation(fields: [teamId], references: [id])
  
  @@map("link")
}

model Pixel {
  id        String    @id @unique @map("pixel_id") @db.Uuid
  name      String    @db.VarChar(100)
  slug      String    @unique() @db.VarChar(100)
  userId    String?   @map("user_id") @db.Uuid
  teamId    String?   @map("team_id") @db.Uuid
  // ... other fields
  user User? @relation("user", fields: [userId], references: [id])
  team Team? @relation(fields: [teamId], references: [id])
  
  @@map("pixel")
}

Sources: prisma/schema.prisma:297-340

This schema definition is then used to generate SQL migration files.

Migration Files

Prisma migrations are stored as individual directories within prisma/migrations/. Each directory contains a migration.sql file with the SQL commands to apply or revert the schema changes.

Example 1: Adding link and pixel tables
The 14_add_link_and_pixel/migration.sql file creates the link and pixel tables and their associated indexes.

-- CreateTable
CREATE TABLE "link" (
    "link_id" UUID NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "url" VARCHAR(500) NOT NULL,
    "slug" VARCHAR(100) NOT NULL,
    "user_id" UUID,
    "team_id" UUID,
    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6),
    "deleted_at" TIMESTAMPTZ(6),

    CONSTRAINT "link_pkey" PRIMARY KEY ("link_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "link_slug_key" ON "link"("slug");

Sources: prisma/migrations/14_add_link_and_pixel/migration.sql:8-23,27-28

The following diagram shows the schema for the link and pixel tables and their relationship to the user and team tables.

Example 2: Altering existing tables
The 04_team_redesign/migration.sql file demonstrates altering existing tables (team, user, website) by adding new columns and dropping an obsolete table (team_website).

-- AlterTable
ALTER TABLE "team" ADD COLUMN     "deleted_at" TIMESTAMPTZ(6),
ADD COLUMN     "logo_url" VARCHAR(2183);

-- AlterTable
ALTER TABLE "website" ADD COLUMN     "created_by" UUID,
ADD COLUMN     "team_id" UUID;

-- DropTable
DROP TABLE "team_website";

Sources: prisma/migrations/04_team_redesign/migration.sql:8-17,23-24

Automated Migration Process

The project includes a script to automate database checks and migrations, which is triggered during application build and startup.

The following sequence diagram illustrates the automated migration flow initiated by pnpm run build.

This diagram shows how the check-db.js script validates the environment, checks the database connection and version, and then executes prisma migrate deploy to apply any pending migrations.

Sources: scripts/check-db.js:10-80, package.json:12,25

ClickHouse Migrations

Migrations for the ClickHouse database are managed through raw SQL files located in db/clickhouse/migrations/. Unlike Prisma, there is no automated tool for applying these migrations mentioned in the provided files; it is implied to be a more manual process.

Sources: db/clickhouse/migrations/05_add_utm_clid.sql

Migration Strategy

The migration 05_add_utm_clid.sql demonstrates a common pattern for altering tables in ClickHouse without downtime:

1. Create New Tables: A new table (website_event_new) and a new materialized view table (website_event_stats_hourly_new) are created with the desired schema changes. In this case, new columns for UTM parameters and various click IDs (gclid, fbclid, etc.) are added.
2. Backfill Data: Data from the old website_event table is selected, transformed, and inserted into the new website_event_new table. The transformation logic uses the extract() function to parse the new field values from the existing url_query column.
3. Rename Tables: The old tables are renamed (e.g., website_event to website_event_old), and the new tables are renamed to take their place (e.g., website_event_new to website_event).
4. Recreate Materialized View: The old materialized view is dropped and recreated to point to the new source and destination tables.

The following flowchart visualizes this ClickHouse migration process.

This approach ensures that data ingestion can continue with minimal interruption while the schema is being updated. The old tables can be dropped later after verifying the success of the migration.