Bolthouse Agriculture SQL

This project deploys a read-only Model Context Protocol server for the Bolthouse_Ag_AI SQL Server database using the sqlprd_ag_ai login.

It is based on the existing MCP4 behavior, adapted for Railway:

• Streamable HTTP MCP endpoint at /mcp
• FastMCP 3 with tested, pinned top-level dependencies
• Bearer-token authentication
• Read-only inline and paged SQL tools
• Background CSV or CSV.GZ exports
• Agent-accessible export-status tool
• Temporary signed download URLs
• Railway dynamic PORT support
• Optional Railway Volume persistence
• No credentials stored in source code

MCP tools

Tool	Purpose
execute_sql_query	Runs a read-only query with capped inline results
execute_sql_query_paged	Returns a requested page; requires ORDER BY
submit_sql_export	Starts a background CSV/CSV.GZ export
get_sql_export_status	Polls an export job and returns its download URL

The expected agent flow for a large download is:

1. Call submit_sql_export.
2. Save the returned job_id.
3. Call get_sql_export_status until status is completed.
4. Download the returned result.download_url.

Before deployment

1. Confirm SQL Server connectivity

Railway must be able to reach the SQL Server host on TCP port 1433. An internal-only IP such as 10.x.x.x is not normally reachable from Railway unless your organization provides a VPN, tunnel, or other private-network path. If SQL Server is exposed through a public endpoint, restrict its firewall as tightly as your network design permits.

The sqlprd_ag_ai login should have read access only to Bolthouse_Ag_AI. SQL Server permissions are the primary security boundary; the application also rejects non-read-only SQL.

2. Optional audit table

By default, query metadata is written to Railway logs without recording the SQL text. To write full audit records into SQL Server:

1. Run sql/create_audit_table.sql.
2. Set AUDIT_TABLE=dbo.MCP_Query_Audit_AG_Railway.

GitHub deployment

1. Create a new private GitHub repository.

2. Copy all files in this folder into the repository root.

3. Commit and push:

   git init
   git add .
   git commit -m "Add Bolthouse Agriculture Railway MCP"
   git branch -M main
   git remote add origin https://github.com/YOUR-ORG/YOUR-REPO.git
   git push -u origin main
   

Never commit .env, SQL passwords, or API tokens.

Railway deployment

1. In Railway, choose New Project → Deploy from GitHub repo.

2. Select the private repository.

3. Railway detects the root Dockerfile.

4. Add these service variables:

   API_TOKEN=<long-random-secret>
   DB_SERVER=<SQL Server hostname or reachable IP>
   DB_PORT=1433
   DB_NAME=Bolthouse_Ag_AI
   DB_USER=sqlprd_ag_ai
   DB_PASS=<SQL password>
   DB_DRIVER=ODBC Driver 18 for SQL Server
   DB_ENCRYPT=yes
   DB_TRUST_SERVER_CERTIFICATE=yes
   SERVICE_NAME=Bolthouse Agriculture AI Read Only MCP
   

5. Deploy the service.

6. Open Settings → Networking → Generate Domain.

7. Copy the generated HTTPS domain and add:

   PUBLIC_BASE_URL=https://YOUR-SERVICE.up.railway.app
   

8. Redeploy after adding PUBLIC_BASE_URL.

Your MCP URL is:

https://YOUR-SERVICE.up.railway.app/mcp

Use the value of API_TOKEN as the bearer token in the MCP registration.

Persistent CSV downloads

Railway container storage is ephemeral. Exports can disappear during a redeploy or restart unless a Railway Volume is attached.

Recommended setup:

1. Add a Railway Volume to this service.
2. Set its mount path to /data.
3. Do not set EXPORT_DIR; the application automatically detects RAILWAY_VOLUME_MOUNT_PATH and stores exports under /data/exports.
4. Keep the service at one replica because export job state is held in the running process.

Completed export files and the export registry persist on the volume. Active background jobs do not survive a restart and must be submitted again.

Verification

Health check:

curl https://YOUR-SERVICE.up.railway.app/health

Inline query:

curl -X POST \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query":"SELECT DB_NAME() AS database_name"}' \
  https://YOUR-SERVICE.up.railway.app/query

Submit an export:

curl -X POST \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query":"SELECT TOP (1000) * FROM dbo.YourTable","gzip_output":true}' \
  https://YOUR-SERVICE.up.railway.app/exports

Check the job:

curl \
  -H "Authorization: Bearer YOUR_API_TOKEN" \
  https://YOUR-SERVICE.up.railway.app/jobs/JOB_ID

Operational notes

• Use exactly one Railway replica for this implementation.
• Signed download links expire after SIGNED_URL_TTL_SECONDS.
• A new signed URL is generated whenever job status is requested.
• MAX_INLINE_ROWS protects MCP responses from oversized JSON payloads.
• CSV.GZ is recommended for large exports.
• The /health route is public but reveals no credentials.
• Download routes use signed URLs and do not require the bearer header.