# Phase 4: Tags & Organization - Research

**Researched:** 2026-01-31
**Domain:** Tag management, many-to-many relationships, autocomplete UI
**Confidence:** HIGH

## Summary

This phase adds tagging capabilities and organizational features (pinning, due dates) to entries. The core challenge is implementing a proper many-to-many relationship between entries and tags using Drizzle ORM with SQLite, while providing a user-friendly tag input with autocomplete.

The schema already has `pinned` (boolean) and `dueDate` (text) columns on the entries table, so ORG-01 and ORG-02 only require UI work. The tag system requires a new `tags` table and `entry_tags` junction table with case-insensitive tag storage.

**Primary recommendation:** Use a normalized three-table schema (entries, tags, entry_tags) with case-insensitive unique index on tag names, and use Svelecte v5 for the tag input component with autocomplete and tag creation.

## Standard Stack

The established libraries/tools for this domain:

### Core
| Library | Version | Purpose | Why Standard |
|---------|---------|---------|--------------|
| drizzle-orm | 0.45.1 | Database ORM | Already in project, handles relations via Query API v1 |
| svelecte | 5.x | Tag input with autocomplete | Svelte 5 native, supports multi-select, tag creation, filtering |

### Supporting
| Library | Version | Purpose | When to Use |
|---------|---------|---------|-------------|
| nanoid | 5.x | ID generation | Already in project, use for tag IDs |
| HTML5 date input | native | Due date picker | Native browser support, no extra dependency |

### Alternatives Considered
| Instead of | Could Use | Tradeoff |
|------------|-----------|----------|
| Svelecte | svelte-tags-input | Less certain Svelte 5 compatibility; Svelecte explicitly supports Svelte 5 |
| Svelecte | Custom component | More control but significant effort for autocomplete, filtering, keyboard nav |
| Normalized tags table | JSON array on entry | Harder to search, no tag reuse, no autocomplete across entries |

**Installation:**
```bash
npm install svelecte
```

## Architecture Patterns

### Recommended Database Schema
```
entries (existing)
├── id (PK)
├── pinned (already exists)
├── dueDate (already exists)
└── ...

tags (new)
├── id (PK, nanoid)
├── name (unique case-insensitive)
└── createdAt

entry_tags (junction table, new)
├── entryId (FK -> entries.id, cascade delete)
├── tagId (FK -> tags.id, cascade delete)
└── (composite PK on entryId + tagId)
```

### Pattern 1: Case-Insensitive Tag Storage
**What:** Store tags with a unique index on lowercased name
**When to use:** All tag operations to ensure "Work" and "work" are the same tag
**Example:**
```typescript
// Source: https://orm.drizzle.team/docs/guides/unique-case-insensitive-email
import { sql, SQL } from 'drizzle-orm';
import { sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core';

export function lower(column: AnySQLiteColumn): SQL {
  return sql`lower(${column})`;
}

export const tags = sqliteTable('tags', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: text('created_at').notNull().$defaultFn(() => new Date().toISOString()),
}, (table) => [
  uniqueIndex('tagNameUniqueIndex').on(lower(table.name)),
]);
```

### Pattern 2: Junction Table with Composite Primary Key
**What:** Link entries to tags through a many-to-many junction table
**When to use:** All entry-tag associations
**Example:**
```typescript
// Source: https://orm.drizzle.team/docs/relations
import { sqliteTable, text, primaryKey } from 'drizzle-orm/sqlite-core';

export const entryTags = sqliteTable('entry_tags', {
  entryId: text('entry_id').notNull().references(() => entries.id, { onDelete: 'cascade' }),
  tagId: text('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (t) => [
  primaryKey({ columns: [t.entryId, t.tagId] }),
]);
```

### Pattern 3: Drizzle Relations v1 for Many-to-Many
**What:** Define relations to enable relational queries with the `with` clause
**When to use:** Fetching entries with their tags in a single query
**Example:**
```typescript
// Source: https://orm.drizzle.team/docs/rqb
import { relations } from 'drizzle-orm';

export const entriesRelations = relations(entries, ({ many }) => ({
  entryTags: many(entryTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  entryTags: many(entryTags),
}));

export const entryTagsRelations = relations(entryTags, ({ one }) => ({
  entry: one(entries, {
    fields: [entryTags.entryId],
    references: [entries.id],
  }),
  tag: one(tags, {
    fields: [entryTags.tagId],
    references: [tags.id],
  }),
}));
```

