Database Schema

Relevant source files

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

• packages/db/prisma/schema.prisma
• packages/db/prisma/migrations/20260131205756_newb/migration.sql
• packages/db/client.ts
• prisma.config.ts
• packages/shared/zod.schema.ts
• PROJECT_OUTLINE.md

Introduction

The Glimpse database schema is designed to support real-time user analytics, session tracking, and multi-dimensional data aggregation. It utilizes PostgreSQL as the primary relational engine, managed through the Prisma ORM. The schema is optimized for high-volume event ingestion and efficient querying of session-based traffic patterns.

The architecture focuses on "tracking sessions, not people," which is reflected in the schema's heavy emphasis on projectId, sessionId, and anonymousId rather than traditional user identity management. This design allows for granular visualization of traffic patterns and live events across multiple distinct projects.

Sources: PROJECT_OUTLINE.md:5-10, packages/db/prisma/schema.prisma:1-10

Core Analytics Entities

The schema defines several primary entities for capturing raw event data and session lifecycles.

Event Tracking

The Event model (mapped to the events table) is the central repository for all tracked actions. It stores raw event data, including timing, identifiers, geolocation, and flexible metadata stored in JSONB fields.

Field	Type	Description
id	BigInt	Primary key, autoincrementing.
projectId	String	Scopes the event to a specific website/app.
name	String	The name of the event (e.g., "click", "page_view").
occurredAt	DateTime	When the event happened on the client.
receivedAt	DateTime	When the server received the event.
anonymousId	String	Unique identifier for a client/browser.
sessionId	String	UUID v7 identifier for the specific session.
traits	Json	Flexible user traits (e.g., email, name).
properties	Json	Event-specific data.
context	Json	Environmental data (URL, viewport, device).

Sources: packages/db/prisma/schema.prisma:18-45, packages/db/prisma/migrations/20260131205756_newb/migration.sql:31-52

Session Management

The AnalyticsSession model tracks the duration and activity level of user interactions.

The diagram shows the relationship between projects, sessions, and events. Sessions are uniquely identified by a composite key of projectId and sessionId.

Sources: packages/db/prisma/schema.prisma:47-61, packages/db/prisma/migrations/20260131205756_newb/migration.sql:54-66

Aggregation and Performance

To support high-performance dashboards and real-time visualization, the schema includes pre-aggregated tables and extensive indexing.

Aggregate Models

Aggregation tables reduce the need for expensive calculations on the raw events table during read operations.

• DailyEventAggregate: Tracks event counts, unique users, and sessions per event type per day.
• DailyPageAnalytics: Stores path-specific metrics like views, unique users, and bounces.
• DailyUserAnalytics: Tracks session counts and "new user" status per anonymous ID.

Sources: packages/db/prisma/schema.prisma:64-106, packages/db/prisma/migrations/20260131205756_newb/migration.sql:68-100

Indexing Strategy

The schema implements specific indexes to optimize common analytics queries, particularly those filtered by projectId.

• events: Indexed by projectId combined with occurredAt, sessionId, anonymousId, and userId.
• analytics_sessions: Indexed by projectId combined with startedAt and anonymousId.

Sources: packages/db/prisma/schema.prisma:41-45, packages/db/prisma/migrations/20260131205756_newb/migration.sql:106-114

System Tracking and State

The schema includes administrative tables to manage user access and processing state.

User Management

The User model manages administrative access to the Glimpse platform itself, storing basic profile information.

Sources: packages/db/prisma/schema.prisma:7-13, packages/db/prisma/migrations/20260131205756_newb/migration.sql:21-29

Checkpoints

The AnalyticsCheckpoint table is used to track the progress of background workers or analytics pipelines. It stores the lastEventId processed for a given named checkpoint, ensuring data consistency during batch processing.

Sources: packages/db/prisma/schema.prisma:109-113, packages/db/prisma/migrations/20260131205756_newb/migration.sql:102-104

Database Connection and Configuration

Glimpse uses the @prisma/adapter-pg to handle connections via the pg driver, allowing for efficient connection pooling.

// packages/db/client.ts
const connectionString = process.env.DATABASE_URL!;
const adapter = new PrismaPg({ connectionString });

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({ adapter });

Sources: packages/db/client.ts:6-12, prisma.config.ts:4-13

Summary

The Database Schema provides a robust foundation for session-based analytics by separating raw event ingestion from aggregated analytical views. By utilizing BigInt for event IDs and JSONB for flexible metadata, the system is designed to scale with high event volumes while maintaining the flexibility required for diverse tracking requirements.