api: Write API + bulk upsert with idempotency

[victorquinn]

Summary

Implement authenticated write endpoints for all entity types with optimistic locking, delta-based versioning, and bulk upsert with idempotency keys.

Endpoints

• POST /api/v1/{entity} — Create (auth required)
• PATCH /api/v1/{entity}/:slug — Update with version check (auth required)
• POST /api/v1/{entity}/bulk — Bulk upsert with idempotency key (auth required)

Implementation

• Optimistic locking: WHERE version = expectedVersion on UPDATE
• Delta-based version creation on every write (via versioning middleware)
• Bulk upsert with idempotency_key in bulk_operations table
• Provenance tracking: source, submitted_by, change_summary
• Zod validation on all request bodies

Acceptance Criteria

• Create/Update for all entity types
• Optimistic locking prevents concurrent write conflicts
• Delta versions created automatically
• Bulk upsert with idempotency (safe to retry)
• Auth required for all write operations
• npm run build passes

Spec ref: Sections 4.3, 4.8, 3.6
Blocked by: #5, #6, #7, #8 (API utils, auth, seed, versioning)
Blocks: Sync pipeline update

[meridian-texture]

Triage note (2026-09-18): keeping this open — it is not done. There is no bulk-upsert or entity write endpoint anywhere under app/api/v1. The only write handlers that exist are contributions, developer/keys, discussions, follows, mod/*, notifications and utilities/resolve. The supporting bulk_operations table (idempotency-key primary key) does exist in lib/db/schema/bulk-operations.ts, so the schema half landed and the API half never did. Public entity mutation currently goes through the community contributions flow instead, which may well be the right answer — if so this should be closed as not planned with that rationale rather than as completed. Needs a product call.

[meridian-texture]

Status check against the code (2026-09-18)

Not built, and the architecture moved past it. Recording the evidence before a close/build call.

What exists under /api/v1 with a POST/PATCH/PUT handler:
contributions (+ [id], withdraw), developer/keys, discussions (+ posts), follows, mod/contributions/[id]/review, mod/templates, mod/users/[id], notifications/*, utilities/resolve.

There is no POST /api/v1/{entity}, no PATCH /api/v1/{entity}/:slug, and no bulk path anywhere under app/api (find app/api -ipath "*bulk*" → empty).

But the two things this issue was meant to enable both happened by other means:

1. Public mutation now goes through the contributions + moderation flow (submit → review → apply), which gives provenance, review and audit that a raw authenticated PATCH would not.
2. Sync writes go straight to Postgres via Drizzle, not over HTTP. scripts/sync-power-plants-monthly.ts and scripts/sync-ev-charging.ts both do db.insert(...).onConflictDoUpdate(...); versioning is handled in the shared sync layer rather than in API middleware.

Loose end: the bulk_operations table (lib/db/schema/bulk-operations.ts) is exported from the schema index and referenced in no application or script code. It's dead schema — the idempotency-key half of this design with nothing writing to it.

Recommendation: close as not planned, and handle bulk_operations separately (drop it in a migration, or keep it deliberately if the contributions pipeline should adopt idempotency keys). Building a public entity-write API now would be a second mutation path competing with contributions, plus a new authenticated attack surface on data nobody has asked to write over HTTP.