# Progress

## 2026-08-11

- 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.
- Started Phase 18 after the first successful realtime total-only report returned zero video facts despite nonzero DAU and return data.
- Confirmed the generated Flow predicate used a full `yyyyMMdd` value where the physical partition stores two-digit `DD`; a bounded diagnostic proved the correct current-hour partition has millions of video events.
- Compared realtime video sources: `video_action_log_per5min` uses `dt=yyyyMMddHHmmss` and has all required all-version metric fields but no `versioncode`; Flow uses `year/month/DD/hh` and retains version support.
- Phase 18 implementation: documented both realtime video partition layouts, changed all-version normalization to `video_action_log_per5min`, and added host guards for source choice, per5min day-prefix filters, Flow literal formats, and forbidden video `business` predicates.
- Phase 18 verification: 55 tests passed, both affected Skills validated, a fresh GPT-5.6 Terra plan selected video per5min with the correct day-prefix predicate, and the Feishu long-connection service restarted as PID 76682.