Social Commerce Service (SCS) - PostgreSQL Edition

A Java/Maven implementation with PostgreSQL for real-time product chats, community feeds, and AI moderation

🚀 Features

✔ Product Chat - Real-time messaging with WebSockets and PostgreSQL history
✔ Community Feed - TikTok-style feed with personalized recommendations
✔ Content Moderation - AI + human review workflow with audit trails
✔ PostgreSQL Optimized - Full leverage of relational model with JSON capabilities for social commerce data

📦 Project Structure

social-commerce-service/
├── 📁 .github/                   # GitHub workflows
│   └── 📁 workflows/
│       ├── ci-cd.yml            # CI/CD pipeline
│       └── codeql-analysis.yml  # Security scanning
│
├── 📁 config/                    # Configuration files
│   ├── application-dev.properties
│   ├── application-prod.properties
│   └── db-init.sql              # DB initialization scripts
│
├── 📁 src/
│   ├── 📁 main/
│   │   ├── 📁 java/com/socialcommerce/
│   │   │   ├── 📁 chat/                # Product Chat module
│   │   │   │   ├── 📁 api/             # Controllers
│   │   │   │   │   └── ChatController.java
│   │   │   │   ├── 📁 config/          # WebSocket config
│   │   │   │   │   └── WebSocketConfig.java
│   │   │   │   ├── 📁 domain/          # Models and DTOs
│   │   │   │   │   ├── ChatMessage.java
│   │   │   │   │   └── MessageStatus.java
│   │   │   │   ├── 📁 repository/      # JPA repositories
│   │   │   │   │   └── ChatRepository.java
│   │   │   │   ├── 📁 service/         # Business logic
│   │   │   │   │   └── ChatService.java
│   │   │   │   └── 📁 exception/       # Custom exceptions
│   │   │   │       └── ChatException.java
│   │   │   │
│   │   │   ├── 📁 feed/                # Community Feed module
│   │   │   │   ├── 📁 api/
│   │   │   │   │   └── FeedController.java
│   │   │   │   ├── 📁 domain/
│   │   │   │   │   └── FeedItem.java
│   │   │   │   ├── 📁 repository/
│   │   │   │   │   └── FeedRepository.java
│   │   │   │   ├── 📁 service/
│   │   │   │   │   └── FeedService.java
│   │   │   │   └── 📁 ranking/         # Recommendation algorithms
│   │   │   │       └── RecommendationEngine.java
│   │   │   │
│   │   │   ├── 📁 moderation/          # Content Moderation
│   │   │   │   ├── 📁 api/
│   │   │   │   │   └── ModerationController.java
│   │   │   │   ├── 📁 domain/
│   │   │   │   │   ├── ModerationDecision.java
│   │   │   │   │   └── ModerationStatus.java
│   │   │   │   ├── 📁 repository/
│   │   │   │   │   └── ModerationRepository.java
│   │   │   │   ├── 📁 service/
│   │   │   │   │   └── ModerationService.java
│   │   │   │   └── 📁 rules/           # Moderation rules
│   │   │   │       └── ContentRules.java
│   │   │   │
│   │   │   ├── 📁 common/              # Shared components
│   │   │   │   ├── 📁 config/          # Global configs
│   │   │   │   │   ├── DatabaseConfig.java
│   │   │   │   │   └── SecurityConfig.java
│   │   │   │   ├── 📁 exception/       # Global exception handler
│   │   │   │   │   └── GlobalExceptionHandler.java
│   │   │   │   ├── 📁 util/            # Utility classes
│   │   │   │   │   └── DateTimeUtil.java
│   │   │   │   └── 📁 security/        # Auth and security
│   │   │   │       └── JwtUtil.java
│   │   │   │
│   │   │   └── SocialCommerceApplication.java  # Main class
│   │   │
│   │   └── 📁 resources/
│   │       ├── db/migration/          # Flyway migrations
│   │       │   ├── V1__init_schema.sql
│   │       │   └── V2__seed_data.sql
│   │       ├── static/                # Frontend assets
│   │       ├── templates/             # Thymeleaf templates
│   │       └── application.properties
│   │
│   └── 📁 test/
│       ├── 📁 java/com/socialcommerce/
│       │   ├── 📁 chat/
│       │   │   └── ... (tests for chat)
│       │   ├── 📁 feed/
│       │   │   └── ... (tests for feed)
│       │   └── 📁 moderation/
│       │       └── ... (tests for moderation)
│       └── 📁 resources/
│           └── test-data/             # Test JSON fixtures
│
├── 📁 docs/                      # Documentation
│   ├── API.md                    # API specifications
│   ├── ARCHITECTURE.md           # System design
│   └── DB_SCHEMA.md              # Database schema docs
│
├── 📁 scripts/                   # Deployment scripts
│   ├── deploy.sh
│   ├── db-backup.sh
│   └── health-check.sh
│
├── .gitignore
├── Dockerfile
├── docker-compose.yml            # For local development
├── HELP.md
├── LICENSE
├── pom.xml                       # Parent POM
└── README.md

