Twenty-five years ago, SharePoint set out to help people share knowledge and work better together, a mission that today operates at extraordinary scale.

The post SharePoint at 25: How Microsoft is putting knowledge to work in the AI era appeared first on Microsoft 365 Blog.

In the previous post, you added a custom channel to the project. In this post, you’ll define custom lifecycles.

Prerequisites

• An Octopus Cloud account. If you don’t have one, you can sign up for a free trial.
• The Octopus AI Assistant Chrome extension. You can install it from the Chrome Web Store.

Creating the project

Paste the following prompt into the Octopus AI Assistant and run it:

Create a Script project called "13. Script App with Lifecycle", and then:
* Define a lifecycle called "Auto Deploy" that includes:
  * The "Development" phase with the "Development" environment as the first phase set to automatically deploy
  * The "Test" phase with the "Test" environment as the second phase
  * The "Production" phase with the "Production" environment as the third phase
* Define a channel to the project called "Application" that uses the "Auto Deploy" lifecycle, and make this the default channel

You can now create a release and see it automatically deployed to the Development environment as part of the Auto Deploy lifecycle. This is because the first phase of the lifecycle is set to deploy automatically.

What just happened?

You created a sample project with:

• A custom lifecycle to automatically deploy to the Development environment when a release is created, followed by the Test and Production environments.
• A new channel called Application using the Auto Deploy lifecycle as the default channel for the project.

What’s next?

The next step is to create a mock Kubernetes deployment project.

Did you know? The async and await keywords in C# were introduced in C# 5.0 (2012) to simplify asynchronous programming. Under the hood, the compiler uses a state machine to transform your asynchronous code into manageable tasks. As a developer, you never need to worry about that complexity.

Welcome to dotInsights by JetBrains! This newsletter is the home for recent .NET and software development related information.

🔗 Links

Here’s the latest from the developer community.

• The Skill That Separates Good Developers from GREAT ONES 🎥 – Emily Bache
• Predicting the Next Edit in JetBrains IDEs 🎥 – Michelle Frost
• You’re Refactoring When You Should Be Deleting 🎥 – Gui Ferreira
• Async Await Just Got A Massive Improvement in .NET 🎥 – Nick Chapsas
• Simplifying Grid Layout in .NET MAUI Using Extension Methods – Leomaris Reyes
• Why Small Changes Turn Into Big Refactors – CodeOpinion by Derek Comartin
• Lease Pattern in .NET: A Lock With an Expiration Date That Saves Your Data – Chris Woodruff
• An ode to “Slowly” handcrafted code – Urs Enzler
• Creating standard and “observable” instruments – Andrew Lock
• Announcing the Duende IdentityServer4 Migration Analysis Tool – Khalid Abuhakmeh & Maarten Balliauw
• Encrypting Properties with System.Text.Json and a TypeInfoResolver Modifier (Part 2) and Encrypting Properties with System.Text.Json and a TypeInfoResolver Modifier (Part 1) – Steve Gordon
• Introducing MoreSpeakers.com and The Technology Behind MoreSpeakers.com – Joseph Guadagno
• Writing a .NET Garbage Collector in C#  – Part 7: Marking handles – Kevin Gosse
• A minimal way to integrate Aspire into your existing project – Tim Deschryver
• WinUI Tips & Tricks for WinForms Developers – Greg Lutz
• AI-Powered Smart TextArea for ASP.NET Core: Smarter Typing with Intelligent Autocompletion – Arun Kumar Ragu
• Building a Greenfield System with the Critter Stack – Jeremy D. Miller
• Are exceptions exposing vulnerabilities in your .NET App? – David Grace
• Use client assertions in ASP.NET Core using OpenID Connect, OAuth DPoP and OAuth PAR – Damien Bowden
• I Started Programming When I Was 7. I’m 50 Now, and the Thing I Loved Has Changed – James Randall
• Public Speaking at Tech Events 101: Being Uncomfortable Is Worth It – Lou Creemers
• Ralph Wiggum Explained: Stop Telling AI What You Want — Tell It What Blocks You – Matt Mattei
• Implementing strongly-typed IDs in .NET for safer domain models – Ali Hamza Ansari
• Automatic Service Discovery in C# with Needlr: How It Works – Nick Cosentino

☕ Coffee Break

Take a break to catch some fun social posts.

😅 American friends…

Coding then vs coding now….

🗞️ JetBrains News

What’s going on at JetBrains? Find out here:

📊 Check out our Developer Ecosystem Survey: The State of .NET 2025 📊

• C# Extension Members
• Rider 2025.3: Day-One Support for .NET 10 and C# 14, a New Default UI, and Faster Startup
• Open Source in Focus: .NET Projects and the Tools Behind Them

✉️ Comments? Questions? Send us an email.

Subscribe to dotInsights

The Model Context Protocol (MCP) is changing how AI applications connect to external tools and data. While some tutorials stop at "connect to GitHub" or "read a file," the real power of MCP is unlocking your internal data—databases, internal APIs, knowledge bases, and proprietary systems—for AI assistants in a structured, secure way.

In this guide, I'll walk you through building production-grade MCP servers that expose your organization's internal data to AI models. We'll go beyond simple examples and cover authentication, multi-tenancy, streaming, and deployment patterns you'll actually need.

Table of Contents

• Prerequisites

• What is MCP and Why Does It Matter for Internal Data?

• Architecture Overview

• Setting Up the Project

• Building the MCP Server

  • Step 1: Server Skeleton

  • Step 2: Connecting to Internal Data

  • Step 3: Defining Tools

  • Tool Design Principles

  • Step 4: Exposing Resources

  • Step 5: Transport and Startup

• Adding Authentication

  • Bearer Token Authentication

  • OAuth 2.0 for MCP

• Scoping Data Access Per User

• Connecting to Internal APIs

• Building a RAG Tool for Internal Documents

• Production Deployment

  • Dockerizing the MCP Server

  • Health Checks and Monitoring

  • Logging and Audit Trail

• Connecting Your MCP Server to AI Clients

  • Claude Desktop

  • Custom Application (using the MCP Client SDK)

• Common Pitfalls

• Wrapping Up

Prerequisites

This is an advanced guide. You should be comfortable with:

• TypeScript / Node.js

• REST APIs and server-side development

• Basic understanding of LLMs and tool calling

• Familiarity with protocols like JSON-RPC

What is MCP, and Why Does It Matter for Internal Data?

MCP is an open protocol (created by Anthropic) that standardizes how AI assistants discover and invoke external tools. Think of it as a USB-C port for AI — one standard interface that lets any AI model connect to any data source.

Before MCP, connecting an AI assistant to your internal database meant:

• Writing custom tool definitions for each LLM provider

• Hardcoding data access logic into your AI application

• Rebuilding everything when you switched models or added new data sources

MCP separates the data layer from the AI layer. Your MCP server exposes tools and resources. Any MCP-compatible client—Claude, ChatGPT, your custom app—can use them without modification.

For internal data, this is significant because:

• Your CRM, ERP, ticketing system, and wiki all become AI-accessible through one protocol

