Telemetry and Storage Analysis

[!IMPORTANT] Incident Context and Gandi Recommendations Following a high server load incident observed on our production infrastructure, our hosting provider Gandi recommended reducing the size of the data stored in the telemetry table to decrease write/read IO pressure on the database server.
This document provides a detailed analysis of the data volume generated by the system to rationalize data retention and anticipate future scaling requirements.

1. Telemetry Table Structure (`telemetries`) in MySQL

In the MySQL production database, the telemetries table stores the periodic diagnostic reports transmitted by each running desktop client. It consists of the following columns:

Column Name	SQL Type	Technical Role and Description
`id`	BigInt (Primary Key)	Sequential unique identifier of the telemetry record.
`appname`	String (VARCHAR)	Name of the source client application (e.g. `alta-call`).
`version`	String (VARCHAR)	Installed desktop client version (e.g. `0.33.0`).
`process_id`	String (VARCHAR)	Unique running process instance UUID.
`date`	DateTime	Exact timestamp when metrics were collected by the client.
`cp_nbrunningproc`	Int	Total number of concurrent Alta Call processes running in the Windows user session.
`payload`	LongText (JSON Cast)	Cleaned JSON payload containing processed telemetry metrics (see Section 1.8).
`client_id`	Int (Foreign Key)	Reference to the organization client account in the `clients` table.
`user_id`	Int (Foreign Key)	Reference to the call center agent in the `users` table.
`first_for_date`	TinyInt (Boolean)	True if this is the first telemetry packet received for the active date.
`last_for_date`	TinyInt (Boolean)	True if this is the last telemetry packet received for the active date.
`created_at`	DateTime	Record database insertion timestamp.
`updated_at`	DateTime	Record database last update timestamp.

1.2. Global Database Architecture (MySQL)

The Alta Voce platform utilizes a normalized, high-performance MySQL relational database schema. The complete analysis of all database tables, entity cardnalities, specific SQL columns properties, and physical indexing optimizations are available on our dedicated page:

👉 Database Architecture (MySQL)

1.5. Telemetry Payload Optimization (`TelemetryCleaningService`)

To resolve database server load issues and limit the size increase of the database, a telemetry payload cleaning service has been deployed in the Laravel backend (App\Services\Telemetry\TelemetryCleaningService).

Optimization Metrics

Average Payload Size Reduction: 96.32% average size reduction.
Initial Average Size: ~14 KB per payload.
Optimized Average Size: ~535 Bytes per payload.
Saved Storage Space: ~13.6 KB per record.

Retained Keys (Whitelist / Essential Data)

Root level: audio_state, transfo_preset, name, version, reach_data_consent, consent_data_collection, from_downtime, datetime, virtdev_out_appname, ACP (mandatory for audio device tracking in summaries), and PT (full section needed for transformations and billing).
libs.voimooo level: machine_id, process_id, user_id, session_id_current, session_id_active, session_is_locked, cpu_machine, cpu (for process telemetry), session_active_unlocked, session_active_unlocked_since, audio (for connected application mapping).
PT level: transfo_mode, processing_state, tcfp, pvoccmb, levels (for input/output decibels), ctxb (for fundamental pitch frequency stats), alpha, beta, delta (voice model weights).

Cleaned Keys (Removed Data)

RT Section: Completely removed (runtime stats, API counts, and license keys).
UI Section: Completely removed (UI slider values and graphical states).
configs Section: Completely removed (redundant with the dedicated users_configs database table).
libs.voimooo Section (partial): Removed complete system hardware specs, detailed history logs of past audio devices, and debug session hashes.

1.8. Exhaustive Payload JSON Dictionary

The following table documents every parameter sent by the Alta Call desktop client inside the telemetry payload, along with its specific functional role:

Root Level JSON Keys

