prazy1208/text2sql

1-- Backfill session metadata for legacy rows (run once against text2sql_db).2-- Safe to re-run for title backfill: only fills sessions where title IS NULL or blank.3--4-- Usage:5--   psql "$DATABASE_URL" -f scripts/backfill_session_metadata.sql6--   or: python scripts/run_backfill_session_metadata.py7--   or paste sections into pgAdmin / Supabase SQL Editor.8 9BEGIN;10 11-- ---------------------------------------------------------------------------12-- 1) Title from first user message (matches app derive_session_title logic:13--    collapse whitespace, max 60 chars, ellipsis)14-- ---------------------------------------------------------------------------15WITH first_user AS (16  SELECT DISTINCT ON (session_id)17    session_id,18    regexp_replace(trim(content), '\s+', ' ', 'g') AS normalized19  FROM app_schema.chat_messages20  WHERE lower(trim(role)) = 'user'21  ORDER BY session_id, id ASC22),23titles AS (24  SELECT25    session_id,26    CASE27      WHEN normalized IS NULL OR normalized = '' THEN 'New chat'28      WHEN char_length(normalized) <= 60 THEN normalized29      ELSE (left(normalized, 59) || E'…')30    END AS new_title31  FROM first_user32)33UPDATE app_schema.sessions s34SET title = t.new_title,35    updated_at = CURRENT_TIMESTAMP36FROM titles t37WHERE s.session_id = t.session_id38  AND (s.title IS NULL OR trim(s.title) = '');39 40-- ---------------------------------------------------------------------------41-- 2) client_id: use Python runner (parameterized) instead of pasting UUID here:42--    python scripts/run_backfill_session_metadata.py --client-id "<text2sql_client_id>"43--    Or uncomment and replace the UUID below (sessions with messages, NULL client_id only).44-- ---------------------------------------------------------------------------45-- UPDATE app_schema.sessions s46-- SET client_id = '00000000-0000-0000-0000-000000000000'::uuid,47--     updated_at = CURRENT_TIMESTAMP48-- WHERE s.client_id IS NULL49--   AND EXISTS (50--     SELECT 1 FROM app_schema.chat_messages m WHERE m.session_id = s.session_id51--   );52 53-- ---------------------------------------------------------------------------54-- 3) OPTIONAL: Remove empty shell sessions (no messages). Uncomment to run.55-- ---------------------------------------------------------------------------56-- DELETE FROM app_schema.sessions s57-- WHERE NOT EXISTS (58--   SELECT 1 FROM app_schema.chat_messages m WHERE m.session_id = s.session_id59-- );60 61COMMIT;62