# ADR: The SQL Server backend
Status: accepted, 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:
```sql
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:
```csharp
///
/// 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.
///
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);
///
/// 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).
///
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/YardConnection.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/YardConnection.cs#L44-L73), region `choose`, read from this build at 1d6b50b.*
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:**
```csharp
///
/// 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 , 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.
///
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
///
/// 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
/// 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.
///
public const int ConnectSeconds = 60;
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/YardConnection.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/YardConnection.cs#L77-L170), region `configure`, read from this build at 1d6b50b.*
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:
```sql
-- 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`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Database/Tables/Vehicles.sql#L1-L50), region `*`, read from this build at 1d6b50b.*
The mapping that has to agree with it:
```csharp
///
/// 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.
///
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().Property(user => user.Id).HasMaxLength(IdentityKeyLength);
model.Entity().Property(role => role.Id).HasMaxLength(IdentityKeyLength);
model.Entity>().Property(claim => claim.UserId).HasMaxLength(IdentityKeyLength);
model.Entity>().Property(claim => claim.RoleId).HasMaxLength(IdentityKeyLength);
model.Entity>(login =>
{
login.Property(row => row.LoginProvider).HasMaxLength(IdentityKeyLength);
login.Property(row => row.ProviderKey).HasMaxLength(IdentityKeyLength);
login.Property(row => row.UserId).HasMaxLength(IdentityKeyLength);
});
model.Entity>(role =>
{
role.Property(row => row.UserId).HasMaxLength(IdentityKeyLength);
role.Property(row => row.RoleId).HasMaxLength(IdentityKeyLength);
});
model.Entity>(token =>
{
token.Property(row => row.UserId).HasMaxLength(IdentityKeyLength);
token.Property(row => row.LoginProvider).HasMaxLength(IdentityKeyLength);
token.Property(row => row.Name).HasMaxLength(IdentityKeyLength);
});
model.Entity(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(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().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(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(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(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(IdentityKeyLength);
bid.Property(row => row.VehicleId).HasMaxLength(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()
.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();
}
});
// #region activity-model
// The activity counters (ADR: Site activity, and the line an address
// does not cross). Natural keys again: an hour on a store is one row,
// a visitor token on a store on a day is one row. The counters move by
// ExecuteUpdate so two containers writing the same Azure SQL row add
// rather than overwrite; the paths column is a JSON object the store
// reads, merges and writes back, bounded at twenty entries, and the
// one place a batch can lose to another batch, which the record says.
model.Entity(hour =>
{
hour.ToTable("ActivityHours");
hour.HasKey(row => new { row.Store, row.Hour });
hour.Property(row => row.Store).HasMaxLength(StoreKeyLength);
hour.Property(row => row.Paths).HasMaxLength(PathsLength);
});
model.Entity(visitor =>
{
visitor.ToTable("ActivityVisitors");
visitor.HasKey(row => new { row.Store, row.Day, row.Visitor });
visitor.Property(row => row.Store).HasMaxLength(StoreKeyLength);
// yyyy-MM-dd, and nothing else is ever written here.
visitor.Property(row => row.Day).HasMaxLength(10);
// The token: the first 16 bytes of a keyed hash, as 32 hex characters.
visitor.Property(row => row.Visitor).HasMaxLength(32);
// Three octets and an x, or the same shape for an address that
// could not be read: never four octets.
visitor.Property(row => row.Network).HasMaxLength(NetworkLength);
visitor.Property(row => row.Paths).HasMaxLength(PathsLength);
visitor.Property(row => row.Sources).HasMaxLength(PathsLength);
});
// #endregion activity-model
}
```
*Live from [`api/TheYard.Infrastructure/YardDbContext.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/YardDbContext.cs#L49-L280), region `model`, read from this build at 1d6b50b.*
**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:
```csharp
///
/// 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.
///
public static readonly Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter
AuctionStartToDateTime = new(
text => DateTime.ParseExact(text, AuctionStartFormat, System.Globalization.CultureInfo.InvariantCulture),
moment => moment.ToString(AuctionStartFormat, System.Globalization.CultureInfo.InvariantCulture));
```
*Live from [`api/TheYard.Infrastructure/YardDbContext.Columns.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/YardDbContext.Columns.cs#L37-L46), region `auction-start`, read from this build at 1d6b50b.*
**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:
```sql
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:
```csharp
/// Every stored bid, for BidService to replay into its indexes at startup.
public async Task> LoadAsync()
{
using var db = await factory.CreateDbContextAsync();
return await db.Bids.AsNoTracking()
.Select(row => new StoredBid(
row.UserId,
row.VehicleId,
new BidState(row.Amount, row.BidCount, row.WonBuyNow, row.AtMs)))
.ToListAsync();
}
///
/// One row per buyer per vehicle, replaced rather than appended. Called
/// inside BidService's lock, which is what makes "read the row, decide,
/// write the row" safe here without the database needing an opinion about
/// it.
///
public async Task SaveAsync(string userId, string vehicleId, BidState state)
{
// Three tries, then the exception travels. A conflict here means
// another writer moved this same buyer's row between the read and the
// write, which takes the one account bidding from two containers at
// once. The retry reads the row again and writes this bid over it: the
// rules already ran on this side against the standing this container
// held, and what the row held in between is not re-examined. Two buyers
// racing each other are two rows and never meet here: the store keeps no
// per-vehicle standing, so the compare-and-set standing that would join
// them is a decision still open (ADR: Three readers with no memory of the project). A fresh
// context per attempt, because a context that has just thrown a
// concurrency exception is holding the values that lost.
for (int attempt = 1; ; attempt++)
{
try
{
await WriteAsync(userId, vehicleId, state);
return;
}
catch (DbUpdateConcurrencyException) when (attempt < 3)
{
// Deliberately empty. The retry is the handling.
}
}
}
///
/// One attempt at the write: insert the buyer's row for this vehicle, or
/// update it in place, on a fresh context, and move the concurrency token
/// where the provider does not move it itself.
///
private async Task WriteAsync(string userId, string vehicleId, BidState state)
{
using var db = await factory.CreateDbContextAsync();
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();
}
await db.SaveChangesAsync();
}
/// Deletes one buyer's bids, every vehicle, in one statement.
public async Task ClearAsync(string userId)
{
using var db = await factory.CreateDbContextAsync();
// 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).
await db.Bids.Where(bid => bid.UserId == userId).ExecuteDeleteAsync();
}
```
*Live from [`api/TheYard.Infrastructure/EfSources.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/EfSources.cs#L54-L149), region `bid-store`, read from this build at 1d6b50b.*
**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:
```csharp
///
/// 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 .
/// The connection strings here are design-time only: `dotnet ef` builds the
/// model from them, it does not connect.
///
public sealed class YardDbContextFactory : IDesignTimeDbContextFactory
{
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/DesignTime.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Api/DesignTime.cs#L7-L45), region `design-time`, read from this build at 1d6b50b.*
### 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.
```csharp
[Fact]
public void Every_text_column_this_application_owns_has_a_length()
{
using var db = SqlServer();
Type[] mine = [typeof(VehicleRow), 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)}");
}
///
/// 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.
///
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`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Tests/SqlServerModelTests.cs#L69-L124), region `lengths`, read from this build at 1d6b50b.*
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:
```csharp
[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(() => second.SaveChanges());
}
[Fact]
public async Task The_store_moves_the_token_on_every_save()
{
var store = new EfBidStore(new PlainFactory(Connection));
await store.SaveAsync("buyer-1", "veh-1", new BidState(10_000, 1, false, 1));
byte[] first = TokenOf();
await store.SaveAsync("buyer-1", "veh-1", new BidState(11_000, 2, false, 2));
byte[] second = TokenOf();
Assert.NotEmpty(first);
Assert.NotEqual(first, second);
}
[Fact]
public async Task The_store_gets_past_a_conflict_it_did_not_cause()
{
var store = new EfBidStore(new PlainFactory(Connection));
await store.SaveAsync("buyer-1", "veh-1", new BidState(10_000, 1, false, 1));
// Somebody else moves the row between this store reading it and writing
// it. The store's retry starts again from what is there now, which is
// the only correct answer: the alternative is overwriting a bid that
// was already accepted.
using (var other = Context())
{
var row = other.Bids.Single();
row.Amount = 10_800;
row.RowVersion = Guid.NewGuid().ToByteArray();
other.SaveChanges();
}
await store.SaveAsync("buyer-1", "veh-1", new BidState(12_000, 3, false, 3));
using var db = Context();
Assert.Equal(12_000, db.Bids.Single().Amount);
}
```
*Live from [`api/TheYard.Tests/RelationalConstraintTests.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Tests/RelationalConstraintTests.cs#L94-L157), region `concurrency`, read from this build at 1d6b50b.*
## 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:
```csharp
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
- [`api/TheYard.Infrastructure/YardConnection.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/YardConnection.cs): the provider choice, the retry policy, and what may be said about the database.
- [`api/TheYard.Infrastructure/YardDbContext.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/YardDbContext.cs): the model, both providers, and the value converters.
- [`api/TheYard.Infrastructure/Rows.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/Rows.cs): the row types, and the concurrency token.
- [`api/TheYard.Infrastructure/EfSources.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/EfSources.cs): the adapters, the seed, and the store that retries a conflict.
- [`api/TheYard.Api/DesignTime.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/DesignTime.cs): the one design-time factory, and the two commands that use it.
- [`api/TheYard.Database`](https://github.com/SteveStout/TheYard/tree/main/api/TheYard.Database): the SQL Server schema, hand written, and the authority for it.
- [`api/TheYard.Migrations.Sqlite`](https://github.com/SteveStout/TheYard/tree/main/api/TheYard.Migrations.Sqlite): the SQLite schema's history.
- [`api/TheYard.Tests/SchemaConformanceTests.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Tests/SchemaConformanceTests.cs): what holds the mapping to the schema.
- [`api/TheYard.Tests/SqlServerModelTests.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Tests/SqlServerModelTests.cs): the schema, asserted without a SQL Server.
- [`api/TheYard.Tests/RelationalConstraintTests.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Tests/RelationalConstraintTests.cs): the foreign key and the token, exercised against an engine.
- [`infra/aci-theyard.yaml`](https://github.com/SteveStout/TheYard/blob/main/infra/aci-theyard.yaml): the setting, and the placeholder that is not a connection string.
- [`.github/workflows/deploy.yml`](https://github.com/SteveStout/TheYard/blob/main/.github/workflows/deploy.yml): where the setting is built, at roll time, from the resource itself.
- [`docs/decisions/ADR-040-database-source-control.md`](https://github.com/SteveStout/TheYard/blob/main/docs/decisions/ADR-040-database-source-control.md): the SQL project, why it is the authority, and what enforces that.
- [`docs/decisions/ADR-041-two-providers-explained.md`](https://github.com/SteveStout/TheYard/blob/main/docs/decisions/ADR-041-two-providers-explained.md): the same setup, walked at a new developer's level.
## 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.