# ADR: The relational store **Read this first: the store described below is no longer the deployed one.** This record is the decision to have a relational store at all, and it is still the reason the ports, the row types, the seeding and the fallback are shaped the way they are. What changed later the same day is the engine underneath it and who owns the schema: the deployed site is on Azure SQL Database, and the schema is a SQL project rather than something Entity Framework creates. That is ADR: The SQL Server backend and ADR: Data first, and the database in source control. Everything below is left as it was written rather than edited into agreement with today. A record that is edited until it reads as true again, with no note of the edit, is worse than one that says when it stopped being true, and the measurements in here are measurements of SQLite and should keep saying so. Where a sentence is in the present tense and no longer is, read it as "as of 1.0.0.43". The one paragraph worth carrying forward without a caveat is the fallback. A container that cannot reach its store serves the catalogue from files and says so on the health check. That was built here, it is unchanged, and on 2026-09-03 it was the only reason a release that shipped with a misconfigured connection string was invisible to everyone using the site. --- Status: accepted, 2026-09-03, shipped as 1.0.0.43. Steve asked for the in-memory or file-backed data layer to be replaced by a real relational store behind the existing ports, with migrations, seeding, and bids that survive a restart. ## In plain words This page is the decision to keep the vehicle catalogue and the bids in a real database (SQLite through EF Core) behind the existing ports, so a bid survives a restart. If the database cannot be opened, the site serves the catalogue from files and says so on the health check. What that is worth: a developer can change where data is kept without touching the bidding rules, and the organization keeps its bids through a restart, with a site that degrades instead of going down when storage fails. ## Context Two things were being kept in places that could not keep them. The catalogue was `data/vehicles.json`, read once at startup by `JsonFileVehicleSource` and expanded to a hundred thousand records by a decorator. That is a perfectly good arrangement for a fixed dataset and it was never the problem. The bids were the problem. `BidService` held them in a `ConcurrentDictionary` and its own comment admitted what that meant: "held in API memory (this is an isolated demo; a real system would persist per-user bids)". Every container roll erased every bid on the site. A deploy is a roll, so an auction site that shipped twice a day forgot its auction twice a day. ## Decision **SQLite through EF Core, in the Infrastructure layer, behind the ports that already existed.** `IVehicleSource` and `IPhotoManifestSource` are unchanged. `EfVehicleSource` implements the first one, the synthetic scale-up still decorates it, and nothing in Application or Domain learned that a database exists. The one seam that is new is `IBidStore`, which is a port for the same reason the other two are: `BidService` decides the rules and does not get to know where the answer is kept. **Row types, not domain records.** `TheYard.Data.Vehicle` is a sealed record with init-only members and `IReadOnlyList` collections. Those are the right choices for a value the whole application passes around and the wrong ones for something a mapper builds a field at a time, so `VehicleRow` is its own class and `VehicleRows` holds the mapping in one place. The cost is a dull file that has to be kept complete. The benefit is that the domain record never has to become whatever EF finds convenient. **A `Seq` column, because a table has no order.** 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 and a different set of test expectations. The column records where each row sat in the seed file and every read is ordered by it. **Natural keys and no indexes.** A vehicle already has an id, a photo already has a unique file name, and a bid is one per vehicle by definition. There are no indexes beyond the keys and the uniqueness of `Seq`, because there are no queries: both catalogue tables are read whole, once, at startup, and every filter and sort after that runs in memory. An index here would cost writes at seed time and earn nothing. **Read once, write through, store first.** The catalogue is read at startup into `InventoryService`'s `Lazy`, and the bids are read at startup into `BidService`'s dictionary. Bidding writes to both the dictionary and the store, inside the lock that was already there, and the order is the store and then the dictionary. The other order shipped first and is wrong in a way that looks harmless: a store that throws would leave the dictionary holding a bid the caller had just been told had failed, displayed as winning until the next restart deleted it. Writing the store first means a failed write is a bid that did not happen anywhere, which is the answer the caller already has. This is the only shape that works: the bid overlay runs over a hundred thousand vehicles on a listing request, so a per-row query would not be a slower feature, it would be no feature. **Migrations, not `EnsureCreated`.** The schema's history is a set of files in this repository, applied by `Database.Migrate()` at startup, so a container starting against an older database brings it forward instead of finding a shape it half recognises. A test asserts that `GetAppliedMigrations()` is not empty, which is the difference the two approaches leave behind. **Seeded from the files that used to be the catalogue.** First boot fills the tables from `JsonFileVehicleSource` and `JsonFilePhotoManifestSource`. That keeps `npm run data` the way the dataset is regenerated and means a fresh database cannot drift from the file it came from. The check is whether the table is empty, not whether the file is new, so a process that died halfway through seeding is not left half seeded forever. **The connection string is configuration, and its absence is a scratch file.** `ConnectionStrings:Yard` names the database. Without it the process gets a uniquely named file in the temp directory, logs a warning saying so, and deletes it on shutdown. That is what every test wants, and it is a better answer for a misconfigured deploy than writing, with no warning, somewhere nobody thinks to look. The container sets it to `/app/state/yard.db`, in a directory the Dockerfile creates and chowns because `/app` itself belongs to root. ## The numbers Measured on the development machine, each side built before it was measured, the request timings being the median of four runs against a warm process. | | before, JSON files | after, SQLite | | --- | --- | --- | | cold start to `/healthz`, empty database | 3,137 ms | 4,539 ms | | cold start, database already seeded | | 3,984 ms | | of which migrating | | 635 ms | | of which seeding 200 vehicles and 50 photos | | 439 ms | | a 100-vehicle listing | 264 ms | 265 ms | | a text search over 100,000 records | 265 ms | 256 ms | | API suite, test execution | 8 s | 17 s | | API suite, wall clock | 17 s | 24 s | Three things to read out of that. **The request path did not move**, which is the whole point of reading the catalogue once at startup. The database is not on the path a visitor takes. **A cold container costs about 1.4 seconds more.** Roughly a second of it is creating the schema and half a second is inserting 250 rows. Every container roll pays it, because nothing is mounted and the database starts empty each time. **The API suite roughly doubled**, from eight seconds of execution to seventeen, because every test class now boots an application that migrates and seeds its own scratch database. That is a real cost and it is the one thing here worth revisiting: seeding once into a template file and copying it per class would recover most of it. It has not been done, because eight seconds of CI time is not yet worth the machinery. One measurement went wrong on the way, and the wrong explanation for it turned out to be worth as much as the right one. A shell probe reported the database file as 4,096 bytes, one SQLite page, immediately after a boot that had demonstrably served a hundred thousand vehicles out of it. That reads as "the data never reached disk", which would make the whole feature theatre. The first explanation was stale directory metadata, which is a real Windows behaviour and was wrong here: reading the length through an open `FileStream` gave the same 4,096. The file's own header settles it. SQLite stores its page size at byte 16 and its page count at byte 28, and the header said one page of four kilobytes, so the database really was one page. The answer had been sitting next to it the whole time, and a directory listing shows it: | file | bytes | | --- | --- | | `yard.db` | 4,096 | | `yard.db-wal` | 313,152 | | `yard.db-shm` | 32,768 | SQLite is running in write-ahead logging mode. Committed rows go to the log first and are checkpointed into the database proper later. A clean shutdown checkpoints and the main file grows; a process killed from outside does not, and leaves a one-page database beside a three-hundred-kilobyte log. Nothing is lost either way, because the next open replays the log, which is why the restart test passes and always did. The test that asks about the file on disk now counts both, since between them they are the data. The record kept the wrong explanation for about ten minutes. It is written down here rather than replaced without a trace because "the first explanation that fits" is the most expensive habit in debugging, and this is a cheap example of it. ## What happens when the store will not open The container runtime on the development machine was not running the night this shipped, so the image could not be built and run before CI built it. The failure being risked was specific: a container that cannot create `/app/state/yard.db` throws during startup, exits, gets restarted, and crash-loops. The site would be down until somebody rolled it back. The answer was not a better guess about file permissions. `YardDatabase.Prepare` runs before anything is registered, and if it throws, the composition root registers the JSON file readers and `NullBidStore` instead. Those were the production path until this record existed, so the inventory, the filters, the photos and the bidding all keep working; the only thing lost is that bids stop outliving the process, which is exactly where this site was an hour earlier. The failure is loud rather than silent. The exception's type and message go to the log as an error, and the health check on the Admin tab turns red saying the catalogue is being served from files. The health check does not repeat the exception's message, and the first version did. `/api/health` is public on purpose, and a storage failure's message is usually a filesystem path: handing that to anonymous callers is the same "map of the inside of the process" that `ProblemHandler.ServerDetail` exists to refuse. The reason belongs in the log, which is where the trace id sends you anyway. The catch is deliberately over `Exception`, which is usually a smell. It is not one here: the whole purpose of the block is that the caller keeps serving whatever went wrong, and a narrower catch would be a list of the failures somebody thought of. ## What this does not give you **Durability across a container roll.** `/app/state` is the container's own writable layer, and nothing is mounted there. A bid survives a process restart, which is what the test proves and what the record claims. It does not survive the container group being replaced, which is what a deploy does. Fixing that means an Azure Files share mounted at `/app/state`, which is a new resource in a subscription this project is deliberately not allowed to add to, so it is written down here rather than added without permission. That is the honest shape of it: the persistence layer is real, the storage it writes to is not durable, and the two are separate problems with separate costs. **Concurrent writers.** One container, one SQLite file, one lock around bidding. A second replica would need the file to be somewhere both could reach and SQLite is the wrong answer at that point. This is the version that matches the deployment, and the deployment is one container by choice (ADR: Deployment strategy). ## In the code Bringing the store up, or reporting that it could not be brought up (`api/TheYard.Infrastructure/EfSources.cs`): ```csharp /// /// Opens the database, brings the schema up or checks it is there, seeds an /// empty catalogue, and times both halves. Never throws: a failure comes /// back as a state that is not ready, with the exception attached for the log. /// public static async Task PrepareAsync( YardConnection connection, IVehicleSource seedVehicles, IPhotoManifestSource seedPhotos) { try { using var db = new YardDbContext(connection.Options()); // Both halves are timed because both are new work on every cold // start, and a container that takes longer to answer its first // request is a cost this change has to be able to state. var migrating = System.Diagnostics.Stopwatch.StartNew(); string schemaNote = await BringSchemaUpAsync(db, connection); migrating.Stop(); var seeding = System.Diagnostics.Stopwatch.StartNew(); var seeded = await YardSeed.EnsureSeededAsync(db, seedVehicles, seedPhotos); seeding.Stop(); return new DatabaseState( true, $"{connection.Describe()}, {schemaNote} in {migrating.ElapsedMilliseconds} ms " + $"and seeded in {seeding.ElapsedMilliseconds} ms, " + $"inserting {seeded.VehiclesInserted} vehicles and {seeded.PhotosInserted} photos, " + $"now holding {seeded.VehiclesTotal} and {seeded.PhotosTotal}") { SchemaMs = migrating.ElapsedMilliseconds, SeedMs = seeding.ElapsedMilliseconds, }; } catch (Exception ex) { // Deliberately every exception. The caller's job is to keep serving // without a store, and it cannot do that if this throws. What went // wrong travels back as a sentence, is logged as an error, and shows // up as a failed health check on the Admin tab. // The type, not the message. See DatabaseState.Note: the message // goes back as the exception so a log can have it and a public page // cannot. return new DatabaseState(false, $"{connection.Describe()}: {ex.GetType().Name}", ex); } } ``` *Live from [`api/TheYard.Infrastructure/YardDatabase.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/YardDatabase.cs#L17-L63), region `prepare`, read from this build at 1d6b50b.* The model, and why the ordering column exists (`api/TheYard.Infrastructure/YardDbContext.cs`): ```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.* The seam, unchanged above it (`api/TheYard.Infrastructure/EfSources.cs`): ```csharp /// Every seed vehicle, in the order it was seeded, read once without change tracking. public async Task> LoadAsync() { using var db = await factory.CreateDbContextAsync(); // Read once, in seed order, tracking nothing: these rows are a // catalogue this process will never write back. return await db.Vehicles.AsNoTracking().OrderBy(row => row.Seq).Select(row => row.ToVehicle()).ToListAsync(); } ``` *Live from [`api/TheYard.Infrastructure/EfSources.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Infrastructure/EfSources.cs#L23-L30), region `ef-sources`, read from this build at 1d6b50b.* Bids, written through the lock that was already there (`api/TheYard.Application/BidService.cs`): ```csharp /// /// Read once, through . Every read after that one is /// a dictionary: Apply runs over a hundred thousand vehicles on a listing /// request, and a per-row query would end the feature rather than persist /// it (ADR: The relational store). /// /// The constructor does not read the store, because a constructor cannot /// wait and the store has to be waited for (ADR: The ports learn to /// wait). The host calls LoadAsync once at startup; the writing methods /// call it too, so a service nobody warmed loads itself on its first bid. /// public BidService(IBidStore store) { _store = store; _standing = new ConcurrentDictionary(StringComparer.Ordinal); _byUser = new ConcurrentDictionary>(StringComparer.Ordinal); } /// Held only while deciding whether a load has to start, so two first callers share one replay. private readonly object _loadGate = new(); /// The replay in flight or finished, or null before anybody has asked for one. private Task? _loaded; /// /// Replay the store into both indexes, once, whoever asks first. A replay /// that failed is not kept: the next caller starts another, so a store /// that was down at startup is read the first time it is up rather than /// never (ADR: The ports learn to wait, addendum). Replaying twice is safe, /// because Record keeps the higher standing and the same bid twice is the /// same standing. /// public Task LoadAsync() { lock (_loadGate) { if (_loaded is null || _loaded.IsFaulted || _loaded.IsCanceled) { _loaded = LoadFromStoreAsync(); } return _loaded; } } /// Every stored bid, recorded into both indexes in the order the store returns them. private async Task LoadFromStoreAsync() { foreach (var bid in await _store.LoadAsync()) { Record(bid.UserId, bid.VehicleId, bid.State); } } ``` *Live from [`api/TheYard.Application/BidService.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Application/BidService.cs#L38-L89), region `store`, read from this build at 1d6b50b.* The proof (`api/TheYard.Tests/PersistenceTests.cs`): ```csharp [Fact] public async Task A_bid_survives_the_api_restarting() { string id; int amount; string email; await using (var first = Api()) { var client = await Buyers.SignedIn(first); using var page = JsonDocument.Parse( await client.GetStringAsync(ALiveVehicleQuery)); var live = page.RootElement.GetProperty("vehicles"); // The schedule always has live auctions, so a live vehicle is // always there. Saying so out loud costs nothing and turns a future // index-out-of-range into a sentence. Assert.True(live.GetArrayLength() > 0, "the schedule always has a live auction"); var vehicle = live[0]; id = vehicle.GetProperty("id").GetString()!; amount = vehicle.GetProperty("min_next_bid").GetInt32(); var placed = await client.PostAsJsonAsync( $"/api/vehicles/{id}/bids", new { amount }); // The reason, not just the number. Every rejection on this API // carries its sentence in `detail` (ADR: Error handling), and a // bare "Expected OK, actual BadRequest" is a test that knows // something is wrong and will not say what. Assert.True( placed.StatusCode == HttpStatusCode.OK, $"the bid of {amount} was refused: {await placed.Content.ReadAsStringAsync()}\n" + $"the vehicle was {vehicle}"); email = await WhoAmI(client); } // A second application. It shares nothing with the first except the // bytes on disk, which is the entire claim under test. The same person // signs in again, because a bid that survived and could not be found by // its owner would not have survived in any useful sense. await using var second = Api(); var returning = second.CreateClient(); (await returning.PostAsJsonAsync( "/api/auth/login", new { email, password = "correct horse" })).EnsureSuccessStatusCode(); string bids = await returning.GetStringAsync("/api/bids"); // Parsed rather than matched as a substring: a thirteen-digit // timestamp contains almost any five-digit amount somewhere inside it, // so a substring match could pass on a bid that had not survived at all. using var restored = JsonDocument.Parse(bids); Assert.True( restored.RootElement.TryGetProperty(id, out var mine), $"the restored process has no bid for {id}: {bids}"); Assert.Equal(amount, mine.GetProperty("amount").GetInt32()); } ``` *Live from [`api/TheYard.Tests/PersistenceTests.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Tests/PersistenceTests.cs#L57-L109), region `restart`, read from this build at 1d6b50b.* ## Consequences - A bid survives a restart, which is the sentence the record exists for. - Every test class now boots an application that migrates and seeds its own scratch database. The cost is in the table above and it is real. - `BidService` still works with no store at all, through `NullBidStore`, so the unit tests that construct it directly did not change. - The health endpoint has a fourth check that reads the catalogue tables, so a database that failed to seed shows up on the Admin tab rather than as an empty inventory. - The site cannot be taken down by the storage. It can be degraded by it, and it says so when it is. - A scratch database runs with connection pooling off. The alternative is `SqliteConnection.ClearAllPools()`, which is process wide, and xUnit runs test classes in parallel in one process: one class tidying up its own file on shutdown was pulling connections out from under nine others. The persistence test that proved it passes alone, passes with its own class, and failed only in the full suite, which is the signature of a global. - A container killed rather than stopped leaves an uncheckpointed write-ahead log beside the database. That is safe, because the next open replays it, and it is worth knowing before somebody copies a `.db` file somewhere and wonders why it is empty. - Adding a field to `Vehicle` is now three edits instead of one: the record, the row, and the mapping. That is the price of not letting the persistence layer dictate the domain shape, and it is paid once per field. ## Addendum, 2026-09-03: the fallback worked and the deploy failed anyway This record's central promise is that the site comes up when the database does not. 1.0.0.51 was the first roll where that promise was actually tested, and it held: the container could not reach Azure SQL Database, fell back to the JSON files, and served the full catalogue, the filters, the photos and the bidding to 100,000 vehicles. The deploy failed. `/readyz` answered "ready" only when every health check passed, and the database check had joined that set when the relational store did. So a container doing precisely what this ADR designed it to do reported itself unfit for traffic, and the deploy's `curl -fsS "$ORIGIN/readyz"` failed the roll. The defect is a category error, and it is worth naming because the three probes already had names that should have prevented it: - **Liveness** (`/healthz`): is this process running. Restart it if not. - **Readiness** (`/readyz`): send traffic here. Take it out of rotation if not. - **Health** (`/api/health`): the full picture, for a person. "The database is unreachable" is a health fact. It is not a readiness fact, because the container can serve. Putting it in the readiness set means an outage in a dependency takes down a service that was specifically built to survive that outage, which is worse than having no fallback at all: with no fallback the failure at least happens once, rather than being engineered around and then undone by the check. Readiness now asks only the checks that gate it: ```csharp app.MapGet("/readyz", () => RunChecks().Where(check => check.GatesReadiness).All(check => check.Status == "pass") ? Results.Text("ready") : Results.StatusCode(503)); ``` and the database check is the one that declares itself out: ```csharp Check("database", ..., gatesReadiness: false) ``` `/api/health` is unchanged: it still reports `degraded`, still names the store, and the Admin tab still shows the failure. The deploy still warns when a roll lands on files rather than the database. What changed is that a site which is serving is now allowed to say so. The test asserts both halves, because asserting only the first would pass if every check stopped gating readiness. ## Addendum, 2026-09-08: a second store, and what this record is now about This record decided to have a relational store, and every sentence in it is about that store: the row types, the migrations, the seed, the fallback, the measurements of SQLite. As of 1.0.0.89 there is a second, complete store beside it, Azure Cosmos DB, running the same image in a second container so the two can be compared side by side (ADR: A second store on Cosmos DB, and what it costs). Read this record as "how the relational backend works", which is what it always was. Three things in it stopped being general truths and became relational ones, and are named here so the record does not disagree with the one beside it without saying so: - **"Migrations, not `EnsureCreated`."** True of SQLite. The document store has no schema and no migrations; what it has instead is a container definition, held in the repository as JSON and applied by a person (ADR: Data first, and the database in source control, addendum). - **"Read once, write through, store first."** Still the shape on both sides, and the ports that carry it changed: every member returns a `Task` now, because the third store has no synchronous driver (ADR: The ports learn to wait). The `Load()` in this record's code samples is `LoadAsync()`. - **The fallback.** Unchanged, and now shared: a container that cannot reach either store serves the catalogue from files and says so on the health check, and the Cosmos DB adapter answers the same `DatabaseState` the relational one does. ## Addendum, 2026-09-23 (1.0.3.6): the second chance The fallback had a hole this record did not name: the store was tried once, before the container was built, and never again. On 2026-09-23 at 09:53 CDT both containers rolled onto 1.0.3.4 and both asked Azure SQL Database in the same second; both got `SqlException: A connection was successfully established with the server, but then an error occurred during the login process (provider: TCP Provider, error: 35)`, which is a login-phase transient that `EnableRetryOnFailure` does not retry. Both came up on the files, the health check said so, and for the rest of that process's life sign-in and bids on the SQL site went nowhere while the database sat there answering. The kept log holds both lines, one per container, at 14:53:07 UTC. So a store is asked five times at startup before it is written off (`StorePrepare` in Stores.cs; the same morning, Steve asked for five tries against SQL and Cosmos DB before anything is logged as an error), the waits doubling from two seconds, about thirty seconds in all, and the error is logged only after the fifth refusal, with the count in it. And a store that refused all five is asked again (`StoreSecondChance`): every thirty seconds for an hour, and when it answers, everything that stands on it, the catalogue, the bids, the activity store, the probe and the accounts, is built against it, the catalogue is warmed and the bids are loaded, and the backend is attached to it in one call (`Backend.Attach`). A request in flight during that second sees the files or the store for each member, both of which answer correctly, and the swap happens at most once in a process's life. The window is an hour so that a database that is genuinely gone is not a query every thirty seconds for ever; after it the fallback is what this record always said it was, until the next roll. `StoreSecondChanceTests` holds both: the five asks with their doubling waits and the count in the note, and the loop, where a store that answers on the third ask is attached once and the asking stops, a ready store is never asked, an attach that throws is asked again, and the window closes. **Not proven live.** A roll onto 1.0.3.6 whose store comes up at once never runs the loop, and there is no honest way to make Azure SQL refuse a login on purpose; the loop is held by its tests, and the next transient at the wrong second is what proves it, in a log line that reads "came up on the second chance". ## Where it sits The store is Infrastructure code (EfSources.cs, YardDbContext.cs and the row types in Rows.cs) implementing ports that Application declares in Ports.cs, including the IBidStore this record added, and the host's composition root registers the file readers and NullBidStore when the store will not open. Dependency inversion is the heart of it: BidService depends on IBidStore, which its own ring defines, so it never learns that a database exists. NullBidStore with the JSON readers stands in for the EF adapters without the rest of the app noticing (Liskov substitution), and a visitor notices only one thing: bids made on NullBidStore are gone at the next restart. It cost about 1.4 seconds of cold start plus a doubled API suite, and bids that had to outlive a deploy are what moved the deployed site to Azure SQL in ADR-039. ## Files - [`api/TheYard.Infrastructure/YardDbContext.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/YardDbContext.cs): the context and the model. - [`api/TheYard.Api/DesignTime.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/DesignTime.cs): the design-time factory. - [`api/TheYard.Infrastructure/Rows.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/Rows.cs): the three row types. - [`api/TheYard.Infrastructure/EfSources.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/EfSources.cs): the adapters, the mapping, and the seed. - [`api/TheYard.Migrations.Sqlite`](https://github.com/SteveStout/TheYard/tree/main/api/TheYard.Migrations.Sqlite): the schema's history. It lived beside the context when there was one provider, and moved to a project of its own when there were two, because a migrations assembly holds one model snapshot (ADR: The SQL Server backend). This link pointed at the old folder from that day until a test started checking. - [`api/TheYard.Application/Ports.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Application/Ports.cs): the third port, and the null store. - [`api/TheYard.Api/Composition/StoreRegistration.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/Composition/StoreRegistration.cs): the registration, and the migrate and seed block. - [`api/TheYard.Api/Endpoints/HealthEndpoints.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/Endpoints/HealthEndpoints.cs): the health check. - [`api/TheYard.Tests/PersistenceTests.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Tests/PersistenceTests.cs): the restart, the seeding, the migration history, and the file on disk. - [`Dockerfile`](https://github.com/SteveStout/TheYard/blob/main/Dockerfile): the writable directory and the connection string. - [`docs/decisions/ADR-034-entity-framework-explained.md`](https://github.com/SteveStout/TheYard/blob/main/docs/decisions/ADR-034-entity-framework-explained.md): the same setup, walked at a new developer's level. ## Addendum, 2026-10-10: the first wait is a setting The five asks keep their waits, two seconds doubling to sixteen, about thirty in all. What changed is where the first wait comes from: `StorePrepare.FirstWaitFrom` reads `Store:FirstWaitMs` at startup and answers two seconds when the setting is absent or not a whole number of milliseconds. Nothing deployed sets it. The one caller that does is the test that proves the fallback (`PersistenceTests`, the database that will not open), which sets it to zero, because the five asks are the proof and the thirty seconds between them were not; with the waits in, that one test held every run of the suite on both stores to no less than half a minute (ADR: The five-minute gate, the addendum of 10 October on the first wait). `StoreSecondChanceTests` holds the setting in five shapes (absent, zero, a number, a negative, a word) with the four waits that follow from each. ```csharp /// /// A store is asked five times at startup before it is written off, so a /// refusal that lasts a few seconds does not cost the store for the life of /// the process (ADR: The relational store). The waits between asks double /// from two seconds, about thirty seconds in all, which fits inside /// the deploy's five minutes with the container's own start-up still to pay /// for. The state that comes back says how many asks it took, so the log line /// and the Admin tab's startup card carry the count; the error is logged only /// after the fifth refusal, by the caller. /// public static class StorePrepare { /// How many times a store is asked before it is written off. public const int Tries = 5; /// The wait after the first refusal; each wait after it is twice the one before. public static readonly TimeSpan FirstWait = TimeSpan.FromSeconds(2); /// /// The setting that shortens the first wait: a whole number of milliseconds, read by the /// host at startup. Absent, the first wait is two seconds. A test that proves what the site /// does when its store never answers sets it to zero, because the five asks are the proof /// and the thirty seconds of waiting between them were not: with the waits in, that one test /// held every test run on both stores to no less than half a minute. /// public const string FirstWaitSetting = "Store:FirstWaitMs"; /// The first wait the configuration asks for, or the default when it names none. public static TimeSpan FirstWaitFrom(IConfiguration configuration) { string? configured = configuration[FirstWaitSetting]; return int.TryParse(configured, out int ms) && ms >= 0 ? TimeSpan.FromMilliseconds(ms) : FirstWait; } /// /// Asks the store up to five times with real waits between the asks, the /// first one as long as . /// public static Task WithTriesAsync(Func> prepare, TimeSpan firstWait) => WithTriesAsync(prepare, Task.Delay, firstWait); /// /// Asks the store up to five times, waiting through /// between asks so a test can run it without a real clock. Returns the first /// ready state, with the ask count in its note when it took more than one, /// or the last refusal with the count of refusals in its note. The waits /// double from , two seconds unless the caller /// read a shorter one from the configuration. /// public static async Task WithTriesAsync( Func> prepare, Func wait, TimeSpan? firstWait = null) { DatabaseState state = new(false, "never asked"); var between = firstWait ?? FirstWait; for (int attempt = 1; attempt <= Tries; attempt++) { try { state = await prepare(); } catch (Exception ex) { // Prepare answers a state rather than throwing; this is the belt to that brace. state = new DatabaseState(false, ex.GetType().Name, ex); } if (state.Ready) { return attempt == 1 ? state : Renamed(state, $"{state.Note}, on ask {attempt} of {Tries}"); } if (attempt < Tries) { await wait(between); between *= 2; } } return Renamed(state, $"{state.Note}, refused {Tries} times"); } /// The same state with the count in its note; the note is read-only by design, so this is the constructor again. private static DatabaseState Renamed(DatabaseState state, string note) => new(state.Ready, note, state.Failure) { SchemaMs = state.SchemaMs, SeedMs = state.SeedMs, SeedRequestUnits = state.SeedRequestUnits, }; } ``` *Live from [`api/TheYard.Api/StorePrepare.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Api/StorePrepare.cs#L8-L98), region `five-tries`, read from this build at 1d6b50b.*