How Saved Keyword Tags Are Persisted and Used to Organize Research Data in Open-SEO

Saved keyword tags in Open-SEO are stored in a normalized SQLite/Postgres database table with case-insensitive unique constraints, linked to keywords via a many-to-many assignment table, and managed through a repository layer that handles batch operations and tag-based filtering.

The Open-SEO platform uses saved keyword tags to categorize and filter SEO research data efficiently. These tags persist in a relational database with strict normalization rules to prevent duplicates and enable fast lookups. Understanding the persistence layer reveals how the application maintains data integrity while supporting flexible organizational workflows across large keyword datasets.

Database Schema and Normalization

The saved_keyword_tags Table

Tag persistence begins in the database schema defined in src/db/app.schema.ts. The saved_keyword_tags table stores tag metadata with the following columns: id, projectId, name, normalizedName, color, and createdAt.

A unique composite index on (projectId, normalizedName) guarantees that each project maintains only one tag per normalized name, preventing duplicates at the database level. This constraint ensures that "SEO" and "seo" are treated as identical tags within the same project.

Many-to-Many Assignment Table

The relationship between saved keywords and tags is modeled through the saved_keyword_tag_assignments table, also defined in app.schema.ts. Each row links a savedKeywordId to a tagId, creating a flexible many-to-many relationship. This design allows a single keyword to carry multiple tags while enabling tags to be reused across many keywords.

Input Processing and Tag Normalization

Normalizing Tag Strings

Before persistence, all tag strings undergo normalization via src/shared/saved-keyword-tags.ts. The normalizeSavedKeywordTag function cleans user input to ensure consistent storage:

export function normalizeSavedKeywordTag(value: string) {
  const name = value.trim().replace(/\s+/g, " ");
  if (name.length === 0) return null;
  return { name, normalizedName: name.toLocaleLowerCase() };
}

This process removes surrounding whitespace, collapses internal spaces to single characters, and generates a lower-cased normalizedName for case-insensitive lookups. The function returns null for empty strings, preventing blank tags from entering the system.

Parsing Free-Form Input

When users input multiple tags via text areas or CSV uploads, the parseSavedKeywordTagInput function handles the parsing:

import { parseSavedKeywordTagInput } from "@/shared/saved-keyword-tags";

const rawInput = " SEO, high‑intent\nlocal,  ";
const tagNames = parseSavedKeywordTagInput(rawInput);
// tagNames => ["SEO", "high-intent", "local"]

This utility splits comma-separated or newline-separated values, applies normalization to each element, and filters out empty results.

Repository Pattern and CRUD Operations

Core Repository Methods

All database interactions for saved keyword tags are encapsulated in SavedKeywordTagsRepository located at src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts. The repository provides several key methods:

• upsertSavedKeywordTags – Inserts new tags using ON CONFLICT DO NOTHING semantics, then returns the complete tag set for the project. This method handles the idempotent creation of tags.
• listSavedKeywordTagsByProject – Retrieves every tag for a given project along with a count of attached saved keywords, enabling UI displays that show tag popularity.
• updateSavedKeywordTag – Modifies a tag's display name and/or color, re-normalizing the name to maintain uniqueness constraints.
• deleteSavedKeywordTag – Removes a tag only when it has zero assignments, preventing accidental data loss through cascading deletes.

Batch Operations and Chunking

Because SEO projects often contain thousands of keywords and tags, the repository processes all insert, delete, and lookup operations in configurable chunks. The system uses a QUERY_CHUNK_SIZE of 80 (or similar configurable value) to stay within database query-parameter limits and prevent timeout errors during bulk operations.

Adding Tags to Keywords

To associate tags with specific keywords, use the addTagsToSavedKeywords method:

import { SavedKeywordTagsRepository } from "@/server/features/keywords/repositories/SavedKeywordTagsRepository";

await SavedKeywordTagsRepository.addTagsToSavedKeywords({
  projectId: "proj_123",
  savedKeywordIds: ["kw_1", "kw_2"],
  tagNames: ["SEO", "high-intent"],
});

This method normalizes the provided tag names, creates any missing tags via upsert, and then inserts the appropriate rows into the assignment table.

Organizing Research Data with Tags

