INFX Cache API

This project is a Laravel API for importing, caching, and searching INFX offer data. It downloads the daily INFX files from Phoenix SFTP, parses them with the milzer/infx package, stores offers in PostgreSQL, and serves search/facet endpoints from the currently active offer database.

The application is designed for large offer imports. It requires two PostgreSQL databases, Redis, and Laravel Horizon so imports can run in the background without blocking API traffic.

Architecture

The project uses a blue-green database pattern:

• pgsql_first and pgsql_second are two separate PostgreSQL databases with the same schema.
• One database is active and serves the public API.
• The other database is inactive and receives the next INFX import.
• After all high-priority import jobs finish, the inactive database is marked active.
• The old active database becomes inactive and its offers are truncated so it is ready for the next import.

This keeps the API available while a new offer set is being imported and avoids serving partially imported data.

Main runtime components:

• Laravel 12 API application.
• PostgreSQL with two databases for active/inactive offer storage.
• Redis for Horizon, queue workers, and efficient import processing.
• Laravel Horizon to run the default and high queues.
• Scheduler to start the daily INFX import.
• Phoenix SFTP access for the compressed INFX files.
• The system gzip command for decompression.

Dependencies

Required server dependencies:

• PHP 8.3 or compatible PHP 8.2+ runtime with common Laravel extensions.
• Composer.
• Node.js and npm if frontend assets need to be built.
• PostgreSQL.
• Redis.
• gzip available on the application server.
• Supervisor, systemd, or another process manager for Horizon and the scheduler.

Infrastructure Requirements

This project imports and searches a large number of INFX offers. For good import speed and API performance, the production environment must be sized correctly.

Required infrastructure:

• Two PostgreSQL databases. This is mandatory because the project imports into an inactive database while the active database continues serving API traffic.
• Redis. This is mandatory for Laravel Horizon, queued import jobs, cache, sessions, queue locks, and database status caching.
• More than 150 GB of available disk space. The server must have enough storage for PostgreSQL data, indexes, downloaded compressed INFX files, decompressed raw files, Laravel logs, and temporary import data.
• Good CPU, memory, and disk I/O. The import runs many queue jobs, parses large files, writes many database rows, and builds/uses PostgreSQL indexes. Better hardware directly improves import time and search performance.
• A reliable network connection to the Phoenix SFTP server.

Recommended production setup:

• Keep PostgreSQL on fast SSD/NVMe storage.
• Allocate enough RAM for PostgreSQL, Redis, PHP-FPM, and Horizon workers.
• Tune HORIZON_MAX_PROCESSES_ON_QUEUE_HIGH based on the available CPU, memory, Redis capacity, and PostgreSQL write performance.
• Monitor disk usage, queue size, failed jobs, PostgreSQL load, and Redis memory.

Deployment Checklist

Follow these steps for a production deployment:

1. Provision the server with enough CPU, memory, and fast disk storage. More than 150 GB of disk space is required, and better hardware improves import and API performance.
2. Install PHP, Composer, Node.js/npm, PostgreSQL client tools, Redis, gzip, and a process manager such as Supervisor or systemd.
3. Create two PostgreSQL databases, for example infx_first and infx_second.
4. Create PostgreSQL users and grant them access to both databases.
5. Install and start Redis.
6. Clone or deploy the project code.
7. Create .env from .env.example.
8. Configure .env carefully with the real app URL, app key, two PostgreSQL database connections, Redis settings, queue/cache/session drivers, Horizon prefixes, and Phoenix SFTP credentials.
9. Install production PHP dependencies:

composer install --no-dev --optimize-autoloader

10. Install and build assets if the deployment includes frontend assets:

npm ci
npm run build

11. Run migrations:

php artisan migrate --force

12. Cache Laravel configuration:

php artisan config:cache
php artisan route:cache
php artisan view:cache

13. Configure the web server and PHP-FPM to serve the Laravel public directory.
14. Configure Horizon under Supervisor or systemd:

php artisan horizon

15. Configure the Laravel scheduler to run every minute:

* * * * * cd /path/to/project && php artisan schedule:run >> /dev/null 2>&1

16. Confirm the server can connect to PostgreSQL, Redis, and Phoenix SFTP.
17. Confirm Horizon is running and processing the default and high queues.
18. Start the first import manually or wait for the scheduled import time.

For later code deployments, use the standard Laravel flow:

php artisan down
git pull
composer install --no-dev --optimize-autoloader
npm ci
npm run build
php artisan migrate --force
php artisan config:cache
php artisan route:cache
php artisan view:cache
php artisan horizon:terminate
php artisan up

horizon:terminate lets the process manager restart Horizon with the new code.

Environment Configuration

Configure .env carefully before running the application. This is a Laravel project, so the environment file controls the application URL, app key, database connections, Redis, queues, cache, sessions, mail, and Phoenix SFTP credentials.

Create the environment file from the example:

cp .env.example .env
php artisan key:generate

Then update .env for the target server. The application must be configured with two PostgreSQL databases, and both databases must exist before migrations run.

Important configuration rules:

• Set APP_ENV=production and APP_DEBUG=false in production.
• Set APP_URL to the real application URL.
• Set DB_CONNECTION=pgsql_first.
• Set both database names: DB_DATABASE_FIRST and DB_DATABASE_SECOND.
• Set both database users/passwords: DB_USERNAME_FISRT, DB_PASSWORD_FIRST, DB_USERNAME_SECOND, and DB_PASSWORD_SECOND.
• Set SESSION_DRIVER=redis, CACHE_STORE=redis, and QUEUE_CONNECTION=redis.
• Configure Redis host, port, password, and prefixes.
• Configure Phoenix SFTP host, username, password, and port.
• Set IMPORT_INFX_AT to the daily import time.
• Set HORIZON_MAX_PROCESSES_ON_QUEUE_HIGH according to the server hardware.

