# PRD: New Workflow Automation Nodes

## Overview
Add six new automation nodes to the SmartBotic workflow engine: MySQL, PostgreSQL, RSS Reader, Crypto, Image Manipulation, and Code (JavaScript). These nodes expand the platform's integration and data processing capabilities, following the existing node architecture (JavaScript modules executed in QuickJS on the Runner service). Database nodes leverage the existing credential management system and provide live schema introspection. The Image node uses the existing binary storage logic for passing image data between nodes.

## Goals
- Provide MySQL and PostgreSQL connectivity with full CRUD, bulk insert, upsert, and custom SQL support
- Enable RSS feed reading with filtering and optional stateful new-item detection using database-backed collections
- Offer cryptographic operations including hashing, HMAC, random generation, and AES/RSA encrypt/decrypt
- Support image manipulation (resize, crop, rotate, filters, format conversion) and metadata extraction using ImageMagick
- Allow users to write and execute custom JavaScript in workflows via a dedicated Code node with built-in helper utilities
- Integrate all nodes with the existing credential management, binary storage, and node configuration systems

## Quality Gates

These commands must pass for every user story:
- `cd webui && npm run build` - Frontend build
- `cd build && ninja -j4` - Backend build

## User Stories

### US-001: MySQL Node - Connection & Schema Introspection
As a workflow designer, I want to configure a MySQL node that connects using stored credentials and auto-detects tables and columns so that I can quickly build database queries without memorizing schema details.

**Acceptance Criteria:**
- [ ] MySQL node definition created in `nodes/` following existing node module interface
- [ ] Node config UI allows selecting credentials from the existing credential management system
- [ ] Connection fields: host, port, username, password, database (stored via credential system)
- [ ] Live schema introspection fetches available tables when the node is configured in the editor
- [ ] After selecting a table, columns are fetched and displayed for selection
- [ ] Introspection works in real-time (requires active DB connection at design time)
- [ ] Node appears in the workflow editor node palette under a "Database" category

### US-002: MySQL Node - CRUD Operations
As a workflow designer, I want to perform Select, Insert, Update, Delete, Upsert, and Bulk Insert operations on MySQL tables using a dropdown selector so that I can manipulate data without writing SQL.

**Acceptance Criteria:**
- [ ] Operation selector dropdown with options: Select, Insert, Update, Delete, Upsert, Bulk Insert, Custom SQL
- [ ] **Select:** allows choosing columns, adding WHERE conditions, LIMIT, ORDER BY
- [ ] **Insert:** allows mapping input fields to table columns
- [ ] **Update:** allows mapping fields to columns with WHERE conditions
- [ ] **Delete:** allows specifying WHERE conditions
- [ ] **Upsert:** allows specifying the conflict key and update fields
- [ ] **Bulk Insert:** accepts an array of objects and inserts multiple rows
- [ ] **Custom SQL:** free-text SQL input with parameterized query support
- [ ] Output format: array of objects (each row as JSON with column-name keys) plus metadata (affected rows count, last insert ID for write operations)
- [ ] Parameterized queries used for all operations to prevent SQL injection

### US-003: PostgreSQL Node - Connection & Schema Introspection
As a workflow designer, I want to configure a PostgreSQL node with the same UX as the MySQL node so that I have a consistent experience across database types.

**Acceptance Criteria:**
- [ ] PostgreSQL node definition created in `nodes/` following existing node module interface
- [ ] Node config UI allows selecting credentials from the existing credential management system
- [ ] Connection fields: host, port, username, password, database (stored via credential system)
- [ ] Live schema introspection fetches available tables when configured in the editor
- [ ] After selecting a table, columns are fetched and displayed for selection
- [ ] Node appears in the workflow editor node palette under a "Database" category

### US-004: PostgreSQL Node - CRUD Operations
As a workflow designer, I want the same CRUD, Upsert, Bulk Insert, and Custom SQL operations on PostgreSQL as on MySQL so that I can work with PostgreSQL data the same way.

**Acceptance Criteria:**
- [ ] Operation selector dropdown with options: Select, Insert, Update, Delete, Upsert, Bulk Insert, Custom SQL
- [ ] **Select:** column selection, WHERE conditions, LIMIT, ORDER BY
- [ ] **Insert:** map input fields to table columns
- [ ] **Update:** map fields to columns with WHERE conditions
- [ ] **Delete:** specify WHERE conditions
- [ ] **Upsert:** uses PostgreSQL `ON CONFLICT` syntax, allows specifying conflict key and update fields
- [ ] **Bulk Insert:** accepts array of objects, inserts multiple rows
- [ ] **Custom SQL:** free-text SQL with parameterized query support
- [ ] Output format: array of objects plus metadata (affected rows count, last insert ID for write operations)
- [ ] Parameterized queries used for all operations to prevent SQL injection

### US-005: RSS Reader Node - Feed Parsing & Filtering
As a workflow designer, I want to read and parse RSS/Atom feeds with optional filtering so that I can integrate external content sources into workflows.