• Access control stays in your MCP server, not scattered across AI application code

• New AI models or clients automatically get access without rewiring integrations

• Tool definitions live close to the data, making them easier to maintain and version

Architecture Overview

Here's what we're building:

The MCP server sits between your AI client and your internal systems. It handles:

• Tool discovery: Tells the AI what operations are available

• Parameter validation: Ensures the AI sends correct inputs

• Data access: Queries your internal systems

• Response formatting: Returns structured data the AI can reason about

• Authentication: Verifies who's making the request

Setting Up the Project

Let's build an MCP server that exposes an internal employee directory and project management system.

mkdir internal-data-mcp && cd internal-data-mcp
npm init -y
npm install @modelcontextprotocol/sdk zod express pg
npm install -D typescript @types/node @types/express @types/pg tsx

These commands scaffold the project. npm install pulls in the runtime dependencies: the official MCP SDK, Zod for schema validation, Express for the HTTP server, and pg for PostgreSQL. The -D flag installs TypeScript and its type definitions as dev-only dependencies — they're needed to compile the code but don't ship to production. tsx lets you run TypeScript directly during development without a separate compile step.

Now, create your tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "Node16",
    "moduleResolution": "Node16",
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "declaration": true
  },
  "include": ["src/**/*"]
}

This TypeScript config targets ES2022, which supports modern JavaScript features like top-level await. "module": "Node16" and "moduleResolution": "Node16" are required when using the MCP SDK's .js import extensions. "strict": true enables all of TypeScript's strictness checks, which helps catch bugs in tool handlers before they reach production. The outDir/rootDir pair tells the compiler to take source files from src/ and emit compiled JavaScript into dist/.

Building the MCP Server

Step 1: Server Skeleton

Create src/server.ts:

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { z } from "zod";

const server = new McpServer(
  { name: "internal-data", version: "1.0.0" },
  { capabilities: { tools: {}, resources: {} } }
);

The McpServer class from the official SDK handles the JSON-RPC protocol, transport negotiation, and lifecycle management. We declare support for both tools (actions the AI can take) and resources (data the AI can read).

Step 2: Connecting to Internal Data

Let's say you have a PostgreSQL database with employee and project data. Create a data access layer:

// src/db.ts
import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.INTERNAL_DB_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

export interface Employee {
  id: string;
  name: string;
  email: string;
  department: string;
  role: string;
  manager_id: string | null;
  start_date: string;
}

export interface Project {
  id: string;
  name: string;
  status: "active" | "completed" | "on_hold";
  lead_id: string;
  department: string;
  deadline: string | null;
}

export async function searchEmployees(
  query: string,
  department?: string
): Promise<Employee[]> {
  const conditions = ["(name ILIKE \(1 OR email ILIKE \)1 OR role ILIKE $1)"];
  const params: string[] = [`%${query}%`];

  if (department) {
    conditions.push(`department = $${params.length + 1}`);
    params.push(department);
  }

  const result = await pool.query<Employee>(
    `SELECT id, name, email, department, role, manager_id, start_date
     FROM employees
     WHERE ${conditions.join(" AND ")}
     ORDER BY name
     LIMIT 25`,
    params
  );

  return result.rows;
}

export async function getProjectsByStatus(
  status: string
): Promise<Project[]> {
  const result = await pool.query<Project>(
    `SELECT id, name, status, lead_id, department, deadline
     FROM projects
     WHERE status = $1
     ORDER BY deadline ASC NULLS LAST`,
    [status]
  );

  return result.rows;
}

export async function getProjectMembers(
  projectId: string
): Promise<Employee[]> {
  const result = await pool.query<Employee>(
    `SELECT e.id, e.name, e.email, e.department, e.role,
            e.manager_id, e.start_date
     FROM employees e
     JOIN project_members pm ON pm.employee_id = e.id
     WHERE pm.project_id = $1
     ORDER BY e.name`,
    [projectId]
  );

  return result.rows;
}

Notice this is plain SQL with parameterized queries. Your MCP server's data access layer should use whatever your team already uses — Prisma, Drizzle, Knex, raw SQL. MCP doesn't dictate your data access patterns.

Step 3: Defining Tools

Now expose this data through MCP tools. This is where the design matters most. Good tool definitions directly impact how well the AI uses your data.

// src/tools.ts
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { z } from "zod";
import {
  searchEmployees,
  getProjectsByStatus,
  getProjectMembers,
} from "./db.js";

export function registerTools(server: McpServer) {
  // Tool 1: Search the employee directory
  server.tool(
    "search_employees",
    `Search the internal employee directory by name, email, or role.
     Returns matching employees with their department and reporting structure.
     Use this when the user asks about people, teams, or org structure.`,
    {
      query: z
        .string()
        .describe("Search term: employee name, email, or role title"),
      department: z
        .string()
        .optional()
        .describe(
          "Filter by department name (e.g., 'Engineering', 'Marketing')"
        ),
    },
    async ({ query, department }) => {
      const employees = await searchEmployees(query, department);

      if (employees.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: `No employees found matching "\({query}"\){department ? ` in ${department}` : ""}.`,
            },
          ],
        };
      }

      const formatted = employees
        .map(
          (e) =>
            `- **\({e.name}** (\){e.email})\n  Role: \({e.role} | Dept: \){e.department} | Since: ${e.start_date}`
        )
        .join("\n");

      return {
        content: [
          {
            type: "text",
            text: `Found \({employees.length} employee(s):\n\n\){formatted}`,
          },
        ],
      };
    }
  );

  // Tool 2: List projects by status
  server.tool(
    "list_projects",
    `List internal projects filtered by status.
     Returns project name, lead, department, and deadline.
     Use this when the user asks about ongoing work, project status, or deadlines.`,
    {
      status: z
        .enum(["active", "completed", "on_hold"])
        .describe("Project status to filter by"),
    },
    async ({ status }) => {
      const projects = await getProjectsByStatus(status);

      if (projects.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: `No ${status} projects found.`,
            },
          ],
        };
      }

      const formatted = projects
        .map(
          (p) =>
            `- **\({p.name}** [\){p.status}]\n  Lead: \({p.lead_id} | Dept: \){p.department} | Deadline: ${p.deadline ?? "None"}`
        )
        .join("\n");

      return {
        content: [
          {
            type: "text",
            text: `\({projects.length} \){status} project(s):\n\n${formatted}`,
          },
        ],
      };
    }
  );

  // Tool 3: Get team members for a project
  server.tool(
    "get_project_team",
    `Get all team members assigned to a specific project.
     Returns employee details for each member.
     Use this when the user asks who is working on a project.`,
    {
      project_id: z
        .string()
        .uuid()
        .describe("The UUID of the project to look up"),
    },
    async ({ project_id }) => {
      const members = await getProjectMembers(project_id);

      if (members.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: "No team members found for this project.",
            },
          ],
        };
      }

      const formatted = members
        .map((m) => `- \({m.name} (\){m.role}, ${m.department})`)
        .join("\n");

      return {
        content: [
          {
            type: "text",
            text: `Project team (\({members.length} members):\n\n\){formatted}`,
          },
        ],
      };
    }
  );
}

