denissa4/nlsql-coder-api repository overview

⁠nlsql-coder API

Asynchronous API layer built around the nlsql-coder CLI. Supports multiple users with user-dedicated workspaces, Azure blob storage for persistence, and user-friendly downloadable links to created projects.

⁠Environment Variables

MCP OAuth tokens persist in an Azure Table on the same storage account used for blob uploads (AZURE_STORAGE_ACCOUNT_NAME), authenticated with the same user-assigned managed identity (AZURE_CLIENT_ID). The mcptokens table is created automatically if absent. No extra credentials are required beyond what blob uploads already use. | GOOGLE_OAUTH_CLIENT_ID | …apps.googleusercontent.com | No | Set (with the two below) to provision the built-in Gmail MCP for every user without an mcp.json upload. | | GOOGLE_OAUTH_PASSWORD | GOCSPX-… | No | The Google OAuth client secret for the built-in Gmail MCP. | | GOOGLE_OAUTH_REDIRECT_URI | https://your-api/mcp/oauth/callback | No | Redirect URI for the built-in Gmail MCP; must be registered in the Google client. | | GOOGLE_OAUTH_SCOPE | https://www.googleapis.com/auth/gmail.readonly | No | Optional scope override for the built-in Gmail MCP (the server otherwise dictates scopes). | | GMAIL_MCP_URL | https://gmailmcp.googleapis.com/mcp/v1 | No | Optional override for the built-in Gmail MCP endpoint (defaults to the official URL). |

⁠MCP servers requiring OAuth

Users can add an MCP server that requires OAuth (e.g. Gmail) by uploading an mcp.json. Because DCR is not supported by most providers, register an OAuth app per server and put its credentials in the server's oauth block:

{
  "mcpServers": {
    "gmail": {
      "url": "https://gmail-mcp.example.com/mcp",
      "oauth": {
        "client_id": "<app client id>",
        "client_secret": "<app client secret>",
        "scope": "https://www.googleapis.com/auth/gmail.readonly",
        "redirect_uri": "https://your-api-url/mcp/oauth/callback"
      }
    }
  }
}

redirect_uri may be omitted per-server if MCP_OAUTH_REDIRECT_URI is set globally; it must match what is registered in the provider's console. client_secret and scope are optional (omit client_secret for public/PKCE clients).

⁠Built-in Gmail for all users (no upload)

Setting GOOGLE_OAUTH_CLIENT_ID + GOOGLE_OAUTH_PASSWORD (client secret) + GOOGLE_OAUTH_REDIRECT_URI provisions a built-in gmail server for every session — users don't upload an mcp.json. Tokens are stored per user_id, so only the server config is shared, never credentials. An uploaded mcp.json still works and its own gmail entry (or any other server) overrides the built-in.

Automatic link at session start. When a session starts, any MCP server that still needs OAuth has its authorization link returned in the POST /session/start response (the session's first message) — no /mcp reload needed. The handshake then completes in the background: the user can keep chatting with other tools, and the Gmail tools activate automatically once they click the link and authorize. The authorization code is delivered to the session out-of-band (a per-user file under the session dir written by GET /mcp/oauth/callback), never over the chat/PTY channel. Uploading an mcp.json mid-session via POST /session/mcp still surfaces links the same way, streamed in that call's response.

Refresh tokens / offline access. For Google authorization endpoints, the flow adds access_type=offline and prompt=consent automatically so Google returns a refresh token (sessions then survive past the ~1h access-token lifetime without re-authorizing). For other providers, or to override, add an authorization_params object to the oauth block — its keys are merged into the authorization URL, e.g.:

"oauth": {
  "client_id": "…",
  "authorization_params": { "access_type": "offline", "prompt": "consent" }
}

⁠Flow

1. AI-Employee calls POST /session/mcp with {user_id, mcp_bytes} (base64 mcp.json). The server writes the file, reloads MCP servers, and streams the reply.
2. Any server needing OAuth streams back a clickable authorization link.
3. The user clicks it, authorizes with the provider, and is redirected to GET /mcp/oauth/callback. The token is exchanged and stored in Azure Table (keyed by user_id), so it is reused in future sessions with no re-authorization.
4. The stream then completes with a success/summary line and the tool becomes callable via POST /session/prompt.