Golang Product Service: Full-Text Search PostgreSQL tsvector, Inventory Clean Architecture
Implementing the Santekno Shop Product Service. ListProducts, GetProduct, SearchProducts with PostgreSQL full-text search tsvector, stock management, and an AI-ready pgvector column.
A Golang product service with PostgreSQL full-text search is the reliable foundation of any e-commerce catalog. From the spec in Article 01: the product catalog (4 ACs) covers listing products with filters, full-text search, and product detail. In this article we build the complete Product Repository with PostgreSQL tsvector search, optimistic locking for stock management, and a pgvector column that’s already primed for Topic 6.
07.01 ListProducts UseCase
The first use case is ListProducts — the entry point for displaying the catalog with safe pagination. The code below sanitizes the pagination input before passing it to the repository.
1// internal/usecase/product/list_products.go
2
3package product
4
5import (
6 "context"
7 "fmt"
8 "math"
9
10 domainProduct "github.com/santekno/santekno-shop/internal/domain/product"
11)
12
13type ListProductsOutput struct {
14 Products []*domainProduct.Product
15 Total int
16 Page int
17 Limit int
18 TotalPages int
19}
20
21func (uc *ListProductsUseCase) Execute(ctx context.Context, filter domainProduct.SearchFilter) (*ListProductsOutput, error) {
22 // Sanitize pagination
23 if filter.Page < 1 { filter.Page = 1 }
24 if filter.Limit < 1 { filter.Limit = 10 }
25 if filter.Limit > 50 { filter.Limit = 50 }
26
27 products, total, err := uc.productRepo.List(ctx, filter)
28 if err != nil {
29 return nil, fmt.Errorf("listProducts.Execute: %w", err)
30 }
31
32 totalPages := int(math.Ceil(float64(total) / float64(filter.Limit)))
33 if totalPages < 1 { totalPages = 1 }
34
35 return &ListProductsOutput{
36 Products: products,
37 Total: total,
38 Page: filter.Page,
39 Limit: filter.Limit,
40 TotalPages: totalPages,
41 }, nil
42}By capping Limit at 50 and defaulting to 10, we stop clients from forcing heavy queries while keeping responses lightweight.
07.02 GetProduct UseCase
The second use case fetches a single product by ID. Notice how the code below distinguishes “not found” from “inactive product”.
1// internal/usecase/product/get_product.go
2
3package product
4
5import (
6 "context"
7 "fmt"
8
9 domainProduct "github.com/santekno/santekno-shop/internal/domain/product"
10)
11
12func (uc *GetProductUseCase) Execute(ctx context.Context, id string) (*domainProduct.Product, error) {
13 p, err := uc.productRepo.GetByID(ctx, id)
14 if err != nil {
15 return nil, fmt.Errorf("getProduct.Execute: %w", err)
16 }
17 // AC3: Product doesn't exist → return nil → handler returns 404
18 if p == nil {
19 return nil, domainProduct.ErrProductNotFound
20 }
21 // AC4: inactive product is still returned (stock = 0)
22 return p, nil
23}The key decision here: an inactive product is still returned (per AC4); only a product that genuinely doesn’t exist triggers ErrProductNotFound.
07.03 PostgreSQL Product Repository: List with Full-Text Search
This is the heart of the Product Service — a repository that builds a dynamic query with tsvector full-text search. The code below assembles the WHERE conditions incrementally so it stays safe from SQL injection.
1// internal/repository/postgres/product_repository.go
2
3package postgres
4
5import (
6 "context"
7 "fmt"
8 "strings"
9
10 "github.com/jackc/pgx/v5"
11 "github.com/jackc/pgx/v5/pgxpool"
12
13 domainProduct "github.com/santekno/santekno-shop/internal/domain/product"
14 "github.com/santekno/santekno-shop/pkg/pagination"
15)
16
17type ProductRepository struct {
18 db *pgxpool.Pool
19}
20
21func NewProductRepository(db *pgxpool.Pool) *ProductRepository {
22 return &ProductRepository{db: db}
23}
24
25func (r *ProductRepository) List(ctx context.Context, filter domainProduct.SearchFilter) ([]*domainProduct.Product, int, error) {
26 // Build dynamic query
27 args := []interface{}{}
28 conditions := []string{"p.is_active = true"}
29 argIdx := 1
30
31 // AC2: Full-text search using tsvector
32 if filter.Query != "" {
33 conditions = append(conditions, fmt.Sprintf(
34 "p.search_vector @@ plainto_tsquery('indonesian', $%d)", argIdx))
35 args = append(args, filter.Query)
36 argIdx++
37 }
38
39 // AC1: Filter by category
40 if filter.CategoryID != nil {
41 conditions = append(conditions, fmt.Sprintf("p.category_id = $%d", argIdx))
42 args = append(args, filter.CategoryID)
43 argIdx++
44 }
45
46 // AC1: Filter by price range
47 if filter.MinPriceIDR != nil {
48 conditions = append(conditions, fmt.Sprintf("p.price_idr >= $%d", argIdx))
49 args = append(args, *filter.MinPriceIDR)
50 argIdx++
51 }
52 if filter.MaxPriceIDR != nil {
53 conditions = append(conditions, fmt.Sprintf("p.price_idr <= $%d", argIdx))
54 args = append(args, *filter.MaxPriceIDR)
55 argIdx++
56 }
57
58 whereClause := "WHERE " + strings.Join(conditions, " AND ")
59
60 // Count total
61 countQuery := fmt.Sprintf(`SELECT COUNT(*) FROM products p %s`, whereClause)
62 var total int
63 if err := r.db.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
64 return nil, 0, fmt.Errorf("productRepo.List count: %w", err)
65 }
66
67 // Fetch page
68 offset := pagination.Offset(filter.Page, filter.Limit)
69 args = append(args, filter.Limit, offset)
70
71 // Order by relevance if there's a query, otherwise by created_at
72 orderClause := "ORDER BY p.created_at DESC"
73 if filter.Query != "" {
74 orderClause = fmt.Sprintf(
75 "ORDER BY ts_rank(p.search_vector, plainto_tsquery('indonesian', $1)) DESC")
76 }
77
78 dataQuery := fmt.Sprintf(`
79 SELECT p.id, p.category_id, p.name, p.description, p.price_idr,
80 p.stock, p.reserved_stock, p.is_active, p.created_at, p.updated_at
81 FROM products p
82 %s
83 %s
84 LIMIT $%d OFFSET $%d`,
85 whereClause, orderClause, argIdx, argIdx+1)
86
87 rows, err := r.db.Query(ctx, dataQuery, args...)
88 if err != nil {
89 return nil, 0, fmt.Errorf("productRepo.List query: %w", err)
90 }
91 defer rows.Close()
92
93 var products []*domainProduct.Product
94 for rows.Next() {
95 var p domainProduct.Product
96 if err := rows.Scan(
97 &p.ID, &p.CategoryID, &p.Name, &p.Description, &p.PriceIDR,
98 &p.Stock, &p.ReservedStock, &p.IsActive, &p.CreatedAt, &p.UpdatedAt,
99 ); err != nil {
100 return nil, 0, fmt.Errorf("productRepo.List scan: %w", err)
101 }
102 products = append(products, &p)
103 }
104
105 if err := rows.Err(); err != nil {
106 return nil, 0, fmt.Errorf("productRepo.List rows: %w", err)
107 }
108
109 return products, total, nil
110}Building conditions as a slice and joining them with numbered placeholders ($1, $2, …) ensures every user input is bound as a parameter — never concatenated into the SQL string — so injection is sealed off tight.
07.04 GetByID and Stock Management
After List, we need GetByID and the stock operations. The code below shows the optimistic locking pattern for reserving and restoring stock without a separate SELECT.
1func (r *ProductRepository) GetByID(ctx context.Context, id string) (*domainProduct.Product, error) {
2 var p domainProduct.Product
3 err := r.db.QueryRow(ctx,
4 `SELECT id, category_id, name, description, price_idr,
5 stock, reserved_stock, is_active, created_at, updated_at
6 FROM products WHERE id = $1`,
7 id,
8 ).Scan(
9 &p.ID, &p.CategoryID, &p.Name, &p.Description, &p.PriceIDR,
10 &p.Stock, &p.ReservedStock, &p.IsActive, &p.CreatedAt, &p.UpdatedAt,
11 )
12 if err != nil {
13 if err == pgx.ErrNoRows { return nil, nil } // not found
14 return nil, fmt.Errorf("productRepo.GetByID: %w", err)
15 }
16 return &p, nil
17}
18
19// ReserveStock: optimistic locking pattern
20// UPDATE ... WHERE stock - reserved_stock >= qty
21// If 0 rows affected → insufficient stock
22func (r *ProductRepository) ReserveStock(ctx context.Context, id string, qty int) error {
23 result, err := r.db.Exec(ctx,
24 `UPDATE products
25 SET reserved_stock = reserved_stock + $1, updated_at = NOW()
26 WHERE id = $2
27 AND (stock - reserved_stock) >= $1
28 AND is_active = true`,
29 qty, id,
30 )
31 if err != nil {
32 return fmt.Errorf("productRepo.ReserveStock: %w", err)
33 }
34 if result.RowsAffected() == 0 {
35 return domainProduct.ErrInsufficientStock
36 }
37 return nil
38}
39
40// RestoreStock: return reserved back to available (on order cancel)
41func (r *ProductRepository) RestoreStock(ctx context.Context, id string, qty int) error {
42 _, err := r.db.Exec(ctx,
43 `UPDATE products
44 SET reserved_stock = GREATEST(0, reserved_stock - $1), updated_at = NOW()
45 WHERE id = $2`,
46 qty, id,
47 )
48 if err != nil {
49 return fmt.Errorf("productRepo.RestoreStock: %w", err)
50 }
51 return nil
52}The key to ReserveStock: the check (stock - reserved_stock) >= $1 happens inside the same UPDATE, so there’s no race window between “check stock” and “decrement stock” — atomicity is guaranteed by PostgreSQL.
07.05 Product Handler
The handler layer translates HTTP query params into a use case filter. The code below parses optional parameters like category and price range with validation.
1// delivery/http/handler/product_handler.go
2
3package handler
4
5import (
6 "net/http"
7
8 "github.com/google/uuid"
9 "github.com/labstack/echo/v4"
10 "github.com/santekno/santekno-shop/internal/domain/product"
11 "github.com/santekno/santekno-shop/internal/usecase/product"
12 "github.com/santekno/santekno-shop/pkg/apierror"
13 "github.com/santekno/santekno-shop/pkg/pagination"
14)
15
16func (h *ProductHandler) List(c echo.Context) error {
17 p := pagination.ParseParams(c)
18
19 filter := product.SearchFilter{
20 Query: c.QueryParam("q"),
21 Page: p.Page,
22 Limit: p.Limit,
23 }
24
25 // Optional category filter
26 if catStr := c.QueryParam("category"); catStr != "" {
27 catID, err := uuid.Parse(catStr)
28 if err != nil {
29 return c.JSON(http.StatusBadRequest, apierror.Response{Error: "INVALID_CATEGORY_ID"})
30 }
31 filter.CategoryID = &catID
32 }
33
34 // Optional price filter
35 if minStr := c.QueryParam("min_price"); minStr != "" {
36 var minPrice int64
37 if _, err := fmt.Sscan(minStr, &minPrice); err == nil && minPrice >= 0 {
38 filter.MinPriceIDR = &minPrice
39 }
40 }
41 if maxStr := c.QueryParam("max_price"); maxStr != "" {
42 var maxPrice int64
43 if _, err := fmt.Sscan(maxStr, &maxPrice); err == nil && maxPrice >= 0 {
44 filter.MaxPriceIDR = &maxPrice
45 }
46 }
47
48 out, err := h.listUC.Execute(c.Request().Context(), filter)
49 if err != nil {
50 return apierror.Handle(c, err)
51 }
52
53 return c.JSON(http.StatusOK, map[string]interface{}{
54 "data": toProductResponses(out.Products),
55 "pagination": pagination.NewMeta(out.Page, out.Limit, out.Total),
56 })
57}
58
59func (h *ProductHandler) Get(c echo.Context) error {
60 id := c.Param("id")
61 if _, err := uuid.Parse(id); err != nil {
62 return c.JSON(http.StatusBadRequest, apierror.Response{Error: "INVALID_PRODUCT_ID"})
63 }
64
65 p, err := h.getUC.Execute(c.Request().Context(), id)
66 if err != nil {
67 return apierror.Handle(c, err)
68 }
69
70 return c.JSON(http.StatusOK, toProductResponse(p))
71}
72
73// Response DTOs
74type productResponse struct {
75 ID string `json:"id"`
76 Name string `json:"name"`
77 Description string `json:"description,omitempty"`
78 PriceIDR int64 `json:"price_idr"`
79 Stock int `json:"stock"`
80 IsActive bool `json:"is_active"`
81 CreatedAt string `json:"created_at"`
82}
83
84func toProductResponse(p *product.Product) productResponse {
85 return productResponse{
86 ID: p.ID.String(),
87 Name: p.Name,
88 Description: p.Description,
89 PriceIDR: p.PriceIDR,
90 Stock: p.AvailableStock(), // show available, not total
91 IsActive: p.IsActive,
92 CreatedAt: p.CreatedAt.Format("2006-01-02T15:04:05Z"),
93 }
94}
95
96func toProductResponses(products []*product.Product) []productResponse {
97 resp := make([]productResponse, len(products))
98 for i, p := range products { resp[i] = toProductResponse(p) }
99 return resp
100}Notice that toProductResponse returns AvailableStock(), not the raw Stock — the customer sees the stock that can actually be bought, not a number partly reserved by other orders.
07.06 Full-Text Search: tsvector Setup
Indonesian full-text search relies on a tsvector column and the right dictionary. The SQL script below repopulates search_vector and verifies the trigger from an earlier migration.
1-- migrations/20260701000007_add_search_vector.up.sql
2
3-- Make sure the 'indonesian' PostgreSQL dictionary is available
4-- (alternative: use 'simple' for testing)
5
6-- Populate search_vector for existing products
7UPDATE products
8SET search_vector = to_tsvector('indonesian',
9 COALESCE(name, '') || ' ' || COALESCE(description, ''));
10
11-- Trigger already exists from the Article 02 migration
12-- (products_search_vector_trigger)
13-- But we verify:
14SELECT tgname FROM pg_trigger WHERE tgrelid = 'products'::regclass;
15-- Expected: products_search_vector_trigger
16
17-- Test search:
18SELECT name, price_idr
19FROM products
20WHERE search_vector @@ plainto_tsquery('indonesian', 'running shoes')
21ORDER BY ts_rank(search_vector, plainto_tsquery('indonesian', 'running shoes')) DESC;With the trigger populating search_vector automatically on every insert/update, we never have to refresh the index manually — search always stays consistent with the latest data.
07.07 Product Tests
Every AC must have a test. The suite below uses testify + gomock to verify pagination, default values, and query pass-through to the repository.
1// internal/usecase/product/list_products_test.go
2
3package product_test
4
5import (
6 "context"
7 "testing"
8
9 "github.com/stretchr/testify/suite"
10 "go.uber.org/mock/gomock"
11
12 domainProduct "github.com/santekno/santekno-shop/internal/domain/product"
13 productUC "github.com/santekno/santekno-shop/internal/usecase/product"
14 "github.com/santekno/santekno-shop/testutil/mock"
15)
16
17type ListProductsSuite struct {
18 suite.Suite
19 ctrl *gomock.Controller
20 productRepo *mock.MockProductRepository
21 uc *productUC.ListProductsUseCase
22}
23
24func (s *ListProductsSuite) SetupTest() {
25 s.ctrl = gomock.NewController(s.T())
26 s.productRepo = mock.NewMockProductRepository(s.ctrl)
27 s.uc = productUC.NewListProductsUseCase(s.productRepo)
28}
29
30// AC1: List products with pagination
31func (s *ListProductsSuite) TestListProducts_ValidFilter_ReturnsPaginatedProducts() {
32 mockProducts := []*domainProduct.Product{
33 {Name: "Running Shoe A", PriceIDR: 500_000_00, Stock: 10},
34 {Name: "Running Shoe B", PriceIDR: 750_000_00, Stock: 5},
35 }
36
37 s.productRepo.EXPECT().
38 List(gomock.Any(), gomock.Any()).
39 Return(mockProducts, 2, nil)
40
41 out, err := s.uc.Execute(context.Background(), domainProduct.SearchFilter{
42 Page: 1, Limit: 10,
43 })
44
45 s.NoError(err)
46 s.Len(out.Products, 2)
47 s.Equal(2, out.Total)
48 s.Equal(1, out.TotalPages)
49}
50
51// AC3: Default pagination values
52func (s *ListProductsSuite) TestListProducts_ZeroPage_DefaultsToPage1() {
53 s.productRepo.EXPECT().
54 List(gomock.Any(), gomock.AssignableToTypeOf(domainProduct.SearchFilter{})).
55 DoAndReturn(func(_ context.Context, f domainProduct.SearchFilter) ([]*domainProduct.Product, int, error) {
56 s.Equal(1, f.Page) // default
57 s.Equal(10, f.Limit) // default
58 return nil, 0, nil
59 })
60
61 _, err := s.uc.Execute(context.Background(), domainProduct.SearchFilter{Page: 0, Limit: 0})
62 s.NoError(err)
63}
64
65// AC2: Search filter applied
66func (s *ListProductsSuite) TestListProducts_WithQuery_PassesQueryToRepo() {
67 s.productRepo.EXPECT().
68 List(gomock.Any(), gomock.AssignableToTypeOf(domainProduct.SearchFilter{})).
69 DoAndReturn(func(_ context.Context, f domainProduct.SearchFilter) ([]*domainProduct.Product, int, error) {
70 s.Equal("running shoes", f.Query)
71 return nil, 0, nil
72 })
73
74 _, err := s.uc.Execute(context.Background(), domainProduct.SearchFilter{Query: "running shoes"})
75 s.NoError(err)
76}
77
78func TestListProductsSuite(t *testing.T) {
79 suite.Run(t, new(ListProductsSuite))
80}The DoAndReturn pattern lets us assert the values the repository actually receives — not just the final result — so the default pagination logic is genuinely tested, not assumed.
07.08 Get Product Tests
For GetProduct, the two crucial cases are “not found” and “inactive”. The tests below make sure the two are treated differently, exactly as the spec requires.
1// internal/usecase/product/get_product_test.go
2
3// AC3: Product not found
4func (s *GetProductSuite) TestGetProduct_NotFound_ReturnsProductNotFound() {
5 s.productRepo.EXPECT().
6 GetByID(gomock.Any(), "non-existent-uuid").
7 Return(nil, nil) // nil, nil = not found
8
9 _, err := s.uc.Execute(context.Background(), "non-existent-uuid")
10 s.ErrorIs(err, domainProduct.ErrProductNotFound)
11}
12
13// AC4: Inactive product is still returned (stock = 0)
14func (s *GetProductSuite) TestGetProduct_Inactive_ReturnsWithStock0() {
15 inactiveProduct := &domainProduct.Product{
16 Name: "Inactive Product",
17 PriceIDR: 100_000_00,
18 Stock: 0,
19 IsActive: false,
20 }
21 s.productRepo.EXPECT().
22 GetByID(gomock.Any(), "some-uuid").
23 Return(inactiveProduct, nil)
24
25 p, err := s.uc.Execute(context.Background(), "some-uuid")
26 s.NoError(err)
27 s.NotNil(p) // still returned
28 s.Equal(0, p.Stock) // stock 0
29 s.False(p.IsActive) // inactive status
30}The AC4 test locks in an important contract: the backend does not hide inactive products — the decision to show a “Not Available” badge is left to the frontend.
07.09 pgvector Column: AI-Ready from Day One
One of the most strategic architecture decisions is preparing the embedding column from the very start. The script below shows the planned use of pgvector for semantic search in upcoming topics.
1-- The embedding column already exists from the Article 02 migration:
2-- embedding VECTOR(1536) -- for OpenAI text-embedding-ada-002
3
4-- In Topic 6, we'll populate this column:
5-- UPDATE products SET embedding = $1 WHERE id = $2
6
7-- In Topic 7, we'll use it for semantic search:
8-- SELECT * FROM products
9-- ORDER BY embedding <=> $1::vector -- cosine distance
10-- LIMIT 10
11
12-- Index for semantic search (to be created in Topic 6):
13-- CREATE INDEX CONCURRENTLY idx_products_embedding
14-- ON products USING ivfflat(embedding vector_cosine_ops)
15-- WITH (lists = 100);
16
17-- Verify the extension is available:
18SELECT * FROM pg_extension WHERE extname = 'vector';With the embedding column present from day one, migrating to semantic search in Topic 6 requires no schema change at all — just populating data and creating an index.
07.10 Seed Data for Development
Development needs realistic test data. The seed script below inserts sample categories and products, then verifies that search_vector is populated automatically.
1-- testdata/seed_products.sql
2-- Run: psql "$DATABASE_URL" < testdata/seed_products.sql
3
4INSERT INTO categories (id, name, slug) VALUES
5 ('cat-electronics-uuid', 'Electronics', 'electronics'),
6 ('cat-fashion-uuid', 'Fashion & Apparel', 'fashion'),
7 ('cat-sport-uuid', 'Sports & Fitness', 'sports');
8
9INSERT INTO products (id, category_id, name, description, price_idr, stock, is_active) VALUES
10 (
11 'prod-001-uuid',
12 'cat-electronics-uuid',
13 'Smartphone Santekno X1',
14 'Flagship smartphone with a 200MP camera and a 6000mAh battery',
15 599_999_900, -- Rp 5,999,999
16 50,
17 true
18 ),
19 (
20 'prod-002-uuid',
21 'cat-sport-uuid',
22 'Santekno Pro Running Shoes',
23 'Lightweight running shoes with the latest air-cushion technology',
24 89_999_900, -- Rp 899,999
25 200,
26 true
27 ),
28 (
29 'prod-003-uuid',
30 'cat-fashion-uuid',
31 'Santekno Classic Batik Shirt',
32 'Premium batik shirt with a nusantara motif, premium cotton fabric',
33 29_999_900, -- Rp 299,999
34 100,
35 true
36 );
37
38-- Verify the search vector is populated via the trigger:
39SELECT name, search_vector IS NOT NULL AS has_vector FROM products;The verification query at the end confirms the tsvector trigger really works on the seed data — if has_vector is false, the trigger isn’t installed correctly.
07.11 Repository Integration Test
Unit tests alone aren’t enough to validate the real SQL query. The integration test below runs search and reserve-stock against a real PostgreSQL.
1// internal/repository/postgres/product_repository_integration_test.go
2//go:build integration
3
4func TestProductRepository_List_WithSearch(t *testing.T) {
5 pool := setupDB(t)
6 repo := postgres.NewProductRepository(pool)
7 ctx := context.Background()
8
9 // Seed test data
10 _, err := pool.Exec(ctx,
11 `INSERT INTO products (name, description, price_idr, stock, is_active)
12 VALUES ($1, $2, $3, $4, $5)`,
13 "Test Running Shoes", "Lightweight shoes for running", 89_999_900, 10, true)
14 require.NoError(t, err)
15
16 // Test full-text search
17 products, total, err := repo.List(ctx, product.SearchFilter{
18 Query: "running shoes", Page: 1, Limit: 10,
19 })
20 require.NoError(t, err)
21 assert.GreaterOrEqual(t, total, 1)
22 assert.GreaterOrEqual(t, len(products), 1)
23
24 // Cleanup
25 pool.Exec(ctx, "DELETE FROM products WHERE name = 'Test Running Shoes'")
26}
27
28func TestProductRepository_ReserveStock_Success(t *testing.T) {
29 pool := setupDB(t)
30 repo := postgres.NewProductRepository(pool)
31 ctx := context.Background()
32
33 // Create test product with 10 stock
34 var productID string
35 pool.QueryRow(ctx,
36 `INSERT INTO products (name, price_idr, stock, is_active)
37 VALUES ('Test Product', 100000, 10, true) RETURNING id`,
38 ).Scan(&productID)
39
40 err := repo.ReserveStock(ctx, productID, 3)
41 assert.NoError(t, err)
42
43 // Verify reserved_stock increased
44 var reserved int
45 pool.QueryRow(ctx, "SELECT reserved_stock FROM products WHERE id = $1", productID).Scan(&reserved)
46 assert.Equal(t, 3, reserved)
47
48 // Cleanup
49 pool.Exec(ctx, "DELETE FROM products WHERE id = $1", productID)
50}
51
52func TestProductRepository_ReserveStock_InsufficientStock(t *testing.T) {
53 pool := setupDB(t)
54 repo := postgres.NewProductRepository(pool)
55 ctx := context.Background()
56
57 var productID string
58 pool.QueryRow(ctx,
59 `INSERT INTO products (name, price_idr, stock, is_active)
60 VALUES ('Low Stock', 100000, 2, true) RETURNING id`,
61 ).Scan(&productID)
62
63 err := repo.ReserveStock(ctx, productID, 5) // request 5, only 2 available
64 assert.ErrorIs(t, err, product.ErrInsufficientStock) // optimistic lock fail
65
66 pool.Exec(ctx, "DELETE FROM products WHERE id = $1", productID)
67}The ReserveStock_InsufficientStock integration test proves the optimistic lock genuinely rejects over-reservation at the database level — not just in Go logic that might be built on wrong assumptions.
07.12 Caching Strategy (Preview)
For a high-traffic catalog, caching is a necessity. The code below previews the CachedProductRepository pattern that will be fully implemented in Article 13.
1// Preview: Redis caching for the product list
2// To be implemented in Article 13 (Redis Caching Strategy)
3
4// For now, the product repository talks to the DB directly.
5// In Article 13, we wrap it with a Redis cache:
6
7type CachedProductRepository struct {
8 repo domainProduct.Repository
9 cache *redis.Client
10}
11
12func (r *CachedProductRepository) GetByID(ctx context.Context, id string) (*domainProduct.Product, error) {
13 key := "product:" + id
14
15 // Try cache first
16 cached, err := r.cache.Get(ctx, key).Bytes()
17 if err == nil {
18 var p domainProduct.Product
19 json.Unmarshal(cached, &p)
20 return &p, nil
21 }
22
23 // Cache miss: fetch from DB
24 p, err := r.repo.GetByID(ctx, id)
25 if err != nil || p == nil { return p, err }
26
27 // Store in cache (TTL 5 minutes)
28 data, _ := json.Marshal(p)
29 r.cache.Set(ctx, key, data, 5*time.Minute)
30
31 return p, nil
32}This decorator pattern keeps the Repository interface unchanged — the cache is added as a wrapper layer, so the use case never needs to know whether the data came from Redis or PostgreSQL.
07.13 Specify Audit After Article 07
Every article raises spec coverage. The specify audit command below shows which features are covered once the Product Service is done.
1specify audit --output json | jq '{
2 score: .score,
3 covered: [.features[] | select(.status == "covered") | .feature]
4}'
5
6# Expected after Article 07:
7# {
8# "score": 50,
9# "covered": [
10# "user-registration", ← Article 06
11# "user-login", ← Article 06
12# "product-catalog" ← Article 07 (new!)
13# ]
14# }The score rises from 35 to 50 once product-catalog is covered — measurable proof that the implementation genuinely satisfies the spec, not just “looks like it works”.
07.14 SearchProducts: AC2 Detail
Let’s dig deeper into AC2 about full-text search. The example below maps an HTTP query to a database query and shows how to test it with curl.
1# AC2: "Search using full-text search"
2# Query: GET /api/v1/products?q=running+shoes
3# Database: WHERE search_vector @@ plainto_tsquery('indonesian', 'running shoes')
4# Sorted: ORDER BY ts_rank(search_vector, ...) DESC
5
6# Test with curl:
7curl "http://localhost:8080/api/v1/products?q=running+shoes"
8
9# Expected response:
10# {
11# "data": [
12# {"id": "...", "name": "Santekno Pro Running Shoes", ...},
13# ],
14# "pagination": {"page": 1, "limit": 10, "total": 1, "total_pages": 1}
15# }
16
17# Test with a price filter:
18curl "http://localhost:8080/api/v1/products?min_price=50000000&max_price=100000000"
19# Products between Rp 500,000 - Rp 1,000,000 (in cents)Notice the search results are already sorted by ts_rank — the products most relevant to the keyword appear at the top, not just in creation-time order.
07.15 Out-of-Stock Product Behavior
AC4 has a specific rule: sold-out products are still displayed. The notes below summarize how the repository and handler treat out-of-stock products.
1// AC4: "Out-of-stock products are still returned, not hidden"
2
3// Repository: no is_active = false or stock > 0 filter for GetByID
4// Query:
5// SELECT * FROM products WHERE id = $1
6// (without WHERE stock > 0)
7
8// Handler response:
9// stock: p.AvailableStock() -- can return 0
10// is_active: p.IsActive -- can return false
11
12// The frontend handles the "Sold Out" or "Not Available" display
13// The backend does not hide products from search results
14
15// Note: ListProducts ONLY returns is_active = true
16// GetByID: returns any product including inactive and out-of-stockDistinguish the two endpoints: ListProducts still filters is_active = true (a clean catalog), while GetByID returns any product — perfect for a detail page a customer has bookmarked even when the stock is already gone.
07.16 Product Images
The UI needs product images even though they aren’t in the spec yet. The structure below shows the domain extension to support images without breaking the spec contract.
1// The domain entity supports images (not yet spec'd, but needed for the UI)
2
3type Product struct {
4 // ... existing fields
5 Images []ProductImage
6}
7
8type ProductImage struct {
9 ID uuid.UUID
10 ProductID uuid.UUID
11 URL string
12 IsPrimary bool
13 SortOrder int
14}
15
16// Repository extension (outside the spec):
17func (r *ProductRepository) GetImagesForProduct(ctx context.Context, productID string) ([]domainProduct.ProductImage, error) {
18 rows, err := r.db.Query(ctx,
19 `SELECT id, product_id, url, is_primary, sort_order
20 FROM product_images WHERE product_id = $1 ORDER BY sort_order`,
21 productID)
22 // ... scan rows
23}Marking this feature “outside the spec” matters for SDD discipline — we’re still allowed to add genuine UI needs, as long as it’s clear that they aren’t part of the audited ACs.
07.17 Performance: Query Optimization
The catalog query must be fast at scale. The script below defines the critical indexes and shows how to verify the execution plan.
1-- Indexes that matter for the Product Service
2
3-- For ListProducts with filters
4CREATE INDEX idx_products_category_active
5 ON products(category_id, is_active)
6 WHERE is_active = true;
7
8-- For the price range filter
9CREATE INDEX idx_products_price
10 ON products(price_idr)
11 WHERE is_active = true;
12
13-- For full-text search (already exists from Article 02)
14CREATE INDEX idx_products_search_vector
15 ON products USING GIN(search_vector);
16
17-- Query analysis:
18EXPLAIN ANALYZE
19SELECT * FROM products
20WHERE search_vector @@ plainto_tsquery('indonesian', 'running shoes')
21ORDER BY ts_rank(search_vector, plainto_tsquery('indonesian', 'running shoes')) DESC
22LIMIT 10;
23-- Expected: "Bitmap Index Scan on idx_products_search_vector"Seeing “Bitmap Index Scan” in the EXPLAIN ANALYZE output confirms the GIN index is being used — if “Seq Scan” appears instead, the index isn’t used and search will be slow in production.
07.18 Atomic Stock Operation Pattern
Race conditions are inventory’s main enemy. The diagram below compares the scenarios without and with optimistic locking when two users check out the last unit.
1RACE CONDITION SCENARIO:
2
3User A and User B check out a product with stock 1 at the same time:
4
5WITHOUT optimistic locking:
6 A: SELECT stock = 1 → OK
7 B: SELECT stock = 1 → OK
8 A: UPDATE stock = 0 → OK (1 sold)
9 B: UPDATE stock = 0 → OK (oversold! -1 stock)
10
11WITH optimistic locking (ReserveStock):
12 A: UPDATE SET reserved = reserved + 1
13 WHERE (stock - reserved) >= 1
14 → 1 row affected → SUCCESS
15 B: UPDATE SET reserved = reserved + 1
16 WHERE (stock - reserved) >= 1
17 → stock=1, reserved=1, available=0
18 → 0 rows affected → ErrInsufficientStock
19
20Single SQL update = atomic in PostgreSQL.
21No need to SELECT first — no race window.The diagram’s conclusion is clear: combining the check and update in a single UPDATE statement removes the race window entirely — no explicit lock or extra transaction needed.
07.19 Soldout Handling
How do we show the correct stock to the customer? The entity method below computes available stock and ensures the handler never leaks the reserved number.
1// Sold-out product: stock - reserved_stock = 0
2// AvailableStock() method on the entity:
3
4func (p *Product) AvailableStock() int {
5 available := p.Stock - p.ReservedStock
6 if available < 0 { return 0 } // safety
7 return available
8}
9
10// In the response: show available_stock, not raw stock
11// Handler:
12return c.JSON(200, map[string]interface{}{
13 "id": p.ID,
14 "name": p.Name,
15 "price_idr": p.PriceIDR,
16 "stock": p.AvailableStock(), // ← available, not total
17 "is_active": p.IsActive,
18})Placing AvailableStock() as an entity method (not in the SQL query or the handler) keeps the business rule in the domain layer — easy to test without a database and consistent everywhere it’s used.
07.20 Summary: Product Service Done
The Product Service is now fully functional. The four pillars below summarize what we built.
ListProducts: dynamic SQL with tsvector full-text search, category/price filters, pagination, sorted by relevance when there’s a query.
GetProduct: single product by ID, returns both active and inactive products (stock 0 is still returned per AC4).
ReserveStock: optimistic locking via a single UPDATE — no SELECT needed, race-free, atomic.
pgvector column: embedding VECTOR(1536) already exists, empty for now, to be populated in Topic 6.
The specify audit score after Article 07 rises to ~50/100 (from 35/100). The catalog foundation is done — next we enter the heart of transactions: the Order Service.