osint/DUCKDB_STACK.md

Stack DuckDB: la base de datos como fuente de verdad del project osint

Documento de arquitectura y operación del stack DuckDB + Obsidian del project osint. Construido y verificado end-to-end el 12/06/2026. Sustituye (amplía) la decisión "KISS sin BD intermedia" del issue 0172: la base DuckDB pasa a ser la fuente de verdad estructurada del ecosistema OSINT, y el vault de Obsidian queda como capa de prosa + vista.

Visión general

                    ┌─────────────────────────────────────────┐
                    │  osint_db (FastAPI, 127.0.0.1:8771)      │
                    │  dueño ÚNICO de data/osint.duckdb        │
   Xandikos ───────▶│  /api/ingest/dav                         │
   (CardDAV/CalDAV) │  /api/ingest/vault ◀────── vault osint   │
                    │  /api/query · /api/query/named           │
                    │  /api/render/note ────────▶ notas .md    │
                    └───────────────┬─────────────────────────┘
                                    │ HTTP (requestUrl)
                    ┌───────────────▼─────────────────────────┐
                    │  Plugin Obsidian "osint-db"              │
                    │  code blocks ```osintdb en notas         │
                    └─────────────────────────────────────────┘

Tres piezas:

Pieza	Dónde	Qué hace
Service osint_db	projects/osint/apps/osint_db/	FastAPI local (solo 127.0.0.1, puerto 8771). Dueño único en escritura de data/osint.duckdb. Ingesta vault + DAV, sirve queries read-only y renderiza tablas dentro de notas.
Plugin osint-db	projects/osint/apps/osint_obsidian_plugin/	Plugin de Obsidian FINO (TypeScript, sin BD embebida). Ejecuta queries contra el service via HTTP y pinta tablas dentro de las notas.
Render headless	Funciones del registry	Proyecta resultados de query como tablas Markdown congeladas dentro de notas (bloques sentinel). Legible en móvil y en cualquier editor, sin plugin.

Modelo de datos: tres categorías de tablas

La regla central del diseño, en orden:

1. Tablas maestras con referencia a notas (schema main)

Fuente de verdad de las entidades estructuradas del vault. Cada fila lleva note_path (path relativo de su nota dentro del vault ~/Obsidian/osint).

Tabla	Origen	Clave
notes	índice completo del vault (path, slug, tipo, title, mtime, frontmatter JSON)	note_path
persons	personas/*.md (nombre, aliases, sexo, fecha_nacimiento, dni, telefono, email, direccion, pais, contexto, fuente, dav_uid, tags)	slug
organizations	organizaciones/*.md	slug
domains	dominios/*.md	slug
cases	casos/*.md	slug
places	lugares/*.md	slug

2. Tablas maestras DAV (schema main)

Eventos y contactos importados del servidor Xandikos (CardDAV/CalDAV). También fuente de verdad. contacts.note_path enlaza el contacto con su ficha del vault cuando hay match (por dav_uid extraído del campo fuente de la ficha, por teléfono o por email); puede ser NULL (contacto sin ficha — visibles con la named query contactos_sin_nota).

Tabla	Origen	Clave
contacts	colecciones CardDAV (uid, collection, etag, fn, tels, emails, raw, note_path)	uid
events	calendarios CalDAV (uid, calendar, dtstart/dtend, all_day, summary, location, rrule, raw)	uid

3. Tablas derivadas (schema derived)

Datos computados/extra para consultar desde Obsidian. REGLA DURA: ninguna tabla de derived lleva columna que referencie notas (note_path prohibido). Se reconstruyen (DROP + CREATE) en cada ingest. Hay un test que verifica la regla contra information_schema.

Tabla	Qué
derived.person_stats	agregados de personas por contexto, país y tag
derived.event_monthly	conteo de eventos por calendario y mes
derived.contact_link_quality	contactos enlazados a ficha vs sin enlazar (solo números)

Ownership por campo (regla anti two-way-sync)

Dato	Dueño	Dirección
Campos estructurados (entidades, contactos, eventos)	DuckDB	DB → nota (render)
Prosa libre de cada nota (cuerpo, notas de investigación)	Markdown	nunca se toca desde la DB
Frontmatter editado a mano en Obsidian	Markdown	se re-ingesta con /api/ingest/vault

Los bloques generados en notas van entre sentinels y no se editan a mano; el resto de la nota es del humano.

Inversión completada (13/06/2026)

La dirección de la verdad quedó invertida: DuckDB es ahora la fuente de verdad de los campos estructurados (personas, contactos, eventos), no un espejo. Cambios implementados:

• Ingest selectivo (anti-pisado): ingest_vault ya NO hace DELETE+INSERT ciego sobre persons. Si el slug ya existe en la DB, solo actualiza note_path + extra_fm (vía duckdb_upsert con update_cols restringido); los campos OWNED por la DB no se pisan. Una ficha nueva creada a mano en Obsidian se bootstrapea (INSERT completo). Verificado: un centinela DB-owned sobrevive a un POST /api/ingest/vault sobre las 697 fichas.
• Multi-valor: persons ganó telefonos/emails/direcciones/extra_fm (JSON) — migración 002_multivalue.sql, backfill desde los singulares (que se mantienen por compat, = primer elemento). contacts.tels/emails ya eran arrays. Las 634 fichas con dato se materializaron a multi-valor (DB→nota, preservando la prosa).
• Libretas/agendas de contactos: tabla addressbooks (003_addressbooks.sql). ingest_dav itera todas las libretas registradas. Crear libreta CardDAV nueva vía dav_make_addressbook (extended MKCOL).
• Endpoints de escritura estructurada (ver tabla API): CRUD de person/contact/event, addressbook/calendar, /api/person/{slug}/render (DB→nota) y /api/push/dav (reconcilia DB→Xandikos). La escritura DB va bajo el write lock; el push DAV y el render ocurren fuera de la transacción (no bloquean la DB con latencia de red).
• osint_web consume osint_db tras el feature flag OSINT_DB_BACKEND (en apps/osint_web/dev/feature_flags.json, hoy ON): la vista Contactos lee/escribe contra el service (DuckDB), con contactos multi-valor y libretas en la UI. Con el flag OFF vuelve al camino histórico (vault .md + vCard Xandikos directo).
• Funciones del registry nuevas (grupo duckdb/dav): duckdb_execute, duckdb_upsert, dav_make_addressbook, dav_list_addressbooks, build_vcard.

Runbook (evitar doble-verdad): con OSINT_DB_BACKEND ON, editar contactos/personas SOLO por la app (osint_web → osint_db) o por la API de osint_db. No editar el mismo campo a mano en el .md y por la app a la vez; el .md es la vista materializada desde la DB.

Runtime: osint_db corre como systemd user service osint-db.service (Restart=always), no como proceso manual. Health: curl 127.0.0.1:8771/api/health.

API del service (contrato)

Todas las respuestas son HTTP 200 con campo status en el body (ok | error); los clientes parsean el body, no el código HTTP.

Endpoint	Qué
GET /api/health	{"status":"ok","db_path":"...","tables":N}
GET /api/tables	catálogo: schema, nombre, kind (master/derived), row_count, columnas
POST /api/query	{"sql":"SELECT ...","params":[],"max_rows":500} → {status, columns, rows, row_count, truncated}. Solo lectura (conexión read_only=True).
GET /api/queries	catálogo de named queries (server/named_queries.py)
POST /api/query/named	{"name":"personas_por_contexto","max_rows":500} → misma shape que /api/query
POST /api/ingest/vault	escanea el vault completo, upserta maestras y reconstruye derivadas
POST /api/ingest/dav	baja colecciones Xandikos, upserta contacts/events, enlaza contactos con fichas
POST /api/render/note	{"note_path":"tableros/x.md","block_id":"y","sql":...|"query":...,"title":...} → renderiza tabla Markdown dentro del bloque sentinel de la nota (la crea si no existe)
POST/PUT/DELETE /api/person[/{slug}]	CRUD de persons (multi-valor) + materializa la ficha (DB→nota)
POST /api/person/{slug}/render	DB→nota: escribe el frontmatter OWNED (listas) preservando extra_fm y la prosa del cuerpo
POST/PUT/DELETE /api/contact[/{uid}]	CRUD de contacts + push DB→Xandikos (build_vcard + carddav_put_vcard / dav_delete_resource)
POST/PUT/DELETE /api/event[/{uid}]	CRUD de events + push DB→Xandikos (caldav_put_event / dav_delete_resource)
POST /api/addressbook	crea una libreta CardDAV (dav_make_addressbook) + registra en addressbooks
POST /api/calendar	crea un calendario (dav_make_calendar)
POST /api/push/dav	reconcilia DB→Xandikos en bloque (contacts/events de la DB → servidor)

Named queries incluidas: personas_por_contexto, personas_recientes, eventos_proximos, contactos_sin_nota, stats_personas, calidad_enlace_contactos, eventos_por_mes.

Plugin de Obsidian (osint-db)

Dentro de cualquier nota del vault, un code block con lenguaje osintdb:

```osintdb
query: contactos_sin_nota
max_rows: 25
```

o SQL crudo:

```osintdb
SELECT nombre, telefono, contexto, note_path
FROM persons
ORDER BY updated_at DESC
LIMIT 15
```

Comportamiento:

• Directivas soportadas al inicio del bloque: query: (named), max_rows:, title:. Sin directivas, el bloque entero es SQL crudo contra /api/query.
• Las celdas de una columna note_path cuyo valor termina en .md se pintan como enlace interno de Obsidian: click → abre la ficha. Así las tablas maestras enlazan a sus notas.
• Botón "Refrescar" por bloque. Errores del service se muestran en un callout; si el service no responde, mensaje con hint de arranque.
• Settings del plugin: base URL (default http://127.0.0.1:8771) y max_rows default.
• Comando de paleta: "OSINT DB: insertar bloque de query".
• HTTP siempre via requestUrl de Obsidian (evita CORS). Desktop only.

Render headless (sin plugin, legible en móvil)

Para tablas congeladas que viajan con la nota (sync móvil incluido), el service compone tres funciones del registry:

1. duckdb_query_readonly_py_infra — ejecuta la query con conexión read-only.
2. render_markdown_table_py_core — filas → tabla Markdown GFM.
3. upsert_sentinel_block_py_core — inserta/reemplaza el bloque entre <!-- osintdb:begin id=X --> y <!-- osintdb:end id=X --> de forma idempotente.

Ejemplo real: tableros/db-personas.md combina una tabla congelada (sentinel) y bloques vivos del plugin en la misma nota.

curl -s -X POST http://127.0.0.1:8771/api/render/note \
  -H 'Content-Type: application/json' \
  -d '{"note_path":"tableros/db-personas.md","block_id":"personas","query":"personas_por_contexto","title":"Personas por contexto"}'

Operación

# Arrancar el service (runtime manual, sin systemd por ahora)
cd /home/enmanuel/fn_registry/projects/osint/apps/osint_db
.venv/bin/python server/main.py            # flags: --vault --db --port --host

# Re-ingestar (tras editar fichas a mano o tras cambios en Xandikos)
curl -s -X POST http://127.0.0.1:8771/api/ingest/vault
curl -s -X POST http://127.0.0.1:8771/api/ingest/dav

# Desplegar el plugin tras un cambio
cd /home/enmanuel/fn_registry/projects/osint/apps/osint_obsidian_plugin
pnpm build && ./deploy.sh                  # copia a .obsidian/plugins/osint-db/

Activación del plugin: ya está activado headless (id osint-db en .obsidian/community-plugins.json del vault). Si Obsidian arranca en Restricted mode la primera vez, un toggle manual único en Settings → Community plugins.

Credenciales DAV: pass dav/xandikos-enmanuel (via pass_get_secret_py_infra). La config de colecciones espeja tools/sync_dav_to_osint.py.

Gotchas

• Single-writer DuckDB: solo el service escribe la base. Cualquier otro proceso lee con read_only=True (duckdb_query_readonly) o pasa por la API HTTP. Una lectura durante un ingest puede devolver status:error momentáneo por el lock exclusivo; reintentar.
• Versión del motor: no abrir el .duckdb con CLIs/WASM de versión distinta a la del venv (1.5.x). El formato de archivo puede divergir entre versiones mayores.
• read_only=True exige que el archivo exista — no crea bases nuevas.
• Los bloques sentinel no se editan a mano: el siguiente render los pisa. La prosa fuera de los sentinels nunca se toca.
• Migraciones del schema: archivos numerados migrations/NNN_*.sql con tabla _migrations (regla db_migrations del registry). Nunca borrar la base para "arreglar" el schema.

Cifras del primer ingest real (12/06/2026)

• Vault: 1175 notes, 697 persons, 367 organizations, 9 places.
• DAV: 1065 contacts (704 enlazados a ficha, 361 sin nota), 98 events.

Referencias

• Apps: apps/osint_db/app.md (service, e2e_checks) y apps/osint_obsidian_plugin/app.md (build, deploy, uso).
• Grupos de capacidad del registry: docs/capabilities/duckdb.md y docs/capabilities/obsidian.md (en el repo fn_registry).
• Convenciones del vault: CONVENTIONS.md (este project).
• Sync DAV ↔ vault preexistente: tools/sync_dav_to_osint.py / tools/sync_osint_to_dav.py.