DOC: AFM-2026-LSF-005 | STACK: C# 13 · .NET 10 · EF CORE 10 · POSTGRESQL | STATUS: FIELD-TESTING
A food-rescue non-profit in Ghent collects surplus from supermarkets — Delhaize, Colruyt, Albert Heijn — and redistributes it at community points. Volunteers are non-technical and working from phones. Belgian food-safety law (FAVV) makes every one of those movements legally traceable. This is the inventory engine that keeps the books an inspector will accept. It is the one chamber in this facility that ships regulated software to real users.
Before a line was written, one question decided the whole data model: model inventory as
state — a live InventoryItems table you increment and
decrement — or as events — an immutable log of donations and
distributions, with current stock as a derived projection? Both satisfy FAVV. They
trade against each other on complexity, auditability, and offline-sync conflicts.
One InventoryItems row per batch, mutated in place. A separate
AuditLog records every change. "What's in stock now?" is a
plain SELECT. Razor PageModels stay thin. Non-technical volunteers, simple
forms, fast queries. Audit is the safety net under the mutation.
Cost: audit is a bolt-on. If the audit path fails silently, traceability is lost — so
it is welded to SaveChanges, not left to callers (see below).
Every action is an immutable event; stock is SUM(QuantityDelta) or a
rebuilt snapshot. The audit trail is the data. Natural offline-merge, full
point-in-time replay ("what was in stock last Tuesday?").
Cost: heavier queries, steeper model, likely overkill for a volunteer-run org today. Kept in the design docs as the migration target for when batch-recall or offline sync forces the issue.
Four entities. Product is the catalogue (from Open Food Facts).
InventoryItem is a physical batch on a shelf. Volunteer is who
touched it. AuditLog sits underneath everything — it is written automatically
and points at any table by name, not by a foreign key.
OnDelete: Restrict — you cannot delete a
product or volunteer out from under an item. The dashed amber arrow is the audit trail: it
references any row by (TableName, RecordId), so one table logs the
history of all of them.
FAVV's core question is "where did this come from and where did it go?" — every
change has to be traceable to a person and a time. The naive answer is "remember to write an
audit row in every service method." That is exactly the thing that gets forgotten. Instead,
LsfDbContext overrides SaveChanges / SaveChangesAsync
so the audit is physically unavoidable: you cannot persist a mutation without generating its
audit row.
WriteAuditLogs() walks ChangeTracker.Entries(), skips the
AuditLog entity itself (no auditing the audit), and for every Added / Modified /
Deleted entry emits a row: TableName, primary key, Action
(INSERT / UPDATE / DELETE), the before/after state as JSON, the timestamp, and the current
volunteer's id from ICurrentVolunteerService. Item and audit row commit in the
same transaction — they cannot drift apart.
One deliberate exception: the Open Food Facts bulk import sets a
SuppressAuditLog flag on the context, so importing ~100–200 k catalogue products
doesn't generate ~100–200 k meaningless audit rows. Operational writes — receiving,
distributing, wasting — always audit. The suppression is scoped to the one job that has no
business being in an inspection log.
Run the real operations against one inventory batch. Each button calls the matching
InventoryService method; the ledger below shows the exact AuditLog
row that SaveChanges would append. Watch a distribution that empties the batch
turn into a soft delete — an UPDATE, never a
DELETE. Values are illustrative; the real column stores the full row as jsonb.
FAVV compliance means no data loss: distributed and wasted items must stay on the books.
So there are no hard deletes. Emptying a batch flips IsDeleted = true with a
DeleteReason ("DISTRIBUTED", a waste reason, or a stock-correction
note) and stamps DeletedAt. A single EF Core 10 named query
filter hides those rows from every day-to-day query:
builder.HasQueryFilter("SoftDelete", i => !i.IsDeleted);
db.InventoryItems.ToListAsync()
Soft-deleted rows are invisible. Volunteers only ever see live stock. No
WHERE IsDeleted = false to forget.
.IgnoreQueryFilters(["SoftDelete"])
The inspector's view — everything, including distributed and wasted items, with their
full audit timeline. Naming the filter means you can lift just soft-delete and
keep any other filter (e.g. future multi-tenancy) intact.
FAVV — the Federaal Agentschap voor de Veiligheid van de Voedselketen, Belgium's food-safety regulator — enforces EU traceability law (Reg. 178/2002) even for non-profits redistributing surplus. The requirement is "one step back, one step forward": who gave you this, and where did it go. Every clause maps to a concrete field or mechanism.
| REQUIREMENT | IMPLEMENTED BY |
|---|---|
| One step back (source) | InventoryItem.SourceStore + ReceivedByVolunteer + ReceivedAt |
| One step forward (destination) | DeleteReason ("DISTRIBUTED") + the AuditLog timeline |
| Allergen communication (Reg. 1169/2011) | Product.Allergens — 14 EU allergens as jsonb, from OFF |
| Best-before / use-by tracking | ExpiryDate : DateOnly? + expiring-soon query |
| Cold-chain awareness | Product.StorageRequirement — Ambient / Chilled / Frozen |
| Audit trail (who / what / when) | AuditLog via the SaveChanges override |
| No data loss | Soft delete + named "SoftDelete" query filter |
| Donor privacy | SourceStore excluded from every public DTO — inspectors only |
InventoryItemDto simply has no SourceStore field — the private
provenance never leaves the service layer except through an authorised inspection query.Barcodes have to resolve instantly on a volunteer's phone, so the product catalogue is pre-loaded from Open Food Facts' nightly dump rather than hit per-scan. The importer streams a 7–10 GB gzipped JSONL file line by line — never loading it whole — filters to Belgian and Dutch products, and upserts them in batches of 1,000.
Barcode blocks duplicates. A malformed line is skipped and counted; a failed
batch falls back to saving rows individually to isolate the bad record. Barcodes not found
locally still fall back to a single-product API lookup via ProductLookupService.
Data is ODbL-licensed, so the app carries the required Open Food Facts attribution.
Domain failures — "not enough stock", "product not found" — return
Result.Fail(message), not thrown exceptions. The PageModel checks
IsSuccess and shows the message. Exceptions are reserved for the genuinely
exceptional. Waste the batch below five, then hit Distribute ×5 in the
demo above — the service returns a failure instead of an audit row, because
SaveChanges is never reached.
Every operation takes a C# record with required init members —
ReceiveItemCommand, DistributeItemCommand,
WasteItemCommand, AdjustStockCommand. The service is a pure
function of its command; nothing mutates the request mid-flight.
Login is a list of names — tap yours, no password. It issues a cookie carrying a
VolunteerId claim, which ICurrentVolunteerService reads to stamp
every audit row. Deliberately frictionless for non-technical volunteers on a shared phone;
hardening real identity is a known, listed gap — not an oversight.
Razor PageModels bind a form and call one IInventoryService method. All
logic lives in the service; complex types (Allergens, Nutrition)
map to jsonb via EF Core 10 ComplexProperty(...).ToJson(). The
AuditLog key is a Postgres identity-always bigserial.
We're not building a startup. We're feeding people. That audit trail isn't for investors — it's for an inspector who can padlock the door if the paperwork's wrong. So you build it like someone's dinner depends on it. Because it does. — Cave Johnson, on regulated software
One bench: a regulated inventory system, finished before the next thing was allowed to start. That is the rule, applied to food safety. See it stated plainly in The Prime Directive, or return to the Facility Directory.