> ## Documentation Index
> Fetch the complete documentation index at: https://si.easyaf.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Native Npgsql authenticated task store. Use this instead of EF when you want LISTEN/NOTIFY fan-out without a DbContext.

`McpEssentials.PostgreSQL` is the authenticated task store for hosts that already talk to Postgres through Npgsql or Dapper. It subclasses [`NotifyingMcpTaskStoreBase`](https://si.easyaf.dev/mcpessentials/api-reference/CloudNimble/McpEssentials/Core/Tasks/NotifyingMcpTaskStoreBase) and persists [`StoredMcpTask`](https://si.easyaf.dev/mcpessentials/api-reference/CloudNimble/McpEssentials/Core/Tasks/StoredMcpTask) as one `McpTasks` table.

Prefer this over [Entity Framework Core](https://si.easyaf.dev/mcpessentials/guides/stores/entity-framework-core.md) with `UseNpgsql` when there is no `DbContext` to hang `AddMcpTasksTable` on, or when several MCP nodes wait on `ElicitAsync` and you want Postgres itself to wake the originating process. Redis pub/sub is the other native channel.

> [card] No EF model
>
> The host already uses Npgsql or Dapper. There is no `DbContext` to hang `AddMcpTasksTable` on.

> [card] LISTEN / NOTIFY
>
> Postgres itself wakes `ElicitAsync` waiters. Echoes of this process are dropped.

> [card] RowVersion CAS
>
> Integer `RowVersion` on `public.McpTasks`. `INSERT` is committed before create returns.

The host owns [`NpgsqlDataSource`](https://www.npgsql.org/doc/basic-usage.html). This package ships the store, the table script, and registration. Register the data source as a singleton.

> [!TIP]
>
> [Entity Framework Core](https://si.easyaf.dev/mcpessentials/guides/stores/entity-framework-core.md) already talks to PostgreSQL (`UseNpgsql`). Stay there when the host has a `DbContext` and migrations. EF + Npgsql still needs an optional [`IMcpTaskInputSignalBus`](https://si.easyaf.dev/mcpessentials/api-reference/CloudNimble/McpEssentials/Core/Tasks/IMcpTaskInputSignalBus). Native Postgres **is** that bus.

## Install

**1. Add the package**

```bash theme={"dark"}
dotnet add package McpEssentials.PostgreSQL
```

**2. Register the data source**

```csharp theme={"dark"}
builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(connectionString));
```

## Wire the store

Same identity factory as [Authenticated tasks](https://si.easyaf.dev/mcpessentials/guides/authenticated-tasks.md). PostgreSQL has no generic context type, so the MCP builder method is [`WithPostgreSqlTasks`](https://si.easyaf.dev/mcpessentials/api-reference/Microsoft/Extensions/DependencyInjection/IMcpServerBuilder) rather than `WithTasksPlus<TContext>`.

> [tab] HTTP
>
> ```csharp theme={"dark"}
> builder.Services.AddHttpContextAccessor();
> builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(connectionString));
>
> builder.Services.AddMcpServer()
>     .WithHttpTransport(o => o.Stateless = true)
>     .WithTools<MyTools>()
>     .WithPostgreSqlTasks(
>         sp => sp.GetRequiredService<IHttpContextAccessor>().HttpContext?.User,
>         o =>
>         {
>             o.DefaultTimeToLive = TimeSpan.FromMinutes(5);
>             o.Schema = "public";
>             o.TableName = "McpTasks";
>             o.ListenChannel = "mcp_essentials_tasks";
>         });
> ```

> [tab] Stdio
>
> ```csharp theme={"dark"}
> builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(connectionString));
>
> builder.Services.AddMcpServer()
>     .WithPostgreSqlTasks("local-user");
> ```

> [tab] Store only
>
> Same factory, without `IMcpServerBuilder`:
>
> ```csharp theme={"dark"}
> builder.Services.AddHttpContextAccessor();
> builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(connectionString));
>
> builder.Services.AddMcpEssentialsPostgreSqlTasks(
>     sp => sp.GetRequiredService<IHttpContextAccessor>().HttpContext?.User,
>     o => o.DefaultTimeToLive = TimeSpan.FromMinutes(5));
> ```

Schema, table, and listen-channel names must be 1–63 ASCII letters, digits, or underscores, starting with a letter or underscore. The store quotes them for DDL.

## Table and TTL

| Object | Role |
| - | - |
| `{schema}.{table}` (default `public.McpTasks`) | Canonical `StoredMcpTask` columns plus integer `RowVersion` for CAS |
| `LISTEN {channel}` (default `mcp_essentials_tasks`) | Dedicated connection; payload is task id + request id |

On first use the store creates the table when missing (`CREATE TABLE IF NOT EXISTS`) with indexes on `OwnerUserId` and `DiscardAfter`. Hosts that own migrations can create the same shape themselves.

`CreateTaskAsync` awaits `INSERT` commit so a later get on any node can see the row. Updates compare `TaskId` and `RowVersion`. A terminal owner get with `ServeAndDrop` (default) deletes the row after serving it.

`DiscardAfter` is `timestamptz`, stamped at write as `CreatedAt + 2×TimeToLive`. `AutoSweep` defaults to **true**. Sweep is `DELETE WHERE "DiscardAfter" <= now`. Call [`SweepExpiredAsync`](https://si.easyaf.dev/mcpessentials/api-reference/CloudNimble/McpEssentials/Core/Tasks/IMcpTaskStorePlus) from a hosted service or `pg_cron` when `AutoSweep` is off. Unlimited TTL (`null`) leaves the row until a terminal owner get or an explicit delete.

> [!NOTE]
>
> `LISTEN` holds one connection for the life of the store. Size the pool for ordinary CRUD plus that extra connection. Postgres does not deliver notifications on a connection that is sitting in the pool.

## Isolation

`CreateTaskAsync` stamps `OwnerUserId`. Client-facing get/cancel/resolve require that user. Server-side complete/fail/input-request skip the live-user check. Another user's task is not found. A missing row is not found; get does not distinguish expiry from isolation.

## Events

After a successful resolve, the publishing node raises `InputResponseReceived` locally, then `pg_notify` with `taskId` + newline + `requestId` on the listen channel. Other nodes `LISTEN` and raise a stub response so `ElicitAsync` waiters wake. The publisher drops its own notify echo.

> [!TIP]
>
> Postgres **is** the bus. Redis pub/sub is the other native channel on [`NotifyingMcpTaskStoreBase`](https://si.easyaf.dev/mcpessentials/api-reference/CloudNimble/McpEssentials/Core/Tasks/NotifyingMcpTaskStoreBase).

## Related

> [card] [Sample](https://github.com/CloudNimble/McpEssentials/tree/main/samples/CloudNimble.McpEssentials.Samples.PostgreSQL)
>
> Runnable host at `http://localhost:5320/`. Send `X-User-Id: alice`. Default connection string is `Host=localhost;Database=mcpessentials`.

> [card] [Stores](https://si.easyaf.dev/mcpessentials/guides/stores.md)
>
> Pick a backing store by the engine you already run.

> [card] [Entity Framework Core](https://si.easyaf.dev/mcpessentials/guides/stores/entity-framework-core.md)
>
> Stay on EF + `UseNpgsql` when the host already has a `DbContext`.

> [card] [Redis](https://si.easyaf.dev/mcpessentials/guides/stores/redis.md)
>
> The other native channel: pub/sub plus `PEXPIREAT`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.