### Pattern 4: Querying Entries with Tags
**What:** Use Drizzle's relational queries to fetch entries with nested tags
**When to use:** Loading entry list with all associated tags
**Example:**
```typescript
// Source: https://orm.drizzle.team/docs/rqb
const entriesWithTags = await db.query.entries.findMany({
  with: {
    entryTags: {
      with: {
        tag: true,
      },
    },
  },
});

// Transform to flat tag array if needed
const result = entriesWithTags.map(entry => ({
  ...entry,
  tags: entry.entryTags.map(et => et.tag),
}));
```

### Pattern 5: Svelecte for Tag Input
**What:** Use Svelecte with creatable mode for tag input with autocomplete
**When to use:** Tag input UI in entry edit form
**Example:**
```svelte
<!-- Source: https://github.com/mskocik/svelecte -->
<script lang="ts">
  import Svelecte from 'svelecte';

  interface Props {
    availableTags: { id: string; name: string }[];
    selectedTags: { id: string; name: string }[];
    onchange: (tags: { id: string; name: string }[]) => void;
  }

  let { availableTags, selectedTags, onchange }: Props = $props();
</script>

<Svelecte
  options={availableTags}
  value={selectedTags}
  multiple
  creatable
  valueField="id"
  labelField="name"
  placeholder="Add tags..."
  on:change={(e) => onchange(e.detail)}
/>
```

### Anti-Patterns to Avoid
- **Storing tags as JSON array:** Prevents efficient querying, no autocomplete, can't find "all entries with tag X"
- **Case-sensitive tag matching:** Leads to duplicate tags like "Work", "work", "WORK"
- **Not using onDelete cascade:** Orphaned junction records when entry or tag deleted
- **Fetching tags in separate query per entry:** N+1 query problem; use relational queries instead

## Don't Hand-Roll

Problems that look simple but have existing solutions:

| Problem | Don't Build | Use Instead | Why |
|---------|-------------|-------------|-----|
| Tag autocomplete | Custom dropdown with keyboard nav | Svelecte | Keyboard navigation, accessibility, fuzzy search, virtual scrolling |
| Multi-select tag input | Custom chip input | Svelecte with `multiple` | Tag removal UX, paste support, styling |
| Date picker | Custom calendar component | HTML5 `<input type="date">` | Native browser support, mobile-friendly, accessible |
| Case-insensitive unique | Runtime deduplication | SQLite unique index with `lower()` | Database-level enforcement, handles concurrent inserts |

**Key insight:** Autocomplete dropdowns have many edge cases (keyboard navigation, accessibility, scroll behavior, mobile touch) that Svelecte handles out of the box.

## Common Pitfalls

### Pitfall 1: Case Sensitivity in Tag Matching
**What goes wrong:** User creates "Work" tag, then types "work" and creates duplicate
**Why it happens:** Default SQLite text comparison is case-sensitive
**How to avoid:** Create unique index on `lower(name)`, normalize during insert/lookup
**Warning signs:** Multiple tags that differ only in case appearing in autocomplete

### Pitfall 2: N+1 Queries When Loading Tags
**What goes wrong:** Fetching entry list, then making separate query for each entry's tags
**Why it happens:** Not using Drizzle's relational queries with `with` clause
**How to avoid:** Always use `db.query.entries.findMany({ with: { entryTags: { with: { tag: true }}}})`
**Warning signs:** Page load slows linearly with entry count

