Batch write with audit log

Use a transaction when a user action touches more than one table.

import { backend } from "@layeron/core"
import { db, table, text, integer, rawSql } from "@layeron/modules"

const app = backend()

const database = db({
  name: "main",
  schema: {
    posts: table({
      id: text().primaryKey(),
      title: text().notNull(),
      status: text().notNull().default("draft"),
    }),
    audit_logs: table({
      id: text().primaryKey(),
      action: text().notNull(),
      targetId: text().notNull().index(),
      createdAt: integer().notNull().default(rawSql("(unixepoch())")),
    }),
  },
})

app.use(database)

app.post("/posts/:id/publish", async (request) => {
  const pathSegments = new URL(request.url).pathname.split("/")
  const id = pathSegments[2]
  const auditId = crypto.randomUUID()

  await database.transaction((tx) => [
    tx.table("posts").update({ status: "published" }).where({ id }),
    tx.table("audit_logs").insert({
      id: auditId,
      action: "post.published",
      targetId: id,
    }),
  ])

  return Response.json({ ok: true })
})