Skip to content

14 01 Building An API Server

Jump to bottom
Cyberdyne Development edited this page Mar 2, 2026 · 4 revisions

14-01 Building an API Server

Step-by-step tutorial for building a FractalDataWorks API server, based on the Reference.Api project in ReferenceSolutions/ApiSolution/. This guide walks through every section of Program.cs, explains the hosting extensions, and covers Docker setup, database initialization, authentication, endpoints, and troubleshooting.

For the general-purpose server creation guide (covering all server types), see 12-01 Creating a Server.

Prerequisites

Before starting, ensure you have:

  • .NET 10.0 SDK (preview) installed
  • FDW NuGet packages available (either from a NuGet feed or built locally via ./scripts/pack-local.sh)
  • Docker Desktop (for SQL Server and Seq containers)
  • ConfigurationDb deployed via dacpac (see Section 11)

1. Project Setup

Create a new web project and add the required packages:

dotnet new web -n My.Api
cd My.Api

Add FDW hosting packages (these pull in the core framework transitively):

dotnet add package FractalDataWorks.Hosting.MsSql

Add endpoint and service packages for each domain you need. The Reference.Api uses the following:

# Endpoint packages (auto-discovered by FastEndpoints)
dotnet add package FractalDataWorks.Services.Connections.Endpoints
dotnet add package FractalDataWorks.Services.Data.Endpoints
dotnet add package FractalDataWorks.Services.Pipelines.Endpoints
dotnet add package FractalDataWorks.Services.Scheduling.Endpoints
dotnet add package FractalDataWorks.Services.Users.Endpoints
dotnet add package FractalDataWorks.Services.Authorization.Endpoints
dotnet add package FractalDataWorks.Services.Multitenancy.Endpoints
dotnet add package FractalDataWorks.Services.Quality.Endpoints
dotnet add package FractalDataWorks.Services.Catalog.Endpoints
dotnet add package FractalDataWorks.Operations.Endpoints
dotnet add package FractalDataWorks.Schema.Endpoints
dotnet add package FractalDataWorks.Web.Search.Endpoints

# Authentication and authorization
dotnet add package FractalDataWorks.Services.Authentication
dotnet add package FractalDataWorks.Services.Authentication.Jwt
dotnet add package FractalDataWorks.Services.Authorization
dotnet add package Microsoft.AspNetCore.Authentication.JwtBearer

# Configuration writers (CQRS write-side)
dotnet add package FractalDataWorks.Configuration.Writers
dotnet add package FractalDataWorks.Configuration.Writers.MsSql

# Rate limiting and resiliency
dotnet add package FractalDataWorks.Services.RateLimiting
dotnet add package FractalDataWorks.Services.Resiliency

# FastEndpoints and Scalar API docs
dotnet add package FastEndpoints
dotnet add package FastEndpoints.Swagger
dotnet add package Scalar.AspNetCore

# Serilog
dotnet add package Serilog.AspNetCore
dotnet add package Serilog.Enrichers.Span
dotnet add package Serilog.Extensions.Logging
dotnet add package Serilog.Settings.Configuration
dotnet add package Serilog.Sinks.Console
dotnet add package Serilog.Sinks.Seq
dotnet add package Serilog.Enrichers.Environment

Add FDW source generators (required for MessageLogging, TypeCollections, and configuration):

dotnet add package FractalDataWorks.MessageLogging.SourceGenerators
dotnet add package FractalDataWorks.Registration.SourceGenerators
dotnet add package FractalDataWorks.Collections.SourceGenerators
dotnet add package FractalDataWorks.Configuration.SourceGenerators
dotnet add package FractalDataWorks.Data.SourceGenerators

Source generators must be referenced with OutputItemType="Analyzer" and ReferenceOutputAssembly="false" in the .csproj. The dotnet add package command does not set these automatically, so verify the .csproj contains entries like:

<PackageReference Include="FractalDataWorks.MessageLogging.SourceGenerators"
                  OutputItemType="Analyzer"
                  ReferenceOutputAssembly="false" />

2. Program.cs Walkthrough

The Reference.Api Program.cs is approximately 540 lines, but the core structure follows the same five-step hosting pattern as any FDW server. Everything else is application-specific wiring.

The Five Framework Extension Calls

