@venturialstd/user

User Management Module for Venturial - A NestJS module for managing users with comprehensive profile management, settings, and role-based access control.

🎯 Key Design Principle: This module provides a minimal, extensible base User entity. Extend it with related entities for your specific needs (employment data, contact info, etc.) for optimal performance.

📋 Table of Contents

• Features
• Installation
• Module Structure
• Quick Start
• ACL System
• Notification System
• Extending User Statuses
• Extending the Module
  • Performance Considerations
  • Recommended Approach: Related Entities
  • Hybrid Approach
  • Complete Extension Example
  • Extension Methods Comparison
• Configuration
• Entities
• API Reference
• Enums & Constants
• Guards & Decorators
• Test Server
• NPM Scripts
• Usage Examples
• Performance Best Practices
• Migration Guide

✨ Features

• 👤 User Management: Complete CRUD operations for users
• 📧 Email Verification: Email verification system
• ⚙️ User Settings: Configurable user preferences
• 🎭 Extensible Statuses: Define custom user statuses for your application
• 🔔 Notification System: Business-agnostic user notifications with configurable types, categories, and priorities
• 🔐 ACL System: Complete database-driven role and permission management
• 🛡️ Guards & Decorators: Request-level access control
• 🔄 TypeORM Integration: Full database support with migrations
• 📦 Fully Typed: Complete TypeScript support
• 🧪 Test Infrastructure: Standalone test server on port 3003
• ⚡ High Performance: Indexed columns and optimized queries

📦 Installation

npm install @venturialstd/user

Peer Dependencies

{
  "@nestjs/common": "^11.0.11",
  "@nestjs/core": "^11.0.5",
  "@nestjs/typeorm": "^10.0.0",
  "@venturialstd/core": "^1.0.16",
  "class-transformer": "^0.5.1",
  "class-validator": "^0.14.1",
  "typeorm": "^0.3.20"
}

📁 Module Structure

src/user/
├── src/
│   ├── constants/
│   │   ├── user.constant.ts           # USER_STATUS enum
│   │   └── user.settings.constant.ts   # Settings keys and config
│   ├── decorators/
│   │   ├── user.decorator.ts           # @CurrentUserId(), @CurrentUser()
│   │   └── acl.decorator.ts            # @RequirePermissions(), @RequireAclRoles()
│   ├── entities/
│   │   ├── user.entity.ts              # User entity
│   │   ├── role.entity.ts              # ACL Role entity
│   │   ├── permission.entity.ts        # ACL Permission entity
│   │   ├── role-permission.entity.ts   # ACL Role-Permission junction
│   │   └── user-role.entity.ts         # ACL User-Role junction
│   ├── guards/
│   │   ├── user.guard.ts               # UserGuard
│   │   └── acl.guard.ts                # PermissionGuard, AclRoleGuard
│   ├── services/
│   │   ├── user.service.ts             # UserService
│   │   └── acl.service.ts              # AclService
│   ├── settings/
│   │   └── user.settings.ts            # UserSettings interface
│   ├── index.ts                        # Public API exports
│   └── user.module.ts                  # Main module
├── test/
│   ├── controllers/
│   │   └── user-test.controller.ts     # Test controller
│   ├── migrations/                     # Database migrations
│   ├── .env.example                    # Environment template
│   ├── data-source.ts                  # TypeORM data source
│   ├── main.ts                         # Test server bootstrap
│   └── test-app.module.ts              # Test module
├── package.json
├── tsconfig.json
└── README.md

🚀 Quick Start

1. Import the Module

import { Module } from '@nestjs/common';
import { UserModule } from '@venturialstd/user';

@Module({
  imports: [
    // Basic import (uses default statuses)
    UserModule.forRoot(),
    
    // Or with custom statuses
    UserModule.forRoot({
      allowedStatuses: ['ACTIVE', 'INACTIVE', 'SUSPENDED', 'BANNED'],
      additionalEntities: [UserEmployment, UserProfile],
    }),
  ],
})
export class AppModule {}

2. Use in Your Service

