8f05a7c188
- Update ASR, face, OCR, pose processors - Add release pre-flight check script - Add synonym generation, chunk processing scripts - Add face recognition, stamp search utilities
34 lines
1.6 KiB
SQL
34 lines
1.6 KiB
SQL
-- P2: Person Identity & Talent Management
|
|
-- 1. Create Talents table (Global Identities / TMDB Actors)
|
|
CREATE TABLE IF NOT EXISTS talents (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
real_name VARCHAR(255) NOT NULL UNIQUE,
|
|
actor_name VARCHAR(255),
|
|
voice_embedding TEXT,
|
|
face_embedding TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 2. Create Identity Bindings (Maps machine IDs to Talents)
|
|
CREATE TABLE IF NOT EXISTS identity_bindings (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
talent_id BIGINT REFERENCES talents(id) ON DELETE CASCADE,
|
|
binding_type VARCHAR(20) NOT NULL, -- 'face', 'speaker'
|
|
binding_value VARCHAR(100) NOT NULL,
|
|
source VARCHAR(50) DEFAULT 'manual',
|
|
confidence DOUBLE PRECISION DEFAULT 1.0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(talent_id, binding_type, binding_value)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_identity_bindings_talent ON identity_bindings(talent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_identity_bindings_value ON identity_bindings(binding_type, binding_value);
|
|
|
|
-- 3. Extend person_identities with temporal overlap and confidence fields
|
|
ALTER TABLE person_identities ADD COLUMN IF NOT EXISTS character_name VARCHAR(255);
|
|
ALTER TABLE person_identities ADD COLUMN IF NOT EXISTS global_person_id BIGINT REFERENCES talents(id);
|
|
ALTER TABLE person_identities ADD COLUMN IF NOT EXISTS temporal_overlap_score DOUBLE PRECISION;
|
|
ALTER TABLE person_identities ADD COLUMN IF NOT EXISTS audio_visual_confidence DOUBLE PRECISION;
|
|
ALTER TABLE person_identities ADD COLUMN IF NOT EXISTS match_strategy VARCHAR(30);
|