---
name: experion-crawler
description: ExperionCrawler .NET 8 project — OPC UA data acquisition, PostgreSQL/TimescaleDB, P&ID DXF/PDF parsing, MCP server bridge, high-frequency data capture. Use when building, debugging, or modifying this project.
license: MIT
compatibility: opencode
metadata:
  domain: iiot
  framework: dotnet
---

## Build / Run / Test

| Action | Command | Working Dir |
|--------|---------|-------------|
| Build | `dotnet build src/Web/ExperionCrawler.csproj` | repo root |
| Run (dev) | `dotnet run` | `src/Web/` |
| Tests | `dotnet test` | repo root |
| Publish | `dotnet publish -c Release -o /opt/ExperionCrawler` | `src/Web/` |

Single project, linux-arm64 target. `src/Core/` and `src/Infrastructure/` are included via `<Compile Include>` globs.

## Architecture

```
src/
├── Core/            — Interfaces, Domain entities, DTOs, Application Services
├── Infrastructure/  — OpcUa/, Database/, Certificates/, Csv/, Mcp/
└── Web/             — Program.cs, Controllers/, wwwroot/ (SPA)
```

- Controllers: `src/Web/Controllers/ExperionControllers.cs` (single file)
- Interfaces: `src/Core/Application/Interfaces/IExperionServices.cs` (single file)

## Database — PostgreSQL (TimescaleDB)

**Docker container** `iiot-timescaledb` (image: `timescale/timescaledb-ha:pg16`):
- Port: `localhost:5432`
- Volume: `iiot-pgdata` → `/var/lib/postgresql/data`
- Init scripts: `/opt/iiot-platform/timescaledb/init/`
- Container IP: `172.17.0.3`
- Network: `bridge` (default)

**Docker env credentials:**
- DB: `iiot_platform`, User: `iiot_admin`, Pass: `ChangeMe2026`

**appsettings.json connection strings** (`src/Web/appsettings.json`):
```
DefaultConnection:    Host=localhost;Port=5432;Database=iiot_platform;  Username=postgres;Password=postgres
ExperionDbConnection: Host=localhost;Port=5432;Database=postgres;       Username=postgres;Password=postgres;Trust Server Certificate=true;Include Error Detail=true
```

**Key tables:** `raw_node_map`, `node_map_master`, `realtime_table`, `history_table`, `fast_session`, `fast_record`, `tag_metadata`, `pid_equipment`, `pid_graph_status`

**Quick CLI access:** `psql -U postgres -d iiot_platform -h localhost`

## Critical Convention — JSON camelCase

`PropertyNamingPolicy = null` so C# PascalCase becomes JSON keys. Frontend expects camelCase:

```csharp
// ✅ Correct
return Ok(new { id = x.Id, tagName = x.TagName, nodeId = x.NodeId, liveValue = x.LiveValue, timestamp = x.Timestamp });

// ❌ Broken — shorthand = PascalCase keys
return Ok(new { x.Id, x.TagName });
// ❌ Broken — typed object = PascalCase keys
return Ok(myDto);
```

**Checklist for every new endpoint:**
- [ ] All anonymous object keys are camelCase (`id`, `tagName`, `nodeId` ...)
- [ ] No `new { x.SomeProp }` shorthand anywhere
- [ ] No typed record/class passed directly to `Ok()`
- [ ] C# reserved words (`class`) prefixed with `@`

## API Endpoints (all in `src/Web/Controllers/ExperionControllers.cs`)

