What Does “Consistent” Actually Mean? ACID, BASE, and Correctness.

How transactions, idempotency, asynchronous workflows, isolation levels, and convergence contracts work together in a reliable financial system.

Database Engineering Fundamentals · Article 2

ACID protects database transactions—not entire distributed workflows. This guide uses a B2B payment system to explain transaction boundaries, idempotency, concurrency control, eventual consistency, and recovery.

A payment provider sends a webhook confirming that a client has paid an invoice.

The invoicing platform now needs to:

• Record the provider event
• Prevent duplicate processing
• Create a payment record
• Write balanced ledger entries
• Update the invoice balance
• Record an audit event
• Generate a receipt
• Send an email
• Refresh a management dashboard
• Update financial analytics

Should all these operations happen in one transaction?

Putting everything into one transaction sounds safe, but a local database transaction cannot normally include an email provider, object-storage upload, analytics warehouse, and external message broker as though they were PostgreSQL rows.

Making every step synchronous also means that a temporary email failure could make the payment endpoint appear to fail even though the payment has already occurred.

At the other extreme, making everything asynchronous risks accepting a webhook without durably recording it, creating duplicate payments, losing audit history, or showing an incorrect financial balance.

The correct design begins with one question:

Which facts must become true together, and which consequences may happen later?

That question is the practical foundation of ACID, BASE, and consistency engineering.

“Consistency” has several meanings

The word consistency is often used as if it described one universal database property. In practice, engineers use it to describe several different concerns.

1. Invariant consistency

Invariant consistency means that the data satisfies the business rules that must always remain true.

For a B2B invoicing platform:

1payment.amount > 0 completed_refunds <= captured_payment invoice.business_id = payment.business_id invoice_number is unique within one business

This is the meaning of consistency inside ACID: a successful transaction moves the database from one valid state to another valid state.

The database does not invent these business rules. The engineering team must express them through:

• Data types
• NOT NULL constraints
• Unique constraints
• Foreign keys
• Check constraints
• Transaction logic
• Controlled state transitions

An “ACID-compliant database” cannot protect an invariant that the schema and application never define.

2.Transaction isolation

Isolation concerns what concurrent transactions are allowed to observe and how they may interfere.

Suppose two workers attempt to issue a KES 40,000 refund against a payment with only KES 50,000 remaining.

Each worker independently reads:

1Refundable balance: KES 50,000

Both conclude that their refund is valid. If both commit, the system returns KES 80,000.

Each worker’s decision appeared valid when considered alone. The combined result became invalid because the transactions interacted concurrently.

3.Replica consistency

Replica consistency concerns whether different database copies expose the same state at the same time.

A user might:

1. Update an invoice on the primary database.
2. Refresh the page.
3. Read from a replica that has not replayed the update.
4. See the previous invoice state.

The authoritative data may be correct while the replica is temporarily behind

4.Workflow consistency

Workflow consistency concerns state spread across independent components.

After a payment commits:

• The invoice page may show it immediately.
• The receipt email may arrive several seconds later.
• The PDF may still be generating.
• The dashboard projection may update after one minute.
• The analytics warehouse may update after an hourly batch.

The system is temporarily inconsistent at the workflow level even though the authoritative financial state is correct.

5.User-observed consistency

Users experience consistency through product behavior:

• Read-your-writes: After changing an invoice, the user sees the new version.
• Monotonic reads: After seeing version 8, the user does not later see version 7.
• Authorization consistency: A deactivated user cannot continue indefinitely with cached permissions.
• Honest intermediate state: A payment awaiting confirmation is shown as pending—not paid or silently absent.

These guarantees are not automatically produced by describing a database as ACID-compliant.

Start by drawing transaction boundaries

The running payment workflow contains three important boundaries.

Boundary 1: durable webhook receipt

Before acknowledging a provider, the platform should either complete the financial operation or durably record enough information to process it later.

The receipt needs a provider-scoped identity:

1UNIQUE ( provider, provider_account_id, provider_event_id )

This protects the intake stage from duplicate provider deliveries.

