Emote Catalog

The emote catalog is the durable store behind every emote lookup: the chat_emotes table in the primary PostgreSQL, with Redis and an in-process LRU demoted to caches in front of it. It is read on the chat-ingest path and written whenever we learn about an emote.

Why Postgres owns this

Before the catalog, every shortcode → image-URL mapping lived only in Redis (lumio:emotes:{platform}:{channel_id} for the fetched third-party sets, lumio:yt:channel_emotes:{channel_id} for YouTube observations). The only persistence was the denormalised emotes JSONB on each chat message, with the CDN URL frozen at write time.

That made a cache expiry indistinguishable from "this channel has no emotes", which cost real data: the YouTube Data-API ingest path has no emote source of its own, so a cold key meant the message was stored with emotes = NULL — and because the mapping existed nowhere else, that loss was permanent and unrepairable. With a durable catalog, an expiry only empties a cache.

The catalog lives in the primary Postgres, not TimescaleDB. It is a slowly-changing dimension: keyed upserts, a unique constraint, point lookups, a few thousand rows per channel. Hypertables win on append-only time series with range scans; time partitioning buys nothing here, would cost the unique constraint, and would put reference data in the wrong pool for ingest and config reads.

The three tiers

LruCache (in-process, ~60s)  ->  Redis (6h)  ->  Postgres chat_emotes

Tier	Purpose	Miss behaviour
1 — LRU	Removes the per-message Redis round-trip. Chat ingest previously did a full GET plus a full-map JSON deserialize for every inbound message.	Falls through to Redis
2 — Redis	Shared hot cache across API instances. A present but empty map is a hit, not a miss — that is what stops an emote-less channel from querying Postgres on every message.	Falls through to Postgres
3 — Postgres	The truth. Result is written back into Redis (even when empty) and the LRU.	Returns an empty map

Implemented by lo_chat::emote_catalog::EmoteCatalog in crates/lo-chat/src/emote_catalog.rs, modelled on the existing two-tier MembershipBadgeCache (crates/lo-chat/src/youtube_badges.rs).

Fail-open at every tier

Neither an unreachable Redis nor a Postgres error may drop or delay a chat message — only leave it un-enriched. Two consequences worth knowing:

• A read that cannot reach Postgres is deliberately not cached. Caching an error as "no emotes" would pin a wrong answer for a whole TTL window, and Postgres being unreachable already means chat rows are not being written.
• During a Redis outage the LRU still bounds the load to one Postgres query per channel per LRU TTL, rather than one per message.

Writes invalidate, they do not merge

When EmoteCatalog::upsert changes something in Postgres, it then deletes the Redis key for every channel it touched, so the next reader rebuilds from the truth. The previous read-modify-write against Redis was not atomic and could lose entries when two workers observed the same channel concurrently. A write that changes nothing invalidates nothing — see A write that changes nothing is not a write below.

Because a DEL cannot reach another process's LRU, the LRU TTL is also the worst-case staleness for a freshly observed emote across API instances — which is why it is short (60 s) on a store that otherwise changes slowly.

A write that changes nothing is not a write

The upsert's DO UPDATE carries a WHERE: a row is rewritten only when a field a reader can observe actually changed (url, animated, owner_id, platform) or when last_seen_at has gone stale by more than an hour. Everything else is skipped by Postgres and reported back as 0 rows affected, and invalidation is keyed off that count — an upsert that changed nothing leaves the Redis map and the LRU entry alone.

This matters because of how often the write path runs. Chat ingest upserts the emotes it saw once per poll batch (every ~10 s per live channel), so in steady state it is re-stating rows Postgres already holds. Unconditionally, each of those batches would:

• write a new tuple version plus WAL and leave a dead tuple for autovacuum, for every emote, forever. The row count stays flat — that is what the unique key buys — but the write volume would track stream-hours, which is the one quantity here that grows without bound; and
• DEL the channel's Redis map and drop its LRU entry, so the 6 h and 60 s TTLs would be discarded every 10 s on exactly the channels busy enough to need them.

