Architecture

XPrivate Education — Architecture (Step 2)

Status: ✅ DONE & SIGNED OFF 2026-05-26 — All schema decisions + architecture locked (2.1–2.20 done).

Last updated: 2026-05-26 Session resume: See plans/RESUME.md §Step 2 for sub-task checklist.

1. Monorepo Structure

Package manager + builder + monorepo manager: Bun (NOT runtime) Runtime: Wrangler (Cloudflare Workers)

/
├── apps/                          # Deployment targets (@apps/*)
│   ├── web/                       # TanStack Start (React SSR + frontend) + oRPC API
│   └── cron-<task-name>/          # CF Cron Workers (one per distinct cron job)
│
├── packages/                      # Shared libraries (@packages/*)
│   ├── db/                        # Drizzle schema + migrations + client
│   ├── service/                   # Business logic (auth-agnostic)
│   ├── contract/                  # oRPC contract + Zod base I/O schemas
│   └── auth/                      # Shared auth utilities: RBAC resolution, WhoAmI builder
│
├── bun.lock
├── package.json                   # Workspace root
├── CLAUDE.md                      # Root Claude doc (Step 6)
└── .claude/
    ├── rules/                     # Claude rules (Step 6)
    └── agents/                    # Sonnet worker subagent (Step 7)

Key invariants:

@packages/service is auth-agnostic — receives resolved, pre-authenticated input. Never checks auth.
@packages/auth holds RBAC resolution + permission middleware — shared across @apps/web and any future consumer.
@packages/db holds the single source of truth for all Drizzle schemas.
@packages/contract holds the oRPC contract + Zod base I/O schemas. It is a leaf package (depends only on zod and @orpc/contract; no internal package deps).
Business logic lives in @packages/service, not in @apps/web route handlers.
Cron jobs (@apps/cron-*) call @packages/service directly (trusted internal context).

2. Frontend — `@apps/web` (UI)

Decision	Value
Framework	TanStack Start (React)
Deployment	CF Workers via Nitro adapter (Wrangler)
Capacitor (iter 2)	SPA mode build — no backend changes required
Code splitting	TanStack Start built-in (follow framework defaults, don’t fight it)
Styling	shadcn/ui + Tailwind CSS + Radix UI primitives
Animation	`motion` (Framer Motion v11+) — subtle slide-in / fade / scale
Charts	shadcn/ui charts module (Recharts under the hood)
Data table	`@tanstack/react-table` — headless, paired with shadcn `<Table>`
Forms	`@tanstack/react-form` + Zod adapter
Toast notifications	`sonner`
Server state	`@tanstack/react-query`
Icons	`lucide-react`
Markdown rendering	`react-markdown` + `remark-gfm` + `rehype-sanitize`

See §12 Frontend UI Conventions for full library list, animation guidelines, stability patterns, testid mandate, and UI tone.

Capacitor-ready design rule:

All backend logic must be accessible via explicit oRPC/REST API routes (not only createServerFn)
createServerFn used for SSR data loading only (web context)
Mobile calls same oRPC/REST API routes via absolute URL: https://dashboard.xprivate.education/api/...
Capacitor.isNativePlatform() used client-side to switch base URL if needed

Auth interaction (frontend flow):

User hits website → FE calls WhoAmI before rendering any protected UI
WhoAmI returns 401 → redirect to / (login page)
WhoAmI returns 200 → render dashboard based on resolved permissions
No UI render while WhoAmI is pending (no flash before redirect)
WhoAmI response stays in app memory for lifecycle; refresh on explicit logout/session update

3. API Layer — `@apps/web` (Server)

Architecture: Dual-mode server