It should not be confused with an outbound idempotency key. A webhook identity deduplicates an event received from a provider. An outbound idempotency key prevents the platform from requesting the same charge or refund twice

Boundary 2: authoritative financial transaction

The system applies the business operation by writing:

• The payment record
• Balanced ledger entries
• The invoice payment projection, if it is stored
• The authoritative audit event
• An outbox or durable background-job record

Where these records represent one business decision, they should commit atomically.

Boundary 3: asynchronous consequences

The system can then:

• Generate a versioned receipt PDF
• Deliver the receipt email
• Send another notification
• Refresh analytical projections
• Update a search index

These effects must be retryable. Their temporary failure should not erase a valid payment.

Two valid webhook-processing models

There is more than one defensible place to acknowledge the provider.

Model A: synchronous financial processing

If the process crashes after commit but before acknowledgement, the provider retries. The unique receipt identity reveals that the complete transaction already committed, so the platform can acknowledge the duplicate without creating another payment.

This model keeps the state transition simple but makes provider response latency depend on financial processing.

Model B: durable intake followed by asynchronous processing

This model acknowledges quickly and absorbs traffic bursts more easily. It requires a richer receipt state machine:

1RECEIVED -> PROCESSING -> PROCESSED \-> FAILED_RETRYABLE \-> FAILED_FINAL

In this design, an existing receipt does not necessarily mean that payment processing succeeded. The application must inspect its state instead of treating every uniqueness conflict as a completed operation.

What ACID actually guarantees

ACID describes four properties of database transactions:

• Atomicity
• Consistency
• Isolation
• Durability

Each protects against a different class of failure.

Atomicity: all or nothing

Atomicity means that the transaction’s database changes commit together or roll back together.

1BEGIN; INSERT INTO payments (...); INSERT INTO ledger_entries (...); -- debit INSERT INTO ledger_entries (...); -- credit UPDATE invoices SET payment_status = 'PAID' WHERE id = $1; INSERT INTO audit_events (...); INSERT INTO outbox_events (...); COMMIT;

If one ledger entry violates a constraint, the payment, invoice update, audit event, and outbox record are rolled back—provided they were all placed in the same transaction.

Atomicity does not automatically include:

• Sending an email
• Uploading a PDF
• Calling another service
• Publishing directly to an external broker
• Updating an independently managed analytics database

Those operations exist outside the local transaction.

Consistency: preserve defined invariants

Consistency means a committed transaction leaves the database in a valid state.

1CHECK (amount > 0); UNIQUE ( provider, provider_account_id, provider_event_id );

A tenant-scoped foreign key can prevent a payment belonging to Business A from referencing an invoice belonging to Business B:

1FOREIGN KEY (business_id, invoice_id) REFERENCES invoices (business_id, id);

PostgreSQL Row-Level Security may provide additional access control, but it does not replace the composite relationship constraint.

Consistency is shared between:

• Schema constraints
• Transaction logic
• State-transition rules
• Application validation
• Correct concurrency control

Isolation: control concurrent interference

Isolation determines how simultaneous transactions affect one another.

Concurrent excessive refunds might be protected by:

• Locking the payment aggregate
• Atomically reserving refundable value
• Serializing refund commands per payment
• Using serializable transactions with whole-transaction retries

Isolation does not mean requests literally execute one at a time. It means their observable interaction satisfies the selected concurrency guarantees.

Durability: survive the defined failures

After a database confirms a commit, durability means that the transaction survives failures covered by the database’s configured durability model.

For PostgreSQL, this involves WAL, commit configuration, storage behavior, and—when used—replication. Backups and recovery testing extend protection to broader events such as operator error, corruption, malicious deletion, or regional loss.

Durability should be described through explicit objectives:

• Which failures must the system survive?
• How much committed data may be lost?
• How quickly must service be restored?
• What happens if the synchronous replica is unavailable?
• Has restoration actually been tested?

Synchronous replication can target an RPO of zero within a defined failure model. It may also block or reject writes when the required replica is unavailable. Durability and availability can impose competing operational choices.

What ACID does not guarantee

Even an ACID-compliant database does not automatically provide:

