[Bug][Migration][Postgres] testmo and q_dev plugins use MySQL-specific BIGINT UNSIGNED in raw SQL ALTER TABLE

[kornelwencek-esky]

Search before asking

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

What happened

Two migration scripts use BIGINT UNSIGNED and (in one case) MySQL backticks
in raw db.Exec() ALTER TABLE statements without dialect-aware branching.
Both fail on PostgreSQL with ERROR: syntax error (SQLSTATE 42601), blocking
any upgrade path that crosses these scripts on a Postgres backend.

Affected migrations (audited at v1.0.3-beta12):

1. backend/plugins/testmo/models/migrationscripts/20250629_add_scope_config_id_to_projects.go

   return db.Exec("ALTER TABLE `_tool_testmo_projects` ADD COLUMN `scope_config_id` bigint unsigned NOT NULL DEFAULT 0")

— backticks + bigint unsigned.

2. backend/plugins/q_dev/models/migrationscripts/20251123_add_scope_config_id_to_s3_slice.go

err := db.Exec(`
    ALTER TABLE _tool_q_dev_s3_slices
    ADD COLUMN scope_config_id BIGINT UNSIGNED DEFAULT 0
`)

— BIGINT UNSIGNED (Postgres has no unsigned modifier).
This is the same class of bug as #8564 (closed by #8565), reproduced in
two new migrations.

Sibling migration plugins/testmo/.../20250629_add_scope_config_id.go in the
same plugin uses the portable migrationhelper.AutoMigrateTables(...)
pattern correctly — confirming this is a coding mistake, not a missing
infrastructure piece.

What do you expect to happen

Migrations should either:

• Use migrationhelper.AutoMigrateTables(&entity{}) with GORM tags (portable across MySQL/Postgres), or
• Branch on db.Dialect() for dialect-specific raw SQL (as q_dev/20250320_modify_file_meta.go does correctly after refactor(migrations): enhance file meta migration to check column existence and nullability before modification #8565).

How to reproduce

1. Deploy DevLake v1.0.3-beta12 (or any beta from 2025-06+) with PostgreSQL 15+ as backend.
2. Run a fresh deployment, or upgrade from v1.0.1+.
3. Migration 20250629000001 (testmo) fails with SQLSTATE 42601.
4. Even if testmo is patched, the next blocker is 20251123000001 (q_dev) with the same root cause.

Reproduced on PostgreSQL 15 managed by the Zalando operator.

Anything else

Recommendation: add a CI step that runs the full migration chain against
PostgreSQL on every PR. Most contributors use the portable helpers
correctly; these two slipped through specifically because they were
hand-written raw SQL bypassing the helpers.

Related: #8617 (teambition ORDER BY on json column — unresolved,
different root cause but blocks Postgres on the same upgrade path),
#8564 (q_dev MODIFY — fixed in #8565), #8350 (Postgres support parity
question, closed without resolution).

Version

v1.0.3-beta12

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

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

[klesh]

Seems like it is covered by #8866