Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• prisma/schema.prisma
• scripts/check-db.js
• scripts/seed/index.ts
• src/lib/prisma.ts
• prisma/migrations/04_team_redesign/migration.sql
• README.md
• package.json

The Umami application utilizes a PostgreSQL database to store all its data, from user accounts and website configurations to detailed analytics events. The schema is managed using Prisma ORM, which provides a declarative way to define data models and relationships. This structure is designed to be both robust for transactional data and efficient for querying analytics information.

The primary database connection is configured via the DATABASE_URL environment variable. The system also supports read replicas for scaling query performance, which can be configured with DATABASE_REPLICA_URL. Database initialization and migrations are handled by Prisma's migration tools, ensuring the schema is always up-to-date.

Sources: README.md:15-22, src/lib/prisma.ts:223-255, prisma/schema.prisma:8-12

Technology Stack

The database architecture is built on the following technologies:

Technology	Purpose	Configuration
PostgreSQL	The primary relational database system. Version 12.14 or higher is required.	DATABASE_URL in .env file.
Prisma	ORM for database access, schema definition, and migrations.	prisma/schema.prisma
@prisma/adapter-pg	Prisma adapter for connecting to PostgreSQL.	scripts/check-db.js:5

The check-db.js script verifies the database connection and version compatibility before the application starts. Migrations are applied using the prisma migrate deploy command.

Sources: README.md:12-13, package.json:52, scripts/check-db.js:38-46

Entity Relationship Diagram

This diagram provides a high-level overview of the main entities in the PostgreSQL database and their relationships.

Sources: prisma/schema.prisma

Prisma Schema Models

The database schema is defined in prisma/schema.prisma. Below are the key models that structure the application's data.

Sources: prisma/schema.prisma

Core Models

These models represent the foundational entities of the application: users, teams, and websites.

User

Stores user account information. A user can own multiple websites and be a member of multiple teams.

Field	Type	Description
id	String (UUID)	Primary key.
username	String	Unique username for login.
password	String	Hashed password.
role	String	User role (e.g., 'admin').
displayName	String?	User's display name.
createdAt	DateTime?	Timestamp of user creation.

Sources: prisma/schema.prisma:14-27

Team

Represents a team of users who can collaboratively manage websites.

Field	Type	Description
id	String (UUID)	Primary key.
name	String	The name of the team.
accessCode	String?	A unique code for users to join the team.

Sources: prisma/schema.prisma:217-231

TeamUser

A join table connecting User and Team, defining a user's membership and role within a team.

Field	Type	Description
id	String (UUID)	Primary key.
teamId	String (UUID)	Foreign key to the Team model.
userId	String (UUID)	Foreign key to the User model.
role	String	The user's role within the team (e.g., 'team-manager').

Sources: prisma/schema.prisma:233-245

Website

Stores configuration for each website being tracked. A website can be owned by a single user or by a team.

Field	Type	Description
id	String (UUID)	Primary key.
name	String	Display name of the website.
domain	String?	The domain of the website.
shareId	String?	A unique ID for sharing public-facing stats.
userId	String?	Foreign key to the User who owns the website (if not team-owned).
teamId	String?	Foreign key to the Team that owns the website.
createdBy	String?	Foreign key to the User who created the website.

Sources: prisma/schema.prisma:82-105

Analytics Data Models

These models are central to storing the analytics data collected by the tracking script.

Session

Represents a single user session on a website, containing information about the user's environment.

Field	Type	Description
id	String (UUID)	Primary key.
websiteId	String (UUID)	Foreign key to the Website.
browser	String?	Browser used in the session.
os	String?	Operating system used.
device	String?	Device type (e.g., 'desktop', 'mobile').
country	String?	Two-letter country code.
distinctId	String?	A unique identifier for the user across sessions.

Sources: prisma/schema.prisma:29-54

WebsiteEvent

Records every event that occurs during a session, such as a page view or a custom event. This is the most frequently written-to table.

Field	Type	Description
id	String (UUID)	Primary key.
websiteId	String (UUID)	Foreign key to the Website.
sessionId	String (UUID)	Foreign key to the Session.
urlPath	String	The path of the URL where the event occurred.
eventType	Int	Type of event (1 for pageview, 2 for custom event).
eventName	String?	The name of the custom event.
referrerDomain	String?	The domain of the referring URL.

Sources: prisma/schema.prisma:107-147

EventData and SessionData

These tables store custom key-value data associated with events (EventData) or entire sessions (SessionData). This allows for flexible and rich data collection. Both tables have a similar structure to store string, number, or date values.

Sources: prisma/schema.prisma:149-173, prisma/schema.prisma:175-197

Feature-Specific Models

These models support advanced features like custom reports, user segmentation, and revenue tracking.

• Report: Stores definitions for custom reports (e.g., Funnel, Retention) created by users.
• Segment: Saves a defined set of filters as a "segment" or "cohort" for reuse in analysis.
• Revenue: Tracks revenue transactions associated with specific events.
• Link and Pixel: Support for tracking marketing links and conversion pixels.

Sources: prisma/schema.prisma:247-340

Data Seeding

The repository includes a comprehensive seeding script to populate the database with realistic sample data for development and testing. This is useful for understanding how data flows into the analytics tables.

The main seeding logic is in scripts/seed/index.ts. It performs the following steps:

1. Finds the admin user to associate the new websites with.
2. Clears any existing demo data if the --clear flag is used.
3. Creates two websites: "Demo Blog" (low traffic) and "Demo SaaS" (high traffic with revenue).
4. For a specified number of days, it generates sessions, page view events, custom events, and revenue data.
5. Data is inserted in large batches for efficiency using prisma.<model>.createMany.

This process is illustrated below:

Sources: scripts/seed/index.ts:147-280

Schema Migrations

Database schema changes are managed by Prisma Migrate. Migrations are automatically generated based on changes to prisma/schema.prisma and stored as SQL files in the prisma/migrations directory.

An example of a migration is 04_team_redesign/migration.sql, which refactored how teams and websites are related. It involved:

• Adding new columns like deleted_at and logo_url to the team table.
• Adding team_id and created_by columns to the website table.
• Dropping the old team_website join table.
• Migrating existing data with an UPDATE statement.

These migrations are applied automatically during the build process (pnpm run build) or manually via prisma migrate deploy, which is executed by the check-db.js script.

Sources: prisma/migrations/04_team_redesign/migration.sql, scripts/check-db.js:48-54