[BUG] Backend Allows Creating Markets with Identical Questions

[Hahfyeex]

Type: BUG
Severity: Medium
Component: backend/src/routes/markets.js

Context

The POST /api/markets route does not check for duplicate market questions. An admin can create two markets with the exact same question, confusing users about which market to bet on and splitting liquidity between duplicates. Duplicate markets also waste oracle resources and pollute the discovery feed.

Root Cause

There is no uniqueness check on the question field in the POST /api/markets route. The database schema has no unique constraint on the question column.

Fix Guide

1. Before inserting a new market, query: SELECT id FROM markets WHERE LOWER(TRIM(question)) = LOWER(TRIM()) AND deleted_at IS NULL.
2. If a match is found, return 409 Conflict with error: A market with this question already exists (market ID: [id]).
3. Add a partial unique index on the markets table: CREATE UNIQUE INDEX idx_markets_unique_question ON markets (LOWER(TRIM(question))) WHERE deleted_at IS NULL.
4. Add a unit test that attempts to create two markets with the same question and verifies the second returns 409.

Guidelines

• Key requirement: Case-insensitive Duplicate Check / Partial Unique Index / 409 Conflict Response / Existing Market ID in Error.

Definition of Done

• Creating a market with a duplicate question returns 409 with the existing market ID.
• The check is case-insensitive and trims whitespace.
• Partial unique index prevents duplicates at the database level.
• Unit test covers exact duplicate, case-variant duplicate, and unique question scenarios.
• Test coverage > 95%.

PR and Checkout

git checkout -b fix/duplicate-market-question
git add .
git commit -m "fix: prevent duplicate market creation with case-insensitive question uniqueness check"
git push origin fix/duplicate-market-question

Open a PR against main and include the issue number in the PR description.