Syncing Jellyfin watch history to Tunarr tags (for Smart Collections)

[joopdo]

Hey all,

I wanted to share something I've been working on that others might find useful.

The use case: I want to create Smart Collections based on watch history. For example, a "Simpsons - Watched" collection that only contains episodes I've already seen, so I can build a channel from those.

My setup: I use local media files as my Tunarr source (not Jellyfin/Plex as a media source) to avoid unnecessary transcoding. But I do use Jellyfin as my primary player, so that's where my watch history lives. The two systems don't talk to each other natively.

The problem: Tunarr's Smart Collections use the Meilisearch index for filtering, but the tags field in the index only gets populated during media ingestion. If you add tags directly to the SQLite database (via tag_relations), they show up in the API (/api/programs/{id} returns "tags": ["watched"]) but the search index doesn't know about them. So tags in ["watched"] returns 0 results in a Smart Collection.

The solution: After writing tags to the database, you can also update the Meilisearch index directly using a PUT request to the embedded Meilisearch instance. The key insight is that PUT to /indexes/programs/documents does a partial update (only changes the fields you send, keeps everything else intact), while POST does a full replace (which will destroy your documents if you only send {id, tags}). Learned that one the hard way.

I wrote a script that does the full sync. It also handles the reverse: when you mark something as unwatched in Jellyfin, the tag gets removed from both the DB and the search index on the next run. I run it on a cron every hour or so.

Some things I learned along the way that might save others some pain:

• Tags in the index are plain strings (same format as the folder-path tags that local media gets)
• The tag_relations table needs explicit columns: (tag_id, program_id, grouping_id, source) with grouping_id = NULL and source = 'media'. If you skip grouping_id, the columns shift and things break silently.
• Use PUT (not POST!) to /indexes/programs/documents?primaryKey=id for partial updates
• The Meilisearch port can be found via /proc/*/cmdline inside the container (it's random on each start)

Here's the script for anyone interested:

sync-watched.sh (click to expand)

#!/bin/bash
set -euo pipefail

# =============================================================================
# sync-watched.sh
#
# Syncs ALL watched TV episodes from Jellyfin to Tunarr:
# 1. Tags watched episodes with "watched" in SQLite
# 2. Removes "watched" tag from episodes no longer marked as watched
# 3. Updates Meilisearch index (PUT = partial update, preserves other fields)
#
# Run from the host where docker runs.
# =============================================================================

JELLYFIN_URL="http://your-jellyfin:8096"
API_KEY="your-jellyfin-api-key"
USER_ID="your-jellyfin-user-id"

TUNARR_DB="/opt/tunarr/tunarr-data/db.db"
TUNARR_CONTAINER="tunarr"

TAG_UUID="dc9f3931-d18d-4ecf-935a-2693cb6da50c"
TAG_NAME="watched"

TMP_DIR="/tmp/sync-watched"
mkdir -p "$TMP_DIR"

echo "========================================"
echo " Sync Watched Episodes (All Shows)"
echo " Jellyfin -> Tunarr DB + Search Index"
echo "========================================"
echo ""

# --- Step 0: Detect Meilisearch port ---
echo "Detecting Meilisearch port..."

MS_PORT=$(docker exec "$TUNARR_CONTAINER" sh -c \
  "cat /proc/*/cmdline 2>/dev/null | tr '\0' ' ' | grep -o 'http-addr localhost:[0-9]*' | grep -o '[0-9]*$' | head -1" \
  2>/dev/null || true)

if [ -z "$MS_PORT" ]; then
    for port in $(seq 7700 7720) $(seq 35000 35100) $(seq 40000 46000); do
        if docker exec "$TUNARR_CONTAINER" wget -qO- "http://localhost:${port}/health" 2>/dev/null | grep -q "available"; then
            MS_PORT=$port
            break
        fi
    done
fi

if [ -z "$MS_PORT" ]; then
    echo "FATAL: Cannot find Meilisearch port. Is Tunarr running?"
    exit 1
fi

echo "Meilisearch on port: ${MS_PORT}"
echo ""

# --- Step 1: Ensure tag exists ---
sqlite3 "$TUNARR_DB" "INSERT OR IGNORE INTO tags (uuid, tag) VALUES ('${TAG_UUID}', '${TAG_NAME}');"

# --- Step 2: Get all TV shows from Jellyfin that exist in Tunarr ---
echo "Fetching TV shows from Jellyfin..."

