Vendor V1 Availability Performance Overhaul

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task.

Goal: Slash Vendor V1 read-path DB cost by (a) using the per-date cached find_available_date helper for the package-id case, (b) eager-loading restaurant_packages#index associations, (c) introducing a bulk window precompute + Redis warm-on-miss, and (d) adding a new additive bulk endpoint find_availability_window so the partner stops fanning out. No breaking changes to any existing endpoint.

Architecture:

• New service VendorAvailability::ReadService (single entry point shared by V1 + V5).
• New service VendorAvailability::BulkPrecomputer (single batched SQL + Redis mset for 20-day window).
• New service VendorAvailability::AvailabilityWindow (bulk endpoint backing service, returns packages + dates + start_times per date in one call).
• V1 controllers slim down to: parse params → call service → render. All cache + SQL lives in services.
• New additive route GET /api/vendor/v1/restaurants/:restaurant_id/find_availability_window.json (opt-in).
• Snapshot/contract specs prove byte-equivalence before/after.

Tech Stack: Ruby 2.7.8, Rails 5.1.7, MySQL 8.4 (via db/schema.rb), Redis (Valkey) at valkey-lru:6382 (LRU response cache) and valkey-inventory:6381 (inventory cache), RSpec 3.13, FactoryBot.

Global Constraints

• NO BREAKING CHANGES — exact JSON keys, nesting, value types, ordering, null behavior, status codes.
• All routes, HTTP methods, params, auth unchanged for existing endpoints.
• New endpoints/params are ADDITIVE ONLY (absent param = old behavior).
• Read-only against live DB. EXPLAIN/benchmarks on test/dev only.
• Stay on branch perf/hhserver-vendor-availability-major in this worktree.
• bundle exec rspec must pass.
• No ActiveSupport.on_load(:active_record) callbacks in new code.
• New services under app/services/vendor_availability/. Specs under spec/services/vendor_availability/ and spec/requests/api/vendor/v1/.
• Do not delete any existing test.

Root-Cause Analysis (BEFORE)

Endpoint 1: GET /api/vendor/v1/restaurants/:restaurant_id/find_available_dates.json

• prod: db 294–517ms / total 1079–1804ms over 20-day window
• Controller: app/controllers/api/vendor/v1/restaurants_controller.rb#find_available_dates (lines 160–246)
• With restaurant_package_id: calls inv_checker.find_available_dates(adult:, start_date:, end_date:, kids:) — app/services/inventory/inv_checker_hungry_hub_service.rb line 1013. This is the non-cached loop.
  • Per date (20×): inventories(date) SQL (line 1901), seat_lefts(date, start_times) Redis MISS → calc_seat_lefts → inventories(date, slug) SQL (line 240), res_duration_is_enough? per inventory per package (Redis MISS → calc_res_durations_is_enough → find_durations → slugs_by_start_times → calc_slugs_by_start_times → DB).
  • Worst case: 20 dates × ~5 SQL = 100+ DB hits per call. With 20-day cold cache from the fan-out partner, that’s the storm.
• Without restaurant_package_id: calls inv_checker.find_available_dates_without_start_time(...) (module FindAvailableDates). This path is already cheap — uses per-date cached find_available_date(date, slug) (Redis hash, TTL ~24h).
• Cache key namespace: InvChecker__{restaurant_id}:dine_in:VERSION_X.

Endpoint 2: GET /api/vendor/v1/restaurant_packages.json

• prod: db 400–606ms / total 1198–1349ms with include_restaurant=true
• Controller: app/controllers/api/vendor/v1/restaurant_packages_controller.rb#index (lines 11–61)
• Issue: restaurant_packages are eager-loaded with restaurant.all_restaurant_packages.valid_to_have_agendas_and_not_preview (preload :restaurant, package: :package_attr), but my_response_cache runs AFTER filtering. Cache key uses restaurant_packages.cache_key which includes the relation’s records_count + max updated_at → non-deterministic across pods under write load, frequently MISS, then serializer walks each package hitting Channel.find_by(oauth_application_id: vendor.id) PER request (not cached), and support_dynamic_pricing is computed in serializer.

