Design a product-agnostic tagging system for several products

Questions first: are tags free text typed by users, or managed in a list by admins? Are tags scoped to an organization, or shared across organizations? Does a tag have attributes (color, description)? Do users need AND, OR and NOT queries? Who owns permissions, the tag service or each product? Is rename or merge needed? How quickly must a new tag appear in search?

Assumed scope: tags scoped to one organization (tenant), free-text creation, multi-tag AND and OR search, autocomplete, rename and merge. The product owns its items and its permissions. Out of scope: tag hierarchies, machine-suggested tags, and cross-organization sharing.

Planning figures for the exercise, not facts about any real product.

• Edges: 1 billion items × 3 tags = 3 billion item-tag rows. At 60 B each that is 180 GB; stored in two directions (item to tags, tag to items) it is 360 GB. That fits a handful of shards with room to grow.
• Tag edits: 20 million a day ÷ 86,400 = 231 a second; a 5× peak is about 1,160 a second. Each edit writes two edge rows plus one outbox row, so about 3,500 row writes a second at peak: under 5,000, but not by much, so plan to shard by tenant.
• Item views that show tags: 500 million a day ÷ 86,400 = 5,787 a second; at 5× that is about 29,000 lookups a second, above the 20,000 line. A cache with a 90% hit rate leaves 2,900 a second on the database.
• Tag searches: 50 million a day = 579 a second, about 2,900 at peak.
• Tag catalogue: 100,000 organizations × 500 tags × 100 B = 5 GB. One organization's tag list is about 50 KB (500 × 100 B), so suggestions can be served from a per-tenant cache or even by the browser.

The conclusion: writes are modest, item-to-tags reads need a cache and a bulk API, and the catalogue is tiny. The hard part is permissions and multi-tag search, not volume.

Data model.

• tags(tenant_id, tag_id, name_norm, display_name, created_by, archived), unique on (tenant_id, name_norm). name_norm is the trimmed, case-folded, Unicode-normalized name, so "Backend" and "backend " are one tag.
• item_tags(tenant_id, item_key, tag_id, scope_id, added_by, added_at), primary key (tenant_id, item_key, tag_id): the item to tags direction. item_key is an opaque string made of product, type and the product's own id, so the service never needs a product's schema. scope_id is the coarse container the product reports (project, space, repository).
• tag_items(tenant_id, tag_id, added_at, item_key, scope_id), the tag to items direction, written in the same transaction.

Edges refer to tag_id, not the name, so renaming a tag changes one row. Shard by tenant_id: every query is inside one tenant, and a tenant is a natural isolation unit. A directory table maps very large tenants to their own shard.

Write path. PUT /items/{item_key}/tags/{tag} is idempotent by its natural key. The service resolves or creates the tag (upsert on (tenant_id, name_norm)), inserts both edge rows and an outbox row in one transaction, and enforces limits (for example 50 tags per item, and a per-user rate limit). The outbox feeds the search index and gives products a change event. Remove is a delete of the same rows.

Read paths.

1. Tags for items: a bulk call, POST /tags/lookup with up to 50 item keys, so a list page makes one call instead of fifty. Results come from a cache keyed by (tenant_id, item_key); a write deletes the key after commit, with a short TTL as a backstop.
2. Items for one tag: a range scan on tag_items, returned with a cursor on (added_at, item_key), not an offset.
3. Multi-tag AND/OR and text: served by a search index (one document per item with a tags keyword field, routed by tenant) fed from the outbox, as in the search chapter. Intersecting two postings lists in the relational store is fine for rare tags and bad for a tag on millions of items.
4. Suggestions: take the tenant's tag list from cache, filter by prefix, and order by recent use; the list is small enough that no special index is needed.

Hard problem: permissions. The product owns who can see an item, and the tag service must not leak it. Two layers. First, each edge carries scope_id, and the product tells the service which scopes the caller can reach, so the query filters inside the index, avoiding pages that come back short after filtering. Second, items with item-level restrictions get a final batched permission check against the product on the candidate page. Suggestions are limited to tags used in scopes the caller can see, so a tag name does not leak from a private project.

Merge and rename. Merge re-points edges from the old tag to the new one in batches in the background, keeping an alias row so reads resolve the old id during the move.

• Central service vs each product storing tags itself. A central service gives one vocabulary, cross-product search and one set of rules; each product keeping its own is simpler and faster to ship but the tags never line up. Pick central when cross-product search is the goal; flip to per-product when products have nothing in common.
• Relational edges vs a search index for queries. Relational edges give exact, transactional single-tag lookup; the search index gives multi-tag queries and text but is eventually consistent. Use relational as the source of truth and the index as a derived view, as in the search chapter.
• Pre-filter by scope vs post-filter by permission check. Post-filtering is exact but returns short pages and costs a call per page; pre-filtering by scope is fast but only as precise as the scope. Use both, and flip to post-filter alone for products with highly irregular item permissions.
• Tenant shard key vs tag shard key. Tenant keeps queries local and isolates noisy tenants; a tag-based key spreads a huge tenant evenly but turns each query into a fan-out. Pick tenant and split the few huge tenants out by directory.

• Storing tags as a comma-separated string on the item, which makes tag search a scan and rename a rewrite.
• Making the service product-aware, with a table per product, instead of an opaque item key.
• Forgetting permissions entirely, or checking them after pagination so users see empty pages.
• Case and whitespace variants creating duplicate tags, because there is no normalized unique name.
• One call per item to show tags in a list (the N+1 problem).
• Dual-writing to the database and the search index instead of using an outbox.
• Offset pagination on large tag results.