Last updated: 2026-03-05
| Priority | Category | Issues | Status |
|---|
| P0 | Critical bottlenecks | 4 | ✅ All fixed |
| P1 | Performance at 100+ teachers | 6 | ✅ All fixed |
| P2 | Future optimization | 5 | Documented |
Current safe scale: ~50 teachers / 2,500 students
Target after fixes: 500+ teachers / 25,000+ students
- Multi-tenancy with
teacherId scoping on all tables
- Circuit breakers + Redis-backed rate limiting for Google/Stripe APIs
- Credit ledger idempotency via
INSERT ON CONFLICT
- Webhook idempotency (atomic dedup)
- Soft-delete pattern with
notDeleted() helper (7 tables)
- Route lazy loading (all frontend routes)
- Optimistic mutations for low-risk actions
- BullMQ job queues with retry/backoff
| Table | Index Added | Query Pattern |
|---|
contact_log | (teacher_id, student_id, sent_at DESC) | Student list last-contact aggregation |
contact_log | (teacher_id, sent_at DESC) | Teacher contact history |
availability_rules | (teacher_id, is_active) WHERE is_active = true | Slot generation on every booking |
sessions | (teacher_id, status, starts_at, ends_at) | Conflict detection for bookings |
| 7 soft-delete tables | (teacher_id) WHERE deleted_at IS NULL | Partial indexes for active records |
Migration: 0074_add_performance_indexes.sql — 11 indexes total. Schema files updated for contact_log, availability_rules, class_sessions.
| |
|---|
| Files | slot-engine.ts, session-service.ts |
| Fix | isSlotAvailable() uses SELECT ... FOR UPDATE SKIP LOCKED when called inside a transaction. 6 session-creation methods now wrap availability check + insert in db.transaction(): bookSession, bookMultipleSessions, createTrialSession, scheduleManualSession, rescheduleSession, createRecurringSessions. |
| |
|---|
| File | slot-engine.ts |
| Fix | MAX_SLOT_RANGE_DAYS = 14 — date range capped at top of getAvailableSlots(). Prevents 60K+ array elements in memory. |
| |
|---|
| File | packages/db/src/index.ts |
| Fix | postgres(connectionString, { max: 20, idle_timeout: 30, connect_timeout: 5 }) |
| File | Method | Fix |
|---|
review-service.ts | getStats() | Replaced in-memory aggregation with COUNT(*), AVG(rating), GROUP BY rating SQL |
legal-document-service.ts | create() | Replaced findMany + reduce with SELECT MAX(sort_order) SQL aggregate |
| Worker | Pattern | Fix |
|---|
lifecycle-detection-worker | Was: sequential loop, concurrency: 1 | Dispatcher + per-teacher jobs, concurrency: 5 |
content-analytics-worker | Was: sequential loop, concurrency: 1 | Dispatcher + per-teacher jobs, concurrency: 5 |
metric-alerts-worker | Was: sequential loop, concurrency: 1 | Dispatcher + per-teacher jobs, concurrency: 5 |
Pattern: Cron triggers a dispatcher job (no teacherId) that queries all teachers and enqueues one job per teacher on the same queue. Per-teacher jobs run in parallel with concurrency: 5. Date-based jobId (worker-{teacherId}-{YYYY-MM-DD}) provides daily idempotency. Individual teacher errors throw (proper BullMQ failure tracking) instead of silently continuing.
| |
|---|
| Fix | Added staleTime: 2min to dashboard summary, smart actions, pending sessions, and calendar queries. Settings query cached for 5 min. |
Backend Redis cache remains a future optimization.
| Route | Query | staleTime Added |
|---|
| Students | teacher-students | 2 min |
| Messages | teacher-students-messages | 2 min |
| Payments | teacher-payments, teacher-payments-kpis | 1 min |
| Dashboard | teacher-dashboard-summary, teacher-dashboard-smart-actions, teacher-sessions-pending, teacher-calendar | 2 min |
| Dashboard | teacher-settings | 5 min |
| Payments | teacher-settings | 5 min |
| Organization | tags | 5 min |
Total: 11 useQuery hooks updated across 5 route files.
| |
|---|
| Files | session-service.ts, calendar-sync-worker.ts |
| Fix | queueCalendarSync() marks syncStatus='pending' in session_calendar_sync and enqueues a BullMQ job. Calendar-sync worker (5-min cron) processes pending records in batches of 20. Session creation never blocks on Google Calendar API. Circuit breaker skips batch when google-calendar circuit is open. |
10 call sites in session-service.ts use queueCalendarSync() for create/update/delete operations.
| |
|---|
| Files | session-service.ts, lib/redis.ts |
| Fix | Cache-aside pattern: getSession() checks Redis (session:{id} key, 30s TTL) → falls back to PostgreSQL on miss → populates cache on hit. deleteSession() invalidates cache before DB delete. All Redis operations wrapped in try/catch — auth falls back to direct DB queries if Redis is unavailable. Shared ioredis singleton (lib/redis.ts) with lazy connect and exponential backoff. |
| # | Issue | Notes |
|---|
| P2-1 | No list virtualization (students, contacts, payments) | Add @tanstack/react-virtual when lists exceed 100 items |
| P2-2 | i18n: 5,700 lines in single file, both languages loaded | Split into namespace files, lazy-load secondary language |
| P2-3 | Analytics queries without materialized views | Create retention_cohorts materialized view, refresh hourly |
| P2-4 | No BullMQ queue depth monitoring | Add Prometheus metrics + alerts on depth > threshold |
| P2-5 | Teachers table: 38KB avg JSONB per row | Ensure all queries use column selection, not SELECT * |
| Metric | Current (~10) | 100 teachers | 1,000 teachers |
|---|
| Students | ~500 | 5K | 50K |
| Sessions | ~5K | 50K | 500K |
| Contact logs | ~2K | 20K | 200K |
| Auth queries/sec | ~10 | ~100 | ~1,000 → Redis cached (P1-6 ✅) |
| Slot generation | 10ms | 30ms | 100ms (was 500ms) |
| Lifecycle job | 10s | 100s | ~34min with concurrency:5 (P1-2 ✅) |
