client -> API Gateway -> service -> database -> response

client -> API Gateway -> service -> database commit -> response lost
client -> API Gateway -> service -> database timeout -> commit maybe happened
client -> API Gateway -> service -> database success -> event publish failed
client -> API Gateway -> service -> database deadlock -> retry unsafe?
client -> API Gateway -> service -> DB write success -> cache update failed
client -> API Gateway -> service -> partial external payment call -> DB rollback impossible

"Bagaimana cara insert row dari API?"

"Apa atomic boundary dari command ini, apa yang boleh diulang, apa yang tidak boleh diulang, dan state mana yang menjadi sumber kebenaran setelah failure?"

- operasi yang bisa dijadikan atomic,
- operasi yang hanya bisa eventual,
- operasi yang harus direkonsiliasi,
- operasi yang tidak boleh dilakukan sebelum commit,
- operasi yang tidak boleh dilakukan dua kali,
- operasi yang harus bisa di-replay.

BEGIN;
  INSERT INTO orders (...);
  INSERT INTO order_items (...);
  INSERT INTO idempotency_records (...);
COMMIT;

createOrderInDatabase();
publishOrderCreatedEvent();
chargePayment();
updateCache();
return 201;

1. database,
2. event bus,
3. payment provider,
4. cache.

API command should commit one durable source-of-truth state.
Everything outside that atomic write must be derived, retried, reconciled, or compensated.

Jika business state berubah, idempotency/audit/outbox yang relevan ikut berubah dalam atomic boundary yang sama.

- menggunakan database yang sama,
- berada dalam ownership boundary yang sama,
- perlu atomic terhadap business state,
- tidak melakukan network call eksternal,
- tidak menunggu operasi lambat,
- tidak memegang lock lebih lama dari perlu,
- bisa selesai dalam latency budget command.

- insert/update aggregate utama,
- insert idempotency record,
- insert audit event,
- insert outbox row,
- update version number,
- condition check state transition,
- append domain event row,
- enforce uniqueness via unique constraint,
- reserve local resource yang dimiliki service.

- publish ke EventBridge secara langsung,
- call payment provider,
- kirim email,
- update OpenSearch,
- update Redis cache,
- call service lain,
- call API partner,
- generate file besar,
- melakukan HTTP callback,
- menunggu human approval.

No remote I/O inside a database transaction unless you can prove the failure semantics are safe.

Semua perubahan durable yang harus commit atau rollback bersama untuk mempertahankan invariant bisnis.

Case hanya boleh berubah dari SUBMITTED ke APPROVED jika:
- approver punya authority,
- case belum closed,
- evidence sudah lengkap,
- approval record dibuat,
- case version naik,
- audit trail tercatat,
- event CaseApproved diproduksi untuk downstream.

- update case status SUBMITTED -> APPROVED,
- increment version,
- insert approval row,
- insert audit row,
- insert outbox row CaseApproved,
- insert/update idempotency record.

- notify investigator,
- update dashboard projection,
- index to OpenSearch,
- invalidate cache,
- send email,
- trigger SLA workflow.

1. validate request shape,
2. normalize command,
3. open transaction,
4. lock/read aggregate with expected version,
5. enforce state transition,
6. write business mutation,
7. write idempotency record,
8. write outbox row,
9. commit,
10. return response,
11. publisher emits outbox asynchronously.

BEGIN;

SELECT id, status, version
FROM cases
WHERE id = :case_id
FOR UPDATE;

-- Application verifies allowed transition.

UPDATE cases
SET status = 'APPROVED',
    version = version + 1,
    updated_at = now()
WHERE id = :case_id
  AND status = 'SUBMITTED';

INSERT INTO case_approvals (
  case_id,
  approved_by,
  approved_at,
  decision_reason
) VALUES (
  :case_id,
  :user_id,
  now(),
  :reason
);

INSERT INTO outbox_events (
  event_id,
  aggregate_type,
  aggregate_id,
  event_type,
  event_version,
  payload,
  created_at,
  publish_status
) VALUES (
  :event_id,
  'Case',
  :case_id,
  'CaseApproved',
  1,
  :payload_json,
  now(),
  'PENDING'
);

COMMIT;

- business state dan event intent tidak bisa terpisah,
- event publish boleh gagal tanpa kehilangan event,
- retry API bisa dikontrol dengan idempotency key,
- downstream bisa eventual,
- audit bisa dipercaya.

- membutuhkan publisher outbox,
- membutuhkan cleanup/archival outbox,
- membutuhkan observability atas stuck pending event,
- membutuhkan ordering strategy bila event per aggregate harus berurutan.

