Performance Plan: Indexes and Server-Side Caches

1. Goal and Boundary

This plan addressed slower API and page queries as Tasuki Keifu's dataset grew. It covered database indexing and query design, plus server-side and derived-result caches. Browser-local caches, offline caching, Service Workers, and CDN design were outside scope.

The project did not yet require Redis. Query improvements, composite indexes, in-process caches, and precomputed results appeared sufficient for the current and next data scale.

2. Starting State

Existing Database Indexes

The production database already had basic indexes, mostly from Prisma schema generation or earlier migrations: primary keys and unique slugs; Membership indexes on person, organization, type, and start/end dates; RaceResult indexes on person, race, and organization; Race indexes on edition and leg; PlayerRelationCache indexes on person and generation time; and some composite indexes for TeamCompetitionResult and TeamCompetitionLegSnapshot.

The gap was not an absence of indexes. It was a shortage of composite and search-oriented indexes designed around real query paths.

Approximate Production Row Counts

At the time of the notes, RaceResult and TeamCompetitionLegSnapshot each had around 3,700 rows, Person and Membership around 2,300 each, and PersonalBest around 1,900. These were not large tables. Perceived slowness suggested inefficient queries and data assembly rather than raw database size alone.

Main Code Risks

The athlete list fetched people, memberships, and PBs in bulk, then filtered and sorted them in Node. The athlete detail loaded all race results, memberships, and PBs at once. A competition-edition page loaded every race and every result in the edition. A relation-cache miss could synchronously trigger several large queries. There was a database cache table but no hot in-process cache.

Those patterns increased database reads, data transferred to the application, and in-memory sorting, filtering, or aggregation work.

3. Principles

Optimize the query before adding a cache. Favor changes with small impact on the existing architecture rather than introducing middleware. Support the current search pages and import workflows without a risky overhaul. Every cache needs an invalidation or refresh path in addition to a TTL. Index changes should run during a low-import or paused-import window.

4. First-Phase Workstreams

1. Trace frequent page-query paths.
2. Add composite and search indexes for actual filters and ordering.
3. Reduce broad include queries and full-record loading.
4. Cache hot pages and expensive derived results on the server.

5. Proposed Indexes

Membership

Membership queries find a person's affiliations, an organization's members, and whether an affiliation was active at a time. Proposed composite indexes were (personId, type, startDate, endDate) and (organizationId, type, startDate, endDate). Current-membership queries might also benefit from a better filter shape rather than relying entirely on indexes.

RaceResult

Common paths fetched one person's results, one race or stage's table, and an organization's results in a race. Proposed indexes were (personId, raceId), (raceId, position), (organizationId, raceId), and (personId, recordedAt) or the actual time-sort field used by the application. The last key should follow the real query if no recordedAt field exists.

PersonalBest

The primary queries fetched all PBs for a person or one event for a person. (personId, discipline) was proposed. (discipline, mark) would only be justified if event rankings or cross-person event filters were built.

Person Search

If database-level keyword search remained the main path, the first step was to define whether names were matched by prefix or containment across nameKana, nameJa, and nameEn. Natural multi-field search could justify full-text search later. A separate search engine was premature at the time.

6. Query Changes

Athlete List

Move search, sorting, and pagination into the database where practical. Fetch only fields needed for list cards; trim affiliation and PB details to what is displayed. Do not load everything required by a detail page while rendering a list.

Athlete Detail

The first viewport should fetch identity, current affiliation, key PBs, and a recent-results summary. Complete historical results could load later or be paginated, while relationships should read the cache first. That reduces the initial query cost.

Competition Edition

Do not flatten every race and result into one eager page query. Fetch the first-screen summary, then load race details as needed or by module. Rankings, team results, and stage results can use separate query shapes.

Relationship Calculation

A PlayerRelationCache miss should not force a full synchronous recomputation during the page request. Return acceptable stale data or a simplified result and refresh in the background, or precompute popular athletes after imports and updates.

7. Server Cache Layers

The first cache candidates were competition-edition pages, busy organization pages, mature athlete pages, and relationship modules. They changed relatively slowly but could be expensive to query and assemble.

Two layers were proposed: a hot in-process cache for seconds-to-minutes reuse within one instance, and a persistent database cache for derived results reusable across requests. Redis was not required. Page-level caching fit races, organizations, and mature athletes; computed-result caching fit relationships, aggregates, and derived rankings.

8. Invalidation

A TTL alone could leave updated pages stale for too long and cause hot pages to rebuild simultaneously on expiry. Importing race results, correcting a person or organization affiliation, recalculating PBs, or recomputing relationships should invalidate the affected cache entries. TTL remained a safety net.

For a read-heavy site, stale-while-revalidate was a useful pattern: return fresh cache immediately; for stale but still acceptable data, return the old value and rebuild in the background. That was preferable to forcing a slow synchronous rebuild on an expired entry.

9. Delivery Order

1. Measure genuinely slow pages and frequent queries.
2. Add the clearest low-risk, high-value composite indexes.
3. Remove unnecessary broad includes and full-page data assembly.
4. Cache the slowest stable pages and derived relationships.
5. Log query and cache-hit behavior so the effect can be verified.

10. Deferred Infrastructure

The plan did not prioritize a Redis cluster, separate search engine, complex multi-layer cache platform, major table redesign, or heavy infrastructure for a still-small dataset. Those options could be reconsidered if scale justified them later.

11. Conclusion

The observed problem was a mismatch between indexes and query paths, overly broad page queries, and incomplete caching of derived results. The first-phase recommendation was to add targeted composite indexes, narrow reads, cache the stable expensive pages and relationship results, and verify the outcome through logs and hit records. This is a proposed optimization path, not a measured report of latency improvement.