box-nestjs-monorepo/docs/MGNT_MYSQL_TO_MONGO_MIGRATION.md

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