Skip to content

Control Plane — Storage

The Control Plane follows a strict database-per-service model: each microservice owns its data and exposes it only through its API and published events. No service reads another service's database. Relational state lives in Azure SQL or PostgreSQL (NHibernate via ConnectSoft.Extensions.PersistenceModel.NHibernate with ConnectSoft.Extensions.Saas.NHibernate tenant filters); Redis provides caching and short-lived coordination; Azure Service Bus is the durable message backbone; Azure Key Vault holds secrets.

Target Architecture — Final-State Design

Store choice per service balances transactional consistency (Azure SQL for identity, tenancy, workflow saga state) against graph/time-series/append-only workloads (PostgreSQL for catalogue, dependency graph, audit, usage, lineage). Every row carries tenantId as part of its index for isolation.

Storage Technologies

Technology Role Used by
Azure SQL Transactional aggregates, saga state, identity. Identity, Tenancy, Blueprint, Workflow saga, Agent, Governance.
PostgreSQL Catalogue, dependency graph, append-only audit, time-series usage, lineage graph, projections. Module catalogue, Dependency, Audit, Cost & Usage, Lineage, ProcessState.
Redis Cache, quota counters, feature-flag eval, agent-pool leases. FeatureFlag, Quota, AgentPoolManager, ProcessState, ApiToken.
Azure Service Bus Durable events/commands (canonical envelope), saga messaging, dead-letter. All services (MassTransit).
Azure Key Vault Secrets: connection strings, integration credentials, signing keys. All services; Integration connections reference secrets by configRef.
Azure Blob Storage Large artifact bodies and audit exports. ArtifactService, AuditService (via AuditExportWorker).

Data Inventory

Data Store Owner Service Access Pattern Retention Notes
User/principal identity, clients Azure SQL IdentityService Read-heavy on auth; write on registration Lifetime of tenant OpenIddict schema (ConnectSoft.IdentityTemplate/AuthorizationServerTemplate).
Roles, permissions, scopes Azure SQL AuthorizationService Read-heavy (per request) Lifetime of tenant Cached in Redis.
Service identities, M2M credentials Azure SQL ServiceIdentityService Read on token issue Lifetime; rotate Secrets in Key Vault.
API tokens Azure SQL + Redis ApiTokenService Read-heavy validation Until expiry/revocation Hashed at rest; Redis caches validation.
Tenants Azure SQL TenantService Read-heavy; low write Lifetime + legal hold Slug uniquely indexed; isolation root.
Editions Azure SQL EditionService Read-heavy Lifetime; versioned Published editions immutable.
Subscriptions Azure SQL SubscriptionService Mixed Lifetime + 7y billing Drives quota & billing.
Feature flags Azure SQL + Redis FeatureFlagService Very read-heavy (eval) Lifetime Redis-cached evaluation.
Quota definitions & balances Azure SQL + Redis QuotaService High-frequency read/incr Current period + history Redis counters; SQL system of record.
Projects Azure SQL ProjectService Mixed Lifetime + archive Slug unique per tenant.
Environments Azure SQL EnvironmentService Read-heavy Lifetime of project prod gated by approvals.
Module catalogue PostgreSQL ModuleCatalogService Read-heavy; catalogue queries Lifetime of project JSONB metadata.
Module dependency graph PostgreSQL DependencyService Graph traversal Lifetime of project Cycle detection; recursive queries.
Blueprints & versions Azure SQL BlueprintService Mixed; versioned read Lifetime + audit Validated versions immutable.
Blueprint validation results PostgreSQL BlueprintValidatorService Write on validate; read 1y Projected from validation runs.
Context maps PostgreSQL ContextMapService Read on orchestration Lifetime of project Derived from blueprints.
Domain models PostgreSQL DomainModelService Read on orchestration Lifetime of project Derived from blueprints.
Workflow definitions Azure SQL WorkflowDefinitionService Read-heavy Lifetime; versioned Seeded from platform registry.
Workflow instance saga + event store Azure SQL WorkflowOrchestrator Append-only writes; saga reads Lifetime + replay window Event-sourced; enables replay.
Agent tasks Azure SQL TaskAssignmentService Mixed Lifetime of workflow + audit Idempotent assignment.
Process-state projection PostgreSQL + Redis ProcessStateService Very read-heavy Rebuildable from events Read model; Redis hot cache.
Replay shadow streams PostgreSQL WorkflowReplayService Append on replay 90d Never mutates source events.
Agent definitions Azure SQL AgentRegistryService Read-heavy Lifetime; versioned —
Skill definitions Azure SQL SkillRegistryService Read-heavy Lifetime; versioned —
Agent pool leases Redis + Azure SQL AgentPoolManager High-frequency read/write Ephemeral (leases) Leases in Redis; pool config in SQL.
Model policies Azure SQL ModelPolicyService Read on task assignment Lifetime; versioned Constrains model usage.
Policy definitions & decisions Azure SQL PolicyEngineService Read-heavy eval; append decisions Decisions append-only, 7y Decisions immutable.
Approval requests Azure SQL ApprovalService Mixed Lifetime + 7y Human gate state.
Audit entries PostgreSQL (append-only) AuditService Append; range queries 7y+ (compliance) Hash-chained; exported to Blob/SIEM.
Usage records & rollups PostgreSQL (time-series) CostUsageService High-volume append; aggregate Raw 90d, rollups 7y Time-partitioned.
Integration connections Azure SQL IntegrationService Read-heavy Lifetime Secrets in Key Vault (configRef).
Artifacts & metadata PostgreSQL + Blob ArtifactService Mixed; body in Blob Lifetime + retention policy Shares metadata with Knowledge Platform.
Artifact versions PostgreSQL VersioningService Read on resolve Lifetime Version chains.
Lineage edges PostgreSQL (graph) LineageService Graph traversal Lifetime Task→artifact→deployment lineage.
All events/commands Azure Service Bus (transport) Publish/subscribe; dead-letter Per topic TTL + DLQ Canonical envelope.
Secrets & credentials Azure Key Vault (all) Read on startup/rotation Managed; rotated Referenced, never inlined.

Multi-Tenant Data Isolation

  • Tenant column + filter: every aggregate carries tenantId; ConnectSoft.Extensions.Saas.NHibernate applies a global tenant filter so queries cannot cross tenants by accident.
  • Index discipline: tenantId is the leading column of tenant-scoped indexes; correlation columns (traceId, correlationId, projectId, moduleId) are indexed where cross-entity queries run (per the Metadata Schema).
  • Isolation models: editions may map to shared or dedicated database isolation (Tenant.IsolationModel); high-tier tenants can be provisioned to dedicated databases per service.
  • Encryption: data at rest is encrypted (TDE/Azure-managed keys); secrets live in Key Vault; sensitive payloads are classified and may be redacted while metadata fields remain queryable.

Caching & Consistency

  • Read models over joins: ProcessStateService projects workflow/task state for fast reads; the SQL event store remains the system of record.
  • Cache invalidation: caches key on tenantId + entity id; writes publish change events that invalidate Redis entries.
  • Eventual consistency across services: cross-service consistency is achieved via events and sagas (no distributed transactions); compensation handles failures (see Workflows).