TanStack Start server (Nitro / CF Workers)
├── Frontend routes  (/, /dashboard/*, etc.)   → TanStack Start router + SSR
├── Server functions (__server/*)              → createServerFn (SSR data loading, web only)
└── /api/*                                     → oRPC OpenAPIHandler (external clients)
      ├── /api/auth/*                           → Better Auth handler
      ├── /api/v1/...                           → oRPC domain routes (contract-first)
      ├── /api/docs                             → Swagger UI (generated from oRPC contract)
      └── /api/openapi.json                     → Generated OpenAPI 3.x spec

Dispatch lives in apps/web/src/routes/api/$.ts: /api/auth/* → Better Auth, /v1/traces → OTEL proxy, everything else → OpenAPIHandler (oRPC).

Decision	Value
API router	oRPC (`@orpc/server` + `@orpc/openapi`)
Contract	`@packages/contract` — oRPC `oc` procedures with `.route()`, contract-first
OpenAPI	`@orpc/openapi` `OpenAPIHandler` — auto-generated from contract definitions
Routing library	oRPC for `/api/*`; TanStack Start for frontend routes
Validation	Zod (base schemas in `@packages/contract/src/service/<domain>/`)

API versioning: /api/v1/ prefix baked into contract paths. Future: /api/v2/ additive. Contract paths are fully qualified — do NOT pass prefix to OpenAPIHandler.

WhoAmI Endpoint — `/api/v1/whoami`

// Response shape (200 OK)
{
  user_id: string,       // uuidv7
  name: string,
  avatar_url: string | null,
  roles: string[],       // role names for UI display only (not for permission logic)
  permissions: {         // resolved permission map — only granted permissions included
    [key: string]: true
  },
  superuser: boolean     // Better Auth admin plugin "blessed user" flag
}
// 401 Unauthorized — no session

4. Database — `@packages/db`

Decision	Value
Provider	Neon Postgres 18
Connection (production)	CF Hyperdrive (Neon + Hyperdrive binding in Wrangler)
Connection (dev)	Direct Neon serverless HTTP driver
ORM	Drizzle ORM
Migration tool	Drizzle Kit
Schema location	`/packages/db/schema/`
Migrations location	`/packages/db/migrations/`
Client export	`/packages/db/client.ts`

Naming Conventions

Target	Convention
Drizzle schema fields	`snake_case` (matches DB columns)
Zod I/O schemas (service layer)	`snake_case`
DTOs (all layers)	`snake_case`
Table names	`snake_case`, plural (e.g., `users`, `lesson_sessions`)

ID & Timestamp Standards

Field type	Standard
All primary keys	`uuid` type, default `uuidv7()` (Postgres 18 native)
All timestamps	`timestamptz` (`TIMESTAMP WITH TIME ZONE`)
All duration fields	`integer` or `bigint` with `_ms` suffix (millisecond precision)

Drizzle Relations

All tables that have FK relationships must have Drizzle relations() defined. Service layer listing queries should prefer ORM-style (using db.query.*) over raw SQL for readability.

Extensions (First Migration — `0000_enable_extensions.sql`)

CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- fuzzy string search
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- schedule overlap GiST indexes
CREATE EXTENSION IF NOT EXISTS unaccent;     -- text normalization (pre-tsvector)
CREATE EXTENSION IF NOT EXISTS rum;          -- compound tsvector + filter queries

Verify at Step 4: Confirm rum extension availability on Neon Postgres 18. Fallback if unavailable: GIN index + materialized partial index per entity type.

Index Patterns

Fuzzy search (pg_trgm GIN): Fields: username, email, phone, ktp_number, transaction_id, transaction_no, lesson_session_id, session_no

CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);

Full-text search (tsvector — presensi/progress reports): Use simple or english dictionary (NOT Indonesian tokenizer, for bilingual future support). Store as generated tsvector column. Use rum index for compound queries with entity filters (e.g., student_id).

-- Example: compound rum index for progress report full-text + student filter
CREATE INDEX idx_progress_reports_fts ON progress_reports
  USING rum (content_tsvector rum_tsvector_ops, student_id);

Schedule overlap (btree_gist + tstzrange): Use Postgres 18 WITHOUT OVERLAPS temporal constraint for DB-level double-booking prevention.

-- Example: prevent overlapping tutor schedules
ALTER TABLE lesson_sessions ADD CONSTRAINT no_tutor_overlap
  EXCLUDE USING GIST (tutor_id WITH =, tstzrange(start_at, end_at) WITH &&);

5. Object Storage — Cloudflare R2

Decision	Value
Provider	Cloudflare R2
Access mode	S3-compatible API (presigned URLs — NOT via Worker proxy)
Upload flow	Client → `/api/v1/upload-url` → R2 presigned PUT URL → client direct upload → `/api/v1/upload-confirm` → DB update

Why S3 mode: Minimizes Worker bandwidth. Worker only handles URL generation and DB record management, not file bytes.

Use cases: KYC dokumen (foto KTP), kontrak PDF, bukti bayar, foto laporan presensi.

R2 bucket bindings in wrangler.jsonc:

{
  "r2_buckets": [
    { "binding": "DOCUMENTS", "bucket_name": "xprivate-documents" }
  ]
}

6. Observability — SigNoz

Decision	Value
Backend	SigNoz (OTEL-compatible)
Instrumentation library	`@tigorhutasuhut/telemetry-js` — cloudflare module
Config (env vars)	`OTEL_EXPORTER_OTLP_ENDPOINT`, `OTEL_SERVICE_NAME`, resource attributes
Scope	All `@apps/*` (web + cron jobs)

7. Auth — `@packages/auth` + Better Auth

Foundation

Decision	Value
Auth library	Better Auth
Plugins	Admin plugin (blessed user), Invite (custom flow), Email/Password
OAuth providers	Google OAuth (active iter 1)
Session management	Better Auth built-in (cookie-based)
DB adapter	Better Auth Drizzle adapter (Neon)
`superuser` field	`additionalFields` boolean on user schema in Better Auth config

Provisioning flow: Admin pre-creates users via Better Auth Admin plugin (admin.createUser) → optional manual “Send Welcome Email” trigger (via A2 notification system, DB-ready iter 1). New admin completes signin via Google OAuth (prod: disableImplicitSignUp on, existing-user-only; dev: self-provision allowed). First-ever admin is bootstrapped by email allowlist (BOOTSTRAP_ADMIN_EMAILS → promote-on-create, see §11.x), not a seeded password row; tutor/siswa/parent iter 2+ via forgotPassword (set initial password) OR Magic Link plugin (passwordless). No Organization plugin (overhead too large). See §11 for full detail.

Admin plugin superuser field: Added via additionalFields, not the plugin’s internal role field. WhoAmI exposes superuser: boolean. The admin plugin’s internal role is used for detecting blessed users only.

Custom RBAC (Roll Your Own — in `@packages/auth`)

Permission definition (in codebase — NOT DB-driven):

export const PERMISSIONS = {
  STUDENT_READ: 'student:read',
  STUDENT_WRITE: 'student:write',
  INVOICE_EXPORT: 'invoice:export',
  // ... all permissions exhaustively listed
} as const;

export type Permission = typeof PERMISSIONS[keyof typeof PERMISSIONS];

Resolution algorithm:

1. Fetch all roles for user
2. Union all permissions from each role
3. Dedupe
4. Apply negation ACL:
   - Negation entry format: "!permission:name"
   - For each negation: remove matching positive permission + remove the negation entry itself
5. Result: flat list of granted permissions only

DB Schema (RBAC tables in /packages/db):

roles          → id, name, description, created_at, updated_at
permissions    → id, key (string, unique), description  [seed only, not runtime-editable]
role_permissions → role_id, permission_key, is_negation (bool)
user_roles     → user_id, role_id, assigned_by, assigned_at

Resolved permission output (sent to client):

{ "student:read": true, "invoice:export": true }

Only granted permissions are included. false values omitted.

Server-side guard pattern (in @packages/auth):

// Server-side permission guard (used by oRPC middleware)
export function requirePermission(permission: Permission) {
  return createMiddleware(async (c, next) => {
    const resolved = c.get('resolvedPermissions');
    if (c.get('superuser') || resolved[permission]) return next();
    return c.json({ error: 'Forbidden' }, 403);
  });
}

Multi-role: 1 user can have N roles. Roles are cumulative + negation. No role hierarchy.

8. Partner-Authority Items (Tooling)

Concern	Decision
ORM	Drizzle ORM
API routing	oRPC (`@orpc/server` + `@orpc/openapi`) for `/api/*`; TanStack Start for frontend
Validation	Zod (base I/O schemas in `@packages/contract`, composed in oRPC procedures)
Monorepo	Bun workspaces
Unit tests	Vitest (automated, in CI)
Integration tests	Vitest — manual only for MVP (no CI automation yet)
E2E tests	Playwright
CI/CD	Cloudflare Workers CI (built-in)
i18n	Paraglide JS (type-safe, Vite-native, tree-shakeable; BI iter 1, EN iter 2)

Integration Test Strategy

MVP: Manual only. Run locally on demand, not part of CI pipeline.

Future (post-MVP) flow — Neon branch-based:

1. Create Neon branch from production branch
2. Apply pending migrations to the branch
3. Run Vitest integration test suite against the branch
4. Delete branch (cleanup)

This ensures integration tests run against a real Postgres schema (including extensions, constraints, triggers) without touching production data. Neon branching is near-instant (copy-on-write), so this is practical even in CI.

CI pipeline (MVP) only runs:

Type check (tsc --noEmit)
Lint
Unit tests (Vitest, no DB)
Build

9. Pending — Schema Definitions (Sub-tasks 2.10–2.19)

These are to be defined in the next session. Each schema section will be appended here.

10. Open Verification Items (resolve at Step 4)

Item	Risk	Fallback
`rum` extension on Neon Postgres 18	Medium — not all extensions available on Neon	GIN + materialized partial index per entity
Better Auth + TanStack Start + CF Workers timeout issue (reported Oct 2025)	Low — likely resolved in May 2026	File GitHub issue + workaround: async boundary fix
oRPC mounted in Nitro (TanStack Start) — integration stability	Low — documented pattern	✅ RESOLVED: oRPC `OpenAPIHandler` dispatched via file route `src/routes/api/$.ts` using `createFileRoute('/api/$')`. Better Auth and OTEL proxy handled before oRPC. Contract paths fully qualified — no `prefix` option needed.
Paraglide JS + TanStack Start routing integration	Low	i18next as fallback

11. Auth Schema (Step 2.10)

Locked design for the auth/identity layer. Lean tables on hot path; profile/KYC data is excluded from this section (separate sub-task — tutor_profiles, student_profiles, parent_profiles, documents).

Design principles:

Stay close to Better Auth defaults — codebase orbits Better Auth as the foundation. Custom built only where Better Auth doesn’t fit (= RBAC, login audit).

Lean auth, separate profile/KYC — auth tables = hot-path queries (WhoAmI, permission resolve). Profile/KYC = cold-path + privacy-limited.

DB-ready, code-flip iter 2 — multi-IdP, 2FA, magic link, full state machine all schema-present iter 1; activate via code change iter 2+ without migration.

Better Auth CLI schema workflow — map names in the BA config first, then auth generate, then postfix to project DB standards (uuidv7(), timestamptz, pg_trgm indexes). Full procedure in .claude/rules/schema.md.

11.1 Table Inventory (9 tables)

#	Table	Schema owner	Iter 1 status	Purpose
1	`users`	Better Auth core + Admin plugin + 2FA plugin + `additionalFields`	✅ Active	Core identity (lean — no profile/KYC)
2	`sessions`	Better Auth core + Admin plugin (`impersonatedBy`)	✅ Active	Active sessions
3	`accounts`	Better Auth core	✅ Active (Google OAuth iter 1)	Identity provider links (multi-link per user)
4	`verifications`	Better Auth core	✅ Active (password reset iter 1)	Generic token store: password reset + email verification + magic link
5	`two_factor`	Better Auth 2FA plugin	⏳ DB-ready (UI gated iter 2)	TOTP secret + backup codes
6	`roles`	Custom (`@packages/db`)	✅ Active	Roles, dual-language, slug stable
7	`role_permissions`	Custom	✅ Active	Role ↔ permission key (`!` prefix for negation)
8	`user_roles`	Custom	✅ Active	User ↔ role junction
9	`login_audit_events`	Custom	✅ Active	Security event log, 2-year retention

Dropped vs initial plan:

❌ Separate identity_providers catalog — provider is accounts.provider_id string column; Better Auth handles provider config in code.
❌ Separate permissions table — codebase PERMISSIONS const is single source of truth.

Profile/KYC tables NOT in 2.10: tutor_profiles, student_profiles, parent_profiles, documents, tutor_slots, etc. — deferred to subsequent sub-task.

11.2 Better Auth Core Integration

All Better Auth model + field names overridden via config to align with project naming conventions (snake_case + plural).

import { type BetterAuthOptions, betterAuth } from 'better-auth';
import { drizzleAdapter } from 'better-auth/adapters/drizzle';
import { admin, twoFactor } from 'better-auth/plugins';
import { createAuthMiddleware } from 'better-auth/api';
import { db } from '@packages/db/client';
import * as authSchema from '@packages/db/schema/auth';
import { env } from '@/lib/env';
import { recordLoginAudit, mapBetterAuthPathToEventType } from '@packages/service/login-audit';

const config = {
  database: drizzleAdapter(db, {
    provider: 'pg',
    schema: authSchema,
    usePlural: false,                                    // we set modelName explicitly below
  }),

  user: {
    modelName: 'users',
    fields: {
      emailVerified: 'email_verified',
      image: 'avatar_url',
      createdAt: 'created_at',
      updatedAt: 'updated_at',
    },
    additionalFields: {
      user_type: { type: 'string', required: true, defaultValue: 'ADMIN', input: false },
      account_state: { type: 'string', required: true, defaultValue: 'ACTIVE', input: false },
      superuser: { type: 'boolean', required: true, defaultValue: false, input: false },
    },
  },

  session: {
    modelName: 'sessions',
    fields: {
      userId: 'user_id', expiresAt: 'expires_at',
      ipAddress: 'ip_address', userAgent: 'user_agent',
      createdAt: 'created_at', updatedAt: 'updated_at',
    },
    expiresIn: 60 * 60 * 24 * 7,                         // 7 days max (remember-me)
    updateAge: 60 * 60 * 24,                             // refresh every 24h
    cookieCache: { enabled: true, maxAge: 60 * 5 },      // 5min cookie cache
  },

  account: {
    modelName: 'accounts',
    fields: {
      userId: 'user_id', accountId: 'account_id', providerId: 'provider_id',
      accessToken: 'access_token', refreshToken: 'refresh_token',
      accessTokenExpiresAt: 'access_token_expires_at',
      refreshTokenExpiresAt: 'refresh_token_expires_at',
      idToken: 'id_token',
      createdAt: 'created_at', updatedAt: 'updated_at',
    },
    accountLinking: {
      enabled: true,
      trustedProviders: ['google'],
      allowDifferentEmails: false,                       // require same email across linked identities
      allowUnlinkingAll: false,                          // prevent self-lockout
    },
  },

  verification: {
    modelName: 'verifications',
    fields: {
      expiresAt: 'expires_at',
      createdAt: 'created_at', updatedAt: 'updated_at',
    },
  },

  emailAndPassword: {
    enabled: true,                                       // backup password reset path
    disableSignUp: true,                                 // ❗ admin pre-creates users only
    requireEmailVerification: false,                     // iter 1; flip true iter 2
    sendResetPassword: async ({ user, url }) => {
      await notificationService.sendPasswordResetEmail(user.email, url);
    },
    resetPasswordTokenExpiresIn: 60 * 60,                // 1h (scope F requirement)
  },

  // ── Google OAuth — DEFERRED (not wired in iter-1 dev yet) ──────────────────
  // Target config below. NOT enabled in code today; admin auth in dev is the
  // credential seed + the bootstrap-email promote hook. Wire this only after the
  // security checklist below is satisfied.
  socialProviders: {
    google: {
      clientId: env.GOOGLE_CLIENT_ID,
      clientSecret: env.GOOGLE_CLIENT_SECRET,
      disableImplicitSignUp: true,                         // see ⚠️ — NOT sufficient alone
      scope: ['email', 'profile'],
      mapProfileToUser: (profile) => {
        if (profile.hd !== 'xprivate.education') {
          throw new Error('Domain not allowed. Use your @xprivate.education account.');
        }
        return { name: profile.name, image: profile.picture };
      },
    },
  },

  // ⚠️ SECURITY CHECKLIST — REQUIRED before enabling Google sign-in (Opus review
  // 2026-06-09, BA 1.7.0-beta.3). Do NOT ship Google OAuth until all four hold:
  //   1. `disableImplicitSignUp` is CLIENT-BYPASSABLE — the `/sign-in/social`
  //      body accepts `requestSignUp:true` (gate is `disableImplicitSignUp &&
  //      !requestSignUp`). It is NOT a security boundary. The real prod gate must
  //      live in `databaseHooks.user.create.before` (deny-by-default; allow only
  //      admin-initiated creates by inspecting the hook ctx path).
  //   2. The promote hook fires on EVERY create path incl. admin-plugin
  //      `createUser`. With `defaultRole:'admin'`, any admin holding `user:create`
  //      could mint a superadmin via an allowlisted email → admin→superadmin
  //      escalation. Once multi-admin prod is live, gate promotion (e.g. only
  //      while no superadmin exists yet) — see §below. (Accepted for iter-1 DEV:
  //      always-promote, single trusted operator.)
  //   3. Promote hook must re-check domain `@xprivate.education` AND
  //      `email_verified === true` — email-string match alone can elevate a
  //      non-Workspace / unverified address.
  //   4. `secret` (BETTER_AUTH_SECRET) FAILS OPEN on Workers — BA falls back to a
  //      hardcoded default and only throws when NODE_ENV==='production' (undefined
  //      on Workers). Hard-assert the secret is present in createAuth before prod.

  // ── Bootstrap admin via email allowlist (replaces BOOTSTRAP_ADMIN_IDS) ──────
  // The Better Auth admin plugin's `adminUserIds` matches `user.id` — a
  // DB-generated uuidv7 unknown until after the row exists, so it rots on every
  // dev truncate/reseed. Instead, promote-on-create by EMAIL: the allowlist is
  // stable across reseeds and needs no password row seeded into a migration.
  // Runs for BOTH credential and social user creation; combined with the per-env
  // implicit-signup knob, a bootstrap admin self-provisions on first Google login
  // in dev, and is promoted when an admin pre-creates the row in prod.
  databaseHooks: {
    user: {
      create: {
        before: async (user) => {
          const allow = (env.BOOTSTRAP_ADMIN_EMAILS ?? '')
            .split(',').map((e) => e.trim().toLowerCase()).filter(Boolean);
          if (allow.includes(user.email.toLowerCase())) {
            return {
              data: {
                ...user,
                user_type: 'ADMIN',
                account_state: 'ACTIVE',
                superuser: true,
                role: 'superadmin', // BA field name; adapter maps → admin_role column (do NOT write admin_role here)
              },
            };
          }
          return { data: user };
        },
      },
    },
  },

  plugins: [
    admin({
      defaultRole: 'admin',
      adminRoles: ['admin', 'superadmin'],
      // No `adminUserIds` — bootstrap admins are granted via the
      // BOOTSTRAP_ADMIN_EMAILS databaseHook above (email-stable across reseeds).
      impersonationSessionDuration: 60 * 60,
      bannedUserMessage: 'Akun Anda dinonaktifkan. Hubungi administrator.',
      // Map plugin field names → project columns. `role` → `admin_role` to
      // disambiguate from our RBAC (roles/user_roles); it is the admin-API
      // access role ONLY, not business permissions.
      schema: {
        user: { fields: { role: 'admin_role', banReason: 'ban_reason', banExpires: 'ban_expires' } },
        session: { fields: { impersonatedBy: 'impersonated_by' } },
      },
    }),
    twoFactor({
      issuer: 'XPrivate Education',
      totpOptions: { digits: 6, period: 30 },
      backupCodeOptions: { amount: 10, length: 10, storeBackupCodes: 'hashed' },
      skipVerificationOnEnable: false,
      schema: {
        user: { fields: { twoFactorEnabled: 'two_factor_enabled' } },
        twoFactor: {
          modelName: 'two_factor',
          // NOTE: beta twoFactor also generates a `verified` boolean (default true).
          fields: { backupCodes: 'backup_codes', userId: 'user_id' },
        },
      },
    }),
    // magicLink({ ... }) — iter 2+ activation (zero schema impact, uses `verifications` table)
  ],

  hooks: {
    before: createAuthMiddleware(async (ctx) => {
      // Login gate: reject signin if account_state not allowed (see §11.6)
      if (!ctx.path.startsWith('/sign-in/')) return;
      // ... see §11.6.7 for full body
    }),
    after: createAuthMiddleware(async (ctx) => {
      // Login audit event capture
      const event = mapBetterAuthPathToEventType(ctx.path);
      if (!event) return;
      await recordLoginAudit({
        user_id: ctx.context.session?.userId ?? null,
        event_type: event,
        success: !ctx.context.responseHeaders?.has('x-error'),
        ip_address: ctx.request.headers.get('x-forwarded-for') ?? null,
        user_agent: ctx.request.headers.get('user-agent') ?? null,
        metadata: { path: ctx.path },
      });
    }),
  },
} satisfies BetterAuthOptions;

export const auth = betterAuth(config);

Better Auth CLI schema flow: the authoritative, verified procedure lives in .claude/rules/schema.md (“Better Auth schema flow”). In short:

Map all names in config first (core fields + each plugin schema, as above) — this renames both the column AND the generated Drizzle property key to snake_case, and is also where ambiguous names are disambiguated (role → admin_role).
Generate with the auth CLI: node node_modules/.bin/auth generate --config <cfg> --output <dst> --yes (the new tool, replaces @better-auth/cli; config must sit in a workspace that depends on better-auth).

Postfix only what config can’t express: text id → uuid('id').default(sql\uuidv7()`); timestamp→{ withTimezone: true }; .$type<…>()narrowing; **pg_trgm GIN indexes on searched fields (users.email + users.name)**;idx__index naming; delete inlinerelations()(centralized inrelations.ts`); split into per-domain schema files.

Any plugin add/remove/upgrade that changes schema requires re-map + regenerate + re-postfix.

11.3 Plugins

Plugin	Iter 1	Iter 2+	Rationale
Admin	✅ Install + active	Active	Provides `createUser`/`banUser`/`listUsers`/`setUserRole`/`impersonate` APIs. Plugin’s `role` field (mapped to column `admin_role`) is for admin API access check only (NOT our RBAC).
2FA	✅ Install + UI-gated	UI exposed	Schema present iter 1 = “DB-ready” satisfied cheaply. UI doesn’t expose 2FA setup iter 1 (rely on Google account 2FA per scope §2).
Magic Link	❌	Optional install (no migration needed — uses `verifications` table)	Passwordless option for tutor/siswa first-login. Decide iter 2 based on UX research.
Organization	❌ Declined	Declined	Multi-tenant org overhead; we don’t have org concept. Custom provisioning via `admin.createUser` + welcome email instead.
Phone Number / Username / Anonymous	❌ Declined	TBD	Not needed for our flow.
Bearer	⏳ Iter 2+ candidate	Possibly enable for Capacitor mobile (Bearer token vs cookie)	Decide at mobile launch.
Generic OAuth	❌	iter 3+	Only Google iter 1; Facebook/Apple iter 2+ via Better Auth’s first-party social providers directly.

Better Auth’s admin plugin role ≠ our custom RBAC:

Plugin role field: only used internally for “can call admin APIs”. Stored on user table as column admin_role (renamed to avoid colliding with our RBAC roles).
Custom RBAC (user_roles + role_permissions): source of truth for business permission checks.
The two coexist. Don’t conflate.
superuser bool (additionalField): bypasses both for blessed users (e.g., founder).

11.4 Custom RBAC (`@packages/db/schema/rbac.ts`)

11.4.1 Schema

import { pgTable, uuid, text, boolean, timestamp, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { sql, relations } from 'drizzle-orm';
import { users } from './auth';

export const roles = pgTable('roles', {
  id: uuid('id').primaryKey().default(sql`uuidv7()`),
  slug: text('slug').notNull(),                          // lowercase enforced app-side, unique CI
  name_en: text('name_en').notNull(),                    // markdown-rendered display; dup allowed (UI warning only)
  name_id: text('name_id').notNull(),
  description_en: text('description_en'),                // markdown source
  description_id: text('description_id'),
  is_system: boolean('is_system').notNull().default(false),  // prevent UI delete for system roles
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  slug_lower_unique: uniqueIndex('uq_roles_slug_lower').on(sql`LOWER(${t.slug})`),
  name_en_trgm: index('idx_roles_name_en_trgm').using('gin', sql`${t.name_en} gin_trgm_ops`),
  name_id_trgm: index('idx_roles_name_id_trgm').using('gin', sql`${t.name_id} gin_trgm_ops`),
}));

export const role_permissions = pgTable('role_permissions', {
  id: uuid('id').primaryKey().default(sql`uuidv7()`),
  role_id: uuid('role_id').notNull().references(() => roles.id, { onDelete: 'cascade' }),
  key: text('key').notNull(),                            // 'auth:user:read' or '!auth:user:read' (negation)
  granted_by: uuid('granted_by').references(() => users.id),
  granted_at: timestamp('granted_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  role_idx: index('idx_role_permissions_role').on(t.role_id),
  key_idx: index('idx_role_permissions_key').on(t.key),
  role_key_unique: uniqueIndex('uq_role_permissions_role_key').on(t.role_id, t.key),
}));

export const user_roles = pgTable('user_roles', {
  id: uuid('id').primaryKey().default(sql`uuidv7()`),
  user_id: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  role_id: uuid('role_id').notNull().references(() => roles.id, { onDelete: 'cascade' }),
  assigned_by: uuid('assigned_by').references(() => users.id),
  assigned_at: timestamp('assigned_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  user_idx: index('idx_user_roles_user').on(t.user_id),
  role_idx: index('idx_user_roles_role').on(t.role_id),
  user_role_unique: uniqueIndex('uq_user_roles_user_role').on(t.user_id, t.role_id),
}));

// Drizzle relations
export const rolesRelations = relations(roles, ({ many }) => ({
  role_permissions: many(role_permissions),
  user_roles: many(user_roles),
}));
export const role_permissionsRelations = relations(role_permissions, ({ one }) => ({
  role: one(roles, { fields: [role_permissions.role_id], references: [roles.id] }),
  granted_by_user: one(users, { fields: [role_permissions.granted_by], references: [users.id] }),
}));
export const user_rolesRelations = relations(user_roles, ({ one }) => ({
  user: one(users, { fields: [user_roles.user_id], references: [users.id] }),
  role: one(roles, { fields: [user_roles.role_id], references: [roles.id] }),
  assigned_by_user: one(users, { fields: [user_roles.assigned_by], references: [users.id] }),
}));

Slug enforcement (app-layer):

const SLUG_REGEX = /^[a-z0-9_]+$/;
export const slugSchema = z.string().min(1).regex(SLUG_REGEX, 'Slug harus huruf kecil, angka, underscore saja');

11.4.2 PERMISSIONS Constant (Codebase = Source of Truth)

Convention: <domain>:<entity>:<action>[_self] (3-segment). _self suffix means action is restricted to own-data — service layer enforces row-level filter when permission ends in _self.

Domains: auth, rbac, master, schedule, report, hr, finance, audit, notif, anomaly, doc (11 domains).

export const PERMISSIONS = {
  // M1 — Auth
  AUTH_USER_READ: 'auth:user:read',
  AUTH_USER_READ_SELF: 'auth:user:read_self',
  AUTH_USER_WRITE: 'auth:user:write',
  AUTH_USER_WRITE_SELF: 'auth:user:write_self',
  AUTH_USER_DELETE: 'auth:user:delete',
  AUTH_USER_IMPERSONATE: 'auth:user:impersonate',
  AUTH_SESSION_REVOKE: 'auth:session:revoke',
  AUTH_SESSION_REVOKE_SELF: 'auth:session:revoke_self',
  AUTH_MFA_RESET: 'auth:mfa:reset',

  // M1 — RBAC
  RBAC_ROLE_READ: 'rbac:role:read',
  RBAC_ROLE_WRITE: 'rbac:role:write',
  RBAC_ROLE_ASSIGN: 'rbac:role:assign',

  // M2 — Master Data
  MASTER_SUBJECT_READ: 'master:subject:read',
  MASTER_SUBJECT_WRITE: 'master:subject:write',
  MASTER_PRICING_READ: 'master:pricing:read',
  MASTER_PRICING_WRITE: 'master:pricing:write',
  MASTER_CATALOG_WRITE: 'master:catalog:write',
  MASTER_CANCEL_POLICY_WRITE: 'master:cancel_policy:write',
  MASTER_APP_CONFIG_WRITE: 'master:app_config:write',

  // M3 — Schedule
  SCHEDULE_SESSION_READ: 'schedule:session:read',
  SCHEDULE_SESSION_READ_SELF: 'schedule:session:read_self',
  SCHEDULE_SESSION_REQUEST: 'schedule:session:request',
  SCHEDULE_SESSION_REQUEST_SELF: 'schedule:session:request_self',
  SCHEDULE_SESSION_APPROVE: 'schedule:session:approve',
  SCHEDULE_SESSION_CANCEL: 'schedule:session:cancel',
  SCHEDULE_SESSION_CANCEL_SELF: 'schedule:session:cancel_self',
  SCHEDULE_SESSION_RESCHEDULE: 'schedule:session:reschedule',
  SCHEDULE_SESSION_RESCHEDULE_SELF: 'schedule:session:reschedule_self',
  SCHEDULE_SESSION_ATTENDANCE: 'schedule:session:attendance',
  SCHEDULE_SESSION_ATTENDANCE_SELF: 'schedule:session:attendance_self',

  // M4 — Report
  REPORT_PROGRESS_READ: 'report:progress:read',
  REPORT_PROGRESS_READ_SELF: 'report:progress:read_self',
  REPORT_PROGRESS_WRITE: 'report:progress:write',
  REPORT_PROGRESS_WRITE_SELF: 'report:progress:write_self',
  REPORT_MATERI_WRITE: 'report:materi:write',
  REPORT_MATERI_WRITE_SELF: 'report:materi:write_self',
  REPORT_EXPORT: 'report:export',

  // M6 — HR
  HR_TUTOR_READ: 'hr:tutor:read',
  HR_TUTOR_READ_SELF: 'hr:tutor:read_self',
  HR_TUTOR_WRITE: 'hr:tutor:write',
  HR_TUTOR_WRITE_SELF: 'hr:tutor:write_self',
  HR_STUDENT_READ: 'hr:student:read',
  HR_STUDENT_READ_SELF: 'hr:student:read_self',
  HR_STUDENT_WRITE: 'hr:student:write',
  HR_STUDENT_WRITE_SELF: 'hr:student:write_self',
  HR_PARENT_READ: 'hr:parent:read',
  HR_PARENT_READ_SELF: 'hr:parent:read_self',
  HR_PARENT_WRITE: 'hr:parent:write',
  HR_PARENT_WRITE_SELF: 'hr:parent:write_self',
  HR_HONORARIUM_READ: 'hr:honorarium:read',
  HR_HONORARIUM_READ_SELF: 'hr:honorarium:read_self',
  HR_HONORARIUM_EXPORT: 'hr:honorarium:export',
  HR_CONTRACT_READ: 'hr:contract:read',
  HR_CONTRACT_READ_SELF: 'hr:contract:read_self',
  HR_CONTRACT_WRITE: 'hr:contract:write',
  HR_STRIKE_OVERRIDE: 'hr:strike:override',
  HR_FM_APPROVE: 'hr:fm:approve',
  HR_FM_REQUEST_SELF: 'hr:fm:request_self',
  HR_AVAILABILITY_READ_SELF: 'hr:availability:read_self',
  HR_AVAILABILITY_WRITE_SELF: 'hr:availability:write_self',

  // M7 — Finance
  FINANCE_INVOICE_READ: 'finance:invoice:read',
  FINANCE_INVOICE_READ_SELF: 'finance:invoice:read_self',
  FINANCE_INVOICE_EXPORT: 'finance:invoice:export',
  FINANCE_PAYMENT_RECORD: 'finance:payment:record',
  FINANCE_PAYMENT_UPLOAD_SELF: 'finance:payment:upload_self',
  FINANCE_SETTLEMENT_READ: 'finance:settlement:read',
  FINANCE_SETTLEMENT_READ_SELF: 'finance:settlement:read_self',
  FINANCE_PERIOD_CLOSE: 'finance:period:close',

  // A1 — Audit
  AUDIT_EVENT_READ: 'audit:event:read',
  AUDIT_LOGIN_READ: 'audit:login:read',

  // A2 — Notification
  NOTIF_MESSAGE_SEND: 'notif:message:send',
  NOTIF_MESSAGE_READ_SELF: 'notif:message:read_self',
  NOTIF_TEMPLATE_READ: 'notif:template:read',
  NOTIF_TEMPLATE_WRITE: 'notif:template:write',

  // Cross-cutting — Anomaly
  ANOMALY_EVENT_READ: 'anomaly:event:read',
  ANOMALY_EVENT_RESOLVE: 'anomaly:event:resolve',

  // Cross-cutting — Document / KYC
  DOC_FILE_READ: 'doc:file:read',
  DOC_FILE_READ_SELF: 'doc:file:read_self',
  DOC_FILE_WRITE: 'doc:file:write',
  DOC_FILE_WRITE_SELF: 'doc:file:write_self',
  DOC_KYC_REVIEW: 'doc:kyc:review',
} as const;

export type Permission = typeof PERMISSIONS[keyof typeof PERMISSIONS];

11.4.3 Resolution Algorithm

import { db } from '@packages/db/client';
import { eq } from 'drizzle-orm';
import { user_roles, role_permissions } from '@packages/db/schema/rbac';

export async function resolvePermissions(user_id: string): Promise<Record<string, true>> {
  const rows = await db
    .select({ key: role_permissions.key })
    .from(user_roles)
    .innerJoin(role_permissions, eq(role_permissions.role_id, user_roles.role_id))
    .where(eq(user_roles.user_id, user_id));

  const granted = new Set<string>();
  const negated = new Set<string>();

  for (const { key } of rows) {
    if (key.startsWith('!')) negated.add(key.slice(1));
    else granted.add(key);
  }
  for (const neg of negated) granted.delete(neg);

  return Object.fromEntries([...granted].map((k) => [k, true]));
}

Permission middleware (used by oRPC requirePerm factory):

export function requirePermission(permission: Permission) {
  return createMiddleware(async (c, next) => {
    const session = c.get('session');
    if (!session) return c.json({ error: 'Unauthorized' }, 401);
    if (session.user.superuser) return next();          // bypass
    const resolved = c.get('resolvedPermissions');
    if (!resolved[permission]) return c.json({ error: 'Forbidden' }, 403);
    return next();
  });
}

11.4.4 Seed Migration (7 Default Roles + Permissions)

Description columns use markdown source following WHO / SIAPA + WHY / KENAPA + WHAT / APA structure. Rendered client-side via react-markdown + remark-gfm + rehype-sanitize.

import { PERMISSIONS, type Permission } from '@packages/auth/permissions';

const SEED_ROLES = [
  // ===== ADMIN-SIDE (iter 1 active) =====
  {
    slug: 'admin_operator',
    name_en: 'Admin Operator',
    name_id: 'Admin Operasional',
    description_en: `**WHO:** Daily office operator (1–3 per office).\n\n**WHY:** Core staff running day-to-day ops — intentionally NOT given finance write or HR-sensitive areas (contract, strike, KYC). Operational focus, not strategic.\n\n**WHAT:** Master data, scheduling, reporting, basic HR/Finance read.`,
    description_id: `**SIAPA:** Operator harian di kantor XPrivate (1–3 staf per kantor).\n\n**KENAPA:** Pegawai inti yang menjalankan operasi sehari-hari — sengaja TIDAK diberi akses tulis finance atau HR sensitif (kontrak, strike, KYC). Fokus operasional, bukan keputusan strategis.\n\n**APA:** Master data, penjadwalan, pelaporan, akses dasar HR/Keuangan.`,
    is_system: true,
    permissions: [
      PERMISSIONS.MASTER_SUBJECT_READ, PERMISSIONS.MASTER_SUBJECT_WRITE,
      PERMISSIONS.MASTER_PRICING_READ, PERMISSIONS.MASTER_PRICING_WRITE,
      PERMISSIONS.MASTER_CATALOG_WRITE,
      PERMISSIONS.MASTER_CANCEL_POLICY_WRITE,
      PERMISSIONS.MASTER_APP_CONFIG_WRITE,
      PERMISSIONS.SCHEDULE_SESSION_READ, PERMISSIONS.SCHEDULE_SESSION_REQUEST,
      PERMISSIONS.SCHEDULE_SESSION_APPROVE, PERMISSIONS.SCHEDULE_SESSION_CANCEL,
      PERMISSIONS.SCHEDULE_SESSION_RESCHEDULE, PERMISSIONS.SCHEDULE_SESSION_ATTENDANCE,
      PERMISSIONS.REPORT_PROGRESS_READ, PERMISSIONS.REPORT_PROGRESS_WRITE,
      PERMISSIONS.REPORT_MATERI_WRITE, PERMISSIONS.REPORT_EXPORT,
      PERMISSIONS.HR_TUTOR_READ, PERMISSIONS.HR_TUTOR_WRITE,
      PERMISSIONS.HR_STUDENT_READ, PERMISSIONS.HR_STUDENT_WRITE,
      PERMISSIONS.HR_PARENT_READ, PERMISSIONS.HR_PARENT_WRITE,
      PERMISSIONS.FINANCE_INVOICE_READ, PERMISSIONS.FINANCE_SETTLEMENT_READ,
      PERMISSIONS.AUDIT_EVENT_READ,
      PERMISSIONS.NOTIF_MESSAGE_SEND, PERMISSIONS.NOTIF_TEMPLATE_READ,
      PERMISSIONS.ANOMALY_EVENT_READ,
      PERMISSIONS.DOC_FILE_READ, PERMISSIONS.DOC_FILE_WRITE,
    ],
  },
  {
    slug: 'finance_manager',
    name_en: 'Finance Manager',
    name_id: 'Manajer Keuangan',
    description_en: `**WHO:** Finance manager or finance team member.\n\n**WHY:** Owns the invoice → payment → settlement → period-close cycle. Needs full finance access + honorarium export. Period-close is high-impact — gates entire month's books.\n\n**WHAT:** Invoice, payment, settlement, period close, honorarium export.`,
    description_id: `**SIAPA:** Manajer atau staf bagian keuangan.\n\n**KENAPA:** Owner siklus tagihan → pembayaran → settlement → tutup periode. Butuh akses lengkap finance + ekspor honorarium. Tutup periode = action high-impact yang gate buku seluruh bulan.\n\n**APA:** Tagihan, pembayaran, settlement, tutup periode, ekspor honorarium.`,
    is_system: true,
    permissions: [
      PERMISSIONS.FINANCE_INVOICE_READ, PERMISSIONS.FINANCE_INVOICE_EXPORT,
      PERMISSIONS.FINANCE_PAYMENT_RECORD,
      PERMISSIONS.FINANCE_SETTLEMENT_READ,
      PERMISSIONS.FINANCE_PERIOD_CLOSE,
      PERMISSIONS.MASTER_PRICING_READ,
      PERMISSIONS.HR_HONORARIUM_READ, PERMISSIONS.HR_HONORARIUM_EXPORT,
      PERMISSIONS.REPORT_PROGRESS_READ,
      PERMISSIONS.AUDIT_EVENT_READ,
      PERMISSIONS.DOC_FILE_READ,
    ],
  },
  {
    slug: 'hr_officer',
    name_en: 'HR Officer',
    name_id: 'Staf HR',
    description_en: `**WHO:** HR staff handling tutor lifecycle (recruit → contract → strike → terminate) and student onboarding (KYC review).\n\n**WHY:** Owns sensitive HR data flow: contract docs, KTP/akta review, strike override (override 3-strike termination given context), force majeure approval. Dispute-resolution-grade decisions.\n\n**WHAT:** Profile, contract, honorarium, strike, force majeure, KYC review.`,
    description_id: `**SIAPA:** Staf HR yang menangani lifecycle tutor (rekrut → kontrak → strike → terminate) dan onboarding siswa (review KYC).\n\n**KENAPA:** Owner aliran data HR sensitif: dokumen kontrak, review KTP/akta, override strike, approve force majeure. Keputusan tingkat dispute-resolution.\n\n**APA:** Profil, kontrak, honorarium, strike, force majeure, review KYC.`,
    is_system: true,
    permissions: [
      PERMISSIONS.HR_TUTOR_READ, PERMISSIONS.HR_TUTOR_WRITE,
      PERMISSIONS.HR_STUDENT_READ, PERMISSIONS.HR_STUDENT_WRITE,
      PERMISSIONS.HR_PARENT_READ, PERMISSIONS.HR_PARENT_WRITE,
      PERMISSIONS.HR_HONORARIUM_READ, PERMISSIONS.HR_HONORARIUM_EXPORT,
      PERMISSIONS.HR_CONTRACT_READ, PERMISSIONS.HR_CONTRACT_WRITE,
      PERMISSIONS.HR_STRIKE_OVERRIDE,
      PERMISSIONS.HR_FM_APPROVE,
      PERMISSIONS.DOC_FILE_READ, PERMISSIONS.DOC_FILE_WRITE,
      PERMISSIONS.DOC_KYC_REVIEW,
      PERMISSIONS.MASTER_CATALOG_WRITE,
      PERMISSIONS.AUDIT_EVENT_READ,
    ],
  },
  {
    slug: 'viewer',
    name_en: 'Viewer',
    name_id: 'Pengamat',
    description_en: `**WHO:** Business owner, internal auditor, intern — any user needing visibility without modification rights.\n\n**WHY:** Read-only access across modules for review/monitoring. Cannot break anything. Ideal for temporary roles, oversight, compliance audit access.\n\n**WHAT:** Read-only across all modules; no write, no export.`,
    description_id: `**SIAPA:** Pemilik bisnis, auditor internal, intern — user yang butuh visibility tanpa hak ubah.\n\n**KENAPA:** Akses baca lintas modul untuk review/monitoring. Tidak bisa merusak apapun. Cocok untuk role sementara, oversight, audit compliance.\n\n**APA:** Read-only lintas semua modul; tidak bisa ubah, tidak bisa ekspor.`,
    is_system: true,
    permissions: [
      PERMISSIONS.AUTH_USER_READ, PERMISSIONS.RBAC_ROLE_READ,
      PERMISSIONS.MASTER_SUBJECT_READ, PERMISSIONS.MASTER_PRICING_READ,
      PERMISSIONS.SCHEDULE_SESSION_READ,
      PERMISSIONS.REPORT_PROGRESS_READ,
      PERMISSIONS.HR_TUTOR_READ, PERMISSIONS.HR_STUDENT_READ, PERMISSIONS.HR_PARENT_READ,
      PERMISSIONS.HR_HONORARIUM_READ, PERMISSIONS.HR_CONTRACT_READ,
      PERMISSIONS.FINANCE_INVOICE_READ, PERMISSIONS.FINANCE_SETTLEMENT_READ,
      PERMISSIONS.AUDIT_EVENT_READ,
      PERMISSIONS.NOTIF_TEMPLATE_READ,
      PERMISSIONS.ANOMALY_EVENT_READ,
      PERMISSIONS.DOC_FILE_READ,
    ],
  },

  // ===== USER-SIDE (iter 2+ DB-ready) =====
  {
    slug: 'tutor',
    name_en: 'Tutor',
    name_id: 'Tutor',
    description_en: `**WHO:** Tutor with own portal login (iter 2+).\n\n**WHY:** Reduces admin-proxy load from iter 1 — tutor self-views honorarium, edits profile, manages availability, marks own session attendance, appeals FM cases. Iter 1 placeholder: role pre-seeded so iter 2 activation = single admin click.\n\n**WHAT:** Own profile, honorarium, sessions, materi log, availability slots, FM appeal (self-scoped only).`,
    description_id: `**SIAPA:** Tutor dengan login portal sendiri (iter 2+).\n\n**KENAPA:** Mengurangi beban admin proxy iter 1 — tutor lihat honor sendiri, edit profil, kelola slot ketersediaan, mark attendance sendiri, banding FM. Iter 1 placeholder: role pre-seeded supaya aktivasi iter 2 = satu klik admin.\n\n**APA:** Profil sendiri, honor, sesi, materi log, slot ketersediaan, banding FM (self-scope saja).`,
    is_system: true,
    permissions: [
      PERMISSIONS.AUTH_USER_READ_SELF, PERMISSIONS.AUTH_USER_WRITE_SELF,
      PERMISSIONS.AUTH_SESSION_REVOKE_SELF,
      PERMISSIONS.HR_TUTOR_READ_SELF, PERMISSIONS.HR_TUTOR_WRITE_SELF,
      PERMISSIONS.HR_HONORARIUM_READ_SELF,
      PERMISSIONS.HR_CONTRACT_READ_SELF,
      PERMISSIONS.HR_AVAILABILITY_READ_SELF, PERMISSIONS.HR_AVAILABILITY_WRITE_SELF,
      PERMISSIONS.HR_FM_REQUEST_SELF,
      PERMISSIONS.SCHEDULE_SESSION_READ_SELF,
      PERMISSIONS.SCHEDULE_SESSION_CANCEL_SELF,
      PERMISSIONS.SCHEDULE_SESSION_RESCHEDULE_SELF,
      PERMISSIONS.SCHEDULE_SESSION_ATTENDANCE_SELF,
      PERMISSIONS.REPORT_PROGRESS_READ_SELF, PERMISSIONS.REPORT_PROGRESS_WRITE_SELF,
      PERMISSIONS.REPORT_MATERI_WRITE_SELF,
      PERMISSIONS.NOTIF_MESSAGE_READ_SELF,
      PERMISSIONS.DOC_FILE_READ_SELF, PERMISSIONS.DOC_FILE_WRITE_SELF,
    ],
  },
  {
    slug: 'student',
    name_en: 'Student',
    name_id: 'Siswa',
    description_en: `**WHO:** Student with own portal login (iter 2+).\n\n**WHY:** Self-service for actions students can reasonably do themselves — request sessions, cancel within policy, view invoice, upload payment proof. Reduces admin entry for routine asks. Iter 1 = admin proxy; this role pre-seeded for iter 2 flip.\n\n**WHAT:** Own profile, session request/cancel, view invoice, upload payment proof (self-scoped only).`,
    description_id: `**SIAPA:** Siswa dengan login portal sendiri (iter 2+).\n\n**KENAPA:** Self-service untuk action yang wajar dilakukan siswa — request sesi, cancel sesuai policy, view tagihan, upload bukti bayar. Mengurangi admin entry untuk permintaan rutin. Iter 1 = admin proxy; role pre-seeded untuk flip iter 2.\n\n**APA:** Profil sendiri, request/cancel sesi, view tagihan, upload bukti bayar (self-scope saja).`,
    is_system: true,
    permissions: [
      PERMISSIONS.AUTH_USER_READ_SELF, PERMISSIONS.AUTH_USER_WRITE_SELF,
      PERMISSIONS.AUTH_SESSION_REVOKE_SELF,
      PERMISSIONS.HR_STUDENT_READ_SELF, PERMISSIONS.HR_STUDENT_WRITE_SELF,
      PERMISSIONS.SCHEDULE_SESSION_READ_SELF,
      PERMISSIONS.SCHEDULE_SESSION_REQUEST_SELF,
      PERMISSIONS.SCHEDULE_SESSION_CANCEL_SELF,
      PERMISSIONS.REPORT_PROGRESS_READ_SELF,
      PERMISSIONS.FINANCE_INVOICE_READ_SELF,
      PERMISSIONS.FINANCE_PAYMENT_UPLOAD_SELF,
      PERMISSIONS.NOTIF_MESSAGE_READ_SELF,
      PERMISSIONS.DOC_FILE_READ_SELF, PERMISSIONS.DOC_FILE_WRITE_SELF,
    ],
  },
  {
    slug: 'parent',
    name_en: 'Parent',
    name_id: 'Orang Tua',
    description_en: `**WHO:** Parent of a (minor) student (iter 2+ with family group activation).\n\n**WHY:** Parents typically handle invoice/payment on behalf of minor children. Iter 2 family group lets parent see linked children profiles + consolidated invoices. Iter 1 = admin proxy for everything.\n\n**WHAT:** Own profile, invoice & payment management (extends to linked children iter 2+).`,
    description_id: `**SIAPA:** Orang tua siswa (di bawah umur), aktif iter 2+ dengan family group.\n\n**KENAPA:** Orang tua biasanya pegang tagihan & pembayaran atas nama anak. Family group iter 2 memungkinkan orang tua lihat profil anak yang ter-link + tagihan terkonsolidasi. Iter 1 = admin proxy untuk semua.\n\n**APA:** Profil sendiri, manajemen tagihan & pembayaran (extend ke anak yang ter-link iter 2+).`,
    is_system: true,
    permissions: [
      PERMISSIONS.AUTH_USER_READ_SELF, PERMISSIONS.AUTH_USER_WRITE_SELF,
      PERMISSIONS.AUTH_SESSION_REVOKE_SELF,
      PERMISSIONS.HR_PARENT_READ_SELF, PERMISSIONS.HR_PARENT_WRITE_SELF,
      PERMISSIONS.FINANCE_INVOICE_READ_SELF,
      PERMISSIONS.FINANCE_PAYMENT_UPLOAD_SELF,
      PERMISSIONS.NOTIF_MESSAGE_READ_SELF,
      PERMISSIONS.DOC_FILE_READ_SELF, PERMISSIONS.DOC_FILE_WRITE_SELF,
    ],
  },
] satisfies Array<{
  slug: string;
  name_en: string;
  name_id: string;
  description_en: string;
  description_id: string;
  is_system: boolean;
  permissions: Permission[];
}>;

// Migration logic
for (const { slug, name_en, name_id, description_en, description_id, is_system, permissions } of SEED_ROLES) {
  const [role] = await db
    .insert(roles)
    .values({ slug, name_en, name_id, description_en, description_id, is_system })
    .onConflictDoNothing({ target: sql`LOWER(${roles.slug})` })
    .returning();

  if (!role) continue;

  await db.insert(role_permissions).values(
    permissions.map((key) => ({ role_id: role.id, key, granted_by: null })),
  ).onConflictDoNothing();
}

11.4.5 UI Metadata (description per permission key)

import { PERMISSIONS, type Permission } from '@packages/auth/permissions';

export const DOMAIN_LABELS: Record<string, { id: string; en: string }> = {
  auth: { id: 'Autentikasi & User', en: 'Auth & User Management' },
  rbac: { id: 'Role & Permission', en: 'Role & Permission' },
  master: { id: 'Master Data', en: 'Master Data' },
  schedule: { id: 'Penjadwalan', en: 'Scheduling' },
  report: { id: 'Pelaporan', en: 'Reporting' },
  hr: { id: 'HR & Profil', en: 'HR & Profile' },
  finance: { id: 'Keuangan', en: 'Finance' },
  audit: { id: 'Audit Log', en: 'Audit Log' },
  notif: { id: 'Notifikasi', en: 'Notification' },
  anomaly: { id: 'Deteksi Anomali', en: 'Anomaly Detection' },
  doc: { id: 'Dokumen & KYC', en: 'Document & KYC' },
};

// Exhaustive Record<Permission, ...> — TS catches missing entries
export const PERMISSION_DESCRIPTIONS: Record<Permission, { id: string; en: string }> = {
  [PERMISSIONS.AUTH_USER_READ]: { id: 'Lihat data semua user', en: 'View all user data' },
  [PERMISSIONS.AUTH_USER_READ_SELF]: { id: 'Lihat data diri sendiri', en: 'View own user data' },
  // ... full mapping (one entry per key in PERMISSIONS)
};

// Helper: derive domain from key
export function domainOf(key: Permission): string {
  return key.split(':')[0]!;
}

11.5 login_audit_events

11.5.1 Schema

import { pgTable, uuid, text, boolean, timestamp, jsonb, index } from 'drizzle-orm/pg-core';
import { sql, relations } from 'drizzle-orm';
import { users } from './auth';

export const login_audit_events = pgTable('login_audit_events', {
  id: uuid('id').primaryKey().default(sql`uuidv7()`),
  user_id: uuid('user_id').references(() => users.id, { onDelete: 'set null' }),
  event_type: text('event_type').notNull(),
  success: boolean('success').notNull(),
  failure_reason: text('failure_reason'),
  ip_address: text('ip_address'),
  user_agent: text('user_agent'),
  country_code: text('country_code'),                    // ISO-3166-1 alpha-2; null iter 1 (geo-lookup iter 2+)
  provider_id: text('provider_id'),                      // 'google' | 'credentials' | 'magic_link' | null
  session_id: uuid('session_id'),                        // ref to sessions.id — no FK (session may be deleted)
  metadata: jsonb('metadata'),
  occurred_at: timestamp('occurred_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  user_occurred_idx: index('idx_login_audit_user_occurred').on(t.user_id, t.occurred_at),
  event_type_idx: index('idx_login_audit_event_type').on(t.event_type),
  occurred_idx: index('idx_login_audit_occurred').on(t.occurred_at),
  ip_trgm: index('idx_login_audit_ip_trgm').using('gin', sql`${t.ip_address} gin_trgm_ops`),
}));

export const login_audit_eventsRelations = relations(login_audit_events, ({ one }) => ({
  user: one(users, { fields: [login_audit_events.user_id], references: [users.id] }),
}));

11.5.2 Event Type Taxonomy (codebase constant)

export const LOGIN_AUDIT_EVENT = {
  // Authentication
  SIGN_IN_SUCCESS: 'sign_in_success',
  SIGN_IN_FAILURE: 'sign_in_failure',
  SIGN_OUT: 'sign_out',
  SESSION_REVOKED: 'session_revoked',
  SESSION_EXPIRED: 'session_expired',
  // Account provisioning
  SIGN_UP_ATTEMPT_BLOCKED: 'sign_up_attempt_blocked',
  USER_CREATED_BY_ADMIN: 'user_created_by_admin',
  // Identity linking
  IDENTITY_LINKED: 'identity_linked',
  IDENTITY_UNLINKED: 'identity_unlinked',
  // Verification & password
  PASSWORD_RESET_REQUESTED: 'password_reset_requested',
  PASSWORD_RESET_COMPLETED: 'password_reset_completed',
  PASSWORD_CHANGED: 'password_changed',
  EMAIL_VERIFICATION_REQUESTED: 'email_verification_requested',
  EMAIL_VERIFICATION_COMPLETED: 'email_verification_completed',
  MAGIC_LINK_REQUESTED: 'magic_link_requested',
  MAGIC_LINK_USED: 'magic_link_used',
  // MFA
  MFA_ENROLLED: 'mfa_enrolled',
  MFA_REMOVED: 'mfa_removed',
  MFA_CHALLENGE_SUCCESS: 'mfa_challenge_success',
  MFA_CHALLENGE_FAILURE: 'mfa_challenge_failure',
  MFA_BACKUP_CODE_USED: 'mfa_backup_code_used',
  // Account state
  ACCOUNT_BANNED: 'account_banned',
  ACCOUNT_UNBANNED: 'account_unbanned',
  ACCOUNT_STATE_CHANGED: 'account_state_changed',        // metadata: { from, to, actor_user_id, reason }
  // Admin actions
  IMPERSONATION_STARTED: 'impersonation_started',        // metadata: { target_user_id }
  IMPERSONATION_ENDED: 'impersonation_ended',
  ROLE_ASSIGNED: 'role_assigned',                        // metadata: { role_slug, target_user_id }
  ROLE_REVOKED: 'role_revoked',
  // Security
  SUSPICIOUS_ACTIVITY: 'suspicious_activity',
  RATE_LIMIT_TRIGGERED: 'rate_limit_triggered',
} as const;

export type LoginAuditEventType = typeof LOGIN_AUDIT_EVENT[keyof typeof LOGIN_AUDIT_EVENT];

11.5.3 Service Layer

export interface LoginAuditRecord {
  user_id: string | null;
  event_type: LoginAuditEventType;
  success: boolean;
  failure_reason?: string | null;
  ip_address?: string | null;
  user_agent?: string | null;
  provider_id?: string | null;
  session_id?: string | null;
  metadata?: Record<string, unknown> | null;
}

export async function recordLoginAudit(record: LoginAuditRecord): Promise<void> {
  await db.insert(login_audit_events).values({
    user_id: record.user_id,
    event_type: record.event_type,
    success: record.success,
    failure_reason: record.failure_reason ?? null,
    ip_address: record.ip_address ?? null,
    user_agent: record.user_agent ?? null,
    provider_id: record.provider_id ?? null,
    session_id: record.session_id ?? null,
    metadata: record.metadata ?? null,
  });
}

11.5.4 Retention (2-year, cron)

{
  "name": "xprivate-cron-cleanup-login-audit",
  "main": "src/index.ts",
  "triggers": { "crons": ["0 3 * * *"] }
}

export default {
  async scheduled() {
    await db.execute(sql`DELETE FROM login_audit_events WHERE occurred_at < now() - interval '2 years'`);
  },
};

Why 2-year: Industry norm — sufficient for forensic investigation + breach disclosure timelines (GDPR 72h notification + extended audit). Shorter than business audit (7-year, regulatory) because security event volume is much higher and personal IP/UA data ages poorly under privacy norms.

11.6 account_state Machine

11.6.1 State Constants

export const USER_TYPE = {
  ADMIN: 'ADMIN', TUTOR: 'TUTOR', SISWA: 'SISWA', PARENT: 'PARENT',
} as const;
export type UserType = typeof USER_TYPE[keyof typeof USER_TYPE];

export const ACCOUNT_STATE = {
  PENDING_INTERVIEW: 'PENDING_INTERVIEW',
  APPROVED: 'APPROVED',
  ACTIVE: 'ACTIVE',
  SUSPENDED: 'SUSPENDED',
  ARCHIVED: 'ARCHIVED',
} as const;
export type AccountState = typeof ACCOUNT_STATE[keyof typeof ACCOUNT_STATE];

11.6.2 State Semantics

State	Login	Visible	Reversible	Meaning
`PENDING_INTERVIEW`	❌	“Pending” tab	✅	Admin pre-created; HR interview not yet done (TUTOR primarily). Iter 1: unused.
`APPROVED`	✅ (first-login)	“Pending First Login” tab	✅	Ready to sign in. First success → ACTIVE. Iter 1: unused.
`ACTIVE`	✅	Default	✅	Fully functional. Iter 1 default for all new users.
`SUSPENDED`	❌	“Suspended” filter	✅	Investigation / strike / payment freeze. Reversible.
`ARCHIVED`	❌	“Archive” filter only	❌ terminal	Resigned / terminated / graduated. No data deletion (audit retention).

11.6.3 Initial State (iter 1 vs iter 2)

/**
 * Iter 1: all admin-created users start ACTIVE.
 * Rationale: iter 1 = admin manually curates onboarding OFFLINE before creating
 * user in system. Users only enter system after vetting, so PENDING_INTERVIEW /
 * APPROVED states are not used. Iter 2+ swap to CURATED_INITIAL_STATE when
 * business scales + self-register flow activates.
 */
export const INITIAL_ACCOUNT_STATE = {
  ADMIN: ACCOUNT_STATE.ACTIVE,
  TUTOR: ACCOUNT_STATE.ACTIVE,
  SISWA: ACCOUNT_STATE.ACTIVE,
  PARENT: ACCOUNT_STATE.ACTIVE,
} as const satisfies Record<UserType, AccountState>;

// Iter 2+ activation — swap above when self-register / curation pipeline online
export const CURATED_INITIAL_STATE = {
  ADMIN: ACCOUNT_STATE.APPROVED,
  TUTOR: ACCOUNT_STATE.PENDING_INTERVIEW,
  SISWA: ACCOUNT_STATE.APPROVED,
  PARENT: ACCOUNT_STATE.APPROVED,
} as const satisfies Record<UserType, AccountState>;

11.6.4 Transition Matrix (full DB-ready, iter 1 mostly idle)

const ALLOWED_TRANSITIONS = {
  ADMIN: {
    PENDING_INTERVIEW: [],
    APPROVED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ACTIVE: [ACCOUNT_STATE.SUSPENDED, ACCOUNT_STATE.ARCHIVED],
    SUSPENDED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ARCHIVED: [],
  },
  TUTOR: {
    PENDING_INTERVIEW: [ACCOUNT_STATE.APPROVED, ACCOUNT_STATE.ARCHIVED],
    APPROVED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.PENDING_INTERVIEW, ACCOUNT_STATE.ARCHIVED],
    ACTIVE: [ACCOUNT_STATE.SUSPENDED, ACCOUNT_STATE.ARCHIVED],
    SUSPENDED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ARCHIVED: [],
  },
  SISWA: {
    PENDING_INTERVIEW: [ACCOUNT_STATE.APPROVED, ACCOUNT_STATE.ARCHIVED],
    APPROVED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ACTIVE: [ACCOUNT_STATE.SUSPENDED, ACCOUNT_STATE.ARCHIVED],
    SUSPENDED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ARCHIVED: [],
  },
  PARENT: {
    PENDING_INTERVIEW: [ACCOUNT_STATE.APPROVED, ACCOUNT_STATE.ARCHIVED],
    APPROVED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ACTIVE: [ACCOUNT_STATE.SUSPENDED, ACCOUNT_STATE.ARCHIVED],
    SUSPENDED: [ACCOUNT_STATE.ACTIVE, ACCOUNT_STATE.ARCHIVED],
    ARCHIVED: [],
  },
} as const satisfies Record<UserType, Record<AccountState, readonly AccountState[]>>;

