xinmi/migrations/versioned/000091_digital_humans.up.sql

CREATE TABLE IF NOT EXISTS digital_humans (
    id VARCHAR(36) PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    avatar VARCHAR(64),
    model_id VARCHAR(64),
    status VARCHAR(24) NOT NULL DEFAULT 'draft',
    latest_version INTEGER NOT NULL DEFAULT 1,
    active_version INTEGER NOT NULL DEFAULT 0,
    primary_agent_id VARCHAR(36) NOT NULL,
    processing_job_id VARCHAR(36) NOT NULL DEFAULT '',
    revision BIGINT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_dh_owner ON digital_humans (tenant_id, owner_id, created_at DESC) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS digital_human_versions (
    id VARCHAR(36) PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    digital_human_id VARCHAR(36) NOT NULL REFERENCES digital_humans(id),
    number INTEGER NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'draft',
    persona JSONB NOT NULL DEFAULT '{}',
    sources JSONB NOT NULL DEFAULT '[]',
    advice TEXT,
    review_note TEXT,
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_dh_version UNIQUE(digital_human_id, number)
);
CREATE INDEX IF NOT EXISTS idx_dh_version_owner ON digital_human_versions (tenant_id, owner_id, digital_human_id);

CREATE TABLE IF NOT EXISTS digital_human_bindings (
    id VARCHAR(36) PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    digital_human_id VARCHAR(36) NOT NULL REFERENCES digital_humans(id),
    agent_id VARCHAR(36) NOT NULL,
    base_prompt TEXT,
    applied_version INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_dh_agent UNIQUE(tenant_id, agent_id)
);
CREATE INDEX IF NOT EXISTS idx_dh_binding_human ON digital_human_bindings (tenant_id, owner_id, digital_human_id);

CREATE TABLE IF NOT EXISTS digital_human_jobs (
    id VARCHAR(36) PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    owner_id VARCHAR(36) NOT NULL,
    digital_human_id VARCHAR(36) NOT NULL REFERENCES digital_humans(id),
    status VARCHAR(24) NOT NULL,
    progress INTEGER NOT NULL DEFAULT 0,
    stage VARCHAR(255),
    error TEXT,
    version INTEGER NOT NULL DEFAULT 0,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_dh_jobs_owner ON digital_human_jobs (tenant_id, owner_id, digital_human_id, created_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS uq_dh_running_job ON digital_human_jobs (digital_human_id) WHERE status = 'running';