Cleared fields persist as '' instead of NULL (dead Zod empty-string transform) #2

Severity: medium (data quality; silently breaks COALESCE fallbacks) · Status: open

Symptom

The same "missing" state is stored two ways. Cleared fields persist as empty string ''; omitted fields persist as NULL.

Current data:

• explorer_markers.city = '' → 5 rows (ids 28, 34, 35, 41, 44); city IS NULL → 1 row (id 30)
• explorer_markers.district = '' → 4 rows (ids 13, 98, 198, 199); district IS NULL → 35 rows
• explorer_markers.country = '' → 2 rows (ids 198, 199)

Impact: queries use COALESCE(m.district, l.district) to fall back to the location's value, but an empty string is non-null, so COALESCE returns '' instead of falling through. Result: blank district/city/country on pages where a fallback was expected.

Root cause: dead-code Zod transform

In src/lib/markers.ts the pattern is:

city: z.string().max(120).optional().or(z.literal("").transform(() => undefined))

.or(A, B) is a union that tries the left branch first. For input "", the left branch z.string().max(120).optional() succeeds (empty string is a valid ≤120-char string) and returns "", so the z.literal("").transform(() => undefined) branch never runs. Verified against the installed Zod:

markerSchema.city   | in=''         -> parsed "" -> DB writes ""
markerSchema.city   | in=undefined  -> parsed undefined -> DB writes null
locationUpdate.city | in=''         -> parsed "" -> DB writes ""

The write paths then do payload.city ?? null, and since "" ?? null === "", the empty string is persisted:

• src/app/api/markers/route.ts:92
• src/app/api/markers/[id]/route.ts:128
• src/app/api/markers/batch/route.ts:168 (via setIfPresent)

Affected fields (plain z.string() — base accepts ""): city, district, country, description, summary in markerSchema, and the same in locationUpdateSchema / videoMetadataSchema.
Not affected: videoUrl/channelUrl/screenshotUrl (their .url() rejects "", so the transform branch runs → null) and timestamp (.regex() rejects ""). That is why those never show empty strings.

Why the data is mixed ('' vs NULL)

• '' = field sent present-but-cleared (city: ""); schema keeps it, ?? null keeps it.
• NULL = field omitted/undefined (URL-field transform path, or — in the batch route — setIfPresent skips writing the column entirely, leaving the create-time NULL).

Fix plan (not done — triage only)

1. Schema: make "" actually collapse to undefined, e.g. put the empty-literal branch first — z.literal("").transform(() => undefined).or(z.string().max(120)).optional() — or z.preprocess(v => v === "" ? undefined : v, z.string().max(120).optional()), applied to all plain-string fields.
2. Backfill: UPDATE explorer_markers SET city = NULLIF(city,''), district = NULLIF(district,''), country = NULLIF(country,'') (and same for description/summary).
3. Note: Jerusalem rows 198/199 also have empty country — backfilling to NULL puts them under "Unknown Country" on /locations; consider setting an actual country instead.