# 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` (no references in `apps/box-mgnt-api/src`)
- `ProviderVideoSync` (defined in MySQL schema, but mgnt provider sync code writes to Mongo)

## 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)`

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

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