⚙️ Tech Stack

Java 17+ (Spring Boot 3.x)
PostgreSQL 14+ (Relational model with JSONB)
Spring WebSockets (STOMP protocol)
Spring Data JPA (Hibernate ORM)
Flyway (Database migrations)
Test Containers (Integration testing)
Lombok (Reduced boilerplate)
JWT (Authentication)

📄 Key Files Implementation

1. Core Domain Models

ChatMessage.java:

package com.socialcommerce.chat.domain;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import java.time.LocalDateTime;

@Entity
@Table(name = "chat_messages", indexes = {
    @Index(name = "idx_product_status_timestamp", 
           columnList = "product_id, status, timestamp DESC")
})
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ChatMessage {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "product_id", nullable = false)
    private String productId;
    
    @Column(name = "user_id", nullable = false)
    private String userId;
    
    @Column(nullable = false, length = 1000)
    private String content;
    
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private MessageStatus status = MessageStatus.SENT;
    
    @Column(nullable = false)
    private LocalDateTime timestamp = LocalDateTime.now();
    
    // Constructor for easy creation of a new message
    public ChatMessage(String productId, String userId, String content) {
        this.productId = productId;
        this.userId = userId;
        this.content = content;
    }
}

MessageStatus.java:

package com.socialcommerce.chat.domain;

public enum MessageStatus {
    SENT,
    DELIVERED,
    READ,
    MODERATED,
    HIDDEN
}

FeedItem.java:

package com.socialcommerce.feed.domain;

import jakarta.persistence.*;
import lombok.Data;
import org.hibernate.annotations.Type;

import java.time.LocalDateTime;

@Entity
@Table(name = "feed_items", indexes = {
    @Index(name = "idx_user_score", columnList = "user_id, score DESC")
})
@Data
public class FeedItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "user_id", nullable = false)
    private String userId;
    
    @Column(nullable = false)
    private Double score;
    
    @Column(name = "content_type", nullable = false)
    private String contentType;
    
    // Using JSONB for flexible content
    @Column(columnDefinition = "jsonb", nullable = false)
    private String content;
    
    @Column(nullable = false)
    private LocalDateTime createdAt = LocalDateTime.now();
    
    @Column(name = "is_promoted", nullable = false)
    private boolean promoted = false;
    
    @Column(name = "engagement_count")
    private Integer engagementCount = 0;
}

ModerationDecision.java:

package com.socialcommerce.moderation.domain;

import jakarta.persistence.*;
import lombok.Data;

import java.time.LocalDateTime;

@Entity
@Table(name = "moderation_decisions")
@Data
public class ModerationDecision {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "content_id", nullable = false)
    private String contentId;
    
    @Column(name = "content_type", nullable = false)
    private String contentType;
    
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private ModerationStatus status;
    
    @Column(name = "ai_confidence")
    private Double aiConfidence;
    
    @Column(name = "review_required")
    private boolean reviewRequired;
    
    @Column(name = "reviewer_id")
    private String reviewerId;
    
    @Column
    private String reason;
    
    @Column(name = "created_at", nullable = false)
    private LocalDateTime createdAt = LocalDateTime.now();
    
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
}

ModerationStatus.java:

package com.socialcommerce.moderation.domain;

public enum ModerationStatus {
    PENDING,
    APPROVED,
    REJECTED,
    NEEDS_REVIEW
}

2. Repository Layer

ChatRepository.java:

package com.socialcommerce.chat.repository;

import com.socialcommerce.chat.domain.ChatMessage;
import com.socialcommerce.chat.domain.MessageStatus;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ChatRepository extends JpaRepository<ChatMessage, Long> {
    
    List<ChatMessage> findByProductIdOrderByTimestampDesc(String productId, Pageable pageable);
    
    @Query("SELECT m FROM ChatMessage m WHERE m.productId = ?1 AND m.status <> 'HIDDEN' ORDER BY m.timestamp DESC")
    List<ChatMessage> findVisibleMessagesByProductId(String productId, Pageable pageable);
    
    List<ChatMessage> findByUserIdAndStatus(String userId, MessageStatus status);
    
    @Query(value = "SELECT * FROM chat_messages WHERE product_id = ?1 AND timestamp > ?2 ORDER BY timestamp", 
          nativeQuery = true)
    List<ChatMessage> findNewMessagesSince(String productId, java.sql.Timestamp since);
}