11.6.5 Transition Helper (app-enforced, no DB CHECK)

export function canTransition(user_type: UserType, from: AccountState, to: AccountState): boolean {
  return ALLOWED_TRANSITIONS[user_type][from].includes(to);
}

export class InvalidStateTransitionError extends Error {
  constructor(public user_type: UserType, public from: AccountState, public to: AccountState) {
    super(`Invalid state transition for ${user_type}: ${from} → ${to}`);
  }
}

export async function transitionAccountState(args: {
  user_id: string;
  to: AccountState;
  actor_user_id: string;
  reason?: string;
}): Promise<void> {
  const user = await db.query.users.findFirst({ where: eq(users.id, args.user_id) });
  if (!user) throw new Error('User not found');

  if (!canTransition(user.user_type, user.account_state, args.to)) {
    throw new InvalidStateTransitionError(user.user_type, user.account_state, args.to);
  }

  await db.transaction(async (tx) => {
    await tx.update(users)
      .set({ account_state: args.to, updated_at: sql`now()` })
      .where(eq(users.id, args.user_id));

    await recordLoginAudit({
      user_id: args.user_id,
      event_type: LOGIN_AUDIT_EVENT.ACCOUNT_STATE_CHANGED,
      success: true,
      metadata: { from: user.account_state, to: args.to, actor_user_id: args.actor_user_id, reason: args.reason ?? null },
    });

    // Cascade: archive/suspend → revoke all sessions
    if (args.to === ACCOUNT_STATE.ARCHIVED || args.to === ACCOUNT_STATE.SUSPENDED) {
      await tx.delete(sessions).where(eq(sessions.user_id, args.user_id));
    }
  });
}

Self-archive prevention:

if (args.to === ACCOUNT_STATE.ARCHIVED && args.user_id === args.actor_user_id) {
  throw new Error('Cannot archive yourself.');
}

11.6.6 Auto-Transition (Better Auth `after` hook)

// in Better Auth after-hook on successful signin
if (user.account_state === ACCOUNT_STATE.APPROVED) {
  // Iter 1: never taken (users start ACTIVE).
  // Iter 2+: APPROVED → ACTIVE on first successful signin.
  await transitionAccountState({
    user_id: user.id,
    to: ACCOUNT_STATE.ACTIVE,
    actor_user_id: user.id,
    reason: 'first_successful_signin',
  });
}

11.6.7 Login Gate (Better Auth `before` hook)

// Iter 1: only ACTIVE allowed
const allowedStates: AccountState[] = [ACCOUNT_STATE.ACTIVE];
// Iter 2+: extend to allow APPROVED
// const allowedStates: AccountState[] = [ACCOUNT_STATE.APPROVED, ACCOUNT_STATE.ACTIVE];

