Separate Operational Database for Each Merchant Using AWS RDS

Objective

Implement a separate MySQL operational database for each merchant using Amazon RDS, while maintaining a central database for merchant accounts, subscriptions, authentication, and platform management.

Reason for This Request

KMRS is expanding beyond online ordering to support multiple order sources and restaurant applications, including:

• POS

• Flutter Waiter App

• Kitchen Display System (KDS)

• Self-service Kiosk

• QR Menu ordering

• Single Site

• Single App

• Main KMRS Customer App

  As more restaurants use these applications simultaneously, each merchant will generate increasing volumes of orders, order items, payment records, inventory updates, kitchen status changes, and other operational transactions.

  Keeping all merchants' operational data in one shared database could increase database contention, complicate maintenance, and make performance optimization more difficult as the platform grows.

  Requested Implementation

  1. Separate merchant databases: Automatically create an isolated MySQL operational database for each merchant on AWS RDS when their account is provisioned.

  2. Central merchant registry: Maintain a central database that maps each merchant account to its operational database and stores platform-level information.

  3. Automatic database routing: The application must securely identify the authenticated merchant and route requests to the correct database without affecting other merchants.

  4. Scalable AWS infrastructure: Initially, multiple merchant databases may share an RDS instance. The architecture must support moving high-volume merchants to separate RDS instances when required.

  5. Complete operational data isolation: Each merchant's orders, order items, products, inventory records, payments, tables, and other operational data must remain isolated from other merchants.

  6. Automated provisioning and maintenance: Implement automatic schema creation, database migrations, backups, recovery procedures, connection pooling, and monitoring.

  7. Compatibility with all KMRS applications: All supported ordering channels and restaurant applications must use the correct merchant database without changing the existing user experience.

  Why This Is Important

  • Better scalability: The architecture can accommodate growing numbers of merchants and increasing transaction volumes.

  • Performance management: Merchant workloads can be monitored and optimized individually, with the option to move busy merchants to dedicated RDS instances.

  • Data isolation: Separate databases reduce the risk of accidental cross-merchant data access and simplify merchant-specific backup and recovery.

  • Reliable restaurant operations: As POS, Waiter, KDS, Kiosk and online ordering generate transactions simultaneously, the database architecture can evolve to accommodate the workload.

  • Easier maintenance: Individual merchant databases can be backed up, restored, migrated and maintained more independently.

  • Long-term growth: The system can expand without requiring a complete redesign of the merchant database architecture.

  Expected Outcome

  KMRS should use a scalable multi-tenant architecture with a central platform database and separate operational databases for individual merchants. The solution should support automatic provisioning, secure database routing, reliable synchronization with the KMRS Local Server, and future distribution across multiple AWS RDS instances.

  The objective is to provide every merchant with reliable, secure and scalable restaurant operations as KMRS expands across POS, in-house ordering, and multiple online ordering channels.