**Acceptance Criteria:**
- [ ] RSS Reader node definition created in `nodes/` following existing node module interface
- [ ] Config accepts a feed URL as input
- [ ] Parses both RSS 2.0 and Atom feed formats
- [ ] Outputs an array of feed items with fields: title, link, description, pubDate, author, categories, guid
- [ ] Supports filtering by keyword (matches against title and description)
- [ ] Supports filtering by date range (items newer than a specified date)
- [ ] Supports limiting the number of returned items (count limit)
- [ ] Node appears in the workflow editor node palette

### US-006: RSS Reader Node - Stateful New Item Detection
As a workflow designer, I want to optionally track which RSS items have already been seen so that my workflow only processes new items on each execution.

**Acceptance Criteria:**
- [ ] Optional "Detect new items" toggle in node configuration
- [ ] When enabled, user must select a collection from the database service for state storage
- [ ] The selected collection must be configured as a read-write (RW) collection on the workflow before it can be selected in the node
- [ ] On each execution, the node compares feed items against stored item GUIDs/links
- [ ] Only new (unseen) items are output
- [ ] Seen item identifiers are persisted to the selected collection after successful execution
- [ ] When disabled, the node outputs all items matching filter criteria (stateless mode)

### US-007: Crypto Node - Hashing & HMAC
As a workflow designer, I want to hash data and generate HMACs so that I can verify data integrity and authenticate messages in workflows.

**Acceptance Criteria:**
- [ ] Crypto node definition created in `nodes/` following existing node module interface
- [ ] Operation selector with categories: Hash, HMAC, Random Generation, Encrypt, Decrypt
- [ ] **Hash** operation supports: MD5, SHA-1, SHA-256, SHA-384, SHA-512
- [ ] Hash input accepts string or binary data
- [ ] Hash output format configurable: hex, base64
- [ ] **HMAC** operation supports the same algorithms with a secret key input
- [ ] Node appears in the workflow editor node palette

### US-008: Crypto Node - Random Generation
As a workflow designer, I want to generate random values (UUIDs, tokens, passwords) so that I can create unique identifiers and secure credentials in workflows.

**Acceptance Criteria:**
- [ ] **Random Generation** sub-operation in the Crypto node
- [ ] Supports generating: UUID v4, random hex token (configurable length), random alphanumeric string, secure password (configurable length, character sets)
- [ ] Output is a string value passed to subsequent nodes

### US-009: Crypto Node - Encrypt & Decrypt
As a workflow designer, I want to encrypt and decrypt data using AES and RSA so that I can protect sensitive information in workflows.

**Acceptance Criteria:**
- [ ] **Encrypt** operation supports AES-256-CBC, AES-256-GCM, and RSA
- [ ] AES requires: key (or passphrase-derived key), IV (auto-generated if not provided)
- [ ] RSA requires: public key (PEM format) for encryption
- [ ] **Decrypt** operation supports the same algorithms
- [ ] RSA decrypt requires: private key (PEM format)
- [ ] Input/output supports both string and base64-encoded binary data
- [ ] Error output provides clear messages for invalid keys or corrupted data

### US-010: Image Node - Information & Metadata
As a workflow designer, I want to extract image information and metadata so that I can make workflow decisions based on image properties.

**Acceptance Criteria:**
- [ ] Image node definition created in `nodes/` following existing node module interface
- [ ] Operation selector with categories: Info, Resize, Crop, Rotate, Filter, Convert Format
- [ ] **Info** operation outputs: width, height, format, color space, file size, DPI
- [ ] EXIF metadata extraction (camera, date taken, GPS coordinates, orientation, etc.)
- [ ] Input accepts base64-encoded images, file paths on the runner filesystem, and URLs (auto-downloaded)
- [ ] Uses the existing binary storage logic for passing image data between nodes
- [ ] Uses ImageMagick for image processing
- [ ] Node appears in the workflow editor node palette

### US-011: Image Node - Manipulation (Resize, Crop, Rotate)
As a workflow designer, I want to resize, crop, and rotate images so that I can transform images as part of automated workflows.

**Acceptance Criteria:**
- [ ] **Resize** operation: accepts target width and/or height, maintains aspect ratio option, supports percentage-based scaling
- [ ] **Crop** operation: accepts x, y, width, height parameters for the crop region
- [ ] **Rotate** operation: accepts angle in degrees (90, 180, 270, or arbitrary), optional background fill color
- [ ] All operations output the result using the existing binary storage mechanism
- [ ] Output format configurable (JPEG, PNG, WebP, GIF)
- [ ] Quality setting available for lossy formats

### US-012: Image Node - Filters & Watermark
As a workflow designer, I want to apply filters and watermarks to images so that I can process images for various output requirements.

**Acceptance Criteria:**
- [ ] **Filter** operations: blur (configurable radius), sharpen, grayscale, sepia, brightness adjustment, contrast adjustment
- [ ] **Watermark** operation: overlay text or image watermark, configurable position (center, corners), configurable opacity
- [ ] Filters can be chained (apply multiple in sequence)
- [ ] All operations use ImageMagick and output via existing binary storage

### US-013: Code Node - JavaScript Execution
As a workflow designer, I want to write custom JavaScript code in a node so that I can implement custom logic that isn't covered by built-in nodes.