hooks: {
  before: createAuthMiddleware(async (ctx) => {
    if (!ctx.path.match(/^\/sign-in\//)) return;
    const email = ctx.body?.email;
    if (!email) return;
    const user = await db.query.users.findFirst({ where: eq(users.email, email) });
    if (!user) return;       // Better Auth handles "no user"
    if (!allowedStates.includes(user.account_state)) {
      await recordLoginAudit({
        user_id: user.id,
        event_type: LOGIN_AUDIT_EVENT.SIGN_IN_FAILURE,
        success: false,
        failure_reason: `account_state_${user.account_state.toLowerCase()}`,
      });
      throw new APIError('FORBIDDEN', {
        message: `Akun dalam status ${user.account_state}. Hubungi administrator.`,
      });
    }
  }),
}

11.7 Admin-Initiated Provisioning

export async function createUserHandler(input: CreateUserInput): Promise<{ user_id: string }> {
  // 1. Better Auth admin plugin creates user
  const result = await auth.api.createUser({
    body: {
      email: input.email,
      name: input.name,
      role: 'admin',
      data: {
        user_type: input.user_type,
        account_state: INITIAL_ACCOUNT_STATE[input.user_type],
        superuser: false,
        avatar_url: null,
      },
    },
  });

  // 2. Assign custom RBAC roles (separate from Better Auth role field)
  await db.insert(user_roles).values(
    input.role_slugs.map((slug) => ({
      user_id: result.user.id,
      role_id: lookupRoleIdBySlug(slug),
      assigned_by: input.actor_user_id,
    })),
  );

  return { user_id: result.user.id };
}

11.8 Welcome Email (manual trigger iter 1)

export async function sendWelcomeEmailHandler(args: { user_id: string; actor_user_id: string }): Promise<void> {
  const user = await db.query.users.findFirst({ where: eq(users.id, args.user_id) });
  if (!user) throw new Error('User not found');

  const template = await db.query.notification_templates.findFirst({
    where: and(
      eq(notification_templates.slug, `welcome_${user.user_type.toLowerCase()}`),
      eq(notification_templates.is_active, true),
    ),
  });

  await notificationService.send({
    to: user.email,
    template_slug: template.slug,
    locale: 'id',                                        // BI iter 1
    variables: { name: user.name, dashboard_url: env.PUBLIC_DASHBOARD_URL },
    user_id: user.id,
    triggered_by: args.actor_user_id,
    channel: 'email',
  });
}

Welcome email templates seeded (full notification_templates schema deferred to A2 sub-task):

slug	locales	audience
`welcome_admin`	id + en	New admin: “log in via Google with your @xprivate.education account at [dashboard_url]“
`welcome_tutor`	id + en	Optional iter 1 (no login); iter 2 includes set-password link
`welcome_siswa`	id + en	Optional iter 1; iter 2 includes set-password link
`welcome_parent`	id + en	Optional iter 1; iter 2 includes set-password link

ADMIN (iter 1 + iter 2)

User visits https://dashboard.xprivate.education
FE calls WhoAmI → 401 → redirect /sign-in
User clicks “Sign in with Google”
Better Auth /sign-in/social/google → Google OAuth → callback
mapProfileToUser validates profile.hd === 'xprivate.education'
Better Auth links accounts row (Google) ↔ existing users row (matched by email)
Provisioning gate enforced in databaseHooks.user.create.before (deny-by-default; disableImplicitSignUp alone is bypassable — see ⚠️ checklist above). Google is DEFERRED — this flow is the target once the checklist is satisfied.
databaseHooks.user.create.before promotes the row to superadmin when its email is in BOOTSTRAP_ADMIN_EMAILS
Session created → cookie set → redirect /dashboard
FE calls WhoAmI → 200 → render dashboard with resolved permissions

TUTOR / SISWA / PARENT (iter 1)

No signin flow. All actions via admin proxy.

TUTOR / SISWA / PARENT (iter 2+)

Option A — forgot password setup:
1. Admin clicks “Send Welcome Email”
2. Recipient receives email with link /reset-password?token=<verification_token>
3. Click → “Set up your password” form
4. Submit → Better Auth resetPassword API → password stored on accounts row provider=credentials
5. Auto-signin → session → portal
Option B — magic link:
1. Admin clicks “Send Magic Link”
2. Recipient receives email with /magic-link?token=...
3. Click → Better Auth verifies → session → portal (no password set)

Decide A vs B at iter 2 based on UX research with target demographic.

11.10 Edge Cases

Scenario	Handling
Admin re-links own Google OAuth (different email)	`accountLinking.allowDifferentEmails: false` rejects
Suspended user signin attempt	Login gate (§11.6.7) → 403 + audit `sign_in_failure` with `failure_reason: 'account_state_suspended'`
Admin archives self	App-side validation: reject when `target_user_id === actor_user_id` for ARCHIVED
Admin tries unlink user’s only identity	`accountLinking.allowUnlinkingAll: false` prevents lockout
OAuth callback fails domain check	Better Auth returns error → FE: “Akun tidak dapat dibuat. Hubungi administrator.”
Email collision (admin creates user with existing email)	`users.email` unique constraint → 409 → UI: “Email sudah terdaftar”

11.11 Cross-References & Dependencies

Better Auth CLI postfix workflow → captured as workflow rule for CLAUDE.md (Step 6 sub-task)
Notification templates schema (notification_templates) → full design deferred to A2 sub-task; iter 1 seeds 4 welcome templates
Profile + KYC tables → separate sub-task (tutor_profiles, student_profiles, parent_profiles, documents)
§4 uuidv7() correction → fixed in §4 above
§7 provisioning wording → revised; full detail here in §11

12. Frontend UI Conventions

Direction: Friendly dashboard UI (not industrial/sterile). Subtle motion that enhances state-change communication without delaying interaction. Library-first (no hand-roll where mainstream lib exists). Stable, predictable, accessible by default.

12.1 Component Library Philosophy

Foundation: Radix UI primitives → shadcn/ui → custom XPrivate components.

Radix UI: Unstyled, accessible, behavior-correct primitives. The React equivalent of bits-ui (which is Svelte-only). Stable interactions out of the box (focus management, keyboard nav, ARIA roles).
shadcn/ui: Tailwind-styled components built on Radix. Copy-paste pattern (we own the source), not a black-box npm dep.
XPrivate components: thin wrappers + composed components in @apps/web/src/components/ui/* (extends shadcn), @apps/web/src/components/features/* (business-specific).

Prefer composing existing components over building from scratch.

12.2 Full Library Inventory

Concern	Library	Bundle (gz)	Why this one
Component primitives	`@radix-ui/*` (via shadcn)	varies (tree-shakeable)	Industry-standard React headless primitives
Styled components	`shadcn/ui` (copy-paste)	— (we own source)	Customizable, accessible defaults
Tailwind	`tailwindcss` v4+	— (build-time)	Utility-first; pairs with shadcn
Class merging	`tailwind-merge` + `clsx`	~5kb	Idiomatic shadcn pattern
CSS variants	`class-variance-authority` (CVA)	~2kb	Variant-based components
Animation	`motion` (Framer Motion v11+)	~30kb	Declarative animation, well-tuned for subtle UI motion
Charts	`shadcn/ui charts` (= Recharts)	~50kb	Native shadcn ecosystem; theme via Tailwind CSS vars
Data table	`@tanstack/react-table`	~15kb	Headless table; sort/filter/pagination
Forms	`@tanstack/react-form`	~10kb	Type-first, tightly integrated with TanStack ecosystem (Start, Query). Less mature ecosystem than react-hook-form but better TypeScript inference.
Form validation adapter	`@tanstack/zod-form-adapter` + `zod`	~3kb	Shared Zod schemas (client + server)
Toast	`sonner`	~5kb	shadcn-integrated, beautiful defaults
Command palette	`cmdk`	~4kb	shadcn `<Command>` uses it; fuzzy search
Drawer / Sheet	`vaul`	~8kb	shadcn `<Drawer>` uses it; mobile bottom-sheet
Date picker	`react-day-picker` v8	~15kb	shadcn `<Calendar>` uses it; ARIA-compliant
Date utilities	`date-fns`	tree-shakeable	Immutable, TZ-safe, lighter than moment
Virtualization	`@tanstack/react-virtual`	~5kb	For tables/lists > 1k rows
URL filter state	`nuqs`	~3kb	Query params binding (`?status=active&page=2`)
Markdown render	`react-markdown` + `remark-gfm` + `rehype-sanitize`	~25kb	Description columns (admin-managed entity)
Icons	`lucide-react`	tree-shakeable (~1kb per icon)	shadcn default; consistent style
Server state	`@tanstack/react-query`	~13kb	Cache, refetch, optimistic updates
Client state (rare)	`zustand`	~1kb	Only for truly-local non-server state

Total animation + new libs added: ~150kb gz across all features. Acceptable for an internal admin dashboard.

12.3 Stability Patterns

12.3.1 React Query Defaults (Project-Wide)

export const queryClient = new QueryClient({
  defaultOptions: {
    queries: {
      staleTime: 30 * 1000,                  // 30s — admin sees fresh-ish data
      gcTime: 5 * 60 * 1000,                 // 5min cache retention
      refetchOnWindowFocus: true,            // critical for admin returning from other tab
      retry: 2,                              // retry transient failures
      retryDelay: (i) => Math.min(1000 * 2 ** i, 30_000),
    },
    mutations: { retry: 0 },                 // mutations not retried automatically
  },
});

12.3.2 Loading + Error States via Suspense + Error Boundaries

Every route file in TanStack Start router exposes:

export const Route = createFileRoute('/dashboard/users')({
  pendingComponent: UsersListSkeleton,
  errorComponent: GenericRouteError,
  loader: async ({ context }) => context.queryClient.ensureQueryData(usersQuery()),
  component: UsersList,
});

pendingComponent → shadcn <Skeleton> shimmer
errorComponent → generic error UI with “Coba lagi” button + report-to-Sentry style action

12.3.3 Form Pattern (Single SoT for Validation)

// 1. Zod schema in @packages/service (shared client + server)
export const createUserSchema = z.object({
  email: z.email(),
  name: z.string().min(2),
  user_type: z.enum(['ADMIN', 'TUTOR', 'SISWA', 'PARENT']),
});

// 2. Server validates via oRPC (contract schema)
// 3. Client validates via @tanstack/react-form + Zod adapter
import { useForm } from '@tanstack/react-form';
import { zodValidator } from '@tanstack/zod-form-adapter';

const form = useForm({
  defaultValues: { email: '', name: '', user_type: 'ADMIN' as const },
  validatorAdapter: zodValidator(),
  validators: { onSubmit: createUserSchema },
  onSubmit: async ({ value }) => { await api.users.create(value); },
});

12.3.4 Mutation = Toast on Every Submit

const createUser = useMutation({
  mutationFn: (input) => api.users.create(input),
  onSuccess: () => {
    toast.success('User berhasil dibuat');
    queryClient.invalidateQueries({ queryKey: ['users'] });
  },
  onError: (err) => toast.error(err.message ?? 'Gagal membuat user'),
});

Mandatory toast on success + error for every admin-triggered mutation. Implicit feedback is broken UX — admin should never wonder “did it work?“.

12.3.5 Optimistic Updates Where Safe

For low-risk mutations (toggle, rename, reorder), use React Query optimistic update pattern:

const toggleActive = useMutation({
  mutationFn: api.users.toggleActive,
  onMutate: async (input) => {
    await queryClient.cancelQueries({ queryKey: ['users', input.id] });
    const prev = queryClient.getQueryData(['users', input.id]);
    queryClient.setQueryData(['users', input.id], { ...prev, is_active: input.is_active });
    return { prev };
  },
  onError: (_, input, ctx) => queryClient.setQueryData(['users', input.id], ctx?.prev),
  onSettled: (_, __, input) => queryClient.invalidateQueries({ queryKey: ['users', input.id] }),
});

NOT for: financial mutations, role changes, state machine transitions — those require server confirmation before UI updates.

12.4 `data-testid` Mandate (Business-Flow Components)

Convention: data-testid="<feature>__<element>__<action>" (kebab-case, double-underscore separator).

Examples:

<Button data-testid="user-create__form__submit">Create</Button>
<Button data-testid="user-create__form__cancel">Cancel</Button>
<Input data-testid="user-create__email-input" {...form.register('email')} />
<Dialog data-testid="user-archive__confirm-modal">...</Dialog>
<Button data-testid="user-archive__confirm-yes">Yes, archive</Button>

Mandatory on:

Submit/cancel buttons on forms
Primary action buttons (Save, Delete, Approve, Reject, Cancel Sesi, etc.)
Form inputs that gate validation
Modal confirm/cancel buttons
Tab/segment switches that change view
List row action triggers (kebab menu items)
Critical navigation (logout, dashboard tabs)

Optional on: decorative elements, read-only display rows, layout chrome.

Playwright pattern:

await page.getByTestId('user-create__form__submit').click();
// NEVER: await page.click('button.btn-primary');

State (loading / error / invalid) tracked via ARIA, NOT extra data attributes:

State	ARIA attribute	Example
Async loading	`aria-busy="true"`	`<Button data-testid="..." aria-busy={isPending}>`
Disabled	`aria-disabled="true"`	shadcn Button handles automatically
Invalid input	`aria-invalid="true"`	shadcn Form Field handles automatically
Expanded/collapsed	`aria-expanded`	Radix accordion/dropdown handle automatically
Selected tab	`aria-selected="true"`	Radix Tabs handle automatically

Why ARIA over data-qa-state: ARIA must already be correct for accessibility. Reusing it for tests avoids duplicate maintenance + ensures tests catch a11y regressions.

Playwright assertion example:

const submit = page.getByTestId('user-create__form__submit');
await expect(submit).not.toHaveAttribute('aria-busy', 'true');   // not loading
await submit.click();
await expect(submit).toHaveAttribute('aria-busy', 'true');       // now loading
await expect(page.getByTestId('user-create__form__email-input'))
  .toHaveAttribute('aria-invalid', 'true');                       // validation triggered

Enforcement (optional iter 2): ESLint custom rule require-testid-on-action-buttons matching <Button> components with non-disabled onClick or type="submit".

12.5 Animation Guidelines

12.5.1 Allowed Animation Patterns

Pattern	Timing	Easing	Where
Page transition fade	200ms	ease-out	Route change
Drawer/Sheet slide-in	250ms	ease-out	Edit panels from right
Modal scale + fade	180ms	ease-out	Confirm dialogs
Toast slide + fade	200ms in / 150ms out	ease-out	Top-right notifications
List item enter	220ms	ease-out	New row in table after create
List item exit	180ms	ease-in	Row removed
Card hover lift	150ms	ease-out	Scale 1.01 + shadow elevation
Button press	80ms	ease-out	Scale 0.98 brief tap feedback
Loading skeleton shimmer	1500ms loop	linear	While data loads
Focus ring fade	100ms	ease-out	Keyboard focus indicator

12.5.2 Forbidden Patterns

Animation duration > 400ms for UI feedback (feels laggy)
Parallax scrolling (motion sickness, distracting)
3D transforms (overhead, gimmicky)
Animation on every scroll event (jank)
Decorative animations on critical paths (blocks interaction)
Auto-playing video/loop animations in dashboard chrome

12.5.3 Motion Code Example

import { motion, AnimatePresence } from 'motion/react';

<AnimatePresence mode="wait">
  {isOpen && (
    <motion.div
      key="drawer"
      initial={{ x: '100%' }}
      animate={{ x: 0 }}
      exit={{ x: '100%' }}
      transition={{ duration: 0.25, ease: 'easeOut' }}
      className="fixed right-0 top-0 h-full w-96 bg-card"
      data-testid="user-edit__drawer"
    >
      {/* drawer content */}
    </motion.div>
  )}
</AnimatePresence>

12.6 Brand Color Palette

Decision derived from two mocks user shared (2026-05-26):

Admin dashboard wireframe → blue chrome, clean white, Linear/Stripe-anchor style
Marketing landing page → yellow (XPrivate logo) + purple (decorative) + green (WhatsApp CTA), warm cream BG

Mix strategy: admin dashboard keeps blue operational chrome but pulls in yellow + purple sebagai brand touches sehingga admin tetap “feels XPrivate” tanpa overwhelm operational clarity.

Token	Hex	HSL	Use
`primary`	`#3b82f6`	`217 91% 60%`	Primary buttons, focus ring, active nav, primary CTA, breadcrumb active
`brand-yellow` (custom)	`#fbbf24`	`45 96% 56%`	XPrivate logo area, ADMIN badge fill, brand status accents in header
`accent` (secondary)	`#a78bfa`	`258 90% 76%` (muted purple)	Decorative underlines, info pills, soft highlights, card hover state
`warning`	`#f59e0b`	`38 92% 50%`	Empty state, validation warning, FM banding pending
`destructive`	`#ef4444`	`0 84% 60%`	Delete, cancel sesi, suspended status
`success` (custom)	`#10b981`	`158 64% 40%`	Approved, paid, settled, OK toasts
`background`	`#f8fafc`	slate-50	App background (NEVER pure white)
`card`	`#ffffff`	white	Card surfaces
`border`	`#e2e8f0`	slate-200	Subtle borders
`muted-foreground`	`#64748b`	slate-500	Secondary text, breadcrumbs, captions
`foreground`	`#0f172a`	slate-900	Primary text (NEVER pure black)

Usage rules:

Logo + top-of-page header chrome → yellow-forward (brand identity)
Operating chrome (nav active, primary buttons, focus rings) → blue-forward
Decorative + soft hover + info pills + non-semantic status badges → purple-forward
Semantic states → use warning / destructive / success strictly — don’t mix with brand tokens

Marketing landing site uses different ratio (yellow primary + purple secondary, B2C feel). Admin dashboard uses blue primary + yellow brand accents (operational feel). Both share same palette tokens but emphasis differs.

Dark mode: Defer iter 2+. When activated:

Background → slate-950 (#020617)
Brand yellow keeps similar saturation (slight desat for low-luminance)
Blue primary slightly brighter
Purple accent stays same

12.7 UI Tone (general)

Aspect	Choice
Avoid	Pure white (`#fff`) and pure black (`#000`) — feels harsh. Use `slate-50` / `slate-900` instead
Spacing scale	Tailwind default (4/8/12/16/24/32/48 px) — generous whitespace
Typography	Inter (body + headings). Body 14-15px. Headings bold, generous line-height
Border radius	Medium rounded (8-10px) — friendly, not sharp/industrial
Shadow elevation	Subtle (`shadow-sm` / `shadow-md`). Reserve `shadow-lg` for modals/elevated panels
Density	Comfortable. Table cells 12-14px padding. Cards 16-20px padding.
Focus indicators	Always visible (a11y). Ring 2px in `primary` with offset.

12.8 Component Categories (Suggested folder structure)

apps/web/src/components/
├── ui/                           # shadcn/ui components (copy-pasted, owned by us)
│   ├── button.tsx
│   ├── card.tsx
│   ├── dialog.tsx
│   ├── form.tsx
│   └── ...
├── motion/                       # animation primitives (wrap motion library)
│   ├── slide-in.tsx              # standardized slide-in for drawers/sheets
│   ├── fade.tsx                  # standardized fade transitions
│   └── stagger-list.tsx          # list item stagger animation
├── layouts/                      # page layouts
│   ├── dashboard-layout.tsx      # sidebar + topbar + content
│   └── auth-layout.tsx           # centered card for sign-in
├── features/                     # feature-specific components
│   ├── users/
│   │   ├── user-create-form.tsx
│   │   ├── user-list-table.tsx
│   │   └── user-archive-dialog.tsx
│   ├── sessions/
│   ├── invoices/
│   └── ...
└── shared/                       # cross-feature reusables
    ├── confirm-dialog.tsx        # used by many features
    ├── data-table.tsx            # @tanstack/react-table wrapper
    └── filter-bar.tsx            # nuqs-bound filter inputs

12.9 Deferred to Iter 2+

Concern	Decision	Why deferred
Storybook	Defer iter 2+	Iter 1 component library masih small (mostly shadcn copy-paste). Setup cost + ongoing maintenance not justified until ≥10 custom XPrivate-specific composed components exist. Activate when component library matures + design review process needs visual reference.
Visual regression testing (Percy / Chromatic / Playwright `toHaveScreenshot`)	Defer iter 2+	Iter 1 UI still rapidly evolving — VRT would create constant false-positive churn (“intentional change”). Activate when admin dashboard stabilizes + intentional changes become rare enough that diffs signal real regressions.
Dark mode	Defer iter 2+	Iter 1 = single light theme. Token palette designed dark-mode-ready (see §12.6). Activation = swap CSS variable values + add toggle.
i18n EN locale active	Iter 2+ swap	BI primary iter 1 (per scope §16 locked). EN key file present iter 1 (Paraglide structure DB-ready) — swap iter 2.

12.10 Cross-References

shadcn/ui setup + theming → see Step 3 (bootstrap codebase) for components.json config
data-testid ESLint rule → defer to Step 5 (tooling) or Step 6 (rules)
Animation primitives in motion/ folder → seeded as stubs in Step 3
Brand color tokens → applied as Tailwind CSS variables in apps/web/src/styles/globals.css (Step 3)

13. Pagination Standard

Cross-cutting standard for ALL listing endpoints in /api/v1/*. Hybrid cursor + offset strategy: cursor for performance on monotonic sort, offset fallback for non-monotonic sort, FE-side page-number hint accepting minor drift on deep-page reload.

13.1 Goals & Philosophy

Goals:

Fast pagination on deep pages (cursor seek = O(log n) vs offset skip = O(n))
Display “Page X of Y” UX (admin expects classic page-number affordance)
Stable, predictable across pagination clicks (no skipped/duplicated items)
Acceptable trade-offs on rare edge cases (Hari+1 reload of deep page)

Philosophy: Hybrid by design — cursor is the performant primary mechanism, offset is the UX hint + fallback for non-monotonic sort. Search bar (post-MVP) handles “find specific item” use case, reducing pressure for deep-page navigation.

Accepted trade-offs:

Page-number display can drift on Hari+1 reload at page > 1 (items deleted before cursor → “Page 5 of N” might actually be page 4 content-wise). Acceptable because Page 1 is always fresh + search bar handles the “find” use case.
Deep-page navigation by non-monotonic sort (e.g., name ASC page 50) is slow (offset skip). Acceptable because admin pakai search bar for this.

13.2 Request Schema

import { z } from 'zod';

export const paginationParamsSchema = z.object({
  next: z.uuid().optional(),                            // cursor for forward
  prev: z.uuid().optional(),                            // cursor for backward
  offset: z.coerce.number().int().min(0).default(0),    // FE-side page hint, default 0
  limit: z.coerce.number().int().positive().optional(), // endpoint clamps default + max
  sort: z.string().optional(),                          // endpoint constrains to enum
  order: z.enum(['asc', 'desc']).optional(),
});

export type PaginationParams = z.infer<typeof paginationParamsSchema>;

Field semantics:

Field	Behavior
`next`	UUID of last item from previous page. Backend uses for `WHERE id > ${next}` predicate (when sort is monotonic).
`prev`	UUID of first item from previous page. Backend reverses query, app reverses items back for display.
`next` AND `prev` both given	`next` wins silently. No 400 error — backend ignores `prev`.
`offset`	FE-side page hint. Default `0`. Backend uses for offset query when cursor strategy not active. Also returned for FE page calc.
`limit`	Default per endpoint. `0` / negative → silently coerced to default. Exceeds endpoint max → silently clamped to max.
`sort`	Default per endpoint. Invalid value → silently coerced to default (no 400).
`order`	If `sort` given without `order` → default `asc`. If both unset → endpoint default.

13.3 Response Schema

export const paginationResponseSchema = <T extends z.ZodTypeAny>(itemSchema: T) =>
  z.object({
    items: z.array(itemSchema),
    next: z.uuid().nullable(),                          // null = no more pages forward
    prev: z.uuid().nullable(),                          // null = at logical page 1
    total: z.number().int().min(0),                     // count WITHOUT cursor filter
    limit: z.number().int().positive(),                 // resolved (default if not sent)
    sort: z.string(),                                   // resolved
    order: z.enum(['asc', 'desc']),                     // resolved
  });

Why no offset in response: FE keeps its own offset hint client-side. Backend doesn’t authoritatively echo offset since backend doesn’t use offset when cursor is active.

13.4 Backend Strategy Auto-Select

Backend chooses strategy per request based on resolved sort field:

Sort field category	Examples	Strategy	Why
Monotonic / id-correlated	`id`, `created_at`, `updated_at`, `started_at`, `period_end`, `occurred_at`	Cursor pagination active	Item insertion preserves cursor anchor (uuidv7 = time-ordered). `WHERE id > cursor` works correctly.
Non-monotonic	`name`, `email`, `slug`, `subject_name`, free-text columns	Offset pagination, cursor params silently ignored	`WHERE id > cursor` would skip/dup items when sort != id-correlated. Offset is correct but slower. Admin pakai search bar for deep navigation.

Each endpoint declares which sort fields it allows + which are monotonic. The @packages/service/pagination helper exposes a registry:

export interface SortFieldDef {
  /** Drizzle column reference */
  column: PgColumn;
  /** True when this field is monotonic relative to row insertion (uuidv7 id) */
  isMonotonic: boolean;
}

export type SortRegistry<TSort extends string> = Record<TSort, SortFieldDef>;

Endpoint usage:

const userSortRegistry = {
  created_at: { column: users.created_at, isMonotonic: true },
  updated_at: { column: users.updated_at, isMonotonic: true },
  name: { column: users.name, isMonotonic: false },
  email: { column: users.email, isMonotonic: false },
} satisfies SortRegistry<'created_at' | 'updated_at' | 'name' | 'email'>;

13.5 Backend Query Pattern

import { db } from '@packages/db/client';
import { and, asc, desc, count, gt, lt, type SQL } from 'drizzle-orm';