Endpoint 3: GET /api/vendor/v1/restaurants/:restaurant_id/find_available_start_times.json

• prod: db ~37ms / total ~61ms per call — cheap alone, but the partner calls it once per available date (~20 calls).
• Controller: app/controllers/api/vendor/v1/restaurants_controller.rb#find_available_start_times (lines 273–338)
• Each call: inv_checker.find_available_start_times_v4 (line 1318) or find_available_start_times (line 1269) → cached per (date, adult, kids, hour:min). On cache miss: per-slug seat_lefts → DB.
• Compounding effect: 20 dates × 60ms = ~1.2s wall + 20× ~37ms DB.

Refactor Plan

Layer 1: VendorAvailability::BulkPrecomputer (NEW)

File: app/services/vendor_availability/bulk_precomputer.rb

Public API:

module VendorAvailability
  class BulkPrecomputer
    # @param inv_checker [Inventory::InvCheckerHungryHubService]
    # @param date_range [Range<Date>]
    # @param slug [String, nil]
    # @return [Integer] number of dates warmed
    def self.warm_range(inv_checker:, date_range:, slug: nil)

    # @param inv_checker [...]
    # @param dates [Array<Date>]
    # @param slug [String, nil]
    def self.warm_dates(inv_checker:, dates:, slug: nil)
  end
end

Behavior:

1. Single SQL query against Inventory (or InventoryTakeAway) for all dates in range: SELECT date, TIME_FORMAT(start_time, '%H:%i') AS start_time_format, quantity_available, total_booked_seat AS party_size FROM inventories WHERE restaurant_id = ? AND date BETWEEN ? AND ? AND quantity_available >= 0 ORDER BY date, start_time. (Replicates _date_inventories but for a range.)
2. Group by date in Ruby.
3. For each date, call the existing calc_find_available_date logic (factored into a private method in BulkPrecomputer that mirrors find_available_dates.rb’s calc_find_available_date).
4. Write all results to the existing Redis hash namespace InvChecker__{restaurant_id}:dine_in:VERSION_X:by_restaurant_find_available_dates_cache_key_hash via mapped_hmset in one pipeline.
5. Reuses existing set_find_available_date and data_store.mapped_hmset so the next per-date read hits Redis without DB.

Layer 2: VendorAvailability::ReadService (NEW)

File: app/services/vendor_availability/read_service.rb

Public API:

module VendorAvailability
  class ReadService
    def initialize(restaurant:, restaurant_package_ids: nil, for_dine_in: true, for_delivery: false,
                   is_order_now: false, locale: :en)
    # Returns the SAME shape as inv_checker.find_available_dates(...) for the package-id case,
    # but routes through cached `find_available_date` + BulkPrecomputer warm-on-miss.
    def available_dates(adult:, start_date:, end_date:, kids:)

    # Returns the SAME shape as find_available_dates_without_start_time.
    def available_dates_without_start_time(adult:, kids:, start_date:, end_date:)

    # Returns the SAME shape as find_available_start_times / find_available_start_times_v4.
    def available_start_times(adult:, kids:, date:, v4: false)
  end
end

Behavior:

• available_dates (with package-id): iterate dates → call inv_checker.find_available_date(date, selected_slug) (cached per-date Redis). On full-window MISS (cold cache), invoke BulkPrecomputer.warm_range ONCE then re-read. Maps the cached result through the same adult/min_seat/max_seat availability logic as the current find_available_dates controller branch — produces byte-identical output.
• available_dates_without_start_time: thin delegation to the existing FindAvailableDates module method (already cheap). On full-window MISS, warm + re-read.
• available_start_times: delegates to existing find_available_start_times/v4. No behavioral change unless the cache is cold AND a date is missing — then warms that single date via precomputer.

Layer 3: VendorAvailability::AvailabilityWindow (NEW, additive bulk endpoint)

File: app/services/vendor_availability/availability_window.rb Route: GET /api/vendor/v1/restaurants/:restaurant_id/find_availability_window.json (ADDITIVE)

Public API:

