06-user-import-reuse-query-spec
默认周度查询:合集运营与增量主题
Section titled “默认周度查询:合集运营与增量主题”本规范默认不执行下方旧完整QA快照。冻结当前registry水位lower和本轮MAX(id) upper,只取lower < canonical_import_id ≤ upper的导入ID、标题、来源、创建时间、来源时长、公开/私有范围与必要处理状态。用户ID不返回;正文、全库sentence_stats、旧HOLD、历史音频offset全扫不进入默认采集。元数据来自24h内本轮快照时直接复用原观察时间。
默认查询先核验实际数据库身份与相关表schema;只有单条SELECT/WITH。平台明确匹配YouTube/Bilibili,其他类型单列,不能用ELSE猜平台。去重保留YouTube/BVID大小写和分P;不能将包含文章等类型的upper当视频成功水位。标题主题归类后,如需独立用户覆盖,对该主题明确源键和同一lower/upper范围做COUNT(DISTINCT pluto_user_id),只返回聚合;逐条人数不能相加,缺跨期cohort则持续性UNKNOWN。
合集表现来自生产iOS公开目录与GA实际合集上下文聚合,不能把staging相同ID当生产成员映射。已有库存仅作ID/标题/来源索引。最终候选唯一成员才查必要播放、字幕、版权、难度、地区、完整已知合集权益与alias;重复成员共享证据。查不到保持UNKNOWN并block,不用质量机器人flag当已确认缺陷。原保护/人工QA/历史候选不改。
按需兼容:旧质量工具契约
Section titled “按需兼容:旧质量工具契约”以下原文保留供明确调用旧QA工具时查阅;其中全量与HOLD重查不再是默认周度行为。它不是新默认run.mjs的输入采集要求。
用户导入复用周报:只读数据契约与查询规范
Section titled “用户导入复用周报:只读数据契约与查询规范”Python 规则引擎不持有数据库或 Admin 凭证。每周 heartbeat 通过 mysql-access-mcp 从当次确认的环境读取只读数据,先生成不含原始用户 ID 的快照,再交给 scripts/yomiya_user_import_reuse_radar.py。默认环境是当前设计基线 yomiya-staging;任何真实分发前必须重新确认环境。
核心表:
user_content_imports:导入事实、水位、URL 和元数据。transcription_tasks、news、news_sentences、audio_sentence_offsets:处理、可见性与轻审事实。editorial_collections、editorial_collection_items、editorial_collection_imports:已有合集和条目冲突。featured_home_layouts、YouTube/Bilibili playlist 表:活动首页及其内容冲突。
所有 SQL 必须是单条 SELECT / WITH。不得执行 INSERT、UPDATE、DELETE、DDL 或存储过程。
运行前硬检查
Section titled “运行前硬检查”ping返回目标 database,写入快照environment。- 上述核心表均可读,并记录各表 schema;任一核心表/查询失败,设置
core_complete=false。 - 读取活动首页
is_active=1,如果有多条活动布局要SKIP,不得任选一条。 - 读取全部 EditorialCollections(包括空合集),不能只检查本流程创建的合集。
- 快照完成前不推进登记表水位。
以下是字段契约;可按 canonical_import_id > :last_id 分页,每页最多 200 行。第一次/全量对账从 0 开始。SQL 聚合用户数量,但不返回 pluto_user_id。
WITH sentence_stats AS ( SELECT ns.news_id, COUNT(*) AS body_sentence_count, SUM(CASE WHEN LOWER(COALESCE(ns.content_language, '')) IN ('ja', 'ja-jp') THEN 1 ELSE 0 END) AS japanese_sentence_count, SUM(CASE WHEN aso.sentence_id IS NOT NULL THEN 1 ELSE 0 END) AS aligned_sentence_count, MIN(aso.offset) AS first_offset_ms, MAX(aso.offset + aso.duration_ms) AS last_offset_end_ms FROM news_sentences ns LEFT JOIN audio_sentence_offsets aso ON aso.article_id = ns.news_id AND aso.sentence_id = ns.id WHERE LOWER(COALESCE(ns.type, 'body')) NOT IN ('title', 'translation') GROUP BY ns.news_id), import_demand AS ( SELECT content_type, unique_id, COUNT(*) AS import_count, COUNT(DISTINCT pluto_user_id) AS distinct_user_count, MAX(id) AS canonical_import_id FROM user_content_imports GROUP BY content_type, unique_id), ranked_imports AS ( SELECT uci.*, ROW_NUMBER() OVER ( PARTITION BY uci.content_type, uci.unique_id ORDER BY CASE WHEN tt.status = 'COMPLETED' THEN 1 ELSE 0 END DESC, CASE WHEN uci.news_id IS NOT NULL THEN 1 ELSE 0 END DESC, uci.id DESC ) AS row_rank FROM user_content_imports uci LEFT JOIN transcription_tasks tt ON tt.id = uci.transcription_task_id), existing_editorial_news AS ( SELECT news_id FROM editorial_collection_items UNION SELECT news_id FROM editorial_collection_imports WHERE news_id IS NOT NULL), existing_editorial_keys AS ( SELECT DISTINCT unique_id FROM editorial_collection_imports WHERE unique_id IS NOT NULL AND unique_id <> ''), home_youtube_playlists AS ( SELECT DISTINCT CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(src.source_id, '-', 2), '-', -1) AS UNSIGNED ) AS playlist_id FROM featured_home_layouts fhl JOIN JSON_TABLE(fhl.sections, '$[*]' COLUMNS(section JSON PATH '$')) sec JOIN JSON_TABLE(sec.section, '$.sources[*]' COLUMNS( source_type VARCHAR(64) PATH '$.type', source_id VARCHAR(64) PATH '$.id' )) src WHERE fhl.is_active = 1 AND src.source_type IN ('youtube_playlist', 'youtube_playlist_items')), active_home_youtube_videos AS ( SELECT DISTINCT ypv.video_id FROM youtube_playlist_videos ypv JOIN home_youtube_playlists hp ON hp.playlist_id = ypv.youtube_playlist_id WHERE ypv.removed_at IS NULL)SELECT d.canonical_import_id, ri.created_at AS imported_at, CASE WHEN LOWER(ri.content_type) LIKE '%bilibili%' OR ri.unique_id LIKE 'Bilibili_%' THEN 'BILIBILI' ELSE 'YOUTUBE' END AS platform, ri.unique_id, ri.url, ri.title, ri.thumbnail_url, ri.duration AS duration_seconds, ri.availability, tt.status AS task_status, tt.review_status, ri.news_id, n.visibility AS news_visibility, n.source_scope AS news_source_scope, n.content_language, n.duration_seconds AS news_duration_seconds, COALESCE(ss.body_sentence_count, 0) AS body_sentence_count, COALESCE(ss.aligned_sentence_count, 0) AS aligned_sentence_count, COALESCE(ss.japanese_sentence_count, 0) AS japanese_sentence_count, CASE WHEN COALESCE(ss.body_sentence_count, 0) = 0 THEN NULL ELSE ss.japanese_sentence_count / ss.body_sentence_count END AS japanese_sentence_ratio, ss.first_offset_ms, ss.last_offset_end_ms, CASE WHEN ahv.video_id IS NULL THEN FALSE ELSE TRUE END AS in_active_home, CASE WHEN een.news_id IS NULL AND eek.unique_id IS NULL THEN FALSE ELSE TRUE END AS in_existing_editorial, d.import_count, d.distinct_user_countFROM import_demand dJOIN ranked_imports ri ON ri.content_type = d.content_type AND ri.unique_id = d.unique_id AND ri.row_rank = 1LEFT JOIN transcription_tasks tt ON tt.id = ri.transcription_task_idLEFT JOIN news n ON n.id = ri.news_idLEFT JOIN sentence_stats ss ON ss.news_id = ri.news_idLEFT JOIN existing_editorial_news een ON een.news_id = ri.news_idLEFT JOIN existing_editorial_keys eek ON eek.unique_id = ri.unique_idLEFT JOIN active_home_youtube_videos ahv ON ahv.video_id = CASE WHEN ri.unique_id LIKE 'YouTube_%' THEN SUBSTRING(ri.unique_id, LENGTH('YouTube_') + 1) ELSE NULL ENDWHERE d.canonical_import_id > :last_idORDER BY d.canonical_import_idLIMIT 200;分页结束条件是返回行数少于 200;合并后再次按脚本的平台键去重:YouTube 为 video_id,Bilibili 为 bvid + cid/page。不得只按母 BVID 去重。
活动首页与合集快照
Section titled “活动首页与合集快照”活动首页必须保留 id、name、updated_at、全部 section/source 的 type/id,并解析出首页实际覆盖的 news ID。合集快照至少包含:
SELECT ec.id, ec.title, ec.description, ec.deleted_at, ec.created_at, ec.updated_at, COUNT(DISTINCT eci.news_id) AS item_count, COUNT(DISTINCT eci2.transcription_task_id) AS import_countFROM editorial_collections ecLEFT JOIN editorial_collection_items eci ON eci.collection_id = ec.idLEFT JOIN editorial_collection_imports eci2 ON eci2.collection_id = ec.idGROUP BY ec.id, ec.title, ec.description, ec.deleted_at, ec.created_at, ec.updated_at;受管合集的 OPEN、容量、专题 8 周期限以本地登记 JSON 为准;数据库里的历史合集不得被脚本自行标成受管 OPEN。
平台 VIP 库存与需求集中度
Section titled “平台 VIP 库存与需求集中度”平台库存只统计当前可见内容,用于 45%–60% 监控:
SELECT CASE WHEN c.source_type = 'bilibili' THEN 'BILIBILI' WHEN c.source_type = 'youtube' THEN 'YOUTUBE' END AS platform, n.visibility, COUNT(*) AS countFROM news nJOIN channels c ON c.id = n.channel_idWHERE c.source_type IN ('bilibili', 'youtube') AND n.visibility IN ('VISIBLE', 'MEMBERSHIP_VISIBLE', 'TIME_LIMIT_VISIBLE')GROUP BY platform, n.visibility;必须排除 news、podcast 等其他 channels.source_type;不能把所有非 Bilibili 内容都归入 YouTube。2026-08-31 的实时对账证明,错误的 ELSE 'YOUTUBE' 会把新闻与播客库存计入 YouTube,显著扭曲 VIP 比例。
需求集中度只能返回聚合数字:每个平台导入人数、导入总数、Top 1/Top 5 占比。查询内部可以使用用户 ID,输出快照不得包含用户 ID、邮箱、令牌或其他后台敏感数据。
处理供应商必须按去重后的已完成内容实时重算,并写入 processing_provider_summary。优先使用 actual_transcription_provider,否则回退到 transcription_provider,按 news.duration_seconds 计算分钟;不能长期沿用启动基线里的 ElevenLabs 时长。至少输出平台、供应商、内容数和完成分钟数。
分发效果与首页候选数据契约
Section titled “分发效果与首页候选数据契约”周任务不修改首页。对于登记为 OPEN / DISTRIBUTED 且有 distributed_at 的受管合集,只有上线满 14 天后才读取 collection_metrics:
{ "collection_id": 88, "collection_view": 35, "click_through_rate": 0.22, "platform_click_through_median": 0.20, "effective_learning_rate": 0.60, "platform_effective_learning_median": 0.55, "source_stable": true, "fills_home_gap": true, "duplicates_home_promise": false}缺少任一字段或同平台中位数时必须为 SKIP。全部通过时只标记 READY_HOME_PROPOSAL,仍须另提首页方案并单独确认。
平台源播放检查
Section titled “平台源播放检查”source_playable 只接受以下证据:
- YouTube:当前视频公开元数据/播放器端点明确可用;private、deleted、unavailable 为失败。
- Bilibili:公开 view API 返回
code=0,母 BVID 存在,并且指定cid/page仍存在。 - 已缓存检查最多复用 7 天;待分发内容在执行 Admin 操作前必须当天再查。
网络超时或响应不确定时写 null,状态进入 HOLD_QA;不能把超时当作可播放。明确下架进入 SOURCE_REMOVED。
快照 JSON 最小结构
Section titled “快照 JSON 最小结构”{ "schema_version": 1, "environment": "yomiya-staging", "generated_at": "ISO-8601", "core_complete": true, "watermark": {"maximum_import_id": 709}, "active_home_layout": {}, "editorial_collections": [], "platform_visibility": [], "user_concentration": [], "processing_provider_summary": [], "collection_metrics": [], "imports": []}| 故障 | 报告 | 水位 | Slack | Admin |
|---|---|---|---|---|
| MySQL / 核心表失败 | SKIP | 不推进 | 仅异常摘要/预览 | 无写入 |
| Admin/首页解析失败 | SKIP | 不推进 | 仅异常摘要/预览 | 无写入 |
| 源播放检查失败 | 对应内容 HOLD_QA | 核心查询完整时可推进 | 放入 Thread 3 | 无写入 |
| Slack 明确失败 | 本地报告保留 | 已按核心数据规则处理 | 重试一次 | 无写入 |
| Slack 超时/不确定 | 本地报告保留 | 已按核心数据规则处理 | 不重发,防重复 | 无写入 |
python3 scripts/yomiya_user_import_reuse_radar.py \ --snapshot product/YomiyaContentOperator/reports/YYYY-MM-DD-user-import-reuse-source.json \ --date YYYY-MM-DD \ --update-registry \ --print-slack默认只生成 Slack 预览。第一次真实测试必须另行确认;不要在 heartbeat 提示词中绕过登记表双重开关。