# Mgnt Storage Migration (MySQL → MongoDB `box_admin`)

## Scope
- Target: Move **box-mgnt-api** admin storage from MySQL to MongoDB database `box_admin`.
- Keep **box_stats** unchanged (MongoStats stays as-is).
- Read-only analysis; no code modifications.

## 1) MySQL Prisma Models Used by box-mgnt-api at Runtime

### Used models (direct Prisma access in mgnt services)
- `User` (`prisma/mysql/schema/user.prisma`)
- `Role` (`prisma/mysql/schema/role.prisma`)
- `UserRole` (`prisma/mysql/schema/user-role.prisma`)
- `Menu` (`prisma/mysql/schema/menu.prisma`)
- `RoleMenu` (`prisma/mysql/schema/role-menu.prisma`)
- `ApiPermission` (`prisma/mysql/schema/api-permission.prisma`)
- `RoleApiPermission` (`prisma/mysql/schema/role-api-permission.prisma`)
- `LoginLog` (`prisma/mysql/schema/login-log.prisma`)
- `OperationLog` (`prisma/mysql/schema/operation-log.prisma`)
- `QuotaLog` (`prisma/mysql/schema/quota-log.prisma`)
- `CacheSyncAction` (`prisma/mysql/schema/cache-sync-action.prisma`)

### Not used by box-mgnt-api runtime (present in schema only)
- `ImageConfig` (schema deleted; no runtime references; table removed before Mongo migration)
- `ProviderVideoSync` (model data flows from provider sync code and now lives under `prisma/mongo/schema/sys-providerVideoSync.prisma`)

## 2) Constraints per Model (PK, Unique, Relations)

### Summary table
| Model | Primary Key | Unique Constraints | Relations |
|---|---|---|---|
| User | `id` (auto-increment int) | `username` | `UserRole.userId -> User.id` |
| Role | `id` | `name` | `UserRole.roleId -> Role.id`, `RoleMenu.roleId -> Role.id`, `RoleApiPermission.roleId -> Role.id` |
| UserRole | `id` | `(userId, roleId)` | `userId -> User`, `roleId -> Role` |
| Menu | `id` | `frontendAuth` | self relation (`parentId -> Menu.id`), `RoleMenu.menuId -> Menu.id`, `ApiPermission.menuId -> Menu.id` |
| RoleMenu | `id` | `(roleId, menuId)` | `roleId -> Role`, `menuId -> Menu` |
| ApiPermission | `id` | `(menuId, path, method)` | `menuId -> Menu`, `RoleApiPermission.apiPermissionId -> ApiPermission.id` |
| RoleApiPermission | `id` | `(roleId, apiPermissionId)` | `roleId -> Role`, `apiPermissionId -> ApiPermission` |
| LoginLog | `id` | none | none |
| OperationLog | `id` | none | `menuId` is nullable but not enforced by FK in schema |
| QuotaLog | `id` | none | none |
| CacheSyncAction | `id` (BigInt) | none | none; indexed by `status,nextAttemptAt` and `entityType,entityId` |

### Additional field constraints (from schema)
- Enums used in mgnt:
  - `MenuType` (`DIRECTORY`, `MENU`, `SUBMENU`, `BUTTON`)
  - `LoginType` / `LoginStatus`
  - `OperationType`
- JSON fields:
  - `User.twoFARecoveryCodes`, `User.allowIps`
  - `Menu.meta`
  - `OperationLog.body`, `OperationLog.response`
  - `CacheSyncAction.payload`
- Special uniqueness / lookup usage in code:
  - `Menu.frontendAuth` queried by `findUnique` in `apps/box-mgnt-api/src/mgnt-backend/core/menu/menu.service.ts`

## 3) Proposed Mongo Collection Schemas + Indexes (`box_admin`)

### Collections (1-to-1 with current MySQL models)
- `sys_user`
  - Fields: `id` (int), `username`, `password`, `status`, `nick`, `photo`, `remark`, `twoFA`, `twoFALastUsedStep`, `twoFARecoveryCodes`, `allowIps`, `jwtToken`, `oAuthJwtToken`, `lastLoginTime`, `create_time`, `update_time`
  - Indexes: unique on `username`, index on `status` (optional for listing)
- `sys_role`
  - Fields: `id` (int), `name`, `status`, `remark`, `create_time`, `update_time`
  - Indexes: unique on `name`
- `sys_user_role`
  - Fields: `id` (int), `user_id`, `role_id`, `create_time`, `update_time`
  - Indexes: unique compound `(user_id, role_id)`; index on `user_id`, `role_id`
- `sys_menu`
  - Fields: `id` (int), `parent_id`, `title`, `status`, `type`, `order`, `frontend_auth`, `path`, `name`, `icon`, `redirect`, `component_key`, `meta`, `canView`, `canCreate`, `canUpdate`, `canDelete`, `create_time`, `update_time`
  - Indexes: unique on `frontend_auth`, index on `parent_id`, index on `type`
