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

> Discover how Open-SEO persists saved keyword tags in its normalized database, enabling efficient research data organization via a repository layer and batch operations.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: internals
- Published: 2026-07-31

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/shared/saved-keyword-tags.ts)**. The `normalizeSavedKeywordTag` function cleans user input to ensure consistent storage:

```typescript
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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
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`:

```typescript
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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.