export async function paginatedList<TRow, TSort extends string>(opts: {
  table: PgTable;
  idColumn: PgColumn;                            // typically `id` (uuidv7)
  filter: SQL | undefined;                       // shared WHERE conditions (sans cursor)
  sortRegistry: SortRegistry<TSort>;
  sortDefault: TSort;
  orderDefault: 'asc' | 'desc';
  limitDefault: number;
  limitMax: number;
  params: PaginationParams;
}): Promise<PaginatedResult<TRow>> {
  // 1. Resolve params (apply defaults + clamp)
  const sort: TSort = opts.sortRegistry[opts.params.sort as TSort]
    ? (opts.params.sort as TSort)
    : opts.sortDefault;
  const order: 'asc' | 'desc' = opts.params.order ?? opts.orderDefault;
  const limit = Math.min(
    opts.params.limit && opts.params.limit > 0 ? opts.params.limit : opts.limitDefault,
    opts.limitMax,
  );

  // 2. Determine cursor direction (next wins if both)
  const cursor = opts.params.next
    ? { value: opts.params.next, direction: 'next' as const }
    : opts.params.prev
    ? { value: opts.params.prev, direction: 'prev' as const }
    : null;

  // 3. Auto-select strategy
  const sortDef = opts.sortRegistry[sort];
  const useCursor = cursor !== null && sortDef.isMonotonic;

  // 4. Build ordering — ALWAYS append id as final tie-breaker for stability
  const idSortDir =
    cursor?.direction === 'prev'
      ? (order === 'asc' ? 'desc' : 'asc')           // reverse base order for prev
      : order;

  const sortColumnDir =
    cursor?.direction === 'prev'
      ? (order === 'asc' ? 'desc' : 'asc')
      : order;

  const orderBy = [
    sortColumnDir === 'asc' ? asc(sortDef.column) : desc(sortDef.column),
    idSortDir === 'asc' ? asc(opts.idColumn) : desc(opts.idColumn),
  ];

  // 5. Cursor predicate (only when monotonic + cursor present)
  const cursorPredicate = useCursor
    ? (cursor!.direction === 'next'
        ? (order === 'asc' ? gt(opts.idColumn, cursor!.value) : lt(opts.idColumn, cursor!.value))
        : (order === 'asc' ? lt(opts.idColumn, cursor!.value) : gt(opts.idColumn, cursor!.value)))
    : undefined;

  // 6. Offset only when cursor NOT used
  const offset = useCursor ? 0 : opts.params.offset;

  // 7. Parallel: items query + total count (count WITHOUT cursor predicate)
  const [rawItems, [{ value: total }]] = await Promise.all([
    db
      .select()
      .from(opts.table)
      .where(and(opts.filter, cursorPredicate))
      .orderBy(...orderBy)
      .limit(limit + 1)                            // +1 to detect has-more
      .offset(offset) as Promise<TRow[]>,
    db
      .select({ value: count() })
      .from(opts.table)
      .where(opts.filter),                         // NO cursor predicate
  ]);

  // 8. Detect has-more + trim sentinel
  const hasMore = rawItems.length > limit;
  let items = hasMore ? rawItems.slice(0, limit) : rawItems;

  // 9. Reverse for prev (we queried in reversed order)
  if (cursor?.direction === 'prev') items = items.reverse();

  // 10. Compute next/prev cursors for response
  const nextCursor =
    (cursor?.direction === 'next' && hasMore) || (cursor?.direction === 'prev')
      ? (items[items.length - 1] as { id: string } | undefined)?.id ?? null
      : !cursor && hasMore
      ? (items[items.length - 1] as { id: string } | undefined)?.id ?? null
      : null;

  const prevCursor =
    cursor !== null && items.length > 0
      ? (items[0] as { id: string } | undefined)?.id ?? null
      : null;

  return {
    items,
    next: nextCursor,
    prev: prevCursor,
    total,
    limit,
    sort,
    order,
  };
}

(Sketch — real impl perlu adapt to specific Drizzle table types via generics. Endpoint wraps with concrete table + sortRegistry.)

13.6 FE Behavior

1. Initial page load (/dashboard/users):

No cursor params, no offset. Backend returns page 1 with next cursor.
FE stores: offset = 0, next = response.next, prev = null.

2. Click “Next”:

Send: ?next=${currentNext}&offset=${currentOffset + limit}&sort=...&order=...
Receive: new items + new next + new prev
Update FE state: offset = currentOffset + limit, next = response.next, prev = response.prev.

3. Click “Prev”:

Send: ?prev=${currentPrev}&offset=${max(0, currentOffset - limit)}&sort=...&order=...
Receive: items (already reversed for display) + new next + new prev
Update FE state: offset = max(0, currentOffset - limit), next = response.next, prev = response.prev.

4. Filter change (any WHERE field changes) OR Sort/Order change:

Reset cursor + offset to undefined/0. Treat as initial load.
Update URL via nuqs: drop next & prev params, set offset=0, set new filter params.

5. Page-number display (FE-computed, best-effort):

const currentPage = Math.floor(offset / limit) + 1;
const totalPages = Math.max(1, Math.ceil(total / limit));
// Display: "Halaman 5 dari 12" — may drift on Hari+1 reload, accepted per scope

6. URL state via nuqs:

const [filters, setFilters] = useQueryStates({
  next: parseAsString,
  prev: parseAsString,
  offset: parseAsInteger.withDefault(0),
  limit: parseAsInteger,
  sort: parseAsString,
  order: parseAsStringEnum(['asc', 'desc']),
  // + endpoint-specific filters (status, user_type, etc.)
});

13.7 Stale Page Number — Accepted Drift

Scenario: User at page 5 (offset=80) reloads URL Hari+1. Between T0 and T1, 10 items deleted from page 1-4.

What stays correct:

✅ items content — cursor still anchors to same time-ordered position
✅ total count — fresh at request time
✅ Navigation (Next/Prev clicks) — continue working from current position

What may drift:

⚠️ Display “Page 5 of N” — FE computed from old offset=80, but cursor actual position now ~70 in current dataset. Display says “5”, content is “4”.

Drift mitigation: Acceptable per scope (Page 1 always fresh + search bar for deep finds). Document but don’t engineer around. If becomes annoying iter 2+, add parallel cursor_position query.

13.8 Stale Cursor — Item Deleted

Scenario: Cursor value = UUID of an item that admin lain just hard-deleted.

Handling: continue as-is.

UUID comparison (WHERE id > '01H...') is purely numeric — doesn’t require the row to exist. Query returns items with id greater than the (now-missing) cursor value. Semantics preserved.

No explicit “cursor stale” detection. No 410 Gone. No fallback to page 1. Simplest path.

13.9 Search Bar Interplay (iter 2+)

When search bar active (?q=pak+andi):

Pagination strategy = relevance-ranked top N (via pg_trgm similarity score)
Backend ignores next/prev/offset/sort cursor mechanics
Returns top N most-relevant matches without traditional pagination
FE shows “Top 20 hasil pencarian” with optional “Lihat semua” → drops to traditional pagination with current filters

Search bar is iter 2+ feature (per current admin-MVP scope). DB-ready via pg_trgm GIN indexes (already in §4 extensions). Pagination standard above remains intact for non-search lists.

13.10 Cross-References

Pagination utility implementation → Step 3 (bootstrap) creates @packages/service/pagination/
FE table integration via @tanstack/react-table → see §12 (frontend conventions)
URL state via nuqs → see §12.2 library inventory
pg_trgm GIN indexes (for search bar iter 2+) → see §4 database extensions

14. Pricing Matrix Schema + Tahun Ajaran Versioning (Base + Override model)

Supersedes the old flat matrix model. See ADR-001-base-override-pricing.md for full rationale.

Design decisions locked (ADR-001):

Base + Override + Applicability inheritance model (NOT a flat 5D matrix)

SubjectLevel dropped as a pricing axis

Segmentasi + Golongan = DB rows (operator-managed, CRUD-able)

Amount = integer IDR (whole rupiah, no decimals), CHECK > 0

Pricing change audit = unified audit middleware (§2.14)

Max 1 ACTIVE academic year = service-layer guard (not DB constraint)

File split:

packages/db/src/schema/catalog.ts — master catalog: tingkats, segmentasis, kategoris, subjects, golongans

packages/db/src/schema/pricing.ts — academic_years, student_base_prices, tutor_base_prices, subject_tingkat_applicability, student_price_overrides, tutor_price_overrides

14.1 Table Inventory

#	Table	File	Purpose
1	`tingkats`	catalog.ts	Tingkat pendidikan (SD 1-6, SMP, SMA, Mahasiswa, Umum, Profesional)
2	`segmentasis`	catalog.ts	Service tier (REGULER, REGULER_PLUS, INTERNASIONAL)
3	`kategoris`	catalog.ts	Subject grouping — organizational only, NOT a pricing axis
4	`subjects`	catalog.ts	Mata pelajaran (e.g., Bahasa Mandarin, Piano, CPNS)
5	`golongans`	catalog.ts	Tutor tier (GOL_1..4 + NON_GOLONGAN, operator-managed)
6	`academic_years`	pricing.ts	Tahun Ajaran versioning container (Jul–Jun cycle)
7	`student_base_prices`	pricing.ts	Subject-agnostic student base: year × seg × tkt → amount_idr
8	`tutor_base_prices`	pricing.ts	Subject-agnostic tutor base: year × seg × tkt × gol → amount_idr
9	`subject_tingkat_applicability`	pricing.ts	Which tingkats a subject is sold at (opt-in, per year)
10	`student_price_overrides`	pricing.ts	Subject-specific student override: year × subj × seg × tkt → amount_idr
11	`tutor_price_overrides`	pricing.ts	Subject-specific tutor override: year × subj × seg × tkt × gol → amount_idr

Dropped from old model: student_pricing_matrix, tutor_pricing_matrix, subject_levels as pricing axis. Snapshot fields on lesson_sessions (captured at APPROVED) → defined in §15.

14.2 Catalog Schema (`packages/db/src/schema/catalog.ts`)

Same as before for tingkats, segmentasis, kategoris, subjects, golongans. Key notes:

tingkats: slug, label_id/label_en, education_group, sort_order, status. Unique lower-slug index.
segmentasis: slug, label_id/label_en, sort_order, status.
kategoris: slug, name_id/name_en (note name_* not label_*), sort_order, status.
subjects: kategori_id FK, code (unique), name_id/name_en, has_levels, sort_order, status.
golongans: slug, label_id/label_en, tier_rank (unique; 1=highest, NON_GOLONGAN=99), status.

subject_levels table still exists in DB for non-pricing uses; it is NOT referenced by the pricing tables.

14.3 Pricing Schema (`packages/db/src/schema/pricing.ts`)

academic_years

// status enum: 'UPCOMING' | 'ACTIVE' | 'CLOSED'
// Lifecycle: UPCOMING → ACTIVE → CLOSED
// Max 1 ACTIVE at a time (service-layer guard, not DB constraint).

export const academicYears = pgTable('academic_years', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  label:      text('label').notNull(),          // '2026/2027' — unique
  starts_on:  date('starts_on').notNull(),      // '2026-07-01'
  ends_on:    date('ends_on').notNull(),         // '2027-06-30'
  status:     academicYearStatusEnum('status').notNull().default('UPCOMING'),
  created_by: uuid('created_by').references(() => users.id),
  updated_by: uuid('updated_by').references(() => users.id),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});
// Indexes: uq_academic_years_label (label), idx_academic_years_status (status)

student_base_prices

Subject-agnostic student side. Unique: (academic_year_id, segmentasi_id, tingkat_id).

// (year × segmentasi × tingkat) → amount_idr
// CHECK amount_idr > 0
export const studentBasePrices = pgTable('student_base_prices', { ... });

tutor_base_prices

Subject-agnostic tutor side. Unique: (academic_year_id, segmentasi_id, tingkat_id, golongan_id).

// (year × segmentasi × tingkat × golongan) → amount_idr
// CHECK amount_idr > 0
export const tutorBasePrices = pgTable('tutor_base_prices', { ... });

subject_tingkat_applicability

Controls which tingkats a subject is available for in a given year. Opt-in model: a subject is sellable at a tingkat only if a row exists AND is_available = true. Absence = not sold.

// Unique: (academic_year_id, subject_id, tingkat_id)
// is_available boolean default true
// Clearing all = admin sets is_available=false for all rows (or deletes them)
export const subjectTingkatApplicability = pgTable('subject_tingkat_applicability', { ... });

student_price_overrides

Subject-specific overrides. Only rows that differ from Base are stored (sparse). Unique: (academic_year_id, subject_id, segmentasi_id, tingkat_id).

// CHECK amount_idr > 0
export const studentPriceOverrides = pgTable('student_price_overrides', { ... });

tutor_price_overrides

Subject-specific tutor overrides. Unique: (academic_year_id, subject_id, segmentasi_id, tingkat_id, golongan_id).

// CHECK amount_idr > 0
export const tutorPriceOverrides = pgTable('tutor_price_overrides', { ... });

All pricing tables carry: created_by, updated_by, created_at, updated_at (timestamptz, manual updated_at bump per db-conventions.md). PK uuid + uuidv7().

14.4 Price Resolution (`resolvePrice` — single source of truth)

For a given (academic_year_id, subject_id, segmentasi_id, tingkat_id [, golongan_id]):

1. Applicability gate:
   If subject_tingkat_applicability row is absent OR is_available = false
   → NOT SOLD (return null — no price shown, cell grayed out)

2. Override lookup:
   If student_price_overrides (or tutor_price_overrides) row exists for this combo
   → use override amount_idr  (source = 'OVERRIDE')

3. Base lookup:
   If student_base_prices (or tutor_base_prices) row exists for (year × seg × tkt [× gol])
   → use base amount_idr  (source = 'BASE')

4. Unpriced:
   → return null with source = null  (cell empty — admin must fill Base or Override)

Branches 1+2 fetched in parallel; Base fetched lazily (only when branch 2 misses). Encapsulated in resolveStudentPrice / resolveTutorPrice in packages/service/src/service/web/pricing/resolve-price.ts. Covered by unit tests (all 4 branches each).

The grid endpoint (getPricingMatrix) returns per cell: amount, source ('OVERRIDE' | 'BASE' | null), base_amount (for strike-through display on override cells), and — tutor side — honor_amount (tutor resolved price) + margin_pct + warn (true if margin ≤ 0).

“BASE” subject context: when subject_id = 'BASE', the grid skips applicability + override lookup and reads only Base rows directly. Source always 'BASE' or null.

14.5 “Clone Tahun Ajaran” Operation

Service op: packages/service/src/service/web/academic-years/clone-academic-year.ts.

One DB transaction:

INSERT academic_years (label, starts_on, ends_on, status = 'UPCOMING', created_by).
Fetch + remap all rows from student_base_prices → insert with new academic_year_id.
Fetch + remap all rows from tutor_base_prices → same.
Fetch + remap all rows from subject_tingkat_applicability → same.
Fetch + remap all rows from student_price_overrides → same.
Fetch + remap all rows from tutor_price_overrides → same.
Audit: action = 'academic_year:clone', after = { source_id, rows_copied: { student_base, tutor_base, applicability, student_overrides, tutor_overrides } }.

Rules:

Rejected if source year has 0 rows across all 5 pricing tables (“Cannot clone an empty year.”).
Does NOT close the source year.
New year starts UPCOMING; admin activates explicitly.

14.6 Key Business Rules (enforced in service layer)

Rule	Enforcement
`amount_idr > 0`	DB `CHECK` constraint on all 4 price tables
No duplicate combo per year	`UNIQUE` composite index per table
Subject/Tingkat soft-delete	`onDelete: 'restrict'` on FK; service sets `status = 'ARCHIVED'`; FK preserved in pricing rows
Mid-year price edits allowed	Admin can upsert any pricing row mid-year; snapshot on `lesson_sessions` is immune
Max 1 ACTIVE academic year	Service guard in `activate-academic-year.ts`: queries for any ACTIVE year with `id != input.id` inside tx; throws with label+id if found
Pricing audit trail	Unified audit middleware (§2.14) captures before/after JSON on each write
Applicability opt-in	Absence of `subject_tingkat_applicability` row = subject not sold at that tingkat

14.7 Snapshot Reference (cross-ref §15)

At lesson_sessions state transition REQUESTED → APPROVED, the schedule entity captures the resolved price (output of resolvePrice) in the same transaction as the status update:

// Fields defined in full in §15 (Sesi + Schedule schema):
amount_student_idr:    integer   // snapshot of resolveStudentPrice() output — immune to later edits
student_price_source:  text      // 'OVERRIDE' | 'BASE' — which branch resolved
student_price_ref_id:  uuid      // FK → student_price_overrides.id OR student_base_prices.id (traceability)
amount_tutor_idr:      integer   // snapshot of resolveTutorPrice() output
tutor_price_source:    text      // 'OVERRIDE' | 'BASE'
tutor_price_ref_id:    uuid      // FK → tutor_price_overrides.id OR tutor_base_prices.id
mode_surcharge_idr:    integer   // nullable — per-sesi escape hatch override

Why snapshot resolved amount + source + ref_id: amount = immutable economic record; source + ref_id = traceability to the exact pricing row used (supports audit queries like “how many sessions used override X”).

The old student_pricing_matrix_id / tutor_pricing_matrix_id FK fields do not exist — those tables were replaced by the Base + Override model.

See ADR-001-base-override-pricing.md for full rationale.

§15 Sesi State Machine + Schedule Schema (Step 2.12)

15.1 Entity Overview

Two tables:

Table	Purpose
`schedules`	Recurring jadwal tetap template: student × tutor × subject × day/time
`lesson_sessions`	Individual sesi occurrence (from a schedule or one-off)

A lesson_session is always the source of truth for billing, state, and audit. A schedule is optional — admin can create one-off sessions without a recurring template.

15.2 Sesi State Machine

                    ┌──────────────────────────────────┐
                    │         REQUESTED                │  ← initial state
                    └──────────┬───────────────────────┘
                               │
              ┌────────────────┴────────────────┐
              ▼                                 ▼
       ┌─────────────┐                  ┌─────────────┐
       │  APPROVED   │                  │  REJECTED   │ ← terminal
       └──┬──────────┘                  └─────────────┘
          │
     ┌────┼─────────────────────────────────────────────┐
     │    │                                             ▼
     │    ▼                                     ┌─────────────────┐
     │  ┌──────────────┐                        │   CANCELLED     │ ← terminal
     │  │ RESCHEDULED  │ ← terminal             └─────────────────┘
     │  │ (creates new │   (creates new REQUESTED
     │  │  REQUESTED)  │    with rescheduled_from_id)
     │  └──────────────┘
     │
     ▼
┌────────────────────┐
│    IN_PROGRESS     │  ← manual (tutor "Mulai Sesi") or future CF Cron auto
└──┬────────┬────────┘
   │        │
   ▼        ▼                    ▼
┌──────────────┐  ┌───────────────────┐  ┌───────────────────────┐
│  COMPLETED   │  │ NO_SHOW_STUDENT   │  │   NO_SHOW_TUTOR       │
│  (terminal)  │  │ (terminal)        │  │   (terminal)          │
└──────────────┘  └───────────────────┘  └───────────────────────┘

Terminal states: COMPLETED, REJECTED, CANCELLED, RESCHEDULED, NO_SHOW_STUDENT, NO_SHOW_TUTOR

Billing implications:

Terminal State	Student Charged	Tutor Paid
`COMPLETED`	Yes — `amount_student_final_idr`	Yes — `amount_tutor_final_idr`
`NO_SHOW_STUDENT`	Yes — per cancellation policy (§2.13)	Partial / protected fee
`NO_SHOW_TUTOR`	No	No (tutor penalized)
`CANCELLED`	Depends on cancellation_policy_bracket	Depends
`REJECTED`	No	No
`RESCHEDULED`	No (session continues as new)	No

Snapshot timing: Pricing snapshot captured at REQUESTED → APPROVED transition. Admin selects pricing rows at approve time (or service layer auto-matches by lookup).

15.3 Drizzle Schema: `packages/db/schema/scheduling.ts`

import { pgTable, pgEnum, uuid, text, integer, smallint,
         timestamp, time, date, index, check }           from 'drizzle-orm/pg-core';
import { relations, sql, type AnyPgColumn }              from 'drizzle-orm';
import { users }                    from './auth';
import { subjects, subject_levels, tingkats,
         segmentasis, academic_years,
         student_pricing_matrix, tutor_pricing_matrix }  from './catalog';

// ─── Enums ────────────────────────────────────────────────────────────────────

export const sessionStatusEnum = pgEnum('session_status', [
  'REQUESTED', 'APPROVED', 'IN_PROGRESS', 'COMPLETED',
  'REJECTED', 'CANCELLED', 'RESCHEDULED',
  'NO_SHOW_STUDENT', 'NO_SHOW_TUTOR',
]);

export const sessionModeEnum   = pgEnum('session_mode',    ['ONLINE', 'OFFLINE']);
export const scheduleStatusEnum = pgEnum('schedule_status', ['ACTIVE', 'PAUSED', 'ENDED']);

// ─── Schedules (Recurring Template) ──────────────────────────────────────────

export const schedules = pgTable('schedules', {
  id:               uuid('id').primaryKey().default(sql`uuidv7()`),
  student_id:       uuid('student_id').notNull().references(() => users.id,          { onDelete: 'restrict' }),
  tutor_id:         uuid('tutor_id').notNull().references(() => users.id,            { onDelete: 'restrict' }),
  subject_id:       uuid('subject_id').notNull().references(() => subjects.id,       { onDelete: 'restrict' }),
  subject_level_id: uuid('subject_level_id').references(() => subject_levels.id,    { onDelete: 'restrict' }),
  tingkat_id:       uuid('tingkat_id').notNull().references(() => tingkats.id,       { onDelete: 'restrict' }),
  segmentasi_id:    uuid('segmentasi_id').notNull().references(() => segmentasis.id, { onDelete: 'restrict' }),
  academic_year_id: uuid('academic_year_id').notNull().references(() => academic_years.id, { onDelete: 'restrict' }),

  session_mode: sessionModeEnum('session_mode').notNull(),
  day_of_week:  smallint('day_of_week').notNull(),        // ISO: 1=Mon … 7=Sun
  start_time:   time('start_time').notNull(),             // e.g. '15:00:00'
  duration_ms:  integer('duration_ms').notNull(),         // e.g. 5400000 (90 min)
  location:     text('location'),                         // offline: address / landmark
  meeting_url:  text('meeting_url'),                      // online: Zoom/Meet link

  status:         scheduleStatusEnum('status').notNull().default('ACTIVE'),
  effective_from: date('effective_from').notNull(),
  effective_to:   date('effective_to'),                   // null = open-ended

  notes:      text('notes'),
  created_by: uuid('created_by').references(() => users.id),
  updated_by: uuid('updated_by').references(() => users.id),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_schedules_student').on(t.student_id),
  index('idx_schedules_tutor').on(t.tutor_id),
  index('idx_schedules_ay').on(t.academic_year_id),
  index('idx_schedules_status').on(t.status),
  check('chk_schedules_duration_positive', sql`${t.duration_ms} > 0`),
  check('chk_schedules_day_of_week',       sql`${t.day_of_week} BETWEEN 1 AND 7`),
  check('chk_schedules_effective_range',
    sql`${t.effective_to} IS NULL OR ${t.effective_to} >= ${t.effective_from}`),
]);

// ─── Lesson Sessions ──────────────────────────────────────────────────────────