Every FDW API server uses these five calls in this order:

// Before Build
var loggerFactory = builder.AddFrameworkSerilog("Reference.Api");
using var configDb = await builder.AddFrameworkConfigurationDb(loggerFactory);
builder.AddFrameworkServiceTypes(loggerFactory, types =>
{
    types.AddSecretManagers()
         .AddConnections()
         .AddDataStores(ds => ds.RegisterMsSql())
         .AddDataSets()
         .AddAuthentication()
         .AddEtlPipelines()
         .AddConfigurationWriters()
         .AddDataGateway();
});

var app = builder.Build();

// After Build
app.InitializeFrameworkServiceTypes(loggerFactory);
app.UseFrameworkMiddleware(securityHeadersOptions);

Source: ReferenceSolutions/ApiSolution/src/Reference.Api/Program.cs, lines 63-311.

Call 1: AddFrameworkSerilog("Reference.Api")

Configures two-stage Serilog bootstrap. Returns an ILoggerFactory for use during startup before the DI container is built.

  • Stage 1 (Bootstrap): Creates a console-only logger with FromLogContext, WithSpan, WithMachineName, and WithEnvironmentName enrichers.
  • Stage 2 (Full): Calls builder.Host.UseSerilog() to read the complete Serilog configuration from appsettings.json.

Source: FractalDataWorks.Hosting/Extensions/SerilogExtensions.cs.

Call 2: AddFrameworkConfigurationDb(loggerFactory)

Connects to ConfigurationDb and loads all [ManagedConfiguration] tables. Returns a ConfigurationDbResult (implements IDisposable) containing the bootstrap connection. The using ensures the bootstrap connection is disposed after startup.

Internally it:

  1. Reads ConfigurationDb section from appsettings.json as MsSqlConnectionConfiguration
  2. Creates a bootstrap EnvironmentVariableSecretManager from the BootstrapSecretManager section
  3. Creates an MsSqlConnection to ConfigurationDb
  4. Adds the MsSqlConfigurationSource which loads ALL [ManagedConfiguration] tables

Call 3: AddFrameworkServiceTypes(loggerFactory, types => { ... })

Phase 1 (Configure + Register) for all ServiceTypeCollections. Each Add*() method on the fluent builder calls {Domain}Types.Configure() and {Domain}Types.Register() internally. The builder stores registration state in DI so that InitializeFrameworkServiceTypes knows which domains to initialize.

Call 4: InitializeFrameworkServiceTypes(loggerFactory)

Phase 2 (Initialize) for all ServiceTypeCollections registered in Phase 1. Each domain's {Domain}Types.Initialize(services, loggerFactory) is called to eagerly resolve factories and fail fast if configuration is missing.

Source: FractalDataWorks.Hosting/Extensions/ServiceTypeExtensions.cs.

Call 5: UseFrameworkMiddleware(securityHeadersOptions)

Adds four middleware components in this order:

  1. GlobalExceptionHandlerMiddleware -- catches unhandled exceptions, returns structured ErrorResponse JSON with correlation ID
  2. UseHttpsRedirection() -- redirects HTTP to HTTPS
  3. SecurityHeadersMiddleware -- OWASP security headers (X-Content-Type-Options, X-Frame-Options, Strict-Transport-Security, Content-Security-Policy)
  4. UseSerilogRequestLogging() -- structured HTTP request/response logs

The SecurityHeadersOptions parameter is optional. Reference.Api reads it from configuration:

var securityHeadersOptions = builder.Configuration
    .GetSection("SecurityHeaders")
    .Get<FractalDataWorks.Hosting.Configuration.SecurityHeadersOptions>()
    ?? new FractalDataWorks.Hosting.Configuration.SecurityHeadersOptions();
app.UseFrameworkMiddleware(securityHeadersOptions);

Source: FractalDataWorks.Hosting/Extensions/MiddlewareExtensions.cs.

3. Service Domain Registration

The AddFrameworkServiceTypes fluent builder enforces correct dependency ordering. The first four domains must be called in order; the rest can follow in any order.

Required Order (First Four)

AddSecretManagers()   -- Must be first (provides password resolution)
       |
AddConnections()      -- Depends on SecretManagers for secret resolution
       |
AddDataStores(...)    -- Depends on Connections for database access
       |
