How OpenSEO Manages Saved Keywords and Tags: A Technical Deep Dive

OpenSEO manages saved keywords and tags through a three-layer architecture that normalizes user input on the client, persists deduplicated tags via upsert operations in the repository layer, and maintains referential integrity through a dedicated assignment table in the database.

The every-app/open-seo repository implements a robust tagging system that allows users to organize saved keywords with custom labels. Understanding how OpenSEO manages saved keywords and tags reveals a sophisticated approach to data normalization, bulk operations, and database consistency that prevents duplicate entries while supporting efficient filtering.

The Three-Layer Architecture for Saved Keywords and Tags

OpenSEO organizes its tagging functionality into three distinct layers that work together to ensure data integrity. The client-side helpers in src/shared/saved-keyword-tags.ts handle input normalization, converting messy user input into canonical forms. The repository layer in src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts manages persistence and bulk operations. Finally, the database schema defined in src/db/schema.ts stores tags and their assignments to saved keywords with proper foreign key constraints.

This separation ensures that "Technical SEO", " technical seo ", and "TECHNICAL SEO" all resolve to a single canonical tag, while supporting efficient bulk operations across thousands of keyword records.

Normalizing and Parsing Tag Input

Client-Side Tag Processing

When users enter tags in the OpenSEO interface—whether typing "content, technical seo" or pasting a newline-separated list—the system immediately normalizes this input through the parseSavedKeywordTagInput function.

// src/shared/saved-keyword-tags.ts
export function parseSavedKeywordTagInput(value: string): string[] {
  return normalizeSavedKeywordTags(value.split(TAG_SEPARATOR)).map(
    (tag) => tag.name,
  );
}

The TAG_SEPARATOR regex (/[\n,]+/) splits input on commas or newlines, while normalizeSavedKeywordTag trims whitespace, collapses multiple spaces, and lowercases the string for deduplication. The helper returns a clean array where "Technical SEO" becomes the canonical "technical seo" stored as normalizedName, while preserving the original casing in the name field.

Persisting Tags with Deduplication

The Upsert Pattern

The repository layer ensures idempotent tag creation through an upsert mechanism that prevents duplicate entries. When upsertSavedKeywordTags receives tag names, it first normalizes them using normalizeSavedKeywordTags, then inserts any missing records.

// src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts
async function upsertSavedKeywordTags(projectId: string, tagNames: readonly string[] | undefined) {
  const normalizedTags = normalizeSavedKeywordTags(tagNames);
  if (normalizedTags.length === 0) return [];

  // Insert any missing tags (ON CONFLICT DO NOTHING)
  await runBatch((tx) =>
    normalizedTags.map((tag) =>
      tx.insert(savedKeywordTags).values({
        id: crypto.randomUUID(),
        projectId,
        name: tag.name,
        normalizedName: tag.normalizedName,
      }).onConflictDoNothing(),
    ),
  );

  // Return the full tag records for the project
  return db
    .select()
    .from(savedKeywordTags)
    .where(and(
      eq(savedKeywordTags.projectId, projectId),
      inArray(savedKeywordTags.normalizedName, normalizedTags.map(t => t.normalizedName)),
    ))
    .orderBy(asc(savedKeywordTags.normalizedName));
}

Each tag receives a UUID and stores both the display name and normalizedName for fast lookups. The onConflictDoNothing clause ensures that concurrent requests for the same tag don't create duplicates.

Linking Tags to Saved Keywords

Bulk Assignment Operations

OpenSEO assigns tags to saved keywords using chunked inserts to stay within database query limits. The repository creates a Cartesian product of selected keywords and target tags, then inserts these assignments in batches.

// src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts
for (let i = 0; i < assignments.length; i += ASSIGNMENT_INSERT_CHUNK_SIZE) {
  const chunk = assignments.slice(i, i + ASSIGNMENT_INSERT_CHUNK_SIZE);
  await db.insert(savedKeywordTagAssignments).values(chunk).onConflictDoNothing();
}

The ASSIGNMENT_INSERT_CHUNK_SIZE constant prevents query parameter limits from being exceeded when tagging thousands of keywords simultaneously.

Filtering by Tags

When filtering keywords by tags, OpenSEO translates human-readable names into database IDs through the getTagFilterIds method. This supports both ID-based and name-based filter parameters, normalizing tag names before querying.

// src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts
async function getTagFilterIds({ projectId, tagIds, tagNames }) {
  const directTagIds = tagIds ?? [];
  const normalizedTags = normalizeSavedKeywordTags(tagNames);
  if (normalizedTags.length === 0) {
    return { tagIds: [...new Set(directTagIds)], emptyTagNameMatch: false };
  }

  const rows = await db
    .select({ id: savedKeywordTags.id })
    .from(savedKeywordTags)
    .where(and(
      eq(savedKeywordTags.projectId, projectId),
      inArray(savedKeywordTags.normalizedName, normalizedTags.map(t => t.normalizedName)),
    ));

  return {
    tagIds: [...new Set([...directTagIds, ...rows.map(r => r.id)])],
    emptyTagNameMatch: directTagIds.length === 0 && rows.length === 0,
  };
}

