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

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

📦 Module Structure

social-commerce-service/
├── product-chat/               # Real-time messaging
│   ├── src/main/java/com/example/chat/
│   │   ├── config/            # WebSocket config
│   │   ├── entity/            # JPA entities
│   │   ├── repository/        # Spring Data JPA
│   │   └── service/           # Chat logic
├── community-feed/            # Content feed engine
│   ├── src/main/java/com/example/feed/
│   │   ├── entity/            # Feed entities
│   │   └── service/           # Recommendation logic
├── content-moderation/        # Moderation system
│   ├── src/main/java/com/example/moderation/
│   │   ├── entity/            # Moderation records
│   │   └── service/           # AI/human workflow
└── integration-tests/         # Test containers

⚙️ Tech Stack

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

🔧 Setup

1. Prerequisites

  • PostgreSQL 14+ (Local or Cloud)
  • Java 17 JDK
  • Maven 3.9+

2. Configuration

# application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/social_commerce
spring.datasource.username=postgres
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.flyway.enabled=true

# WebSocket endpoint
app.websocket.endpoint=/live-chat

3. Run the Service

mvn spring-boot:run -pl product-chat  # Start chat service
mvn spring-boot:run -pl community-feed  # Start feed service

🧪 Testing

1. Unit Tests

mvn test  # Runs all unit tests

2. Integration Tests

Test Containers (PostgreSQL):

@Testcontainers
class ChatRepositoryIT {
    @Container 
    static PostgreSQLContainer postgres = new PostgreSQLContainer("postgres:14")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");
    
    @DynamicPropertySource
    static void registerProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }
    
    @Test
    void whenMessageSaved_thenCanRetrieve() {
        ChatMessage msg = new ChatMessage("prod123", "user1", "Hello!");
        repository.save(msg);
        assertThat(repository.findByProductId("prod123")).hasSize(1);
    }
}

3. API Testing

WebSocket Chat:

# Connect using STOMP client
stomp-cli connect ws://localhost:8080/live-chat
> SEND destination:/chat/prod123 content-type:application/json {"text":"Test message"}

Feed API:

curl -X GET "http://localhost:8080/feed/user123?limit=10"

📊 PostgreSQL Optimization

Entity Models

// Product Chat Entity
@Entity
@Table(name = "chat_messages", indexes = {
    @Index(name = "idx_product_status_timestamp", 
           columnList = "product_id, status, timestamp DESC")
})
@Data
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)
    private String text;
    
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private MessageStatus status;
    
    @Column(nullable = false)
    private LocalDateTime timestamp;
    
    // Constructors, getters, setters
}

// Feed Entity
@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;
    
    // Constructors, getters, setters
}

Database Migrations

// Using Flyway for migrations
// V1__init_schema.sql
CREATE TABLE chat_messages (
    id BIGSERIAL PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    text 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);

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
);

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

Real-time Notifications

// Using PostgreSQL LISTEN/NOTIFY for real-time updates
@Service
public class PostgresNotificationListener {
    private final DataSource dataSource;
    private final WebSocketService websocketService;
    
    @PostConstruct
    public void startListening() {
        CompletableFuture.runAsync(() -> {
            try (Connection conn = dataSource.getConnection()) {
                PGConnection pgConn = conn.unwrap(PGConnection.class);
                Statement stmt = conn.createStatement();
                
                // Create notification trigger if not exists
                stmt.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;");
                
                stmt.execute("DROP TRIGGER IF EXISTS moderation_update_trigger " +
                             "ON moderation_decisions;");
                
                stmt.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();");
                
                // Listen for notifications
                pgConn.addNotificationListener(notification -> {
                    try {
                        JsonNode payload = new ObjectMapper().readTree(notification.getParameter());
                        if ("APPROVED".equals(payload.get("status").asText())) {
                            websocketService.notifyUser(payload.get("id").asText());
                        }
                    } catch (Exception e) {
                        log.error("Error processing notification", e);
                    }
                });
                
                stmt.execute("LISTEN moderation_channel");
                
                // Keep connection open to receive notifications
                while (!Thread.currentThread().isInterrupted()) {
                    Thread.sleep(500);
                }
            } catch (Exception e) {
                log.error("Error in notification listener", e);
                // Implement reconnection logic
            }
        });
    }
}

🚨 Error Handling

SQL Retry Logic:

@Service
public class ChatService {
    @Retryable(value = DataAccessException.class, maxAttempts = 3)
    @Transactional
    public void saveMessage(ChatMessage message) {
        chatRepository.save(message);
    }
}

Dead Letter Queue:

@KafkaListener(topics = "moderation-dlq")
public void handleFailedContent(ModerationRequest request) {
    log.error("Failed to moderate: {}", request);
    humanReviewQueue.add(request);
}

📈 Monitoring

Key Metrics:

  • chat.messages.sent (Counter)
  • feed.recommendation.latency (Timer)
  • moderation.decision.time (Gauge)
  • db.connection.pool.active (Gauge)
  • db.query.time (Timer)

Sample Dashboard:

# Prometheus config
- job_name: 'social-commerce'
  metrics_path: '/actuator/prometheus'

🔄 Maven Dependencies

<!-- pom.xml -->
<dependencies>
    <!-- Spring Boot Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- PostgreSQL Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>
    
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    
    <!-- WebSockets -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-websocket</artifactId>
    </dependency>
    
    <!-- Flyway for migrations -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    
    <!-- Lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    
    <!-- Test dependencies -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

📜 License

Apache 2.0

📮 Support

For issues, contact:

▶️ Quick Start:

git clone https://github.com/your-repo/social-commerce-postgresql.git
cd social-commerce-postgresql
mvn test  # Verify setup
mvn spring-boot:run -pl product-chat
⚠️ **GitHub.com Fallback** ⚠️