Database Package

The @repo/db package contains TypeORM entity definitions and database configuration shared across the application.

Overview

This package provides:

TypeORM entity definitions for all database tables
Shared database configuration
Relationships between entities
Database naming strategies

Structure

packages/db/
├── src/
│   ├── entities/          # Entity definitions
│   │   ├── User.entity.ts
│   │   ├── Course.entity.ts
│   │   ├── Module.entity.ts
│   │   ├── Quiz.entity.ts
│   │   └── ...
│   │
│   ├── index.ts           # Main exports
│   └── data-source.ts     # DataSource configuration (if applicable)
│
├── package.json
└── tsconfig.json

Entity Definitions

Entities use TypeORM decorators to define database schema and relationships.

Example Entity

typescript

// src/entities/Course.entity.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  OneToMany,
  CreateDateColumn,
  UpdateDateColumn,
} from "typeorm";
import { Module } from "./Module.entity";
import { Membership } from "./Membership.entity";
import { CourseFile } from "./CourseFile.entity";

@Entity()
export class Course {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ unique: true })
  code: string;

  @Column({ type: "text", nullable: true })
  description: string | null;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  // Relationships
  @OneToMany(() => Module, (module) => module.course)
  modules: Module[];

  @OneToMany(() => Membership, (membership) => membership.course)
  memberships: Membership[];

  @OneToMany(() => CourseFile, (file) => file.course)
  files: CourseFile[];
}

Key Entities

User Management

User: Core user entity

typescript

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column()
  name: string;

  @Column({ nullable: true })
  passwordHash: string | null;

  @OneToMany(() => OAuthCredential, (credential) => credential.user)
  oauthCredentials: OAuthCredential[];

  @OneToMany(() => Membership, (membership) => membership.user)
  memberships: Membership[];
}

OAuthCredential: OAuth provider credentials

typescript

@Entity()
export class OAuthCredential {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  provider: string; // 'google', 'github', etc.

  @Column()
  providerUserId: string;

  @ManyToOne(() => User, (user) => user.oauthCredentials)
  user: User;

  @Column()
  userId: number;
}

Course Structure

Course: Main course entity (shown above)

Module: Course modules/sections

typescript

@Entity()
export class Module {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ default: 0 })
  order: number;

  @ManyToOne(() => Course, (course) => course.modules)
  course: Course;

  @Column()
  courseId: number;

  @OneToMany(() => Chapter, (chapter) => chapter.module)
  chapters: Chapter[];
}

Chapter: Module chapters

typescript

@Entity()
export class Chapter {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column({ type: "text", nullable: true })
  content: string | null;

  @Column({ default: 0 })
  order: number;

  @ManyToOne(() => Module, (module) => module.chapters)
  module: Module;

  @Column()
  moduleId: number;
}

Course Content

CourseFile: File attachments

typescript

@Entity()
export class CourseFile {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  storagePath: string;

  @ManyToOne(() => Course, (course) => course.files)
  course: Course;

  @Column()
  courseId: number;

  @OneToOne(() => CourseFileMetadata, (metadata) => metadata.file)
  metadata: CourseFileMetadata;
}

CourseFileMetadata: File metadata

typescript

@Entity()
export class CourseFileMetadata {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  mimeType: string;

  @Column({ type: "bigint" })
  size: number;

  @OneToOne(() => CourseFile, (file) => file.metadata)
  @JoinColumn()
  file: CourseFile;

  @Column()
  fileId: number;
}

Quizzes

Quiz: Quiz definition

typescript

@Entity()
export class Quiz {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column({ type: "text", nullable: true })
  description: string | null;

  @Column({ nullable: true })
  timeLimit: number | null; // in minutes

  @Column({ default: 1 })
  maxAttempts: number;

  @ManyToOne(() => Course, (course) => course.quizzes)
  course: Course;

  @Column()
  courseId: number;

  @OneToMany(() => QuizQuestion, (question) => question.quiz)
  questions: QuizQuestion[];

  @OneToMany(() => QuizAttempt, (attempt) => attempt.quiz)
  attempts: QuizAttempt[];
}

QuizQuestion: Individual questions

typescript

