[OPTIMIZATION] Database & Query Performance - src/lib/db.ts #54

New issue

Closed

opened 2026-06-06 20:41:16 +00:00 by Mark · 0 comments

Mark commented

2026-06-06 20:41:16 +00:00

Owner

Critical Issues (7)

getUpcomingEvents: Full table scan + JS filter instead of indexed range query
- Current: db.dateEvents.toArray() + db.people.toArray() + JS filter
- Fix: Add compound index [personId+date], use where("date").between()
- Impact: 10-100x faster for large datasets
getFamilyEventsForHousehold: Full scan + JS filter on participants.personId
- Fix: Add index on participants.personId or denormalize
getUpcomingFamilyEvents: Full scan + JS filter on date
- Fix: Use where("date").between()
getOverduePeople: Loads all people + JS filter
- Fix: Composite index on [lastContact, contactFrequencyGoal]
searchPeople: Full scan with complex string matching
- Fix: Add search index / precomputed tokens
getPeopleByTag: Full scan on array field
- Fix: Denormalize with junction table + index
deletePerson: 4 sequential deletes
- Fix: Promise.all parallel deletes

High Issues (2)

Missing compound indexes for common query patterns
Multiple .toArray() loads entire tables into memory

Medium Issues (2)

Linear find() for person by ID - use Map
getUpcomingEvents sorts in JS - let IndexedDB sort via index

## Critical Issues (7) 1. **getUpcomingEvents**: Full table scan + JS filter instead of indexed range query - Current: `db.dateEvents.toArray()` + `db.people.toArray()` + JS filter - Fix: Add compound index `[personId+date]`, use `where("date").between()` - Impact: 10-100x faster for large datasets 2. **getFamilyEventsForHousehold**: Full scan + JS filter on `participants.personId` - Fix: Add index on `participants.personId` or denormalize 3. **getUpcomingFamilyEvents**: Full scan + JS filter on date - Fix: Use `where("date").between()` 4. **getOverduePeople**: Loads all people + JS filter - Fix: Composite index on `[lastContact, contactFrequencyGoal]` 5. **searchPeople**: Full scan with complex string matching - Fix: Add search index / precomputed tokens 6. **getPeopleByTag**: Full scan on array field - Fix: Denormalize with junction table + index 7. **deletePerson**: 4 sequential deletes - Fix: `Promise.all` parallel deletes ## High Issues (2) 8. Missing compound indexes for common query patterns 9. Multiple `.toArray()` loads entire tables into memory ## Medium Issues (2) 10. Linear `find()` for person by ID - use Map 11. `getUpcomingEvents` sorts in JS - let IndexedDB sort via index