Skip to content

Generated SaaS Data Model

Target Architecture — Final-State Design

This page describes the logical data model of the common SaaS spine. Persistence is via NHibernate to Azure SQL or PostgreSQL, generated into each service's PersistenceModel.NHibernate and DatabaseModel.Migrations projects. Each bounded context owns its own schema/database; the model below is logical and crosses contexts for clarity, but no context reads another's tables directly.

The generated data model is database-per-service at the physical level and tenant-isolated at the row or schema level. The diagram below shows the core spine entities and their relationships; product-specific domain entities extend this baseline within their own services.

Logical model

  • Tenant is the root of the isolation hierarchy. Every other entity carries a TenantId foreign key (logical; physically enforced by discriminator).
  • User belongs to a tenant and is linked to Role through a many-to-many RoleAssignment.
  • Role is linked to Permission through a many-to-many RolePermission.
  • Subscription belongs to a tenant and references an Edition.
  • Edition and Permission are platform-level catalogs (seeded from the blueprint), shared across tenants but never tenant-writable.

Entity-relationship diagram

erDiagram
    TENANT ||--o{ USER : "has"
    TENANT ||--o{ SUBSCRIPTION : "has"
    TENANT ||--o{ ROLE : "defines"
    USER }o--o{ ROLE : "assigned via RoleAssignment"
    ROLE }o--o{ PERMISSION : "granted via RolePermission"
    SUBSCRIPTION }o--|| EDITION : "on"

    TENANT {
        string TenantId PK
        string Name
        string Slug UK
        string Status
        string EditionCode
        datetime CreatedAt
    }
    USER {
        string UserId PK
        string TenantId FK
        string Email
        string DisplayName
        string Status
        datetime CreatedAt
    }
    ROLE {
        string RoleId PK
        string TenantId FK
        string Code
        string Name
        bool IsSystem
    }
    PERMISSION {
        string PermissionId PK
        string Code UK
        string Name
        string Category
        bool IsSystem
    }
    SUBSCRIPTION {
        string SubscriptionId PK
        string TenantId FK
        string EditionCode FK
        string Status
        string BillingCycle
        datetime CurrentPeriodEnd
    }
    EDITION {
        string EditionId PK
        string Code UK
        string Name
        string Status
        decimal Price
    }
Hold "Alt" / "Option" to enable pan & zoom

Multi-tenancy strategy

Generated products use a tenant discriminator model by default, configurable per product to schema- or database-per-tenant for high-isolation requirements.

Strategy When generated Mechanism
Shared database, tenant discriminator (default) Standard products Every tenant-owned table has a TenantId column; an NHibernate global filter (ConnectSoft.Extensions.Saas integration) injects WHERE TenantId = :currentTenant on every query.
Schema-per-tenant Compliance/isolation needs Each tenant gets a dedicated schema; the data-access layer resolves the schema from the ambient tenantId.
Database-per-tenant Strict isolation / large tenants A connection-string resolver maps tenantId to a dedicated database.

Implementation Notes

The discriminator filter is enforced at the data-access layer (NHibernate global filter) and asserted in each handler against the envelope tenantId, giving defense in depth. The ambient tenantId is established from the validated token claim at the API Gateway and flows through the call chain and into every published event. Platform-level catalogs (Permission, Edition) are exempt from the tenant filter because they are read-only across tenants.

Retention

Data Retention Mechanism
Operational domain data Lifetime of the tenant Deleted/anonymized on TenantDecommissioned
Audit entries Long-term (e.g. 7 years, product-configurable) Append-only table + Blob archive export
Notifications 90 days hot, then archived Worker-driven archival to Blob
Report runs / artifacts Configurable (e.g. 1 year) Lifecycle policy on Blob container
Feature flag / config history Retained as audit entries Captured via ConfigurationChanged / audit
Soft-deleted records Grace period then purged DeletedAt marker + purge worker

Retention windows are product-configurable through ConfigurationSetting and enforced by scheduled workers and Blob lifecycle policies.

How the data model contributes to the pillars

  • Traceability — TenantId and event-sourced audit link every row to a tenant and to the actions that changed it.
  • Reusability — the spine schema is generated identically across products from the ConnectSoft.Saas.* templates.
  • Autonomy — schema and migrations are generated from the blueprint domain model by the Backend Developer Agent.
  • Governance — retention, soft-delete, and append-only audit encode compliance directly in the model.
  • Observability — query-level metrics are tagged by tenant; the discriminator makes per-tenant data volume measurable.
  • Multi-tenant scale — the discriminator strategy scales to many tenants per database, with schema/database-per-tenant available for the largest or most regulated tenants.