amtins commented 7 months ago

As a Pillarbox developer, I'd like to evaluate the possibility of harvesting player-related data in order to have a fully customizable diagnostic tool.

Acceptance Criteria

Ask the infra team if it's possible to redirect diagnostic traffic to the POC.
See if this is a viable solution
Shadow traffic to a new endpoint
Try video.js CMCD CMCD validator

amtins commented 4 months ago

Snitch storage alternative

This investigation aims to test the collection of diagnostic data using PocketBase.

PocketBase

PocketBase is an open source Go backend, consisting of:

embedded database (SQLite) with realtime subscriptions
built-in files and users management
convenient Admin dashboard UI
and simple REST-ish API

Benefits

Data ownership
Limitless
Fixed cost

Drawbacks

Server management
No chart builder
Everything is "hand-made"

Material and Cost

Server

CPU: Intel Xeon E3-1230v6 - 4 c / 8 t - 3,5 GHz / 3,9 GHz
RAM: 16 Go DDR4 ECC
Storage: 2 x 450 Go SSD NVMe Soft RAID
Bandwidth: 250 Mbit/s

Link to the server configuration SYS-4-SSD-16

Configuration

Omitted

Cost

Price per month 22.99€ plus installation fee 22.99€ which equals to 298.87€ annually.

End points

Omitted

Improvements

Today, data is stored in a JSON field, which slows down SQL queries and index creation.

Several approaches are possible:

Extract fields as needed and leave the rest in a JSON object. This will require updating the extracted fields to keep the data consistent.
Create a table containing all fields, where each value represents the value of each JSON object. We might be tempted to normalize the database in 3NF, but this would bring no benefit and would require queries with joins.

The advantage of either solution is that it would be possible to create indexes according to need, to speed up queries.

The data migration script could look like this:

INSERT INTO reports (
  `id`,
  `updated`,
  `created`,
  `PlayerDuration`,
  `PlayerUrl`,
  `browser`,
  `clientTime`,
  `device`,
  `drmDuration`,
  `drmUrl`,
  `duration`,
  `environment`,
  `ilDuration`,
  `ilHttpStatusCode`,
  `ilNoPlayableResourceFound`,
  `ilPlayableAbroad`,
  `ilUrl`,
  `networkType`,
  `platform`,
  `player`,
  `referrer`,
  `screenType`,
  `tokenDuration`,
  `tokenHttpStatusCode`,
  `tokenUrl`,
  `urn`
)
SELECT
  id,
  created,
  updated,
  coalesce(json_extract(report, '$.playerResult.duration'),'n/a'),
  coalesce(json_extract(report, '$.playerResult.url'),'n/a'),
  coalesce(json_extract(report, '$.browser'),'n/a'),
  coalesce(json_extract(report, '$.clientTime'),'n/a'),
  coalesce(json_extract(report, '$.device'),'n/a'),
  coalesce(json_extract(report, '$.drmResult.duration'),'n/a'),
  coalesce(json_extract(report, '$.drmResult.url'),'n/a'),
  coalesce(json_extract(report, '$.duration'),'n/a'),
  coalesce(json_extract(report, '$.environment'),'n/a'),
  coalesce(json_extract(report, '$.ilResult.duration'),'n/a'),
  coalesce(json_extract(report, '$.ilResult.httpStatusCode'),'n/a'),
  coalesce(json_extract(report, '$.ilResult.noPlayableResourceFound'),'n/a'),
  coalesce(json_extract(report, '$.ilResult.playableAbroad'),'n/a'),
  coalesce(json_extract(report, '$.ilResult.url'),'n/a'),
  coalesce(json_extract(report, '$.networkType'),'n/a'),
  coalesce(json_extract(report, '$.platform'),'n/a'),
  coalesce(json_extract(report, '$.player'),'n/a'),
  coalesce(json_extract(report, '$.referrer'),'n/a'),
  coalesce(json_extract(report, '$.screenType'),'n/a'),
  coalesce(json_extract(report, '$.tokenResult.duration'),'n/a'),
  coalesce(json_extract(report, '$.tokenResult.httpStatusCode'),'n/a'),
  coalesce(json_extract(report, '$.tokenResult.url'),'n/a'),
  coalesce(json_extract(report, '$.urn'),'n/a')
FROM snitch;

Finally, it's important to add indexes to the created and updated fields so that the admin interface isn't slow to load when the database starts to contain several million entries.