Soft1 MCP

Soft1 MCP is a standalone Python 3.13 MCP server for read-only analysis of one Soft1 ERP SQL Server database. It combines guarded T-SQL, live schema discovery, Greek-aware knowledge search, signed CSV/XLSX exports, Google OAuth, and bounded MCP responses without introducing an application database.

The service is company-agnostic. It discovers each installation's schema and leaves company IDs, document codes, fiscal rules, sign conventions, and custom CCC* objects to deployment-local evidence.

Contents

• What the server provides

• Public surface

• Quick start

• Recommended analyst workflow

• Results, limits, and exports

• Configuration

• Authentication and production checks

• Read-only and data-safety model

• Catalog and knowledge-base lifecycle

• Architecture

• Docker

• Development and testing

• Operations and troubleshooting

Related MCP server: Warp SQL Server MCP

What the server provides

• One guarded query path for SELECT and WITH ... SELECT in the T-SQL dialect.

• Reflected tables, columns, primary keys, foreign keys, and approximate row counts.

• Confidence-labelled Soft1 vocabulary that distinguishes documented, structural, inferred, and unknown meanings.

• BM25 knowledge search with Greek stemming and accent stripping across bundled guidance and the current reflected schema.

• A uniform 25,000-character result envelope for interactive work.

• Bounded CSV and XLSX exports with expiring, HMAC-signed download links.

• Google OAuth with DCR/PKCE, a case-insensitive email allowlist, and encrypted Redis state.

• Structured query audits, production JSON logs, optional Sentry-compatible observability, and a shallow liveness route.

Soft1 remains the only business database. Redis stores OAuth state and schema metadata. DuckDB runs in process for full-text search and XLSX generation. Neither service stores business rows.

Public surface

Tools

All six tools are marked read-only, non-destructive, and idempotent in their MCP annotations.

Prompts

Each prompt directs the client to search the knowledge base, inspect the current schema, verify installation-specific rules, run a narrow query, and reconcile important totals.

HTTP routes

Quick start

Requirements

• Python 3.13

• uv

• Microsoft ODBC Driver 18 for SQL Server

• A reachable Soft1 SQL Server database for live schema and query tools

• Redis and a Google OAuth client for authenticated MCP use

Install the Python environment:

uv sync

DuckDB needs its fts and excel extensions once on a local workstation. The Docker image pre-bakes both extensions.

uv run python -c "import duckdb; c=duckdb.connect(); c.execute('INSTALL fts'); c.execute('INSTALL excel'); c.close()"

The development defaults let the process boot without credentials. An unreachable Soft1 database or Redis does not prevent /healthz from becoming available, but placeholder OAuth and database settings do not provide a usable analytics endpoint.

Start the server:

uv run python main.py

It listens on 0.0.0.0:6097. Check liveness separately from dependency readiness:

curl -fsS http://127.0.0.1:6097/healthz

{"status":"ok"}

After configuring Google OAuth, Redis, and Soft1, connect an MCP client to http://localhost:6097/mcp. Use s1_status inside the authenticated MCP session to inspect live database and catalog health.

Recommended analyst workflow

Soft1 schemas vary by version, enabled modules, localization, and customization. Follow this order for every business question:

1. Call kb_search with the business question, in Greek or English.

2. Call kb_get for the most relevant hit and follow any next_offset continuation.

3. Use s1_tables to find current table and column names.

4. Use s1_describe to verify types, keys, relationships, and semantic confidence.

5. Resolve company, branch, fiscal period, document type, status, currency, and sign conventions from current evidence or operator input.

6. Run a small s1_sql query and validate its grain and totals.

7. Use mode="export" only when the focused result belongs in a file.

Example discovery calls:

{"query":"πωλήσεις ανά μήνα και πελάτη","source":"all","limit":10}

{"search":"TRNDATE"}

{"table":"dbo.<confirmed_table>","sample":false}

Replace every placeholder before submitting SQL. Never copy numeric IDs, document-series codes, or joins from another Soft1 installation.

Results, limits, and exports

Interactive envelopes

Rows-mode tools return a common envelope. Tool-specific metadata may appear before these required keys:

{
  "columns": ["column_a", "column_b"],
  "rows": [["value", 42]],
  "rowcount": 1,
  "truncated": false,
  "duration_ms": 8,
  "db": "s1",
  "note": "Optional next-step guidance"
}

