MCA Benches - A Multi-Tenant Guild Platform

August 10, 2026 Next.jsAWSServerlessTypeScriptMulti-tenancy

Guild Wars 2 endgame raiding is, more or less, a damage optimization problem. Every build has a known theoretical ceiling: the DPS you can squeeze out of a perfect rotation against a stationary training golem. The community site Snow Crows publishes those numbers as official benchmarks, and a big part of getting into a serious squad is proving you can get close to them.

My guild [MCA] (join.mca.gg) is one of the top Guild Wars 2 raiding guilds, with tens of active world records, and wanted a way to track that, plus a clear view of who can play what so raid nights are easier to plan. The workflow before was the one every guild has: people paste dps.report links into a Discord channel, an officer squints at them, and the results end up in a spreadsheet nobody fully trusts. That doesn’t scale, and checking “did you actually run the right food?” by hand is nobody’s idea of fun. Also, builds change over time so it’s hard to keep track of what you have to redo after a patch. So I built MCA Benches.

That was three months ago and it’s grown well past validating logs since, so this is a second pass over it, including the parts that fought back.

What it does

For every build the app keeps an official bench: a reference dps.report log plus its target number, imported straight from the build’s Snow Crows page. Players then submit their own attempts as dps.report links, and the app:

• auto-validates the attempt against the official bench (right spec, weapons, food, buffs, golem),
• diffs the rotation against the reference cast by cast,
• and ranks everyone on a leaderboard by how close their valid attempts get.

Around that there’s now an achievement system, per-patch recaps, saved squad compositions, raid night planning that posts itself to Discord, and cross-guild tournaments with a live draft and a bracket. One deployment serves several guilds at once, each can configure their own settings.

Three months in: a 31 player roster, 348 submissions from 20 of them, against 117 official benches drawn from a catalogue of 94 builds. 314 of those 348 cleared the automatic check on their own, and 277 were never looked at by a human at all.

Where a log comes from

Guild Wars 2 exposes no API for combat data at all, and I’ve written before about what it takes to pull it out of the running client yourself. What the raiding scene uses instead is arcdps, a third-party addon that hooks the game and records every combat event it sees: skill casts, damage ticks, buffs applied and removed, all timestamped to the millisecond. When a fight ends it dumps everything into a compressed .evtc file on your disk.

That file is unreadable on its own, so you upload it to dps.report, which runs it through Elite Insights, the open source parser the community maintains. Out comes a structured report: per player DPS, the full rotation, buff uptimes, damage broken down by skill, what everyone was wearing and eating. So the whole competitive side of the game rests on a hooking addon, a volunteer parser and a community upload service, none of which ArenaNet built or is obliged to keep working. It holds up remarkably well.

dps.report hands you back a permalink. Before this tool, they ended up in a Discord Channel or a Google Sheet.

Ingesting logs

MCA Benches never touches the game or the raw file. Everything starts from a dps.report link somebody pasted. dps.report exposes a getJson endpoint that returns the parsed report, so the whole pipeline is short: pull the permalink out of the URL, fetch the JSON, then read structured data out of one big blob. Logs are immutable once uploaded, so the fetch is using NextJS’s force-cache and we never fetch the same log twice.

Out of that JSON I pull the DPS, the recorder’s account name, spec, weapon sets, consumables, buff uptimes, the full skill rotation, and the per-skill damage distribution. The rest of the app is really just a bunch of opinionated views over that structure.

Two things that used to be typed in by hand are now imported. Builds come from Snow Crows: paste the build’s page and the app scrapes its traits, gear and published DPS, then keys the build on that URL, so a re-bench next patch lands on the existing build instead of creating a duplicate. Patches sync from GW2 Wingman in one click. Wingman doubles as a second accepted source for raid logs, since a log already linked in Discord shouldn’t need re-uploading somewhere else.

Auto-validation

The tricky thing about “prove you hit the bench” is that a big number on its own means nothing. You can inflate DPS with the wrong food, an extra boon from a friendly source, or a weapon set the build never intended. So the official bench log is the source of truth, and a submission is only auto-valid if it matches on the things that actually change the result.

