DEV Community

Karan
Karan

Posted on

🚀 Built a Cross-Database(AI) Recommendation System using FDW + Prisma

#architecture #sql #prisma #postgres

Recently worked on an interesting backend problem — sharing the approach in case it helps others working with multi-DB systems 👇

🧩 Problem

We had two separate databases:

  • Core DB → transactional data (activities, participants, items)
  • Profile DB → user/entity master data + metadata

We needed:
👉 A unified dataset for ranking/recommendation
👉 Without duplicating data across databases

💡 Solution: PostgreSQL FDW

Used Foreign Data Wrapper (FDW) to query Profile DB directly from Core DB.

CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_profile_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host 'host',
  dbname 'profile_db',
  port '5432'
);
Enter fullscreen mode Exit fullscreen mode

🔗 Foreign Tables

Mapped remote tables into local DB:

  • fdw_schema.entities
  • fdw_schema.entity_details
  • fdw_schema.entity_context

👉 Important: Convert enums → text to avoid cross-DB type issues.

🧠 Data Layer Design

Built 4 layered views:

1️⃣ entity_profile_view

  • Entity + metadata + classifications
  • Handles JSON structures (like category mappings)

2️⃣ activity_base_view

  • Core activities + configurations + rules
  • Pivot configs using: 
BOOL_OR(...)  -- instead of MAX(boolean)
Enter fullscreen mode Exit fullscreen mode

3️⃣ item_aggregation_view

  • Extracts dynamic JSON fields
  • Aggregates quantities, values, etc.

4️⃣ entity_activity_master_view (🔥 final)

  • Joins everything
  • Output = activity × entity dataset

⚙️ Challenge: Prisma + ENV in SQL

Prisma migrations don’t support .env inside SQL ❌

✅ Fix

Used a custom Node script to inject env values:

const finalSQL = sql
  .replace(/__HOST__/g, process.env.DB_HOST)
  .replace(/__DB__/g, process.env.DB_NAME);
Enter fullscreen mode Exit fullscreen mode

Then executed via pg client.

🏗️ Final Architecture 

Profile DB
   ↓ (FDW)
Core DB (views)
   ↓
entity_activity_master_view
   ↓
Ranking / ML / APIs
Enter fullscreen mode Exit fullscreen mode

⚡ Key Learnings

  • FDW is powerful for real-time cross-DB joins
  • Always cast enums → text
  • Use BOOL_OR instead of MAX(boolean)
  • Prisma needs custom migration handling
  • Views help create a clean analytical layer

🎯 Use Cases

  • Recommendation systems
  • Ranking engines
  • Analytics without ETL pipelines

🔥 Takeaway

You don’t always need:
❌ data duplication
❌ complex ETL pipelines

Sometimes:
👉 FDW + smart SQL views = a clean and scalable solution

If you're working on distributed data systems or recommendation problems, this approach can be really useful.

Happy to discuss more 🤝

postgres #nestjs #prisma #backend #sql #architecture

Top comments (0)