Lesson 59 of 83 advanced

System Design II: School/Productivity App with Local DB & Sync

Architecting Hazira Khata — attendance, reports, offline-first, multi-role

Open interactive version (quiz + challenge)

Real-world analogy

Designing a school management app is like building a distributed gradebook that every teacher carries in their pocket, that works even when the school's internet goes down, syncs to the principal's dashboard overnight, and generates printable report cards on demand. The tricky part: the principal, teacher, parent, and student all see different versions of the same data.

What is it?

School management app system design combines multi-role access control, offline-first attendance marking, Firebase+Room hybrid sync, on-device report generation, push notification targeting, and export features. It is a common system design question in Bangladeshi and South Asian tech interviews, where edtech and school management apps are a significant market. The Hazira Khata framing adds local market context that interviewers find impressive.

Real-world relevance

Hazira Khata is a conceptual school management app for Bangladesh's 100,000+ schools. Similar real apps include Shikho (edtech), 10MS (Math Solution), and various government school management systems used by the Bangladesh Directorate of Primary Education. These apps must handle patchy 3G coverage in rural schools, extremely varied device hardware, and non-technical primary users.

Key points

Code example

// Room schema — core entities
@Entity(tableName = "attendance_records",
    indices = [
        Index(value = ["student_id", "date"], unique = true),
        Index(value = ["class_id", "date"])
    ])
data class AttendanceRecord(
    @PrimaryKey val id: String = UUID.randomUUID().toString(),
    val studentId: String,
    val classId: String,
    val date: String,
    val status: AttendanceStatus,
    val markedBy: String,
    val syncStatus: SyncStatus = SyncStatus.PENDING
)

enum class AttendanceStatus { PRESENT, ABSENT, LATE, LEAVE }

// DAO with aggregation query for reports
@Dao
interface AttendanceDao {
    @Query("""
        SELECT studentId,
               SUM(CASE WHEN status = 'PRESENT' THEN 1 ELSE 0 END) as presentDays,
               SUM(CASE WHEN status = 'ABSENT' THEN 1 ELSE 0 END) as absentDays,
               SUM(CASE WHEN status = 'LATE' THEN 1 ELSE 0 END) as lateDays,
               COUNT(*) as totalDays
        FROM attendance_records
        WHERE classId = :classId AND date BETWEEN :from AND :to
        GROUP BY studentId
    """)
    suspend fun getMonthlyReport(classId: String, from: String, to: String): List<AttendanceSummary>

    @Query("SELECT * FROM attendance_records WHERE classId = :classId AND date = :date")
    fun getTodayAttendance(classId: String, date: String): Flow<List<AttendanceRecord>>

    @Upsert
    suspend fun upsert(record: AttendanceRecord)
}

// Firebase + Room hybrid repository
class AttendanceRepository(
    private val db: AppDatabase,
    private val firestore: FirebaseFirestore,
    private val prefs: SyncPrefs
) {
    // Read always from Room (offline-safe)
    fun observeTodayAttendance(classId: String, date: String): Flow<List<AttendanceRecord>> =
        db.attendanceDao().getTodayAttendance(classId, date)

    // Write to Room first, then sync to Firebase
    suspend fun markAttendance(record: AttendanceRecord) {
        db.attendanceDao().upsert(record)
        // WorkManager will sync later
    }

    // Firebase -> Room sync (called when online)
    suspend fun syncFromFirebase(classId: String) {
        val lastSync = prefs.getLastSyncTime(classId)
        firestore.collection("attendance")
            .whereEqualTo("classId", classId)
            .whereGreaterThan("updatedAt", lastSync)
            .get()
            .await()
            .documents
            .forEach { doc ->
                val record = doc.toObject(AttendanceRecord::class.java) ?: return@forEach
                db.attendanceDao().upsert(record)
            }
        prefs.setLastSyncTime(classId, System.currentTimeMillis())
    }
}

// PDF report generation
class ReportGenerator(private val context: Context) {

    suspend fun generateMonthlyPdf(
        className: String,
        summaries: List<AttendanceSummary>
    ): File = withContext(Dispatchers.IO) {
        val pdfDocument = PdfDocument()
        val pageInfo = PdfDocument.PageInfo.Builder(595, 842, 1).create() // A4
        val page = pdfDocument.startPage(pageInfo)
        val canvas = page.canvas

        val paint = Paint().apply {
            textSize = 16f
            color = Color.BLACK
        }
        canvas.drawText("Monthly Attendance Report — $className", 40f, 60f, paint)

        paint.textSize = 12f
        var y = 100f
        summaries.forEach { summary ->
            canvas.drawText(
                "${summary.studentName}: ${summary.presentDays}/${summary.totalDays} days present",
                40f, y, paint
            )
            y += 25f
        }
        pdfDocument.finishPage(page)

        val file = File(context.cacheDir, "report_${System.currentTimeMillis()}.pdf")
        pdfDocument.writeTo(file.outputStream())
        pdfDocument.close()
        file
    }
}

// FCM notification targeting
class NotificationService {
    private val messaging = FirebaseMessaging.getInstance()

    fun subscribeToSchool(schoolId: String) {
        messaging.subscribeToTopic("school_$schoolId")
    }

    fun subscribeToClass(classId: String) {
        messaging.subscribeToTopic("class_$classId")
    }

    // Server-side: send to topic (shown as pseudo-code)
    // POST https://fcm.googleapis.com/fcm/send
    // { "to": "/topics/school_123", "notification": { "title": "...", "body": "..." } }
}

