Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Database: heavy operations and concurrency under D1

Aperta
  • Qualcosa non va?
#3 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
25/100
Tipo di issue
Refactoring
Chiarezza
Da chiarire
Stato di attività
Attiva
Stack tecnologico
sqlite, typescript
Ambito
backend, databases, performance

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.
Lingua principale
TypeScript
Stelle
0
Fork
1
Merge medio
54m
PR unite (30g)
49

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di JacobLinCool/lookline

Tutte le issue di JacobLinCool/lookline

Issue simili

Altre issue su TypeScript

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.