perf: index chat history and cover per-user quota counts - #6340

Pull Request Type

• 🐛 fix (Database query performance)

Relevant Issues

No corresponding issue identified. I understand CONTRIBUTING.md requires a corresponding issue before merge. This is a draft proposal for feedback on whether this read/write tradeoff fits AnythingLLM; it is not merge-ready.

Description

With retained chat history and a per-user daily message cap, counting workspace_chats by user and creation cutoff scans the chat table. This adds one Prisma index and its SQLite migration:

(user_id, workspaceId, thread_id, api_session_id, include, id, createdAt)

The user prefix lets the quota count scan covering index entries for that user instead of all chat payloads. Time is evaluated within that user's entries; this is not a timestamp range seek. For history queries constraining the full user/workspace/thread/API/include partition, the index supplies id DESC LIMIT 20 without a temporary sort. No query predicates or application JavaScript change.

This branch contains only the schema declaration and migration. Reproducible checks, all raw results and explanation are preserved in the fork.

Measurements and tradeoffs

Baseline: upstream 049d721f900f394c1415ebd7db69a552194c9736. Twelve synthetic matched SQLite pairs on one Windows host, three pairs per size/cache group, 31 measured repetitions after three warm-ups for reads/sequences; no discarded pairs.

At 100k–1M rows across 8 MiB and 512 MiB cache settings:

• Busy-user quota median time decreased 92.8–98.5%; measured CPU per correct quota result decreased 91.5–98.6%.
• A constructed quota + history + single insert + commit sequence took 54.2–96.7% less time.
• Sparse-history reads took 87.6–95.1% less time.
• Busy history took 6.8–33.4% more time, but the absolute median increase was below 0.1 ms in every pair; neither history plan required a temporary sort.

This has meaningful costs:

• Database pages grew 8.90–9.17% at those sizes.
• All six million-row sequences omitting the quota count were 1.1–27.3% slower.
• At 1M / 8 MiB, the median of trial median times for a 100-row insert transaction rose 6.023 → 22.989 ms; median trial p95 rose 7.992 → 31.002 ms.
• At 1M / 512 MiB, bulk-write tails were especially noisy and worse: median trial p95 207.084 → 1,271.740 ms. These results are retained.

These results support a bounded query improvement, not a universal application improvement. Bulk-write overhead, alternate query shapes, production concurrency and migration locking need further evaluation before adoption. There is no measured hosting/LLM bill saving or accepted-user-outcome cost claim.

Validation

• Prisma 5.3.1 schema validation passed; generated migration DDL matched.
• All 41 migrations applied to a disposable database.
• The linked actual Prisma-client check passed 168 count/history comparisons over user/workspace/thread/API/include partitions, NULLs, cutoff boundaries, ordering/limit, index removal/reapplication and insert/update/delete. Full-row, reopen, integrity and foreign-key checks passed.
• All 2,976 timed SQL reads and 1,488 timed constructed sequences matched fixture expectations; persisted rows matched between arms.
• Public standalone benchmark smoke check passed; public summary script reproduced all aggregate results. The full published run used the same functions before extraction into the standalone script.
• Performance runtime: Python SQLite 3.43.1. Prisma correctness runtime: SQLite 3.41.2. Actual Prisma/application performance, full application tests and Docker build were not run.
• git diff --check passed. The MIT license and upstream attribution remain unchanged.

Developer Validations

• I ran yarn lint from the root of the repo & committed changes
• Relevant documentation has been updated in the linked fork
• I have tested the proposed database change within the scope above
• Docker build succeeds locally