Confirming Uploads Before Committing Database Records #

Insert the asset row as pending when you issue upload credentials, have the client call a complete endpoint when its upload finishes, and in that endpoint HEAD the object to confirm it exists with the expected size (and checksum) before switching the row to uploaded in a conditional update; let the storage event perform the same idempotent transition as a backstop for clients that never call complete, and sweep pending rows older than the credential lifetime.

Direct-to-storage uploads split one logical operation across two systems that do not share transactions: the file goes to object storage, the record goes to your database. Create the record first and you get rows pointing at files that never arrived; create it after and you get files nobody knows about. Trust the client’s “done” message and anyone can mark an upload complete without uploading. The fix is a small state machine with confirmation from the one party that knows the truth — the storage service. This page belongs to upload completion events in backend validation and cloud storage architecture. It is the backend half of the flow started in generating secure presigned URLs with AWS SDK v3.

When to use this approach #

• Clients upload straight to S3, GCS or Azure Blob with signed credentials, and your database must reflect what is actually stored.
• The UI needs to know synchronously that an upload is safely stored (to show “uploaded”, to enable “submit”).
• You want storage events for asynchronous work without making them the only source of truth.

Prerequisites #

1. A relational table for assets with a status column and a unique storage_key.
2. @aws-sdk/client-s3 v3 (or your cloud’s SDK) with s3:GetObject/HeadObject on the upload prefix.
3. Upload-completion events wired to a consumer — see routing S3 upload events with EventBridge.
4. A scheduled job runner for the sweeper (cron, EventBridge Scheduler, Cloud Scheduler).

The two-system problem #

Implementation #

import { S3Client, PutObjectCommand, HeadObjectCommand, NotFound } from "@aws-sdk/client-s3";
import { getSignedUrl } from "@aws-sdk/s3-request-presigner";
import pg from "pg";
import { randomUUID } from "node:crypto";

const s3 = new S3Client({});
const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const BUCKET = process.env.UPLOAD_BUCKET!;
const URL_TTL = 900;

/* CREATE TABLE assets (
     id uuid PRIMARY KEY, owner_id text NOT NULL, storage_key text UNIQUE NOT NULL,
     expected_size bigint NOT NULL, content_type text NOT NULL, checksum_sha256 text,
     status text NOT NULL CHECK (status IN ('pending','uploaded','failed','expired')),
     created_at timestamptz NOT NULL DEFAULT now(), uploaded_at timestamptz);
   CREATE INDEX assets_pending_idx ON assets (created_at) WHERE status = 'pending'; */

/** 1. Issue credentials and record the intent. */
export async function beginUpload(ownerId: string, size: number, contentType: string, sha256b64?: string) {
  const id = randomUUID();
  const key = `uploads/${ownerId}/${id}`;
  await db.query(
    `INSERT INTO assets (id, owner_id, storage_key, expected_size, content_type, checksum_sha256, status)
     VALUES ($1, $2, $3, $4, $5, $6, 'pending')`,
    [id, ownerId, key, size, contentType, sha256b64 ?? null],
  );
  const url = await getSignedUrl(s3, new PutObjectCommand({
    Bucket: BUCKET, Key: key, ContentType: contentType, ContentLength: size,
    ...(sha256b64 ? { ChecksumSHA256: sha256b64 } : {}),
  }), { expiresIn: URL_TTL });
  return { id, url };
}

/** 2. Shared transition used by the client's /complete AND the storage event consumer. */
export async function confirmUpload(key: string): Promise<"uploaded" | "missing" | "mismatch" | "noop"> {
  const { rows } = await db.query(
    `SELECT id, expected_size, checksum_sha256, status FROM assets WHERE storage_key = $1`, [key]);
  const a = rows[0];
  if (!a || a.status !== "pending") return "noop";                 // unknown or already done

  let head;
  try {
    head = await s3.send(new HeadObjectCommand({ Bucket: BUCKET, Key: key, ChecksumMode: "ENABLED" }));
  } catch (err) {
    if (err instanceof NotFound || (err as { name?: string }).name === "NotFound") return "missing";
    throw err;
  }
  const sizeOk = Number(head.ContentLength) === Number(a.expected_size);
  const sumOk = !a.checksum_sha256 || head.ChecksumSHA256 === a.checksum_sha256;
  if (!sizeOk || !sumOk) {
    await db.query(`UPDATE assets SET status = 'failed' WHERE storage_key = $1 AND status = 'pending'`, [key]);
    return "mismatch";
  }
  // Conditional: only the first confirmer wins; the other becomes a no-op.
  const upd = await db.query(
    `UPDATE assets SET status = 'uploaded', uploaded_at = now()
      WHERE storage_key = $1 AND status = 'pending'`, [key]);
  return upd.rowCount === 1 ? "uploaded" : "noop";
}