export const lesson_sessions = pgTable('lesson_sessions', {
  id: uuid('id').primaryKey().default(sql`uuidv7()`),

  // ── Origin ──
  schedule_id:         uuid('schedule_id').references(() => schedules.id, { onDelete: 'restrict' }),
  // Self-referencing FK: lazy callback required in Drizzle to avoid circular reference
  rescheduled_from_id: uuid('rescheduled_from_id')
                         .references((): AnyPgColumn => lesson_sessions.id, { onDelete: 'set null' }),

  // ── Participants ──
  student_id: uuid('student_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  tutor_id:   uuid('tutor_id').notNull().references(() => users.id,   { onDelete: 'restrict' }),

  // ── Subject context (stable FKs, denormalized for query perf) ──
  subject_id:       uuid('subject_id').notNull().references(() => subjects.id,       { onDelete: 'restrict' }),
  subject_level_id: uuid('subject_level_id').references(() => subject_levels.id,    { onDelete: 'restrict' }),
  tingkat_id:       uuid('tingkat_id').notNull().references(() => tingkats.id,       { onDelete: 'restrict' }),
  segmentasi_id:    uuid('segmentasi_id').notNull().references(() => segmentasis.id, { onDelete: 'restrict' }),
  academic_year_id: uuid('academic_year_id').notNull().references(() => academic_years.id, { onDelete: 'restrict' }),

  // ── Schedule details ──
  session_mode: sessionModeEnum('session_mode').notNull(),
  scheduled_at: timestamp('scheduled_at', { withTimezone: true }).notNull(),
  duration_ms:  integer('duration_ms').notNull(),
  location:     text('location'),
  meeting_url:  text('meeting_url'),

  // ── State machine ──
  status: sessionStatusEnum('status').notNull().default('REQUESTED'),

  // ── State transition timestamps ──
  requested_at:   timestamp('requested_at',   { withTimezone: true }).notNull().defaultNow(),
  approved_at:    timestamp('approved_at',    { withTimezone: true }),
  started_at:     timestamp('started_at',     { withTimezone: true }),
  completed_at:   timestamp('completed_at',   { withTimezone: true }),
  rejected_at:    timestamp('rejected_at',    { withTimezone: true }),
  cancelled_at:   timestamp('cancelled_at',   { withTimezone: true }),
  no_show_at:     timestamp('no_show_at',     { withTimezone: true }),
  rescheduled_at: timestamp('rescheduled_at', { withTimezone: true }),

  // ── Transition actors ──
  requested_by:        uuid('requested_by').notNull().references(() => users.id),
  approved_by:         uuid('approved_by').references(() => users.id),
  rejected_by:         uuid('rejected_by').references(() => users.id),
  rejection_reason:    text('rejection_reason'),
  cancelled_by:        uuid('cancelled_by').references(() => users.id),
  cancellation_reason: text('cancellation_reason'),
  // FK to cancellation policy bracket resolved at cancel time (§2.13)
  cancellation_policy_bracket_id: uuid('cancellation_policy_bracket_id'),  // FK added in §2.13 migration

  // ── Pricing snapshot (captured at REQUESTED → APPROVED) ──
  student_pricing_matrix_id: uuid('student_pricing_matrix_id')
                               .references(() => student_pricing_matrix.id, { onDelete: 'restrict' }),
  tutor_pricing_matrix_id:   uuid('tutor_pricing_matrix_id')
                               .references(() => tutor_pricing_matrix.id,   { onDelete: 'restrict' }),
  amount_student_idr:       integer('amount_student_idr'),        // null until APPROVED
  amount_tutor_idr:         integer('amount_tutor_idr'),          // null until APPROVED
  mode_surcharge_idr:       integer('mode_surcharge_idr'),        // nullable per-sesi admin override
  // Stored computed at approve time: amount_X_idr + COALESCE(mode_surcharge_idr, 0)
  amount_student_final_idr: integer('amount_student_final_idr'),
  amount_tutor_final_idr:   integer('amount_tutor_final_idr'),

  // ── Notes ──
  student_notes: text('student_notes'),
  tutor_notes:   text('tutor_notes'),
  admin_notes:   text('admin_notes'),

  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_ls_student').on(t.student_id),
  index('idx_ls_tutor').on(t.tutor_id),
  index('idx_ls_status').on(t.status),
  index('idx_ls_scheduled_at').on(t.scheduled_at),
  index('idx_ls_ay').on(t.academic_year_id),
  index('idx_ls_schedule').on(t.schedule_id),
  index('idx_ls_tutor_day').on(t.tutor_id, t.scheduled_at),       // calendar view
  index('idx_ls_student_status').on(t.student_id, t.status),      // student dashboard
  index('idx_ls_rescheduled_from').on(t.rescheduled_from_id),
  check('chk_ls_duration_positive', sql`${t.duration_ms} > 0`),
  // Tutor double-booking guard via EXCLUDE requires raw SQL migration (§15.4)
]);

15.4 Tutor Double-Booking Guard (Raw SQL Migration)

Drizzle does not yet support EXCLUDE constraints natively. Add as a separate migration step after the table is created. Requires btree_gist extension (enabled in initial migration per §4):

-- Migration: add_tutor_no_overlap_constraint
ALTER TABLE lesson_sessions
  ADD CONSTRAINT excl_ls_tutor_no_overlap
  EXCLUDE USING gist (
    tutor_id WITH =,
    tstzrange(
      scheduled_at,
      scheduled_at + (duration_ms || ' milliseconds')::interval
    ) WITH &&
  )
  WHERE (status NOT IN ('REJECTED', 'CANCELLED', 'RESCHEDULED'));

Student double-booking: intentionally not constrained at DB level (admin service-layer warning only).

15.5 Drizzle Relations

export const schedulesRelations = relations(schedules, ({ one, many }) => ({
  student:       one(users,          { fields: [schedules.student_id],       references: [users.id],          relationName: 'schedule_student' }),
  tutor:         one(users,          { fields: [schedules.tutor_id],         references: [users.id],          relationName: 'schedule_tutor'   }),
  subject:       one(subjects,       { fields: [schedules.subject_id],       references: [subjects.id]        }),
  subject_level: one(subject_levels, { fields: [schedules.subject_level_id], references: [subject_levels.id]  }),
  tingkat:       one(tingkats,       { fields: [schedules.tingkat_id],       references: [tingkats.id]        }),
  segmentasi:    one(segmentasis,    { fields: [schedules.segmentasi_id],    references: [segmentasis.id]     }),
  academic_year: one(academic_years, { fields: [schedules.academic_year_id], references: [academic_years.id]  }),
  sessions:      many(lesson_sessions),
}));

export const lessonSessionsRelations = relations(lesson_sessions, ({ one, many }) => ({
  schedule: one(schedules, { fields: [lesson_sessions.schedule_id], references: [schedules.id] }),

  rescheduled_from: one(lesson_sessions, {
    fields: [lesson_sessions.rescheduled_from_id],
    references: [lesson_sessions.id],
    relationName: 'reschedule_chain',
  }),
  rescheduled_to: many(lesson_sessions, { relationName: 'reschedule_chain' }),

  student: one(users, { fields: [lesson_sessions.student_id], references: [users.id], relationName: 'ls_student'      }),
  tutor:   one(users, { fields: [lesson_sessions.tutor_id],   references: [users.id], relationName: 'ls_tutor'        }),

  subject:        one(subjects,       { fields: [lesson_sessions.subject_id],        references: [subjects.id]        }),
  subject_level:  one(subject_levels, { fields: [lesson_sessions.subject_level_id],  references: [subject_levels.id]  }),
  tingkat:        one(tingkats,       { fields: [lesson_sessions.tingkat_id],         references: [tingkats.id]        }),
  segmentasi:     one(segmentasis,    { fields: [lesson_sessions.segmentasi_id],      references: [segmentasis.id]     }),
  academic_year:  one(academic_years, { fields: [lesson_sessions.academic_year_id],   references: [academic_years.id]  }),

  student_pricing: one(student_pricing_matrix, { fields: [lesson_sessions.student_pricing_matrix_id], references: [student_pricing_matrix.id] }),
  tutor_pricing:   one(tutor_pricing_matrix,   { fields: [lesson_sessions.tutor_pricing_matrix_id],   references: [tutor_pricing_matrix.id]   }),

  requested_by_user: one(users, { fields: [lesson_sessions.requested_by], references: [users.id], relationName: 'ls_requested_by' }),
  approved_by_user:  one(users, { fields: [lesson_sessions.approved_by],  references: [users.id], relationName: 'ls_approved_by'  }),
  rejected_by_user:  one(users, { fields: [lesson_sessions.rejected_by],  references: [users.id], relationName: 'ls_rejected_by'  }),
  cancelled_by_user: one(users, { fields: [lesson_sessions.cancelled_by], references: [users.id], relationName: 'ls_cancelled_by' }),
}));

15.6 Service Layer Contract

All transitions in @packages/service/src/sessions/transitions.ts. Service does NOT handle auth; caller uses requirePermission(ctx, 'session:approve') before calling.

// APPROVE — captures pricing snapshot
interface ApproveSessionInput {
  session_id:                string;
  actor_id:                  string;
  student_pricing_matrix_id: string | null;
  tutor_pricing_matrix_id:   string | null;
  mode_surcharge_idr?:       number;
}
// 1. Assert status === 'REQUESTED'
// 2. Fetch pricing rows; validate amounts > 0
// 3. amount_X_final = amount_X + (mode_surcharge ?? 0)
// 4. UPDATE status='APPROVED', approved_at, approved_by, all snapshot fields
// 5. Audit middleware captures before/after

// CANCEL
interface CancelSessionInput {
  session_id:                       string;
  actor_id:                         string;
  reason:                           string;
  cancellation_policy_bracket_id?:  string;
}
// 1. Assert status in ['REQUESTED', 'APPROVED']
// 2. UPDATE status='CANCELLED', cancelled_at, cancelled_by, reason, bracket_id

// RESCHEDULE (atomic)
// 1. UPDATE original: status='RESCHEDULED', rescheduled_at=now()
// 2. INSERT new lesson_session: status='REQUESTED', rescheduled_from_id=original.id
// All in one DB transaction

15.7 Key Business Rules

Rule	Enforcement
Snapshot only at APPROVED	Service: pricing fields populated only in approve transition
`amount_X_final = amount_X + COALESCE(surcharge, 0)`	Service: computed and stored at approve
Tutor no double-booking	DB `EXCLUDE` constraint (§15.4)
Cannot cancel after IN_PROGRESS	Service guard: reject if status not in `[REQUESTED, APPROVED]`
Reschedule = atomic new REQUESTED	Service: transaction — old→RESCHEDULED, new inserted
`cancellation_policy_bracket_id` FK	Added via separate migration after §2.13 defines that table

§16 Cancellation Policy Schema (Step 2.13)

Source of truth: extra/2026-05-25-xprivate-tutor-cancel-policy-v1.md. Option (b) LOCKED.

16.1 Table Inventory

Table	Purpose
`cancellation_policy_configs`	Versioned policy header (TUTOR_CANCEL or SISWA_CANCEL)
`cancellation_policy_brackets`	Penalty brackets per lead-time window
`tutor_strikes`	Per-tutor strike log with 90-day rolling decay
`app_configs`	Key-value store for operational tunables (strike decay days, FM appeal window, etc.)

Snapshot semantic: At REQUESTED → APPROVED, lesson_sessions.cancellation_policy_bracket_id stores the active bracket FK. Cancel calculation uses that bracket — immune from later policy changes. Migration for FK: added in this section (§16.3).

Immutability rule: Brackets are never edited after creation. Admin changes = RETIRE old config + create new ACTIVE config with new brackets.

16.2 Drizzle Schema: `packages/db/schema/cancellation.ts`

import { pgTable, pgEnum, uuid, text, integer, boolean,
         timestamp, date, numeric, index, uniqueIndex, check } from 'drizzle-orm/pg-core';
import { relations, sql }                                        from 'drizzle-orm';
import { users }         from './auth';
import { lesson_sessions } from './scheduling';

// ─── Enums ────────────────────────────────────────────────────────────────────

export const policyTypeEnum  = pgEnum('cancellation_policy_type',   ['TUTOR_CANCEL', 'SISWA_CANCEL']);
export const policyStatusEnum = pgEnum('cancellation_policy_status', ['ACTIVE', 'RETIRED']);

// ─── Cancellation Policy Config (versioned header) ────────────────────────────

export const cancellation_policy_configs = pgTable('cancellation_policy_configs', {
  id:              uuid('id').primaryKey().default(sql`uuidv7()`),
  policy_type:     policyTypeEnum('policy_type').notNull(),
  effective_from:  date('effective_from').notNull(),
  effective_until: date('effective_until'),               // null = currently active
  status:          policyStatusEnum('status').notNull().default('ACTIVE'),
  notes:           text('notes'),
  created_by:      uuid('created_by').references(() => users.id),
  updated_by:      uuid('updated_by').references(() => users.id),
  created_at:      timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:      timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_cpconfig_type_status').on(t.policy_type, t.status),
  // Enforced at service layer: only one ACTIVE config per policy_type
  // (partial unique index below covers it at DB level)
  uniqueIndex('uq_cpconfig_one_active_per_type')
    .on(t.policy_type)
    .where(sql`${t.status} = 'ACTIVE'`),
]);

// ─── Cancellation Policy Brackets ─────────────────────────────────────────────

export const cancellation_policy_brackets = pgTable('cancellation_policy_brackets', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  config_id:  uuid('config_id').notNull().references(() => cancellation_policy_configs.id, { onDelete: 'restrict' }),
  sort_order: integer('sort_order').notNull(),             // ascending = shorter lead-time = higher penalty
  // Lead-time window: applies when (hours_before_session <= threshold_hours)
  // Use numeric for precise hour fractions (e.g. 0.5 = 30 min)
  threshold_hours: numeric('threshold_hours', { precision: 5, scale: 2 }).notNull(),

  // SISWA_CANCEL fields (null when policy_type = TUTOR_CANCEL)
  charge_pct_siswa:   integer('charge_pct_siswa'),        // 0-100: % of session cost charged to student
  honor_pct_tutor:    integer('honor_pct_tutor'),         // 0-100: % of tutor honor paid anyway

  // TUTOR_CANCEL fields (null when policy_type = SISWA_CANCEL)
  substitution_denda_pct: integer('substitution_denda_pct'), // 0-100: % of 1-sesi cost as denda
  base_penalty_enabled:   boolean('base_penalty_enabled').notNull().default(false),
  // base_penalty = zero pay for cancelled sesi (always true for TUTOR_CANCEL Option β;
  //   stored here for configurability even though policy locks it true)

  strike_added: integer('strike_added').notNull().default(0),
  notes:        text('notes'),
  created_at:   timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:   timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_cpbracket_config').on(t.config_id),
  uniqueIndex('uq_cpbracket_sort_order').on(t.config_id, t.sort_order),
  check('chk_cpbracket_threshold_positive', sql`${t.threshold_hours} > 0`),
  check('chk_cpbracket_charge_pct_range',
    sql`${t.charge_pct_siswa} IS NULL OR (${t.charge_pct_siswa} >= 0 AND ${t.charge_pct_siswa} <= 100)`),
  check('chk_cpbracket_honor_pct_range',
    sql`${t.honor_pct_tutor} IS NULL OR (${t.honor_pct_tutor} >= 0 AND ${t.honor_pct_tutor} <= 100)`),
  check('chk_cpbracket_denda_pct_range',
    sql`${t.substitution_denda_pct} IS NULL OR (${t.substitution_denda_pct} >= 0 AND ${t.substitution_denda_pct} <= 100)`),
  check('chk_cpbracket_strike_non_negative', sql`${t.strike_added} >= 0`),
]);

// ─── Tutor Strikes ────────────────────────────────────────────────────────────

export const strikeStatusEnum = pgEnum('strike_status', ['ACTIVE', 'EXPIRED', 'WAIVED']);

