Settings Tables: A Global → Tenant → User Override Hierarchy

Version note: The C# code in this post was tested with .NET 10 and EF Core 10, and the SQL examples with SQL Server 2025.

Guess First: Does the Unique Index Stop Two Global Rows?

Setting values live in a single table: global values have NULL for both TenantId and UserId, tenant values have only UserId as NULL. To allow a single value per key at each level, the EF Core model defines a unique index:

b.HasIndex(v => new { v.Key, v.TenantId, v.UserId }).IsUnique();

Then someone adds two global rows for the same key:

INSERT SettingValues ([Key], Scope, TenantId, UserId, Value) VALUES ('invoice.due-days', 1, NULL, NULL, '30');
INSERT SettingValues ([Key], Scope, TenantId, UserId, Value) VALUES ('invoice.due-days', 1, NULL, NULL, '31');

In a unique index, SQL Server treats NULLs as equal to each other, so the second INSERT should get a duplicate key error. Does it really? And who will know which value is the "global due date" now? The answer hides in something EF Core does silently.

Context and Problem

In the HR Portal the invoice due date defaults to 30 days. Acme's contract says 45. Ada wants to turn off e-mail notifications; Alan doesn't. Globex has two-level leave approval; the other tenants have one level. These values have things in common: they change at runtime, the person who changes them isn't a developer (it's the tenant admin or the user), they must take effect without waiting for a deploy, and for every change someone may ask who made it and why.

appsettings.json is the wrong place for this. A configuration file is for values that vary by environment and arrive with a deploy: connection strings, external service addresses, log levels. The provider chain we covered in the ASP.NET Core Web API series post "Configuration and the Options Pattern in ASP.NET Core: Provider Order, User Secrets and Validation" produces one configuration for one application; it wasn't designed to carry separate values for 120 tenants and 9,000 users. What we need is an override hierarchy for business settings in the database: code default → global → tenant → user.

Symptoms

You know you've solved this badly when the Settings table has Key and Value, Value is an nvarchar, and the code calls int.Parse on every read. When a tenant admin types a value wrong, the error shows up not on the settings screen but when an invoice is created. Nothing says which setting can be overridden at which level; a user can change their own invoice due date. Every HTTP request reads the same settings from the database over and over. The SMTP password sits in the same table as plain text "so it can be changed from the settings screen". There's no answer to "when did Acme's due date become 45?".

The Naive Solution and Why It Breaks

The first version started with the structure that looked most flexible: key-value per tenant.

CREATE TABLE TenantSettingsEav (TenantId int NOT NULL, [Key] varchar(100) NOT NULL, Value nvarchar(400) NOT NULL, PRIMARY KEY (TenantId, [Key]));
INSERT TenantSettingsEav VALUES (1, 'InvoiceDueDays', N'45 days');

string raw = await db.Database
    .SqlQueryRaw<string>("SELECT Value FROM TenantSettingsEav WHERE TenantId = 1 AND [Key] = 'InvoiceDueDays'")
    .SingleAsync();
int dueDays = int.Parse(raw);

FormatException: The input string '45 days' was not in a correct format.

The tenant admin typed "45 days" into the box, the save went through without an error, and three days later an exception was thrown when the first invoice was created. The break happens at read time, not write time: the database doesn't know the type, and the application doesn't validate on write. This is the most typical form of Entity-Attribute-Value, as Bill Karwin describes it in "SQL Antipatterns". On top of that, this table has no global or user level; when they're added, the precedence rule will be rewritten everywhere the value is read.

Options

A) Untyped Key-Value (EAV)

The structure above. Adding a new setting is easy, and everything fits. The cost is type safety, validation and a precedence rule scattered everywhere. It shouldn't be chosen on its own.

B) Defined and Typed Key-Value

The disciplined version of EAV: every setting has a definition (SettingDefinition: key, type, default, lowest override level, valid range), and values (SettingValue) are tied to that definition with an FK. Writes are validated against the definition, and reads use a typed key. Values are still stored as text; type safety comes not from the database but from a single write path.

C) A Settings Table with Typed Columns

Each setting is a column: type, default and range live in the database.

CREATE TABLE dbo.TenantSettings
(
    TenantId int NOT NULL PRIMARY KEY,
    InvoiceDueDays int NOT NULL CONSTRAINT DF_TenantSettings_InvoiceDueDays DEFAULT (30)
        CONSTRAINT CK_TenantSettings_InvoiceDueDays CHECK (InvoiceDueDays BETWEEN 0 AND 180),
    EmailNotifications bit NOT NULL CONSTRAINT DF_TenantSettings_EmailNotifications DEFAULT (1)
);

This gives the strongest integrity. But every new setting is a migration, the user level needs a second table, and the question "did the tenant enter this value or is it the default?" needs NULL semantics. A good choice when the settings are few and stable.

