momentry_core/migrations/006_face_recognition_tables.sql

-- ================================================================
-- Migration 006: Face Recognition Tables
-- Version: 006
-- Date: 2026-03-30
-- Description: Add tables for face recognition feature storage
--              Includes face embeddings, identities, and clusters
-- ================================================================

-- 6.1: Enable pgvector extension if not already enabled
CREATE EXTENSION IF NOT EXISTS vector;

-- 6.2: Create face_identities table
CREATE TABLE IF NOT EXISTS face_identities (
    id SERIAL PRIMARY KEY,
    face_id VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255),
    embedding VECTOR(512), -- InsightFace default embedding dimension
    attributes JSONB,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Indexes for performance
    CONSTRAINT face_identities_face_id_key UNIQUE (face_id)
);

-- 6.3: Create face_detections table
CREATE TABLE IF NOT EXISTS face_detections (
    id SERIAL PRIMARY KEY,
    video_uuid VARCHAR(255) NOT NULL,
    frame_number BIGINT NOT NULL,
    timestamp_secs DOUBLE PRECISION NOT NULL,
    face_id VARCHAR(255),
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    confidence DOUBLE PRECISION NOT NULL,
    embedding VECTOR(512),
    attributes JSONB,
    identity_id INTEGER REFERENCES face_identities(id) ON DELETE SET NULL,
    identity_confidence DOUBLE PRECISION,
    cluster_id VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Ensure unique detection per frame
    CONSTRAINT unique_detection_per_frame UNIQUE (video_uuid, frame_number, x, y, width, height)
);

-- 6.4: Create face_clusters table
CREATE TABLE IF NOT EXISTS face_clusters (
    id SERIAL PRIMARY KEY,
    cluster_id VARCHAR(255) NOT NULL,
    video_uuid VARCHAR(255) NOT NULL,
    centroid VECTOR(512),
    size INTEGER NOT NULL DEFAULT 0,
    representative_face_id VARCHAR(255),
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT face_clusters_cluster_id_key UNIQUE (cluster_id)
);

-- 6.5: Create face_recognition_results table
CREATE TABLE IF NOT EXISTS face_recognition_results (
    id SERIAL PRIMARY KEY,
    video_uuid VARCHAR(255) NOT NULL UNIQUE,
    frame_count BIGINT NOT NULL DEFAULT 0,
    fps DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    total_faces INTEGER NOT NULL DEFAULT 0,
    recognized_faces INTEGER NOT NULL DEFAULT 0,
    clusters_count INTEGER NOT NULL DEFAULT 0,
    result_data JSONB NOT NULL,
    processing_time_secs DOUBLE PRECISION,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT face_recognition_results_video_uuid_key UNIQUE (video_uuid)
);