server.tool() registers each tool with four arguments: the tool name, a plain-English description the AI reads to decide when to call it, a Zod schema defining the parameters, and the async handler that runs when the tool is invoked. The handler receives validated, typed parameters — Zod rejects malformed inputs before your handler ever runs. Each handler returns a content array; the type: "text" block is the most common format and tells the AI client to treat the response as readable text. Returning an empty result (zero matches) is handled explicitly so the AI gets a useful message rather than an empty array it might misinterpret.

Tool Design Principles

Three things make the difference between tools an AI uses well and tools it struggles with:

1. Descriptive names and descriptions. The AI decides which tool to call based entirely on the description. Be specific about when to use the tool, not just what it does. Compare:

// Vague — the AI won't know when to pick this
"Search employees"

// Specific — the AI knows exactly when this tool is relevant
"Search the internal employee directory by name, email, or role.
 Use this when the user asks about people, teams, or org structure."

2. Typed parameters with descriptions. Use Zod's .describe() on every parameter. The AI needs to understand what each field expects:

// The AI has to guess what format "query" expects
{ query: z.string() }

// The AI knows exactly what to pass
{ query: z.string().describe("Search term: employee name, email, or role title") }

3. Structured return values. Return data in a format the AI can reason about. Use markdown tables or structured lists rather than raw JSON dumps. The AI processes structured text better than deeply nested objects.

Step 4: Exposing Resources

Resources are read-only data the AI can pull into its context. Unlike tools (which the AI invokes during reasoning), resources are typically loaded upfront to provide background knowledge.

// src/resources.ts
import {
  McpServer,
  ResourceTemplate,
} from "@modelcontextprotocol/sdk/server/mcp.js";

export function registerResources(server: McpServer) {
  // Static resource: org chart overview
  server.resource(
    "org-structure",
    "internal://org-structure",
    {
      description:
        "Overview of the organization structure including departments and leadership",
      mimeType: "text/markdown",
    },
    async (uri) => ({
      contents: [
        {
          uri: uri.href,
          mimeType: "text/markdown",
          text: await generateOrgOverview(),
        },
      ],
    })
  );

  // Dynamic resource template: department details
  server.resource(
    "department-info",
    new ResourceTemplate("internal://departments/{name}", {
      list: undefined,
    }),
    {
      description: "Detailed information about a specific department",
      mimeType: "text/markdown",
    },
    async (uri, variables) => ({
      contents: [
        {
          uri: uri.href,
          mimeType: "text/markdown",
          text: await getDepartmentDetails(
            variables.name as string
          ),
        },
      ],
    })
  );
}

