discord order desk

#what it is

a small shop sells services online and takes every order through discord, across two servers that share one back office. a customer presses a button, fills a short form, and the bot opens a private ticket thread for them. the order lands as rows in a google sheet the staff work from. the bot works out each order's place in the queue and its expected wait, keeps a queue board image up to date, and lets staff start, edit, close or cancel an order from the thread or from the shop's own website.

it started in may as a few channel tools, two quote calculators and one command that appended a row to a sheet. it grew one request at a time into the shop's order desk.

#the hard parts

almost none of this showed up in testing. it showed up under real load.

discord allows 1,000 active threads per server. one ticket is one thread, and threads stayed active for 7 days. one evening a broadcast i'd built, one message into every open ticket, reset the idle timer of 700+ threads at once. the server hit the limit and every new ticket failed for about 10.5 hours. now tickets auto-archive after 24 idle hours, a sweeper runs every 30 minutes, and if the limit is close right before a ticket gets created, an emergency sweep walks down a ladder of 3, 2 and 1 idle days first. it only archives, never deletes, so writing in an old ticket reopens it.

google sheets hands out the same row twice. values.append reads the last row and inserts after it, so two appends at the same moment get the same row. on a scratch sheet, 6 parallel appends came back as rows 2, 4, 4, 4, 3, 3, and the formula cells on 3 rows stayed blank for good. every write to a tab now goes through one lock, held for the whole find-then-write.

95 tickets in one hour. normal is 2 to 15. the sheet answered 429 for half an hour: the quota is 60 reads a minute, and one application read the sheet 14 times. now reads go through a short cache with single-flight, small ranges are merged into one request, and 429s back off with jitter.

button clicks timing out. discord gives a bot 3 seconds to answer a click. a status sweep every 30 seconds rescanned the whole ledger for every open draft: about 22.3 million row visits per run. that froze the bot for up to 5.6 seconds, and every click in that window died. one index per sweep brought it to 60ms, and a test runs the old and new lookups side by side to prove they agree.

the ticket counter went backwards. the next order number was the highest among live channels and active threads. archived threads don't show up there, and my own sweeper archived a lot. so 11 order numbers went out twice, two drafts claimed the same ledger rows, and one real cancel was undone 55 seconds later by its twin. the counter is now a stored high-water mark per server that only goes up.

freezes of 88 and 359 seconds. my monitor blamed swap. the container had swap off, and its counters showed 0 anonymous page refaults against 1.02 million file page refaults. the host was evicting the bot's own code pages under memory pressure, and running that code again meant reading it back from a busy disk. a memory reservation fixed it, and the monitor learned the difference.

#the sheet stopped being the database

in september orders moved into SQLite and the sheet became a copy. the queue columns had been 8,172 array formulas on the tab every append touched, each row scanning all rows above it, so the cost grew with the square of the row count. now one function computes queue position and wait, and the bot rewrites those columns as plain values every 20 seconds. before the switch, a check over 3,798 live rows found 0 mismatches.

#what else is in there

• a journal on disk for sheet writes that finish in the background. before it, one restart dropped 250 queued writes.
• a web api for the shop's website: synchronous answers, retries made safe by a request id, stable error codes. a Cloudflare Worker passes requests through untouched.
• every new order is pushed to the website too, without ever blocking intake: no await, a 3 second timeout, at most 8 in flight.
• queue and top-order boards drawn as png with @napi-rs/canvas and edited in place.

#where it is now

live in both servers and used every day. the order database holds about 5,900 orders and 4,900 commands from the website, and the bigger server's ticket counter is near 7,500. most commits follow the same loop: a report from the shop, a measurement on the live process, a fix and a regression test. most of the 51k lines of scripts are that measuring: repros, dry runs and read-only checks against the live sheet.