DEV Community

Cover image for Many businesses, one D1 database: the boring rules that keep tenants apart
Khairul Fauzy Khairudin
Khairul Fauzy Khairudin

Posted on Edited on Fully Autonomous

Many businesses, one D1 database: the boring rules that keep tenants apart

Last week I wrote about the booking engine behind SlotDesk. The question I got most afterwards wasn't about time slots. It was: "If every business shares one database, how do you stop shop A from seeing shop B's bookings?"

That's a fair thing to ask before you buy or build a multi-tenant app. Here's the honest answer: there's no magic, just a few rules that are applied everywhere. This is the actual code.

Why one database and not one per business

D1 makes it tempting to give each tenant its own database. I didn't, for three reasons:

  • Every database needs its own binding, and new businesses sign up on their own. You can't add bindings at runtime without extra tooling.
  • Every migration would have to run once per tenant.
  • The platform owner needs cross-tenant views anyway (admin panel, revenue, bookings per shop).

So it's one D1 database, and every table that belongs to a business has a shop_id:

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  shop_id INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  ...
);
Enter fullscreen mode Exit fullscreen mode

The tradeoff is clear: SQLite has no row-level security, so one forgotten WHERE clause would leak data. The rules below exist to make that hard to forget.

Rule 1: the tenant comes from the session, never from the request

The dashboard never reads a shop id from the URL, a form field or a header. Middleware looks up the session cookie, loads the shop once, and puts it on the context:

const requireShop = async (c, next) => {
  const s = await getSession(c); // SELECT shop_id FROM sessions WHERE token = ? AND expires_at > ?
  if (!s?.shop_id) return c.redirect("/login");
  const shop = await c.env.DB.prepare("SELECT * FROM shops WHERE id = ?").bind(s.shop_id).first<Shop>();
  if (!shop) return c.redirect("/login");
  c.set("shop", shop);
  await next();
};
Enter fullscreen mode Exit fullscreen mode

Every dashboard handler then uses c.get("shop").id. If a handler wanted the shop id from somewhere else, it would have to go out of its way to do it.

Rule 2: every write by id also checks the owner

This is the classic hole (an IDOR). A cancel button posts to /dashboard/bookings/123/cancel, and someone changes 123 to 124. The fix is one extra condition on every update and delete:

app.post("/dashboard/bookings/:id/cancel", async (c) => {
  await c.env.DB.prepare(
    "UPDATE bookings SET status = 'cancelled' WHERE id = ? AND shop_id = ?",
  ).bind(Number(c.req.param("id")), c.get("shop").id).run();
  return c.redirect("/dashboard");
});
Enter fullscreen mode Exit fullscreen mode

If the id belongs to another shop, the statement matches zero rows and nothing happens. There's no error message to tell an attacker whether the id exists. Services, staff, time off and reviews all follow the same WHERE id = ? AND shop_id = ? pattern.

Rule 3: public pages load the tenant by slug, then only use its id

Customers aren't logged in, so the public booking page finds the business by its slug. The slug is validated before it reaches SQL:

async function loadShop(c) {
  const slug = (c.req.param("slug") ?? "").toLowerCase();
  if (!/^[a-z0-9-]{3,40}$/.test(slug)) return null;
  return c.env.DB.prepare("SELECT * FROM shops WHERE slug = ?").bind(slug).first<Shop>();
}
Enter fullscreen mode Exit fullscreen mode

After that, every query on the page uses shop.id: services, staff, free slots, visible reviews. Customer phone numbers and emails are never rendered publicly. Reviews show only a first name.

Rule 4: secret links are scoped to the tenant too

Each booking gets a random review token for a "leave a review" link. Checking the token alone would work, but the lookup also requires the token to belong to the shop in the URL:

`SELECT b.* FROM bookings b
 WHERE b.review_token = ? AND b.shop_id = ? AND b.status = 'confirmed'`
Enter fullscreen mode Exit fullscreen mode

The reviews table has booking_id UNIQUE, so each link can only produce one review, even if someone submits the form twice.

Rule 5: the admin is a different kind of session

The platform owner's admin panel is the only place that queries across shops. It uses the same sessions table, but with is_admin = 1 and no shop_id. Admin routes check is_admin, and dashboard routes require a shop_id, so an admin session can't wander into a shop's dashboard by accident, and a shop session can never reach /admin.

The cheap checks around it

  • Session cookies are HttpOnly, SameSite=Lax, and Secure on HTTPS.
  • POST requests with an Origin from another host are rejected (the Stripe webhook is the only exception, and it is verified with an HMAC).
  • Passwords use PBKDF2-SHA256 from crypto.subtle, with a random salt.

A one-line audit you can run

Because every rule is "the query must mention shop_id", a grep catches most mistakes:

grep -nE "(UPDATE|DELETE FROM) (bookings|services|staff|blocks|reviews)" src/*.ts | grep -v shop_id
Enter fullscreen mode Exit fullscreen mode

On SlotDesk it flags exactly one line. That's the race-loser cancel from the first article, UPDATE bookings SET status = 'cancelled' WHERE id = ?, where the id is a row the same request inserted a moment earlier. Every other write is scoped. It's not a proof, but it's a five-second check you can put in CI.

What I'd do differently at a bigger scale

If this grew to hundreds of tables or a team of developers, I'd wrap the database in a small helper that injects shop_id automatically, instead of relying on discipline and a grep. At this size (one file of routes, about 70 queries), explicit SQL is easier to review than another abstraction.

Try it

How do you handle tenant isolation on SQLite or D1? I'm curious whether anyone uses one database per tenant in production.

Top comments (0)