Database Architecture (MySQL)

1. Overview

The Alta Call ecosystem relies on a unified MySQL relational database backend. This database orchestrates client organizations, licenses, real-time diagnostic telemetries, consolidated audio quality analysis (Business Intelligence), and automated issue detection.

This document serves as the official reference for the database schema, covering the entity-relationship model, tables dictionaries, and physical indexing optimizations.

2. Entity-Relationship Model (ERD)

The following entity-relationship diagram illustrates the primary tables managed by the Laravel backend and their operational relationships:

classDiagram
    direction LR
    class clients {
        int id PK
        string name
        string email
        string vat_number
        json config
    }
    class groups {
        int id PK
        int client_id FK
        string name
    }
    class users {
        int id PK
        int group_id FK
        string name
        string email
        string vmo_user_id
        int audiostream1_id FK
    }
    class licenses {
        int license_id PK
        int group_id FK
        string licenses_key
        bool valid
    }
    class machines {
        int id PK
        int group_id FK
        string vmo_machine_id
        string computer
        string version
    }
    class telemetries {
        bigint id PK
        int client_id FK
        int user_id FK
        string process_id
        datetime date
        json payload
    }
    class users_summaries {
        bigint id PK
        int user_id FK
        string transfo_mode
        date date
        float duration
    }
    class machines_issues {
        bigint id PK
        int machine_id FK
        string issue_code
        datetime first_update
        datetime last_update
        string var1
    }
    class audiodevices {
        int id PK
        string name
        string type
    }
    class audiostreams {
        int id PK
        int device_capture_id FK
        int device_playback_id FK
        float duration
    }

    clients "1" --> "0..*" groups : owns
    groups "1" --> "0..*" users : contains
    groups "1" --> "0..*" licenses : assigns
    groups "1" --> "0..*" machines : registers
    users "1" --> "0..*" telemetries : generates
    users "1" --> "0..*" users_summaries : owns
    users "0..1" --> "0..*" audiostreams : uses
    machines "1" --> "0..*" machines_issues : undergoes
    telemetries "0..1" --> "0..*" machines_issues : triggers
    audiostreams "0..1" --> "1" audiodevices : captures
    audiostreams "0..1" --> "1" audiodevices : plays

3. Comprehensive Database Dictionary

This section details the technical role, SQL types, and column properties of each key table in the database:

3.1. Table clients (Organizations)

Represents the corporate organizations subscribing to Alta Voce services (e.g. contact centers).

3.2. Table groups (Teams / Departments)

Under a client account, users are categorized into logical teams or business departments.

3.3. Table users (Agents and Supervisors)

Contains credentials, session statuses, and configuration parameters of final users.

3.4. Table machines (Workstations inventory)

Stores physical computers and hosts that have run and registered an Alta Call instance.

3.5. Table licenses (Authentication Keys)

Stores client access tokens validating workstation connection to the backend.

3.6. Table telemetries (Raw Diagnostic Reports)

Stores raw and filtered telemetry reports received from client instances.

3.7. Table users_summaries (Daily BI Consolidated Reports)

Calculated daily summaries used for Business Intelligence and invoicing reports.

3.8. Table machines_issues (Incident Records Log)

Automated diagnostics registry tracking workstations hardware and audio failures. Resolved issues are deleted by the background cleanups.

3.9. Table audiodevices (Hardware Specifications Directory)

Directory listing all peripheral models and physical headsets detected on hosts.

3.10. Table audiostreams (Active Pipeline Streams)

Represents the active voice transformations and diagnostic variables of streams.

3.11. Auxiliary, Utility and Logging Tables

Outside of the primary telemetry and real-time audio pipeline streams, the MySQL database maintains several secondary tables dedicated to invoicing, security access logs, A/B Testing campaigns, and support telemetry logs.

Category A: Invoicing, Tariffs, and Consumption Logs

• invoices: Register of generated monthly PDF client invoices, tracking payment statuses, dates, and amounts.
• pricings and pricing_clients: Billing matrix tiers and custom pricing associations per client (billing cost calculated per second of transformed voice call).
• daily_usages and monthly_usages: Consolidated transformed voice seconds and network bandwidth consumption pre-calculated daily and monthly per client.
• accounts: Gandi or Stripe billing profiles bound to tenant subscriptions.
• addresses: Physical street addresses linked to corporate client profiles.

Category B: A/B Testing Infrastructure and Settings

• transfos: Available voice transformation algorithms and base configuration presets (e.g. SMA Smile preset configurations).
• abtest_transfos: Pivot table associating an A/B test campaign to target voice presets.
• labels: Skill tags (e.g. "Language - German", "Support Team") assignable to agents to narrow down targeted testing groups.
• user_labels: Pivot table mapping users (users) to their respective skill tags (labels).

Category C: Systems Diagnostics and Application Loggers

• processes2: Live tracking registry of all running Alta Call client processes across workstations (used for concurrent multiple-instance checks).
• telemetry_incidents: Aggregate registry of critical anomalies triggering immediate email alert logs to administrators.
• altacall_log_entries and altacall_log_records: Core diagnostic debug trace files sent periodically by desktop clients when encountering exceptions or application failures.
• plugin_logs: Event registry sent by softphones integrations modules.
• events: System audit trail logs (e.g. "Audio pipeline started", "Microphone switched").
• user_request_logs: HTTP request logs journal, used to secure API backends and enforce rate limiting.

Category D: Audio Metrics and Quality BI

• audio_data: Metadata associated with sound record files collected for analysis.
• recordings and recording_clients: Dynamic rules dictating when audio recordings should be enabled remotely for diagnostic purposes per client/group.
• machine_summaries and user_daily_summaries: Daily calculated workstation and user KPIs enabling instant administration dashboard render speeds.
• metrics and performance_metrics: Hardware diagnostic indicators (system latency, xruns count, process RAM).

Category E: Security Access, Versioning, and Notifications

• access_attempts: Audit logs capturing login attempts to Laravel Filament dashboards (IP, credentials correctness) to secure against brute-force attacks.
• api_keys: Client access tokens used by third party developers to securely request Alta Voce APIs.
• impersonations: Administrative logs recording when an Alta Voce support user logs in as a tenant administrator.
• configs: Global Laravel system configuration settings (e.g. mandatory minimum software version).
• software_versions: Registry of published desktop versions detailing download links used by the auto-update services.
• outdated_machines: Temporary monitoring list tracking physical computers running obsolete desktop versions.
• notifications: User notifications dispatched to administrators.
• suggestions: Workstation agent user feedback or technical ticket requests submitted directly from the desktop client.

4. Physical Recommendations & Indexing (MySQL)

[!IMPORTANT] Strategic Indexing for Business Intelligence & Diagnostics To secure response times below 100ms inside the Filament Admin dashboard and Customer Workspace portal, the following indexes are strictly recommended in production:

1. Composite Index on telemetries:
   CREATE INDEX idx_telemetries_user_date ON telemetries (user_id, date);
   Why: Essential for rapid nightly consolidation of raw telemetry packets per caller agent.

2. Composite Index on users_summaries:
   CREATE INDEX idx_users_summaries_date_user ON users_summaries (date, user_id);
   Why: Mandated to immediately load calling charts, duration graphs, and pitch statistics on Customer Client portals.

3. Incidents lookup index on machines_issues:
   CREATE UNIQUE INDEX idx_machines_issues_lookup ON machines_issues (machine_id, issue_code);
   Why: Resolves immediate hardware health check diagnostics without scanning the complete incidents registry.