93 lines
3.1 KiB
PL/PgSQL
93 lines
3.1 KiB
PL/PgSQL
-- ============================================================
|
|
-- Public Schema Migration: V3.x → V4.0
|
|
-- Purpose: Sync public schema with dev schema for v1.0.0 release
|
|
-- Date: 2026-04-30
|
|
-- ============================================================
|
|
|
|
BEGIN;
|
|
|
|
-- ============================================================
|
|
-- 1. videos table: Add missing columns
|
|
-- ============================================================
|
|
|
|
-- Add parent_uuid column (V4.0: for parent-child video relationships)
|
|
ALTER TABLE public.videos
|
|
ADD COLUMN IF NOT EXISTS parent_uuid VARCHAR(255);
|
|
|
|
-- ============================================================
|
|
-- 2. chunks table: Add missing columns
|
|
-- ============================================================
|
|
|
|
-- Add summary_text (V4.0: LLM-generated summaries)
|
|
ALTER TABLE public.chunks
|
|
ADD COLUMN IF NOT EXISTS summary_text TEXT;
|
|
|
|
-- Add metadata_version (V4.0: track metadata updates)
|
|
ALTER TABLE public.chunks
|
|
ADD COLUMN IF NOT EXISTS metadata_version INTEGER DEFAULT 0;
|
|
|
|
-- Add content_version (V4.0: track content updates)
|
|
ALTER TABLE public.chunks
|
|
ADD COLUMN IF NOT EXISTS content_version INTEGER DEFAULT 0;
|
|
|
|
-- ============================================================
|
|
-- 3. face_detections table: Migrate V3.x → V4.0 structure
|
|
-- ============================================================
|
|
|
|
-- Step 3a: Add new V4.0 columns
|
|
ALTER TABLE public.face_detections
|
|
ADD COLUMN IF NOT EXISTS file_uuid VARCHAR(255);
|
|
|
|
ALTER TABLE public.face_detections
|
|
ADD COLUMN IF NOT EXISTS bbox JSONB;
|
|
|
|
-- Step 3b: Migrate video_uuid → file_uuid
|
|
UPDATE public.face_detections
|
|
SET file_uuid = video_uuid
|
|
WHERE file_uuid IS NULL AND video_uuid IS NOT NULL;
|
|
|
|
-- Step 3c: Create bbox JSONB from x/y/width/height columns
|
|
UPDATE public.face_detections
|
|
SET bbox = jsonb_build_object(
|
|
'x', x,
|
|
'y', y,
|
|
'width', width,
|
|
'height', height
|
|
)
|
|
WHERE bbox IS NULL AND x IS NOT NULL;
|
|
|
|
-- Step 3d: Add NOT NULL constraint to file_uuid (after migration)
|
|
ALTER TABLE public.face_detections
|
|
ALTER COLUMN file_uuid SET NOT NULL;
|
|
|
|
-- Step 3e: Add index for new file_uuid column
|
|
CREATE INDEX IF NOT EXISTS idx_face_detections_file_uuid
|
|
ON public.face_detections(file_uuid);
|
|
|
|
-- Note: We keep old columns (video_uuid, x, y, width, height, timestamp_secs,
|
|
-- embedding, identity_confidence, cluster_id) for backward compatibility.
|
|
-- They can be removed in a future cleanup after verifying no dependencies.
|
|
|
|
-- ============================================================
|
|
-- 4. Verify migration
|
|
-- ============================================================
|
|
|
|
-- Check video count
|
|
SELECT COUNT(*) AS total_videos FROM public.videos;
|
|
|
|
-- Check chunk columns
|
|
SELECT column_name, data_type
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = 'chunks'
|
|
AND column_name IN ('summary_text', 'metadata_version', 'content_version')
|
|
ORDER BY column_name;
|
|
|
|
-- Check face_detections migration
|
|
SELECT
|
|
COUNT(*) AS total_faces,
|
|
COUNT(CASE WHEN file_uuid IS NOT NULL THEN 1 END) AS with_file_uuid,
|
|
COUNT(CASE WHEN bbox IS NOT NULL THEN 1 END) AS with_bbox
|
|
FROM public.face_detections;
|
|
|
|
COMMIT;
|