box-nestjs-monorepo/docs/MONGODB_REMOVAL_FEASIBILITY.md

MongoDB Removal Feasibility

Feasibility Summary

• Removing MongoDB + MongoStats is feasible but high-effort.
• MongoDB is the primary store for ads, videos, categories, tags, channels, system params, and stats/events.
• Code paths assume Mongo-native arrays/JSON and ObjectId semantics.
• A MySQL-only approach requires a full schema replacement plus extensive query rewrites and migration tooling.

1) Inventory of Mongo/MongoStats Usage

Prisma clients

• Mongo: @prisma/mongo/client via MongoPrismaService (libs/db/src/prisma/mongo-prisma.service.ts)
• MongoStats: @prisma/mongo-stats/client via MongoStatsPrismaService (libs/db/src/prisma/mongo-stats-prisma.service.ts)
• App-scoped services:
  • apps/box-app-api/src/prisma/prisma-mongo.service.ts
  • apps/box-app-api/src/prisma/prisma-mongo-stats.service.ts
  • apps/box-stats-api/src/prisma/prisma-mongo.service.ts

Mongo (main) schema usage

• Ads / AdsModule (mgnt CRUD, app reads, recommendations, cache warmups)
  • apps/box-mgnt-api/src/mgnt-backend/feature/ads/ads.service.ts
  • apps/box-app-api/src/feature/ads/ad.service.ts
  • apps/box-app-api/src/feature/recommendation/recommendation.service.ts
  • libs/core/src/ad/*, libs/common/src/services/ad-pool.service.ts
• Category / Tag / Channel (mgnt CRUD, app reads, cache builders)
  • apps/box-mgnt-api/src/mgnt-backend/feature/category/category.service.ts
  • apps/box-mgnt-api/src/mgnt-backend/feature/tag/tag.service.ts
  • apps/box-mgnt-api/src/mgnt-backend/feature/channel/channel.service.ts
  • apps/box-app-api/src/feature/video/video.service.ts
  • libs/core/src/cache/*
• VideoMedia (mgnt CRUD, app reads, recommendation, stats aggregation, cache builders)
  • apps/box-mgnt-api/src/mgnt-backend/feature/video-media/video-media.service.ts
  • apps/box-app-api/src/feature/video/video.service.ts
  • apps/box-app-api/src/feature/recommendation/recommendation.service.ts
  • apps/box-stats-api/src/feature/stats-events/stats-aggregation.service.ts
  • libs/core/src/cache/video/*
• SystemParam (mgnt CRUD + app reads)
  • apps/box-mgnt-api/src/mgnt-backend/feature/system-params/system-params.service.ts
  • apps/box-app-api/src/feature/sys-params/sys-params.service.ts
• Sync services (write-heavy)
  • apps/box-mgnt-api/src/mgnt-backend/feature/sync-videomedia/sync-videomedia.service.ts
  • apps/box-mgnt-api/src/mgnt-backend/feature/provider-video-sync/provider-video-sync.service.ts
• Cache tooling/dev tools
  • apps/box-mgnt-api/src/cache-sync/cache-sync.service.ts
  • apps/box-mgnt-api/src/dev/services/video-cache-coverage.service.ts
  • apps/box-mgnt-api/src/dev/services/video-stats.service.ts

MongoStats schema usage

• User + login history
  • apps/box-stats-api/src/feature/user-login/user-login.service.ts
• Raw events + processed message dedupe
  • apps/box-stats-api/src/feature/stats-events/stats-events.consumer.ts
• Aggregated stats + Redis sync
  • apps/box-stats-api/src/feature/stats-events/stats-aggregation.service.ts

Mongo schemas defined but not used

• Home collection (prisma/mongo/schema/home.prisma) — no code references found
• AdsClickHistory (prisma/mongo-stats/schema/ads-click-history.prisma) — no code references found

2) Data Types Stored in Mongo and Usage Patterns

Core content (Mongo main)

• Ads / AdsModule
  • Fields: ObjectId ids, adType enum, start/end BigInt epoch, content/cover/url
  • Patterns: mgnt CRUD; app reads for lists and recommendation; cache warmups populate Redis
• Category
  • Fields: name, subtitle, seq, status, tagNames (String[]), tags (Json)
  • Patterns: mgnt CRUD; app reads categories; cache builders precompute lists
• Tag
  • Fields: name, categoryId (ObjectId), seq/status
  • Patterns: mgnt CRUD; app reads tags by category; cache builders build tag lists
• Channel
  • Fields: channelId (unique), name, URLs, tags/categories as Json/arrays
  • Patterns: mgnt CRUD; app auth uses channel lookup; cache builders
• VideoMedia
  • Large document with arrays (categoryIds, tagIds, tags, actors, secondTags, appids, japanNames), string metadata, BigInt size, DateTime timestamps, tagsFlat
  • Patterns: mgnt CRUD & sync; app reads by IDs (often from Redis); recommendation queries; stats aggregation needs tagIds; cache builders depend on indices

System parameters (Mongo main)

• SystemParam
  • Fields: id (int), side/type enums, name/value, BigInt timestamps
  • Patterns: mgnt CRUD; app reads for enums and ad modules

Stats/event data (MongoStats)

• User & UserLoginHistory
  • Writes on login events (upsert user, insert login record)
  • Reads for stats aggregation/reporting (inferred via indexes)
• Raw events: AdClickEvents, VideoClickEvents, AdImpressionEvents
  • Append-only writes from RabbitMQ consumer
  • Reads by aggregation jobs to compute scores and sync Redis
• ProcessedMessage
  • Deduplication on messageId, created by consumer, deleted on failure
• Aggregated stats: VideoGlobalStats, AdsGlobalStats
  • Upserted by aggregation jobs; read for scoring/sync to Redis
• Unused: AdsClickHistory (no code usage)

Mongo-specific features used

• ObjectId primary keys stored as strings in code (@db.ObjectId)
• Arrays (String[], Int[]) and Json fields
• aggregateRaw used in apps/box-app-api/src/feature/video/video.service.ts

3) Minimal MySQL Schema Proposal

Core content tables

• ads
  • id CHAR(24) PRIMARY KEY, ads_module_id CHAR(24), advertiser, title, ads_content, ads_cover_img, ads_url, img_source, start_dt BIGINT, expiry_dt BIGINT, seq INT, status INT, create_at BIGINT, update_at BIGINT
  • Index: ads_module_id, status, start_dt, expiry_dt
• ads_module
  • id CHAR(24) PRIMARY KEY, ad_type VARCHAR, ads_module VARCHAR, module_desc, seq INT
  • Unique: ad_type, ads_module
• category
  • id CHAR(24) PRIMARY KEY, name, subtitle, seq, status, create_at BIGINT, update_at BIGINT
• tag
  • id CHAR(24) PRIMARY KEY, name, category_id CHAR(24), seq, status, create_at BIGINT, update_at BIGINT
  • Index: category_id, status
• channel
  • id CHAR(24) PRIMARY KEY, channel_id VARCHAR UNIQUE, name, landing_url, video_cdn, cover_cdn, client_name, client_notice, remark, create_at BIGINT, update_at BIGINT
• system_param
  • id INT PRIMARY KEY, side, data_type, name, value, remark, create_at BIGINT, update_at BIGINT
  • Unique: (side, name)
• video_media
  • id CHAR(24) PRIMARY KEY, core fields + tags_flat VARCHAR, list_status INT, edited_at BIGINT, img_source, provider fields, timestamps
  • Index: (list_status, tags_flat), added_time, updated_at

Join tables to replace arrays

• video_media_category (video_media_id, category_id, PRIMARY KEY both)
  • Index on category_id
• video_media_tag (video_media_id, tag_id, PRIMARY KEY both)
  • Index on tag_id
• Optional denormalized tag names: video_media_tags as JSON if minimizing join complexity

Stats/event tables

• user_stats
  • id CHAR(24) PRIMARY KEY, uid VARCHAR UNIQUE, ip, os, u_channel_id, machine, create_at BIGINT, last_login_at BIGINT
• user_login_history
  • id CHAR(24) PRIMARY KEY, uid, ip, user_agent, app_version, os, u_channel_id, machine, create_at BIGINT, token_id
  • Index: (uid, create_at), (u_channel_id, create_at), (ip, create_at)
• ad_click_events, video_click_events, ad_impression_events
  • Fields mirror MongoStats schema; index on (ad_id|video_id, time), (uid, time), (ad_type, time)
• processed_messages
  • message_id VARCHAR UNIQUE, event_type, processed_at BIGINT, created_at BIGINT
• ads_global_stats, video_global_stats
  • per-id unique rows, indexes on computed_score, computed_recency, computed_ctr, last_seen_at

Notes

• Use CHAR(24) or BINARY(12) for former ObjectId values to preserve IDs used in APIs.
• Arrays used in filters should become join tables; JSON columns can be a stopgap but complicate query parity.

4) Migration Plan (Phases, Risks, Rollback)

Phases

1. Schema introduction
   • Add new MySQL tables (content + stats) while keeping Mongo/MongoStats active.
2. Backfill
   • One-time migration from Mongo/MongoStats into MySQL (ads, videoMedia, tags, events, stats).
3. Dual-write
   • Update write paths to write both Mongo and MySQL (mgnt CRUD, stats consumer, sync services).
4. Read switch
   • Shift read paths (app APIs, cache builders, stats aggregation) to MySQL.
5. Stabilization
   • Keep Mongo in read-only mode for verification.
6. Decommission
   • Remove Mongo dependencies after parity signoff.

Key risks

• Array semantics: Mongo arrays for categoryIds, tagIds, tagsFlat used in filters and Redis sync.
• Aggregation logic: aggregateRaw in apps/box-app-api/src/feature/video/video.service.ts is Mongo-specific.
• Event volume: stats events are append-heavy; MySQL indexing/partitioning needed for aggregation performance.
• ID consistency: APIs and caches assume ObjectId string IDs.
• Cache rebuilds depend on Mongo data shape and indexing; porting to MySQL must preserve ordering and filters.

Rollback strategy

• Keep Mongo/MongoStats live during migration.
• If errors: switch reads back to Mongo, stop MySQL writes, keep MySQL for postmortem.
• Preserve dual-write toggles to flip back quickly.

5) Test Checklist for Parity

API parity

• Ads API: /api/v1/ads/list, /api/v1/ads/click, /api/v1/ads/impression
• Video API: /api/v1/video/list, /api/v1/video/search-by-tag, /api/v1/video/category/:id/latest, /api/v1/video/recommended
• Homepage API: /api/v1/homepage (ads, categories, announcements, videos)
• Recommendation APIs: /api/v1/recommend/*, /api/v1/recommendation/*

Mgnt parity

• CRUD: ads, categories, tags, channels, system params, video media
• Sync endpoints: provider sync and video sync flow
• Cache rebuild endpoints: cache-sync and admin rebuild

Stats parity

• RabbitMQ ingestion → event writes
• Aggregation results: ads_global_stats, video_global_stats values and Redis scores
• Redis sync results: ads:global:score, video:global:score, video:tag:*:score

Performance

• Cache rebuild job duration and Redis write throughput
• Video listing latency (paged list by category/tag)
• Recommendation query latency (similar ads/videos)
• Stats aggregation runtime with realistic event volumes

Data integrity

• ID mapping unchanged (ObjectId format preserved)
• Tags/categories relations consistent for video media
• No loss of BigInt epoch precision in timestamps