AddDataSets()         -- Depends on DataStores for physical storage

Then Any Order

AddAuthentication()        -- Depends on Connections
AddEtlPipelines()          -- Depends on DataStores + DataSets
AddConfigurationWriters()  -- Depends on Connections + DataStores
AddDataGateway()           -- Depends on DataStores + DataSets

What Reference.Api Registers

From the actual Program.cs:

builder.AddFrameworkServiceTypes(loggerFactory, types =>
{
    types.AddSecretManagers()
         .AddConnections()
         .AddDataStores(ds => ds.RegisterMsSql())
         .AddDataSets()
         .AddAuthentication()
         .AddEtlPipelines()
         .AddConfigurationWriters()
         .AddDataGateway();
});

Note that AddAuthorization() is called separately because the RBAC authorization bridge was added after the initial hosting builder was designed:

builder.Services.AddFrameworkAuthorizationPolicies();

The MsSql configuration writer backend is also registered separately:

builder.Services.AddFrameworkMsSqlConfigurationWriterBackend(loggerFactory);

DataStore Registration Callback

DataStoreTypes is a MutableTypeCollection. The MsSql DataStore type lives in a separate assembly and must be explicitly registered through the callback:

.AddDataStores(ds => ds.RegisterMsSql())

4. Authentication and Authorization

JWT Bearer Authentication

Reference.Api reads JWT configuration from the Authentication:Jwt section of IConfiguration. This section is populated from ConfigurationDb via MsSqlConfigurationSource and also has development defaults in appsettings.json.

The configuration is validated with fail-fast behavior:

var jwtConfig = builder.Configuration.GetSection("Authentication:Jwt")
    .Get<List<JwtAuthenticationConfiguration>>()?.FirstOrDefault();

if (jwtConfig is null)
{
    BootstrapLog.JwtConfigurationMissing(bootstrapLogger);
    return 1;
}

JWT bearer is configured using the standard ASP.NET Core pattern:

builder.Services.AddAuthentication(options =>
{
    options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
    options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
})
.AddJwtBearer(options =>
{
    options.MapInboundClaims = false;
    options.TokenValidationParameters = new TokenValidationParameters
    {
        ValidateIssuer = jwtConfig.ValidateIssuer,
        ValidateAudience = jwtConfig.ValidateAudience,
        ValidateLifetime = jwtConfig.ValidateLifetime,
        ValidateIssuerSigningKey = jwtConfig.ValidateIssuerSigningKey,
        ValidIssuer = jwtConfig.Issuer,
        ValidAudience = jwtConfig.Audience,
        IssuerSigningKey = new SymmetricSecurityKey(
            Encoding.UTF8.GetBytes(jwtConfig.SecretKey)),
        ClockSkew = TimeSpan.FromSeconds(jwtConfig.ClockSkewSeconds),
        RoleClaimType = jwtConfig.RoleClaimType,
        NameClaimType = jwtConfig.NameClaimType
    };
});

RBAC Authorization

FDW provides database-backed RBAC with fdw:{resource}:{action} endpoint policies:

builder.Services.AddFrameworkAuthorizationPolicies();
builder.Services.AddAuthorization();

This registers FdwAuthorizationPolicyProvider and FrameworkPermissionHandler. Permissions are stored in cfg.Permission, roles in cfg.Role, and mappings in cfg.RolePermission. See 12-05 Authorization for details.

SQL-Based Authentication

Reference.Api uses SQL-backed authentication for user credential verification:

builder.Services.AddSqlAuthentication(configDb.BootstrapConnection.ConnectionString);

The configDb.BootstrapConnection provides a direct SQL connection before the DI container is built.

5. FastEndpoints

Registration

Add FastEndpoints and Swagger document generation before Build():

builder.Services.AddFastEndpoints();
builder.Services.SwaggerDocument(o =>
{
    o.DocumentSettings = s =>
    {
        s.Title = "Reference.Api - NFL Stats";
        s.Version = "v1";
        s.Description = "FractalDataWorks Reference API demonstrating DataGateway, " +
                        "SecretManager, ConnectionProvider, and NFL 2024 statistics.";
    };
});

Middleware

After Build(), configure the endpoint middleware with a route prefix and role claim type:

app.UseFastEndpoints(config =>
{
    config.Endpoints.RoutePrefix = "api/v1";
    config.Security.RoleClaimType = jwtConfig.RoleClaimType;
});
app.UseSwaggerGen();

Per-Domain Endpoint Packages

FDW uses per-domain .Endpoints packages that are auto-discovered by FastEndpoints. Each package contains endpoint base classes that your application subclasses with thin closures. Reference.Api includes 15 endpoint packages:

Package Domain
FractalDataWorks.Services.Connections.Endpoints Connection management
FractalDataWorks.Services.Data.Endpoints DataStore and DataSet operations
FractalDataWorks.Services.Pipelines.Endpoints ETL pipeline management
FractalDataWorks.Services.Scheduling.Endpoints Schedule CRUD
FractalDataWorks.Services.Users.Endpoints User management
FractalDataWorks.Services.Authentication.Endpoints Authentication (login, token refresh, logout, user info)
FractalDataWorks.Services.Authorization.Endpoints Role and permission management
FractalDataWorks.Services.Multitenancy.Endpoints Tenant management
FractalDataWorks.Services.Quality.Endpoints Data quality checks
FractalDataWorks.Services.Catalog.Endpoints Data catalog
FractalDataWorks.Calculations.Endpoints Calculation execution and type listing
FractalDataWorks.Operations.Endpoints Execution tracking
FractalDataWorks.Schema.Endpoints Schema introspection
FractalDataWorks.Web.Search.Endpoints Search API
FractalDataWorks.UI.Themes.Endpoints Theme management

See 12-08 Customizing Endpoints and 13-02 Creating Consumer Packages for building endpoints from these packages.

6. SignalR Hub Registration

Reference.Api registers three SignalR hubs for real-time updates. First, register SignalR and the FDW broadcasters before Build():

builder.Services.AddSignalR();
PipelineTypes.RegisterBroadcaster(builder.Services, loggerFactory);
CalculationSignalR.RegisterBroadcaster(builder.Services, loggerFactory);
DataSignalR.RegisterBroadcaster(builder.Services, loggerFactory);

Then map the hub routes after Build():

app.MapHub<PipelineStatusHub>("/hubs/pipelines");
app.MapHub<CalculationHub>("/hubs/calculations");
app.MapHub<SchemaDiscoveryHub>("/hubs/schema-discovery");
Hub Route Purpose
PipelineStatusHub /hubs/pipelines ETL pipeline execution status updates
CalculationHub /hubs/calculations Calculation chain progress
SchemaDiscoveryHub /hubs/schema-discovery Schema introspection progress

7. CORS, Rate Limiting, and Resiliency

CORS

Register CORS from the "Cors" section of appsettings.json before Build():

builder.Services.AddFrameworkCors(builder.Configuration);

Activate the middleware after UseFrameworkMiddleware() but before authentication (required for preflight OPTIONS requests):

var corsOptions = app.Services.GetService<FractalDataWorks.Hosting.Configuration.CorsOptions>();
if (corsOptions?.Enabled == true)
{
    app.UseCors();
}

Source: FractalDataWorks.Hosting/Extensions/CorsExtensions.cs.

Rate Limiting

// Before Build
builder.Services.AddFrameworkRateLimiting(loggerFactory);

// After Build, after authentication
app.UseRateLimiter();

Resiliency

// Before Build
builder.Services.AddResiliency();

8. Scalar API Documentation

Reference.Api uses Scalar for API documentation with multi-tenant theming. The core setup is:

app.MapScalarApiReference(options =>
{
    options.WithOpenApiRoutePattern("/swagger/{documentName}/swagger.json");
    options.WithTheme(ScalarTheme.None);
    options.AddHeadContent(@"<style>/* Custom CSS */</style>");
});

Key Scalar.AspNetCore API usage (version 2.12.18):

Correct Wrong (old/nonexistent)
EnableDarkMode() WithPreferredScheme(ScalarScheme.Dark)
AddHeadContent(html) WithHeadContent(html)
WithTheme(ScalarTheme.None) N/A

A root redirect sends users to the API docs:

app.MapGet("/", () => Results.Redirect("/scalar"))
   .ExcludeFromDescription();

9. appsettings.json Reference

The appsettings.json for Reference.Api contains the following sections. Each is explained below.

Kestrel

