SQL Editor → DuckDB: retiro del shim legacy + DML tipado e2e

0 · El diagnóstico (una línea)

Hoy el SQL Editor no tiene motor: el modo internal traduce Spark→Postgres por regex (dialect.ts) y ejecuta un CTE sobre dataset_rows.data JSONB (translateQuery/baseCteString/jsonbCast), sirviendo un information_schema/SHOW/DESCRIBE falsos sintetizados desde la tabla PG datasets. El Warehouse real (Iceberg/R2/Lakekeeper) nunca se toca, y el tipo se pierde por el cable (la respuesta son filas sin schema; el frontend re-infiere por Object.keys(rows[0])). duck-server ya lee Iceberg real vía Arrow, pero es un ejecutor delgado (sin tipos-en-JSON, sin errores estructurados, sin resolución de nombre lógico, sin identidad/workspace, guard read-only).

1 · Arquitectura objetivo

SqlEditorTab / SqlConsoleDock
      │  POST /api/sql-editor/execute   (la route CONSERVA su rol de puerta PDP/PEP + emisor del envelope)
      ▼
  clasificador (resuelve/valida — fase durable)
      │
      ▼
  JUNCTION (lib/compute/gateway.ts — semilla = la facade loadItemForConsumption)
      │
      ▼
  duck-server  POST /query  (Arrow IPC TIPADO)
      │
      ▼
  Lakekeeper (Iceberg REST)  →  R2

• La route deja de ser motor; sigue siendo la puerta (gobernanza) y el traductor del envelope {success,data,totalRows,executionTimeMs,columns[],...}.
• Lectura: la route resuelve el nombre lógico → coordenada física (iceberg_namespace desde datasets.schema_id), DuckDB scanea Iceberg y devuelve Arrow con schema; la route decodifica Arrow→filas + columns:[{name,type}].
• Metadata: information_schema/SHOW/DESCRIBE del catálogo REAL vía duckdb_tables()/duckdb_columns() (el catálogo REST no expone information_schema — duck-server shimea su FORMA). Tier (datasets.tier) y DESCRIBE HISTORY (dataset_transactions) siguen PG-sourced y se fusionan.
• Escritura: el gate read-only se abre solo para DML ruteado por la puerta, ejecutado por duck-server envuelto en withNativeControlPlane (ledger dataset_transactions → DML → commit ledger + iceberg_sync_log + stats de datasets). Gobernanza (RLS/workspace/purpose) enforced aguas arriba, nunca en un path directo UI→duck-server.

2 · Fases (inert-first: shadow → flip → retire)

3 · Retire list (lo que se elimina, con refs)

• lib/warehouse/query/dialect.ts — archivo entero (Spark→PG). Y su call-sites en route.ts:45,488,869,1157,1192.
• lib/warehouse/query/dialect.test.ts — entero (pinnea salida PG de un módulo que se borra; NO portar).
• route.ts:843-897 translateQuery · :905-927 baseCteString (el core del shim: data->>'col'::cast FROM dataset_rows) · :299-310 jsonbCast.
• route.ts:77-105 extractTableNames + :113-118 rewriteDottedFromRefs + :137-146 mergeWithCtes (munging regex).
• route.ts:1155-1177 rama information_schema + :813-831 buildInfoSchemaRows (las 8 columnas fijas → bug character_maximum_length).
• route.ts:175-182 extractExplainPrefix (DuckDB tiene su EXPLAIN).
• route.ts:151-166 isReadOnly + :1135-1140 — NO se borra: se ABRE en F4 para DML gobernado.
• lib/lakehouse/sql-source-hydration.ts — rama hidratada inline-jsonb (la facade como puerta sobrevive; la materialización a jsonb no).
• item-consumption.ts:605-611 — el THROW del PG-sink en write() (WRITE legacy del Paso 6) → ruta compute/Junction.
• UI: SqlEditorTab.tsx:40-56 inferColumnType + :641 Object.keys y SqlConsoleDock.tsx:166 → degradan a fallback tras data.columns tipadas.

4 · Plan de tipado (el "SQL tipado")

Cadena canónica sin vocabulario paralelo — todo desemboca en el mismo set BASE_TYPE:

1. Inverso Arrow→BASE_TYPE: crear arrowTypeToBaseType(field) co-ubicado en lib/types/iceberg-types.ts junto a BASE_TYPE_TO_ICEBERG, para que los dos sentidos no diverjan. Consistente con arrow_type_for_iceberg (ml-runner types.py) → round-trip BASE_TYPE→Iceberg→Arrow→BASE_TYPE estable, con test.
2. Overlay semántico (crítico): los 12 JSON_ENCODED (Object/Map/Array/Vector/TimeSeries/GeoPoint/…/Media) se serializan a Iceberg string y vuelven como Arrow Utf8. El schema Arrow da el tipo físico pero no distingue String real de Object aplanado → fusionar el schema Arrow con datasets.schema por nombre de columna para decode (decodeIcebergRow) + label. En el MISMO lugar que read-client ya lo hace para NDJSON.
3. Canal de schema: añadir columns:[{name,type}] al envelope; el UI consume el tipo real (Object.keys/inferColumnType = fallback). TypeBadge sigue por normalizeDbType pero alimentado por tipo real.
4. Precisión: DECIMAL/BIGINT > Number.MAX_SAFE_INTEGER → string tipado (evita pérdida silenciosa); renderResultCell decide por tipo declarado, no por typeof.
5. Dependencia dura: el tipado sólido solo existe por el path Arrow (format=arrow). El JSON lossy (solo column_names) no sirve.

5 · Plan de gobernanza (sin bypass)