| Controller | Route | Method | Purpose |
|---|---|---|---|
| Certificate | `/api/certificate/status` | GET | Certificate status |
| | `/api/certificate/create` | POST | Create certificate |
| Connection | `/api/connection/test` | POST | Test OPC UA connection |
| | `/api/connection/read` | POST | Read single tag |
| | `/api/connection/browse` | POST | Browse nodes |
| Crawl | `/api/crawl/nodemap` | POST | Full node map crawl → CSV |
| | `/api/crawl/start` | POST | Sync crawl with tags |
| Database | `/api/database/files` | GET | List CSV files |
| | `/api/database/import` | POST | Import CSV → DB |
| | `/api/database/records` | GET | Query records |
| PointBuilder | `/api/pointbuilder/build` | POST | Build realtime_table from node_map_master |
| | `/api/pointbuilder/preview` | POST | Preview matching points |
| | `/api/pointbuilder/apply` | POST | Apply selected points |
| | `/api/pointbuilder/points` | GET | Get realtime_table points |
| | `/api/pointbuilder/add` | POST | Add point by node_id |
| | `/api/pointbuilder/{id}` | DELETE | Delete point |
| Tags | `/api/tags/metadata/reload` | POST | Reload metadata from OPC UA |
| | `/api/tags/metadata` | GET | Get tag metadata |
| Realtime | `/api/realtime/start` | POST | Start subscription |
| | `/api/realtime/stop` | POST | Stop subscription |
| | `/api/realtime/status` | GET | Subscription status |
| History | `/api/history/tagnames` | GET | Tagnames from realtime_table |
| | `/api/history/query` | GET | Query history (tags, time range, limit) |
| OpcServer | `/api/opcserver/status` | GET | OPC UA server status |
| | `/api/opcserver/start` | POST | Start OPC UA server |
| | `/api/opcserver/stop` | POST | Stop OPC UA server |
| | `/api/opcserver/rebuild` | POST | Rebuild address space |
| NodeMap | `/api/nodemap/names` | GET | Distinct name values |
| | `/api/nodemap/stats` | GET | node_map_master stats |
| | `/api/nodemap/query` | GET | Query with filters + pagination |
| Hypertable | `/api/experion/hypertable/status` | GET | TimescaleDB hypertable status |
| | `/api/experion/hypertable/create` | POST | Create hypertable manually |
| Fast | `/api/fast/start` | POST | Start fast session |
| | `/api/fast/{id}/stop` | POST | Stop session |
| | `/api/fast/sessions` | GET | List sessions |
| | `/api/fast/{id}` | GET | Session details |
| | `/api/fast/{id}/records` | GET | Records (long format) |
| | `/api/fast/{id}/csv` | GET | Export CSV streaming |
| | `/api/fast/{id}` | DELETE | Delete session |
| | `/api/fast/{id}/pin` | POST | Pin/unpin session |
| P&ID | `/api/pid/extract` | POST | Extract from DXF/PDF (100MB limit) |
| | `/api/pid/equipment` | GET | Equipment list (paginated) |
| | `/api/pid/statistics` | GET | P&ID statistics |
| | `/api/pid/{id}/confidence` | PUT | Update confidence (0-1) |
| | `/api/pid/{id}/activate` | POST | Activate equipment |
| | `/api/pid/{id}/deactivate` | POST | Deactivate equipment |
| | `/api/pid/mappings` | GET | Tag mappings (paginated) |
| | `/api/pid/mappings` | POST | Create mapping |
| | `/api/pid/mappings/{id}` | PUT | Update mapping |
| | `/api/pid/mappings/{id}` | DELETE | Clear mapping |
| | `/api/pid/mappings/available-tags` | GET | Available tags for mapping |
| | `/api/pid/export/csv` | GET | Export CSV |
| | `/api/pid/export/excel` | GET | Export Excel (.xlsx) |

P&ID controllers are conditional — enabled by `PidControllers:Enabled` in config (default: true).

## Service Interfaces & Implementations

All interfaces in `src/Core/Application/Interfaces/IExperionServices.cs`.