SHOWS_JSON=$(curl -fsS "${JELLYFIN_URL}/Users/${USER_ID}/Items?IncludeItemTypes=Series&Recursive=true&Fields=ProviderIds&api_key=${API_KEY}")
SHOW_COUNT=$(echo "$SHOWS_JSON" | jq '.Items | length')
echo "Found ${SHOW_COUNT} shows in Jellyfin"
echo ""

# --- Step 3: Build the full watched episode list ---
echo "Collecting watched episodes across all shows..."

> "$TMP_DIR/all_watched_uuids.txt"

echo "$SHOWS_JSON" | jq -r '.Items[] | "\(.Id) \(.Name)"' | while IFS= read -r line; do
    SHOW_ID=$(echo "$line" | cut -d' ' -f1)
    SHOW_NAME=$(echo "$line" | cut -d' ' -f2-)

    # Get watched episodes for this show
    WATCHED_EPS=$(curl -fsS \
      "${JELLYFIN_URL}/Shows/${SHOW_ID}/Episodes?userId=${USER_ID}&Fields=UserData&api_key=${API_KEY}" 2>/dev/null \
      | jq -r '.Items[] | select(.UserData.Played == true) | "\(.ParentIndexNumber) \(.IndexNumber)"' 2>/dev/null || true)

    [ -z "$WATCHED_EPS" ] && continue

    # Try to find this show in Tunarr by name
    TUNARR_SHOW_UUID=$(sqlite3 "$TUNARR_DB" "
      SELECT DISTINCT tv_show_uuid FROM program
      WHERE show_title = '$(echo "$SHOW_NAME" | sed "s/'/''/g")'
      LIMIT 1;
    " 2>/dev/null || true)

    [ -z "$TUNARR_SHOW_UUID" ] && continue

    # Match episodes
    MATCHED=0
    while read -r season episode; do
        [ -z "${season:-}" ] && continue
        [ -z "${episode:-}" ] && continue

        PROG_UUID=$(sqlite3 "$TUNARR_DB" "
          SELECT uuid FROM program
          WHERE tv_show_uuid = '${TUNARR_SHOW_UUID}'
            AND season_number = ${season}
            AND episode = ${episode}
          LIMIT 1;
        " 2>/dev/null || true)

        if [ -n "$PROG_UUID" ]; then
            echo "$PROG_UUID" >> "$TMP_DIR/all_watched_uuids.txt"
            MATCHED=$((MATCHED + 1))
        fi
    done <<< "$WATCHED_EPS"

    if [ "$MATCHED" -gt 0 ]; then
        echo "  ${SHOW_NAME}: ${MATCHED} episodes matched"
    fi
done

TOTAL_WATCHED=$(wc -l < "$TMP_DIR/all_watched_uuids.txt" | tr -d ' ')
echo ""
echo "Total watched episodes matched in Tunarr: ${TOTAL_WATCHED}"
echo ""

# --- Step 4: Determine what to add and what to remove ---
echo "Calculating diff..."

sqlite3 "$TUNARR_DB" "
  SELECT program_id FROM tag_relations WHERE tag_id = '${TAG_UUID}' AND program_id IS NOT NULL;
" > "$TMP_DIR/currently_tagged.txt"

sort -u "$TMP_DIR/all_watched_uuids.txt" > "$TMP_DIR/watched_sorted.txt"
sort -u "$TMP_DIR/currently_tagged.txt" > "$TMP_DIR/tagged_sorted.txt"

# New: in watched but not yet tagged
comm -23 "$TMP_DIR/watched_sorted.txt" "$TMP_DIR/tagged_sorted.txt" > "$TMP_DIR/to_add.txt"
TO_ADD=$(wc -l < "$TMP_DIR/to_add.txt" | tr -d ' ')

# Removed: tagged but no longer watched
comm -13 "$TMP_DIR/watched_sorted.txt" "$TMP_DIR/tagged_sorted.txt" > "$TMP_DIR/to_remove.txt"
TO_REMOVE=$(wc -l < "$TMP_DIR/to_remove.txt" | tr -d ' ')

echo "  To add   : ${TO_ADD}"
echo "  To remove: ${TO_REMOVE}"
echo ""

# --- Step 5: Update SQLite ---
if [ "$TO_ADD" -gt 0 ] || [ "$TO_REMOVE" -gt 0 ]; then
    echo "Updating database..."

    SQL_FILE="$TMP_DIR/update.sql"
    echo "BEGIN TRANSACTION;" > "$SQL_FILE"

    while read -r UUID; do
        [ -z "$UUID" ] && continue
        echo "INSERT OR IGNORE INTO tag_relations (tag_id, program_id, grouping_id, source) VALUES ('${TAG_UUID}', '${UUID}', NULL, 'media');" >> "$SQL_FILE"
    done < "$TMP_DIR/to_add.txt"

    while read -r UUID; do
        [ -z "$UUID" ] && continue
        echo "DELETE FROM tag_relations WHERE tag_id = '${TAG_UUID}' AND program_id = '${UUID}';" >> "$SQL_FILE"
    done < "$TMP_DIR/to_remove.txt"

    echo "COMMIT;" >> "$SQL_FILE"
    sqlite3 "$TUNARR_DB" < "$SQL_FILE"
    echo "  Done."
else
    echo "No changes needed."
fi
echo ""

# --- Step 6: Update Meilisearch index ---
UUIDS_TO_UPDATE="$TMP_DIR/index_update.txt"
cat "$TMP_DIR/to_add.txt" "$TMP_DIR/to_remove.txt" | sort -u > "$UUIDS_TO_UPDATE"
INDEX_UPDATE_COUNT=$(wc -l < "$UUIDS_TO_UPDATE" | tr -d ' ')

if [ "$INDEX_UPDATE_COUNT" -gt 0 ]; then
    echo "Updating Meilisearch index (${INDEX_UPDATE_COUNT} documents)..."

    BATCH_SIZE=50
    BATCH_NUM=0

    while IFS= read -r uuid_batch; do
        [ -z "$uuid_batch" ] && continue

        JSON_DOCS="["
        FIRST=true

        for UUID in $uuid_batch; do
            # Get ALL current tags for this program from DB
            TAGS=$(sqlite3 "$TUNARR_DB" "
              SELECT t.tag FROM tags t
              JOIN tag_relations tr ON t.uuid = tr.tag_id
              WHERE tr.program_id = '${UUID}';
            " | jq -R . | jq -s .)

            if [ "$FIRST" = true ]; then
                FIRST=false
            else
                JSON_DOCS="${JSON_DOCS},"
            fi
            JSON_DOCS="${JSON_DOCS}{\"id\":\"${UUID}\",\"tags\":${TAGS}}"
        done

        JSON_DOCS="${JSON_DOCS}]"

        # PUT = partial update (only updates tags, keeps everything else)
        RESULT=$(docker exec -i "$TUNARR_CONTAINER" sh -c "
          if command -v curl >/dev/null 2>&1; then
            curl -s -X PUT 'http://localhost:${MS_PORT}/indexes/programs/documents?primaryKey=id' \
              -H 'Content-Type: application/json' \
              --data-raw '${JSON_DOCS}'
          else
            wget -qO- --method=PUT \
              --header='Content-Type: application/json' \
              --body-data='${JSON_DOCS}' \
              'http://localhost:${MS_PORT}/indexes/programs/documents?primaryKey=id'
          fi
        " 2>/dev/null || true)

        if echo "$RESULT" | grep -q "taskUid"; then
            BATCH_NUM=$((BATCH_NUM + 1))
            echo "  Batch ${BATCH_NUM} queued"
        else
            echo "  WARNING: Batch failed: ${RESULT}"
        fi

    done < <(xargs -n "$BATCH_SIZE" < "$UUIDS_TO_UPDATE")

    # Wait for indexing
    sleep 2
    for i in $(seq 1 15); do
        PENDING=$(docker exec "$TUNARR_CONTAINER" sh -c "
          if command -v curl >/dev/null 2>&1; then
            curl -s 'http://localhost:${MS_PORT}/tasks?statuses=enqueued,processing&indexUids=programs&limit=1'
          else
            wget -qO- 'http://localhost:${MS_PORT}/tasks?statuses=enqueued,processing&indexUids=programs&limit=1'
          fi
        " 2>/dev/null || true)

        if echo "$PENDING" | grep -q '"total":0'; then
            echo "  Indexing complete."
            break
        fi
        sleep 1
    done
else
    echo "No search index updates needed."
fi
echo ""

# --- Summary ---
FINAL_COUNT=$(sqlite3 "$TUNARR_DB" "SELECT COUNT(*) FROM tag_relations WHERE tag_id = '${TAG_UUID}';")
echo "Total episodes tagged 'watched': ${FINAL_COUNT}"
echo "Use in Smart Collection: tags in [\"watched\"]"

rm -rf "$TMP_DIR"

Works on Tunarr 1.2.20 with the embedded Meilisearch 1.30. Requires jq, sqlite3, and curl on the host.