Episode 3: Database Integration and Schedule Management

Introduction: Bringing the Calendar to Life

In the previous episodes, we established a solid architectural foundation and optimized date calculation engine. Now it’s time to bring the calendar to life by adding the ability for users to create, store, and manage their schedules. This episode covers database design, Room integration, and efficient data persistence strategies.

1. Database Design: Schema and Relationships

1.1. Schedule Entity Design

The core of any calendar app is the schedule (or event) entity. Our design balances simplicity with flexibility:

@Entity(tableName = "schedules")
data class Schedule(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,

    val title: String,
    val description: String? = null,

    // Using Rata Die for efficient date storage
    val startRataDie: Int,
    val endRataDie: Int,

    val startTime: String? = null, // HH:mm format
    val endTime: String? = null,

    val isAllDay: Boolean = false,
    val recurrenceRule: String? = null,

    val createdAt: Long = System.currentTimeMillis(),
    val updatedAt: Long = System.currentTimeMillis()
)

1.2. Why Rata Die for Date Storage?

Storing dates as Rata Die integers instead of Date or Calendar objects offers several advantages:

Performance: Integer comparisons are faster than Date object comparisons Storage: 4 bytes per date vs 12+ bytes for Date objects Calculation: Adding/subtracting days is simple integer arithmetic Consistency: No timezone or DST complications

1.3. Recurrence Pattern Design

For recurring events, we store a recurrence rule string following a simplified iCalendar RFC 5545 format:

FREQ=WEEKLY;INTERVAL=1;BYDAY=MO,WE,FR
FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=15
FREQ=YEARLY;INTERVAL=1;BYMONTH=12;BYMONTHDAY=25

This approach provides flexibility for future expansion while keeping storage minimal.

2. Room Database Integration

2.1. DAO (Data Access Object) Design

The DAO defines how we interact with the database:

@Dao
interface ScheduleDao {
    @Query("SELECT * FROM schedules WHERE startRataDie <= :endRataDie AND endRataDie >= :startRataDie")
    suspend fun getSchedulesInRange(startRataDie: Int, endRataDie: Int): List<Schedule>

    @Query("SELECT * FROM schedules WHERE startRataDie = :rataDie")
    suspend fun getSchedulesForDay(rataDie: Int): List<Schedule>

    @Insert
    suspend fun insert(schedule: Schedule): Long

    @Update
    suspend fun update(schedule: Schedule)

    @Delete
    suspend fun delete(schedule: Schedule)

    @Query("DELETE FROM schedules WHERE id = :scheduleId")
    suspend fun deleteById(scheduleId: Long)
}

2.2. Database Class Setup

@Database(entities = [Schedule::class], version = 1)
abstract class DalendarDatabase : RoomDatabase() {
    abstract fun scheduleDao(): ScheduleDao