Important variables:

APP_NAME=Laravel
APP_ENV=production
APP_DEBUG=false
APP_URL=https://your-api-domain.example

DB_CONNECTION=pgsql_first
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE_FIRST=infx_first
DB_DATABASE_SECOND=infx_second
DB_USERNAME_FISRT=infx_user
DB_PASSWORD_FIRST=change_me
DB_USERNAME_SECOND=infx_user
DB_PASSWORD_SECOND=change_me

QUEUE_CONNECTION=redis
CACHE_STORE=redis
SESSION_DRIVER=redis
REDIS_PREFIX="infx_cache_api_ps_example"
HORIZON_PREFIX="infx_cache_api_ps_horizon:"
CACHE_PREFIX="infx_cache_api_ps_example"

REDIS_CLIENT=phpredis
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

PHOENIX_SFTP_HOST='sftp.example.com'
PHOENIX_SFTP_USERNAME='infx_import'
PHOENIX_SFTP_PASSWORD='change_me'
PHOENIX_SFTP_PORT=22

IMPORT_INFX_AT='03:00'
HORIZON_MAX_PROCESSES_ON_QUEUE_HIGH=40

Note: the current database config reads DB_USERNAME_FISRT for the first PostgreSQL username. Use that exact variable name.

Installation

Install PHP dependencies:

composer install --no-dev --optimize-autoloader

Install and build frontend assets if needed:

npm ci
npm run build

Run migrations:

php artisan migrate --force

The migrations create the same application tables in both PostgreSQL databases, including offers, offer details, lookup tables, jobs, cache, sessions, and database status records.

Optimize Laravel for production:

php artisan config:cache
php artisan route:cache
php artisan view:cache

Required Production Processes

The deployment must run these processes continuously:

• Web server/PHP-FPM for HTTP API requests.
• Redis server.
• Laravel Horizon with Redis queues.
• Laravel scheduler.

Run Horizon under a process manager:

php artisan horizon

The configured Horizon queues are:

• default for coordination jobs such as finalizing the import.
• high for INFX file shard processing.

In production, high can run up to HORIZON_MAX_PROCESSES_ON_QUEUE_HIGH workers. This is important for importing offers efficiently.

Run the scheduler every minute:

* * * * * cd /path/to/project && php artisan schedule:run >> /dev/null 2>&1

The scheduler starts the INFX import once every day. The exact daily import time is configurable with IMPORT_INFX_AT in .env.

API Endpoints

The API exposes offer search and facet endpoints:

• GET /api/offers
• GET /api/offers/facet
• GET /api/offers/object-facet/{code}

Common query options:

• per_page
• page
• sort=duration, sort=price, or sort=start_date
• filter[start_date]
• filter[start_date_after]
• filter[start_date_before]
• filter[start_date_ranges]
• filter[duration]
• filter[duration_lt]
• filter[duration_gt]
• filter[departure_airport_outbound]
• filter[arrival_airport_outbound]
• filter[board_codes]
• filter[object_codes]
• filter[room_short_code]
• filter[adults]
• filter[children_birthdates]
• filter[group_object_codes]
• filter[group_start_dates]

Example:

/api/offers?filter[start_date_after]=2026-07-01&filter[duration]=7&filter[adults]=2&sort=price

INFX Import Process

The import can be started manually:

php artisan download-compressed-files

It is also scheduled to run once every day at the configurable IMPORT_INFX_AT time:

Schedule::command(DownloadCompressedFilesCommand::class)
    ->dailyAt(config('console.import_infx_at'));

Workflow:

1. The command connects to the Phoenix SFTP disk.
2. It expects exactly three compressed INFX files on the remote disk.
3. Files are copied to local storage under storage/app/private/infx/{date}/compressed.
4. Each compressed file is decompressed with gzip -dc.
5. Raw files are written under storage/app/private/infx/{date}/raw.
6. The raw files are read line by line.
7. Every 1,000 lines are dispatched as a ProcessFileShardJob on the high queue.
8. Each shard parses INFX lines using Milzer\Infx\Entities\Line::parseInfx.
9. Parsed records are normalized into airports, hotels, boards, rooms, offers, and offer details.
10. Data is inserted into the inactive PostgreSQL database.
11. A finalize job waits until the high queue is empty.
12. The inactive database becomes active.
13. The old active database becomes inactive and its offers table is truncated for the next import.

Required Import Inputs

The import expects:

• Phoenix SFTP connection details.
• Exactly three files available on the configured Phoenix SFTP disk.
• Files compressed with gzip.
• Raw content that can be parsed as Phoenix-layout INFX lines by milzer/infx.
• One offer per non-empty line.

The parser extracts fields such as:

• start date
• duration
• departure and arrival airports
• hotel code, hotel name, category, and destination
• board code
• room code and room description
• occupancy and adult occupancy limits
• max child age
• currency and price
• flight and travel details stored in offer_details

Empty lines are skipped.

Local Development

For development, install dependencies and run:

composer install
npm install
php artisan key:generate
php artisan migrate
composer run dev

For import testing, Redis and Horizon should be running:

php artisan horizon
php artisan download-compressed-files

Testing

Run the test suite:

php artisan test --compact

Run code style:

vendor/bin/pint