Memory Record v2 schema + provenance + migration

[valiantone]

Summary

Extend the HotMem memory record to Memory Record v2: add the vNext metadata fields (provenance, file references, tier, promotion state, relations, tags, schema_version) while keeping the current payload fully compatible. This is the schema foundation every other vNext ticket builds on.

Scope

• Extend the memories table additively: namespace, tier, memory_type, source_uri, source_format, source_checksum, byte_offset, byte_length, updated_at, snapshot_id, promotion_state, promotion_candidate, parent_memory, related_memories, tags, schema_version. New columns default to sensible values so existing rows remain valid.
• Introduce a schema_version notion (DB-level pragma + per-row) and a migration helper that upgrades an existing v0.1 DB in place (add columns via ALTER TABLE, recompute defaults).
• Extend the MemoryRecord dataclass / write+read paths to carry the new fields. Existing API payloads stay unchanged; new fields are exposed only when populated.
• Wire provenance here: every memory records source_uri, source_format, source_checksum, byte_offset, byte_length, and updated_at. Provenance must never be lost across writes/snapshots.
• Keep identifier, fact_text, embedding, importance, ttl_seconds, content_hash, and the pre-existing created_at column semantics intact.

Out of scope: file hydration-by-reference (#38), snapshot v2 format (#39), event log (#41). This ticket only defines storage of the fields. Surfacing created_at in search responses and chronological retrieval are owned by #34, not this ticket — created_at already exists in the v0.1 schema; #36 adds updated_at, not created_at.

Acceptance Criteria

• All v2 columns added additively; v0.1 DB opens and serves without manual intervention
• schema_version recorded at DB + row level; migration from v0.1 is idempotent
• MemoryRecord carries all new fields; reads default missing fields cleanly
• Write path persists provenance fields; round-trip write→read preserves them
• Existing /v1/add and /v1/search payloads unchanged for callers that don't set v2 fields
• FTS5 triggers + indexes remain functional after schema change

Migration

• ALTER TABLE memories ADD COLUMN ... for each new field with safe defaults ('', 0, NULL, 'HOT' for promotion_state, 1 for schema_version).
• On open, detect missing columns via PRAGMA table_info and add them (existing pattern in db.py).
• Bump DB schema_version pragma; refuse to silently downgrade.

Testing

• Unit: migration opens a v0.1 fixture DB, adds columns, serves existing rows with defaults.
• Unit: write/read round-trip preserves all v2 fields including provenance.
• Unit: legacy callers (no v2 fields) behave identically to v0.1.
• Regression: existing test suite green.

Depends on

• RFC: File-Aware Memory Sidecar architecture + roadmap #35 (field list + mapping ratified by the design doc)

Related

• Surface created_at and add chronological memory retrieval #34 — owns created_at surfacing + chronological retrieval (consumes the pre-existing created_at column; no schema change there).