The check itself is a pure function, which makes it easy to unit test and easy to reason about. It walks a list of rules and collects reasons to reject:

• Account - the log must be recorded by the account tied to that player.
• Spec - the log’s elite spec must match the build.
• Weapons - compared without caring about order. I normalize each weapon set (sort within a set, then sort the sets) so swapping which set is “first” doesn’t register as a difference.
• Consumables - food and utility are compared by their stats, not by the exact item. A different food with the same stats isn’t cheating, only a genuinely stronger one is.
• Boons / conditions - Some builds scale damage per boon or per unique condition, so the count only gets flagged when the build actually has a scaling modifier and your log carries more of them than the official one. Carrying fewer is your own loss, so it passes.
• Damage-modifier uptimes - things like “hit from behind” or “target above 90% health”. If you kept a modifier up far more than the reference log did, beyond a tolerance, then something you did was off and it deserves a manual verification..
• The golem - no bigger than the one the official bench used, the same health pool, and dead by the end of the log.

If nothing trips, the bench is auto-valid. Reviewers can still override either way, with a reason, and that manual verdict wins.

How often they have to is the whole question. Across those 348 submissions, 314 cleared the check and 34 were refused. A reviewer has touched 61: thirty-five to confirm a bench that was already valid, twenty-three to overturn a refusal, two to reject something the checker had passed, and one to agree with a refusal. So four submissions in five are decided end to end with nobody looking, and when the checker is wrong it’s nearly always wrong in the strict direction. That’s the direction I’d pick. A false rejection annoys one person for an evening; a false pass corrupts the leaderboard everyone is being ranked on, and nobody finds out.

The 34 refusals are also not spread evenly across the rules:

Three submissions trip two rules at once, so the column sums past 34. Account, spec, weapons and golem have never refused anything, which is what you would hope: those four are the rules nobody can trip by accident.

Since every one of those rules reads its answer off the official log, the build page can just show you what to pay attention to up front instead of only telling you afterwards why your log got rejected.

Three of those rules took me a long time to get right.

Boon scaling has to come from the build’s traits, not from the log. My first version asked the log which damage modifiers existed. Because the game does not expose traits, Elite Insights lists every modifier your profession could trait, whether you took it or not, so almost every submission got flagged and almost every flag then got overridden by hand. Not great. The fix was to scrape traits at Snow Crows import time and ask the build instead, and the table above is what that looks like now: boon count is responsible for exactly one refusal in the entire database.

The golem is read off the official bench log rather than hardcoded. It would be tidy to assume the standard training golem, but a bench can be a guild’s own recording rather than an imported Snow Crows one, and there have been benches on the Large Kitty Golem in the past. Holding every build to one standard would reject exactly the runs that copied the official setup most faithfully.

Off-weapon damage is the one that feels like an arms race. Park a staff on your second weapon set, cast Meteor Shower out of combat, swap back to your real weapons, then pull. The meteors land inside the log and count towards your number, but a log only starts when the fight does, so the cast itself happens outside it. arcdps never recorded it, so Elite Insights ends up looking at damage from a weapon it has no evidence you ever held, and reports the unused set as “Unknown”. Every check above passes happily. Catching it means comparing which weapons dealt damage against which weapons you cast from, and flagging damage credited to a weapon that never shows up in the rotation. Honest precasting still works, because a Dragon’s Tooth thrown a moment before the pull comes off the weapons you actually go on to fight with. It’s refused nobody in three months of production. A check that never fires is either deterrence or dead weight, and from the inside I can’t tell you which.

Venoms & Allies-Benches

Then there’s a validation problem that has nothing to do with cheating. Thief builds that share damaging venoms apply them to four allies as well as themselves, and Snow Crows publishes a benchmark number that includes that ally damage, measured against a log recorded solo. So a flawless run scores about 89% and there’s nothing the player can do about it. You can also not measure without, because that would incentivise pressing venoms at a time that would not be optimal in a group settings. All raids are group content, so we want to incentivice playing what is actually best for your group.

