data-query-agent/task_plan.md

Task Plan: Data Query Agent

Goal

Build a Python Codex SDK service that receives allowlisted Feishu messages, keeps per-user-per-chat multi-turn context, generates and safely executes read-only ODPS SQL, and automatically publishes results as tenant-readable Feishu spreadsheets.

Phases

Phase 1: Scaffold and configuration

• Create package, configuration, runtime directories, and launch scripts
• Migrate local credentials into an ignored .env without exposing them
• Status: completed

Phase 2: Core runtime

• Implement SQLite state, command parsing, SQL validation, ODPS execution, and report generation
• Implement Codex SDK structured planning and multi-turn thread persistence
• Status: completed

Phase 3: Feishu integration

• Implement WebSocket message intake, allowlists, replies, spreadsheet import, and tenant-readable permissions
• Connect the end-to-end asynchronous workflow
• Status: completed

Phase 4: Skills

• Create the generic ODPS query Skill
• Vendor and adapt the four existing query Skills and Feishu publisher Skill
• Validate every Skill
• Status: completed

Phase 5: Verification

• Add focused unit tests and read-only external connectivity checks
• Run compile, tests, dependency check, secret scan, and launch-script checks
• Status: completed (OpenRouter credential health check is externally blocked by a stale key)

Phase 6: Skill-aware execution routing

• Define a structured Codex decision that identifies the selected Skill and execution mode
• Let Codex read matched Skill instructions while keeping credentials out of its environment
• Reuse validated Skill SQL/scripts when available; generate SQL only for generic or uncovered queries
• Status: completed

Phase 7: Host-side Skill execution and Feishu delivery

• Add an allowlisted Skill executor for supported script-based workflows
• Execute generated or reused read-only queries and publish the resulting spreadsheet to Feishu
• Add progress replies that accurately reflect routing, querying, and publishing
• Status: completed

Phase 8: Skill-routing verification

• Add tests for implicit Skill selection, clarification, generic SQL, and script execution plans
• Run the full test suite and restart the Feishu long-connection service
• Status: completed

Phase 9: Optional apptype behavior-path filtering

• Change timeline Skill contracts so omitted apptype means all products
• Update offline, realtime, and batch SQL scripts to add the predicate only when apptype is explicit
• Add regression tests, validate the Skill, and restart the Feishu service
• Status: completed

Phase 10: Diagnose product-efficiency formatting failure

• Identify the failed run and distinguish ODPS execution from formatting
• Trace the invalid raw fact and exact formatter failure path
• Report root cause, impact, and recommended fix without changing production code
• Status: completed

Phase 11: Harden product-efficiency Skill

• Make offline rootSessionId source mappings explicit in the Skill contract
• Reject semantically inconsistent raw facts and tolerate legitimate zero denominators
• Add formatter regressions, validate the Skill, and restart the Feishu service
• Status: completed

Phase 12: Add a Chinese generic-query data catalog

• Inventory the 14 verified source tables and fields used by existing query Skills
• Add a Chinese table/field catalog and convert the generic query Skill instructions to Chinese
• Validate catalog coverage, forward-check generic SQL generation, and restart the Feishu service
• Status: completed

Phase 13: Review updated product-efficiency Skill and reload service

• Identify and analyze the externally modified Skill files and behavioral changes
• Validate the updated Skill and run relevant regressions
• Wait for active queries to finish, then restart and verify the Feishu service
• Status: completed

Phase 14: Diagnose zero product-efficiency returns

• Trace the latest report from generated SQL through raw and formatted return fields
• Compare source-share filtering with the established offline return contract
• Confirm the cause using a bounded read-only ODPS diagnostic
• Status: completed

Phase 15: Fix product-efficiency return attribution contract

• Specify exact source-share and click topic filters in the product-efficiency Skill
• Align the generic table catalog and add regression coverage
• Forward-check fresh SQL generation, then restart and verify the Feishu service
• Status: completed

Phase 13: Diagnose all-zero product-efficiency video facts

• Inspect the latest SDK request, repaired SQL, raw facts, formatter output, and host validation path
• Compare rootSessionId mappings and video partitions with the known-good offline query
• Report the root cause and guard gap without changing production code
• Status: completed

Phase 14: Enforce no-business product-efficiency video queries

• Update the repository-local product-efficiency Skill for offline and realtime video sources
• Reject any product-efficiency video business predicate and accept offline dt-only partition filtering
• Add regressions, run the full suite, validate the Skill, and restart the Feishu service
• Status: completed

Phase 16: General natural-language assistant routing

