-- Memory candidate workflow for the reflect (Tier 2) thread.
--
-- Reflect proposes new strong/constraint memories from accumulated
-- advice + outcomes. The user reviews each one and approves or
-- rejects. Without a status column, proposed rows would show up
-- everywhere (advice prompt injection, dashboard list, audit prompt)
-- as if they were already accepted.

BEGIN;

ALTER TABLE public.memories
    ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'proposed', 'rejected')),
    ADD COLUMN IF NOT EXISTS proposed_reason text;

CREATE INDEX IF NOT EXISTS memories_status_idx
    ON public.memories (status)
    WHERE deleted_at IS NULL;

COMMIT;