    companion object {
        @Volatile
        private var INSTANCE: DalendarDatabase? = null

        fun getDatabase(context: Context): DalendarDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    DalendarDatabase::class.java,
                    "dalendar_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

2.3. Why Room?

Room offers several advantages over raw SQLite:

• Compile-time verification: SQL queries are checked at compile time
• Less boilerplate: No need for cursor management
• LiveData/Flow integration: Reactive data updates
• Migration support: Easy database schema updates

3. Repository Pattern for Data Management

To separate database logic from UI, we implement the Repository pattern:

class ScheduleRepository(private val scheduleDao: ScheduleDao) {

    suspend fun getSchedulesForMonth(year: Int, month: Int): List<Schedule> {
        val startRataDie = calculateMonthStartRataDie(year, month)
        val endRataDie = start RataDie + getDaysInMonth(year, month) - 1
        return scheduleDao.getSchedulesInRange(startRataDie, endRataDie)
    }

    suspend fun getSchedulesForDay(rataDie: Int): List<Schedule> {
        return scheduleDao.getSchedulesForDay(rataDie)
    }

    suspend fun addSchedule(schedule: Schedule): Long {
        return scheduleDao.insert(schedule)
    }

    suspend fun updateSchedule(schedule: Schedule) {
        scheduleDao.update(schedule)
    }

    suspend fun deleteSchedule(schedule: Schedule) {
        scheduleDao.delete(schedule)
    }
}

4. Efficient Query Strategies

4.1. Range Queries for Month View

When displaying a month, we query all schedules within that month’s Rata Die range:

// Get first and last Rata Die of the month
val monthStart = EAFCalculator.ymdToRataDie(year, month, 1)
val monthEnd = monthStart + EAFCalculator.getDaysInMonth(year, month) - 1

// Single query gets all schedules for the entire month
val schedules = repository.getSchedulesInRange(monthStart, monthEnd)

This approach is far more efficient than querying day-by-day.

4.2. Caching Strategy

To avoid repeated database queries during UI scrolling:

class ScheduleCache {
    private val cache = mutableMapOf<Int, List<Schedule>>()

    suspend fun getSchedulesForDay(rataDie: Int, repository: ScheduleRepository): List<Schedule> {
        return cache.getOrPut(rataDie) {
            repository.getSchedulesForDay(rataDie)
        }
    }

    fun invalidate() {
        cache.clear()
    }
}

4.3. Indexing for Performance

We add database indexes to speed up common queries:

@Entity(
    tableName = "schedules",
    indices = [
        Index(value = ["startRataDie"]),
        Index(value = ["endRataDie"]),
        Index(value = ["startRataDie", "endRataDie"])
    ]
)

5. Handling Recurring Events

5.1. Expansion Algorithm

Recurring events require special handling. We expand them into individual occurrences:

fun expandRecurringEvent(schedule: Schedule, rangeStart: Int, rangeEnd: Int): List<Schedule> {
    if (schedule.recurrenceRule == null) return listOf(schedule)

    val occurrences = mutableListOf<Schedule>()
    val rule = parseRecurrenceRule(schedule.recurrenceRule)

    var currentRataDie = schedule.startRataDie

    while (currentRataDie <= rangeEnd) {
        if (currentRataDie >= rangeStart) {
            occurrences.add(schedule.copy(
                id = 0, // Generated occurrence, not stored
                startRataDie = currentRataDie,
                endRataDie = currentRataDie + (schedule.endRataDie - schedule.startRataDie)
            ))
        }

        currentRataDie = calculateNextOccurrence(currentRataDie, rule)
    }

    return occurrences
}

5.2. Performance Considerations

Expanding recurring events can be expensive. We optimize by:

• Only expanding for visible date ranges
• Caching expanded results
• Limiting expansion to reasonable future dates (e.g., 2 years ahead)

6. Testing Strategy

6.1. Unit Tests for Database Operations

@Test
fun testInsertAndRetrieveSchedule() = runBlocking {
    val schedule = Schedule(
        title = "Meeting",
        startRataDie = 738000,
        endRataDie = 738000,
        isAllDay = false
    )

    val id = scheduleDao.insert(schedule)
    val retrieved = scheduleDao.getSchedulesForDay(738000)

    assertEquals(1, retrieved.size)
    assertEquals("Meeting", retrieved[0].title)
}

6.2. Integration Tests

We test the entire data flow from Repository to UI:

@Test
fun testMonthScheduleRetrieval() = runBlocking {
    // Insert test schedules
    insertTestSchedules()

    // Query month
    val schedules = repository.getSchedulesForMonth(2025, 11)

    // Verify correct schedules returned
    assertTrue(schedules.isNotEmpty())
}

7. Migration Strategy

Planning for future schema changes:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE schedules ADD COLUMN color INTEGER DEFAULT 0")
    }
}

Room.databaseBuilder(context, DalendarDatabase::class.java, "dalendar_database")
    .addMigrations(MIGRATION_1_2)
    .build()

Conclusion: Data Layer Complete

We now have a robust data layer featuring:

✅ Efficient Rata Die-based date storage ✅ Room database with compile-time query verification ✅ Repository pattern for clean separation ✅ Optimized range queries and caching ✅ Recurring event support ✅ Comprehensive testing

In the next episode, we’ll build the UI for schedule creation and editing, connecting our data layer to a beautiful, intuitive user interface.

Next Episode Preview:

• Schedule creation/editing UI
• Date/time picker implementation
• Recurrence rule UI builder
• Calendar integration with schedules

The foundation is solid—now we build the features users will love!