🚀 Built a Cross-Database(AI) Recommendation System using FDW + Prisma
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 d...
Source: DEV Community
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️⃣ acti
