# 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
- [x] Create package, configuration, runtime directories, and launch scripts
- [x] Migrate local credentials into an ignored `.env` without exposing them
- **Status:** completed

### Phase 2: Core runtime
- [x] Implement SQLite state, command parsing, SQL validation, ODPS execution, and report generation
- [x] Implement Codex SDK structured planning and multi-turn thread persistence
- **Status:** completed

### Phase 3: Feishu integration
- [x] Implement WebSocket message intake, allowlists, replies, spreadsheet import, and tenant-readable permissions
- [x] Connect the end-to-end asynchronous workflow
- **Status:** completed

### Phase 4: Skills
- [x] Create the generic ODPS query Skill
- [x] Vendor and adapt the four existing query Skills and Feishu publisher Skill
- [x] Validate every Skill
- **Status:** completed

### Phase 5: Verification
- [x] Add focused unit tests and read-only external connectivity checks
- [x] 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
- [x] Define a structured Codex decision that identifies the selected Skill and execution mode
- [x] Let Codex read matched Skill instructions while keeping credentials out of its environment
- [x] 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
- [x] Add an allowlisted Skill executor for supported script-based workflows
- [x] Execute generated or reused read-only queries and publish the resulting spreadsheet to Feishu
- [x] Add progress replies that accurately reflect routing, querying, and publishing
- **Status:** completed

### Phase 8: Skill-routing verification
- [x] Add tests for implicit Skill selection, clarification, generic SQL, and script execution plans
- [x] Run the full test suite and restart the Feishu long-connection service
- **Status:** completed

### Phase 9: Optional apptype behavior-path filtering
- [x] Change timeline Skill contracts so omitted apptype means all products
- [x] Update offline, realtime, and batch SQL scripts to add the predicate only when apptype is explicit
- [x] Add regression tests, validate the Skill, and restart the Feishu service
- **Status:** completed

### Phase 10: Diagnose product-efficiency formatting failure
- [x] Identify the failed run and distinguish ODPS execution from formatting
- [x] Trace the invalid raw fact and exact formatter failure path
- [x] Report root cause, impact, and recommended fix without changing production code
- **Status:** completed

### Phase 11: Harden product-efficiency Skill
- [x] Make offline rootSessionId source mappings explicit in the Skill contract
- [x] Reject semantically inconsistent raw facts and tolerate legitimate zero denominators
- [x] Add formatter regressions, validate the Skill, and restart the Feishu service
- **Status:** completed

### Phase 12: Add a Chinese generic-query data catalog
- [x] Inventory the 14 verified source tables and fields used by existing query Skills
- [x] Add a Chinese table/field catalog and convert the generic query Skill instructions to Chinese
- [x] 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
- [x] Identify and analyze the externally modified Skill files and behavioral changes
- [x] Validate the updated Skill and run relevant regressions
- [x] Wait for active queries to finish, then restart and verify the Feishu service
- **Status:** completed

### Phase 14: Diagnose zero product-efficiency returns
- [x] Trace the latest report from generated SQL through raw and formatted return fields
- [x] Compare source-share filtering with the established offline return contract
- [x] Confirm the cause using a bounded read-only ODPS diagnostic
- **Status:** completed

### Phase 15: Fix product-efficiency return attribution contract
- [x] Specify exact source-share and click topic filters in the product-efficiency Skill
- [x] Align the generic table catalog and add regression coverage
- [x] Forward-check fresh SQL generation, then restart and verify the Feishu service
- **Status:** completed

### Phase 13: Diagnose all-zero product-efficiency video facts
- [x] Inspect the latest SDK request, repaired SQL, raw facts, formatter output, and host validation path
- [x] Compare rootSessionId mappings and video partitions with the known-good offline query
- [x] Report the root cause and guard gap without changing production code
- **Status:** completed

### Phase 14: Enforce no-business product-efficiency video queries
- [x] Update the repository-local product-efficiency Skill for offline and realtime video sources
- [x] Reject any product-efficiency video `business` predicate and accept offline `dt`-only partition filtering
- [x] Add regressions, run the full suite, validate the Skill, and restart the Feishu service
- **Status:** completed

### Phase 16: General natural-language assistant routing
- [x] Save the pre-change implementation as a Git baseline
- [x] Add a direct response mode for conversation, explanations, and analysis of existing results
- [x] Preserve the existing Skill, SQL generation, validation, repair, and execution path for new data requests
- [x] Add regressions, run a real Codex routing check, and restart the Feishu service
- **Status:** completed

### Phase 17: Fix realtime product-efficiency preflight
- [x] Reproduce the false missing-partition error from the saved failed run
- [x] Validate partition predicates within each CTE and every repeated table occurrence
- [x] Enforce realtime per5min day-prefix filters and support explicit total-only reports
- [x] Run full regressions, restart the Feishu service, and push the fix
- **Status:** completed

### Phase 18: Distinguish realtime video partition contracts
- [x] Diagnose the zero-video realtime report against actual Flow and per5min partitions
- [x] Document both partition formats and prefer video per5min for all-version realtime queries
- [x] Enforce source selection and exact partition values in the host SQL contract
- [x] 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. |