SQU IT Exit: Duplicates, Long-Lived Tokens and the Crash on Real Devices

Duplicates, Long-Lived Tokens and the Crash on Real Devices

An agricultural extension office uses an Android app (Kotlin, minSdk 24) for field officers to record farm visits. Forty-five officers work in areas with no coverage for up to three days. Each visit record is small, but up to six photos of 3 to 5 MB accompany it. The app writes JSON to a local SQLite table and, on reconnect, POSTs each record to a REST API. Complaints this quarter: about 8% of visits appear two or three times on the head-office dashboard, always after a flaky connection, and the API has no idempotency key, creating a new row for every POST; officers report being logged out randomly and retyping passwords in the field, so a developer 'fixed' it by storing the JWT in plain SharedPreferences with a 30-day expiry; and the visit list screen calls GET /visits with no paging, so the app now crashes on some devices once a district exceeds roughly 1,500 records, although officers say it works on the test phone in the office. The backend team is two people and can ship a change next sprint.

1. Design an idempotent sync so a retried upload cannot create a second visit, including what has to happen before the client release ships.

Read the worked answer

The server is where duplicates are actually prevented; the client can only make it likely.

Add client_uuid UUID NOT NULL to visits with a unique index. The client generates it with UUID.randomUUID() when the record is created in the field and stores it in the same SQLite row, not at upload time, which is the current mistake in disguise.
The POST becomes INSERT ... ON CONFLICT (client_uuid) DO NOTHING RETURNING id. If no row is returned, select the existing one and return 200 with it. A retry after a timeout whose first attempt actually succeeded then returns the same canonical record instead of a second visit.
Photos need their own key: upload each with its own UUID or a SHA-256 of its bytes, so a retried visit does not attach six more copies. Record first, then images by reference, keeps the small JSON call fast on a poor link and lets images resume independently.
Client state machine: pending, in_flight, synced, with an attempt counter, exponential backoff with jitter, and 200, 201 and 409 all treated as success.

Sequencing risk, which is the part that gets skipped. The unique index cannot be created while duplicates already exist, so a backfill (de-duplicate on officer, person and date, keep the earliest) must run first. And the client release generating UUIDs must be in the field before the API starts ignoring requests without a key; an old APK still installed will keep creating duplicates, and there is always one old APK.

2. The 30-day token in plain SharedPreferences was introduced to stop random logouts. Assess the change and propose a better solution that actually removes the complaints.

Read the worked answer

What the fix actually did. Plain SharedPreferences is an XML file in the app's private directory: readable on a rooted or debuggable device, and included in cloud backup unless excluded. Extending the token to 30 days turns a stolen token into a month of access, and because a bearer token carries no proof of who is holding it, the server cannot distinguish the thief from the officer.

What to do instead.

Access token: short-lived, about 15 minutes, held in memory only, so it dies with the process.
Refresh token: long-lived, stored via EncryptedSharedPreferences with a Keystore-backed key, or in the Keystore directly. Rotate it on every use and add reuse detection that revokes the whole family if an old refresh token is presented twice.
Set android:allowBackup="false" or an explicit backup exclusion rule for the token file.
On 401, refresh silently and retry once; send the officer to the login screen only when the refresh itself fails.

The diagnosis the team missed. 'Logged out randomly in the field' is almost always a transport failure being treated as an authentication failure: the app gets an IOException or a captive-portal HTML page, fails to parse it, and clears the session. Separating transport errors from 401 fixes most of the complaints on its own; the 30-day token was solving the wrong problem. Add an offline grace period so the officer can keep capturing visits with an expired access token and queue them for sync after re-authentication, which is what field work actually requires.

3. Diagnose why the list screen crashes only on real districts, and set out the fix in priority order, including what would stop the crash returning.

Read the worked answer

Reproduce before fixing. Seed a test district with 5,000 records rather than 1,500, and test a release build on a 2 GB device, because debug builds and flagship phones hide the profile. Read Logcat for OutOfMemoryError and GC storms, or take a heap dump.

Likely mechanism. An unpaged GET /visits means Retrofit plus the JSON converter materialises every record into a List<Visit> before the UI sees one, and the adapter then holds them again alongside image thumbnails. Memory scales with the district's whole history instead of with what is on screen. It 'works in the office' because the office dataset is small and the Wi-Fi fast enough that everything parses and renders before anyone scrolls. Different dataset, not different code, which is why 'it works on the test phone' is not evidence of anything.

Fix, in priority order.

Server: keyset pagination, GET /visits?after=<cursor>&limit=50, returning next_cursor. Keyset rather than ?page=3, because offset paging skips and repeats rows as new visits arrive, which field staff will report as a sync bug.
Client: Paging 3 with a RemoteMediator writing into Room, so the list is served from the database and memory is bounded by page size plus the visible window.
Images: load server-resized thumbnails, never full 4 MB photos, inside a list row.

Stopping the return. Add a debug-only fixture that seeds 5,000 rows on first run, and a LeakCanary or StrictMode check in CI, otherwise the same crash reappears with the next unpaged endpoint the team adds.