Implement Comprehensive Database Migration System and Optimize Query Performance

[eccentriccoder01]

🎯 Issue Type

Architecture / Performance / Hard

📝 Description

The current database implementation has several critical issues that need addressing:

Problems Identified:

1. No Migration System: Database schema is created directly in code (database.py) with no versioning or migration history
2. SQL Injection Risks: Multiple string interpolation queries in database.py (lines with f-strings in SQL)
3. N+1 Query Problems: Sequential database calls in orders.py and products.py
4. No Connection Pooling: Each request opens/closes connections inefficiently
5. Missing Indexes: No optimization for frequently queried columns
6. No Query Caching: Repeated queries for categories, products fetch from DB every time
7. Transaction Management: Incomplete rollback handling for complex operations

🔍 Current Issues in Code

In backend/database.py:

• Line 64: Uses string formatting in UPDATE queries
• Line ~500-600: get_products_with_filters does multiple queries instead of JOIN
• No database indexes defined for Email, StoreID, CategoryID
• Context manager doesn't handle nested transactions

In backend/orders.py:

• create_order function makes 4+ separate DB calls that should be atomic
• No proper transaction rollback if Razorpay fails but DB insert succeeds

✅ Proposed Solution

Phase 1: Migration System (Critical)

1. Implement Alembic for database migrations
2. Convert current schema to initial migration
3. Add version control for schema changes
4. Document migration workflow

Phase 2: Query Optimization

1. Add database indexes:
   • User.Email (unique index)
   • User.StoreID (foreign key index)
   • Inventory.CategoryID
   • Orders.UserId, Orders.Status
2. Implement connection pooling with SQLAlchemy
3. Fix N+1 queries using JOINs and eager loading
4. Add query result caching (Redis/in-memory)

Phase 3: Security Hardening

1. Replace all string interpolation with parameterized queries
2. Add SQL query logging in development
3. Implement prepared statements for common queries
4. Add query timeout limits

Phase 4: Transaction Management

1. Implement proper ACID transactions for orders
2. Add savepoints for complex multi-step operations
3. Implement retry logic for deadlock scenarios
4. Add transaction monitoring and logging

🔧 Technical Implementation

Required Dependencies:

alembic==1.13.1
sqlalchemy==2.0.25
redis==5.0.1
flask-sqlalchemy==3.1.1

Database Schema Changes Needed:

CREATE INDEX idx_user_email ON User(Email);
CREATE INDEX idx_user_store ON User(StoreID);
CREATE INDEX idx_inventory_category ON Inventory(CategoryID);
CREATE INDEX idx_orders_user ON Orders(UserId);
CREATE INDEX idx_orders_status ON Orders(Status);

Example Query Optimization:

Before (N+1):

orders = get_user_orders(user_id)
for order in orders:
    order['items'] = get_order_items(order['id'])

After (JOIN):

orders_with_items = db.execute('''
    SELECT o.*, i.* FROM Orders o
    LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
    WHERE o.UserId = ?
''', (user_id,))

🔧 Acceptance Criteria

Must Have:

• Alembic migration system integrated
• All existing tables have migration files
• Database indexes added for key columns
• Connection pooling implemented
• All SQL injection vulnerabilities fixed
• Transaction management for orders working
• Query performance improved by 50%+

Should Have:

• Redis caching for product categories
• Query logging in development mode
• Migration documentation
• Database backup scripts
• Performance benchmarks documented

Nice to Have:

• Database query monitoring dashboard
• Automated query optimization suggestions
• Read replica support

📊 Expected Impact

• Performance: 50-70% reduction in database query time
• Security: Elimination of SQL injection risks
• Maintainability: Easy schema updates via migrations
• Scalability: Support for 10x more concurrent users

🧪 Testing Requirements

1. Migration up/down tests
2. Load testing with 1000+ concurrent requests
3. Transaction rollback tests
4. SQL injection penetration tests
5. Query performance benchmarks

📚 References

• Alembic: https://alembic.sqlalchemy.org/
• SQLAlchemy: https://www.sqlalchemy.org/
• Database Indexing Best Practices: https://use-the-index-luke.com/
• OWASP SQL Injection Prevention: https://cheatsheetseries.owasp.org/

⚠️ Breaking Changes

This will require:

• Database backup before migration
• Potential downtime for index creation
• Code changes across multiple files
• Updated deployment procedures

💡 Additional Context

This is a high-priority architectural improvement that affects scalability, security, and maintainability. The current implementation works for development but will cause serious issues in production with real user load.

[Aditya8369]

@eccentriccoder01 I would like to work on this issue under jwoc, please assign me

[eccentriccoder01]

Sure @Aditya8369! Just make sure to:
 

• Stay updated by fetching changes from the upstream in your local repo, as multiple people are constantly updating the main.
• Include relevant screenshots/info in the PR.
• Reach out in case of any doubts...
   
  Assigning this issue to you, 6 days for this. All the best👍🏻