-- D2 的检索结果使用独立快照,避免更改方案后污染过去的结果和顺序。 CREATE TABLE search_scheme ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, FOREIGN KEY (user_id) REFERENCES app_user(id), name VARCHAR(128) NOT NULL, conditions TEXT NOT NULL, version BIGINT NOT NULL DEFAULT 1 ); CREATE TABLE search_batch ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, FOREIGN KEY (user_id) REFERENCES app_user(id), scheme_id VARCHAR(36), FOREIGN KEY (scheme_id) REFERENCES search_scheme(id) ON DELETE SET NULL, conditions TEXT NOT NULL, platform_total BIGINT, fetched_count INT NOT NULL DEFAULT 0, scope_summary TEXT, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ); CREATE TABLE search_item ( batch_id VARCHAR(36) NOT NULL, FOREIGN KEY (batch_id) REFERENCES search_batch(id) ON DELETE CASCADE, position INT NOT NULL, project_id VARCHAR(64) NOT NULL, FOREIGN KEY (project_id) REFERENCES project(id), snapshot TEXT NOT NULL, PRIMARY KEY(batch_id, position) ); CREATE TABLE conversation ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, FOREIGN KEY (user_id) REFERENCES app_user(id), title VARCHAR(128) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ); CREATE TABLE conversation_message ( id VARCHAR(36) PRIMARY KEY, conversation_id VARCHAR(36) NOT NULL, FOREIGN KEY (conversation_id) REFERENCES conversation(id) ON DELETE CASCADE, role VARCHAR(16) NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) );