FeedRepository.java:

package com.socialcommerce.feed.repository;

import com.socialcommerce.feed.domain.FeedItem;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface FeedRepository extends JpaRepository<FeedItem, Long> {
    
    List<FeedItem> findByUserIdOrderByScoreDesc(String userId, Pageable pageable);
    
    @Query("SELECT f FROM FeedItem f WHERE f.contentType = ?1 ORDER BY f.score DESC")
    List<FeedItem> findTopContentByType(String contentType, Pageable pageable);
    
    @Query(value = "SELECT * FROM feed_items WHERE user_id = ?1 ORDER BY score DESC LIMIT ?2", 
           nativeQuery = true)
    List<FeedItem> getPersonalizedFeed(String userId, int limit);
    
    @Query(value = "SELECT * FROM feed_items WHERE is_promoted = true ORDER BY created_at DESC LIMIT ?1", 
           nativeQuery = true)
    List<FeedItem> getPromotedContent(int limit);
}

ModerationRepository.java:

package com.socialcommerce.moderation.repository;

import com.socialcommerce.moderation.domain.ModerationDecision;
import com.socialcommerce.moderation.domain.ModerationStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface ModerationRepository extends JpaRepository<ModerationDecision, Long> {
    
    Optional<ModerationDecision> findByContentIdAndContentType(String contentId, String contentType);
    
    List<ModerationDecision> findByStatus(ModerationStatus status);
    
    List<ModerationDecision> findByReviewRequiredTrueAndStatusNot(ModerationStatus status);
    
    @Query("SELECT m FROM ModerationDecision m WHERE m.aiConfidence < 0.8 AND m.status = 'PENDING' ORDER BY m.createdAt")
    List<ModerationDecision> findLowConfidenceDecisions();
    
    @Query("SELECT COUNT(m) FROM ModerationDecision m WHERE m.status = ?1 AND m.createdAt BETWEEN ?2 AND ?3")
    Long countDecisionsByStatusInTimeRange(ModerationStatus status, LocalDateTime start, LocalDateTime end);
}

3. Service Layer

ChatService.java:

package com.socialcommerce.chat.service;

import com.socialcommerce.chat.domain.ChatMessage;
import com.socialcommerce.chat.domain.MessageStatus;
import com.socialcommerce.chat.exception.ChatException;
import com.socialcommerce.chat.repository.ChatRepository;
import com.socialcommerce.moderation.service.ModerationService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.messaging.simp.SimpMessagingTemplate;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDateTime;
import java.util.List;

@Service
@Slf4j
@RequiredArgsConstructor
public class ChatService {
    
    private final ChatRepository chatRepository;
    private final SimpMessagingTemplate messagingTemplate;
    private final ModerationService moderationService;
    
    @Transactional
    @Retryable(value = Exception.class, maxAttempts = 3)
    public ChatMessage saveAndSendMessage(ChatMessage message) {
        // Save to database
        ChatMessage savedMessage = chatRepository.save(message);
        
        // Request moderation asynchronously
        moderationService.moderateContentAsync(
            savedMessage.getId().toString(),
            "CHAT",
            savedMessage.getContent()
        );
        
        // Broadcast to clients
        messagingTemplate.convertAndSend(
            "/topic/chat/" + message.getProductId(), 
            savedMessage
        );
        
        return savedMessage;
    }
    
    public List<ChatMessage> getRecentMessages(String productId, int limit) {
        Pageable pageable = PageRequest.of(0, limit);
        return chatRepository.findVisibleMessagesByProductId(productId, pageable);
    }
    
    public List<ChatMessage> getNewMessages(String productId, Instant since) {
        return chatRepository.findNewMessagesSince(
            productId, 
            Timestamp.from(since)
        );
    }
    
    @Transactional
    public void updateMessageStatus(Long messageId, MessageStatus status) {
        ChatMessage message = chatRepository.findById(messageId)
            .orElseThrow(() -> new ChatException("Message not found"));
        
        message.setStatus(status);
        chatRepository.save(message);
        
        // Notify clients about status change
        messagingTemplate.convertAndSend(
            "/topic/chat/status/" + message.getProductId(), 
            message
        );
    }
}

FeedService.java:

package com.socialcommerce.feed.service;

