fix(migrations): database migration crashes under PostgreSQL due to SQLite syntax assumptions

[darkleono]

Description:
Several database migration scripts inside the backend/alembic/versions/ directory fail when run against a PostgreSQL database. While these scripts work fine on SQLite, PostgreSQL's strict type verification throws errors that abort the migration transaction.

1. v2940_assemblies_resource_type.py (JSON extraction):
   • Problem: Runs raw SQL update using SQLite's json_extract(metadata, '$.resource_type'). PostgreSQL throws:
     psycopg2.errors.UndefinedFunction: function json_extract(jsonb, unknown) does not exist
   • Fix: Check database connection dialect and use ->> operator on PostgreSQL.
2. v3011_subcontractors.py (Index name length limit):
   • Problem: Creates an index named ix_oe_subcontractors_payment_application_line_payment_application_id which is 68 characters long, exceeding PostgreSQL's limit of 63 characters. Throws IdentifierError.
   • Fix: Truncate the index name to ix_oe_subcontractors_pal_pa_id.
3. v3034_match_pipeline_stages.py (Boolean insert mismatch):
   • Problem: Inserts integer literal 1 into a boolean column (is_system):
     VALUES (... 1, 1, NULL, NULL, :meta)
     PostgreSQL throws psycopg2.errors.DatatypeMismatch: column "is_system" is of type boolean but expression is of type integer.
   • Fix: Use standard SQL TRUE literal:
     VALUES (... 1, TRUE, NULL, NULL, :meta)
4. v3114_propdev_house_type_catalogue.py (Boolean comparison mismatch):
   • Problem: Compares boolean column is_preset using an integer comparison:
     WHERE is_preset = 1
     PostgreSQL throws psycopg2.errors.UndefinedFunction: operator does not exist: boolean = integer.
   • Fix: Change query filter to WHERE is_preset = TRUE.

Proposed Code Diffs:

diff --git a/backend/alembic/versions/v2940_assemblies_resource_type.py b/backend/alembic/versions/v2940_assemblies_resource_type.py
index f6080160..2e3ad975 100644
--- a/backend/alembic/versions/v2940_assemblies_resource_type.py
+++ b/backend/alembic/versions/v2940_assemblies_resource_type.py
@@ -61,13 +61,19 @@ def upgrade() -> None:
     # Back-fill: prefer the metadata-stored hint, fall back to a small
     # heuristic on description so legacy assemblies stop showing every
     # row as "material" once the UI starts filtering by type.
+    dialect_name = bind.dialect.name
+    json_path = (
+        "metadata ->> 'resource_type'"
+        if dialect_name == "postgresql"
+        else "json_extract(metadata, '$.resource_type')"
+    )
     bind.execute(
         sa.text(
-            """
+            f"""
             UPDATE oe_assemblies_component
-               SET resource_type = json_extract(metadata, '$.resource_type')
+               SET resource_type = {json_path}
              WHERE resource_type IS NULL
-               AND json_extract(metadata, '$.resource_type') IS NOT NULL
+               AND {json_path} IS NOT NULL
             """
         )
     )
diff --git a/backend/alembic/versions/v3011_subcontractors.py b/backend/alembic/versions/v3011_subcontractors.py
index 643f2795..6e33ae34 100644
--- a/backend/alembic/versions/v3011_subcontractors.py
+++ b/backend/alembic/versions/v3011_subcontractors.py
@@ -123,7 +123,7 @@ _INDEXES: tuple[tuple[str, str, tuple[str, ...], bool], ...] = (
         False,
     ),
     (
-        "ix_oe_subcontractors_payment_application_line_payment_application_id",
+        "ix_oe_subcontractors_pal_pa_id",
         _TABLE_PA_LINE,
         ("payment_application_id",),
         False,
diff --git a/backend/alembic/versions/v3034_match_pipeline_stages.py b/backend/alembic/versions/v3034_match_pipeline_stages.py
index d4ef8a59..de41f5a9 100644
--- a/backend/alembic/versions/v3034_match_pipeline_stages.py
+++ b/backend/alembic/versions/v3034_match_pipeline_stages.py
@@ -345,7 +345,7 @@ def upgrade() -> None:
                 "system_prompt, user_template, allowed_providers, "
                 "version, is_system, created_by, forked_from_id, metadata) "
                 "VALUES (:id, :ts, :ts, :k, :n, :d, :sp, :ut, NULL, "
-                "1, 1, NULL, NULL, :meta)"
+                "1, TRUE, NULL, NULL, :meta)"
             ).bindparams(
                 id=str(uuid.uuid4()),
                 ts=now,
diff --git a/backend/alembic/versions/v3114_propdev_house_type_catalogue.py b/backend/alembic/versions/v3114_propdev_house_type_catalogue.py
index 657c94b9..63700f1e 100644
--- a/backend/alembic/versions/v3114_propdev_house_type_catalogue.py
+++ b/backend/alembic/versions/v3114_propdev_house_type_catalogue.py
@@ -191,7 +191,7 @@ def upgrade() -> None:
     existing_preset_count = (
         bind.execute(
             sa.text(
-                "SELECT COUNT(*) FROM oe_property_dev_house_type_catalogue WHERE is_preset = 1 AND project_id IS NULL"
+                "SELECT COUNT(*) FROM oe_property_dev_house_type_catalogue WHERE is_preset = TRUE AND project_id IS NULL"
             )
         ).scalar()
         or 0

[datadrivenconstruction]

Fixed in 9.8.0. The migrations no longer assume SQLite-only syntax, so they run cleanly on PostgreSQL. Thanks for the detailed report.