Every table in our database is inserted with the word "placeholder" in a NOT NULL column, because the QR code contains the row's own id

In PubTrivia a pub puts a printed code on each table. A team scans it, the phone opens a page, and they are in that night's quiz as that table. No app install, no account, which is the entire reason the product works in a room full of strangers. The feature page walks through what the player actually does, and the short version is that the code encodes a URL and nothing else:

https://pub-trivia.app/play/<venueId>/<tableId>

Both of those are UUIDs, and tableId is a Postgres defaultRandom() primary key. Which produces a small, genuinely awkward ordering problem that I have now hit in three different projects: the image cannot be generated until the row exists, and the column holding the image is NOT NULL.

The insert that cannot know its own id

export const tablesTable = pgTable('tables', {
    id: uuid('id').primaryKey().defaultRandom(),
    venue_id: uuid('venue_id').notNull().references(() => venuesTable.id),
    table_number: text('table_number').notNull(),
    qr_code_url: text('qr_code_url').notNull(),
    is_active: boolean('is_active').notNull().default(true),
    created_at: timestamp('created_at').notNull().defaultNow(),
})

There are three ways out of this, and I picked the ugliest one on purpose.

You could generate the UUID in application code and pass it in, which makes the insert self-sufficient but moves identity generation out of the database for exactly one table. You could drop the NOT NULL, which makes every reader deal with a nullable column forever because of a window measured in milliseconds. Or you can insert a known-wrong value and immediately correct it:

const newTables = await db
  .insert(tablesTable)
  .values(
    tableNames.map((tableName) => ({
      venue_id: venueId,
      table_number: tableName,
      qr_code_url: 'placeholder',
      is_active: true,
    }))
  )
  .returning()

await Promise.all(
  newTables.map(async (table) => {
    const qrCodeDataUrl = await generateQrCode(venueId, table.id)

    await db
      .update(tablesTable)
      .set({ qr_code_url: qrCodeDataUrl })
      .where(eq(tablesTable.id, table.id))
  })
)

.returning() is what makes it work: one insert for all the tables, the generated ids come back, and then the codes are rendered and written in parallel.

The honest flaw is visible in that snippet. The insert and the updates are not in one transaction. If the process dies between them, you have rows whose qr_code_url is literally the string placeholder, and the UI will try to render that as an image. In practice the two statements are microseconds apart and the recovery is deleting the table and adding it again, so it has stayed on the list of things I know about rather than the list of things I have fixed. If I were doing it again I would wrap both in db.transaction and accept holding it open for the QR render, which is pure CPU and fast.

The image is a data URL in a text column

This is the decision people usually argue with, so here is the actual size. A real table URL is 101 characters. Rendered at 512 pixels with the qrcode package, the data URL it produces is 5,934 characters, which is about 4.3 KB of PNG once you undo the base64:

async function generateQrCode(venueId: string, tableId: string) {
  const playerUrl = `${PUBLIC_URL}/play/${venueId}/${tableId}`

  return QRCode.toDataURL(playerUrl, {
    width: 512,
    margin: 2,
    color: { dark: '#000000', light: '#FFFFFF' },
  })
}

So a 50 table venue, which is the ceiling on our Pro plan according to the pricing page, is carrying roughly 300 KB of base64 text in Postgres. That is not free, and the alternative, object storage, would mean a bucket, a public URL policy, a cleanup job for orphaned objects, and a second thing that can be down while the venue is trying to print its codes an hour before the quiz.

What the data URL buys is that every consumer is trivial. The card in the dashboard sets it as an src. The download button sets it as an href. The print-everything button hands it straight to jsPDF:

doc.addImage(table.qr_code_url, 'PNG', qrX, qrY, qrSize, qrSize)

Three consumers, no fetch, no signed URLs, no CORS. For an asset that is a few KB and is read by the owner of the row, embedding beat storing. If the venues were thousands of tables each I would have chosen differently.

The same reasoning, pushed further, is why our free QR generator has no backend at all. It renders in the browser and the result never leaves the machine, because for an image this small there is nothing a server adds.

A regular expression built from a name the user typed

The part of this file I actually want to show you. Venues add tables in groups: a label and a count, "Table" times 20, then "Booth" times 6. The next group of "Table" rows has to continue from the highest existing number rather than restarting at 1, so something has to read numbers off names that already exist:

function getNextTableNumber(label: string, tableNames: string[]) {
  const pattern = new RegExp(`^${escapeRegExp(label)}\\s+(\\d+)$`, 'i')
  const highest = tableNames.reduce((max, tableName) => {
    const match = tableName.match(pattern)
    if (!match) return max
    return Math.max(max, Number(match[1]))
  }, 0)

  return highest + 1
}

label comes from a form field. It is a user-controlled string being interpolated into a regular expression, and the escape is not decoration:

function escapeRegExp(value: string) {
  return value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')
}

Without it, three things go wrong at increasing severity.

A label of Table [1 is an unterminated character class, so new RegExp throws a SyntaxError from inside a server action, and adding tables fails with a message about nothing the venue can see.

A label of Table (back) does not throw. It compiles into a pattern with a capture group in the middle, which matches Table back 7 and does not match the row actually called Table (back) 7. Nothing errors, the numbering just restarts at 1 and the venue ends up with two tables sharing a name.

And a label made of nested quantifiers is the start of a denial of service against your own server, because the thing evaluating that pattern is your Node process rather than the visitor's browser.

None of this is SQL injection, and that is sort of the point. The input is going into a regex, not a query, and the regex is the under-watched one. Any time you see a template literal inside new RegExp with a variable in it, the question is where the variable came from.

The three caps, in the order they apply

After the names are built, they go through a short funnel, and the order matters:

tableNames = Array.from(new Set(tableNames))

if (tableNames.length === 0) return { message: 'Add at least one table name', success: false }
if (tableNames.length > 100) return { message: 'Create 100 or fewer tables at a time', success: false }

Dedupe first, then reject empty, then cap the batch at 100, then check the plan limit with a count over active rows only. The plan check is last because it is the only one that costs a query.

There is a gap in that funnel I will name rather than pretend about: the Set dedupes the batch against itself, and getNextTableNumber keeps generated names clear of existing ones, but a venue typing custom names by hand can create a second row called "Table 1". There is no unique index on (venue_id, table_number) to stop it. It is not harmful, each row still has its own id and its own code, and the host sees two cards with the same label, which is confusing enough that I would rather the database refused. That is a one-line migration I have not written.

Deleting a table does not delete the table

Last thing, and it is the one that constrains everything above:

// Soft delete - set is_active to false
await db
  .update(tablesTable)
  .set({ is_active: false })
  .where(and(eq(tablesTable.id, tableId), eq(tablesTable.venue_id, venues[0].id)))

Every answer row from every quiz ever played references table_id. A hard delete would either be refused by the foreign key or, with a cascade, would quietly delete the history of a quiz from six months ago because somebody rearranged the furniture. So the table goes inactive, the plan limit counts only active rows, and the session history still resolves the name of a table that no longer exists in the room.

Note the where clause in that update, which is the shape every action in this file shares. The id comes from the client. The venue comes from the session. Both must match, so a table id belonging to somebody else's venue updates zero rows rather than theirs.