{
  "Kestrel": {
    "Endpoints": {
      "Http": { "Url": "http://localhost:5000" },
      "Https": { "Url": "https://localhost:5001" }
    }
  }
}

Configures HTTP (port 5000) and HTTPS (port 5001) endpoints.

BootstrapSecretManager

{
  "BootstrapSecretManager": {
    "Type": "EnvironmentVariable",
    "Prefix": "FDW_SECRET_",
    "StripPrefix": true
  }
}

Tells AddFrameworkConfigurationDb how to resolve the database password. The SecretKeyName in ConfigurationDb.Authentication (e.g., CONFIG_PASSWORD) is resolved via the environment variable FDW_SECRET_CONFIG_PASSWORD.

ConfigurationDb

{
  "ConfigurationDb": {
    "Server": "127.0.0.1",
    "Port": 1433,
    "Database": "ConfigurationDb",
    "DefaultSchema": "cfg",
    "TrustServerCertificate": true,
    "Encrypt": true,
    "EnableMultipleActiveResultSets": true,
    "Authentication": {
      "Type": "SqlAuth",
      "Username": "fdw_config",
      "SecretKeyName": "CONFIG_PASSWORD"
    }
  }
}

Connection details for the ConfigurationDb. The fdw_config SQL login has SELECT/INSERT/UPDATE/DELETE on the cfg schema.

Serilog

{
  "Serilog": {
    "Using": [
      "Serilog.Sinks.Console",
      "Serilog.Sinks.Seq",
      "Serilog.Enrichers.Environment"
    ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Information",
        "FractalDataWorks": "Debug"
      }
    },
    "WriteTo": [
      {
        "Name": "Console",
        "Args": {
          "outputTemplate": "[{Timestamp:HH:mm:ss} {Level:u3}] [{SourceContext}] {Message:lj} {Properties:j}{NewLine}{Exception}"
        }
      },
      {
        "Name": "Seq",
        "Args": { "serverUrl": "http://localhost:5341" }
      }
    ],
    "Enrich": [
      "FromLogContext", "WithMachineName", "WithEnvironmentName",
      "WithThreadId", "WithProcessId"
    ],
    "Properties": {
      "Application": "Reference.Api",
      "Version": "1.0.0"
    }
  }
}

Stage 2 Serilog configuration. Writes to both console and Seq. The FractalDataWorks source context is set to Debug for detailed framework diagnostics.

Authentication (JWT)

{
  "Authentication": {
    "Jwt": [
      {
        "Name": "Default",
        "Issuer": "FractalDataWorks.Reference.Api",
        "Audience": "FractalDataWorks.Reference.Api",
        "SecretKey": "ThisIsADevelopmentSecretKeyThatShouldBeAtLeast32BytesLong!",
        "AccessTokenExpirationMinutes": 60,
        "RefreshTokenExpirationMinutes": 10080,
        "ValidateIssuer": true,
        "ValidateAudience": true,
        "ValidateLifetime": true,
        "ValidateIssuerSigningKey": true,
        "ClockSkewSeconds": 30,
        "RoleClaimType": "role",
        "NameClaimType": "name",
        "AvailableRoles": ["Admin", "Operator", "Viewer"]
      }
    ]
  }
}

Development JWT configuration. In production, load the SecretKey from a secret manager. The three RBAC roles (Admin, Operator, Viewer) map to database-backed permissions.

CORS

{
  "Cors": {
    "Enabled": true,
    "Origins": ["https://localhost:5007", "http://localhost:5007"],
    "Methods": ["GET", "POST", "PUT", "DELETE", "PATCH", "OPTIONS"],
    "Headers": ["Content-Type", "Authorization", "X-Requested-With", "X-Tenant-Id", "X-Correlation-Id"],
    "ExposedHeaders": ["X-Correlation-Id", "X-Request-Id", "WWW-Authenticate", "X-RateLimit-Limit", "X-RateLimit-Remaining", "X-RateLimit-Reset"],
    "AllowCredentials": true,
    "PreflightMaxAgeSeconds": 600
  }
}

Origins should include the ManagementUI host address. The exposed headers allow the UI to read rate-limit and correlation headers from responses.

SecurityHeaders

{
  "SecurityHeaders": {
    "AllowFraming": false,
    "EnableDefaultCsp": true
  }
}