export const tutor_strikes = pgTable('tutor_strikes', {
  id:               uuid('id').primaryKey().default(sql`uuidv7()`),
  tutor_id:         uuid('tutor_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  lesson_session_id: uuid('lesson_session_id').notNull().references(() => lesson_sessions.id, { onDelete: 'restrict' }),
  bracket_id:       uuid('bracket_id').references(() => cancellation_policy_brackets.id, { onDelete: 'restrict' }),
  strike_count:     integer('strike_count').notNull().default(1), // usually 1; bracket may override
  issued_at:        timestamp('issued_at',    { withTimezone: true }).notNull().defaultNow(),
  expires_at:       timestamp('expires_at',   { withTimezone: true }),  // issued_at + strike_decay_days; null = permanent
  status:           strikeStatusEnum('status').notNull().default('ACTIVE'),

  // Force majeure waiver
  waived_by:     uuid('waived_by').references(() => users.id),
  waived_at:     timestamp('waived_at', { withTimezone: true }),
  waiver_reason: text('waiver_reason'),
  fm_evidence:   text('fm_evidence'),  // document reference or free text description

  notes:     text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_strikes_tutor').on(t.tutor_id),
  index('idx_strikes_tutor_status').on(t.tutor_id, t.status),
  index('idx_strikes_session').on(t.lesson_session_id),
  index('idx_strikes_expires_at').on(t.expires_at),  // for decay cron job
]);

// ─── App Configs (operational tunables) ───────────────────────────────────────

export const app_configs = pgTable('app_configs', {
  id:          uuid('id').primaryKey().default(sql`uuidv7()`),
  key:         text('key').notNull(),
  value:       text('value').notNull(),               // stored as text; service layer parses per key
  description: text('description'),
  updated_by:  uuid('updated_by').references(() => users.id),
  created_at:  timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:  timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_app_configs_key').on(t.key),
]);

Initial app_configs seed data (applied in first migration):

Key	Default	Description
`tutor_cancel.self_sub_window_hours`	`4`	Window (hours) during which tutor may self-substitute
`tutor_cancel.no_penalty_threshold_hours`	`4`	> this many hours = no penalty bracket applies
`siswa_cancel.no_penalty_threshold_hours`	`4`	Same for student-side cancellation
`strike.decay_days`	`90`	Rolling window (days) after which a strike expires
`strike.terminate_threshold`	`3`	Cumulative active strikes before contract termination
`force_majeure.appeal_window_days`	`14`	Days tutor has to appeal an FM decision

16.3 Migration: Add FK to `lesson_sessions`

This migration runs after the cancellation_policy_brackets table is created:

-- Migration: add_cancellation_bracket_fk_to_lesson_sessions
ALTER TABLE lesson_sessions
  ADD CONSTRAINT fk_ls_cancellation_bracket
  FOREIGN KEY (cancellation_policy_bracket_id)
  REFERENCES cancellation_policy_brackets (id)
  ON DELETE RESTRICT;

In Drizzle schema (scheduling.ts), update the field definition:

// In lesson_sessions table definition — update this field:
cancellation_policy_bracket_id: uuid('cancellation_policy_bracket_id')
  .references(() => cancellation_policy_brackets.id, { onDelete: 'restrict' }),
// (import cancellation_policy_brackets from './cancellation')

16.4 Drizzle Relations

export const cancellationPolicyConfigsRelations = relations(cancellation_policy_configs, ({ many }) => ({
  brackets: many(cancellation_policy_brackets),
}));

export const cancellationPolicyBracketsRelations = relations(cancellation_policy_brackets, ({ one, many }) => ({
  config:          one(cancellation_policy_configs, { fields: [cancellation_policy_brackets.config_id], references: [cancellation_policy_configs.id] }),
  lesson_sessions: many(lesson_sessions),
  strikes:         many(tutor_strikes),
}));

export const tutorStrikesRelations = relations(tutor_strikes, ({ one }) => ({
  tutor:          one(users,                        { fields: [tutor_strikes.tutor_id],          references: [users.id],                         relationName: 'strike_tutor'   }),
  lesson_session: one(lesson_sessions,              { fields: [tutor_strikes.lesson_session_id], references: [lesson_sessions.id]               }),
  bracket:        one(cancellation_policy_brackets, { fields: [tutor_strikes.bracket_id],        references: [cancellation_policy_brackets.id]   }),
  waived_by_user: one(users,                        { fields: [tutor_strikes.waived_by],         references: [users.id],                         relationName: 'strike_waiver'  }),
}));

16.5 Service Layer: Cancel Calculation

In @packages/service/src/cancellation/:

// cancelSession.ts — called after permission check
interface CancelSessionPayload {
  session_id:       string;
  cancelled_by_id:  string;
  reason:           string;
  policy_type:      'TUTOR_CANCEL' | 'SISWA_CANCEL';
  force_majeure?:   boolean;
  fm_evidence?:     string;
}

// Steps (all in one transaction):
// 1. Fetch lesson_session (assert status = APPROVED or REQUESTED)
// 2. Compute hours_before = (scheduled_at - now()) in hours
// 3. Fetch ACTIVE cancellation_policy_config for policy_type
// 4. Find matching bracket: lowest threshold_hours where hours_before <= threshold_hours
//    (null bracket = no-penalty, > no_penalty_threshold_hours)
// 5. UPDATE lesson_sessions:
//    status = 'CANCELLED', cancelled_at = now(), cancelled_by, cancellation_reason,
//    cancellation_policy_bracket_id = bracket.id (or null if no-penalty)
// 6. If TUTOR_CANCEL + bracket found + NOT force_majeure:
//    INSERT tutor_strikes (issued_at, expires_at = now() + strike_decay_days, strike_count = bracket.strike_added)
//    Check cumulative active strikes → if >= strike_terminate_threshold: flag tutor for admin review
// 7. If TUTOR_CANCEL + force_majeure:
//    INSERT tutor_strikes with status='WAIVED' immediately (for audit trail)
//    waived_by = cancelled_by_id, waiver_reason = fm_evidence
// 8. Audit middleware captures all changes

// Active strike count query (for service or admin dashboard):
// SELECT SUM(strike_count) FROM tutor_strikes
// WHERE tutor_id = ? AND status = 'ACTIVE'
//   AND (expires_at IS NULL OR expires_at > now())

16.6 Strike Decay (CF Cron)

In @apps/cron-strike-decay (or merged into a general maintenance cron):

// Runs daily; marks expired strikes
// UPDATE tutor_strikes
//   SET status = 'EXPIRED', updated_at = now()
//   WHERE status = 'ACTIVE'
//     AND expires_at IS NOT NULL
//     AND expires_at <= now()

16.7 Key Business Rules

Rule	Enforcement
One ACTIVE config per policy_type	Partial unique index `uq_cpconfig_one_active_per_type`
Bracket percentages 0–100	DB `CHECK` constraints on all `_pct` columns
Brackets immutable after creation	No UPDATE allowed in service layer; change = retire config + new one
Snapshot at APPROVED	`lesson_sessions.cancellation_policy_bracket_id` set at approve time
Strike decay	CF Cron daily; sets `status='EXPIRED'` when `expires_at <= now()`
FM waiver = strike issued with status=‘WAIVED’	Audit trail preserved; not counted in active total
Terminate at threshold	Service warning flag (not auto-terminate in MVP; admin reviews manually)

§17 Audit Middleware Schema (Step 2.14)

Source: plans/scope/001-mvp.md §7 (A1 epic). Cross-cutting — applies to ALL state-changing operations.

17.1 Design Principles

INSERT-only. No UPDATE, no DELETE. DB trigger enforces at server level.
Unified table. One audit_events table covers all entities (session, pricing, policy, strikes, users, etc.).
7-year retention. Postgres stays as primary store for MVP. Archival to R2 cold storage = future ops task (flag at Step 5).
No async queue. For MVP: audit writes are synchronous inside the same DB transaction as the mutating operation. If audit fails → whole transaction rolls back. Simpler, no dropped events.
Context propagated. Service functions receive an AuditContext (actor_id, ip, user_agent, session_id) from the API layer.

17.2 Drizzle Schema: `packages/db/schema/audit.ts`

import { pgTable, pgEnum, uuid, text, jsonb, timestamp, index } from 'drizzle-orm/pg-core';
import { sql }   from 'drizzle-orm';
import { users } from './auth';

export const actorTypeEnum = pgEnum('audit_actor_type', ['USER', 'SYSTEM', 'CRON']);

export const audit_events = pgTable('audit_events', {
  id:          uuid('id').primaryKey().default(sql`uuidv7()`),

  // ── Who ──
  actor_id:    uuid('actor_id').references(() => users.id, { onDelete: 'set null' }), // null = SYSTEM/CRON
  actor_type:  actorTypeEnum('actor_type').notNull().default('USER'),

  // ── What ──
  action:      text('action').notNull(),      // 'session:approve', 'pricing:update', etc.
  entity_type: text('entity_type').notNull(), // table name: 'lesson_sessions', 'student_pricing_matrix', etc.
  entity_id:   text('entity_id').notNull(),   // UUIDv7 as text; text for future-proofing

  // ── State delta ──
  before_state: jsonb('before_state'),        // null for INSERTs
  after_state:  jsonb('after_state'),         // null for hard deletes (soft-delete updates = before+after)

  // ── Request context ──
  ip_address:  text('ip_address'),
  user_agent:  text('user_agent'),
  session_id:  text('session_id'),            // Better Auth session ID for session correlation

  // ── Extra context ──
  metadata: jsonb('metadata'),                // action-specific bag (e.g. { reason, policy_version })

  // No updated_at — immutable; trigger prevents UPDATE/DELETE (§17.3)
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_audit_entity').on(t.entity_type, t.entity_id),  // "show history of this record"
  index('idx_audit_actor').on(t.actor_id),                   // "what did this admin do"
  index('idx_audit_action').on(t.action),
  index('idx_audit_created_at').on(t.created_at),            // time-range reports
]);

17.3 Immutability Trigger (Raw SQL Migration)

Drizzle has no native trigger support. Add as a separate migration step:

-- Migration: audit_events_immutable_trigger
CREATE OR REPLACE FUNCTION fn_audit_events_immutable()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'audit_events is insert-only; UPDATE and DELETE are forbidden';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_audit_events_no_update
  BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH ROW EXECUTE FUNCTION fn_audit_events_immutable();

17.4 Action Naming Convention

Format: <entity>:<verb> — lowercase, kebab words separated by :.

Category	Action strings
Sessions	`session:request`, `session:approve`, `session:reject`, `session:start`, `session:complete`, `session:cancel`, `session:reschedule`, `session:no-show`
Pricing	`pricing:create`, `pricing:update`, `academic-year:clone`
Cancellation policy	`cancel-policy:create`, `cancel-policy:retire`
Strikes	`strike:issue`, `strike:waive`, `strike:expire` (CRON)
App config	`app-config:update`
Users / auth	`user:invite`, `user:role-assign`, `user:role-revoke`, `user:suspend`, `user:reactivate`, `user:terminate`
Master data	`tingkat:create`, `tingkat:update`, `tingkat:archive`, `subject:create`, `subject:update`, etc.
Settlement	`settlement:mark-paid`, `settlement:reverse`, `settlement:adjust`, `settlement:write-off`
Billing period	`billing-period:close`, `billing-period:reopen`
System / cron	`cron:strike-decay`, `cron:session-auto-start`

17.5 Service Middleware Integration

In @packages/service/src/audit/:

export interface AuditContext {
  actor_id:    string | null;           // null for CRON/SYSTEM
  actor_type:  'USER' | 'SYSTEM' | 'CRON';
  ip_address?: string;
  user_agent?: string;
  session_id?: string;                  // Better Auth session_id
}

export interface AuditPayload {
  action:       string;
  entity_type:  string;
  entity_id:    string;
  before_state?: Record<string, unknown> | null;
  after_state?:  Record<string, unknown> | null;
  metadata?:    Record<string, unknown>;
}

// record.ts
import type { DB } from '@packages/db';     // Drizzle db instance type
import { audit_events } from '@packages/db/schema/audit';

export async function recordAudit(
  db: DB,
  ctx: AuditContext,
  payload: AuditPayload,
): Promise<void> {
  await db.insert(audit_events).values({
    actor_id:    ctx.actor_id ?? undefined,
    actor_type:  ctx.actor_type,
    action:      payload.action,
    entity_type: payload.entity_type,
    entity_id:   payload.entity_id,
    before_state: payload.before_state ?? null,
    after_state:  payload.after_state  ?? null,
    ip_address:  ctx.ip_address,
    user_agent:  ctx.user_agent,
    session_id:  ctx.session_id,
    metadata:    payload.metadata ?? null,
  });
  // Runs inside caller's transaction — if outer tx rolls back, audit row rolls back too
}

Usage pattern inside a service function:

// Example: session approve
export async function approveSession(
  db: DB,
  ctx: AuditContext,
  input: ApproveSessionInput,
): Promise<void> {
  await db.transaction(async (tx) => {
    const [session] = await tx.select().from(lesson_sessions)
      .where(eq(lesson_sessions.id, input.session_id));

    // ... validation + compute snapshot ...

    const [updated] = await tx.update(lesson_sessions)
      .set({ status: 'APPROVED', approved_at: new Date(), /* ... */ })
      .where(eq(lesson_sessions.id, input.session_id))
      .returning();

    await recordAudit(tx, ctx, {
      action:      'session:approve',
      entity_type: 'lesson_sessions',
      entity_id:   session.id,
      before_state: session,
      after_state:  updated,
    });
  });
}

17.6 AuditContext in API Layer

In oRPC middleware (apps/web/src/server/middlewares/whoami.ts):

// withWhoami middleware (apps/web/src/server/middlewares/whoami.ts)
export const withWhoami = osWithDb.middleware(async ({ context, next }) => {
  const sessionToken = getCookieValue(context.request.headers.get("cookie") ?? "", "better-auth.session_token");
  if (!sessionToken) throw new ORPCError("UNAUTHORIZED");
  // resolve session + user + permissions from DB ...
  const auditCtx: AuditContext = {
    actor_id:   sessionRow.user_id,
    actor_type: "USER",
    ip_address: context.request.headers.get("CF-Connecting-IP") ?? context.request.headers.get("x-forwarded-for") ?? undefined,
    user_agent: context.request.headers.get("user-agent") ?? undefined,
    session_id: sessionRow.session_id,
  };
  return next({ context: { whoami, auditCtx } });
});

CF Workers provides CF-Connecting-IP for the real client IP — do not trust x-forwarded-for in CF Workers context.

17.7 Retention Policy (MVP note)

Tier	Policy
Hot (Postgres)	All rows, indefinitely for MVP
Archive (R2 cold storage)	Future: export rows older than 2 years to R2 as JSONL; delete from Postgres
Legal retention	7-year minimum per Indonesian accounting law (UU 8/1997 Dokumen Perusahaan)

Archive job = CF Cron (future) + Drizzle batch read → R2 presigned PUT → delete from Postgres. Not scoped in iter 1.

§18 Office, Profiles, Settlement, DocumentUpload, Notification, Anomaly (Step 2.15)

18.1 Entity Overview

Table(s)	File	Notes
`offices`	`schema/org.ts`	Multi-office DB-ready; MVP seed 1 row
`tutor_profiles`, `student_profiles`	`schema/profiles.ts`	XPrivate-specific user data extending Better Auth `users`
`billing_periods`	`schema/finance.ts`	Monthly period with lock status
`student_settlements`, `tutor_settlements`	`schema/finance.ts`	Per-session billing/payout records
`document_uploads`	`schema/documents.ts`	R2 file upload tracking
`notification_templates`, `notification_logs`, `notification_preferences`	`schema/notifications.ts`	DB-ready; iter 1 = manual welcome email only
Anomaly columns on `lesson_sessions`	migration	DB-ready; engine = iter 2

18.2 `packages/db/schema/org.ts` — Office

import { pgTable, uuid, text, integer, timestamp, index, check } from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';
import { users } from './auth';

export const officeStatusEnum = pgEnum('office_status', ['ACTIVE', 'INACTIVE']);

export const offices = pgTable('offices', {
  id:                  uuid('id').primaryKey().default(sql`uuidv7()`),
  name:                text('name').notNull(),
  address:             text('address').notNull(),
  transport_base_idr:  integer('transport_base_idr').notNull().default(0), // per-session base transport for offline sesi in this office area
  status:              officeStatusEnum('status').notNull().default('ACTIVE'),
  notes:               text('notes'),
  created_by:          uuid('created_by').references(() => users.id),
  updated_by:          uuid('updated_by').references(() => users.id),
  created_at:          timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:          timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_offices_status').on(t.status),
  check('chk_offices_transport_non_negative', sql`${t.transport_base_idr} >= 0`),
]);

Add office_id to lesson_sessions via migration (FK added after offices table exists):

-- Migration: add_office_id_to_lesson_sessions
ALTER TABLE lesson_sessions
  ADD COLUMN office_id uuid REFERENCES offices(id) ON DELETE RESTRICT;
CREATE INDEX idx_ls_office ON lesson_sessions(office_id);

18.3 `packages/db/schema/profiles.ts` — User Profiles

Extends Better Auth users table with XPrivate-domain attributes. One row per user (1:1 with users). Created in same transaction as user creation.

import { pgTable, pgEnum, uuid, text, integer, boolean,
         timestamp, index, uniqueIndex }  from 'drizzle-orm/pg-core';
import { relations, sql }                from 'drizzle-orm';
import { users }             from './auth';
import { offices }           from './org';
import { golongans, tingkats, segmentasis } from './catalog';

// ─── Tutor Profiles ───────────────────────────────────────────────────────────

export const tutorStatusEnum = pgEnum('tutor_status', ['ACTIVE', 'INACTIVE', 'SUSPENDED', 'TERMINATED', 'ARCHIVED']);

export const tutor_profiles = pgTable('tutor_profiles', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  user_id:    uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }).unique(),
  golongan_id: uuid('golongan_id').notNull().references(() => golongans.id, { onDelete: 'restrict' }),
  office_id:  uuid('office_id').references(() => offices.id, { onDelete: 'restrict' }),  // home office

  status:     tutorStatusEnum('status').notNull().default('ACTIVE'),
  phone:      text('phone'),
  // Bank account for settlement payout
  bank_name:          text('bank_name'),
  bank_account_no:    text('bank_account_no'),
  bank_account_name:  text('bank_account_name'),  // account holder name

  // Suspension tracking
  suspended_at:     timestamp('suspended_at', { withTimezone: true }),
  suspended_by:     uuid('suspended_by').references(() => users.id),
  suspend_until:    timestamp('suspend_until', { withTimezone: true }),  // null = indefinite
  suspension_reason: text('suspension_reason'),

  // Termination
  terminated_at:    timestamp('terminated_at', { withTimezone: true }),
  terminated_by:    uuid('terminated_by').references(() => users.id),
  termination_reason: text('termination_reason'),

  notes:      text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_tutor_profiles_user_id').on(t.user_id),
  index('idx_tutor_profiles_golongan').on(t.golongan_id),
  index('idx_tutor_profiles_status').on(t.status),
  index('idx_tutor_profiles_office').on(t.office_id),
]);

// ─── Student Profiles ─────────────────────────────────────────────────────────

export const studentStatusEnum = pgEnum('student_status', ['ACTIVE', 'INACTIVE', 'GRADUATED', 'WITHDRAWN']);

export const student_profiles = pgTable('student_profiles', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  user_id:    uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }).unique(),
  office_id:  uuid('office_id').references(() => offices.id, { onDelete: 'restrict' }),

  status:     studentStatusEnum('status').notNull().default('ACTIVE'),
  phone:      text('phone'),
  parent_phone: text('parent_phone'),             // for underage students
  school_name: text('school_name'),

  // Default context for session booking pre-fill
  default_tingkat_id:    uuid('default_tingkat_id').references(() => tingkats.id, { onDelete: 'set null' }),
  default_segmentasi_id: uuid('default_segmentasi_id').references(() => segmentasis.id, { onDelete: 'set null' }),

  notes:      text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_student_profiles_user_id').on(t.user_id),
  index('idx_student_profiles_status').on(t.status),
  index('idx_student_profiles_office').on(t.office_id),
]);

18.4 `packages/db/schema/finance.ts` — Billing Periods + Settlements

import { pgTable, pgEnum, uuid, text, integer, boolean,
         timestamp, date, index, uniqueIndex, check } from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';
import { users }          from './auth';
import { lesson_sessions } from './scheduling';
import { offices }        from './org';
import { academic_years } from './catalog';

// ─── Billing Periods ──────────────────────────────────────────────────────────

export const billingPeriodStatusEnum = pgEnum('billing_period_status', ['OPEN', 'CLOSING', 'CLOSED', 'LOCKED']);

export const billing_periods = pgTable('billing_periods', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  label:      text('label').notNull(),            // 'YYYY-MM', e.g. '2026-05'
  starts_on:  date('starts_on').notNull(),
  ends_on:    date('ends_on').notNull(),
  status:     billingPeriodStatusEnum('status').notNull().default('OPEN'),
  closed_by:  uuid('closed_by').references(() => users.id),
  closed_at:  timestamp('closed_at', { withTimezone: true }),
  reopened_by: uuid('reopened_by').references(() => users.id),
  reopened_at: timestamp('reopened_at', { withTimezone: true }),
  reopen_reason: text('reopen_reason'),
  notes:      text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_billing_periods_label').on(t.label),
  index('idx_billing_periods_status').on(t.status),
  check('chk_billing_periods_dates', sql`${t.ends_on} >= ${t.starts_on}`),
]);

// ─── Student Settlements ──────────────────────────────────────────────────────

export const settlementStatusEnum = pgEnum('settlement_status', ['UNPAID', 'PAID', 'ADJUSTED', 'REVERSED', 'WRITTEN_OFF']);

