88 lines
4.3 KiB
SQL
88 lines
4.3 KiB
SQL
-- Bot Persona A/B Testing
|
|
-- Adds: bot_persona_variants, bot_persona_experiments, bot_persona_assignments, bot_persona_metrics
|
|
|
|
CREATE TABLE `bot_persona_variants` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`bot_id` INT NOT NULL,
|
|
`experiment_id` INT NULL,
|
|
`variant_key` VARCHAR(20) NOT NULL,
|
|
`label` VARCHAR(50) NOT NULL,
|
|
`description` VARCHAR(200) NULL,
|
|
`stance_keywords` JSON NULL,
|
|
`top_topic_types` JSON NULL,
|
|
`top_human_users` JSON NULL,
|
|
`style_hints` JSON NULL,
|
|
`weight` FLOAT NOT NULL DEFAULT 0.5,
|
|
`is_control` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`is_active` BOOLEAN NOT NULL DEFAULT TRUE,
|
|
`sample_count` INT NOT NULL DEFAULT 0,
|
|
`reply_count` INT NOT NULL DEFAULT 0,
|
|
`like_count` INT NOT NULL DEFAULT 0,
|
|
`human_reply_count` INT NOT NULL DEFAULT 0,
|
|
`engagement_score` FLOAT NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `bot_persona_variants_bot_id_variant_key_key`(`bot_id`, `variant_key`),
|
|
INDEX `bot_persona_variants_bot_id_is_active_idx`(`bot_id`, `is_active`),
|
|
INDEX `bot_persona_variants_experiment_id_idx`(`experiment_id`)
|
|
);
|
|
|
|
CREATE TABLE `bot_persona_experiments` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`bot_id` INT NOT NULL,
|
|
`name` VARCHAR(80) NOT NULL,
|
|
`description` VARCHAR(200) NULL,
|
|
`status` VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
`winner_variant_key` VARCHAR(20) NULL,
|
|
`min_samples_per_arm` INT NOT NULL DEFAULT 20,
|
|
`significance_level` FLOAT NOT NULL DEFAULT 0.1,
|
|
`primary_metric` VARCHAR(30) NOT NULL DEFAULT 'engagement_score',
|
|
`config` JSON NULL,
|
|
`started_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`ended_at` DATETIME(3) NULL,
|
|
`last_analyzed_at` DATETIME(3) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
INDEX `bot_persona_experiments_bot_id_status_idx`(`bot_id`, `status`),
|
|
INDEX `bot_persona_experiments_status_idx`(`status`)
|
|
);
|
|
|
|
CREATE TABLE `bot_persona_assignments` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`bot_id` INT NOT NULL,
|
|
`variant_id` INT NOT NULL,
|
|
`ref_type` VARCHAR(20) NOT NULL,
|
|
`ref_id` INT NOT NULL,
|
|
`forum_slug` VARCHAR(30) NULL,
|
|
`replies` INT NOT NULL DEFAULT 0,
|
|
`likes` INT NOT NULL DEFAULT 0,
|
|
`human_replies` INT NOT NULL DEFAULT 0,
|
|
`impressions` INT NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `bot_persona_assignments_ref_type_ref_id_key`(`ref_type`, `ref_id`),
|
|
INDEX `bot_persona_assignments_bot_id_variant_id_idx`(`bot_id`, `variant_id`),
|
|
INDEX `bot_persona_assignments_variant_id_created_at_idx`(`variant_id`, `created_at`)
|
|
);
|
|
|
|
CREATE TABLE `bot_persona_metrics` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`variant_id` INT NOT NULL,
|
|
`date` DATE NOT NULL,
|
|
`impressions` INT NOT NULL DEFAULT 0,
|
|
`replies` INT NOT NULL DEFAULT 0,
|
|
`likes` INT NOT NULL DEFAULT 0,
|
|
`human_replies` INT NOT NULL DEFAULT 0,
|
|
`content_count` INT NOT NULL DEFAULT 0,
|
|
`avg_replies` FLOAT NOT NULL DEFAULT 0,
|
|
`avg_likes` FLOAT NOT NULL DEFAULT 0,
|
|
`computed_score` FLOAT NOT NULL DEFAULT 0,
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `bot_persona_metrics_variant_id_date_key`(`variant_id`, `date`),
|
|
INDEX `bot_persona_metrics_date_idx`(`date`)
|
|
);
|