Milestone 2.5.G5.M2 — Multi-Label Category Support

Status: Completed (2026-08-25)
Goal: Track E — Schema Pre-Work
Phase: 2.5 — Provider Generalization & Foundation
Estimated effort: 2 days
ADR: ADR-0048

Why This Milestone Exists

The single category enum column cannot represent memories that legitimately span multiple categories — for example, "I always use dark mode because bright screens give me migraines" is both preference and factual. This milestone adds an additive join table so Phase 3's write pipeline is built against the right shape from day one.

Non-Goals

Application code depending on the join table (Phase 3)
Removing or renaming the existing category column
Changing the category taxonomy
memory_relationships (G5.M3)

What shipped

Migration 000015_create_memory_labels — ibex_core.memory_labels (not sketch memory_categories)
Columns: memory_id, org_id, label, confidence (default 1.00), created_at
Composite FK (memory_id, org_id) → memories; FORCE RLS via rls_org_visible
Trigger syncs memories.category from highest-confidence label (confidence DESC, label ASC)
Backfill from existing scalar category; cascade delete with parent memory
Integration tests; ADR-0048; DATABASE_SCHEMA; 3.1.1 / 3.C.4 expand notes

Success signals

Migration applies cleanly on top of ibex_core.memories (000014); no app writers yet
RLS policy on memory_labels matches memories (FORCE + rls_org_visible)
make db-migrate succeeds (version 15)
Forward-only, expand-contract consistent with ADR-0005
DATABASE_SCHEMA.md updated

Prerequisites

Milestone 2.5.G5.M1 (temporal validity / memories foundation)

Orientation (historical sketch)

SQL

-- Sketch only — shipped path uses memory_labels + org_id (000015 / ADR-0048)
CREATE TABLE ibex_core.memory_categories (
 memory_id UUID NOT NULL REFERENCES ibex_core.memories(id) ON DELETE CASCADE,
 category TEXT NOT NULL CHECK (category IN ('factual','preference','behavioral','episodic','procedural')),
 confidence NUMERIC(3,2) NOT NULL DEFAULT 1.00,
 PRIMARY KEY (memory_id, category)
);