Skip to content

0003. Central vs per-tenant schema boundary

  • Status: Accepted (retroactive)
  • Date: 2026-06-09
  • Deciders: POS Integration team

Context

The service is multi-tenant, and a single tenant can have multiple providers connected at once (e.g. Deliverect for some outlets, Revel for others). Some data is identical for everyone (a provider's transform logic, the provider catalog), while other data is tenant-private (credentials, which outlet maps to which POS location). We needed a clear rule for what lives where so that the right thing is shared and the wrong thing never leaks across tenants.

Decision

We split data along a central vs per-tenant boundary:

Central (centraldb — shared, one copy for all tenants)

Table Holds
central.providers POS provider definitions (slug, display_name, auth_type, status).
central.adapter_scripts The JS transform scripts (ADR 0001) — one per (provider, topic).
central.knowledge_bases GitHub repos of POS API docs that feed AI generation (ADR 0004).
central.adapter_generation_jobs AI generation pipeline run tracking (ADR 0005).

Per-tenant (tenantdb)

Table Holds
tenantdb.provider_connections This tenant's credentials for a provider (unique on provider_slug). Required for any tenant-wide sync.
tenantdb.outlet_providers Which outlet is served by which provider, the external_id, and the per-outlet settings JSON (unique on store_id). Required before any per-outlet sync.

Guards (internal/service/provider.go): tenant-wide calls (POST /products/sync, POST /outlets/sync) check provider_connections; per-outlet calls (POST /outlets/{id}/menu) check outlet_providers. The Cata provider is always allowed for the tenant-wide guard (it has no provider_connections row by design). The legacy settings.nameOf3rdPartyKDS single-provider switch is ignored by this service — it belongs to kds-management-service.

Secrets never reach the JS sandbox: scripts read non-secret config from outlet_providers.settings; HMAC/credential material is resolved in Go.

Consequences

Positive

  • Transform logic and provider definitions are written once and shared — no per-tenant copies to keep in sync.
  • A tenant's credentials and outlet mappings are isolated by construction.
  • Multiple providers per tenant are a first-class case, gated by explicit guards.

Negative / costs

  • Onboarding a provider on a tenant requires a bootstrap order: connect credentials (provider_connections) → map outlets (outlet_providers) → then sync. Calls that arrive early are rejected with provider mismatch — correct, but a known onboarding gotcha.
  • Cross-schema operations need care. Known hazard: SoftDeleteOrphanItems does not filter by provider, so a full-replace sync_products with deleteOrphans=true on a multi-provider tenant could delete another provider's items. The adapter path passes deleteOrphans=false; scope the delete by provider before relying on full-replace.

Alternatives considered

  • Everything per-tenant. Simplest isolation, but duplicates identical transform scripts and provider definitions across every tenant and makes a global fix an N-tenant migration. Rejected.
  • Everything central with a tenant column. One schema, but mixes tenant secrets into shared tables and makes isolation a query-discipline problem rather than a structural guarantee. Rejected.

References