tree-survey-backend

Node.js / Express REST API powering the Flutter app tree-project-frontend. Handles authentication, tree-survey CRUD, project/area management, image storage (Cloudinary), reverse-proxying to a Python ML service, an AI chat assistant (Text-to-SQL) and a tool-using AI Agent.

Production runs on a self-hosted Ubuntu host behind a reverse proxy (Nginx; the original deployment used Tailscale for private networking), with PM2 cluster-mode supervision (see ecosystem.config.js).

Documentation hub (architecture, API, deploy, workflow):
frontend/docs/README.md → ONBOARDING_READING_PATH.md
Backend catalog (this repo): docs/README.md → docs/SOURCE_LAYOUT.md

---

Architecture overview

flowchart TB
  app["Flutter app<br/>sustainable_treeai (Android / iOS)"]
  subgraph backendHost["Backend host (Ubuntu)"]
    nbe["Nginx<br/>:443 → Node :3000<br/>(Tailscale Funnel exposes :443 publicly)"]
    be["backend (this repo)<br/>Node 20 + Express<br/>PM2 cluster ×2 · ecosystem.config.js"]
    nbe --> be
  end
  subgraph mlHost["ML host (separate machine, private network)"]
    nml["Nginx<br/>:443 → uvicorn :8100"]
    ml["ml_service/ (FastAPI)<br/>Depth Anything v3 metric-large<br/>OpenVINO FP16 on Intel NPU<br/>+ server YOLOv8m-seg on Intel iGPU<br/>+ pure pinhole DBH calculator"]
    nml --> ml
  end
  ext[("PostgreSQL 15+ · Cloudinary · PlantNet / GBIF /<br/>iNaturalist · OpenAI / Gemini / SiliconFlow / Anthropic")]
  app -- "HTTPS (TLS)" --> nbe
  app -- "HTTPS + JWT<br/>multipart image / EXIF / bbox" --> nbe
  be -- "multipart + X-ML-API-Key" --> nml
  nml -- "DBH / mask / warnings" --> be
  be --> ext
  gh["GitHub push (main)"] -- "HMAC-SHA256" --> nbe
  be -. triggers .-> deploy["scripts/deploy.sh<br/>git pull → npm install → migrate<br/>→ pm2 reload tree-backend → /health → auto-rollback"]

