Strong database consistency does not automatically create strong application behavior.

app:
  database:
    endpoints:
      ap-northeast-1: case-prod-a.cluster-id.dsql.ap-northeast-1.on.aws
      ap-northeast-3: case-prod-b.cluster-id.dsql.ap-northeast-3.on.aws
    sslMode: require
    dbName: postgres
    auth: iam-token
    applicationName: enforcement-command-api
    pool:
      maxSize: 40
      connectionMaxLifetime: 50m
      acquisitionTimeout: 2s

Client -> nearest healthy Region -> local DSQL endpoint

caseId / tenantId / accountId -> home Region -> that Region API -> local DSQL endpoint

Use aggregate-home routing for writes.
Use nearest-region routing for safe reads.

1. Validate API request shape.
2. Resolve command identity.
3. Choose Region/endpoint.
4. Enter retry loop for retryable transaction errors.
5. Insert or load command record.
6. Load aggregate source row(s).
7. Validate invariant.
8. Mutate source row(s).
9. Insert audit/action row.
10. Insert outbox event.
11. Commit.
12. Return stable response.

public CommandResult approveCase(ApproveCaseRequest req) {
  UUID commandId = stableCommandId(req.idempotencyKey());
  String requestHash = hashCanonical(req);

  return dsqlRetry.execute(() -> tx.inTransaction(() -> {
    CommandRecord existing = commandStore.insertOrGet(
      commandId,
      req.tenantId(),
      "ApproveCase",
      req.idempotencyKey(),
      requestHash
    );

    if (existing.isCompleted()) return existing.result();
    if (existing.hasDifferentRequestHash(requestHash)) {
      return rejectIdempotencyConflict(commandId);
    }

    CaseRow c = caseRepo.get(req.caseId());
    domainPolicy.assertCanApprove(c, req.actorId());

    caseRepo.updateStatus(
      c.caseId(),
      c.version(),
      "APPROVED",
      commandId
    );

    actionRepo.insertApprovalAction(commandId, c.caseId(), req.actorId(), req.reason());
    outbox.insert(commandId, "CaseApproved", payload(...));

    CommandResult result = CommandResult.accepted(c.caseId(), commandId);
    commandStore.complete(commandId, result);
    return result;
  }));
}

Idempotency is not a cache entry.
It is part of the command state machine.

retry:
  serializationFailure:
    maxAttempts: 5
    baseDelayMs: 20
    maxDelayMs: 500
    jitter: full
  ambiguousCommit:
    maxAttempts: 3
    recovery: read-command-record

Application sends COMMIT.
Network fails before response.
Did commit succeed?

Do not re-run blindly.
Read by command_id/idempotency_key.
If command completed, return stored result.
If command absent/in-progress after timeout, use recovery policy.

system_config.current_case_number++
tenant_summary.open_cases++
global_sequence.nextval()

CREATE TABLE case_number_reservation (
  tenant_id UUID NOT NULL,
  case_number VARCHAR(64) NOT NULL,
  command_id UUID NOT NULL,
  reserved_at TIMESTAMPTZ NOT NULL,
  status VARCHAR(32) NOT NULL,
  PRIMARY KEY (tenant_id, case_number)
);

1. Insert reservation with unique key.
2. If exists, compare command_id.
3. Create case using reserved number.
4. Mark reservation consumed.

CREATE TABLE case_sla_event (
  sla_event_id UUID PRIMARY KEY,
  case_id UUID NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  event_at TIMESTAMPTZ NOT NULL,
  command_id UUID NOT NULL
);

{
  "commandId": "uuid",
  "tenantId": "uuid",
  "aggregateType": "EnforcementCase",
  "aggregateId": "uuid",
  "aggregateHomeRegion": "ap-northeast-1",
  "submittedRegion": "ap-northeast-3",
  "operation": "ApproveCase",
  "idempotencyKey": "client-key",
  "requestHash": "sha256",
  "causationId": "uuid",
  "correlationId": "uuid",
  "submittedAt": "2026-07-07T10:15:30Z"
}

