One backend, many coffee shops: multi-tenancy with Express and PostgreSQL

My Coffee is an ordering platform that many coffee shops share. Each shop has its own menu, staff, customers and orders, and none may ever see another's. All of them live in one Express API and one PostgreSQL database. There are heavier ways to do this, like a database per shop. My Coffee uses the plainest one, and the interesting part is what keeps the plain approach safe.

1. Every row knows its shop#

About three quarters of the database's 100 tables carry a shop_id column that points at the shops table. The rest hang off a parent that has one, like the items of an order. Uniqueness is per shop too: the same email can register at two different shops, and every shop's order numbers start from 1.

CREATE UNIQUE INDEX IF NOT EXISTS users_shop_email_uq ON users (shop_id, lower(email)) WHERE shop_id IS NOT NULL;

-- on the orders table
UNIQUE (shop_id, number)

Order numbers come from a counter on the shop's own row. One UPDATE both increments it and returns the number, so two orders placed at the same moment can't get the same one.

UPDATE shops SET next_order_number = next_order_number + 1 WHERE id = $1 RETURNING next_order_number - 1 AS number

2. The shop comes from the database, not from the request#

A signed-in request carries a token, but the API doesn't take the token's word for which shop the user belongs to. On every request the middleware loads the user's row again and reads the shop from it. There is no shop header and no shop ID in the URL for a client to change.

const { rows } = await query(
  `SELECT u.*, s.is_active AS shop_active
     FROM users u LEFT JOIN shops s ON s.id = u.shop_id
    WHERE u.id = $1`,
  [payload.sub],
);
const user = rows[0];
if (!user || user.deleted_at) throw unauthorized("accountGone");
// ...
req.user = user;

A useful side effect: removing a staff member takes effect on their next request, with no wait for a token to expire. The one exception is an owner who runs several shops. They can switch between them, and the token's shop is honoured only after checking a table of the shops that owner was granted.

3. Every query says whose data it wants#

There is no ORM and no hidden filter. Queries are plain SQL and each one names the shop: req.user.shop_id appears more than 450 times in the backend. Reading or changing something by ID always puts the shop in the WHERE as well.

adminRouter.delete("/categories/:id", can("menu"), async (req, res) => {
  const { rows: [gone] } = await query(`DELETE FROM categories WHERE id = $1 AND shop_id = $2 RETURNING *`, [idParam(req), req.user.shop_id]);
  if (!gone) throw notFound("category");
  await audit(req, { action: "category.delete", entity: "category", entityId: gone.id, label: gone.name });
  res.json({ data: { ok: true } });
});

If the row belongs to another shop, the answer is 404, the same as for an ID that doesn't exist.

The API never confirms that another shop's record is there. IDs inside a request body get the same treatment: a product can't be filed under another shop's category, and a cart that contains another shop's product is refused by counting the rows that came back.

const { rows } = await db.query(
  `SELECT id, name, name_ar, price, options, bundle_items, category_id, kind, shippable, retail_stock FROM products WHERE shop_id = $1 AND is_available AND id = ANY($2)`,
  [shopId, ids],
);
if (rows.length !== ids.length) throw badRequest("itemsUnavailable");

4. Tests that try to cross the line#

A convention holds until the day someone forgets it. So the tests behave like a nosy shop owner: they sign in as one shop and go after another shop's products.

test("a shop cannot touch another shop's data", async () => {
  const other = (await api().get(`/api/shops/${nileBeans.id}/menu`)).body.data.categories[0].products[0];
  const res = await api().put(`/api/admin/products/${other.id}`).set(auth(owner.token)).send({ price: 1 });
  assert.equal(res.status, 404);
  const cat = await api().post("/api/admin/products").set(auth(owner.token)).send({ name: "X", price: 1, categoryId: other.categoryId });
  assert.equal(cat.status, 400);
});

What this design does not have is a safety net inside the database. PostgreSQL's row-level security would refuse a query that forgot its shop filter; here, a forgotten filter is a bug that only a test or a review catches. That is the price of SQL that stays plain and readable, and row-level security is the next step if more people start writing queries.

What I'd tell anyone building this#

• Put the shop on every row, and in every unique constraint.
• Read the tenant from the database on each request, never from what the client sends.
• Answer 404 for another tenant's data, not 403.
• Write tests that attack your own isolation, and be honest about what the database doesn't enforce.