Database

Overview

Database menggunakan Cloudflare D1 (SQLite-compatible) dengan Drizzle ORM. Semua tables — termasuk auth tables — berada dalam satu database.

Stack Versions

Package	Version	Notes
drizzle-orm	0.45.2 (latest stable)	D1 adapter built-in via drizzle-orm/d1
drizzle-kit	0.31.10 (latest stable)	Schema generation + migration tooling
D1 dialect	sqlite	dialect: "sqlite" 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

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	student, teacher, admin, owner

Migrations

• Generate: drizzle-kit generate
• Apply local: wrangler d1 migrations apply --local
• Apply production: wrangler d1 migrations apply
• 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.
• Batch reads untuk minimize D1 round-trips.
• D1 support limited transactions — gunakan application-level consistency checks jika perlu.