track_access_aggregates

Who has access to each track — and how they got it. Splits access between paid (via sale/integration) and manual (granted by an admin).

• Grain: (id_tenant, id_track) — one row per track
• ETL strategy: merge
• PK: (id_tenant, id_track)

When to use it

• How many learners paid for each track vs. received manual access.
• Identify tracks with high manual / low paid ratios (suspect of bypassing the sales funnel).
• Audit of granted accesses.

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
id_track	BIGINT	FK → tracks.id. Part of PK.
user_ids	BIGINT[]	Array of all users currently holding active access to the track.
user_ids_paid	BIGINT[]	Subset who got access via sale/integration.
user_ids_manual	BIGINT[]	Subset who got access manually from an admin.
updated_at	TIMESTAMPTZ	Last update of the aggregate.

By construction: user_ids ⊇ (user_ids_paid ∪ user_ids_manual). Other minor origins may exist inside user_ids that aren't in either subset.

Usage pattern

-- Paid × manual distribution per track
SELECT id_track,
       array_length(user_ids,        1) AS total,
       array_length(user_ids_paid,   1) AS paid,
       array_length(user_ids_manual, 1) AS manual,
       round(100.0 * array_length(user_ids_manual, 1)::numeric
                   / NULLIF(array_length(user_ids, 1), 0), 1) AS manual_pct
FROM metrics.track_access_aggregates
ORDER BY manual_pct DESC NULLS LAST;

-- Learners with access to a specific track
SELECT u.id, u.name, u.email
FROM lms.users u
WHERE u.id = ANY (
    SELECT unnest(user_ids)
    FROM metrics.track_access_aggregates
    WHERE id_track = 42
);