wbsysc

# MCP Gateway MCP Gateway provides a data relay capability for WeCom's **approval / reporting / check-in** data, exposing it to WorkBuddy/CodeBuddy through the MCP protocol. > Solving pain points: WorkBuddy's WeCom connector **only supports MCP** and can only "create/write", **not read** historical business data. This service acts as a data relay layer, actively pulling data from WeCom's OpenAPI and then exposing it to WorkBuddy in MCP Server form. ## ✨ Features - **MCP Gateway** (Streamable HTTP + Bearer Token, remote multi-client connection) - **Three types of data reading**: approval, reporting, check-in (smart table temporarily suspended: see below for details) - **Multi-tenancy physical isolation**: each client has an independent MySQL schema (`wbd_{corpid_hash}`), with credentials encrypted using AES - **Tenant-level feature switches**: each client can choose a combination of `report`/`approval`/`checkin` modules - **Automatic contact synchronization**: with contact synchronization secret → automatically call `user/list_id` to pull all user IDs - **Incremental synchronization**: cursor-driven + breakpoint continuation + idempotent UPSERT + APScheduler timing - **Management backend**: React + Ant Design, tenant CRUD, module switching, manual synchronization, single-password login - **CI/CD**: GitHub Actions automatically build and push images to GHCR, server `docker pull` without local compilation - **Production readiness**: Dockerfile + Nginx/HTTPS + systemd + health check + log rotation ## 🏗 Architecture ``` ┌─ WeCom OpenAPI ──────────────────────────────────────────┐ │ Approval oa/getapprovalinfo+getapprovaldetail │ │ Reporting oa/journal/get_record_list+get_record_detail │ │ Check-in checkin/getcheckindata │ │ Contact list user/list_id │ └──────────────────────────────────────────────────────────┘ │ ① Synchronization layer (APScheduler schedules by tenant, cursor increment) ▼ ┌─ MySQL (multi-tenancy schema isolation) ─────────────────────────┐ │ Central database websysc: tenant_config (credential AES encryption) │ │ Tenant database wbd_{hash}: wecom_report/approval/checkin + │ │ sync_cursor + audit_log │ └──────────────────────────────────────────────────────────┘ │ ② MCP Gateway exposure layer (Streamable HTTP) ▼ WorkBuddy / CodeBuddy (Bearer Token → tenant strong binding → read the tenant schema) ▲ ③ Management backend (independent session authentication) │ Browser http://server/admin/ui/ (React+AntD) ``` - **Production transport**: HTTP (Streamable HTTP), path `/mcp` - **Authentication**: MCP uses Bearer Token (each tenant is independent, token → tenant strong binding, not trusted client tenant_id); management backend uses single-password session - **Synchronization strategy**: first-period timing polling (incremental cursor), reserved Webhook position, no MQ ### Data Reading Mode Each tenant can choose `stored` or `direct`. `stored` synchronizes business data to the tenant's MySQL schema regularly, and MCP queries the local table. `direct` requests WeCom API for each MCP call, without writing to approval, reporting, or check-in business tables, nor participating in backend synchronization. Both modes save tenant configuration, encrypted credentials, MCP Token, and audit logs in MySQL. Direct connection requests that fail will return WeCom errors, without reading historical cache. Existing tenants are upgraded to keep `stored`. In direct mode, querying large time windows will start traversing WeCom's list pagination from the latest time segment and request details for each single number, resulting in higher API call costs. Production calls recommend reducing time windows and setting smaller `limit`. ## 🚀 Quick Start ### Method 1: Docker (production recommended, using CI-built images) ```bash git clone https://github.com/hkxiaoyao/wbsysc.git && cd wbsysc cp .env.prod.example .env && vim .env # Confirm WECOM_USE_MOCK=false, fill in DB_PASSWORD + ADMIN_PASSWORD + CREDENTIAL_KEY docker pull ghcr.io/hkxiaoyao/wbsysc:latest docker compose up -d curl http://localhost:8001/health # Expect {"status":"ok"} # Connect to the first tenant docker compose exec wbsysc python -m app.tenant_init \ --tenant-id tenant1 --corpid wwXXX --secret XXX \ --token $(openssl rand -hex 24) --contact-secret XXXX \ --modules report,approval,checkin --display "Test Client1" ``` ### Production Upgrade (migrate and then switch) Recommended execution `bash deploy/server_deploy.sh`: the script will first verify production configuration, then use the host `mysql` CLI to execute `sql/004_gateway_hardening.sql`; only after successful migration will it pull the image and start the new application. Migration defaults to connecting `127.0.0.1`, remote MySQL can be overridden using `DB_MIGRATION_HOST` environment variable or `.env` same-name item. ```bash git pull bash deploy/server_deploy.sh ``` Manual upgrade requires: "backup database → execute `004` → pull new image → start". The following non-sensitive connection parameters must be consistent with `.env`, and passwords are passed through `MYSQL_PWD` environment variable, not placed in command-line arguments: ```bash DB_MIGRATION_HOST=127.0.0.1 DB_PORT=3306 DB_USER=wbsysc_app DB_NAME=websysc read -rsp "DB_PASSWORD: " MYSQL_PWD && export MYSQL_PWD && echo mysql --protocol=TCP --host="$DB_MIGRATION_HOST" --port="$DB_PORT" \ --user="$DB_USER" "$DB_NAME" < sql/004_gateway_hardening.sql unset MYSQL_PWD docker pull ghcr.io/hkxiaoyao/wbsysc:latest docker compose up -d ``` > `004_gateway_hardening.sql` contains `DELIMITER` and stored procedure statements, which must be executed using MySQL `mysql` CLI. Migration failure should not start the new version. ### Method 2: Local Development ```bash python -m venv .venv && .venv/Scripts/activate # Windows pip install -r requirements.txt cp .env.example .env # Default mock mode python -m app.main # Start (http://localhost:8001) # Open another terminal: MCP client real protocol smoke test python tests/test_smoke_client.py ``` ## 📋 Configuration (.env) | Variable | Description | Required | |------|------|------| | `DB_HOST` | Same machine deployment fill `host.docker.internal`; cross-machine fill MySQL IP | ✓ | | `DB_PASSWORD` | MySQL `websysc` account password | ✓ | | `ADMIN_PASSWORD` | Management backend login password | ✓ | | `CREDENTIAL_KEY` | Credential encryption master key (development can be empty; production must be strong random) | Production required | | `WECOM_USE_MOCK` | `true`=desensitization mock; production must be `false` and configure tenant credentials | Production required | | `SYNC_INTERVAL_*_MIN` | Synchronization interval (report/approval/smarttable) | - | > Tenant WeCom credentials (corpid/secret) **not in .env**, written to `tenant_config` (AES encryption) through management backend or `tenant_init`. > > Production startup must set `WECOM_USE_MOCK=false`, `CREDENTIAL_KEY`, `ADMIN_PASSWORD`, and `DB_PASSWORD` simultaneously; application will refuse to start if missing or using example values. ## 🔧 MCP Tools (6) | Tool | Description | Corresponding WeCom API | |------|------|----| | `wecom_list_reports` | Report list | `oa/journal/get_record_list` | | `wecom_get_report` | Report details | `oa/journal/get_record_detail` | | `wecom_list_approvals` | Approval list | `oa/getapprovalinfo` | | `wecom_get_approval_detail` | Approval details | `oa/getapprovaldetail` | | `wecom_list_checkins` | Check-in records | `checkin/getcheckindata` | | `wecom_list_smart_table_records` | Smart table records (temporarily suspended) | `wedoc/smartsheet/get_records` | ### WorkBuddy Connection Configuration ```json { "mcpServers": { "wecom-gateway": { "type": "http", "url": "https://<your-server>/mcp", "headers": { "Authorization": "Bearer ${WORKBUDDY_MCP_TOKEN}" } } } } ``` > **Proxy pit points (must read)**: httpx on Windows reads system-level proxy, localhost/intranet may be incorrectly proxied to 502. If WorkBuddy machine has system proxy, `NO_PROXY=mcp.example.com`. ## 🎛 Management Backend Access `http://<server>:8001/admin/ui/`, single-password login (`.env`'s `ADMIN_PASSWORD`). | API | Description | |-----|------| | `POST /admin/login` | Password login → session token (Cookie + Bearer dual support) | | `GET /admin/tenants` | List tenants (secret not returned in plaintext) | | `POST /admin/tenants` | Add tenant (automatically create schema) | | `PUT /admin/tenants/{id}` | Edit tenant (key empty = no change) | | `DELETE /admin/tenants/{id}` | Delete tenant configuration | | `POST /admin/tenants/{id}/sync` | Manually trigger tenant synchronization | Frontend development: `cd admin-ui && pnpm install && pnpm dev` (5178 cross-domain proxy backend); `pnpm build` produces `app/static/dist`. ## 🏢 Multi-tenancy **Physical schema isolation**: each tenant has an independent `wbd_{corpid_md5}` schema, with credentials encrypted using AES-Fernet and stored in the central database. ```bash # Connect to new tenant (automatically create schema + 5 tables + refresh cache) python -m app.tenant_init \ --tenant-id customerA --corpid wwXXX --secret XXXX \ --token $(openssl rand -hex 24) \ --modules report,approval,checkin \ --contact-secret XXXX # Optional: contact synchronization secret, automatically pull user IDs | --checkin-userids "userA" # Optional: manual fallback without contact secret ``` **Module switch**: `--modules`: optional `report`/`approval`/`checkin` combination. **Check-in user ID**: (one of two options): automatic priority (`--contact-secret` calls list_id to pull all employees, 10-minute cache), failure fallback manual configuration. **Isolation guarantee**: token → tenant → schema server-side strong binding, SQL with schema prefix to prevent connection pool competition, audit logs physically separated by tenant. ## 🔄 Synchronization Task - APScheduler starts immediate first synchronization + periodic traversal of all enabled tenants - Incremental cursor stored in each tenant's `sync_cursor`, breakpoint continuation - Thread pool execution does not block MCP event loop - Single tenant/single record failure does not interrupt overall process; `MAX_DETAIL_PER_RUN=500` to prevent explosion - Check-in pulls one person at a time + error handling: 301021 (unauthorized personnel) silently skips without affecting others ## 📦 CI/CD Push to `main` automatically triggers GitHub Actions (`.github/workflows/build-push.yml`): 1. Build multi-stage image (node constructs frontend → python runs) 2. Push to `ghcr.io/hkxiaoyao/wbsysc:latest` (commit sha + latest dual tags) 3. Server `docker pull` ready, no local Node/Python compilation required GHCR private package access: public image (GitHub package page → Package settings → Public, anonymous pull) or server `docker login ghcr.io`. ## 🔐 WeCom Access Pre-configuration (real mode must read) Before cutting `WECOM_USE_MOCK=false`, configure in WeCom management backend (see `docs/WeCom Access Configuration List.md`): | Interface class | Required configuration | Error code comparison | |--------|--------|-----------| | All | **Enterprise trusted IP whitelist** (add server public IP) | `60020` = IP not added to whitelist | | Approval/reporting | Approval/reporting application → API → callable interface application add self-built application | `301055` = unauthorized | | Check-in | Same as above + application visible range contains target employee | `301021` = personnel not in visible range | | Contact list | Contact list synchronization secret (independent of self-built application secret) | `40001` = secret error | | Smart table | Application opens document/smart table permission + real docid | `48002` = permission not opened | ⚠️ **Smart table reading temporarily suspended**: WeCom docid only obtained through API creating documents, member manual storage table cannot get docid, so reading is limited. ## 🛡 Security Red Line - Credentials (corpid/secret/DB password/ADMIN_PASSWORD) **prohibited hardcoding**, all go through `.env` or AES encryption into DB - `.env` in `.gitignore`, never submit (repository only has `.env.example`/`.env.prod.example` templates) - Before client authorization completed: only mock/desensitized data, do not save client original data for a long time, do not connect to production database - Production launch must do: DB strong password / `CREDENTIAL_KEY` strong random / WeCom secret re-sign / `ADMIN_PASSWORD` change to strong ## 📁 Project Structure ``` app/ config.py / auth.py / db.py / main.py # Configuration/authentication/data layer/entry admin.py # Management backend API crypto.py / tenant.py / tenant_init.py # Credential encryption/decryption/tenant query/access script mcp_server.py # 6 MCP tools wecom/ client.py # WeCom API client (token cache isolation dual secret) sync.py / approval_sync.py / checkin_sync.py # Three types of synchronization (segmented + cursor + deduplication) contact.py # Contact list user ID automatic pull dispatch.py # Multi-tenancy scheduling traversal mock.py # Desensitization mock data admin-ui/ # React+Vite+Ant Design management frontend sql/ # Table creation script deploy/ # nginx.conf / server_deploy.sh / wbsysc.service .github/workflows/ # CI build image docs/ # Architecture plan/WeCom access list/deployment guide tests/test_smoke_client.py # MCP client smoke test ``` ## 📚 Documents - Complete architecture plan: [`docs/PLAN-wecom-mcp-gateway.md`](docs/PLAN-wecom-mcp-gateway.md) - WeCom access configuration list: [`docs/WeCom Access Configuration List.md`](docs/WeCom Access Configuration List.md) - Deployment guide: [`docs/Deployment Guide.md`](docs/Deployment Guide.md) ## 🔧 Technology Stack - **Backend**: Python 3.11 + FastAPI + official MCP Python SDK + SQLAlchemy + APScheduler + httpx - **Frontend**: React 18 + Vite + Ant Design 5 - **Storage**: MySQL 5.7+ (multi-tenancy schema) + optional Redis (token cache) - **Deployment**: Docker + docker-compose + Nginx/HTTPS + systemd - **CI**: GitHub Actions → GHCR