Skip to main content

​
Database Schema

KnowledgeCheckr uses MySQL as its relational database with Drizzle ORM for type-safe database operations. The schema is defined in database/drizzle/schema.ts.

​
Core Entities

​
User

Stores user account information for both authenticated and anonymous users. 
export const db_user = mysqlTable('User', {
  id: varchar({ length: 36 }).notNull().primaryKey(),
  name: tinytext().notNull(),
  email: tinytext().notNull(),
  emailVerified: tinyint().notNull(),
  image: varchar({ length: 512 }),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  isAnonymous: boolean(),
})
Fields:
  • id: UUID primary key (36 characters)
  • name: User’s display name
  • email: User’s email address
  • emailVerified: Boolean flag (0/1)
  • image: Optional profile image URL (max 512 chars)
  • isAnonymous: Distinguishes temporary anonymous users
  • Timestamps: createdAt, updatedAt

​
Account

Stores OAuth provider information for social login integrations. 
export const db_account = mysqlTable('Account', {
  id: varchar({ length: 36 }).notNull().primaryKey(),
  accountId: tinytext().notNull(),
  providerId: tinytext().notNull(),
  userId: varchar('user_id', { length: 36 }).notNull(),
  accessToken: mediumtext(),
  refreshToken: mediumtext(),
  idToken: mediumtext(),
  accessTokenExpiresAt: datetime({ mode: 'string' }),
  refreshTokenExpiresAt: datetime({ mode: 'string' }),
  scope: tinytext(),
  password: tinytext(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
})
Relationships:
  • Foreign key: userId → User.id (cascade delete)
Supported Providers:
  • GitHub OAuth
  • Google OAuth
  • Dex (Generic OAuth)
  • Email/Password

​
Session

Manages user authentication sessions. 
export const db_session = mysqlTable('Session', {
  id: varchar({ length: 36 }).notNull().primaryKey(),
  token: tinytext().notNull(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  expiresAt: datetime({ mode: 'string' }).notNull(),
  ipAddress: tinytext(),
  userAgent: tinytext(),
  userId: varchar('user_id', { length: 36 }).notNull(),
})
Features:
  • Session token storage
  • IP address tracking
  • User agent tracking
  • Expiration management
  • Foreign key: userId → User.id (cascade delete)

​
Verification

Handles email verification and password reset tokens. 
export const db_verification = mysqlTable('Verification', {
  id: varchar({ length: 36 }).notNull().primaryKey(),
  identifier: tinytext().notNull(),
  value: varchar({ length: 1024 }).notNull(),
  expiresAt: datetime({ mode: 'string' }).notNull(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
})

​
Knowledge Check Entities

​
KnowledgeCheck

The main entity representing a quiz or assessment. 
export const db_knowledgeCheck = mysqlTable('KnowledgeCheck', {
  id: primaryKeyUUID,
  name: tinytext().notNull(),
  description: mediumtext(),
  owner_id: varchar('owner_id', { length: 36 }).notNull(),
  share_key: varchar('public_token', { length: 50 }).unique(),
  openDate: datetime({ mode: 'string' }).notNull(),
  closeDate: datetime({ mode: 'string' }).$default(() => sql`NULL`),
  difficulty: int().notNull(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  expiresAt: datetime({ mode: 'string' }).default(sql`NULL`),
})
Fields:
  • id: Auto-generated UUID
  • name: Check title
  • description: Optional detailed description
  • owner_id: Creator user ID
  • share_key: Unique sharing token (50 chars)
  • openDate: When check becomes available
  • closeDate: Optional deadline
  • difficulty: Numeric difficulty rating
  • expiresAt: Optional expiration date
Relationships:
  • Foreign key: owner_id → User.id (cascade delete)

​
KnowledgeCheck_Settings

Configurable settings for examinations and practice modes. 
export const db_knowledgeCheckSettings = mysqlTable('KnowledgeCheck_Settings', {
  id: primaryKeyUUID,
  knowledgecheckId: varchar('knowledgecheck_id', { length: 36 }).notNull(),
  
  // Examination Settings
  allowAnonymous: tinyint('allow_anonymous').notNull().default(0),
  allowFreeNavigation: tinyint('allow_free_navigation').notNull().default(0),
  questionOrder: mysqlEnum(['create-order', 'random']).notNull().default('create-order'),
  answerOrder: mysqlEnum(['create-order', 'random']).notNull().default('create-order'),
  examTimeFrameSeconds: int().notNull().default(3600),
  examinationAttemptCount: int().notNull().default(1),
  enableExaminations: int().notNull().default(1),
  startDate: datetime({ mode: 'string' }).notNull(),
  endDate: datetime({ mode: 'string' }),
  
  // Practice Settings
  enablePracticing: int().notNull().default(1),
  allowedPracticeCount: int().default(sql`NULL`),
  
  // Sharing Settings
  shareAccessibility: int().notNull().default(1),
})
Configuration Options: Examination Mode:
  • allowAnonymous: Permit anonymous users to take exams
  • allowFreeNavigation: Allow jumping between questions
  • questionOrder: Display order (create-order or random)
  • answerOrder: Answer display order
  • examTimeFrameSeconds: Time limit in seconds
  • examinationAttemptCount: Maximum attempts allowed
  • startDate / endDate: Availability window
Practice Mode:
  • enablePracticing: Toggle practice mode
  • allowedPracticeCount: Attempt limit (NULL = unlimited)
Relationships:
  • Foreign key: knowledgecheckId → KnowledgeCheck.id (cascade delete)

​
Category

Organizes questions into logical groups with optional prerequisites. 
export const db_category = mysqlTable('Category', {
  id: varchar({ length: 36 }).notNull().primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  prequisiteCategoryId: varchar('prequisite_category_id', { length: 36 }),
  knowledgecheckId: varchar('knowledgecheck_id', { length: 36 }).notNull(),
})
Constraints:
  • Unique constraint on (knowledgecheckId, name) - prevents duplicate categories within a check
  • Self-referencing foreign key for prequisiteCategoryId
Relationships:
  • Foreign key: knowledgecheckId → KnowledgeCheck.id (cascade delete)
  • Foreign key: prequisiteCategoryId → Category.id (set null on delete)

​
Question

Individual assessment questions with multiple type support. 
export const db_question = mysqlTable('Question', {
  id: primaryKeyUUID,
  type: mysqlEnum(['single-choice', 'multiple-choice', 'open-question', 'drag-drop']).notNull(),
  question: mediumtext().notNull(),
  points: int().notNull(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  categoryId: varchar('category_id', { length: 36 }).notNull(),
  accessibility: mysqlEnum(['all', 'practice-only', 'exam-only']).notNull().default('all'),
  _position: int().notNull(),
  knowledgecheckId: varchar('knowledgecheck_id', { length: 36 }).notNull(),
})
Question Types:
  • single-choice: One correct answer
  • multiple-choice: Multiple correct answers
  • open-question: Free-text response
  • drag-drop: Order-based answer
Accessibility Modes:
  • all: Available in practice and examination
  • practice-only: Only in practice mode
  • exam-only: Only in examination mode
Fields:
  • _position: Display order within the check
  • points: Score value for correct answer
Relationships:
  • Foreign key: categoryId → Category.id (cascade delete)
  • Foreign key: knowledgecheckId → KnowledgeCheck.id (cascade delete)

​
Answer

Possible answers for questions. 
export const db_answer = mysqlTable('Answer', {
  id: primaryKeyUUID,
  answer: mediumtext().notNull(),
  correct: tinyint(),
  position: int(),
  createdAt: datetime({ mode: 'string' }).notNull(),
  updatedAt: datetime({ mode: 'string' }).notNull(),
  questionId: varchar('Question_id', { length: 36 }).notNull(),
  _position: int().notNull(),
})
Fields:
  • answer: The answer text
  • correct: Boolean indicating correctness (for choice questions)
  • position: Correct position (for drag-drop questions)
  • _position: Display order
Relationships:
  • Foreign key: questionId → Question.id (cascade delete)

​
User Activity & Results

​
User_contributesTo_KnowledgeCheck

Many-to-many relationship for collaborators. 
export const db_userContributesToKnowledgeCheck = mysqlTable(
  'User_contributesTo_KnowledgeCheck',
  {
    userId: varchar('user_id', { length: 36 }).notNull(),
    knowledgecheckId: varchar('knowledgecheck_id', { length: 36 }).notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.userId, table.knowledgecheckId] }),
  ]
)
Purpose: Tracks users who can edit a knowledge check (beyond the owner).

​
User_has_done_KnowledgeCheck

Records completed assessments and results. 
export const db_userHasDoneKnowledgeCheck = mysqlTable(
  'User_has_done_KnowledgeCheck',
  {
    id: int().autoincrement().notNull(),
    type: mysqlEnum(['practice', 'examination']).notNull(),
    userId: varchar('user_id', { length: 36 }).notNull(),
    knowledgeCheckId: varchar('knowledgeCheck_id', { length: 36 }).notNull(),
    startedAt: datetime({ mode: 'string' }).notNull(),
    finishedAt: datetime({ mode: 'string' }).notNull(),
    score: int().notNull(),
    results: json().$type<QuestionInput[]>().notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.id, table.userId, table.knowledgeCheckId] }),
  ]
)
Fields:
  • type: practice or examination
  • startedAt / finishedAt: Session duration tracking
  • score: Calculated score
  • results: JSON array of question responses
