user_tags

M:N junction between users and tags.

• ETL strategy: merge
• PK: id

Columns

Column	Type	Description
id	BIGINT	PK.
id_tenant	BIGINT	FK → tenants.id.
id_user	BIGINT	FK → users.id.
id_tag	BIGINT	Logical FK to tags. Resolves to tags.id_origin when present, otherwise tags.id.
created_at	TIMESTAMPTZ	When the user↔tag link was created. Partially estimated for historical rows — read the caveat below before using it.
deleted_at	TIMESTAMPTZ	Soft delete. NULL means the link is active. Fully reliable across the whole range.

created_at is estimated for rows created before 2026-07-31

The source column in the LMS was written by a rare code path only, so it was empty in 99.85% of rows (application bug, tracked as V6-BUG-120). The history was reconstructed by estimation on 2026-07-31. The provenance of each value is not carried in this table — a reconstructed timestamp is indistinguishable from a real one here. Composition of the reconstruction:

Tier	Share	How it was derived	Measured error
Real	~0.15%	Value written by the application.	—
Interpolated	~26%	Linear interpolation on id (serial, monotonic in time) between two real anchors.	p99 = 43 min
GREATEST	~73%	GREATEST(user.created_at, tag.created_at), capped at the first anchor.	median 0 days, p90 = 154 days

What this means in practice:

• ✅ Safe for period aggregation — month, cohort, vintage. That is what the estimate was built for and validated against.
• ❌ Not safe for fine ordering or for the elapsed time between two nearby links. In the GREATEST tier created_at does not follow insertion order: on a sample of 175,821 consecutive pairs, 14.96% have a created_at earlier than the row with the preceding id.
• 👉 If you need true ordering, use id. It is a serial and is monotonic in time.

deleted_at is not affected — it comes straight from the source with no estimation.

Daily delta no longer depends on source dates

This table used to under-read new links: a brand-new row arrived with created_at andupdated_at NULL, so the date-only delta filter never saw it — 9 of 35 new rows per day (26%), which had compounded into a 25% shortfall on fiberschool before the 2026-07-31 fresh load.

Since 2026-08-01 the delta filters on id > <highest id already loaded> OR the date columns. The keyset half catches new rows without depending on any date being filled; the date half catches old rows that changed (soft delete, tag swap), whose id is below the watermark. The application bug that left the dates empty was fixed the same day, but the keyset stays — it is what makes this pipeline independent of the source filling any date column at all.

Patterns

-- Users carrying the "VIP" tag (active links only)
SELECT u.*
FROM lms.users u
JOIN lms.user_tags ut
    ON ut.id_tenant = u.id_tenant AND ut.id_user = u.id
JOIN lms.tags t
    ON t.id_tenant = ut.id_tenant
   AND (t.id = ut.id_tag OR t.id_origin = ut.id_tag)
WHERE t.title = 'VIP'
  AND ut.deleted_at IS NULL
  AND u.deleted_at IS NULL
  AND t.deleted_at IS NULL;

-- Tagging by month (valid use of created_at — aggregation by period)
SELECT date_trunc('month', ut.created_at) AS month, t.title, count(*)
FROM lms.user_tags ut
JOIN lms.tags t ON t.id_tenant = ut.id_tenant AND t.id = ut.id_tag
WHERE ut.id_tenant = 100
GROUP BY 1, 2
ORDER BY 1;

-- Most recent links (use id, NOT created_at — see the caveat above)
SELECT *
FROM lms.user_tags
WHERE id_tenant = 100
ORDER BY id DESC
LIMIT 50;