# Android Data-Layer Optimization Patterns

## Room Composite Indexes for Query Acceleration

### Pattern: Multi-column filter + sort

When a DAO query does `WHERE col1 = ? AND col2 = ? ORDER BY col3 DESC`, Room/SQLite does a full table scan + sort without the right index.

**Fix:** Add a composite `@Entity` index covering the filter columns AND the sort column:

```kotlin
@Entity(
    tableName = "watch_progress",
    indices = [
        Index(value = ["profileId", "mediaId"], unique = true),
        Index(value = ["profileId", "isWatched", "lastPlayedAt"]),  // NEW
    ]
)
data class WatchProgress(
    val profileId: Int,
    val mediaId: String,
    val isWatched: Boolean,
    val lastPlayedAt: Long,
    // ...
)
```

**Migration:** Version bump + `CREATE INDEX IF NOT EXISTS`:

```kotlin
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "CREATE INDEX IF NOT EXISTS `ix_progress_pid_watched_played` " +
            "ON `watch_progress` (`profileId`, `isWatched`, `lastPlayedAt`)"
        )
    }
}
```

**Register:** `.addMigrations(DB.MIGRATION_1_2, DB.MIGRATION_2_3)` in the Road builder.

**Result:** `getContinueWatching(profileId, limit)` goes from O(n) full scan + sort to O(log n) index range scan.

## Cache Pruning — Pre-Computed Cutoff

### Problem: SQLite can't use index with arithmetic expressions

```sql
-- OLD — index on timestamp ignored, full table scan
DELETE FROM addon_cache WHERE (:now - timestamp) > ttl
```

### Solution: Pre-compute cutoff in Kotlin

```kotlin
// Pre-compute: 24h = longest possible TTL
val cutoff = System.currentTimeMillis() - 86_400_000L
dao.pruneExpired(cutoff)
```

```kotlin
@Query("DELETE FROM addon_cache WHERE timestamp < :cutoff")
suspend fun pruneExpired(cutoff: Long)
```

This lets SQLite use the index on `timestamp` directly for O(log n) seek.

**Entity must have the index:**

```kotlin
@Entity(tableName = "addon_cache", indices = [Index(value = ["timestamp"])])
```

**Choosing cutoff:** Use the longest possible TTL in the table (e.g., 24h for MANIFEST/SUBTITLE entries). This is conservative — no fresh entry is deleted, and every expired entry is cleaned up.

## OkHttp Rate Limiting — Sliding Window Interceptor

### Pattern: Sliding window with ConcurrentLinkedDeque

```kotlin
class RateLimitInterceptor(
    private val maxRequests: Int = 40,
    private val windowMs: Long = 10_000L,
) : Interceptor {
    private val callTimes = ConcurrentLinkedDeque<Long>()

    override fun intercept(chain: Chain): Response {
        val windowStart = System.currentTimeMillis() - windowMs
        while (callTimes.peekFirst() != null && callTimes.peekFirst() < windowStart) {
            callTimes.pollFirst()
        }

        if (callTimes.size >= maxRequests) {
            val oldest = callTimes.peekFirst()
            if (oldest != null) Thread.sleep(oldest - windowStart)
        }

        callTimes.addLast(System.currentTimeMillis())

        val response = chain.proceed(chain.request())
        if (response.code == 429) {
            val retrySecs = response.header("Retry-After")?.toLongOrNull() ?: 1L
            response.close()
            Thread.sleep(retrySecs * 1000L)
            return chain.proceed(chain.request())
        }
        return response
    }
}
```

**Wire it first** — before logging interceptors:

```kotlin
OkHttpClient.Builder()
    .addInterceptor(RateLimitInterceptor())    // FIRST
    .addInterceptor(HttpLoggingInterceptor())  // SECOND
    .build()
```

## Narrow Exception Handling — Never Catch Exception

### Problem: Broad `catch (e: Exception)` swallows programming bugs

`Exception` catches `NullPointerException`, `IndexOutOfBoundsException`, `ClassCastException` — all programming errors that should crash the process so they're visible.

### Solution: Catch only recoverable I/O + protocol failures

```kotlin
try {
    api.call()
} catch (e: CancellationException) {
    throw e                                          // cooperative cancellation
} catch (e: java.io.IOException) {
    Result.failure(e)                                 // DNS/SSL/timeout
} catch (e: retrofit2.HttpException) {
    Result.failure(e)                                 // 4xx/5xx
} catch (e: SerializationException) {
    Result.failure(e)                                 // malformed JSON
}
// OutOfMemoryError, AssertionError → propagate uncaught (fail-fast)
```

**Coverage:** `IOException` covers `SocketTimeoutException`, `UnknownHostException`, `SSLException`. `HttpException` covers non-2xx. `SerializationException` covers JSON parse/schema errors.

**Coroutine rule:** Always rethrow `CancellationException` — it's the structured concurrency cancellation signal. Swallowing it leaks coroutines.