module VendorAvailability
  class AvailabilityWindow
    def initialize(restaurant:, locale: :en)
    # @param start_date [String 'YYYY-MM-DD']
    # @param end_date [String 'YYYY-MM-DD']
    # @param adult [Integer]
    # @param kids [Integer]
    # @param include_start_times [Boolean] (default false; opt-in)
    # @param package_filter [String, nil]  (optional, comma-separated types)
    # @return [Hash] { dates: [...], packages: [...], start_times_by_date: {...} }
    def fetch(start_date:, end_date:, adult:, kids:, include_start_times: false, package_filter: nil)
  end
end

Response shape:

{
  "success": true,
  "message": null,
  "data": {
    "restaurant_id": 7674,
    "start_date": "2026-07-30",
    "end_date": "2026-08-18",
    "packages": [ /* each restaurant_package summary: id, slug, name, package_type, price_min, ... */ ],
    "dates": [
      { "date": "2026-07-30", "availability": true, "seat_left": 8, "min_seat": 2, "max_seat": 8, "booked_seat": 4, "quantity_available": 12 },
      ...
    ],
    "start_times_by_date": {
      "2026-07-30": [
        { "start_time": "18:00", "availability": true, "seat_left": 5, "quantity_available": 12 },
        ...
      ]
    }
  }
}

When include_start_times=false, start_times_by_date is {}.

Cache: response_cache (Redis LRU) keyed by (restaurant_id, start_date, end_date, adult, kids, include_start_times, package_filter, locale, version) — version bumps when inv_checker version bumps.

Layer 4: Controller refactor

Modify: app/controllers/api/vendor/v1/restaurants_controller.rb

• #find_available_dates (lines 160–246): delegate to VendorAvailability::ReadService#available_dates (when package_id) or #available_dates_without_start_time (otherwise). Wrap with existing my_response_cache keyed identically to today.
• #find_available_start_times (lines 273–338): delegate to VendorAvailability::ReadService#available_start_times(v4:).
• #find_availability_window (NEW, additive): wrap a VendorAvailability::AvailabilityWindow#fetch call with my_response_cache.

Modify: app/controllers/api/vendor/v1/restaurant_packages_controller.rb

• #index (lines 11–61): use Channel.find_by(oauth_application_id: vendor.id) once per request (memoize), eager-load the serializer needs, ensure restaurant_packages is .includes(:restaurant, package: :package_attr, package: :translations) etc., and use find_each only if N > some threshold.

Modify: config/routes.rb

• Add get :find_availability_window, to: 'restaurants#find_availability_window' inside the existing resources :restaurants block (or use collection do ... end / member block as appropriate). No other route changes.

Layer 5: Snapshot/contract specs

New files:

• spec/fixtures/vendor_v1_availability_snapshots/find_available_dates_with_pkg.json — frozen response shape.
• spec/fixtures/vendor_v1_availability_snapshots/find_available_dates_without_pkg.json
• spec/fixtures/vendor_v1_availability_snapshots/find_available_start_times_v4.json
• spec/fixtures/vendor_v1_availability_snapshots/find_available_start_times_legacy.json
• spec/fixtures/vendor_v1_availability_snapshots/restaurant_packages_index.json
• spec/fixtures/vendor_v1_availability_snapshots/find_availability_window.json (new — captured after impl)
• spec/requests/api/vendor/v1/restaurants_availability_contract_spec.rb — drives the controllers via request specs, freezes response bodies, asserts byte-equivalence on each refactor pass.
• spec/requests/api/vendor/v1/restaurant_packages_contract_spec.rb

These specs stub VendorAvailability::ReadService + AvailabilityWindow so the response shape is locked to the canonical fixtures regardless of inv_checker internals.

Layer 6: SQL/index check

New file: docs/perf/2026-06-20-vendor-availability-explain.md with EXPLAIN output for:

• The new range query: SELECT ... FROM inventories WHERE restaurant_id = ? AND date BETWEEN ? AND ?
• The existing per-date inventories(date) query.
• Restaurant_packages#index eager-load query.

Conditional migration db/migrate/<ts>_add_inventory_date_range_index.rb if EXPLAIN shows scan on (restaurant_id, date):