rows is row-major and follows columns order. rowcount describes the result before the 25,000-character response budget removes rows; it is not an uncapped SQL count. When the envelope does not fit, the server keeps the largest complete-row prefix and sets truncated=true.

The note distinguishes a confirmed response trim from a server row cap whose next row was deliberately not probed. A caller-supplied concrete TOP below the server limit remains intact and does not, by itself, imply truncation.

Important limits

Export mode

Request an export through s1_sql:

{
  "query": "SELECT <confirmed_columns> FROM <confirmed_schema>.<confirmed_table>",
  "mode": "export",
  "export_format": "csv"
}

Export mode ignores the rows-mode display limit and applies the selected format's hard cap. The response bypasses the MCP envelope:

{
  "url": "https://mcp.example.com/exports/<signed-token>",
  "rows": 12500,
  "size": 482901,
  "expires": "2026-08-03T12:00:00Z"
}

CSV streams in batches and includes a UTF-8 BOM. XLSX materializes a bounded Arrow table and uses DuckDB's excel extension. Both formats fetch one sentinel row beyond their public cap so the writer can reject overflow instead of silently truncating a file.

Artifacts are published atomically with mode 0600. Download tokens expire after 24 hours; a bounded sweep removes service-owned artifacts older than seven days at startup and before each export. The download route rejects malformed tokens, traversal, symlinks, and paths outside EXPORT_DIR. See docs/exports.md for the complete export contract.

Configuration

AppConfig loads environment variables and an untracked .env in the repository root. The defaults in mise.toml mirror development settings. Environment variables take precedence.

Runtime and authentication

Soft1 SQL Server

The service constructs an ODBC Driver 18 connection string with Encrypt=yes, ApplicationIntent=ReadOnly, and application name Soft1 MCP. The optional SOFT1_MCP_DSN override is validated attribute by attribute: it cannot change the configured driver, route, database, user, encryption, certificate, or read-intent policy. Only its password is caller-supplied.

Redis and export storage

Redis is required for durable authenticated operation. Catalog persistence treats Redis as a best-effort cache: Redis failure does not prevent a live database reflection or process startup.

Production .env template

The values wrapped in <...> are intentionally invalid. Replace all of them and keep the file out of version control.

APP_ENV=production
LOG_LEVEL=INFO
SENTRY_DSN=

MCP_BASE_URL=https://mcp.example.com
MCP_ALLOWED_EMAILS=analyst@example.com,operator@example.com
MCP_GOOGLE_CLIENT_ID=<replace-google-client-id>
MCP_GOOGLE_CLIENT_SECRET=<replace-google-client-secret>
MCP_JWT_SIGNING_KEY=<replace-fernet-key>
MCP_STORAGE_ENCRYPTION_KEY=<replace-second-fernet-key>

SOFT1_DB_HOST=sql.example.internal
SOFT1_DB_PORT=1433
SOFT1_DB_INSTANCE=
SOFT1_DB_DATABASE=<replace-database>
SOFT1_DB_USER=<replace-read-only-user>
SOFT1_DB_PASSWORD=<replace-database-password>
SOFT1_DB_TRUST_SERVER_CERTIFICATE=false
SOFT1_DB_VARCHAR_ENCODING=cp1253

REDIS_HOST=redis
REDIS_PORT=6379
MCP_REDIS_DB=0
EXPORT_DIR=/data/exports

FASTMCP_HTTP_ALLOWED_HOSTS=["mcp.example.com"]
FASTMCP_HTTP_ALLOWED_ORIGINS=["https://mcp.example.com"]

Generate the two keys separately and store them in the deployment's secret manager:

uv run python -c 'from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())'

Development auth replaces placeholder keys with process-stable, throwaway Fernet keys. Persisted OAuth state therefore does not survive a process restart. Export signing uses the configured storage key directly, so keep development exports disposable. Production requires persistent, valid Fernet keys.

Authentication and production checks

Google is the upstream identity provider. FastMCP supplies dynamic client registration and PKCE. The server requests openid and the Google email scope, then a global middleware admits only emails in MCP_ALLOWED_EMAILS. Missing tokens, missing or non-string email claims, and unknown emails fail closed.

Configure this Google OAuth redirect URI:

<MCP_BASE_URL>/auth/callback

The server accepts MCP client callbacks for Claude's hosted callback and localhost clients. A different remote client callback requires a deliberate code/configuration change; it is not accepted implicitly.

With APP_ENV=production, startup rejects:

• placeholder values in the public URL, email allowlist, Google credentials, both keys, and the four required Soft1 connection fields;

• a non-HTTPS, localhost, loopback, or unspecified MCP_BASE_URL;

• malformed or duplicate allowlisted email addresses;

• invalid Fernet key material;

• Host and Origin allowlists that differ from the public URL.

Production requires exactly one allowed host—the MCP_BASE_URL netloc—and exactly one allowed origin—its scheme and netloc. Configuration validation runs before catalog reflection, so an invalid public/auth boundary aborts startup without opening a database connection.

Read-only and data-safety model

Database grants differ across Soft1 installations. ApplicationIntent=ReadOnly is a routing hint, not an authorization boundary. Give the service a database-enforced read-only login, then retain the application guard as mandatory defense in depth.

Every s1_sql request follows this sequence:

1. Resolve and normalize the authenticated email.

2. Check the Soft1 live-query safety policy before opening a connection.

3. Validate mode, format, and row limits.

4. Parse exactly one root SELECT in the tsql dialect and inject or tighten TOP.

5. Repeat the guard at the adapter boundary, borrow a bounded pooled connection, and execute with a 60-second timeout.

6. Normalize values and build a bounded envelope or export artifact.

7. Emit a structured success or failure audit.

The guard rejects:

• stacked statements and every non-SELECT root;

• DML, DDL, procedures, transactions, and nested mutations in CTEs or subqueries;

• SELECT INTO and NEXT VALUE FOR;

• OPENDATASOURCE, OPENQUERY, and OPENROWSET;

• four-part linked-server references.

The guard does not preserve percentage, WITH TIES, parameterized, or oversized outer row bounds; it replaces them with the service cap.

The adapter applies the cap again before execution. This duplicate validation is intentional and idempotent.

Result rows never enter Redis, the knowledge index, or audit logs. Query audits contain the caller email, submitted or executed SQL text, duration, row count, truncation flag, and error status. Avoid embedding unnecessary sensitive literals in SQL because the query text is operational audit data.

Explicitly requested exports are the only business-row artifacts written to local storage. Keep EXPORT_DIR private, encrypted at the host/storage layer when required, and outside backups that are not approved for ERP data.

Before production use, complete the deployment-specific Soft1 live-verification checklist.

Catalog and knowledge-base lifecycle

Schema catalog

Startup registers the MCP surface first, then tries to load the Soft1 catalog. It checks Redis only when memory is empty; otherwise in-memory state is authoritative. A cache miss triggers two batched, metadata-only SQL Server queries: one for tables/columns/keys/count estimates and one for foreign keys.

The resulting snapshot is immutable. Concurrent requests coalesce onto one reflection. A Soft1 or Redis outage does not abort startup, and a failed refresh can continue serving a stale in-memory snapshot. s1_status reads catalog state without causing a reflection, then performs its own bounded version/freshness probe and reports failure as data.

Redis stores a bounded compressed schema snapshot under an isolated key. It never stores sampled rows. The snapshot keeps its original reflected_at, so the three-day TTL survives restarts.

Knowledge base

The corpus combines:

• src/kb/routing.md;

• src/kb/soft1-cookbook.md;

• src/kb/soft1-table-notes.md;

• table and column entries derived from the current catalog.

kb_search assembles the selected sources on each call. DuckDB keeps a process-local generation and rebuilds the physical FTS index only when the corpus changes. The index searches titles and search text with Greek stemming and accent stripping; kb_get returns lossless content pages.

Bundled-document IDs are positional and may change when Markdown sections change. Reflected-schema IDs are hashes of their identifiers and remain stable while the identifier remains stable.

Semantic confidence

Soft1 names suggest useful vocabulary, but names alone cannot prove business grain or arithmetic. Schema responses label every semantic note:

Confirm inferred and unknown meanings with current metadata, narrow samples where approved, and operator knowledge before using them in business calculations.

Architecture

MCP client
   │  Streamable HTTP /mcp
   ▼
