[DETAIL] Publishing can start an AWS CodeBuild without an application audit log due to transaction boundary violation

[adriangohjw]

Note

From: https://app.detail.dev/oss/opengovsg/isomer/bug_e10ad3a4-fb12-48f2-a461-256c0f4c902a
FYI YouthTech participants: do not work on this

Summary

• Context: The publishResource, publishSiteConfig functions and the publish mutation in site.router.ts call publishSite inside database transactions.
• Bug: publishSite makes external AWS CodeBuild API calls inside the transaction, which cannot be rolled back if the transaction fails to commit.
• Actual vs. expected: External publishSite calls should happen AFTER transactions complete, following the pattern in publishPageResource and schedulePublishingJob.ts.
• Impact: If publishSite succeeds but the transaction fails to commit, an AWS CodeBuild runs without corresponding audit logs, creating orphaned builds with no traceability in the application database.

Code with Bug

export const publishResource = async (...) => {
  const byUser = await db.selectFrom("User")...

  return db.transaction().execute(async (tx) => {
    await logPublishEvent(tx, {...})

    await publishSite(logger, { siteId: resource.siteId }) // <-- BUG 🔴 external AWS call inside DB tx; audit log can roll back
  })
}

export const publishSiteConfig = async (...) => {
  const byUser = await db.selectFrom("User")...

  return db.transaction().execute(async (tx) => {
    await logPublishEvent(tx, {...})

    await publishSite(logger, { siteId: site.id }) // <-- BUG 🔴 external AWS call inside DB tx; audit log can roll back
  })
}

publish: protectedProcedure
  .input(publishSiteSchema)
  .mutation(async ({ ctx, input: { siteId } }) => {
    const byUser = await db.selectFrom("User")...

    return db.transaction().execute(async (tx) => {
      await logPublishEvent(tx, {...})
      await publishSite(ctx.logger, { siteId: siteId }) // <-- BUG 🔴 external AWS call inside DB tx; audit log can roll back
    })
  }),

Explanation

• logPublishEvent writes the application audit entry inside the transaction.
• publishSite triggers irreversible external side effects (AWS CodeBuild API calls via computeBuildChanges / startProjectById).
• If the external call succeeds but the DB transaction fails to commit, the audit log insert is rolled back while the build continues, leaving an orphaned CodeBuild run with no corresponding record in the app database.

Codebase Inconsistency

The codebase already uses the safer pattern (DB work in a transaction, then external publish after commit):

export const publishPageResource = async ({...}) => {
  await db.transaction().execute(async (tx) => {
    const fullResource = await getFullPageById(tx, {...})
    const version = await incrementVersion({ tx, ... })
    await logPublishEvent(tx, {...})
  })

  if (sitePublish)
    await publishSite(logger, {...}) // <-- called AFTER transaction
}

Recommended Fix

Move publishSite outside the transaction in publishResource, publishSiteConfig, and site.router.ts publish mutation, leaving only DB writes (including logPublishEvent) inside the transaction:

export const publishResource = async (...) => {
  const byUser = await db.selectFrom("User")...

  await db.transaction().execute(async (tx) => {
    await logPublishEvent(tx, {...})
  })

  await publishSite(logger, { siteId: resource.siteId })
}

History

This bug was introduced in commit 8f3d3b2. The original publishResource function (commit 3056719, Oct 2024) correctly called publishSite outside any transaction. The audit logging feature (Mar 2025) wrapped both logPublishEvent and publishSite inside a new db.transaction().execute() block to ensure atomic logging, inadvertently placing an external AWS API call inside a database transaction boundary.