D) A JSON Document per Tenant

All of a tenant's settings in a single json column. On SQL Server 2025 you can update a single value in place with modify. But there's no type checking; when we tried, writing text in place of a numeric value went through without an error:

UPDATE dbo.TenantSettingsDoc SET Settings.modify('$.invoiceDueDays', 60) WHERE TenantId = 1;
UPDATE dbo.TenantSettingsDoc SET Settings.modify('$.invoiceDueDays', '60 days') WHERE TenantId = 1;

{"invoiceDueDays":60,"emailNotifications":true}
{"invoiceDueDays":"60 days","emailNotifications":true}

A check constraint can enforce that a path exists (JSON_PATH_EXISTS), but constraints for types and ranges quickly become unreadable. Change history also stays at the document level: you answer "which setting changed?" with a diff.

E) An External Configuration Store

A service like Azure App Configuration can separate environments or tenants with labels and push changes to the application. It's very good for environment and operational settings (rate limits, timeouts, feature flags). It's the wrong tool for thousands of user-level values and values that business units manage from a screen: a user preference is business data with transactions and an audit trail.

For the HR Portal we choose B: the number of settings will grow (40 today, 70 expected by the end of the year), levels differ between settings, and every change needs an audit trail.

Implementation: Definitions, Values and Override Rules

A definition is a setting's contract. LowestScope says the lowest level at which the setting can be overridden: the invoice due date goes down to the tenant level at most, the notification preference all the way to the user.

public enum SettingScope : byte { Default = 0, Global = 1, Tenant = 2, User = 3 }

public enum SettingType : byte { Int = 1, Bool = 2, String = 3 }

public class SettingDefinition
{
    public string Key { get; set; } = "";
    public SettingType Type { get; set; }
    public string DefaultValue { get; set; } = "";
    public SettingScope LowestScope { get; set; }
    public int? Min { get; set; }
    public int? Max { get; set; }
}

public class SettingValue
{
    public long Id { get; set; }
    public string Key { get; set; } = "";
    public SettingScope Scope { get; set; }
    public int? TenantId { get; set; }
    public int? UserId { get; set; }
    public string Value { get; set; } = "";
}

Some of the rules go into the schema. A check constraint keeps Scope consistent with which of TenantId/UserId are filled; a unique index ensures a single value per key at each level. This is where the answer to the opening question comes in:

modelBuilder.Entity<SettingValue>(b =>
{
    b.Property(v => v.Key).HasMaxLength(100).IsUnicode(false);
    b.Property(v => v.Value).HasMaxLength(400);
    b.HasOne<SettingDefinition>().WithMany().HasForeignKey(v => v.Key);
    b.HasIndex(v => new { v.Key, v.TenantId, v.UserId }).IsUnique().HasFilter(null);
    b.HasIndex(v => new { v.TenantId, v.UserId });
    b.ToTable(t => t.HasCheckConstraint("CK_SettingValues_Scope",
        "([Scope] = 1 AND [TenantId] IS NULL AND [UserId] IS NULL) OR " +
        "([Scope] = 2 AND [TenantId] IS NOT NULL AND [UserId] IS NULL) OR " +
        "([Scope] = 3 AND [TenantId] IS NOT NULL AND [UserId] IS NOT NULL)"));
});

The index EF Core generates without HasFilter(null), and the result of the two opening INSERTs:

CREATE UNIQUE INDEX [IX_SettingValues_Key_TenantId_UserId] ON [SettingValues] ([Key], [TenantId], [UserId]) WHERE [TenantId] IS NOT NULL AND [UserId] IS NOT NULL;

inserted
inserted

EF Core's SQL Server provider adds an automatic IS NOT NULL filter for every nullable column in a unique index; the EF Core indexes documentation describes this convention and turning it off with HasFilter(null). Our index covered only user rows; global and tenant rows weren't protected at all. With the filter removed, SQL Server's behavior of treating NULLs as equal does exactly what we want. The first global row was inserted; then a second global row, a second tenant value for Acme and a user row without a UserId were attempted:

SqlException 2601: Cannot insert duplicate key row in object 'dbo.SettingValues' with unique index 'IX_SettingValues_Key_TenantId_UserId'. The duplicate key value is (invoice.due-days, <NULL>, <NULL>).
SqlException 2601: Cannot insert duplicate key row in object 'dbo.SettingValues' with unique index 'IX_SettingValues_Key_TenantId_UserId'. The duplicate key value is (invoice.due-days, 1, <NULL>).
SqlException 547: The INSERT statement conflicted with the CHECK constraint "CK_SettingValues_Scope". The conflict occurred in database "HrPortal", table "dbo.SettingValues".

Watch out if you move to PostgreSQL: there, a unique index treats NULLs as distinct by default; for the same behavior you need NULLS NOT DISTINCT, introduced in PostgreSQL 15.