The cost of the guard is that last_seen_at is accurate only to the hour. Every reader is fine with that: it is a liveness signal, used as the provider tie-break when two providers host the same shortcode, not a per-message timestamp — chat messages already carry their own.

Schema

CREATE TABLE chat_emotes (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform          TEXT NOT NULL,        -- where it is usable
    provider          TEXT NOT NULL,        -- who hosts it
    channel_id        TEXT,                 -- NULL = global set
    provider_emote_id TEXT NOT NULL,
    name              TEXT NOT NULL,
    url               TEXT NOT NULL,
    animated          BOOLEAN NOT NULL DEFAULT FALSE,
    owner_id          TEXT,
    source            TEXT NOT NULL,        -- 'api_fetch' | 'observed'
    first_seen_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chat_emotes_key UNIQUE NULLS NOT DISTINCT
        (provider, channel_id, provider_emote_id, name)
);

platform vs provider are genuinely different axes: a 7TV emote is hosted by 7TV (provider = '7tv') but usable in a Twitch channel (platform = 'twitch'). provider values match lo_chat::EmoteProvider.

Three details that are easy to get wrong:

• UNIQUE NULLS NOT DISTINCT (PostgreSQL >= 15; dev runs postgres:18, CI postgres:16). The global sets all carry channel_id IS NULL; under the default NULLS DISTINCT every re-fetch would insert a fresh row forever.
• The upsert must name the constraint — ON CONFLICT ON CONSTRAINT chat_emotes_key. A NULLS NOT DISTINCT constraint cannot be inferred from a column list.
• provider_emote_id is part of the key on purpose. A renamed emote that keeps its id becomes a second row, so both shortcodes stay resolvable — which is what chat history needs. A re-fetch of the same (id, name) pair is an idempotent update — and, per A write that changes nothing is not a write, usually no write at all. first_seen_at is never touched.

channel_id means "usable here", not "owned by"

For a YouTube observation, channel_id is the broadcaster whose chat the emote appeared in, and the channel that hosts it goes in owner_id.

This fixes a long-standing read/write asymmetry: the old Redis write keyed on the hosting channel (the emojiId prefix) while the read keyed on the broadcaster being polled. Those coincide only for a broadcaster's own emotes, so any emote hosted elsewhere — a viewer pasting another channel's custom emote — was written under a key nobody ever read. channel_id now answers exactly the question the ingest asks: which emotes can appear in this channel's chat?

Adding a writer

1. Build lo_chat::EmoteRow values. Set source to SOURCE_API_FETCH (fetched from a provider API) or SOURCE_OBSERVED (seen in-band in a chat message).

2. Persist and invalidate:

   • A caller that owns a long-lived EmoteCatalog (the chat-ingest worker) calls EmoteCatalog::upsert(&rows) — it also pops the in-process LRU.
   • A request-path caller with no catalog instance (the emotes query) calls the standalone emote_catalog::upsert_and_invalidate(db, redis, &rows).

   Both dedupe within the batch, do a single UNNEST round-trip regardless of batch size, and invalidate (DEL) the affected channel map keys rather than merging.

3. Treat the result as best-effort: log a failure, never propagate it into the path that produced the rows.

Rows are written only on a set fetch or an observation batch — never per chat message. platform_chat_messages.emotes remains the usage log that db::top_emotes aggregates; the catalog is the definition store.

The two writers today

Writer	source	channel_id	Entry point
Emote-set API fetch — the emotes query fetching Twitch/7TV/FFZ/BTTV/Trovo sets	api_fetch	the channel for a channel set, NULL for a provider's global set	services::emotes::resolve_channel_emotes → upsert_and_invalidate
YouTube observation — shortcodes seen in InnerTube chat	observed	the broadcaster whose chat it appeared in (host in owner_id)	workers::youtube → EmoteCatalog::upsert

A provider's global set must be collected with channel_id = None, not the channel being resolved. This is why the native Twitch fetch is split into fetch_twitch_channel_emotes and fetch_twitch_global_emotes rather than one combined call: merging them would write every global Twitch emote once per channel, inflating the catalog and defeating the NULL-channel global read.