UPDATE outbox_event
SET publish_status = 'CLAIMED',
    claimed_by = :publisherId,
    claimed_at = :now,
    publish_attempts = publish_attempts + 1
WHERE event_id = :eventId
  AND publish_status = 'PENDING';

Claim then publish then mark published

Is the queue regional or global?
Can the same logical message be processed in both Regions?
Who owns retries?
Where is idempotency recorded?

Regional queue + DSQL inbox table + idempotent command transaction

1. Receive message.
2. Parse event envelope.
3. Insert inbox row by event_id/message_id.
4. If already processed, ack.
5. Apply local transaction to DSQL.
6. Insert outbox if needed.
7. Mark inbox processed.
8. Commit.
9. Ack message.

Step Functions owns process progress.
Aurora DSQL owns domain state.

executionName = workflowType + '-' + commandId

1. Workflow creates approval_requested row in DSQL.
2. Workflow waits with callback token.
3. Human action API updates DSQL in short transaction.
4. API sends task success/failure using token.
5. Workflow continues.

case:{caseId}:v:{version}

case:{caseId}

Source state is DSQL.
Projection is rebuildable.
Projection freshness is measured.
Projection writes are idempotent.
Projection consumers deduplicate by event_id.

CREATE TABLE projection_checkpoint (
  projection_name VARCHAR(128) PRIMARY KEY,
  last_event_time TIMESTAMPTZ,
  last_event_id UUID,
  lag_seconds INTEGER NOT NULL DEFAULT 0,
  status VARCHAR(32) NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

{
  "items": [],
  "projectionFreshness": {
    "asOf": "2026-07-07T10:20:00Z",
    "lagSeconds": 12
  }
}

Endpoint chooseEndpoint(Command cmd) {
  Region home = homeRegionResolver.resolve(cmd.aggregateId());
  if (health.isHealthy(home.dsqlEndpoint())) {
    return home.dsqlEndpoint();
  }
  if (cmd.isFailoverAllowed() && health.isHealthy(alternate(home).dsqlEndpoint())) {
    return alternate(home).dsqlEndpoint();
  }
  throw new ServiceUnavailable("No safe DSQL endpoint for command");
}

CREATE TABLE external_side_effect (
  side_effect_id UUID PRIMARY KEY,
  command_id UUID NOT NULL,
  provider VARCHAR(128) NOT NULL,
  operation VARCHAR(128) NOT NULL,
  external_idempotency_key VARCHAR(256) NOT NULL,
  status VARCHAR(32) NOT NULL,
  request_hash VARCHAR(128) NOT NULL,
  response_json JSONB,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  UNIQUE (provider, operation, external_idempotency_key)
);

1. Domain command commits with outbox event.
2. Integration worker claims side effect idempotently.
3. External call uses stable external idempotency key.
4. Result stored in side_effect table.
5. Follow-up event emitted if needed.

POST /cases/{caseId}/approve
Idempotency-Key: 123

{
  "commandId": "...",
  "status": "ACCEPTED",
  "caseId": "...",
  "statusUrl": "/commands/..."
}

CREATE TABLE tenant_region_assignment (
  tenant_id UUID PRIMARY KEY,
  home_region VARCHAR(64) NOT NULL,
  failover_region VARCHAR(64),
  failover_mode VARCHAR(32) NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

api.command.count
api.command.latency
api.command.retry.count
api.command.retry.exhausted
api.command.ambiguous_commit.count
api.command.idempotency.hit
api.command.idempotency.conflict
dsql.sqlstate.40001.count
dsql.occ.oc000.count
dsql.occ.oc001.count
dsql.endpoint.connection_error.count
dsql.endpoint.latency
outbox.pending.count
outbox.oldest_age_seconds
projection.lag_seconds
workflow.duplicate_start.count
cache.hit_ratio
cache.stale_read.detected

{
  "commandId": "uuid",
  "tenantId": "uuid",
  "aggregateId": "uuid",
  "operation": "ApproveCase",
  "submittedRegion": "ap-northeast-3",
  "dsqlEndpointRegion": "ap-northeast-1",
  "attempt": 2,
  "sqlState": "40001",
  "occCode": "OC000",
  "outboxEventId": "uuid",
  "correlationId": "uuid"
}

Given 100 concurrent approvals for same case
When all commands race
Then exactly one succeeds or state machine rejects duplicates deterministically
And retries do not create duplicate case_action rows
And outbox has one CaseApproved event

Given connection is dropped around commit response
When API retries same command
Then command record returns stable result
And no duplicate external side effect occurs

Given case home Region A is unhealthy
When failover-allowed command arrives
Then it routes to Region B endpoint
And command envelope records submittedRegion and executionRegion

Given outbox event is published twice
When consumer processes both
Then inbox deduplicates by eventId
And projection is updated once or idempotently to same value

Given app v1 and v2 run during expand phase
When DDL happens and commands run concurrently
Then OC001 retries succeed
And both versions can read/write safely

1. Deploy schema expand.
2. Deploy app reading old+new.
3. Enable writes for new fields.
4. Backfill small batches.
5. Compare source/projection.
6. Switch reads.
7. Contract old fields.

1. Deploy Region A dark.
2. Deploy Region B dark.
3. Enable read traffic in one Region.
4. Enable write traffic for low-risk tenants.
5. Enable aggregate-home routing.
6. Enable failover drills.
7. Enable all tenants.

- sqlstate.40001 rises
- p95/p99 command latency rises
- retry exhausted increases
- one aggregate/tenant dominates

1. Identify top operation/aggregate/tenant causing conflict.
2. Check recent deployment or traffic change.
3. Reduce concurrency for affected operation.
4. Route writes for hot aggregate to one home Region.
5. Disable non-critical batch/backfill job.
6. Inspect schema/index changes causing OC001.
7. Review command transaction write set.
8. Create follow-up to redesign hot row/counter if persistent.

- connection error rate rises for one DSQL endpoint
- API Region latency spikes
- health checker fails

1. Confirm issue is endpoint/Region-specific, not app-wide.
2. Circuit-break affected endpoint.
3. Route failover-allowed commands to alternate endpoint.
4. Fail closed for dangerous commands.
5. Monitor duplicate command/idempotency hits.
6. Watch outbox publisher ownership.
7. Re-enable endpoint gradually after recovery.
8. Reconcile commands submitted during impairment.

- outbox.pending.count grows
- outbox.oldest_age_seconds exceeds SLO
- projection lag grows
- users complain search/dashboard stale

1. Check publisher health in both Regions.
2. Check claim query/index performance.
3. Check target EventBridge/SNS/SQS failures.
4. Check duplicate publish errors.
5. Increase publisher concurrency carefully.
6. Pause low-priority outbox event types if necessary.
7. Rebuild projection if consumer bug caused divergence.

[ ] Every command has stable commandId and idempotency key.
[ ] Every command transaction is deterministic and retryable.
[ ] SQLSTATE 40001 is retried with backoff and jitter.
[ ] Ambiguous commit is recovered by reading command record.
[ ] External side effects are outside DB transaction.
[ ] Outbox events are inserted transactionally.
[ ] Outbox publisher claim logic is safe in multi-Region deployment.
[ ] Consumers have inbox/dedup table.
[ ] Write routing policy is explicit: any-region, home-region, or failover.
[ ] Dangerous commands fail closed during regional uncertainty.
[ ] Cache has clear freshness semantics.
[ ] Projection lag is measured and visible to users/operators.
[ ] Backfills are chunked and checkpointed.
[ ] DDL deployment handles OC001 retries.
[ ] Conflict metrics are broken down by operation, tenant, aggregate, and Region.
[ ] Fault injection validates endpoint failure and connection error handling.