Design notes from Dynet: Excel as the integration contract, transactional imports, and six roles on one API

Dynet is a planning and tracking system for fibre-to-the-home installations in Dutch housing districts. A contractor gets a district from the network operator as a spreadsheet; six kinds of people — planners, surveyors, work preparation, installation planners, installers, and the office — have to move every flat in that district from “address on a list” to “connected”. I’ve been the only developer on it since October 2023: Express and MongoDB on the back, React and TypeScript on the front.

These are the decisions that turned out to matter, written for the next person who has to build a system where the input is a spreadsheet somebody else controls.

Table of contents

1. Excel is an API you don’t own

The operator’s sheet is the integration contract, and nobody negotiated it with you. The house number column is Huisnummer on one sheet, Huis nummer on the next, HuisNr from the colleague who exports differently. Rejecting the file with “invalid format” trains people to fix it by hand in Excel, which is how you get a district where every flat is on floor 1 with a trailing space.

The importer therefore treats headers as a matching problem, not a schema:

const requiredFields = [
  { field: "identifier", required: true,
    names: ["Opdrachtnummer", "Zoeksleutel", "Sleutel", "ID", "Reference", "Kenmerk", /* ... */] },
  { field: "address", required: true,
    names: ["Volledig adres", "Adres", "Address", "Straat", "Straatnaam en huisnummer", /* ... */] },
  { field: "houseNumber", required: false,
    names: ["Huisnummer", "Huis nummer", "HuisNr", "Nr.", "House Number", /* ... */] },
  // postcode, addition …
];

for (const def of requiredFields) {
  let found = def.names.find(n => columns.includes(n));
  if (!found) found = fuzzyMatch(columns, def.names); // substring, then Levenshtein ≤ 30%
  if (found) mapping[def.field] = found;
  else if (def.required) errors.push(`Missing column for ${def.field}. Expected one of: ${def.names.join(", ")}`);
}

Three things about this that earned their keep:

• Exact first, fuzzy second. Fuzzy matching alone will happily map Postcode to Post Code and Plaats. Try the exact synonym list, then normalise (lowercase, strip non-alphanumerics), then substring, then edit distance with a 30% ceiling. Order matters more than cleverness.
• Tell the user which column is missing and what would have been accepted. The error message is the documentation. Nobody reads a “file requirements” panel until the import fails; then they read it word for word.
• Preview before write. The parsed rows come back as a table the admin looks at before anything hits the database. Most bad imports die here, at zero cost.

Rows are grouped into buildings by the key hidden inside the operator’s order number, POSTCODE_HOUSENUMBER_ADDITION: the first two segments identify the building, the third the flat. That one convention replaced an address-string comparison that had been generating duplicate buildings whenever someone typed straat instead of Straat.

What I’d still add: a column-mapping step in the UI where the admin confirms the guess. Fuzzy matching removed most failures; a confirmation screen would remove the rest and make the guesses visible.

2. An import is a transaction or it’s a support ticket

A district import creates a district, links it into its area, and writes eight-to-eighty buildings with a few hundred flats. The failure you actually get in production is not “the import failed”; it’s “the import failed on row 212 and now there’s a district with 40 buildings, no flats and a name the admin can’t reuse.”

So the import runs inside a MongoDB session:

const session = await mongoose.startSession();
session.startTransaction();
try {
  const [district] = await District.create([{ name, area: areaId }], { session });
  await Area.findByIdAndUpdate(areaId, { $addToSet: { districts: district._id } }, { session });
  await Building.insertMany(buildings, { session });
  await Flat.insertMany(flats, { session });
  await session.commitTransaction();
} catch (err) {
  await session.abortTransaction();
  throw err;
} finally {
  session.endSession();
}

Either the whole district lands or none of it does, and “just re-run the import” becomes a safe instruction to give over the phone.

Two consequences worth knowing before you commit to this:

• Transactions need a replica set. Atlas is one by default; a mongod on a laptop isn’t. Every new machine hits Transaction numbers are only allowed on a replica set member or mongos exactly once — including mine, this week, when I stood up a throwaway database to take the screenshots for the case study. mongodb-memory-server has a MongoMemoryReplSet for precisely this.
• A transaction that takes ten seconds looks like a hung page. The import publishes progress — step number, stage, counts — to an in-memory tracker keyed by import id, and the browser subscribes over Server-Sent Events:

router.get("/import-progress/:importId", (req, res) => {
  res.set({ "Content-Type": "text/event-stream", "Cache-Control": "no-cache", Connection: "keep-alive" });
  const send = p => res.write(`data: ${JSON.stringify(p)}\n\n`);
  send(progressTracker.get(req.params.importId));
  const unsubscribe = progressTracker.subscribe(req.params.importId, send);
  req.on("close", unsubscribe);
});

