Database ER Diagram — Architecture Documentation

Database ER Diagram

Entities and relationships across the two independent Postgres databases — miniandmore (catalog core) and gateway (users/authorities).

Diagram v1.3 · Updated 04 Aug 2026, 08:10 GST— Logical string references separated from real foreign keys; JSON-column entities drawn dotted.

100%

Rendering diagram…

Scroll to zoom · drag to pan

1 · Step-by-step workflow

01

Catalog is seeded

CATEGORY rows are created first; PRODUCT rows reference them through category_id.
Variants and colour images live inside product JSON columns, not separate tables.

02

A customer identity is established

JHI_USER and JHI_AUTHORITY live in the gateway database, joined through jhi_user_authority.
CUSTOMER_PROFILE in miniandmore is matched to that user by user_login string.

03

An order is placed

CUSTOMER_ORDER is written with 1:N ORDER_ITEM rows.
Order items store a denormalised product snapshot via product_id with no FK.

04

Discounts and payment states are applied

COUPON links to orders through coupon_code with no foreign key.
OrderStatus and PaymentStatus enumerations track the lifecycle.

05

Returns are processed

ORDER_RETURN is a child of CUSTOMER_ORDER and walks the OrderReturnStatus enumeration.

06

Integration state is recorded

LedgerlySyncStatus tracks push outcomes; ledgerly_webhook_event provides inbound idempotency.

2 · Additional details

01

Databases

miniandmore (5432 dev) — backend, JPA/Hibernate over JDBC: catalog, orders, coupons, returns, CMS, Ledgerly settings.
gateway (5433 dev) — gateway, R2DBC reactive: users and authorities.
No shared schema; store and commerce own no tables.

02

Relationships

CATEGORY 1:N PRODUCT (category_id).
CUSTOMER_ORDER 1:N ORDER_ITEM and 1:N ORDER_RETURN.
JHI_USER N:M JHI_AUTHORITY via jhi_user_authority (composite PK).
JHI_USER 1:1 CUSTOMER_PROFILE and 1:N CUSTOMER_ORDER are logical only, matched on user_login.

03

CMS and config entities

banner, blog_post, home_section, page_visibility, policy — standalone, no relationships.
site_setting and ledgerly_setting are singleton rows.
ledgerly_webhook_event has an eventId unique key for idempotency.

04

Enumerations

DiscountType: PERCENT, FLAT.
OrderStatus: PENDING, CONFIRMED, CANCELLED, FULFILLED.
PaymentStatus: PENDING, PAID, FAILED, REFUNDED.
OrderReturnStatus: REQUESTED, APPROVED, REJECTED, RECEIVED, REFUNDED, FAILED, CANCELLED.
LedgerlySyncStatus: PENDING, SYNCED, FAILED.

3 · Advantages & usage

Advantage

No cross-service write contention or schema ownership disputes.
Migrations ship with the service that depends on them.

Usage

Every catalog, order and CMS write path goes through backend repositories.

4 · Phase 2 additions

01

Promote JSON columns to tables

Model PRODUCT_VARIANT and PRODUCT_COLOR_IMAGE as real tables once variant querying is needed.

02

Referential integrity checks

Add background validation for logical user_login and coupon_code references.

03

Read replicas and archival

Introduce a reporting replica and archive closed orders once volume grows.