Updating and Removing Tags

Safe Deletion with Reference Checks

OpenSEO prevents accidental data loss by checking assignment counts before deleting tags. The deleteSavedKeywordTag method in the repository first queries the savedKeywordTagAssignments table to determine if the tag is in use.

If assignmentCount returns greater than zero, the operation aborts and returns a status of 'in_use', allowing the UI to warn users about existing dependencies. Updates to tag names trigger re-normalization through updateSavedKeywordTag, ensuring the normalizedName field stays synchronized with the display name.

Practical Implementation Examples

Parsing User Input and Creating Tags

import { parseSavedKeywordTagInput } from '@/shared/saved-keyword-tags';
import { SavedKeywordTagsRepository } from '@/server/features/keywords/repositories/SavedKeywordTagsRepository';

async function addUserTags(projectId: string, rawInput: string) {
  const tagNames = parseSavedKeywordTagInput(rawInput);   // → ['content','technical seo','BOFU']
  await SavedKeywordTagsRepository.addTagsToSavedKeywords({
    projectId,
    savedKeywordIds: [],          // No keywords yet – just ensure tags exist
    tagNames,
  });
}

Attaching Tags to Saved Keywords

await SavedKeywordTagsRepository.addTagsToSavedKeywords({
  projectId,
  savedKeywordIds: ['kw1', 'kw2'],
  tagNames: ['Content', 'BOFU'],
});

Replacing All Tags on Selected Keywords

await SavedKeywordTagsRepository.replaceTagsForSavedKeywords({
  projectId,
  savedKeywordIds: ['kw1', 'kw2'],
  tagNames: ['Technical SEO'],
});

Deleting Tags Safely

const result = await SavedKeywordTagsRepository.deleteSavedKeywordTag({
  projectId,
  tagId: 'tag-uuid',
});

if (result.status === 'in_use') {
  console.warn(`Tag is still attached to ${result.assignmentCount} keywords`);
}

Summary

• OpenSEO implements a three-layer architecture for managing saved keywords and tags: client-side normalization, repository persistence, and database storage.
• Tag normalization occurs in src/shared/saved-keyword-tags.ts using parseSavedKeywordTagInput to split, trim, lowercase, and deduplicate user input.
• The repository layer in SavedKeywordTagsRepository.ts uses upsert operations with onConflictDoNothing to prevent duplicate tag creation while maintaining both display names and normalized lookup names.
• Bulk assignments use chunked inserts via ASSIGNMENT_INSERT_CHUNK_SIZE to handle large-scale tagging operations without hitting database parameter limits.
• Safe deletion requires checking assignmentCount in the savedKeywordTagAssignments table to prevent orphaned relationships.
• Database schema stores tags in savedKeywordTags and relationships in savedKeywordTagAssignments, enforcing referential integrity between projects, tags, and keywords.

Frequently Asked Questions

How does OpenSEO normalize tag names to prevent duplicates?

OpenSEO normalizes tags through the normalizeSavedKeywordTag function in src/shared/saved-keyword-tags.ts, which trims whitespace, collapses multiple spaces, and converts strings to lowercase. This ensures that "Technical SEO", " technical seo ", and "TECHNICAL SEO" all resolve to the same canonical normalizedName while preserving the original casing in the display name field.

What database tables store saved keywords and their tags?

According to the schema in src/db/schema.ts, OpenSEO uses two tables: savedKeywordTags stores the tag definitions with id, projectId, name, and normalizedName columns, while savedKeywordTagAssignments maintains the many-to-many relationships between saved keywords and tags with foreign key references ensuring referential integrity.

How does OpenSEO handle bulk tagging operations for large datasets?

The repository implements chunked inserts using ASSIGNMENT_INSERT_CHUNK_SIZE to stay within database query limits. When assigning tags to multiple keywords, OpenSEO creates a Cartesian product of keyword IDs and tag IDs, then inserts these assignments in batches rather than as a single massive query, preventing parameter limit errors.

Can I delete a tag in OpenSEO if it's still assigned to keywords?

No, OpenSEO prevents deletion of tags that have existing assignments. The deleteSavedKeywordTag method checks the assignmentCount in the savedKeywordTagAssignments table before proceeding. If the tag is in use, the repository returns { status: 'in_use', assignmentCount: n }, allowing the application to warn users and require them to remove the tag from keywords first.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}