DEV Community

EltaCrew
EltaCrew

Posted on

I turned my website directory into an offline Android app. Here's the one SQL trick that made it work

#android #showdev #kotlin #sqlite

For about two months I've run a small web directory of useful sites and AI tools at eltacrew.com/sitedex. It's a server-rendered Astro site with Postgres behind it. This week I shipped the same directory as an Android app — and the interesting part isn't the app, it's the constraint I gave myself: the app must work with the network off, and must show every user the same "today's picks" without asking a server.

Here's how that went.

The constraint

A directory app that fetches a list from an API is a web page with extra steps. If the API is slow, the app is slow; if I take the server down, the app is dead. So the data (~800 sites, 9 categories, tags, pricing, descriptions in 5 languages) ships inside the APK as a JSON asset and is loaded into a Room database on first run. Search, filter and browse all hit SQLite locally. The only things that touch the network are the site thumbnails (served from my box) and the ad SDK.

That gave me a second problem.

"Today's picks" without a server

On the web version the home page shuffles the featured sites once a day, so it looks alive but stays stable within the day. The server just does that. In the app there is no server in the loop, and I also wanted this to be a Paging query — the list is long and I didn't want to load 800 rows to shuffle them in Kotlin.

So the shuffle has to be an ORDER BY expression that SQLite can evaluate, seeded by the date, and I need Kotlin to be able to compute the same ordering (for the detail screen's "next / previous" navigation).

The seed is just today's date:

fun today(): Int {
    val c = Calendar.getInstance()
    return c.get(Calendar.YEAR) * 10000 + (c.get(Calendar.MONTH) + 1) * 100 + c.get(Calendar.DAY_OF_MONTH)
}
Enter fullscreen mode Exit fullscreen mode

And the ordering key is a linear congruential hash of (id, seed):

ORDER BY (id * 1103515245 + :seed * 12345) % 2147483647
Enter fullscreen mode Exit fullscreen mode 
fun key(id: Int, seed: Int): Long =
    (id.toLong() * 1103515245L + seed.toLong() * 12345L) % 2147483647L
Enter fullscreen mode Exit fullscreen mode

Two things bit me here, and they're the reason this post exists:

  1. SQLite has no md5(), no XOR, no random(seed). Anything fancier than multiply/add/modulo isn't available in a Room query without a custom function. So the hash is deliberately dumb.
  2. SQLite integers are 64-bit; Kotlin Int is 32-bit and wraps. My first Kotlin version used Int and produced a different order from the SQL, so "next" on the detail screen jumped somewhere random. Do the arithmetic in Long on the Kotlin side and it matches exactly.

Pull-to-refresh uses a seed derived from the current time instead, so it's a genuinely new shuffle that can never collide with the day's seed.

What else is in the app

  • Three view modes (mixed cards, list, grid), search, category and pricing filters.
  • Five languages (en, ko, ja, zh, es) — the descriptions are bundled, not translated at runtime.
  • A submit form that posts to the web directory, so app users and web users feed the same catalogue.
  • No login, no account, no billing code. Ads are one banner in a reserved slot plus a dismissible native card on start and exit — no interstitials, app-open or rewarded ads (InterstitialAd/AppOpenAd/RewardedAd: zero occurrences).

What I'd tell past me

  • Bundling the data was the right call. First-run is instant and the app is indifferent to my server's uptime. The cost is that adding sites means shipping an app update — fine for a directory that changes weekly, wrong for anything real-time.
  • If Kotlin and SQL both need the same ordering, write the SQL first and make Kotlin imitate it, not the other way around.
  • Paging treats an empty table as "loaded, zero rows", not "loading" — so on first run, before the JSON import finished, the list was a blank white screen with no spinner. Gate the list on the import, not on the query.

It went live this week; the web version has been up since July. If you try the app and something's off — especially a site that's dead or miscategorised — the submit form goes straight to me.

Play Store: https://play.google.com/store/apps/details?id=com.eltacrew.sitedex
Web: https://eltacrew.com/sitedex

I'm the developer of both the site and the app.

Top comments (1)

Collapse
 
raknaos profile image
Raknaos •

The Int-vs-Long wrap is the kind of bug that only shows up as 'next' jumping somewhere random, and the fact that you traced it to the arithmetic width rather than the query is the real story here.

One thing I'd be curious about: your seed is a local-date integer, so two users on opposite sides of midnight get different orderings, and the detail screen's next/previous has to agree with the list. Did you pin the seed to UTC on device, or just accept that a user crossing midnight mid-session sees the shuffle change under them? We hit the same class of drift with any date-seeded rotation in our daily jobs.