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: Session storage and caching layer

Naming Conventions

All database tables and columns follow snake_case convention:

• Tables: users, organizations, organization_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: OAuth provider accounts (future)
• Verification: Email verification tokens

Organization Models

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

Business Models (Future)

• Event: Event planning records
• EventBlock: Modular event components
• Vendor: Service provider profiles
• Resource: 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

Data Types

Primary Keys

• All tables use UUID primary keys for security and scalability
• Generated using PostgreSQL's gen_random_uuid()

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
• Foreign keys have indexes for join performance

Query Optimization

• Composite indexes for common query patterns
• Partial indexes for filtered queries
• GIN indexes for JSONB search operations

Data Integrity

Constraints

• Foreign key constraints with appropriate cascade rules
• NOT NULL constraints for required fields
• UNIQUE constraints for business rules
• CHECK constraints for data validation

Security Considerations

Authentication

• Passwords hashed with Argon2 (via AdonisJS hash config)
• Session-based authentication via Better Auth
• Organization isolation enforced at application layer

Performance Optimization

Query Patterns

• Eager loading to prevent N+1 queries
• Query result caching in Redis where appropriate
• Proper indexing on foreign keys and commonly queried fields