feat(M3): 只读 API 端点实现

- store: pgx 只读数据访问层(productByGTIN/ByID/search/nutriments/msrp/brands/categories/source)
- handler: 真实查询替换 501 占位, 统一分页 + 错误信封, MSRP 带免责声明无购买入口
- main: pgxpool 连接池接线
- search: 名称模糊 + 分类子树过滤(ltree <@)
- 测试: healthz/pageParams 单测 + DB-backed handler 集成测试(无库自动跳过)
- CI: Go job 增加 postgres service + migrate up, 实跑 DB 测试
- 依赖: pgx v5.7.2 (固定到兼容 go1.23 的版本)
- 本地实跑: 8 个端点对真实 OFF 数据返回正确(barcode/search/nutriments/msrp/brands/categories/source/404)

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

.github/workflows/ci.yml

@@ -12,12 +12,31 @@ jobs:
     defaults:
       run:
         working-directory: api
+    services:
+      postgres:
+        image: postgres:16-alpine
+        env:
+          POSTGRES_USER: opengoods
+          POSTGRES_PASSWORD: opengoods
+          POSTGRES_DB: opengoods
+        ports:
+          - "5432:5432"
+        options: >-
+          --health-cmd "pg_isready -U opengoods"
+          --health-interval 5s --health-timeout 5s --health-retries 10
+    env:
+      OPENGOODS_DATABASE_URL: postgres://opengoods:opengoods@localhost:5432/opengoods?sslmode=disable
     steps:
       - uses: actions/checkout@v4
       - uses: actions/setup-go@v5
         with:
           go-version: "1.23"
           cache-dependency-path: api/go.sum
+      - name: Apply migrations
+        working-directory: .
+        run: |
+          go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@v4.18.1
+          migrate -path migrations -database "$OPENGOODS_DATABASE_URL" up
       - name: Verify gofmt
         run: test -z "$(gofmt -l .)"
       - run: go vet ./...

api/cmd/server/main.go

