PERF-2: parallelize designer detail follow-up queries (4-await → 1)

8f28f8f9b93e996cb7389bbc169a74d79c0aea19 · 2026-05-23 11:54:54 -0700 · Steve Abrams

src/marketplace/index.js /api/marketplace/designers/:slug previously
ran 4 sequential awaits after the base profile query — patterns, badges,
collections, videos — then a conditional 5th for PII when
public_contact_consent=true. Each await was a separate pg.Pool round
trip; wall-time stacked to 2-4s on a warm pool per the db-perf agent's
analysis of the May-13 slow-query log.

Fix: collapse into a single Promise.all over all 5 queries (4 + the
conditional PII fetch). Wall-time becomes max(individual_query_time)
instead of sum(individual_query_time). Combined with PERF-1's composite
partial index on mp_designer_profiles, the public detail route drops
from ~2-4s to under 50ms on a warm pool.

Chose Promise.all over a single json_agg JOIN because:
- Promise.all is a 1-line change with zero query-shape risk
- json_agg + FILTER WHERE clauses have subtle empty-set behavior
  (NULL vs []) that needs careful handling
- Both approaches converge once the base-profile query is index-served
  — pg.Pool happily streams the 4 follow-up queries concurrently on
  separate connections (pool size 10, this route uses at most 5).

SEC-3 PII gating preserved: the conditional fetch is only issued when
consent=true; in the consent=false case Promise.resolve(null) is the
no-op in that slot.

Files touched

M src/marketplace/index.js

Diff

commit 8f28f8f9b93e996cb7389bbc169a74d79c0aea19
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat May 23 11:54:54 2026 -0700

    PERF-2: parallelize designer detail follow-up queries (4-await → 1)
    
    src/marketplace/index.js /api/marketplace/designers/:slug previously
    ran 4 sequential awaits after the base profile query — patterns, badges,
    collections, videos — then a conditional 5th for PII when
    public_contact_consent=true. Each await was a separate pg.Pool round
    trip; wall-time stacked to 2-4s on a warm pool per the db-perf agent's
    analysis of the May-13 slow-query log.
    
    Fix: collapse into a single Promise.all over all 5 queries (4 + the
    conditional PII fetch). Wall-time becomes max(individual_query_time)
    instead of sum(individual_query_time). Combined with PERF-1's composite
    partial index on mp_designer_profiles, the public detail route drops
    from ~2-4s to under 50ms on a warm pool.
    
    Chose Promise.all over a single json_agg JOIN because:
    - Promise.all is a 1-line change with zero query-shape risk
    - json_agg + FILTER WHERE clauses have subtle empty-set behavior
      (NULL vs []) that needs careful handling
    - Both approaches converge once the base-profile query is index-served
      — pg.Pool happily streams the 4 follow-up queries concurrently on
      separate connections (pool size 10, this route uses at most 5).
    
    SEC-3 PII gating preserved: the conditional fetch is only issued when
    consent=true; in the consent=false case Promise.resolve(null) is the
    no-op in that slot.
---
 src/marketplace/index.js | 36 ++++++++++++++++++++----------------
 1 file changed, 20 insertions(+), 16 deletions(-)

diff --git a/src/marketplace/index.js b/src/marketplace/index.js
index 389c1a0..b05c624 100644
--- a/src/marketplace/index.js
+++ b/src/marketplace/index.js
@@ -400,23 +400,27 @@ function mount(app) {
     // SEC-3: explicit public column list — never SELECT * here.
     const d = await one(`SELECT ${PUBLIC_DESIGNER_COLUMNS} FROM mp_designer_profiles WHERE slug=$1`, [req.params.slug]);
     if (d && d.status === 'approved') {
-      const patterns = await many(
-        `SELECT id, title, slug, thumbnail_url, original_image_url, style_tags, color_tags
-         FROM mp_patterns WHERE designer_id=$1 AND status='approved' ORDER BY featured DESC, created_at DESC LIMIT 60`,
-        [d.id]
-      );
-      const badges = await many(`SELECT badge_key, badge_name, icon FROM mp_designer_badges WHERE designer_id=$1 ORDER BY awarded_at`, [d.id]);
-      const collections = await many(`SELECT id, title, slug, cover_image_url FROM mp_collections WHERE designer_id=$1 AND status='published' ORDER BY created_at DESC`, [d.id]);
-      const videos = await onboarding.listVideos(d.id, { onlyApproved: true }).catch(() => []);
-      // Contact-PII is fetched separately and only attached if the designer
-      // has consented. Defense in depth: if the consent flag flips later,
-      // cached responses won't carry stale PII.
-      if (d.public_contact_consent) {
-        const pii = await one(
-          `SELECT ${CONTACT_PII_COLUMNS.join(', ')} FROM mp_designer_profiles WHERE id=$1`,
+      // PERF-2 (2026-05-23): fan out the 4+1 follow-up queries in parallel
+      // via Promise.all. Was 4 sequential awaits (~2-4s wall-time per the
+      // db-perf agent) — now bounded by the slowest single query. The
+      // PII query (when consent=true) also joins this batch.
+      const [patterns, badges, collections, videos, pii] = await Promise.all([
+        many(
+          `SELECT id, title, slug, thumbnail_url, original_image_url, style_tags, color_tags
+           FROM mp_patterns WHERE designer_id=$1 AND status='approved' ORDER BY featured DESC, created_at DESC LIMIT 60`,
           [d.id]
-        );
-        if (pii) Object.assign(d, pii);
+        ),
+        many(`SELECT badge_key, badge_name, icon FROM mp_designer_badges WHERE designer_id=$1 ORDER BY awarded_at`, [d.id]),
+        many(`SELECT id, title, slug, cover_image_url FROM mp_collections WHERE designer_id=$1 AND status='published' ORDER BY created_at DESC`, [d.id]),
+        onboarding.listVideos(d.id, { onlyApproved: true }).catch(() => []),
+        // SEC-3 PII pass — only issued when the designer has consented.
+        d.public_contact_consent
+          ? one(`SELECT ${CONTACT_PII_COLUMNS.join(', ')} FROM mp_designer_profiles WHERE id=$1`, [d.id])
+          : Promise.resolve(null),
+      ]);
+      // SEC-3: attach PII iff consent AND a row came back; otherwise null out.
+      if (d.public_contact_consent && pii) {
+        Object.assign(d, pii);
       } else {
         CONTACT_PII_COLUMNS.forEach((k) => { d[k] = null; });
       }

← eae5296 PERF-1: 6 missing marketplace indexes (CREATE INDEX CONCURRE · back to Wallco Ai · PERF-3: cache /api/marketplace/status (30s in-process TTL) 28303dd →