Seed development data from the database command (task 47)

rob commented

2026-08-03 13:13:01 +00:00

Owner

dotnet run --project src/PlaceMark.Database -- seed — 3 users with personal groups, one shared group covering every role and both invitation statuses, 8 places spanning both hemispheres, the prime meridian, the equator and longitude 180. Idempotent via ON CONFLICT ON CONSTRAINT … DO NOTHING in one transaction. Recorded in ADR-0026.

Two independent production guards: the seed reads its own ConnectionStrings__PlaceMarkSeedTarget, never the variable ADR-0023 expects to be exported when applying schema to production; and it refuses any database holding accounts it did not create. An empty production database is protected by the variable alone — stated in the ADR.

Seed SQL lives in C# constants, not a .sql file: DbUp is handed the whole assembly, so a seed script under Scripts/ would be journalled and deployed to production.

Needs a decision:

Seeded accounts cannot sign in. password_hash is NULL and no external_identities row exists, because nothing hashes passwords yet. Whichever auth ticket adds hashing must supply SeedData a real value.
Top-up, not reset. A re-run adds what is missing; it does not restore an edited seed row. Reset semantics would need a delete, and the guard cannot distinguish a hand-made place from a seeded one.
Registering through the app against a seeded database stops the seed running there again — deliberate, but it means docker compose down -v or a second database.

Program.cs in the Database project gained a three-line dispatch; no arguments still means schema upgrade, so CI and ADR-0023 are unaffected. That dispatch is the one line no test covers.

Incidental, relevant to the Dapper work ahead: Dapper sends a collection to PostgreSQL as a single array parameter rather than expanding it, so IN @Ids fails with 42601 — use = ANY(@Ids).

`dotnet run --project src/PlaceMark.Database -- seed` — 3 users with personal groups, one shared group covering every role and both invitation statuses, 8 places spanning both hemispheres, the prime meridian, the equator and longitude 180. Idempotent via `ON CONFLICT ON CONSTRAINT … DO NOTHING` in one transaction. Recorded in ADR-0026. Two independent production guards: the seed reads its own `ConnectionStrings__PlaceMarkSeedTarget`, never the variable ADR-0023 expects to be exported when applying schema to production; and it refuses any database holding accounts it did not create. An *empty* production database is protected by the variable alone — stated in the ADR. Seed SQL lives in C# constants, not a `.sql` file: DbUp is handed the whole assembly, so a seed script under `Scripts/` would be journalled and deployed to production. Needs a decision: - **Seeded accounts cannot sign in.** `password_hash` is `NULL` and no `external_identities` row exists, because nothing hashes passwords yet. Whichever auth ticket adds hashing must supply `SeedData` a real value. - **Top-up, not reset.** A re-run adds what is missing; it does not restore an edited seed row. Reset semantics would need a delete, and the guard cannot distinguish a hand-made place from a seeded one. - Registering through the app against a seeded database stops the seed running there again — deliberate, but it means `docker compose down -v` or a second database. `Program.cs` in the *Database* project gained a three-line dispatch; no arguments still means schema upgrade, so CI and ADR-0023 are unaffected. That dispatch is the one line no test covers. Incidental, relevant to the Dapper work ahead: Dapper sends a collection to PostgreSQL as a single array parameter rather than expanding it, so `IN @Ids` fails with `42601` — use `= ANY(@Ids)`.

rob added 1 commit

2026-08-03 13:13:01 +00:00

Add a guarded seed command for development databases

CI / build (pull_request) Successful in 43s

Details

b19d69b2b0

Ticket #47 asks for representative data for manual testing and demos:
one command, several users, a shared group with more than one member,
places with varied coordinates, and safe to re-run. Its wording predates
ADR-0020 and ADR-0021, so the "EF Core data-seeding routine" it names no
longer exists; only its acceptance criteria carry over.

The seed is an argument on the existing PlaceMark.Database command
rather than a second tool, and running it against production is made an
act rather than a slip by two independent guards: a connection string
variable of its own, so nothing that already points at production points
at the seed, and a refusal to touch any database holding an account the
seed did not create. Idempotency is by construction — constant UUIDv7
identifiers, ON CONFLICT DO NOTHING, one transaction — so a second run
inserts nothing and moves no identifier.

Seeded accounts carry an explicit NULL password_hash and no external
identity, because nothing hashes a password yet: they cannot sign in
until the auth epic gives that column a real value. A placeholder would
have been a credential nothing could verify or tell apart from one it
could.

The seed SQL is C# string constants rather than an embedded .sql file:
DbUp treats every .sql resource in the assembly as a schema script, so a
seed script beside the schema scripts would be journalled and applied to
production by the deployment command. ADR-0026 records that and the rest.

rob commented

2026-08-03 13:22:53 +00:00

Author

Owner

DatabaseSeeder.GuardAsync counts accounts at READ COMMITTED, so a registration that commits between that count and the seed's COMMIT is invisible to it — against a fresh schema-only database the seed exits 0 and writes its three demo users beside a real account. Either take LOCK TABLE users IN SHARE MODE inside the transaction before the count (checked: the seed then waits and refuses, writing nothing), or record the limit in ADR-0026's Consequences beside the empty-database one; as written, both the record and the PR body claim a refusal that does not hold while anything else is writing to users.

