Database Schema Needs Updates

[TejasGoyal0]

🚨 Database Schema Issues & Inconsistencies

📋 Issue Summary

The current database schema is insufficient to handle new pages and requirements, particularly around customer tags and thresholds. This has led to data inconsistencies and functionality issues across the application.

🔍 Current Problems

1. Shop ID Mismatch Issues

• Problem: Session IDs vs Shop IDs confusion
  • Session ID: offline_tejas0.myshopify.com
  • Session Shop: tejas0.myshopify.com
  • Customer ShopId: offline_tejas0.myshopify.com
• Impact: Customer tagging system fails because thresholds and customers belong to different shop IDs
• Evidence: Tenma Kenzo has no tags despite meeting all threshold criteria

2. Inconsistent Data Relationships

• Problem: Customers and thresholds are linked to different shop identifiers
• Impact: Tag assignment fails silently
• Evidence:

  -- Customers belong to one shop ID
  SELECT shopId FROM Customer WHERE firstName = 'Tenma';
  -- Returns: offline_tejas0.myshopify.com
  
  -- Thresholds belong to different shop ID  
  SELECT shopId FROM Threshold LIMIT 1;
  -- Returns: tejas0.myshopify.com

3. Missing Data Integrity Constraints

• Problem: No foreign key constraints between related tables
• Impact: Orphaned records and inconsistent data
• Evidence: Thresholds can exist without corresponding sessions

4. Insufficient Schema for Multi-Shop Support

• Problem: Schema doesn't properly handle multiple Shopify shops
• Impact: Data isolation issues between different shops
• Evidence: All data mixed together regardless of shop

🎯 Required Schema Updates

1. Standardize Shop Identification

-- Add consistent shop_id field across all tables
ALTER TABLE Session ADD COLUMN shop_id VARCHAR(255);
ALTER TABLE Customer ADD COLUMN shop_id VARCHAR(255);
ALTER TABLE Threshold ADD COLUMN shop_id VARCHAR(255);
ALTER TABLE CustomerTag ADD COLUMN shop_id VARCHAR(255);

2. Add Foreign Key Constraints

-- Ensure data integrity
ALTER TABLE Customer ADD CONSTRAINT fk_customer_session 
  FOREIGN KEY (shop_id) REFERENCES Session(shop_id);
  
ALTER TABLE Threshold ADD CONSTRAINT fk_threshold_session 
  FOREIGN KEY (shop_id) REFERENCES Session(shop_id);
  
ALTER TABLE CustomerTag ADD CONSTRAINT fk_customertag_customer 
  FOREIGN KEY (customer_id) REFERENCES Customer(id);

3. Improve CustomerTag Schema

-- Current schema issues:
-- - No shop_id for multi-shop isolation
-- - No proper indexing for performance
-- - Missing audit fields

-- Proposed improvements:
ALTER TABLE CustomerTag ADD COLUMN shop_id VARCHAR(255);
ALTER TABLE CustomerTag ADD COLUMN created_by VARCHAR(255);
ALTER TABLE CustomerTag ADD COLUMN updated_at TIMESTAMP;
CREATE INDEX idx_customertag_shop_customer ON CustomerTag(shop_id, customer_id);

4. Add Audit Fields

-- Add audit fields to all tables
ALTER TABLE Customer ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE Customer ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE Threshold ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE Threshold ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

🔧 Technical Debt

1. Data Migration Required

• Existing data needs to be migrated to new schema
• Shop ID standardization across all tables
• Cleanup of orphaned records

2. Application Code Updates

• Update all database queries to use new schema
• Modify Prisma schema file
• Update threshold processing logic
• Fix customer tagging system

3. Testing Requirements

• Unit tests for new schema constraints
• Integration tests for multi-shop scenarios
• Data migration testing
• Performance testing with new indexes

📊 Impact Assessment

High Priority Issues

1. Customer Tagging System Broken - Users can't see proper tags
2. Data Inconsistency - Same customer data across different shops
3. Performance Issues - Missing indexes on frequently queried fields

Medium Priority Issues

1. Audit Trail Missing - No tracking of data changes
2. Multi-Shop Support - Current schema doesn't support multiple shops properly

🚀 Proposed Solution

Phase 1: Schema Migration

1. Create new migration with updated schema
2. Add foreign key constraints
3. Standardize shop identification
4. Add audit fields

Phase 2: Data Migration

1. Migrate existing data to new schema
2. Clean up orphaned records
3. Standardize shop IDs across all tables

Phase 3: Application Updates

1. Update Prisma schema
2. Modify all database queries
3. Fix customer tagging system
4. Add proper error handling

Phase 4: Testing & Validation

1. Comprehensive testing of all features
2. Performance testing
3. Data integrity validation

📝 Acceptance Criteria

• All tables have consistent shop_id field
• Foreign key constraints are properly implemented
• Customer tagging system works correctly
• Multi-shop data isolation is maintained
• Audit trail is available for all data changes
• Performance is acceptable with new indexes
• All existing functionality continues to work

🔗 Related Issues

• Customer tagging not working properly
• Inconsistent data between shops
• Missing audit trail for data changes
• Performance issues with large datasets

📋 Labels

• database
• schema
• migration
• high-priority
• technical-debt
• data-integrity

---

Priority: 🔴 High
Estimated Effort: Weekend
Dependencies: None
Assigned To: Tejas