On the read side, keys aren't strings but typed:

public sealed record SettingKey<T>(string Name);

public static class SettingKeys
{
    public static readonly SettingKey<int> InvoiceDueDays = new("invoice.due-days");
    public static readonly SettingKey<bool> EmailNotifications = new("notifications.email");
}

Implementation: Resolution, Cache and Audit

The service keeps two cache entries: definitions and global values once for all tenants, and tenant and user values once per tenant. The precedence rule is written in a single place:

public async Task<(T Value, SettingScope Source)> GetAsync<T>(
    SettingKey<T> key, int tenantId, int userId, CancellationToken ct = default)
{
    GlobalSettings global = (await cache.GetOrCreateAsync(GlobalKey, async e =>
    {
        e.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
        return new GlobalSettings(
            await db.SettingDefinitions.AsNoTracking().ToDictionaryAsync(d => d.Key, ct),
            await db.SettingValues.AsNoTracking().Where(v => v.Scope == SettingScope.Global).ToListAsync(ct));
    }))!;

    List<SettingValue> tenantValues = (await cache.GetOrCreateAsync(TenantKey(tenantId), async e =>
    {
        e.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
        return await db.SettingValues.AsNoTracking().Where(v => v.TenantId == tenantId).ToListAsync(ct);
    }))!;

    // Precedence: user > tenant > global > code default
    SettingValue? best =
        tenantValues.FirstOrDefault(v => v.Key == key.Name && v.UserId == userId)
        ?? tenantValues.FirstOrDefault(v => v.Key == key.Name && v.UserId == null)
        ?? global.Values.FirstOrDefault(v => v.Key == key.Name);

    ResolvedSetting r = best is not null
        ? new ResolvedSetting(best.Value, best.Scope)
        : new ResolvedSetting(global.Definitions[key.Name].DefaultValue, SettingScope.Default);
    return ((T)Convert.ChangeType(r.Value, typeof(T), CultureInfo.InvariantCulture), r.Source);
}

The return value carries not only the value but also the level it came from. The settings screen can say "this value comes from the company setting", and the support team can answer "why 45?" at a glance. The write path is the single gate for validation:

public async Task SetAsync<T>(SettingKey<T> key, T value, SettingScope scope,
    int? tenantId, int? userId, int changedBy, string reason, CancellationToken ct = default)
    where T : notnull
{
    SettingDefinition def = await db.SettingDefinitions.SingleAsync(d => d.Key == key.Name, ct);
    if (scope > def.LowestScope)
        throw new InvalidOperationException($"Setting '{key.Name}' cannot be overridden at {scope} scope.");

    string text = Convert.ToString(value, CultureInfo.InvariantCulture)!;
    if (def.Type == SettingType.Bool) text = text.ToLowerInvariant();
    if (def.Type == SettingType.Int && value is int n && (n < def.Min || n > def.Max))
        throw new ArgumentOutOfRangeException(nameof(value), $"Value {n} is out of range [{def.Min}, {def.Max}].");

    SettingValue? row = await db.SettingValues.SingleOrDefaultAsync(
        v => v.Key == key.Name && v.TenantId == tenantId && v.UserId == userId, ct);
    string? old = row?.Value;
    if (row is null)
    {
        row = new SettingValue { Key = key.Name, Scope = scope, TenantId = tenantId, UserId = userId };
        db.SettingValues.Add(row);
    }
    row.Value = text;

    db.SettingChanges.Add(new SettingChange
    {
        Key = key.Name, Scope = scope, TenantId = tenantId, UserId = userId,
        OldValue = old, NewValue = text, ChangedBy = changedBy,
        ChangedAt = clock.GetUtcNow(), Reason = reason
    });
    await db.SaveChangesAsync(ct);

    cache.Remove(tenantId is int t ? TenantKey(t) : GlobalKey);
}

Writing values with InvariantCulture matters: if a server running under the Turkish culture writes 1,5 and one running under the English culture writes 1.5, the same table carries two formats. A 45-day due date for Acme, notifications off for Ada, and two invalid attempts:

await settings.SetAsync(SettingKeys.InvoiceDueDays, 45, SettingScope.Tenant, Acme, null, Admin, "Contract amendment 2026-03");
await settings.SetAsync(SettingKeys.EmailNotifications, false, SettingScope.User, Acme, Ada, Ada, "User preference");

acme/ada      invoice.due-days=45 (Tenant), notifications.email=False (User)
acme/alan     invoice.due-days=45 (Tenant), notifications.email=True (Default)
globex/linus  invoice.due-days=30 (Default), notifications.email=True (Default)
Setting 'invoice.due-days' cannot be overridden at User scope.
Value 400 is out of range [0, 180]. (Parameter 'value')

The error now shows up when the setting is saved, not when an invoice is created. The effect of the cache is visible in the queries counted by a DbCommandInterceptor:

