Self-hosted deployments break on update due to missing database migrations

[pcrausaz]

Description

When updating a self-hosted Cap instance (e.g. pulling a newer Docker image and redeploying the stack), the application crashes with a database error because new columns are not automatically added to the existing database.

Error:

Error: Unknown column 'videos.effectiveCreatedAt' in 'field list'
code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlState: '42S22'

The app code references videos.effectiveCreatedAt, but this column doesn't exist in the database because no migration was run.

Reproduction

1. Deploy Cap self-hosted using docker compose up -d

Additional Context

• Cap version: (Latest) Image ID: e7b63b58a91350337037ae40d3eae7ba17193f693622aece534a2349c0ee708f (2026-01-26 14:44:11)
• Operating system, version: Portainer 2.33.7 LTS on Synology NAS

[linear]

CAP-644

[pcrausaz]

The fix:

ALTER TABLE videos ADD COLUMN effectiveCreatedAt DATETIME DEFAULT NULL;
UPDATE videos SET effectiveCreatedAt = createdAt WHERE effectiveCreatedAt IS NULL;

[tembo]

The effectiveCreatedAt column was introduced in PR #1361 (merged November 2025) and the migration SQL that adds it is in packages/database/migrations/0007_public_toxin.sql:

ALTER TABLE `videos` ADD `effectiveCreatedAt` datetime GENERATED ALWAYS AS (COALESCE(
  STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%s.%fZ'),
  STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%sZ'),
  `createdAt`
)) STORED;

The migrations are copied into the Docker image at build time (see apps/web/Dockerfile:32) and there's a migrateDb() function in packages/database/migrate.ts that uses Drizzle's migrator. Looking at the logs from other self-hosting issues (e.g., #1352, #1407), migrations appear to run on startup with messages like "Running DB migrations..." and "Migrations run successfully!".

Immediate workaround: You can manually apply the migration by running the SQL from 0007_public_toxin.sql against your database. The critical statement for your error is the ALTER TABLE videos ADD effectiveCreatedAt... line above.

Root cause investigation: The migration infrastructure exists but something is preventing it from running or completing on your instance. A few things to check:

• Are you seeing the "Running DB migrations..." log message on startup?
• Check the drizzle.__drizzle_migrations table in your database to see which migrations have been applied
• The migration file also drops several indexes before adding the column, which could fail if those indexes don't exist in older schemas

This is similar to #721 where another user hit "Unknown column 'videos.id'" after a fresh install, also pointing to migration timing issues in self-hosted Docker deployments.

[pcrausaz]

Thanks for the follow up, I am up and running. I suspect you are right about some timing issue in the docker deployment (using the fix indicated in my previous comment ). If you need a specific log, let me know which, there was nothing in the 3 containers logs pertaining to migration or drizzle

[pcrausaz]

Would love to see #1626 go through. I hit a similar issue today

This issue is still occurring with the latest ghcr.io/capsoftware/cap-web:latest image as of March 14, 2026.
After pulling the latest image and redeploying, the app now fails with a different missing column:

Error: Unknown column 'phase' in 'field list'
code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlState: '42S22'
sql: "insert into `video_uploads` (`video_id`, `uploaded`, `total`, `started_at`, `updated_at`, `mode`, `phase`, `processing_progress`, `processing_message`, `processing_error`, `raw_file_key`) values ...

The missing columns this time are: phase, processing_progress, processing_message, processing_error, raw_file_key on the video_uploads table.
Interestingly, the built-in migration does run on startup and reports success:

💿 Running DB migrations...
💿 Migrations run successfully!

Yet the columns are still missing after migration completes, suggesting the migration files bundled in the image are out of sync with the actual schema expected by the app code.

Workaround: manually applying the ALTER TABLE directly against MySQL resolves it, but this is required after every image update.

[richiemcilroy]

Database migrations now run automatically on boot for self-hosted Docker deployments. This should be in the latest release, please reopen if updates still break for you.