DynamoDB correctness berasal dari key design, condition expression, version attribute, dan TransactWriteItems saat benar-benar perlu atomic multi-item.

- gunakan condition expression untuk mencegah invalid transition,
- gunakan version attribute untuk optimistic concurrency,
- gunakan idempotency item untuk duplicate command,
- gunakan outbox/event item dalam transaction bila event harus atomic dengan state,
- hindari transaksi luas lintas banyak aggregate.

{
  "TableName": "CaseTable",
  "Key": {
    "PK": { "S": "CASE#123" },
    "SK": { "S": "META" }
  },
  "UpdateExpression": "SET #status = :approved, #version = #version + :one",
  "ConditionExpression": "#status = :submitted AND #version = :expectedVersion",
  "ExpressionAttributeNames": {
    "#status": "status",
    "#version": "version"
  },
  "ExpressionAttributeValues": {
    ":approved": { "S": "APPROVED" },
    ":submitted": { "S": "SUBMITTED" },
    ":expectedVersion": { "N": "7" },
    ":one": { "N": "1" }
  }
}

TransactWriteItems:
  - ConditionCheck idempotency key does not exist OR completed with same hash
  - Update CASE#123 status SUBMITTED -> APPROVED with expected version
  - Put APPROVAL#abc
  - Put OUTBOX#event-id
  - Put IDEMPOTENCY#key status COMPLETED

- TransactWriteItems memberi atomicity lintas beberapa item.
- TransactWriteItems lebih mahal dan punya batas ukuran/aksi.
- Transaction conflict harus dianggap sebagai normal concurrency signal.
- Global Tables punya semantics khusus yang harus dianalisis per workload.

repository.save(order);
eventBridge.putEvents(orderCreatedEvent);

Dalam transaksi database:
  - tulis business state,
  - tulis outbox row.

Di luar transaksi:
  - publisher membaca outbox row PENDING,
  - publish ke EventBridge/SNS/SQS,
  - mark PUBLISHED atau retry.

Tidak boleh ada business state change tanpa durable event intent bila downstream bergantung pada event tersebut.

Database commit berhasil.
Backend mengirim response.
Connection putus.
Client menerima timeout atau 502/504.
Client tidak tahu apakah command berhasil.

client retry -> duplicate command -> duplicate state/effect

client retry dengan key sama -> server menemukan completed command -> replay response atau return canonical resource state

transaction.begin();
caseRepository.approve(caseId);
paymentClient.charge(...);       // remote I/O inside DB transaction
outboxRepository.insert(...);
transaction.commit();

- lock ditahan selama network call,
- payment mungkin sukses lalu DB rollback,
- timeout payment ambiguous,
- retry transaction bisa men-charge ulang,
- database pool cepat habis,
- deadlock probability naik.

1. Commit intent/reservation local.
2. Emit event/workflow task.
3. Execute remote side effect with idempotency key.
4. Record result.
5. Compensate jika perlu.

- update banyak aggregate sekaligus,
- loop ribuan row dalam satu request,
- backfill dilakukan lewat API transaction,
- user action memicu table scan + update,
- lock semua dependent entity.

- latency tinggi,
- lock contention,
- deadlock,
- connection pool exhaustion,
- failover recovery lebih sakit,
- retry mahal dan tidak aman.

- command kecil,
- batch job asynchronous,
- workflow orchestration,
- chunked migration,
- eventual projection.

repository.save(entity);
redis.set(key, entity);

- cache-aside dengan TTL,
- invalidate via event after commit,
- versioned cache key,
- projection rebuild,
- stale-while-revalidate untuk read-heavy non-critical data.

Service A transaction:
  update A database
  update B database directly

- API command ke owning service,
- event-driven projection,
- saga/workflow,
- shared kernel hanya untuk truly shared immutable reference data.

- lost update,
- dirty read,
- non-repeatable read,
- phantom read,
- write skew,
- deadlock,
- serialization failure,
- stale replica read.

T1 reads case.version=7, status=SUBMITTED
T2 reads case.version=7, status=SUBMITTED
T1 approves -> version 8
T2 rejects -> version 8 or overwrites approval

UPDATE cases
SET status = :new_status,
    version = version + 1
WHERE id = :case_id
  AND version = :expected_version
  AND status = :expected_status;

409 Conflict, not 500.

- konflik jarang,
- command pendek,
- user bisa retry/refresh,
- aggregate punya version,
- latency penting.

read version -> send command with expectedVersion -> update where version matches

- lock duration rendah,
- scalable,
- cocok untuk API.

