user_tracks

History of accesses granted to a user through a track. Each row = one access event (purchase, import, manual assignment, …).

• ETL strategy: merge
• PK: id

Columns

Column	Type	Description
id	BIGINT	PK.
id_tenant	BIGINT	FK → tenants.id.
id_origin	BIGINT	Resolved tracks.id_origin.
id_track	BIGINT	FK → tracks.id.
id_user	BIGINT	FK → users.id.
is_active	BOOLEAN	Access has not been revoked. See below — active ≠ not expired.
ends_at	TIMESTAMPTZ	Fixed end date set by an admin. Takes priority over subscription_ends_at.
subscription_ends_at	TIMESTAMPTZ	End of the current recurrence cycle, as reported by the payment gateway.
created_at	TIMESTAMPTZ	When access was granted.
deleted_at	TIMESTAMPTZ	Soft delete of the access row. Revocation is is_active = false, not this.

The same (id_user, id_track) pair can appear multiple times — grants and revocations over time.

Active ≠ not expired

is_active means "was not revoked" — an admin or a gateway cancellation turned it off. It does not mean the access is still valid today: expiry is computed dynamically and never writes back to this flag.

The rule the application itself applies when loading a user's accesses is:

WHERE is_active OR ends_at > now()

An access with is_active = false but a future ends_at still grants access until that date.

Full expiry cannot be computed here. The access duration lives on the track (integracoes.acesso), which is not extracted into tracks, and the application combines multiple accesses of the same user with overlap/gap rules — overlapping durations are summed, lifetime always wins. Use the rule above for "was it revoked / did the scheduled end pass", not for exact entitlement.

For the current set of users per track, metrics.track_access_aggregates already consolidates is_active upstream — but it inherits the same expiry caveat, and it drops accesses that are is_active = false with a future ends_at.

Patterns

-- Accesses currently granted to user X
SELECT t.title, ut.created_at, ut.ends_at
FROM lms.user_tracks ut
JOIN lms.tracks t ON t.id = ut.id_track AND t.id_tenant = ut.id_tenant
WHERE ut.id_user = 12345
  AND (ut.is_active OR ut.ends_at > now())
  AND ut.deleted_at IS NULL
  AND t.deleted_at IS NULL
ORDER BY ut.created_at DESC;

-- Revoked accesses per track, last 30 days
SELECT ut.id_track, t.title, count(*) AS revoked
FROM lms.user_tracks ut
JOIN lms.tracks t ON t.id = ut.id_track AND t.id_tenant = ut.id_tenant
WHERE NOT ut.is_active
  AND ut.created_at >= now() - INTERVAL '30 days'
GROUP BY ut.id_track, t.title
ORDER BY revoked DESC;