@Entity()
export class QuizQuestion {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  type: "MULTIPLE_CHOICE" | "TRUE_FALSE" | "SHORT_ANSWER" | "ESSAY";

  @Column({ type: "text" })
  questionText: string;

  @Column({ type: "jsonb", nullable: true })
  options: string[] | null; // For multiple choice

  @Column({ type: "jsonb" })
  correctAnswer: unknown;

  @Column({ default: 1 })
  points: number;

  @Column({ default: 0 })
  order: number;

  @ManyToOne(() => Quiz, (quiz) => quiz.questions)
  quiz: Quiz;

  @Column()
  quizId: number;
}

QuizAttempt: Student quiz attempts

typescript

@Entity()
export class QuizAttempt {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User)
  user: User;

  @Column()
  userId: number;

  @ManyToOne(() => Quiz, (quiz) => quiz.attempts)
  quiz: Quiz;

  @Column()
  quizId: number;

  @CreateDateColumn()
  startedAt: Date;

  @Column({ nullable: true })
  submittedAt: Date | null;

  @Column({ type: "decimal", precision: 5, scale: 2, nullable: true })
  score: number | null;

  @OneToMany(() => QuizResponse, (response) => response.attempt)
  responses: QuizResponse[];
}

Discussions

Discussion: Discussion threads

typescript

@Entity()
export class Discussion {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column({ type: "text" })
  content: string;

  @ManyToOne(() => User)
  author: User;

  @Column()
  authorId: number;

  @ManyToOne(() => Course)
  course: Course;

  @Column()
  courseId: number;

  @CreateDateColumn()
  createdAt: Date;

  @OneToMany(() => DiscussionComment, (comment) => comment.discussion)
  comments: DiscussionComment[];
}

DiscussionComment: Comments on discussions

typescript

@Entity()
export class DiscussionComment {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: "text" })
  content: string;

  @ManyToOne(() => User)
  author: User;

  @Column()
  authorId: number;

  @ManyToOne(() => Discussion, (discussion) => discussion.comments)
  discussion: Discussion;

  @Column()
  discussionId: number;

  @CreateDateColumn()
  createdAt: Date;
}

Memberships and Permissions

Membership: User-course relationship

typescript

@Entity()
export class Membership {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User, (user) => user.memberships)
  user: User;

  @Column()
  userId: number;

  @ManyToOne(() => Course)
  course: Course;

  @Column()
  courseId: number;

  @ManyToOne(() => Role, (role) => role.memberships)
  role: Role;

  @Column()
  roleId: number;

  @CreateDateColumn()
  joinedAt: Date;
}

Role: User roles

typescript

@Entity()
export class Role {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  name: string;

  @Column({ type: "text", nullable: true })
  description: string | null;

  @ManyToMany(() => Permission, (permission) => permission.roles)
  @JoinTable()
  permissions: Permission[];

  @OneToMany(() => Membership, (membership) => membership.role)
  memberships: Membership[];
}

Permission: Granular permissions

typescript

@Entity()
export class Permission {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  type: string;

  @Column({ type: "text", nullable: true })
  description: string | null;

  @ManyToMany(() => Role, (role) => role.permissions)
  roles: Role[];
}

Analytics

ContentActivity: Content engagement tracking

typescript

@Entity()
export class ContentActivity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User)
  user: User;

  @Column()
  userId: number;

  @Column()
  contentType: "MODULE" | "CHAPTER" | "FILE" | "QUIZ";

  @Column()
  contentId: number;

  @Column()
  activityType: "VIEW" | "DOWNLOAD" | "COMPLETE";

  @Column({ type: "int", nullable: true })
  duration: number | null; // seconds

  @CreateDateColumn()
  timestamp: Date;
}

ChatActivity: Chat interaction tracking

typescript

@Entity()
export class ChatActivity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User)
  user: User;

  @Column()
  userId: number;

  @Column({ type: "text" })
  userMessage: string;

  @Column({ type: "text" })
  aiResponse: string;

  @Column()
  tokenCount: number;

  @CreateDateColumn()
  timestamp: Date;
}

Calendar

Calendar: Course calendar

typescript

@Entity()
export class Calendar {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToOne(() => Course)
  @JoinColumn()
  course: Course;

  @Column()
  courseId: number;