server.resource() registers two kinds of resources here. The first uses a fixed URI (internal://org-structure) — this is a static resource the AI can request by name. The second uses a ResourceTemplate, which defines a URI pattern with a {name} placeholder; the AI can request internal://departments/Engineering and the variables.name parameter will be populated with "Engineering" at runtime. Both resources return a contents array with mimeType: "text/markdown" — this tells the client how to render the response. Resources differ from tools in that they're meant to be read as background context, not invoked as actions.

Resources are useful for data that provides context rather than answering a specific question — company policies, API documentation, database schemas, configuration references.

Step 5: Transport and Startup

MCP supports multiple transports. For internal data servers, you'll typically use one of two:

Streamable HTTP — the recommended transport for remote servers (replaces the older SSE transport):

// src/index.ts
import express from "express";
import { randomUUID } from "node:crypto";
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js";
import { registerTools } from "./tools.js";
import { registerResources } from "./resources.js";

const app = express();
app.use(express.json());

const server = new McpServer(
  { name: "internal-data", version: "1.0.0" },
  { capabilities: { tools: {}, resources: {} } }
);

registerTools(server);
registerResources(server);

// Store transports by session ID
const transports = new Map<string, StreamableHTTPServerTransport>();

// Handle all MCP requests on a single endpoint
app.all("/mcp", async (req, res) => {
  // Check for existing session
  const sessionId = req.headers["mcp-session-id"] as string | undefined;

  if (sessionId && transports.has(sessionId)) {
    // Existing session — route to its transport
    const transport = transports.get(sessionId)!;
    await transport.handleRequest(req, res);
    return;
  }

  if (sessionId && !transports.has(sessionId)) {
    // Unknown session ID
    res.status(404).json({ error: "Session not found" });
    return;
  }

  // New session — create transport and connect
  const transport = new StreamableHTTPServerTransport({
    sessionIdGenerator: () => randomUUID(),
    onsessioninitialized: (id) => {
      transports.set(id, transport);
    },
  });

  transport.onclose = () => {
    if (transport.sessionId) {
      transports.delete(transport.sessionId);
    }
  };

  await server.connect(transport);
  await transport.handleRequest(req, res);
});

app.listen(3100, () => {
  console.log("MCP server running on http://localhost:3100/mcp");
});

This sets up a single /mcp endpoint that handles all MCP communication. When a new client connects (no mcp-session-id header), a StreamableHTTPServerTransport is created and stored in the transports Map keyed by a generated UUID. On subsequent requests, the session ID from the header is used to look up the existing transport and route the request to it — this is how the server maintains stateful sessions with multiple clients simultaneously. transport.onclose cleans up the Map entry when a session ends, preventing memory leaks. The StdioServerTransport alternative (shown below) skips all of this: it reads from stdin and writes to stdout, which is how Claude Desktop spawns local servers as child processes.

Stdio — for local development or when the MCP client spawns the server as a child process:

// src/stdio.ts
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { registerTools } from "./tools.js";
import { registerResources } from "./resources.js";

const server = new McpServer(
  { name: "internal-data", version: "1.0.0" },
  { capabilities: { tools: {}, resources: {} } }
);

registerTools(server);
registerResources(server);

const transport = new StdioServerTransport();
await server.connect(transport);

For internal data in a production setting, HTTP/SSE is almost always what you want. Stdio is convenient for development and when the client and server run on the same machine.

Adding Authentication

Internal data servers need authentication. You don't want every AI client on the network querying your employee database unauthenticated.

Bearer Token Authentication

The simplest approach is to validate a token on every request:

// src/auth-middleware.ts
import { Request, Response, NextFunction } from "express";

interface AuthenticatedRequest extends Request {
  userId?: string;
  orgId?: string;
}

export function authMiddleware(
  req: AuthenticatedRequest,
  res: Response,
  next: NextFunction
) {
  const authHeader = req.headers.authorization;

  if (!authHeader?.startsWith("Bearer ")) {
    return res.status(401).json({ error: "Missing authorization header" });
  }

  const token = authHeader.slice(7);

  try {
    // Validate against your internal auth system
    const claims = validateInternalToken(token);
    req.userId = claims.sub;
    req.orgId = claims.org;
    next();
  } catch {
    return res.status(403).json({ error: "Invalid token" });
  }
}

function validateInternalToken(token: string) {
  // Replace with your actual token validation:
  // - JWT verification against your auth service
  // - API key lookup in your database
  // - Session token validation against Redis
  // This is a placeholder
  return { sub: "user-123", org: "org-456" };
}

The middleware checks every request for an Authorization: Bearer <token> header before it reaches the MCP handler. validateInternalToken is a placeholder — replace it with your real validation logic: JWT verification using a library like jsonwebtoken, an API key lookup in your database, or a session token check against Redis. The validated claims are attached to the request object (req.userId, req.orgId) so downstream tool handlers can use them for access scoping. The app.use("/mcp", authMiddleware) line ensures no request reaches the MCP endpoint without passing this check first.

Add it to your Express app:

app.use("/mcp", authMiddleware);

OAuth 2.0 for MCP

For clients that support MCP's built-in OAuth flow (like Claude Desktop), you can implement the full OAuth handshake. The MCP SDK provides the OAuthServerProvider interface with these required methods:

import type { OAuthServerProvider } from "@modelcontextprotocol/sdk/server/auth/provider.js";
import type {
  AuthorizationParams,
  OAuthClientInformationFull,
  OAuthRegisteredClientsStore,
  OAuthTokens,
  AuthInfo,
} from "@modelcontextprotocol/sdk/server/auth/types.js";

class InternalOAuthProvider implements OAuthServerProvider {
  // Store for registered OAuth clients
  get clientsStore(): OAuthRegisteredClientsStore {
    return this._clientsStore;
  }

  private _clientsStore: OAuthRegisteredClientsStore = {
    async getClient(clientId: string) {
      // Look up the registered client in your database
      return db.getOAuthClient(clientId);
    },
    async registerClient(clientMetadata) {
      // Register a new dynamic client
      return db.createOAuthClient(clientMetadata);
    },
  };

  // Redirect the user to your internal SSO for authorization
  async authorize(
    client: OAuthClientInformationFull,
    params: AuthorizationParams,
    res: Response
  ): Promise<void> {
    const authUrl = new URL(
      "https://sso.internal.company.com/authorize"
    );
    authUrl.searchParams.set("client_id", client.client_id);
    authUrl.searchParams.set("redirect_uri", params.redirectUri);
    authUrl.searchParams.set("state", params.state ?? "");
    authUrl.searchParams.set(
      "code_challenge",
      params.codeChallenge
    );
    // The method writes to the response directly
    res.redirect(authUrl.toString());
  }

  // Return the PKCE challenge for a given authorization code
  async challengeForAuthorizationCode(
    _client: OAuthClientInformationFull,
    authorizationCode: string
  ): Promise<string> {
    const session = await db.getSessionByCode(authorizationCode);
    return session.codeChallenge;
  }

  // Exchange authorization code for access + refresh tokens
  async exchangeAuthorizationCode(
    client: OAuthClientInformationFull,
    authorizationCode: string,
    _codeVerifier?: string,
    _redirectUri?: string
  ): Promise<OAuthTokens> {
    const response = await fetch(
      "https://sso.internal.company.com/token",
      {
        method: "POST",
        headers: {
          "Content-Type": "application/x-www-form-urlencoded",
        },
        body: new URLSearchParams({
          grant_type: "authorization_code",
          code: authorizationCode,
          client_id: client.client_id,
        }),
      }
    );

    return response.json() as Promise<OAuthTokens>;
  }

  // Refresh expired tokens
  async exchangeRefreshToken(
    client: OAuthClientInformationFull,
    refreshToken: string
  ): Promise<OAuthTokens> {
    const response = await fetch(
      "https://sso.internal.company.com/token",
      {
        method: "POST",
        headers: {
          "Content-Type": "application/x-www-form-urlencoded",
        },
        body: new URLSearchParams({
          grant_type: "refresh_token",
          refresh_token: refreshToken,
          client_id: client.client_id,
        }),
      }
    );

    return response.json() as Promise<OAuthTokens>;
  }

  // Validate an access token on every request
  async verifyAccessToken(token: string): Promise<AuthInfo> {
    const response = await fetch(
      "https://sso.internal.company.com/introspect",
      {
        method: "POST",
        headers: {
          "Content-Type": "application/x-www-form-urlencoded",
        },
        body: new URLSearchParams({ token }),
      }
    );

    const data = await response.json();
    if (!data.active) throw new Error("Token inactive");

    return {
      token,
      clientId: data.client_id,
      scopes: data.scope?.split(" ") ?? [],
      expiresAt: data.exp,
    };
  }
}

InternalOAuthProvider implements the OAuthServerProvider interface, which the MCP SDK calls at each stage of the OAuth flow. clientsStore handles dynamic client registration — MCP clients like Claude Desktop register themselves the first time they connect. authorize() redirects the user to your internal SSO; it writes directly to the Express response. challengeForAuthorizationCode() returns the PKCE code challenge stored when the authorization session began — this is how the token exchange is verified without transmitting secrets. exchangeAuthorizationCode() and exchangeRefreshToken() make server-to-server calls to your SSO's token endpoint, keeping credentials out of the browser. verifyAccessToken() is called on every incoming MCP request using the token introspection endpoint to confirm the token is still active and extract the user's scopes.

Scoping Data Access Per User

This is the most important part of an internal data MCP server: the AI should only access data the requesting user is authorized to see.

Don't skip this. Without user-scoped access, you're building a data exfiltration tool with an AI wrapper.

// src/scoped-tools.ts
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { z } from "zod";

export function registerScopedTools(
  server: McpServer,
  getUserContext: () => { userId: string; orgId: string; role: string }
) {
  server.tool(
    "search_employees",
    "Search the employee directory. Results are filtered based on your access level.",
    {
      query: z.string().describe("Name, email, or role to search for"),
    },
    async ({ query }) => {
      const ctx = getUserContext();

      // Enforce access boundaries
      let departmentFilter: string | undefined;

      if (ctx.role === "manager") {
        // Managers see their department only
        departmentFilter = await getUserDepartment(ctx.userId);
      } else if (ctx.role === "employee") {
        // Regular employees see limited fields
        departmentFilter = await getUserDepartment(ctx.userId);
      }
      // Admins and HR see everything — no filter

      const employees = await searchEmployees(query, departmentFilter);

      // Redact sensitive fields based on role
      const results = employees.map((e) => ({
        name: e.name,
        email: e.email,
        department: e.department,
        role: e.role,
        // Only HR and admins see start date and manager info
        ...(["admin", "hr"].includes(ctx.role)
          ? { start_date: e.start_date, manager_id: e.manager_id }
          : {}),
      }));

      return {
        content: [
          {
            type: "text",
            text: formatEmployeeList(results),
          },
        ],
      };
    }
  );
}

The pattern here:

1. Extract user context from the authenticated session

2. Filter queries at the database level (not after fetching everything)

3. Redact fields the user shouldn't see

4. Log access for audit trails

Connecting to Internal APIs

Not all internal data lives in databases. You often need to wrap existing internal APIs:

server.tool(
  "get_ticket_details",
  `Look up a support ticket from the internal ticketing system.
   Returns ticket status, assignee, priority, and recent updates.`,
  {
    ticket_id: z
      .string()
      .regex(/^TK-\d+$/)
      .describe("Ticket ID in format TK-12345"),
  },
  async ({ ticket_id }) => {
    const ctx = getUserContext();

    const response = await fetch(
      `\({process.env.TICKETING_API_URL}/api/v2/tickets/\){ticket_id}`,
      {
        headers: {
          Authorization: `Bearer ${process.env.TICKETING_SERVICE_TOKEN}`,
          "X-On-Behalf-Of": ctx.userId,
        },
      }
    );

    if (response.status === 404) {
      return {
        content: [
          { type: "text", text: `Ticket ${ticket_id} not found.` },
        ],
      };
    }

    if (response.status === 403) {
      return {
        content: [
          {
            type: "text",
            text: `You don't have access to ticket ${ticket_id}.`,
          },
        ],
      };
    }

    const ticket = await response.json();

    return {
      content: [
        {
          type: "text",
          text: [
            `**\({ticket.id}: \){ticket.title}**`,
            `Status: \({ticket.status} | Priority: \){ticket.priority}`,
            `Assignee: ${ticket.assignee?.name ?? "Unassigned"}`,
            `Created: ${ticket.created_at}`,
            "",
            `**Latest Update:**`,
            ticket.updates?.[0]?.body ?? "No updates yet.",
          ].join("\n"),
        },
      ],
    };
  }
);

Key points when wrapping internal APIs:

• Use service tokens for server-to-server auth, but pass user identity via headers like X-On-Behalf-Of

• Handle HTTP errors explicitly — return user-friendly messages, not raw error objects

• Validate input formats — the regex on ticket_id prevents injection and guides the AI on expected format

• Don't leak internal implementation details in error messages

Building a RAG Tool for Internal Documents

One of the highest-value use cases: letting the AI search your internal knowledge base. Here's a tool that performs vector search against an internal document store:

server.tool(
  "search_internal_docs",
  `Search the internal knowledge base for relevant documents.
   Covers engineering docs, runbooks, architecture decisions, and policies.
   Use this when the user asks about internal processes, systems, or decisions.`,
  {
    query: z
      .string()
      .describe("Natural language search query"),
    category: z
      .enum(["engineering", "policy", "runbook", "architecture", "all"])
      .default("all")
      .describe("Document category to search within"),
    limit: z
      .number()
      .min(1)
      .max(10)
      .default(5)
      .describe("Maximum number of results"),
  },
  async ({ query, category, limit }) => {
    // Generate embedding for the search query
    const embedding = await generateEmbedding(query);

    // Vector similarity search against your document store
    const results = await pool.query(
      `SELECT
         d.id,
         d.title,
         d.category,
         d.content_chunk,
         d.source_url,
         d.updated_at,
         1 - (d.embedding <=> $1::vector) AS similarity
       FROM document_chunks d
       WHERE (\(2 = 'all' OR d.category = \)2)
         AND 1 - (d.embedding <=> $1::vector) > 0.7
       ORDER BY d.embedding <=> $1::vector
       LIMIT $3`,
      [JSON.stringify(embedding), category, limit]
    );

    if (results.rows.length === 0) {
      return {
        content: [
          {
            type: "text",
            text: `No relevant documents found for "${query}".`,
          },
        ],
      };
    }

    const formatted = results.rows
      .map(
        (doc, i) =>
          `### \({i + 1}. \){doc.title}\n` +
          `Category: \({doc.category} | Updated: \){doc.updated_at} | Relevance: ${(doc.similarity * 100).toFixed(0)}%\n\n` +
          `${doc.content_chunk}\n\n` +
          `Source: ${doc.source_url}`
      )
      .join("\n\n---\n\n");

    return {
      content: [
        {
          type: "text",
          text: `Found \({results.rows.length} relevant document(s):\n\n\){formatted}`,
        },
      ],
    };
  }
);

This tool combines two operations: embedding generation and vector similarity search. generateEmbedding(query) calls an embedding model (such as OpenAI's text-embedding-3-small or a self-hosted model) to convert the user's query into a numeric vector. The SQL query then uses pgvector's <=> operator to compute cosine distance between the query vector and stored document chunk embeddings — lower distance means higher similarity. The 1 - (embedding <=> $1) > 0.7 condition filters out results below 70% similarity, so the AI doesn't receive loosely related noise. Results are ordered by ascending distance (most similar first) and capped by the limit parameter. The formatted output includes a relevance percentage so the AI can communicate confidence levels to the user.

Production Deployment

Dockerizing the MCP Server

FROM node:22-slim AS builder

WORKDIR /app
COPY package*.json ./
RUN npm ci
COPY . .
RUN npm run build

FROM node:22-slim AS runtime

WORKDIR /app
COPY --from=builder /app/dist ./dist
COPY --from=builder /app/node_modules ./node_modules
COPY --from=builder /app/package.json ./

ENV NODE_ENV=production
EXPOSE 3100

HEALTHCHECK --interval=30s --timeout=5s \
  CMD curl -f http://localhost:3100/health || exit 1

CMD ["node", "dist/index.js"]

The Dockerfile uses a two-stage build. The builder stage installs all dependencies (including devDependencies) and compiles TypeScript to JavaScript in dist/. The runtime stage starts fresh from a clean Node image and copies only the compiled output and node_modules — devDependencies like TypeScript are excluded, keeping the final image small. The HEALTHCHECK instruction tells Docker (and orchestrators like Kubernetes) to poll /health every 30 seconds; if the endpoint fails, the container is marked unhealthy and can be automatically restarted or removed from the load balancer rotation.

Health Checks and Monitoring

Add a health endpoint that verifies your dependencies:

app.get("/health", async (_req, res) => {
  const checks = {
    database: false,
    ticketingApi: false,
  };

  try {
    await pool.query("SELECT 1");
    checks.database = true;
  } catch {}

  try {
    const resp = await fetch(
      `${process.env.TICKETING_API_URL}/health`
    );
    checks.ticketingApi = resp.ok;
  } catch {}

  const healthy = Object.values(checks).every(Boolean);
  res.status(healthy ? 200 : 503).json({
    status: healthy ? "healthy" : "degraded",
    checks,
    uptime: process.uptime(),
  });
});

The /health endpoint runs two dependency checks in parallel: a lightweight SELECT 1 query to confirm the database connection is live, and an HTTP ping to the ticketing API. Both results are collected into a checks object. If any check fails, the endpoint returns HTTP 503 (Service Unavailable) — this is the signal load balancers and container orchestrators use to stop routing traffic to an unhealthy instance. process.uptime() is included as a diagnostic field so you can quickly tell whether a degraded instance just started or has been running for hours.

Logging and Audit Trail

Every tool invocation against internal data should be logged:

function createAuditLogger() {
  return {
    logToolCall(params: {
      userId: string;
      tool: string;
      input: Record<string, unknown>;
      resultSize: number;
      durationMs: number;
    }) {
      // Ship to your logging infrastructure
      // (Datadog, ELK, CloudWatch, etc.)
      console.log(
        JSON.stringify({
          event: "mcp_tool_call",
          timestamp: new Date().toISOString(),
          ...params,
        })
      );
    },
  };
}

createAuditLogger returns a logger object rather than a class instance, which makes it easy to swap the underlying transport (stdout, a logging SDK, etc.) without changing the call sites. The audited wrapper function is a higher-order function: it takes a tool handler and returns a new function with the same signature, but with timing and logging added around the original call. The try/catch ensures a log entry is written even when the handler throws — you want failed calls in your audit trail, not just successful ones. Shipping these logs to a centralized store (Datadog, CloudWatch, ELK) lets you answer questions like "what data did this user's AI session access last Tuesday?" — which is often required for compliance in organizations handling sensitive internal data.

Wrap your tool handlers to automatically log every call:

function audited<T extends Record<string, unknown>>(
  handler: (params: T) => Promise<ToolResult>,
  toolName: string,
  audit: ReturnType<typeof createAuditLogger>
) {
  return async (params: T): Promise<ToolResult> => {
    const start = Date.now();
    const ctx = getUserContext();

    try {
      const result = await handler(params);
      audit.logToolCall({
        userId: ctx.userId,
        tool: toolName,
        input: params,
        resultSize: JSON.stringify(result).length,
        durationMs: Date.now() - start,
      });
      return result;
    } catch (error) {
      audit.logToolCall({
        userId: ctx.userId,
        tool: toolName,
        input: params,
        resultSize: 0,
        durationMs: Date.now() - start,
      });
      throw error;
    }
  };
}

Connecting Your MCP Server to AI Clients

Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "internal-data": {
      "url": "http://localhost:3100/mcp",
      "headers": {
        "Authorization": "Bearer your-internal-token"
      }
    }
  }
}

