Occupancy Export System Documentation

This system exports restaurant seat availability data from Redis inventory cache to CSV files for analytics and data processing.

Overview

The system extracts seat availability data for all active restaurants from Redis keys following this pattern:

InvChecker__{restaurant_id}:dine_in:VERSION_X:by_restaurant_package_{package_id}:seat_lefts:{date}:by_hmset

Files Created

1. OccupancyExportWorker (app/workers/schedule_workers/occupancy_export_worker.rb)

Main Sidekiq job that:

• Iterates through all active, non-expired restaurants
• Scans Redis for seat availability data
• Exports data to CSV format
• Includes proper logging and error handling

2. Trigger Script (bin/inventory_system/trigger_occupancy_export.rb)

Manual trigger script for testing and one-off exports

3. Verification Script (bin/inventory_system/verify_occupancy_export.rb)

Validates and inspects generated CSV files

Data Schema

The exported CSV file contains the following columns:

Usage Instructions

1. Running the Export Job

Automatic (via Sidekiq Scheduler): The job runs automatically every hour via sidekiq-scheduler.

Manual (via Sidekiq):

# Enqueue job with current timestamp
ScheduleWorkers::OccupancyExportWorker.perform_async

# Enqueue job with custom timestamp
ScheduleWorkers::OccupancyExportWorker.perform_async(1698547200)

Manual Script:

# Use current timestamp
./bin/exec_prod.sh bundle exec rails runner bin/inventory_system/trigger_occupancy_export.rb

# Use custom timestamp  
./bin/exec_prod.sh bundle exec rails runner bin/inventory_system/trigger_occupancy_export.rb 1698547200

2. Monitoring Job Progress

# Watch logs for progress
tail -f log/development.log | grep occupancy_export

# Check Sidekiq web UI for job status
# Visit /sidekiq in your application

3. Verifying Output

# Verify the generated file
./bin/exec_prod.sh bundle exec rails runner bin/inventory_system/verify_occupancy_export.rb [timestamp]

# Check file manually
ls -la tmp/occupancy_*.csv

4. Reading CSV Files

Ruby (using CSV library):

require 'csv'

# Read row by row
CSV.foreach("tmp/occupancy_1698547200.csv", headers: true) do |row|
  puts "Restaurant #{row['restaurant_id']}: #{row['seat_left']} seats at #{row['start_time']} on #{row['date']}"
end

# Read all data into memory
data = CSV.read("tmp/occupancy_1698547200.csv", headers: true)
data.each { |row| puts row['restaurant_id'] }

Python (using pandas):

import pandas as pd

df = pd.read_csv("tmp/occupancy_1698547200.csv")
print(df.head())
print(df.groupby('restaurant_id')['seat_left'].sum())

SQL (using DuckDB):

SELECT restaurant_id, date, SUM(seat_left) as total_capacity
FROM 'tmp/occupancy_1698547200.csv' 
WHERE seat_left > 0
GROUP BY restaurant_id, date
ORDER BY total_capacity DESC;

Excel/Google Sheets:

• Simply open the CSV file directly
• Use pivot tables for data analysis
• Filter and sort as needed

Configuration

Redis Connection

The worker uses the global $inv_redis connection pool that is already configured in the application. This provides:

• Connection pooling for better performance
• Automatic reconnection handling
• Consistent configuration with other inventory operations

Job Configuration

The worker is configured with:

• Queue: :default (can be changed to :longprocess for large datasets)
• Uniqueness: Prevents concurrent exports with same timestamp
• Batch Size: 100 restaurants per batch
• Format: CSV with headers for maximum compatibility
• Retry Logic: 3 attempts with exponential backoff for Redis operations

Performance Considerations

Memory Usage

• Data is processed in batches to manage memory consumption
• Large restaurants (1000+ packages) may generate significant data
• Consider increasing worker memory limits for production

Execution Time

• Typical runtime: 2-5 minutes for 100 restaurants
• Depends on Redis latency and data volume
• Use :longprocess queue for very large exports

File Sizes

• Typical output: 5-100MB per export depending on restaurant count
• CSV format is larger than binary formats but universally compatible
• Text-based format allows easy inspection and debugging

Error Handling

The job includes comprehensive error handling:

1. Redis Connection Issues: Retry with exponential backoff
2. Individual Restaurant Errors: Log and continue with other restaurants
3. File Writing Errors: Fail fast with detailed logging
4. Memory Issues: Batch processing prevents OOM errors

Monitoring Failures

Check logs for these patterns:

# Redis connectivity issues
grep "Redis error for restaurant" log/production.log

# Job failures  
grep "Occupancy export failed" log/production.log

# File system issues
grep "Writing Parquet file" log/production.log

Scheduled Execution

The job is automatically scheduled to run every hour via config/sidekiq_scheduler.yml:

occupancy_export:
  every: "1h" # Runs every 1 hour
  class: "ScheduleWorkers::OccupancyExportWorker"
  description: "Export restaurant occupancy data to CSV files for analytics"

This ensures continuous data export for real-time analytics.

Data Retention

CSV files are written to tmp/ directory. Consider:

1. Archival: Move completed files to permanent storage (S3, etc.)
2. Cleanup: Remove old files to prevent disk space issues
3. Monitoring: Track file sizes and generation frequency

Example cleanup script:

# Remove CSV files older than 7 days
find tmp/ -name "occupancy_*.csv" -mtime +7 -delete

Troubleshooting

Common Issues

1. Redis Connection Issues

# Check if $inv_redis is properly configured
# This should be handled by the application's Redis initializers
# Check config/initializers/ for Redis configuration

2. Empty CSV Files

• Check if restaurants have inventory data in Redis using $inv_redis

• Verify the Redis connection pool is pointing to the correct database

• Check restaurant active/expired status

3. Job Timeout

# Increase job timeout in initializer
Sidekiq.configure_server do |config|
  config.server_middleware do |chain|
    chain.add Sidekiq::Middleware::Server::ActiveRecord
  end
end

4. Memory Issues

• Reduce batch sizes in the worker
• Use :longprocess queue with higher memory limits
• Process restaurants in smaller chunks

Debug Mode

Enable detailed logging by setting log level:

# In Rails console or script
BUSINESS_LOGGER.level = Logger::DEBUG
ScheduleWorkers::OccupancyExportWorker.perform_async(timestamp)

Integration Examples

Data Pipeline Integration

1. Airflow DAG:

from airflow import DAG
from airflow.providers.http.operators.http import SimpleHttpOperator

# Trigger export via API call to Rails app
trigger_export = SimpleHttpOperator(
    task_id='trigger_occupancy_export',
    http_conn_id='rails_app',
    endpoint='/admin/trigger_occupancy_export',
    method='POST'
)

2. Analytics Processing:

import pandas as pd
from datetime import datetime, timedelta

# Load recent exports
files = glob.glob(f"tmp/occupancy_*.csv")
recent_files = [f for f in files if 
    datetime.fromtimestamp(int(f.split('_')[1].split('.')[0])) > 
    datetime.now() - timedelta(days=7)]

# Combine and analyze
dfs = [pd.read_csv(f) for f in recent_files]
combined = pd.concat(dfs, ignore_index=True)

# Generate insights
daily_capacity = combined.groupby(['restaurant_id', 'date'])['seat_left'].sum()
peak_hours = combined.groupby('start_time')['seat_left'].mean().sort_values(ascending=False)

This system provides a robust foundation for extracting, storing, and analyzing restaurant occupancy data at scale.