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

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

Tool	Inputs	Purpose
kb_search	query, source=null, limit=10	Search bundled guidance and reflected schema. Queries are limited to 500 characters; source is cookbook, schema, or all; limit is 1–20.
kb_get	id, offset=0	Read the complete entry behind a search hit. IDs are limited to 500 characters. Continue with the returned next_offset when content is paged.
s1_tables	search=null	Find tables by schema/name, column name, or semantic summary. Returns at most 200 matches.
s1_describe	table, sample=false, column_offset=0, outbound_fk_offset=0, inbound_fk_offset=0	Describe columns, primary key, outbound/inbound foreign keys, and semantic confidence. sample=true adds at most three live rows.
s1_status	none	Report live connectivity, SQL Server version/freshness, safety policy, caps, and cached catalog state without triggering schema reflection.
s1_sql	query, row_limit=200, mode="rows", export_format="csv"	Run one guarded T-SQL read. Rows mode returns an envelope; export mode returns a signed CSV/XLSX URL.

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

Prompts

Prompt	Arguments	Guidance
sales_analysis	date_from, date_to	Net sales, dimensions, sign handling, and reconciliation.
customer_ledger	customer	Customer identity, balance, and activity at a verified grain.
inventory_stock	none	Quantity, valuation, warehouse grain, and movement dates.
aging_balances	none	Receivables/payables and evidence-based aging buckets.
pl_financials	date_from, date_to	General-ledger profit and loss with explicit account and sign rules.

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

Route	Authentication	Contract
/mcp	Google OAuth plus email allowlist	FastMCP Streamable HTTP endpoint.
/healthz	None	Shallow process liveness only; performs no Redis or database I/O and returns {"status":"ok"} with Cache-Control: no-store.
/exports/{token}	Signed capability token	Downloads one verified export. The token is the authorization boundary.

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

Boundary	Limit
Default / maximum SQL rows-mode limit	200 / 2,000 rows
Serialized MCP envelope	25,000 characters
s1_tables matches	200 tables
s1_describe live samples	3 rows
kb_search default / maximum	10 / 20 hits
SQL login / pool acquisition / query timeout	5 / 15 / 60 seconds
Soft1 connection pool	3 connections
CSV export	500,000 rows
XLSX export	100,000 rows
Export cumulative input guard	128 MiB
Signed download lifetime	24 hours
Export artifact retention	7 days
Catalog TTL / failed-refresh backoff	3 days / 5 minutes

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

Variable	Default	Purpose
APP_ENV	development	Set to production to enable strict startup checks and JSON logs.
LOG_LEVEL	DEBUG	Loguru stderr threshold.
SENTRY_DSN	empty	Enables Sentry/GlitchTip only when APP_ENV=production.
MCP_BASE_URL	http://localhost:6097	Public service origin used for OAuth callbacks and export URLs. Routes such as /mcp are appended to it.
MCP_ALLOWED_EMAILS	dev	Comma-separated, case-insensitive Google email allowlist.
MCP_GOOGLE_CLIENT_ID	dev	Google OAuth client ID.
MCP_GOOGLE_CLIENT_SECRET	dev	Google OAuth client secret.
MCP_JWT_SIGNING_KEY	dev	Persistent Fernet-formatted key used by FastMCP's token issuer.
MCP_STORAGE_ENCRYPTION_KEY	dev	Persistent Fernet key for encrypted OAuth state and export-token signing.
FASTMCP_HTTP_ALLOWED_HOSTS	local wildcard tuple	Accepted HTTP Host values. Supply a JSON array in environment files.
FASTMCP_HTTP_ALLOWED_ORIGINS	local wildcard tuple	Accepted HTTP Origin values. Supply a JSON array in environment files.

Soft1 SQL Server

Variable	Default	Purpose
SOFT1_DB_HOST	localhost	SQL Server host.
SOFT1_DB_PORT	1433	SQL Server TCP port.
SOFT1_DB_INSTANCE	empty	Optional named instance.
SOFT1_DB_DATABASE	dev	Soft1 database name.
SOFT1_DB_USER	dev	Dedicated database login. Use least privilege.
SOFT1_DB_PASSWORD	dev	Database password.
SOFT1_DB_TRUST_SERVER_CERTIFICATE	false	TLS certificate override; enable only after deployment-specific review.
SOFT1_DB_VARCHAR_ENCODING	cp1253	Decoder for legacy char/varchar; nvarchar uses Unicode.

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

Variable	Default	Purpose
REDIS_HOST	localhost	Redis host for OAuth state and catalog metadata.
REDIS_PORT	6379	Redis port.
MCP_REDIS_DB	0	Explicit Redis logical database.
EXPORT_DIR	/data/exports	Writable directory for requested CSV/XLSX artifacts.

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:

Confidence	Evidence
documented	Curated Soft1 vocabulary bundled with the service.
structural	Reflected primary-key or foreign-key evidence.
inferred	Table or column naming patterns; a lead, not a verified contract.
unknown	No authoritative or reliable structural meaning is available.

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:

Layer	Responsibility
src/tools/	MCP validation, result contracts, audit orchestration, and error translation. No direct I/O.
src/catalog/, src/kb/	Immutable schema state, lifecycle coordination, semantics, corpus, and search domain logic.
src/adapters/	ODBC, Redis, DuckDB, export storage, signing, and other external I/O.

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

Symptom	Likely cause and action
/healthz is healthy but schema/query tools fail	This is expected during a dependency outage. Call s1_status, then verify SQL Server network, TLS, and credentials.
Production exits before catalog loading	Replace placeholders, use public HTTPS, validate both Fernet keys, and make Host/Origin allowlists match MCP_BASE_URL exactly.
HTTP 421 or 403 at /mcp	The reverse proxy sent an unapproved Host or Origin. Preserve the public values and correct the two FastMCP allowlists.
OAuth succeeds once but fails after restart	Redis state is unavailable or key material changed. Restore Redis and the original persistent keys; rotating the storage key also invalidates export links.
Catalog is unavailable or stale	Restore Redis or Soft1 connectivity. Memory remains authoritative, failed automatic refreshes back off, and the next eligible catalog request retries.
Greek varchar text decodes incorrectly	Verify the installation's code page and set SOFT1_DB_VARCHAR_ENCODING; do not change Unicode nvarchar handling.
s1_sql rejects a query before execution	Submit exactly one row-producing SELECT; remove mutation, procedure, linked-server, and external-data constructs.
A query reaches the 60-second timeout	Narrow date ranges and WHERE filters. Export mode changes delivery, not the database timeout.
kb_search reports DuckDB FTS unavailable	Install the local fts extension once or use the production image.
XLSX export fails	Install/load the local excel extension, reduce rows/columns, or use CSV.
Export storage is unavailable	Make EXPORT_DIR writable by the runtime identity. Changing SQL pagination cannot fix a storage permission error.

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.