The only fix is to compute the missing half yourself. The app reads the venom casts out of the log and simulates what those shared procs would have done on four allies: six poison stacks per cast, the game’s standard condition formula, the Poison Master multiplier, 25 stacks of vulnerability on the golem, and the average uptime of the build’s stacking damage buffs, read back out of the log. Casts too close together get deduplicated, and a cast near the end of the fight is prorated instead of counted whole.

I ported the community calculator first and it undershot every published benchmark, because it hardcodes a 6 second poison duration when the duration actually scales with the build’s expertise. Its own build table even carries a flag for this that the formula never reads. Solving the duration back out of the published numbers per build gets within about 1% on all four Antiquary benchmarks. Most affected benches move by 1-3%, and the submission form shows what the simulation added so you can see where your number came from.

Diffing rotations

This is still my favourite part. Two rotations are just two sequences of skill casts, and I want to line them up so a player can see exactly where they drifted: which casts match, which ones they skipped, and which ones they added that the reference never used.

That’s a diff. Same problem git diff solves, and the same algorithm underneath: the longest common subsequence. I filter out auto-attacks and weapon swaps, which are pure noise here, then build the classic LCS table over skill IDs and backtrack it into match / official-only / personal-only rows:

// off[] and pers[] are the two cast sequences, by skillId
for (let i = 1; i <= n; i++) {
  for (let j = 1; j <= m; j++) {
    dp[i][j] =
      off[i - 1].skillId === pers[j - 1].skillId
        ? dp[i - 1][j - 1] + 1
        : Math.max(dp[i - 1][j], dp[i][j - 1]);
  }
}
// backtrack: equal skill -> "match", otherwise step the side
// with the larger LCS value to emit an "only" row

The backtrack walks from the bottom-right corner: when the two casts agree it emits a match and steps both sequences, otherwise it follows whichever neighbour preserved the longer subsequence and emits the dropped cast as official-only or personal-only. Rendered side by side with timestamps, you get a readable timeline of where a rotation fell apart, plus per-skill damage deltas, buff uptimes, first-cast timing, and where you went idle between casts.

Here is the same diff, running on a short rotation you can break yourself. Drop a cast, add one the reference never used, or load one of the presets, and watch the alignment rearrange itself:

Leaderboard, badges and recaps

Ranking is deliberately forgiving. Hitting 100% of a Snow Crows benchmark is HARD, so getting close should still be worth something. Each valid attempt is scored as a percentage of the official number and bucketed into points:

Only a player’s best valid attempt per build counts. Points get summed across builds, ties are broken by average percentage, and ranks use standard competition ranking (“1224”, so tied players share a rank and the next distinct row jumps past them). Those four cutoffs are per-guild configuration now, because a casual guild wants a gentler scale than a world record one.

On top of that there are about 35 achievements in tiers, announced in Discord when they unlock. Some are derived live from existing data, but the ones that depend on a moment in time have to be written to a ledger, and a ledger can go stale. So if the bench behind a badge gets edited or invalidated later, a reconciliation pass takes the badge back instead of leaving it standing.

When a patch closes, every player gets a recap: best benches, rank movement, class spread, awards. It renders to a downloadable 1200x630 PNG, drawn client-side on a 2D canvas. The module is split in half: a pure half that turns data into display strings, unit tested because that’s where something can be silently wrong, and an imperative half that pushes pixels, which I verified by looking at it.

Planning a raid night is an optimization problem

Benchmarks were always a means to an end. What the guild actually wants is to know who can play what, so raid nights can be planned. That’s where comps came from: a reusable squad for one encounter, ten positions, each with a build or a reserved role like “Any Quickness”. On top of those sit plannings, one specific evening with a date, one or more bosses, and a crew for each, pushed to Discord as an embed the guild’s bot keeps up to date as the plan changes.

Splitting those two apart was the first real modelling decision. They used to be one thing, which meant an evening’s roster got written into a template that then gets reused every week. Now there’s exactly one place where a guild says who plays a position, and that’s the planning, not the comp.

