Java, PostgreSQL, and Microservices Data Model Implementation Blueprint

1. Core Idea

Setelah data model didesain, tantangan berikutnya adalah mengimplementasikannya tanpa kehilangan semantics.

Banyak incident data bukan karena model conceptual salah, tetapi karena implementasi:

• entity persistence langsung diekspos sebagai DTO,
• repository bypass invariant,
• migration tidak safe,
• event dipublish sebelum DB commit,
• idempotency tidak dipersist,
• validation hanya di frontend,
• status update dilakukan via generic patch,
• audit tidak konsisten,
• index tidak sesuai query,
• test tidak memakai real DB constraint,
• cache memakai key yang salah.

Mental model:

Implementation is where the model either becomes production truth or becomes a diagram that code ignores.

Blueprint ini membantu senior Java/backend engineer menerjemahkan data model ke kode dan database yang aman.

2. Implementation Layers

Layer yang perlu konsisten:

API DTO / Resource
  -> Application Command
  -> Domain Service / Aggregate
  -> Repository
  -> PostgreSQL Tables / Constraints
  -> Outbox / Integration Event
  -> Projection / Read Model
  -> Audit / Observability

Setiap layer punya tanggung jawab.

Anti-pattern:

JAX-RS resource -> repository.updateStatus(...)

tanpa domain command, validation, audit, event, idempotency, and state guard.

3. Entity Does Not Equal DTO

Persistence entity:

ProductOrderEntity

Domain aggregate:

ProductOrder

API DTO:

ProductOrderResponse
CreateProductOrderRequest
CancelOrderRequest

These are different models.

Why separation matters:

• API contract must be stable,
• DB schema can evolve internally,
• sensitive fields can be hidden,
• derived fields can be computed,
• lazy relationships do not leak,
• command semantics are explicit,
• field-level authorization is possible.

Do not expose JPA/MyBatis entity directly.

4. Command-Oriented API

State changes should be commands.

Bad:

PATCH /orders/{id}
{
  "status": "CANCELLED"
}

Better:

POST /orders/{id}/cancel
{
  "expectedVersion": 7,
  "reasonCode": "CUSTOMER_REQUEST",
  "comment": "Customer requested cancellation"
}

Command includes:

• intent,
• actor,
• expected version,
• reason,
• idempotency key,
• correlation ID,
• command-specific validation.

This protects lifecycle semantics.

5. Application Service Pattern

Application service coordinates one use case.

Example responsibilities:

CancelOrderApplicationService
  -> resolve actor/tenant
  -> validate idempotency
  -> load order with lock/version
  -> invoke domain behavior
  -> persist aggregate
  -> write audit
  -> append outbox event
  -> commit transaction
  -> return DTO

Pseudo-code:

@Transactional
public CancelOrderResult cancel(CancelOrderCommand command) {
    IdempotencyResult existing = idempotency.tryStart(command.key(), command.hash());
    if (existing.isCompleted()) return existing.result();

    ProductOrder order = orders.findForUpdate(command.tenantId(), command.orderId());
    order.cancel(command.actor(), command.expectedVersion(), command.reasonCode());

    orders.save(order);
    audit.append(OrderAudit.cancelled(order, command));
    outbox.append(OrderCancelledEvent.from(order, command.correlationId()));

    idempotency.complete(command.key(), order.id());
    return CancelOrderResult.from(order);
}

The exact implementation style may differ, but the transaction boundary is the key.

6. Domain Aggregate Pattern

Aggregate protects invariants.

Example aggregate behavior:

public void accept(Actor actor, int expectedVersion, Clock clock) {
    assertVersion(expectedVersion);
    assertStatus(PRICED, APPROVED);
    assertNotExpired(clock);
    assertApprovalStillValid();
    this.status = ACCEPTED;
    this.acceptedAt = clock.instant();
    this.version++;
}

Avoid:

order.setStatus("ACCEPTED");

Setter-heavy models often bypass invariants.

Domain behavior should encode:

• allowed transitions,
• required fields,
• side-effect intent,
• version increment,
• business error codes.

7. Repository Responsibility

Repository should persist aggregate and query by aggregate identity.

Repository should not:

• contain business rules,
• decide state transitions,
• expose query that bypasses tenant,
• update status without version guard,
• leak storage details to API layer.

Good repository methods:

Optional<ProductOrder> findById(TenantId tenantId, OrderId orderId);
ProductOrder findForUpdate(TenantId tenantId, OrderId orderId);
boolean existsBySourceQuoteVersion(TenantId tenantId, QuoteId quoteId, int quoteVersion);
void save(ProductOrder order);

Suspicious method:

void updateStatus(UUID orderId, String status);

unless strictly internal and guarded.

8. PostgreSQL Schema From Domain Invariants

Domain invariant should map to DB constraints where possible.

Example invariant:

One order per accepted quote version.

DB:

create unique index uq_order_source_quote_version
on product_order (tenant_id, source_quote_id, source_quote_version)
where source_quote_id is not null;

Invariant:

Only one current characteristic per product instance/name.

DB:

create unique index uq_current_product_characteristic
on product_instance_characteristic (tenant_id, product_instance_id, characteristic_name)
where effective_to is null;

App validation plus DB constraint is stronger than either alone.

9. Table Design Implementation Checklist

For each table:

• primary key,
• tenant/environment scope if applicable,
• business number,
• owner/service,
• lifecycle/status fields,
• version field,
• audit timestamps,
• created_by/updated_by if needed,
• source reference/lineage,
• external reference mapping,
• indexes for query patterns,
• uniqueness constraints,
• foreign keys/logical references,
• retention/archive strategy,
• classification for sensitive fields.

Do not create table only from UI screen fields.

10. Money and Time Implementation

Money:

amount numeric(18,4)
currency char(3)

Avoid float/double for persisted financial amount.

Java:

BigDecimal amount;
CurrencyCode currency;

Time:

• use Instant/OffsetDateTime for instants,
• use LocalDate for business date,
• store instants as timestamptz,
• document timezone for billing period/cutoff,
• define inclusive/exclusive effective dates.

Do not mix event time, business effective date, and ingestion time.

11. Status Implementation

Avoid uncontrolled strings scattered everywhere.

Options:

• Java enum for stable internal states,
• DB reference table for business-managed status/reason codes,
• text + check constraint for controlled lifecycle,
• code set service for governed reference data.

Status must have:

• allowed transitions,
• terminal flag,
• reporting group,
• external mapping,
• customer visibility,
• billing/fulfillment impact.

Implementation should not treat status as only UI label.

12. DTO Mapping and Authorization

DTO mapper should enforce field visibility.

Example:

public QuoteResponse toResponse(Quote quote, Actor actor) {
    QuoteResponse dto = new QuoteResponse();
    dto.id = quote.id().value();
    dto.status = quote.status().name();
    dto.totalAmount = moneyMapper.toDto(quote.totalAmount());

    if (authorization.canViewMargin(actor, quote)) {
        dto.marginAmount = moneyMapper.toDto(quote.marginAmount());
    }

    return dto;
}

Do not return sensitive data and rely on frontend to hide it.

13. Validation Implementation

Validation layers:

Java Bean Validation is useful but not enough for domain invariants.

Example:

@NotNull billingAccountId

does not verify account active, scoped to tenant, or usable for billing.

14. Error Model Implementation

Use stable error codes.

Example:

{
  "errorCode": "ORDER_STATE_CONFLICT",
  "message": "Order cannot be cancelled in its current state",
  "details": {
    "currentStatus": "COMPLETED"
  },
  "correlationId": "corr-123"
}

Error classes:

• validation,
• authorization,
• not found,
• conflict/version,
• state transition,
• idempotency conflict,
• downstream failure,
• data quality issue.

Avoid exposing SQL exception messages to API clients.

15. Transaction Boundary

Transaction should include:

• aggregate state change,
• audit record,
• outbox event,
• idempotency update,
• status history.

Transaction should not include:

• long external API call,
• slow file upload/download,
• blocking remote service call,
• user interaction,
• huge backfill batch.

Use outbox for asynchronous side effects after commit.

16. Outbox Implementation

Pattern:

Within DB transaction:
  update business table
  insert outbox_event
Commit

Publisher:
  poll outbox
  publish message
  mark published

Outbox fields:

event_id
event_type
event_version
aggregate_type
aggregate_id
aggregate_version
payload
status
retry_count
next_retry_at
correlation_id

Event ID should be stable. Consumers should be idempotent.

17. Inbox Implementation

Consumer pattern:

Begin transaction
  insert inbox row with event_id + subscriber
  if duplicate: skip
  load/update local projection or state
  mark inbox processed
Commit

Inbox prevents duplicate processing inside consumer.

But side effects must still use business idempotency if they call external systems.

18. Idempotency Implementation

Idempotency table:

create table idempotency_record (
  scope text not null,
  idempotency_key text not null,
  request_hash text,
  status text not null,
  result_entity_type text,
  result_entity_id uuid,
  created_at timestamptz not null,
  updated_at timestamptz not null,
  primary key (scope, idempotency_key)
);

Implementation rules:

• same key + same payload returns same result,
• same key + different payload returns conflict,
• result persisted after success,
• in-progress records expire or recover,
• key scope includes tenant/operation.

Use for create/convert/charge/external callbacks.

19. Migration Implementation

Use expand/contract.

Example:

1. Add nullable column.
2. Deploy code that dual-writes or can read both.
3. Backfill in batches.
4. Validate old/new.
5. Switch reads.
6. Enforce not-null/constraint.
7. Remove old field after compatibility window.

Avoid:

alter table huge_table add column new_col uuid not null;

without volume/lock assessment.

Migration script should have:

• naming,
• rollback/forward fix,
• lock timeout,
• idempotency,
• validation query,
• runbook.

20. Testing Implementation

Test levels:

• domain unit tests,
• application service transaction tests,
• repository integration tests with PostgreSQL,
• migration tests,
• contract tests,
• event consumer tests,
• idempotency tests,
• concurrency tests,
• reconciliation tests,
• security field visibility tests.

