Tag Synonym Architecture Note – Minimal Design, Trust Boundaries, and Operational Checks

Problem Statement

Stack Overflow relies on tags to surface relevant questions. Because users often create semantically identical tags with different spellings, a tag‑synonym system is required to map non‑canonical names to a single canonical tag. The architecture must guarantee consistency, prevent misuse, and remain maintainable as the platform evolves.

Design Requirements

• Persist synonym mappings in a single, relational table.
• Enforce uniqueness: a source tag can map to only one canonical tag.
• Prevent cycles: a tag cannot synonymise to itself or to a tag that already maps back to it.
• Limit the number of active synonyms per canonical tag (default 10).
• Restrict creation, editing, and deletion to users with the Moderator or Tag Synonym Moderator role.
• Allow regular users to propose synonyms and vote on them; the final decision remains with moderators.
• Provide a background job to clean stale synonyms when a canonical tag is renamed or deleted.

Minimal Implementation

The core table is tag_synonyms:

CREATE TABLE tag_synonyms (
  synonym_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
  canonical_tag_id BIGINT NOT NULL,
  source_tag_id    BIGINT NOT NULL,
  created_by       BIGINT NOT NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_synonym UNIQUE (canonical_tag_id, source_tag_id),
  CONSTRAINT fk_canonical_tag FOREIGN KEY (canonical_tag_id) REFERENCES tags(tag_id),
  CONSTRAINT fk_source_tag    FOREIGN KEY (source_tag_id)    REFERENCES tags(tag_id)
);

Key points:

• canonical_tag_id and source_tag_id reference the same tags table.
• The unique constraint guarantees that a source tag cannot be mapped to two different canonicals.
• Foreign keys maintain referential integrity.

Trust & Data Boundaries

Only users with elevated roles can modify the table. In the application layer, a permission check runs before any INSERT, UPDATE, or DELETE:

def can_modify_synonym(user):
    return user.is_moderator or user.is_tag_synonym_moderator

Regular users can submit a SynonymProposal record, which is reviewed by a moderator. The proposal table is separate and does not directly affect tag_synonyms until approved.

Operational Checks

1. Cycle Prevention

Before inserting a new row, the system must confirm that the mapping does not introduce a cycle. A simple recursive check using the database can be performed:

-- Pseudocode for cycle detection
WITH RECURSIVE chain AS (
  SELECT source_tag_id, canonical_tag_id FROM tag_synonyms WHERE source_tag_id = :new_source
  UNION ALL
  SELECT s.source_tag_id, s.canonical_tag_id
  FROM tag_synonyms s
  JOIN chain c ON s.source_tag_id = c.canonical_tag_id
)
SELECT 1 FROM chain WHERE canonical_tag_id = :new_canonical;

-- If the query returns a row, reject the insertion.

2. Synonym Limit Enforcement

When a moderator attempts to add a new synonym, count existing synonyms for the target canonical tag:

SELECT COUNT(*) FROM tag_synonyms WHERE canonical_tag_id = :canonical;

If the count meets or exceeds the limit (10), the operation is blocked and the moderator receives a warning.

Failure Modes & Mitigations

• Duplicate Synonyms: The unique constraint blocks duplicates, but if the constraint is accidentally removed or bypassed, a background job can detect and merge duplicates by checking for identical source_tag_id entries.
• Stale Synonyms: When a canonical tag is renamed or deleted, orphaned synonyms remain. A nightly job scans for canonical_tag_id references that no longer exist and removes or flags them for moderator review.
• Cycle Detection Failure: If the recursive check is not executed (e.g., due to a code path bug), cycles could form. A periodic audit script can detect cycles by performing a graph traversal over the tag_synonyms table.
• Permission Escalation: Granting moderator rights to untrusted users could allow arbitrary synonym creation. Role checks are enforced at the application layer and reinforced by database row‑level security (RLS) if supported.

Change Triggers

Design adjustments become necessary under the following conditions:

• Role Model Evolution: If new moderation roles are introduced or existing ones are re‑defined, the permission checks and database constraints must be updated.
• Synonym Limit Modification: Raising or lowering the maximum number of synonyms per canonical tag requires changing the check logic and potentially the UI to reflect the new limit.
• Multi‑Tenant or Sharded Deployment: The current single‑tenant design assumes a shared tags table. Sharding would necessitate tenant identifiers in tag_synonyms and cross‑shard consistency mechanisms.
• API Changes: If the public API for synonym proposals changes (e.g., new fields or validation rules), the proposal handling code and database schema must adapt.

Verification Checklist

1. Inspect the schema: SHOW CREATE TABLE tag_synonyms; should match the design.
2. Simulate creation: Use the UI or API to create a synonym, then query SELECT * FROM tag_synonyms WHERE source_tag_id = :id; to confirm insertion.
3. Attempt a duplicate insertion: The unique constraint should reject it.
4. Run a cycle test: Insert A → B, then attempt B → A and verify rejection.
5. Check the synonym limit: Insert 10 synonyms for a canonical tag, then attempt the 11th and confirm the operation is blocked.
6. Simulate a canonical tag deletion: Delete the tag, run the cleanup job, and ensure related synonyms are removed.

Conclusion

By centralizing synonym mappings in a single, well‑constrained table and enforcing strict trust boundaries, Stack Overflow can maintain tag consistency with minimal operational overhead. Regular audits, cycle detection, and cleanup jobs guard against the most common failure modes, while the design remains flexible enough to adapt to future policy or infrastructure changes.