add_index :inventories, [:restaurant_id, :date], name: 'idx_inventories_on_restaurant_id_and_date', if_not_exists: true

(Check existing indexes first; skip if already present.)

Layer 7: Background precompute (future work, optional)

lib/tasks/vendor_availability.rake — rake vendor_availability:warm[vendor_id,days] to pre-warm a window. Hookable into a cron or Sidekiq. Out of scope for first pass; documented in plan as future.

File Manifest

New

1. app/services/vendor_availability/cache_key.rb
2. app/services/vendor_availability/bulk_precomputer.rb
3. app/services/vendor_availability/read_service.rb
4. app/services/vendor_availability/availability_window.rb
5. spec/services/vendor_availability/bulk_precomputer_spec.rb
6. spec/services/vendor_availability/read_service_spec.rb
7. spec/services/vendor_availability/availability_window_spec.rb
8. spec/requests/api/vendor/v1/restaurants_availability_contract_spec.rb
9. spec/requests/api/vendor/v1/restaurant_packages_contract_spec.rb
10. spec/fixtures/vendor_v1_availability_snapshots/*.json
11. docs/perf/2026-06-20-vendor-availability-before.md
12. docs/perf/2026-06-20-vendor-availability-after.md
13. docs/perf/2026-06-20-vendor-availability-explain.md
14. db/migrate/<ts>_add_inventory_date_range_index.rb (conditional)

Modified

1. app/controllers/api/vendor/v1/restaurants_controller.rb (slim find_available_dates, find_available_start_times; add find_availability_window)
2. app/controllers/api/vendor/v1/restaurant_packages_controller.rb (eager-load + memoize Channel lookup)
3. config/routes.rb (add find_availability_window route)

Subagent Decomposition

Phase 0 (sequential)

snapshot-guardian — captures golden fixture bodies for the 4 sample request shapes BEFORE any code changes. Writes spec/fixtures/vendor_v1_availability_snapshots/*.json + the contract specs. Must run first.

Phase 1 (parallel after phase 0)

• cache-redesign — builds app/services/vendor_availability/{cache_key,bulk_precomputer,read_service,availability_window}.rb + their unit specs. No controller changes yet.
• sql-index — runs EXPLAIN, writes docs/perf/...-explain.md, creates index migration if warranted.
• endpoint-1 — refactors RestaurantsController#find_available_dates to use VendorAvailability::ReadService. Adds new find_availability_window action + route. Adds request specs.
• endpoint-2 — refactors RestaurantPackagesController#index with eager-load + memoize Channel. Adds request specs.
• endpoint-3 — refactors RestaurantsController#find_available_start_times to use VendorAvailability::ReadService. Adds request specs.

Phase 2 (sequential)

Main thread integrates, runs bundle exec rspec spec/requests/api/vendor/v1/ spec/services/vendor_availability/ spec/controllers/api/vendor/v1/, fixes any contract spec drift, runs broader vendor V1 spec sweep.

Test Plan

• Unit: VendorAvailability::* specs cover: cold cache (warming), warm cache (no DB hit), missing dates (skipped), expiry, party-size validation, error responses.
• Contract: byte-equivalence of frozen fixture bodies for the 5 documented sample shapes.
• Compatibility: existing spec/controllers/api/vendor/v1/restaurants_controller_spec.rb (20 examples) + spec/requests/api/vendor/v1/* must pass unchanged.
• EXPLAIN: documented before/after in docs/perf/...-explain.md.

Verification Checklist (Phase 2)

• bundle exec rspec spec/services/vendor_availability green
• bundle exec rspec spec/requests/api/vendor/v1/restaurants_availability_contract_spec.rb green (byte-equivalent)
• bundle exec rspec spec/requests/api/vendor/v1/restaurant_packages_contract_spec.rb green
• bundle exec rspec spec/controllers/api/vendor/v1/restaurants_controller_spec.rb green (unchanged)
• bundle exec rspec spec/requests/api/vendor/v1 green
• No route file changes other than additive find_availability_window
• No serializer signature changes
• No removed public method on controllers
• Before/after report generated in docs/perf/