-- Add source column to relationships table to distinguish assembly-derived
-- BOM entries from manually-added ones.
ALTER TABLE relationships
    ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'manual'
    CHECK (source IN ('manual', 'assembly'));

-- Migrate existing metadata.source values where they exist.
-- The metadata field stores source as a free-form string; promote to column.
UPDATE relationships
SET source = 'manual'
WHERE metadata->>'source' IS NOT NULL;

-- Remove the source key from metadata since it's now a dedicated column.
UPDATE relationships
SET metadata = metadata - 'source'
WHERE metadata ? 'source';