import com.socialcommerce.feed.domain.FeedItem;
import com.socialcommerce.feed.ranking.RecommendationEngine;
import com.socialcommerce.feed.repository.FeedRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.concurrent.TimeUnit;

@Service
@Slf4j
@RequiredArgsConstructor
public class FeedService {
    
    private final FeedRepository feedRepository;
    private final RecommendationEngine recommendationEngine;
    
    public List<FeedItem> getPersonalizedFeed(String userId, int limit) {
        long startTime = System.nanoTime();
        
        // Get user feed with custom ranking by score
        List<FeedItem> feedItems = feedRepository.getPersonalizedFeed(userId, limit);
        
        // Apply recommendation engine to rerank items
        List<FeedItem> rankedItems = recommendationEngine.rankItems(userId, feedItems);
        
        long duration = TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - startTime);
        log.info("Generated feed for user {} with {} items in {}ms", userId, rankedItems.size(), duration);
        
        return rankedItems;
    }
    
    public FeedItem createFeedItem(FeedItem item) {
        // Initialize score based on content type and other factors
        if (item.getScore() == null) {
            item.setScore(calculateInitialScore(item));
        }
        
        return feedRepository.save(item);
    }
    
    public List<FeedItem> getPromotedContent(int limit) {
        return feedRepository.getPromotedContent(limit);
    }
    
    public void updateEngagementCount(Long itemId, int delta) {
        feedRepository.findById(itemId).ifPresent(item -> {
            item.setEngagementCount(item.getEngagementCount() + delta);
            // Recalculate score based on new engagement
            item.setScore(calculateEngagementScore(item));
            feedRepository.save(item);
        });
    }
    
    private Double calculateInitialScore(FeedItem item) {
        // Simple scoring algorithm based on content type
        double baseScore = 5.0;
        
        if ("PRODUCT_REVIEW".equals(item.getContentType())) {
            baseScore = 7.0;
        } else if ("USER_PHOTO".equals(item.getContentType())) {
            baseScore = 6.0;
        } else if ("QUESTION".equals(item.getContentType())) {
            baseScore = 4.0;
        }
        
        // Apply promotion boost
        if (item.isPromoted()) {
            baseScore *= 1.5;
        }
        
        return baseScore;
    }
    
    private Double calculateEngagementScore(FeedItem item) {
        // Scoring based on engagement count
        double engagementBoost = Math.log10(1 + item.getEngagementCount()) * 2.0;
        return calculateInitialScore(item) + engagementBoost;
    }
}

ModerationService.java:

package com.socialcommerce.moderation.service;

import com.socialcommerce.moderation.domain.ModerationDecision;
import com.socialcommerce.moderation.domain.ModerationStatus;
import com.socialcommerce.moderation.repository.ModerationRepository;
import com.socialcommerce.moderation.rules.ContentRules;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.kafka.core.KafkaTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.CompletableFuture;

@Service
@Slf4j
@RequiredArgsConstructor
public class ModerationService {
    
    private final ModerationRepository moderationRepository;
    private final ContentRules contentRules;
    private final KafkaTemplate<String, Object> kafkaTemplate;
    private final JdbcTemplate jdbcTemplate;
    private final DataSource dataSource;
    
    @Value("${app.moderation.ai-threshold:0.8}")
    private double aiConfidenceThreshold;
    
    /**
     * Setup PostgreSQL NOTIFY/LISTEN for real-time updates
     */
    public void setupNotificationTriggers() {
        // Create function for notification
        jdbcTemplate.execute(
            "CREATE OR REPLACE FUNCTION notify_moderation_update() " +
            "RETURNS trigger AS $$ " +
            "BEGIN " +
            "  PERFORM pg_notify('moderation_channel', " +
            "    json_build_object('id', NEW.id, 'status', NEW.status)::text); " +
            "  RETURN NEW; " +
            "END; " +
            "$$ LANGUAGE plpgsql;"
        );
        
        // Create trigger
        jdbcTemplate.execute(
            "DROP TRIGGER IF EXISTS moderation_update_trigger " +
            "ON moderation_decisions;"
        );
        
        jdbcTemplate.execute(
            "CREATE TRIGGER moderation_update_trigger " +
            "AFTER UPDATE ON moderation_decisions " +
            "FOR EACH ROW " +
            "WHEN (OLD.status IS DISTINCT FROM NEW.status) " +
            "EXECUTE PROCEDURE notify_moderation_update();"
        );
        
        log.info("PostgreSQL notification triggers for moderation set up successfully");
    }
    