  @OneToMany(() => CalendarEvent, (event) => event.calendar)
  events: CalendarEvent[];
}

CalendarEvent: Calendar events

typescript

@Entity()
export class CalendarEvent {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column({ type: "text", nullable: true })
  description: string | null;

  @Column()
  startTime: Date;

  @Column()
  endTime: Date;

  @ManyToOne(() => Calendar, (calendar) => calendar.events)
  calendar: Calendar;

  @Column()
  calendarId: number;
}

AI & Tool System Entities

CourseAiConfig: Per-course AI provider configuration

typescript

@Entity("course_ai_configs")
export class CourseAiConfig {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  courseId: number;

  @Column({
    type: "varchar",
    length: 50,
    default: "openai",
  })
  provider: "openai" | "anthropic" | "google" | "custom" | "mock";

  @Column({ nullable: true })
  baseUrl: string; // For custom backends (Ollama, LM Studio)

  @Column({ nullable: true })
  apiKey: string; // Per-course API key override

  @Column({ nullable: true })
  model: string; // Model selection (e.g., gpt-4, claude-3-opus)

  @Column({ default: false })
  useCustomBackend: boolean;

  @Column({ default: true })
  isEnabled: boolean;
}

AIToolDefinition: AI-callable tool/function definitions

typescript

@Entity("ai_tool_definitions")
export class AIToolDefinition {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  name: string; // e.g., "calendar_create_event"

  @Column()
  displayName: string;

  @Column({ type: "text" })
  description: string;

  @Column()
  category: string; // e.g., "calendar", "quiz", "course"

  @Column()
  handlerPath: string; // e.g., "CalendarService.createEvent"

  @Column({ default: true })
  isEnabled: boolean;

  @Column({ default: true })
  requiresAuth: boolean;

  @Column({ type: "varchar", nullable: true })
  requiredPermission: string | null;

  @OneToMany(() => AIToolParameter, (param) => param.tool)
  parameters: AIToolParameter[];
}

AIToolParameter: Parameters for AI tools

typescript

@Entity("ai_tool_parameters")
export class AIToolParameter {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  toolId: number;

  @ManyToOne(() => AIToolDefinition, (tool) => tool.parameters)
  tool: AIToolDefinition;

  @Column()
  name: string; // e.g., "calendarId", "title"

  @Column({
    type: "enum",
    enum: ["string", "number", "boolean", "date", "array", "object"],
  })
  type: string;

  @Column({ type: "text" })
  description: string;

  @Column({ default: false })
  isRequired: boolean;
}

AIToolPermission: Per-user AI tool permissions

typescript

@Entity("ai_tool_permissions")
export class AIToolPermission {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @ManyToOne(() => User)
  user: User;

  @Column()
  toolId: number;

  @ManyToOne(() => AIToolDefinition)
  tool: AIToolDefinition;

  @Column()
  courseId: number;

  @ManyToOne(() => Course)
  course: Course;

  @Column({ type: "varchar", length: 20 })
  scope: "always" | "conversation" | "once"; // Permission duration

  @Column({ default: false })
  isGranted: boolean;
}

Conversation: AI chat conversations

typescript

@Entity("conversations")
export class Conversation {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  userId: number;

  @ManyToOne(() => User)
  user: User;

  @Column()
  courseId: number;

  @ManyToOne(() => Course)
  course: Course;

  @OneToMany(() => Message, (m) => m.conversation)
  messages: Message[];

  @CreateDateColumn()
  createdAt: Date;
}

Message: Chat messages with tool call support

typescript

@Entity("messages")
export class Message {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  conversationId: number;

  @ManyToOne(() => Conversation, (c) => c.messages)
  conversation: Conversation;

  @Column({ type: "varchar", length: 20 })
  role: "user" | "assistant";

  @Column({ type: "text" })
  content: string;

  // Tool call data (for assistant requesting tool execution)
  @Column({ type: "json", nullable: true })
  toolCall: {
    toolId: number;
    toolName: string;
    parameters: Record<string, any>;
    requiresPermission: boolean;
  } | null;

  // Tool result data (for tool execution results)
  @Column({ type: "json", nullable: true })
  toolResult: {
    toolId: number;
    toolName: string;
    success: boolean;
    result: any;
    error?: string;
  } | null;