-- 6.6: Create face_similarity_search function
CREATE OR REPLACE FUNCTION find_similar_faces(
    query_embedding VECTOR(512),
    similarity_threshold DOUBLE PRECISION DEFAULT 0.6,
    limit_count INTEGER DEFAULT 10
)
RETURNS TABLE (
    face_id VARCHAR(255),
    name VARCHAR(255),
    similarity DOUBLE PRECISION,
    attributes JSONB,
    metadata JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        fi.face_id,
        fi.name,
        1 - (fi.embedding <=> query_embedding) AS similarity,
        fi.attributes,
        fi.metadata
    FROM face_identities fi
    WHERE fi.is_active = TRUE
      AND fi.embedding IS NOT NULL
      AND 1 - (fi.embedding <=> query_embedding) >= similarity_threshold
    ORDER BY fi.embedding <=> query_embedding
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- 6.7: Create function to update face cluster centroids
CREATE OR REPLACE FUNCTION update_cluster_centroid(cluster_uuid VARCHAR(255))
RETURNS VOID AS $$
DECLARE
    new_centroid VECTOR(512);
BEGIN
    -- Calculate new centroid from all face embeddings in the cluster
    SELECT AVG(embedding) INTO new_centroid
    FROM face_detections
    WHERE cluster_id = cluster_uuid
      AND embedding IS NOT NULL;
    
    -- Update cluster centroid
    UPDATE face_clusters
    SET centroid = new_centroid,
        size = (SELECT COUNT(*) FROM face_detections WHERE cluster_id = cluster_uuid)
    WHERE cluster_id = cluster_uuid;
END;
$$ LANGUAGE plpgsql;

-- 6.8: Create function to find or create face identity
CREATE OR REPLACE FUNCTION find_or_create_face_identity(
    p_face_id VARCHAR(255),
    p_name VARCHAR(255) DEFAULT NULL,
    p_embedding VECTOR(512) DEFAULT NULL,
    p_attributes JSONB DEFAULT NULL,
    p_metadata JSONB DEFAULT '{}'::jsonb
)
RETURNS INTEGER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    -- Try to find existing face identity
    SELECT id INTO v_id
    FROM face_identities
    WHERE face_id = p_face_id;
    
    IF v_id IS NULL THEN
        -- Create new face identity
        INSERT INTO face_identities (face_id, name, embedding, attributes, metadata)
        VALUES (p_face_id, p_name, p_embedding, p_attributes, p_metadata)
        RETURNING id INTO v_id;
    ELSE
        -- Update existing face identity
        UPDATE face_identities
        SET 
            name = COALESCE(p_name, name),
            embedding = COALESCE(p_embedding, embedding),
            attributes = COALESCE(p_attributes, attributes),
            metadata = COALESCE(p_metadata, metadata),
            updated_at = CURRENT_TIMESTAMP
        WHERE id = v_id;
    END IF;
    
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

-- 6.9: Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_face_detections_video_uuid ON face_detections(video_uuid);
CREATE INDEX IF NOT EXISTS idx_face_detections_face_id ON face_detections(face_id);
CREATE INDEX IF NOT EXISTS idx_face_detections_frame ON face_detections(video_uuid, frame_number);
CREATE INDEX IF NOT EXISTS idx_face_detections_identity ON face_detections(identity_id);
CREATE INDEX IF NOT EXISTS idx_face_detections_cluster ON face_detections(cluster_id);
CREATE INDEX IF NOT EXISTS idx_face_clusters_video_uuid ON face_clusters(video_uuid);
CREATE INDEX IF NOT EXISTS idx_face_recognition_results_created_at ON face_recognition_results(created_at);

-- 6.10: Create indexes for vector similarity search
CREATE INDEX IF NOT EXISTS idx_face_identities_embedding 
ON face_identities USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

CREATE INDEX IF NOT EXISTS idx_face_detections_embedding 
ON face_detections USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- 6.11: Add comments
COMMENT ON TABLE face_identities IS 'Stores registered face identities with embeddings';
COMMENT ON TABLE face_detections IS 'Stores individual face detections from videos';
COMMENT ON TABLE face_clusters IS 'Stores face clusters from video analysis';
COMMENT ON TABLE face_recognition_results IS 'Stores face recognition processing results';
COMMENT ON FUNCTION find_similar_faces IS 'Finds similar faces based on embedding similarity';
COMMENT ON FUNCTION update_cluster_centroid IS 'Updates cluster centroid from member embeddings';
COMMENT ON FUNCTION find_or_create_face_identity IS 'Finds or creates a face identity record';

-- 6.12: Create trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create triggers only if they don't exist
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger 
        WHERE tgname = 'update_face_identities_updated_at' 
        AND tgrelid = 'face_identities'::regclass
    ) THEN
        CREATE TRIGGER update_face_identities_updated_at
            BEFORE UPDATE ON face_identities
            FOR EACH ROW
            EXECUTE FUNCTION update_updated_at_column();
    END IF;
    
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger 
        WHERE tgname = 'update_face_recognition_results_updated_at' 
        AND tgrelid = 'face_recognition_results'::regclass
    ) THEN
        CREATE TRIGGER update_face_recognition_results_updated_at
            BEFORE UPDATE ON face_recognition_results
            FOR EACH ROW
            EXECUTE FUNCTION update_updated_at_column();
    END IF;
END $$;