Spensara Spensara Knowledge Base & Documentation Repository
System Overview Brink & Borg Master Index (722 Docs) Spensara Platform Project Tracker

Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)
Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Categories

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.
branches/tenat-admin/architecture/docs/features/tenant-admin-control-plane/data-model.md 11,311 bytes · Updated Oct 23, 2026 10:44 AM EDT
Raw File

Tenant Control Plane Data Model

Purpose

This document proposes the PoC data model and migration direction for the tenant control-plane described in:

  • feature-proposal.md
  • api-contract.md

Scope:

  • Clients
  • Tenants
  • tenant systems storage
  • relation to existing TenantWarehouseMappings
  • migration direction from the current model

This is a design document only. No implementation details or code are included.

Naming Conventions Alignment

The proposed model should follow the naming style already used in backend/Kds.Ai.Data.

Current repository conventions:

  • entity class names are singular PascalCase, for example MonitorSpec, TenantMonitorSpec, TenantWarehouseMapping
  • DbSet names and table names are plural PascalCase, for example MonitorSpecs, TenantMonitorSpecs, TenantWarehouseMappings
  • CLR property names are PascalCase
  • database schema name is backend, but table names themselves do not include the schema prefix in model naming

Recommended application of those conventions for this feature:

  • entity: Client, table: Clients
  • entity: Tenant, table: Tenants
  • entity: TenantSystem, table: TenantSystems

Important alignment note:

  • the existing warehouse model already uses the name TenantDatabase in TenantWarehouseMapping and warehouse services
  • to stay consistent with backend/Kds.Ai.Data and backend/Kds.Ai.Warehouse, the persistence model should prefer TenantDatabase
  • API contracts may still expose databaseName as response/request field naming, with mapping done at the API boundary

Design Goals

  1. Keep tenant as the technical term already used in backend code.
  2. Make tenant represent a restaurant/store.
  3. Introduce client as the customer organization.
  4. Keep one Redshift database per tenant.
  5. Keep bootstrap as a separate workflow.
  6. Favor flexible system configuration over rigid source classification.
  7. Avoid over-modeling integrations in the PoC.

Responsibility Split

This data model is shared by both API surfaces.

For the PoC:

  • admin manages Clients
  • client manages its own Tenants
  • client bootstrap operates only on client-owned tenants

The storage model does not change between surfaces. Only request scoping changes.

Proposed Tables

1. Clients

Entity: Client

Table: Clients

Columns

  • ClientId - uuid, primary key
  • Name - varchar(...), required
  • Code - varchar(...), required, unique, immutable
  • IsActive - boolean, required, default true
  • CreatedAt - timestamp with time zone, required
  • UpdatedAt - timestamp with time zone, required

Constraints

  • primary key on ClientId
  • unique index on Code

Rules

  • Code is generated from Name
  • Code is immutable after insert
  • Name is editable

2. Tenants

Entity: Tenant

Table: Tenants

Columns

  • TenantId - uuid, primary key
  • ClientId - uuid, required, foreign key to Clients
  • Name - varchar(...), required
  • Code - varchar(...), required, unique, immutable
  • TenantDatabase - varchar(...), required, unique, immutable
  • Status - varchar(...) or enum-backed string, required
  • IsActive - boolean, required, default false
  • CreatedAt - timestamp with time zone, required
  • UpdatedAt - timestamp with time zone, required

Constraints

  • primary key on TenantId
  • foreign key ClientId -> Clients.ClientId
  • unique index on Code
  • unique index on TenantDatabase
  • unique index on (ClientId, Name)

Rules

  • Name must be unique within a client
  • Code is generated from Name
  • Code is globally unique across the whole system
  • Code is immutable after insert
  • TenantDatabase is derived from Code
  • TenantDatabase must comply with Redshift naming rules
  • TenantDatabase is immutable after insert
  • Name is editable
  • editing Name does not recalculate Code
  • editing Name does not recalculate TenantDatabase

Tenant status

For the PoC:

  • PendingBootstrap
  • Active
  • BootstrapFailed

Default state after creation:

  • Status = PendingBootstrap
  • IsActive = false

3. Tenant systems

There are two viable storage options.

For the PoC, the recommended option is a separate child table.

Reason:

  • API shape stays flexible as systems: string[]
  • relational storage stays normalized
  • deduplication is easier
  • querying and validation are simpler than with a JSON blob

Recommended option: TenantSystems

Entity: TenantSystem

Table: TenantSystems

Columns

  • TenantId - uuid, required, foreign key to Tenants
  • SystemName - varchar(...), required
  • NormalizedSystemName - varchar(...), required
  • CreatedAt - timestamp with time zone, required

Primary key options

Recommended:

  • composite primary key: (TenantId, NormalizedSystemName)

This prevents duplicates like:

  • Brink
  • brink
  • BRINK

Rules

  • SystemName stores the display value or canonical input value
  • NormalizedSystemName stores the normalized comparison key
  • normalization should be case-insensitive
  • trim whitespace before normalization
  • duplicate systems for the same tenant are not allowed
  • order is not significant
  • tenants may exist with zero systems before bootstrap
  • bootstrap must be blocked when a tenant has zero systems

Normalization recommendation

Recommended normalization for NormalizedSystemName:

  • trim leading/trailing whitespace
  • collapse repeated internal spaces if desired
  • convert to lowercase invariant

