Skip to main content

03 — Database Schema

Semua tabel database ELJoy, ditulis dalam Drizzle ORM schema format (TypeScript). Setiap tabel dilengkapi penjelasan kolom dan relasi.

Enum Types​

import { pgEnum } from 'drizzle-orm/pg-core';

// Level CEFR untuk kemampuan bahasa
export const cefrLevelEnum = pgEnum('cefr_level', [
  'pre_a1', 'a1', 'a2', 'b1', 'b2', 'c1', 'c2',
]);

// Tipe assessment
export const assessmentTypeEnum = pgEnum('assessment_type', [
  'ice_breaker',         // Assessment pertama (tanpa login)
  'universal',           // Assessment lengkap (tanpa login)
  'adaptive_confirmation', // Konfirmasi setelah register
  'continuous',          // Assessment berkelanjutan (otomatis)
]);

// Status assessment session
export const assessmentStatusEnum = pgEnum('assessment_status', [
  'in_progress', 'completed', 'abandoned',
]);

// Mode speaking practice (12 mode)
export const practiceModeEnum = pgEnum('practice_mode', [
  'repeat_after_me',
  'shadowing',
  'role_play',
  'daily_life_conversation',
  'storytelling',
  'opinion_giving',
  'discussion',
  'debate',
  'problem_solving',
  'picture_description',
  'create_your_speech',
  'pronunciation_drill',
]);

// Status mission
export const missionStatusEnum = pgEnum('mission_status', [
  'available',    // Bisa dikerjakan
  'in_progress',  // Sedang dikerjakan
  'completed',    // Selesai
  'skipped',      // Dilewati user
  'expired',      // Melewati batas waktu
]);

// Status conversation session
export const conversationStatusEnum = pgEnum('conversation_status', [
  'active', 'completed', 'abandoned',
]);

// Role dalam conversation
export const messageRoleEnum = pgEnum('message_role', [
  'user', 'assistant', 'system',
]);

// Status subscription
export const subscriptionStatusEnum = pgEnum('subscription_status', [
  'active', 'cancelled', 'expired', 'past_due',
]);

// Status payment
export const paymentStatusEnum = pgEnum('payment_status', [
  'pending', 'paid', 'failed', 'refunded', 'expired',
]);

// Billing period
export const billingPeriodEnum = pgEnum('billing_period', [
  'monthly', 'yearly',
]);

// User membership tier
export const membershipTierEnum = pgEnum('membership_tier', [
  'free', 'member',
]);

// Priority level untuk missions
export const priorityEnum = pgEnum('priority_level', [
  'high', 'medium', 'low',
]);

// Difficulty level konten
export const difficultyEnum = pgEnum('difficulty_level', [
  'beginner', 'elementary', 'intermediate',
  'upper_intermediate', 'advanced', 'proficient',
]);

1. Auth & User Tables​

users​

Tabel utama user. Dikelola bersama Better Auth.

import { pgTable, text, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id:            text('id').primaryKey(),            // nanoid
  email:         text('email').notNull().unique(),
  name:          text('name').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  image:         text('image'),                       // URL avatar
  tier:          membershipTierEnum('tier').default('free').notNull(),

  // Metadata
  nativeLanguage: text('native_language').default('id'), // ISO 639-1
  timezone:       text('timezone').default('Asia/Jakarta'),
  onboardingCompleted: boolean('onboarding_completed').default(false).notNull(),

  // Timestamps
  createdAt:     timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:     timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  deletedAt:     timestamp('deleted_at', { withTimezone: true }),
});

sessions (Auth)​

Session login. Dikelola oleh Better Auth.