**Acceptance Criteria:**
- [ ] Code node definition created in `nodes/` following existing node module interface
- [ ] Node config provides a code editor textarea in the workflow editor for writing JavaScript
- [ ] User's code has access to `input` (the node's input data) and `context` (workflow execution context)
- [ ] User's code returns a value that becomes the node's output
- [ ] Built-in helper utilities available: lodash-like collection/string/object helpers, date formatting utilities
- [ ] Code executes in the existing QuickJS sandbox on the Runner service
- [ ] Execution timeout configurable (default: 30 seconds)
- [ ] Runtime errors are caught and reported as node execution failures with the error message and line number
- [ ] Node appears in the workflow editor node palette

### US-014: Node Registration & Editor Integration
As a workflow designer, I want all new nodes to appear in the workflow editor with proper icons, categories, and configuration panels so that I can easily discover and configure them.

**Acceptance Criteria:**
- [ ] All six nodes (MySQL, PostgreSQL, RSS Reader, Crypto, Image, Code) are registered in the node system
- [ ] Each node has a distinct icon in the editor palette
- [ ] Nodes are categorized: Database (MySQL, PostgreSQL), Data (RSS Reader), Security (Crypto), Media (Image), Developer (Code)
- [ ] Each node's configuration panel renders all relevant fields based on the selected operation
- [ ] Dynamic form updates: selecting an operation shows/hides relevant fields
- [ ] All nodes can be migrated to the database using the existing `POST /api/v1/nodes/migrate` endpoint

## Functional Requirements
- FR-1: Database nodes must use parameterized queries for all operations to prevent SQL injection
- FR-2: Database schema introspection must query the live database when configuring nodes in the editor
- FR-3: Database operation results must include an array of row objects and metadata (affected rows, last insert ID)
- FR-4: RSS Reader must support both RSS 2.0 and Atom feed formats
- FR-5: RSS stateful detection requires a pre-configured RW collection on the workflow before it can be used
- FR-6: Crypto node must use established cryptographic libraries (not custom implementations)
- FR-7: Image node must use ImageMagick via the Runner's C++ integration or QuickJS bindings
- FR-8: Image node must use the existing binary storage mechanism for input and output
- FR-9: Code node must execute in the QuickJS sandbox with a configurable timeout
- FR-10: Code node must provide meaningful error messages including line numbers on failure
- FR-11: All nodes must follow the existing node module interface (configSchema, inputSchema, outputSchema, execute)
- FR-12: All nodes must be hot-reloadable by the Runner service

## Non-Goals (Out of Scope)
- MongoDB or other NoSQL database nodes (future enhancement)
- Stored procedure execution for database nodes
- RSS feed publishing/writing
- Asymmetric key generation in the Crypto node (users provide their own keys)
- Video processing or manipulation in the Image node
- Multi-language support in the Code node (JavaScript only)
- Visual code editor with syntax highlighting (plain textarea for now; can be enhanced later)
- Database connection pooling (single connection per execution for now)

## Technical Considerations
- **Database connectivity in QuickJS:** MySQL and PostgreSQL client libraries are C/C++ native. These will need C++ bindings exposed to the QuickJS runtime on the Runner, similar to how other native features are exposed.
- **ImageMagick integration:** ImageMagick's MagickWand C API can be wrapped and exposed to QuickJS as native functions on the Runner.
- **RSS parsing:** A lightweight XML parser (e.g., pugixml or RapidXML, already available in C++) can be used for feed parsing, exposed to QuickJS.
- **Crypto:** OpenSSL (likely already a dependency via gRPC) provides all needed cryptographic primitives. Wrap and expose to QuickJS.
- **Binary storage:** The existing binary storage mechanism must be used for Image node input/output. Consult existing node implementations that handle binary data for the pattern.
- **Credential system:** Database nodes must integrate with the existing credential management. Consult the current credential storage/retrieval API.
- **Schema introspection API:** A new endpoint or WebSocket message may be needed for the editor to trigger live schema introspection through the WebServer to the Runner (which holds the DB connection logic).

## Success Metrics
- All six nodes pass the build quality gates (frontend build + backend ninja build)
- All nodes appear in the workflow editor with proper configuration UIs
- Database nodes can connect, introspect schema, and execute all supported operations
- RSS Reader correctly parses sample RSS 2.0 and Atom feeds
- Crypto node produces correct outputs for known test vectors (e.g., SHA-256 of known input)
- Image node can read, manipulate, and output images via the binary storage system
- Code node executes user JavaScript and returns results correctly
- All nodes can be migrated to the database via the existing migration endpoint

## Open Questions
- What specific lodash-like helpers should be bundled with the Code node? (e.g., `_.map`, `_.filter`, `_.groupBy`, date-fns subset)
- Should database schema introspection go through a new REST endpoint on the WebServer, or use WebSocket for real-time feedback?
- Are there existing QuickJS native bindings in the Runner that can serve as a template for the new ImageMagick/MySQL/PostgreSQL bindings?
- Should the RSS Reader support authenticated feeds (HTTP Basic Auth, API keys)?