| Interface | Impl | Lifetime |
|---|---|---|
| `IExperionCertificateService` | `ExperionCertificateService` | Singleton |
| `IExperionStatusCodeService` | `ExperionStatusCodeService` | Singleton |
| `IOpcUaConfigProvider` | `OpcUaConfigProvider` | Singleton |
| `IExperionOpcClient` | `ExperionOpcClient` | Scoped |
| `IExperionCsvService` | `ExperionCsvService` | Scoped |
| `IExperionDbService` | `ExperionDbService` | Scoped |
| `IExperionRealtimeService` | `ExperionRealtimeService` | Singleton + HostedService |
| `IExperionOpcServerService` | `ExperionOpcServerService` | Singleton + HostedService |
| `IExperionFastService` | `ExperionFastService` | Singleton + HostedService |
| `IMetadataLoaderService` | `MetadataLoaderService` | Singleton |
| `ITextToSqlService` | — | Scoped |
| `IMcpService` | `McpService` | Singleton |
| `IPidExtractorService` | — | — |
| `ITagMappingService` | — | — |

Singleton + HostedService pattern: same instance shared via `sp.GetRequiredService<T>()`.

## Background Services

All Singleton + HostedService, registered in `Program.cs`:
- `ExperionRealtimeService` — OPC UA subscription, 500ms batch flush
- `ExperionHistoryService` — snapshot 60s realtime_table → history_table
- `ExperionOpcServerService` — OPC UA server (port 4841)
- `McpServerHostedService` — Python MCP server process lifecycle (`uv run server.py --http`)
- `ExperionFastService` — high-frequency data capture
- `ExperionFastCleanupService` — expired session cleanup

Autostart flag files: `realtime_autostart.json`, `opcserver_autostart.json`

## Frontend

Vanilla JS SPA in `wwwroot/`. No build step. Tab navigation, no auto-fire on entry.

**API helper:** `async function api(method, path, body)` → wraps fetch, JSON in/out, returns parsed JSON
**All responses use camelCase:** `d.success`, `d.records`, `d.total`, `d.tagNames`, `d.nodeId`, `d.running`, `d.subscribedCount`, `d.sessionId`
**`wwwroot/js/app.js`** (3075 lines) — main app logic
**`wwwroot/js/pid-viewer.js`** (416 lines) — canvas-based P&ID graph viewer

## appsettings.json — Key Config

| Key | Value | Notes |
|---|---|---|
| `ConnectionStrings:DefaultConnection` | PostgreSQL `iiot_platform` | Main app DB |
| `ConnectionStrings:ExperionDbConnection` | PostgreSQL `postgres` | Utility connection |
| `OpcUaServer:Port` | 4841 | OPC UA server port |
| `OpcUaServer:EnableSecurity` | false | No security for now |
| `PidControllers:Enabled` | true | P&ID feature flag |
| `Fast:MaxConcurrentSessions` | 3 | |
| `Fast:MaxRowsPerSession` | 5000000 | |
| `Fast:FlushIntervalMs` | 2000 | |
| `McpServer:WorkingDirectory` | `../../mcp-server` | |
| `Kestrel:Endpoints:Http:Url` | `http://0.0.0.0:5000` | App port |

## OPC UA Gotchas

- SDK v1.5.378.134 — use `DefaultSessionFactory.CreateAsync()` (not obsolete `Session.Create()`)
- `Subscription.Create()`/`Delete()`/`ApplyChanges()` → async variants
- Certificate validation AFTER `OpcUaConfigProvider.GetConfigAsync()`
- Wrap `SelectEndpointAsync` with 10s CancellationTokenSource (OS default 127s)
- Tag address format: `ns=3;s=ficq-6113.pv`

## MCP Server (Python)

**Location:** `mcp-server/` — Python FastMCP server on port `5001`

**Startup:** `McpServerHostedService` launches `uv run server.py --http` in `McpServer:WorkingDirectory` (default `../../mcp-server`). Pings `localhost:5001` up to 30s to confirm ready.

**C# → Python bridge:**
- `IMcpService` → `McpService` → `McpClient` (Singleton)
- `McpClient` uses JSON-RPC over HTTP to `http://localhost:5001/mcp`
- All DTOs use `[JsonPropertyName]` for snake_case JSON keys
- `McpQueryResult { Success, Error, Data }`

