Transaction boundaries

A user replaces doc-456 in workspace W9 with immutable PDF version v8. The product permits processing to finish later. When we return acceptance, the service must remember which version it accepted and how to process it. Use ingestion job ing-123 and pending event evt-123 for that responsibility. Acceptance is not the later promise that search can already use the new PDF.

Consider three independent database writes: save version metadata, create the job, then remember to publish it. If the API stops after the first write, the library can show an accepted version that nobody will process. If a job refers to metadata never committed, the worker can receive work it cannot reconstruct. Successful statements are not enough when their combination carries the product promise.

State the invariant before choosing syntax: every newly accepted version has a durable matching job and discoverable handoff intent. We place those records in the same transactional database. That is an assumption about this design's ownership boundary, not a claim that any collection of services becomes atomic when it shares a request ID.

The PDF bytes are stored durably before this transaction; the database records an immutable reference to them. It does not contain the object store inside its transaction. A later database failure can leave an unaccepted object for safe cleanup. It must not leave us claiming responsibility for input or work that exists only in API memory.

In PostgreSQL, a transaction block groups the database statements we want to succeed or fail together. The displayed protocol is application pseudocode, including its branches, rather than executable SQL. It creates a version, changes the document's requested-version reference, creates the job and records pending publication before committing. The API sends acceptance only after that commit is confirmed.

Application protocol pseudocode: one new submission

Persist immutable PDF input before this database transaction.
BEGIN on one database connection
  Insert the scoped submission identity and its immutable input binding.
  Insert document-version v8 metadata referencing the retained input.
  Conditionally set doc-456's requested version to v8 at expected revision 7.
  Require exactly one accepted document row; otherwise ROLLBACK.
  Insert processing job ing-123 for v8.
  Insert pending outbox event evt-123 for ing-123.
  Record the submission's accepted version and job outcome.
COMMIT
Return acceptance only after confirmed durable commit.

On a duplicate submission constraint: ROLLBACK and resolve the original
bound outcome from authoritative state in a fresh transaction.
Do not perform broker or model calls inside this transaction.

Suppose creating ing-123 fails after the document row was changed. Roll back the transaction. Its uncommitted document and version changes do not become a partially accepted operation. Another fresh read after successful commit can observe the committed records; it does not observe half of this transaction while it is still open.

Use one transaction on one database connection. Sending separate statements through arbitrary pooled connections would not create one shared transaction. Client libraries can manage transaction blocks for you, but the application still needs to know when its transaction begins, commits, fails and is returned cleanly to the pool.

The scoped submission record belongs in this same atomic unit. It binds one client retry identity to its original inputs and accepted result. A unique key prevents concurrent repeated submissions from creating independent operations. If that insertion conflicts, roll back and resolve the existing outcome in a fresh transaction. A key without matching work, or work without its key, would reopen the retry gap.

Two clients both read document revision 7. One submits v8 and the other submits a different replacement. If each later writes its preferred version unconditionally, both transactions can commit in sequence and the second can overwrite a change its user never saw. A transaction around each write does not define our desired conflict policy.

UPDATE documents
SET requested_version_id = :new_version_id,
    revision = revision + 1
WHERE workspace_id = :workspace_id
  AND document_id = :document_id
  AND revision = :expected_revision
RETURNING revision;

-- Application requires exactly one returned row before proceeding.
-- Zero rows: roll back this submission's transaction and resolve the conflict.
-- One returned row is provisional until the enclosing COMMIT succeeds.

For this example, accept a replacement only when the document still has the revision the client edited. The UPDATE checks revision = 7 and increments it while setting the requested version. Require one returned row. A stale contender that returns zero must roll back its version, submission and other changes rather than create a job for a rejected replacement.

Under PostgreSQL Read Committed, competing updates can wait, then recheck their condition against the updated row. After the first accepted edit changes revision 7 to 8, the stale revision-7 condition no longer matches. The database performs that condition with the update; a separate application read of revision 7 would not provide the same gate.

A row-lock protocol can be another valid approach when it checks the same product rule. It also holds contending writers while the transaction remains open. Here the conditional update is enough for our single-document transition. Tell the second caller about the conflict so it can fetch current state and make a deliberate new request; do not silently reinterpret the old intent.

After commit, a dispatcher reads evt-123 and publishes a message identifying ing-123 and v8. The outbox is the durable record that publication is still owed. If the accepting API crashes immediately after the database commit, a replacement dispatcher can discover that record. It does not need the original request process to remember the next step.

Relay protocol pseudocode