@@ -2,20 +2,39 @@
 package main
 
 import (
+	"context"
 	"log"
 	"net/http"
 	"time"
 
+	"github.com/jackc/pgx/v5/pgxpool"
+
 	"github.com/baicai2026-baicai/goods/api/internal/config"
 	"github.com/baicai2026-baicai/goods/api/internal/handler"
+	"github.com/baicai2026-baicai/goods/api/internal/store"
 )
 
 func main() {
 	cfg := config.Load()
 
+	ctx := context.Background()
+	pool, err := pgxpool.New(ctx, cfg.DatabaseURL)
+	if err != nil {
+		log.Fatalf("failed to create db pool: %v", err)
+	}
+	defer pool.Close()
+
+	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
+	defer cancel()
+	if err := pool.Ping(pingCtx); err != nil {
+		log.Printf("warning: database not reachable at startup: %v", err)
+	}
+
+	h := handler.New(store.New(pool))
+
 	srv := &http.Server{
 		Addr:              cfg.Addr,
-		Handler:           handler.Router(),
+		Handler:           h.Router(),
 		ReadHeaderTimeout: 10 * time.Second,
 	}
 

api/go.mod

@@ -2,4 +2,16 @@ module github.com/baicai2026-baicai/goods/api
 
 go 1.23.4
 
-require github.com/go-chi/chi/v5 v5.1.0
+require (
+	github.com/go-chi/chi/v5 v5.1.0
+	github.com/jackc/pgx/v5 v5.7.2
+)
+
+require (
+	github.com/jackc/pgpassfile v1.0.0 // indirect
+	github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
+	github.com/jackc/puddle/v2 v2.2.2 // indirect
+	golang.org/x/crypto v0.31.0 // indirect
+	golang.org/x/sync v0.10.0 // indirect
+	golang.org/x/text v0.21.0 // indirect
+)

api/go.sum

@@ -1,2 +1,30 @@
+github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
+github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
+github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/go-chi/chi/v5 v5.1.0 h1:acVI1TYaD+hhedDJ3r54HyA6sExp3HfXq7QWEEY/xMw=
 github.com/go-chi/chi/v5 v5.1.0/go.mod h1:DslCQbL2OYiznFReuXYUmQ2hGd1aDpCnlMNITLSKoi8=
+github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
+github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
+github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo=
+github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
+github.com/jackc/pgx/v5 v5.7.2 h1:mLoDLV6sonKlvjIEsV56SkWNCnuNv531l94GaIzO+XI=
+github.com/jackc/pgx/v5 v5.7.2/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ=
+github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo=
+github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
+github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
+github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
+github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
+github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
+github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
+github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk=
+github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
+golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U=
+golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk=
+golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ=
+golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
+golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo=
+golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ=
+gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
+gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
+gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=

api/internal/handler/handler.go

@@ -1,5 +1,4 @@
 // Package handler wires up the public, read-only OpenGoods HTTP API.
-//
 // The OpenGoods service is a public-good product information API: it only
 // collects and serves product facts. It exposes no purchase, checkout, or
 // commerce endpoints by design.
@@ -7,48 +6,188 @@ package handler
 
 import (
 	"encoding/json"
+	"errors"
 	"net/http"
+	"strconv"
 
 	"github.com/go-chi/chi/v5"
 	"github.com/go-chi/chi/v5/middleware"
+
+	"github.com/baicai2026-baicai/goods/api/internal/store"
 )
 
 // APIVersion is the current public API version prefix.
 const APIVersion = "v1"
 
+const (
+	defaultPageSize = 20
+	maxPageSize     = 100
+)
+
+// Handler holds dependencies shared by the HTTP routes.
+type Handler struct {
+	store *store.Store
+}
+
+// New constructs a Handler backed by the given store.
+func New(s *store.Store) *Handler {
+	return &Handler{store: s}
+}
+
 // Router builds the top-level HTTP handler with middleware and routes mounted.
-func Router() http.Handler {
+func (h *Handler) Router() http.Handler {
 	r := chi.NewRouter()
 	r.Use(middleware.RequestID)
 	r.Use(middleware.RealIP)
 	r.Use(middleware.Recoverer)
 
-	r.Get("/healthz", Healthz)
+	r.Get("/healthz", h.Healthz)
 
 	r.Route("/api/"+APIVersion, func(r chi.Router) {
 		r.Route("/products", func(r chi.Router) {
-			r.Get("/barcode/{gtin}", notImplemented)
-			r.Get("/search", notImplemented)
-			r.Get("/{id}", notImplemented)
-			r.Get("/{id}/nutriments", notImplemented)
-			r.Get("/{id}/msrp", notImplemented)
+			r.Get("/barcode/{gtin}", h.ProductByBarcode)
+			r.Get("/search", h.SearchProducts)
+			r.Get("/{id}", h.ProductByID)
+			r.Get("/{id}/nutriments", h.ProductNutriments)
+			r.Get("/{id}/msrp", h.ProductMSRP)
 		})
-		r.Get("/brands", notImplemented)
-		r.Get("/categories", notImplemented)
-		r.Get("/sources/{id}", notImplemented)
+		r.Get("/brands", h.ListBrands)
+		r.Get("/categories", h.ListCategories)
+		r.Get("/sources/{id}", h.SourceByID)
 	})
 
 	return r
 }
 
 // Healthz reports liveness of the service.
-func Healthz(w http.ResponseWriter, r *http.Request) {
+func (h *Handler) Healthz(w http.ResponseWriter, r *http.Request) {
 	writeJSON(w, http.StatusOK, map[string]string{"status": "ok"})
 }
 
-// notImplemented is a placeholder for endpoints scoped to later milestones.
-func notImplemented(w http.ResponseWriter, r *http.Request) {
-	writeError(w, r, http.StatusNotImplemented, "not_implemented", "endpoint not implemented yet")
+// ProductByBarcode returns a product by its GTIN.
+func (h *Handler) ProductByBarcode(w http.ResponseWriter, r *http.Request) {
+	p, err := h.store.ProductByGTIN(r.Context(), chi.URLParam(r, "gtin"))
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, p)
+}
+
+// ProductByID returns a product by its UUID.
+func (h *Handler) ProductByID(w http.ResponseWriter, r *http.Request) {
+	p, err := h.store.ProductByID(r.Context(), chi.URLParam(r, "id"))
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, p)
+}
+
+// SearchProducts runs a fuzzy name search with optional category filter + paging.
+func (h *Handler) SearchProducts(w http.ResponseWriter, r *http.Request) {
+	q := r.URL.Query().Get("q")
+	category := r.URL.Query().Get("category")
+	page, size := pageParams(r)
+
+	items, total, err := h.store.SearchProducts(r.Context(), q, category, size, (page-1)*size)
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, map[string]any{
+		"items": items,
+		"page":  page,
+		"size":  size,
+		"total": total,
+	})
+}
+
+// ProductNutriments returns just the nutrition facts of a product.
+func (h *Handler) ProductNutriments(w http.ResponseWriter, r *http.Request) {
+	n, err := h.store.Nutriments(r.Context(), chi.URLParam(r, "id"))
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, n)
+}
+
+// ProductMSRP returns official suggested retail price snapshots (no purchase link).
+func (h *Handler) ProductMSRP(w http.ResponseWriter, r *http.Request) {
+	items, err := h.store.ListMSRP(r.Context(), chi.URLParam(r, "id"))
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, map[string]any{
+		"items":      items,
+		"disclaimer": "厂商建议零售价历史快照，仅供参考，不构成购买建议，本服务不提供任何购买入口。",
+	})
+}
+
+// ListBrands returns a paginated list of brands.
+func (h *Handler) ListBrands(w http.ResponseWriter, r *http.Request) {
+	page, size := pageParams(r)
+	items, total, err := h.store.ListBrands(r.Context(), size, (page-1)*size)
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, map[string]any{
+		"items": items, "page": page, "size": size, "total": total,
+	})
+}
+
+// ListCategories returns the full category tree.
+func (h *Handler) ListCategories(w http.ResponseWriter, r *http.Request) {
+	items, err := h.store.ListCategories(r.Context())
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, map[string]any{"items": items})
+}
+
+// SourceByID returns a single data source.
+func (h *Handler) SourceByID(w http.ResponseWriter, r *http.Request) {
+	src, err := h.store.SourceByID(r.Context(), chi.URLParam(r, "id"))
+	if h.handleErr(w, r, err) {
+		return
+	}
+	writeJSON(w, http.StatusOK, src)
+}
+
+// handleErr writes an appropriate error response; returns true if it handled one.
+func (h *Handler) handleErr(w http.ResponseWriter, r *http.Request, err error) bool {
+	if err == nil {
+		return false
+	}
+	if errors.Is(err, store.ErrNotFound) {
+		writeError(w, r, http.StatusNotFound, "not_found", "resource not found")
+		return true
+	}
+	writeError(w, r, http.StatusInternalServerError, "internal_error", "internal server error")
+	return true
+}
+
+func pageParams(r *http.Request) (page, size int) {
+	page = atoiDefault(r.URL.Query().Get("page"), 1)
+	if page < 1 {
+		page = 1
+	}
+	size = atoiDefault(r.URL.Query().Get("size"), defaultPageSize)
+	if size < 1 {
+		size = defaultPageSize
+	}
+	if size > maxPageSize {
+		size = maxPageSize
+	}
+	return page, size
+}
+
+func atoiDefault(s string, fallback int) int {
+	if s == "" {
+		return fallback
+	}
+	v, err := strconv.Atoi(s)
+	if err != nil {
+		return fallback
+	}
+	return v
 }
 
 func writeJSON(w http.ResponseWriter, status int, body any) {

api/internal/handler/handler_db_test.go

@@ -0,0 +1,134 @@
+package handler
+
+import (
+	"context"
+	"encoding/json"
+	"net/http"
+	"net/http/httptest"
+	"os"
+	"testing"
+	"time"
+
+	"github.com/jackc/pgx/v5/pgxpool"
+
+	"github.com/baicai2026-baicai/goods/api/internal/store"
+)
+
+// newTestHandler connects to the test database, skipping if unavailable or
+// unmigrated. It inserts a known product (cleaned up via t.Cleanup) so the
+// endpoint assertions are deterministic.
+func newTestHandler(t *testing.T) (*Handler, string) {
+	t.Helper()
+	dsn := os.Getenv("OPENGOODS_DATABASE_URL")
+	if dsn == "" {
+		dsn = "postgres://opengoods:opengoods@localhost:5432/opengoods?sslmode=disable"
+	}
+	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
+	defer cancel()
+
+	pool, err := pgxpool.New(ctx, dsn)
+	if err != nil {
+		t.Skipf("no database: %v", err)
+	}
+	if err := pool.Ping(ctx); err != nil {
+		pool.Close()
+		t.Skipf("database not reachable: %v", err)
+	}
+
+	var hasProduct bool
+	if err := pool.QueryRow(ctx, "SELECT to_regclass('public.product') IS NOT NULL").Scan(&hasProduct); err != nil || !hasProduct {
+		pool.Close()
+		t.Skip("migrations not applied")
+	}
+
+	gtin := "4006381333931"
+	_, err = pool.Exec(context.Background(), `
+		INSERT INTO product (gtin, name, category_id, net_content_value, net_content_unit)
+		VALUES ($1, 'Test Cola', (SELECT id FROM category WHERE path='food.beverages.carbonated'), 330, 'ml')
+		ON CONFLICT (gtin) WHERE gtin IS NOT NULL DO UPDATE SET name = EXCLUDED.name`, gtin)
+	if err != nil {
+		pool.Close()
+		t.Fatalf("seed insert failed: %v", err)
+	}
+	var pid string
+	_ = pool.QueryRow(context.Background(), "SELECT id FROM product WHERE gtin=$1", gtin).Scan(&pid)
+	_, _ = pool.Exec(context.Background(), `
+		INSERT INTO food_detail (product_id, nutrition_basis, nutriments)
+		VALUES ($1, 'per_100ml', '{"energy_kcal": 42}'::jsonb)
+		ON CONFLICT (product_id) DO UPDATE SET nutriments = EXCLUDED.nutriments`, pid)
+
+	t.Cleanup(func() {
+		_, _ = pool.Exec(context.Background(), "DELETE FROM product WHERE gtin=$1", gtin)
+		pool.Close()
+	})
+	return New(store.New(pool)), gtin
+}
+
+func doGET(t *testing.T, h *Handler, path string) *httptest.ResponseRecorder {
+	t.Helper()
+	req := httptest.NewRequest(http.MethodGet, path, nil)
+	rec := httptest.NewRecorder()
+	h.Router().ServeHTTP(rec, req)
+	return rec
+}
+
+func TestProductByBarcode(t *testing.T) {
+	h, gtin := newTestHandler(t)
+	rec := doGET(t, h, "/api/"+APIVersion+"/products/barcode/"+gtin)
+	if rec.Code != http.StatusOK {
+		t.Fatalf("status = %d, body = %s", rec.Code, rec.Body.String())
+	}
+	var p store.Product
+	if err := json.NewDecoder(rec.Body).Decode(&p); err != nil {
+		t.Fatal(err)
+	}
+	if p.Name != "Test Cola" || p.GTIN == nil || *p.GTIN != gtin {
+		t.Fatalf("unexpected product: %+v", p)
+	}
+	if p.CategoryPath == nil || *p.CategoryPath != "food.beverages.carbonated" {
+		t.Fatalf("category not joined: %+v", p.CategoryPath)
+	}
+}
+
+func TestProductByBarcodeNotFound(t *testing.T) {
+	h, _ := newTestHandler(t)
+	rec := doGET(t, h, "/api/"+APIVersion+"/products/barcode/0000000000000")
+	if rec.Code != http.StatusNotFound {
+		t.Fatalf("expected 404, got %d", rec.Code)
+	}
+}
+
+func TestSearchProducts(t *testing.T) {
+	h, _ := newTestHandler(t)
+	rec := doGET(t, h, "/api/"+APIVersion+"/products/search?q=Cola&category=food.beverages")
+	if rec.Code != http.StatusOK {
+		t.Fatalf("status = %d", rec.Code)
+	}
+	var body struct {
+		Items []store.ProductSummary `json:"items"`
+		Total int                    `json:"total"`
+	}
+	if err := json.NewDecoder(rec.Body).Decode(&body); err != nil {
+		t.Fatal(err)
+	}
+	if body.Total < 1 {
+		t.Fatalf("expected at least 1 result, got %d", body.Total)
+	}
+}
+
+func TestListCategories(t *testing.T) {
+	h, _ := newTestHandler(t)
+	rec := doGET(t, h, "/api/"+APIVersion+"/categories")
+	if rec.Code != http.StatusOK {
+		t.Fatalf("status = %d", rec.Code)
+	}
+	var body struct {
+		Items []store.Category `json:"items"`
+	}
+	if err := json.NewDecoder(rec.Body).Decode(&body); err != nil {
+		t.Fatal(err)
+	}
+	if len(body.Items) < 20 {
+		t.Fatalf("expected seeded categories, got %d", len(body.Items))
+	}
+}

api/internal/handler/handler_test.go

@@ -11,7 +11,7 @@ func TestHealthz(t *testing.T) {
 	req := httptest.NewRequest(http.MethodGet, "/healthz", nil)
 	rec := httptest.NewRecorder()
 
-	Router().ServeHTTP(rec, req)
+	New(nil).Router().ServeHTTP(rec, req)
 
 	if rec.Code != http.StatusOK {
 		t.Fatalf("expected status %d, got %d", http.StatusOK, rec.Code)
@@ -26,13 +26,23 @@ func TestHealthz(t *testing.T) {
 	}
 }
 
-func TestProductEndpointNotImplemented(t *testing.T) {
-	req := httptest.NewRequest(http.MethodGet, "/api/"+APIVersion+"/products/barcode/3017624010701", nil)
-	rec := httptest.NewRecorder()
-
-	Router().ServeHTTP(rec, req)
-
-	if rec.Code != http.StatusNotImplemented {
-		t.Fatalf("expected status %d, got %d", http.StatusNotImplemented, rec.Code)
+func TestPageParams(t *testing.T) {
+	cases := []struct {
+		query            string
+		wantPage, wantSz int
+	}{
+		{"", 1, defaultPageSize},
+		{"page=3&size=10", 3, 10},
+		{"page=0&size=-5", 1, defaultPageSize},
+		{"size=1000", 1, maxPageSize},
+		{"page=abc", 1, defaultPageSize},
+	}
+	for _, c := range cases {
+		req := httptest.NewRequest(http.MethodGet, "/?"+c.query, nil)
+		page, size := pageParams(req)
+		if page != c.wantPage || size != c.wantSz {
+			t.Errorf("query %q: got page=%d size=%d, want page=%d size=%d",
+				c.query, page, size, c.wantPage, c.wantSz)
+		}
 	}
 }

api/internal/store/store.go

@@ -0,0 +1,278 @@
+// Package store is the read-only data access layer for the OpenGoods API.
+// It only issues SELECT queries; all writes happen in the Python ingestion path.
+package store
+
+import (
+	"context"
+	"errors"
+	"strconv"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgxpool"
+)
+
+// ErrNotFound is returned when a requested row does not exist.
+var ErrNotFound = errors.New("not found")
+
+// Store wraps a PostgreSQL connection pool.
+type Store struct {
+	pool *pgxpool.Pool
+}
+
+// New constructs a Store from an existing pgx pool.
+func New(pool *pgxpool.Pool) *Store {
+	return &Store{pool: pool}
+}
+
+// Ping verifies database connectivity.
+func (s *Store) Ping(ctx context.Context) error {
+	return s.pool.Ping(ctx)
+}
+
+// Product is the full public view of a product.
+type Product struct {
+	ID              string         `json:"id"`
+	GTIN            *string        `json:"gtin"`
+	Name            string         `json:"name"`
+	Brand           *string        `json:"brand"`
+	CategoryPath    *string        `json:"category_path"`
+	GPCBrickCode    *string        `json:"gpc_brick_code"`
+	NetContentValue *float64       `json:"net_content_value"`
+	NetContentUnit  *string        `json:"net_content_unit"`
+	CountryOfOrigin *string        `json:"country_of_origin"`
+	QualityScore    float64        `json:"quality_score"`
+	Nutriments      map[string]any `json:"nutriments,omitempty"`
+	NutritionBasis  *string        `json:"nutrition_basis,omitempty"`
+	NutriScore      *string        `json:"nutri_score,omitempty"`
+	Ingredients     *string        `json:"ingredients_text,omitempty"`
+	Allergens       []string       `json:"allergens,omitempty"`
+	Additives       []string       `json:"additives,omitempty"`
+}
+
+// ProductSummary is a lightweight row used in search/listing responses.
+type ProductSummary struct {
+	ID           string  `json:"id"`
+	GTIN         *string `json:"gtin"`
+	Name         string  `json:"name"`
+	Brand        *string `json:"brand"`
+	CategoryPath *string `json:"category_path"`
+}
+
+const productSelect = `
+SELECT p.id, p.gtin, p.name, b.name, c.path::text, p.gpc_brick_code,
+       p.net_content_value, p.net_content_unit, p.country_of_origin, p.quality_score,
+       f.nutriments, f.nutrition_basis, f.nutri_score, f.ingredients_text,
+       f.allergens, f.additives
+FROM product p
+LEFT JOIN brand b ON b.id = p.brand_id
+LEFT JOIN category c ON c.id = p.category_id
+LEFT JOIN food_detail f ON f.product_id = p.id
+`
+
+func scanProduct(row pgx.Row) (*Product, error) {
+	var p Product
+	err := row.Scan(
+		&p.ID, &p.GTIN, &p.Name, &p.Brand, &p.CategoryPath, &p.GPCBrickCode,
+		&p.NetContentValue, &p.NetContentUnit, &p.CountryOfOrigin, &p.QualityScore,
+		&p.Nutriments, &p.NutritionBasis, &p.NutriScore, &p.Ingredients,
+		&p.Allergens, &p.Additives,
+	)
+	if errors.Is(err, pgx.ErrNoRows) {
+		return nil, ErrNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &p, nil
+}
+
+// ProductByGTIN looks up an active product by its barcode.
+func (s *Store) ProductByGTIN(ctx context.Context, gtin string) (*Product, error) {
+	row := s.pool.QueryRow(ctx, productSelect+" WHERE p.gtin = $1 AND p.status = 'active'", gtin)
+	return scanProduct(row)
+}
+
+// ProductByID looks up a product by its UUID.
+func (s *Store) ProductByID(ctx context.Context, id string) (*Product, error) {
+	row := s.pool.QueryRow(ctx, productSelect+" WHERE p.id = $1", id)
+	return scanProduct(row)
+}
+
+// SearchProducts performs a fuzzy name search with optional category subtree filter.
+func (s *Store) SearchProducts(ctx context.Context, q, category string, limit, offset int) ([]ProductSummary, int, error) {
+	args := []any{}
+	where := "WHERE p.status = 'active'"
+	if q != "" {
+		args = append(args, q)
+		where += " AND p.name ILIKE '%' || $1 || '%'"
+	}
+	if category != "" {
+		args = append(args, category)
+		where += " AND c.path <@ $" + strconv.Itoa(len(args)) + "::ltree"
+	}
+
+	countSQL := "SELECT count(*) FROM product p LEFT JOIN category c ON c.id = p.category_id " + where
+	var total int
+	if err := s.pool.QueryRow(ctx, countSQL, args...).Scan(&total); err != nil {
+		return nil, 0, err
+	}
+
+	args = append(args, limit, offset)
+	listSQL := `
+SELECT p.id, p.gtin, p.name, b.name, c.path::text
+FROM product p
+LEFT JOIN brand b ON b.id = p.brand_id
+LEFT JOIN category c ON c.id = p.category_id ` + where +
+		" ORDER BY p.name LIMIT $" + strconv.Itoa(len(args)-1) + " OFFSET $" + strconv.Itoa(len(args))
+
+	rows, err := s.pool.Query(ctx, listSQL, args...)
+	if err != nil {
+		return nil, 0, err
+	}
+	defer rows.Close()
+
+	out := []ProductSummary{}
+	for rows.Next() {
+		var ps ProductSummary
+		if err := rows.Scan(&ps.ID, &ps.GTIN, &ps.Name, &ps.Brand, &ps.CategoryPath); err != nil {
+			return nil, 0, err
+		}
+		out = append(out, ps)
+	}
+	return out, total, rows.Err()
+}
+
+// Nutriments returns just the nutrition payload for a product.
+type Nutriments struct {
+	ProductID  string         `json:"product_id"`
+	Basis      *string        `json:"basis"`
+	NutriScore *string        `json:"nutri_score"`
+	Values     map[string]any `json:"values"`
+}
+
+// Nutriments fetches the nutrition facts of a product.
+func (s *Store) Nutriments(ctx context.Context, id string) (*Nutriments, error) {
+	var n Nutriments
+	n.ProductID = id
+	err := s.pool.QueryRow(ctx,
+		"SELECT nutriments, nutrition_basis, nutri_score FROM food_detail WHERE product_id = $1", id,
+	).Scan(&n.Values, &n.Basis, &n.NutriScore)
+	if errors.Is(err, pgx.ErrNoRows) {
+		return nil, ErrNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &n, nil
+}
+
+// MSRP is an official suggested retail price snapshot (never a purchase link).
+type MSRP struct {
+	Amount        float64 `json:"amount"`
+	Currency      string  `json:"currency"`
+	Region        string  `json:"region"`
+	EffectiveDate *string `json:"effective_date"`
+	SourceURL     *string `json:"source_url"`
+	Note          *string `json:"note"`
+}
+
+// ListMSRP returns all MSRP snapshots for a product.
+func (s *Store) ListMSRP(ctx context.Context, id string) ([]MSRP, error) {
+	rows, err := s.pool.Query(ctx,
+		`SELECT amount, currency, region, effective_date::text, source_url, note
+		 FROM product_msrp WHERE product_id = $1 ORDER BY effective_date DESC NULLS LAST`, id)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	out := []MSRP{}
+	for rows.Next() {
+		var m MSRP
+		if err := rows.Scan(&m.Amount, &m.Currency, &m.Region, &m.EffectiveDate, &m.SourceURL, &m.Note); err != nil {
+			return nil, err
+		}
+		out = append(out, m)
+	}
+	return out, rows.Err()
+}
+
+// Brand is a public brand entry.
+type Brand struct {
+	ID   string `json:"id"`
+	Name string `json:"name"`
+}
+
+// ListBrands returns brands ordered by name.
+func (s *Store) ListBrands(ctx context.Context, limit, offset int) ([]Brand, int, error) {
+	var total int
+	if err := s.pool.QueryRow(ctx, "SELECT count(*) FROM brand").Scan(&total); err != nil {
+		return nil, 0, err
+	}
+	rows, err := s.pool.Query(ctx, "SELECT id, name FROM brand ORDER BY name LIMIT $1 OFFSET $2", limit, offset)
+	if err != nil {
+		return nil, 0, err
+	}
+	defer rows.Close()
+	out := []Brand{}
+	for rows.Next() {
+		var b Brand
+		if err := rows.Scan(&b.ID, &b.Name); err != nil {
+			return nil, 0, err
+		}
+		out = append(out, b)
+	}
+	return out, total, rows.Err()
+}
+
+// Category is a node in the self-built category tree.
+type Category struct {
+	ID           string  `json:"id"`
+	NameZH       string  `json:"name_zh"`
+	NameEN       *string `json:"name_en"`
+	Path         string  `json:"path"`
+	GPCBrickCode *string `json:"gpc_brick_code"`
+	Level        int     `json:"level"`
+}
+
+// ListCategories returns the full category tree ordered by path.
+func (s *Store) ListCategories(ctx context.Context) ([]Category, error) {
+	rows, err := s.pool.Query(ctx,
+		"SELECT id, name_zh, name_en, path::text, gpc_brick_code, level FROM category ORDER BY path")
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	out := []Category{}
+	for rows.Next() {
+		var c Category
+		if err := rows.Scan(&c.ID, &c.NameZH, &c.NameEN, &c.Path, &c.GPCBrickCode, &c.Level); err != nil {
+			return nil, err
+		}
+		out = append(out, c)
+	}
+	return out, rows.Err()
+}
+
+// Source describes a data source with its license and trust weight.
+type Source struct {
+	ID          string  `json:"id"`
+	Name        string  `json:"name"`
+	Homepage    *string `json:"homepage"`
+	License     *string `json:"license"`
+	TrustWeight float64 `json:"trust_weight"`
+}
+
+// SourceByID fetches a single data source.
+func (s *Store) SourceByID(ctx context.Context, id string) (*Source, error) {
+	var src Source
+	err := s.pool.QueryRow(ctx,
+		"SELECT id, name, homepage, license, trust_weight FROM source WHERE id = $1", id,
+	).Scan(&src.ID, &src.Name, &src.Homepage, &src.License, &src.TrustWeight)
+	if errors.Is(err, pgx.ErrNoRows) {
+		return nil, ErrNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &src, nil
+}