---
phase: 04-tags
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/lib/server/db/schema.ts
  - src/lib/server/db/repository.ts
autonomous: true

must_haves:
  truths:
    - "Tags table exists with case-insensitive unique constraint"
    - "Entry-tag associations stored in junction table"
    - "Finding tag by name is case-insensitive"
    - "Updating entry tags replaces entire tag set atomically"
  artifacts:
    - path: "src/lib/server/db/schema.ts"
      provides: "tags and entryTags table definitions with relations"
      contains: "tags = sqliteTable"
    - path: "src/lib/server/db/repository.ts"
      provides: "tagRepository singleton with CRUD operations"
      contains: "tagRepository"
  key_links:
    - from: "entryTags.entryId"
      to: "entries.id"
      via: "foreign key with cascade delete"
      pattern: "onDelete.*cascade"
    - from: "entryTags.tagId"
      to: "tags.id"
      via: "foreign key with cascade delete"
      pattern: "onDelete.*cascade"
---

<objective>
Add database schema and repository layer for many-to-many tag relationships between entries and tags.

Purpose: Foundation for TAG-01, TAG-02, TAG-03, TAG-04 requirements - enables tag storage and querying
Output: tags table, entry_tags junction table, tagRepository with findOrCreate/getAll/updateEntryTags methods
</objective>

<execution_context>
@/home/tho/.claude/get-shit-done/workflows/execute-plan.md
@/home/tho/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/04-tags/04-RESEARCH.md

@src/lib/server/db/schema.ts
@src/lib/server/db/repository.ts
</context>

<tasks>

<task type="auto">
  <name>Task 1: Add tags schema with case-insensitive unique index</name>
  <files>src/lib/server/db/schema.ts</files>
  <action>
Add tags table and entry_tags junction table to schema.ts:

1. Create `lower()` SQL helper function for case-insensitive indexing:
```typescript
import { sql, SQL } from 'drizzle-orm';
import { AnySQLiteColumn } from 'drizzle-orm/sqlite-core';

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

2. Add tags table:
```typescript
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)),
]);
```

3. Add entry_tags junction table with composite primary key:
```typescript
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] }),
]);
```

4. Add type exports:
```typescript
export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;
export type EntryTag = typeof entryTags.$inferSelect;
```

Import requirements: Add `uniqueIndex`, `primaryKey` to drizzle-orm/sqlite-core imports.
  </action>
  <verify>Run `npm run check` - TypeScript compiles without errors</verify>
  <done>Schema file contains tags table with case-insensitive unique index and entry_tags junction table with cascade deletes</done>
</task>

<task type="auto">
  <name>Task 2: Create tagRepository with tag operations</name>
  <files>src/lib/server/db/repository.ts</files>
  <action>
Add TagRepository interface and implementation after ImageRepository:

1. Import new schema tables:
```typescript
import { entries, images, tags, entryTags, lower, ... } from './schema';
```

2. Define TagRepository interface:
```typescript
export interface TagRepository {
  findOrCreate(name: string): Tag;
  getAll(): Tag[];
  getById(id: string): Tag | undefined;
  getByEntryId(entryId: string): Tag[];
  updateEntryTags(entryId: string, tagNames: string[]): void;
}
```

3. Implement SQLiteTagRepository:
- `findOrCreate(name)`:
  - Normalize: `name.trim()`
  - Query with `sql\`lower(${tags.name}) = lower(${normalizedName})\``
  - If exists, return it
  - If not, insert with nanoid() and return

- `getAll()`:
  - Select all from tags ordered by name ASC

- `getById(id)`:
  - Simple select where eq(tags.id, id)

- `getByEntryId(entryId)`:
  - Join entry_tags with tags where entryId matches
  - Return Tag[] (the tag objects, not junction records)

- `updateEntryTags(entryId, tagNames)`:
  - Delete all from entry_tags where entryId matches
  - For each tagName: findOrCreate tag, then insert into entry_tags

4. Export singleton:
```typescript
export const tagRepository: TagRepository = new SQLiteTagRepository();
```

Import `sql` from 'drizzle-orm' for case-insensitive queries.
  </action>
  <verify>Run `npm run check` - TypeScript compiles. Manually test in dev console or add temporary test route if needed.</verify>
  <done>tagRepository exported with findOrCreate, getAll, getById, getByEntryId, and updateEntryTags methods working correctly</done>
</task>

</tasks>

<verification>
1. `npm run check` passes
2. Schema includes tags table with unique case-insensitive index on name
3. Schema includes entry_tags with composite PK and cascade deletes
4. tagRepository singleton exported with all five methods
5. Run `npm run db:push` to sync schema to database
</verification>

<success_criteria>
- Tags table created with case-insensitive unique constraint
- Entry-tag junction table links entries to tags with cascade delete
- tagRepository.findOrCreate returns same tag for "work", "Work", "WORK"
- tagRepository.updateEntryTags atomically replaces all tags for an entry
</success_criteria>

<output>
After completion, create `.planning/phases/04-tags/04-01-SUMMARY.md`
</output>
