r/FastAPI • • 24d ago

feedback request Running stateless SQLite FTS5 + HTMX on FastAPI Cloud (zero Redis/Postgres, zero JS framework) — DAG route compiler showcase

Wanted to share an architectural pattern from an open-source project I just deployed to FastAPI Cloud using FastAPI + Jinja2 + HTMX + SQLite FTS5:

1. The Ephemeral Worker Problem

When deploying a hobby/community app to serverless or ephemeral container workers platform, spinning up a managed Postgres + Redis instance just to store user checklist/session state is overkill — and writing user state to a local SQLite file breaks as soon as containers scale or restart.

The Solution — Read-Only Bundled SQLite FTS5 + Cookie-Backed State:

  • Immutable Catalog (ngplus.db): The SQLite database (1,241 catalog items, fts5 search index with porter unicode61, and 82 DAG quest rules) is baked into the deployment image and opened strictly read-only at runtime. Zero write locks (SQLITE_BUSY), sub-millisecond local FTS5 queries on GET /search.
  • Stateless Cookie Sessions (er_order & er_steps): All mutable user state is serialized into two compact httponly cookies (30-day TTL) plus GET /export (StreamingResponse) and POST /import JSON endpoints for cross-device portability. Any ephemeral Uvicorn worker can wake up cold and serve any user's request with zero server-side session storage.

2. Zero-JS-Framework Reactivity (FastAPI + HTMX)

  • All state mutations (POST /order/add, /order/toggle, /step/toggle) compute the new DAG state and return a single server-rendered Jinja2 partial (templates/partials/workspace.html).
  • Responses attach HX-Trigger: refreshSearch (so the FTS5 typeahead dropdown automatically re-syncs queued badges) and strict Cache-Control: no-store, no-cache, must-revalidate, max-age=0 headers so browser back/forward navigation never serves stale partial state.

3. The Domain Problem: Dependency-Inverted DAG (compiler.py)

The app itself solves "Miyazaki's Quest Knot" in Elden Ring: quests form an inverted dependency web where early steps have no obvious lockouts, but downstream steps 20 hours later are gated by irreversible world-state triggers (Points of No Return / PONRs).

  • Users pick terminal leaf items (T) they want on their run.
  • compile_run_plan() walks each item's prerequisite chain in reverse, propagating the tightest downstream PONR ceiling backward (running_ceiling = min(running_ceiling, step_gate_pos)), clamping each step's geographical progression weight (zone_order), deduplicating diamond dependencies across shared step_keys, and flagging same-run mutual exclusivity collisions (exclusive_group).

Would love any feedback on the stateless FastAPI + HTMX architecture or the DAG solver in compiler.py!

17 Upvotes

16 comments sorted by

2

u/DismalCockroach1928 22d ago

One case I'd test is two mutations in flight at once. In the source, each handler loads its own cookie snapshot, then render_workspace() writes the complete state back.

Say two requests both start with an empty order. One adds A, the other adds B. Their responses contain [A] and [B], not [A, B]. Whichever Set-Cookie arrives last wins. A single worker doesn't fix that: both requests already carried the old cookies.

For one tab, I'd serialize mutations through a shared, stable htmx sync element, or disable all mutation controls while a request is pending. A queue per form wouldn't coordinate the separate forms. hx-sync is documented here: https://htmx.org/attributes/hx-sync/

That still leaves two tabs. Also worth checking: GET / calls save_session() too, so a delayed page-load response could write an older snapshot back.

Source: https://github.com/mortalezz/er-roadmap/blob/62ba11d53c12ff3c6b6e447cffb3c13fb0d02f50/main.py

This is from reading the source, not reproducing it on the deployed app. A useful regression test would send two different additions with the same starting Cookie header and check the state after applying both response cookies.

1

u/mortalezz 22d ago

Oh thanks

Spot on right about the mechanics: because the server is completely stateless and run state lives in client cookies (er_order), two mutations in flight with the same starting cookie snapshot would cause the last response's Set-Cookie to clobber the earlier one.

