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.