• Correctly defined business rules
• Request idempotency
• Exactly-once external effects
• Cross-service atomicity
• Correct authorization
• Tenant isolation
• Successful backups
• Multi-region recovery
• Event publication after commit
• Correct application code

The core principle is:

ACID protects a database transaction. It does not make the entire distributed workflow one transaction.

Distributed transactions and two-phase commit exist, but they introduce coordination, availability, recovery, and operational costs. A local transaction plus durable messages and idempotent consumers is often the more practical boundary for SaaS workflows

Failure location determines the recovery mechanism

This classification prevents teams from trying to solve every failure by increasing the database isolation level.

ACID and BASE are not opposites

BASE is commonly expanded as:

• Basically Available
• Soft state
• Eventual consistency

BASE is less a precise transaction specification than a description of systems that allow state to propagate and converge while prioritizing distributed operation and availability.

A reliable application can use both.

ACID at the financial core

When a payment is applied, the platform atomically commits its authoritative financial records.

That decision is immediately true inside the system of record.

Eventual consistency outside the core

After the commit:

• The receipt may still be generating.
• The email may still be queued.
• A dashboard may be behind.
• The analytics warehouse may not yet contain the event.
• A search index may not show the payment.

These are delayed projections and side effects. Their temporary lag does not need to invalidate the committed payment.

Basically available means honest degradation

A basically available system attempts to remain useful when some components are delayed or unavailable.

For example:

• The invoice page can show a committed payment when email delivery is unavailable.
• The payment endpoint does not wait for the analytics warehouse.
• A dashboard displays its last successful update time.
• A receipt endpoint returns “generating” rather than a misleading 404.
• Exact invoice-number lookup may fall back to PostgreSQL during a search outage.

Availability should not mean falsely claiming that every subsystem is current.

Soft state is rebuildable state

Soft state may change because events arrive, retries complete, caches expire, or projections are rebuilt—even when no user directly edits it.

Examples include:

• Cached invoice summaries
• Dashboard totals
• Search documents
• Receipt-generation status
• Analytics aggregates
• Replica state

This data may be operationally important. It is “soft” because an authoritative source or durable event history can correct or reconstruct it.

Eventual consistency needs a convergence contract

Saying “the system is eventually consistent” is incomplete. A defensible convergence contract defines:

1. Authoritative source: Which record represents the truth?
2. Expected lag: How long may the derived state be behind?
3. Retry policy: How are temporary failures retried?
4. Ordering policy: What happens when events arrive out of order?
5. Deduplication: What happens when an event is delivered twice?
6. Reconciliation: How are missing or incorrect projections detected?
7. Visibility: How do users and operators know that state is stale?
8. Terminal failure: When does automation stop and request intervention?

Without these controls, “eventual” may simply mean “we hope it updates later.”

1Durable authoritative state + Durable publication record + Retryable idempotent consumer + Reconciliation = Defensible eventual consistency

Domain identity matters more than gateway identity

An invoice-status projection should not use gateway_event_id as its universal idempotency key. Invoice state may change because of bank reconciliation, M-Pesa, withholding tax, credit notes, refunds, and reversals.

Use a domain event:

1event_id invoice_id invoice_version event_type occurred_at recorded_at

The consumer deduplicates by event_id and prevents older state from replacing newer state by comparing invoice_version.

Timestamps do not safely order financial events

Provider clocks may differ, multiple events may share one timestamp, and late deliveries are normal in distributed systems.

Use an aggregate version or stream sequence:

1invoice_2841, version 17 invoice_2841, version 18 invoice_2841, version 19

A projection can reject regression:

1UPDATE invoice_projection SET status = $1, source_version = $2 WHERE invoice_id = $3 AND source_version < $2;

If version 19 arrives before version 18, the consumer can ignore version 18 after 19 has been applied, buffer it while waiting for the gap, or rebuild the projection from authoritative state. The correct choice depends on whether events represent complete state or incremental changes.

Idempotency must match the business effect

Each derived workload needs an identity that remains stable across retries.

A new ETL batch ID identifies a processing attempt. It does not identify the logical financial fact being projected.

Immutable receipt artifacts must be versioned

A WORM object cannot simultaneously be overwritten in place.

