[db] get_db commits after the response is sent → create-then-immediately-use 404s (seed publishes 0/12 on first run; k8s job-seed leaves an empty site)

[antosubash]

Summary

A client that creates a row and immediately references it by id in a second
request gets a deterministic 404. The canopy_atlas seed publishes
0/12 pages on its first pass (saved but publish failed (404)); a second,
identical pass publishes all 12. Same for news article attach.

This matters beyond the seed: the official k8s/job-seed runs the seed
once, so a fresh cluster can come up with every page in draft and an empty
public site.

Root cause

simple_module_db.get_db commits after yield (deps.py, ~L46):

async with factory() as session:
    yield session
    if has_writes:
        await session.commit()   # runs in the dependency's exit code

FastAPI runs a yield dependency's exit code after the response has been
delivered. Page/article create() deliberately only flush()es — get_db
owns the commit — so:

1. POST …/pages flushes (page gets an id), returns 201 {id}; the response
   reaches the client before the create request's get_db commit runs.
2. The client immediately POST …/pages/{id}/publish.
3. That request opens a fresh session; the page isn't committed yet →
   get_page → 404.
4. The create's commit finally lands, which is why a second pass (page now
   present) succeeds.

Deterministic locally because the follow-up request reliably beats the
post-response commit.

Reproduce

POST /api/pagebuilder/pages {…}         -> 201 {"id": N}
POST /api/pagebuilder/pages/N/publish   -> 404 Page not found

or run python -m canopy_atlas.seed once against a fresh DB → Seeded 0/12.

Impact

Any fast create-then-use client: seeds, provisioning scripts, integration
tests, and the packaged k8s/job-seed.

Fix options (design call)

1. Commit before the response is sent — e.g. a route/middleware that
   commits the unit of work before Starlette delivers the response, rather than
   in the get_db exit code. Fixes every caller.
2. An atomic create-and-publish endpoint for the workflow the seed needs.
3. Downstream-only mitigation: make the seed resilient (retry / two-pass). This
   is what canopy_atlas's seed does today, but the single-run k8s/job-seed
   doesn't get that for free.

Found upgrading a downstream app (Global Canopy Atlas); its seed already
tolerates this with a two-pass run, but the k8s job does not.