- conflict harus ditangani eksplisit,
- client perlu stale-state strategy,
- high contention bisa menyebabkan banyak retry.

- konflik sering,
- state transition harus serial,
- operasi pendek,
- database relational,
- jumlah row kecil,
- user tidak boleh membuat keputusan dari stale state.

SELECT * FROM cases WHERE id = :id FOR UPDATE;

- serialisasi jelas,
- business invariant mudah dijaga.

- lock contention,
- deadlock,
- timeout,
- pool pressure,
- buruk untuk operasi panjang.

Pessimistic lock hanya aman bila transaksi sangat pendek dan tidak melakukan remote I/O.

Retry correctness = retry policy + idempotency + bounded attempts + observability.

- session variable,
- temporary table,
- prepared statement behavior tertentu,
- transaction/session state,
- long-running transaction.

- keep transaction short,
- avoid session state mutation,
- set statement timeout,
- set connection acquisition timeout,
- expose pool metrics,
- cap concurrency before DB melts,
- use RDS Proxy when connection storm risk exists,
- understand pinning before assuming pooling works.

public ApproveCaseResponse approveCase(ApproveCaseRequest request) {
    var command = normalizeAndValidate(request);
    var requestHash = hashCanonicalRequest(command);

    return transactionTemplate.execute(tx -> {
        var idem = idempotencyRepository.findForUpdate(command.idempotencyKey());

        if (idem != null) {
            return handleDuplicate(idem, requestHash);
        }

        idempotencyRepository.insertInProgress(
            command.idempotencyKey(),
            requestHash,
            command.actorId()
        );

        var caseRow = caseRepository.findForUpdate(command.caseId())
            .orElseThrow(NotFound::new);

        casePolicy.assertCanApprove(caseRow, command.actorId());
        caseStateMachine.assertTransition(caseRow.status(), APPROVED);

        caseRepository.approve(
            command.caseId(),
            caseRow.version(),
            command.reason()
        );

        var event = CaseApprovedEvent.from(command, caseRow.version() + 1);
        outboxRepository.insert(event);
        auditRepository.record(command, event);

        var response = ApproveCaseResponse.from(event);

        idempotencyRepository.markCompleted(
            command.idempotencyKey(),
            response
        );

        return response;
    });
}

- duplicate check berada dalam transaction,
- request hash mencegah key reuse dengan payload berbeda,
- business state dan outbox event commit bersama,
- external effects tidak terjadi di transaction,
- response bisa direplay dari idempotency record,
- lock dipegang sesingkat mungkin.

public ApproveCaseResponse approveCase(ApproveCaseRequest request) {
    var command = normalizeAndValidate(request);
    var event = CaseApprovedEvent.from(command);

    dynamo.transactWriteItems(builder -> builder
        .transactItems(
            conditionCheckIdempotencyKeyAbsent(command.idempotencyKey()),
            updateCaseStatusIfSubmittedAndVersionMatches(command),
            putApprovalRecord(command),
            putOutboxEvent(event),
            putIdempotencyCompleted(command, event.response())
        )
        .clientRequestToken(command.idempotencyKey())
    );

    return event.response();
}

- gunakan condition expression untuk state transition,
- gunakan transaction hanya jika multi-item atomicity dibutuhkan,
- gunakan client request token/idempotency where applicable,
- jangan campur aggregate tidak terkait dalam satu transaction,
- transaction conflict adalah concurrency signal.

- siapa melakukan apa,
- kapan,
- berdasarkan state apa,
- command input apa,
- authorization decision apa,
- state berubah dari apa ke apa,
- event apa yang diterbitkan,
- downstream side effect apa yang terjadi,
- apakah command duplicate/replay,
- apakah ada compensation.

business state change + audit fact + outbox intent = one commit

- log bisa hilang,
- log bisa terlambat,
- log bukan relational business evidence,
- log retention berbeda,
- log tidak selalu transactional dengan business state.

1. write ke writer,
2. langsung read dari reader,
3. return response berdasarkan reader,

- return state yang baru saja ditulis dari writer transaction,
- atau read-after-write dari writer,
- atau return 202 + polling canonical resource,
- atau gunakan version token dan wait/refresh policy,
- jangan menjanjikan read-your-writes dari eventually consistent projection.

POST /cases/{id}/approve -> returns committed command result
GET /cases/{id} -> may be eventually consistent only if documented
GET /case-dashboard -> projectionFreshness <= N seconds

- approval multi-step,
- waiting external provider,
- document generation,
- human review,
- timeout/compensation,
- multi-service saga,

API transaction:
  - validate command,
  - create workflow instance / business intent,
  - record idempotency,
  - insert outbox/workflow-start intent,
  - commit.