import { Injectable } from '@nestjs/common';
import { UserService, AclService } from '@venturialstd/user';

@Injectable()
export class MyService {
  constructor(
    private readonly userService: UserService,
    private readonly aclService: AclService,
  ) {}

  async getUser(userId: string) {
    return this.userService.getUserById(userId);
  }
  
  async assignModeratorRole(userId: string) {
    const role = await this.aclService.getRoleByName('MODERATOR');
    return this.aclService.assignRoleToUser(userId, role.id);
  }
}

3. Use Guards and Decorators

import { Controller, Get, UseGuards } from '@nestjs/common';
import { CurrentUserId, UserGuard, RequirePermissions, PermissionGuard } from '@venturialstd/user';

@Controller('profile')
@UseGuards(UserGuard)
export class ProfileController {
  @Get()
  async getProfile(@CurrentUserId() userId: string) {
    return { userId };
  }
  
  @Get('admin')
  @RequirePermissions('users.delete', 'content.manage')
  @UseGuards(UserGuard, PermissionGuard)
  async adminAction() {
    return { message: 'Admin action' };
  }
}

🔐 ACL System

The module includes a complete database-driven Access Control List (ACL) system for managing roles and permissions. See ACL_SYSTEM.md for comprehensive documentation.

Quick Overview

• Roles: Named collections of permissions (e.g., ADMIN, MODERATOR)
• Permissions: Granular access rights in resource.action format (e.g., users.delete, posts.create)
• Multiple Roles: Users can have multiple roles with different scopes
• Temporal Access: Roles can expire automatically
• Conditional Permissions: Optional conditions on role-permission assignments

Example Usage

// Create roles and permissions
const adminRole = await aclService.createRole('ADMIN', 'Administrator');
const userPerm = await aclService.createPermission('users', 'delete', 'Delete users');

// Assign permission to role
await aclService.assignPermissionToRole(adminRole.id, userPerm.id);

// Assign role to user
await aclService.assignRoleToUser(userId, adminRole.id);

// Check permissions
const canDelete = await aclService.userHasPermission(userId, 'users.delete');

🔔 Notification System

The module includes a business-agnostic user notification system. Notification types, categories, and priorities are configurable per application (similar to user statuses). No business logic lives in the core package—each project defines its own types.

See NOTIFICATIONS_SYSTEM.md for full documentation.

Configuration

// examples
UserModule.forRoot({
  allowedNotificationTypes: [
    'system', 'transaction', 'security', 'compliance', 'marketing', 'account',
  ],
  allowedNotificationCategories: ['info', 'success', 'warning', 'error', 'announcement'],
  allowedNotificationPriorities: ['low', 'medium', 'high', 'urgent'],
  defaultChannels: { inApp: true, email: true, sms: false, push: false },
})

UserModule.forRoot({
  allowedNotificationTypes: [
    'system', 'task', 'client', 'document', 'deadline', 'team', 'billing',
  ],
  allowedNotificationCategories: ['info', 'success', 'warning', 'error'],
  allowedNotificationPriorities: ['low', 'medium', 'high', 'critical'],
})

Usage (generic, no business logic in core)

await notificationService.createNotification({
  userId: 'user-123',
  type: 'system',
  category: 'success',
  title: 'Action Completed',
  message: 'Your action has been completed successfully.',
  priority: 'medium',
});

🎭 Extending User Statuses

The module provides a flexible system for defining custom user statuses.

Default Values

By default, the module includes:

Statuses:

• ACTIVE - User is active and can use the system
• INACTIVE - User is inactive
• SUSPENDED - User has been suspended
• PENDING_VERIFICATION - User email needs verification

Defining Custom Statuses

Create an enum for your application-specific statuses:

// Define custom statuses
export enum AppUserStatus {
  ACTIVE = 'ACTIVE',
  INACTIVE = 'INACTIVE',
  SUSPENDED = 'SUSPENDED',
  PENDING_VERIFICATION = 'PENDING_VERIFICATION',
  ON_HOLD = 'ON_HOLD',
  BANNED = 'BANNED',
  DELETED = 'DELETED',
}