Examples:

  • Brink -> brink
  • brink -> brink
  • Logic Controls -> logic controls

Important note:

  • do not over-normalize into a slug unless it is clearly needed
  • for the PoC, simple trimmed lowercase matching is enough

Why separate table is better than JSON for the PoC

  • easier uniqueness enforcement
  • easier querying by tenant/system
  • easier future migration into richer integration records
  • cleaner relational design

4. Existing TenantWarehouseMappings

Entity: TenantWarehouseMapping

Table: TenantWarehouseMappings

Current role in code:

  • technical mapping from tenantId to Redshift database
  • activation flag for warehouse runtime

Recommended future role:

  • infrastructure/runtime support table only
  • not the primary business table for tenant administration
  • TenantId - uuid, primary key, foreign key to Tenants
  • TenantDatabase - varchar(...), unique
  • IsActive - boolean
  • CreatedAt
  • UpdatedAt

Optional future columns if operationally useful:

  • LastBootstrapAttemptAt
  • LastBootstrapError

Important alignment change

Today this table still uses string TenantId in warehouse-related code.

The target model should align it with the rest of the application:

  • change TenantId from string to uuid

Client -> Tenants

  • one client has many tenants
  • one tenant belongs to exactly one client

Tenant -> TenantSystems

  • one tenant has zero or many systems
  • one system entry belongs to exactly one tenant

Tenant -> TenantWarehouseMappings

  • one tenant has exactly one warehouse mapping
  • warehouse mapping belongs to exactly one tenant

Suggested Entity Boundaries

From a domain perspective:

  • Client is the customer organization aggregate root
  • Tenant is the restaurant/store aggregate root for operational onboarding
  • TenantSystem is a child collection of Tenant
  • TenantWarehouseMapping is an infrastructure projection/support record

This keeps the business write model centered on Tenant, not on the warehouse mapping.

API To Storage Mapping

Create client

POST /admin/clients

Writes:

  • one row in Clients

Create tenant

POST /client/tenants

Writes:

  • one row in Tenants
  • zero or more rows in TenantSystems
  • one row in TenantWarehouseMappings

Recommended initial warehouse mapping values:

  • TenantId = new tenant id
  • TenantDatabase = Tenant.TenantDatabase
  • IsActive = false

This keeps the current bootstrap flow compatible with existing warehouse resolution logic.

Bootstrap tenant

POST /client/tenants/{tenantId}/bootstrap

Reads:

  • Tenants
  • TenantSystems
  • TenantWarehouseMappings

Precondition:

  • at least one TenantSystems row must exist for the tenant

Writes on success:

  • Tenants.Status = Active
  • Tenants.IsActive = true
  • Tenants.UpdatedAt = now
  • TenantWarehouseMappings.IsActive = true
  • TenantWarehouseMappings.UpdatedAt = now

Writes on failure:

  • Tenants.Status = BootstrapFailed
  • Tenants.IsActive = false
  • Tenants.UpdatedAt = now

Migration Plan

Phase 1. Add new business tables

Add:

  • Clients
  • Tenants
  • TenantSystems

Keep existing warehouse tables and flows working.

Phase 2. Align warehouse mapping with new tenant model

Update TenantWarehouseMappings:

  • change TenantId to uuid
  • connect it to Tenants
  • keep TenantDatabase unique

Phase 3. Update backend service assumptions

Align warehouse-related services and models that still use string tenant ids:

  • repository interfaces
  • bootstrap request/response models
  • tenant warehouse descriptor models
  • runtime resolution logic
  • ingestion metadata if those records still store string tenant ids

Phase 4. Move admin reads/writes to business tables

API surfaces should read and write through:

  • Clients
  • Tenants
  • TenantSystems

with TenantWarehouseMappings used only as a technical support table.

Index Recommendations

Clients

  • unique index on Code

Tenants

  • unique index on Code
  • unique index on TenantDatabase
  • unique index on (ClientId, Name)
  • index on ClientId
  • index on Status
  • index on IsActive

TenantSystems

  • primary key or unique index on (TenantId, NormalizedSystemName)
  • index on NormalizedSystemName if searching by system across tenants will be needed soon

TenantWarehouseMappings

  • unique index on TenantDatabase
  • index on IsActive

Integrity Rules Summary

  1. client code is unique and immutable
  2. tenant code is unique and immutable
  3. tenant database name is unique and immutable
  4. tenant name is unique within a client
  5. tenant systems are deduplicated per tenant using normalized comparison
  6. one tenant maps to one warehouse database
  7. warehouse mapping cannot exist without a tenant

Why this model is a good fit

This model fits the current product direction because:

  • it preserves the existing technical tenant concept
  • it introduces the missing business layer client
  • it matches the ETL architecture of one database per tenant
  • it supports admin-managed clients and client-managed tenants without changing persistence shape
  • it keeps bootstrap independent from tenant creation
  • it allows very flexible tenant system combinations
  • it does not force premature classification of source systems

Minimal PoC Recommendation

If the team wants the smallest correct relational model, use exactly:

  1. Clients
  2. Tenants
  3. TenantSystems
  4. aligned TenantWarehouseMappings

Do not add yet:

  • user access tables
  • group access tables
  • integration secret tables
  • typed integration configuration tables
  • advanced provisioning audit tables

Those can be added later without invalidating this model.