# Soft1 MCP

[![CI](https://github.com/cpouldev/soft1-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/cpouldev/soft1-mcp/actions/workflows/ci.yml)
[![Python 3.13](https://img.shields.io/badge/Python-3.13-3776AB?logo=python&logoColor=white)](pyproject.toml)
[![License: FSL 1.1 ALv2](https://img.shields.io/badge/License-FSL--1.1--ALv2-5C6BC0)](LICENSE.md)

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](#what-the-server-provides)
- [Public surface](#public-surface)
- [Quick start](#quick-start)
- [Recommended analyst workflow](#recommended-analyst-workflow)
- [Results, limits, and exports](#results-limits-and-exports)
- [Configuration](#configuration)
- [Authentication and production checks](#authentication-and-production-checks)
- [Read-only and data-safety model](#read-only-and-data-safety-model)
- [Catalog and knowledge-base lifecycle](#catalog-and-knowledge-base-lifecycle)
- [Architecture](#architecture)
- [Docker](#docker)
- [Development and testing](#development-and-testing)
- [Operations and troubleshooting](#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](https://docs.astral.sh/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:

```bash
uv sync
```

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

```bash
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:

```bash
uv run python main.py
```

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

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

```json
{"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:

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

```json
{"search":"TRNDATE"}
```

```json
{"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:

```json
{
  "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`:

```json
{
  "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:

```json
{
  "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](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.

```dotenv
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:

```bash
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:

```text
<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](docs/soft1-live-verification.md).

## 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

```text
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:

```bash
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:

```bash
./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:

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

The [CI workflow](.github/workflows/ci.yml) 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:

```bash
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:

```bash
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:

```bash
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

```text
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](docs/soft1-live-verification.md). For export security and
retention details, read [docs/exports.md](docs/exports.md).