### Pitfall 3: Orphaned Tags
**What goes wrong:** After removing tag from all entries, tag still appears in autocomplete
**Why it happens:** Junction records deleted but tag record remains
**How to avoid:** Either accept orphaned tags (they're useful for autocomplete) OR periodically clean up tags with no associations
**Warning signs:** Autocomplete shows tags that aren't used anywhere

### Pitfall 4: Race Condition Creating New Tags
**What goes wrong:** Two users simultaneously create "urgent" tag, one fails with unique constraint error
**Why it happens:** Check-then-insert pattern without proper handling
**How to avoid:** Use "INSERT OR IGNORE" or try/catch with lookup on conflict
**Warning signs:** Intermittent errors when creating new tags

### Pitfall 5: Not Updating Tags Atomically
**What goes wrong:** Entry has ["work", "urgent"], user saves ["work", "home"], "urgent" not removed
**Why it happens:** Only inserting new tags, not removing old ones
**How to avoid:** Replace pattern: delete all entry_tags for entry, then insert new set
**Warning signs:** Tags accumulate on entries, can't remove tags

## Code Examples

Verified patterns from official sources:

### Tag Repository Operations
```typescript
// Find or create tag by name (case-insensitive)
async findOrCreate(name: string): Tag {
  const normalizedName = name.trim();

  // Try to find existing (case-insensitive)
  const existing = db
    .select()
    .from(tags)
    .where(sql`lower(${tags.name}) = lower(${normalizedName})`)
    .get();

  if (existing) return existing;

  // Create new tag
  const newTag = {
    id: nanoid(),
    name: normalizedName,
    createdAt: new Date().toISOString(),
  };
  db.insert(tags).values(newTag).run();
  return newTag;
}

// Get all tags for autocomplete
getAll(): Tag[] {
  return db.select().from(tags).orderBy(asc(tags.name)).all();
}
```

### Updating Entry Tags
```typescript
// Replace all tags for an entry atomically
async updateEntryTags(entryId: string, tagNames: string[]): void {
  // Delete existing associations
  db.delete(entryTags).where(eq(entryTags.entryId, entryId)).run();

  // Find or create each tag and create associations
  for (const name of tagNames) {
    const tag = await this.findOrCreate(name);
    db.insert(entryTags).values({ entryId, tagId: tag.id }).run();
  }
}
```

### Form Action for Tags
```typescript
// In +page.server.ts
updateTags: async ({ request }) => {
  const formData = await request.formData();
  const id = formData.get('id')?.toString();
  const tagsJson = formData.get('tags')?.toString() || '[]';

  if (!id) return fail(400, { error: 'Entry ID required' });

  const tagNames = JSON.parse(tagsJson) as string[];
  await tagRepository.updateEntryTags(id, tagNames);

  return { success: true };
}
```

### Native Date Input for Due Date
```svelte
<!-- HTML5 native date picker - mobile-friendly -->
<input
  type="date"
  bind:value={dueDate}
  onchange={handleDueDateChange}
  class="px-3 py-2 border rounded-lg"
/>
```

## State of the Art

| Old Approach | Current Approach | When Changed | Impact |
|--------------|------------------|--------------|--------|
| Drizzle Relations v1 | Relations v2 with `through` | 1.0.0-beta.1 | Simpler many-to-many queries (not available in 0.45.x) |
| Manual joins for M:M | `with` clause in relational queries | 0.17.0+ | Single query, automatic aggregation |
| Svelte stores for reactivity | Svelte 5 runes ($state, $props) | Svelte 5 | More explicit reactivity |

**Note on Drizzle version:** Project uses Drizzle 0.45.1 which uses Relations v1 API. Relations v2 with `through` syntax requires 1.0.0-beta.1+. The patterns documented here use v1 API which is stable and well-supported.

**Deprecated/outdated:**
- Svelecte v4.x for Svelte 4 projects (use v5.x for Svelte 5)
- svelte-tags-input may have Svelte 5 compatibility issues (not verified)

## Open Questions

Things that couldn't be fully resolved:

1. **Orphaned tag cleanup strategy**
   - What we know: When tag removed from all entries, tag record remains
   - What's unclear: Should orphaned tags be kept (useful for autocomplete history) or cleaned up?
   - Recommendation: Keep orphaned tags for now, they provide autocomplete suggestions. Add cleanup later if tag list grows too large.

2. **Tag rename cascading**
   - What we know: Tags have unique IDs, name stored in tags table
   - What's unclear: If user renames a tag, should it propagate to all entries?
   - Recommendation: Not in scope for this phase. Tags are simple strings; if users want to "rename", they add new tag and remove old one.

## Sources

### Primary (HIGH confidence)
- [Drizzle ORM Relations](https://orm.drizzle.team/docs/relations) - Junction table pattern
- [Drizzle Query API v1](https://orm.drizzle.team/docs/rqb) - Relational queries with `with` clause
- [Drizzle Case-Insensitive Guide](https://orm.drizzle.team/docs/guides/unique-case-insensitive-email) - `lower()` helper function
- [Drizzle SQLite Column Types](https://orm.drizzle.team/docs/column-types/sqlite) - Schema definition
- [Svelecte GitHub](https://github.com/mskocik/svelecte) - Svelte 5 support confirmed (v5.x)

### Secondary (MEDIUM confidence)
- [SQLite COLLATE NOCASE](https://www.designcise.com/web/tutorial/how-to-do-case-insensitive-comparisons-in-sqlite) - Case-insensitive patterns
- [Tagging System Database Design](https://www.geeksforgeeks.org/dbms/how-to-design-a-database-for-tagging-service/) - Normalized schema patterns

### Tertiary (LOW confidence)
- svelte-tags-input Svelte 5 compatibility status - Unclear from docs, Svelecte is safer choice

## Metadata

**Confidence breakdown:**
- Standard stack: HIGH - Drizzle patterns verified via official docs, Svelecte explicitly supports Svelte 5
- Architecture: HIGH - Many-to-many junction table is standard pattern, verified with Drizzle docs
- Pitfalls: MEDIUM - Based on general database design principles and common issues with tagging systems

**Research date:** 2026-01-31
**Valid until:** 60 days (stable patterns, no fast-moving dependencies)