export const student_settlements = pgTable('student_settlements', {
  id:                 uuid('id').primaryKey().default(sql`uuidv7()`),
  lesson_session_id:  uuid('lesson_session_id').notNull().references(() => lesson_sessions.id, { onDelete: 'restrict' }),
  student_id:         uuid('student_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  office_id:          uuid('office_id').references(() => offices.id, { onDelete: 'restrict' }),
  billing_period_id:  uuid('billing_period_id').references(() => billing_periods.id, { onDelete: 'restrict' }),

  // Amounts (snapshot from lesson_sessions at COMPLETED trigger)
  gross_amount_idr:      integer('gross_amount_idr').notNull(),      // = lesson_sessions.amount_student_final_idr
  adjustment_amount_idr: integer('adjustment_amount_idr').notNull().default(0),  // admin manual delta
  net_amount_idr:        integer('net_amount_idr').notNull(),         // gross + adjustment; kept in sync by service layer

  status: settlementStatusEnum('status').notNull().default('UNPAID'),

  // Payment
  paid_at:            timestamp('paid_at',  { withTimezone: true }),
  paid_by:            uuid('paid_by').references(() => users.id),
  payment_reference:  text('payment_reference'),           // external bank transfer ref

  // Adjustment trail
  adjusted_by:         uuid('adjusted_by').references(() => users.id),
  adjusted_at:         timestamp('adjusted_at', { withTimezone: true }),
  adjustment_reason:   text('adjustment_reason'),

  // Reversal
  reversed_by:         uuid('reversed_by').references(() => users.id),
  reversed_at:         timestamp('reversed_at', { withTimezone: true }),
  reversal_reason:     text('reversal_reason'),

  // Bad debt write-off
  written_off_by:      uuid('written_off_by').references(() => users.id),
  written_off_at:      timestamp('written_off_at', { withTimezone: true }),
  write_off_reason:    text('write_off_reason'),
  tax_compliance_flag: boolean('tax_compliance_flag').notNull().default(false),

  notes:      text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_student_settlement_session').on(t.lesson_session_id),  // 1:1 per session
  index('idx_ss_student').on(t.student_id),
  index('idx_ss_status').on(t.status),
  index('idx_ss_billing_period').on(t.billing_period_id),
  index('idx_ss_office').on(t.office_id),
  check('chk_ss_gross_non_negative', sql`${t.gross_amount_idr} >= 0`),
]);

// ─── Tutor Settlements ────────────────────────────────────────────────────────

export const tutor_settlements = pgTable('tutor_settlements', {
  id:                 uuid('id').primaryKey().default(sql`uuidv7()`),
  lesson_session_id:  uuid('lesson_session_id').notNull().references(() => lesson_sessions.id, { onDelete: 'restrict' }),
  tutor_id:           uuid('tutor_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  office_id:          uuid('office_id').references(() => offices.id, { onDelete: 'restrict' }),
  billing_period_id:  uuid('billing_period_id').references(() => billing_periods.id, { onDelete: 'restrict' }),

  // Amounts
  honor_amount_idr:      integer('honor_amount_idr').notNull(),       // = lesson_sessions.amount_tutor_final_idr
  transport_amount_idr:  integer('transport_amount_idr').notNull().default(0),  // from office.transport_base_idr at session time (offline only)
  penalty_amount_idr:    integer('penalty_amount_idr').notNull().default(0),    // denda from cancellation (positive = deducted)
  adjustment_amount_idr: integer('adjustment_amount_idr').notNull().default(0), // manual delta
  pph_withheld_idr:      integer('pph_withheld_idr').notNull().default(0),      // PPh 21 withheld (operator manual input)
  // net = honor + transport - penalty + adjustment - pph_withheld (kept in sync by service)
  net_amount_idr:        integer('net_amount_idr').notNull(),

  status: settlementStatusEnum('status').notNull().default('UNPAID'),

  // Payment
  paid_at:            timestamp('paid_at', { withTimezone: true }),
  paid_by:            uuid('paid_by').references(() => users.id),
  payment_reference:  text('payment_reference'),

  // Adjustment trail (same pattern as student_settlements)
  adjusted_by:       uuid('adjusted_by').references(() => users.id),
  adjusted_at:       timestamp('adjusted_at', { withTimezone: true }),
  adjustment_reason: text('adjustment_reason'),
  reversed_by:       uuid('reversed_by').references(() => users.id),
  reversed_at:       timestamp('reversed_at', { withTimezone: true }),
  reversal_reason:   text('reversal_reason'),
  written_off_by:    uuid('written_off_by').references(() => users.id),
  written_off_at:    timestamp('written_off_at', { withTimezone: true }),
  write_off_reason:  text('write_off_reason'),

  notes:      text('notes'),
  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_tutor_settlement_session').on(t.lesson_session_id),  // 1:1 per session
  index('idx_ts_tutor').on(t.tutor_id),
  index('idx_ts_status').on(t.status),
  index('idx_ts_billing_period').on(t.billing_period_id),
]);

// ─── Settlement auto-creation (service layer note) ────────────────────────────
// Triggered when lesson_session transitions to COMPLETED or NO_SHOW_STUDENT:
// 1. INSERT student_settlement (gross = session.amount_student_final_idr, net = gross)
// 2. INSERT tutor_settlement   (honor = session.amount_tutor_final_idr, transport = office rate if OFFLINE)
// Both in same DB transaction as the session status update.
// billing_period_id = lookup OPEN billing_period covering session.scheduled_at.

18.5 `packages/db/schema/documents.ts` — Document Uploads

Integrates with R2 presigned PUT flow (§5):

import { pgTable, pgEnum, uuid, text, integer, timestamp, index } from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';
import { users } from './auth';

export const documentTypeEnum = pgEnum('document_type', [
  'TUTOR_CONTRACT', 'TUTOR_KTP', 'TUTOR_CERTIFICATE',
  'TUTOR_PORTFOLIO', 'STUDENT_REPORT_PHOTO', 'OTHER',
]);

export const documentStatusEnum = pgEnum('document_status', [
  'PENDING',     // upload URL generated, file not yet uploaded
  'UPLOADED',    // client confirmed upload
  'VERIFIED',    // admin verified
  'REJECTED',    // admin rejected
]);

export const document_uploads = pgTable('document_uploads', {
  id:               uuid('id').primaryKey().default(sql`uuidv7()`),
  uploader_id:      uuid('uploader_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  subject_user_id:  uuid('subject_user_id').references(() => users.id, { onDelete: 'restrict' }),  // whose document (e.g. tutor being KYC'd)
  document_type:    documentTypeEnum('document_type').notNull(),

  // R2 storage reference
  r2_key:            text('r2_key').notNull().unique(),    // R2 object key
  filename_original: text('filename_original').notNull(),  // display name
  content_type:      text('content_type').notNull(),       // MIME type
  file_size_bytes:   integer('file_size_bytes'),

  status:            documentStatusEnum('status').notNull().default('PENDING'),

  // Verification
  verified_by:       uuid('verified_by').references(() => users.id),
  verified_at:       timestamp('verified_at', { withTimezone: true }),
  rejection_reason:  text('rejection_reason'),

  // Expiry (e.g. contract end date; null = no expiry)
  expires_at:        timestamp('expires_at', { withTimezone: true }),

  notes:             text('notes'),
  created_at:        timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:        timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_docup_subject_user').on(t.subject_user_id),
  index('idx_docup_uploader').on(t.uploader_id),
  index('idx_docup_type_subject').on(t.document_type, t.subject_user_id),
  index('idx_docup_status').on(t.status),
]);

18.6 `packages/db/schema/notifications.ts` — Notification System (DB-ready)

Iter 1 scope: Manual “Send welcome email” only. These tables are created and seeded with the welcome template. Auto-trigger engine = iter 2.

import { pgTable, pgEnum, uuid, text, boolean, timestamp, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';
import { users } from './auth';

export const notifChannelEnum = pgEnum('notif_channel', ['EMAIL', 'WHATSAPP', 'SMS', 'IN_APP', 'PUSH']);
export const notifStatusEnum  = pgEnum('notif_status',  ['PENDING', 'SENT', 'DELIVERED', 'FAILED', 'SKIPPED']);

// ─── Templates ───────────────────────────────────────────────────────────────

export const notification_templates = pgTable('notification_templates', {
  id:            uuid('id').primaryKey().default(sql`uuidv7()`),
  event_type:    text('event_type').notNull(),     // 'user:welcome', 'session:approved', etc.
  channel:       notifChannelEnum('channel').notNull(),
  language:      text('language').notNull().default('id'),  // 'id' | 'en'
  subject:       text('subject'),                  // nullable (email subject; N/A for WhatsApp)
  body_template: text('body_template').notNull(),  // Mustache/Handlebars template string
  is_active:     boolean('is_active').notNull().default(true),
  notes:         text('notes'),
  created_at:    timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at:    timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_notif_template_combo').on(t.event_type, t.channel, t.language),
  index('idx_notif_template_event').on(t.event_type),
]);

// ─── Logs ─────────────────────────────────────────────────────────────────────

export const notification_logs = pgTable('notification_logs', {
  id:                 uuid('id').primaryKey().default(sql`uuidv7()`),
  template_id:        uuid('template_id').references(() => notification_templates.id, { onDelete: 'set null' }),
  recipient_id:       uuid('recipient_id').notNull().references(() => users.id, { onDelete: 'restrict' }),
  recipient_contact:  text('recipient_contact').notNull(),  // email / phone snapshot at send time
  channel:            notifChannelEnum('channel').notNull(),
  event_type:         text('event_type'),
  subject:            text('subject'),
  body:               text('body').notNull(),               // rendered body (snapshot)
  status:             notifStatusEnum('status').notNull().default('PENDING'),

  // Trigger context
  entity_type: text('entity_type'),   // what triggered (e.g. 'users')
  entity_id:   text('entity_id'),

  sent_at:       timestamp('sent_at',       { withTimezone: true }),
  delivered_at:  timestamp('delivered_at',  { withTimezone: true }),
  failed_at:     timestamp('failed_at',     { withTimezone: true }),
  failure_reason: text('failure_reason'),

  created_at: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index('idx_notiflog_recipient').on(t.recipient_id),
  index('idx_notiflog_status').on(t.status),
  index('idx_notiflog_created_at').on(t.created_at),
]);

// ─── Preferences ─────────────────────────────────────────────────────────────

export const notification_preferences = pgTable('notification_preferences', {
  id:         uuid('id').primaryKey().default(sql`uuidv7()`),
  user_id:    uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  channel:    notifChannelEnum('channel').notNull(),
  event_type: text('event_type').notNull(),   // specific event or '*' for all on this channel
  enabled:    boolean('enabled').notNull().default(true),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex('uq_notif_pref_combo').on(t.user_id, t.channel, t.event_type),
  index('idx_notif_pref_user').on(t.user_id),
]);

Seed data (initial migration):

INSERT INTO notification_templates (id, event_type, channel, language, subject, body_template) VALUES
  (uuidv7(), 'user:welcome', 'EMAIL', 'id',
   'Selamat datang di XPrivate Education',
   'Halo {{name}},\n\nAkun Anda di XPrivate Education telah dibuat. ...');

18.7 Anomaly Fields Migration (lesson_sessions extension)

Iter 1 minimal: SLA flag = admin UI query filter (WHERE status=''REQUESTED'' AND requested_at < now() - interval ''24 hours''). No anomaly_flag written by code in iter 1.

DB-ready iter 2: Schema present, engine deferred.

-- Migration: add_anomaly_fields_to_lesson_sessions
DO $$ BEGIN
  CREATE TYPE anomaly_type AS ENUM (
    'TIMING_OUTLIER', 'DURATION_OUTLIER', 'NO_SHOW_IMPLICIT',
    'CONCURRENCY', 'PATTERN_ABUSE', 'SLA_BREACH'
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

ALTER TABLE lesson_sessions
  ADD COLUMN anomaly_flag        boolean     NOT NULL DEFAULT false,
  ADD COLUMN anomaly_type        anomaly_type,
  ADD COLUMN anomaly_resolved_at timestamptz,
  ADD COLUMN anomaly_resolved_by uuid REFERENCES users(id) ON DELETE SET NULL;

CREATE INDEX idx_ls_anomaly_flag ON lesson_sessions(anomaly_flag) WHERE anomaly_flag = true;

In Drizzle schema (scheduling.ts), update lesson_sessions to include these fields (after the migration adds them):

// Add to lesson_sessions table:
anomaly_flag:         boolean('anomaly_flag').notNull().default(false),
anomaly_type:         anomalyTypeEnum('anomaly_type'),
anomaly_resolved_at:  timestamp('anomaly_resolved_at', { withTimezone: true }),
anomaly_resolved_by:  uuid('anomaly_resolved_by').references(() => users.id, { onDelete: 'set null' }),

18.8 Settlement Auto-Creation Flow (Service Layer)

// Called inside lesson_sessions complete/no-show transition (same DB transaction):
export async function createSettlementsForSession(
  tx: TX,
  ctx: AuditContext,
  session: LessonSession,
): Promise<void> {
  // Lookup open billing period covering session.scheduled_at
  const billingPeriod = await findOpenBillingPeriod(tx, session.scheduled_at);

  // Transport for tutor: from session's office.transport_base_idr if OFFLINE
  const transport = session.session_mode === 'OFFLINE'
    ? await getOfficeTransport(tx, session.office_id)
    : 0;

  await tx.insert(student_settlements).values({
    lesson_session_id: session.id,
    student_id:        session.student_id,
    office_id:         session.office_id,
    billing_period_id: billingPeriod?.id,
    gross_amount_idr:  session.amount_student_final_idr,
    net_amount_idr:    session.amount_student_final_idr,
  });

  await tx.insert(tutor_settlements).values({
    lesson_session_id:    session.id,
    tutor_id:             session.tutor_id,
    office_id:            session.office_id,
    billing_period_id:    billingPeriod?.id,
    honor_amount_idr:     session.amount_tutor_final_idr,
    transport_amount_idr: transport,
    net_amount_idr:       session.amount_tutor_final_idr + transport,
  });

  await recordAudit(tx, ctx, { action: 'settlement:create-pair', ... });
}

§19 Bank Soal Architecture — Plug-in Domain Module Pattern (Step 2.16)

Per scope §4 item 4: “Formalize bank soal architecture (plug-in domain module pattern, shared core interface)”. Bank soal is NOT in iter 1 scope. This section formalizes the integration contract so future implementation doesn’t require schema migrations against existing tables.

19.1 Core Principle

Bank soal is an additive plug-in — it extends the platform without modifying any existing table in the core schema (public.*). Shared infrastructure (auth, users, RBAC, audit, billing period) is consumed via stable interfaces. Core tables never grow bank-soal-specific columns.

19.2 Monorepo Placement

packages/
  bank-soal/               ← new package when iter starts
    src/
      schema.ts            ← all Drizzle tables in 'bank_soal' PG schema
      service/             ← business logic (no auth handling)
    package.json           → @packages/bank-soal

apps/
  bank-soal-web/           ← optional separate CF Workers deploy
  OR
  web/src/server/routes/bank-soal/   ← mounted in existing @apps/web oRPC router

Iteration 1 recommendation: mount under @apps/web at /api/bank-soal/* to avoid a separate CF Workers deploy. Promote to @apps/bank-soal-web when traffic/feature set warrants isolation.

19.3 Postgres Schema Isolation

All bank soal tables live in the bank_soal Postgres schema (not public). This gives namespace separation with zero risk of name collisions with the core schema.

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

const bq = pgSchema('bank_soal');  // all tables via bq.table(...)

export const questions = bq.table('questions', { ... });
export const question_options = bq.table('question_options', { ... });
// etc.

Initial migration enables the schema:

CREATE SCHEMA IF NOT EXISTS bank_soal;

Cross-schema FKs to public.subjects, public.tingkats, public.users are valid in Postgres (reference fully-qualified table names). Drizzle supports this via the pgSchema API.

19.4 Shared Infrastructure Contract

Infrastructure	How bank soal consumes it
User identity	FK to `public.users(id)` directly — same identity, no duplication
Auth / RBAC	Adds permission keys: `bq:question:read`, `bq:question:write`, `bq:exam:take`, etc. registered in `@packages/auth` permission list
WhoAmI endpoint	Already returns resolved permissions — no change needed; bank soal checks `bq:*` permissions from same response
Audit trail	Uses same `public.audit_events` table; `entity_type` = `'bq_questions'`, `'bq_exams'`, etc.
Billing period	`public.billing_periods` → FK from `bank_soal.subscriptions.billing_period_id`
Subject / Tingkat catalog	FK to `public.subjects`, `public.tingkats` for tagging questions
Document uploads	Uses same `public.document_uploads` table if question assets stored in R2
Telemetry / OTEL	Same `@tigorhutasuhut/telemetry-js` instance injected at app layer

19.5 Minimal Schema Sketch (for contract planning — not iter 1 implementation)

bank_soal.question_categories   (id, parent_id self-ref, name, subject_id, sort_order)
bank_soal.questions             (id, category_id, tingkat_id, subject_id, body, type enum[MCQ,ESSAY], difficulty)
bank_soal.question_options      (id, question_id, label A-E, body, is_correct)
bank_soal.question_explanations (id, question_id, explanation, author_id → public.users)
bank_soal.exams                 (id, title, subject_id, tingkat_id, time_limit_ms, status)
bank_soal.exam_questions        (exam_id, question_id, sort_order — junction)
bank_soal.exam_sessions         (id, exam_id, user_id → public.users, started_at, finished_at, score)
bank_soal.subscriptions         (id, user_id, plan, valid_from, valid_until, billing_period_id)

This sketch is intentionally minimal — detailed schema = separate planning document for the bank soal iteration.

19.6 What Does NOT Change in Core Schema

public.users — no bank soal columns added
public.lesson_sessions — no bank soal FK
public.subjects / public.tingkats — read-only FK targets only
public.audit_events — only new entity_type values, no schema change
@packages/auth RBAC — only new permission key strings registered; no new tables

19.7 Iteration Gateway

Bank soal iteration begins when:

Core tutoring platform (iter 1) is in production and stable
A separate planning doc (plans/bank-soal/) is created
@packages/bank-soal and its migrations are in a feature branch reviewed before merge

§20 i18n Strategy — Paraglide JS (Step 2.17)

Stack decision: Paraglide JS (locked in §8). Iter 1 = Bahasa Indonesia only. English UI = iter 2+.

20.1 Why Paraglide JS

Type-safe at compile time — message keys generate TypeScript, IDE autocomplete on every m.key_name() call
Vite-native — zero runtime overhead; messages compiled to tree-shakeable modules
No locale prefix URLs in iter 1 — admin-MVP has 1 admin user in ID; URL routing = iter 2
Bundle size — only translations used in the current render bundle are included

20.2 Folder Structure

apps/web/
  src/
    lib/
      i18n/
        messages/
          id.json        ← Bahasa Indonesia (source language, iter 1)
          en.json        ← English (stub file, auto-copy of id.json for iter 2)
        paraglide/       ← generated output (gitignored or committed per team pref)
          messages.js
          runtime.js
    app.tsx              ← setLocale('id') once at boot

id.json is the source of truth. The Paraglide Vite plugin generates the paraglide/ directory from it.

20.3 Key Naming Convention

Format: <namespace>__<component>__<description> — double underscore as namespace separator. All lowercase, words separated by single underscore.

Namespace	Usage
`nav__*`	Navigation labels (sidebar, header)
`auth__*`	Login/logout/session strings
`action__*`	Buttons and CTA labels (Save, Cancel, Approve, etc.)
`status__*`	Status badge labels (REQUESTED, APPROVED, etc.)
`form__*`	Form labels, field placeholders, helper text
`table__*`	Table column headers
`page_<page_name>__*`	Page-specific strings
`error__*`	Error messages
`confirm__*`	Confirmation dialog content
`notif__*`	Notification/toast messages
`report__*`	Report titles and column headers

Examples:

{
  "nav__sessions": "Sesi",
  "nav__pricing": "Harga",
  "action__approve": "Setujui",
  "action__reject": "Tolak",
  "action__cancel": "Batalkan",
  "action__save": "Simpan",
  "action__save_loading": "Menyimpan...",
  "status__session__requested": "Menunggu Persetujuan",
  "status__session__approved": "Disetujui",
  "status__session__completed": "Selesai",
  "status__session__cancelled": "Dibatalkan",
  "status__session__no_show_student": "Siswa Tidak Hadir",
  "form__session__student_label": "Siswa",
  "form__session__tutor_label": "Tutor",
  "form__session__scheduled_at_label": "Jadwal Sesi",
  "form__session__scheduled_at_placeholder": "Pilih tanggal dan waktu",
  "table__session__col_student": "Siswa",
  "table__session__col_tutor": "Tutor",
  "table__session__col_status": "Status",
  "table__session__col_scheduled_at": "Jadwal",
  "page_session_detail__title": "Detail Sesi",
  "error__required": "Kolom ini wajib diisi",
  "error__session__not_found": "Sesi tidak ditemukan",
  "confirm__cancel_session__title": "Batalkan Sesi?",
  "confirm__cancel_session__body": "Sesi yang dibatalkan tidak dapat dikembalikan. Lanjutkan?",
  "notif__session__approved_success": "Sesi berhasil disetujui",
  "notif__session__cancel_success": "Sesi berhasil dibatalkan"
}

20.4 Usage in Code

import * as m from '../i18n/paraglide/messages.js';
export { m };

// In component:
import { m } from '~/lib/i18n';

<Button>{m.action__approve()}</Button>
<Badge>{m.status__session__requested()}</Badge>

For dynamic values (interpolation):

{ "notif__session__missing_pricing": "Pricing belum diset untuk sesi {{session_id}}" }

m.notif__session__missing_pricing({ session_id: 'abc' })

20.5 Enum → i18n Key Mapping

Server-side session_status enum values map to client-side display keys via a lookup:

import { m } from '~/lib/i18n';

export const sessionStatusLabel: Record<string, () => string> = {
  REQUESTED:        m.status__session__requested,
  APPROVED:         m.status__session__approved,
  IN_PROGRESS:      m.status__session__in_progress,
  COMPLETED:        m.status__session__completed,
  REJECTED:         m.status__session__rejected,
  CANCELLED:        m.status__session__cancelled,
  RESCHEDULED:      m.status__session__rescheduled,
  NO_SHOW_STUDENT:  m.status__session__no_show_student,
  NO_SHOW_TUTOR:    m.status__session__no_show_tutor,
};

Do NOT hard-code display strings in UI components — always reference m.*.

20.6 English Stub (Iter 2)

en.json starts as an empty object or a copy of id.json with EN translations TODO-marked:

{
  "action__approve": "Approve",
  "action__reject": "Reject"
}

When EN keys are missing, Paraglide falls back to id.json. This means iter 2 can be done incrementally — translate high-traffic pages first, admin-only pages later.

20.7 Locale Switching (Iter 2+)

In iter 2+, locale preference is stored per-user (in app_configs or a user_preferences table). Locale is set at the oRPC API level from the Accept-Language header or user preference, then passed to TanStack Start for SSR.

§21 Deployment Architecture (Step 2.18)

21.1 CF Workers Topology

Cloudflare Network
│
├── @apps/web  ─────── Workers Route: xprivate.co.id/*
│   Nitro (TanStack Start) + oRPC OpenAPIHandler at /api/*
│   Bindings: HYPERDRIVE, R2_BUCKET, KV (sessions), OTEL_ENDPOINT (secret)
│
├── @apps/cron-daily ─ Cron Trigger: 0 1 * * *  (01:00 WIB = 18:00 UTC prev day)
│   Strike decay + anomaly scan stubs
│   Bindings: HYPERDRIVE
│
└── @apps/cron-billing ─ Cron Trigger: 0 2 1 * *  (1st of month 02:00 WIB)
    Monthly billing period open/close checks
    Bindings: HYPERDRIVE

All Workers run in Cloudflare Workers (not Pages) — wrangler deploy.

21.2 `apps/web/wrangler.jsonc`

{
  "$schema": "https://raw.githubusercontent.com/cloudflare/workers-types/main/schemas/config-schema.json",
  "name": "xprivate-web",
  "main": "dist/_worker.js",         // Nitro CF Workers output
  "compatibility_date": "2025-09-01",
  "compatibility_flags": ["nodejs_compat"],

  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "id": "<hyperdrive-config-id>"   // created via `wrangler hyperdrive create`
    }
  ],

  "r2_buckets": [
    {
      "binding": "R2_BUCKET",
      "bucket_name": "xprivate-uploads"
    }
  ],

  "kv_namespaces": [
    {
      "binding": "SESSION_KV",
      "id": "<kv-namespace-id>"        // Better Auth session storage (if using KV backend)
    }
  ],

  "observability": {
    "enabled": true                    // CF Workers built-in metrics
  },

  // Secrets (set via `wrangler secret put`; not in wrangler.jsonc):
  // NEON_DATABASE_URL      — used for local dev / Drizzle Kit migrations only
  // BETTER_AUTH_SECRET     — session signing secret
  // GOOGLE_CLIENT_ID       — OAuth
  // GOOGLE_CLIENT_SECRET
  // OTEL_EXPORTER_OTLP_ENDPOINT  — SigNoz ingest URL
  // OTEL_EXPORTER_OTLP_HEADERS   — SigNoz auth header
  // R2_PUBLIC_DOMAIN       — custom domain for public R2 assets (if any)
}

21.3 Hyperdrive Connection (DB Access in Workers)

Hyperdrive wraps the Neon Postgres connection string for CF Workers. Drizzle receives the Hyperdrive connection string instead of the direct Neon URL:

import { drizzle }    from 'drizzle-orm/postgres-js';
import postgres        from 'postgres';
import * as schema     from './schema';

export function createDB(env: { HYPERDRIVE: Hyperdrive }) {
  // Hyperdrive provides a pooled connection string compatible with postgres.js
  const client = postgres(env.HYPERDRIVE.connectionString, {
    max: 5,                    // Workers: keep pool small (CF handles concurrency via isolates)
    idle_timeout: 10,
    connect_timeout: 5,
  });
  return drizzle(client, { schema });
}

export type DB = ReturnType<typeof createDB>;

Local dev: Use NEON_DATABASE_URL directly (no Hyperdrive). Toggle via env.HYPERDRIVE ?? process.env.NEON_DATABASE_URL.

21.4 R2 Bindings Usage

R2 is used only for presigned PUT URL generation (§5). The Worker itself never streams file bytes.

import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';
import { getSignedUrl }               from '@aws-sdk/s3-request-presigner';

export function createR2Client(env: { R2_ACCOUNT_ID: string; R2_ACCESS_KEY_ID: string; R2_SECRET_ACCESS_KEY: string }) {
  return new S3Client({
    region: 'auto',
    endpoint: `https://${env.R2_ACCOUNT_ID}.r2.cloudflarestorage.com`,
    credentials: {
      accessKeyId:     env.R2_ACCESS_KEY_ID,
      secretAccessKey: env.R2_SECRET_ACCESS_KEY,
    },
  });
}

// Presigned URL generation (expires 15 min):
const url = await getSignedUrl(r2Client, new PutObjectCommand({
  Bucket: env.R2_BUCKET_NAME,
  Key:    r2Key,
  ContentType: contentType,
}), { expiresIn: 900 });

Note: Use S3-compatible API keys (R2_ACCESS_KEY_ID, R2_SECRET_ACCESS_KEY) — NOT the Workers R2_BUCKET binding — for presigned URL generation. Workers R2 binding is for direct read/delete in Workers context.

21.5 Cron Worker Example: `apps/cron-daily/wrangler.jsonc`

{
  "name": "xprivate-cron-daily",
  "main": "src/index.ts",
  "compatibility_date": "2025-09-01",
  "compatibility_flags": ["nodejs_compat"],

  "triggers": {
    "crons": ["0 18 * * *"]          // 18:00 UTC = 01:00 WIB next day
  },

  "hyperdrive": [
    { "binding": "HYPERDRIVE", "id": "<same-hyperdrive-config-id>" }
  ]
}

import { createDB }           from '@packages/db';
import { decayExpiredStrikes } from '@packages/service/cancellation/strikeDecay';

export default {
  async scheduled(event: ScheduledEvent, env: Env, ctx: ExecutionContext) {
    const db = createDB(env);
    await decayExpiredStrikes(db, {
      actor_id: null, actor_type: 'CRON',
    });
  },
};

21.6 Migration Strategy

Drizzle Kit migrations run from local machine (or CI), NOT from CF Workers (no filesystem access in Workers):

# Local / CI migration workflow:
bunx drizzle-kit generate           # generate SQL from schema changes
bunx drizzle-kit migrate            # apply to Neon via NEON_DATABASE_URL

wrangler.jsonc never contains NEON_DATABASE_URL — migrations hit Neon directly, bypassing Hyperdrive. Production Workers access Neon only through Hyperdrive at runtime.

21.7 Environment Variable Reference

Variable	Where set	Used by
`HYPERDRIVE` (binding)	wrangler.jsonc	`@packages/db` → all Workers
`NEON_DATABASE_URL`	`.dev.vars` + CI secret	Drizzle Kit migrations + local dev
`BETTER_AUTH_SECRET`	wrangler secret	`@packages/auth` → `@apps/web`
`GOOGLE_CLIENT_ID`	wrangler secret	Better Auth Google OAuth
`GOOGLE_CLIENT_SECRET`	wrangler secret	Better Auth Google OAuth
`OTEL_EXPORTER_OTLP_ENDPOINT`	wrangler secret	`@tigorhutasuhut/telemetry-js`
`OTEL_EXPORTER_OTLP_HEADERS`	wrangler secret	`@tigorhutasuhut/telemetry-js`
`R2_ACCOUNT_ID`	wrangler secret	R2 S3 client
`R2_ACCESS_KEY_ID`	wrangler secret	R2 presigned URLs
`R2_SECRET_ACCESS_KEY`	wrangler secret	R2 presigned URLs
`R2_BUCKET_NAME`	`wrangler.jsonc` vars section	R2 client
`R2_BUCKET` (binding)	`wrangler.jsonc`	Workers direct R2 access (future)

21.8 Build + Deploy Pipeline

# From monorepo root via bun workspaces:
bun run build:web          # → apps/web: `wrangler deploy`
bun run build:cron-daily   # → apps/cron-daily: `wrangler deploy`
bun run build:cron-billing # → apps/cron-billing: `wrangler deploy`

# CI (Cloudflare Workers CI via GitHub Actions + wrangler action):
# Trigger: push to main
# Steps: typecheck → lint → unit tests → build → wrangler deploy

§22 Architecture Summary & Section Index (Step 2.19 Final Review)

Section Index

§	Topic	Status
1	Monorepo structure + key invariants	✅ Locked
2	Frontend — TanStack Start, library inventory, Capacitor-ready rules	✅ Locked
3	API Layer — oRPC + OpenAPI, WhoAmI spec, request routing	✅ Locked
4	Database — Neon PG18, Drizzle, extensions, index patterns	✅ Locked
5	Object Storage — R2, presigned PUT URL flow	✅ Locked
6	Observability — SigNoz, telemetry-js, OTEL env vars	✅ Locked
7	Auth — Better Auth, RBAC, permission guard	✅ Locked
8	Partner-authority items — tooling, integration test strategy	✅ Locked
9	(Open: bank soal placeholder — formalized in §19)	—
10	Open verification items (resolve at Step 4)	⏳ Pending Step 4
11	Auth schema (Better Auth + custom RBAC tables, 9 tables)	✅ Locked
12	Frontend UI conventions (brand palette, animation, stability patterns)	✅ Locked
13	Pagination standard (hybrid cursor+offset)	✅ Locked
14	5D Pricing Matrix schema + Tahun Ajaran versioning	✅ Locked
15	Sesi state machine + Schedule schema	✅ Locked
16	Cancellation Policy schema (Option b)	✅ Locked
17	Audit middleware schema (INSERT-only, 7-year retention)	✅ Locked
18	Office, Profiles, Settlement, DocumentUpload, Notification, Anomaly	✅ Locked
19	Bank soal plug-in domain module pattern	✅ Locked
20	i18n strategy — Paraglide JS key naming convention	✅ Locked
21	Deployment architecture (wrangler.jsonc, Hyperdrive, cron config)	✅ Locked
22	This summary	✅

Full Database Schema File Map

Schema file	Tables
`packages/db/schema/auth.ts`	users, sessions, accounts, verifications, two_factor, roles, role_permissions, user_roles, login_audit_events
`packages/db/schema/catalog.ts`	tingkats, segmentasis, kategoris, subjects, subject_levels, golongans
`packages/db/schema/pricing.ts`	academic_years, student_pricing_matrix, tutor_pricing_matrix
`packages/db/schema/scheduling.ts`	schedules, lesson_sessions
`packages/db/schema/cancellation.ts`	cancellation_policy_configs, cancellation_policy_brackets, tutor_strikes, app_configs
`packages/db/schema/audit.ts`	audit_events
`packages/db/schema/org.ts`	offices
`packages/db/schema/profiles.ts`	tutor_profiles, student_profiles
`packages/db/schema/finance.ts`	billing_periods, student_settlements, tutor_settlements
`packages/db/schema/documents.ts`	document_uploads
`packages/db/schema/notifications.ts`	notification_templates, notification_logs, notification_preferences
`packages/bank-soal/schema.ts`	(bank_soal.* — iter 2+ only)

Total tables (public schema): 32 tables across 11 schema files.

Key Cross-cutting Decisions (Quick Reference)

Decision	Location
UUIDv7 for all PKs (`uuidv7()` native in PG 18)	§4
All timestamps `WITH TIME ZONE`	§4
Duration columns as integer `_ms` suffix	§4
snake_case everywhere	§4
`NULLS NOT DISTINCT` for nullable FK in unique indexes	§14
`EXCLUDE USING gist` for tutor double-booking	§15.4
Pricing snapshot at REQUESTED→APPROVED	§14.6 + §15
Audit INSERT-only (trigger-enforced)	§17
Settlement auto-created at COMPLETED/NO_SHOW	§18.8
Cancellation policy immutable (retire + new, no edit)	§16
AuditContext from oRPC middleware → service layer	§17.6
CF-Connecting-IP for real client IP	§17.6
bank_soal Postgres schema namespace	§19.3
Paraglide JS: `namespace__component__desc` key format	§20.3
Drizzle Kit migrations from local/CI (not Workers)	§21.6
S3 API keys for R2 presigned URLs (not Workers binding)	§21.4

Open Items for Step 3+

Item	Step
Verify `rum` extension availability on Neon PG18	Step 4
Confirm Better Auth + TanStack Start + CF Workers timeout resolved	Step 4
Confirm oRPC-in-Nitro integration pattern	Step 3 (bootstrap) ✅ RESOLVED
`@tigorhutasuhut/telemetry-js` CF Workers compat test	Step 4
Final PPh/tax field placement on `tutor_settlements.pph_withheld_idr`	Step 3
`app_configs` seed data migration	Step 3
Bank soal — separate planning doc when iteration begins	Iter 2

End of ARCHITECTURE.md — Step 2 complete (pending user sign-off 2.20). All schema decisions locked.