Custom Application (using the MCP Client SDK)

import { Client } from "@modelcontextprotocol/sdk/client/index.js";
import { StreamableHTTPClientTransport } from "@modelcontextprotocol/sdk/client/streamableHttp.js";

const transport = new StreamableHTTPClientTransport(
  new URL("http://localhost:3100/mcp"),
  {
    requestInit: {
      headers: {
        Authorization: `Bearer ${userToken}`,
      },
    },
  }
);

const client = new Client(
  { name: "my-ai-app", version: "1.0.0" }
);

await client.connect(transport);

// Discover available tools
const { tools } = await client.listTools();
console.log("Available tools:", tools.map((t) => t.name));

// Call a tool
const result = await client.callTool({
  name: "search_employees",
  arguments: { query: "engineering manager" },
});

console.log(result.content);

StreamableHTTPClientTransport manages the HTTP connection to your MCP server, including attaching the Authorization header to every request. client.connect(transport) performs the MCP initialization handshake — the client announces its capabilities and the server responds with the list of available tools and resources. client.listTools() returns the full tool catalog, which you can use to dynamically build a UI or pass directly to an LLM's tool-calling API. client.callTool() sends a JSON-RPC request to invoke a specific tool by name and returns the content array from the handler — the same format the AI model receives. In a production application, you'd pass this content back to the model as a tool result in the conversation history.

