Implement User Profile Data Layer with Supabase

[salazarsebas]

Implement User Profile Data Layer with Supabase

Description

Implement the data layer for StellarMarket's user profile functionality using Supabase. This includes setting up the necessary database tables, relationships, and APIs to manage user profiles, orders, reviews, wishlists, and delivery tracking.

Acceptance Criteria

Database Schema Implementation

1. Extended User Profiles Table

   • Extend the existing profiles table with:
     • membership_level: enum ('standard', 'premium', 'platinum')
     • membership_since: timestamp
     • display_name: string
     • avatar_url: string
     • email_public: boolean
     • last_active: timestamp

2. Orders Table

   • Create table with schema:
     • id: UUID primary key
     • user_id: FK to auth.users
     • status: enum ('processing', 'shipped', 'in_transit', 'delivered', 'cancelled')
     • total_amount: numeric
     • currency: string default 'XLM'
     • created_at: timestamp
     • updated_at: timestamp
     • shipping_address_id: FK to addresses
     • payment_intent_id: string (for payment processor reference)

3. Order Items Table

   • id: UUID primary key
   • order_id: FK to orders
   • product_id: FK to products
   • quantity: integer
   • price_at_purchase: numeric
   • product_name_at_purchase: string
   • product_options: JSONB (for size, color, etc.)

4. Reviews Table

   • id: UUID primary key
   • user_id: FK to auth.users
   • product_id: FK to products
   • order_item_id: FK to order_items (optional)
   • rating: integer (1-5)
   • comment: text
   • created_at: timestamp
   • updated_at: timestamp
   • helpful_votes: integer
   • reported: boolean

5. Wishlist Items Table

   • id: UUID primary key
   • user_id: FK to auth.users
   • product_id: FK to products
   • added_at: timestamp
   • notes: text

6. Shipment Tracking Table

   • id: UUID primary key
   • order_id: FK to orders
   • tracking_number: string
   • carrier: string
   • estimated_delivery: timestamp
   • status: enum ('processing', 'shipped', 'in_transit', 'out_for_delivery', 'delivered', 'exception')
   • status_updated_at: timestamp
   • last_location: string
   • delivery_notes: text

Row-Level Security Policies

1. Profiles Table Policies

   • Users can read their own profile
   • Admin can read all profiles
   • Users can update only their own profile
   • Public fields accessible to everyone

2. Orders Table Policies

   • Users can read only their own orders
   • Admin can read all orders
   • Orders can only be created/updated by system or admin

3. Reviews Table Policies

   • Anyone can read published reviews
   • Users can create/update only their own reviews
   • Users can only create one review per product

4. Wishlist Table Policies

   • Users can only see their own wishlist items
   • Users can only add/remove from their own wishlist

5. Shipment Tracking Policies

   • Users can view tracking for their own orders
   • Admin and system can update tracking info

API Implementation

1. Profile Management

   • Get user profile with membership details
   • Update profile information
   • Upload and manage profile avatar
   • Get activity metrics (order count, review count, wishlist count)

2. Order History

   • Get paginated order history
   • Get order details by ID
   • Get recent orders (last 3-5)
   • Filter orders by status
   • Search orders by product name or order ID

3. Review Management

   • Get user's submitted reviews
   • Create/update reviews for purchased products
   • Delete user's own reviews
   • Get review metrics (total, average rating given)

4. Wishlist Management

   • Add/remove items to wishlist
   • Get wishlist items with product details
   • Get wishlist count
   • Move wishlist item to cart

5. Shipment Tracking

   • Get upcoming deliveries
   • Get delivery calendar (next 30 days)
   • Subscribe to shipment updates
   • Get shipment history

Database Functions and Triggers

1. Update User Activity

   • Function to update last_active timestamp
   • Trigger on user actions (login, purchase, etc.)

2. Calculate Review Metrics

   • Function to update product ratings when reviews change
   • Function to calculate user review metrics

3. Order Status Updates

   • Function to update order status based on shipment status
   • Trigger email notifications on status change

4. Auto-Archive Old Orders

   • Function to move old orders to archive table
   • Scheduled via pgcron

