Multilocation Database Ownership

Status

Model

A resource has four separate facts.

Why business_id and not entity_id

We audited 68 tables before choosing. The existing entity_id could not be reused as the owner.

• Authorization reads a row's entity_id as the location in 17 lookups. Forms and offers reject any other entity type.
• About 274 query sites filter these tables by a location id. Clients alone has about 110. A missed reader returns an empty list, not an error.
• Seven unique indexes are scoped to entity_id. Two locations of one business holding the same add-on source id, product SKU, tag name or phrase would collide.
• entity_id is NOT NULL. It cannot represent an unresolved owner.
• Sales and other events keep the location in entity_id in any design. One name would then mean business on a coupon and location on its usage row.

vector.brain_memory_quality_scores already pairs entity_id with business_id this way.

Tables

Owner column and mapping table

The 11 new mapping tables share one shape: id, the resource id, location_entity_id, is_active, audit columns, and a unique constraint on (resource id, location).

Owner column only

Not changed

Every other table keeps its columns. The full audit below gives the verdict for each group.

• Child and event rows take the owner from their parent: variations, line items, payments, refunds, coupon and offer usages, form submissions, stock on hand, stock movements, client notes and contacts.
• Per-location settings stay per location: gift card settings, commission settings, tax rates, booking rules and policies.
• scheduling.form_templates has no merchant rows today. Its entity_id will mean the business when the first merchant writer ships.
• Three tables already hold the business in entity_id: scheduling.onboarding, scheduling.platform_migrations and staff.invites. They pair it with a location column and are left as they are.

Owner resolver

EntityOwnershipService.resolveOwningBusinessId(entityId) in libs/entity decides the owner for every writer. It runs one query and never throws.

A failed lookup is logged and stored as no owner. It never blocks a create.

Writers

Each writer resolves the owner once before its transaction, stores it on the new row, and inserts the mapping row with the same location the row is created for.

Stored without an owner by design

• Categories created without an entity: the mobile POST /services/category, the batch endpoint, and GBP sync.
• Everything a booking playground creates.
• Global Zoca gift card designs and system note labels.
• Test fixture seeds in scripts/.

Mobile compatibility

Mobile routes are tagged App. Their responses must not gain a key. A new column leaks wherever a whole row is returned, so these reads now select explicit columns or drop the key.

Remark. Web responses for resources, resource groups, coupons, auto-coupon configs, gift card designs, subscriptions and the staff service-provider toggle now include businessId. No mobile route returns them.

Full table audit

The first audit covered the tables behind the Your business sidebar. A second pass classified every table. The database has 818 tables plus 170 log twins. 311 carry an entity or location column.

The audit read code, not data. Each verdict comes from what the writers store and what the readers filter on. Seven tables whose names end in log were outside the 818; none has an owner column.

Candidates

Confirmed with no change

• Front desk. Every configuration row is per location. Calls, callbacks and front desk bookings are events. Business-wide inbox, enquiries and call reports already read through the location column.
• Content queue. Location-only by decision. Every content table is keyed to a location; the business calendar is a view.
• Media. An asset has no owner. media_entities maps it to locations, and the business gallery reads through that.
• Reviews. One Google listing, one location.
• Roles and permissions. Already business-scoped with a per-location lane.
• Brain. Memories accept a business or a location. Parked.
• Campaign broadcasts and requests. Sends to one location's clients, not definitions.

Business defaults, not owner columns

These are per-location settings where "set once for the business" would be a fallback step in a resolver.

• entities.preferences: resolves location, then cohort, then default. No business step.
• Booking rules and policies: one row per location in practice. A business mapping row is allowed but unused.
• Booking message templates, automated lead messages, follow-up rules: location, then global.
• Gift card settings, commission settings, review-invite templates, FAQs, front desk agent context.

Design issues the audit found

1. Service tax. service_tax_mappings has no location. A service shared by two locations shares one taxable flag and one tax item, written by whichever location saved last.
2. Staff hours. Working hours are keyed by staff member with no location. A member at two locations has one weekly schedule for both.
3. Wallets and saved cards. A wallet follows the client row. Stripe customers are one per client row, so one person at two locations has two customers on the same account.
4. Tip presets. Stored per location, pushed to the account's single Terminal configuration. Sibling locations overwrite each other.
5. Billing customer. Code creates one Chargebee customer per location. The intended model is one per business. The mapping table can already hold a business row; the resolver cannot.
6. Unused override table. services.location_variation_overrides has no reader or writer in the application.

Backfill

scripts/ops/backfill-business-ownership.sh fills existing rows for one or more locations. Each location runs in its own transaction.

# Dry run: prints the per-table report, writes nothing.
scripts/ops/backfill-business-ownership.sh <location-uuid>...
# Write the changes.
scripts/ops/backfill-business-ownership.sh --apply <location-uuid>...
# Leave sales, purchased packages, subscriptions and gift cards untouched.
scripts/ops/backfill-business-ownership.sh --apply --definitions-only <location-uuid>...

Events are included by default. That stamps a location's past sales with its current business. Use --definitions-only for a location that has changed business.

Preview VM result, 2026-10-05. Bloom & Co and Mirror & Muse: 61 clients, 123 sales, 14 staff, 28 services and 6 categories filled; 61 client location rows written; no conflicts. A second run filled nothing. A business id and an unknown id were both refused.

Verification

Behaviour changes

• Creating a bundle, add-on, add-on group, coupon, product or form now runs in a transaction with its mapping row.
• A product whose mapping insert fails is rolled back, so no product exists without a location row.
• The client_locations insert uses Knex. Client creation already runs in a Knex transaction, and a Drizzle statement cannot join it.
• Archiving or soft-deleting a resource leaves its mapping rows active. The resource's own flag hides it, so a reader must check both.
• Hard-deleting a product removes its location rows in the same transaction. The VM test found they were being left behind.

Remaining work

Decisions made

Open decisions

1. Historical sales when a location changes business. Sales, purchased packages, subscriptions and gift cards created before this stay unowned until we decide which business keeps them.
2. Rows with no resolvable owner. Local data: 803 of 2,898 services have one clean owner candidate. The current rule leaves the rest null and out of business-wide lists.
3. Offers relinked to another business. An offer later attached to a location of a different business keeps its first owner.
4. Forms contract. The scheduling forms pack says not to add a per-location association table. form_locations now exists. The pack and an ADR need updating.
5. Inventory invariant. The inventory pack locks location to equal entity. Shared products need the v2 ADR before any reader uses product_locations.
6. Audit candidates. Whether loyalty journeys, inbox text templates and time-off types are defined once per business, and whether onboarding gets an owner column.
7. Location changes business. What re-stamps business_id, and whether roles, contracts and the Stripe account move with the location.
8. Service tax for a shared service. One tax setting per service, or one per location.
9. Staff hours across locations. One weekly schedule per member, or one per location.
10. Business defaults. Whether preferences, booking rules and message templates get a business step before the global default.
11. Billing customer. One Chargebee customer per business needs a change to the customer resolver, not a column.

Risks

• The old entity_id stays on every table. A reader that still filters on it keeps working and will under-report a record shared to a second location. Nothing forces that reader to be fixed.
• staff.members.entity_id holds a location on most rows and a business on some. No new code should depend on it. business_id is the owner.
• PR #23863 conflicts with develop and is 21 commits behind.