/** 3. Client-facing endpoint: authorise, then confirm. */
export async function completeHandler(ownerId: string, assetId: string): Promise<{ status: number; body: object }> {
  const { rows } = await db.query(`SELECT storage_key FROM assets WHERE id = $1 AND owner_id = $2`, [assetId, ownerId]);
  if (!rows[0]) return { status: 404, body: { error: "not found" } };
  const r = await confirmUpload(rows[0].storage_key);
  if (r === "missing") return { status: 409, body: { error: "upload not found in storage — retry the upload" } };
  if (r === "mismatch") return { status: 422, body: { error: "stored file does not match the declared size or checksum" } };
  return { status: 200, body: { id: assetId, status: "uploaded" } };
}

/** 4. Sweeper: pending rows whose credentials expired and whose object never appeared. */
export async function sweep(): Promise<number> {
  const { rows } = await db.query(
    `SELECT storage_key FROM assets WHERE status = 'pending'
      AND created_at < now() - make_interval(secs => $1) LIMIT 500`, [URL_TTL * 2]);
  let expired = 0;
  for (const { storage_key } of rows) {
    const r = await confirmUpload(storage_key);         // a late upload still gets confirmed
    if (r === "missing") {
      await db.query(`UPDATE assets SET status = 'expired' WHERE storage_key = $1 AND status = 'pending'`, [storage_key]);
      expired++;
    }
  }
  return expired;
}

Line-by-line on the decisions that matter #

• The row exists before the upload, as pending. It reserves the key, records who may upload there and what they declared (size, type, checksum), and gives the sweeper something to find if the upload never happens. Nothing user-visible reads pending rows.
• ContentLength (and ChecksumSHA256) in the signed request. The signature binds the declared size and hash, so storage itself refuses a different body. The HEAD check then confirms what storage accepted. Binding checksums into presigned PUT URLs explains the checksum part.
• One function, two callers. The client’s complete call and the storage event both run confirmUpload. Whichever arrives first performs the transition; the conditional UPDATE … WHERE status = 'pending' makes the second a no-op. There is no race to resolve and no duplicate processing.
• ChecksumMode: "ENABLED" on HEAD. S3 returns stored checksums only when asked. Comparing them with the declared hash proves the stored bytes are the bytes the client hashed.
• Distinct outcomes. missing (409) tells the client to retry the upload; mismatch (422) means the stored object is wrong and the asset is failed; the UI can explain each.
• Sweeper re-confirms before expiring. An upload can finish after its row was created long ago (a slow multipart upload, a queued event). Confirming first means the sweeper never expires a file that is actually there.

The asset’s states #

Why both the client call and the event #

Each confirmation path covers the other’s weakness. The client’s complete call is fast — it runs the moment the upload finishes, so the UI can show “uploaded” and enable the next step without waiting — but clients crash, lose connectivity after the last byte, or are closed by users, and then the call never comes. The storage event is reliable in the long run — the storage service emits it whatever the client does — but it has no latency guarantee and can arrive seconds or occasionally minutes later.

Configuration gotchas #

HeadObject returns 403 instead of 404 for missing objects. Without s3:ListBucket permission, S3 hides whether a key exists and returns 403. Grant s3:ListBucket on the bucket (scoped by prefix) to the confirming role, or treat 403 on HEAD as “missing” deliberately.

complete returns 409 right after a successful PUT. Read-after-write is strongly consistent on S3 today, but some S3-compatible stores and CDN-fronted endpoints are not. Retry complete a few times with short backoff before telling the user to re-upload.

Duplicate processing started twice. Downstream work (thumbnails, scans) was triggered by both the complete call and the event. Trigger downstream work only from the transition itself — when the conditional UPDATE affected one row — not from each caller.

Sweeper expires multipart uploads still in progress. A 20 GB upload can take hours. For multipart, record the multipart upload ID and check ListParts before expiring, or scale the pending timeout with the declared size.

Verification #

# 1. Begin, then call complete WITHOUT uploading: expect 409.
curl -s -X POST localhost:8080/api/uploads/$ID/complete -H "Authorization: Bearer $T" -w ' %{http_code}\n'
# {"error":"upload not found in storage — retry the upload"} 409

# 2. Upload with the signed URL, then complete: expect 200 and status=uploaded.
curl -s -X PUT -H 'Content-Type: image/jpeg' --data-binary @photo.jpg "$URL" -o /dev/null
curl -s -X POST localhost:8080/api/uploads/$ID/complete -H "Authorization: Bearer $T"

# 3. The event arrives later: confirmUpload returns "noop"; no second transition in the audit log.
psql "$DATABASE_URL" -c "SELECT status, uploaded_at FROM assets WHERE id = '$ID'"

Frequently Asked Questions