xinmi/migrations/versioned/000200_native_graph.up.sql

CREATE TABLE IF NOT EXISTS native_graph_records (
    tenant_id BIGINT NOT NULL,
    knowledge_base_id VARCHAR(36) NOT NULL,
    source_table TEXT NOT NULL,
    legacy_id BIGINT NOT NULL,
    graph_version_id BIGINT NOT NULL,
    payload JSONB NOT NULL,
    source_snapshot_sha256 VARCHAR(64) NOT NULL,
    imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (tenant_id, source_table, legacy_id)
);
CREATE INDEX IF NOT EXISTS idx_native_graph_scope ON native_graph_records(tenant_id, knowledge_base_id, source_table, legacy_id);
CREATE INDEX IF NOT EXISTS idx_native_graph_evidence_subject ON native_graph_records(tenant_id, knowledge_base_id, (payload->>'subject_type'), (payload->>'subject_id')) WHERE source_table = 'knowledgegraphevidence';
CREATE INDEX IF NOT EXISTS idx_native_graph_relation_from ON native_graph_records(tenant_id, knowledge_base_id, (payload->>'from_entity_id')) WHERE source_table = 'knowledgerelation';
CREATE INDEX IF NOT EXISTS idx_native_graph_relation_to ON native_graph_records(tenant_id, knowledge_base_id, (payload->>'to_entity_id')) WHERE source_table = 'knowledgerelation';
CREATE TABLE IF NOT EXISTS native_graph_sources (
    tenant_id BIGINT NOT NULL,
    knowledge_base_id VARCHAR(36) NOT NULL,
    source_chunk_id TEXT NOT NULL,
    source_file TEXT NOT NULL,
    source_content_hash TEXT NOT NULL,
    excerpt TEXT NOT NULL,
    native_knowledge_id VARCHAR(36),
    native_chunk_id VARCHAR(36),
    native_content_sha256 VARCHAR(64),
    mapping_state TEXT NOT NULL DEFAULT 'unmapped',
    PRIMARY KEY (tenant_id, knowledge_base_id, source_chunk_id)
);
CREATE INDEX IF NOT EXISTS idx_native_graph_sources_native ON native_graph_sources(tenant_id, native_chunk_id) WHERE native_chunk_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_native_graph_sources_scope ON native_graph_sources(tenant_id, knowledge_base_id, native_chunk_id);