97 lines
4.0 KiB
PL/PgSQL
97 lines
4.0 KiB
PL/PgSQL
-- ============================================================
|
|
-- Public Schema Migration V4.0 (Complete)
|
|
-- Purpose: Sync public schema with dev schema for v1.0.0 release
|
|
-- Date: 2026-04-30
|
|
-- ============================================================
|
|
|
|
BEGIN;
|
|
|
|
-- ============================================================
|
|
-- 1. videos table
|
|
-- ============================================================
|
|
ALTER TABLE public.videos ADD COLUMN IF NOT EXISTS parent_uuid VARCHAR(255);
|
|
|
|
-- ============================================================
|
|
-- 2. chunks table
|
|
-- ============================================================
|
|
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS summary_text TEXT;
|
|
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS metadata_version INTEGER DEFAULT 0;
|
|
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS content_version INTEGER DEFAULT 0;
|
|
|
|
-- ============================================================
|
|
-- 3. face_detections table - Migrate V3.x → V4.0
|
|
-- ============================================================
|
|
|
|
-- Add 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;
|
|
|
|
-- Migrate data from old columns to new columns
|
|
UPDATE public.face_detections
|
|
SET file_uuid = video_uuid
|
|
WHERE file_uuid IS NULL AND video_uuid IS NOT NULL;
|
|
|
|
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;
|
|
|
|
-- Make file_uuid NOT NULL after migration
|
|
ALTER TABLE public.face_detections ALTER COLUMN file_uuid SET NOT NULL;
|
|
|
|
-- Add indexes
|
|
CREATE INDEX IF NOT EXISTS idx_face_detections_file_uuid ON public.face_detections(file_uuid);
|
|
CREATE INDEX IF NOT EXISTS idx_face_detections_identity_id ON public.face_detections(identity_id) WHERE identity_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_face_detections_candidates ON public.face_detections(confidence DESC) WHERE identity_id IS NULL;
|
|
|
|
-- ============================================================
|
|
-- 4. pre_chunks table
|
|
-- ============================================================
|
|
ALTER TABLE public.pre_chunks ADD COLUMN IF NOT EXISTS timestamp DOUBLE PRECISION;
|
|
|
|
-- ============================================================
|
|
-- 5. file_identities table (V4.0 N:N relationship)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS public.file_identities (
|
|
id SERIAL PRIMARY KEY,
|
|
file_uuid VARCHAR(255) NOT NULL,
|
|
identity_id INTEGER NOT NULL REFERENCES public.identities(id),
|
|
face_count INTEGER DEFAULT 0,
|
|
speaker_count INTEGER DEFAULT 0,
|
|
first_appearance BIGINT,
|
|
last_appearance BIGINT,
|
|
confidence DOUBLE PRECISION DEFAULT 1.0,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(file_uuid, identity_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_file_identities_file ON public.file_identities(file_uuid);
|
|
CREATE INDEX IF NOT EXISTS idx_file_identities_identity ON public.file_identities(identity_id);
|
|
|
|
-- ============================================================
|
|
-- 6. jobs table updates
|
|
-- ============================================================
|
|
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS error_message TEXT;
|
|
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS processor_list JSONB;
|
|
|
|
-- ============================================================
|
|
-- Verification
|
|
-- ============================================================
|
|
SELECT 'Migration Complete' AS status;
|
|
|
|
-- Check column counts
|
|
SELECT table_name, COUNT(*) AS column_count
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name IN ('videos', 'chunks', 'face_detections', 'pre_chunks', 'file_identities', 'jobs')
|
|
GROUP BY table_name
|
|
ORDER BY table_name;
|
|
|
|
-- Check data 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;
|