Composite Primary Key: (id, userId, knowledgeCheckId)

​
Entity Relationship Diagram

​
Schema Conventions

​
UUID Generation

const primaryKeyUUID = varchar({ length: 36 })
  .notNull()
  .primaryKey()
  .$defaultFn(() => getUUID())
All primary entities use UUIDs for:
  • Distributed system compatibility
  • Security (non-sequential IDs)
  • Easy data migration

​
Timestamps

createdAt: datetime({ mode: 'string' })
  .notNull()
  .default(sql`CURRENT_TIMESTAMP`)
  .$default(() => formatDatetime(new Date(Date.now()))),
updatedAt: datetime({ mode: 'string' })
  .notNull()
  .default(sql`CURRENT_TIMESTAMP`)
  .$onUpdate(() => formatDatetime(new Date(Date.now())))
  • createdAt: Set on insert
  • updatedAt: Updated on every modification
  • Stored as ISO string format

​
Foreign Key Actions

Cascade Delete: Used when child entities should be removed with parent
  • Account.userId → User.id
  • KnowledgeCheck.owner_id → User.id
  • Question.categoryId → Category.id
Set Null: Used for optional relationships
  • Category.prequisiteCategoryId → Category.id

​
Indexes

Foreign keys are automatically indexed: 
index('fk_Question_Category1_idx').on(table.categoryId)
Improves query performance for:
  • Joins
  • WHERE clauses on foreign keys
  • CASCADE operations