first read: 3 queries
next 100 reads: 0 queries
after write: 1 queries, invoice.due-days=60

The first read loaded the definitions, the global values and the tenant values; the next 100 reads came from memory; when Acme's due date was changed to 60, only Acme's entry was removed and reloaded with a single query. This setup is correct for a single server. If your application runs on three instances, the other two see the old value until the 10-minute expiration; you can accept that, or propagate invalidation through a distributed mechanism such as Redis pub/sub or HybridCache. The details of that decision belong to the planned Caching series; the ASP.NET Core Web API series post "Caching in ASP.NET Core Web API: IMemoryCache, Redis and Response Caching" covers the basics.

Every write leaves a change record within the same SaveChanges:

invoice.due-days     Tenant tenant=1 user=- null -> 45 by 99: Contract amendment 2026-03
notifications.email  User   tenant=1 user=10 null -> false by 10: User preference
invoice.due-days     Tenant tenant=1 user=- 45 -> 60 by 99: Renewal 2026-11

"When and why did Acme's due date become 45?" is now a query. If you have the general audit infrastructure from the series post "Audit Trail: Who Changed What, When and Why?", you can hand this table over to it; the advantage of keeping a separate one for settings is being able to show the history directly on the settings screen.

Secrets don't go in this table. Even if someone wants the SMTP password, an external service API key or a signing key to be changeable from the settings screen, it belongs in a secret store such as Key Vault; the settings table may hold only the secret's name (smtp.credential-name). The Web Security series post "OWASP A04: Cryptographic Failures (2) — Password Storage, Data Protection and Secret Management" explains this separation. The boundary with IOptions is clear too: values that change with a deploy and are specific to an environment live in configuration and are read with IOptions; values that change at runtime by business users and are specific to a tenant or user live in this table and are read with SettingsService. Keeping one value in both places means a team that doesn't know which one wins.

Trade-off Table

When Not to Use It

Don't build this structure if all your settings change with a deploy and don't vary by tenant or user; IOptions and configuration are enough. If you have five tenant settings that haven't changed in years, C (typed columns) gives stronger integrity with less code. If a value is really part of a business rule (like Acme's contractual payment term), it may be better kept in its own entity than in a general "settings" table: a PaymentTermDays column in a contracts table is data with relationships and an effective date. Don't put feature flags in this table either; the on/off logic, targeting and cleanup discipline are different, and they're the topic of the next post.

Checklist

• Every setting has a typed key in code and a definition in the database; the definition carries the type, default, range and lowest level.
• Writes go through a single service and are validated against the definition; errors surface at save time.
• The unique index on nullable columns isn't caught by EF Core's IS NOT NULL filter (HasFilter(null)), and a check constraint keeps the levels consistent.
• The precedence rule lives in one place: user, tenant, global, code default.
• Reads return which level the value came from.
• Reads come from the cache; a write removes the related entry; with multiple instances the staleness window was chosen deliberately.
• Every change is recorded with who, when, old and new value, and a reason.
• Secrets aren't in the settings table; values are written with InvariantCulture.

Related Patterns and Sources

This post builds on the tenant resolution from the previous post; SettingsService can be extended to take the tenant from ITenantContext. The change record relates to the series post "Audit Trail: Who Changed What, When and Why?", and the validity of setting values over time to "Temporal Data: Effective-Dated Records, Price History and the Bitemporal Model". For the configuration side, see the ASP.NET Core Web API series post "Configuration and the Options Pattern in ASP.NET Core: Provider Order, User Secrets and Validation". Sources: the Entity-Attribute-Value chapter of Bill Karwin's "SQL Antipatterns, Volume 1"; on Microsoft Learn, the EF Core "Indexes" doc (the IS NOT NULL convention for unique indexes on nullable columns in SQL Server and HasFilter(null)), the SQL Server "Create unique indexes" and "JSON data type" (the modify method) docs, and the .NET "Caching in .NET" doc; the NULLS NOT DISTINCT description on the CREATE INDEX page of the PostgreSQL docs; the Azure App Configuration docs.

A Small Experiment

★☆☆ Add a global value (35) for invoice.due-days and watch Globex's result change from Default to Global. Then change the global value and verify from the query count that only the global cache entry was removed.

★★☆ Add a list of allowed values (AllowedValues) for SettingType.String, for example single and two-level for approval.mode. Reject an invalid value at write time.

★★★ Make setting values effective-dated: add a ValidFrom column and make it possible to schedule a future change such as "from January 1 the due date is 60 days". Test that resolution picks the right value based on the date from TimeProvider.

Settings answer "what's the value for this tenant?". In the next post we'll look at something that looks similar but lives very differently: feature flags. We'll discuss Hodgson's four toggle types, filters with Microsoft.FeatureManagement, and how to manage flag debt in code.