Wrap D1 fallback paths in batch() for multi-table write atomicity

[johlju]

Problem

All multi-table DAL writes use db.transaction() for BetterSQLiteSession (local dev / tests) but fall back to sequential await calls on Cloudflare D1, which doesn't support Drizzle's transaction() API. This means D1 paths are non-atomic — a failure partway through can leave the database in an inconsistent state.

Affected functions

lib/dal/requirements.ts

Function	Tables touched
createRequirement	requirementAreas, requirements, requirementVersions, requirementVersionUsageScenarios, requirementVersionNormReferences
editRequirement (Draft path)	requirements, requirementVersions, requirementVersionUsageScenarios, requirementVersionNormReferences
editRequirement (Published→new Draft path)	requirements, requirementVersions, requirementVersionUsageScenarios, requirementVersionNormReferences
deleteDraftVersion	requirementVersionUsageScenarios, requirementVersionNormReferences, requirementVersions, requirements
restoreVersion	requirementVersions, requirementVersionUsageScenarios, requirementVersionNormReferences

lib/dal/requirement-packages.ts

Function	Tables touched
deletePackage	requirementPackageItems, packageNeedsReferences, requirementPackages
updatePackageItemFields	deviations (read), requirementPackageItems (write)

Existing precedent

linkRequirementsToPackageWithD1Batch in requirement-packages.ts already implements the D1BatchClient.batch() pattern successfully, using getD1BatchClient() + raw prepared statements. This approach is proven and can be replicated.

Proposed approach

For each affected function, add a D1 batch path (using getD1BatchClient() + raw prepared statements) mirroring the existing linkRequirementsToPackageWithD1Batch pattern. The BetterSQLiteSession transaction paths remain unchanged.

References

• Database writes instruction: .github/instructions/database-writes.instructions.md
• Existing batch implementation: lib/dal/requirement-packages.ts → linkRequirementsToPackageWithD1Batch

[johlju]

We should also evaluate migrating to a database with proper transaction support once the production hosting environment is decided. D1's lack of Drizzle transaction() support forces dual-path code (BetterSQLiteSession transactions + sequential D1 awaits) and raw prepared statements for batch(). A database with native transaction support would eliminate this complexity entirely.