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