[PR #23853] refac/perf/security: Migrate chat tags to chat_tag association table #43039

New Issue

GiteaMirror · 2026-04-25T14:45:21-05:00

GiteaMirror commented

2026-04-25 14:45:21 -05:00

📋 Pull Request Information

Original PR: https://github.com/open-webui/open-webui/pull/23853
Author: @Classic298
Created: 4/17/2026
Status: 🔄 Open

Base: dev ← Head: chat_tag_migration

📝 Commits (1)

2eb6328 refac: Re-normalize chat tags into chat_tag association table

📊 Changes

6 files changed (+848 additions, -221 deletions)

View changed files

📝 backend/open_webui/internal/db.py (+65 -0)
➕ backend/open_webui/migrations/versions/17a6d37e23d2_add_chat_tag_table.py (+363 -0)
📝 backend/open_webui/models/chats.py (+315 -155)
📝 backend/open_webui/models/tags.py (+44 -17)
📝 backend/open_webui/routers/analytics.py (+19 -12)
📝 backend/open_webui/routers/chats.py (+42 -37)

📄 Description

Summary

Replaces the denormalized chat.meta['tags'] JSON array with a dedicated
chat_tag(chat_id, tag_id, user_id) association table. Tags are now fully
relational: dialect-agnostic queries, FK-enforced tag → chat_tag, index-
backed list/count, and no more json_each / json_array_elements_text
branches for every read path.

What's changing

New table + migration (17a6d37e23d2_add_chat_tag_table)

chat_tag with composite PK (chat_id, tag_id, user_id), composite
FK to tag(id, user_id), FK to chat(id), both ON DELETE CASCADE.
Single secondary index chat_tag_user_tag_idx (user_id, tag_id);
chat_id lookups are covered by the PK's leading column.
Paginated keyset backfill (chat.id > last_chat_id) with per-dialect
bulk upsert (ON CONFLICT DO NOTHING), so concurrent re-runs / partial
replays are safe.
Shared-chat snapshots (user_id='shared-{chat_id}') are filtered from
the backfill — tags are personal organization metadata and don't
propagate to public snapshots.
After backfill, chat.meta['tags'] is stripped per page so reads of
ChatModel.meta no longer leak stale arrays.
Downgrade reconstructs meta['tags'] from a chat_tag ⋈ tag join
(preserves display casing; order loss is documented).

Model layer

New ChatTag ORM model; new helpers get_chat_tag_ids_by_id_and_user_id
and ..._by_chat_ids_and_user_id (batch).
update_chat_tags_by_id takes SELECT ... FOR UPDATE on the chat row
and diffs to_add / to_remove instead of delete-all / insert-all.
add_chat_tag_by_id_and_user_id_and_tag_name likewise locks the chat
row and uses dialect INSERT ... ON CONFLICT DO NOTHING.
ensure_tags_exist gains commit=False + dedupes on normalized id;
rewritten over insert_all_on_conflict_nothing (closes the pre-
existing TOCTOU race).
delete_orphan_tags_for_user replaces the N+1 count loop with one
GROUP BY query (and counts across archived chats — see below).
Analytics top-tags N+1 (per-chat meta scan) collapsed to one
aggregate ChatTag query, chunked for SQLite bind limits.
Tag-filter rewrite in get_chats_by_user_id_and_search_text: dialect-
specific JSON-path branches replaced with portable EXISTS subqueries.
Shared insert_on_conflict_nothing / insert_all_on_conflict_nothing
utilities in internal/db.py, dialect-aware row-batch sizing that
respects SQLite < 3.32's 999-bind limit (scales by column width).
Centralized normalize_tag_id + RESERVED_TAG_ID_NONE in
models/tags.py; killed eight inline replace(' ', '_').lower() sites.

Authorization tightening

update_chat_tags_by_id and add_chat_tag_* now reject when
chat.user_id != user.id. Was previously missing.
Admin chat-delete flow: delete_orphan_tags_for_user now scopes to
chat.user_id (was scoped to the admin's id, so chat-owner orphans
were never cleaned up).

Behavior changes to flag

chat.meta['tags'] is no longer returned by any endpoint — clients
must use the existing /chats/{id}/tags etc. endpoints.
Shared-chat snapshots no longer expose the origin chat's tags. Pre-PR
this leaked via the meta JSON blob copy; not considered intentional.
delete_orphan_tags_for_user counts across archived chats. Without
this, the chat_tag FK ON DELETE CASCADE would destroy archived
chats' tag associations the next time any non-archived chat dropped
the tag.
Tag.__table_args__ pre-existing double-assignment bug fixed — the
user_id_idx is now actually declared at the ORM level (existing DBs
already have the index from the 2024 migration).
ensure_tags_exist(commit=False, db=None) now raises instead of
silently discarding the insert.

Contributor License Agreement

By submitting this pull request, I confirm that I have read and fully agree to the Contributor License Agreement (CLA), and I am providing my contributions under its terms.

Note

Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/open-webui/open-webui/pull/23853 **Author:** [@Classic298](https://github.com/Classic298) **Created:** 4/17/2026 **Status:** 🔄 Open **Base:** `dev` ← **Head:** `chat_tag_migration` --- ### 📝 Commits (1) - [`2eb6328`](https://github.com/open-webui/open-webui/commit/2eb632853c6db4690fa0c64965708176282aedee) refac: Re-normalize chat tags into chat_tag association table ### 📊 Changes **6 files changed** (+848 additions, -221 deletions) <details> <summary>View changed files</summary> 📝 `backend/open_webui/internal/db.py` (+65 -0) ➕ `backend/open_webui/migrations/versions/17a6d37e23d2_add_chat_tag_table.py` (+363 -0) 📝 `backend/open_webui/models/chats.py` (+315 -155) 📝 `backend/open_webui/models/tags.py` (+44 -17) 📝 `backend/open_webui/routers/analytics.py` (+19 -12) 📝 `backend/open_webui/routers/chats.py` (+42 -37) </details> ### 📄 Description ## Summary Replaces the denormalized `chat.meta['tags']` JSON array with a dedicated `chat_tag(chat_id, tag_id, user_id)` association table. Tags are now fully relational: dialect-agnostic queries, FK-enforced tag → chat_tag, index- backed list/count, and no more `json_each` / `json_array_elements_text` branches for every read path. ## What's changing **New table + migration** (`17a6d37e23d2_add_chat_tag_table`) - `chat_tag` with composite PK `(chat_id, tag_id, user_id)`, composite FK to `tag(id, user_id)`, FK to `chat(id)`, both `ON DELETE CASCADE`. - Single secondary index `chat_tag_user_tag_idx (user_id, tag_id)`; `chat_id` lookups are covered by the PK's leading column. - Paginated keyset backfill (`chat.id > last_chat_id`) with per-dialect bulk upsert (`ON CONFLICT DO NOTHING`), so concurrent re-runs / partial replays are safe. - Shared-chat snapshots (`user_id='shared-{chat_id}'`) are filtered from the backfill — tags are personal organization metadata and don't propagate to public snapshots. - After backfill, `chat.meta['tags']` is stripped per page so reads of `ChatModel.meta` no longer leak stale arrays. - Downgrade reconstructs `meta['tags']` from a `chat_tag` ⋈ `tag` join (preserves display casing; order loss is documented). **Model layer** - New `ChatTag` ORM model; new helpers `get_chat_tag_ids_by_id_and_user_id` and `..._by_chat_ids_and_user_id` (batch). - `update_chat_tags_by_id` takes `SELECT ... FOR UPDATE` on the chat row and diffs `to_add` / `to_remove` instead of delete-all / insert-all. - `add_chat_tag_by_id_and_user_id_and_tag_name` likewise locks the chat row and uses dialect `INSERT ... ON CONFLICT DO NOTHING`. - `ensure_tags_exist` gains `commit=False` + dedupes on normalized id; rewritten over `insert_all_on_conflict_nothing` (closes the pre- existing TOCTOU race). - `delete_orphan_tags_for_user` replaces the N+1 count loop with one `GROUP BY` query (and counts across archived chats — see below). - Analytics top-tags N+1 (per-chat `meta` scan) collapsed to one aggregate `ChatTag` query, chunked for SQLite bind limits. - Tag-filter rewrite in `get_chats_by_user_id_and_search_text`: dialect- specific JSON-path branches replaced with portable `EXISTS` subqueries. - Shared `insert_on_conflict_nothing` / `insert_all_on_conflict_nothing` utilities in `internal/db.py`, dialect-aware row-batch sizing that respects SQLite < 3.32's 999-bind limit (scales by column width). - Centralized `normalize_tag_id` + `RESERVED_TAG_ID_NONE` in `models/tags.py`; killed eight inline `replace(' ', '_').lower()` sites. **Authorization tightening** - `update_chat_tags_by_id` and `add_chat_tag_*` now reject when `chat.user_id != user.id`. Was previously missing. - Admin chat-delete flow: `delete_orphan_tags_for_user` now scopes to `chat.user_id` (was scoped to the admin's id, so chat-owner orphans were never cleaned up). **Behavior changes to flag** - `chat.meta['tags']` is no longer returned by any endpoint — clients must use the existing `/chats/{id}/tags` etc. endpoints. - Shared-chat snapshots no longer expose the origin chat's tags. Pre-PR this leaked via the `meta` JSON blob copy; not considered intentional. - `delete_orphan_tags_for_user` counts across archived chats. Without this, the chat_tag FK `ON DELETE CASCADE` would destroy archived chats' tag associations the next time any non-archived chat dropped the tag. - `Tag.__table_args__` pre-existing double-assignment bug fixed — the `user_id_idx` is now actually declared at the ORM level (existing DBs already have the index from the 2024 migration). - `ensure_tags_exist(commit=False, db=None)` now raises instead of silently discarding the insert. ### Contributor License Agreement  - [x] By submitting this pull request, I confirm that I have read and fully agree to the [Contributor License Agreement (CLA)](https://github.com/open-webui/open-webui/blob/main/CONTRIBUTOR_LICENSE_AGREEMENT), and I am providing my contributions under its terms. > [!NOTE] > Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

GiteaMirror added the pull-request label 2026-04-25 14:45:21 -05:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/open-webui#43039