Skip to content

ADR: The SQL Server backend

ADR: The SQL Server backend

Status
accepted
Date
2026-09-03
Shipped as
1.0.0.49

Steve asked for a SQL backend built correctly on SQL Server with Entity Framework, with clear diagrams, ready to expand.

In plain words

This page moves the site's data into Microsoft's managed SQL Server (Azure SQL Database), so a bid now survives a deploy as well as a restart. The site signs in to the database with its own Azure identity (a managed identity), so the connection string carries no password, and laptops and CI keep using a single-file database (SQLite).

What that is worth: a developer can clone and test the code with no Azure credential, and the organization has no database password to leak or rotate, with a site that still comes up when the database is paused.

The connection string that is not a credential

Steve's second instruction that day was to avoid standard connection strings wherever possible, because they are a security risk, and this is the part of the work that answers it, so it goes first.

A standard connection string is a security risk because it carries a password. It ends up in a settings file, in a deployment variable, in a screenshot of a deployment variable, in a support ticket, and in the shell history of whoever tested it. Rotating it means finding every copy. The usual answers are to make the copies harder to read: a key vault, a secure setting, a masked log. All of them are still guarding a password.

There is no password here. The logical server was created with --enable-ad-only-auth, which means it has no SQL administrator login at all and no ability to grow one. The container authenticates as the user-assigned managed identity it already carried for pulling its own image, id-theyard-ss, and the token comes from Azure at the moment it is needed.

Before, on the storage this replaces:

Data Source=/app/state/yard.db

After:

Server=tcp:sql-theyard-ss-westus3.database.windows.net,1433;Initial Catalog=sqldb-theyard-ss;
Encrypt=True;TrustServerCertificate=False;Connect Timeout=30;
Authentication=Active Directory Managed Identity;User Id=2888a6ca-be1c-46a5-a1de-c666b1d193e5;

Every field in that is public information. The server name is in DNS. The database name is in the resource group. The client id is an identifier that already appears in Program.cs beside the Application Insights wiring. There is nothing in it to steal, nothing to rotate, and nothing that stops working if it appears in a log.

Two things follow from that and both are worth saying out loud.

The identity's database user was created without Microsoft Graph. The usual CREATE USER [name] FROM EXTERNAL PROVIDER needs the logical server to hold a directory permission so it can look the name up. Giving a database server the ability to read a directory is a permission that outlives the reason it was granted. The user was created from the SID of the identity's client id instead, which the server can verify without asking anybody:

CREATE USER [id-theyard-ss] WITH SID = 0xCAA688281CBEA546A1DEC666B1D193E5, TYPE = E;
ALTER ROLE db_datareader ADD MEMBER [id-theyard-ss];
ALTER ROLE db_datawriter ADD MEMBER [id-theyard-ss];

Read back from sys.database_principals afterwards, because a grant nobody verified is a grant nobody has:

principal: name=id-theyard-ss  type_desc=EXTERNAL_USER  sid_hex=0xCAA688281CBEA546A1DEC666B1D193E5
role: db_datareader
role: db_datawriter

Two roles, and the third one is the interesting part. db_ddladmin was granted first, because the plan at that point was for the container to apply its own migrations at startup, and then removed when the schema became the SQL project's (ADR: Data first, and the database in source control). The running application now cannot create, alter or drop a table. It is not that it does not; it is not permitted to.

The code still refuses to print it. YardConnection.Describe returns "Azure SQL Database" and nothing else, and a test asserts that the server, the database name and the user id are all absent from what it returns. There is no secret in today's connection string, but that is a fact about today's configuration and not a property of the method. The next person to add a setting should not have to notice that this method would have printed it.

Context

ADR: The relational store put the catalogue and the bids into SQLite through EF Core, behind the ports that already existed, and was explicit about what it did not give you: /app/state is the container's own writable layer, so a bid survived a process restart and did not survive a deploy. That record named the fix and declined to do it, because it needed a resource in a subscription this project was not allowed to add to.

Steve authorised exactly one exception on 2026-09-03: one Azure SQL Database and the logical server it needs.

The database, and what it costs

Priced before it was created, from the Azure retail price API rather than from memory, for the region the container runs in:

meter, westus2 measured
General Purpose serverless Gen5, 1 vCore $0.521758 per vCore-hour
General Purpose serverless Gen5, 1 vCore - Free $0.00
General Purpose data stored $0.115 per GB-month
General Purpose data stored, free $0.00
Single Basic, 5 DTU $0.161 per day, $4.90 per month

Azure SQL Database has a free limit: 100,000 vCore-seconds of serverless compute, 32 GB of data and 32 GB of backup per database per month, renewing on the first, on any subscription type, and it does not expire. Both free meters exist in this region at $0.00, which is the check that the offer is real here rather than announced somewhere else.

The database was created with it, and read back rather than assumed:

sku            GP_S_Gen5_2
minCapacity    0.5 vCore
autoPauseDelay 60 minutes
maxSize        32 GB
useFreeLimit   true
exhaustion     AutoPause
backup         Local
status         Online
location       westus3

Committed cost: $0.00 per month. When the monthly allowance runs out the database auto-pauses until the first rather than billing, which is the setting freeLimitExhaustionBehavior: AutoPause and is not reversible once changed to the other option.

It is in westus3 and the container is in westus2, because West US 2 refused:

(RegionDoesNotAllowProvisioning) Location 'West US 2' is not accepting creation
of new Windows Azure SQL Database servers at this time.

That is the third capacity refusal this project has taken from Azure, after App Service quota and Container Apps (ADR: Deployment strategy). The cost of the extra region is one hop on the writes, and nothing at all on the reads, because the catalogue is read into memory once at startup and never queried again per request.

One thing that refusal taught, and it cost a run to learn: a create that fails still pins the name to the region it failed in. The first retry loop kept one server name and walked nine regions, and every region after the first answered InvalidResourceLocation: the resource already exists in location 'westus2', which is ARM remembering a resource that az sql server list says does not exist. The loop that worked uses a name per region.

Decision

Two providers, one model, and the difference is in one method. Azure SQL Database is what the deployed container talks to. SQLite is what a developer and a CI runner get, because neither has an Azure credential and neither is getting one. YardConnection is the only place that knows which is which: it picks the provider, names that provider's migrations assembly, and adds the retry policy a cloud database needs.

The provider is chosen by whether there is a SQL Server to talk to, and a placeholder counts as nothing:

/// <summary>
/// SQL Server when there is a SQL Server connection string to use, SQLite
/// otherwise.
///
/// The `__` test is not decoration. The container's SQL Server setting is
/// substituted at roll time by the deploy, exactly as the Application
/// Insights key is (ADR: Telemetry), and a substitution that fails leaves
/// the literal placeholder `__YARD_SQL_CONNECTION__` behind. Treating a
/// placeholder as absent means a broken deploy falls back to the SQLite
/// path that shipped this site for a week, rather than crash-looping
/// against a connection string that is not one.
/// </summary>
public static YardConnection Choose(string? sqlServer, string sqlite) =>
    !string.IsNullOrWhiteSpace(sqlServer) && !sqlServer.StartsWith("__", StringComparison.Ordinal)
        ? new YardConnection(YardProvider.SqlServer, sqlServer)
        : new YardConnection(YardProvider.Sqlite, sqlite);

/// <summary>
/// Cosmos DB when there is an account endpoint to talk to, and the two
/// relational choices otherwise. The endpoint is a URL and not a credential:
/// the account has no keys, and the container authenticates as its managed
/// identity. The same placeholder rule applies, so a failed substitution at
/// roll time falls through to the next store rather than crash-looping
/// against a string that is not an address (ADR: A second store on Cosmos
/// DB, and what it costs).
/// </summary>
public static YardConnection Choose(string? cosmosEndpoint, string? sqlServer, string sqlite) =>
    !string.IsNullOrWhiteSpace(cosmosEndpoint) && !cosmosEndpoint.StartsWith("__", StringComparison.Ordinal)
        ? new YardConnection(YardProvider.Cosmos, cosmosEndpoint)
        : Choose(sqlServer, sqlite);

Live from api/TheYard.Infrastructure/Shared/Stores/YardConnection.cs, region choose, read from this build at 8003063.

