Building a Category, Config & Product Management System in Go: A Deep Dive

Overview

This article walks through a significant feature addition to go-wordpress — a Go-based headless CMS backend. The changes introduce full CRUD management for categories, website configs, and products, each backed by PostgreSQL (via sqlc), Redis caching, a RESTful HTTP API (Gin), and a gRPC endpoint for product lookups.

Go-Wordpress

Git commits

Database Schema

Each new entity gets its own table with proper constraints and indexes.

Categories

sql
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    website_id INTEGER NOT NULL
        REFERENCES websites(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    link TEXT NOT NULL UNIQUE,
    status entity_status DEFAULT 'active' NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);
sql
CREATE INDEX idx_categories_status     ON categories(status);
CREATE INDEX idx_categories_website_id ON categories(website_id);

A few design decisions worth noting here: the link field has a UNIQUE constraint to prevent duplicate slugs, ON DELETE CASCADE ensures categories are cleaned up when a website is deleted, and the entity_status enum (shared across the project) keeps status values consistent.

SQL Queries with sqlc

The queries cover every access pattern the application needs:

  • CreateCategory, GetCategoryByID, UpdateCategory, DeleteCategory — the standard CRUD set
  • ListAllCategories — paginated, no filter
  • ListAllActiveCategories — only status = 'active'
  • ListCategoriesByWebsiteID — scoped to one website
  • ListCategoriesByStatus — arbitrary status filter

One thing to watch out for in the generated code — ListActiveCategoriesByStatus has a redundant condition:

sql
WHERE status = $1 AND status = 'active'

This is functionally correct (the parameter would always need to be 'active'), but it's dead weight and should be simplified to WHERE status = 'active' with no parameter.

Redis Caching Layer

The cache store uses a structured key naming convention to make invalidation predictable:

go
func (s *Store) KeyCategory(ID int32) string {
    return s.prefix + ":category:" + fmt.Sprint(ID)
}
func (s *Store) KeyAllCategories(limit, offset int64) string {
    return s.prefix + ":categories:all:" + fmt.Sprint(limit) + ":" + fmt.Sprint(offset)
}
func (r *Store) DeleteAllCategoryCache(ctx context.Context) error {
    return r.deleteByPattern(ctx, r.prefix+":categories:all:*")
}

The pattern-based deletion (SCAN + DEL) is a practical approach that invalidates all paginated list caches on any write. It's worth being aware that SCAN is non-blocking but iterative — in a very large keyspace, this could take multiple round-trips. For most CMS-scale deployments, it's perfectly fine.

The same pattern is applied consistently to products, configs, and websites, making the caching behavior predictable across all entities.

Service Layer

The category service demonstrates the cache-aside pattern clearly:

go
func (s *Category) GetCategoryByID(ctx context.Context, id int32) (*sqlc.Category, error) {
    var category sqlc.Category
    err := s.memory.Get(ctx, s.memory.KeyCategory(id), &category)
    if err != nil {
        // Cache miss — go to DB category, err = s.query.GetCategoryByID(ctx, id) if err != nil {
            return nil, err
        }
        s.memory.Set(ctx, s.memory.KeyCategory(category.ID), category, s.cfg.Redis.DefaultTTL)
    }
    return &category, nil
}

On writes (create/update), the individual key is updated, and the list caches are invalidated. On delete, both the item key and list caches are cleared before the DB deletion — a sensible ordering that avoids serving stale data between the DB operation and cache eviction.

HTTP API

The AdminCategory controller follows the same structure used for websites across the project. Routes are protected with JWT middleware:

go
func (c *AdminCategory) RegisterRoutes(rg *gin.RouterGroup, cfg *config.Config) {
    auth := middleware.JWTAuth(cfg)
    rg.POST("/", auth, c.CreateCategory)
    rg.PUT("/:id", auth, c.UpdateCategory)
    rg.DELETE("/:id", auth, c.DeleteCategory)
    rg.GET("/:id", auth, c.GetCategoryByID)
    rg.GET("/", auth, c.ListCategories)
}

All handlers follow the same pattern: parse input → call service → return result or error. Swagger annotations are included on every handler, making the API self-documenting.

gRPC Support for Products

Products get an additional gRPC endpoint alongside the REST API:

go
func TestProductGRPC(t *testing.T) {
    //...
    client := productv1.NewProductServiceClient(conn)
    grpcGetProductByID(t, product, client)
}

This dual-protocol approach is common in systems where internal microservices communicate over gRPC for performance, while external clients use REST. The test sets up the full prerequisite chain (website → category → product) over HTTP, then validates gRPC retrieval — a clean integration test strategy.

Integration Tests

Every entity follows the same integration test flow:

  1. Verify unauthenticated requests return 401 Unauthorized
  2. Create the entity (handling FK prerequisites first)
  3. List and confirm the new entity appears
  4. Fetch by ID and verify field values
  5. Update and verify the updated fields
  6. Delete and confirm 204 No Content
  7. Attempt to fetch the deleted entity and confirm an error response

The tests use defer for cleanup, ensuring the database is left clean even if assertions fail midway. The category test also serves as a helper for product and config tests, since both depend on a website and the category existing.

Summary

The additions follow a clean, consistent architecture: schema → sqlc queries → cache keys → service layer → HTTP controller → integration tests. The same pattern that works for websites is applied uniformly to categories, configs, and products, making the codebase easy to extend and navigate.

The main areas to refine going forward would be the redundant SQL condition in ListActiveCategoriesByStatus, and potentially adding a 404 Not Found response for missing resources (currently the service returns a generic 500 for DB errors, including sql.ErrNoRows).