• Save the pre-change implementation as a Git baseline
• Add a direct response mode for conversation, explanations, and analysis of existing results
• Preserve the existing Skill, SQL generation, validation, repair, and execution path for new data requests
• Add regressions, run a real Codex routing check, and restart the Feishu service
• Status: completed

Phase 17: Fix realtime product-efficiency preflight

• Reproduce the false missing-partition error from the saved failed run
• Validate partition predicates within each CTE and every repeated table occurrence
• Enforce realtime per5min day-prefix filters and support explicit total-only reports
• Run full regressions, restart the Feishu service, and push the fix
• Status: completed

Phase 18: Distinguish realtime video partition contracts

• Diagnose the zero-video realtime report against actual Flow and per5min partitions
• Document both partition formats and prefer video per5min for all-version realtime queries
• Enforce source selection and exact partition values in the host SQL contract
• Add regressions, forward-test Codex SQL, restart the service, and push
• Status: completed

Decisions

• Python 3.12, openai-codex==0.144.4, OpenRouter, GPT-5.6 Terra.
• PyODPS direct read-only query path.
• Feishu WebSocket long connection and automatic spreadsheet publication.
• Context key is chat type + chat ID + sender open ID.
• Commands: /new, /clear, /help plus exact English and Chinese aliases.
• SQLite is a local state file; query artifacts remain ordinary files.
• Deployment uses nohup management scripts, not Docker or systemd.
• Skill SQL and scripts are preferred over newly generated SQL when a matching Skill already defines a validated workflow.
• Codex selects the Skill and extracts parameters; the Python host owns credentials, executes allowlisted workflows, and publishes results.
• For behavior-path queries, omitted apptype means no product filter; explicit apptype remains an exact filter.
• Natural-language requests that need no new data use direct_reply; any answer requiring new numeric facts must retain the existing guarded Skill/SQL execution path.

Errors Encountered

Error	Attempt	Resolution
SOCKS proxy extras missing in the isolated venv	1	Added httpx[socks] and requests[socks] dependencies.
Feishu SDK INFO log included ephemeral WebSocket query credentials	1	Switched SDK logging to ERROR, deleted the affected log, and verified the replacement log contains no access key or ticket.
OpenRouter returned 401 User not found	1	Found a stale shell OPENROUTER_API_KEY overriding the valid project .env; changed project config to prefer .env and added a regression test.
Initial result-publishing refactor referenced pre-refactor local variables	1	Replaced them with the method arguments and derived run directory before running tests.
OpenRouter rejected the nested Skill routing schema because additionalProperties: false was absent	1	Set all structured-output Pydantic models to forbid extra properties, producing a strict compatible schema.
Enabling read-only file tools could expose project credential files	1	Replaced the broad read-only sandbox preset with a least-privilege permission profile that denies root, .env*, runtime, logs, and Git while allowing read-only Skill workspace access.
Repeated standalone multi-case SDK probes completed without forwarding Python stdout	3	Stopped repeating the wrapper probe; inspect the generated Codex session record and rely on the service/doctor path for integration validation.
Git diff/status checks failed because the project is not a Git repository	1	Switched final validation to compilation, tests, configuration/doctor checks, and a secret scan excluding the expected local .env.
Diagnostic shell used reserved zsh variable status after reproducing the formatter traceback	1	The traceback was captured successfully; avoid that variable name in subsequent commands.
First real-plan probe had invalid nested shell quoting	1	Removed the embedded single-quoted JSON-path literal and changed the assertion to safe SQL substrings.
nohup service process was reaped when the tool command ended	1	Removed the stale PID and launched the service in a persistent foreground exec session; PID and WebSocket startup were verified.
Conversation diagnostic queried a nonexistent updated_at column	1	Inspect the SQLite schema first and query only the actual conversation columns.
Planned cleanup patch no longer matched sql_guard.py	1	Detected a concurrent 15:39 Skill/guard update, re-read the changed files, and preserved the newer business-field validation instead of overwriting it.
A diagnostic rg pattern used backticks inside double quotes, causing zsh command substitution	1	The file output was still obtained; use single-quoted search patterns or plain terms for future shell searches.
First fresh-plan assertion command had unmatched nested shell quotes	1	Switch to a quoted Python heredoc so SQL literals are not interpreted by zsh.
Product-efficiency repair added video_action_log_applet.business='applet'	1	Diagnosed that product-efficiency SQL must not filter business at all; the host metadata guard incorrectly required every reported partition column instead of accepting the Skill's verified dt-only offline predicate.