Tex.PoolSyncer.Worker

A .NET 8.0 background worker service that performs ETL (Extract, Transform, Load) operations between PostgreSQL databases, specifically designed to sync pool data from a source database to a destination database with token pair processing.

🚀 Features

• Automated ETL Process: Extracts pool data from source database and transforms it into token pairs for the destination database
• Cron Job Scheduling: Configurable scheduling using cron expressions or simple intervals
• Database Initialization: Automatically creates required tables if they don't exist
• Error Handling: Comprehensive error logging with database persistence for failed operations
• Transaction Support: Uses database transactions to ensure data consistency
• Docker Support: Ready-to-deploy Docker configuration included

🏗️ Architecture

The application follows a clean architecture pattern with:

• Worker Service: Background service that orchestrates the ETL process
• Repository Pattern: Separate repositories for source and destination databases
• Service Layer: Business logic encapsulated in dedicated services
• Dependency Injection: Full DI container configuration
• Hosted Services: Automatic database initialization and worker scheduling

📋 Prerequisites

• .NET 8.0 SDK
• PostgreSQL databases (source and destination)
• Docker (optional, for containerized deployment)

⚙️ Configuration

Connection Strings

Configure your database connections in appsettings.json:

{
  "ConnectionStrings": {
    "Source": "Host=your-source-host;Port=5432;Database=syncer;Username=postgres;Password=your-password;Include Error Detail=true",
    "Destination": "Host=your-dest-host;Port=5432;Database=trading_bot;Username=postgres;Password=your-password;Include Error Detail=true"
  }
}

Scheduling Options

{
  "CronExpression": "*/2 * * * *",
  "SchedulingOptions": {
    "UseCronScheduler": false,
    "SimpleIntervalMinutes": 5
  }
}

• CronExpression: Standard cron expression for scheduling (e.g., */2 * * * * runs every 2 minutes)
• UseCronScheduler: Set to true to use cron expression, false for simple interval
• SimpleIntervalMinutes: Interval in minutes when not using cron scheduler

🗄️ Database Schema

Source Database Tables

• pool_v2: Contains pool information with assets, reserves, and liquidity data

Destination Database Tables

The application automatically creates these tables if they don't exist:

• Token: Stores token information with policy ID and names
• TokenPair: Links tokens together with pool associations
• SyncHistory: Logs all sync operations and errors

🚀 Getting Started

Local Development

1. Clone the repository
2. Update appsettings.json with your database connection strings
3. Run the application:

cd Tex.PoolSyncer.Worker
dotnet restore
dotnet run

Docker Deployment

1. Build the Docker image:

docker build -t tex-poolsyncer .

2. Run the container:

docker run -d --name poolsyncer-worker \
  -e ConnectionStrings__Source="Host=source-host;Port=5432;Database=syncer;Username=postgres;Password=password" \
  -e ConnectionStrings__Destination="Host=dest-host;Port=5432;Database=trading_bot;Username=postgres;Password=password" \
  -e CronExpression="*/5 * * * *" \
  tex-poolsyncer

Docker Compose

version: '3.8'
services:
  poolsyncer:
    build: .
    container_name: poolsyncer-worker
    environment:
      - ConnectionStrings__Source=Host=source-db;Port=5432;Database=syncer;Username=postgres;Password=password
      - ConnectionStrings__Destination=Host=dest-db;Port=5432;Database=trading_bot;Username=postgres;Password=password
      - CronExpression=*/2 * * * *
      - DOTNET_ENVIRONMENT=Production
    restart: unless-stopped

🔧 Key Components

TokenSyncerService

The main ETL service that:

• Extracts pool data from the source database
• Transforms pool data into token pairs
• Loads data into the destination database with proper error handling

DatabaseInitializer

Automatically creates required database tables on startup:

• Checks for table existence
• Creates tables with proper schema if missing
• Handles database initialization errors

Worker

The main background service that:

• Manages the scheduling of ETL operations
• Coordinates between different services
• Handles application lifecycle

📊 Monitoring and Logging

The application provides comprehensive logging:

• Information: ETL process start/completion, record counts
• Error: Failed operations with full exception details
• Database Logging: All exceptions are stored in the SyncHistory table

View logs in real-time:

# Docker logs
docker logs -f poolsyncer-worker

# Local development
# Logs appear in console output

🛠️ Dependencies

• Microsoft.Extensions.Hosting (8.0.0): Background service hosting
• Dapper (2.1.35): Lightweight ORM for database operations
• Npgsql (8.0.3): PostgreSQL .NET data provider
• Cronos (0.7.1): Cron expression parsing and scheduling

🔒 Security Considerations

• Store sensitive connection strings in environment variables or secure configuration
• Use connection string encryption for production deployments
• Implement proper database user permissions
• Regular security updates for dependencies

🆘 Troubleshooting

Common Issues

1. Database Connection Errors

   • Verify connection strings are correct
   • Ensure database servers are accessible
   • Check firewall settings

2. Table Creation Failures

   • Verify database user has CREATE TABLE permissions
   • Check for existing tables with conflicting names

3. Scheduling Issues

   • Validate cron expression syntax
   • Check system time zone settings

Error Logs

All errors are logged both to the console and to the SyncHistory table in the destination database. Check the exception column in SyncHistory for detailed error information.

---

For additional support or questions, please create an issue in the repository.