Retrieving Tags for Keyword Lists

When the UI renders keyword lists with their associated tags, the application calls listTagsBySavedKeywordIds:

const tagsMap = await SavedKeywordTagsRepository.listTagsBySavedKeywordIds(
  "proj_123",
  ["kw_1", "kw_2", "kw_3"]
);

// Example usage:
for (const [kwId, tags] of tagsMap.entries()) {
  console.log(`Keyword ${kwId} has tags:`, tags.map(t => t.name));
}

This method returns a Map where keys are savedKeywordId strings and values are arrays of SavedKeywordTagRecord objects. This structure allows the front-end to efficiently render tag badges alongside keyword data without performing N+1 queries.

Tag-Based Filtering

The repository supports sophisticated filtering through getTagFilterIds, which accepts either explicit tag IDs or raw tag names supplied by users:

const { tagIds, emptyTagNameMatch } = await SavedKeywordTagsRepository.getTagFilterIds({
  projectId: "proj_123",
  tagIds: [],               // optional explicit IDs
  tagNames: ["local"],     // raw names supplied by the user
});

// Use tagIds in a keyword query:
if (!emptyTagNameMatch) {
  const keywords = await db.select()
    .from(savedKeywords)
    .where(inArray(savedKeywords.id, tagIds)); // simplified example
}

The function normalizes input names, looks up matching tag IDs, and returns a combined list along with an emptyTagNameMatch boolean flag. This flag indicates whether the filter criteria would match zero rows, allowing the application to short-circuit expensive queries when no matching tags exist.

Color Management

Tag visual distinction is handled through the color column in the database. When this value is null, the helper utilities in src/shared/tag-colors.ts derive a stable color based on the tag name or ID, ensuring consistent visual presentation across the interface without requiring explicit color selection for every tag.

Summary

• Saved keyword tags persist in the saved_keyword_tags table with a unique constraint on (projectId, normalizedName) to prevent duplicates.
• The normalizeSavedKeywordTag function in src/shared/saved-keyword-tags.ts standardizes input by trimming whitespace and lower-casing names for case-insensitive storage.
• A many-to-many relationship via saved_keyword_tag_assignments links tags to keywords without cascade deletion risks.
• SavedKeywordTagsRepository handles chunked batch operations to manage large datasets efficiently.
• listTagsBySavedKeywordIds provides optimized retrieval of tag mappings for UI rendering, while getTagFilterIds enables flexible filtering by name or ID.
• Color consistency is maintained through src/shared/tag-colors.ts when explicit colors are not specified.

Frequently Asked Questions

How does Open-SEO prevent duplicate saved keyword tags within a project?

The database schema enforces uniqueness through a composite index on (projectId, normalizedName) in the saved_keyword_tags table. Additionally, the normalizeSavedKeywordTag function converts all input to lowercase for the normalizedName field while preserving the original casing in the name column. This ensures that "SEO", "seo", and " Seo " are treated as identical tags within the same project, with the repository using ON CONFLICT DO NOTHING during upsert operations.

What happens to keyword assignments when a tag is deleted?

The deleteSavedKeywordTag method in SavedKeywordTagsRepository includes a safety check that prevents deletion if the tag has any existing assignments in the saved_keyword_tag_assignments table. Users must first remove all keyword associations before the tag can be deleted, preventing accidental data loss and maintaining referential integrity without cascading deletes.

How does the repository handle bulk tagging operations for large keyword sets?

The repository implements chunked processing using a configurable QUERY_CHUNK_SIZE (typically 80 items per batch). All bulk insert, update, and delete operations are split into smaller chunks to avoid exceeding database parameter limits and to prevent query timeouts. This approach allows users to tag hundreds or thousands of keywords simultaneously without overwhelming the database connection.

Can users filter keywords by typing tag names rather than selecting from a dropdown?

Yes, the getTagFilterIds method accepts an array of raw tagNames alongside explicit tagIds. The function normalizes the input strings, looks up matching tag IDs in the database, and returns a consolidated list of IDs for use in SQL WHERE clauses. The method also returns an emptyTagNameMatch boolean to indicate when the provided names don't correspond to any existing tags, allowing the application to handle empty result sets gracefully.

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