Use a versioned key:

1receipts/{business_id}/{payment_id}/v1.pdf receipts/{business_id}/{payment_id}/v2.pdf

The generator reads an immutable receipt snapshot containing the receipt number, business and client details, amount, currency, allocation, tax treatment, and template version.

Two executions may still generate different bytes because PDF metadata, fonts, or timestamps differ. Idempotency is therefore enforced through a unique business record such as:

1UNIQUE (payment_id, receipt_version)

The platform accepts one artifact for that version and retains its object identifier and content checksum.

Email is usually at-least-once

Checking receipt_sent_at before calling an email provider does not close the crash gap:

1Check marker: not sent Provider accepts email Worker crashes Marker remains not sent Retry sends again

If the provider supports idempotency, pass a stable notification identity. Otherwise, retain attempt history, make the content duplicate-tolerant, and document the delivery behavior honestly.

The system can guarantee durable intent to send. It cannot generally guarantee exactly one human-visible email across an independent provider boundary.

Monitor projection age, not only queue depth

Queue depth alone is insufficient. A queue can be empty because the upstream publisher has stopped.

Useful signals include:

• Age of the oldest unprocessed event
• Last successful consumption time
• Source and consumer high-water marks
• Projection version lag
• End-to-end event latency
• Retry and dead-letter counts
• Reconciliation mismatches
• Object-generation backlog

These measurements turn convergence from a promise into an observable service objective.

Isolation levels and concurrent correctness

Atomicity protects the contents of one transaction. Isolation protects transactions from unsafe interaction with other transactions.

A transaction can be perfectly atomic and still participate in an incorrect concurrent result.

Common isolation anomalies

Dirty read

A transaction reads data written by another transaction that has not committed. If the writer rolls back, the reader acted on data that never officially existed.

PostgreSQL does not permit dirty reads. Its READ UNCOMMITTED mode behaves like READ COMMITTED.

Non-repeatable read

A transaction reads the same row twice and receives different committed values because another transaction changed it between the reads.

Under PostgreSQL READ COMMITTED, each statement receives a new snapshot, so this can occur.

Phantom read

A transaction repeats a predicate query and receives a different set of rows because another transaction inserted or removed matching records.

An aggregate rule such as “sum of completed refunds must not exceed the payment” is vulnerable if concurrent transactions independently query and then insert different child rows.

Lost update

Two operations read one value, calculate new values independently, and one overwrites the other.

1Paid amount: KES 10,000 Worker A adds KES 5,000 -> writes KES 15,000 Worker B adds KES 8,000 -> writes KES 18,000 Correct result: KES 23,000

An atomic expression avoids the application read-modify-write gap:

1UPDATE invoices SET paid_amount = paid_amount + $1 WHERE id = $2;

Write skew

Two transactions read the same logical condition but update different rows, leaving an aggregate invariant invalid.

Concurrent refund inserts are an example: the rows do not directly conflict, but their combined total may exceed the allowed amount.

PostgreSQL isolation levels

PostgreSQL defaults to READ COMMITTED, not serializable isolation.

Serializable transactions may execute concurrently. PostgreSQL rejects one when the outcome cannot be reconciled with a safe serial order.

The application must handle:

1serialization failure -> roll back -> retry the complete transaction

Retrying only the last statement is unsafe because earlier decisions may have used a stale snapshot.

Isolation levels are not a substitute for data design

Different invariants have more direct controls.

Unique tenant-scoped client identity

Normalize a tax PIN and enforce its uniqueness within the business:

1CREATE UNIQUE INDEX clients_business_tax_pin_uq ON clients (business_id, normalized_tax_pin) WHERE normalized_tax_pin IS NOT NULL;

RLS controls access; the unique index prevents concurrent duplicates. The product must also decide whether an archived client’s PIN may be reused. For financial history, linking a new record to the existing archived client may be safer than reusing the identity.

Optimistic concurrency for drafts

Use an integer version rather than a timestamp:

1UPDATE invoices SET notes = $1, version = version + 1 WHERE id = $2 AND version = $3;