@Module({
  imports: [
    UserModule.forRoot({
      allowedStatuses: Object.values(AppUserStatus),
    }),
  ],
})
export class AppModule {}

Managing Statuses

@Injectable()
export class UserManagementService {
  constructor(private userService: UserService) {}

  // Set user status with validation
  async banUser(userId: string) {
    return this.userService.setStatus(userId, AppUserStatus.BANNED);
  }

  // Get users by status
  async getBannedUsers() {
    return this.userService.getUsersByStatus(AppUserStatus.BANNED);
  }

  // Get all allowed statuses for your application
  getAllStatuses() {
    return this.userService.getAllowedStatuses();
  }
}

Validation

The module automatically validates statuses:

// ✅ Valid - status is in allowedStatuses
await userService.setStatus(userId, AppUserStatus.BANNED);

// ❌ Invalid - throws BadRequestException
await userService.setStatus(userId, 'INVALID_STATUS');
// Error: Invalid status "INVALID_STATUS". Allowed statuses: ACTIVE, INACTIVE, SUSPENDED, ...

🔧 Extending the Module

Performance Considerations

⚠️ Important: The metadata and settings JSONB fields are available but NOT recommended for filterable attributes due to poor query performance.

Performance Comparison:

-- ❌ SLOW: JSONB query (full table scan)
SELECT * FROM "user" WHERE metadata->>'department' = 'Engineering';
-- 1M records: ~50 seconds

-- ⚡ FAST: Indexed column query  
SELECT * FROM user_employment WHERE department = 'Engineering';
-- 1M records: ~10 milliseconds

Result: 5,000x faster with proper indexes!

Recommended Approach: Related Entities

Always use related entities for any data you need to query or filter.

Step 1: Create Your Extended Entity

import { Entity, PrimaryGeneratedColumn, Column, OneToOne, JoinColumn, Index } from 'typeorm';
import { User } from '@venturialstd/user';

@Entity('user_employment')
@Index(['department', 'isActive']) // Composite index for common queries
export class UserEmployment {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  @Index() // Fast lookups by userId
  userId: string;

  @OneToOne(() => User)
  @JoinColumn({ name: 'userId' })
  user: User;

  // ⚡ Indexed fields for fast filtering
  @Column({ unique: true })
  employeeId: string;

  @Column()
  @Index() // Fast department filtering
  department: string;

  @Column()
  jobTitle: string;

  @Column({ nullable: true })
  @Index() // Fast manager lookups
  managerId: string;

  @Column({ type: 'date' })
  hireDate: Date;

  @Column({ default: true })
  @Index() // Fast active status queries
  isActive: boolean;