FastMCP + Google OAuth + email middleware ───── Redis
   │                                           OAuth state (encrypted)
   │                                           schema metadata only
   ├── knowledge tools ── DuckDB FTS ── bundled docs + catalog snapshot
   │
   ├── schema tools ───── in-memory catalog ── metadata reflection ─┐
   │                                                               │
   └── s1_sql ── SQL guard ── row cap ── ODBC pool ────────────────┤
                                                                   ▼
                                                        Soft1 SQL Server
   ├── rows mode ── 25,000-character MCP envelope
   └── export mode ── private artifact ── signed /exports/{token}

The code follows three main layers:

main.py initializes observability, builds the server, initializes catalogs, and serves HTTP. src/server.py discovers any module-level register(mcp) function in src/tools/; adding a tool namespace does not require editing the composition root. Import or registration errors abort boot instead of exposing a partial tool surface.

Docker

Build and run the production image:

docker build --check .
docker build -t soft1-mcp .
docker run --rm \
  --name soft1-mcp \
  --env-file .env \
  --publish 6097:6097 \
  --volume soft1-mcp-exports:/data/exports \
  soft1-mcp

The image:

• installs Microsoft ODBC Driver 18;

• installs production dependencies from the frozen uv.lock;

• pre-bakes DuckDB's fts and excel extensions for network-free runtime loading;

• runs as UID/GID 10001:10001;

• keeps /app root-owned and non-writable;

• keeps /data/exports writable and excludes development dependencies.

A bind-mounted export directory must be writable by UID/GID 10001. Persist the directory only as long as operational export retention requires.

Run the full container gate with:

./scripts/smoke-docker.sh

The script builds a clean image, verifies ODBC Driver 18, loads DuckDB extensions with networking disabled, checks the non-root filesystem boundary, starts the server, and waits for Docker health. Pass linux/amd64 or linux/arm64 to test an explicit platform.

Development and testing

Run the standard checks from the repository root:

uv run ruff check .
uv run ruff format --check .
uv lock --check
uv run pytest

The CI workflow runs this gate on pull requests and pushes to main. It uses the locked dependencies and installs the DuckDB extensions required by the complete suite.

The default test run uses FastMCP's in-memory transport and touches no live database, Redis, or network. Integration tests are collected by default and skip themselves when their environment prerequisites are absent.

Useful focused commands:

uv run pytest tests/test_sql_guard.py
uv run pytest tests/test_config.py::test_valid_production_configuration_is_accepted
uv run pytest -k "placeholder or allowlist"

Run live integration tests only against approved isolated infrastructure:

SOFT1_MCP_DSN='Driver={ODBC Driver 18 for SQL Server};Server=...' \
MCP_REDIS_TEST_URL='redis://localhost:6379/9' \
uv run pytest tests/integration

Do not commit the DSN, database names, outputs, or deployment evidence. The live tests validate permissions, Greek text encoding, connection reuse, timeout behavior, executed row caps, and Redis catalog restoration.

Cheap Docker and shell pre-checks:

docker build --check .
bash -n scripts/smoke-docker.sh

Operations and troubleshooting

Startup and observability

The startup order is observability → production configuration gate → server/tool registration → catalog load → HTTP serve. An invalid production boundary aborts startup. An unavailable Soft1 catalog degrades startup and still allows the process to serve liveness and status responses.

Development logs are human-readable. Production logs are structured JSON. When SENTRY_DSN is set in production, the server initializes the Sentry SDK with default PII collection disabled.

/healthz proves only that the HTTP process responds. It deliberately does not prove OAuth, Redis, DuckDB extensions, export storage, or Soft1 connectivity. Use s1_status and deployment monitoring for those checks.

Common failures

Project layout

main.py                    HTTP entrypoint
config.py                  environment model and production gate
src/server.py              FastMCP, Google OAuth, middleware, discovery
src/sqlguard.py            T-SQL validation and TOP injection
src/tools/                 six tools, five prompts, and custom routes
src/catalog/               reflected schema model and lifecycle
src/kb/                    bundled guidance, semantics, and corpus logic
src/adapters/              ODBC, Redis, DuckDB, and export I/O
docs/                      export and live-verification runbooks
tests/                     isolated unit/contract tests and live integrations
scripts/                   Docker and runtime smoke checks

For deployment-specific checks, read docs/soft1-live-verification.md. For export security and retention details, read docs/exports.md.