The native Twitch fetch additionally needs a Helix bearer token, minted from the twitch system credential as an app access token — see Emotes → The Twitch app access token. Without a mintable token both Twitch fetches are skipped, and the catalog simply keeps whatever the other providers wrote.

Serving the catalog on an upstream outage

The emotes query is also a reader of the catalog. When every third-party fetch for a channel fails (a total upstream outage), it serves emote_catalog::load_channel_emote_sets(db, platform, channel_id) — the channel's rows plus the platform's global (channel_id IS NULL) rows, grouped by provider into the full EmoteSet shape — instead of an empty set. This is what stops a provider outage from wiping a channel's emote map. Like every catalog path it is fail-open: a Postgres error degrades to an empty result and is never pinned in the hot cache.

Read paths beyond ingest (ZAF-578)

Three more callers read the catalog. None writes; all are best-effort and fail-open — an unavailable catalog only leaves a message or report un-enriched.

Reader	What it reads	Why
Kick chat webhook (routes::webhooks)	state.emote_catalog.channel_map("kick", broadcaster_id)	Kick sends [{emote_id, positions:[{s,e}]}] with no name/url/provider. Ingest normalises it to {id,name,url,provider,animated}: name from content[s..e], url filled from the catalog (best-effort; omitted on a miss so the entry still carries a name).
Trovo chat worker (workers::trovo)	load_channel_emote_sets("trovo", channel_id) once per connection	Enriches messages via enrich_message_emotes_from_sets (word-scan) instead of storing emotes: None.
Stream-report read path (services::history::service)	resolve_emote_urls(db, &[(provider, name)])	Fills a missing image on a frozen top_emotes entry at read time; an entry that already has a url keeps its snapshot. Covers the report, exports, share page and summary e-mail.

Two ingest fixes ride along:

• Kick now counts in analytics. Because Kick emotes carry a name, they survive db::top_emotes' name IS NOT NULL filter and rank normally — closing the disagreement where emote_total counted them but top_emotes dropped them.
• URL freshness on the renderer. The chat renderer (message-content.tsx / emote-text.tsx) prefers the live channel-map URL over the frozen per-message one when the name matches, so a rotated CDN URL renders again; the frozen URL is the fallback. A name with no usable URL on either side renders as plain text (a url-less Kick emote), not a broken image.

Read through the tiers, not around them

load_channel_map and load_channel_emote_sets are tier 3 — they go straight to Postgres. Reach for them only where the read happens once per connection or per request (the Trovo worker loads its sets once when the socket opens), or from inside the catalog itself.

Anything on a per-message path must go through an EmoteCatalog so the LRU can absorb it. In a request handler that means state.emote_catalog, which is built once in main.rs and shared by every route; workers build their own (workers::youtube). Calling load_channel_map per message instead silently converts the hottest path in the API into one Postgres round-trip per message — the catalog still answers correctly, so nothing fails and nothing is logged; only the write-ahead log and the connection pool notice.

A second EmoteCatalog is not a correctness problem: tier 1 is per-instance and short-lived by design, and invalidation is a Redis DEL that no LRU sees anyway (see Writes invalidate, they do not merge).

Privacy

Catalog rows hold only public chat metadata — the same image URL every viewer's browser loads, fetched from an open API for the third-party sets. There is deliberately no account_id: a tenant boundary on public reference data would duplicate every row per account and make the global 7TV/BTTV/FFZ sets unmodellable. Read paths that need a tenant-specific answer constrain by the channel ids they already hold.

For the same reason the catalog emits no audit event — see the audit-events guide for what does qualify.

The YouTube GDPR erasure path (services::youtube_memberships) does not touch the catalog — it deletes neither chat_emotes rows nor the emote cache key. The catalog is deliberately left intact, for the reasons in the note below.

Emote catalog is out of Art. 17 scope and is not erased (ZAF-580)

The emote catalog is a cross-channel reference directory, so the YouTube GDPR erasure leaves it untouched. Two independent reasons:

1. Not personal data. An emote shortcode plus its public CDN image URL is channel content/branding reference data — the same URL every viewer's browser loads — with no message text, no account_id, and no behavioural or profile linkage. It is not personal data of the erased member that the right to erasure (Art. 17) reaches (founder's legal position, 2026-08-19: "unser emote verzeichnis fällt nicht unter Artikel 17, und muss nicht gelöscht oder geleert werden" — ZAF-580).

