Migrate persistence layer from MongoDB/Mongoose to PostgreSQL/Drizzle

[f-schnabel]

Problem

The persistence model is increasingly relational, while the current MongoDB/Mongoose schema relies on embedded arrays, duplicated relationships, and application-side joins/aggregation.

Examples:

• contests embed participants and problem references
• profiles duplicate follower/following relationships across documents
• coin balances embed and cap transaction history
• leaderboard loads all submissions/problems into application memory
• notes are globally unique per problem instead of scoped per user
• ownership, uniqueness, and referential rules are mostly application-enforced

MongoDB remains suitable for document-shaped data such as problem test cases and playground files, but PostgreSQL can retain those fields as jsonb while enforcing relational integrity elsewhere.

Proposal

Migrate to PostgreSQL with Drizzle ORM and Drizzle Kit migrations.

Suggested tables:

• problems
• submissions
• bookmarks — unique (user_id, problem_id)
• notes — unique (user_id, problem_id)
• comments
• profiles
• follows — unique (follower_id, followed_id)
• contests
• contest_problems
• contest_participants — unique (contest_id, user_id)
• coin_transactions
• user_balances
• blogs
• playground_snippets
• github_integrations
• user_roles

Use jsonb or arrays only for genuinely document-shaped values, including problem examples/test cases and playground files/test cases.

Work

• Design Drizzle schema, foreign keys, constraints, and indexes
• Add PostgreSQL connection/configuration and versioned migrations
• Add a repeatable MongoDB → PostgreSQL data migration script
• Preserve existing identifiers or define explicit ID mapping
• Replace Mongoose access route-by-route
• Move leaderboard/admin aggregation into SQL
• Make follow, contest membership, submission rewards, and coin ledger writes transactional
• Add ownership constraints and user scoping for notes/bookmarks/submissions
• Add migration and query integration tests
• Update Docker Compose, environment variables, and README
• Define cutover, verification, and rollback steps
• Remove MongoDB/Mongoose after verification

Acceptance criteria

• Existing production data migrates without loss
• All current database-backed features retain behavior
• Relational integrity is enforced by PostgreSQL constraints
• User-owned records cannot cross user boundaries
• Leaderboard/admin queries do not load entire collections into application memory
• Coin/reward and relationship updates are atomic
• Schema can be reproduced from committed Drizzle migrations
• Application has no runtime dependency on MongoDB or Mongoose

[Avinier]

Hi, would love to know more about this, can we continue this convo on any other channel?