app_requirements/3_feature_agentic_reasoning_loop.txt

3. Feature: Graph-Driven Agent Execution
3.1 Story: As an agent, I must read all instructions from Neo4j nodes.

3.1.1 Task: Agent main loop calls MCP server's get_next_instruction tool every 30 seconds to fetch pending instructions - agent NEVER connects directly to Neo4j
3.1.2 Task: Parse returned instruction node to extract type ('query_postgres', 'analyze_schema', 'generate_sql') and parameters JSON object
3.1.3 Task: Before execution, call MCP write_graph to update instruction status to 'executing' with current timestamp
3.1.4 Task: After execution, call MCP write_graph to create Execution node with result data and create EXECUTED_AS relationship to instruction
3.1.5 Task: Call MCP write_graph to update instruction status to 'complete' or 'failed' based on execution outcome, including error details if failed

3.2 Story: As an agent, I need to connect to PostgreSQL and store its schema in Neo4j.

3.2.1 Task: Read PostgreSQL connection string from POSTGRES_CONNECTION environment variable (format: postgresql://user:pass@host:5432/dbname)
3.2.2 Task: When instruction type is 'discover_schema', query PostgreSQL information_schema.tables and information_schema.columns to get full schema
3.2.3 Task: For each discovered table, call MCP write_graph to create Table node, then for each column create Column node and HAS_COLUMN relationship
3.2.4 Task: Generate 3 example SQL queries per table and store as Instruction nodes with type='query_template' linked to table via QUERIES relationship

3.3 Story: As an operator, I need a 5-minute pause between instructions for human review.

3.3.1 Task: Read pause_duration from Instruction node (default 300 seconds) before starting execution, log "Pausing for X seconds for human review"
3.3.2 Task: Implement interruptible sleep that checks every 10 seconds for a 'stop' flag in the Workflow node (allows emergency stop)
3.3.3 Task: Before and after pause, call MCP write_graph to create Log nodes with pause_started_at and pause_ended_at timestamps
3.3.4 Task: Document that operators can use Neo4j Browser during pause to modify instruction parameters via Cypher: "MATCH (i:Instruction {id: 'X'}) SET i.parameters = '{...}'"

3.4 Story: As an agent, I need LLM access for natural language to SQL translation.

3.4.1 Task: Configure LLM_API_KEY and LLM_MODEL (gpt-4 or claude-3) via environment variables, validate on startup
3.4.2 Task: When instruction type is 'generate_sql', fetch schema context via MCP query_graph, format as "Tables: [list], Question: [user question]"
3.4.3 Task: Send prompt to LLM: "Given this PostgreSQL schema: [schema], generate SQL for: [question]. Return only valid SQL, no explanation."
3.4.4 Task: Execute generated SQL against PostgreSQL, store both query and results in Execution node via MCP write_graph with execution_time_ms