La puerta (loadItemForConsumption/Junction) es el único PDP/PEP; no hay ruta directa UI→duck-server. Reconciliación con el write-terrain Tier B: factorizar withNativeControlPlane (iceberg-native-write.ts:155) para que el paso DATA-plane sea pluggable al brazo "duck-server ejecuta el DML". Bundle atómico preservado: ledger open → duck DML (commit Iceberg) → commit ledger → iceberg_sync_log succeeded+snapshot (alimenta el oráculo de frescura; sin él los reads STRICT sirven stale) → update datasets (row_count/size/schema/PK/schema_version). Invariantes: unicidad de coordenada (workspace,schema_id,name) → un CTAS mint-ea datasets y reserva la coordenada ANTES del write físico; tier medallón vía tierFromName; lineage derivando sourceRefs del FROM/USING → mergeUpstreamProvenance + arista con el nodo Warehouse. Credencial: llaves R2 estáticas interim (vending roto #792); slot gobernado = norte.

6 · Plan E2E (hoy = CERO)

No hay NI UN test que ejerza route.ts o corra SQL real asertando resultados. El único test pinneado (dialect.test.ts) es de un módulo que se borra = falsa cobertura. Harness, en orden:

1. Extracción helpers de route.ts → classify.ts/catalog.ts → classify.test.ts (resolución de nombres, gate, EXPLAIN, LIMIT).
2. Diferencial (la red ausente): scripts/duckdb/f1-differential-parity.ts — diffea PG-shim↔duck-server por query (filas, orden, NULLs, casts, DAYOFWEEK 0/1-based, REGEXP_EXTRACT ”vs NULL, DATEDIFF por unidad). Gate del flip F3.
3. E2E tipado: scripts/duckdb/f1-sql-editor-harness.ts — SELECT tipado + round-trip Arrow→BASE_TYPE + overlay de los 12 complejos, en main.test del Lakekeeper de prod (patrón = pipeline-output-canary.ts).
4. info_schema real (character_maximum_length funciona).
5. DML (F4): scripts/duckdb/f4-dml-harness.ts — cada verbo, rows_affected/snapshot_id, read-your-writes, tier, mint de datasets, MoR positional-delete.

7 · Top riesgos (con mitigación)

1. Split-brain PG-ledger ↔ commit-Iceberg — un snapshot DuckDB committeado NO es reversible desde PG. → reconcile-from-catalog (sweep que lee el snapshot real y repara el ledger) o compensación idempotente. Owner de la frontera atómica a decidir.
2. Divergencia semántica silenciosa Spark/PG↔DuckDB. → harness diferencial gatea el flip F3 ANTES de borrar dialect.ts.
3. Doble fuente de verdad de nombres (datasets vs Lakekeeper). → un solo owner de la resolución nombre→FQN + un SSOT del catálogo.
4. IDOR de workspace — duck-server sin identidad corre cualquier SQL sobre TODO el catálogo. → claim firmado / header de confianza solo desde el tier Node.
5. Tipado depende de Arrow — si UI/Junction consume JSON lossy, el tipo se pierde. → Arrow como path preferente; fijarlo en el contrato de Junction.
6. Precisión numérica (BIGINT/DECIMAL) · Overlay semántico ignorado · Pérdida de tier/lineage · Vending roto (#792) · Posicionamiento de error tras rewrite.

8 · Decisiones abiertas (del owner, antes de implementar)

1. Auth duck-server: ¿JWT firmado de workspace, mTLS, o X-Workspace-Id de confianza del tier Node? (hoy Bearer compartido, sin identidad → riesgo IDOR).
2. Resolución nombre-lógico→FQN (el linchpin): ¿Node pre-cualifica el FROM (reescribe a iceberg_namespace) o duck-server bindea? → define qué se retira de route.ts y el SSOT del catálogo.
3. DML: directo vs control-plane + quién posee la frontera atómica (reconcile-from-catalog vs compensación) cuando el commit Iceberg tiene éxito pero el update PG falla.
4. Transporte: ¿UI/Junction consume Arrow directo, o Node adapta a {success,data,columns,...}? → define si el tipado va en JSON y si los edits de UI son aditivos.
5. Alcance del retiro del dialecto: hard-delete en F3 respaldado por el harness, o shim mínimo hasta cerrar paridad.
6. CTAS e identidad · Tier (PG vs propiedades Iceberg) · Vistas (Iceberg nativa vs objeto-catálogo PG) · Multi-statement transaccional · DESCRIBE HISTORY · Nivel de test — decisiones de F2/F4 (se cierran al llegar).

9 · Estado de implementación

Decisiones §8.1-8.4 cerradas (2026-07-09): secuencia = harness-first (F1.0+F1.1) → checkpoint; FQN = Node pre-cualifica (datasets = SSOT); auth = JWT de workspace firmado; transporte = Node adapta → envelope (hop duck→Node en JSON tipado con BIGINT/DECIMAL como string; Arrow queda canónico para Junction/UI-directo).

• F1.0 · contrato duck-server — ✅ HECHO + verificado local (2026-07-09): /query devuelve columns:[{name,type,nullable}] (tipo lógico desde el schema Arrow vía pa_type_to_logical), BIGINT/DECIMAL/binary serializados como string (precisión), errores estructurados ({error_type,message,line,detail}), y auth JWT de workspace (require_principal: JWT firmado → Bearer estático → abierto). Commit en feat/duck-server. Falta el redeploy con DUCK_JWT_SECRET seteado en Railway (Duck + tier Node) para activar el JWT — inerte hasta que la route llame.

• F1.0 · cliente Node — ✅ HECHO + verificado (2026-07-09): duck-client.ts (firma JWT HS256 + POST JSON tipado + overlay de los 12 JSON_ENCODED vía iceberg-decode.ts), fqn.ts (nombre lógico→lake.<ns>.ds_<id>, espeja native_table_identifier de writer.py), pre-qualify.ts (+test 7/7). Harness scripts/duckdb/f1-sql-editor-harness.ts VERDE 15/15 contra duck-server: tipos, precisión BIGINT, overlay, timestamptz, error estructurado, write-guard.

• F1.1 · diferencial de paridad — ✅ VERIFICADO contra datasets reales (2026-07-09): scripts/duckdb/f1-differential-parity.ts animal_events diets → copias de prod (main.default) con paridad delta=0 (filas + valores idénticos). El diferencial destapó y arregló 2 bugs de integración (commit 0402cd1): (a) FQN de namespaces multi-nivel — main.default es un schema con punto; DuckDB exige citarlo (lake."main.default"."ds_x"), sin citar da NameListToString NOT IMPLEMENTED; (b) clock-skew JWT → leeway=60s. Nuance F3: timestamptz se renderiza distinto (PG=UTC …Z vs DuckDB=local +02:00) — MISMO instante, normalizar el display en el flip. Nuance F2: duckdb_tables()/duckdb_schemas() NO enumeran namespaces multi-nivel (lazy) → el listado del catálogo debe resolverse por otra vía. Las copias main.test (PG=0/Iceberg>0) son artefactos de canary, no divergencia.

• F1.1 · read-shadow — ✅ cableado (flag-gated, INERTE): shadow.ts + rama en route.ts (SQL_EDITOR_DUCK_SHADOW=1 → ejecuta el SELECT también en duck-server pre-cualificado y diffea vs PG, sin tocar la respuesta; skip vistas; try/catch total). Typecheck del proyecto limpio (mis ficheros 0 errores). El diferencial vive en el shadow (loguea rowCountDelta + columnas por query). Pendiente: activar SQL_EDITOR_DUCK_SHADOW=1 en un entorno con duck-server alcanzable desde Node + prod PG, correr queries reales y barrer los diffs. El flip/retiro (F3) NO arranca hasta que el shadow salga limpio.

• Deploy consolidado a main (2026-07-10, PR #26): Railway Duck y Vercel Next despliegan de main. DuckDB CONFIRMADO sirviendo datos reales (query directa al desplegado → filas reales de animal_events tipadas). El shadow loguea el diff en Railway (duck-server), no en Vercel: Node manda {pg_rows,pg_cols} en la misma /query y duck-server emite [shadow] {ok,delta,cols_only_*} (excluye columnas __ de identidad). Flag SQL_EDITOR_DUCK_SHADOW acepta 1/true/yes/on. Fix colateral: buildCatalog resuelve nombres duplicados al MÁS RECIENTE (arreglaba COUNT=0 sobre tabla con datos).

• F2 · information_schema real — backend ✅ HECHO+verificado (2026-07-10): endpoint POST /columns en services/duck-server/app/routers/metadata.py (motor DuckEngine.columns_for) → carga la tabla (LIMIT 0, el catálogo REST es lazy) y devuelve information_schema.columns REAL con la forma estándar COMPLETA: character_maximum_length, numeric_precision/scale, is_nullable, ordinal_position, column_default. Arregla de raíz el bug SELECT character_maximum_length FROM information_schema.columns → "column does not exist" (el shim tenía 8 columnas fijas). Verificado contra animal_events.

• F2 · Node ✅ HECHO+verificado (2026-07-10): lib/warehouse/query/metadata-client.ts (duckColumns) + buildInfoSchemaRows (route.ts) ahora enriquece con metadata real de /columns (best-effort, gated en DUCK_SERVER_URL, fallback total a null si falla) — data_type se mantiene como el BASE_TYPE (compatibilidad, no divergir) y se AÑADEN character_maximum_length/numeric_precision/numeric_scale reales; la CTE (route.ts:1167) ampliada con esas 3 columnas → el bug queda arreglado (la columna existe, real o null). duckColumns verificado e2e (8 cols, prec 64/32). fetchUserWarehouse +workspace_id. Pendiente F2 opcional: SHOW/DESCRIBE también desde metadata real (hoy PG-sourced, aceptable); tier + DESCRIBE HISTORY siguen PG-sourced.

• F3 · FLIP de lectura ✅ HECHO (flag-gated, REVERSIBLE — el RETIRO NO): rama en route.ts (helper buildDuckRefs compartido con el shadow): con SQL_EDITOR_DUCK_READ=1/true/yes/on, el SELECT interno se ejecuta en duck-server (motor real sobre Iceberg, pre-cualificado) devolviendo filas + columns:[{name,type}] TIPADAS, con fallback TOTAL al shim PG si off/vista/EXPLAIN/error. Oculta las cols __ de identidad (paridad SELECT *). Verificado e2e (preQualify→duckQuery→envelope tipado sobre animal_events). DELIBERADAMENTE NO se retira nada (dialect.ts, translateQuery, baseCteString, jsonbCast, info_schema sintetizado siguen como fallback) — el RETIRO es el gate de "shadow limpio" (validación e2e diferida). EXPLAIN + vistas siguen por PG. Nuance: el flip NO reescribe dialecto Spark→PG (DuckDB habla SQL real) → funciones Spark-only (DATEDIFF, REGEXP_EXTRACT…) pueden diferir; el shadow/validación las caza antes del retiro.

• F3-fin · DuckDB AUTORITATIVO por defecto ✅ (2026-07-10): el flip dejó de ser opt-in → el SELECT interno va a DuckDB por DEFECTO (kill-switch SQL_EDITOR_DUCK_READ=0/false/off), con fallback PG en error + PG para vistas/EXPLAIN. Gate pasado: sweep de 12/12 queries diversas (CTE+window, subquery, CASE, HAVING, string/math/cast, LIKE/ILIKE, EXTRACT, UNION) verde en DuckDB + diferencial delta=0 + POST /query 200 en prod. DML del motor probado (CTAS/INSERT/UPDATE/DELETE/DROP e2e sobre main.test, aislado). HALLAZGO — el BORRADO FÍSICO del shim está BLOQUEADO: translateQuery/dialect.ts/baseCteString/jsonbCast siguen VIVOS para (a) expandir VISTAS (CREATE VIEW → SELECT expandido) y (b) el EXPLAIN de PG. Retirarlos ahora rompe esos dos. Retire-list real = primero migrar vistas+EXPLAIN a DuckDB, LUEGO borrar.

• F3-RETIRO · Fase A — vistas + EXPLAIN en DuckDB ✅ (2026-07-10, inert-first): desbloquea el borrado. Nuevos módulos: lib/warehouse/query/duck-plan.ts (buildDuckReadPlan — expande VISTAS a CTEs DuckDB recursivas en orden topológico, base→FQN in-place, guard de ciclos+profundidad, hasViews; duckExplainPrefix — normaliza a EXPLAIN/EXPLAIN ANALYZE nativo, descarta opciones PG) + lib/warehouse/query/sql-parse.ts (extraídos de route.ts: extractTableNames/extractCteNames/rewriteDottedFromRefs — puros, COMPARTIDOS; NO son el shim de dialecto, sobreviven porque DuckDB tampoco parsea namespaces con punto sin comillas). Verificado con unit duck-plan.test.ts 14/14.

• F3-RETIRO · Fase B — BORRADO del shim ✅ (2026-07-10). El SQL Editor ya NO tiene shim PG; DuckDB es el motor de lectura de las TABLAS del Warehouse.

  ⚠️ Matiz verificado en F4 (2026-07-28): "motor ÚNICO" era inexacto. Queda un path SQL sobre Postgres en la route: information_schema.columns (route.ts, vía executeReadOnlyQuery), que sirve las filas como CTE desde jsonb_to_recordset. No es shim: es una tabla virtual del plano de metadata —la propia rama rechaza unirla con tablas reales— y por decisión del owner se queda ahí, coherente con DESCRIBE/SHOW. Lo retirado por F3-RETIRO fue el shim de traducción (dialect.ts/translateQuery/baseCteString/jsonbCast), no toda ejecución en PG. Ver junction-f4-sql-editor.md §1.

  • GATE PASADO: scripts/duckdb/f3-views-explain-harness.ts VERDE contra duck-server vivo sobre 2 datasets reales (animal_events, diets): vista simple/anidada/agregación corren sobre el motor + paridad columnas/rowcount vista↔directo + EXPLAIN y EXPLAIN ANALYZE nativos. (Paridad de tablas base ya estaba: sweep 12/12 + diferencial delta=0 en F3-fin.)
  • BORRADO: ficheros lib/warehouse/query/dialect.ts + dialect.test.ts + shadow.ts (git rm). Funciones en route.ts: translateQuery, baseCteString, jsonbCast, buildDuckRefs, qIdent, sLit (muertas). Imports retirados: rewriteDialect, runReadShadow, resolveSqlSource/hydratedFrom/SqlSourcePlan, DuckRef. Call-sites de rewriteDialect eliminados (handleCreateView valida solo detectando nombres; info_schema sin rewrite). El read-path es ahora: detectar tablas → buildDuckReadPlan → duckQuery (EXPLAIN con prefijo nativo); en fallo devuelve error estructurado (errorType/line para el marker Monaco), sin fallback (no hay a qué caer). Kill-switch SQL_EDITOR_DUCK_READ y flag SQL_EDITOR_DUCK_VIEWS retirados con el shim.
  • Verificado: tsc 0 errores en todo el proyecto + vitest lib/warehouse/query/ 21/21 + grep sin referencias de código colgando.
  • SOBREVIVE en PG (self-contained, NO es el shim): el path information_schema.columns (rows sintéticas + mergeWithCtes/ensureLimit/withExplain/extractExplainPrefix — enriquecido con metadata real de duck en F2); DESCRIBE/SHOW/tier/DESCRIBE HISTORY (metadata-plane PG); DDL curado (CREATE/DROP VIEW, ALTER RENAME). rewriteDottedFromRefs sobrevive (movido a sql-parse). El jsonbCast de app/api/dashboards/query/route.ts es OTRO módulo (dashboards), fuera de alcance.

Plan vivo. Siguiente: F4 · DML gobernado (la bestia) — abrir el gate para DML por la puerta, ejecutado por duck-server envuelto en withNativeControlPlane (ledger dataset_transactions → duck DML allow_writes:true → commit ledger + iceberg_sync_log succeeded+snapshot + update datasets stats). CTAS con identidad (mint fila datasets + reservar coordenada única (ws,schema_id,name) ANTES del write físico), estampar tier (tierFromName) + lineage (sourceRefs del FROM/USING → arista con el nodo Warehouse), y la decisión de frontera atómica (riesgo #1 split-brain PG-ledger↔commit-Iceberg → reconcile-from-catalog vs compensación idempotente). El motor de lectura ya es DuckDB ✅; el DML del motor ya está probado aislado ✅ — falta la GOBERNANZA. Luego F5 · Junction (lib/compute/gateway.ts). Ver §5/§7.