- `sys_role_menu`
  - Fields: `id` (int), `role_id`, `menu_id`, `canView`, `canCreate`, `canUpdate`, `canDelete`, `create_time`, `update_time`
  - Indexes: unique compound `(role_id, menu_id)`; index on `role_id`, `menu_id`
- `sys_api_permission`
  - Fields: `id` (int), `menu_id`, `path`, `method`, `create_time`, `update_time`
  - Indexes: unique compound `(menu_id, path, method)`; index on `menu_id`
- `sys_role_api_permission`
  - Fields: `id` (int), `role_id`, `api_permission_id`, `create_time`, `update_time`
  - Indexes: unique compound `(role_id, api_permission_id)`; index on `role_id`, `api_permission_id`
- `sys_login_log`
  - Fields: `id` (int), `type`, `status`, `username`, `ip_address`, `user_agent`, `create_time`, `update_time`
  - Indexes: index on `username`, `create_time`
- `sys_operation_log`
  - Fields: `id` (int), `username`, `menu_id`, `description`, `type`, `status`, `method`, `path`, `body`, `response`, `ip_address`, `call_method`, `create_time`, `update_time`
  - Indexes: index on `username`, `menu_id`, `create_time`
- `sys_quota_log`
  - Fields: `id` (int), `username`, `op_username`, `amount`, `is_inc`, `quota`, `remark`, `create_time`, `update_time`
  - Indexes: index on `username`, `create_time`
- `cache_sync_action`
  - Fields: `id` (long), `entityType`, `entityId`, `operation`, `status`, `attempts`, `nextAttemptAt`, `lastError`, `payload`, `createdAt`, `updatedAt`
  - Indexes: `(status, nextAttemptAt)`, `(entityType, entityId)`

### Mongo Prisma schema filenames & mappings
- `prisma/mongo/schema/sys-user.prisma` → maps to `sys_user` (mirrors `prisma/mysql/schema/user.prisma`)
- `prisma/mongo/schema/sys-role.prisma` → maps to `sys_role`
- `prisma/mongo/schema/sys-user-role.prisma` → maps to `sys_user_role`
- `prisma/mongo/schema/sys-menu.prisma` → maps to `sys_menu`
- `prisma/mongo/schema/sys-role-menu.prisma` → maps to `sys_role_menu`
- `prisma/mongo/schema/sys-api-permission.prisma` → maps to `sys_api_permission`
- `prisma/mongo/schema/sys-role-api-permission.prisma` → maps to `sys_role_api_permission`
- `prisma/mongo/schema/sys-login-log.prisma` → maps to `sys_login_log`
- `prisma/mongo/schema/sys-operation-log.prisma` → maps to `sys_operation_log`
- `prisma/mongo/schema/sys-cache-sync-action.prisma` → maps to `sys_cacheSyncAction` (preserves BigInt PK/indexes from the MySQL schema)
- `prisma/mongo/schema/sys-providerVideoSync.prisma` → maps to `sys_providerVideoSync` (moved out of `prisma/mysql/schema/main.prisma` so Mongo owns the provider sync store)
- `sys_quota_log` intentionally remains only in MySQL; no Mongo schema file is created so Mongo services keep relying on the existing `prisma/mysql/schema/quota-log.prisma`.

Every Mongo schema file uses `@@map("sys_<table>")` so the collection name stays aligned with the MySQL table while Prisma now loads the Mongo-friendly schema per file.

### ID strategy
- Preserve numeric IDs used by current code (e.g., `User.id`, `Role.id`, `Menu.id`).
- Use a monotonically increasing counter per collection to emulate auto-increment.

## 6) Verification Checklist

| Schema | Mgnt service(s) still touching it | What to verify before cut-over |
|---|---|---|
| `sys_user` | `apps/box-mgnt-api/src/mgnt-backend/core/user/user.service.ts` (User CRUD/role assignment) and `apps/box-mgnt-api/src/mgnt-backend/core/auth/auth.service.ts` (lookup during login). | Ensure `SysUser` stays feature-compatible while token, password/2FA, and role-linking flows move to the Mongo client. |
| `sys_role` | `apps/box-mgnt-api/src/mgnt-backend/core/role/role.service.ts`. | Confirm role creation/updating in Mongo respects the same `Menu` grants and status flags. |
| `sys_user_role` | `user.service.ts` (link/unlink roles) and `role.service.ts` (read assignments). | Validate transactional user+role updates work once `sys_user_role` is served from Mongo. |
| `sys_menu` | `apps/box-mgnt-api/src/mgnt-backend/core/menu/menu.service.ts`, `role.service.ts` (menu grant computations), and `operation-log.service.ts` (frontendAuth lookup). | Keep the menu tree, related meta, and `frontendAuth` resolution identical in Mongo. |
| `sys_role_menu` | `role.service.ts` (grant storage). | Ensure create/delete/replace flows still maintain `(roleId, menuId)` uniqueness. |
| `sys_api_permission` | `menu.service.ts` (API-permission CRUD) and `role.service.ts` (permission-based exports). | Preserve `(menuId, path, method)` uniqueness and any joins/resets. |
| `sys_role_api_permission` | `role.service.ts` (grant updates / queries). | Confirm transactions that delete/recreate role-permission links continue to run. |
| `sys_login_log` | `apps/box-mgnt-api/src/mgnt-backend/core/logging/login-log/login-log.service.ts` and the auth flow (via `LoginLogService`). | Logins will keep writing to `SysLoginLog` for auditing, so the new Mongo collection must mirror the MySQL schema. |
| `sys_operation_log` | `apps/box-mgnt-api/src/mgnt-backend/core/logging/operation-log/operation-log.service.ts` (and the decorator/`operation-log` controller that reads it). | Validate the operation decorator still resolves `menuId` via `MenuService` and writes request/response bodies as JSON. |
| `sys_cacheSyncAction` | `apps/box-mgnt-api/src/cache-sync/cache-sync.service.ts` (queue callback) plus every mgnt feature (ads/category/channel/tag/video) that calls it and the cache-sync controllers/checklist services. | The durable action queue must keep its BigInt PK/indexes and retry semantics while `CacheSyncService` continues scheduling old entity types. |
| `sys_providerVideoSync` | `apps/box-mgnt-api/src/mgnt-backend/feature/provider-video-sync/provider-video-sync.service.ts`. | Provider sync writes counts/counters to Mongo, so verify the new collection respects the original `lastSyncedAt`/`syncStatus` semantics. |

