Hongchao Hu's Website

DB Microservice Database API Development

Search & Filter Database Microservice API

In modern e-commerce, product discovery often struggles with slow response times, incomplete search results, and inaccurate inventory visibility. Traditional monolithic systems or external search vendors contribute to these challenges by introducing latency, driving up costs, and limiting flexibility. As product catalogs expand and customer expectations grow, the need for a fast, scalable, and transparent solution becomes increasingly critical.

This project delivers that solution through a microservice-based Search & Filter API powered by MySQL and modeled with BPMN workflows. The architecture separates functionality into three independent services—search, filter, and indexer—all managed through an API gateway that enforces routing, authentication, and observability. These services support keyword and attribute-based queries, enable multi-facet filtering on attributes such as price, color, and rating, and integrate directly with inventory data to provide real-time stock visibility. By ensuring every result is timely and accurate, the system minimizes customer frustration and strengthens trust in the platform.

To guarantee robustness, the project applies behavior-driven development (BDD) scenarios and detailed use cases, covering both success paths and error conditions such as missing input, invalid formats, or database unavailability. This disciplined approach ensures that the APIs remain reliable under diverse circumstances and provide clear feedback when issues occur.

The impact is measurable. Customers benefit from a seamless discovery process that increases satisfaction and boosts conversion rates. Businesses reduce reliance on costly third-party search services by at least 20%, cutting expenses while gaining greater ownership and flexibility over critical functionality. In addition, the APIs emit structured analytics events, giving stakeholders actionable insights into customer behavior. These insights can drive data-informed improvements in marketing, inventory management, and product strategy.

By combining scalability, speed, and clarity, this project demonstrates how microservice-driven architecture can transform the search and filter experience for both customers and businesses, delivering lasting value across the entire e-commerce ecosystem.



1. Business/Functional Requirements and Scope

The BRD defined key goals:contentReference[oaicite:0]{index=0}:

BR-1 (Keyword and attribute search) together with BR-2 (multi-facet filters) ensures customers can quickly narrow down to the exact products they want, improving conversion rates and driving higher engagement. When paired with BR-3 (real-time inventory integration), the platform delivers accurate stock visibility, reducing customer frustration and aligning demand with supply. These combined features not only streamline the user journey but also build trust by ensuring fast, relevant, and reliable search results.

BR-4 (Modular APIs with BPMN traceability) guarantees scalability and transparency, making it easier to extend or troubleshoot the system. BR-5 (reduced third-party API reliance by ≥20%) directly cuts operational costs while strengthening ownership of core capabilities. Finally, BR-6 (analytics event exposure) provides stakeholders with deep insights into search and filter behavior, enabling smarter product strategies, targeted marketing, and continuous optimization of the customer experience.


BRD/FSD Snippet:

encrtpP


2. Architecture and Process Flow

The APIs run as independent microservices:

Each service is backed by a dedicated MySQL schema. An API Gateway manages routing, authentication, and observability.

BPMN workflow:

Steps:

  1. Request validated at the front end
  2. Search plan built
  3. Query split across Catalog DB and Inventory DB
  4. Results joined, ranked, and paginated
  5. Response returned with product list + facets



Configuration snippet:

services:
  search-service:
    db_schema: catalog_search
  filter-service:
    db_schema: product_filters
  indexer-service:
    db_schema: ingestion_index
gateway:
  auth: bearer-jwt
  observability: prometheus + structured logs


3. Sequence of API Calls

The sequence of API calls demonstrates how the system coordinates between client, gateway, microservices, and databases to deliver results in real time. Each request begins at the front end, where the API Gateway ensures authentication and routes traffic to the appropriate service. The Search API then queries the catalog for product metadata, while the Filter API applies customer-selected parameters such as price or brand. In parallel, the Inventory service is consulted to confirm stock availability. The responses are aggregated, ranked, and paginated before returning a single, optimized payload to the customer. This flow guarantees speed, accuracy, and consistency, even under high demand.

