Critical: Authentication failure caused by migration bug in 20250927-01-geoextracts.js — all users blocked
Hi ODK Support,
We have a production ODK Central instance (currently on v2025.1.3) where the service is running but all users are unable to log in. Field data collection is completely halted as a result.
Below is a summary of the issue and what we've tried so far.
The Problem
The database migration file 20250927-01-geoextracts.js contains invalid PostgreSQL syntax:
(theline::jsonb)[-1]
This fails with: "cannot subscript type jsonb because it is not an array"
Versions 1.0.5 through 1.0.9 all crash on this same migration. Version 2025.1.3 starts successfully but fails on authentication, which we believe is caused by a version/schema mismatch — the database schema was partially migrated and doesn't match what 2025.1.3 expects.
What We've Tried
Rolled back through versions 1.0.9, 1.0.8, 1.0.7, 1.0.6, and 1.0.5 — all crash on the same migration bug
Manually corrected the SQL functions directly in the database
Marked the affected migrations as complete in the migrations table
The migration file still executes the buggy SQL during startup validation, so the issue persists
Data Status
All user submissions and form data are intact and safe. The issue is purely with the migration/authentication layer.
What We Need (any one of these would resolve it)
A hotfix release (e.g. v1.0.10) with corrected migration SQL — replacing the jsonb[-1] subscript with a jsonb_array_length()-based approach
An intermediate version between v1.0.5 and v2025.1.3 that is compatible with our current (September 2025) database schema
A SQL patch script that allows us to bypass the migration validation so v2025.1.3 can authenticate properly
This is blocking all users across our organisation. We'd be grateful for any guidance or a fix as soon as possible.
Welcome to ODK Forum, if you have a moment, feel free to introduce yourself here
Can you please share the details of your setup. What is the version of your postgresql? are you using database configured using docker-compose or some cloud hosted postgresql like RDS?
Also please share what was the last working version of Central, were you on v1.0.5 before upgrading? Did you follow this doc: https://docs.getodk.org/central-upgrade/? Some version upgrade requires special steps.
It’s not invalid syntax per se (at least for postgres versions 14 and up, we support version 14).
It looks rather like a runtime error that would occur when theline is not a json-array, which would point to a problem elsewhere. Ideally we’d learn what your input was, but I suppose that’s going to be hard.
Anyway, to get you going again, one thing you could try is to prevent the migration 20250928-01-backfill-submission-geocache-doit.js from running, because that’s the migration actually exercising the function defined in the 20250927-01-geoextracts.js migration. In other words, you’d leave the function definition as-is, but you would just not exercise it. It’d still be exercised when you use the .geojson API endpoints though, but the effect would be an HTTP error on a request to one of those endpoints, which doesn’t affect the rest of the system — a rather better state of things than where you are at right now.
I fear with all the migration and version shuffling (you’re naming some version numbers I don’t recognize?) you have been doing you may have wedged the DB into an inconsistent state (maybe! maybe not!) so my advice would be to restore a backup from before you started your upgrade, double-check the upgrade docs and release notes as @Sadiq_Khoja says, and just skip the 20250928-01-backfill-submission-geocache-doit.js migration.
That wouldn’t solve the root cause, but at least it would get you going again, and then we could try to resolve the actual root cause with a less panicked disposition.