    @Async
    public CompletableFuture<ModerationDecision> moderateContentAsync(
            String contentId, String contentType, String content) {
        
        return CompletableFuture.supplyAsync(() -> {
            try {
                return moderateContent(contentId, contentType, content);
            } catch (Exception e) {
                log.error("Error moderating content: {}", e.getMessage(), e);
                sendToDeadLetterQueue(contentId, contentType, content);
                throw new RuntimeException("Moderation failed", e);
            }
        });
    }
    
    @Transactional
    public ModerationDecision moderateContent(String contentId, String contentType, String content) {
        // Check if already moderated
        Optional<ModerationDecision> existingDecision = 
            moderationRepository.findByContentIdAndContentType(contentId, contentType);
        
        if (existingDecision.isPresent()) {
            return existingDecision.get();
        }
        
        // Apply AI moderation rules
        Map<String, Object> aiResult = contentRules.evaluateContent(content);
        
        Double aiConfidence = (Double) aiResult.getOrDefault("confidence", 0.0);
        boolean isViolation = (Boolean) aiResult.getOrDefault("isViolation", false);
        String reason = (String) aiResult.getOrDefault("reason", "");
        
        // Create decision
        ModerationDecision decision = new ModerationDecision();
        decision.setContentId(contentId);
        decision.setContentType(contentType);
        decision.setAiConfidence(aiConfidence);
        decision.setReason(reason);
        
        // Determine if human review is needed
        boolean needsReview = aiConfidence < aiConfidenceThreshold;
        decision.setReviewRequired(needsReview);
        
        // Set status based on AI evaluation
        if (needsReview) {
            decision.setStatus(ModerationStatus.NEEDS_REVIEW);
        } else {
            decision.setStatus(isViolation ? 
                ModerationStatus.REJECTED : ModerationStatus.APPROVED);
        }
        
        return moderationRepository.save(decision);
    }
    
    @Transactional
    public ModerationDecision updateModeration(Long decisionId, ModerationStatus status, 
                                               String reviewerId, String reason) {
        ModerationDecision decision = moderationRepository.findById(decisionId)
            .orElseThrow(() -> new IllegalArgumentException("Decision not found"));
        
        decision.setStatus(status);
        decision.setReviewerId(reviewerId);
        decision.setReason(reason);
        decision.setUpdatedAt(LocalDateTime.now());
        
        return moderationRepository.save(decision);
    }
    
    public List<ModerationDecision> getPendingReviews() {
        return moderationRepository.findByReviewRequiredTrueAndStatusNot(ModerationStatus.APPROVED);
    }
    
    private void sendToDeadLetterQueue(String contentId, String contentType, String content) {
        Map<String, Object> message = new HashMap<>();
        message.put("contentId", contentId);
        message.put("contentType", contentType);
        message.put("content", content);
        message.put("timestamp", System.currentTimeMillis());
        
        kafkaTemplate.send("moderation-dlq", message);
    }
    
    // Other moderation methods as needed
}

4. Controllers/API Layer

ChatController.java:

package com.socialcommerce.chat.api;

import com.socialcommerce.chat.domain.ChatMessage;
import com.socialcommerce.chat.service.ChatService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.messaging.handler.annotation.DestinationVariable;
import org.springframework.messaging.handler.annotation.MessageMapping;
import org.springframework.messaging.handler.annotation.Payload;
import org.springframework.web.bind.annotation.*;

import java.time.Instant;
import java.util.List;

@RestController
@RequestMapping("/api/chat")
@RequiredArgsConstructor
@Slf4j
public class ChatController {
    
    private final ChatService chatService;
    
    @MessageMapping("/chat/{productId}")
    public void handleChatMessage(@DestinationVariable String productId, 
                                  @Payload ChatMessage message) {
        log.info("Received message for product {}: {}", productId, message.getContent());
        message.setProductId(productId);
        chatService.saveAndSendMessage(message);
    }
    
    @GetMapping("/product/{productId}")
    public List<ChatMessage> getProductMessages(@PathVariable String productId, 
                                               @RequestParam(defaultValue = "50") int limit) {
        return chatService.getRecentMessages(productId, limit);
    }
    
    @GetMapping("/product/{productId}/since")
    public List<ChatMessage> getNewMessages(@PathVariable String productId,
                                           @RequestParam Long timestamp) {
        Instant since = Instant.ofEpochMilli(timestamp);
        return chatService.getNewMessages(productId, since);
    }
}

FeedController.java:

package com.socialcommerce.feed.api;

import com.socialcommerce.feed.domain.FeedItem;
import com.socialcommerce.feed.service.FeedService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/feed")
@RequiredArgsConstructor
public class FeedController {
    