## 7) Follow-up: Mongo auto-increment / counters

- Naming has stabilized with the `sys_*` models; the next checkpoint is to implement a `counters` collection (e.g., `{ _id: "sys_user", seq: 123 }`) so each Mongo schema can request the next integer ID before inserting.
- Track this in the migration board and revisit once every `Sys*` collection has Prisma + service wiring in place; the `CounterService` can live alongside the Mongo Prisma client or inside a shared `@box/db` helper.

## 4) Relational Assumptions and Transaction Usage

### Joins / relational queries used in mgnt services
- User ↔ Role via `UserRole` (list, get, update)
  - `apps/box-mgnt-api/src/mgnt-backend/core/user/user.service.ts`
  - Uses `include` on `userRoles.role` and `where` clauses with `userRoles.some`
- Role ↔ Menu via `RoleMenu` (get permissions)
  - `apps/box-mgnt-api/src/mgnt-backend/core/role/role.service.ts`
- Menu ↔ ApiPermission via `ApiPermission` + RoleApiPermission
  - `apps/box-mgnt-api/src/mgnt-backend/core/menu/menu.service.ts`
- Menu self-relation (parent/children), used for tree building
  - `apps/box-mgnt-api/src/mgnt-backend/core/menu/menu.service.ts`

### Transaction usage that assumes relational guarantees
- Role create/update
  - `RoleService.create` and `RoleService.update` use `$transaction` to insert role + roleMenu or replace roleMenu
- User create/update/delete
  - `UserService.create`, `UserService.update`, `UserService.delete` use `$transaction` to change user and userRole together
- Menu permission update
  - `MenuService.updatePermission` deletes `roleApiPermission` + `apiPermission`, then recreates in a `$transaction`
- Menu delete
  - `MenuService.delete` deletes `roleMenu` + `menu` in a `$transaction`
- Pagination
  - `UserService.list` and `RoleService.list` use `$transaction` to fetch list + count

### How to adapt in Mongo
- Replace relational joins with one of:
  - **Explicit join collections** (retain `sys_user_role`, `sys_role_menu`, `sys_role_api_permission`) and perform multi-step queries in services.
  - **Embedded arrays** (e.g., store `roleIds` on `sys_user` and `menuIds` on `sys_role`) to reduce query count.
- Replace transactions with Mongo multi-document transactions where atomicity is required:
  - Role create/update (role + role_menu)
  - User create/update/delete (user + user_role)
  - Menu permission update (api_permission + role_api_permission)
- Replace `include` and `some` relational filters with:
  - Pre-join in application code (fetch roles by roleIds, map)
  - `$lookup` aggregation pipelines (only if performance is acceptable)

## 5) Migration Design Notes
- Keep data model parity with existing MySQL shape to minimize code change risk.
- Preserve unique constraints and compound indexes listed above.
- Maintain enum values as strings in Mongo to match Prisma usage.
- Plan for auto-increment simulation using a counters collection (e.g., `counters` with `{ _id: "sys_user", seq: 123 }`).

## Appendix: MySQL Schema Files (Reference)
- `prisma/mysql/schema/user.prisma`
- `prisma/mysql/schema/role.prisma`
- `prisma/mysql/schema/user-role.prisma`
- `prisma/mysql/schema/menu.prisma`
- `prisma/mysql/schema/role-menu.prisma`
- `prisma/mysql/schema/api-permission.prisma`
- `prisma/mysql/schema/role-api-permission.prisma`
- `prisma/mysql/schema/login-log.prisma`
- `prisma/mysql/schema/operation-log.prisma`
- `prisma/mysql/schema/quota-log.prisma`
- `prisma/mysql/schema/cache-sync-action.prisma`