SSE over WebSockets because the data flows one way, it survives proxies that dislike upgrades, and EventSource reconnects on its own. The modal shows “Creating district…”, “Processing buildings 40/80”, then the summary. Same information the log has, just pointed at the person waiting.

The operator sends a refreshed sheet weekly. That goes through the same validation in update mode: find the district, diff against what’s there, report conflicts, and only then write. The import history per area exists because “did last Sunday’s file go in?” was the most common question in the first month.

3. Six roles, one API

The roles are the organisation chart of a fibre crew: Technische Planning, Technische Schouwer, Werkvoorbereider, HAS Planning, HAS Monteur, and Admin. They log into the same app and should see different apps.

Authentication is a short-lived JWT in an httpOnly cookie plus a refresh cookie, so the front end never handles a token it could leak. Authorisation is a middleware that reads the role codes from the token and compares them with what a route allows:

router.put("/block/:buildingId", verifyRoles(ROLES.Werkvoorbereider), validateBuildingBlock, blockBuilding);
router.post("/", verifyRoles(ROLES.Admin), validateCreateCity, addCity);

On the client the same role array drives everything: a RequireAuth allowedRoles={[…]} wrapper around route groups, the navigation, and which apartment form renders. The installer gets a photo upload; the surveyor gets cable direction and a signature; the planner gets contacts and a calendar. One /api/apartment/:id endpoint, one page component, six shapes.

Numeric role codes were the right amount of machinery for a six-person operation and I’d choose them again at that size. They stop being right the first time a client asks for “a planner who can also see the installer’s photos” — that’s the day to move to a permission table and let roles become named bundles of permissions. Build the seam (one verifyRoles middleware, one allowedRoles prop) so that day is a refactor and not a rewrite.

4. A building is not a list of flats

The domain fact that shaped the most code: fibre is pulled per riser, not per flat. Installers work a cable from the basement up; the flats on that cable get connected together. So the interesting object is the building’s layout — blocks, each with a wing type and a top floor, each floor mapped to a flat, a cable number and a cable length.

// Layout document
{ building: ObjectId, blocks: [
  { blockType: "leftWing", firstFloor: 0, topFloor: 3,
    floors: [ { floor: 0, flat: ObjectId, cableNumber: 1, cableLength: 8 }, /* … */ ] }
] }

The React side renders twelve schema components for the wing types (stairs left, stairs right, no stairs, apartment blocks, wings without a ground floor, straight cable runs) and a preview per type so the person configuring it can see what they’re describing. The payoff is in scheduling: a planner picks cable 2 and gets exactly the flats that hang off it, and books them as one appointment. That’s what the crew actually does on the day, so it’s what the software books.

5. The boring parts that paid for themselves

• Rate limits per surface. Five login attempts per fifteen minutes, a hundred API calls, ten uploads an hour. My own screenshot script tripped the login limit twice today, which is the limit doing its job.
• One error handler. Controllers are wrapped in express-async-handler and throw; a single middleware turns errors into { success, message, errors[] }. Validation via express-validator reports field-level problems in the same shape, so the front end has exactly one error format to render.
• Helmet, a CORS allow-list from the environment, request sanitisation. Unremarkable, non-negotiable.
• Structured logs with daily rotation (Winston) and /health, /health/live, /health/ready for the platform. Render restarts a container that fails readiness; that has saved a Monday morning at least once.
• OpenAPI from route annotations. Swagger UI at /api-docs is generated from JSDoc on the routes, so the documentation can’t drift further than the comment above the handler.
• Seeders. One command builds users with role colours, apartments and a month of appointments. That’s how features get demoed, how the tests get a known world, and how a case study gets screenshots with zero customer data in them.
• Tests where the blast radius is. Jest and Supertest on authentication, role checks, validation, the security middleware, the error handler and the health probes; React Testing Library on the hooks and pages. Not exhaustive — targeted at the places where a regression means somebody sees data they shouldn’t.

6. Caching, honestly

District aggregations (buildings, flats, completion percentages) are cached in-process for five minutes and invalidated on import. It cut the district page from “noticeable” to “instant” and cost twenty lines. It’s also wrong the moment there are two instances behind the load balancer. That’s a Redis afternoon, deliberately not spent yet; the note is in the code so it doesn’t get spent by accident either.

What I’d change with hindsight

TypeScript on the API, so the Mongoose models and the React types are one set of types rather than two that agree by discipline. Permissions instead of role codes at the first sign of overlap. The column-mapping confirmation screen. And a replica set in every developer’s local setup from the first commit, because transactions are the right call and the error message is not self-explanatory.

If you’re standing where this project started — an operation running on a spreadsheet, a few specialist roles, and a system that has to be faster than the spreadsheet on day one — the case study has the screenshots, and here’s how I work.