The __ test is the difference between a bad deploy and an outage. The container's SQL setting is substituted at roll time by the deploy exactly as the Application Insights key is, and a substitution that fails leaves the literal __YARD_SQL_CONNECTION__ behind. Reading a placeholder as "no SQL Server here" means a broken deploy falls back to the SQLite path that ran this site for a week, instead of crash-looping against a string that is not a connection string.

Retries, because a serverless database is sometimes asleep:

/// <summary>
/// The provider, its migrations assembly, and on SQL Server the retry policy
/// a cloud database needs.
///
/// `EnableRetryOnFailure` is the difference between a working site and a
/// crash loop here. The database is serverless and auto-pauses after an
/// idle hour; the first connection after a pause wakes it, and while it is
/// waking it answers with a transient error rather than a connection.
///
/// The numbers are a budget, not a preference, and the two of them have to
/// be read together. This runs during startup, before the container serves
/// anything, and the deploy gives a new build five minutes to answer.
///
/// A connect timeout is per attempt, and a connect timeout is transient, so
/// the retry policy multiplies it. The worst case is
/// (1 + maxRetryCount) attempts of <see cref="ConnectSeconds"/>, plus the
/// backoff between them:
///
///   3 attempts x 60s + about 12s of backoff = about 3 minutes.
///
/// That fits inside the deploy's five with the container's own start-up
/// still to pay for. Sixty seconds an attempt covers a serverless resume,
/// which takes thirty to sixty, and three attempts mean the second and
/// third arrive at a database the first one already woke.
///
/// A longer timeout or more retries would outrun the deploy: five attempts
/// of ninety seconds plus backoff is about eight minutes, against a five
/// minute deploy. A database that was genuinely gone would then turn an
/// outage into a failed deploy as well, which is the exact failure the
/// file-backed fallback exists to prevent.
/// </summary>
public DbContextOptionsBuilder Configure(DbContextOptionsBuilder builder) => Provider switch
{
    YardProvider.SqlServer => builder.UseSqlServer(WithResumeBudget(ConnectionString), sql =>
    {
        sql.EnableRetryOnFailure(maxRetryCount: 2, maxRetryDelay: TimeSpan.FromSeconds(6), errorNumbersToAdd: null);
        sql.CommandTimeout(120);
    }),
    YardProvider.Sqlite => builder.UseSqlite(ConnectionString, sqlite => sqlite.MigrationsAssembly(SqliteMigrations)),
    // There is no EF context for the document store; its adapters are in
    // TheYard.Infrastructure.Cosmos and take the SDK directly.
    _ => throw new InvalidOperationException($"{Describe()} is not an Entity Framework provider"),
};
// #region resume-budget
/// <summary>
/// Give a paused serverless database time to wake up, by widening the
/// connect timeout in code rather than in the setting.
///
/// The first roll on Azure SQL failed here, and the exception said exactly
/// where:
///
///   Connection Timeout Expired. The timeout period elapsed during the
///   post-login phase. [Pre-Login] initialization=135; handshake=432;
///   [Login] initialization=1; authentication=4; [Post-Login] complete=29347
///
/// Read the phases. The handshake worked, and the login worked in four
/// milliseconds, so the managed identity is fine and the firewall is fine.
/// What ran out was post-login, at 29.3 seconds against SqlClient's default
/// 30, which is what waking a paused database looks like: the free tier
/// auto-pauses after an idle hour and a resume takes thirty to sixty
/// seconds. A container rolls about once a day, so almost every deploy
/// arrives at a database that is asleep.
///
/// Sixty seconds an attempt, not thirty, and not five minutes. Sixty covers
/// the documented resume range on its own, and the retry policy in
/// <see cref="Configure"/> gives three attempts of it, so the total patience
/// is three minutes and the arithmetic is written down in one place. A
/// database that is genuinely gone still falls through to the file-backed
/// path while the deploy is watching.
///
/// In code rather than in the connection string because the connection
/// string is a setting somebody edits under pressure, and this number has a
/// reason that belongs next to it. An explicit Connect Timeout in the
/// setting still wins: this only fills in a value that was never given.
/// </summary>
public const int ConnectSeconds = 60;

/// <summary>
/// The connection string with room to wait for a serverless database to wake up: fills in
/// <see cref="ConnectSeconds"/> as the connect timeout, and leaves an explicit one alone. Asks the
/// builder whether the caller supplied the keyword rather than whether it knows it, because a typed
/// connection string builder knows every keyword the provider has and would answer yes to all of them.
/// </summary>
/// <param name="connectionString">The connection string as the setting gave it.</param>
public static string WithResumeBudget(string connectionString)
{
    var builder = new SqlConnectionStringBuilder(connectionString);
    // ShouldSerialize, not ContainsKey. A strongly typed connection string
    // builder knows every keyword the provider has, so ContainsKey answers
    // true for all of them whether or not anybody set one, and the first
    // version of this method used it and therefore never widened anything.
    // ShouldSerialize is the one that means "the caller supplied this".
    if (!builder.ShouldSerialize("Connect Timeout"))
    {
        builder.ConnectTimeout = ConnectSeconds;
    }

    return builder.ConnectionString;
}
// #endregion resume-budget

Live from api/TheYard.Infrastructure/Shared/Stores/YardConnection.cs, region configure, read from this build at 8003063.

The database auto-pauses after an idle hour. The first connection after that wakes it, and while it is waking it answers with a transient error rather than a connection. The numbers are a budget rather than a preference: this runs during startup, before the container serves anything, and the deploy gives a new build five minutes to answer, so four retries with a ceiling of eight seconds over a thirty-second connect timeout is about ninety seconds of patience. That is enough for a serverless resume, which takes thirty to sixty, and short enough that a database which is genuinely gone falls through to the path below while the deploy is still watching. Waiting longer would not fix an outage, it would turn it into a failed deploy as well.

The site still comes up when the database does not. ADR: The relational store built that and this did not touch it: YardDatabase.Prepare runs before anything is registered, and if it throws, the composition root registers the JSON file readers and NullBidStore instead. A paused serverless database is now a routine event rather than a hypothetical one, which makes that path more important than it was, not less. The health check names the provider so the Admin tab says which one is serving.

The schema, done properly

Steve scoped this: the current model done properly. Vehicles, photos, accounts, bids. Correct types and lengths, real foreign keys, the indexes the queries actually need, a concurrency token on bids. Nothing speculative.

Every decision below is written in api/TheYard.Database as DDL and mirrored in the EF model, and a conformance test fails the build if the two disagree. The catalogue table, with the reason beside every length:

-- The seed catalogue: 200 vehicles, read whole and in order, once, at startup.
-- SyntheticVehicleSource expands these to 100,000 in memory, so this table is
-- the seed and not the inventory (ADR: The SQL Server backend).
--
-- Every length here was chosen from what the dataset actually holds, with
-- headroom. The longest observed value in the seed is in brackets beside each
-- one. Without a length every column would be nvarchar(max), which cannot be
-- indexed, is stored off-row past 8,000 bytes, and tells a reader nothing about
-- what belongs in it.
CREATE TABLE [dbo].[Vehicles] (
    [Id]                nvarchar(64)    NOT NULL,  -- 36 today; a bid references the synthetic id, which adds six
    [Seq]               int             NOT NULL,  -- where this row sat in the seed file; a table has no order of its own
    [Vin]               varchar(17)     NOT NULL,  -- 17 characters from a defined alphabet, by ISO 3779, so varchar and not nvarchar
    [Year]              int             NOT NULL,
    [Make]              nvarchar(64)    NOT NULL,  -- [10]
    [Model]             nvarchar(64)    NOT NULL,  -- [14]
    [Trim]              nvarchar(64)    NOT NULL,  -- [19]
    [BodyStyle]         nvarchar(32)    NOT NULL,  -- [9]
    [ExteriorColor]     nvarchar(32)    NOT NULL,  -- [16]
    [InteriorColor]     nvarchar(32)    NOT NULL,  -- [12]
    [Engine]            nvarchar(128)   NOT NULL,  -- [25]
    [Transmission]      nvarchar(64)    NOT NULL,  -- [12]
    [Drivetrain]        nvarchar(16)    NOT NULL,  -- [3]
    [OdometerKm]        int             NOT NULL,
    [FuelType]          nvarchar(32)    NOT NULL,  -- [8]
    [ConditionGrade]    decimal(3,1)    NOT NULL,  -- 1.0 to 5.0 to one decimal: compared and displayed, never accumulated, so exact and not float
    [ConditionReport]   nvarchar(1024)  NOT NULL,  -- [143]
    [DamageNotes]       nvarchar(max)   NOT NULL,  -- a JSON array: read and written whole, never queried into
    [TitleStatus]       nvarchar(32)    NOT NULL,  -- [7]
    [Province]          nvarchar(64)    NOT NULL,  -- [16]
    [City]              nvarchar(64)    NOT NULL,  -- [11]
    [AuctionStart]      datetime2(0)    NOT NULL,  -- a local wall-clock instant to the second, with no zone
    [StartingBid]       int             NOT NULL,
    [ReservePrice]      int             NULL,      -- null means no reserve
    [BuyNowPrice]       int             NULL,      -- null means no buy-now
    [Images]            nvarchar(max)   NOT NULL,  -- a JSON array, same reasoning as DamageNotes
    [SellingDealership] nvarchar(128)   NOT NULL,  -- [24]
    [Lot]               nvarchar(32)    NOT NULL,  -- [6]
    [CurrentBid]        int             NULL,      -- null until the first bid
    [BidCount]          int             NOT NULL,
    CONSTRAINT [PK_Vehicles] PRIMARY KEY NONCLUSTERED ([Id])
);
GO

