Virtual Views & Computed Resources

Raw table access is the simplest pattern, but it forces the AI model to understand your normalization strategy, join logic, and business rules. Virtual views — database views or computed resources that pre-join and transform data — give the model business-level abstractions. Instead of reading customers and orders separately and joining in-context, the model reads a customer_summary view that already includes order count, total spend, and last order date. This reduces context consumption, eliminates join errors, and aligns the data with how business users think about it.

There are two implementation strategies. Database views (CREATE VIEW) live in the database and are queried like tables. They are efficient because the database optimizes the join, but they require schema changes and DBA approval. Computed resources are views implemented in the MCP server — the server executes the join query and shapes the response without creating database objects. Computed resources are faster to deploy and change but add server-side complexity. In practice, use database views for stable, well-defined aggregations and computed resources for experimental or rapidly changing views.

// Option 1: Database view (created via migration)
// CREATE VIEW customer_summary AS
//   SELECT c.id, c.name, c.company,
//     COUNT(o.id) AS order_count,
//     SUM(o.total) AS total_spend,
//     MAX(o.created_at) AS last_order_date
//   FROM customers c
//   LEFT JOIN orders o ON o.customer_id = c.id
//   GROUP BY c.id, c.name, c.company;

// Expose the view as a resource
server.resource("customer-summary", "db://views/customer-summary",
  async (uri) => {
    const rows = await pool.query(
      "SELECT * FROM customer_summary ORDER BY total_spend DESC LIMIT 500"
    );
    return {
      contents: [{ uri: uri.href, mimeType: "application/json",
        text: JSON.stringify(rows.rows) }],
    };
  }
);

// Option 2: Computed resource (server-side join)
server.resource("order-detail", 
  new ResourceTemplate("db://computed/order/{orderId}", { list: undefined }),
  async (uri, { orderId }) => {
    const result = await pool.query(`
      SELECT o.id, o.status, o.total, o.created_at,
        c.name AS customer_name, c.company,
        json_agg(json_build_object(
          'product', p.name, 'quantity', oi.quantity, 'price', oi.price
        )) AS line_items
      FROM orders o
      JOIN customers c ON c.id = o.customer_id
      JOIN order_items oi ON oi.order_id = o.id
      JOIN products p ON p.id = oi.product_id
      WHERE o.id = $1
      GROUP BY o.id, o.status, o.total, o.created_at, c.name, c.company
    `, [orderId]);
    return {
      contents: [{ uri: uri.href, mimeType: "application/json",
        text: JSON.stringify(result.rows[0] ?? null) }],
    };
  }
);

1. Identify common queries Review what questions users ask the AI about your data. Each recurring multi-table question is a candidate for a virtual view that pre-joins the answer.
2. Choose implementation strategy Use database views for stable aggregations that benefit from query optimization. Use computed resources for experimental views that change frequently.
3. Expose view schemas Add virtual views to your schema resource so the model knows they exist. Include column descriptions that explain the business meaning of each computed field.