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).
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
<!--
🚨 DO NOT DELETE THE TEXT BELOW 🚨
Keep the "Contributor License Agreement" confirmation text intact.
Deleting it will trigger the CLA-Bot to INVALIDATE your PR.
Your PR will NOT be reviewed or merged until you check the box below confirming that you have read and agree to the terms of the CLA.
-->
- [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>
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
📋 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)
2eb6328refac: 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 dedicatedchat_tag(chat_id, tag_id, user_id)association table. Tags are now fullyrelational: dialect-agnostic queries, FK-enforced tag → chat_tag, index-
backed list/count, and no more
json_each/json_array_elements_textbranches for every read path.
What's changing
New table + migration (
17a6d37e23d2_add_chat_tag_table)chat_tagwith composite PK(chat_id, tag_id, user_id), compositeFK to
tag(id, user_id), FK tochat(id), bothON DELETE CASCADE.chat_tag_user_tag_idx (user_id, tag_id);chat_idlookups are covered by the PK's leading column.chat.id > last_chat_id) with per-dialectbulk upsert (
ON CONFLICT DO NOTHING), so concurrent re-runs / partialreplays are safe.
user_id='shared-{chat_id}') are filtered fromthe backfill — tags are personal organization metadata and don't
propagate to public snapshots.
chat.meta['tags']is stripped per page so reads ofChatModel.metano longer leak stale arrays.meta['tags']from achat_tag⋈tagjoin(preserves display casing; order loss is documented).
Model layer
ChatTagORM model; new helpersget_chat_tag_ids_by_id_and_user_idand
..._by_chat_ids_and_user_id(batch).update_chat_tags_by_idtakesSELECT ... FOR UPDATEon the chat rowand diffs
to_add/to_removeinstead of delete-all / insert-all.add_chat_tag_by_id_and_user_id_and_tag_namelikewise locks the chatrow and uses dialect
INSERT ... ON CONFLICT DO NOTHING.ensure_tags_existgainscommit=False+ dedupes on normalized id;rewritten over
insert_all_on_conflict_nothing(closes the pre-existing TOCTOU race).
delete_orphan_tags_for_userreplaces the N+1 count loop with oneGROUP BYquery (and counts across archived chats — see below).metascan) collapsed to oneaggregate
ChatTagquery, chunked for SQLite bind limits.get_chats_by_user_id_and_search_text: dialect-specific JSON-path branches replaced with portable
EXISTSsubqueries.insert_on_conflict_nothing/insert_all_on_conflict_nothingutilities in
internal/db.py, dialect-aware row-batch sizing thatrespects SQLite < 3.32's 999-bind limit (scales by column width).
normalize_tag_id+RESERVED_TAG_ID_NONEinmodels/tags.py; killed eight inlinereplace(' ', '_').lower()sites.Authorization tightening
update_chat_tags_by_idandadd_chat_tag_*now reject whenchat.user_id != user.id. Was previously missing.delete_orphan_tags_for_usernow scopes tochat.user_id(was scoped to the admin's id, so chat-owner orphanswere never cleaned up).
Behavior changes to flag
chat.meta['tags']is no longer returned by any endpoint — clientsmust use the existing
/chats/{id}/tagsetc. endpoints.this leaked via the
metaJSON blob copy; not considered intentional.delete_orphan_tags_for_usercounts across archived chats. Withoutthis, the chat_tag FK
ON DELETE CASCADEwould destroy archivedchats' tag associations the next time any non-archived chat dropped
the tag.
Tag.__table_args__pre-existing double-assignment bug fixed — theuser_id_idxis now actually declared at the ORM level (existing DBsalready have the index from the 2024 migration).
ensure_tags_exist(commit=False, db=None)now raises instead ofsilently discarding the insert.
Contributor License Agreement
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.