-- The clustered index is the table's physical order, and the only query this
-- table serves is ORDER BY Seq, run once at startup. Putting it on Seq rather
-- than leaving it on the primary key is the difference between reading the rows
-- in order and sorting them every boot.
CREATE UNIQUE CLUSTERED INDEX [IX_Vehicles_Seq] ON [dbo].[Vehicles] ([Seq]);
GO

Live from api/TheYard.Database/Tables/Vehicles.sql, region *, read from this build at 8003063.

The mapping that has to agree with it:

/// <summary>
/// The model for both providers: Identity's tables with their keys
/// narrowed, the catalogue, the photos, the bids and the activity
/// counters, with the SQL Server only parts behind one flag.
/// </summary>
protected override void OnModelCreating(ModelBuilder model)
{
    // Identity's own tables first. Skipping this call is the classic way to
    // get a context that compiles, migrates, and has nowhere to put a user.
    base.OnModelCreating(model);

    // The two providers differ in what they can express, not in what the
    // application means. Everything below that is inside this flag is a
    // SQL Server feature SQLite has no equivalent for.
    bool sqlServer = Database.IsSqlServer();

    // Identity's key columns, narrowed from its default of 450. A clustered
    // index key is capped at 900 bytes on SQL Server and nvarchar(450) is
    // 900 bytes on its own, so Identity's own composite keys are over the
    // cap out of the box: at the default widths PK_AspNetUserTokens is
    // 2,700 bytes and PK_Bids is 1,028, each of which fails an insert on a
    // long enough value. The ids this
    // application creates are GUIDs. The widths here exist to match
    // api/TheYard.Database, which is the authority, and a test holds them
    // to it (ADR: Data first, and the database in source control).
    model.Entity<YardUser>().Property(user => user.Id).HasMaxLength(YardColumns.IdentityKeyLength);
    model.Entity<IdentityRole>().Property(role => role.Id).HasMaxLength(YardColumns.IdentityKeyLength);
    model.Entity<IdentityUserClaim<string>>().Property(claim => claim.UserId).HasMaxLength(YardColumns.IdentityKeyLength);
    model.Entity<IdentityRoleClaim<string>>().Property(claim => claim.RoleId).HasMaxLength(YardColumns.IdentityKeyLength);
    model.Entity<IdentityUserLogin<string>>(login =>
    {
        login.Property(row => row.LoginProvider).HasMaxLength(YardColumns.IdentityKeyLength);
        login.Property(row => row.ProviderKey).HasMaxLength(YardColumns.IdentityKeyLength);
        login.Property(row => row.UserId).HasMaxLength(YardColumns.IdentityKeyLength);
    });
    model.Entity<IdentityUserRole<string>>(role =>
    {
        role.Property(row => row.UserId).HasMaxLength(YardColumns.IdentityKeyLength);
        role.Property(row => row.RoleId).HasMaxLength(YardColumns.IdentityKeyLength);
    });
    model.Entity<IdentityUserToken<string>>(token =>
    {
        token.Property(row => row.UserId).HasMaxLength(YardColumns.IdentityKeyLength);
        token.Property(row => row.LoginProvider).HasMaxLength(YardColumns.IdentityKeyLength);
        token.Property(row => row.Name).HasMaxLength(YardColumns.IdentityKeyLength);
    });

    model.Entity<VehicleRow>(vehicle =>
    {
        vehicle.ToTable("Vehicles");

        // Natural keys throughout. A vehicle already has an id the rest of
        // the system uses, a photo already has a unique file name, and a
        // bid is one per buyer per vehicle by definition, so a surrogate
        // key here would be a second identity to keep in step with the
        // first.
        vehicle.HasKey(row => row.Id);

        // Insertion order, kept explicitly. The synthetic scale-up expands
        // the seed catalogue deterministically from its order, so a set
        // that came back in a different order would be a different hundred
        // thousand vehicles. A table has no order of its own; this column
        // is what makes the answer the same every time.
        vehicle.Property(row => row.Seq).IsRequired();
        vehicle.HasIndex(row => row.Seq).IsUnique();

        // Which index is clustered is not decided here. On SQL Server the
        // physical design belongs to the SQL project, which puts the
        // clustered index on Seq because that is the only column this table
        // is ever read in order of, and leaves the primary key
        // nonclustered. This model maps to that schema and does not build it
        // (ADR: Data first, and the database in source control).

        vehicle.Property(row => row.Id).HasMaxLength(YardColumns.IdLength);

        // Seventeen characters, from a defined alphabet, by ISO 3779. This
        // is the one string in the catalogue whose length and character set
        // are fixed by a standard rather than by whatever the dataset
        // happens to hold, so it is the one that gets varchar rather than
        // nvarchar. Not fixed-length: nchar pads on read, and a padded
        // string would stop matching the record it came from.
        vehicle.Property(row => row.Vin).HasMaxLength(17).IsUnicode(false);

        vehicle.Property(row => row.Make).HasMaxLength(64);
        vehicle.Property(row => row.Model).HasMaxLength(64);
        vehicle.Property(row => row.Trim).HasMaxLength(64);
        vehicle.Property(row => row.BodyStyle).HasMaxLength(32);
        vehicle.Property(row => row.ExteriorColor).HasMaxLength(32);
        vehicle.Property(row => row.InteriorColor).HasMaxLength(32);
        vehicle.Property(row => row.Engine).HasMaxLength(128);
        vehicle.Property(row => row.Transmission).HasMaxLength(64);
        vehicle.Property(row => row.Drivetrain).HasMaxLength(16);
        vehicle.Property(row => row.FuelType).HasMaxLength(32);
        vehicle.Property(row => row.ConditionReport).HasMaxLength(1024);
        vehicle.Property(row => row.TitleStatus).HasMaxLength(32);
        vehicle.Property(row => row.Province).HasMaxLength(64);
        vehicle.Property(row => row.City).HasMaxLength(64);
        vehicle.Property(row => row.SellingDealership).HasMaxLength(128);
        vehicle.Property(row => row.Lot).HasMaxLength(32);

        if (sqlServer)
        {
            // A grade between 1.0 and 5.0 to one decimal place. `float` can
            // represent 2.7 only approximately, and this number is compared
            // and displayed rather than accumulated, so the exact type is
            // the right one. The CLR property stays `double` because that is
            // what the domain record uses and the persistence layer does not
            // get to change the domain's shape.
            vehicle.Property(row => row.ConditionGrade).HasConversion<decimal>().HasPrecision(3, 1);

            // An instant, stored as an instant. The dataset carries it as
            // `2026-04-05T19:00:00`, which is a local wall-clock time with no
            // zone, so `datetime2(0)` is exactly the type for it: seconds
            // precision, no offset, and sortable and comparable in the
            // database rather than only in C#. The property stays a string
            // for the same reason the grade stays a double.
            vehicle.Property(row => row.AuctionStart)
                .HasConversion(YardColumns.AuctionStartToDateTime)
                .HasColumnType("datetime2(0)");
        }

        // DamageNotes and Images are primitive collections: EF stores each
        // as a JSON array in one column. They are read and written whole and
        // never queried into, which is the case a JSON column is for. The
        // day something filters on a damage note is the day this becomes a
        // table, and not before (ADR: The SQL Server backend).
    });

    model.Entity<PhotoRow>(photo =>
    {
        photo.ToTable("Photos");
        photo.HasKey(row => row.File);
        photo.Property(row => row.Seq).IsRequired();
        photo.HasIndex(row => row.Seq).IsUnique();

        photo.Property(row => row.File).HasMaxLength(128);
        photo.Property(row => row.Style).HasMaxLength(32);
        photo.Property(row => row.Title).HasMaxLength(256);
    });

    model.Entity<BidRow>(bid =>
    {
        bid.ToTable("Bids");

        // A bid belongs to a person and to a vehicle, and one person has one
        // standing bid per vehicle, so the pair is the key
        // (ADR: Accounts and per-user bids).
        bid.HasKey(row => new { row.UserId, row.VehicleId });

        // Identity's key column is nvarchar(450) on SQL Server, and a
        // foreign key has to match the column it points at.
        bid.Property(row => row.UserId).HasMaxLength(YardColumns.IdentityKeyLength);
        bid.Property(row => row.VehicleId).HasMaxLength(YardColumns.IdLength);

        // A real foreign key, and the only one this model can honestly
        // carry. Deleting an account takes its bids with it, which is the
        // right answer and is the database's answer rather than something
        // the application has to remember to do.
        bid.HasOne<YardUser>()
            .WithMany()
            .HasForeignKey(row => row.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        // There is deliberately no foreign key from VehicleId to Vehicles.
        // The catalogue in this table is 200 rows expanded in memory to
        // 100,000 by SyntheticVehicleSource, and a visitor bids on the
        // expanded set, so 99.8 per cent of legitimate bids name a vehicle
        // id that has no row here. A constraint would reject them. The
        // constraint becomes correct the day the expansion is persisted,
        // and writing it before then would be writing a rule the
        // application breaks on purpose (ADR: The SQL Server backend).

        // No index on UserId either, and its absence is the point: the
        // primary key is (UserId, VehicleId), so its leading column already
        // answers every "what has this person bid on" query. An index on
        // UserId would be a second copy of the first half of the key,
        // costing a write on every bid and earning nothing.

        // The concurrency token. Two containers, or two requests that get
        // past one container's lock, can both read this row and both decide
        // to write it; the token is what makes the second write fail
        // instead of silently overwriting the first.
        var version = bid.Property(row => row.RowVersion);
        if (sqlServer)
        {
            // SQL Server keeps the token itself: `rowversion` is a database
            // generated, monotonically increasing 8-byte value, and nothing
            // in the application can forget to bump it.
            version.IsRowVersion();
        }
        else
        {
            // SQLite has no rowversion. The token still exists and still
            // fails a stale write; the difference is that the store assigns
            // it, which is why EfBidStore sets one on every save.
            version.IsConcurrencyToken();
        }
    });

    ActivityModel.Configure(model);

    StandingsModel.ConfigureStandings(model);
    CatalogueModel.ConfigureCatalogue(model, sqlServer);
    BlogModel.ConfigureBlog(model, sqlServer);
    CatalogueModel.ConfigureCatalogueWords(model);
}

Live from api/TheYard.Infrastructure/YardDbContext.cs, region model, read from this build at 8003063.

Lengths on every text column this application owns. Without them every string is nvarchar(max) on SQL Server, which cannot be indexed, is stored off-row past 8,000 bytes, and tells a reader nothing about what belongs in it. The lengths were chosen from what the dataset actually holds, with headroom, and the longest observed value in the 200-record seed is in brackets: id 64 (36), make 64 (10), model 64 (14), trim 64 (19), body style 32 (9), colours 32 (16), engine 128 (25), transmission 64 (12), drivetrain 16 (3), fuel type 32 (8), condition report 1024 (143), title status 32 (7), province 64 (16), city 64 (11), dealership 128 (24), lot 32 (6).

The id gets 64 rather than 40 because bids reference the synthetic ids, which are the seed id plus six characters, and a foreign key column and the column it points at have to agree.

A VIN is varchar(17) and it is the only non-Unicode column here. Seventeen characters from a defined alphabet is not a guess about this dataset, it is ISO 3779. It is deliberately not char(17): fixed-length columns pad on read, and a padded VIN stops equalling the one in the record it came from.

A condition grade is decimal(3,1), not a float. It is a number between 1.0 and 5.0 to one decimal place that gets compared and displayed, never accumulated, so the exact type is the right one and float's approximation of 2.7 is not. The CLR property stays double, because that is what the domain record uses and the persistence layer does not get to change the shape of what it stores. A converter sits between them.

An auction start is datetime2(0). The dataset carries it as 2026-04-05T19:00:00, a local wall-clock instant to the second with no zone, which is exactly datetime2(0). It is sortable and comparable in the database now rather than only in C#. The property stays a string for the same reason the grade stays a double, and the converter is asserted to round-trip every one of the 200 rows in the dataset rather than an example:

/// <summary>
/// The catalogue's `auction_start` on the wire is a string and in the
/// database is a `datetime2(0)`. Round-tripping through this converter is
/// exact for every row in the dataset, which a test asserts over all two
/// hundred of them rather than over an example.
/// </summary>
public static readonly Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter<string, DateTime>
    AuctionStartToDateTime = new(
        text => DateTime.ParseExact(text, AuctionStartFormat, System.Globalization.CultureInfo.InvariantCulture),
        moment => moment.ToString(AuctionStartFormat, System.Globalization.CultureInfo.InvariantCulture));

Live from api/TheYard.Infrastructure/Shared/Stores/YardColumns.cs, region auction-start, read from this build at 8003063.

Damage notes and images stay JSON columns. They are read and written whole and never queried into, which is the case a JSON column is for. The day something filters on a damage note is the day this becomes a table.

The catalogue is clustered on the column it is read in order of. A clustered index is the table's physical order, and SQL Server puts it on the primary key by default. The only query this table ever serves is SELECT ... ORDER BY Seq, run once at startup, so Seq is the clustered index here and the primary key on Id is explicitly not. This one is decided in the DDL and nowhere else, because physical design belongs beside the storage and the EF model has no business knowing about it:

CONSTRAINT [PK_Vehicles] PRIMARY KEY NONCLUSTERED ([Id])
CREATE UNIQUE CLUSTERED INDEX [IX_Vehicles_Seq] ON [Vehicles] ([Seq]);

One real foreign key, and one that would be a lie.

A bid now references its account, with a cascade, so deleting an account takes its bids with it and the database is what guarantees that rather than something the application has to remember. Before this, a bid whose account had been deleted stayed in the table forever, was loaded into BidService at every startup, and counted toward a vehicle's standing price on behalf of nobody.

There is deliberately no foreign key from a bid to the catalogue. The Vehicles table holds 200 rows, which SyntheticVehicleSource expands in memory to 100,000 by deriving ids from them, and a visitor bids on the expanded set. A constraint there would reject 99.8 per cent of legitimate bids. It becomes correct the day the expansion is persisted, and a test asserts its absence so that the day it is added, something fails and sends the next person to this paragraph.

One index removed. Bids had an index on UserId and its primary key is (UserId, VehicleId). The leading column of the key already answers every question that index existed for, so it was a second copy of the first half of the key: a write on every bid, and nothing earned.

A concurrency token on bids. Two containers, or two requests that get past one container's lock, can both read a bid row and both decide to write it. The token makes the second write fail instead of silently overwriting the first, which is a lost update, and a lost update on an auction is somebody's money.

On SQL Server it is a real rowversion: 8 bytes, maintained by the database, impossible for the application to forget to move, and declared in Tables/Bids.sql. SQLite has no such type, so the store assigns one on every save and the guarantee is the same with a different owner. The store retries a conflict rather than failing the bid, because the correct answer to "somebody moved this row" is to start again from what is there now:

/// <summary>Every stored bid, for BidBook to replay into its indexes at startup.</summary>
/// <param name="cancellation">The caller's; the replay's starter lends it, and in a container that is the host.</param>
public async Task<IReadOnlyList<StoredBid>> LoadAsync(CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync(cancellation);
    return await db.Bids.AsNoTracking().Select(row => new StoredBid(
            row.UserId,
            row.VehicleId,
            new BidState(row.Amount, row.BidCount, row.WonBuyNow, row.AtMs)))
        .ToListAsync(cancellation);
}

/// <summary>
/// Every buyer's row on one vehicle. There is no index on VehicleId alone,
/// so this reads the table; it runs only when a vehicle has no standing yet,
/// once per vehicle, on a table of visitor bids.
/// </summary>
/// <param name="vehicleId">The vehicle.</param>
/// <param name="cancellation">The caller's. A table read is the most expensive thing this adapter does, so it is the one most worth stopping.</param>
public async Task<IReadOnlyList<StoredBid>> BidsOnAsync(string vehicleId, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync(cancellation);
    return await db.Bids.AsNoTracking().Where(row => row.VehicleId == vehicleId).Select(row => new StoredBid(
            row.UserId,
            row.VehicleId,
            new BidState(row.Amount, row.BidCount, row.WonBuyNow, row.AtMs)))
        .ToListAsync(cancellation);
}

/// <summary>
/// The buyer's row and the vehicle's standing in one transaction, the
/// standing first with a compare-and-set on its version. An update that
/// names a version no longer there changes no row, and an insert where
/// another writer has just inserted breaks the key; either way the
/// transaction is rolled back and the answer is false, so BidBook reads
/// again and runs the rules again. The buyer's row keeps its own token,
/// which catches one account writing from two sites at once, and that too
/// answers false rather than writing over what it never saw.
///
/// <para>The token this takes is never handed to the transaction, and that is the whole of
/// what the ports record promised: a write abandoned between the standing and the buyer's
/// row would leave the store holding a standing nothing has a row for. The caller above
/// has already stopped honouring cancellation by the time it gets here, so the parameter
/// exists to keep one shape across the port and not to be used (ADR: The ports learn to
/// wait, the addendum on the token).</para>
/// </summary>
public async Task<bool> TrySaveAsync(string userId, string vehicleId, BidState state, VehicleStanding standing, string? expectedVersion, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync();
    await using var transaction = await db.Database.BeginTransactionAsync();
    if (expectedVersion is not null && !await TryMoveStandingAsync(db, vehicleId, standing, long.Parse(expectedVersion, CultureInfo.InvariantCulture), CancellationToken.None))
    {
        return false;
    }

    if (expectedVersion is null)
    {
        db.Standings.Add(RowOf(vehicleId, standing, version: 1));
    }

    await StageBuyerRowAsync(db, userId, vehicleId, state, cancellation);
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        return false;
    }
    catch (DbUpdateException) when (expectedVersion is null)
    {
        // An insert that broke a key lost to another writer's insert of the
        // same standing. Anything else that broke is a real failure and
        // travels.
        await transaction.RollbackAsync();
        if (await StandingExistsAsync(vehicleId, CancellationToken.None))
        {
            return false;
        }
        throw;
    }

    await transaction.CommitAsync();
    return true;
}