Then there’s Autofill: given the players who signed up for an evening, fill the squad. The obvious implementation, walking the positions and taking the best available candidate for each, is wrong in a way that’s easy to miss. Take the first candidate and the player who can cover both the quickness position and a DPS position gets spent on whichever one comes first, leaving the other open. Fill greedily by DPS instead and one position takes the player another position needed.

It’s a maximum weight bipartite matching, so the whole squad gets solved at once with the Hungarian algorithm, which is trivial at ten positions. The concerns are banded into a single score: a bench on the open patch outranks a stale bench, which outranks a bare assignment, since somebody meaning to play a position beats leaving it empty. Within a band, the crew that puts up the most DPS wins. Candidates are ranked in signup order when weights are equal, so the same evening and comp always produce the same squad.

The difference is easier to see than to describe. Here is a four-position squad with a roster you can change, filled both ways at once:

Every position says what it rests on, which matters more than it sounds. “Assigned but never benched” is the guild’s own to-do list, falling out of a feature that wasn’t built to produce one. Right now that list has 46 entries on it, out of 109 positions the guild has told somebody they own, and nobody had to compile it.

Benchmarking has three months of use behind it. Night planning shipped a week before I wrote this, and its numbers are three nights, eight boss squads and eighty positions filled, so I can’t tell you yet whether it makes a raid night faster to organise..

A comp can also be imported from a raid log. That reads the squad and their subgroups out of a dps.report or Wingman log, then matches accounts to the roster and specs to builds. “Which build” can’t come from the spec alone though, because power and condition variants share one, so it comes from the weapons the position’s damage came out of, reusing the same machinery the off-weapon check needed. Who was playing quickness and alacrity gets worked out from boon generation, because what class somebody was on tells you very little about that.

One deployment, several guilds

The biggest architectural change since the first version is multi-tenancy: several guilds on one deployment, each seeing only its own roster, benches, assignments and comps, with a shared build catalogue maintained centrally.

The visible half of that is cheap and was still the first thing to break. A guild’s name and accent colour started out as NEXT_PUBLIC_* environment variables, which Next inlines at build time, so one bundle could only ever carry one guild’s name. Obvious in hindsight. Branding lives on the guild’s own row now and resolves per request.

The naive version of this is a tenant_id on everything. What it actually needs is four categories of data with different owners. The catalogue (patches, builds, official benches) is global, because those are facts about the game and identical for everyone. Selection is per guild: which parts of the catalogue a guild shows, plus its own private builds. Guild owned things like assignments, comps, plannings and achievements carry a tenant_id. And identity, meaning players and their personal benches, stays global, because a GW2 account and a dps.report log are facts about the world too. Tenant scoped player rows would give a dual member two identities, two uploads of the same log, and two places to apply a rename.

You can see a personal bench if its player shares your active guild, and its build is in that guild’s selection.

Both halves matter. The first stops guild B seeing guild A’s roster. The second stops a dual member’s bench on one of MCA’s private builds from showing up on guild B’s site.

Enforcing it is where things get uncomfortable, because Aurora DSQL has no foreign keys and no row level security. Nothing catches a cross-guild read except the where clause the query remembered to include. Three rules keep it honest:

1. Scope is a parameter, never resolved inside a query. This one is a real trap. Next’s unstable_cache derives its key from the wrapped function’s arguments, so a cached query that looked up its own guild would bake the first caller’s data into an entry that then gets served to everybody. It would hand out the wrong rows without throwing anything, and you’d have no way of noticing. A test enforces the rule mechanically by forbidding the query layer from importing the ambient tenant helpers at all.
2. Scope is a union rather than a string. Only the tenant arm is constructible today. The global arm exists purely so the compiler will find every call site if I ever add a public view.
3. Every scoped query has an isolation test. An integration suite seeds two guilds and asserts each query returns exactly one guild’s rows. A query without one isn’t finished.

Then why this database

Three rules in application code standing in for two database features is a fair thing to be suspicious of. Why put a multi-tenant app on the one Postgres that has neither of the features built for multi-tenancy? Row level security would let the database refuse a cross-guild read instead of trusting a where clause to remember, and both Neon and Aurora Serverless v2 are Postgres with RLS and foreign keys in the box.