Workflow:
  - execute steps,
  - call services,
  - wait/callback,
  - compensate,
  - update durable state per step.

{
  "error": {
    "code": "CASE_VERSION_CONFLICT",
    "message": "The case was modified by another operation.",
    "retryable": false,
    "correlationId": "req-abc",
    "details": {
      "expectedVersion": 7,
      "currentVersion": 8
    }
  }
}

- correlation_id,
- command_id,
- idempotency_key hash, not raw if sensitive,
- actor_id,
- aggregate_id,
- aggregate_version_before,
- aggregate_version_after,
- transaction_duration_ms,
- db_wait_ms,
- lock_wait_ms,
- rows_affected,
- outbox_event_id,
- response_status,
- retry_attempt,
- error_code.

- command_success_total,
- command_conflict_total,
- command_duplicate_total,
- command_validation_failed_total,
- transaction_duration_p50/p95/p99,
- db_connection_acquire_latency,
- db_pool_exhaustion_total,
- deadlock_total,
- serialization_failure_total,
- outbox_pending_count,
- outbox_oldest_age_seconds,
- idempotency_in_progress_oldest_age_seconds.

- outbox oldest pending age exceeds SLO,
- DB connection pool saturation,
- transaction p99 exceeds API timeout budget,
- conflict rate spikes unexpectedly,
- deadlock rate spikes,
- idempotency in-progress stuck,
- command failure rate crosses burn-rate threshold.

[ ] What is the command?
[ ] What aggregate/entity owns the state?
[ ] What is the source of truth?
[ ] What invariant must hold after commit?
[ ] What goes into the atomic transaction?
[ ] What must not be inside the transaction?
[ ] What is the idempotency key?
[ ] How is request hash stored and compared?
[ ] How are duplicate completed commands handled?
[ ] How are in-progress commands handled?
[ ] What optimistic/pessimistic concurrency strategy is used?
[ ] What is the outbox/event strategy?
[ ] How are external side effects made idempotent?
[ ] How are DB errors mapped to API errors?
[ ] What retry errors are safe?
[ ] What retry errors are unsafe?
[ ] What metrics prove transaction health?
[ ] What alarm catches stuck outbox/state?
[ ] What reconciliation job detects drift?
[ ] What load test validates connection pool and transaction p99?

- local durable state is committed,
- command is complete from caller perspective,
- downstream effects are not required for immediate contract.

- command starts a long-running operation,
- external process/workflow must finish,
- state is pending,
- client should poll operation status or receive callback/event.

- relational constraints matter,
- joins/foreign keys are core to write invariant,
- aggregate mutation spans normalized tables,
- ad hoc query/debugging is valuable,
- strong transaction semantics are primary.

- access patterns are known,
- latency/scale predictability matters,
- aggregate maps well to item collection,
- conditional write can encode invariant,
- multi-item transaction is small and bounded.

- event must not be lost after DB commit,
- downstream relies on event for correctness,
- audit/replay matters,
- dual-write inconsistency is unacceptable.

- event is best-effort telemetry,
- source can be polled/reconciled cheaply,
- duplicate/missing event does not violate business invariant,
- explicit risk is accepted in ADR.

POST /cases/{caseId}/approve

- case must be SUBMITTED,
- approver must have role,
- approval reason required,
- command must be idempotent,
- dashboard must update eventually,
- notification must be sent eventually,
- audit must be reliable,
- duplicate approval must not happen.

A case cannot be APPROVED unless there is exactly one durable approval record and at least one durable event intent for CaseApproved.

"Does the transaction commit?"

- What happens if response is lost after commit?
- What happens if publish fails after commit?
- What happens if DB says timeout but commit actually happened?
- What happens if two actors mutate same aggregate?
- What happens if cache is stale?
- What happens if read model lags?
- What happens if retry happens five minutes later?
- What happens if event is replayed six months later?
- What happens if schema changed between original command and replay?
- What happens if failover occurs during commit?

- Keep atomic boundary small and explicit.
- Commit source-of-truth state, idempotency, audit, and outbox together.
- Never assume remote side effects are part of DB transaction.
- Treat lost response after commit as normal, not rare.
- Use optimistic/pessimistic concurrency intentionally.
- Use outbox to avoid dual-write inconsistency.
- Use RDS Proxy/pooling/backpressure to protect database connections.
- Use DynamoDB condition expressions/transactions as correctness primitives, not just performance tools.
- Expose stable API error semantics instead of raw DB failure.
- Test ambiguous commit, duplicate request, deadlock, replay, and outbox delay.