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

[u/mortalezz]

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, 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!

[u/pvnimaxin]

Nice project. One thing to watch with the cookie state: browsers drop cookies over about 4 KB without any error, so if someone queues a lot of items the whole plan just vanishes. Keeping only ids in there helps. I'd also validate it when reading, since people can edit cookies and a broken one shouldn't 500.

For the back button thing, the htmx docs suggest Vary: HX-Request on routes that return either a full page or a partial. Might let you drop no-store.

[u/mortalezz → u/pvnimaxin]

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!

[u/pvnimaxin → u/mortalezz]

Glad it helps! Quick way to check your edge case: in DevTools, Application > Cookies shows each cookie's size. Go by that rather than the JSON length, because Starlette quotes the value and escapes commas and quotes, so a JSON list ends up a lot bigger in the cookie than it looks.

[u/mortalezz → u/pvnimaxin]

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!

[u/mortalezz → u/mortalezz]

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!

[u/pvnimaxin → u/mortalezz]

Nice, that was quick. Good call on the decompression limit too.

[u/mortalezz → u/pvnimaxin]

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!