Senior PostgreSQL Database Architect – MongoDB to PostgreSQL Migration

# Senior PostgreSQL Database Architect – MongoDB to PostgreSQL Migration ## Project Overview We are looking for an experienced **PostgreSQL Database Architect / Senior Backend Database Engineer** to help us migrate our existing production application from **MongoDB to PostgreSQL**. At the same time, we are restructuring our application into a new **monorepo architecture** with: * Client/Web application * Admin application * Single backend API * Shared packages and types * PostgreSQL database * Prisma ORM We already have an existing MongoDB database and application. Your job is **not simply to convert MongoDB collections into SQL tables**. We want someone who can analyze the existing data model, understand the application requirements, identify problems, and design a **clean, normalized, scalable and production-grade PostgreSQL database architecture**. ## What You Will Do ### 1. Analyze Existing MongoDB Database You will first analyze our existing MongoDB database, including: * All collections * Document structures * Existing indexes * References between collections * Embedded documents * Duplicate or redundant data * Existing relationships * Current API and database usage We need to understand how the existing MongoDB model works before designing the new database. ### 2. Design the New PostgreSQL Database Design a proper relational database architecture rather than doing a one-to-one collection-to-table conversion. You will define: * Tables * Columns * Primary keys * Foreign keys * Relationships * One-to-one relationships * One-to-many relationships * Many-to-many relationships * Enums * Unique constraints * Check constraints where appropriate * Nullable and non-nullable fields * Timestamps * Soft-delete strategy where appropriate The database should be designed for long-term maintainability and scalability. ### 3. Production-Grade Database Design The database needs to be optimized for real production usage. You should consider: * Proper PostgreSQL data types * Normalization versus denormalization * Index strategy * Composite indexes * Query performance * Filtering * Sorting * Pagination * Search requirements * Foreign-key performance * Transaction requirements * Data integrity * Concurrency * Future data growth * Avoiding N plus 1 query problems * Efficient API and database interaction We expect you to explain important architectural decisions and not simply create tables based on the existing MongoDB structure. ### 4. Prisma ORM The new application will use **Prisma ORM**. You will: * Create the Prisma schema * Define all relationships correctly * Configure appropriate indexes and constraints * Create Prisma migrations * Ensure the schema is maintainable * Consider Prisma query behavior and performance * Structure the database package appropriately for our monorepo ### 5. MongoDB to PostgreSQL Migration You will help define the complete migration strategy. The process should include: MongoDB Collection ↓ Data Mapping ↓ PostgreSQL Tables ↓ Relationships and Foreign Keys ↓ Data Transformation ↓ Validation We need a clear mapping such as: MongoDB Collection → PostgreSQL Table → Column Mapping → Data Type → Relationship → Required Transformation You should also identify: * Missing data * Invalid data * Duplicate records * Inconsistent structures * ID conversion issues * Relationship problems * Data that needs transformation * Migration dependencies * Data integrity risks ### 6. ERD and Database Documentation A complete **ERD, Entity Relationship Diagram, is required**. The ERD should clearly show: * Tables * Columns * Primary keys * Foreign keys * Relationships * Cardinality We also need documentation explaining important database design decisions. ### 7. New Monorepo Architecture Our target architecture will look approximately like: monorepo/ * apps/ * web/ — Client application * admin/ — Admin application * api/ — Single backend API * packages/ * database/ — Prisma and PostgreSQL * types/ — Shared types * other shared packages The database architecture needs to support both the **client application and admin application through the same API**. We want the database layer to be cleanly separated and reusable across the monorepo. # Expected Deliverables ## Database Architecture * Complete PostgreSQL database design * Complete ERD * Table and relationship definitions * Primary and foreign key strategy * Constraints * Enum strategy * Index strategy ## Prisma * Complete Prisma schema * Prisma migrations * Proper relations * Production-ready database configuration ## Migration * MongoDB to PostgreSQL mapping document * Migration strategy * Migration scripts or implementation guidance * Data transformation strategy * Handling of existing IDs and relationships * Data validation and reconciliation strategy ## Documentation * Database architecture documentation * Explanation of important design decisions * Explanation of indexes and performance considerations * Migration risks and recommendations * Identified problems in the existing MongoDB model * Recommended improvements # Required Experience You should have strong experience with: * PostgreSQL * Relational database architecture * Database normalization * Database indexing * SQL query optimization * MongoDB * MongoDB to PostgreSQL migrations * Prisma ORM * Backend and API architecture * Production database design Experience with **Node.js, NestJS and TypeScript** is a strong plus because our backend uses this ecosystem. Experience with **monorepo architecture** is also a plus. # Important: We Are Looking for a Database Architect This is **not a basic CRUD implementation project**. We are specifically looking for someone who can think about: **What should the database architecture look like for the next several years?** rather than: **How can I convert this MongoDB collection into a PostgreSQL table?** The existing MongoDB structure may contain design limitations, inconsistencies or relationships that should not simply be copied into PostgreSQL. We expect you to **challenge the existing model when necessary**, identify potential problems and recommend better solutions. # What We Would Like to See in Your Proposal - Please provide an example of a similar database architecture or migration project you have worked on. If possible, please include examples of **ERDs, database architectures, or migration projects** you have previously worked on. # Project Goal The final result should be a **clean, scalable, maintainable and production-ready PostgreSQL database** that can serve our new monorepo architecture through a single API. We want to establish the correct database architecture **before starting the full migration and application development**.