export const sessions = pgTable('sessions', {
  id:        text('id').primaryKey(),
  userId:    text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  token:     text('token').notNull().unique(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

accounts (OAuth)​

Koneksi ke provider OAuth. Dikelola oleh Better Auth.

export const accounts = pgTable('accounts', {
  id:                text('id').primaryKey(),
  userId:            text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  accountId:         text('account_id').notNull(),
  providerId:        text('provider_id').notNull(),     // 'google', 'apple', 'credential'
  accessToken:       text('access_token'),
  refreshToken:      text('refresh_token'),
  accessTokenExpiresAt:  timestamp('access_token_expires_at', { withTimezone: true }),
  refreshTokenExpiresAt: timestamp('refresh_token_expires_at', { withTimezone: true }),
  scope:             text('scope'),
  password:          text('password'),                   // Hashed, only for 'credential' provider
  createdAt:         timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:         timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

verifications​

Token verifikasi (email verification, password reset). Dikelola oleh Better Auth.

export const verifications = pgTable('verifications', {
  id:         text('id').primaryKey(),
  identifier: text('identifier').notNull(),    // email address
  value:      text('value').notNull(),          // token
  expiresAt:  timestamp('expires_at', { withTimezone: true }).notNull(),
  createdAt:  timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt:  timestamp('updated_at', { withTimezone: true }).defaultNow(),
});

user_preferences​

Preferensi user (terpisah dari tabel users agar tidak membengkak).

export const userPreferences = pgTable('user_preferences', {
  id:          text('id').primaryKey(),
  userId:      text('user_id').notNull().unique()
                 .references(() => users.id, { onDelete: 'cascade' }),

  // UI Preferences
  theme:       text('theme').default('system'),         // 'light' | 'dark' | 'system'
  uiLanguage:  text('ui_language').default('id'),       // 'id' | 'en'

  // Audio Preferences
  ttsSpeed:    real('tts_speed').default(1.0),           // 0.5 - 2.0
  ttsVoice:    text('tts_voice').default('alloy'),       // OpenAI voice ID
  muteSfx:     boolean('mute_sfx').default(false),
  autoPlayTts: boolean('auto_play_tts').default(true),

  // Learning Preferences
  dailyGoalMinutes: integer('daily_goal_minutes').default(15),
  reminderTime:     text('reminder_time').default('19:00'),  // HH:mm
  reminderEnabled:  boolean('reminder_enabled').default(true),

  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

2. Assessment Tables​

learner_profiles​

Profil kemampuan bahasa Inggris user — diupdate setiap kali ada assessment atau practice.

import { jsonb, integer, real } from 'drizzle-orm/pg-core';

export const learnerProfiles = pgTable('learner_profiles', {
  id:             text('id').primaryKey(),
  userId:         text('user_id').notNull().unique()
                    .references(() => users.id, { onDelete: 'cascade' }),

  // CEFR Level
  cefrLevel:      cefrLevelEnum('cefr_level').default('pre_a1').notNull(),
  cefrConfidence: real('cefr_confidence').default(0),  // 0-1, seberapa yakin dengan level ini

  // 7 Dimensi Skill Radar (0-100)
  skillScores:    jsonb('skill_scores').notNull().default('{}'),
  // Type: {
  //   pronunciation: number,
  //   grammar: number,
  //   vocabulary: number,
  //   fluency: number,
  //   coherence: number,
  //   confidence: number,
  //   engagement: number,
  // }

  // Aggregated Stats
  totalSessions:       integer('total_sessions').default(0).notNull(),
  totalSpeakingMinutes: integer('total_speaking_minutes').default(0).notNull(),
  averageScore:        real('average_score').default(0),

  // Streak
  currentStreak:  integer('current_streak').default(0).notNull(),
  longestStreak:  integer('longest_streak').default(0).notNull(),
  lastActiveDate: text('last_active_date'),  // YYYY-MM-DD

  // XP & Level
  totalXp:        integer('total_xp').default(0).notNull(),
  currentLevel:   integer('current_level').default(1).notNull(),

  createdAt:      timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:      timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

assessment_sessions​

Setiap kali user menjalani assessment (Ice Breaker, Universal, Adaptive).

export const assessmentSessions = pgTable('assessment_sessions', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').references(() => users.id),  // Nullable untuk guest
  guestId:      text('guest_id'),                             // ID sementara untuk guest

  type:         assessmentTypeEnum('type').notNull(),
  status:       assessmentStatusEnum('status').default('in_progress').notNull(),

  // Results (diisi saat completed)
  cefrResult:   cefrLevelEnum('cefr_result'),
  overallScore: real('overall_score'),
  skillScores:  jsonb('skill_scores'),
  // Type: sama dengan learner_profiles.skillScores

  // Metadata
  totalQuestions:   integer('total_questions').default(0),
  answeredQuestions: integer('answered_questions').default(0),
  metadata:     jsonb('metadata'),
  // Type: { device: string, browser: string, duration_ms: number }

  startedAt:    timestamp('started_at', { withTimezone: true }).defaultNow().notNull(),
  completedAt:  timestamp('completed_at', { withTimezone: true }),
  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

assessment_answers​

Setiap jawaban dalam satu assessment session.

export const assessmentAnswers = pgTable('assessment_answers', {
  id:          text('id').primaryKey(),
  sessionId:   text('session_id').notNull()
                 .references(() => assessmentSessions.id, { onDelete: 'cascade' }),

  questionIndex: integer('question_index').notNull(),
  questionText:  text('question_text').notNull(),
  questionType:  text('question_type').notNull(),  // 'open_ended', 'repeat', 'describe'

  // User's answer
  audioUrl:    text('audio_url'),                    // URL ke file audio user
  transcript:  text('transcript'),                   // Hasil STT

  // AI Evaluation
  score:       real('score'),                        // 0-100
  evaluation:  jsonb('evaluation'),
  // Type: {
  //   pronunciation: number,
  //   grammar: number,
  //   fluency: number,
  //   feedback: string,
  //   corrections: Array<{ original: string, corrected: string, explanation: string }>,
  // }

  durationMs:  integer('duration_ms'),               // Berapa lama user bicara
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

3. Learning Journey Tables​

learning_blueprints​

Rencana belajar personal yang di-generate AI berdasarkan profil user.

export const learningBlueprints = pgTable('learning_blueprints', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').notNull()
                  .references(() => users.id, { onDelete: 'cascade' }),

  // Blueprint Data (AI Generated)
  title:        text('title').notNull(),          // "Your Path to Confident Communication"
  summary:      text('summary').notNull(),        // Ringkasan rencana belajar
  blueprintData: jsonb('blueprint_data').notNull(),
  // Type: {
  //   currentLevel: CefrLevel,
  //   targetLevel: CefrLevel,
  //   estimatedWeeks: number,
  //   focusAreas: string[],
  //   phases: Array<{
  //     name: string,
  //     description: string,
  //     durationWeeks: number,
  //     objectives: string[],
  //   }>,
  // }

  version:      integer('version').default(1).notNull(),  // Blueprint bisa di-regenerate
  isActive:     boolean('is_active').default(true).notNull(),
  generatedAt:  timestamp('generated_at', { withTimezone: true }).defaultNow().notNull(),
  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:    timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

learning_objectives​

Target belajar spesifik yang dipecah dari blueprint.

export const learningObjectives = pgTable('learning_objectives', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').notNull()
                  .references(() => users.id, { onDelete: 'cascade' }),
  blueprintId:  text('blueprint_id').notNull()
                  .references(() => learningBlueprints.id, { onDelete: 'cascade' }),

  title:        text('title').notNull(),          // "Improve Meeting Vocabulary"
  description:  text('description'),
  targetSkill:  text('target_skill').notNull(),   // 'vocabulary', 'pronunciation', etc.
  cefrTarget:   cefrLevelEnum('cefr_target'),

  // Progress (0-100)
  progress:     real('progress').default(0).notNull(),
  status:       text('status').default('not_started').notNull(),
  // 'not_started' | 'in_progress' | 'completed'

  sortOrder:    integer('sort_order').default(0).notNull(),
  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:    timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

missions​

Misi harian yang diberikan ke user.

export const missions = pgTable('missions', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').notNull()
                  .references(() => users.id, { onDelete: 'cascade' }),
  objectiveId:  text('objective_id')
                  .references(() => learningObjectives.id),

  // Mission Config
  title:        text('title').notNull(),           // "Practice Meeting Discussion"
  description:  text('description'),
  mode:         practiceModeEnum('mode').notNull(),
  priority:     priorityEnum('priority').default('medium').notNull(),

  // Content Link
  contentId:    text('content_id')
                  .references(() => speakingContents.id),
  difficulty:   difficultyEnum('difficulty').notNull(),

  // Status & Result
  status:       missionStatusEnum('status').default('available').notNull(),
  score:        real('score'),                     // 0-100
  xpEarned:     integer('xp_earned').default(0),

  // Scheduling
  scheduledDate: text('scheduled_date').notNull(), // YYYY-MM-DD
  completedAt:   timestamp('completed_at', { withTimezone: true }),

  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:    timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

4. Conversation Tables​

conversation_sessions​

Setiap sesi percakapan/latihan speaking dengan AI.

export const conversationSessions = pgTable('conversation_sessions', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').notNull()
                  .references(() => users.id, { onDelete: 'cascade' }),
  missionId:    text('mission_id')
                  .references(() => missions.id),

  // Session Config
  mode:         practiceModeEnum('mode').notNull(),
  topicTitle:   text('topic_title').notNull(),
  contentId:    text('content_id')
                  .references(() => speakingContents.id),
  difficulty:   difficultyEnum('difficulty').notNull(),

  // Status
  status:       conversationStatusEnum('status').default('active').notNull(),
  turnCount:    integer('turn_count').default(0).notNull(),
  durationMs:   integer('duration_ms').default(0),

  // Final Evaluation (diisi saat session selesai)
  overallScore: real('overall_score'),
  evaluation:   jsonb('evaluation'),
  // Type: {
  //   pronunciation: number,
  //   grammar: number,
  //   fluency: number,
  //   vocabulary: number,
  //   coherence: number,
  //   strengths: string[],
  //   improvements: string[],
  //   detailedFeedback: string,
  // }

  startedAt:    timestamp('started_at', { withTimezone: true }).defaultNow().notNull(),
  completedAt:  timestamp('completed_at', { withTimezone: true }),
  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

conversation_messages​

Setiap pesan dalam percakapan (user atau AI).

export const conversationMessages = pgTable('conversation_messages', {
  id:          text('id').primaryKey(),
  sessionId:   text('session_id').notNull()
                 .references(() => conversationSessions.id, { onDelete: 'cascade' }),

  role:        messageRoleEnum('role').notNull(),   // 'user' | 'assistant' | 'system'
  content:     text('content').notNull(),            // Teks pesan

  // Audio (opsional)
  audioUrl:    text('audio_url'),                    // URL audio (user recording / AI TTS)
  audioDurationMs: integer('audio_duration_ms'),

  // Untuk pesan user: hasil evaluasi AI
  evaluation:  jsonb('evaluation'),
  // Type: {
  //   pronunciation: number | null,
  //   corrections: Array<{ word: string, issue: string, suggestion: string }>,
  //   feedback: string | null,
  // }

  turnIndex:   integer('turn_index').notNull(),      // Urutan dalam percakapan
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

5. Content Tables​

content_domains​

Domain konten level tertinggi.

export const contentDomains = pgTable('content_domains', {
  id:          text('id').primaryKey(),
  name:        text('name').notNull().unique(),       // "Business English"
  nameId:      text('name_id').notNull(),              // "Bahasa Inggris Bisnis" (Indonesian)
  description: text('description'),
  icon:        text('icon'),                           // Emoji atau URL icon
  sortOrder:   integer('sort_order').default(0).notNull(),
  isActive:    boolean('is_active').default(true).notNull(),
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

content_topics​

Topik di dalam setiap domain.

export const contentTopics = pgTable('content_topics', {
  id:          text('id').primaryKey(),
  domainId:    text('domain_id').notNull()
                 .references(() => contentDomains.id, { onDelete: 'cascade' }),

  name:        text('name').notNull(),                 // "Meeting & Discussion"
  nameId:      text('name_id').notNull(),               // Indonesian translation
  description: text('description'),

  // CEFR eligibility range
  cefrMin:     cefrLevelEnum('cefr_min').default('a1').notNull(),
  cefrMax:     cefrLevelEnum('cefr_max').default('c2').notNull(),

  sortOrder:   integer('sort_order').default(0).notNull(),
  isActive:    boolean('is_active').default(true).notNull(),
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

speaking_contents​

Konten latihan speaking yang actual.

export const speakingContents = pgTable('speaking_contents', {
  id:          text('id').primaryKey(),
  topicId:     text('topic_id').notNull()
                 .references(() => contentTopics.id, { onDelete: 'cascade' }),

  title:       text('title').notNull(),                // "Weekly Team Standup"
  titleId:     text('title_id'),                        // Indonesian translation
  description: text('description'),

  // Content Type & Mode
  mode:        practiceModeEnum('mode').notNull(),
  difficulty:  difficultyEnum('difficulty').notNull(),

  // Stimulus Data (berbeda per mode)
  stimulusData: jsonb('stimulus_data').notNull(),
  // Type varies by mode:
  //
  // repeat_after_me / shadowing:
  //   { sentences: Array<{ text: string, audioUrl: string }> }
  //
  // role_play / daily_life_conversation:
  //   { scenario: string, aiRole: string, userRole: string, context: string }
  //
  // storytelling / opinion_giving:
  //   { prompt: string, guidingQuestions: string[], prepTimeSeconds: number }
  //
  // picture_description:
  //   { imageUrl: string, instruction: string }
  //
  // debate:
  //   { topic: string, userPosition: string, aiPosition: string }
  //
  // problem_solving:
  //   { scenario: string, problem: string, constraints: string[] }

  // CEFR eligibility
  cefrMin:     cefrLevelEnum('cefr_min').default('a1').notNull(),
  cefrMax:     cefrLevelEnum('cefr_max').default('c2').notNull(),

  // Stats
  timesUsed:   integer('times_used').default(0).notNull(),
  avgScore:    real('avg_score'),

  isActive:    boolean('is_active').default(true).notNull(),
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  deletedAt:   timestamp('deleted_at', { withTimezone: true }),
});

6. Membership & Payment Tables​

subscription_plans​

Paket berlangganan yang tersedia.

export const subscriptionPlans = pgTable('subscription_plans', {
  id:          text('id').primaryKey(),
  name:        text('name').notNull(),                 // "Monthly", "Yearly"
  description: text('description'),
  period:      billingPeriodEnum('period').notNull(),
  priceIdr:    integer('price_idr').notNull(),         // Harga dalam Rupiah
  originalPriceIdr: integer('original_price_idr'),     // Harga coret (untuk diskon)

  // Features included
  features:    jsonb('features').notNull(),
  // Type: {
  //   unlimitedAssessment: boolean,
  //   unlimitedPractice: boolean,
  //   personalBlueprint: boolean,
  //   dailyMissions: boolean,
  //   progressTracking: boolean,
  //   prioritySupport: boolean,
  // }

  isActive:    boolean('is_active').default(true).notNull(),
  isPopular:   boolean('is_popular').default(false).notNull(),  // Badge "Paling Populer"
  sortOrder:   integer('sort_order').default(0).notNull(),
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

subscriptions​

Langganan aktif user.

export const subscriptions = pgTable('subscriptions', {
  id:          text('id').primaryKey(),
  userId:      text('user_id').notNull()
                 .references(() => users.id, { onDelete: 'cascade' }),
  planId:      text('plan_id').notNull()
                 .references(() => subscriptionPlans.id),

  status:      subscriptionStatusEnum('status').default('active').notNull(),

  startsAt:    timestamp('starts_at', { withTimezone: true }).notNull(),
  expiresAt:   timestamp('expires_at', { withTimezone: true }).notNull(),
  cancelledAt: timestamp('cancelled_at', { withTimezone: true }),
  cancelReason: text('cancel_reason'),

  // Auto-renew
  autoRenew:   boolean('auto_renew').default(true).notNull(),

  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  deletedAt:   timestamp('deleted_at', { withTimezone: true }),
});

payments​

Riwayat pembayaran.

export const payments = pgTable('payments', {
  id:           text('id').primaryKey(),
  userId:       text('user_id').notNull()
                  .references(() => users.id, { onDelete: 'cascade' }),
  subscriptionId: text('subscription_id')
                  .references(() => subscriptions.id),

  // Amount
  amount:       integer('amount').notNull(),          // Dalam Rupiah
  currency:     text('currency').default('IDR').notNull(),

  // Payment Gateway
  gatewayName:  text('gateway_name').notNull(),       // 'midtrans'
  gatewayRef:   text('gateway_ref'),                   // Transaction ID dari gateway
  gatewayData:  jsonb('gateway_data'),                 // Raw response dari gateway
  paymentMethod: text('payment_method'),               // 'gopay', 'bca_va', 'credit_card'

  status:       paymentStatusEnum('status').default('pending').notNull(),
  paidAt:       timestamp('paid_at', { withTimezone: true }),
  expiresAt:    timestamp('expires_at', { withTimezone: true }),

  createdAt:    timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt:    timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

7. Admin & System Tables​

audit_logs​

Log semua aksi penting di sistem (untuk admin tracking).

export const auditLogs = pgTable('audit_logs', {
  id:          text('id').primaryKey(),
  actorId:     text('actor_id'),                       // User atau admin yang melakukan aksi
  actorType:   text('actor_type').notNull(),            // 'user' | 'admin' | 'system'

  action:      text('action').notNull(),                // 'user.register', 'content.create', 'payment.received'
  resource:    text('resource').notNull(),               // 'user', 'content', 'payment'
  resourceId:  text('resource_id'),

  details:     jsonb('details'),                        // Data tambahan tentang aksi
  ipAddress:   text('ip_address'),

  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

system_configs​

Konfigurasi sistem yang bisa diubah tanpa deploy.

export const systemConfigs = pgTable('system_configs', {
  key:         text('key').primaryKey(),                // 'max_daily_missions', 'free_assessment_limit'
  value:       jsonb('value').notNull(),
  description: text('description'),
  updatedBy:   text('updated_by'),
  updatedAt:   timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

Ringkasan Tabel​

#TabelJumlah KolomRelasi Utama
1users11→ sessions, accounts, learner_profiles
2sessions7→ users
3accounts11→ users
4verifications5-
5user_preferences12→ users
6learner_profiles14→ users
7assessment_sessions12→ users
8assessment_answers10→ assessment_sessions
9learning_blueprints9→ users
10learning_objectives10→ users, learning_blueprints
11missions14→ users, learning_objectives, speaking_contents
12conversation_sessions13→ users, missions, speaking_contents
13conversation_messages9→ conversation_sessions
14content_domains8-
15content_topics9→ content_domains
16speaking_contents14→ content_topics
17subscription_plans10-
18subscriptions10→ users, subscription_plans
19payments13→ users, subscriptions
20audit_logs8-
21system_configs4-

Total: 21 tabel