# 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.