Windows Service for PostgreSQL Data Sync

? Goal
Develop the central component of the middleware application consisting of:
1. A Windows Service that handles automatic synchronization tasks.
2. A centralized PostgreSQL data access layer for storing products, orders, logs, etc.
3. A modern graphical user interface (GUI) for managing product and order data manually.
? Architectural Overview
- Windows Service: executes sync tasks in the background
- Database Handler: unified access class for PostgreSQL operations
- GUI: management interface for products, orders, shipping, and logs
- Multithreaded execution: each marketplace runs independently
- Error handling: resilient logging, no process interruption
- Schedule control: configurable sync interval per marketplace
⚙️ Technology & Requirements
- .NET 9 (net9.0)
- C# 13
- Windows Service based on BackgroundService / Worker Service
- GUI built with WPF (preferred) using MVVM
- PostgreSQL 15+ as central database
- Data access via Npgsql + Dapper or Entity Framework Core
- Structured database logging
- Secure credential handling
? Windows Service Responsibilities
- Starts/stops all marketplace connectors in isolated threads
- Loads configuration from PostgreSQL
- Executes SyncProducts, SyncOrders, SyncShippingStatus at defined intervals
- Resilient to exceptions (error logging without blocking main process)
- Triggers automatic log cleanup per type
Example:
public class SyncService : BackgroundService
{
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
while (!stoppingToken.IsCancellationRequested)
{
foreach (var connector in _connectors)
_ = Task.Run(() => RunConnector(connector));
await Task.Delay(TimeSpan.FromMinutes(1), stoppingToken);
}
}
}
? Database Handler Responsibilities
- Centralizes all PostgreSQL access logic
- Used by Windows Service and GUI components
- Example methods:
- UpsertProduct(ProductDto product)
- SaveOrder(OrderDto order)
- UpdateShipping(string orderId, string tracking, string carrier)
- GetProducts(FilterOptions filters)
- GetOrders(DateRange range)
- LogSync(LogEntry log)
- CleanupLogs(string logType, int retentionDays)
?️ GUI: Product & Order Management
? Product Management:
- Display and filter product list
- Edit product data (title, price, stock, etc.)
- Manage marketplace-specific fields
- Import from CSV/XML with validation and preview
- Trigger manual sync for selected products
? Order Management:
- View and filter incoming orders
- Order detail view (customer, items, total)
- Set shipping data and status
- Send shipping confirmation to marketplace
- Export order list (CSV)
? Log Management:
- View logs per sync type
- Auto-cleanup logic per category
- Filter by severity (error/warning/info)
- Detailed log entry display
? Deliverables
- WindowsService.cs: Core background execution engine
- DatabaseHandler.cs: All PostgreSQL access logic
- Models/: DTOs for Product, Order, Shipping, Log
- GUI/MainWindow.xaml: Main WPF window with tabs
- GUI/Controls/ProductView.xaml: Product data grid view
- GUI/Controls/OrderView.xaml: Order list view
- GUI/Controls/LogView.xaml: Log entry browser
- Import/ImportHandler.cs: CSV/XML import logic
- README.md: Setup instructions
✅ Summary
- Core component of the middleware application
- Automates all data exchange and sync flows
- Stores and manages data in a PostgreSQL backend
- Provides a modern WPF-based GUI for real-time control and corrections
- Built on modern .NET architecture with multithreading and service isolation