If no row is updated, another user edited the draft. Return 409 Conflict and ask the user to review the newer version rather than automatically overwriting it.

Atomic refundable-value reservation

One design stores an authoritative refundable amount on the payment:

1UPDATE payments SET refundable_amount = refundable_amount - $1 WHERE id = $2 AND refundable_amount >= $1 RETURNING refundable_amount;

The refund intent and reservation commit together. The external provider call then occurs outside the database transaction using a stable platform refund idempotency key.

If the provider permanently rejects the refund, a compensating transaction releases the reservation. The database transaction should not remain open across the network call.

Short job claims with leases

FOR UPDATE SKIP LOCKED allows workers to claim different jobs without waiting for one another. It does not create exactly-once side effects.

A worker should:

1. Lock a small batch in a short transaction.
2. Mark the jobs PROCESSING with a worker identity and lease expiry.
3. Commit and release the locks.
4. Perform the external work.
5. Record success or a retryable failure.

If a worker crashes after the side effect but before marking success, another worker may retry after the lease expires. The side effect still needs a stable idempotency identity or duplicate-tolerant behavior.

Corrected concurrency-control matrix

Gapless invoice numbers deserve a separate policy

Unique, monotonic, and gapless are different requirements.

• Unique: No number is used twice.
• Monotonic: Later allocations are higher.
• Gapless: No number is ever absent.

Gapless numbering is difficult under rollbacks, cancellations, crashes, and distributed processing. Retrying by simply fetching a higher number preserves uniqueness but creates a gap.

If regulation or business policy requires an accountable sequence, allocate the number only when the invoice is issued, inside the issuance transaction. Do not delete issued documents. Represent cancellation using VOID while retaining the number and audit history.

Drafts can use internal UUIDs and receive the business-facing number only at issuance.

Reports need a defined point in time

A financial report generated while payments continue posting must state what “consistent” means.

Possible definitions include:

• All data committed before the report transaction began
• All data through a recorded ledger sequence
• All replica data through a confirmed WAL position
• All business events through an accounting cutoff time

A PostgreSQL REPEATABLE READ transaction can provide a stable snapshot without preventing ordinary writes through long-held row locks. Large reports can still consume CPU, memory, I/O, connections, and vacuum resources, so snapshot correctness does not remove the need for workload isolation.

A practical consistency-design checklist

For every important workflow, answer these questions.

Transaction boundary

• Which facts must commit together?
• Which effects cross a system boundary?
• What durable record proves that an external effect is still required?
• What happens if the process crashes immediately after commit?

Invariants and concurrency

• Which conditions must always remain true?
• Can they be expressed through constraints?
• Can two transactions pass the same validation concurrently?
• Is a lock, atomic update, optimistic version, or serializable retry appropriate?

Idempotency

• What is the stable identity of the business operation?
• Is it an inbound delivery, outbound request, domain event, or notification?
• Where is uniqueness enforced?
• Can the effect safely occur twice?

Eventual consistency

• What is the authoritative source?
• How much lag is acceptable?
• How are ordering and duplicate delivery handled?
• Can the projection be rebuilt?
• How is projection age measured?
• What honest stale state does the user see?

Durability and recovery

• Which failure model does durability cover?
• What are the RPO and RTO targets?
• What happens when a synchronous replica is unavailable?
• Are backups protected from privileged deletion?
• Has restoration been tested?

Conclusion

Consistency is not one switch that a database turns on.

It is a set of promises made at different boundaries:

• Database invariants protect valid financial state.
• Atomicity commits related records together.
• Isolation controls concurrent interference.
• Durability protects committed state within a defined failure model.
• Idempotency absorbs duplicate requests and deliveries.
• Outbox records bridge database commits to asynchronous work.
• Convergence contracts make eventual consistency measurable.
• Replication, backups, and immutable retention address broader failures.

The B2B payment workflow therefore uses ACID and BASE together. PostgreSQL protects the authoritative financial decision. Durable events, retryable consumers, versioned projections, and reconciliation carry that decision across the rest of the system.

The next article will move beyond one database and examine what changes when state is replicated across nodes and regions: CAP, network partitions, and the latency trade-offs described by PACELC.