Database

Overview

Database menggunakan Neon Postgres (Serverless PostgreSQL dengan edge connectivity) dengan Drizzle ORM. Semua tables — termasuk auth tables — berada dalam satu database.

Stack Versions

Package	Version	Notes
drizzle-orm	0.45.2 (latest stable)	Neon HTTP adapter via drizzle-orm/neon-http
drizzle-kit	0.31.10 (latest stable)	Schema generation + migration tooling
Neon dialect	postgresql	dialect: "postgresql" in drizzle.config.ts

Schema Domains

Schema diorganisir dalam file terpisah di packages/db/src/schema/:

Auth Tables (managed by better-auth)

better-auth Drizzle adapter mengelola tables berikut:

Table	Purpose
user	Extended dengan role, phone, profilePictureUrl
session	Database sessions
account	OAuth accounts (Google)
verification	Email verification dan magic link tokens

Jangan modify auth tables secara manual. Gunakan better-auth APIs.

App Tables

Domain	Tables	Purpose
Orders	orders, payment_proofs	Order dari murid, bukti bayar
Lessons	lesson_sessions	Jadwal les, status, assignment
Confirmations	lesson_confirmations	Triple-confirmation tracking
Presence	presence_reports	Laporan guru setelah les
Evidence	session_evidence	Foto evidence les
Profile / KYC	profile	User profile, KTP data, KYC review
Teacher Applications	teacher_applications	Teacher application tracking

Key Relationships

user (student) → orders → lesson_sessions → lesson_confirmations
                                       ↓
                                 presence_reports
                                       ↓
                                 session_evidence

user (teacher) ───────→ lesson_sessions (assigned_teacher_id)

Conventions

• Table names: snake_case, plural (e.g., lesson_sessions, payment_proofs)
• Column names: snake_case (e.g., student_id, teacher_id, created_at)
• Foreign key columns: {table}_id (e.g., order_id, session_id)
• Timestamps: Setiap table wajib punya created_at dan updated_at
• Primary keys: text dengan UUID (gunakan uuidv7)

Important Constraints

Entity	Field	Values
orders	status	pending, paid, verified, cancelled
lesson_sessions	status	scheduled, ongoing, completed, cancelled
lesson_confirmations	type	teacher_start, student_start, teacher_end, student_end
user	role	user, student, teacher, admin, owner
teacher_applications	status	pending, approved, rejected

Migrations

• Generate: drizzle-kit generate
• Apply (local/dev): drizzle-kit push
• Apply production: drizzle-kit migrate
• JANGAN edit migration file setelah di-apply ke shared database.

Query Patterns

• Gunakan Drizzle query builder untuk semua DB operations.
• Prefer db.select().from() over raw SQL.
• Gunakan database transactions untuk operasi yang memerlukan konsistensi.
• Neon Postgres provides full transactional support dan advanced features.