03 — Database Design

Dokumen ini menjelaskan strategi database, konvensi penamaan, dan keputusan desain untuk ELJoy.

---

Pemilihan Database

PostgreSQL sebagai Primary Database

Kriteria	Keputusan
Tipe	Relational (RDBMS)
Engine	PostgreSQL 16+
Hosting	Neon (serverless) / Supabase / Railway
ORM	Drizzle ORM
Migration	Drizzle Kit (auto-generate dari schema)

Kenapa 1 database saja (bukan polyglot)?

Untuk tahap MVP dan early growth, satu PostgreSQL sudah cukup menangani semua kebutuhan:

• Relational data → tabel biasa
• Semi-structured data → kolom JSONB (evaluation results, AI metrics)
• Full-text search → PostgreSQL tsvector (pencarian konten)
• Time-series → tabel biasa dengan index pada timestamp
• Queue → Dihandle Redis + BullMQ (bukan DB)

Menambah database lain (MongoDB, Elasticsearch, dll) hanya akan menambah kompleksitas operasional tanpa manfaat yang sebanding di tahap ini.

---

Konvensi Penamaan

Elemen	Konvensi	Contoh
Tabel	snake_case, plural	users, assessment_sessions, speaking_contents
Kolom	snake_case	created_at, cefr_level, is_active
Primary Key	id (tipe text, menggunakan nanoid/cuid)	id: text('id').primaryKey()
Foreign Key	{tabel_singular}_id	user_id, session_id, content_id
Boolean	Prefix is_ atau has_	is_active, is_premium, has_completed
Timestamp	Suffix _at	created_at, updated_at, completed_at
Enum	UPPERCASE, tipe PostgreSQL enum	CEFR_LEVEL, ASSESSMENT_TYPE
Index	idx_{tabel}_{kolom}	idx_users_email, idx_messages_session_id
Unique	unq_{tabel}_{kolom}	unq_users_email

---

Primary Key Strategy

Menggunakan nanoid (21 karakter) sebagai primary key, bukan auto-increment integer atau UUID:

Kriteria	Auto-increment	UUID v4	nanoid ✅
Ukuran	4-8 bytes	36 chars	21 chars
URL-safe	❌ (predictable)	⚠️ (panjang)	✅
Collision-safe	✅	✅	✅
Index performance	Terbaik	Buruk (random)	Bagus
Security	❌ (enumerable)	✅	✅

import { nanoid } from 'nanoid';

// Generate ID: "V1StGXR8_Z5jdHi6B-myT"
const id = nanoid(); // 21 chars, URL-safe, collision-resistant

---

Timestamp Strategy

Semua tabel wajib punya created_at dan updated_at:

// Shared timestamp columns
const timestamps = {
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
};

---

Soft Delete Strategy

Untuk data yang perlu dipertahankan (audit trail), gunakan soft delete:

// Soft delete column
const softDelete = {
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
};

// Query: hanya ambil yang belum dihapus
const activeUsers = await db.query.users.findMany({
  where: isNull(users.deletedAt),
});

Tabel yang menggunakan soft delete:

• users — Akun yang dihapus masih perlu untuk audit
• subscriptions — Riwayat langganan
• speaking_contents — Konten yang diarsipkan

Tabel yang menggunakan hard delete:

• sessions (auth) — Expired sessions langsung dihapus
• audio_files (temporary) — File audio sementara

---

JSONB Usage Strategy

Beberapa data bersifat semi-structured dan akan berubah formatnya seiring evolusi produk. Untuk ini, gunakan kolom JSONB:

Kolom	Tabel	Contoh Data
skill_scores	learner_profiles	{ pronunciation: 72, grammar: 65, ... }
evaluation_result	conversation_messages	{ corrections: [...], suggestions: [...] }
blueprint_data	learning_blueprints	{ phases: [...], milestones: [...] }
metadata	assessment_sessions	{ device: "mobile", duration_ms: 45000 }
preferences	user_preferences	{ theme: "dark", tts_speed: 1.0 }

Aturan JSONB:

1. Pakai JSONB hanya untuk data yang tidak perlu di-query secara relational
2. Selalu definisikan TypeScript type/Zod schema untuk validasi
3. Jangan simpan data yang perlu di-JOIN di JSONB
4. Index JSONB path yang sering di-query: CREATE INDEX ON table USING gin (column jsonb_path_ops)

---

Migration Workflow

# 1. Edit schema file (e.g., db/schema/users.schema.ts)

# 2. Generate migration SQL
pnpm drizzle-kit generate