​
Database Connection Management

​
Drizzle ORM Configuration

Location: database/Database.ts 
export default async function getDatabase() {
  if (connection === null || !(await isConnectionAlive())) {
    connection = await getConnection()
  }
  
  return drizzle({
    client: connection,
    schema: drizzleSchema,
    mode: 'default',
  })
}
Features:
  • Connection health checking
  • Automatic reconnection
  • Development connection caching
  • Production connection per request

​
Migration Management

Configuration: drizzle.config.ts 
# Generate migration
npx drizzle-kit generate

# Run migration
npx drizzle-kit migrate

# View schema
npx drizzle-kit studio
Migration Directory: database/drizzle/migrations/

​
Query Examples

​
Fetching a Knowledge Check with Relations

import getDatabase from '@/database/Database'
import { db_knowledgeCheck, db_question, db_category } from '@/database/drizzle/schema'
import { eq } from 'drizzle-orm'

const db = await getDatabase()
const check = await db.query.db_knowledgeCheck.findFirst({
  where: eq(db_knowledgeCheck.id, checkId),
  with: {
    categories: {
      with: {
        questions: {
          with: {
            answers: true,
          },
        },
      },
    },
    settings: true,
  },
})

​
Recording Examination Results

const db = await getDatabase()
await db.insert(db_userHasDoneKnowledgeCheck).values({
  type: 'examination',
  userId: user.id,
  knowledgeCheckId: check.id,
  startedAt: formatDatetime(startTime),
  finishedAt: formatDatetime(endTime),
  score: calculatedScore,
  results: JSON.stringify(answers),
})

​
Data Integrity

​
Constraints

  1. Unique Constraints:
    • KnowledgeCheck.share_key - Ensures unique sharing tokens
    • (Category.knowledgecheckId, Category.name) - Prevents duplicate categories
  2. Foreign Key Constraints: All relationships enforced at database level
  3. NOT NULL Constraints: Required fields enforced
  4. Enum Constraints: Type-safe values for status fields

​
Validation Layers

  1. Database Level: Constraints and foreign keys
  2. ORM Level: Drizzle schema validation
  3. Application Level: Zod schemas (see src/schemas/)

​
Performance Considerations

​
Indexing Strategy

  • All foreign keys indexed automatically
  • Composite primary keys for junction tables
  • Unique indexes on share tokens

​
Query Optimization

  • Use Drizzle’s query builder for efficient queries
  • Leverage with clause for eager loading
  • Avoid N+1 queries with proper joins

​
Connection Pooling

  • Pool size: 10 connections
  • Development: Cached global pool
  • Production: New pool per deployment

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us