JSON Key	Type	Functional Role and Contents
`name`	String	Client application official name (always `alta-call`).
`version`	String	Client application precise software version (e.g. `0.33.0`).
`audio_state`	String	Active audio engine state (`RUN`, `STP`, `LOD`, `FAI`, `CRA`).
`transfo_preset`	String	Active voice transformation configuration preset identifier.
`reach_data_consent`	Int (0/1)	Indicates whether data collection consent prompts were shown to the agent.
`consent_data_collection`	Int (0/1)	User data collection preference/consent status.
`from_downtime`	Boolean	True if the packet was queued offline and sent after network restoration.
`datetime`	String	Original event timestamp for delayed offline packets (ISO 8601 format).
`virtdev_out_appname`	String	Active softphone/application name connected to Alta Call's output.
`ACP`	Object	Audio Control Panel device and state configurations (see details below).
`PT`	Object	Processing Telemetry parameters and speech attributes (see details below).
`libs`	Object	System-level host metrics and core session information (see details below).
`UI`	Object	UI lock state flags and graphical configuration parameters.
`configs`	Object	Complete user profile config setup at delivery.
`RT`	Object	Diagnostic API call statistics and client runtime counts.
`connectedapp_connected_apps`	Object	Raw list of running target communications applications on the host.

`ACP` (Audio Control Panel) Section Details

capture (Object):
- using (String): Name of the active physical capture device (microphone).
- available (Object): Map of all detected microphone devices with their audio APIs (WASAPI/DirectSound), sampling rates, and hardware descriptors.
playback (Object):
- using (String): Name of the active physical playback device (headphones/speakers).
- available (Object): Map of all connected playback devices.

`PT` (Processing Telemetry) Section Details

transfo_mode (String): Active acoustic processing mode (SMA: Smile, VMO: Voice, STP: Stopped).
processing_state (String): Voice processing status (RUN or STP).
levels (Object): Real-time vocal volume measurements in decibels (dB) for input and output channels.
ctxb (Object): Extracted acoustic features, including fundamental pitch frequency (F0) and Line Spectral Frequencies (LSF) mapping the voice timbre.
alpha, beta, delta (Float): Speech transformation mathematical model weights and factors.

`libs.voimooo` (System Data) Section Details

machine_id (String): Hardware UUID generated for the workstation.
process_id (String): UUID identifying the active Alta Call process instance.
user_id (String): Active host Windows username/user ID.
session_id_current (String): Current system execution session ID.
session_id_active (String): Logged-in active Windows desktop session identifier.
session_is_locked (Boolean): Indicates whether the Windows screen/session is locked.
cpu_machine (Float): Global computer CPU utilization rate.
cpu (Float): CPU utilization dedicated specifically to the Alta Call process (e.g., 0.12 = 12%).
session_active_unlocked (Boolean): True if the machine is active and currently unlocked.
session_active_unlocked_since (String): Timestamp indicating since when the workstation has been active and unlocked.
audio (Object): Virtual audio cables and VoiceMeeter integration states.

2. Volume and Frequency (Raw Telemetry)

Average size of a single telemetry record: ~13 KB
Main frequency: 1 telemetry record per hour of activity (about 8 per day for a standard worker).
Additional events: ~10 events per day (first launch, audio crash, software lock/unlock, user session lock/unlock), representing an estimated ~130 KB extra.

Daily Volume Calculation per User

$$\text{Daily Volume} = (13\text{ KB} \times 8) + 130\text{ KB} = 234\text{ KB / user / day}$$

Storage for 700 Users (Current User Base)

1 day: ~163 MB
3 days: ~489 MB
6 days: ~976 MB
7 days: ~1.1 GB
30 days (1 month): ~4.4 GB

3. Storage Projection by User Count Milestones (Raw Telemetry)

The following table shows the estimated growth of the telemetries table as the active user base grows:

User Count	1 Day	3 Days	7 Days	30 Days (GB)
1,000	~228 MB	~684 MB	~1.56 GB	~6.8 GB
2,000	~457 MB	~1.37 GB	~3.12 GB	~13.6 GB
5,000	~1.14 GB	~3.42 GB	~7.80 GB	~34.0 GB
10,000	~2.29 GB	~6.87 GB	~15.20 GB	~68.0 GB

4. User Summaries Analysis (`users_summaries`)

User summaries (users_summaries) are computed every morning based on the telemetry data collected from the previous day. They offer a much lighter consolidated view.

