Product Database
PostgreSQL database that is the system of record for all product data.
What is this?
The Product Database is the authoritative store for every product in the catalog. The Product APIProduct APIServicev1.0.0
The public-facing API for the product catalog. Handles commands to create, update and delete products, serves product re...Subscribescreate-product, update-product +2APIsOpenAPIOwnerproduct-platformMapRepoView docs reads from and writes to it, and the Product WorkerProduct WorkerServicev1.0.0
Background worker that handles long-running and asynchronous catalog work off the request path, such as enrichment, imag...Ownerproduct-platformMapRepoView docs uses it for asynchronous enrichment. It also holds the outbox table that the Product Search PublisherProduct Search PublisherServicev1.0.0Reads product changes from the product database (outbox) and reliably publishes product-created, product-updated and pro...Publishesproduct-created, product-updated +1Ownerproduct-platformMapRepoView docs reads to publish product change events.
What does it store?
- Products — one row per product: SKU, name, description, price, currency, category and lifecycle status.
- Outbox — one row per product change, used to reliably publish Product CreatedProduct CreatedEventv1.0.0Published when a new product has been added to the catalog. Ownerproduct-platformSchemaMapView docs, Product UpdatedProduct UpdatedEventv1.0.0Published when an existing product's data has changed. Ownerproduct-platformSchemaMapView docs and Product DeletedProduct DeletedEventv1.0.0Published when a product has been removed from the catalog. Ownerproduct-platformSchemaMapView docs events.
Schema
-- Product Database — system of record for the product catalog
CREATE TABLE products ( product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL, description TEXT, price_cents INTEGER NOT NULL CHECK (price_cents >= 0), currency CHAR(3) NOT NULL, category TEXT, status TEXT NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'ACTIVE', 'ARCHIVED')), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE INDEX idx_products_category ON products (category);CREATE INDEX idx_products_status ON products (status);
-- Transactional outbox — change events are written here in the same-- transaction as the product change, then drained by the Product Search Publisher.CREATE TABLE outbox ( id BIGSERIAL PRIMARY KEY, product_id UUID NOT NULL REFERENCES products (product_id), event_type TEXT NOT NULL CHECK (event_type IN ('ProductCreated', 'ProductUpdated', 'ProductDeleted')), payload JSONB NOT NULL, occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(), published_at TIMESTAMPTZ);
-- Index used by the publisher to find unpublished changes in order.CREATE INDEX idx_outbox_unpublished ON outbox (id) WHERE published_at IS NULL;Access patterns
- The Product APIProduct APIServicev1.0.0The public-facing API for the product catalog. Handles commands to create, update and delete products, serves product re...Subscribescreate-product, update-product +2APIsOpenAPIOwnerproduct-platformMapRepoView docs is the only writer to the
productstable on the request path. - The Product Search PublisherProduct Search PublisherServicev1.0.0Reads product changes from the product database (outbox) and reliably publishes product-created, product-updated and pro...Publishesproduct-created, product-updated +1Ownerproduct-platformMapRepoView docs is a read-only consumer of the
outboxtable. - The Product WorkerProduct WorkerServicev1.0.0Background worker that handles long-running and asynchronous catalog work off the request path, such as enrichment, imag...Ownerproduct-platformMapRepoView docs reads and writes for asynchronous enrichment jobs.
Why an outbox?
Writing the product change and the event in the same transaction guarantees we never persist a change without an event, or publish an event for a change that rolled back. The publisher reads the outbox and emits events with at-least-once delivery.