One database per restaurant.

Laayo Biz started as a point of sale system for one restaurant: one Laravel app, one database. When I turned it into a product other restaurants could sign up for, the first decision was how to keep their data apart. I chose a database per tenant.

The usual alternative is a tenant id column on every table. It works and it is cheaper to run. But then every query, report, export and background job has to remember the filter, forever, and the POS code I was starting from was written for a single restaurant from top to bottom. With a database per tenant, that code stays correct as it is, and a forgotten WHERE clause can't read another restaurant's sales. The isolation comes from the structure, not from everyone being careful.

Two planes

The control plane lives in the main Laayo admin. It holds the registry of tenants, plans, subscriptions and sign-up requests, and it creates new tenants.

The data plane is one shared copy of the POS app. Its default database connection is called tenant, and on every request it is pointed at that tenant's database. The registry is read through a separate read-only connection.

Choosing the tenant is a middleware. The web panel reads a business code from the session. The mobile POS app sends it in a header on every request. The middleware looks the code up in the registry, checks that the tenant is active and its subscription is not past the grace period, sets the tenant connection's database, purges the old connection and reconnects. Then the request carries on.

That ordering bit me. Laravel runs authentication middleware ahead of ordinary route middleware. The API guard checks its token against the tenant database, so it ran before any tenant database was selected, and every authenticated API call failed with "No database selected". Login still worked, because login doesn't go through the guard, which made it confusing for a while. The fix was to put the tenant middleware into the priority list right before authentication, and still after the session starts, so the web version can read the business code from the session.

Staff on the mobile app sign in with a PIN and get a different kind of token: an opaque value in the cache that records which tenant it belongs to. Their requests don't need to send the business code at all, because the token already knows.

Provisioning, and keeping schemas in step

Creating a tenant takes four steps:

1. Create the database with a dedicated MySQL user that can only create and use databases matching the tenant prefix.
2. Import a template schema.
3. Seed the owner account, an API client and the business settings: Nepal time zone, rupees, and Bikram Sambat dates.
4. Run the app's migrations against the new database.

Step four was added after the template went stale. The template was a schema dump from an earlier point, and new tenants were born without columns and tables that newer code expected. Pages failed on day one. Provisioning now runs the migrations right after the import, so the template only has to be roughly current.

Existing tenants are migrated by a command that walks the registry and migrates each tenant database in turn. It is idempotent, it can target one tenant, and it has a dry run. I wrote one rule down after a restaurant fell several migrations behind and broke on new code: after adding a migration, run it for every tenant, not only the one you tested on.

What a database per tenant doesn't isolate

The database is separate. Anything else the app process shares is not, unless you make it so. The cache is the obvious one. With a single cache prefix, two tenants can end up sharing a key, for example a payment provider session keyed by a username that might be blank. Choosing a tenant now also sets a per-tenant cache prefix and a per-tenant file cache path, in the same place that sets the database. Anything shared needs the tenant in its key, and a database per tenant doesn't do that for you.

Writes between the planes went the other way from what I expected. The control plane takes subscription payments, and I wanted each restaurant to see its own billing history inside its POS. Reading the control plane's tables from the tenant side would have meant giving the tenant user more access to the registry database. The payment hooks in the control plane already write to tenant databases, so they record each payment attempt in the tenant's own table, and the POS only reads its own data.

Bills that never skip a number

Nepali tax rules want bill numbers without gaps within a fiscal year, and the fiscal year starts in Shrawan, the fourth month of the Bikram Sambat calendar. Inside each tenant there is a counter row per fiscal year and bill type, locked for the length of the order transaction:

START TRANSACTION;
 
SELECT last_no FROM bill_sequences
 WHERE fiscal_year = '2082/83' AND bill_type = 'invoice'
 FOR UPDATE;
 
UPDATE bill_sequences
   SET last_no = last_no + 1
 WHERE fiscal_year = '2082/83' AND bill_type = 'invoice';
 
INSERT INTO orders (bill_no, bill_fiscal_year, bill_type)
VALUES (:next_no, '2082/83', 'invoice');
 
COMMIT;

START TRANSACTION;
 
SELECT last_no FROM bill_sequences
 WHERE fiscal_year = '2082/83' AND bill_type = 'invoice'
 FOR UPDATE;
 
UPDATE bill_sequences
   SET last_no = last_no + 1
 WHERE fiscal_year = '2082/83' AND bill_type = 'invoice';
 
INSERT INTO orders (bill_no, bill_fiscal_year, bill_type)
VALUES (:next_no, '2082/83', 'invoice');
 
COMMIT;

If the order insert fails, the counter rolls back with it, so a failed sale can't burn a number. A unique key on fiscal year, bill type and number is the last line of defence if two writers ever slip past the lock. Voided bills keep their number and get a reason, and every create, void and refund writes a row to an append-only audit log.

The same order transaction locks the product rows it decrements, so two tills selling the last portion of a dish at the same moment can't both succeed.