Validate complete member sets before atomic upserts
A batch result is the full expected member set under one batch key, not one existing row. This note defines read and write rules that never mistake a partial set for completion.
A generated batch is the full expected member_code set under one batch_key, not one existing row. One member does not mean that the batch is ready.
Use the same completeness rule to read and write
Read with GROUP BY batch_key, compare the member set, and skip only when it exactly matches the expected set. Leave partial rows eligible for the next generation round. Before the database, reject duplicate, unsupported, mislabeled, or incomplete member codes.
One batch, one transaction
| Decision | Write behavior | Retry |
|---|---|---|
| Complete expected member set | Upsert in one transaction | None |
| Missing or duplicate member | Write nothing | Regenerate the batch |
| Database error | Roll back | Retry the same batch |
Write the validated expected row count with executemany and one commit. If one row fails, roll back and report a count of zero. The UI can then distinguish partial work from complete work, and the scheduler retains a retry scope.
The rule applies to manual Admin generation and the automatic scheduler as well as LLM output. Last-write-wins may be the collision policy, but it must not turn an incomplete batch into a success.
Related course: Close partial failure, recovery, and platform boundaries
Verify both that the expected member set matches after a write and that injected failures leave the previous row count unchanged.
More in data
All in this category →Related posts
Optimizing search with ILIKE, pg_trgm, and migrations
Content search commonly uses ILIKE '%term%' over post titles, descriptions, slugs, and lesson bodies. Escaping the query and limiting its length are safety contracts; because of the leading and trailing wildcards, a nor…
Make integration-test migration replay fail closed
If an integration test cannot create its schema but continues into entity tests, the delayed failures lose the original cause. A missing migration directory, zero SQL files, a read error, or one failed statement is a bo…
A PostgreSQL advisory lock for concurrent content initialization
Content initialization is one ordered pipeline: create tables, correct the schema, seed static data, and upsert notes and courses. Even idempotent steps can observe an intermediate state when an operator double-clicks o…
Orchestrating multiple PostgreSQL pools
An admin console or back office may need one process to access separate content, catalog, and operations databases. Direct pools can be useful, but connection budgets, permissions, and failure isolation must be designed…