Hardening report

Hardening pass of SRV-28 against the non-functional and security requirements (cross-cutting requirements). Every finding is either fixed in SRV-28 or logged for QA-01 with its severity. What can only be measured on the VPS (load tests, the production restore drill, TLS, monitoring) is listed at the end.

Severity: High — exploitable or breaks a stated objective; Medium — weakens a control or a target at realistic volumes; Low — defence in depth, gap with the documentation; Info — reviewed, nothing to change now.

What runs in CICopy Anchor Link

Result at hand-off: the three sweeps pass with zero unexpected allow and zero exposure.

Findings fixed in SRV-28Copy Anchor Link

Findings logged for QA-01Copy Anchor Link

Slow-query passCopy Anchor Link

A disposable copy of the development database (hoco_perf, dropped afterwards) was multiplied by apps/server/load/perf-seed.sql: 4 860 products, 7 371 variants and stock levels, 3 040 resellers, 10 020 orders, 33 567 order lines. Each list endpoint was called 10 times in-process (server-side time, development machine, no other load).

The schema was already well indexed (resellers by delegate and update time, tours and visits by delegate, orders by reseller, status and queue, trigram indexes): one index was added (F-10). The remaining time was spent in application code (L-01 to L-03).

After the L-01 to L-03 fixesCopy Anchor Link

Measured on a fresh copy built the same way — hoco_perf on the shared PostgreSQL, prisma migrate deploy, reference and demo seeds, perf-seed.sql (same counts), plus two running promotions (−15 % on one brand, −50 DA for super-gros resellers on the whole catalog) so that the promotion paths run — then dropped. Each operation was called 50 times in-process over HTTP after 3 warm-up calls (development machine, no other load); "before" is the code of the sweep on the same copy and harness, which explains the gap with the first table.

An index on orders (updated_at DESC, id DESC) was tried for the delegate list (11 → 3 ms of SQL) and not kept: no measurable gain end to end.

Follow-up: suggestions, facets and homeCopy Anchor Link

catalog.products.suggest, catalog.facets and catalog.home no longer load and price the whole visible catalog (the rest of L-01):

• Suggestions (catalog/suggest.ts): the trigram search (L-02) gives the visible matches and their scores; one query keeps those with an active variant, and only the products returned (at most limit) are loaded. Brands and categories are checked against the visible catalog in SQL, and the category counts are computed for the matching categories only.
• Facets (catalog/facets.ts): the disjunctive counts (computeFacets) are unchanged, but they run on slim variant rows (ids, brand, category, price columns, VAT, stock flag, publication date) restricted in SQL to the text query; the attribute filters are evaluated in SQL (one flag per filter and variant, and a row condition when no category is selected); attribute values are read only for the selected category's variants, with one SQL query instead of a Prisma include. The price range and the promotion count need the caller's price of every matching variant, so the slim rows are priced with the engine (only where an open promotion covers them). The 5-minute cache is unchanged; this is the cold cost.
• Home (catalog/home.ts): popular-category counts are one GROUP BY; the recommended products come from three SQL reads of the in-stock display variants (first in-stock variant in catalog order) — popular (10 rows), new arrivals (17 rows), and the promotion candidates: only the rows an open promotion's scope covers (scopeSql, the inScope rule), priced by popularity in batches of 100 until three show a promotion — then only the picked products (≤ 10) are loaded for their cards. The promotion product counts are one SQL query (productCountsOf).

Same perf copy method (hoco_perf2: migrations, reference and demo seeds, perf-seed.sql — 4 860 products, 7 371 variants — the same two running promotions, then dropped); 50 calls each after 3 warm-up calls, in-process over HTTP; facets with the cache emptied before each call.

What remains: the suggestion time is the trigram scoring of the matches (~70 ms for cable, 1 053 hits); a cold facet call reads and prices every matching variant (SQL ~35 ms, the rest transfer and JavaScript). catalog.brands, catalog.categories, catalog.products.similar and the promotion pages followed (below).

Equivalence: catalog/reads.test.ts compares each with the former whole-catalog algorithm, with promotions of every scope kind (product, variant, category, brand, whole catalog; a capped one; audiences) and visibility rules: facets over 5 callers × 21 filter sets, suggestions over 5 callers × 14 queries × 2 limits and locales, the home's recommended products and their cards, top-category counts and promotion product counts for 5 callers, and the SQL attribute reader against the former Prisma query. The same file passes on the perf copy.

Follow-up: brands, categories, similar products and promotion pagesCopy Anchor Link

The last catalogue reads that loaded and priced the whole visible catalog per request (SRV-08 follow-up):

• Brands and categories (catalog/browse.ts): the product counts are one SQL GROUP BY over the visible products with an active variant (visibleSql); a category counts its sub-categories' products through a LATERAL (category, parent) pair. Nothing is priced.
• Similar products (catalog/browse.ts): the product itself is loaded alone; the candidates are slim in-stock display rows (the first in-stock variant in catalog order, as home.ts) of its category tree, ranked by popularity in SQL and priced with the engine only where an open promotion covers them, for the ±40 % band; the complementary fallback is a LIMITed SQL read of the linked categories (no price needed), the configured pairs (BO-28) are loaded by id. Only the returned products are loaded for their cards.
• Promotion pages (promotions/pages.ts): the brand filter of promotions.list and the product counts are productCountsOf (now with an optional brand condition); promotions.get ranks the eligible products in SQL — one row per product, its first in-scope variant in catalog order (default first), scopeSql for inScope, then in stock, popularity, id like popularOrder — pages with LIMIT/OFFSET on the same cursor, counts with productCountsOf, and loads only the page.

