Data Models Overview

Comprehensive documentation of Jubiloop's database schema and data relationships.

Database Technology

• PostgreSQL 15+: Primary database for all environments
• Digital Ocean Managed Database: Production infrastructure
• Lucid ORM: AdonisJS ORM for database interactions
• Redis: Better Auth runtime session storage and application caching

Naming Conventions

All database tables and columns follow snake_case convention:

• Tables: users, organizations, members, team_members
• Columns: user_id, created_at, active_organization_id

Core Models

Authentication Models

• User: Core user account and profile
• Session: Active user sessions with organization context
• Account: Better Auth credential and provider account records; current auth uses credentials
• Verification: Short-lived Better Auth records used by password reset

Organization Models

• Organization: Multi-tenant workspaces
• Member: Organization membership with roles
• Team: Sub-groups within organizations
• Invitation: Pending organization invitations

See Organization Data Models for complete schemas.

Event Models

• Event: Event planning records
• EventBlock: Modular event components
• Vendor: Planned service provider profiles
• Resource: Planned shared assets and documents

Relationships

User Relationships

User ──┬── has many ──> Session
       ├── has many ──> Organization (as owner)
       ├── has many ──> Member
       └── has many ──> Account

Organization Relationships

Organization ──┬── belongs to ──> User (owner)
               ├── has many ──> Member
               ├── has many ──> Team
               ├── has many ──> Invitation
               └── has many ──> Event

Data Types

Primary Keys

• Application models use UUID v7 primary keys for time-ordered indexing
• Lucid assigns IDs through BaseModelWithUuid; Better Auth uses advanced.database.generateId
• Migration defaults may use gen_random_uuid() as a database fallback

Timestamps

• created_at: Record creation timestamp (required)
• updated_at: Last modification timestamp (nullable)
• All timestamps use UTC timezone

JSON Fields

• metadata: Flexible JSON/JSONB fields for extensibility
• Used for configuration, preferences, and feature flags

Migration Strategy

Version Control

• All schema changes through versioned migrations
• Never modify existing migrations
• Roll forward only, no destructive changes in production

Migration Files

View the actual migration history in apps/server/database/migrations/

• Timestamp-prefixed for ordering
• Descriptive names for clarity
• Reversible with up/down methods

See Current Migrations

For the most up-to-date database schema and migration history, check the actual migration files in the codebase at apps/server/database/migrations/. The migration files are the source of truth for database structure.

Indexing Strategy

Primary Indexes

• UUID primary keys are automatically indexed
• Migrations add indexes to selected foreign keys and common lookup columns

Query Optimization

• Composite indexes support current membership, permission, and polymorphic lookup patterns
• Unique indexes enforce identifiers such as user email, session token, and organization slug
• No current migration defines partial or GIN indexes

Data Integrity

Constraints

• Foreign key constraints with appropriate cascade rules
• NOT NULL constraints for required fields
• UNIQUE constraints for business rules
• Application validators and model hooks enforce rules that are not represented by database CHECK constraints

Security Considerations

Authentication

• Better Auth hashes passwords with the Argon2 functions in apps/server/app/lib/auth.ts
• Session-based authentication via Better Auth
• Organization isolation enforced at application layer

Performance Optimization

Query Patterns

• Eager loading to prevent N+1 queries
• Redis-backed caching only where a feature explicitly configures it
• Proper indexing on foreign keys and commonly queried fields