/// <summary>
/// The buyer's row for this vehicle, added or changed in place on the
/// context, with the concurrency token moved where the provider does not
/// move it itself. Nothing is written until the caller saves.
/// </summary>
private static async Task StageBuyerRowAsync(YardDbContext db, string userId, string vehicleId, BidState state, CancellationToken cancellation)
{
    var existing = await db.Bids.FindAsync(userId, vehicleId);
    if (existing is null)
    {
        existing = new BidRow
        {
            UserId = userId,
            VehicleId = vehicleId,
            Amount = state.Amount,
            BidCount = state.BidCount,
            WonBuyNow = state.WonBuyNow,
            AtMs = state.AtMs,
        };
        db.Bids.Add(existing);
    }
    else
    {
        existing.Amount = state.Amount;
        existing.BidCount = state.BidCount;
        existing.WonBuyNow = state.WonBuyNow;
        existing.AtMs = state.AtMs;
    }

    // SQL Server keeps the token itself; SQLite has no rowversion type, so
    // the store is what moves it. Setting it here rather than in a trigger
    // keeps the difference between the two providers in one readable place
    // (ADR: The SQL Server backend).
    if (db.Database.IsSqlite())
    {
        existing.RowVersion = Guid.NewGuid().ToByteArray();
    }
}

