Upgrade fails — Task250604UpdateFolderInodes inserts into identifier.base_type before Task260407AddBaseTypeColumnToIdentifier adds the column (regression from #35164)

Problem Statement

Upgrading from an older release (reported: 24.04.24_lts_v23_4fe06ee) to evergreen fails during startup schema upgrades, leaving the instance unable to boot. Task250604UpdateFolderInodes runs the FixTask00090RecreateMissingFoldersInParentPath fix, which creates folder identifiers via IdentifierFactoryImpl.saveIdentifier(). That INSERT now writes the base_type column, but the column is only added later in the upgrade chain by Task260407AddBaseTypeColumnToIdentifier. The result is a hard startup failure.

This is a task-ordering regression introduced by PR #35164 (feat(identifier): denormalize base_type onto identifier table), which changed saveIdentifier() to unconditionally write base_type while the column-adding upgrade task runs near the end of the task list.

Impact: Any customer whose database triggers folderIdsNeedFixing() during the upgrade (older DBs with folder inconsistencies, e.g. 24.04.x) cannot start dotCMS at all — the upgrade aborts mid-flight.

Stack trace

ERROR tasks.FixTask00090RecreateMissingFoldersInParentPath - There was a problem during RecreateMissingFoldersInParentPath
com.dotmarketing.exception.DotDataException: ERROR: column "base_type" of relation "identifier" does not exist
  Position: 133
  "SQL": ["INSERT INTO identifier (parent_path,asset_name,host_inode,asset_type,syspublish_date,sysexpire_date,owner,create_date,asset_subtype,base_type,id) values (?,?,?,?,?,?,?,?,?,?,?)"]
    at com.dotmarketing.business.IdentifierFactoryImpl.saveIdentifier(IdentifierFactoryImpl.java:566)
    at com.dotmarketing.business.IdentifierAPIImpl.save(IdentifierAPIImpl.java:125)
    at com.dotmarketing.fixtask.tasks.FixTask00090RecreateMissingFoldersInParentPath.createIdentifier(FixTask00090RecreateMissingFoldersInParentPath.java:188)
    at com.dotmarketing.fixtask.tasks.FixTask00090RecreateMissingFoldersInParentPath.createFolder(FixTask00090RecreateMissingFoldersInParentPath.java:143)
    at com.dotmarketing.startup.runonce.Task250604UpdateFolderInodes.executeUpgrade(Task250604UpdateFolderInodes.java:36)
    at com.dotmarketing.startup.StartupTasksExecutor.executeSchemaUpgrades(StartupTasksExecutor.java:261)
Caused by: org.postgresql.util.PSQLException: ERROR: column "base_type" of relation "identifier" does not exist
  Position: 133

Root cause

Upgrade tasks execute strictly in registration (list) order in TaskLocatorUtil.getStartupRunOnceTaskClasses(), and the executor guard requires ascending task ids (taskId > Config.DB_VERSION, then Config.DB_VERSION = taskId; StartupTasksExecutor.java:248,274).

Task250604 only fires when folderIdsNeedFixing() is true. After #35164, the INSERT in IdentifierFactoryImpl.saveIdentifier() (IdentifierFactoryImpl.java:542 and :546) always includes base_type. Since Task260407 has not yet added the column, the insert fails.

Broader impact (not limited to Task250604)

The runtime code in IdentifierFactoryImpl.saveIdentifier() now hard-depends on the base_type column existing. Therefore any upgrade or fix task numbered below 260407 that creates an Identifier — directly via IdentifierAPI.save() / saveIdentifier(), or indirectly by creating folders/hosts/contentlets through the standard APIs during the upgrade — will hit the same column "base_type" ... does not exist error on a database that has not yet reached Task260407. The entire upgrade window before Task260407 is exposed, not just this one task. The fix should therefore address the dependency itself, not just patch Task250604.

Steps to Reproduce

1. Start from a DB old enough that folderIdsNeedFixing() returns true and identifier.base_type does not yet exist (e.g. 24.04.x), and that has at least one missing folder in a parent path (so FixTask00090 actually does work).
2. Deploy an evergreen build containing PR #35164.
3. Boot dotCMS — startup schema upgrades fail at Task250604UpdateFolderInodes with the stack trace above, and the instance does not start.

Expected: The base_type column exists before any upgrade/fix task that writes identifiers runs, so the upgrade completes and dotCMS boots.

Actual: Task250604UpdateFolderInodes aborts with ERROR: column "base_type" of relation "identifier" does not exist; startup fails.

Acceptance Criteria

• Upgrading from 24.04.x (with a DB where folderIdsNeedFixing() is true and missing parent-path folders exist) to evergreen completes successfully and dotCMS boots.
• The base_type column is guaranteed to exist on the identifier table before any upgrade/fix task that writes identifiers via IdentifierFactoryImpl.saveIdentifier() runs.
• FixTask00090RecreateMissingFoldersInParentPath successfully recreates missing folders during the upgrade without the base_type error.
• The fix addresses the general dependency (any identifier-writing task below Task260407), not only Task250604UpdateFolderInodes.
• Task260407AddBaseTypeColumnToIdentifier remains idempotent (IF NOT EXISTS) and still fires PopulateIdentifierBaseTypeJob for the async backfill; re-running the upgrade is safe.
• Duplicate registration of Task250604UpdateFolderInodes in TaskLocatorUtil.java (lines 598 and 599) is removed.
• An integration test reproduces the ordering failure (identifier insert before the column exists) and verifies it no longer occurs.

Suggested fix (for reference)

Reordering the task list is not safe — moving Task260407 earlier would make Task250604 be skipped by the taskId > Config.DB_VERSION guard. Instead, guarantee the column exists before the dependent code runs:

• Add the idempotent DDL ALTER TABLE identifier ADD COLUMN IF NOT EXISTS base_type INT4 at the start of Task250604UpdateFolderInodes.executeUpgrade() (and/or any other identifier-writing task) before invoking FixTask00090; Task260407 stays in place for the index + async backfill. OR
• More robustly, add the base_type column via a bootstrap/early step guaranteed to run before any identifier-writing upgrade task, since saveIdentifier() now unconditionally depends on it.

Operational workaround (no rebuild)

Pre-create the column and index before booting, then start dotCMS. Task260407 is idempotent and still completes its backfill:

ALTER TABLE identifier ADD COLUMN IF NOT EXISTS base_type INT4;
CREATE INDEX IF NOT EXISTS idx_identifier_base_type ON identifier (base_type);

dotCMS Version

• Source: 24.04.24_lts_v23_4fe06ee
• Target: evergreen (build containing PR #35164)
• Database: PostgreSQL

Severity

High - startup/upgrade fails for affected databases

Links

• Originating PR (regression source): #35164

Start with TaskLocatorUtil.java, StartupTasksExecutor.java, Task250604UpdateFolderInodes.java, Task260407AddBaseTypeColumnToIdentifier.java, and IdentifierFactoryImpl.saveIdentifier(). Reproduce the upgrade path from the issue, including FixTask00090RecreateMissingFoldersInParentPath, then add the requested integration coverage. Done means the upgrade completes with missing folders, the identifier column is available before dependent writes, Task260407 remains idempotent, and the duplicate task registration is removed.