Implement Seller Dashboard Data Layer with Supabase

[salazarsebas]

Implement Seller Dashboard Data Layer with Supabase

Description

Implement the data layer for StellarMarket's seller dashboard using Supabase. This includes setting up database tables, views, functions, and APIs to support seller analytics, order management, and performance tracking.

Acceptance Criteria

Database Schema Implementation

1. Stores Table

   • Extend the existing stores table with:
     • revenue_total: decimal (calculated field)
     • active_product_count: integer (calculated field)
     • pending_order_count: integer (calculated field)
     • average_rating: decimal
     • rating_count: integer
     • monthly_sales_goal: decimal
     • customer_goal: integer
     • review_goal: integer
     • last_updated: timestamp

2. Store Analytics Table

   • Create table with schema:
     • id: UUID primary key
     • store_id: FK to stores
     • date: date
     • revenue: decimal
     • order_count: integer
     • new_customers: integer
     • returning_customers: integer
     • average_order_value: decimal
     • conversion_rate: decimal
     • view_count: integer

3. Store Performance Goals

   • id: UUID primary key
   • store_id: FK to stores
   • goal_type: enum ('sales', 'customers', 'reviews', 'conversion', 'aov')
   • target_value: decimal
   • current_value: decimal
   • time_period: enum ('daily', 'weekly', 'monthly', 'quarterly', 'yearly')
   • start_date: date
   • end_date: date
   • created_at: timestamp
   • updated_at: timestamp