RunAsync_SeedApplied_CreatesPlacesInEveryQuadrantOfTheGlobe asserts that each axis crosses zero, not that the four quadrants are occupied — flipping Ny-Ålesund to longitude -11.9231 empties the north-eastern quadrant and the test still passes. Assert the quadrants, and pin longitude exactly 180, which is the boundary value ADR-0026 calls the point of that row and which nothing currently fails on if it is edited.

Every seeded user breaks docs/data-model.md invariant 5 ("a user should hold at least one usable credential"); the seed paragraph added to that document warns that seeded rows are the first thing that can break an unenforced invariant without saying that one already is. Add the pointer at invariant 5 itself, where an implementer will read it.

dotnet run --project src/PlaceMark.Database -- seed --force prints Unknown command 'seed'. Name the actual fault (unexpected arguments) rather than the command that was recognised.

Verdict: changes required

`DatabaseSeeder.GuardAsync` counts accounts at READ COMMITTED, so a registration that commits between that count and the seed's `COMMIT` is invisible to it — against a fresh schema-only database the seed exits 0 and writes its three demo users beside a real account. Either take `LOCK TABLE users IN SHARE MODE` inside the transaction before the count (checked: the seed then waits and refuses, writing nothing), or record the limit in ADR-0026's Consequences beside the empty-database one; as written, both the record and the PR body claim a refusal that does not hold while anything else is writing to `users`. `RunAsync_SeedApplied_CreatesPlacesInEveryQuadrantOfTheGlobe` asserts that each axis crosses zero, not that the four quadrants are occupied — flipping Ny-Ålesund to longitude `-11.9231` empties the north-eastern quadrant and the test still passes. Assert the quadrants, and pin longitude exactly `180`, which is the boundary value ADR-0026 calls the point of that row and which nothing currently fails on if it is edited. Every seeded user breaks `docs/data-model.md` invariant 5 ("a user should hold at least one usable credential"); the seed paragraph added to that document warns that seeded rows are the first thing that can break an unenforced invariant without saying that one already is. Add the pointer at invariant 5 itself, where an implementer will read it. `dotnet run --project src/PlaceMark.Database -- seed --force` prints `Unknown command 'seed'`. Name the actual fault (unexpected arguments) rather than the command that was recognised. Verdict: changes required

rob added 1 commit

2026-08-03 13:33:40 +00:00

Make the seed's account guard atomic, and test what it claims

CI / build (pull_request) Successful in 1m0s

Details

25749ad63a

The guard counted accounts at READ COMMITTED, which cannot see a
registration that has not committed: a seed running beside one counted
zero accounts, inserted its demo users and committed beside a real
account. A refusal that holds only while nothing else writes is not a
guard, so the count is now taken under LOCK TABLE users IN SHARE ROW
EXCLUSIVE MODE, held for the rest of the transaction. That mode rather
than SHARE because it conflicts with itself: two seeds holding SHARE
would deadlock upgrading to the ROW EXCLUSIVE their inserts need.

The quadrant test asserted that each axis crosses zero, which two
diagonal quadrants satisfy — it now counts places per quadrant, and
longitude exactly 180, the boundary ADR-0026 calls the point of that
row, is pinned by a test of its own. A further test pins every seeded
identifier to a literal list: the idempotency test compares one build
against itself and cannot see a constant that moved in both halves.

'seed --force' reported an unknown command 'seed', sending the reader
to look for a typo they did not make; it now names the unexpected
arguments.

Invariant 5 of docs/data-model.md — a user should hold at least one
usable credential — is broken by every seeded account, and says so
where it is stated rather than only where the seed is described.

rob commented

2026-08-03 13:33:45 +00:00

Author

Owner

All four actioned in 25749ad, plus the two you noted but did not raise: the ux_groups_personal_for_user_id failure mode is recorded in ADR-0026 rather than papered over, and the seeded identifiers are now pinned by a test.

All four actioned in 25749ad, plus the two you noted but did not raise: the `ux_groups_personal_for_user_id` failure mode is recorded in ADR-0026 rather than papered over, and the seeded identifiers are now pinned by a test.

rob commented

2026-08-03 13:39:29 +00:00

Author

Owner

Nothing to act on: all four findings are addressed, the lock-mode reasoning holds under test, and each new assertion fails when the thing it defends is edited.

Verdict: mergeable

Nothing to act on: all four findings are addressed, the lock-mode reasoning holds under test, and each new assertion fails when the thing it defends is edited. Verdict: mergeable

rob referenced this pull request

2026-08-03 14:08:14 +00:00

Blazor layout shell and theming foundation (task 86) #23

~~rob referenced this pull request 2026-08-03 14:08:57 +00:00~~

Validate requests with data annotations (task 59) #21

rob added 1 commit

2026-08-03 14:14:07 +00:00

Merge remote-tracking branch 'origin/main' into feat/seed-data

CI / build (pull_request) Successful in 50s

Details

f9377aa59c