Serverless v2 is easy to answer. An Aurora cluster lives in a VPC, the Lambda has to join that VPC to reach it, and the NAT Gateway from the next section walks straight back onto the bill. I’m not paying $33 a month for a constraint I can write myself, not on a guild tool.

Neon is the one I actually have to defend, because it scales to zero too, sits on a public endpoint, and has both features. Cost isn’t the argument. What DSQL gives me is that there’s nothing to hold: no database password anywhere, since the Lambda signs a short-lived token with its own execution role, and no second vendor with its own bill and its own credential to leak. Nothing to patch or size or upgrade either. And I wanted to run DSQL, which is a real reason - part of why this project exists is to find out where a new database’s constraints bite.

They bite. No foreign keys means nothing catches an orphan row. No RLS means isolation is a property of my code and my tests rather than of the engine, so on the day I write a query and forget its scope parameter, nothing underneath me objects. Some of it fixes itself - DROP COLUMN arrived months after I’d finished working around not having it - but foreign keys and row level security are the two I wanted here, and neither has landed.

If this were payroll I’d want RLS and I’d pay the NAT Gateway to get it. It’s a benchmark tracker for a raiding guild, the whole threat model is one guild seeing another guild’s roster, and I decided a test asserting isolation per query is proportionate to that. That’s a judgement about stakes rather than a claim that the database is better.

Nothing lists the guilds either. There’s no directory and no “who uses this” page, and the switcher only shows guilds you belong to.

Tournaments are tenants too

The community also runs an open community draft tournament, where a set of captains pick from individual players that signed up to form a team. My first reading was that a cross-guild competition needs a third visibility mode somewhere between “my guild” and “public”. It doesn’t. A tournament is just a tenant whose members happen to come from many guilds, and the sentence above already says the right thing: entrants from six guilds can scout each other inside the tournament while their home guilds stay invisible to one another, and nobody’s private builds leak into an open competition.

One thing does change. Because personal benches are global, merely joining a tournament tenant would expose an entrant’s whole bench history the moment they arrived, and somebody who only signed in to read the rules never agreed to that. So on a tournament, visibility keys on an approved signup instead of bare membership. Submitting the application is the moment of consent, and the form says plainly that entering means your logs are visible to everyone else in the event. That way no captain ends up drafting blind either.

A few smaller decisions I liked:

• A captain is someone who owns a team row, not someone with a Discord role. Roles are per server and captains are per edition, so the role version means somebody has to strip and reassign a hundred of them every year, and a forgotten one is a standing privilege bug.
• Draft picks are append-only with a monotonic index. The board is derivable, undo is just a delete, and snake order falls out of arithmetic instead of being stored. Two captains clicking at once is settled by the primary key on (draft, pick number): DSQL is optimistically concurrent, so the second write aborts on its own and I never had to add a lock or a queue.
• A pick has a visible countdown and no auto-pick. When it hits zero nothing happens except that everyone watching knows the captain is stalling, and on a stream with a host that’s the enforcement that actually works. Auto-pick would need a definition of “best available player”, which has no defensible answer and would eventually hand somebody a player they didn’t want, live.

Live updates during a draft are a polled version counter, one cheap row read a second, instead of SSE. Lambda response streaming through OpenNext is fragile enough that I didn’t want a live broadcast leaning on it, and with the auth wall up the audience is about ten captains and one OBS source anyway.

The bracket only stores the first round’s sides. Every later side is just the winner of the two matches feeding it, so storing one would be a second answer that can disagree with the first, and taking a result back needs no cascade. That rule came out of a bug: an empty slot in a later round usually means the match feeding it hasn’t been played, not nobody is coming. Read the first as the second and the first semi-finalist to win gets crowned champion.

Each side’s clear time is submitted on its own and the two together decide the match, so one person can check one team without waiting for the other, or seeing it. A single time on its own decides nothing, and a half submitted match just stays undecided instead of showing somebody in the lead.