`users_summaries` Table Column Definitions

Column Name	Type / Format	Role and Business Purpose
`user_vid`	UUID	Universally unique identifier of the user.
`user_id`	Int	Unique internal user identifier.
`process_id`	Int	Execution process ID.
`version`	String	Version of the client application used.
`process_first_update`	DateTime	First activity timestamp of the process.
`nb_telemetries`	Int	Number of telemetry packets recorded during the day.
`session_is_active`	Boolean (0/1)	Indicates if the session was active and in use.
`session_is_locked`	Boolean (0/1)	Indicates if the user session was locked.
`nbrunningprocs_mean`	Float	Average number of running Alta Call processes.
`is_up`	Boolean (0/1)	Indicates if the OS and client app were fully functional.
`date`	Date	Date of the summarized day.
`transfo_id`	String	ID of the applied voice transformation.
`transfo_mode`	String	Applied voice transformation mode.
`transfo_params`	JSON	Detailed settings of the voice transformation algorithm.
`audio_params`	JSON	Technical audio API parameters.
`time_start`	DateTime	Start of the analyzed period in the day.
`time_end`	DateTime	End of the analyzed period in the day.
`duration`	Float (sec)	Cumulative duration of activity on the audio channel.
`abtest_id`	String	ID of the active A/B test if applicable.
`abtest_label_id`	String	User label in the context of the A/B test.
`device_capture_name`	String	Name of the capture device (microphone).
`device_playback_name`	String	Name of the playback device (speaker/headphones).
`device_capture_id`	String	Technical ID of the capture device.
`device_playback_id`	String	Technical ID of the playback device.
`virtdev_out_appname`	String	Name of the virtual audio application/softphone output.
`audio_state`	Enum	Audio engine state (`RUN`, `STP`, `CRA`, `FAI`, `LOD` - see nomenclature).
`audio_features`	JSON	Extracted acoustic features (e.g. F0 histogram, vocal frames).
`stat_f0_median`	Float	Median fundamental voice frequency (pitch).
`stat_f0_iqr`	Float	Interquartile range of fundamental frequency (pitch variability).
`stat_inputvoiceddb_median`	Float	Median input voice volume (in dB).
`stat_inputunvoicedlowdb_median`	Float	Median background noise / silence level.
`created_at`	DateTime	Creation date of the summary record.
`updated_at`	DateTime	Last update timestamp of the summary record.

5. Volume and Frequency (`users_summaries`)

Average size of a summary record: ~1 KB
Average frequency: ~10 records per user per day (depending on active session cycles and state changes).
Estimated daily volume per user: ~10 KB

Storage for 700 Users (Current User Base)

1 day: ~7 MB
3 days: ~21 MB
7 days: ~49 MB
30 days (1 month): ~210 MB

Storage Projection by User Count Milestones (`users_summaries`)

User Count	1 Day	3 Days	7 Days	30 Days (MB/GB)
1,000	~10 MB	~30 MB	~70 MB	~300 MB
2,000	~20 MB	~60 MB	~140 MB	~600 MB
5,000	~50 MB	~150 MB	~350 MB	~1.5 GB
10,000	~100 MB	~300 MB	~700 MB	~3.0 GB

6. Architectural Recommendations for Data Retention

[!TIP] Partitioning and Auto-Purge Strategy (Retention Policy) To permanently address the server load and database warnings flagged by Gandi, we recommend the following engineering rules:
Purging Raw Telemetries (telemetries): Set up a daily cron script to delete all records from the telemetries table that are older than 7 days. One week of raw telemetry is highly sufficient for debugging recent application crashes or analyzing isolated user complaints.
Retaining Summaries (users_summaries): Since user summaries are 23 times lighter per storage day (10 KB vs. 234 KB per user), we can safely store the complete history of users_summaries indefinitely or for 2 years to fulfill Business Intelligence (BI) and long-term analytics requirements, without risking database overload.
Index Optimization: Ensure indexes are properly built on telemetries (particularly on created_at and user_id columns) to ensure that the periodic purge query does not lock tables during write operations.