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'
);

🔗 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)

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);

Then executed via pg client.

🏗️ Final Architecture

Profile DB
   ↓ (FDW)
Core DB (views)
   ↓
entity_activity_master_view
   ↓
Ranking / ML / APIs

⚡ 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