Read the required planning, OpenAI docs, Skill creator, Feishu publisher, and coding-guideline Skills.
Inspected the reference ODPS, Feishu Sheets, Feishu WebSocket, and portable Skill implementations.
Confirmed the target project did not already exist.
Created the project directory and initialized implementation planning files.
2026-08-12
Built the Python package, environment loader, SQLite state store, exact Feishu commands, SQL AST guard, PyODPS executor, report writer, and structured Codex runtime.
Isolated Codex in a sanitized child environment and disabled shell, unified execution, Web search, Apps, and multi-agent tools.
Implemented the Feishu WebSocket listener, precise bot mention detection, dual allowlists, fast callback queueing, threaded conversation isolation, replies, spreadsheet import, and tenant-readable link permissions.
Initialized the generic ODPS Skill, vendored five reference Skills, adapted Feishu permissions, and validated all six Skills.
Migrated authorized credentials into ignored mode-600 .env; no credential literal was added to tracked source.
Installed the self-contained virtual environment, including the pinned Codex Python SDK and bundled CLI.
Passed Python compilation, dependency checks, 15 unit tests, configuration validation, Feishu bot identity lookup, ODPS project metadata lookup, and two WebSocket/background-process smoke tests.
Lowered Feishu SDK logging after observing an ephemeral WebSocket query credential at INFO; removed that log and verified the new log is clean.
Diagnosed the apparent OpenRouter 401 by reproducing examples/auto_put_ad_mini: a stale shell variable overrode the valid project .env. Fixed dotenv precedence, added a regression test, selected the reference Google model, and passed /models/user, Chat Completions, Responses, and Codex structured-output checks.
Switched to the new Feishu application, changed authorization to allow any mentioned user inside the configured group, and established working long-connection message intake.
Confirmed repository Skill metadata is present in SDK sessions, while full script workflows are blocked by the current planner-only worker configuration.
Started Phase 6 to add structured Skill routing with host-side execution and Skill SQL/script reuse.
Classified Skill execution contracts: deterministic user timeline script; generated raw SQL plus deterministic formatters for experiment reports; reusable SQL templates for ad risk; generated SQL for generic exploration.
Added strict structured Skill routing fields to the Codex decision, enabled read-only Skill file inspection, and kept ODPS/Feishu credentials excluded from the Codex child environment.
Added an allowlisted host Skill executor for deterministic user timelines and report post-formatting.
Passed real SDK checks for generic clarification and complete timeline Skill activation; session evidence confirms full SKILL.md reference loading.
Passed real SDK routing checks for advertising-risk SQL generation and product-efficiency parameter clarification.
Removed the obsolete user allowlist configuration; authorization is now group allowlist + actual bot mention only.
Expanded the suite to 22 passing tests and passed configuration plus Feishu/ODPS/Codex doctor checks.
Completed secret and whitespace checks, restarted the Feishu long connection with the Skill-aware implementation, and confirmed the new service process is running.
Started Phase 9 to make behavior-path apptype optional without silently defaulting to product 0.
Updated the timeline Skill contract plus offline, realtime, and batch scripts so omitted apptype queries all products; explicit values remain exact filters.
Passed 27 tests, Python compilation, Skill quick validation, and a real Codex check returning apptype=null when omitted.
Restarted the Feishu long-connection service with optional apptype behavior; the new process is stable.
Began diagnosing the product-efficiency failure and reproduced its full formatter traceback against the saved raw facts without rerunning ODPS.
Completed diagnosis: parameter routing was correct, ODPS eventually succeeded, offline DAU used the wrong rootSessionId source, and the formatter then crashed while rounding nullable per-DAU rates.
Started Phase 11 to make the offline bucket mapping deterministic and harden zero-denominator handling.
Documented exact per-table rootSessionId mappings, added semantic raw-fact validation and NaN-safe rate formatting, and added three formatter regressions; the focused tests pass.
Passed the full 30-test suite and Skill quick validation; the saved broken raw result now fails with an explicit rootSessionId mapping diagnosis.
Passed a real Codex routing/SQL-generation probe for the original parameters: the generated offline DAU CTE reads GET_JSON_OBJECT(extparams, '$.rootSessionId') from loghubods.useractive_log.
Completed Python compilation, restarted the Feishu long connection, and verified PID 51256 is running with the WebSocket started and no active query runs.
Started Phase 12 to consolidate the 14 verified source-table definitions into the generic query Skill using Chinese documentation and instructions.
Rewrote the generic ODPS Skill in Chinese and added a Chinese catalog covering 14 log tables plus videoods.dim_video, including common field meanings, per-table fields, partitions, JSON-source constraints, and confirmed event values.
Added catalog coverage tests; the focused tests and Skill quick validation pass.
Passed a real generic-query planning check using simpleevent_log, dt, apptype, and businesstype from the new Chinese catalog.
Found a persisted old Codex thread could revive the pre-fix product-efficiency field expression; added a host-side SQL contract check that blocks it before ODPS and routes it through repair.
Passed all 34 tests and restarted the Feishu WebSocket service with the Chinese catalog and host-side contract guard.
Started Phase 13 to review the newly modified product-efficiency Skill, validate its behavior, and reload the service safely.
Reviewed the 15:39 update across Skill docs, SQL guard, and tests: it removes business from product-efficiency video filtering, relies on businesstype, and teaches partition preflight to require dt without forcing the business subpartition.
Confirmed a successful saved SQL still passes ODPS metadata preflight after removing v.business IS NOT NULL; focused Skill/guard validation passed.
Passed all 38 tests, Python compilation, and Skill validation; restarted the Feishu service and verified PID 57203 with the WebSocket active and zero running queries.
Diagnosed the latest report's zero-return result: raw ODPS facts were already zero because the source-share filter used type='share' instead of topic='share'; a read-only diagnostic confirmed 605,411 matched return users under the correct contract. No production code or report was changed.
Started Phase 15 to make source topic='share' and click topic='click' explicit in the product-efficiency Skill, align the generic catalog, validate fresh SQL, and restart the service.
Updated the product-efficiency workflow, metric contract, raw SQL contract, and generic table catalog with explicit source/click topic fields and a prohibition on using type='share' for return attribution; added two static contract regressions.
Focused regressions and quick validation for both affected Skills pass.
A fresh real Codex planning check passed the exact return-field assertions; all 40 tests and compilation passed, then the Feishu service was restarted with the updated Skills.
Diagnosed run 20260812_152831_290915c8: parameters, DAU rootSessionId, video rootSessionId, and share physical rootSessionId mappings were correct, but the first partition-validation repair inserted video_action_log_applet.business='applet', producing zero exposure/play/share facts while DAU and return remained nonzero.
Corrected the diagnosis after domain confirmation: product-efficiency queries must not add any business condition to video_action_log_applet; they filter exact dt, apptype, and businesstype. The current metadata-driven guard incorrectly required every reported partition column and triggered the bad repair.
Updated the repository-local product-efficiency Skill so both offline video_action_log_applet and realtime video_action_log_flow explicitly forbid physical business predicates and use only their date partitions, apptype, and businesstype for video facts.
Hardened the host contract to reject business='applet' and business IN (...) for either video source; offline partition validation now requires dt but ignores metadata-reported business for video_action_log_applet.
Verified the saved failed SQL is rejected and the same SQL with the business line removed is accepted. All 38 tests, Python compilation, and Skill quick validation passed; service PID 56492 is running with the Feishu WebSocket started.
Initialized Git and committed the complete pre-change implementation as baseline commit 21e3d9e; .env, runtime state, logs, and the virtual environment remain ignored.
Started Phase 16 to add a first-class direct_reply route while preserving the existing Skill-aware SQL path whenever new data is required.
Added direct_reply to the structured Codex decision and host dispatcher. Ordinary conversation, explanations, and analysis of existing thread results now reply directly; fresh facts still require the original Skill/SQL or deterministic script route.
Added model and service regressions; all 42 tests and Python compilation pass. A real concurrent Codex check returned direct_reply for an ROV explanation and a 5,825-character guarded SQL plan for a fully specified offline product-efficiency query; the SQL passed the host product-efficiency contract.
Waited for run 20260812_161030_b31d848a to finish and publish before restarting. Service PID 64474 is running with Feishu WebSocket established and no active query runs; persisted per-user thread context remains intact.
Started Phase 17 after realtime run 20260812_163712_f891b997 was falsely rejected for missing partitions before ODPS submission.
Reworked partition preflight to validate each physical table occurrence in its own SELECT/CTE scope, accepting unambiguous unqualified fields while still requiring every repeated source occurrence to be filtered.
Added realtime product-efficiency validation for dt LIKE 'yyyyMMdd%' on per5min active/share sources and a direct workbook path for explicit total-only reports without experiment buckets; updated the repository Skill contract accordingly.
A fresh Codex forward check generated a 5,835-character product-0 realtime total-only SQL with null bucket parameters and passed live ODPS metadata preflight without executing a query.
All 48 tests, Python compilation, whitespace checks, and Skill quick validation pass. Restarted the Feishu service as PID 72780; bot identity lookup and WebSocket startup succeeded with no active queries.
