Reconcile entity/migration drift so the schema drift check passes strictly

[RUKAYAT-CODER]

Summary

Now that #1193 adds a baseline schema migration and migration:run works from scratch, the migration:generate:check drift check exposes a large amount of pre-existing drift between the entity definitions and the schema the incremental migrations produce.

What the drift is

TypeORM's migration:generate compares the database (after all migrations) against the entity metadata and finds differences in ~620 SQL statements, in three categories:

1. Index naming — migrations create indexes with explicit names (IDX_users_createdAt, IDX_course_currency, IDX_audit_logs_*, …) while entities declare @Index() with no name (TypeORM generates hash names). The drift check wants to drop the migration names and create hash names (or vice versa).
2. Column naming/type mismatches — e.g. course.search_vector (migration + raw SQL) vs course.searchVector (entity column), audit_logs http_method/retention_until/timestamp/version columns, user_preferences.locale/currency, course_bulk_operations.notes/reason/undone_by_id/version, and createdAt/updatedAt/deletedAt on grading tables.
3. FK constraint names + enum type names — migrations use explicit constraint names (FK_course_bulk_ops_initiator, FK_forum_votes_authorId_users) and enum type names (audit_logs_action_enum, tier_rewards_tier_enum, …) that don't match entity-generated names.

How it's currently handled

scripts/drift-check.sh compares the generated drift against scripts/known-drift.txt (a snapshot of the current drift). CI fails only when new drift is introduced, so model changes without a corresponding migration are still caught.

What needs to happen

Reconcile entities and migrations so migration:generate --check reports zero drift, then remove the snapshot tolerance from scripts/drift-check.sh:

• Align entity index declarations with the explicit names the migrations use (@Index('IDX_x', { synchronize: false }) or matching names), or update the migrations.
• Align column names/types between entities and migrations (decide the canonical name for search_vector, the audit-log columns, etc.).
• Match FK constraint names and enum type names (or mark them synchronize: false).
• Remove scripts/known-drift.txt and make the drift check strict again.

Verification

• pnpm run migration:run on a fresh Postgres
• pnpm run migration:generate --check should exit 0 with "No changes in database schema were found"
• pnpm run migration:revert

[Nimatstar]

Hi maintainer can i work on this task?, i have read though the description clearly and understand it. Kindly assign

[grantfox-oss]

🦊 GrantFox — @Nimatstar has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #1194)
2. Your PR will be reviewed by the rinafcode maintainers

Good luck! Track your progress on GrantFox.