# Migrations And Seeders Backend

## Purpose

Schema creation and reference-data seeding run through a small Umzug runner (it replaced
sequelize-cli during the TypeScript/ESM migration). This doc covers the developer mechanism:
the runner, file conventions, and how to add a migration or seeder. For VM/PM2 operational use
(when migrate/seed run on deploy, `db:reset` recovery), see `../../docs/deployment-vm.md`.

## Files

- Runner: `src/db/umzug.ts` — builds two `Umzug` instances (`migrator`, `seeder`) and a small CLI.
- Reset: `src/db/reset.ts` — drops every table in the `public` schema, then re-runs
  `migrator.up()` + `seeder.up()`.
- Schema snapshot: `src/db/initial-schema.ts` — DDL snapshot derived from the Sequelize models;
  the models remain the source of truth.
- Migrations: `src/db/migrations/*.ts` — currently `20260610000000-initial-schema.ts` (creates
  the full schema from the snapshot).
- Seeders: `src/db/seeders/*.ts` — `admin-user`, `user-roles`, `product-campuses`,
  `content-catalog` (+ payloads under `seeders/content-catalog-data/`).

## Mechanism

- `migrator` globs `migrations/*.{ts,js}`; history is tracked in the default `SequelizeMeta`
  table via `SequelizeStorage`.
- `seeder` globs `seeders/*.{ts,js}`; history is tracked in a separate `SequelizeData` table.
- Each file is ESM TypeScript with a default export `{ up, down }`, each taking
  `(queryInterface, Sequelize)`. The runner accepts either a `default` export or top-level
  `up`/`down`.
- Tracked names strip the `.ts`/`.js` extension, so history is stable whether the runner is
  executed via `tsx` (dev, `.ts`) or compiled (`prod`, `dist/.../*.js`).
- The `glob` accepts both `.ts` and `.js`, so already-applied entries are not re-run after a
  build.

## CLI And Scripts

`src/db/umzug.ts` exposes: `migrate:up`, `migrate:down`, `migrate:pending`, `seed:up`,
`seed:down`. npm scripts wrap them:

- Dev (via `tsx`): `db:migrate` (`migrate:up`), `db:migrate:undo` (`migrate:down`),
  `db:migrate:pending`, `db:seed` (`seed:up`), `db:seed:undo` (`seed:down`),
  `db:reset` (`tsx src/db/reset.ts`).
- Prod (compiled, no `tsx`): `db:migrate:prod` (`node dist/db/umzug.js migrate:up`),
  `db:seed:prod` (`node dist/db/umzug.js seed:up`).

## Authoring A New Migration / Seeder

1. Add `src/db/migrations/<timestamp>-<name>.ts` (or `seeders/...`) exporting
   `export default { up, down }` with typed `(queryInterface, Sequelize)` signatures.
2. Run `npm run db:migrate` (or `db:seed`) in dev; verify with `db:migrate:pending`.
3. Regenerate `database-schema.md` after any schema change (it is generated from the models).

## Tests

None yet.

## Related

- `database-schema.md` (the generated schema reference), `backend-architecture.md` (DAL layer),
  `../../docs/deployment-vm.md` (operational migrate/seed on the VM).