    private final FeedService feedService;
    
    @GetMapping("/user/{userId}")
    public List<FeedItem> getUserFeed(@PathVariable String userId,
                                     @RequestParam(defaultValue = "20") int limit) {
        return feedService.getPersonalizedFeed(userId, limit);
    }
    
    @GetMapping("/promoted")
    public List<FeedItem> getPromotedContent(@RequestParam(defaultValue = "5") int limit) {
        return feedService.getPromotedContent(limit);
    }
    
    @PostMapping
    public ResponseEntity<FeedItem> createFeedItem(@RequestBody FeedItem feedItem) {
        FeedItem created = feedService.createFeedItem(feedItem);
        return ResponseEntity.ok(created);
    }
    
    @PostMapping("/{itemId}/engage")
    public ResponseEntity<Void> registerEngagement(@PathVariable Long itemId) {
        feedService.updateEngagementCount(itemId, 1);
        return ResponseEntity.ok().build();
    }
}

ModerationController.java:

package com.socialcommerce.moderation.api;

import com.socialcommerce.moderation.domain.ModerationDecision;
import com.socialcommerce.moderation.domain.ModerationStatus;
import com.socialcommerce.moderation.service.ModerationService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/moderation")
@RequiredArgsConstructor
public class ModerationController {
    
    private final ModerationService moderationService;
    
    @PreAuthorize("hasRole('MODERATOR')")
    @GetMapping("/pending")
    public List<ModerationDecision> getPendingReviews() {
        return moderationService.getPendingReviews();
    }
    
    @PreAuthorize("hasRole('MODERATOR')")
    @PutMapping("/{decisionId}")
    public ResponseEntity<ModerationDecision> updateDecision(
            @PathVariable Long decisionId,
            @RequestBody Map<String, Object> updateData) {
        
        String status = (String) updateData.get("status");
        String reviewerId = (String) updateData.get("reviewerId");
        String reason = (String) updateData.get("reason");
        
        ModerationDecision updated = moderationService.updateModeration(
            decisionId,
            ModerationStatus.valueOf(status),
            reviewerId,
            reason
        );
        
        return ResponseEntity.ok(updated);
    }
    
    @PostMapping("/check")
    public ResponseEntity<ModerationDecision> checkContent(@RequestBody Map<String, String> content) {
        String contentId = content.get("contentId");
        String contentType = content.get("contentType");
        String text = content.get("text");
        
        ModerationDecision decision = moderationService.moderateContent(contentId, contentType, text);
        return ResponseEntity.ok(decision);
    }
}

5. Configuration Files

application.properties:

# Server configuration
server.port=8080
spring.application.name=social-commerce-service

# PostgreSQL configuration
spring.datasource.url=jdbc:postgresql://localhost:5432/social_commerce
spring.datasource.username=postgres
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA/Hibernate configuration
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.show-sql=false

# Flyway migration
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true
spring.flyway.locations=classpath:db/migration

# WebSocket configuration
app.websocket.endpoint=/ws
app.websocket.allowed-origins=*

# Kafka configuration for async processing
spring.kafka.bootstrap-servers=localhost:9092
spring.kafka.producer.key-serializer=org.apache.kafka.common.serialization.StringSerializer
spring.kafka.producer.value-serializer=org.springframework.kafka.support.serializer.JsonSerializer

# Moderation settings
app.moderation.ai-threshold=0.8
app.moderation.auto-approve-users=admin,moderator

# Logging
logging.level.com.socialcommerce=INFO
logging.level.org.springframework.web=INFO
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

# Actuator endpoints for monitoring
management.endpoints.web.exposure.include=health,info,prometheus

WebSocketConfig.java:

package com.socialcommerce.chat.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.messaging.simp.config.MessageBrokerRegistry;
import org.springframework.web.socket.config.annotation.EnableWebSocketMessageBroker;
import org.springframework.web.socket.config.annotation.StompEndpointRegistry;
import org.springframework.web.socket.config.annotation.WebSocketMessageBrokerConfigurer;

@Configuration
@EnableWebSocketMessageBroker
public class WebSocketConfig implements WebSocketMessageBrokerConfigurer {

    @Value("${app.websocket.endpoint}")
    private String endpoint;
    
    @Value("${app.websocket.allowed-origins}")
    private String allowedOrigins;

    @Override
    public void registerStompEndpoints(StompEndpointRegistry registry) {
        registry.addEndpoint(endpoint)
                .setAllowedOrigins(allowedOrigins.split(","))
                .withSockJS();
    }