Tournaments are also the one place where throughput actually matters. A guild uploads a few logs a week. A bench challenge with a few hundred entrants means a few hundred dps.report fetches and parses, most of them probably bunched into the last evening before the deadline. Three rules sit on that path, all of them before the fetch, because refusing after it costs exactly what refusing was supposed to save: a sliding allowance of 30 submissions per player per 10 minutes, counted from the rows themselves so it holds across however many Lambda instances are running; one entry per log URL, by anyone; and a cooldown on re-checking, which writes no new row and would otherwise be an unmetered fetch loop.

Auth, for free

There’s still no user system. Access is Discord OAuth gated on guild role membership. Signing in fetches your Discord guilds, intersects them with the registered guilds in the database, and reads each one’s role IDs off its row. That’s what lets one deployment serve several guilds, and lets a dual member hold a different standing in each. There are three tiers: full members, trials who can submit and edit only their own benches, and guild friends who see even less. None of it is hardcoded, so the whole thing can be forked and pointed at any guild. Locally there’s a dev login provider so you don’t need a real Discord account, and it’s hard disabled in production, since the app refuses to boot if it sees the dev flag next to a production database.

Tournaments added one more identity check, because prizes go to a GW2 account and it has to be a real one. A GW2 API key gets used exactly once, at verification time, to fetch the account and match the name, and is then thrown away. It never gets persisted. Verification is a one time answer, not an ongoing capability, and a credential into somebody else’s system is the one thing I’d rather the database never contain.

Architecture & Cost

The whole thing scales to zero, which was exactly what I wanted & what DSQL unlocked. The number worth looking at is the one that doesn’t scale to zero, and on most serverless stacks that’s a NAT Gateway: north of $30 a month before it forwards a single byte, sitting on the bill because the database lives in a VPC and the Lambda had to join the VPC to reach it. Once it’s there it’s usually the largest thing on it.

There isn’t one here. Aurora DSQL authenticates with IAM over a public endpoint, so the Lambda signs a short lived token with its own execution role and connects out over the internet like any other API call. No VPC, no NAT Gateway, and no VPC endpoints standing in for one. That single property does most of the work in the number at the end of this section.

The rest of the stack: Next.js 16 (App Router) with Drizzle ORM, deployed via OpenNext onto Lambda behind CloudFront, talking to DSQL, which is serverless, distributed Postgres.

flowchart LR
  User --> CF[CloudFront]
  CF --> Lambda[Next.js on Lambda]
  Lambda --> DSQL[(Aurora DSQL)]
  Lambda --> S3[(S3 log store)]
  Lambda --> SM[Secrets Manager]
  Lambda -- getJson --> DPS[dps.report / Wingman]
  EB[EventBridge weekly cron] --> Lambda
  Lambda -- webhook --> Discord

Secrets live in Secrets Manager and get fetched at runtime instead of baked into the Lambda environment, so they never sit in plaintext config. A weekly EventBridge cron nudges players who still owe a bench and fans out to each guild’s own webhook.

What I got wrong the first time round was storage. I kept the raw dps.report JSON in the database, and those blobs are megabytes each. DSQL is index organized, so a row’s data sits in the index and every scan over the table reads the blobs off storage whether the query wanted them or not. They completely dominated ReadDPU. They live in S3 now, gzipped and keyed by bench ID, with a plain filesystem store standing in locally.

The whole idle bill is roughly $0.40 a month, and all of it is that one Secrets Manager secret. Everything else (Lambda, CloudFront, DSQL, S3, logs) is pure pay per use, and a low traffic guild mostly stays inside the free tier. The NAT Gateway I never had to provision would have been about eighty times the rest of the bill put together.

This conect is not just applicable to Guild Wars 2. FFXIV has FFLogs, WoW has Warcraft Logs: the same shape of ecosystem, a third-party parser feeding a community upload service that raid leaders then read by hand, and the same spreadsheet nobody fully trusts sitting at the end of it.

What wouldn’t survive that move is the validation, which is welded to one game’s log format. What would is duller and more useful. Judge a submission against a reference log rather than a number somebody typed in. And solve filling a squad as a optimization problem, because the greedy version passes on whatever roster you tested with and fails on the one that turns up.