Milestone 3.1.1 — Memory schema v2: HNSW, temporal validity, multi-label

Status: Completed
Goal: Track A — Schema & Migrations
Phase: 3 — Core Memory Substrate
Estimated effort: 4–5 days
Track: Track A — Schema & Migrations
ADR: ADR-0052 (expand — not ADR-0040). Temporal foundation ADR-0047; multi-label ADR-0048; relationship graph ADR-0049.
Completed: 2026-08-25
Migration: 000017_memory_schema_v2_expand

Post–G5.M1 / G5.M2 / G5.M3: expand, do not CREATE

After 2.5.G5.M1, ibex_core.memories already exists (000014) with valid_from / valid_until / observed_at, FORCE RLS, and composite org-safe FKs. After 2.5.G5.M2, ibex_core.memory_labels already exists (000015) with org_id, composite FK, FORCE RLS, and primary sync onto memories.category — do not CREATE TABLE memory_labels again. After 2.5.G5.M3, ibex_core.memory_relationships already exists (000016) with dual composite FKs, FORCE RLS, bidirectional traversal indexes, supersession view, and tip helper — do not CREATE TABLE memory_relationships again (ADR-0049). Draft RLS that uses memory_id IN (SELECT id FROM memories) is incorrect; keep rls_org_visible(org_id). Milestone 3.1.1 is an expand migration (ADD COLUMN embedding…, HNSW, remaining quality columns). Keep observed_at.

Why This Milestone Exists

Every later milestone in Phase 3, 3.5, and beyond depends on embedding + ANN search + quality columns on the memories foundation. Phase 2.5 Track E already fixed temporal validity and multi-label taxonomy at the schema layer; 3.1.1 completes the expand with HNSW (not IVFFlat) and the remaining quality / hybrid-search columns.

Non-Goals

Any memory service business logic (Track C and D)
The embedding service itself (built in Phase 2.5)
Context assembly, extraction workers, or dashboard (Phase 3.5+)
Alembic / Python ORM mirror (golang-migrate remains source of truth per ADR-0005)
Physical rename of category → primary_category (deferred; ADR-0052)

Deliverables (shipped)

Migration files

infra/migrations/postgres/
 000017_memory_schema_v2_expand.up.sql
 000017_memory_schema_v2_expand.down.sql

Expand summary

CREATE EXTENSION IF NOT EXISTS vector
Columns: embedding vector(1024), embedding_model (≤256 chars), embedding_dim (triplet CHECK), confidence, usefulness_score, source, superseded_by / merged_into (composite FKs), retrieval_count, last_retrieved_at, pii_detected, pii_redacted, metadata (JSON object, ≤8192 octets text), generated search_vector
Indexes: idx_memories_embedding_hnsw (HNSW), idx_memories_validity, idx_memories_search_vector
Hidden mutation (pre-existing, documented in migration comments): sync_memory_primary_category

Success signals

Migration applies cleanly from zero via make db-migrate / CI db-migrate-smoke
idx_memories_embedding_hnsw confirmed as HNSW type via pg_am catalog assertion
RLS integration test proves cross-org denial on memories after expand
Primary-category sync regression (existing trigger; two labels, confidence + label ASC tie-break)
ADR-0052 published in web/content/docs/adr/ (not ADR-0040)

Prerequisites

Phase 2.5 exit (pluggable embedder, MCP skeleton, G5.M1–M3 schema)
infra/migrations/postgres/ layout follows ADR-0005