# Upharm MongoDB to MySQL Migration

## Strategy

The legacy API remains available as the behavioral and data source while domains
are moved into this repository. The replacement API uses Prisma/MySQL at runtime;
legacy MongoDB is read only by dedicated import tooling during the later cutover.

## Database conventions

- MySQL/MariaDB is the production database.
- Primary and foreign keys use `VARCHAR(36)`. Existing 24-character MongoDB
  ObjectIds are imported unchanged; new rows use UUIDs.
- MongoDB arrays of references become join tables.
- Like and dislike collections become reaction tables with a unique
  resource/user constraint.
- Multi-document operations become Prisma transactions.
- Money uses fixed-precision `DECIMAL`, never floating point.
- Soft deletion remains available where the legacy API depends on it.

## Phases

1. **Foundation — code complete; database setup deferred**
   - Validate and generate the Prisma schema.
   - Configure MySQL locally and in cPanel after the code migration is finished.
   - Create and review the initial migration.
2. **Identity — code complete; database integration pending**
   - Port users, sessions, verification, password reset and authorization.
   - Preserve the legacy cookies and response DTOs during transition.
   - Authentication, MFA, OAuth account links, preferences and session
     revocation now use Prisma.
3. **Learning — code complete; database integration pending**
   - Port categories, courses, chapters, topics, subtopics, files, notes and
     enrolment progress.
   - Category/course CRUD, content hierarchy, enrolment progress, notes,
     course views and likes now use Prisma.
   - Course imports, dashboard/latest/popular/recent/mine collections and
     database-backed topic-file attachments are included.
4. **Assessment — code complete; database integration pending**
   - Port quizzes, questions, registrations, attempts and responses.
5. **Publishing and community — code complete; database integration pending**
   - Port blogs, communities, posts, replies, comments, views and reactions.
6. **Real-time and commercial — code complete; database integration pending**
   - Port chat/messages, reports, subscriptions and payment records.
7. **Data cutover**
   - Run a dry import, compare record counts and relationship integrity, then
     perform an incremental/final import during a maintenance window.
8. **Retirement — runtime complete**
   - Mongoose has been removed from the replacement API runtime. Dedicated,
     one-shot import tooling will read legacy MongoDB during data cutover.

## cPanel deployment

The cPanel Node.js application must define `DATABASE_URL` and JWT secrets before
building. Deploy with:

```bash
npm ci
npm run db:migrate:deploy
npm run build
```

Use the cPanel-prefixed database and user names in `DATABASE_URL`. Keep the
connection limit conservative on shared hosting; the default is five.

## Rollback

Until final cutover, rollback means routing traffic back to the legacy API.
Production schema changes must be additive during the compatibility period.
Every import must write reconciliation results before traffic is switched.