a2n-seattle / a2n-seattle/rms-app

Use UserTable's owned/reserved/borrowed arrays to avoid full-table Scans in list-by-user endpoints

Aperta
#384 2 commenti 0 reazioni 0 assegnatari Vedi su GitHub
idea priority-medium
Lingua principale
TypeScript
Stelle
1
Fork
1
Merge medio
27m
PR unite (30g)
4

Descrizione

# Why?

Several backend list-by-user endpoints do a full DynamoDB `Scan` (read every row in the table, filter in-flight with a `FilterExpression` or JS predicate) when a much cheaper targeted lookup is already possible. This repo deliberately avoids Global Secondary Indexes (extra write capacity cost on every write, working against the free/low-cost on-demand-friendly design) — but `ts-code/src/db/Schemas.ts`'s `UserSchema` already carries a "semi foreign key" pattern that solves the same problem without a GSI: `owned`/`reserved`/`borrowed: string[]` fields (ids into `MainSchema`/`ScheduleSchema`/`ItemsSchema` respectively), keyed on Cognito `sub`.

**The write side is already fully wired** — every relevant mutation correctly maintains these arrays today:
- `AddItem.ts` → `userTable.addOwned`
- `DeleteItem.ts` → `userTable.removeOwned`
- `BorrowItem.ts` → `userTable.addBorrowed`
- `ReturnItem.ts` → `userTable.removeBorrowed`
- `CreateReservation.ts` → `userTable.addReserved`
- `DeleteReservation.ts` → `userTable.removeReserved`
- `BorrowFromSchedule.ts` → `addBorrowed` + `removeReserved`

**The read side is completely unused.** Every list-by-user endpoint still does a raw `Scan` instead of reading the id array off `UserTable` and doing targeted `Get`s/`BatchGetCommand`:

| Endpoint | Currently | Could instead |
|---|---|---|
| `ListMyBorrowedItems.ts` | Scans `items`, filters `borrower` | Read `UserTable.get(sub).borrowed`, `BatchGet` those item ids |
| `ListMyOwnedItems.ts` | Scans `main`, filters `ownerId` | Read `.owned`, `BatchGet` those family ids |
| `ListOverdueItems.ts` | Scans `items` unfiltered, then predicate-filters by `borrower` + per-item schedule lookup | Start from `.borrowed` via `BatchGet`, apply the same overdue predicate to a much smaller set |
| `ScheduleTable.listByBorrower` / `ListUpcomingReservations.ts` | Scans `schedule`, filters `borrower` | Read `.reserved`, `BatchGet` those schedule ids |

`ListHistory.ts` (Scans `history` filtered by `borrower`) is the one exception — `HistorySchema` ids aren't tracked in `UserSchema` at all today, so this one either needs a new `UserSchema.history: string[]` field (written wherever history entries get created) or stays a Scan. Treat as out of scope for a first pass unless it's cheap to add alongside the others.

# What?

Rework the four/five list endpoints above to read from `UserTable`'s denormalized arrays instead of scanning the target table, using `BatchGetCommand` (or sequential `Get`s if batch size is small enough that it's not worth the complexity) against just the referenced ids. No new infra, no GSIs — this is purely a read-path change in `ts-code/src/api/*.ts`, since the write side is already correct.

# Testing

Per root CLAUDE.md's testing policy: update the existing unit tests for each reworked `List*.ts` under `ts-code/__tests__/unit/api/` to seed/assert against the `UserTable` read path instead of (or in addition to) the raw table scan, and assert that `scanUntilLimit`/`ScanCommand` is no longer invoked where it's been replaced by targeted Gets. `LocalDBClient`'s existing whole-DB seed/assert pattern (used by `BorrowItem.test.ts` etc.) should extend cleanly to also assert on the read side.

# Additional context

Raised in the context of investigating e2e cold-start flakiness (#382) and generally hardening this backend against unnecessary full-table reads on the deliberately low-throughput `alpha` DynamoDB tables (1 RCU/1WCU per table, see root CLAUDE.md). This is the DynamoDB-native equivalent of "add a foreign key index" without paying for an actual GSI.

Guida per i contributori

Nessuna guida per i contributori indicizzata per questo repository

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.