/// <summary>
/// Deletes one buyer's rows, every vehicle, and the standing of each of
/// those vehicles, in one transaction. The standings go so the next bid on
/// each vehicle works its standing out again from whoever is left.
///
/// <para>Like the bid write, the token is not given to the transaction: the standings and
/// the rows go together or not at all, and a reset stopped between them would leave
/// vehicles with no standing and rows still claiming one.</para>
/// </summary>
public async Task ClearAsync(string userId, CancellationToken cancellation)
{
    using var db = await factory.CreateDbContextAsync();
    await using var transaction = await db.Database.BeginTransactionAsync();
    // One person's rows and nobody else's. A delete over the whole table,
    // on a site two strangers can be looking at, would let either of them
    // delete the other's bids (ADR: Reset is one person's start-over).
    var touched = await db.Bids.Where(bid => bid.UserId == userId).Select(bid => bid.VehicleId).ToListAsync();
    await db.Standings.Where(row => touched.Contains(row.VehicleId)).ExecuteDeleteAsync();
    await db.Bids.Where(bid => bid.UserId == userId).ExecuteDeleteAsync();
    await transaction.CommitAsync();
}

Live from api/TheYard.Infrastructure/Shared/Auction/EfSources.cs, region bid-store, read from this build at 8003063.

Identity's own tables were left alone, including the nvarchar(max) columns it uses for password hashes and stamps. Those belong to a framework whose hashing algorithm can change under this application, they hold a handful of rows, and bounding them would trade nothing measurable for a truncation that would only appear on the day somebody upgraded. The test that requires a length on every text column names the three tables this application owns, and says why.

The schema does not come from here

The first version of this work generated the SQL Server schema from the EF model and applied it with migrations at startup. That lasted about an hour, until Steve asked for data first and gave the reason: the data structure outlives the framework that reads it.

So the schema is api/TheYard.Database, a SQL project of hand-written DDL that builds to a DACPAC, and this application maps to it. The decision, what it costs and what enforces it are in ADR: Data first, and the database in source control. What matters here is the shape it leaves behind:

api/TheYard.Database/            the SQL Server schema, the authority, published by SqlPackage
api/TheYard.Migrations.Sqlite/   the SQLite schema's history, applied by the process that uses it

The SQLite history is the same history it has always had, moved out of TheYard.Infrastructure and renamed, carrying the same migration ids, so a database created last week still matches its own __EFMigrationsHistory rows. The SQL Server migrations were deleted rather than kept, because a migrations chain nobody applies is a second definition of the schema waiting to be believed.

One design-time factory still exists, in the host project, because SQLite still has migrations to write:

/// <summary>
/// What `dotnet ef` uses when it needs the model. Without this the tooling boots
/// the application to find a context, which would run the migrate and seed block
/// against a schema that does not exist yet, on the one command whose whole job
/// is to create that schema.
///
/// It lives in the host rather than beside the context because `dotnet ef` looks
/// for a design-time factory in the startup project or in the project it is
/// writing migrations into, and this one has to serve a migrations project that
/// is neither.
///
/// Only SQLite has migrations. The SQL Server schema is the SQL project,
/// published by SqlPackage, and this application maps to it rather than building
/// it (ADR: Data first, and the database in source control), so there is nothing
/// for `dotnet ef` to write on that side:
///
///   dotnet ef migrations add Name --project api/TheYard.Migrations.Sqlite ^
///     --startup-project api/TheYard.Api -- --sqlite
///
/// The argument is still read, because the SQL Server model is what the schema
/// conformance test builds and it has to be reachable the same way:
///
///   dotnet ef dbcontext script --startup-project api/TheYard.Api -- --sqlserver
///
/// Everything after the bare `--` reaches this method as <paramref name="args"/>.
/// The connection strings here are design-time only: `dotnet ef` builds the
/// model from them, it does not connect.
/// </summary>
public sealed class YardDbContextFactory : IDesignTimeDbContextFactory<YardDbContext>
{
    public YardDbContext CreateDbContext(string[] args)
    {
        bool sqlServer = args.Any(arg => string.Equals(arg, "--sqlserver", StringComparison.OrdinalIgnoreCase));
        var connection = sqlServer
            ? new YardConnection(YardProvider.SqlServer, "Server=design-time;Database=design-time;")
            : new YardConnection(YardProvider.Sqlite, "Data Source=design-time.db");
        return new YardDbContext(connection.Options());
    }
}

Live from api/TheYard.Api/Shared/Stores/DesignTime.cs, region design-time, read from this build at 8003063.

