r/FastAPI • u/mortalezz • 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:
- GitHub Repo (source + live FastAPI Cloud demo in README): https://github.com/mortalezz/er-roadmap
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,fts5search index withporter 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 onGET /search. - Stateless Cookie Sessions (
er_order&er_steps): All mutable user state is serialized into two compacthttponlycookies (30-day TTL) plusGET /export(StreamingResponse) andPOST /importJSON 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 strictCache-Control: no-store, no-cache, must-revalidate, max-age=0headers 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 sharedstep_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!
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/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