# Findings

- The reference `exp_add_feed` project uses PyODPS and contains legacy hardcoded credentials; no credential literal may enter tracked files.
- The portable `mini_new/data_query_skill` repository already contains four self-contained ODPS query Skills and a Feishu spreadsheet publisher Skill.
- Codex Python SDK 0.144.4 supports `AsyncCodex`, `thread_start`, `thread_resume`, per-turn JSON Schema output, read-only sandboxing, and explicit approval modes.
- OpenRouter can be configured as a Codex custom provider using the Responses wire API and `OPENROUTER_API_KEY`.
- The existing Feishu implementation uses `lark-oapi` WebSocket events and the Drive import APIs. The requested permission is tenant-readable, not the reference publisher's public-editable policy.
- External content and reference repositories are treated as untrusted inputs; only verified interfaces and business definitions are reused.
- The `Agent` reference's strongest reusable pattern is a lightweight WebSocket callback plus per-conversation serialization and exact bot-open-id mention checks.
- `lark-oapi` INFO logging includes ephemeral WebSocket URL credentials; production uses ERROR for the SDK logger.
- This host uses a SOCKS proxy, so the isolated environment must include both HTTPX and Requests SOCKS extras.
- The migrated Feishu and ODPS credentials pass read-only identity/project checks.
- The project-local OpenRouter key is valid. Initial 401 responses came from a different stale `OPENROUTER_API_KEY` inherited from the interactive shell because dotenv used `override=False`; project configuration now explicitly prefers its mode-600 `.env`.
- The reference Google route proved the OpenRouter key and dotenv fix. The requested production model is `openai/gpt-5.6-terra` over OpenRouter Responses.
- Codex scans all six repository Skills and injects their metadata, but the current worker disables shell/unified tools and constrains output to one SQL, so script-based Skills cannot load or execute their complete workflow.
- The desired routing policy is: prefer validated SQL/scripts packaged by a matching Skill; use model-generated SQL for generic exploration or uncovered parameters; keep ODPS and Feishu credentials solely in the Python host.
- `query-user-behavior-path` is the only fully deterministic end-to-end query Skill currently packaged: `user_timeline.py` accepts `user_id`, `yyyyMMdd`, optional `apptype`, builds its validated multi-source SQL, executes ODPS, and writes an XLSX.
- Product-efficiency and growth-fission Skills package request normalization and deterministic report formatting, but their raw-facts SQL is intentionally generated from the Skill contracts; their host execution path must therefore remain `Codex generates one validated SQL -> host executes -> Skill formatter`.
- Advertising-risk packages reusable SQL templates plus a standard SQL runner; the Codex planner should select and adapt the matching template, then the existing host SQL guard/ODPS path can execute it.
- Generic ODPS exploration has no script and should continue through generated, guarded single-SQL execution.
- Strict structured output requires `additionalProperties: false` at the root and nested parameter object; Pydantic `ConfigDict(extra="forbid")` produces the accepted schema for the OpenRouter/OpenAI Responses route.
- Real SDK routing now selects `query-odps-data` for an ambiguous DAU request and asks for the missing DAU/table definition rather than fabricating SQL.
- A fully specified timeline request now returns `selected_skill=query-user-behavior-path`, `execution_mode=skill_script`, normalized date/apptype parameters, and `sql=null`.
- The resulting Codex session contains `user_timeline.py` and `logs-and-definitions.md`, proving the complete Skill workflow and required reference were loaded rather than only its metadata description.
- Real routing also selects `odps-ad-risk-analysis` and generates a single SQL from its event-pair template, while an under-specified product-efficiency request selects its dedicated Skill and asks for the required experiment parameters in one message.
- The Skill-reader permission profile successfully preserves full Skill activation while denying `.env`, runtime, logs, Git, non-workspace filesystem reads, and tool network access.
- Behavior-path `apptype` was previously an explicit default of `0` in the Skill and all three scripts; this caused every source query to filter product 0 even when the user omitted the product.
- The revised behavior uses a shared conditional SQL fragment: omitted `apptype` renders no product predicate, while an explicit value is applied identically to all five sources.
- A real Codex routing check for a timeline question without a product now returns `apptype=null`, confirming it no longer invents product 0.
- Product-efficiency run `20260812_145417_f87d8204` repaired two SQL failures and ultimately completed ODPS instance `20260812065533286girvdjg2vxg` with the required 16 raw bucket rows; the user-visible failure occurred afterward in deterministic formatting.
- The raw result has all 26 required columns and no CSV nulls, but `dau=0` for all 16 buckets while video/share/return facts are nonzero.
- `add_rates()` converts zero DAU denominators to `pd.NA`; the resulting per-DAU Series has object dtype, and `Series.round(4)` at formatter line 126 calls Python `round(pd.NA)`, causing the exact `NAType` TypeError.
- The final repaired SQL incorrectly buckets offline `useractive_log` from its physical `rootsessionid` column. The established offline product-efficiency SQL uses `GET_JSON_OBJECT(extparams, '$.rootSessionId')`; the Skill reference documented the realtime mapping explicitly but left the offline mapping ambiguous.
- A one-row read-only diagnostic for `20260808`, `apptype=4`, third-from-end bucket found total DAU 1,610,304; physical-column bucketed DAU 0; extparams bucketed DAU 1,610,300. This proves the all-zero DAU is a SQL field-selection defect, not absent activity.
- The formatter has a second independent robustness defect: even a legitimately zero DAU baseline should render per-DAU rates as blank/unavailable and continue, per the Skill contract, rather than crash on nullable values.
- The established offline and realtime product-efficiency SQLs use `GET_JSON_OBJECT(extparams, '$.rootSessionId')` for both useractive and video-action logs, while source-share logs use their physical `rootsessionid` column. The Skill must state this exact per-table mapping instead of allowing the planner to choose either source.
- Existing query Skills reference 14 distinct `loghubods` source tables across active, video-action, playback, sharing, simple-event, advertising, and operation logs. The behavior-path workflow also joins `videoods.dim_video`, so the generic catalog needs 15 fully qualified confirmed tables.
- The catalog must distinguish confirmed business meanings from schema-only fields. Unqualified logical/target names such as `ad_own_open_conv` are not safe to publish as confirmed source tables without a project-qualified definition.
- Read-only ODPS metadata confirms all 15 fully qualified tables exist. Most fields are `STRING`; `videoods.dim_video.videoid` is `BIGINT`, and many event-table comments are sparse or null, so business meanings must come from the existing Skill contracts rather than metadata alone.
- Experiment fields such as `rootSessionId`, `rootSourceId`, `userShareDepth`, and `isSpecialLayer` may be JSON members of `extparams` on active/video sources, while share sources expose physical lowercase columns. The generic catalog must preserve these per-table distinctions, especially the prohibition on physical offline `useractive_log.rootsessionid` for experiment DAU.
- A live “重新查询” reused an older Codex thread and regenerated the pre-fix physical `useractive_log.rootsessionid` expression despite the revised Skill on disk. Prompt/Skill constraints alone are insufficient across persisted threads, so the host must validate this critical product-efficiency field contract before ODPS submission and route violations through SQL repair.
- The 15:39 product-efficiency update changes video-source handling, not report metrics: offline `video_action_log_applet` and realtime `video_action_log_flow` must never filter physical `business`; event selection uses `businesstype IN ('videoView','videoPlay','videoShareFriend')` with the normal date, app, version, and channel conditions.
- The same update adjusts host preflight to ignore `business` when checking `video_action_log_applet` partition coverage and adds contract checks/tests rejecting `business` references in both offline and realtime product-efficiency SQL. Removing `v.business IS NOT NULL` from the latest successful SQL still passes metadata/partition preflight.
- Product-efficiency run `20260812_154740_92c9afcc` produced zero returns because its source-share CTE used `user_share_log.type='share'`. The established return contract uses source `topic='share'`, click `topic='click'`, same-day/app `shareid` join, source `rootsessionid` bucket, and distinct click-side `machinecode` UV.
- A bounded diagnostic for 20260811/app 4 found 1,265,139 `topic='share'` rows versus zero `type='share'` rows; the correct topic-based join matched 311,039 share IDs and 605,411 globally distinct return users. The zero report is therefore a generated-SQL field error, not absent returns or formatter loss.
- After the contract update, a fresh Codex thread generated product-efficiency SQL with video `businesstype='videoShareFriend'`, source `topic='share'`, click `topic='click'`, source `rootsessionid` bucketing, and no `type='share'` predicate.
- Product-efficiency run `20260812_152831_290915c8` has a separate all-zero-video defect. Its three rootSessionId mappings are correct: offline active/video use `extparams.$.rootSessionId`, while share uses physical `rootsessionid`. The zero facts come from `video_action_log_applet.business='applet'` added during repair after the partition guard reported missing `(dt,business)` predicates.
- Domain correction: the validated product-efficiency query must not filter `video_action_log_applet.business`, including neither `business='applet'` nor a three-event `business IN (...)`. It uses exact `dt`, `apptype`, and `businesstype IN ('videoView','videoPlay','videoShareFriend')`. The metadata-driven guard's requirement that every reported partition column appear in predicates is incompatible with this verified table contract and caused the repair failure.
- The same no-`business` rule applies to realtime `video_action_log_flow`: use `year/month/dt`, `apptype`, and `businesstype`, with `rootSessionId` from `extparams`. The repository-local Skill and host contract now enforce this symmetrically for offline and realtime product-efficiency queries.
- The follow-up “分析下 有什么结论吗” was correctly answered by Codex from the current thread, but the structured decision only allowed `sql` or `skill_script`; the host discarded the answer and raised “Agent 返回 ready 但没有 SQL”. A first-class `direct_reply` execution mode fixes the orchestration mismatch without changing SQL generation or validation.
- A live post-change routing check confirms the boundary: an ROV definition returned a complete direct answer with no SQL, while a fully specified fresh product-efficiency request still selected `odps-product-efficiency-report`, produced SQL, extracted all parameters, and passed the existing host contract guard.
- Realtime run `20260812_163712_f891b997` never reached ODPS: all three source CTEs had partition predicates, but the host accepted unqualified partition columns only when the entire SQL contained one partitioned table, so it falsely rejected unambiguous `dt/year/month/hh` conditions inside single-source CTEs.
- The same saved SQL also used `dt='20260812'` on `useractive_log_per5min` and `user_share_log_per5min`; the verified realtime contract requires `dt LIKE '20260812%'` for current-day cumulative data. Total-only product efficiency also needs a direct aggregate workbook path because the deterministic formatter requires experiment buckets and 16 raw bucket rows.
- Realtime total-only run `20260812_165339_e924353a` returned DAU 2,288,634 and return UV 921,517 but zero video facts because it queried `video_action_log_flow` with `dt='20260812'`; the actual latest Flow partition is `year='2026',month='08',dt='12',hh='16'`.
- The correct Flow hour contains substantial product-0 activity (`videoView` 2,876,646 PV, `videoPlay` 2,524,269 PV, `videoShareFriend` 255,065 PV), proving zero video facts were a partition-value error rather than missing events or the previous offline `business` predicate defect.
- `video_action_log_per5min` uses a single `dt='yyyyMMddHHmmss'` partition and was current through `20260812165000`; product 0 had 25,879,031 videoView, 22,710,351 videoPlay, and 2,378,315 videoShareFriend rows for the day. It contains `mid`, `businesstype`, `pagesource`, `extparams`, `apptype`, and physical `rootsessionid`, but no `versioncode`; all-version realtime queries should prefer it, while version-filtered queries require Flow.
- Realtime video partition contracts are materially different and cannot share a generic `dt` rule: `video_action_log_per5min.dt` is `yyyyMMddHHmmss` and daily cumulative reads use `LIKE 'yyyyMMdd%'`; `video_action_log_flow` uses `year='yyyy'/month='MM'/dt='DD'/hh='HH'`, where `dt` is only the day of month.
- Product-efficiency realtime all-version video now selects `video_action_log_per5min`; specified-version video may use Flow because per5min has no `versioncode`.