    @Override
    public void configureMessageBroker(MessageBrokerRegistry registry) {
        registry.enableSimpleBroker("/topic");
        registry.setApplicationDestinationPrefixes("/app");
    }
}

DatabaseConfig.java:

package com.socialcommerce.common.config;

import jakarta.annotation.PostConstruct;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@RequiredArgsConstructor
@Slf4j
public class DatabaseConfig {

    private final DataSource dataSource;
    private final JdbcTemplate jdbcTemplate;
    
    @PostConstruct
    public void initialize() {
        log.info("Initializing database connection and optimizations");
        
        // Setup PostgreSQL specific configurations
        jdbcTemplate.execute("SET max_parallel_workers_per_gather = 4");
        jdbcTemplate.execute("SET work_mem = '16MB'");
        
        // Log basic database metadata
        try {
            String version = jdbcTemplate.queryForObject(
                "SELECT version()", String.class);
            log.info("Connected to PostgreSQL: {}", version);
        } catch (Exception e) {
            log.error("Failed to query database version", e);
        }
    }
    
    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

6. Database Migration (Flyway)

V1__init_schema.sql:

-- Chat message table
CREATE TABLE chat_messages (
    id BIGSERIAL PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) NOT NULL,
    timestamp TIMESTAMP NOT NULL
);

CREATE INDEX idx_product_status_timestamp 
ON chat_messages(product_id, status, timestamp DESC);

-- Feed items table
CREATE TABLE feed_items (
    id BIGSERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    score DOUBLE PRECISION NOT NULL,
    content_type VARCHAR(30) NOT NULL,
    content JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL,
    is_promoted BOOLEAN NOT NULL DEFAULT FALSE,
    engagement_count INTEGER DEFAULT 0
);

CREATE INDEX idx_user_score 
ON feed_items(user_id, score DESC);

CREATE INDEX idx_content_type
ON feed_items(content_type);

-- Moderation decisions table
CREATE TABLE moderation_decisions (
    id BIGSERIAL PRIMARY KEY,
    content_id VARCHAR(100) NOT NULL,
    content_type VARCHAR(30) NOT NULL,
    status VARCHAR(20) NOT NULL,
    ai_confidence DOUBLE PRECISION,
    review_required BOOLEAN NOT NULL DEFAULT FALSE,
    reviewer_id VARCHAR(50),
    reason TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP
);

CREATE INDEX idx_moderation_content 
ON moderation_decisions(content_id, content_type);

CREATE INDEX idx_moderation_status
ON moderation_decisions(status);

-- Create unique constraint for content ID + type
CREATE UNIQUE INDEX idx_unique_content_moderation
ON moderation_decisions(content_id, content_type);

