Problem RestatementProblem
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.
Data ModelData 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 autocompleteEntities reference tags by ID, so renaming a tag is a single-row update and every entity shows the new name automatically.
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 batchesDesign 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.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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"]Deep Dive — Finding everything with these tagsDeep dive
Tags are simple to store and awkward to query, because the interesting questions are set operations over a many-to-many relationship.
A tags column on the entity
Store tags as a comma-separated string.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
ROW["entity.tags = 'urgent,billing,eu'"] --> Q["Find everything tagged 'billing'"]
Q --> LIKE["WHERE tags LIKE '%billing%'"]
LIKE --> SCAN["Full table scan - no index can help"]
LIKE --> FALSE["Also matches 'pre-billing' and 'billing-old'"]
ROW --> REN["Rename a tag - rewrite every row that mentions it"]A substring search cannot use an index and cannot respect word boundaries. Renaming or merging tags becomes a bulk rewrite, and there is nowhere to hang tag metadata like a description or a colour.
A join table
entity_tags(entity_id, tag_id) with tags in their own table. OR queries are a simple IN:
SELECT DISTINCT entity_id FROM entity_tags WHERE tag_id IN (...);The right model: indexed, renaming is one row, and tags become first-class objects. AND queries are the awkward part — "everything tagged both urgent and billing" needs a grouping trick:
SELECT entity_id FROM entity_tags WHERE tag_id IN (...)
GROUP BY entity_id HAVING COUNT(DISTINCT tag_id) = N;Correct, and it reads and aggregates every matching row before filtering — expensive when one tag is very common.
Index tags alongside the content
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
DB[("entity_tags - source of truth")] --> IDX["Search index: entity + tags + text + permissions"]
Q["tags: urgent AND billing, text: 'refund', visible to me"] --> IDX
IDX --> FILT["One query - tag filters, text match and permissions together"]
FILT --> FACET["Facet counts: how many per remaining tag"]
FACET --> UI["Refinement UI without extra queries"]- Combined filtering is the reason. Real queries are rarely tags alone — they are tags and free text and permissions, and evaluating those in one engine avoids intersecting large result sets across systems.
- AND, OR and NOT are native to the query language rather than a
GROUP BY ... HAVINGconstruction, and they stay efficient when one tag matches millions of entities. - Facet counts come free, which is what makes a tag-refinement UI possible — showing how many results remain per tag needs an aggregation the join table would have to compute separately.
Keep the join table as the source of truth and treat the index as derived, rebuilt from it. That keeps tag edits transactional and correct while the read path stays fast — and a corrupted or stale index is always recoverable by reindexing rather than by repair.
Wrap-UpWrap-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.