// Paging 3 for large student lists
class StudentPagingSource(private val db: AppDatabase, private val classId: String) : PagingSource<Int, Student>() {
    override suspend fun load(params: LoadParams<Int>): LoadResult<Int, Student> {
        val page = params.key ?: 0
        return try {
            val students = db.studentDao().getStudentsPaged(classId, params.loadSize, page * params.loadSize)
            LoadResult.Page(
                data = students,
                prevKey = if (page == 0) null else page - 1,
                nextKey = if (students.size < params.loadSize) null else page + 1
            )
        } catch (e: Exception) {
            LoadResult.Error(e)
        }
    }
    override fun getRefreshKey(state: PagingState<Int, Student>): Int? = state.anchorPosition
}

Line-by-line walkthrough

  1. 1. The unique index on (student_id, date) in AttendanceRecord enforces one record per student per day at the database level — INSERT OR REPLACE semantics with @Upsert handles re-marking correctly.
  2. 2. The second index on (class_id, date) supports the daily class roster query efficiently — without it, retrieving all attendance for a class on a given day requires a full table scan.
  3. 3. The SQL aggregation query uses CASE WHEN inside SUM — this is a single-pass aggregation that computes present, absent, and late counts without multiple queries, efficient for report generation.
  4. 4. observeTodayAttendance returns Flow> — Room emits a new list on every change, driving the RecyclerView to update immediately when a teacher taps a student status.
  5. 5. syncFromFirebase uses whereGreaterThan('updatedAt', lastSync) — this is the delta sync pattern for Firestore, fetching only documents changed since last sync.
  6. 6. prefs.setLastSyncTime() uses server-returned timestamp, not device clock — prevents missed records from devices with clock drift.
  7. 7. withContext(Dispatchers.IO) in generateMonthlyPdf ensures PDF generation never blocks the main thread — PdfDocument.writeTo() is a disk write that can take 500ms+ for large reports.
  8. 8. PdfDocument requires explicit startPage/finishPage pairing — each page is rendered to a canvas then finalized before writing; multiple pages require looping this pattern.
  9. 9. FCM topic subscription (subscribeToTopic) happens on the client — the app subscribes the current device to the school's topic. Server sends to the topic, FCM fans out to all subscribed devices.
  10. 10. StudentPagingSource.load() computes offset as page * params.loadSize — this converts the page index to a SQL OFFSET value for the Room query, enabling efficient pagination without loading all records.

Spot the bug

class AttendanceViewModel(private val repo: AttendanceRepository) : ViewModel() {

    fun markAllPresent(students: List<Student>, classId: String) {
        val today = SimpleDateFormat("yyyy-MM-dd").format(Date())
        students.forEach { student ->
            viewModelScope.launch {
                repo.markAttendance(
                    AttendanceRecord(
                        studentId = student.id,
                        classId = classId,
                        date = today,
                        status = AttendanceStatus.PRESENT,
                        markedBy = "current_teacher"
                    )
                )
            }
        }
    }
}
Need a hint?
There are two bugs: one is a performance/correctness issue caused by excessive coroutine launches, and one causes a crash on devices with non-English locale settings.
Show answer
Bug 1: Launching a separate viewModelScope.launch{} coroutine for each student in a forEach creates N concurrent coroutines for N students. For a class of 50 students, this fires 50 simultaneous Room write operations. Room uses a single write connection — these will serialize internally but create unnecessary coroutine overhead and make it impossible to wrap all inserts in a single transaction. If any one fails (e.g., duplicate key), the others continue independently, resulting in partial state. Fix: launch a single coroutine, collect all records into a list, and call a batch @Upsert that takes List<AttendanceRecord> in one Room transaction. Bug 2: SimpleDateFormat('yyyy-MM-dd').format(Date()) without a Locale parameter uses the device's default locale. On devices with Arabic locale, date formatting can produce Arabic-Indic numerals (e.g., '٢٠٢٦-٠٣-١١' instead of '2026-03-11'). This causes date comparison queries to fail silently — attendance is stored with a locale-specific string but queried with an ASCII date string. Fix: SimpleDateFormat('yyyy-MM-dd', Locale.US) or use Java time's ISO_LOCAL_DATE which is locale-independent.

Explain like I'm 5

Imagine every teacher has a magic notebook that works even without internet. They write attendance in the notebook all day. When they get home and connect to Wi-Fi, the notebook automatically sends everything to a giant school filing cabinet in the cloud. The principal can look at the cloud cabinet. Parents get a text message summary. The notebook always has the latest info, even if the cloud is slow.

Fun fact

Bangladesh has approximately 130,000 primary schools and 20,000 secondary schools. If even 10% adopted a digital attendance system, that would mean 1.5 million daily attendance records generated on Android devices — most with patchy connectivity. This is why every Bangladeshi edtech startup eventually confronts the offline-first architecture problem.

Hands-on challenge

Design the attendance marking and reporting system for Hazira Khata with these constraints: 50K teachers, 500K students, 300 school days/year. Specify: (1) Complete Room schema with all indices justified. (2) The offline attendance marking flow with idempotency guarantees. (3) Firebase security rules that enforce teacher-to-class scoping. (4) The on-device PDF generation flow including how to handle 40+ students on a single report page. (5) How parent notifications are triggered when attendance is synced — define the exact event flow from teacher tap to parent FCM notification. (6) How you handle a teacher who marks attendance offline for 3 days then syncs — what conflict strategy applies if the admin manually entered absence records during that period?

More resources

Open interactive version (quiz + challenge) ← Back to course: Android Interview Mastery