# 3. Review generated SQL di folder db/migrations/

# 4. Apply migration ke database
pnpm drizzle-kit migrate

# 5. (Opsional) Push langsung tanpa file migration (development only)
pnpm drizzle-kit push

Aturan Migration:

• ❌ Jangan edit file migration yang sudah dijalankan di production
• ✅ Selalu review generated SQL sebelum apply
• ✅ Migration harus reversible (bisa rollback)
• ✅ Satu migration = satu perubahan logis

---

Entity Relationship Overview

┌──────────┐     ┌──────────────┐     ┌───────────────────┐
│  users   │────►│  sessions    │     │  accounts (OAuth)  │
│          │     │  (auth)      │     │                    │
│          │────►│              │     │                    │
└────┬─────┘     └──────────────┘     └────────────────────┘
     │
     │ 1:1
     ▼
┌──────────────────┐
│ learner_profiles  │
│ (CEFR, scores)   │
└──────┬───────────┘
       │
       │ 1:N
       ▼
┌──────────────────┐     ┌──────────────────┐
│ assessment_       │     │ learning_         │
│ sessions          │     │ blueprints        │
│                   │     │                   │
│ • ice_breaker     │     │ • phases          │
│ • universal       │     │ • milestones      │
│ • adaptive        │     │                   │
└──────┬───────────┘     └──────┬───────────┘
       │                        │
       │ 1:N                    │ 1:N
       ▼                        ▼
┌──────────────────┐     ┌──────────────────┐
│ assessment_       │     │ learning_         │
│ answers           │     │ objectives        │
│                   │     │                   │
│ • transcript      │     │ • target_skill    │
│ • score           │     │ • cefr_target     │
│ • evaluation      │     │ • progress %      │
└──────────────────┘     └──────┬───────────┘
                                │
                                │ 1:N
                                ▼
                         ┌──────────────────┐
                         │ missions          │
                         │                   │
                         │ • mode            │
                         │ • topic           │
                         │ • status          │
                         │ • score           │
                         └──────┬───────────┘
                                │
                                │ 1:1
                                ▼
                         ┌──────────────────┐      ┌──────────────┐
                         │ conversation_     │      │ speaking_    │
                         │ sessions          │─────►│ contents     │
                         │                   │      │              │
                         │ • mode            │      │ • domain     │
                         │ • status          │      │ • topic      │
                         │ • duration        │      │ • difficulty │
                         └──────┬───────────┘      └──────────────┘
                                │
                                │ 1:N
                                ▼
                         ┌──────────────────┐
                         │ conversation_     │
                         │ messages          │
                         │                   │
                         │ • role (user/ai)  │
                         │ • text            │
                         │ • audio_url       │
                         │ • evaluation      │
                         └──────────────────┘

┌──────────────────┐     ┌──────────────────┐
│ subscription_     │     │ payments          │
│ plans             │     │                   │
│                   │     │ • amount          │
│ • name            │     │ • status          │
│ • price           │     │ • gateway_ref     │
│ • features        │     │                   │
└──────┬───────────┘     └──────────────────┘
       │
       │ 1:N
       ▼
┌──────────────────┐
│ subscriptions     │
│                   │
│ • user_id         │
│ • plan_id         │
│ • status          │
│ • expires_at      │
└──────────────────┘

┌──────────────────┐     ┌──────────────────┐
│ content_domains   │────►│ content_topics   │
│                   │     │                   │
│ • name            │     │ • name            │
│ • icon            │     │ • cefr_range      │
└──────────────────┘     └──────┬───────────┘
                                │
                                │ 1:N
                                ▼
                         ┌──────────────────┐
                         │ speaking_contents │
                         │                   │
                         │ • title           │
                         │ • mode_type       │
                         │ • difficulty      │
                         │ • stimulus_data   │
                         └──────────────────┘

---

Index Strategy

Tabel	Index	Tipe	Alasan
users	email	UNIQUE	Login lookup
users	deleted_at	BTREE	Soft delete filter
learner_profiles	user_id	UNIQUE	1:1 relation
assessment_sessions	user_id, type	BTREE	Filter by user + type
missions	user_id, status, date	BTREE	Daily missions query
conversation_sessions	user_id, created_at	BTREE	History pagination
conversation_messages	session_id, created_at	BTREE	Message ordering
subscriptions	user_id, status	BTREE	Active subscription check
speaking_contents	topic_id, difficulty	BTREE	Content filtering
speaking_contents	search_vector	GIN	Full-text search