77ae5be445
All checks were successful
Build & Deploy Costco Grocery List / build (push) Successful in 1m10s
Build & Deploy Costco Grocery List / verify-images (push) Successful in 3s
Build & Deploy Costco Grocery List / deploy (push) Successful in 11s
Build & Deploy Costco Grocery List / notify (push) Successful in 1s
68 lines
2.3 KiB
Markdown
68 lines
2.3 KiB
Markdown
# 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.
|