2. Shared across channels. An emote hosted by channel A is pasted and stored under channel B's chat (the row carries A's owner_id). Deleting rows by owner_id/channel_id would strip the emote from every other channel's historical messages and moderation reports, leaving them image-less. The delete would actively break the cross-channel directory this catalog exists to be.

So the erasure path removes no catalog rows and clears no emote cache key. A standing integration test (member_erasure_leaves_the_emote_catalog_intact in apps/api/tests/chat_emote_catalog.rs) asserts the rows survive; the erasure function no longer receives a primary-DB handle, so it is structurally incapable of touching the catalog.

What Art. 17 does reach is still erased in full, fatal-on-failure: the subject's YouTube membership rows (Redis) and their chat PII (message text, identity, and badge/emote JSONB in platform_chat_messages).

Key files

File	Purpose
apps/api/migrations/20260818000004_create_chat_emotes.{up,down}.sql	Table, constraint, indexes
crates/lo-chat/src/emote_catalog.rs	EmoteCatalog three-tier read-through, EmoteRow, upsert / load, upsert_and_invalidate, load_channel_emote_sets, resolve_emote_urls
crates/lo-chat/src/youtube_emote_cache.rs	YouTube :shortcut: tokenizer + message enrichment (storage-independent)
crates/lo-chat/src/emotes.rs	parse_emotes + enrich_message_emotes_from_sets (word-scan enrichment used by Trovo)
apps/api/src/services/emotes.rs	Emote-set API-fetch writer + outage fallback reader (resolve_channel_emotes), shared by the REST route and GraphQL resolver
apps/api/src/routes/webhooks.rs	Kick webhook emote normaliser (normalize_kick_emotes) + catalog URL read
apps/api/src/state.rs	AppState::emote_catalog — the shared catalog every request handler reads through
apps/api/src/workers/youtube.rs	Observation writer + both chat-ingest readers
apps/api/src/workers/trovo.rs	Trovo message enrichment from the catalog
apps/api/src/services/history/service.rs	Report read-time image fallback (backfill_emote_urls)
apps/api/src/services/youtube_memberships.rs	GDPR member erasure (membership + chat PII); leaves the catalog intact
apps/api/tests/chat_emote_catalog.rs	Phase 1 integration tests (#[sqlx::test])
apps/api/tests/emote_catalog_writers.rs	Phase 2 integration tests: API-fetch writer + outage fallback (#[sqlx::test])
apps/api/tests/emote_report_fallback.rs	Phase 3 integration tests: read-time report image fallback incl. fail-open (#[sqlx::test])

Testing

apps/api/tests/chat_emote_catalog.rs runs against the ephemeral Postgres #[sqlx::test] provisions. common::offline_redis() points at a refused port and is fail-open, so tier 2 always misses and every channel_map call exercises the cold-cache → Postgres fallback — which doubles as the standing proof that emote lookup survives a full Redis flush.

Covered: upsert idempotency, URL drift, in-batch duplicates, empty batches, renamed emotes, NULL-channel global rows, the cold read-through, the LRU hit, write invalidation, platform and channel scoping, a fail-open Postgres error, and the ZAF-580 ruling that a GDPR member erasure leaves the catalog intact.

apps/api/tests/emote_report_fallback.rs covers the Phase 3 read-time fallback: resolve_emote_urls matching on (provider, name), most-recent-row wins across channels, unknown pairs absent, backfill_emote_urls filling a missing url while leaving a frozen snapshot untouched, and the fail-open branch (an unreachable catalog leaves the report unchanged rather than failing it). The Kick normaliser is unit-tested in apps/api/src/routes/webhooks.rs (span derivation, catalog-miss name-without-url, multibyte content, out-of-range spans) and the Trovo/word-scan enricher in crates/lo-chat/src/emotes.rs. The frontend URL-freshness precedence is covered by apps/web/__tests__/components/message-content-emote-url.test.tsx.