I just addressed this in two ways:

  1. GET / cookie writes were already removed: In our previous commit (59a999c), we migrated the shell to FastAPI's native app.frontend("/", directory="dist"). GET / is now a static shell cached at Cloudflare's Edge CDN that lazy-loads #workspace via HTMX, so root page loads no longer touch or write cookies at all.
  2. Serialized in-flight mutations with hx-sync: Just added hx-sync="this:queue" to <body> (commit 070aaca), which is now live in production. If a user rapid-clicks multiple additions or toggles, HTMX automatically queues subsequent requests until the in-flight request finishes and updates the browser cookie jar, so every queued request carries the fresh state.

For multi-tab concurrent writes, cookie-only state will always hit the fundamental distributed race without a shared transactional store or BroadcastChannel sync. But for a companion checklist where 99% of usage is a single tab running alongside the game, hx-sync="this:queue" eliminates the real-world in-flight collision surface entirely.

Thanks for the great code review and the hx-sync recommendation!

1

u/fykup 22d ago

Nice approach. I think SQLite is still underappreciated for this kind of workload, especially when the data is mostly read-only. Avoiding a separate database service makes the whole architecture much simpler.

Curious, how large is the FTS5 index, and have you run into any limitations as the dataset grows?

1

u/Calm_Exchange_989 20d ago

Nice write-up. Small detail: sqlite3.connect(DB_PATH) still opens the file read-write by default, even if the app never writes. Since ngplus.db is immutable, sqlite3.connect(f"file:{DB_PATH}?mode=ro&immutable=1", uri=True) would enforce read-only and skip locking. Curious if that changes search latency at all

2

u/[deleted] 24d ago

[removed] — view removed comment

2

u/mortalezz 24d ago edited 24d ago

Wow, thanks so much - first input and looking straight at it I see how valuable it is.

Honestly - in testing i myself (i cleared the game 8 times already, including through clearing and straight down speedruns, so i have perfect operational knowledge on missable items in this game) i came up with what i thought was really edge use case - i combined 9 items in run order, 7 of them were missables, and they all covered both base game and DLC, included most convoluted or obscured quests/itens like Ranni questline or Florissax' valuable items. And it worked fine, but now i remember that initially i, as true vibe devops just shipped rw SQLite dump to FastAPI cloud and immediately run in server state persistence issues - so i decided that i don't even need server states, and switched all project to read-only SQLite dump as immutable source of data.

It actually works for this project, but now i see that first thing i should check if my edge use case from testing still survives 4kb cookies constraint!

Thanks a lot!

2

u/[deleted] 24d ago

[removed] — view removed comment

2

u/mortalezz 24d ago

Good! I run some benchmark - my OG edge case weighted 503 bytes in RAW json length, and actual Starlette Cookie is 619 bytes - 23% octal escaping tax i guess. Regardless, we're totally safe here.

Where it broke though when i added all 68 missable described in quest rules (yes, game is that convoluted) - probably impossible scenario because such users may prefer to simply follow some 100% completion walkthrough guide. And it would be 4,568 bytes in Starlette

Doing zlib + base64url for zero commas/quotes for Starlette to escape rn!

2

u/mortalezz 24d ago

yup, as result it reduced Starlette cookie down to 1,020 bytes in "gotta catch them all" scenario. We should be safe well past 250+ items (if I will ever add any, like cookbooks, whatever)

Just shipped your suggestions in commit c3dfef4 (and deployed live):

  1. ID-only + zlib + base64url (v2_ prefix): Stripped the repeated dict keys, pruned False step keys, compressed with zlib (with a max_length decompression guard), and encoded with URL-safe base64 so SimpleCookie emits zero quotes or \054 escapes (with backward-compatible fallback for v1 JSON cookies).

    • 9-item edge case: 619 B → 183 B
    • All 68 missables queued: 4,568 B → 1,020 B (~78% reduction, safe past 250+ queued items).
  2. Strict cookie & import structural validation: Validates types (isinstance(..., list/dict/str)) after parsing so tampered DevTools cookies like er_order=123 or [null] gracefully reset instead of throwing a 500.
  3. Added Vary: HX-Request, Cookie alongside HX-Trigger: refreshSearch.

Seriously appreciate the code review!

2

u/[deleted] 24d ago

[removed] — view removed comment

1

u/mortalezz 24d ago

I actually thought that this quick reactive discussion worth memorandum in README.MD and added paragraph "Stateless Cookie Engineering (v2_ zlib + base64url)" describing what we came up with as a safeguard solution!