Coniglio - Event Tracking and Session Analytics System

Coniglio is a robust event tracking and session analytics system designed for high-volume data processing. It handles millions of events daily with a focus on efficient data processing and linear scalability.

Coniglio has three main responsibilities:

1. Data ingestion
2. Data validation and storage
3. Data aggregation

1. Data Ingestion

Coniglio tracks user interactions within the reader (Volpe) through a variety of events:

• session-start: When a user opens the reader
• session-end: When a user closes the reader
• session-resume: When a user re-opens the reader after closing it (within 30 minutes)
• page-change: When a user changes the page or scrubs the audiobook player
• tab-hidden: When the user changes the browser's tab, the browser or Publica.la Reader goes to the background
• tab-visible: When the user re-opens the tab, puts the browser or Publica.la Reader into the foreground again
• heartbeat: Fired automatically every x seconds (configured in Volpe)

Each time one of these events occurs in the reader, coniglio.js sends the data to coniglio's corresponding endpoint. This data is immediately dispatched to a queue for validation, with the ingestion process doing minimal work and returning a 202 status code to the client. These are referred to as "track requests".

2. Data Validation and Storage

For each track request, a ValidateTrackRequest job is dispatched to validate the incoming data. If the track request is valid, it triggers an event in Laravel that is listened to by TrackEventStorer, which stores the event in the track_events table with detailed information including timestamps.

3. Data Aggregation

The data aggregation process involves several components working together:

TrackEventsAggregator

This scheduled job runs every minute to process track events within specific time windows. It operates using time ranges rather than tracking individual event aggregation states, with these key features:

1. Time-Based Window Processing:

   • Processes events from a starting point (from_created_at) to an ending point (to_created_at)
   • Uses the end of the previous aggregation as the start of the next one, creating a continuous chain
   • Typically limits processing windows to 1 hour to keep batch sizes manageable

2. Race Condition Prevention:

   • Implements a critical 5-second offset (now()->subSeconds(5)) when determining the end time
   • This small buffer ensures we don't include events still being written to the database
   • Guarantees data consistency by preventing partial or incomplete data processing

3. Processing Flow:

   • Acquires a lock to prevent concurrent processing of the same time range
   • Queries for all events within the current time window grouped by tenant
   • Dispatches tenant-specific processing jobs as a batch
   • Records aggregation metadata including time windows, tenant IDs, and event counts
   • Automatically redispatches itself for the next time window when necessary

Batch Processing

The aggregation processing uses Laravel's batch system to efficiently handle large volumes of data:

1. The TrackEventsAggregator creates a batch containing multiple TenantTrackEventsAggregator jobs - one for each tenant with events in the current time window
2. Each tenant's events are processed independently in parallel, allowing efficient use of resources
3. The batch tracks overall progress and handles failure scenarios, ensuring all tenants' data is processed reliably
4. If processing for a tenant takes too long (>15 seconds), it self-requeues with pagination parameters to continue processing in smaller chunks
5. The batch system provides hooks for completion, failure, and final cleanup operations

This approach enables Coniglio to:

• Process millions of events daily with predictable resource usage
• Handle tenant-specific processing independently, preventing one tenant from blocking others
• Automatically recover from failures without losing progress
• Implement time-based circuit breakers to prevent job starvation

TenantTrackEventsAggregator

This job processes event batches for a specific tenant by:

1. Processing events sequentially through the SessionProjector
2. Handling pagination for large event volumes using session_uuid and index references
3. Updating the aggregated_at timestamp for processed events

Data Structure

Coniglio utilizes two primary tables:

• track_events: Stores raw event data with timestamps
• track_events_aggregations: Tracks aggregation metadata including:
  • Time window information (from_created_at and to_created_at)
  • Tenant information (tenants_ids)
  • Processing metrics (amount_of_events_aggregated, duration)
  • Status tracking

The aggregation process generates and maintains the sessions table, which provides a summary view of user reading/listening activity.

System Architecture

Job Middleware

All jobs in Coniglio use these essential middleware components:

• TrackEventsProcessingShortCircuitMiddleware: Controls whether processes execute based on environment variables (TRACK_EVENTS_PROCESSING_AGGREGATION_ENABLED)
• RaceConditionMiddleware: Ensures only one instance of a job runs concurrently, preventing parallel processing conflicts
• SkipIfBatchCancelled: A Laravel core middleware (not a custom implementation) that allows for graceful cancellation of processing jobs when they are part of a batch

Sessions Logic

Sessions follow the Google Analytics model:

• Sessions last up to 30 minutes of inactivity
• If a user returns within 30 minutes, the activity continues in the same session (generating a SessionResume event)
• If a user returns after 30+ minutes, a new session is created

External Access

Coniglio provides an API that Farfalla uses to access statistical data. Additionally, Farfalla has direct read-only access to Coniglio's database for more complex queries.

Performance Considerations

Coniglio is optimized for high throughput:

• Processing hundreds of thousands to millions of events daily
• Using efficient database queries designed for linear scaling
• Implementing time window processing to manage large volumes of data
• Using SingleStore's columnstore with carefully selected SHARD and SORT keys for high-performance data storage and retrieval

Maintenance

Pausing Queues

During maintenance operations or other scenarios where you need to temporarily pause data processing:

1. Pause Validation and Storage: Set TRACK_EVENTS_PROCESSING_AGGREGATION_ENABLED=false to prevent new track requests from being validated and stored.
2. Control Scheduled Aggregation: Set TRACK_EVENTS_PROCESSING_SCHEDULED_AGGREGATION=false to disable the scheduled aggregation process while still allowing manual triggering if needed.
3. Resume Processing: Set both variables back to true to resume normal operations.

This approach is useful for:

• Database maintenance windows
• System updates requiring consistent data state
• Troubleshooting data processing issues
• Performing data migrations
• Decreasing database load

Note that while processing is paused, raw events are still pushed to the queue, validated, and inserted into the track_events table. It's only the aggregation of these events into the sessions table that's paused. This design ensures no data loss during maintenance periods while reducing database load from intensive aggregation operations.

Running Scheduled Tasks Locally

When developing or testing Coniglio locally, you need to run two components to ensure proper functionality:

1. Queue Worker - Processes background jobs:

   php artisan horizon

   Coniglio uses Laravel Horizon for queue management in local environments. If you prefer a simpler setup, you can configure the "sync" driver in your .env file:

   QUEUE_CONNECTION=sync

2. Scheduler - Triggers scheduled tasks:

   php artisan schedule:run

   This command executes any due scheduled tasks defined in the application's console kernel. Since this command only runs tasks that are due at the moment of execution, you have several options for continuous testing:

   • Run schedule:run manually whenever needed

   • Use the development-friendly command that continuously polls for due tasks:

     php artisan schedule:work

   • Set up a cron job to run schedule:run every minute (similar to production)

Running both components is essential as Coniglio's event processing pipeline relies on both queued jobs (for data processing) and scheduled tasks (for aggregation).

Automatic Maintenance Window

Coniglio includes an automatic maintenance window detection system to protect database performance during scheduled database cluster maintenance:

• The system automatically detects if the current time falls within the SingleStore cluster's scheduled maintenance window (Tuesdays between 10:00-12:00 UTC).
• During this window, queuing of intensive aggregation jobs is skipped via the isInMaintenanceWindow() method in Kernel.php.
• This is a precautionary measure for potential DB upgrades or maintenance operations that might impact performance.
• Note that actual maintenance doesn't always occur during these windows, but the system pauses intensive operations by default to ensure stability.

---