Use real PostgreSQL for constraints and transaction behavior.

Mocks cannot prove unique index, lock, or isolation behavior.

21. Observability Implementation

Every production command should include:

• correlation ID,
• actor,
• tenant,
• command type,
• target entity,
• status,
• error code,
• duration,
• audit event,
• outbox event ID if emitted.

Support endpoints/read models:

GET /orders/{id}/timeline
GET /orders/{id}/operational-status
GET /orders/{id}/external-references
GET /orders/{id}/data-quality

Make production debugging a designed capability.

22. Logging Implementation

Do:

• structured logs,
• correlation ID,
• business entity number where safe,
• error code,
• stage/component.

Do not log:

• raw payment data,
• secrets/tokens,
• full PII payload,
• margin/cost broadly,
• full request body by default,
• huge external payload.

Logs are not data model replacement. Important evidence should be persisted.

23. Cache Implementation

Rules:

• cache key includes tenant/scope/version,
• commands load authoritative state,
• cache is invalidated via event/outbox,
• sensitive DTO cache uses short TTL or avoids shared cache,
• value includes source version/freshness,
• stale behavior documented.

Example key:

prod:{tenantId}:quote-summary:{quoteId}:v:{quoteVersion}:view:{visibilityPolicy}

Bad key:

quote:{quoteId}

24. Search/Projection Implementation

Projection should store:

source_entity_id
source_aggregate_version
last_event_id
projected_at
projection_version

Update rule:

apply event only if event.aggregate_version > projection.aggregate_version

Search document should include tenant/security fields.

Projection should be rebuildable.

25. Implementation Sequence for New Aggregate

For new aggregate, sequence:

1. Define domain meaning and owner.
2. Define aggregate ID/business number.
3. Define states and transitions.
4. Define commands.
5. Define invariants.
6. Define PostgreSQL schema/constraints/indexes.
7. Define DTO/API contract.
8. Define events/outbox.
9. Define audit/status history.
10. Define idempotency.
11. Define migration if replacing old model.
12. Define tests.
13. Define observability.
14. Define reconciliation.
15. Define runbook/support view.

Do not start from table columns alone.

26. Example Mini Blueprint — Quote Acceptance

Data model implementation:

Quote
QuoteRevision
QuoteStatusHistory
QuoteAcceptanceEvidence
OutboxEvent
IdempotencyRecord
AuditEvent

Command:

AcceptQuoteCommand
- tenant_id
- quote_id
- expected_version
- idempotency_key
- actor
- accepted_at

Transaction:

check idempotency
load quote for update
validate status/version/approval/validity
set status accepted
append status history
persist acceptance evidence
append outbox QuoteAccepted
complete idempotency
commit

Post-commit:

outbox publisher emits QuoteAccepted
order service consumes idempotently

27. Example Mini Blueprint — Product Activation to Charge

Data model implementation:

ProductInstance
ServiceInstance
RecurringCharge
BillingTrigger
OutboxEvent
ReconciliationRule

Flow:

FulfillmentCompleted
  -> ProductActivated
  -> BillingReadinessChecked
  -> RecurringChargeCreated
  -> ChargeActivated event

Guards:

• product active,
• billing account valid,
• no duplicate active charge,
• effective date valid,
• source order item trace exists.

DB uniqueness:

product_instance_id + charge_type + effective_from

or domain-specific equivalent.

28. Failure Modes

29. PR Review Checklist

When reviewing implementation, ask:

• Are DTO/domain/entity separated?
• Is state change command-oriented?
• Is aggregate enforcing invariant?
• Is repository tenant-safe?
• Are DB constraints aligned with business rules?
• Is idempotency persisted?
• Is outbox used for events?
• Are audit/status history written in transaction?
• Are migrations expand/contract safe?
• Are indexes aligned with query patterns?
• Are sensitive fields filtered/masked?
• Are tests using real DB where needed?
• Are correlation IDs propagated?
• Are support/reconciliation hooks present?
• Are cache/search projections version-aware?

30. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Preferred Java stack and conventions: JAX-RS/JPA/MyBatis/JDBC.
• Transaction management standard.
• Migration tooling and safe DDL standards.
• Outbox/inbox implementation availability.
• Idempotency standard.
• Audit/status history standard.
• Error response standard.
• DTO mapping/security conventions.
• PostgreSQL indexing/partitioning conventions.
• Cache/search projection conventions.
• Contract testing standards.
• Incident history around duplicate commands, missed events, unsafe migration, or DTO leakage.

31. Summary

Implementation is where enterprise data modelling becomes real.

A strong implementation blueprint includes:

• command-oriented API,
• DTO/domain/entity separation,
• application service transaction boundary,
• aggregate invariants,
• repository ownership,
• PostgreSQL constraints/indexes,
• validation layers,
• stable error model,
• outbox/inbox,
• idempotency,
• migration strategy,
• tests,
• observability,
• cache/search projection rules,
• support/reconciliation hooks.

The key principle:

The best data model is not the most elegant diagram. It is the model whose invariants are enforced consistently in API, domain code, database constraints, events, migrations, tests, and operations.