MEDIUM: POST /recipes/saved/collections returns 500 — missing member_count in INSERT RETURNING #94

New issue

Closed

opened 2026-04-18 09:02:56 -07:00 by pyr0ball · 0 comments

pyr0ball commented

2026-04-18 09:02:56 -07:00

Owner

Summary

Creating a recipe collection always returns 500. The create_collection store method returns a raw INSERT ... RETURNING * result, but CollectionSummary requires a member_count field that only exists in the get_collections aggregate query.

Root Cause

app/db/store.py:

def create_collection(self, name, description):
    return self._insert_returning(
        "INSERT INTO recipe_collections (name, description) VALUES (?, ?) RETURNING *",
        (name, description),
    )

RETURNING * gives back id, name, description, created_at, updated_at — no member_count.

app/models/schemas/saved_recipe.py:

class CollectionSummary(BaseModel):
    id: int
    name: str
    description: str | None
    member_count: int  # ← required, not in INSERT result
    created_at: str

Fix

After inserting, fetch with the count query:

def create_collection(self, name: str, description: str | None) -> dict:
    self.conn.row_factory = sqlite3.Row
    cur = self.conn.execute(
        "INSERT INTO recipe_collections (name, description) VALUES (?, ?) RETURNING id",
        (name, description),
    )
    new_id = cur.fetchone()["id"]
    self.conn.commit()
    return self._fetch_one(
        """SELECT rc.*, COUNT(rcm.saved_recipe_id) AS member_count
           FROM recipe_collections rc
           LEFT JOIN recipe_collection_members rcm ON rcm.collection_id = rc.id
           WHERE rc.id = ?
           GROUP BY rc.id""",
        (new_id,),
    )

Verified

POST /api/v1/recipes/saved/collections returns HTTP 500.

## Summary Creating a recipe collection always returns 500. The `create_collection` store method returns a raw `INSERT ... RETURNING *` result, but `CollectionSummary` requires a `member_count` field that only exists in the `get_collections` aggregate query. ## Root Cause `app/db/store.py`: ```python def create_collection(self, name, description): return self._insert_returning( "INSERT INTO recipe_collections (name, description) VALUES (?, ?) RETURNING *", (name, description), ) ``` `RETURNING *` gives back `id, name, description, created_at, updated_at` — no `member_count`. `app/models/schemas/saved_recipe.py`: ```python class CollectionSummary(BaseModel): id: int name: str description: str | None member_count: int # ← required, not in INSERT result created_at: str ``` ## Fix After inserting, fetch with the count query: ```python def create_collection(self, name: str, description: str | None) -> dict: self.conn.row_factory = sqlite3.Row cur = self.conn.execute( "INSERT INTO recipe_collections (name, description) VALUES (?, ?) RETURNING id", (name, description), ) new_id = cur.fetchone()["id"] self.conn.commit() return self._fetch_one( """SELECT rc.*, COUNT(rcm.saved_recipe_id) AS member_count FROM recipe_collections rc LEFT JOIN recipe_collection_members rcm ON rcm.collection_id = rc.id WHERE rc.id = ? GROUP BY rc.id""", (new_id,), ) ``` ## Verified `POST /api/v1/recipes/saved/collections` returns HTTP 500.