A WeChat Mini Program That Replaces Spreadsheets and Group-Chat Relays for Event Management
The unified-table-plus-JSON pattern is a practical alternative to single-table inheritance or polymorphic associations when you need to support multiple similar-but-distinct entity types without constant schema migrations. The automatic `ended`-state detection on read — rather than a cron job — is a low-overhead trick that works well for moderate-traffic apps where eventual consistency is acceptable.
Shubu is a production WeChat Mini Program built to replace the messy spreadsheets and group-chat relays that dominate community event organization in China. It packages five independent tools — event registration, time-slot service booking, activity relays with +1 support, entry/exit registration with approval, and real-time queue number calling — behind a shared user system and notification pipeline. The frontend uses native WeChat Mini Program APIs with Vant Weapp for UI components, while the backend runs on Express, Sequelize, and MySQL 8.4, with JWT-based dual-role authentication for admins and silent-login users.
The database design avoids table sprawl by storing all five tool types in a single activity table. Common fields like title, status, and creator sit in standard columns; tool-specific configuration lives in a JSON `extra` column. Each tool gets its own participation-record table linked by activity ID. Activity status transitions — draft, pending review, active, ended, paused — are enforced server-side, with the `ended` state detected on every detail request rather than via a scheduled job, eliminating a common source of drift.
The first installment of a seven-part series covers architecture decisions: why native development over uni-app or Taro, why Express over Koa or Nest.js, and how 40+ frontend API functions are built atop a unified request wrapper that handles token injection, 401 clearing, and error toasts automatically.
The JSON `extra` column approach is pragmatic for a small, fixed set of tool types but would become a query and validation burden if the number of tool variants grew beyond single digits. At that point, the implicit schema inside JSON would need its own versioning.
Detecting `ended` status on read rather than via a scheduled task removes a whole class of timer-drift and missed-job bugs, but it means the status is only accurate at the moment of the next request — acceptable for an activity platform where stale `active` badges carry little risk.
Choosing native Mini Program development over a cross-platform framework is a bet that WeChat's ecosystem lock-in is worth the performance and API-access gains. The trade-off becomes painful only if the project later needs to target Alipay or Douyin Mini Programs.