Read a committed pending outbox record.
Publish {event_id: evt-123, job_id: ing-123, version_id: v8}.
After the broker confirms publication, record dispatch progress.
On failure or uncertainty, retain the record for another attempt.

Worker protocol boundary
  Consult authoritative job state for ing-123.
  Reuse completed results or perform effect-specific safe processing.
  Record durable outcome before acknowledging message progress.
  Repeated delivery keeps the same job and version identities.

Publishing directly after a document commit would leave a crash gap. Publishing before the database commit creates the opposite risk: a worker receives a message while the database operation can still roll back. Adding BEGIN around the database statements does not include a separate broker call, even when both happen in one function.

The chosen relay publishes and records progress after the broker's acknowledgment. A lost acknowledgment can cause another publication with the same identities. That is acceptable only because the processing side is designed for repetition. Marking an event dispatched before sending it would instead risk losing the only discoverable reminder.

We have added another component and some lag. Observe pending publication age and recover failed dispatchers; an outbox row sitting forever does not finish a user's document. A simpler job table can itself hold a discoverable pending-dispatch state if it preserves the same invariant. The separate outbox is our concrete arrangement, rather than a requirement to add a table to every asynchronous feature.

Suppose the broker accepts evt-123 and the dispatcher stops before recording progress. Recovery sends it again. Both messages still identify ing-123 and v8. Creating a new job on each delivery would turn a recovery attempt into new responsibility, so the worker consults the durable job instead of treating each message as a fresh upload.

Completed work can reuse its recorded outcome. Unfinished work follows a processing protocol whose harmful effects are safe to repeat. For indexing, define entries by document version rather than appending anonymous duplicates. A crash after an indexing call may leave uncertainty, so the index owner's actual write and reconciliation behavior matters. The acceptance transaction does not control that remote effect.

Another version can be accepted while v8 is still processing. Completing old work must not blindly make an obsolete version the current searchable result. Use a publication rule tied to the selected version and authoritative document state. The detailed ownership protocol belongs to the idempotency guide; this article establishes why it is still needed after a perfectly atomic acceptance.

Acknowledge message progress after the durable outcome has been handled. If the worker dies beforehand, recovery must still find unfinished responsibility. If it dies afterward, another delivery should recognize the result. Local transaction atomicity, discoverable delivery and safe processing are complementary boundaries. None alone makes all physical computation execute once.

The API sends COMMIT, then loses its connection before receiving the result. The database might have committed the document, job and event, or the transaction might have failed. The client's missing response does not distinguish those outcomes. Starting a brand-new submission can create another accepted operation while the first already exists.

The browser reuses the same submission identity for this attempt to replace the document. The service scopes it to the authorized workspace and operation and checks that its original input binding still matches. Read the recorded result from authoritative database state. A committed result returns its original version and job rather than creating another pair.

An absent read needs care. The original transaction can still be resolving, and a lagging replica can miss a committed operation. A retry under the same unique identity lets the database arbitrate against the original attempt; a transient miss is not permission to mint a different key. If authoritative state is unavailable, report that the outcome remains unresolved and retry within policy.

Retain submission records for the supported replay window and reject changed inputs under the old key. An intentional new replacement is new work, while a network retry is not. Also defer deletion of the retained PDF until references and the uncertain acceptance are safely resolved. Fast cleanup based on an assumed rollback could destroy input belonging to an accepted job.

Validate the request and persist the immutable PDF before opening the short database transaction. Do not wait inside it for OCR, model calls, queue acknowledgments or a user decision. Those waits keep database resources and acquired locks occupied while adding no atomic control over the remote system. Processing belongs to the job's longer lifetime.

For accepted work, assume the database uses its normal durable commit configuration. PostgreSQL's asynchronous commit can acknowledge before recent changes are durable and permits their loss after a crash. A local commit also does not establish zero-loss regional failover. State the actual failure boundary instead of using “ACID” as a guarantee against every storage disaster.

Observe transaction duration, lock waits, rejected revisions, unresolved submissions and pending publication age. They distinguish contention from a stalled dispatcher or an uncertain client outcome. On a database error, roll back and cleanly end the failed transaction before reusing its connection. Bounded retry should preserve intent and re-evaluate the operation, not repeat arbitrary fragments after a partial application error.

The design can change. A single record with embedded pending work may simplify the schema. A new rule spanning several documents needs a new concurrency argument, not just this one-row revision check. Our conclusion remains specific: commit the accepted document version, job and handoff intent together, then recover delivery and processing across their own boundaries.