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.

The 7TV EventAPI listener is a writer for added and renamed channel emotes. It upserts those rows and invalidates the channel map, while clearing the assembled channel hot cache for every emote-set change. Removal does not delete catalog rows: the upsert-only catalog may continue to resolve that shortcode until its cached map expires. The hot-cache six-hour TTL is a fallback for missed events, not the normal delay before a newly available emote appears in the picker.

Why Postgres owns this

Before the catalog, every shortcode → image-URL mapping lived only in Redis (lumio:emotes:{platform}:{channel_id}, today versioned as lumio:emotes:v4:{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 and CI run postgres:18). 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.

Catalog writers

Writer	source	channel_id	Entry point
Emote-set API fetch - the emotes query fetching Twitch/YouTube/Kick/7TV/FFZ/BTTV 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
7TV EventAPI listener - added and renamed channel emotes	api_fetch	every connected channel using the active set	workers::seventv_events → upsert_and_invalidate

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.

Kick writer. For platform = "kick" the fetch also writes Kick's native sets from the undocumented kick.com/emotes/{slug} (services::emotes::fetch_kick_emotes): the channel set with channel_id = the Kick broadcaster id, Kick's Global and Emojis sets with channel_id = NULL, all provider = "kick", source = api_fetch, URL https://files.kick.com/emotes/{id}/fullsize. The slug comes from channel_connections.channel_login (fallback: official GET /public/v1/channels?broadcaster_user_id=). The catalog does not store subscribers_only or the set section: subscribers_only exists only in a fresh fetch response, and the section of a catalog-rebuilt Kick row is derived from its scope (see below). The fetch is fail-open — a blocked or changed endpoint writes nothing and leaves the catalog as it was.

Every Kick fetch outcome is counted in chat_emote_set_fetch_total{platform="kick",outcome} with outcome one of ok, empty, not_found, challenge, unrecognised, error, no_slug, and every non-ok outcome is also logged at warn with the slug and HTTP status (the slug and status stay in the log, never in a metric label). challenge means Cloudflare answered instead of Kick: a response flagged cf-mitigated: challenge, or an HTML page served with 200. A run of challenge (or error with status 403) on the production host means Kick's bot protection is blocking the server's IP.

Channel-set rows carry owner_id = "kick:<user id>", the broadcaster from the set's user object, namespaced so a Kick owner can never collide with a numeric Twitch owner id. Global/Emojis rows have owner_id = NULL. The catalog does not store the owner's name or avatar: those are only on the fresh fetch (and in the Redis hot cache with it), so a response rebuilt from the catalog returns owners: null and the emote library falls back to its generic "Kick Channel" label.

Kick has no counterpart to Twitch's chat/emotes/user (the emotes of every channel the viewer is subscribed to). The Kick website gets that list through the viewer's session cookie, and Lumio never sends its OAuth token to undocumented Kick hosts, so no writer adds other channels' Kick emotes.

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, at two granularities (services::emotes::assemble_channel_emotes):

What failed	Served	Hot-cache TTL
Nothing	the fresh fetch	6 h
Only the platform-native provider (Kick's own sets; Twitch Helix channel or global)	that provider's catalog rows via emote_catalog::load_channel_provider_emote_sets(db, platform, channel_id, provider), first in order, replacing whatever that provider returned, plus the fresh third-party sets	5 min
Every provider (a total upstream outage)	the whole catalog via emote_catalog::load_channel_emote_sets(db, platform, channel_id)	5 min
The catalog read itself	whatever was fetched	not cached

Both loaders return the channel's rows plus the platform's global (channel_id IS NULL) rows, grouped by provider into the full EmoteSet shape. This is what stops an outage from wiping a channel's emote map - including the common case where only Kick's undocumented endpoint is blocked while 7TV, BTTV and FFZ still answer, which previously dropped every Kick set from the menu.

Catalog-rebuilt Kick rows get their menu section back from the scope: a channel-scoped row is channel; a global row is emojis when its name starts with emoji (every entry of Kick's Emoji set does, e.g. emojiAngel) and global otherwise (emote_catalog::kick_catalog_section). So the menu keeps its Channel / Kick Global / Kick Emojis sections in degraded mode too.

A degraded response is cached for 5 minutes, not 6 hours, so a transient failure (a Cloudflare challenge, a timeout) is retried within minutes instead of being pinned. Like every catalog path it is fail-open: a Postgres error degrades to what was fetched and is never pinned in the hot cache.

The hot-cache key is versioned

The assembled response is cached in Redis under lumio:emotes:{version}:{platform}:{channel_id} (currently v2, EMOTE_CACHE_VERSION in services/emotes.rs). Bump the version whenever the cached Vec<EmoteSet> changes shape or content rules - a new field, a provider added to or removed from a platform, a new section. Nothing else replaces a present key before its TTL runs out, and no user-facing control can bust it (the emote library's Refresh clears only the browser's localStorage), so an unversioned key keeps serving the previous build's response for up to 6 hours after a deploy.

Read paths beyond ingest (ZAF-578)

Two 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 send (services::chat_send::send_kick)	load_channel_provider_emote_sets(db, "kick", broadcaster_id, Kick)	Kick renders an emote only from [emote:ID:NAME] markup, so each outgoing word matching a known Kick emote name is converted before sending. Once per send, not per message ingest.
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 ingest needs no catalog. Kick's webhook carries each emote inline as [emote:ID:NAME] in content, so services::kick_emotes::extract_kick_emotes builds {id, name, url, provider: "kick", animated} per occurrence straight from the marker, with the URL frozen from the id — no catalog lookup on the ingest path. Kick emotes therefore always carry a name and a url and rank in db::top_emotes like Twitch's.
• 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, 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, 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.

What Art. 17 does reach is erased in full, fatal-on-failure: Lumio's cached YouTube membership rows for the member (Redis, across every account) and, in the instructing account only, the member's chat PII (message text, identity, and badge/emote JSONB in platform_chat_messages) and the rest of their channel data (see Chat › Erasure).

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 / load_channel_provider_emote_sets, kick_catalog_section, 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
apps/api/src/services/emotes.rs	Emote-set API-fetch writer + outage fallback reader (resolve_channel_emotes, assemble_channel_emotes), shared by the REST route and GraphQL resolver
apps/api/src/services/kick_emotes.rs	Kick [emote:ID:NAME] parser (ingest) and encoder (send), Kick send-path catalog read
apps/api/tsdb_migrations/20260930000001_kick_chat_emote_markup_backfill.{up,down}.sql	One-off rewrite of Kick rows stored before the parser
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/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 (common::test_db())
apps/api/tests/emote_catalog_writers.rs	Phase 2 integration tests: API-fetch writer + outage and per-provider (native) fallback, degraded TTL, Kick section restore (common::test_db())
apps/api/tests/emote_report_fallback.rs	Phase 3 integration tests: read-time report image fallback incl. fail-open (common::test_db())

Testing

apps/api/tests/chat_emote_catalog.rs runs against the per-test Postgres database common::test_db() clones from the migrated template. In agent runs, the injected DATABASE_URL selects the host test Postgres; run cargo test -p lumio-api --test chat_emote_catalog. common::offline_redis() points at a refused connection 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 parser and encoder are unit-tested in apps/api/src/services/kick_emotes.rs (Kick's doc example verbatim incl. the inclusive positions, back-to-back identical emotes, multibyte text, malformed markers, no-emote messages, send-side conversion); the backfill migration in apps/api/tests/tsdb_kick_emote_backfill.rs (TimescaleDB, gated on RUN_TSDB_MIGRATION_TEST; asserts SQL output == Rust parser output). The frontend URL-freshness precedence is covered by apps/web/__tests__/components/message-content-emote-url.test.tsx.