What the first publish said, and what it cost to ignore

Publishing the schema warned four times:

Warning! The maximum key length for a clustered index is 900 bytes.
The index 'PK_AspNetUserTokens' has maximum length of 2700 bytes.
The index 'PK_AspNetUserLogins' has maximum length of 1800 bytes.
The index 'PK_AspNetUserRoles' has maximum length of 1800 bytes.
The index 'PK_Bids' has maximum length of 1028 bytes.

That is ASP.NET Core Identity's default key width, nvarchar(450), which is 900 bytes on its own, so any composite key built from two of them is over SQL Server's limit before this application adds anything. It is a real defect and not a style note: an insert of a long enough value fails.

Nothing in the C# would have said so. The model compiles, the tests pass, and the number only appears when a real SQL Server is asked to build the thing. That is an argument for the SQL project rather than against it: the schema got compiled by the engine that has to hold it, and the engine objected.

Identity's key columns are now nvarchar(128) here, which makes the widest composite key 768 bytes. The ids this application creates are GUIDs, so 128 is generous, and the widths are declared in the DDL and asserted against the model by the conformance test.

The fix then failed once, with every step reporting success, and the way it failed is worth more than the fix. The DDL was changed, the solution was built, the tests passed, the schema was published, and the database came back with the same four warnings and the same 450-byte columns. dotnet build on the solution does not build a SQL project. It reported success, the DACPAC on disk was the previous one, and the publish shipped a schema that did not contain the change.

Nothing in that sequence looked wrong. The lesson is the one this repository keeps relearning: the gate is an independent read of the thing itself, not a tool's report about it. The database's own sys.indexes is what said so, and CI now has a database job that builds the SQL project explicitly and fails if no package comes out of it.

Tests that run where there is no Azure

The suite runs in CI, CI has no Azure credential, and it is not getting one. So the SQL Server schema is asserted from the model rather than against a server: each test builds the context with the SQL Server provider, reads the model or generates the CREATE script, and never opens a connection.

[Fact]
public void Every_text_column_this_application_owns_has_a_length()
{
    using var db = SqlServer();
    Type[] mine = [typeof(VehicleRow), typeof(CatalogueRow), typeof(PhotoRow), typeof(BidRow)];

    var unbounded = mine
        .Select(type => db.Model.FindEntityType(type)!)
        .SelectMany(entity => entity.GetProperties()
            .Where(property => IsTextColumn(property) && property.GetMaxLength() is null)
            .Select(property => $"{entity.ShortName()}.{property.Name}"))
        .ToList();

    // The failure message matters more than the assertion here: this test
    // exists to catch the next column somebody adds without a length, and
    // "Assert.Empty() failure" would not tell them which one.
    Assert.True(
        unbounded.Count == 0,
        $"these columns would be nvarchar(max) on SQL Server: {string.Join(", ", unbounded)}");
}

/// <summary>
/// A string in C# that is still a string in the database. AuctionStart is
/// the reason this is a method rather than a type check: the property is a
/// string and the column is a datetime2, so asking it for a length would be
/// asking the wrong question.
/// </summary>
private static bool IsTextColumn(IProperty property) =>
    property.ClrType == typeof(string)
    && (property.GetValueConverter()?.ProviderClrType ?? typeof(string)) == typeof(string);

[Theory]
[InlineData(nameof(VehicleRow.Id), 64)]
[InlineData(nameof(VehicleRow.Vin), 17)]
[InlineData(nameof(VehicleRow.Make), 64)]
[InlineData(nameof(VehicleRow.Drivetrain), 16)]
[InlineData(nameof(VehicleRow.ConditionReport), 1024)]
public void The_catalogue_columns_are_the_length_the_data_needs(string property, int expected)
{
    using var db = SqlServer();

    Assert.Equal(expected, db.Model.FindEntityType(typeof(VehicleRow))!.FindProperty(property)!.GetMaxLength());
}

[Fact]
public void A_vin_is_seventeen_characters_of_ascii_because_a_standard_says_so()
{
    using var db = SqlServer();
    var vin = db.Model.FindEntityType(typeof(VehicleRow))!.FindProperty(nameof(VehicleRow.Vin))!;

    Assert.Equal(17, vin.GetMaxLength());
    Assert.False(vin.IsUnicode());
    // Not fixed length: nchar and char pad on read, and a padded VIN would
    // stop equalling the one in the record it came from.
    Assert.NotEqual(true, vin.IsFixedLength());
}

Live from api/TheYard.Tests/SqlServerModelTests.cs, region lengths, read from this build at 8003063.

That covers the lengths, the types, the keys, the clustering, the token and the absent foreign key. What it cannot cover is whether Azure accepts the DDL, which is proved once, by the container, when it migrates on its first boot, and shows up on the Admin tab if it fails.

The constraints that both providers share are exercised against a real engine, which is SQLite for the same reason it is SQLite in CI:

[Fact]
public void The_second_of_two_writers_that_read_the_same_bid_is_refused()
{
    using (var db = Context())
    {
        db.Bids.Add(ABid());
        db.SaveChanges();
    }

    using var first = Context();
    using var second = Context();
    var mine = first.Bids.Single();
    var theirs = second.Bids.Single();

    mine.Amount = 11_000;
    mine.RowVersion = Guid.NewGuid().ToByteArray();
    first.SaveChanges();

    theirs.Amount = 10_500;
    theirs.RowVersion = Guid.NewGuid().ToByteArray();

    // Without the token this would succeed and the site would show 10,500 on
    // a vehicle that stands at 11,000: a lost update, and the one kind of
    // bug an auction cannot have.
    Assert.Throws<DbUpdateConcurrencyException>(() => second.SaveChanges());
}

[Fact]
public async Task The_store_moves_the_token_on_every_save()
{
    var store = new EfBidStore(new PlainFactory(Connection));

    Assert.True(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(10_000, 1, false, 1), Standing(10_000, 1), null, CancellationToken.None));
    byte[] first = TokenOf();
    Assert.True(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(11_000, 2, false, 2), Standing(11_000, 2), "1", CancellationToken.None));
    byte[] second = TokenOf();

    Assert.NotEmpty(first);
    Assert.NotEqual(first, second);
}

/// <summary>
/// Somebody else moves the buyer's row between this store reading it and
/// writing it. The store answers false and writes nothing, the standing
/// included, so the caller reads again and runs the rules again rather than
/// writing over a bid it never saw.
/// </summary>
[Fact]
public async Task A_conflict_on_the_buyers_row_writes_nothing()
{
    var store = new EfBidStore(new PlainFactory(Connection));
    Assert.True(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(10_000, 1, false, 1), Standing(10_000, 1), null, CancellationToken.None));

    using (var other = Context())
    {
        var standing = other.Standings.Single();
        standing.Amount = 10_800;
        standing.Version = 2;
        other.SaveChanges();
    }

    Assert.False(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(12_000, 3, false, 3), Standing(12_000, 3), "1", CancellationToken.None));

    using var db = Context();
    Assert.Equal(10_000, db.Bids.Single().Amount);
    Assert.Equal(10_800, db.Standings.Single().Amount);
}

/// <summary>Two inserts of the first standing on one vehicle: the second loses and keeps nothing.</summary>
[Fact]
public async Task The_second_first_bid_on_a_vehicle_loses_the_insert()
{
    var store = new EfBidStore(new PlainFactory(Connection));
    Assert.True(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(10_000, 1, false, 1), Standing(10_000, 1), null, CancellationToken.None));
    Assert.False(await store.TrySaveAsync("buyer-1", "veh-1", new BidState(10_500, 1, false, 2), Standing(10_500, 1), null, CancellationToken.None));

    using var db = Context();
    Assert.Equal(10_000, db.Standings.Single().Amount);
    Assert.Equal(1, db.Standings.Single().Version);
}

private static VehicleStanding Standing(int amount, int count) => new(amount, count, "buyer-1", false, count);

Live from api/TheYard.Tests/RelationalConstraintTests.cs, region concurrency, read from this build at 8003063.

What this does not give you

A second replica. One container still writes, and the in-process lock in BidService is still what orders bids. The concurrency token is what would make a second writer safe rather than what makes it exist.

A private network path. The logical server is reachable from Azure services through the AllowAzureServices firewall rule, which is the 0.0.0.0 rule and not an open-internet rule, and the runner's own address is a second rule naming one address. The container group has a public egress address that changes on every roll, so pinning it would break the next deploy. What actually gates access is that the server accepts only Entra tokens, and the only principal with a database user is the container's identity. A private endpoint is the real answer and it is a new resource this project is not authorised to add.