Common Pitfalls

1. Returning too much data. LLMs have context limits. If your database query returns 500 rows, don't send them all. Paginate, summarize, or limit results. 25 items is a reasonable default.

2. Tool descriptions that are too generic. If you have search_employees and search_contractors, the AI needs to know the difference. Don't rely on the tool name alone — the description is what the model reads.

3. Missing error handling. When a database query fails, return a structured error message, not a stack trace. The AI needs to tell the user something useful, and raw errors leak implementation details.

4. No rate limiting. AI tool calls can happen in loops. If the model calls your tool 50 times in one conversation, you need circuit breakers:

const rateLimiter = new Map<string, number[]>();

function checkRateLimit(userId: string, limit = 30, windowMs = 60000) {
  const now = Date.now();
  const calls = rateLimiter.get(userId) ?? [];
  const recent = calls.filter((t) => now - t < windowMs);

  if (recent.length >= limit) {
    throw new Error(
      `Rate limit exceeded. Max ${limit} calls per minute.`
    );
  }

  recent.push(now);
  rateLimiter.set(userId, recent);
}

5. Not testing with actual AI models. Your tools might look correct in unit tests but confuse the model. Test the full loop: AI model receives tool definitions, decides to call a tool, gets the result, and reasons about it. Adjust descriptions based on how the model actually behaves.

Wrapping Up

Building MCP servers for internal data is about three things:

1. Good tool design — clear descriptions, typed parameters, structured responses

2. Proper access control — authenticate users, scope data access, log everything

3. Production readiness — health checks, rate limiting, error handling, monitoring

The protocol itself is straightforward. The hard work is designing the right abstractions over your internal systems so the AI can use them effectively without leaking data or overwhelming the context window.

Start with one or two high-value tools (employee lookup, document search), test them with real users, and expand from there. The best internal MCP servers grow organically based on what people actually ask the AI.

The full source code from this guide is available on GitHub.

Stop Syncing State and Start Syncing Facts.

How an append-only event log turns flaky connectivity from a crisis into a non-issue, and gives you time travel for free.

The Two-Layer Event Log Architecture

Event sourcing is usually described as a single, append-only log in the backend.

But in distributed, real-world systems, especially offline-first ones, that mental model is incomplete.

A more resilient model is two independent event logs:

1. A client-side event log (in the browser or device)
2. A server-side event log (in the backend)

Each log is authoritative in its own layer.
They are loosely coupled, synchronized, and not dependent.

Layer 1: The Client Event Log

Instead of treating the browser as a temporary cache, we treat it as a first-class event producer.

Every user action becomes an event:

• Account Created
• Deposited
• Withdrawn

Those events are appended locally first and aggregated into the
UI state in the browser.
State is derived from the local log.

This gives you:

• True offline capability
• Instant UI updates
• Deterministic replay
• Time-travel debugging on the client

The client does not wait for the server to “approve” state changes.
It records intent.

Layer 2: The Server Event Log

The server maintains its own authoritative event store:

• Durable
• Ordered
• Auditable
• Cross-device consistent

When connectivity exists, the client pushes its local events to the server.
The server validates, persists, and possibly emits new domain events.

The key principles:

• The server log does not have to depend on the client log structure.
  Or, if desired, reuse the same event contracts and aggregation logic to guarantee full parity.
• They communicate via a synchronization protocol, not shared state.

Why Two Layers?

Because networks are unreliable.
Instead of treating bad connectivity as an edge case, the architecture assumes:

• Devices disconnect
• Events arrive late
• Multiple devices act concurrently
• Replays happen

The result:

• Flaky connectivity becomes a non-event
• Multi-device consistency becomes deterministic
• Debugging becomes replaying logs
• “What happened?” always has an answer

The Mental Shift

Stop thinking of the client as a thin UI.