Same perf method (hoco_perf3: migrations, reference and demo seeds, perf-seed.sql — 4 860 products, 7 371 variants — and two running promotions: −15 % on one brand, −50 DA for super-gros resellers on the whole catalog; then dropped); 50 calls each after 3 warm-up calls, in-process over HTTP; "before" is the code of dev on the same copy and harness.

No catalogue read loads the whole visible catalog any more; the remaining whole-catalog pricing is the cold facet call (price range and promotion count of every matching variant). Equivalence: catalog/reads.test.ts compares the five reads with the former algorithms (kept in the test) for the same 5 callers and promotions: brands and the category tree in three locales; similar and complementary products for one product per category, a configured complement pair and an unknown product (404), limits 4 and 10, with the same pricing instant; promotions.list over 10 filter sets (daily, tabs, types, brands, limits 1 to 20) walking every cursor; promotions.get for every visible promotion, limits 5 and 50, up to 4 pages, and an unknown id. The same tests pass on the perf copy.

Follow-up: back-office products and orders to confirmCopy Anchor Link

The last two list endpoints of L-03 that loaded every matching row with its relations and paged in memory:

• admin.products.list (catalog/admin-products.ts): when no filter needs the stock, the completeness, the promotions or the attributes and the sort is updatedAt, publishedAt or priceDetail (the default listing), the column conditions (status, brand, category with its descendants, text), the visibility mode, the novelty and the default variant's price are SQL conditions, SQL orders and pages (ties by id, as the former comparison) and counts, and only the page's ids are read. Otherwise one SQL query returns slim rows — default variant price, stock sums of the active variants (all when none is active), availability and worst alert level over the sellable warehouses, visibility mode, publication and update times — already filtered on stock, visibility, novelty and price, with only the columns the call reads; promotions (activePromotionsOf, unchanged), attribute filters (variantMatches on the filtered keys' values) and completeness (one query for the active variants' prices and attribute keys, required keys by definitionsFor, the description trimmed with the characters String.prototype.trim removes) filter those rows, and the former comparison (toLowerCase, id localeCompare) sorts them. Either way only the page is loaded and shaped as before. The text match stays the former Prisma condition (its ids feed the query). The product export (analytics/sources.ts) reads the ranked ids (rankProducts) instead of shaping every product.
• admin.orders.toConfirm (orders/admin-orders.ts): the queue's conditions are unchanged; the rows are slim (status, assisted confirmation and expiry, submission and creation times, total), enough for the kind, the SLA, overdue, the counts and the sort (the same stable sort on the same base order); the orders with their lines and controls are loaded for the page only, or for every candidate when the caller filters on the controls (as before). The former take: 2000 is gone: with 2 505 pending orders the queue now shows and counts all of them.

Same perf method (hoco_perf4: migrations, reference and demo seeds, perf-seed.sql — 4 860 products, 7 371 variants, 10 020 orders of which 2 505 pending — and two running promotions: −15 % on one brand, −50 DA for super-gros resellers on the whole catalog; then dropped); 50 calls each after 3 warm-up calls, in-process over HTTP, page size 25; "before" is the code of dev on the same copy and harness.

"Before" counted at most 2 000 pending orders (2 000 and 1 599 overdue), "after" all 2 505 (1 998 overdue). What remains: the queue's time is the four controls of the page's 25 orders (~110 ms; the slim rows take ~30 ms); the controls filter still runs them for every candidate. The products list's slim query takes ~40 ms for the whole catalogue; the completeness filter adds the active variants' attribute keys, the promotion filter their ids.

Equivalence: catalog/admin-list.test.ts compares admin.products.list with the former implementation (kept in the test) on the demo catalogue plus temporary products — inactive and archived warehouses, variants without levels or all inactive, a product without variants, visibility rules of the three modes, images, null and blank descriptions (non-breaking and ideographic spaces, BOM), missing required attributes and prices, promotions by product, variant and category, an ended and a draft one, equal update times, names equal but for case — over 38 filter sets (status, brand, category, text, availability, alert levels, visibility, completeness, promotion, novelty, price range, attr.* values and ranges, combinations) with every sort and order, the SQL-paged sorts on a middle page for every set, pages of 7 walked for every sort, the export's ids, and one page over HTTP. orders/to-confirm.test.ts compares the queue with the former implementation for an administrator and a "credit only" finance manager on clones of the demo's pending orders (ties on total and submission time, no submission time, assisted orders with and without expiry, credit-only and mixed reasons, overdue or not): 16 filter sets (kinds, reasons, channel, overdue, controls, wilaya, delegate, text) × 3 sorts × 2 orders, pages of 5 walked with their counts, and one page over HTTP.

Backup restore drillCopy Anchor Link

Local drill on 19 Oct 2026: pg_dump -Fc 0.8 s (31 MB database, 1.3 MB dump), restore into a fresh hoco_restore 1.7 s, smoke check (docker/restore-smoke.sql) identical on both databases, the API on the restored database answered /ready, a sign-in, the catalog and admin.orders.list; database dropped. Procedure and results: restore drill.

Load testsCopy Anchor Link

apps/server/load/ holds the k6 scripts and their README: catalog.js (list, facets, search; thresholds p95 < 300 ms), checkout.js (50 resellers on one scarce variant; no oversell, every refusal STOCK_INSUFFICIENT) and sse.js (500 streams held 5 minutes). They were not run on the development machine: they run against staging.

Remaining on the VPSCopy Anchor Link

Development machines with shared servicesCopy Anchor Link

On a machine that already runs shared PostgreSQL, Redis and MinIO containers, docker-compose.yml is not started: .env points at them, with HOCO's own database (hoco), its own Redis database number (never FLUSHALL) and its own buckets. Disposable copies for drills use createdb -T hoco <name> and are dropped afterwards; the other projects' databases are never touched. The test suites read the same .env.