4. Order Status History

   • id: UUID primary key
   • order_id: FK to orders
   • status: enum ('created', 'processing', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded')
   • changed_at: timestamp
   • changed_by: FK to auth.users
   • notes: text

Database Views

1. Store Dashboard View

CREATE VIEW seller_dashboard_view AS
SELECT 
  s.id as store_id,
  s.name as store_name,
  s.owner_id,
  COALESCE(SUM(o.total_amount), 0) as total_revenue,
  COUNT(DISTINCT p.id) FILTER (WHERE p.active = true) as active_products,
  COUNT(DISTINCT o.id) FILTER (WHERE o.status = 'processing') as pending_orders,
  COALESCE(AVG(r.rating), 0) as average_rating,
  COUNT(DISTINCT r.id) as rating_count,
  COUNT(DISTINCT o.id) as total_orders
FROM stores s
LEFT JOIN products p ON s.id = p.store_id
LEFT JOIN orders o ON s.id = o.store_id
LEFT JOIN reviews r ON s.id = r.store_id
GROUP BY s.id;

2. Recent Orders View

CREATE VIEW seller_recent_orders_view AS
SELECT 
  o.id as order_id,
  o.store_id,
  o.total_amount,
  o.status,
  o.created_at,
  o.updated_at,
  u.display_name as customer_name,
  oi.product_id,
  oi.quantity,
  oi.price_at_purchase,
  p.name as product_name,
  p.variant as product_variant
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN auth.users u ON o.user_id = u.id
ORDER BY o.created_at DESC;

3. Store Performance View

CREATE VIEW store_performance_view AS
SELECT
  s.id as store_id,
  g.goal_type,
  g.target_value,
  g.current_value,
  CASE 
    WHEN g.target_value > 0 THEN 
      ROUND((g.current_value / g.target_value) * 100)
    ELSE 0
  END as percentage,
  g.time_period,
  g.start_date,
  g.end_date
FROM stores s
JOIN store_performance_goals g ON s.id = g.store_id;

Database Functions

1. Calculate Store Revenue

CREATE OR REPLACE FUNCTION calculate_store_revenue(store_id UUID)
RETURNS decimal AS $$
DECLARE
  total decimal;
BEGIN
  SELECT COALESCE(SUM(total_amount), 0)
  INTO total
  FROM orders
  WHERE store_id = calculate_store_revenue.store_id
    AND status NOT IN ('cancelled', 'refunded');
  
  RETURN total;
END;
$$ LANGUAGE plpgsql;

2. Update Store Metrics

CREATE OR REPLACE FUNCTION update_store_metrics()
RETURNS trigger AS $$
BEGIN
  UPDATE stores
  SET 
    revenue_total = calculate_store_revenue(NEW.store_id),
    active_product_count = (
      SELECT COUNT(*) FROM products 
      WHERE store_id = NEW.store_id AND active = true
    ),
    pending_order_count = (
      SELECT COUNT(*) FROM orders 
      WHERE store_id = NEW.store_id AND status = 'processing'
    ),
    last_updated = NOW()
  WHERE id = NEW.store_id;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Calculate Performance Percentage

CREATE OR REPLACE FUNCTION calculate_performance_percentage(
  current_value decimal,
  target_value decimal
) RETURNS integer AS $$
BEGIN
  IF target_value IS NULL OR target_value = 0 THEN
    RETURN 0;
  END IF;
  
  RETURN LEAST(ROUND((current_value / target_value) * 100), 100);
END;
$$ LANGUAGE plpgsql;

Row-Level Security Policies

1. Stores Table Policies

-- Allow sellers to read their own store data
CREATE POLICY "Sellers can view their own store"
  ON stores FOR SELECT
  USING (auth.uid() = owner_id);

-- Allow sellers to update their own store
CREATE POLICY "Sellers can update their own store"
  ON stores FOR UPDATE
  USING (auth.uid() = owner_id);

2. Analytics Table Policies

-- Only store owners can view their analytics
CREATE POLICY "Sellers can view their own analytics"
  ON store_analytics FOR SELECT
  USING (
    auth.uid() IN (
      SELECT owner_id FROM stores WHERE id = store_id
    )
  );

3. Orders Policies for Sellers

-- Sellers can view orders for their store
CREATE POLICY "Sellers can view their store orders"
  ON orders FOR SELECT
  USING (
    store_id IN (
      SELECT id FROM stores WHERE owner_id = auth.uid()
    )
  );

-- Sellers can update order status
CREATE POLICY "Sellers can update order status"
  ON orders FOR UPDATE
  USING (
    store_id IN (
      SELECT id FROM stores WHERE owner_id = auth.uid()
    )
  )
  WITH CHECK (
    -- Only allow updating specific fields
    (OLD.store_id = NEW.store_id) AND
    (OLD.user_id = NEW.user_id) AND
    (OLD.total_amount = NEW.total_amount)
  );

API Implementation

1. Dashboard Overview API

   • Get store dashboard summary
   • Get revenue metrics
   • Get product and order counts
   • Get store rating

2. Recent Orders API

   • Get paginated recent orders
   • Get order details
   • Update order status
   • Filter orders by status

3. Store Performance API

   • Get performance metrics
   • Set performance goals
   • Track goal progress
   • Get historical performance data

4. Analytics API

   • Get time-series revenue data
   • Get customer growth metrics
   • Get product performance stats
   • Get conversion analytics

Security Considerations

1. Data Access Control

   • Strict RLS policies to ensure sellers only access their data
   • Audit logging for sensitive operations
   • Prevent data leakage between sellers

2. Input Validation

   • Validate all input parameters
   • Sanitize order data
   • Validate goal targets as positive values

3. Rate Limiting

   • Implement rate limiting for analytics APIs
   • Prevent abuse of dashboard refresh

Performance Optimization

1. Indexing Strategy

   • Create indexes for:
     • orders(store_id, status, created_at)
     • products(store_id, active)
     • store_analytics(store_id, date)
     • store_performance_goals(store_id, goal_type, time_period)

2. Materialized Views

   • Create materialized views for heavy dashboard queries
   • Set up refresh schedule (every hour)
   • Implement incremental view maintenance where possible

3. Caching Strategy

   • Cache dashboard view data (5-minute TTL)
   • Cache performance metrics (15-minute TTL)
   • Use edge caching for static dashboard elements

Testing Requirements

1. Unit Tests

   • Test all database functions
   • Test RLS policy effectiveness
   • Test calculation accuracy

2. Integration Tests

   • Test API endpoints with various scenarios
   • Test dashboard data consistency
   • Test performance under load

3. Security Tests

   • Verify RLS prevents cross-seller data access
   • Test for SQL injection vulnerabilities
   • Verify proper error handling

Definition of Done

• All database schemas implemented and migrated
• Functions and triggers tested and working
• APIs implemented and documented
• RLS policies verified for security
• Performance optimization applied
• Tests passing for all components
• Documentation updated

[kelvinempires]

worked on similar pr will be submitted in less than 3 days

[Bran18]

worked on similar pr will be submitted in less than 3 days

@kelvinempires, could you please update us on whether you will be working on this? Instead of the expected three days, we have already been waiting for two weeks. If you're unable to proceed, that's completely fine; we just need to know the status. Thank you!

[kelvinempires]

Sorry for the late reply.. I had ao much task on my table ..I won't be able-to handle yours

…

On Sat, Aug 2, 2025, 7:27 PM Brandon Fernández ***@***.***> wrote: *Bran18* left a comment (ScaffoldRust/SRust-Marketplace#53) <#53 (comment)> worked on similar pr will be submitted in less than 3 days @kelvinempires <https://github.com/kelvinempires>, could you please update us on whether you will be working on this? Instead of the expected three days, we have already been waiting for two weeks. If you're unable to proceed, that's completely fine; we just need to know the status. Thank you! — Reply to this email directly, view it on GitHub <#53 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BKPOO2JTFEB4JHNKQEYZYFD3LT7JRAVCNFSM6AAAAACCCTEWQ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTCNBWGY2TMOJRGQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[davedumto]

Hi, my name is David, and I am a frontend developer skilled in React, Next.js, TypeScript, and Tailwind CSS. For this task, I will:

• Implement the seller dashboard data layer on Supabase: create/extend tables (stores, store_analytics, store_performance_goals, order_status_history) and add the specified columns (revenue_total, active_product_count, pending_order_count, average_rating, etc.).
• Add DB views (seller_dashboard_view, seller_recent_orders_view, store_performance_view) and materialized-view strategy for heavy queries with refresh plan.
• Implement PL/pgSQL functions and triggers (calculate_store_revenue, update_store_metrics, calculate_performance_percentage) and wire triggers to keep store metrics in sync.
• Create row-level security policies for stores, analytics, and orders to ensure sellers access only their data; add audit logging hooks where appropriate.
• Build API endpoints (dashboard overview, recent orders with pagination & filters, performance goals CRUD, analytics timeseries) with input validation, rate limiting, and caching hints (5–15 min TTL).
• Add indexes, incremental/materialized view maintenance, and caching/edge strategies to meet performance goals and reduce load.
• Write unit tests for DB functions, integration tests for APIs, and security tests verifying RLS and injection protections; include migration scripts.
• Document schema, views, functions, RLS decisions, and provide a migration + rollout plan with rollback steps.

ETA: 2 days

~ OnlyDust Stellar Fellow

Recommended by OnlyDust for high-quality and dependable contributions.

[FabianSanchezD]

Hi @salazarsebas, I'm Fabian Sanchez and I'd like to work on this issue. I have experience working with Supabase and backend, so my ETA is less than 3 days. Thanks!

[FabianSanchezD]

Hi @salazarsebas, I'm sorry but I won't be able to handle this issue. I have a lot of university work that I need to address

[salazarsebas]

Hi @salazarsebas, I'm sorry but I won't be able to handle this issue. I have a lot of university work that I need to address

Hi @FabianSanchezD , no problem. We would be delighted to receive an open source contribution from you another time, or if you would like to revisit the issue at a later date, please let us know.

[lucifer1017]

Hey There. Let me handle this for you!

Recommended by OnlyDust for exceptional experience, quality, and reliability.