Two-service deployment with a Node ML proxy. The Flutter app talks to the Node backend for CRUD/auth/AI and for static DBH measurement through /api/ml-service/*. The backend then forwards multipart images to the FastAPI ml_service and injects X-ML-API-Key from the host environment, so the ML API key is not stored on the client. Legacy or experimental live scan paths may still talk to the FastAPI host directly, but the production DBH HTTP flow is Flutter → Node proxy → FastAPI.

The exact public hostnames, private-network IPs and reverse-proxy domains are deployment-specific and not committed to this repo. Configure them via environment variables (BASE_URL, ML_SERVICE_URL, ML_SERVICE_PUBLIC_URL, CORS_ALLOWED_ORIGINS) on the host.

---

Feature flows

Each subsection traces one user-visible feature from the Flutter app down to the database / external services. Bracketed paths are files in this repo.

1. Authentication (POST /api/login)

flowchart TB
  fp["Flutter LoginPage<br/>{account, password, loginType}"]
  rl["middleware/loginRateLimiter<br/>10 req / 15 min / IP"]
  bl["middleware/ipBlacklist"]
  rt["routes/users.js POST /login<br/>WHERE username=account (+role check if admin)<br/>bcrypt.compare(password, users.password_hash)"]
  db[("PostgreSQL · users<br/>(user_id, username, display_name, role, …)")]
  jwt["JWT (HS256, 24 h)<br/>payload = {user_id, username, role}"]
  resp["{token, user, mlConfig?}<br/>mlConfig.url only — no client ML API key"]
  st["flutter_secure_storage (auth_jwt_token, user_info)<br/>SharedPreferences (ml_service_url, ml_api_key)"]
  fp --> rl --> bl --> rt --> db --> jwt --> resp --> st

Logout is client-side only (delete token + clear ML credentials).

2. Tree survey CRUD with image upload

flowchart TB
  fm["Flutter survey form (V2 / V3)<br/>multipart/form-data: fields + photo[]"]
  jw["middleware/jwtAuth → req.user"]
  up["middleware/upload (multer)<br/>memoryStorage · 10 MB · image/* only"]
  rt["routes/treeSurvey.js POST /api/tree_survey/create_v2<br/>(photos via routes/tree_images.js POST /api/tree-images/upload)"]
  cl["Cloudinary upload_stream(buffer)<br/>→ tree_images (cloud_url, thumbnail_url, public_id)"]
  geo["resolveCountyByLngLat(lng, lat)<br/>utils/geo.js + data/tw_county.geojson<br/>(22 counties, MOI 1140318)"]
  db[("PostgreSQL · tree_survey + tree_images<br/>single transaction")]
  out["{ tree, photos[] }"]
  fm --> jw --> up --> rt
  rt --> cl
  rt --> geo
  rt --> db --> out

GET / PUT / DELETE follow the same auth + ownership-check pattern in routes/treeSurvey.js (tree records) and routes/tree_images.js (photos).

3. AI chat — Text-to-SQL (POST /api/chat)

flowchart TB
  cp["Flutter ChatPage<br/>POST /api/chat {message, model_preference, sessionId, projectAreas}"]
  jw["jwtAuth + requireRole(調查管理員) + aiLimiter"]
  rt["routes/ai.js"]
  sql["gpt-4.1-nano drafts SQL<br/>(or NOT_A_DATA_QUERY → knowledge mode)"]
  sv["services/sqlQueryService.executeSecureQuery<br/>security validation + 1 auto-retry · read-only"]
  db[("PostgreSQL")]
  exp["explain result with selected model<br/>(gpt-5-nano/mini/5.1, gemini-*, Qwen/*,<br/>deepseek-ai/DeepSeek-V3 default)"]
  log[("chat_logs<br/>(user_id, message, response, model_used, session_id)")]
  out["JSON response"]
  cp --> jw --> rt --> sql --> sv --> db --> exp --> out
  exp --> log

Response is a regular JSON payload (not SSE). Audit trail: every Q/A pair + model + final response is written to chat_logs (see routes/ai.js).

4. AI agent — tool-using (/api/agent/*)

flowchart TB
  ap["Flutter AgentPage<br/>POST /api/agent/chat {messages}"]
  rt["routes/agent.js"]
  svc["services/agentService.js<br/>tool-using loop · default model gpt-5.4-mini"]
  t1["fetch_allowed_url(s) / search_public_documents<br/>(allow-listed policy URLs)"]
  t2["list_policy_sources / list_allowed_domains / list_demo_policy_urls"]
  t3["export_excel / export_pdf / export_ai_report"]
  impl["services/agentDataTools.js (tool implementations)"]
  llm["LLM: prefers SiliconFlow if keys configured,<br/>otherwise OpenAI; cross-provider fallback"]
  out["JSON {answer, tool trace}"]
  ap --> rt --> svc
  svc --> t1 --> impl
  svc --> t2 --> impl
  svc --> t3 --> impl
  svc --> llm --> out

5. ML pipeline — Auto DBH measurement

flowchart TB
  fp["Flutter measurement page (V3)<br/>image + EXIF + phone bbox<br/>Authorization: Bearer JWT"]
  proxy["backend routes/ml_service.js<br/>POST /api/ml-service/auto-measure-dbh<br/>multer memory upload · JWT protected"]
  app2["ml_service/app.py<br/>verify_api_key (hmac.compare_digest)<br/>RateLimitMiddleware (ML_RATE_LIMIT, default 30/hr/IP)"]
  seg["ServerYOLO<br/>YOLOv8m-seg OpenVINO<br/>Intel iGPU · 832 px<br/>→ trunk mask"]
  de["depth_estimation.py<br/>Depth Anything v3 metric-large<br/>OpenVINO FP16 · Intel NPU · 504×378<br/>→ monocular metric depth"]
  dbh["dbh_calculator.py<br/>mask row chord width Wpx<br/>central 1/3 chord-depth z<br/>pure pinhole d = z · Wpx/fpx"]
  out["{ dbh_cm, trunk_depth_m, trunk_pixel_width,<br/>quality_code, mask/debug images, confidence }"]
  ui["Flutter form/scanner<br/>user confirms → tree_survey / pending measurement"]
  fp --> proxy --> app2
  app2 --> seg --> dbh
  app2 --> de --> dbh
  dbh --> out --> ui

The production static DBH flow goes through the Node proxy. WebSocket /ws/scan is kept for live preview experiments and should be treated as optional unless it is routed through the same authentication path.

6. Species identification (/api/species/*)

flowchart TB
  sp["Flutter SpeciesIdentifyPage<br/>POST /api/species/identify (image)"]
  rt["routes/speciesIdentification.js"]
  svc["services/speciesIdentificationService"]
  pn["PlantNet REST API<br/>(org=tree, lang=zh-tw)"]
  add["autoAddSpeciesFromIdentification()<br/>insert tree_species (scientific_name, …)<br/>insert common_names → species_synonyms"]
  out["{ candidates[],<br/>top: {species_id, scientific_name,<br/>common_name, score} }"]
  sp --> rt --> svc
  svc --> pn --> svc
  svc --> add
  svc --> out

V3 form auto-creates a species row at submit time (services/speciesIdentificationService.js) so the user never has to "save species" manually.

7. County auto-detection

flowchart TB
  cl["client<br/>GET /api/project_areas/county_by_coords?lng=&lat="]
  rt["routes/project_areas.js"]
  geo["utils/geo.js · resolveCountyByLngLat<br/>data/tw_county.geojson (cached on first call)<br/>bbox prefilter → booleanPointInPolygon (@turf/turf)"]
  out["{ name: '嘉義縣', code: '10010',<br/>eng: 'Chiayi County' } or null"]
  cl --> rt --> geo --> out

scripts/backfill_county.js --apply re-runs the helper for every projects.area row whose county is NULL.

8. Auto-deploy (GitHub webhook → production)

flowchart TB
  gh["GitHub push to main<br/>POST https://&lt;public-host&gt;/webhook/deploy<br/>X-Hub-Signature-256: sha256=…"]
  wh["routes/webhook.js<br/>hmac(DEPLOY_WEBHOOK_SECRET) timing-safe"]
  sp["spawn bash scripts/deploy.sh<br/>(timeout 120 s, log → logs/deploy.log)"]
  s1["/health check<br/>save SHA → .last_good_commit"]
  s2["git pull origin main"]
  s3["npm install --production"]
  s4["run pending migrations<br/>(skip with --skip-migrate)"]
  s5["pm2 reload ecosystem.config.js<br/>(zero-downtime)"]
  s6{"curl /health"}
  ok(["done"])
  rb["git reset --hard $LAST_GOOD<br/>pm2 reload · alert"]
  gh --> wh --> sp --> s1 --> s2 --> s3 --> s4 --> s5 --> s6
  s6 -- pass --> ok
  s6 -- fail --> rb

GET /webhook/status (Bearer ADMIN_API_TOKEN) returns the tail of logs/deploy.log.

---

Stack

• Node.js >= 18 (production runs on 20)
• Express 4 + helmet + express-rate-limit
• PostgreSQL >= 14 (pg + pg-format + pg-copy-streams)
• JWT (jsonwebtoken, HS256, fixed algorithm)
• bcryptjs for password hashing
• Multer for file uploads, ExcelJS / xlsx for import-export, PDFKit for reports
• Cloudinary SDK for image storage
• AI SDKs: openai, @anthropic-ai/sdk, @google/generative-ai
• @turf/turf for point-in-polygon (county auto-detection, project boundaries)
• PM2 (cluster mode, 2 workers — see ecosystem.config.js)

---

Quick start

cd backend
cp tree-app-backend-prod.env .env   # then edit values (see Configuration)
npm install
node scripts/migrate.js             # fresh empty DB: schema only (+ optional dev CSV)
node scripts/seed_dev_users.js    # dev/CI only: test accounts (NOT for production)
npm run dev                         # nodemon on PORT (default 3000)

Health check: GET /health → 200 OK (no auth required).

In production mode, app.js runs scripts/run_pending_migrations.js automatically on startup (schema only; no CSV import). For a fresh empty DB, run node scripts/migrate.js manually once.

---

Configuration

All configuration is via environment variables loaded with dotenv from .env. The keys actually read by the code:

Server / runtime

Database (config/db.js)

Auth (middleware/jwtAuth.js)

CORS (app.js)

Cloudinary (config/cloudinary.js)

Reverse-proxied ML service (routes/ml_service.js, routes/users.js)

AI providers

GitHub auto-deploy (routes/webhook.js)

Other

---

Scripts

package.json:

The tests under tests/ are plain Node scripts, not a framework.

scripts/:

---

Directory layout

backend/
├── app.js                          # Entry. Trust proxy → CORS → helmet → JSON
│                                   # → /health → /webhook → /api(ipBlacklist→
│                                   # burstLimiter→apiLimiter→jwtAuth→router)
│                                   # → global error handler. Hourly cleanup
│                                   # cron and one-shot 5s-after-startup
│                                   # chat-log cleanup are also kicked off here.
├── config/
│   ├── db.js                       # pg.Pool (DATABASE_URL, optional SSL)
│   ├── cloudinary.js               # SDK config + isCloudinaryConfigured guard
│   └── apiKeys.js                  # Static helper for AI key lookup
├── controllers/                    # Thin handlers called from routes/
│   ├── treeSurveyCreateController.js / *Update / *Batch
│   ├── treeManagementController.js
│   ├── carbonSinkController.js
│   ├── csvImportController.js
│   ├── reportController.js / aiReportController.js
│   ├── openaiController.js
│   ├── aiController.js
│   └── knowledgeController.js
├── middleware/
│   ├── jwtAuth.js                  # HS256, Bearer-only, OPTIONS + /login skip
│   ├── rateLimiter.js              # apiLimiter / burstLimiter / loginLimiter / aiLimiter
│   ├── ipBlacklistGuard.js         # Reads ip_blacklist row; auto-expires
│   ├── loginAttemptMonitor.js      # 5-strikes per account, 30-min lock
│   ├── projectAuth.js              # Per-resource project-permission check (5-min cache)
│   └── roleAuth.js                 # 5-tier RBAC (see "Auth model")
├── routes/                         # 24 modules — see "API surface"
├── services/
│   ├── sqlQueryService.js          # Text-to-SQL: intent classifier, schema
│   │                               # info, SQL safety validator (forbidden
│   │                               # keywords/patterns, complexity guard,
│   │                               # whitelist), result formatter
│   ├── agentService.js             # ReAct Agent: tool definitions, multi-key
│   │                               # rotation, persistent token budget table,
│   │                               # max 8 tool steps
│   ├── geminiService.js            # Gemini chat
│   ├── openaiService.js            # OpenAI chat (used by controllers)
│   ├── speciesIdentificationService.js   # PlantNet wrapper + GBIF / iNaturalist enrichment
│   ├── speciesSynonymService.js    # Synonym table + scheduled maintenance
│   ├── knowledgeEmbeddingService.js  # pgvector cosine search (legacy RAG)
│   ├── auditLogService.js          # Centralized audit-log writer
│   └── ipBlacklistService.js       # recordOffense / cleanupOldLoginAttempts
├── utils/
│   ├── cleanup.js                  # Hourly maintenance (see "Background jobs")
│   └── …
├── database/
│   └── initial_data/               # Schema + seed + post-schema fix-up SQL
│                                   # (see "Database")
├── ml_service/                     # Standalone Python FastAPI service
├── scripts/                        # Operational scripts (above)
├── tests/                          # Plain Node tests
├── docs/                           # Internal design notes
├── ecosystem.config.js             # PM2 config (cluster, 2 workers)
└── tree-app-backend-prod.env       # Sample production env-file (do not commit secrets)

---

Request pipeline

trust proxy (Tailscale / Nginx)
   │
   ▼
CORS — whitelist from CORS_ALLOWED_ORIGINS (empty in prod = deny)
   │
   ▼
helmet — standard security headers
   │
   ▼
express.json (limit 10 MB; rawBody captured for /webhook routes)
   │
   ├─► GET /health                       (no auth)
   │
   ├─► POST /webhook/deploy              (HMAC-SHA256 verified)
   ├─► GET  /webhook/status              (X-Admin-Token)
   │
   └─► /api/*
           │
           ▼
       ipBlacklistGuard
           │  (reads ip_blacklist; auto-expires; can be disabled with
           │   DISABLE_IP_GUARD=true for local testing)
           ▼
       burstLimiter        — 20 req / 10 s per IP. Excess writes ip_blacklist
           ▼                  with offense-count escalation (5-min base ban)
       apiLimiter          — 500 req / 15 min per IP
           ▼
       jwtAuth             — Bearer HS256; OPTIONS and /login are skipped
           ▼
       Router              — see "API surface"
           │
           ▼
   global error handler — logs full stack; in prod returns "伺服器發生未預期的錯誤"

---

Auth model

JWT-only authentication (the historical X-Admin-Token fallback was removed on 2026-04-27). Token payload (set in routes/users.js after login) includes user_id, username, role, and is signed HS256.

Roles (middleware/roleAuth.js)

requireRole('業務管理員') admits anyone with a role of equal or higher level. req.isAdmin is set when level ≥ 3.

Per-project permissions

Two middlewares in middleware/projectAuth.js:

• projectAuth — for write/delete on a single resource. Looks up user_projects (level ≥ 4 always passes; deeper levels checked against the junction table). For PUT/DELETE without a body project_code, the middleware first reads the resource's project_code from tree_survey.
• projectAuthFilter — for list endpoints. Sets req.projectFilter to either null (no restriction) or an array of project codes the user can see; routes apply it as WHERE project_code = ANY($1::text[]).

A 5-minute in-memory cache (_cache in projectAuth.js) holds each user's project list to avoid hitting the DB on every request. invalidateUserProjectsCache(userId) is called after permission edits.

Login hardening

• 5 wrong passwords on the same account → 30-minute lock (per loginAttemptMonitor.js).
• loginLimiter: 50 attempts / hour per IP.
• burstLimiter: 20 req / 10 s per IP. Triggers escalating ban writes to ip_blacklist (recordOffense).
• LOGIN_FAILED and LOGIN events go to audit_logs via auditLogService.

---

API surface

All endpoints below are mounted under /api. They require a valid JWT, with an additional minimum role where indicated.

Auth & users (routes/users.js)

• POST /login — accepts { account, password, loginType: 'admin' | … }. Admin login is restricted to roles 系統管理員 / 業務管理員 / 專案管理員 / 調查管理員. Returns JWT + accessible projects + ML service config (URL + API key).
• GET /users (≥業務管理員)
• POST /users (≥業務管理員)
• PUT /users/:id (≥業務管理員)
• PUT /users/:id/status (≥業務管理員) — enable/disable
• DELETE /users/:id (≥業務管理員)
• GET /users/:userId/projects / PUT /users/:userId/projects (≥業務管理員)
• POST /register — public invite-code registration (loginRateLimiter). Consumes an invite code; if the code has requires_approval, the new account is created with is_active=false / pending_approval=true for admin review.
• GET /invites / POST /invites (≥業務管理員) — list / create invite codes (role ≤ creator, max_uses, expires_in_days, project_codes[]).
• PATCH /invites/:inviteId/deactivate (≥業務管理員) — disable a code.
• DELETE /invites/:inviteId (≥業務管理員) — delete (writes audit DELETE_INVITE). See ADMIN_AND_INVITE_DESIGN.md for the full design.

Projects (routes/projects.js, routes/project_areas.js, routes/project_boundaries.js)

• GET /projects / /projects/by_area/:area / /projects/by_name/:name / /projects/by_code/:code — filtered by projectAuthFilter. Each prefers the projects table and falls back to SELECT DISTINCT FROM tree_survey if the row is missing (legacy data path).
• POST /projects/add (≥業務管理員) — resolves area_id by area name lookup.
• DELETE /projects/:code (≥業務管理員)
• GET/POST/PUT/DELETE /project_areas (POST/PUT/DELETE need ≥專案管理員)
• POST /project_areas/cleanup (≥系統管理員) — manual run of the same routine that the hourly cron triggers.
• /project-boundaries: GeoJSON storage. Public reads, writes need ≥專案管理員. Includes POST /check, /find_project, /batch_match (turf-based point-in-polygon for assigning trees to a project automatically).

Tree survey (routes/treeSurvey.js)

• GET /tree_survey — list (paginated, projectAuthFilter).
• GET /tree_survey/map — slim payload for map rendering (only valid coordinates, placeholder rows excluded).
• GET /tree_survey/by_id/:id, /by_project/:projectName, /by_area/:areaName.
• POST /tree_survey/create_v2 (≥調查管理員 + projectAuth) — V2 tree creation flow.
• PUT /tree_survey/update_v2/:id (≥調查管理員 + projectAuth).
• DELETE /tree_survey/:id and /tree_survey/placeholder/:id (≥專案管理員 + projectAuth).
• POST /tree_survey/:id/retire (≥調查管理員 + projectAuth) — soft-retire a tree (lifecycle_status ∈ dead/fallen/removed); keeps history/photos, excludes it from living-biomass carbon totals and the maintenance queue. Matches the maintenance workflow (surveyors report dead/fallen/removed).
• POST /tree_survey/:id/restore (≥調查管理員 + projectAuth) — set lifecycle_status back to active.
• POST /tree_survey/batch_import (≥調查管理員 + projectAuth).
• POST /tree_survey/import (≥專案管理員) — Excel/CSV upload (Multer).
• GET /tree_survey/template — Excel template download.
• GET /tree_survey/next_system_number and /next_project_number/:projectCode.
• GET /tree_survey/common_species/:projectCode.

Tree species (routes/treeSpecies.js)

• CRUD plus /search, /enhanced (每個樹種帶出其同義詞清單), /synonyms/report, /synonyms/merge (≥系統管理員), /next_number.

Two-stage measurement (routes/pending_measurements.js)

• POST /pending-measurements/batch — create a batch (V3 workflow).
• GET /sessions, GET /trees, GET /stats/overview.
• GET /:id, PATCH /:id, POST /transfer — convert pending → real tree in tree_survey.
• PATCH /session/:sessionId/project, DELETE /session/:sessionId.

Reports (routes/reports.js)

• GET /export/excel (≥調查管理員) — ExcelJS, with project filter.
• GET /export/pdf (≥調查管理員) — PDFKit.
• GET /sustainability_report (≥調查管理員) — calls reportController.

Statistics (routes/statistics.js)

• GET /tree_statistics — counts, averages, carbon totals, with project filter.

AI assistant (routes/ai.js)

• POST /chat (≥調查管理員, aiLimiter) — primary endpoint. Pipeline:
  1. Length cap 500 chars + auto-truncation.
  2. Auto-sessionId if absent (<userId>_<yyyymmdd>).
  3. Load history with sqlQueryService.getHistoryQuerySQL — WHERE user_id=$1 AND session_id=$2 AND created_at > NOW() - INTERVAL '15 minutes', up to 10 rows.
  4. shouldQueryDatabase() — keyword + scoring intent classifier.
  5. Data path: build SQL prompt → LLM → validateSQL() (whitelist tables; blacklist of INSERT/UPDATE/DELETE/..., XP_CMDSHELL, PG_SLEEP, CHAR()/CHR()/hex/Unicode escape, OR 1=1, etc.) → run with forced LIMIT ≤ 100 → format result → if rows > 5, generate Excel and return a download URL under /api/download/:filename.
  6. Knowledge path: direct LLM answer.
  7. Insert into chat_logs with chat_mode='chat' and the model name. In production a model whitelist is enforced (DeepSeek-V3, Qwen3-235B/32B, QwQ-32B, GPT-5-nano/mini/5.1, Gemini 2.5-flash/pro). Anything else is silently rewritten to DeepSeek-V3.
• POST /ai/direct-chat (≥調查管理員) — bypass intent classifier; raw LLM.
• GET /reports/ai-sustainability[/pdf] (≥調查管理員) — AI-generated report.
• POST /sustainability-policy, GET /carbon-education/:topic, POST /carbon-footprint/advice, POST /species-comparison.
• GET /download/:filename — temp Excel exports (exports/, auto-cleaned every 30 min for files older than 1 hour).

AI agent (routes/agent.js + services/agentService.js)

• POST /agent/chat (≥調查管理員, agentLimiter 30/10 min) — ReAct loop. Default model gpt-5.4-mini (AGENT_DEFAULT_MODEL, OpenAI-first); SiliconFlow models are an optional fallback chain (useSiliconFlowFirst=false). Tools:
  • query_tree_data(query, project_area?) → reuses sqlQueryService to run safe Text-to-SQL.
  • calculate_carbon(dbh_cm, height_m?, species?) → Chave 2014 allometry.
  • species_carbon_info(species_name) → 以 tree_survey 實際調查資料統計（舊 tree_carbon_data 靜態表已移除）。
  • project_summary(project_area?).
  • carbon_report(...). Memory: 5 prior turns of the same (user_id, session_id, chat_mode='agent'). Per-user token budget: 50 000 tokens / hour, persisted in agent_token_usage (so cluster workers and restarts share the counter). Hard cap: 8 tool-call iterations per turn. The reply summary, tool list, and tokensUsed are stored in chat_logs.metadata.
• GET /agent/status (≥調查管理員), GET /agent/models (≥調查管理員).

Carbon

• 舊的 routes/carbon.js / routes/carbon_data.js（carbon-sink、credit、education、 species-comparison 等）與 tree_carbon_data 靜態表已整批移除。
• 現行碳計算改由 services/carbonCalculationService.js / handbookCarbonService.js 以程式內公式 + tree_survey 欄位計算。

Tree management (routes/management.js)

• POST /tree-management/actions/generate (≥專案管理員)
• GET /actions (≥調查管理員), PUT/DELETE /actions/:action_id (≥專案管理員)

Location (routes/location.js, routes/project_areas.js)

• POST /location/validate, /suggest_area.
• GET /project_areas/county_by_coords?lng=&lat= — returns the official county for a coordinate. All county lookups (this route, project-area auto-fill on submit, and scripts/backfill_county.js) share a single helper utils/geo.js which loads data/tw_county.geojson (Ministry of Interior 1140318 official boundaries, 22 counties, TWD97 lat/lon) and uses @turf/turf booleanPointInPolygon with a bbox prefilter. The legacy data/twCounty2010.fixed.geo.json is no longer read.

Knowledge base（已移除）

• 舊 RAG 知識庫（routes/knowledge.js + tree_knowledge_embeddings_v2 + pgvector） 已整批移除；現行 /chat、Agent 皆不使用。

Species ID (routes/speciesIdentification.js)

• POST /species/identify — multipart image; PlantNet → fallback heuristics.
• GET /species/search, /gbif/:name, /inaturalist/:id, /status.

ML service proxy (routes/ml_service.js)

• GET /ml-service/status, /ml-service/config. The Flutter app calls the Python service directly using the URL + API key returned at login; this proxy is for diagnostics from a browser without exposing ML_API_KEY.

Tree images (routes/tree_images.js)

• POST /tree-images/upload (Multer + Cloudinary), GET /:id, GET /tree/:treeId, DELETE /:id (≥專案管理員).

ML training data (routes/ml_training_data.js)

• POST /ml-training/batch, /image. Reads/exports under ≥業務管理員.

CSV import (routes/csvImport.js)

• POST /admin/import-csv/preview and /execute (≥業務管理員).

IP blacklist (routes/ipBlacklist.js)

• GET / /stats, POST /, DELETE /:ip. All ≥系統管理員.

Admin (routes/admin.js)

• POST /admin/run-script (≥系統管理員), /backup, /restore.
• GET/POST/DELETE /admin/apikeys (≥系統管理員) — encrypted at rest.

Webhook (routes/webhook.js)

• POST /webhook/deploy — HMAC-SHA256 (DEPLOY_WEBHOOK_SECRET); only push events on refs/heads/main trigger scripts/deploy.sh.
• GET /webhook/status — recent deploy log lines (X-Admin-Token only).

---

Background jobs

Started by app.listen in app.js:

• One-shot 5 s after startup: cleanupOldChatLogs().
• Every 60 minutes: cleanupOrphanedPlaceholders → cleanupUnusedSpecies → cleanupUnusedProjectAreas (which also heals dangling projects.area_id refs) → cleanupOldChatLogs → cleanupOldLoginAttempts → scheduledSynonymMaintenance.

Defined in utils/cleanup.js and services/{ipBlacklistService,speciesSynonymService}.js:

Other intervals running outside app.js:

• routes/ai.js: every 30 min — purge exports/ files older than 1 hour.

---

Database

PostgreSQL. Schema files live in database/initial_data/ and are executed in this fixed order by scripts/migrate.js:

00_init_functions.pg.sql            update_updated_at_column() shared trigger
users.pg.sql                        users + ENUM user_role (schema only; no seed rows)
system_settings_and_audit.pg.sql    audit_logs (system_settings 已於 migration 25 移除)
project_areas.pg.sql                project_areas table (schema only; demo ports in dev-fixtures/)
tree_species.pg.sql                 master species list
tree_survey.pg.sql                  main survey table (~7 k rows)
00_normalization_schema.pg.sql      normalization helpers (must run after
                                    tree_survey exists)
tree_management_actions.pg.sql      AI 管理建議表（功能保留、前端暫無入口；冪等建表、不灌 demo）
chat_logs.pg.sql
02_chat_logs_add_session.pg.sql     adds session_id
04_chat_logs_agent_mode.pg.sql      adds chat_mode + metadata JSONB
01_sync_project_id_trigger.sql      auto-creates a projects row when a
                                    tree_survey row arrives with a new
                                    project_code; superseded by 07_…
ml_training_data.pg.sql
z_pending_tree_measurements.pg.sql  V3 two-stage measurement workflow
tree_images.pg.sql                  Cloudinary references, FK to tree_survey
species_synonyms.pg.sql             synonyms + species_merge_log
03_user_projects.pg.sql             junction table; backfilled from
                                    users.associated_projects
05_ip_blacklist.pg.sql              ip_blacklist + ip_login_attempts
06a_project_boundaries_schema.pg.sql  project_boundaries table (schema only, no
                                    seed). Demo 35-port polygons are dev-fixtures:
                                    `node scripts/seed_dev_boundaries.js`.
07_backfill_projects_area_id.pg.sql Heal projects rows that the auto-create
                                    trigger left with NULL area_id; also
                                    repairs placeholder names by copying the
                                    dominant project_name from tree_survey.
                                    Idempotent.

After all tables are created, migrate.js:

1. COPY tree_survey FROM tree_survey_data.csv (a Transform stream rewrites 0000-00-00 00:00:00 to NULL on the fly).
2. Creates the tree_survey_with_areas view.
3. setval(pg_get_serial_sequence('tree_survey','id'), MAX(id)) to keep the auto-increment in sync after the COPY.
4. Calls populateSpeciesRegionScore().

To add a migration: drop a NN_*.pg.sql file under initial_data/ and add its filename to the migrationFiles array in scripts/migrate.js.

---

ML service

ml_service/ is a separate FastAPI / Python app that the Flutter client calls directly (not via the Node backend). The Node backend only stores and forwards the URL + key to the client at login time.

Endpoints (ml_service/app.py):

• GET /health, GET /api/v1/health
• GET /api/v1/config
• POST /api/v1/estimate-depth (auth)
• POST /api/v1/measure-dbh (auth)
• POST /api/v1/auto-measure-dbh (auth)
• POST /api/v1/auto-measure-dbh-multi (auth)
• POST /api/v1/debug/depth-at-point (auth)
• WebSocket /ws/scan — live scan frames.

Auth: X-ML-API-Key header (or Authorization: Bearer …), compared with hmac.compare_digest. Requests without the key are accepted only in dev mode (ML_API_KEY unset). CORS origins come from ML_CORS_ORIGINS.

Models loaded (production default, start.ps1 -Preset da3): Depth-Anything-3 Metric Large as OpenVINO IR on the Intel NPU for depth, plus a server-side YOLOv8-seg mask on the Intel iGPU (ML_FORCE_SERVER_YOLO=true, ML_ENABLE_SAM=false). Apple Depth Pro and SAM 2.1 Tiny remain available as alternative presets. See ml_service/README.md for the full preset matrix and setup.

Local dev startup (Windows)

ml_service/start.ps1 is the canonical Windows launcher. It loads ml_service/.env, picks an env preset, prints a config summary, runs a GPU probe (Intel XPU / CUDA / CPU), and launches uvicorn.

cd backend\ml_service
.\start.ps1                  # default model (DA V2 Base, CPU)
.\start.ps1 -Preset pro      # Depth Pro, PyTorch
.\start.ps1 -Preset pro_ov   # Depth Pro + OpenVINO INT8-W on iGPU (recommended)
.\start.ps1 -Verify          # enable numpy verification
.\start.ps1 -Workers 2 -Port 8101

Key env vars consumed (also documented in ml_service/README*): ML_DEPTH_MODEL, ML_USE_OPENVINO, ML_ENABLE_SAM, ML_SEG_MODEL, ML_API_KEY, ML_CORS_ORIGINS, PORT. On Linux production the same process is supervised by pm2 / systemd and uses the venv at ml_service/venv/.

ml_service/run_export.ps1 is a separate utility that exports the Depth Pro weights to OpenVINO IR INT8-W (one-time setup; required before -Preset pro_ov works).

---

Deployment

Production layout

/opt/tree-app/
├── backend/           # this repo, deployed by deploy.sh
├── logs/              # PM2 + deploy.log
└── scripts/           # symlinks to backend/scripts

Auto-deploy flow

1. Push to main on GitHub.
2. GitHub webhook → POST /webhook/deploy → HMAC verified.
3. routes/webhook.js runs bash scripts/deploy.sh (timeout 120 s).
4. deploy.sh:
   • Save current commit to .last_good_commit (only if /health is OK).
   • git pull origin main.
   • npm install --production.
   • Run migrations (skipped with --skip-migrate).
   • pm2 reload ecosystem.config.js (zero-downtime, cluster).
   • Curl /health; on failure, git reset --hard $LAST_GOOD and reload.

Manual operations

• pm2 reload ecosystem.config.js — reload after a manual pull.
• pm2 logs tree-backend — tail logs.
• scripts/rollback.sh — restore .last_good_commit.
• scripts/backup_db.sh — runs nightly via cron.

---

Testing

Tests are plain Node scripts under tests/. There is no test framework; each script runs assertions, prints, and exits non-zero on failure.

npm test                    # intent classification + sql validation
npm run test:integration    # chat integration (text-to-sql end-to-end)
npm run test:api            # /api routes (smoke)
npm run test:regression     # full regression against prod
npm run test:regression:local
npm run test:all            # regression + api + securityAudit

tests/regression.test.js reads TEST_BASE_URL, TEST_ADMIN_USER/PASS, TEST_SURVEY_USER/PASS.

---

Documentation

交接與技術文件統一維護在 frontend repo 的 docs/（單一正本）。本 repo 的 docs/README.md 為指標頁（含可點擊連結）。

後端架構、API 清單、資料表、背景工作見本檔（上方各節）。

---

License

MIT — see LICENSE. Original development and primary maintenance by KyleliuNDHU; see AUTHORS.md.