Phantom-success writes: muse hub issue create silently lost a row, letting a later create reuse its number

Summary

muse hub issue create returned a successful response (issue number, issue ID, full body) for the aaronrene/knowtation clone-truncation investigation ticket, but that row never actually persisted. When a second, unrelated issue was created later, _next_issue_number computed the same number the first "successful" create had already claimed, and the second issue silently took over that slot — with no error, no conflict, and no indication either time that anything was wrong.

Evidence

• Audited the full issue list for gabriel/musehub (182 issues, paginated via the wire API directly since muse hub issue list --limit doesn't page past 20 through the CLI — separate minor gap, not the focus here). Result: exactly one row per number, 1 through 182, no gaps, no duplicate numbers. If this were a classic read-then-write race in _next_issue_number (SELECT MAX(number) then INSERT with max+1, no locking — see musehub/services/musehub_issues.py::_next_issue_number), the failure mode would be two rows sharing the same number — that is not what was found.
• Since there is exactly one row at #182 (holding only the second ticket's content) and no duplicate-number row anywhere, the first create's row must never have been committed at all, despite the CLI call returning a normal success envelope with a real issueId, number: 182, and the full submitted body.

Suspected root cause

A "phantom success" write: the API/service layer can return a 200/success response (and a fully-formed IssueResponse) before or without the underlying DB transaction actually committing — so the caller (and the CLI) has no way to know the write didn't stick. musehub_issues.create_issue does session.add(issue) + await session.flush() + await session.refresh(issue), which makes the row visible within that session and lets a refresh() succeed, but if the outer request-scoped transaction is never committed (or is silently rolled back after the response is already serialized), the caller sees success while nothing durable happened.

This looks like the same class of bug as #182 (the muse hub proposal merge state-persistence failure, where a merge's DB write to state/merge_commit_id/merged_at didn't persist despite the merge itself succeeding). Both bugs share the same shape: a write path returns success based on an in-session view of the data, without confirming the transaction actually committed to the database the caller will read from next. This may point to a systemic issue in musehub's DB session/transaction lifecycle (e.g. request-scoped session commit handling, or connection-pooling/pgbouncer transaction-mode interaction with implicit rollbacks) rather than two unrelated one-off bugs.

Suggested investigation

1. Audit how the FastAPI dependency that provides AsyncSession (get_db or equivalent) commits/closes sessions on the request lifecycle — confirm every write route actually calls (or relies on middleware to call) session.commit(), and that no exception path can return a 200 after a swallowed commit failure.
2. Check whether staging's DB access goes through any connection pooler (pgbouncer, RDS proxy) in transaction-pooling mode, which can silently discard session-level state that plain asyncpg/psycopg assumes persists across statements in the same "session."
3. Reproduce directly: create an issue, capture the returned issueId, then immediately re-query via a separate DB connection/session (not the same one that served the create) to confirm the row is actually there. Do the same for merge_proposal. If either fails to show up under a fresh connection, that confirms the shared root cause.
4. Once confirmed, apply the fix to whatever the shared mechanism is (session lifecycle / commit handling), not just to create_issue and merge_proposal individually — audit other write paths (comments, labels, collaborators, releases) for the same exposure.
5. Add a regression test pattern: for any write endpoint, call it, then read the result back through a fresh session (not the one that performed the write) and assert it's visible. Apply this pattern at least to create_issue and merge_proposal as the two known-affected paths.

Related

• #182 — muse hub proposal merge state-persistence + stale-merge-base bugs (same failure shape).
• #183 — the aaronrene/knowtation investigation ticket that was lost to this bug; content was manually recreated from the original report and reassigned to aaronrene.

Mirrored from staging#184.