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

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. 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. 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) } And the ordering key is a linear congruential hash of (id, seed): ORDER BY (id * 1103515245 + :seed * 12345) % 2147483647 fun key(id: Int, seed: Int): Long = (id.toLong() * 1103515245L + seed.toLong() * 12345L) % 2147483647L Two things bit me here, and they're the reason this post exists: 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. 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. 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). 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 https://eltacrew.com/sitedex I'm the developer of both the site and the app.