🎯 The Goal
Build a seamless bridge between non-engineering contributors managing data in Notion and engineers who need that data formatted for database insertion.
🌟 The Challenge
- Values like "Japan" or "Tokyo" don't match the foreign keys in different tables.
🛠 Tech Stack Overview
Notion: Used as the collaborative data entry platform for non-engineers.
MCP Server: Bridges Notion and external tools, enabling structured data retrieval via API.
Cursor Agent: An AI-powered agent that automates data transformation, semantic inference, and prompt execution.
TypeScript: Ensures type safety and robust data handling throughout the automation scripts.
🌟 The Solution
🚀 Let's ask Cursor Agent to infer the semantic data to be the foreign keys in one prompt!
🔗 Step 1: Integrate Notion MCP Server to Retrieve Structured Event Data
Non-engineers created and updated event entries directly in Notion—no devs required.
To extract and structure that data:
✅ I used the Notion MCP Server via Cursor Agent.
// .cursor/mcp.json
{
"mcpServers": {
"notionApi": {
"command": "npx",
"args": [
"-y",
"@notionhq/notion-mcp-server"
],
"env": {
"OPENAPI_MCP_HEADERS": "{\"Authorization\": \"Bearer {YOUR_NOTION_API_KEY}\", \"Notion-Version\": \"2022-06-28\" }"
}
}
}
}
This allows you to retrieve structured data from Notion in AI Agent's context.
🧠 Step 2: Let Cursor Agent to Infer Semantic Foreign Keys
Ask AI Agent to fetch the master data from the API with curl command in prior, and to infer the semantic foreign keys in each row.
The data in Notion
// countries table
-----------------
| id | name |
-----------------
| 1 | Japan |
| 2 | USA |
| 3 | Germany |
-----------------
// events table
-----------------------------------------------
| id | name | location | country_id |
-----------------------------------------------
| 1 | Tech Expo | Tokyo, JPN | null |
| 2 | Art Summit | LA, USA | null |
| 3 | Music Fest | Berlin, DE | null |
-----------------------------------------------
Asking the Agent to infer the country_id in each row from the semantic location names to match the foreign keys from data you already fetched via API.
Ultimately the data should look like this in the Agent's context 👏
-----------------------------------------------
| id | name | location | country_id |
-----------------------------------------------
| 1 | Tech Expo | Tokyo, JPN | 1 |
| 2 | Art Summit | LA, USA | 2 |
| 3 | Music Fest | Berlin, DE | 3 |
-----------------------------------------------
🧠 Step 3: Ask Agent to convert the data to the payload for the events table
Finally Ask the Agent to convert the data to the payload for the events table following the schema defined in the schema.json file you put.
Voila, you can now get the payload ready to be inserted into the events table!
// ./output/events.json
{
"name": "Tech Expo",
"location": "Tokyo, JPN",
"country_id": 1
},
{
"name": "Art Summit",
"location": "LA, USA",
"country_id": 2
},
{
"name": "Music Fest",
"location": "Berlin, DE",
"country_id": 3
}
Prompt I used
// /prompt/create-events.md
### Prompt Instructions
## For Events
- Retrieve the rows in the Events DB, sorted by ascending startDateTime in Notion via notionGeodeworkApi in MCP settings.
- Generate the payload for the events table according to the schema defined in @schema.ts.
- Use the following API to fetch country data:
API Endpoint: {NEXT_PUBLIC_SUPABASE_URL}/rest/v1/countries
API Key: {NEXT_PUBLIC_SUPABASE_ANON_KEY}
Sample curl:
curl -H "apikey: <API_KEY>" "<API_ENDPOINT>"
- Replace the `country_id` in each row with the foreign key from the country table.
I stored the prompt in the prompt/create-events.md file so that everybody can use and reproduce the process easily.
📦 Conclusion
Successfully extracted the structured data from Notion and converted it to the payload for the events table with the help of AI Agent.
I actually integragted the process to insert the data with drizzle-kit and supabase all in one go, but I'll write about it in another blog post!
Top comments (1)
Using AI agents to bridge Notion and a database via MCP Server feels like true data alchemy. Super innovative!