phase: 03-db-api-channel-awareness
plan: 01
subsystem: database
tags: [migration, schema, channels, pgvector, postgresql]
dependency_graph:
requires: []
provides: [channels-table, chunks-channel-id, channel-fk, channel-index]
affects: [schema.sql, migrate_channels.sql]
tech_stack:
added: []
patterns: [idempotent-migration, DO-block-constraint-check, IF-NOT-EXISTS]
key_files:
created:
- migrate_channels.sql
modified:
- schema.sql
decisions:
- "channels.id is TEXT (slug-as-PK) — keeps FK joins simple, avoids serial-id indirection"
- "channel_id DEFAULT 'general' on ADD COLUMN — ensures new rows auto-assigned without backfill gap"
- "NOT NULL enforced after backfill — safe ordering: add column, fill, constrain"
- "FK via idempotent DO block — ALTER TABLE ADD COLUMN IF NOT EXISTS has no equivalent for constraints"
metrics:
duration: "~1 minute"
completed: "2026-03-24"
tasks_completed: 2
tasks_total: 2
files_changed: 2
Phase 03 Plan 01: DB Migration — Channels Table + channel_id Summary
One-liner: Idempotent SQL migration adds channels table with TEXT-slug PK, seeds "general", adds channel_id FK column with index to chunks, and updates schema.sql to reflect final DB state.
Tasks Completed
| Task | Name | Commit | Files |
|---|---|---|---|
| 1 | Create idempotent channel migration script | 960dd09 | migrate_channels.sql (created) |
| 2 | Update schema.sql to reflect final DB state | 5e42c37 | schema.sql (modified) |
What Was Built
migrate_channels.sql
A fully idempotent migration script (8 sections):
1. CREATE TABLE IF NOT EXISTS channels — TEXT slug PK, UNIQUE display name
2. Seed INSERT INTO channels ('general', 'General') ON CONFLICT DO NOTHING
3. ALTER TABLE chunks ADD COLUMN IF NOT EXISTS channel_id TEXT DEFAULT 'general'
4. Backfill: UPDATE chunks SET channel_id = 'general' WHERE channel_id IS NULL
5. ALTER TABLE chunks ALTER COLUMN channel_id SET NOT NULL
6. FK via DO block: checks information_schema.table_constraints before adding
7. CREATE INDEX IF NOT EXISTS idx_chunks_channel_id ON chunks(channel_id)
8. RAISE NOTICE verification block (counts channels and chunks with/without channel_id)
schema.sql
Updated to reflect post-migration state for fresh installs:
- channels table defined BEFORE chunks (FK target ordering)
- Default channel seeded before chunks table definition
- chunks.channel_id: TEXT NOT NULL DEFAULT 'general' REFERENCES channels(id)
- Both indexes present: ivfflat embedding index and idx_chunks_channel_id
- Explanatory comment on ivfflat lists=1 preserved
Deviations from Plan
None — plan executed exactly as written.
The worktree's schema.sql had lists = 100 while the plan spec showed lists = 1. Changed to lists = 1 per plan spec (consistent with the main repo schema.sql which also uses lists=1 with the small-dataset comment).
Known Stubs
None. This plan is pure SQL schema/migration with no application logic or UI rendering.