Collapse the dual backend so local dev runs the same code as production

[paccloud]

What to build

End the drift between the local Express+SQLite server and the Vercel serverless functions. They were meant to be kept in sync by hand and have diverged: the local server is missing the public-calcs, consolidated export, and fish-data routes (so the community feed 404s and Export History is broken in local dev), upload validation differs materially, and the local schema lacks the OAuth columns — so OAuth flows can't be exercised locally at all.

Decision needed first: either (a) extract each route's logic into shared handler modules consumed by both the serverless functions and a thin Express adapter (run against local Postgres or a Neon dev branch), or (b) retire the Express server and standardize local dev on vercel dev. Then migrate route-by-route rather than big-bang, and update CLAUDE.md/README to remove the "keep two implementations in sync manually" rule.

Context: AUDIT_REPORT.md §3.6, theme 4, and task 2.1 (branch claude/repo-audit-improvement-ozhlau); Open Question #3.

Acceptance criteria

• Architecture decision recorded (shared handlers + adapter, or vercel dev)
• Local dev serves the same routes with the same validation as production (community feed and export work locally)
• OAuth flows are exercisable in local dev
• Exactly one implementation of each route's logic exists in the repo
• CLAUDE.md/README describe the new local dev story

Blocked by

• Fix sync field contract so saved calcs and custom yields sync correctly #23 (sync field contract)
• Delete round-trip: deleted calculations stay deleted across devices #24 (delete endpoint)
• OAuth users can sync, upload, and export — unified auth headers for the data layer #25 (unified auth headers)

These fix the contract first so the consolidation ports correct behavior, not bugs.

[paccloud]

This was generated by AI during triage.

Re-triage: mostly done — reopened as ready-for-human for the rest

Landed on main (d6ee642): a shared handler core with Neon and SQLite adapters now serves register, login, saved-calcs, public-calcs, fish-data, contributors, contributor profile, and user-data CRUD and sharing (#51, #54, #60, #76).

Not landed. Two issues were closed before their work reached main:

• Migrate user-data, upload, export endpoints to handler core (fix drift) #44's export and upload handlers never reached main. PR refactor: migrate user-data, upload, export endpoints to handler core #56 was merged into the stacked base branch fix/delete-roundtrip, not main, and shared/handlers/export.js and uploadData.js don't exist on main. As a result, GET /api/export and POST /api/upload-data are still implemented twice with inline SQL, and the copies have drifted (different XLSX headers and filenames, prod-only validation). The acceptance criteria of Migrate user-data, upload, export endpoints to handler core (fix drift) #44 and its parent PRD: Architecture deepening — calculation module, API client, shared backend core, data merger #37 are unmet.
• /api/community-data is duplicated too. Prod supports ?format=csv, which the download button uses. Express ignores it and exposes a separate /api/export-community-data that prod lacks.
• Local dev can't run the publish flow. Express has no PATCH /api/saved-calcs/:id/publish or /unpublish, and its CORS config omits PATCH, which also breaks local PATCH on user-data.
• Local and production still use different databases. Local uses SQLite with an inline, hand-maintained schema, while prod uses db/migrations.
• Docs are stale. CLAUDE.md still says "Changes to API logic must be applied to both server/server.js … and the corresponding api/*.js file." No ADR records the architecture.

Why this needs a human first: one open decision is whether local dev keeps SQLite (schema generated from or checked against the migrations) or moves to Postgres/vercel dev, and whether local auth uses the Firebase emulator. Everything else in the brief is mechanical. After the decision, this can be split into ready-for-agent slices: (1) export, upload, and community-data into the core, (2) publish routes plus PATCH CORS, (3) docs and ADR.

Agent Brief

Category: enhancement
Summary: Finish collapsing the dual backend: every route's logic lives once in the handler core, local dev serves the same routes as prod, the decision is recorded, and the docs are corrected.

Desired behavior:

• An ADR records the local dev story: which database, how its schema stays in lockstep with prod migrations, and how Firebase auth is exercised locally.
• Export, upload, and community-data each have one handler in the core (the { status, body } envelope, or a documented file/stream variant for CSV/XLSX). Adapters only parse transport input and write responses.
• Exactly one CSV sanitization function and one yield-row import module exist.
• Local dev serves every prod route with the same paths, methods, query contracts, and validation, including saved-calc publish/unpublish and community-data format=csv. Local-only routes are removed.
• Local CORS permits every method the frontend uses, including PATCH. Errors never leak raw driver messages.
• Expected-revision and share-action validation live in the core, not in one adapter.
• Contributor docs describe the handler-core-plus-adapters model.

Key interfaces: the handler signature (input, db) => { status, body }, the DbAdapter typedef, makeNeonAdapter/makeSqliteAdapter, handlePublishCalc/handleUnpublishCalc, the import helpers (assertAllowedImportFile, parseImportRows, normalizeYieldRows, the upsert variants), sanitizeCsvValue/formatCsvDate, and the frontend publishCalcRaw/unpublishCalcRaw.

Acceptance criteria:

• An ADR is committed covering the local DB and local auth approach, with the rejected alternatives
• Export, upload, and community-data (JSON + CSV) handlers live in the core and are used by both adapters; no inline SQL for them remains in either entrypoint
• One CSV sanitizer and one import-rows module exist, with unit tests (formula prefix, quote escaping)
• Handler tests with a fake DbAdapter cover export type/format validation, upload insert/update/skip, community CSV output, and sanitized failures
• A route-parity test or script confirms every prod route is served locally with the same method and path
• Local CORS allows PATCH, and the publish-preview flow works end to end locally
• The local schema is derived from, or verified against, the prod migrations, per the ADR
• CLAUDE.md, README, and architecture docs describe the new model, and the "apply changes to both backends" rule is gone
• Lint, tests, and build pass

Out of scope: migrating to Firebase SQL Connect, changing the auth provider or removing legacy JWT compatibility, and saved-data lifecycle changes beyond endpoint wiring.

---

Generated by Claude Code