Pre-flight Checks
Problem Description / Descripción del Problema
English: The current Import() function performs blind INSERT operations for observations and prompts. When re-importing an export dump, this creates duplicate rows instead of recognizing existing data by sync_id. Additionally, deleted_at from the export payload is ignored during import, meaning soft-deleted observations can be resurrected unintentionally. The newSyncID() function also has format inconsistency between Go-generated IDs (16 hex chars) and SQL backfill IDs (32 hex chars).
Español: La función Import() actual realiza INSERT ciegos. Al re-importar un dump, crea filas duplicadas en vez de reconocer datos existentes por sync_id. Además, deleted_at del payload se ignora durante el import, permitiendo que observaciones soft-deleted se restauren involuntariamente. newSyncID() también tiene inconsistencia de formato entre IDs generados en Go (16 hex) y los del backfill SQL (32 hex).
Proposed Solution / Solución Propuesta
English: Refactor Import() with per-entity conflict resolution:
Observations (mutable — LWW):
SELECT id, updated_at, deleted_at WHERE sync_id = ? LIMIT 1- No rows → INSERT (preserving
deleted_at from payload)
- Payload newer → UPDATE all fields including
deleted_at
- Local newer → SKIP
Prompts (immutable — SKIP):
SELECT id WHERE sync_id = ? LIMIT 1- No rows → INSERT
- Exists → SKIP (prompts are immutable)
ImportResult expanded:
SessionsImported, ObservationsInserted, ObservationsUpdated, ObservationsSkipped, PromptsInserted, PromptsSkipped
Español: Refactorizar Import() con resolución de conflictos por entidad: LWW para observations (incluyendo deleted_at como campo regular), SKIP para prompts inmutables, y ImportResult con contadores granulares.
Affected Area
Store (Import function, ImportResult struct, newSyncID format)
Additional Context
- No UNIQUE constraint on
sync_id (legacy data may have format inconsistencies) — manual LWW is safer than schema migration
deleted_at participates in LWW as a regular field: a newer soft-deleted payload can soft-delete a local active observation- Normalizes future
newSyncID() to {prefix}-{16hex} format; existing IDs accepted as-is
- All checks within same
tx to prevent race conditions
Pre-flight Checks
Problem Description / Descripción del Problema
English: The current
Import()function performs blindINSERToperations for observations and prompts. When re-importing an export dump, this creates duplicate rows instead of recognizing existing data bysync_id. Additionally,deleted_atfrom the export payload is ignored during import, meaning soft-deleted observations can be resurrected unintentionally. ThenewSyncID()function also has format inconsistency between Go-generated IDs (16 hex chars) and SQL backfill IDs (32 hex chars).Español: La función
Import()actual realizaINSERTciegos. Al re-importar un dump, crea filas duplicadas en vez de reconocer datos existentes porsync_id. Además,deleted_atdel payload se ignora durante el import, permitiendo que observaciones soft-deleted se restauren involuntariamente.newSyncID()también tiene inconsistencia de formato entre IDs generados en Go (16 hex) y los del backfill SQL (32 hex).Proposed Solution / Solución Propuesta
English: Refactor
Import()with per-entity conflict resolution:Observations (mutable — LWW):
SELECT id, updated_at, deleted_at WHERE sync_id = ? LIMIT 1deleted_atfrom payload)deleted_atPrompts (immutable — SKIP):
SELECT id WHERE sync_id = ? LIMIT 1ImportResult expanded:
SessionsImported,ObservationsInserted,ObservationsUpdated,ObservationsSkipped,PromptsInserted,PromptsSkippedEspañol: Refactorizar
Import()con resolución de conflictos por entidad: LWW para observations (incluyendodeleted_atcomo campo regular), SKIP para prompts inmutables, yImportResultcon contadores granulares.Affected Area
Store (Import function, ImportResult struct, newSyncID format)
Additional Context
sync_id(legacy data may have format inconsistencies) — manual LWW is safer than schema migrationdeleted_atparticipates in LWW as a regular field: a newer soft-deleted payload can soft-delete a local active observationnewSyncID()to{prefix}-{16hex}format; existing IDs accepted as-istxto prevent race conditions