[Bug][DB-Migration] DB-Migration Fails on UI Boot with v1.0.3-beta7

[jcg-juan]

Search before asking

• [x] I had searched in the issues and found no similar issues.

What happened

We attempted to upgrade from v1.0.2-beta8 to v1.0.3-beta7, but the UI fails to boot due to a database migration issue. We're unable to proceed with the upgrade and are currently stuck on the previous version.

ERROR: could not identify an ordering operator for type json (SQLSTATE 42883)
[4.975ms] [rows:0] SELECT * FROM "_tool_teambition_scope_configs" ORDER BY "_tool_teambition_scope_configs"."entities" LIMIT 1

What do you expect to happen

The UI should boot successfully, and database migrations should not use ORDER BY on unsortable types like json.

How to reproduce

1. Run devlake v1.0.2-beta8 with Postgresql as DB
2. Upgrade devlake version to v1.0.3-beta7
3. DB-migration fails with error below

ERROR: could not identify an ordering operator for type json (SQLSTATE 42883)
[4.975ms] [rows:0] SELECT * FROM "_tool_teambition_scope_configs" ORDER BY "_tool_teambition_scope_configs"."entities" LIMIT 1

Anything else

From deugging I found this error is due to the usage of ORDER BY on a json column (entities), which PostgreSQL does not support natively. JSON fields cannot be ordered unless they are cast or a specific subfield is extracted.

Version

v1.0.3-beta7

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [x] I agree to follow this project's Code of Conduct

[klesh]

Could not reproduce the problem:

The migration went through on my local machine, and the table was created successfully. Can you identify the offending migration script?

[petkostas]

This looks related to .First method called in the AutoMigrateTables and how PG handles ordering. https://github.com/metabase/metabase/issues/12153 Not sure is this has changed between PG versions, what PG version is that @jcg-juan ?

[klesh]

This looks related to .First method called in the AutoMigrateTables and how PG handles ordering. metabase/metabase#12153 Not sure is this has changed between PG versions, what PG version is that @jcg-juan ?

I thought the same. However, I didn't see anywhere in the migration scripts calling the method.