Tagging System with Clean REST APIs (Confluence / Jira Labels)

0) Problem Restatement

Atlassian asked: design a tagging system (like labels on Confluence pages or Jira issues) with a strong focus on REST API design. Required: create a tag, rename a tag, delete a tag, attach or detach one or more tags to an entity (a page or an issue), list the tags of an entity, and find entities by tag.

1) Data Model

CREATE TABLE tags (
  tag_id     BIGINT PRIMARY KEY,
  workspace_id BIGINT NOT NULL,
  name       TEXT NOT NULL,
  name_norm  TEXT NOT NULL,          -- lowercase/trimmed for uniqueness and search
  created_by BIGINT, created_at TIMESTAMP,
  UNIQUE (workspace_id, name_norm)
);
CREATE TABLE entity_tags (
  tag_id      BIGINT REFERENCES tags(tag_id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL,         -- 'page', 'issue'
  entity_id   BIGINT NOT NULL,
  tagged_by BIGINT, tagged_at TIMESTAMP,
  PRIMARY KEY (tag_id, entity_type, entity_id)
);
CREATE INDEX ON entity_tags (entity_type, entity_id);   -- tags of an entity
CREATE INDEX ON tags (workspace_id, name_norm text_pattern_ops);   -- prefix autocomplete

Entities reference tags by ID, so renaming a tag is a single-row update and every entity shows the new name automatically.

2) REST API

POST   /v1/tags                              { name }                       → 201 { id, name } | 409 exists
GET    /v1/tags?prefix=rel&limit=10                                          → autocomplete
PATCH  /v1/tags/{tagId}                      { name }                       → 200 | 409 name taken
DELETE /v1/tags/{tagId}                                                      → 204 (detaches everywhere)

GET    /v1/{entityType}/{entityId}/tags                                      → 200 [ tags ]
POST   /v1/{entityType}/{entityId}/tags      { tagIds: [..] } or { names: [..] }  → 200 (idempotent attach)
DELETE /v1/{entityType}/{entityId}/tags/{tagId}                             → 204

GET    /v1/tags/{tagId}/entities?type=page&cursor=&limit=50                  → entities with this tag
GET    /v1/search?tags=release,backend&match=all&type=issue&cursor=          → AND / OR search
POST   /v1/tags/bulk-attach                  { tagIds, entities: [...] }    → 202 job for large batches

Design choices to call out:

• Resource nesting: an entity's tags live under the entity. Tags are their own top-level resource.
• Idempotency: attaching an already-attached tag is a no-op (INSERT ... ON CONFLICT DO NOTHING), so retries are safe. Deleting a missing link returns 204 or 404 consistently (document which).
• Attach by name: creates missing tags automatically (with the same normalization), which is convenient for UIs.
• Status codes: 201, 200, 204, 400 (invalid name), 403 (no permission on the entity), 404, 409 (duplicate name).
• Pagination: cursor-based for entity lists.
• Validation: name length, allowed characters, and a max number of tags per entity.

flowchart LR
    UI["Web / API clients"] --> API["Tagging API"]
    API --> DB[("tags + entity_tags")]
    API --> PERM["Permission check on entity"]
    DB -->|"changes"| IDX["Search index update"]

3) Finding Entities by Tags

• OR (any of the tags): SELECT DISTINCT entity_id FROM entity_tags WHERE tag_id IN (...).
• AND (all tags): ... GROUP BY entity_id HAVING COUNT(DISTINCT tag_id) = N.
• At large scale, index tags in the search engine alongside entity content, so tag filters combine with text search and permissions.

4) Wrap-Up

Store tags per workspace with a normalized unique name, and link them to entities through an entity_tags join table (a composite primary key, indexes both ways), so renames are one-row updates and deletes cascade. Expose clean REST resources: tags as a top-level collection (create, rename, delete, prefix autocomplete), and tags nested under entities with idempotent attach/detach, plus by-tag queries with AND/OR, cursor pagination, bulk async attach, permission checks and clear status codes.