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 (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