core: V1 migration fails against older previous-channel database schemas #43139

Summary

The V1→V2 migration fails with SQLiteError: near ",": syntax error when opencode-next.db was created by an older previous-channel build whose project or session table lacks nullable columns expected by the current importer.

The importer reads source rows with SELECT * into current TypeScript shapes. Missing properties become undefined; raw SQL interpolation emits an empty expression such as VALUES (..., , ...), and the per-session transaction rolls back. Migration remains incomplete and retries on the next server process.

This broadens #41341: fork_boundary is one affected field, but the compatibility defect applies to every assumed source column added after the source database was created.

Environment

• opencode version: 0.0.0-beta-17519
• OS: macOS arm64; exact Darwin version unavailable from diagnostics
• Terminal: Not applicable; observed through packaged Desktop
• Shell: Unavailable; diagnostics only report that a login shell environment loaded
• Install/channel: Beta
• Previous-channel database version: service last registered as 0.0.0-next-16416
• Active plugins: Unavailable from exported diagnostics

Reproduction

1. Create or retain an opencode-next.db from an older V2 build such as next-16416.
2. Ensure it contains at least one project and session, and that its schema predates one or more nullable columns expected by the current importer.
3. Start a current V2 server so V1Migration.layer runs.
4. Observe that normal schema migrations complete.
5. Observe the background data migration fail with:

   V1 migration failed
   SQLiteError: near ",": syntax error
   

6. Start another server process and observe the incomplete migration retry.

Expected Behavior

The migration should tolerate all supported historical previous-channel schemas:

• Existing columns should be copied.
• Absent nullable columns should be projected as SQL NULL.
• Absent required columns should produce a direct compatibility error naming the table and column.
• Source values must always be bound safely; undefined must never become an empty SQL expression.
• Successfully committed sessions should remain idempotent across retries.

Actual Behavior

The importer declares current row shapes containing fields such as:

project:
  icon_url_override
  icon_color
  commands

session:
  fork_boundary
  time_suspended

It then reads the old database without adapting its schema:

source.query<NextProject, []>("SELECT * FROM project").all()
source.query<NextSession, []>("SELECT * FROM session ORDER BY id DESC").all()

For a column absent from the source table, property access returns undefined. The destination SQL interpolates those values directly:

sql`
  INSERT INTO session_v2 (..., fork_session_id, fork_boundary, slug, ...)
  VALUES (..., ${session.fork_session_id}, ${session.fork_boundary}, ${session.slug}, ...)
`

The generated statement can contain:

VALUES (..., value, , value, ...)

SQLite rejects it with:

SQLiteError: near ",": syntax error

The transaction rolls back, including its migration cursor update, so the same input fails again on a later server process.

Additional Context

Observed timeline

All timestamps UTC:

23:16:52.519  new server process starts
23:16:52.750  normal database migrations begin
23:16:52.771  normal database migrations complete
23:16:54.798  V1 migration fails: SQLiteError near ","

The diagnostic window contains one server run and one V1 migration attempt. Client relaunches reused the same server. This rules out concurrent or duplicate migration execution as the cause of this occurrence.

The event cleanup is also not the malformed statement. It uses fixed SQL:

DELETE FROM event
WHERE rowid IN (SELECT rowid FROM event LIMIT ?)

The near-comma failure occurs later, while importing rows from the previous-channel database.

Static call stack and data flow

CLI / Desktop client
  Service.ensure(currentVersion)
    starts `opencode-cli serve --service`

ServerProcess.start
  createRoutes
    Database layer
      DatabaseMigration.apply
        normal schema migrations succeed
    V1Migration.layer
      forks V1Migration.run
        read durable `migration.v1-v2` state
        count/read `<global-data>/opencode-next.db`
        importNextDatabase
          SELECT * FROM project
          SELECT * FROM session
          source row lacks historical column
          property read returns undefined
          tx.run(INSERT INTO project/session_v2)
          SQL template emits empty value between commas
          SQLite parser throws
          transaction rolls back
      catches cause
      runtime migration status = error

Inputs and outputs

Input:
  current destination database
  older opencode-next.db
  source table schema
  source project/session rows

Transformation:
  SELECT * into current TypeScript interface
  property access for columns not guaranteed by source schema
  SQL-template interpolation

Output:
  malformed INSERT statement
  SQLite syntax error
  rolled-back session import
  incomplete migration state

TUI and Desktop behavior

Neither UI initiates this migration directly. The server auto-starts it through V1Migration.layer. The TUI migration overlay only polls status. Clients may replace a registered service when versions differ, but the observed failure occurs inside one server process and one migration run.

Related issue

• V1→V2 session migration fails when previous channel DB lacks fork_boundary #41341 identifies the same compatibility pattern specifically for a missing session.fork_boundary. The implementation should handle the complete historical schema surface rather than special-case that column.

Suggested fix

1. Inspect each source table with PRAGMA table_info.
2. Build an explicit source projection for the supported current shape.
3. Select NULL AS <column> for absent nullable fields.
4. Fail early with a named compatibility error for absent required fields.
5. Use bound destination inserts that preserve null and reject undefined before SQL generation.
6. Add fixtures for multiple historical project and session schemas, not only the fork_boundary variant.