Agregator

Agregator is a Ruby on Rails application for collecting, normalizing and aggregating product stock data from supplier Excel price lists.

The application retrieves .xls and .xlsx files from supplier email attachments via Yandex IMAP or accepts files uploaded manually by a user. Each supplier can have an individual import template defining the worksheet, source columns and data transformation rules.

After processing, users can preview imported data, review aggregated stock balances and export the resulting report to Excel.

Features

• Automatic retrieval of supplier price lists from email attachments via Yandex IMAP
• Manual upload of .xls and .xlsx files
• Configurable import templates for different suppliers and warehouses
• Worksheet selection for multi-sheet Excel files
• Configurable mappings for:
  • SKU
  • product name
  • brand
  • available quantity
• SKU normalization
• Conversion of textual stock values into numeric quantities using configurable rules
• Import preview before data is finalized
• Aggregation of stock balances from multiple suppliers and warehouses
• Export of aggregated results to Excel
• Scheduled background imports

Import Workflow

Supplier email attachment or manual upload
                    │
                    ▼
            Excel file validation
                    │
                    ▼
       Supplier import template selection
                    │
                    ▼
       Worksheet and column extraction
                    │
                    ▼
        SKU and quantity normalization
                    │
                    ▼
              Import preview
                    │
                    ▼
          Aggregated stock balances
                    │
                    ▼
               Excel export

Import Templates

Supplier files often have different structures. Agregator handles these differences through configurable templates.

A template can define:

• which worksheet should be processed;
• which columns contain SKU, product name, brand and quantity;
• how SKU values should be normalized;
• how textual stock values should be converted into numeric quantities;
• how files from different suppliers or warehouses should be identified.

This allows new supplier formats to be supported without changing the core import workflow.

Technology Stack

Backend

• Ruby
• Ruby on Rails 8
• PostgreSQL
• Active Record

Background Processing

• Sidekiq
• Sidekiq Cron

Excel Processing

• Roo
• Roo XLS
• Caxlsx

Frontend

• Hotwire
• Turbo
• Stimulus
• Tailwind CSS

Integrations

• Yandex IMAP
• Microsoft SQL Server

Infrastructure

• Docker
• Puma
• Kamal

Running Locally

Requirements

• Ruby
• PostgreSQL
• Redis
• Microsoft SQL Server access, when the corresponding integration is used

Clone the repository:

git clone https://github.com/Unrealimp/agregator.git
cd agregator

Install dependencies:

bundle install

Create an environment file:

cp .env.example .env

Configure the required environment variables in .env, then prepare the database:

bin/rails db:prepare

Start the application:

bin/dev

Run Sidekiq separately when background processing is required:

bundle exec sidekiq

Security

Credentials and connection settings are not stored in the repository.

Sensitive configuration should be provided through environment variables, including:

• database credentials;
• Redis connection settings;
• IMAP credentials;
• external database connection settings.

The .env file must remain excluded from version control.

Testing

Run the test suite with:

bin/rails test

Run system tests with:

bin/rails test:system

Project Status

The application is under active development.

Current functionality includes supplier-specific Excel imports, data normalization, import previews, stock aggregation and Excel export.

Future Improvements

• Increase automated test coverage
• Add import error reports with row-level diagnostics
• Add import history and audit logs
• Add monitoring for scheduled imports
• Add CI with GitHub Actions
• Improve duplicate product matching
• Add import statistics and processing metrics

License

This project is provided as a portfolio project. See the repository license for usage terms.