-- Create function for PostgreSQL notification
CREATE OR REPLACE FUNCTION notify_moderation_update()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('moderation_channel', 
    json_build_object('id', NEW.id, 'status', NEW.status)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for moderation updates
CREATE TRIGGER moderation_update_trigger
AFTER UPDATE ON moderation_decisions
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE PROCEDURE notify_moderation_update();

🔧 Setup and Deployment

Docker Compose for Local Development

version: '3.8'

services:
  postgres:
    image: postgres:14
    container_name: social-commerce-db
    environment:
      POSTGRES_DB: social_commerce
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./config/db-init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

  kafka:
    image: confluentinc/cp-kafka:7.0.0
    container_name: social-commerce-kafka
    ports:
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:29092,PLAINTEXT_HOST://localhost:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
      KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
      KAFKA_AUTO_CREATE_TOPICS_ENABLE: true
    depends_on:
      - zookeeper

  zookeeper:
    image: confluentinc/cp-zookeeper:7.0.0
    container_name: social-commerce-zookeeper
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181

  social-commerce-service:
    build: .
    container_name: social-commerce-app
    environment:
      SPRING_DATASOURCE_URL: jdbc:postgresql://postgres:5432/social_commerce
      SPRING_DATASOURCE_USERNAME: postgres
      SPRING_DATASOURCE_PASSWORD: password
      SPRING_KAFKA_BOOTSTRAP_SERVERS: kafka:29092
    ports:
      - "8080:8080"
    depends_on:
      postgres:
        condition: service_healthy
      kafka:
        condition: service_started

volumes:
  postgres-data:

Dockerfile

FROM eclipse-temurin:17-jdk-alpine as build
WORKDIR /workspace/app

COPY mvnw .
COPY .mvn .mvn
COPY pom.xml .
COPY src src

RUN ./mvnw install -DskipTests
RUN mkdir -p target/dependency && (cd target/dependency; jar -xf ../social-commerce-service-*.jar)

FROM eclipse-temurin:17-jdk-alpine
VOLUME /tmp
ARG DEPENDENCY=/workspace/app/target/dependency
COPY --from=build ${DEPENDENCY}/BOOT-INF/lib /app/lib
COPY --from=build ${DEPENDENCY}/META-INF /app/META-INF
COPY --from=build ${DEPENDENCY}/BOOT-INF/classes /app
ENTRYPOINT ["java","-cp","app:app/lib/*","com.socialcommerce.SocialCommerceApplication"]

pom.xml (Parent)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.4</version>
        <relativePath/>
    </parent>

    <groupId>com.socialcommerce</groupId>
    <artifactId>social-commerce-service</artifactId>
    <version>0.1.0-SNAPSHOT</version>
    <name>Social Commerce Service</name>
    <description>PostgreSQL-based platform for real-time product chats, community feeds, and AI moderation</description>

    <properties>
        <java.version>17</java.version>
        <spring-boot.version>3.1.4</spring-boot.version>
        <postgresql.version>42.6.0</postgresql.version>
        <lombok.version>1.18.30</lombok.version>
        <testcontainers.version>1.18.3</testcontainers.version>
        <flyway.version>9.21.1</flyway.version>
        <kafka.version>3.4.0</kafka.version>
    </properties>

    <dependencies>
        <!-- Spring Boot Starters -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-websocket</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        
        <!-- PostgreSQL Driver -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
        </dependency>
        
        <!-- Flyway for database migrations -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>${flyway.version}</version>
        </dependency>
        
        <!-- Kafka for messaging -->
        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>
        </dependency>
        
        <!-- JWT for authentication -->
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt-api</artifactId>
            <version>0.11.5</version>
        </dependency>
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt-impl</artifactId>
            <version>0.11.5</version>
            <scope>runtime</scope>
        </dependency>
        
        <!-- Utilities -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.retry</groupId>
            <artifactId>spring-retry</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        
        <!-- Testing -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>kafka</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>${flyway.version}</version>
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/social_commerce</url>
                    <user>postgres</user>
                    <password>password</password>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

📈 Monitoring and Performance

Monitoring with Prometheus and Grafana

Key metrics to track:

Database connection pool stats
Query execution times
Message processing rates
Moderation decision times
WebSocket connection count

Performance Optimization Tips

Use proper PostgreSQL indexes for frequently queried fields
Implement query caching for feed items
Use connection pooling (HikariCP) with optimal settings
Implement database sharding for large-scale deployments
Leverage PostgreSQL's JSONB for flexible schema needs
Use asynchronous processing for moderation requests

📜 License

Apache 2.0

📮 Support

For issues, contact:

Email: [email protected]
Slack: #social-commerce

▶️ Quick Start:

git clone https://github.com/your-repo/social-commerce-postgresql.git
cd social-commerce-postgresql
docker-compose up -d postgres kafka  # Start dependencies
mvn flyway:migrate  # Run database migrations
mvn spring-boot:run  # Start the application

5. Social Commerce Service (SCS) ‐ PostgreSQL Edition - Wiz-DevTech/prettygirllz GitHub Wiki

Social Commerce Service (SCS) - PostgreSQL Edition

🚀 Features

📦 Project Structure

⚙️ Tech Stack

📄 Key Files Implementation

1. Core Domain Models

2. Repository Layer

3. Service Layer

4. Controllers/API Layer

5. Configuration Files

6. Database Migration (Flyway)

🔧 Setup and Deployment

Docker Compose for Local Development

Dockerfile

pom.xml (Parent)

📈 Monitoring and Performance

Monitoring with Prometheus and Grafana

Performance Optimization Tips

📜 License

📮 Support

⚠️ GitHub.com Fallback ⚠️

5. Social Commerce Service (SCS) ‐ PostgreSQL Edition - Wiz-DevTech/prettygirllz GitHub Wiki

Social Commerce Service (SCS) - PostgreSQL Edition

🚀 Features

📦 Project Structure

⚙️ Tech Stack

📄 Key Files Implementation

1. Core Domain Models

2. Repository Layer

3. Service Layer

4. Controllers/API Layer

5. Configuration Files

6. Database Migration (Flyway)

🔧 Setup and Deployment

Docker Compose for Local Development

Dockerfile

pom.xml (Parent)

📈 Monitoring and Performance

Monitoring with Prometheus and Grafana

Performance Optimization Tips

📜 License

📮 Support

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️