Durable backups anyone has restored. Backup storage is Local redundancy inside the free limit. Nobody has tested a restore.

The review of this diff

Written after the work, before the ship, by reading the whole diff again as somebody who did not write it. Nine things came out of it and all nine are in the tree.

The health check said too much. Describe() returned "SQL Server, tcp:sql-theyard-ss-westus3.database.windows.net,1433", which put a server name in a log and on a public endpoint. It returns "Azure SQL Database" now, and a test asserts that the server, the database name and the user id are all absent from what it returns. There is no secret in today's connection string, but that is a fact about today's configuration and not a property of the method.

The retry policy had no budget. Six retries with a twelve-second ceiling over a sixty-second connect timeout can spend minutes before giving up, and it spends them during startup, inside the five minutes the deploy gives a new build to answer. It is four retries, an eight-second ceiling and a thirty-second connect timeout now, which is about ninety seconds: enough for a serverless resume, short enough that a database which is genuinely gone falls through to the file-backed path while the deploy is still watching.

Two lists of the same four table names, one in an array and one inside the SQL, on a check whose entire job is to notice drift. One list now.

An index that was a second copy of half a key. Bids had an index on UserId and a primary key of (UserId, VehicleId). Removed, with the reason in the DDL where the next person will look.

Identity's key width, which the first publish caught and is described above.

A test helper living inside a test class file. Repo is used by three classes and now has its own.

A gate that read UTF-8 as Windows-1252. The em dash check reported a hit in docs/app-architecture/PROJECTS.md, which has no em dash: 0x97 is the second byte of a UTF-8 multiplication sign and an em dash in that code page. Read as UTF-8 the tree scans clean at 234 files. Worth recording because a gate that reports a false finding is worse than no gate: it teaches people to ignore it.

A build that succeeded without building. dotnet build on the solution does not build a SQL project, so a schema change was published that the package did not contain, and every tool involved reported success. CI has a job that builds the SQL project on its own and fails if no package comes out.

A browser suite that looked broken and was not. Four full runs, one or two failures each, a different test every time, all passing in isolation. The temptation was to call it a flake. Instead the API was started deliberately, kept alive after the suite and asked what it had recorded: /api/vehicles?limit=100 in 328 to 397 ms, /api/errors holding nothing but the test's own probe, every health check green, and 43 of 43 passing in 1.6 minutes. The cause was on the first line of that same log: eight orphaned dotnet processes left by a day of test runs, and a Playwright config that reuses whatever server it finds listening. The failures were a loaded machine, and that sentence is now a measurement rather than a hope.

Addendum, the same day: the release that shipped on SQLite

1.0.0.49 went out with every gate green and came up on SQLite. The site was healthy, /readyz said ready, 42,775 auctions were answering, and the health check said, correctly, "the seed catalogue is in the store (SQLite)".

The container's own first log line settles what happened and what did not:

Database ready: SQLite, migrated in 2477 ms and seeded in 1158 ms,
inserting 200 vehicles and 50 photos, now holding 200 and 50

No error. Nothing failed to connect. YardConnection.Choose was handed the literal __YARD_SQL_CONNECTION__ and read it as "no SQL Server here", which is exactly what that check exists for.

The placeholder survived because the deploy asked Azure for the server's fully qualified name and the deploy's own identity is not allowed to read it. Its role assignments are Container Instances Contributor, AcrPush and Managed Identity Operator, and no reader role anywhere, so az sql server show failed, || true swallowed it, and the substitution had nothing to substitute.

The fix is to stop asking. An Azure SQL logical server's address is its name plus the service suffix; it is not a secret, it does not change, and it does not need a permission. The deploy composes it now.

Two things worth keeping out of this.

The fallback is not theoretical any more. It was written for a paused serverless database and the first thing it actually caught was a deploy misconfiguration. A visitor saw a working site throughout, and nothing about the release was wrong except which store it was reading from.

Silence was the real defect. A roll landing on SQLite must not fail a deploy, because that is the fallback doing its job. It must also not be invisible, and it was: nothing said so until the health endpoint was read by hand. The Verify step now reads /api/health and warns when the container is not on Azure SQL Database. A warning, not a failure, because the difference between those two is the difference between a safety net and a tripwire.

Addendum: the CI failure that could not explain itself

The commit that fixed the deploy went red on CI, and the only thing the run could say was Process completed with exit code 1 followed by No files were found with the provided path: playwright-report/.

Four of the five jobs passed, including the new one: the SQL project compiled on a Linux runner in fifteen seconds, first time, and the style job was untroubled by a .sqlproj sitting in a C# solution. The browser suite failed, on a runner that took 4m19s where a developer's machine takes 2m.

Two things came out of it.

The evidence step had never worked. CI has uploaded playwright-report/ on failure since the suite existed, and Playwright only writes that directory when an html reporter is configured, which it was not. Every failing browser run on a machine nobody can log into has left nothing behind. That is fixed in playwright.config.ts, and it is the more useful of the two fixes: the next failure will be readable.

A fix for one machine was a defect on another. The bid helper introduced hours earlier waited six seconds for the accepted state and treated anything else as a refusal. On a developer's machine that is generous. On a two-core runner it is not, so a slow accept was read as a refusal and three of them threw, turning an intermittent pass into a hard failure. It races the two possible answers against each other now and returns as soon as either appears: fast when the machine is fast, patient when it is not.

The general lesson is the one this record keeps arriving at from different directions. A number chosen against the machine in front of you is a measurement of that machine, not of the thing you are testing.

Consequences

  • A bid survives a deploy, which is the sentence this record exists for. It survived a process restart before; the storage it was written to did not survive the container being replaced, and now it is not in the container.
  • The site has a network dependency it did not have. A paused database is a routine event on this tier, so the file-backed fallback moved from a safety net to something that runs.
  • Local development and CI are unchanged. No connection string, no credential, no Azure. dotnet test behaves exactly as it did.
  • Adding a column is now four edits rather than three: the record, the row, the mapping, and a length on the column. The fourth is enforced by a test.
  • Two migrations projects exist and both have to be kept. A schema change is two dotnet ef migrations add commands, which is written down in the junior record beside this one.
  • The connection string is configuration, so reverting this is removing an environment variable rather than a deploy. aci-export-v11.yaml is untouched.

Addendum, 2026-09-03: the first container that actually connected

Every part of this had been verified separately. The server exists, the schema is published, the identity has exactly two roles, the connection string carries no secret. What had never been observed was a container coming up and saying it was on Azure SQL Database, because 1.0.0.49 shipped on SQLite through the fallback and 1.0.0.50 never reached the site.

1.0.0.51 got there, and failed, and the failure is worth keeping because of how precisely it named itself:

Connection Timeout Expired. The timeout period elapsed during the post-login
phase. The duration spent while attempting to connect to this server was -
[Pre-Login] initialization=135; handshake=432;
[Login] initialization=1; authentication=4;
[Post-Login] complete=29347;

Read the phases before reaching for an explanation. The handshake completed in 432 ms, so the network path and the firewall rule are fine. Login authentication completed in 4 ms, so the managed identity is fine and the contained user exists with the right SID. Everything this ADR was uncertain about had just been proved working, by the error.

What ran out was post-login, at 29.3 seconds against SqlClient's 30. That is not a connection problem. That is a serverless database waking up: the free tier auto-pauses after an idle hour, a resume takes thirty to sixty seconds, and a container rolls about once a day, so nearly every deploy will arrive at a database that is asleep.

The fix is a wider connect timeout, ninety seconds, set in code:

var builder = new SqlConnectionStringBuilder(connectionString);
// ShouldSerialize, not ContainsKey. A strongly typed connection string builder
// knows every keyword the provider has, so ContainsKey answers true for all of
// them whether or not anybody set one.
if (!builder.ShouldSerialize("Connect Timeout"))
{
    builder.ConnectTimeout = 90;
}

Three things about that, each of which was a choice:

Ninety, not five minutes. A resume that has not finished in ninety seconds is not a resume. The deploy gives a new build five minutes to answer, and the fallback to files has to still happen while the deploy is watching, or an outage becomes a failed deploy as well.

In code, not in the setting. The connection string is a value somebody edits under pressure at two in the morning. This number has a reason, and the reason belongs beside it. An explicit timeout in the setting still wins.

