costco-grocery-list/docs/DB_MIGRATION_WORKFLOW.md

DB Migration Workflow (External Postgres)

This project uses an external on-prem Postgres database. Migration files are canonical in:

• packages/db/migrations

Preconditions

• DATABASE_URL is set and points to the on-prem Postgres instance.
• psql is installed and available in PATH.
• You are in repo root.

Commands

• Apply pending migrations:
  • npm run db:migrate
• Show migration status:
  • npm run db:migrate:status
• Fail if pending migrations exist:
  • npm run db:migrate:verify
• Create a new migration file:
  • npm run db:migrate:new -- <migration-name>
• Track stale legacy SQL in backend/migrations:
  • npm run db:migrate:stale
• Fail when stale legacy SQL exists:
  • npm run db:migrate:stale:check

Active migration set

Migration files are applied in lexicographic filename order from packages/db/migrations.

backend/migrations is legacy reference-only and not part of canonical execution. packages/db/migrations/stale-files.json is the source of truth for canonical files that are intentionally stale/ignored.

Current baseline files:

• add_display_name_column.sql
• add_image_columns.sql
• add_modified_on_column.sql
• add_notes_column.sql
• create_item_classification_table.sql
• create_sessions_table.sql
• multi_household_architecture.sql

Tracking table

Applied migrations are recorded in:

• schema_migrations(filename text unique, applied_at timestamptz)

Expected operator flow

1. Check status:
   • npm run db:migrate:status
2. If a new implementation needs schema changes, create a new file:
   • npm run db:migrate:new -- <migration-name>
3. Apply pending:
   • npm run db:migrate
4. Verify clean state:
   • npm run db:migrate:verify

Troubleshooting

• DATABASE_URL is required:
  • Export/set DATABASE_URL in your environment.
• psql executable was not found in PATH:
  • Install PostgreSQL client tools and retry.
• SQL failure:
  • Fix migration SQL and rerun; only successful files are recorded in schema_migrations.
• Skip known stale SQL files for a specific environment:
  • Set DB_MIGRATE_SKIP_FILES to a comma-separated filename list.
  • Example: DB_MIGRATE_SKIP_FILES=add_modified_on_column.sql,add_image_columns.sql
• Temporarily include files listed in stale-files.json:
  • Set DB_MIGRATE_INCLUDE_STALE=true before running migration commands.