Controls X-Frame-Options (DENY vs SAMEORIGIN) and whether a default Content-Security-Policy is generated.

Support

{
  "Support": {
    "Email": "support@example.com",
    "Phone": null,
    "PortalUrl": null,
    "ExpectedResponseTimeHours": 24,
    "Instructions": "If this error persists, please contact support with the Request ID above."
  }
}

Used by the GlobalExceptionHandlerMiddleware to include support contact information in 500 error responses.

OpenTelemetry

{
  "OpenTelemetry": {
    "ServiceName": "Reference.Api",
    "Tracing": { "Enabled": true, "ExportToConsole": false },
    "Metrics": { "Enabled": true, "ExportToConsole": false }
  }
}

CalculationCache

{
  "CalculationCache": {
    "Enabled": true,
    "DefaultTtlMinutes": 60,
    "MaxTtlMinutes": 1440,
    "TtlByCalculationType": {
      "Sum": 30, "Average": 30, "Count": 15, "Min": 30, "Max": 30
    },
    "InvalidateOnDataChange": true,
    "WarmupOnStartup": false,
    "MaxCachedResultSizeBytes": 10485760,
    "Provider": "Memory",
    "KeyPrefix": "calc:"
  }
}

ServiceEndpoints and InternalApi

{
  "ServiceEndpoints": {
    "Scheduler": "https://localhost:5005",
    "Etl": "https://localhost:5003"
  },
  "InternalApi": {
    "ApiKey": "dev-internal-api-key-change-in-production"
  }
}

Proxy targets for the API gateway pattern. The API server proxies requests to backend services using the internal API key. See 12-03 Service Communication.

10. Docker Setup

Reference.Api uses a docker-compose.yml (dacpac variant) that runs SQL Server 2025 and Seq:

services:
  mssql:
    build:
      context: ./docker/mssql
      dockerfile: Dockerfile
    container_name: apisolution-mssql
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=ApiSolution123!
      - MSSQL_PID=Developer
      - FDW_SECRET_AUTH_PASSWORD=FdwAuthPassword#
      - FDW_SECRET_TENANT_PASSWORD=FdwTenantPassword#
      - FDW_SECRET_CONFIG_PASSWORD=FdwConfigPassword#
      - FDW_SECRET_ETL_PASSWORD=FdwEtlPassword#
      - FDW_SECRET_SCHED_PASSWORD=FdwSchedPassword#
      - FDW_SECRET_OPS_PASSWORD=FdwOpsPassword#
      - FDW_SECRET_CONFIG_RO_PASSWORD=FdwConfigRoPassword#
    ports:
      - "1433:1433"
    volumes:
      - mssql-data:/var/opt/mssql
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "ApiSolution123!" -C -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 90s

  seq:
    image: datalust/seq:latest
    container_name: apisolution-seq
    environment:
      - ACCEPT_EULA=Y
      - SEQ_FIRSTRUN_NOAUTHENTICATION=true
    ports:
      - "5341:80"
      - "5342:5342"
    volumes:
      - seq-data:/data
    restart: unless-stopped

volumes:
  mssql-data:
  seq-data:

Source: ReferenceSolutions/ApiSolution/docker-compose.yml.

Environment Variables

Each FDW_SECRET_* environment variable corresponds to a schema-specific SQL login password. The BootstrapSecretManager resolves these at startup:

Environment Variable SQL Login Schema Permissions
FDW_SECRET_AUTH_PASSWORD fdw_auth auth SELECT
FDW_SECRET_TENANT_PASSWORD fdw_tenant tenant SELECT
FDW_SECRET_CONFIG_PASSWORD fdw_config cfg SELECT/INSERT/UPDATE/DELETE
FDW_SECRET_ETL_PASSWORD fdw_etl etl SELECT/INSERT/UPDATE/DELETE
FDW_SECRET_SCHED_PASSWORD fdw_sched sched SELECT/INSERT/UPDATE/DELETE
FDW_SECRET_OPS_PASSWORD fdw_ops ops SELECT/INSERT/UPDATE/DELETE
FDW_SECRET_CONFIG_RO_PASSWORD fdw_config_ro cfg SELECT only

Ports