Security Implementation

1. Data Access Control

   • Implement proper RLS policies
   • Create service roles with appropriate permissions
   • Audit logging for sensitive operations

2. Personal Data Handling

   • Implement GDPR-compliant data access/removal
   • Personal data encryption where appropriate
   • Data retention policies

Performance Considerations

1. Indexing Strategy

   • Create indexes for:
     • orders(user_id, created_at)
     • reviews(user_id, product_id)
     • wishlist_items(user_id, added_at)
     • shipment_tracking(order_id, estimated_delivery)

2. Query Optimization

   • Optimize joins for order history
   • Use materialized views for activity metrics
   • Implement efficient pagination

3. Caching Strategy

   • Cache profile data (short TTL)
   • Cache activity metrics (medium TTL)
   • Cache recent orders (short TTL)

Integration Requirements

1. Authentication Integration

   • Hook into auth events for profile creation/updates
   • Handle account merging/linking
   • Support social login profile data

2. Notification System

   • Trigger notifications on:
     • Order status changes
     • Upcoming deliveries
     • Review requests (post-delivery)
     • Wishlist item price changes

3. Analytics Integration

   • Track profile completeness
   • Measure user engagement metrics
   • Record order frequency and value

Testing Requirements

1. Unit Tests

   • Test database functions and triggers
   • Test RLS policies effectiveness
   • Test data validation

2. Integration Tests

   • Test API endpoints with various scenarios
   • Test concurrent data modifications
   • Test performance under load

3. Security Tests

   • Verify RLS prevents unauthorized access
   • Test for SQL injection vulnerabilities
   • Verify proper error handling (no data leakage)

Migration Plan

• Create database migration files for new tables
• Create data migration for existing users
• Implement backward compatibility for existing API endpoints
• Deploy in phases with feature flags

[Adecom16]

I enjoy designing secure, scalable data systems. This issue covers end-to-end architecture schema design, RLS, API, and performance which aligns perfectly with my backend and Supabase experience. It’s a great opportunity to contribute meaningful infrastructure to StellarMarket

[iGEORGE17]

I'm excited to apply for the "User Profile Data Layer with Supabase" task. I have hands-on experience building scalable data layers with Supabase, including complex schemas, Row-Level Security, triggers, and custom functions. I'm also very comfortable working with auth integrations, caching strategies, and performance tuning.

Here's how I plan to approach the task:

Set up all required tables (profiles, orders, reviews, etc.) with RLS and relationships

Implement Supabase functions, triggers, and APIs (like update_last_active, review metrics, etc.)

Write unit and integration tests, optimize queries, and set up caching for key data like profiles and orders

Security and data integrity are a top priority for me — I’ll make sure access controls are solid, sensitive actions are audited, and personal data is handled responsibly (GDPR-compliant). I’ll also add support for notifications, analytics hooks, and social login profile linking where needed.

[Adecom16]

Thank you for the opportunity to work on the User Profile Data Layer with Supabase for StellarMarket. I've reviewed the full specification from schema design and RLS policies to API and performance requirements and I’m confident in delivering a complete and production-ready implementation within the next 24 hours.

I’ll be focusing on:

Designing and migrating all required tables with relationships

Implementing strict RLS policies and Supabase functions/triggers

Building performant queries and caching layers

Securing the APIs and ensuring GDPR/data compliance

Writing test cases to validate functionality and security

[salazarsebas]

Thank you for the opportunity to work on the User Profile Data Layer with Supabase for StellarMarket. I've reviewed the full specification from schema design and RLS policies to API and performance requirements and I’m confident in delivering a complete and production-ready implementation within the next 24 hours.

I’ll be focusing on:

Designing and migrating all required tables with relationships

Implementing strict RLS policies and Supabase functions/triggers

Building performant queries and caching layers

Securing the APIs and ensuring GDPR/data compliance

Writing test cases to validate functionality and security

I think the whole approach is great, but two weeks after your comment, there is still no pull request with what you mentioned. We would like to know what the latest update on this issue is.

@Adecom16