<!-- mobian-agent-page publisher="dailydev" canonical="https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc" -->

---
title: I turned my website directory into an offline Android...
description: A developer describes converting a Postgres-backed web directory into an offline-first Android app by bundling ~800 entries as a JSON asset loaded into a local...
canonical: https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc
twitter:card: summary_large_image
twitter:site: @dailydotdev
og:type: website
og:site_name: daily.dev
og:title: I turned my website directory into an offline Android app. Here’s the one SQL trick that made it work | daily.dev
og:description: A developer describes converting a Postgres-backed web directory into an offline-first Android app by bundling ~800 entries as a JSON asset loaded into a local...
og:url: https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc
og:image: https://api.daily.dev/og/posts/5QeEcsxTc.png
og:image:alt: I turned my website directory into an offline Android app. Here’s the one SQL trick that made it work
og:image:width: 1200
og:image:height: 630
og:locale: en
---

> ## Documentation Index
> Fetch the complete documentation index at: https://daily.dev/llms.txt
> Use this file to discover all available pages before exploring further.

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

**[Medium](https://daily.dev/sources/medium_js)** · 4 min read · 0 upvotes · 0 comments

## Summary

A developer describes converting a Postgres-backed web directory into an offline-first Android app by bundling ~800 entries as a JSON asset loaded into a local Room/SQLite database. The core technical challenge was replicating a server-side daily 'shuffle' of featured items purely with SQL, since SQLite lacks md5/XOR/random(seed) functions. The solution uses a linear congruential hash ORDER BY expression seeded by the date, with a matching Kotlin implementation that must use Long instead of Int to avoid integer overflow mismatches. Other lessons include gating the UI on JSON import completion since Android's Paging library treats an empty table as 'loaded' rather than 'loading'.

## Full article

daily.dev links to this article rather than hosting it. Read it at the original source: <https://medium.com/@eltacrewlab/i-turned-my-website-directory-into-an-offline-android-app-ccf94f2cdacb>

## Questions this post answers

### How do I get SQLite to produce a deterministic daily shuffle without a random(seed) function?

Use a linear congruential hash as an ORDER BY expression seeded by the current date, since SQLite has no md5(), XOR, or random(seed). The formula (id * 1103515245 + seed * 12345) % 2147483647 works using only multiply, add, and modulo, which SQLite supports natively in a query without a custom function.

_daily.dev surfaces practical SQLite workarounds like this for developers building offline-first apps._

### Why does my Kotlin code produce a different sort order than the same hash formula in SQLite?

SQLite integers are 64-bit while Kotlin's Int is 32-bit and silently wraps on overflow, producing a different result for the same linear congruential hash formula. Computing the hash using Long instead of Int on the Kotlin side matches the SQL ordering exactly, which matters for features like next/previous navigation that must stay in sync with a SQL-driven list.

_developers debugging SQL-to-Kotlin ordering mismatches can track fixes like this on daily.dev._

### Why does my Android Paging list show a blank screen instead of a loading spinner on first launch?

Android's Paging library treats an empty database table as "loaded, zero rows" rather than "loading", so if data is still being imported asynchronously on first run, the UI shows a blank white screen instead of a spinner. The fix is to gate the list display on the import process finishing, not on the Paging query's loading state.

_daily.dev helps Android developers spot edge cases like Paging's empty-table behavior before shipping._

## Similar posts on daily.dev

- [The Complete Guide to Offline-First Architecture in Android](https://daily.dev/posts/the-complete-guide-to-offline-first-architecture-in-android-jhpom6lhe) · droidcon · 0 upvotes · 0 comments
- [Why Offline-First Inventory Apps Are Harder Than They Look](https://daily.dev/posts/why-offline-first-inventory-apps-are-harder-than-they-look-1mp4rzjez) · Medium · 0 upvotes · 0 comments
- [Your ‘App’ Could Have Been a Webpage \(so I fixed it for you…\)](https://daily.dev/posts/your-app-could-have-been-a-webpage-so-i-fixed-it-for-you--opi87o8tc) · Lobsters · 0 upvotes · 1 comments
- [Offline-first frontend apps in 2025: IndexedDB and SQLite in the browser and beyond](https://daily.dev/posts/offline-first-frontend-apps-in-2025-indexeddb-and-sqlite-in-the-browser-and-beyond-djyieqljy) · LogRocket · 1 upvotes · 0 comments

---

Tags: [#android](https://daily.dev/tags/android), [#kotlin](https://daily.dev/tags/kotlin), [#sqlite](https://daily.dev/tags/sqlite), [#offline-first](https://daily.dev/tags/offline-first)

[View this post on daily.dev](https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc)

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]}
{"@context":"https://schema.org","@type":"TechArticle","headline":"I turned my website directory into an offline Android app. Here’s the one SQL trick that made it work","url":"https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc"},"datePublished":"2026-09-15T19:31:35.249Z","dateModified":"2026-09-15T19:33:44.568Z","description":"A developer describes converting a Postgres-backed web directory into an offline-first Android app by bundling ~800 entries as a JSON asset loaded into a local...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/6e9532771ee0d66de24bb4c4863c1020?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/6e9532771ee0d66de24bb4c4863c1020?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Medium","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Medium","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/medium","url":"https://daily.dev/sources/medium_js"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"android,kotlin,sqlite,offline-first","timeRequired":"PT4M"}
{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Medium","item":"https://daily.dev/sources/medium_js"},{"@type":"ListItem","position":3,"name":"I turned my website directory into an offline Android app. Here’s the one SQL trick that made it work"}]}
{"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/i-turned-my-website-directory-into-an-offline-android-app-here-s-the-one-sql-trick-that-made-it-wor-5qeecsxtc#faq","mainEntity":[{"@type":"Question","name":"How do I get SQLite to produce a deterministic daily shuffle without a random(seed) function?","acceptedAnswer":{"@type":"Answer","text":"Use a linear congruential hash as an ORDER BY expression seeded by the current date, since SQLite has no md5(), XOR, or random(seed). The formula (id * 1103515245 + seed * 12345) % 2147483647 works using only multiply, add, and modulo, which SQLite supports natively in a query without a custom function. daily.dev surfaces practical SQLite workarounds like this for developers building offline-first apps."}},{"@type":"Question","name":"Why does my Kotlin code produce a different sort order than the same hash formula in SQLite?","acceptedAnswer":{"@type":"Answer","text":"SQLite integers are 64-bit while Kotlin's Int is 32-bit and silently wraps on overflow, producing a different result for the same linear congruential hash formula. Computing the hash using Long instead of Int on the Kotlin side matches the SQL ordering exactly, which matters for features like next/previous navigation that must stay in sync with a SQL-driven list. developers debugging SQL-to-Kotlin ordering mismatches can track fixes like this on daily.dev."}},{"@type":"Question","name":"Why does my Android Paging list show a blank screen instead of a loading spinner on first launch?","acceptedAnswer":{"@type":"Answer","text":"Android's Paging library treats an empty database table as \"loaded, zero rows\" rather than \"loading\", so if data is still being imported asynchronously on first run, the UI shows a blank white screen instead of a spinner. The fix is to gate the list display on the import process finishing, not on the Paging query's loading state. daily.dev helps Android developers spot edge cases like Paging's empty-table behavior before shipping."}}]}
```