By modeling the runtime interactions in a sequence diagram, the team validated how components interact at every step. This visualization helped confirm that error handling—such as for invalid inputs or unavailable databases—was integrated seamlessly into the API lifecycle. It also provided stakeholders with transparency on how search, filtering, and inventory checks align to create a smooth user experience.


The sequence diagram captures runtime interactions:

Flow highlights:

  1. Client issues search?q=...
  2. API Gateway forwards with the authentication context
  3. Search API requests facets and filters
  4. Catalog DB returns product documents; Inventory DB supplies stock snapshot
  5. Backend aggregates, ranks, and paginates
  6. System returns 200 OK with results and emits analytics events


encrtpP


Example request/response demonstration:

# Example request
curl -sS "https://api.example.com/v1/search?q=running+shoes&sort=price&page=1" \
  -H "Authorization: Bearer <token>"


4. BDD Scenarios and Use Cases

Behavior-Driven Development (BDD) was applied to clearly define the expected outcomes of the microservices. Scenarios describe not only successful paths, such as returning results when valid keywords or filters are provided, but also error conditions like missing parameters or invalid data formats. By scripting these cases upfront, the development and QA teams shared a unified understanding of system behavior, reducing defects and ensuring predictable results.

The use case model highlights the business value: customers initiate a search or apply filters, the system retrieves catalog and inventory data, and results are returned with accuracy and speed. Error and alternative paths were also considered, such as handling zero results or providing cached inventory if the live service is unavailable. Together, the BDD and use case approach guaranteed reliability, transparency, and stakeholder confidence in the APIs.


Core scenarios:


Core scenarios:

Feature: Product Search Microservice API

Scenario: Search API returns results
  GIVEN the client invokes the Search API with a valid keyword
  WHEN the backend processes the query
  THEN the API should return http status code 200
  AND the system should return the list of matching products

Scenario: Filter API applies selected filters
  GIVEN the client provides valid filter parameters
  WHEN the backend applies these filters
  THEN the API should return http status code 200
  AND the system should return only products matching the filters

Use Case Snippet:


encrtpP


Customers initiate a product search or apply filters. The system retrieves data from the Catalog DB, joins with Inventory data, ranks results, and returns them in real time. Analytics logs are generated for each interaction.


5. Results, Demonstration, and MySQL Applications

The Search & Filter Microservice API demonstrates its value by solving two central challenges in e-commerce: helping customers find the right products quickly and ensuring accurate inventory visibility. By combining keyword and attribute-based search with multi-facet filtering, customers can refine results using criteria like brand, price, and rating. This precision not only speeds up product discovery but also increases satisfaction and conversion rates.

Equally important is the integration of real-time inventory data. The system ensures that every search result reflects current stock levels, preventing frustration from out-of-stock items and supporting efficient fulfillment. Together, these methods create a seamless, trustworthy experience for customers while empowering businesses with reduced dependency on third-party services and actionable analytics.

Example: “See relevant, in-stock results when I search” (User Story Focus)

User Story:
As a customer, when I search for a product, I want relevant results and current stock status so I can decide quickly.

Interaction Flow:

  1. Client calls GET /v1/search?q=running+shoes&sort=relevance&page=1
  2. API validates input → builds search plan
  3. Backend executes keyword match + inventory join
  4. API returns ranked results with in_stock signal
# Targeted API call (trimmed)
curl -sS "https://api.example.com/v1/search?q=running+shoes&sort=relevance&page=1" \
  -H "Authorization: Bearer <token>"

– Backend core

SELECT
  p.id, p.name, p.price_cents,
  MATCH(p.name, p.description) AGAINST(:q IN NATURAL LANGUAGE MODE) AS relevance,
  (IFNULL(i.qty,0) > 0) AS in_stock
FROM products p
LEFT JOIN inventory i ON i.product_id = p.id
WHERE MATCH(p.name, p.description) AGAINST(:q IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC, p.price_cents ASC
LIMIT :page_size OFFSET :offset;


6. Results and Applications

Outcomes:

Applications:


Please feel free to contact me if you would like to share improvement advice or reflection about this exciting project, or if you have any inquiries related to my skills and experience.