Service Port Purpose
SQL Server 1433 Database
Seq UI 5341 Structured log viewer
Seq Ingestion 5342 Log ingestion endpoint
Reference.Api 5000 (HTTP) / 5001 (HTTPS) API server

11. Database Initialization

The ConfigurationDb schema is managed by a SQL Server Database Project (databases/ControlDb.sqlproj). It is deployed to Docker via dacpac.

Build the Dacpac

# PowerShell
cd ReferenceSolutions/ApiSolution
./docker/mssql/build-dacpac.ps1

# Bash
cd ReferenceSolutions/ApiSolution
./docker/mssql/build-dacpac.sh

The script builds databases/ControlDb.sqlproj and copies the resulting .dacpac to docker/mssql/dacpac/ControlDb.dacpac.

Build and Start Containers

cd ReferenceSolutions/ApiSolution
docker-compose build mssql
docker-compose up -d

The custom Dockerfile installs SqlPackage into the SQL Server container and deploys the dacpac on first run. It also runs seed scripts for development data and security scripts for login/permission creation.

Dockerfile Overview

The SQL Server image is based on mcr.microsoft.com/mssql/server:2025-latest with:

  • SqlPackage installed for dacpac deployment
  • Seed data scripts copied to /init/seed/
  • Security scripts (login creation and permissions) copied to /docker-entrypoint-initdb.d/
  • Custom entrypoint.sh that starts SQL Server, waits for it to be ready, deploys the dacpac, and runs seed scripts

Source: ReferenceSolutions/ApiSolution/docker/mssql/Dockerfile.

12. Testing the API

Start the Server

# Set environment variables for development
export FDW_SECRET_CONFIG_PASSWORD=FdwConfigPassword#
export FDW_SECRET_AUTH_PASSWORD=FdwAuthPassword#
export FDW_SECRET_TENANT_PASSWORD=FdwTenantPassword#
export FDW_SECRET_ETL_PASSWORD=FdwEtlPassword#
export FDW_SECRET_SCHED_PASSWORD=FdwSchedPassword#
export FDW_SECRET_OPS_PASSWORD=FdwOpsPassword#

# Run the API
dotnet run --project ReferenceSolutions/ApiSolution/src/Reference.Api

Authenticate

# Get a JWT token
curl -s -X POST https://localhost:5001/api/v1/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username": "admin", "password": "AdminPassword123!"}' \
  --insecure | jq .

Call an Endpoint

# List connections (authenticated)
TOKEN="<jwt-token-from-login>"
curl -s https://localhost:5001/api/v1/connections \
  -H "Authorization: Bearer $TOKEN" \
  --insecure | jq .

Health Check

curl -s https://localhost:5001/health --insecure | jq .
# Returns: { "status": "healthy", "service": "Reference.Api", "timestamp": "..." }

View API Documentation

Open https://localhost:5001/scalar in a browser. The root URL (/) redirects to /scalar.

View Logs

Open http://localhost:5341 for the Seq structured log viewer (no authentication in development mode).

Demo Credentials

User Password Role
admin AdminPassword123! Admin
testuser TestPassword123! Viewer

13. Middleware Pipeline Order

The order of middleware registration in Program.cs determines the request processing pipeline. Getting the order wrong causes subtle bugs (CORS preflight failures, missing security headers, etc.).

Reference.Api uses this order:

1. UseFrameworkMiddleware()           -- Exception handler + HTTPS redirect + security headers + Serilog
2. UseHsts()                         -- Production only (HTTP Strict Transport Security)
3. UseCors()                         -- CORS (before auth for preflight OPTIONS)
4. UseAuthentication()               -- JWT bearer token validation
5. UseAuthorization()                -- RBAC policy evaluation
6. UseMultitenancy()                 -- Multi-tenancy (after auth, needs user identity)
7. UseRateLimiter()                  -- Rate limiting (after auth to rate-limit by user)
8. UseFastEndpoints()                -- Endpoint routing and execution

From the actual code:

// Framework middleware: exception handler + HTTPS + security headers + Serilog
app.UseFrameworkMiddleware(securityHeadersOptions);

// HSTS - Production only
if (!app.Environment.IsDevelopment())
{
    app.UseHsts();
}

// CORS - Must be before authentication for preflight (OPTIONS) to work
var corsOptions = app.Services.GetService<FractalDataWorks.Hosting.Configuration.CorsOptions>();
if (corsOptions?.Enabled == true)
{
    app.UseCors();
}

