Skip to content

08 02 Database Schema

Jump to bottom
Cyberdyne Development edited this page Feb 16, 2026 · 3 revisions

Database Schema

FractalDataWorks uses a SQL Server database project (sqlproj) to manage the configuration database schema. This page documents the schema structure and deployment approach.

Overview

The ConfigurationDb database contains 84 tables across 9 schemas, storing all FDW configuration including connections, datasets, authentication, scheduling, and TypeCollection metadata.

Schema Organization

Schema Tables Purpose
auth 2 User authentication (credential verification)
tenant 5 Multi-tenancy (tenants, user-tenant mappings, features, settings)
cfg 66 Core configuration for service domains
etl 2 ETL pipeline execution history
sched 1 Schedule execution tracking
ops 3 Operations (execution items, events, workflows)
types 3 TypeCollection metadata for runtime discovery
dq 1 Data quality check results
devOnly 1 Development-only credentials (non-production)

cfg Schema - Configuration (66 tables)

The cfg schema follows the ManagedConfiguration pattern where parent tables store common fields and child tables store type-specific fields.

Connections

Table Description
Connection Base table with Id, Name, ServiceOptionType, IsCurrent, IsDeleted
MsSqlConnection SQL Server: Server, Database, Encrypt, TrustServerCertificate
HttpConnection HTTP/REST: BaseUrl, TimeoutSeconds, DefaultHeaders
OdbcConnection ODBC: ConnectionString, DriverName
PostgreSqlConnection PostgreSQL: Host, Port, Database, SslMode

Secret Managers

Table Description
SecretManager Base table for secret storage backends
KeyVaultSecretManager Azure Key Vault: VaultUri, TenantId, ClientId

Authentication

Table Description
Authentication Base table for auth providers
BasicAuthentication Username/password credentials
ApiKeyAuthentication API key with header name
OAuth2Authentication OAuth2/OIDC: TokenEndpoint, ClientId, Scope
JwtAuthentication JWT: Issuer, Audience, SigningKey

DataSets

Table Description
DataSet Base table with Name, DataStoreId, ServiceOptionType
StandardDataSet Single-source dataset configuration
FederatedDataSet Multi-source with strategy (Union, Merge, etc.)
CompoundDataSet Nested/hierarchical dataset structure
DataSetSource Maps datasets to their data sources

Data Infrastructure

Table Description
DataStoreConfiguration Physical data store locations
DataPath Navigation paths within stores
DataPathSegment Individual path segments
DataContainer Physical schema definitions
DataContainerField Field definitions with type, role, constraints

Other Configuration

Table Description
ResiliencyPolicy Retry, circuit breaker policies
Notification Notification channel configuration
Transformation Data transformation definitions
Workflow Workflow definitions
ConfigurationAudit Audit trail for configuration changes

auth Schema - Authentication (2 tables)

The auth schema stores user credentials with restricted access (SELECT only for fdw_auth login).

Table Description
Users User accounts with password hashes
UserRoles User-role assignments

tenant Schema - Multi-Tenancy (5 tables)

The tenant schema stores multi-tenancy configuration with restricted access (SELECT only for fdw_tenant login).

Table Description
Tenants Tenant definitions (slug, display name, branding)
UserTenants User-tenant memberships
TenantRoles Roles available in each tenant
TenantFeatures Feature flags per tenant
TenantSettings Per-tenant configuration key/value pairs

etl Schema - ETL Pipelines (2 tables)

Table Description
PipelineExecution Execution history with status, timing, row counts
ScheduleExecution ETL-specific schedule execution tracking

sched Schema - Scheduling (1 table)

Table Description
ScheduleExecution Execution history with status, duration

ops Schema - Operations (3 tables)

Table Description
ExecutionItem Execution item tracking (workflows, jobs, stages, steps, tasks)
ExecutionEvent Events and state transitions for execution items
WorkflowExecution Workflow instance tracking with step progress

types Schema - TypeCollection Metadata (3 tables)

The types schema enables runtime TypeCollection discovery from the database.

Table Description
TypeCollection Collection definitions (FilterOperators, ConnectionTypes, etc.)
TypeOption Individual options within collections
TypeProperty Properties of each TypeOption for reflection

SQL Server Database Project

The schema is managed as a SQL Server Database Project using Microsoft.Build.Sql SDK 2.0.0.

Project Location

public/databases/
├── ControlDb.sqlproj          # Project file
├── auth/
│   ├── auth.sql               # CREATE SCHEMA auth
│   └── Tables/                # 2 .sql files (Users, UserRoles)
├── tenant/
│   ├── tenant.sql             # CREATE SCHEMA tenant
│   └── Tables/                # 5 .sql files (Tenants, UserTenants, TenantRoles, etc.)
├── cfg/
│   ├── cfg.sql                # CREATE SCHEMA cfg
│   └── Tables/                # 66 .sql files
├── etl/
│   ├── etl.sql
│   └── Tables/
├── sched/
│   ├── sched.sql
│   └── Tables/
├── ops/
│   ├── ops.sql
│   └── Tables/                # 3 .sql files
├── types/
│   ├── types.sql
│   └── Tables/
└── Security/
    ├── logins.sql             # Service-specific SQL logins
    ├── permissions.sql        # Schema-level permission grants
    └── rls-users.sql          # Row-Level Security policies

Building the Dacpac

cd public/databases
dotnet build -c Release
# Output: bin/Release/ControlDb.dacpac

Table File Format

Each table file follows this structure:

CREATE TABLE [schema].[TableName]
(
    [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
    [Name] NVARCHAR(200) NOT NULL,
    -- columns...
    [IsCurrent] BIT NOT NULL DEFAULT 1,
    [IsDeleted] BIT NOT NULL DEFAULT 0,
    CONSTRAINT [PK_TableName] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_...] FOREIGN KEY (...) REFERENCES ...
);
GO

CREATE INDEX [IX_TableName_Column] ON [schema].[TableName]([Column]);
GO

EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Description of the table',
    @level0type = N'SCHEMA', @level0name = N'schema',
    @level1type = N'TABLE', @level1name = N'TableName';
GO

Key Conventions

  1. GO separators - Required between each statement
  2. Extended properties - MS_Description on tables and columns
  3. Standard columns - IsCurrent, IsDeleted for soft-delete pattern
  4. Parent-child FKs - Child tables reference parent via {ParentName}Id column
  5. Naming - PK_Table, FK_Child_Parent, IX_Table_Column, UQ_Table_Column

Docker Deployment

The Reference Solution deploys the schema via dacpac in Docker:

cd ReferenceSolutions/ApiSolution

# Build dacpac
./docker/mssql/build-dacpac.ps1

# Deploy to Docker
docker-compose -f docker-compose.dacpac.yml up -d

The Docker image includes SqlPackage for dacpac deployment, with seed data applied afterward.

Related Documentation

Clone this wiki locally