Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

Database: heavy operations and concurrency under D1

Open
  • Is something wrong?
#3 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Refactor
Clarity
Needs clarification
Activity status
Active
Tech stack
sqlite, typescript
Domain
backend, databases, performance

Research direction

Start with the per-pass checklist and the named hotspots, including packages/engine/src/recommend/retrieve.ts, packages/engine/src/analytics/queries.ts, apps/web/src/app/looks/[id]/page.tsx, and the listed server actions. Run EXPLAIN QUERY PLAN for new queries and inspect select-then-insert paths against packages/db/src/client.ts and packages/db/src/sql.ts. Done means the affected routes and writes satisfy the checklist without exceeding D1's transaction or parameter constraints.

Written by the indexing model from the issue text.

Description

performance standing

Standing issue — do not close. D1 is remote SQLite with no transactions, so every new page or server action can quietly add a
table scan or a read-then-write race.

This one stays open permanently: do not close it — not after a fix lands, not when the
checklist comes back clean. It is re-audited periodically and every pass is recorded in the log
at the bottom.

The bar

  • No interactive request issues a query whose row count grows with the catalog, unless that is
    bounded and deliberate.
  • No query pattern is N+1 where a join or a single inArray would do.
  • Every read-then-write is correct without BEGIN/COMMIT. packages/db/src/client.ts:20 is the
    constraint: db.transaction() is rejected by D1, and no statement may carry more than 100 bound
    parameters (packages/db/src/sql.ts).
  • Anything expressible as one statement — an upsert, onConflictDoNothing, a where guard — is one
    statement.

