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,
...
);
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();
};
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");
});
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>();
}
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'`
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, andSecureon HTTPS. - POST requests with an
Originfrom 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
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
- Live demo (resets hourly): https://slotdesk-demo.rasareka.com/demo-barber. Owner login is
demo@example.com/demo1234. - The full kit, with a README section on exactly how businesses are kept apart: SlotDesk on Gumroad, https://rasareka.gumroad.com/l/slotdesk
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)