One attempt, not a retry loop. Retrying a ninety-second attempt four times is six minutes, which is longer than the deploy will wait. The retry policy that is already here covers transient errors during commands; the cold connect gets one honest budget.

One thing worth knowing, because it surprised the test: the builder rewrites what it is handed. Server comes back as Data Source, User Id as User ID, and Active Directory Managed Identity as ActiveDirectoryManagedIdentity. The driver accepts either spelling, so the setting the deploy writes and the string the driver receives are equivalent without being identical. The test reads the values back as values for exactly that reason.

The first version of that method used ContainsKey and therefore never widened anything, which the test caught before it shipped. That is the second time on this work that a check answered an easier question than the one it was written for, and it is recorded in ADR: The exemption that hid a contrast failure.

Addendum, 2026-09-08: the connection string that is not a credential, on the other store too

This record's first section, on authenticating as a managed identity with no password anywhere, is now the rule for two stores. The Cosmos DB account created on 2026-09-08 has local authentication disabled, so it has no keys at all, and the same identity id-theyard-ss holds a data-plane role on it; the container's configuration for that store is an account endpoint, which is a URL. What was a property of one connection string is a property of the project (ADR: A second store on Cosmos DB, and what it costs).

Two of this record's decisions have a different answer on the document store, and the difference is written where it is made rather than here:

  • The concurrency token. rowversion on SQL Server and a store-assigned token on SQLite; on Cosmos DB the document's own _etag, sent back as If-Match on every replace. The retry loop in the bid store is the same three tries on both sides (ADR: The partition key; ADR: Accounts on a document store).
  • "The schema does not come from here." The rule holds, and the artifact is different: a container definition rather than a SQL project, and a data-plane role that cannot create a container rather than a database user without db_ddladmin (ADR: Data first, and the database in source control, addendum).

The YardProvider enumeration this record introduced has a third member, Cosmos, chosen by the presence of an endpoint, and Configure throws for it because there is no Entity Framework provider behind it.

Where it sits

YardConnection in Infrastructure is the one class that knows which engine sits underneath, and the schema it maps to lives in TheYard.Database beside the onion. Single responsibility gives that choice one home, and Liskov substitution is why it works: the same EF adapters behind IVehicleSource and IBidStore run on SQL Server or SQLite, and the inner rings cannot tell which. It cost a second Azure region for the database, a length on every new text column and, since the free month ran out on 14 September, $4.90 a month for the Basic tier. A second writer or a private network path would change the design, because both need resources this project was not cleared to add.

Files

Addendum, 2026-09-14: the free month ran out on the fourteenth, and the Basic tier beside it

The free limit this record priced, 100,000 vCore-seconds a month with freeLimitExhaustionBehavior: AutoPause, was reached on 14 September at 06:40 UTC. Read off the database itself: status Paused, pausedDate 2026-09-14T06:40:21Z, useFreeLimit true, exhaustion AutoPause; and off the container's log, error 42119, "This database has reached the monthly free amount allowance for the month of September 2026 and is paused for the remainder of the month." At 0.5 vCore minimum a database that never pauses spends 1,800 vCore-seconds an hour, so the allowance is fifty-five awake hours; the site activity collector (ADR: Site activity, and the line an address does not cross) had been writing it a batch every five seconds since the thirteenth, and an idle hour never came.

The consequences were the ones this record predicted for a store that does not come up, and they were visible: the catalogue served from the files, bids that would not outlive the process, accounts, sign-in and the password reset answering 503, the health card reading database=fail. The document store's site was untouched.

Steve's pick, from three priced: the Basic tier this record listed at $4.90 a month, a new database sqldb-theyard-ss-basic on the same Entra-only server, 5 DTU, 2 GB, local backup, created and read back Online at 09:10 CDT; the container's identity given its contained user from its SID and the same two roles, db_datareader and db_datawriter, read back from sys.database_principals; the schema published from the SQL project by SqlPackage with no warnings, twelve tables read back. The deploy's SQL_DB names it, so the next roll connects there and the store fills itself from the seed files on first boot. The paused database is left as it is; it resumes on the first of October and nothing points at it. The other two options were the portal's one-way "continue with additional charges" on the paused database, serverless at about $0.26 an awake hour, and leaving the site browse-only until October for nothing. The second site's deploy carries its own copy of the database name, and it was missed on the first roll: 1.0.0.128 read database=pass on the SQL site and database (sql)=fail on the Cosmos DB site, whose warm second store still named the paused database; 1.0.0.129 is that one line. The activity collector no longer writes here at all, which is what keeps a Basic database from mattering to the bill in a way the free one could not afford.

Store

The Yard, home

Welcome to The Yard

Steven Stout’s showcase: a used-vehicle auction on .NET and React, built and run by one engineer, live on Azure, with its decisions written down.

Answering in

unread

0255075100

server time at p95, the usual five minutes of the last day.

Azure this month

unread

the bill, by resource, read from Azure. The Admin tab shows every line.

Since the last roll

unread

 

rolled blue and green, with no downtime.

People who have visited

unread

30 days agotoday

Scanners, my own tools' and my own reads left out.

  •    
  •    
  •    

Tech stack

Read from the build, never typed by hand

What TheYard runs on

What we use, where it runs, the version as built, and the file that proves it.

  • .NET 1014 projects
  • React 19.3TypeScript 7.0
  • Azure6 Bicep files
  • 9GitHub Actions workflows
LayerWhat we useWhere it runsVersionProof
Backendthe API
Backendthe API.NET and ASP.NET CoreApp Service, the API container10.0TheYard.Api.csproj
Entity Framework Core (SQL Server)App Service, talking to Azure SQL10.0.12TheYard.Infrastructure.csproj
Identity with JWT bearer authApp Service, the API container10.0.12TheYard.Api.csproj
OpenAPI with Scalar API docsApp Service, /api docs2.17.13TheYard.Api.csproj
Frontendthe page
Frontendthe pageReactYour browser19.3package.json
TypeScriptYour browser7.0package.json
ViteBuild step, in the container image8.3package.json
Server-side rendering on NodeApp Service, the renderer container24render/Dockerfile
Datatwo stores, one port
Datatwo stores, one portAzure SQL DatabaseAzure, managed SQLEF Core 10.0.12TheYard.Infrastructure.csproj
Azure Cosmos DBAzure, NoSQLSDK 3.63.2TheYard.Infrastructure.Cosmos.csproj
Azureinfrastructure as code
Azureinfrastructure as codeApp Service and Container Registry (Linux container)Azure App Service and ACRlive resourceappservice.bicep
Front Door (written, switched off)Off: the subscription refuses Front Door, so the Netlify edge stands in front todayoffmain.bicep
Application Insights with OpenTelemetryAzure Monitor1.6.0TheYard.Api.csproj
Communication Services emailAzure Communication Services1.1.0TheYard.Api.csproj
Azure Functions, Flex Consumption (clock, snapshot, tracker merger, Sheet mirror, ATS sweep, daily card)func-jobhunt-ss, Flex Consumption.NET 10 isolatedJobHunt.Functions.csproj
Bicep for every resource it deploysDeployed from the repository6 Bicep filesinfra/
Edge
EdgeNetlify edge in front of the siteNetlify edge networklive resourcenetlify.toml
Quality gatesevery push
Quality gatesevery pushxUnitGitHub Actions runners2.9.3TheYard.Tests.csproj
NetArchTest architecture rulesGitHub Actions runners1.4.5TheYard.Tests.csproj
VitestGitHub Actions runners5.0package.json
Playwright with axe accessibility checksGitHub Actions runners, against the built site1.63package.json
oxlint and PrettierGitHub Actions runners1.86 and 3.9package.json
Delivery and AIhow it ships
Delivery and AIhow it shipsGitHub Actions (azure-read, blog-post, ci, deploy-function, deploy-render, deploy, edge-branch, gate, nightly)GitHub9 workflowsworkflows/
Built with Claude Code under the test gateDeveloper machine, then the gateevery pushgate.yml
Claude Code on GitHub Actions runs the job hunt's nightly lanesGitHub Actions runners, on the Azure share4 AM and every 4 hnightly.yml

Generated, not typed. A build step reads the project files, package.json, the Dockerfiles, the Bicep and the workflows; a test fails CI when this table drifts from the repository.

How it is built

How it is run

Who and why