Known heavy spots (832a1a6)

  • Vector retrieval. packages/engine/src/recommend/retrieve.ts:334 joins
    products ⋈ brands ⋈ product_vectors and orders by a 64-term dot product built at query time
    (cosineExpr, packages/db/src/vectors.ts:43). No index can serve that ordering, so SQLite must
    evaluate every row surviving the prefilter — over a 100k catalog with a weak prefilter, that is
    the whole table, once per recommendation. Cost consequences tracked separately in the D1 cost issue.
  • Trends analytics. packages/engine/src/analytics/queries.ts has 38 .select( call sites, and
    /trends renders with no revalidate and no cache, so one page view re-runs the set.
  • Look detail. apps/web/src/app/looks/[id]/page.tsx issues 7+ selects per view.
  • Social panels. apps/web/src/components/social/data.ts has 14 selects.
  • Nothing in the app sets revalidate or uses unstable_cache. React.cache appears only to
    dedupe within one request (e.g. apps/web/src/app/p/[id]/page.tsx:38), which is correct but does
    nothing across requests.

Known read-then-write races (832a1a6)

Each is a select followed by a conditional insert with no transaction: two concurrent requests
can both observe "absent" and both insert.

  • apps/web/src/app/p/[id]/page.tsx:78 — recommendation-click dedupe, meant to be one click per
    user × session × product. Two fast navigations double-count, which feeds Engine 03 directly.
  • apps/web/src/server/actions/looks.ts:209 — existence check before insert.
  • apps/web/src/server/actions/together.ts:89 — per-participant loop, one round trip per participant.
  • apps/web/src/server/actions/purchase.ts:50 → packages/engine/src/social/purchases.ts:13 — one
    select + insert per bag line, no idempotency key. A double-submitted checkout writes duplicate
    purchases. Partial failure mid-loop is already handled explicitly; duplication is not.

The bag is cookie-backed (apps/web/src/server/bag.ts), so its merge races between tabs rather than
in the database — same class of problem, different store.

Per-pass checklist

  • For each route touched since the last pass, count the statements it issues per render.
  • EXPLAIN QUERY PLAN any new query over products, product_vectors, interactions or
    feedback_events; flag a SCAN that no WHERE covers.
  • Every new select-then-insert either has a unique index plus onConflictDoNothing, or a written
    reason why a duplicate is harmless.
  • No statement exceeds 100 bound parameters.
  • Any new loop that queries per item is rewritten as one inArray or join.

Log

Date Commit Pass Notes
2026-09-19 832a1a6 baseline Heavy spots and races enumerated. Nothing changed.
2026-09-19 73bbc4f auto clean — the deferred refinement adds one update on intent_sessions keyed by primary key inside after(); sessionId is a fresh nanoid per request, so no cross-request race and no read-then-write. intent/jev-parser.ts resolves from static @lookline/catalog constants, never the DB. Widening provider to include jev needs no migration — drizzle/0000_init.sql:98 declares plain TEXT with no CHECK.
2026-09-19 c86a24d auto clean — TRUST_MIN 0.3 → 0.1 is applied in-process at recommend/context.ts:211 over relationship rows already loaded, so no query shape changes. The three social-channel queries stay bounded: socialChannel guards trusted.length === 0, ids is capped at TRUSTED_MAX (20) so inArray carries ≤ 20 bound params, and each query is windowed to 30 d, indexed by user and .limit(200). The relationships lookup is served by the (a_user_id, b_user_id, kind) primary key, not a scan.
2026-09-19 3d4e4a1 auto Fixed (#11). #10 wired the bandit into recommend() on the request path, but loadBanditState defaults to rebuild: true: a missing bandit_state row made the next signed-in recommendation read up to REBUILD_ROW_LIMIT (60,000) feedback_events rows, replay them and write back — a read-then-write with no transaction, so concurrent first requests each did the whole thing, silently behind .catch(() => null). Passing rebuild: false moves the replay to the analytics job that already owns it. Left for Jacob: the same rebuild: true default is reached from two untouched call sites, preference/index.ts:234 (recordFeedback) and :287 (getPreferenceProfile). recordFeedback is not mechanical — it saves right after, so rebuild: false there would persist an empty bandit over a recoverable history.
2026-09-20 52ef4fa auto clean, and the new lifecycle code is a good example. queuePreviewImage (server/preview-generation.ts:235) claims with a conditional UPDATE … WHERE image_generation_id IS NULL … RETURNING, an atomic compare-and-swap that needs no transaction: concurrent POSTs leave exactly one winner and the loser re-reads. cancelPreviewImage guards on the generation id the same way. The new server actions (actions/previews.ts, actions/profile.ts) only issue keyed updates — no select-then-insert. Index coverage survived the H&M swap intact: every products_* index has an articles_* counterpart, plus new articles_colour_family_idx, articles_outfit_role_idx and articles_product_code_idx, and the new previews / article_vision / hm_customers / type_affinity tables all carry indexes. Migrations 0002–0004 landed with them.
2026-09-20 8475f6e auto clean — the image route's query narrowed from the whole article row to image_path alone, still one keyed lookup. No new writes, no new races.
2026-09-20 ad48e14 auto clean. The one new write path, POST /api/articles/[id]/view, records through recordArticleView (engine/src/discovery/index.ts:73), an onConflictDoUpdate upsert whose set is max(viewed_at, excluded.viewed_at) — atomic, no select-then-insert, correct under D1's missing transactions. No UI calls that route yet; only its test does. No new per-item query loops, no statement near the 100-parameter bound.
2026-09-20 437f498 auto clean, and an improvement. /collections/[id] stopped building its invitable list from loadNetworkPeople (8 selects over the computed relationships graph in components/social/data.ts) and now calls friendList — one query, one join, .limit(100), filtered to accepted in process. Fewer statements on the page and the right data: the commit notes the old path both omitted a real friend and would have admitted a stranger carrying a follows edge. Minor edge, not worth a change on its own: the .limit(100) applies before the accepted filter, so someone with more than 100 friendship rows of any state could have accepted friends truncated out of the list; tightening it would change friendList's contract for /me/friends, which needs the pending ones. Also new this range: a CI job asserting migration journal indices are unique and that drizzle-kit generate produces no drift — the commit records that three migrations had already reached main sharing one index.
2026-09-20 ee3ea39 auto clean, and one predicate lighter. prefilterConditions (retrieve.ts:330) and planSearch (search.ts:229) both dropped isNotNull(articles.imagePath). Verified correct rather than assumed: hm/scripts/import.ts now skips the 440 articles H&M never photographed and the 404 with no copy, so those rows are no longer in the table to filter out — the commit's "keep them out of the table, not out of the query". Both CI migration rules checked locally on this HEAD: 15 migrations, indices unique, every file present; and pnpm --filter @lookline/db generate produces no diff. 7cc5d7a removed two migrations that changed no schema. Worth knowing before the next deploy: a D1 database imported before 871b147 still holds those ~844 rows, and with the query-side guard gone they would come back as blank tiles. The exclusion is now an import-time property, so the remote catalogue has to be re-imported, not just migrated.
Dominant language
TypeScript
Stars
0
Forks
1
No merged PRs in 30d

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from JacobLinCool/lookline

All issues in JacobLinCool/lookline

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.