Think of it as a real application, just another service in your microservice mesh.
It can be autonomous, with its own storage and logic, which makes it more resilient and far less sensitive to connectivity issues.

It can communicate via messaging, just like backend services do.

Event sourcing fits naturally with the client, making it natural for simple communication based on the cause rather than the outcome.

Events are the source of truth on both, while the state is an interpretation.

Pure Events Synchronization

The sync boundary is intentionally minimal:

POST /api/events/batch
[
  {
    id: "uuid-1",
    type: "DEPOSITED",
    payload: { amount: 100 },
    accountId: "acc-1",
    sequenceNumber: 7,
    createdAt: 1710000000000
  }
]

That’s it.

Both sides derive the state independently by replaying events.
That loose coupling is the entire point.

For example use
- ✔︎ Funds Deposited/Withdrawn
Rather than
- ✗ Balance equals n.

Network Failures Become a Non-Event

Because the client log is append-only and fully local, outages don’t interrupt the user.

Events as the Single Source of Truth

In this architecture, every user action creates an immutable event appended to a log:

type FundsEvent =
  | { type: 'ACCOUNT_CREATED'; payload: { ownerName: string } }
  | { type: 'DEPOSITED';       payload: { amount: number } }
  | { type: 'WITHDRAWN';       payload: { amount: number } }
  | { type: 'CAPTURED';        payload: { amount: number; referenceId: string } }
  | { type: 'CAPTURE_RELEASED'; payload: { referenceId: string } };

This TypeScript discriminated union is the domain model.
The type field is the discriminator; TypeScript enforces exhaustive handling:

function computeStateFromEvents(events: FundsEvent[]): AccountState {
  let balance = 0;
  const captures = new Map<string, number>();

  for (const event of events) {
    switch (event.type) {
      case 'ACCOUNT_CREATED':
        ownerName = event.payload.ownerName;
        break;
      case 'DEPOSITED':
        balance += event.payload.amount;
        break;
      case 'WITHDRAWN':
        balance -= event.payload.amount;
        break;
      case 'CAPTURED':
        captures.set(event.payload.referenceId, event.payload.amount);
        balance -= event.payload.amount;
        break;
      case 'CAPTURE_RELEASED': {
        const capturedAmount = captures.get(event.payload.referenceId) ?? 0;
        balance += capturedAmount;
        captures.delete(event.payload.referenceId);
        break;
      }
      default: {
        const _exhaustive: never = event;
        throw new Error(`Unknown event type: ${JSON.stringify(_exhaustive)}`);
      }
    }
  }
  return { balance };
}

The default check means adding a new event type without updating this function is a compile error. This is one of the concrete payoffs of the discriminated union pattern.

This function can live in packages/typesand shared between client and server. Both derive the same state from the same events. There's no risk of the client and server computing different balances from identical inputs.

TanStack: Two Different Jobs, Two Different Tools

This is where many people reach for a single state management solution and then fight it. TanStack separates concerns clearly:

TanStack Query manages server state — data that lives on the server and needs to be fetched, cached, and invalidated. It’s used here for the server-authoritative balance view and the time-travel historical state endpoint.

Dexie + useLiveQuery manages local state — the client's own event log stored in IndexedDB. This is the real-time source of truth for everything the user interacts with.

The split is intentional and powerful:

export function useAccountState(accountId: string) {
  return useLiveQuery(async () => {
    const dbEvents = await db.events
      .where('accountId').equals(accountId)
      .sortBy('sequenceNumber');

    return computeAccountState(dbEvents.map(dbEventToStoredEvent));
  }, [accountId]);
}

Every time an event is written to IndexedDB, whether by the user or by the sync engine, marking an event as confirmed, this hook re-runs, and the component updates. No polling. No manual cache invalidation. No useEffect chains.

Offline-First: Pending vs. Confirmed

The local event log tracks a status field: 'pending', 'synced', or 'failed'. This is the key to optimistic UI without lying to the user:

export function computeAccountState(events: StoredEvent[]): AccountState {
  let confirmedBalance = 0;
  let pendingDelta = 0;

  for (const event of events) {
    const delta = balanceDeltaFor(event);
    if (event.status === 'synced') {
      confirmedBalance += delta;
    } else {
      pendingDelta += delta;
    }
  }

  return {
    balance: confirmedBalance + pendingDelta, // shown to user
    pendingBalance: pendingDelta,             // shown as amber badge
  };
}

The user sees their deposit instantly. The event is in a “pending” state, which the user can see, indicating it hasn’t reached the server yet.
When sync completes, the sync is confirmed, and the balance catches up.
User pending indication is gone.
No loading spinner.
No “operation may take a few seconds.”

When the user goes offline. The app functions completely.
When connectivity returns, the sync engine drains the queue in order.

The Sync Engine: Reliability, Not Just Optimism

Optimistic UI is easy. Making it reliable is where most implementations fall short.
The sync engine here is a small but carefully designed state machine:

pending → (POST /api/events/batch) → synced
                                  → failed (409 conflict or max retries)

Batching by account. Events are grouped by accountId and sent in sequence-number order. This ensures the server always sees a contiguous log, not a scrambled one.

Idempotency. Each event carries a UUID generated at creation time. If the same event is submitted twice (e.g., the client crashed after sending but before receiving the ACK), the server detects it by ID and returns accepted again. No duplicate transactions.

Sequence conflict handling. The PostgreSQL schema enforces UNIQUE(account_id, sequence_number). If two clients both created event #7 for the same account, the second POST receives a 409. The engine marks those events failed and stops retrying — an operator needs to investigate. This is the right answer: fail loudly, never silently corrupt.

Exponential backoff. Transient failures (network blip, server restart) are retried with 1s → 2s → 4s delays. After MAX_RETRIESevents are marked as failed. The UI can surface this to the user.

private scheduleRetry(): void {
  this.retryCount = Math.min(this.retryCount + 1, MAX_RETRIES);
  const delay = BASE_DELAY_MS * Math.pow(2, this.retryCount - 1);
  this.syncTimeout = setTimeout(() => void this.sync(), delay);
}

PostgreSQL as an Event Pipeline

On the server, events are inserted into a single events table. But the application never queries that table directly for the current balance. Instead, a PL/pgSQL trigger fires after every row INSERT and materializes the aggregate into account_balances:

CREATE OR REPLACE FUNCTION apply_event_to_balance()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO account_balances (account_id, balance, last_seq, last_event_at)
  VALUES (NEW.account_id, 0, NEW.sequence_number, NEW.created_at)
  ON CONFLICT (account_id) DO UPDATE
    SET balance      = account_balances.balance + (
                         CASE NEW.type
                           WHEN 'DEPOSITED'  THEN (NEW.payload->>'amount')::numeric
                           WHEN 'WITHDRAWN'  THEN -((NEW.payload->>'amount')::numeric)
                           WHEN 'CAPTURED'   THEN -((NEW.payload->>'amount')::numeric)
                           ELSE 0
                         END),
        last_seq     = GREATEST(account_balances.last_seq, NEW.sequence_number),
        last_event_at = GREATEST(account_balances.last_event_at, NEW.created_at),
        updated_at   = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This could have been an eventual consistency or, in this case, a database primitive. The moment an event is committed, the materialized view is updated atomically, in the same transaction. No background worker. No eventual-consistency lag. The account_balances table is always in sync with the event log.