  @Column({ type: 'timestamptz', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @Column({ type: 'timestamptz', default: () => 'CURRENT_TIMESTAMP' })
  updatedAt: Date;
}

Step 2: Create Service with Efficient Queries

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { UserService } from '@venturialstd/user';
import { UserEmployment } from './entities/user-employment.entity';

@Injectable()
export class EmployeeService {
  constructor(
    @InjectRepository(UserEmployment)
    private employmentRepo: Repository<UserEmployment>,
    private userService: UserService,
  ) {}

  async createEmployee(data: {
    firstname: string;
    lastname: string;
    email: string;
    department: string;
    employeeId: string;
    jobTitle: string;
  }) {
    // Create base user
    const user = await this.userService.createUser(
      data.firstname,
      data.lastname,
      data.email,
    );

    // Create employment record
    const employment = this.employmentRepo.create({
      userId: user.id,
      employeeId: data.employeeId,
      department: data.department,
      jobTitle: data.jobTitle,
      hireDate: new Date(),
      isActive: true,
    });

    await this.employmentRepo.save(employment);
    return this.getEmployeeWithUser(user.id);
  }

  // ⚡ FAST: Uses department index
  async getEmployeesByDepartment(department: string) {
    return this.employmentRepo.find({
      where: { department, isActive: true },
      relations: ['user'],
      order: { createdAt: 'DESC' },
    });
  }

  // ⚡ FAST: Complex query with proper indexes
  async searchEmployees(searchTerm: string, department?: string) {
    const query = this.employmentRepo
      .createQueryBuilder('emp')
      .leftJoinAndSelect('emp.user', 'user')
      .where('emp.isActive = :active', { active: true });

    if (department) {
      query.andWhere('emp.department = :dept', { dept: department });
    }

    query.andWhere(
      '(user.firstname ILIKE :search OR user.lastname ILIKE :search OR emp.employeeId ILIKE :search)',
      { search: `%${searchTerm}%` },
    );

    return query.getMany();
  }

  async getEmployeeWithUser(userId: string) {
    return this.employmentRepo.findOne({
      where: { userId },
      relations: ['user'],
    });
  }
}

Step 3: Register with UserModule

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { UserModule } from '@venturialstd/user';
import { UserEmployment } from './entities/user-employment.entity';
import { EmployeeService } from './services/employee.service';

@Module({
  imports: [
    // Register your entities with UserModule
    UserModule.forRoot({
      additionalEntities: [UserEmployment],
    }),
    // Also register for your service
    TypeOrmModule.forFeature([UserEmployment]),
  ],
  providers: [EmployeeService],
  exports: [EmployeeService],
})
export class EmployeeModule {}

Benefits:

• ⚡ 100x-5000x faster queries with proper indexes
• ✅ Database constraints (unique, foreign keys)
• ✅ Full TypeScript type safety
• ✅ Complex queries (JOINs, aggregations)
• ✅ Scales to millions of records

Hybrid Approach (Optional Flexibility)

If you need some flexibility for truly dynamic, non-queryable fields:

@Entity('user_employment')
export class UserEmployment {
  // ... indexed columns for queryable fields ...

  @Column()
  @Index()
  department: string; // ⚡ Fast queries

  @Column({ unique: true })
  employeeId: string; // ⚡ Fast lookups

  // 📦 FLEXIBLE: For truly dynamic, non-queryable data only
  @Column({ type: 'jsonb', nullable: true })
  additionalInfo: {
    shirtSize?: string;
    parkingSpot?: string;
    badgeNumber?: string;
    emergencyContact?: {
      name: string;
      phone: string;
    };
    [key: string]: any;
  };
}

Complete Extension Example

See the examples/ directory for complete working examples:

• user-employee-profile.entity.ts - Entity with proper indexes
• user-employee.service.ts - Service with efficient queries
• user-employee.module.ts - Module configuration

Extension Methods Comparison

| Method | Query Performance | Flexibility | Type Safety | Best For | |--------|------------------|-------------|-------------|----------| | Related Entities ✅ | ⚡⚡⚡ Excellent | ⭐⭐ Medium | ⭐⭐⭐ Strong | Production (99% of cases) | | Hybrid (Entities + JSONB) | ⚡⚡⭐ Good | ⭐⭐⭐ High | ⭐⭐⭐ Strong | Some flexibility needed | | Pure JSONB ❌ | ⚠️ Poor | ⭐⭐⭐ High | ⭐ Weak | Avoid for queryable data | | EAV Pattern ❌ | ⚠️ Very Poor | ⭐⭐⭐ High | ⭐ Weak | Not recommended |

Decision Tree:

• Need to filter/query this data? → Use Related Entity ✅
• Just storing UI preferences? → Can use JSONB 📝
• Truly unknown runtime schema? → Consider Hybrid approach

📊 Entities

User Entity

The main User entity represents a user in the system:

{
  id: string;                           // UUID primary key
  firstname: string;                    // User's first name
  lastname: string;                     // User's last name
  email: string;                        // Unique email address
  phone?: string;                       // Optional phone number
  avatar?: string;                      // Optional avatar URL
  timezone?: string;                    // User's timezone
  locale?: string;                      // User's locale (e.g., 'en-US')
  status?: string;                      // User status (extensible, e.g., 'ACTIVE', 'SUSPENDED')
  role?: string;                        // User role (extensible, e.g., 'ADMIN', 'USER', 'MODERATOR')
  isActive: boolean;                    // Account active flag
  isEmailVerified: boolean;             // Email verification status
  settings?: Record<string, unknown>;   // User settings (JSONB) - for UI preferences only
  metadata?: Record<string, unknown>;   // Additional metadata (JSONB) - for non-queryable data only
  createdAt: Date;                      // Creation timestamp
  updatedAt: Date;                      // Last update timestamp
}

⚠️ Important Notes:

1. role and status are extensible - Define your own values via UserModule.forRoot() configuration
2. Indexed fields - Both role and status have database indexes for fast queries
3. JSONB fields (settings, metadata) - Should only be used for data you'll never query or filter on
4. For business data - Use related entities instead of JSONB (see Extensibility section)

🔌 API Reference

UserService

createUser(firstname, lastname, email, phone?, avatar?, timezone?, locale?)

Creates a new user with validation.

const user = await userService.createUser(
  'John',
  'Doe',
  '[email protected]',
  '+1234567890',
  'https://avatar.url',
  'America/New_York',
  'en-US'
);

getUserById(userId: string)

Gets a user by their ID.

const user = await userService.getUserById('user-uuid');

getUserByEmail(email: string)

Gets a user by their email address.

const user = await userService.getUserByEmail('[email protected]');

updateUserProfile(userId, updates)

Updates user profile fields.

const user = await userService.updateUserProfile('user-uuid', {
  firstname: 'Jane',
  timezone: 'Europe/London',
});

updateUserSettings(userId, settings)

Updates user settings.

const user = await userService.updateUserSettings('user-uuid', {
  notificationsEnabled: true,
  theme: 'dark',
});

updateUserMetadata(userId, metadata)

Updates user metadata.

const user = await userService.updateUserMetadata('user-uuid', {
  role: 'ADMIN',
  department: 'Engineering',
});

setUserStatus(userId, isActive)

Activates or deactivates a user.

const user = await userService.setUserStatus('user-uuid', false);

verifyEmail(userId)

Marks a user's email as verified.

const user = await userService.verifyEmail('user-uuid');

getActiveUsers()

Gets all active users.

const users = await userService.getActiveUsers();

getVerifiedUsers()

Gets all users with verified emails.

const users = await userService.getVerifiedUsers();

setRole(userId: string, role: string)

Sets a user's role with validation.

const user = await userService.setRole('user-uuid', 'MODERATOR');
// Validates against allowedRoles from UserModule configuration

setStatus(userId: string, status: string)

Sets a user's status with validation.

const user = await userService.setStatus('user-uuid', 'BANNED');
// Validates against allowedStatuses from UserModule configuration

getUsersByRole(role: string)

Gets all users with a specific role.

const moderators = await userService.getUsersByRole('MODERATOR');

getUsersByStatus(status: string)

Gets all users with a specific status.

const bannedUsers = await userService.getUsersByStatus('BANNED');

getAllowedRoles()

Gets the list of allowed roles for your application.

const roles = userService.getAllowedRoles();
// Returns: ['ADMIN', 'USER', 'MODERATOR', ...]

getAllowedStatuses()

Gets the list of allowed statuses for your application.

const statuses = userService.getAllowedStatuses();
// Returns: ['ACTIVE', 'INACTIVE', 'SUSPENDED', ...]

isValidRole(role: string)

Checks if a role is valid for your application.

if (userService.isValidRole('MODERATOR')) {
  // Role is valid
}

isValidStatus(status: string)

Checks if a status is valid for your application.

if (userService.isValidStatus('BANNED')) {
  // Status is valid
}

🔐 Enums & Constants

USER_STATUS

enum USER_STATUS {
  ACTIVE = 'ACTIVE',
  INACTIVE = 'INACTIVE',
  SUSPENDED = 'SUSPENDED',
  PENDING_VERIFICATION = 'PENDING_VERIFICATION',
}

USER_ROLE

enum USER_ROLE {
  ADMIN = 'ADMIN',
  USER = 'USER',
}

USER_SETTINGS_KEY

const USER_SETTINGS_KEY = 'USER_SETTINGS';

🛡️ Guards & Decorators

Guards

UserGuard

Ensures the user is authenticated and active.

@UseGuards(UserGuard)
@Get()
async getProfile(@CurrentUserId() userId: string) {
  return this.userService.getUserById(userId);
}

UserRoleGuard

Enforces role-based access control.

@RequireRoles(USER_ROLE.ADMIN)
@UseGuards(UserGuard, UserRoleGuard)
@Delete(':id')
async deleteUser(@Param('id') id: string) {
  return this.userService.setUserStatus(id, false);
}

Decorators

@CurrentUserId()

Extracts the current user ID from the request.

@Get()
async getData(@CurrentUserId() userId: string) {
  return this.service.getUserData(userId);
}

@CurrentUser()

Extracts the full user object from the request.

@Get()
async getData(@CurrentUser() user: User) {
  return { user };
}

@RequireRoles(...roles)

Specifies required roles for a route.

@RequireRoles(USER_ROLE.ADMIN)
@UseGuards(UserGuard, UserRoleGuard)
@Delete(':id')
async delete(@Param('id') id: string) {
  // Only admins can access
}

🧪 Test Server

The module includes a standalone test server for development and testing:

Start Test Server

cd src/user
npm run test:dev

The server starts on port 3003 (configurable via .env).

Watch Mode

npm run test:watch

Environment Setup

1. Copy .env.example to .env:

cp test/.env.example test/.env

2. Configure your database settings in test/.env

Available Test Endpoints

POST   /users                     - Create user
GET    /users                     - Get all users
GET    /users/active              - Get active users
GET    /users/verified            - Get verified users
GET    /users/email/:email        - Get user by email
GET    /users/:id                 - Get user by ID
PUT    /users/:id/profile         - Update user profile
PUT    /users/:id/settings        - Update user settings
PUT    /users/:id/metadata        - Update user metadata
PUT    /users/:id/status          - Update user status
PUT    /users/:id/verify-email    - Verify user email
DELETE /users/:id                 - Delete user

📝 NPM Scripts

{
  "build": "tsc -p tsconfig.json",
  "prepublishOnly": "npm run build",
  "release:patch": "npm run build && npm version patch --no-git-tag-version && npm publish",
  "test:dev": "ts-node -r tsconfig-paths/register test/main.ts",
  "test:watch": "nodemon --watch src --watch test --ext ts --exec npm run test:dev",
  "migration:generate": "ts-node node_modules/.bin/typeorm migration:generate -d test/data-source.ts test/migrations/$npm_config_name",
  "migration:run": "ts-node node_modules/.bin/typeorm migration:run -d test/data-source.ts",
  "migration:revert": "ts-node node_modules/.bin/typeorm migration:revert -d test/data-source.ts"
}

💡 Usage Examples

Example 1: Create and Verify User

// Create a new user
const user = await userService.createUser(
  'Jane',
  'Smith',
  '[email protected]',
  '+1987654321',
  null,
  'America/Los_Angeles',
  'en-US'
);

// Verify email
await userService.verifyEmail(user.id);

Example 2: Update User Profile

const updatedUser = await userService.updateUserProfile(userId, {
  firstname: 'Janet',
  timezone: 'Europe/Paris',
  locale: 'fr-FR',
});

Example 3: Manage User Settings

// Update settings
await userService.updateUserSettings(userId, {
  notificationsEnabled: true,
  emailNotifications: false,
  theme: 'dark',
});

// Get user with settings
const user = await userService.getUserById(userId);
console.log(user.settings);

Example 4: Role-Based Access

// Set user role in metadata
await userService.updateUserMetadata(userId, {
  role: USER_ROLE.ADMIN,
});

// Controller with role guard
@Controller('admin')
export class AdminController {
  @RequireRoles(USER_ROLE.ADMIN)
  @UseGuards(UserGuard, UserRoleGuard)
  @Get('dashboard')
  async getDashboard(@CurrentUser() user: User) {
    return { admin: user };
  }
}

Example 5: Filter Users

// Get all active users
const activeUsers = await userService.getActiveUsers();

// Get verified users
const verifiedUsers = await userService.getVerifiedUsers();

// Find by email
const user = await userService.getUserByEmail('[email protected]');

🔧 Database Migrations

Generate a Migration

npm run migration:generate --name=AddUserFields

Run Migrations

npm run migration:run

Revert Migration

npm run migration:revert

� Best Practices

1. Always Use Indexes for Queryable Fields

// ✅ GOOD: Indexed columns
@Entity('user_profile')
export class UserProfile {
  @Column() @Index() // Fast lookups
  userId: string;
  
  @Column() @Index() // Fast filtering
  country: string;
  
  @Column({ unique: true }) // Automatically indexed
  taxId: string;
}

// ❌ BAD: JSONB for queryable data
user.metadata = {
  country: 'US', // Will be slow to query!
  taxId: '123'   // No unique constraint possible!
};

2. Use JSONB Only for Display Data

// ✅ GOOD: Non-queryable UI preferences
user.settings = {
  theme: 'dark',
  language: 'en',
  sidebarCollapsed: true
};

// ✅ GOOD: Audit/log data you never filter on
user.metadata = {
  lastLoginIp: '192.168.1.1',
  userAgent: 'Mozilla/5.0...',
  registrationSource: 'mobile_app'
};

// ❌ BAD: Business data you need to query
user.metadata = {
  subscriptionTier: 'premium', // Use UserSubscription entity!
  department: 'Engineering',    // Use UserEmployment entity!
  isActive: true               // Use column on User entity!
};

3. Create Composite Indexes for Common Queries

@Entity('user_subscription')
@Index(['userId', 'isActive']) // For userId + status queries
@Index(['plan', 'expiresAt'])  // For plan + expiration queries
export class UserSubscription {
  @Column() userId: string;
  @Column() plan: string;
  @Column() isActive: boolean;
  @Column() expiresAt: Date;
}

// Now these queries are super fast:
await repo.find({
  where: { userId: 'abc-123', isActive: true } // Uses composite index
});

await repo.find({
  where: { 
    plan: 'premium', 
    expiresAt: MoreThan(new Date()) 
  } // Uses composite index
});

4. Use TypeORM QueryBuilder for Complex Queries

// ✅ Complex query with proper indexes
const result = await employmentRepo
  .createQueryBuilder('emp')
  .leftJoinAndSelect('emp.user', 'user')
  .where('emp.department = :dept', { dept: 'Engineering' })
  .andWhere('emp.isActive = :active', { active: true })
  .andWhere('user.isEmailVerified = :verified', { verified: true })
  .orderBy('emp.hireDate', 'DESC')
  .limit(50)
  .getMany();

5. Validate Data at the Application Layer

// Use DTOs with class-validator
export class CreateEmployeeDto {
  @IsString()
  @MinLength(2)
  firstname: string;

  @IsEmail()
  email: string;

  @IsEnum(['Engineering', 'Sales', 'Marketing'])
  department: string;

  @IsPositive()
  @Max(1000000)
  salary: number;
}

6. Handle Relations Efficiently

// ✅ GOOD: Eager load when you know you need it
const employees = await repo.find({
  where: { department: 'Engineering' },
  relations: ['user'] // Load user in same query
});

// ✅ GOOD: Load separately for optional data
const employee = await repo.findOne({ where: { userId } });
if (needUserDetails) {
  employee.user = await userService.findOne(userId);
}

// ❌ BAD: N+1 query problem
const employees = await repo.find({ where: { department: 'Engineering' } });
for (const emp of employees) {
  emp.user = await userService.findOne(emp.userId); // Queries in loop!
}

7. Use Transactions for Multi-Entity Operations

async createEmployeeWithProfile(data: CreateEmployeeDto) {
  return this.dataSource.transaction(async (manager) => {
    // Create user
    const user = await manager.save(User, {
      firstname: data.firstname,
      lastname: data.lastname,
      email: data.email
    });

    // Create employment record
    const employment = await manager.save(UserEmployment, {
      userId: user.id,
      department: data.department,
      salary: data.salary
    });

    // If anything fails, both are rolled back
    return { user, employment };
  });
}

8. Document Your Extension Entities

/**
 * UserEmployment Entity
 * 
 * Stores employee-specific information for users who are employees.
 * Related to User entity via userId.
 * 
 * Indexes:
 * - userId: For fast user lookups
 * - department: For filtering by department
 * - (department, isActive): For active employee queries by department
 * 
 * @example
 * const emp = await employmentRepo.findOne({ 
 *   where: { userId: 'abc-123' } 
 * });
 */
@Entity('user_employment')
@Index(['department', 'isActive'])
export class UserEmployment {
  // ...
}

9. Plan for Data Growth

// Consider pagination for large result sets
async getEmployeesByDepartment(
  department: string, 
  page: number = 1, 
  limit: number = 50
) {
  return this.employmentRepo.find({
    where: { department, isActive: true },
    relations: ['user'],
    skip: (page - 1) * limit,
    take: limit,
    order: { createdAt: 'DESC' }
  });
}

// Add count for pagination UI
async getEmployeeCount(department: string) {
  return this.employmentRepo.count({
    where: { department, isActive: true }
  });
}

10. Monitor Query Performance

// Enable query logging in development
TypeOrmModule.forRoot({
  // ...
  logging: ['query', 'error', 'warn'],
  maxQueryExecutionTime: 1000, // Warn if query takes > 1s
});

// Log slow queries in your service
const startTime = Date.now();
const result = await this.repo.find({ ... });
const duration = Date.now() - startTime;

if (duration > 100) {
  this.logger.warn(`Slow query detected: ${duration}ms`);
}

🔄 Migration from JSONB

If you have existing data in JSONB that needs to be queryable:

Step 1: Create New Entity

@Entity('user_employment')
@Index(['userId'])
@Index(['department'])
export class UserEmployment {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  userId: string;

  @Column()
  department: string;

  @Column({ type: 'decimal', precision: 10, scale: 2 })
  salary: number;
}

Step 2: Create Migration

npm run migration:generate --name=MigrateEmploymentData

Step 3: Write Migration Logic

export class MigrateEmploymentData1234567890 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 1. Create table with indexes
    await queryRunner.query(`
      CREATE TABLE user_employment (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        "userId" UUID NOT NULL,
        department VARCHAR(100),
        salary DECIMAL(10,2),
        "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
      
      CREATE INDEX idx_employment_user ON user_employment("userId");
      CREATE INDEX idx_employment_dept ON user_employment(department);
    `);

    // 2. Migrate data
    await queryRunner.query(`
      INSERT INTO user_employment ("userId", department, salary)
      SELECT 
        id,
        metadata->>'department',
        (metadata->>'salary')::DECIMAL
      FROM "user"
      WHERE metadata ? 'department';
    `);

    // 3. Clean up old data
    await queryRunner.query(`
      UPDATE "user"
      SET metadata = metadata - 'department' - 'salary'
      WHERE metadata ? 'department';
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Migrate back if needed
    await queryRunner.query(`
      UPDATE "user"
      SET metadata = jsonb_set(
        jsonb_set(
          COALESCE(metadata, '{}'::jsonb),
          '{department}',
          to_jsonb(emp.department)
        ),
        '{salary}',
        to_jsonb(emp.salary)
      )
      FROM user_employment emp
      WHERE "user".id = emp."userId";
    `);
    
    await queryRunner.query(`DROP TABLE IF EXISTS user_employment;`);
  }
}

Step 4: Run Migration

npm run migration:run

📚 Additional Resources

Official Documentation

• NestJS Documentation
• TypeORM Documentation
• PostgreSQL Performance Tips

Related Modules

• @venturialstd/core - Core shared functionality
• @venturialstd/organization - Organization management module
• @venturialstd/auth - Authentication module

Performance References

• PostgreSQL Index Types
• JSONB Performance
• Query Optimization

�📄 License

This module is part of the Venturial Standard Library.

🤝 Contributing

For contribution guidelines, please refer to the main repository's CONTRIBUTING.md.

📞 Support

For issues and questions, please open an issue in the main repository.