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

> Discover how OpenSEO manages saved keywords and tags using a three-layer architecture. Learn about client-side normalization, repository layer upserts, and database assignment tables for efficient data handling.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: deep-dive
- Published: 2026-08-05

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/server/features/keywords/repositories/SavedKeywordTagsRepository.ts) manages persistence and bulk operations. Finally, the **database schema** defined in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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.

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

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

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

```

### Replacing All Tags on Selected Keywords

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

```

### Deleting Tags Safely

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/shared/saved-keyword-tags.ts) using `parseSavedKeywordTagInput` to split, trim, lowercase, and deduplicate user input.
- **The repository layer** in [`SavedKeywordTagsRepository.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.