**Python MCP server dependencies:** `mcp[cli]`, `fastapi`, `qdrant-client`, `sentence-transformers`, `openai`, `httpx`, `psycopg`, `ezdxf`, `paddleocr`, `pymupdf`

**Python infra stack:**
- **LLM:** vLLM serving Qwen3.6-35B-A3B-FP8 at `http://localhost:8001/v1`
- **Embeddings:** Ollama `nomic-embed-text` at `http://localhost:11434`
- **Vector DB:** Qdrant at `http://localhost:6333` (2 collections: codebase + OPC docs)
- **Task workers:** `worker/rag_worker.py` (:5002), `worker/nl2sql_worker.py` (:5003)

### MCP Tools (exposed to C# via `IMcpService`)

| Tool | C# Method | Purpose |
|---|---|---|
| `run_sql` | `RunSqlAsync(sql)` | Execute SELECT SQL |
| `query_pv_history` | `QueryPvHistoryAsync(...)` | History query by tag/time |
| `get_tag_metadata` | `GetTagMetadataAsync(query, limit)` | Tag search |
| `list_drawings` | `ListDrawingsAsync(unitNo)` | Drawing list |
| `query_with_nl` | `QueryWithNlAsync(question)` | NL → LLM → SQL → pivot |

## RAG (Retrieval-Augmented Generation)

### Python-side RAG Tools (in `server.py`)

| Tool | Purpose |
|---|---|
| `search_codebase(query, top_k)` | Qdrant search in ExperionCrawler C# code collection |
| `search_r530_docs(query, top_k)` | Qdrant search in Honeywell Experion HS R530 docs collection (266 chunks from `.htm` files) |
| `ask_iiot_llm(question, context)` | Direct Qwen3.6 Q&A with optional context |
| `rag_query(question, search_code, search_docs)` | Search + LLM synthesis in one call |

### C#-side Text-to-SQL (local, no MCP)

**Service:** `ITextToSqlService` → `TextToSqlService` (Scoped), registered in `Program.cs`

**Pipeline:**
1. `ParseNaturalLanguageAsync(input)` — Korean NL parser (tag names, time ranges, aggregates), up to 8 tags
2. `SqlValidator` — 7-stage validation pipeline:
   - SELECT-only, dangerous keywords block, forbidden clauses, function whitelist, table allowlist, subquery depth (max 4), injection patterns
3. `ExecuteQueryAsync(sql, limit)` — parameterized Npgsql, tag existence check
4. `AnalyzeAsync(dto)` — per-tag statistics (AVG, MIN, MAX, STDDEV, FIRST, LAST)

**Allowed tables (from `SqlValidatorOptions` in Program.cs):** `history_table`, `node_map_master`, `realtime_table`, `tag_metadata`, `v_tag_summary`

### Text-to-SQL Controller (`/api/text-to-sql/*`)

| Route | Method | Source | Description |
|---|---|---|---|
| `/parse` | POST | C# local | Korean NL → SQL |
| `/query-nl` | POST | MCP | NL → LLM → SQL → pivot |
| `/tools` | GET | MCP | List MCP tools |
| `/execute-mcp` | POST | MCP | Execute SQL via MCP |
| `/query-history` | POST | MCP | History query |
| `/tags/search` | GET | MCP | Tag search |
| `/drawings` | GET | MCP | Drawing list |
| `/suggest` | GET | C# local | Autocomplete suggestions |
| `/analyze` | POST | C# local | Time-series statistics |
| `/query-history-interval` | POST | C# local | Custom-interval aggregation |

### One-time Doc Indexing

`mcp-server/index_opc_docs.py` — reads `.htm` files from `/home/windpacer/projects/Experion_opcua_documents`, chunks (600 chars, 100 overlap), embeds with Ollama, upserts to Qdrant `experion-opc-docs`.

## Deploy

`sudo bash deploy.sh` → publishes to `/opt/ExperionCrawler`, creates systemd service `experioncrawler`, runs as `www-data`.