// Authentication and Authorization
app.UseAuthentication();
app.UseAuthorization();

// Multi-tenancy (from FractalDataWorks.Services.Multitenancy.Sql)
if (multitenancyEnabled)
{
    app.UseMultitenancy();
}

// Rate limiting
app.UseRateLimiter();

// FastEndpoints with API versioning prefix
app.UseFastEndpoints(config =>
{
    config.Endpoints.RoutePrefix = "api/v1";
    config.Security.RoleClaimType = jwtConfig.RoleClaimType;
});

Why This Order Matters

  • Exception handler first: UseFrameworkMiddleware() places GlobalExceptionHandlerMiddleware first, catching exceptions from all downstream middleware and returning structured 500 responses with request ID and support contact.
  • CORS before auth: Preflight OPTIONS requests do not carry authorization headers. If auth runs before CORS, preflight requests are rejected with 401.
  • Auth before tenant resolution: Tenant resolution needs the authenticated user identity to validate tenant membership.
  • Rate limiting after auth: Enables per-user rate limiting based on authenticated identity.

14. Troubleshooting

ConfigurationDb Connection Fails

Symptom: InvalidOperationException on startup mentioning ConfigurationDb or BootstrapSecretManager.

Causes and fixes:

  • Missing ConfigurationDb section in appsettings.json -- add the section (see Section 9)
  • Missing BootstrapSecretManager section -- add the section
  • Environment variable not set -- ensure FDW_SECRET_CONFIG_PASSWORD is set (value must match the SQL login password)
  • SQL Server not running -- start Docker containers: docker-compose up -d
  • Wrong port or server -- verify Server and Port in ConfigurationDb section match your Docker container

JWT Configuration Missing

Symptom: Application exits with code 1 and log message FDW-1202: JWT authentication configuration not found.

Causes and fixes:

  • cfg.JwtAuthentication table not seeded in ConfigurationDb -- run the seed scripts
  • MsSqlConfigurationSource failed to load -- check earlier log messages for ConfigurationDb connection errors
  • appsettings.json missing Authentication:Jwt section -- add the development fallback section

ServiceType Initialization Fails

Symptom: Exception during InitializeFrameworkServiceTypes with messages about missing factories or providers.

Causes and fixes:

  • ConfigurationDb tables not seeded (no Connection, DataStore, or DataSet rows with IsCurrent=1) -- run seed scripts
  • [ManagedConfiguration] attribute has wrong ServiceCategory or ServiceType -- verify attribute matches the configuration section path
  • C# property names do not match SQL column names -- names must match exactly (case-sensitive)

CORS Preflight Failures

Symptom: Browser console shows CORS preflight response did not succeed for OPTIONS requests.

Causes and fixes:

  • UseCors() called after UseAuthentication() -- move CORS before auth in the pipeline
  • Origin not in Cors.Origins list -- add the ManagementUI origin to appsettings.json
  • Cors.Enabled is false -- set to true

Endpoints Not Discovered

Symptom: FastEndpoints returns 404 for routes that should exist.

Causes and fixes:

  • Missing .Endpoints NuGet package -- add the per-domain endpoint package for the domain you need
  • Source generators not referenced correctly -- ensure OutputItemType="Analyzer" and ReferenceOutputAssembly="false" on generator package references
  • Wrong route prefix -- verify config.Endpoints.RoutePrefix matches the URL you are calling (default: api/v1)

Database Schema Drift

Symptom: Queries fail with "Invalid column name" or "Invalid object name" errors.

Causes and fixes:

  • Dacpac not rebuilt after schema changes -- rebuild with ./docker/mssql/build-dacpac.sh and redeploy
  • Container using cached data volume -- remove the volume: docker-compose down -v && docker-compose up -d

Seq Not Receiving Logs

Symptom: Seq UI at http://localhost:5341 shows no log entries.

Causes and fixes:

  • Seq container not running -- check with docker ps and restart if needed
  • Wrong Seq URL in Serilog config -- verify Serilog.WriteTo[].Args.serverUrl is http://localhost:5341
  • Firewall blocking port 5341 -- ensure port is open

Related Documentation

Clone this wiki locally