perf(crud): bulk batch POST via a single multi-row INSERT (createMany)

[Justus-at-Tazama]

Repo: tazama-lf/admin-service (branch dev)
Follow-up to: #436 (atomic batch POST), #450 (consistent batch reply shaping). Surfaced while testing the rule/typology batch POST against a remote database.

Problem

The atomic batch (array) POST for rule and typology (#436) inserts items one row at a time: the handler pins a single transaction client and loops repo.create(item, tenantId, client) serially (see buildCrudPlugin in src/utils/crud-schema.ts and TypologyConfigRepo.create in src/repositories/configuration/typology.config.repository.ts). Because a single pg connection serialises queries, an N-item batch costs roughly N database round-trips. Over a remote database link this is the dominant latency - a modest typology batch can approach multi-second response times even though each individual insert is cheap.

Naive parallelism does not fix this:

• Promise.all of client.query(...) on the same pinned client gives no concurrency - pg queues queries on one connection and runs them sequentially.
• Spreading inserts across multiple pooled connections would gain concurrency but break the single BEGIN/COMMIT, losing the all-or-nothing atomicity guarantee that the batch feature exists to provide (feat: allow batch (array) submission of configurations on POST endpoints #436).

Goal

Collapse the per-item inserts into a single parameterised multi-row INSERT per batch, so an N-item batch is one round-trip instead of N, while preserving atomicity and the existing response contract.

INSERT INTO typology (configuration) VALUES ($1),($2),...,($n) RETURNING configuration

A single statement is inherently atomic, so the all-or-nothing guarantee holds (a constraint violation on any row aborts the whole statement) without relying on the explicit transaction wrapper for correctness.

Approach (locked after pre-implementation review - see the "Resolved questions" comment below)

1. Add a createMany(payloads, tenantId, client?) method to the batch-enabled repositories (rule, typology), duplicated per repo with fully literal SQL, matching the existing get/create/update/remove duplication (Q4). It:
   • stamps tenantId, creDtTm, updDtTm on each payload exactly as create does today, using one shared timestamp for the whole batch (Q1);
   • builds one parameterised INSERT INTO <table> (configuration) VALUES ($1),($2),...,($n) RETURNING configuration - placeholders generated from the payload count, values bound positionally, no string interpolation of data;
   • returns the created entities in input order (Postgres emits RETURNING rows in insertion order for a plain non-parallel multi-row VALUES - deterministic, stable behaviour, accepted reliance);
   • has no empty-array guard: the schema's minItems: 1 rejects at the boundary and the batch handler is createMany's only caller.
2. Type system (Q3): createMany? is an optional method on the CrudRepository interface, following the existing optional-capability precedent (activate?/deactivate?/getActive?), with a fail-fast guard at plugin registration when batch is configured but the repo lacks createMany.
3. The generic batch handler in buildCrudPlugin calls createMany once for the whole array instead of looping create, keeping the injected runInTransaction wrapper (Q2): the single statement is atomic on its own; the wrapper is retained for interface stability and to future-proof multi-statement batches (noted with a one-line comment).
4. Keep the response contract identical to feat(service-channel): fire-and-log ack sink on reply subject (#445) #450: a 201 JSON array in input order, each element serialised through the route's Entity serialiser (consistent with a single create), and Content-Type: application/json.
5. Respect the existing maxItems cap (default 200, a startup-time constant frozen into the compiled schema - not a runtime knob) and the per-item validation that reports item[<index>] on the first invalid element (Q5).

Acceptance criteria

1. rule and typology batch POST insert the whole array in a single multi-row INSERT (one database round-trip for the rows), not N serial inserts.
2. Atomicity is preserved: a failure on any row (e.g. unique-constraint violation) inserts none of the batch - all-or-nothing, asserted by test.
3. The response is unchanged from feat(service-channel): fire-and-log ack sink on reply subject (#445) #450: 201, a JSON array in input order, each element schema-serialised identically to a single-object create, Content-Type: application/json.
4. Per-item validation and the maxItems cap behave exactly as today (first invalid item -> 400 item[<index>]: ...; empty array and over-cap -> 400).
5. Values are bound positionally; no batch data is ever string-interpolated into SQL (no injection surface) - asserted structurally: exact-equality on the statement text as a pure function of the item count, data present only in values, plus a hostile-payload probe.
6. Red-tests-first: failing unit tests are written first and reviewed before any production code. They must cover: single multi-row statement is issued (one query/round-trip for N rows), input-order preservation in the response, all-or-nothing rollback on a mid-batch failure, validation/cap behaviour unchanged, and the response shape/content-type contract.
7. Docs: update the README "Configuration POST contract (single or batch)" section to describe the single multi-row INSERT and its atomicity, and update any affected OpenAPI/Swagger notes for explainability and maintainability.

Out of scope

• Cross-entity batching (each entity type keeps its own statement/table).
• network_map batch (still deliberately single-object only, feat: allow batch (array) submission of configurations on POST endpoints #436).
• Streaming/COPY-based ingestion or chunking beyond the existing maxItems cap.
• Configurable maxItems overrides (env var, per-request, or per-route): the fixed startup-time cap of 200 is significant enough for now.
• DB-error-to-HTTP mapping (e.g. unique violation 23505 -> 409): none exists today - a mid-batch failure surfaces as Fastify's default 500 and the single statement preserves that contract unchanged. Adding a mapping is a separate issue.

Cross-cutting

Exact-RC pins; red-first TDD with the database/transport mocked at the unit level; minimal, explanatory-only comments; update the README and Swagger docs.

[Justus-at-Tazama]

Resolved questions

Raised and settled during pre-implementation planning (2026-08-10), each verified against dev HEAD 9820b65.

Q1 - Timestamp stamping: one timestamp per batch or per item?

create computes new Date().toISOString() per call, so today's loop gives each item a slightly different creDtTm. A single createMany naturally computes one timestamp for the whole batch.

Answer: one shared timestamp per batch. It is semantically truthful (they were created in one statement), simplifies tests, and no consumer is known to rely on distinct per-item timestamps within a batch.

Q2 - Keep the runInTransaction wrapper or drop to a bare single statement?

A single INSERT is atomic on its own. Options: (a) keep the wrapper and call createMany(items, tenantId, client) inside it; (b) drop the wrapper for the batch path and rely on single-statement atomicity.

Answer: (a) keep the wrapper, after a red-team/blue-team assessment. Decisive asymmetry: (a)'s worst case is 2 redundant round-trips (constant, not O(N) - noise next to the N-1 removed) and a comment; (b)'s worst case is silent loss of atomicity when a future edit adds a second statement to the generic batch path - a low-probability, high-severity, invisible failure. Concurrency/pool-pinning concerns carry near-zero weight because batch POST is a low-use path, predominantly exercised during initial system setup. A one-line comment notes the single statement is atomic on its own and the wrapper is retained for interface stability and future multi-statement batches.

Q3 - Where does createMany live in the type system?

Answer: optional method on CrudRepository: createMany?: (payloads: TEntity[], tenantId: string, client?: PoolClient) => Promise<TEntity[]>, resolved by existing precedent. CrudRepository already uses optional capability methods gated on presence - getNodeByName?, activate?, deactivate?, getActive? ("the CRUD factory registers the activate/deactivate routes only when present", repository.base.ts) - and the batch feature itself is already an optional capability (batch?: BatchCreateOptions narrowed once into batchRuntime, crud-schema.ts). A fail-fast guard at plugin registration (batch configured but createMany missing -> throw) folds the repo capability into the same single existence fact as batchRuntime.

Q4 - Shared implementation between rule and typology repos?

Both repos are identical except for the table name: (a) duplicate the ~15-line method per repo; (b) shared helper parameterised on table name.

Answer: (a) duplicate per repo, resolved by existing precedent. The dominant write-path precedent is per-repo duplication with fully literal SQL: get/create/update/remove are all duplicated with the table name baked into the string literal; only list delegates to config-list.shared.ts, and that helper earns its existence through descriptor complexity (sort columns, filters, keyset pagination) that createMany does not have. Duplication also keeps the SQL text 100% literal - nothing is interpolated into the text, not even a table-name constant - which puts the exact-equality injection assertion (Q6) at its strongest.

Q5 - Practical limit for the number of records in a batch, and how is it enforced?

Three layers, in request order:

1. Schema cap (the enforced limit): maxItems: 200 (DEFAULT_BATCH_MAX_ITEMS) on the array branch of the POST body union; Ajv rejects over-cap with 400 before the handler runs. The cap is a startup-time constant frozen into the compiled schema at route registration - not a runtime knob; API callers cannot influence it.
2. Fastify bodyLimit: not configured, so the default 1 MiB applies (413 before parsing) - caps a 200-item batch at ~5 KB average per payload.
3. Postgres bind-parameter cap (theoretical only): 65,535 params per statement; one placeholder per row makes the ceiling 65,535 rows - unreachable behind layers 1-2 by two orders of magnitude.

Answer: no new enforcement needed; the existing maxItems cap is the practical limit. No chunking; configurable overrides accepted as out of scope.

Q6 - Test seam for "single statement issued"

Answer: two established seams confirmed from the #436 test files, and the createMany tests split across them:

1. Repo level (repositories/crud-create-client-threading.test.ts precedent): jest.mock of database.logic.service; home of single-statement, statement-shape, stamping, input-order, client pass-through, and injection assertions.
2. Route level (crud-plugin.batch.test.ts precedent): standalone Fastify + app.inject, mocked repo modules + a TxRecorder runInTransaction stub; home of "handler calls createMany once, not N create calls", rollback-on-rejection, and the unchanged validation/cap/response/content-type assertions.

Injection coverage (criterion 5) at the repo seam: exact-equality assert that the statement text is a pure function of the item count; data present only in values in input order; and a hostile-payload probe ('), ('{}'); DROP TABLE rule; --) absent from text, verbatim in values. Positional binding itself is native PostgreSQL (extended query protocol: Parse/Bind separation) - we only generate the placeholder list by counting.

Q7 - Local function in admin-service, or extend the frms-coe-lib DatabaseManager?

Answer: independent function in admin-service. frms-coe-lib is the connection provider only for this path (CreateStorageManager yields databaseManager._configuration, a raw pg.Pool); all configuration CRUD SQL is authored locally and executed through handlePostExecuteSqlStatement. Extending the lib would need a lib change + release + exact-pin bump for a method only admin-service uses, and would split configuration CRUD SQL across two codebases.

Q8 - Failure mechanics: is a "partial" insert even possible with a single multi-row INSERT?

Answer: no. Postgres executes a multi-row INSERT ... VALUES as one statement with statement-level atomicity: any row's constraint violation errors the whole statement and zero rows are applied. "Row failure" in acceptance criterion 2 names the cause (one bad row), not the failure granularity - the failure unit is always the statement. Partial application would require a deliberate opt-in we are not doing (ON CONFLICT, per-row SAVEPOINTs, or per-row statements). The rollback test pins the observable contract (repo rejection -> ROLLBACK issued, error response, no partial 201 body) as a regression guard against someone later opting out.

---

The plan was additionally stress-tested against the codebase (8 challenges, all resolved without escalation), confirming among other things: the registration guard affects only 3 test registrations (no production blast radius); mid-batch failures surface as Fastify's default 500 today (no error mapping exists - kept unchanged, mapping declared out of scope); an array of length 1 takes the batch path; and the schema's minItems: 1 makes an empty createMany call impossible via the route.