The API then reads from this materialized view for O(1) balance lookups, regardless of how many events an account has accumulated.

Snapshots: Making Cold Start Fast

Replaying the full event log on every app open is fine for 50 events. For 50,000 it’s not!
The solution is snapshots: periodic checkpoints of the aggregate state.

A balance_snapshots table holds point-in-time captures:

CREATE TABLE balance_snapshots (
  id           BIGSERIAL PRIMARY KEY,
  account_id   TEXT NOT NULL,
  balance      NUMERIC NOT NULL,
  last_seq     INTEGER NOT NULL,
  last_event_at BIGINT NOT NULL,
  captured_at  TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX ON balance_snapshots (account_id, last_event_at DESC);

On a cold start, the client runs a two-phase bootstrap:

1. Fetch GET /api/accounts — returns all accounts' current materialized balance plus lastSeq.
2. For each account, fetch only events after the local max sequence number: GET /api/accounts/{id}/events?afterSeq={n}.
3. Upsert those events into IndexedDB as synced. The local state is now current.

// Phase 1: what does the server know?
const snapshots = await fetch('/api/accounts').then(r => r.json());

// Phase 2: fill in only the gap
for (const { accountId, lastSeq } of snapshots) {
  const localMaxSeq = await getLocalMaxSeq(accountId);
  const missing = await fetch(
    `/api/accounts/${accountId}/events?afterSeq=${Math.min(localMaxSeq, lastSeq)}`
  ).then(r => r.json());

  await db.events.bulkPut(missing.events.map(e => ({ ...e, status: 'synced' })));
}

An alternative approach could be to have the server-side retrieve the snapshot data along with the subsequent events, compute the final state, and return it directly. The client DB would then start from a clean slate with only the initial state.

The result: a returning user starts up in milliseconds, not seconds.
Only the delta is transferred. This is the same pattern CDNs use for cache invalidation. Instead of “give me everything,” it’s “give me what I don’t have.”

Time Travel: The Hidden Superpower of Event Sourcing

Event logs are immutable and ordered. That means you can reconstruct any past state by replaying up to any point in time.
This isn’t just a theoretical benefit, it’s directly useful:

Analytics: “What was the average balance across all accounts at the end of last quarter?” Query the event log, don’t touch the materialized view.

Backoffice inspection: “The customer claims their balance was $2,400 last Tuesday. Show me.” Reconstruct it on demand.

Debugging: “The API returned an error at 14:32:07. What did the account look like at that exact moment?” Replay to that timestamp.

Regulatory audit: Every transaction is immutable and timestamped. There’s no “correcting” a record — you create a compensating event. The trail is complete.

The Algorithm

The server’s GET /api/accounts/:id/state?at=<unix_ms> endpoint uses a two-step algorithm:

// Step 1: Find the closest snapshot at or before the target time
const snapshot = await prisma.balanceSnapshot.findFirst({
  where: { accountId, lastEventAt: { lte: targetMs } },
  orderBy: { lastEventAt: 'desc' },
});

// Step 2: Replay only events after the snapshot up to the target time
const events = await prisma.event.findMany({
  where: {
    accountId,
    sequenceNumber: { gt: snapshot?.lastSeq ?? 0 },
    createdAt: { lte: targetMs },
  },
  orderBy: { sequenceNumber: 'asc' },
});

// Seed from snapshot, then replay delta
let balance = Number(snapshot?.balance ?? 0);
for (const event of events) {
  balance = applyEvent(balance, event);
}

The snapshot index (accountId, lastEventAt DESC) makes the seek O(log n).
The replay is then a linear scan of only the events between the snapshot and the target, usually a small number if snapshots are taken regularly.

The UI

The client’s TimeTravelPanel exposes three navigation modes, all resolving to a single targetMs:

• Date & Time: pick any datetime with a calendar input
• N Events Back: “show me what it looked like 5 events ago”
• Duration Back: “show me what it looked like 2 hours ago” (format: HH:mm:ss)

const targetMs = useMemo(() => {
  switch (mode) {
    case 'datetime':
      return new Date(selectedDatetime).getTime();
    case 'events-back': {
      const idx = Math.max(0, syncedEvents.length - parseInt(eventsBack));
      return syncedEvents[idx]?.createdAt ?? null;
    }
    case 'duration-back':
      return Date.now() - parseDurationMs(durationStr);
  }
}, [mode, selectedDatetime, eventsBack, durationStr, syncedEvents]);

TanStack Query fetches the historical state with staleTime: Infinity a given timestamp always produces the same result, so there's no point ever re-fetching:

Once a result loads, prev/next navigation buttons let you step through the event timeline one event at a time; each step triggers a new query (or a cache hit if you’ve been there before). The UI shows the historical balance alongside the current balance, the sequence number, and how many events were replayed from the nearest snapshot.

You can actually play with it:

• Clone this repo
• Run docker compose up -d to get Postgres up and running.
• npm install
• npm run db:setup to set up the database
• npm run dev

Why This Matters Beyond Funds Management

The architecture described here is domain-agnostic.
The event sourcing + sync + snapshot pattern applies to:

• Collaborative editors — ops are events; CRDTs are the state function
• IoT sensor data — readings are events; aggregates are time-series summaries
• E-commerce carts — add/remove/checkout are events; cart state is derived
• Task managers — create/assign/complete are events; todo list is the view

What you get for free, regardless of domain:

1. True offline support — the local log is complete, not a cache
2. Conflict visibility — sequence violations surface immediately, not silently
3. Audit trail — every change is a first-class record, not an overwrite
4. Time travel — historical state at any point is computable, not reconstructed from backups
5. Optimistic UI without lies — pending/confirmed distinction is explicit, not hidden

Conclusion

Once you stop thinking about syncing state and start thinking about syncing what happened, a lot of hard problems dissolve.

TanStack Router and Query don’t replace this architecture; they amplify it. useLiveQuery from Dexie handles the reactive local layer.
TanStack Query handles the server-authoritative layer. Each does exactly one job and does it well. The sync engine is the small, explicit bridge between them.

And PostgreSQL, rather than being a passive store, becomes an active participant: triggers materialize views the moment events arrive, snapshots accelerate cold start, and a simple WHERE createdAt <= ? turns any query into a time machine.

The result is a system that fails gracefully, scales cleanly, and gives operators the inspection tools they need without any of these capabilities being bolted on after the fact. They emerge from the foundation.

AI Bonus

Agent Skill: Build This Architecture with AI Assistance

When cloning the repo, you’ll find a Skill definition under .claude/skills/tanstack-event-sourcing aimed at helping with the creation of such a solution. Agent Skills are a common standard that can be applied across all major AI providers (Claude Code, Cursor, Copilot, Anti Gravity, etc.).
You can read more on Agent Skill in this article.