  @CreateDateColumn()
  createdAt: Date;
}

Authentication & Activity Entities

RefreshToken: JWT refresh token management

typescript

@Entity("refresh_tokens")
@Unique(["userId", "token"])
export class RefreshToken {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @ManyToOne(() => User, (u) => u.refreshTokens)
  user: User;

  @Column()
  token: string;

  @Column("timestamptz")
  issuedAt: Date;

  @Column("timestamptz")
  expiresAt: Date;
}

UserCredential: User login credentials

typescript

@Entity("user_credentials")
export class UserCredential {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @ManyToOne(() => User)
  user: User;

  @Column({ nullable: true })
  passwordHash: string | null;

  @Column({ type: "varchar", nullable: true })
  provider: "local" | "oauth" | null;

  @UpdateDateColumn()
  lastPasswordChange: Date;
}

UserActivity: User activity tracking

typescript

@Entity("user_activities")
export class UserActivity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @ManyToOne(() => User)
  user: User;

  @Column({ type: "varchar" })
  activityType: string; // e.g., "LOGIN", "LOGOUT", "VIEW_CONTENT"

  @Column({ type: "json", nullable: true })
  metadata: Record<string, any> | null;

  @Column({ nullable: true })
  ipAddress: string | null;

  @CreateDateColumn()
  timestamp: Date;
}

Usage in Backend

Repository Pattern

typescript

import { DataSource } from "typeorm";
import { Course, Module } from "@repo/db";

export class CourseService {
  constructor(private dataSource: DataSource) {}

  async getCourse(id: number): Promise<Course> {
    const courseRepo = this.dataSource.getRepository(Course);

    return courseRepo.findOne({
      where: { id },
      relations: ["modules", "memberships"],
    });
  }

  async createCourse(data: CreateCourseDto): Promise<Course> {
    const courseRepo = this.dataSource.getRepository(Course);

    const course = courseRepo.create(data);
    return courseRepo.save(course);
  }
}

Query Builder

typescript

const courses = await this.dataSource
  .getRepository(Course)
  .createQueryBuilder("course")
  .leftJoinAndSelect("course.modules", "module")
  .leftJoinAndSelect("course.memberships", "membership")
  .where("membership.userId = :userId", { userId })
  .orderBy("course.createdAt", "DESC")
  .getMany();

Naming Strategy

The package uses snake_case for database columns:

typescript

import { SnakeNamingStrategy } from "typeorm-naming-strategies";

export const dataSource = new DataSource({
  // ... other config
  namingStrategy: new SnakeNamingStrategy(),
});

TypeScript:

typescript

class User {
  createdAt: Date;
  passwordHash: string;
}

Database:

sql

created_at TIMESTAMP
password_hash VARCHAR

Migrations

Generate migrations when entities change:

bash

# Generate migration
typeorm migration:generate -n AddQuizTables

# Run migrations
typeorm migration:run

# Revert migration
typeorm migration:revert

Best Practices

Always specify nullable explicitly
Use appropriate column types (text vs varchar, int vs bigint)
Define relationships from both sides
Use indexes on frequently queried columns
Keep entities focused - one entity per table
Document complex relationships
Use transactions for multi-entity operations

Backend Components - Using entities in services
Type Safety Flow - How entity types flow to frontend
Architecture Overview - Database in system architecture

Database Package ​

Overview ​

Structure ​

Entity Definitions ​

Example Entity ​

Key Entities ​

User Management ​

Course Structure ​

Course Content ​

Quizzes ​

Discussions ​

Memberships and Permissions ​

Analytics ​

Calendar ​

AI & Tool System Entities ​

Authentication & Activity Entities ​

Usage in Backend ​

Repository Pattern ​

Query Builder ​

Naming Strategy ​

Migrations ​

Best Practices ​

Related Documentation ​

Database Package

Overview

Structure

Entity Definitions

Example Entity

Key Entities

User Management

Course Structure

Course Content

Quizzes

Discussions

Memberships and Permissions

Analytics

Calendar

AI & Tool System Entities

Authentication & Activity Entities

Usage in Backend

Repository Pattern

Query Builder

Naming Strategy

Migrations

Best Practices

Related Documentation