Postgres + MongoDB — MCP data walkthrough

Starting point — what data you have

Before any playbook work, the demo environment already has several configured sources in profiles/local.yaml. This walkthrough focuses on two of them — the ones that hold related CRM data in different systems:

Postgres — warehouse_pg

Schema public, two relational tables that model an internal CRM. Data is small and readable — ideal for MCP sampling.

• users — sales or account owners. Key: user_id (e.g. alex.ae). Attributes: full_name.
• accounts — companies the org tracks. Key: account_name (e.g. Northwind Traders). Attributes: industry, owner_user_id (FK → users.user_id).

At session start the live data looked like this:

MongoDB — mongo_main

Database anythinggraph (from AG_MONGODB_DATABASE), one collection of interest: customers. Documents are schemaless JSON — no foreign keys, no shared UUID with Postgres.

Sample document discovered via sample_source:

The problem we're trying to solve

You have related business data split across two systems that were never designed to join at the database layer:

• Postgres knows who owns which account (owns_account via owner_user_id).
• MongoDB knows who the customers are and which company name they claim (company).
• Neither database alone can answer: “Customer john — which CRM account do they belong to, who owns it, and what industry is it?”

Why a normal query isn't enough

• No shared ID — MongoDB uses _id and string fields; Postgres uses user_id / account_name. You cannot JOIN on a foreign key.
• Cross-database joins don't exist — even if keys matched, Postgres and MongoDB are separate connections; ad-hoc SQL or shell scripts don't scale for agents or governed access.
• Business-key matching — the real join is customer.company = account.account_name (string equality on a company name). That must be declared explicitly and validated against live samples.

What we build in this walkthrough

A federated playbook graph (crm_customer_relationship) that:

1. Declares three entities (crm_user, crm_account, customer) and two relationships (owns_account, belongs_to_account).
2. Maps each entity to its physical store via per-source bindings (Postgres YAML + MongoDB YAML).
3. Aligns MongoDB company with playbook field account_name so the cross-source relationship can resolve Northwind Traders on both sides.
4. Lets agents (and users) run a single query_graph call instead of hand-written multi-hop scripts.

Before you start

1. Start the stack — ./start-all.sh in ag-cli/ (reasoning-service on :8787, MCP on :3334/mcp).
2. Configure sources in profiles/local.yaml: warehouse_pg (Postgres), mongo_main (MongoDB).
3. Set env vars — AG_SQL_DSN, AG_MONGODB_DSN, AG_MONGODB_DATABASE (e.g. anythinggraph).
4. Connect MCP with an admin token (or AG_AUTH_DISABLED=1 for local dev).

Visual flow

Five phases from raw data inspection to a verified cross-source graph query. Exploration (Phases 1–2) uses admin tools that do not require a playbook.

Full sequence (one line)

Phase 1 Check Postgres data

Schema discovered (introspect_source)

Sample data (sample_source)

public.users — 2 rows

public.accounts — 3 rows

Phase 2 Check MongoDB data

Sample document

Phase 3 Create the playbook

Key design decisions from the session:

• Three entities: crm_user, crm_account (Postgres), customer (MongoDB).
• Relationship owns_account: crm_user → crm_account (same source).
• Relationship belongs_to_account: customer → crm_account (cross-source — join on shared account name).
• Customer entity uses account_name as logical identifier (mapped from MongoDB company) so the federated join can match Northwind Traders.

Playbook JSON (saved shape)

{
  "id": "crm_customer_relationship",
  "name": "CRM Customer Relationship",
  "description": "Postgres CRM users and accounts linked to MongoDB customers.",
  "entities": {
    "crm_user": { "identifier": "user_id", "attributes": ["full_name"] },
    "crm_account": { "identifier": "account_name", "attributes": ["industry"] },
    "customer": {
      "identifier": "account_name",
      "attributes": ["name", "age", "region"]
    }
  },
  "relationships": {
    "owns_account": { "from": "crm_user", "to": "crm_account" },
    "belongs_to_account": { "from": "customer", "to": "crm_account" }
  },
  "sources": {
    "crm_user": "postgres",
    "crm_account": "postgres",
    "customer": "mongodb"
  }
}

Phase 4 Author and save bindings

Create one binding file per source key in the playbook sources map. Each file only maps entities from that source — warnings about missing entities on the other source are expected.

Postgres binding — crm_customer_relationship.postgres.yaml

source_id: warehouse_pg

entities:
  crm_user:
    from: users
    id: user_id
    fields: [full_name]

  crm_account:
    from: accounts
    id: account_name
    fields: [industry]

relationships:
  owns_account:
    object: crm_account
    link_column: owner_user_id

  belongs_to_account:
    object: crm_account
    link_column: account_name

MongoDB binding — crm_customer_relationship.mongodb.yaml

source_id: mongo_main

entities:
  customer:
    from: customers
    id: company
    fields:
      name: name
      age: age
      region: region
      account_name: company

relationships:
  belongs_to_account:
    object: crm_account
    link_column: account_name

Phase 5 Verify with query_graph

Cross-source resolution (verified in session)

SQL executed for the cross-source join:

SELECT industry FROM accounts WHERE account_name = 'Northwind Traders' LIMIT 10

Postgres-only checks

owns_account — Alex (alex.ae) owns 2 accounts: Retail + Technology.

Full relationship chain

End-to-end path from MongoDB customer to Postgres account owner:

The same company name appears in both stores — that is what makes the federated relationship work without a shared surrogate key.

Example prompts (copy-paste)

Explore Postgres only

Use MCP introspect_source and sample_source on warehouse_pg.
Show me tables, columns, and a few sample rows from users and accounts.

Explore MongoDB collection

Check my MongoDB source mongo_main. Introspect the database and
sample_source on collection customers.

Full federated onboarding

Using anythinggraph-thin MCP as admin:
1) Inspect warehouse_pg and mongo_main (introspect + sample)
2) Create playbook crm_customer_relationship linking MongoDB company to Postgres account_name
3) propose_binding, test_binding(execute=true), save_binding for postgres and mongodb
4) query_graph to verify customer john links to Northwind Traders and its industry

Troubleshooting

test_binding returns 422 “missing field playbook_id”

The MCP client must include playbook_id in the JSON body sent to /playbooks/{id}/test-binding, not only in the URL. Update MCP or retry after pulling the latest serverCore.ts fix.

save_binding hangs and never returns

A prior runtime bug held a read lock on the profile while reloading the catalog (deadlock). Restart reasoning-service after updating runtime/src/lib.rs.

Cross-source join returns no rows

• Confirm MongoDB company exactly matches Postgres account_name (case and spacing).
• Map company → playbook field account_name in the Mongo binding (id: company, account_name: company in fields).
• Use sample_source on both sides before authoring — do not guess field values.

Agent lists playbooks when asked “what data is in mongo_main?”

Direct the agent to introspect_source + sample_source — no playbook required for source exploration. See MCP authoring guide — Phase 1.