Database schema

The Tarno corpus is plain SQL (no ORM): the migrations under ../db/migrations/ are the single source of truth. This page is a human-readable reference generated from those migrations. The corpus is neutral — zero RMC fields or cross-references (enforced by pnpm neutrality).

Engine: PostgreSQL 16 + pgvector (halfvec + HNSW), pg_trgm, fuzzystrmatch, unaccent, Spanish full-text search.
Migration tool: dbmate (each file has -- migrate:up / -- migrate:down).

Entity–relationship diagram

erDiagram
    instancia ||--o{ instancia_clase : "classes"
    instancia ||--o{ instancia_persona : "owners/agents"
    instancia ||--o{ anotacion : "events"
    instancia |o--o| instancia : "renewal chain"
    persona ||--o{ instancia_persona : "role"
    clase ||--o{ instancia_clase : ""
    estado ||--o{ instancia : "status"
    tipo_signo ||--o{ instancia : "sign type"
    pais ||--o{ persona : "country"
    raw_source ||--o{ dead_letter : "re-derive handle"
    consumer ||--o{ api_key : "owns"

Corpus (neutral entities)

`instancia` — the trademark application

⭐ estado and tipo_signo are typed with a CHECK, not a foreign key (migration …126, phase 37 ESQ-01), and instancia_clase.clase_numero with CHECK (BETWEEN 1 AND 45). The vocabulary is written LITERALLY into the constraint rather than derived from the table, so it stays true once ESQ-16 retires the catalogues. ✅ Friendly ES/EN/PT labels live in packages/core/src/i18n/ with Accept-Language negotiation; the 14 estado and 14 tipo_signo are complete. Spanish is INAPI's LITERAL wording, not a translation. ✅ The 45 Nice class headings are present IN SPANISH, transcribed verbatim from INAPI's online classifier (read 2026-09-14) — never written from memory. EN and PT are null: INAPI serves only Spanish, and Portuguese is not an official Nice language. The 45 Nice class headings — live in packages/core, not in the database.

⚠ descripcion_etiqueta no longer carries URLs (ESQ-03): the legacy mapper fell back to etiqueta, and in the legacy Mongo that field is the image URL, not a description. 34,262 rows were nulled and the fallback removed, in that order. The central entity. Natural key nro_solicitud (D-01); denominacion is an attribute, never a key (D-02). Enriched authoritatively by the Buscador; seeded (nulls-only) by Sheets.

Column	Type	Notes
`nro_solicitud`	`text`	PK — natural key
`nro_registro`	`text`	appears only on grant, nullable
`denominacion`	`text`	NOT NULL — the mark text
`tipo_signo`	`text`	`CHECK ck_instancia_tipo_signo` (14 valores, literales) — ya no es FK (`…126`)
`estado`	`text`	`CHECK ck_instancia_estado` (14 valores, literales) — ya no es FK (`…126`)
`tipo_nombre`, `subtipo_nombre`	`text`	INAPI tipo/subtipo (D-14)
`fecha_presentacion`, `fecha_publicacion`, `fecha_registro`, `fecha_vencimiento`	`date`	the four explicit dates (D-14)
`traduccion`, `descripcion_etiqueta`, `protection_description`, `imagen_url`	`text`	detail attributes (D-14)
`renovada_de`, `renovada_por`	`text`	self-FK → `instancia(nro_solicitud)` — renewal chain (D-17)
`embedding`	`halfvec(1024)`	semantic vector, HNSW index
`search_vector`	`tsvector`	generated — Spanish FTS over `unaccent(denominacion)`
`content_hash`	`text`	idempotency (INGEST-05)
`created_at`, `updated_at`	`timestamptz`

`persona` — holders & agents

Column	Type	Notes
`id`	`bigint`	PK (identity)
`pais`	`text`	→ `pais(codigo)`
`identificador`	`text`	RUT (Chile) or foreign id, nullable
`nombre`, `apellido`	`text`
`region`, `comuna`	`text`	optional (D-16) — CUT codes, no leading zero (`'13'` / `'13114'`); `99`/`99999` is the unknown/foreign sentinel. Not the street address
`search_vector`	`tsvector`	generated, FTS
`created_at`	`timestamptz`

Unique key uq_persona_pais_identificador (pais, identificador) where identificador IS NOT NULL; no-id personas reconcile best-effort by (pais, lower(nombre)).

`instancia_persona` — role bridge

Column	Type	Notes
`instancia_nro_solicitud`	`text`	PK, → `instancia`
`persona_id`	`bigint`	PK, → `persona`
`rol`	`text`	`titular` \| `representante` \| `ambas`

`instancia_clase` — Nice classes

Column	Type	Notes
`instancia_nro_solicitud`	`text`	PK, → `instancia`
`clase_numero`	`int`	PK, → `clase` (1..45)
`descripcion`, `estado`	`text`	per-class description/status

`anotacion` — Estado-Diario events (append-only)

⭐ seccion has a foreign key to seccion_catalogo.codigo as of migration …125 (phase 37, ESQ-10). There was none before: 166 of the 668 codes in use did not exist in the catalogue and 36,101 rows (0.32%) pointed at nothing. ⚠ seccion_catalogo.nombre is NULLABLE on purpose: there is no official listing of codes — INAPI, asked on 2026-08-26, requested "more background" — and legends are never invented. A NULL nombre means "the code exists and we do not know its legend", not "pending fill-in". Of the 166 seeded, 8 carried the title INAPI prints, harvested from the bulletin; the other 158 stay without a legend, and that is the datum. | Column | Type | Notes | |---|---|---| | id | bigint | PK (identity) | | instancia_nro_solicitud | text | → instancia (a missing parent is stubbed/quarantined) | | tipo | text | M1..M14, or null for an uncatalogued code. No FK since migration …118 (the tipo_anotacion catalog was retired); the guarantee now lives in the mappers. ⛔ It is INSIDE the natural key: populating it where it is null today would split the key's groups | | fecha, fecha_vencimiento | date | | | observacion, seccion, seccion_nombre | text | ⛔ observacion is INSIDE the key (via its md5): changing one byte does not collide, it duplicates | | representante, solicitante | text | The two names the bulletin prints in its own column (migration …081). TEXT, not persona: the bulletin carries a bare name and a person's identity is (pais, identificador) | | partes | text | The litigant pair of the C family (…081). ⭐ The only datum no other source publishes — neither Sheets nor the Buscador give the pair | | tipo_resolucion | text | The bulletin's «Tipo de resolución» label, verbatim (…082) | | seccion_nombre_especifico | text | The title the bulletin prints for that specific section | | fuente | text | Which source produced the row: estado_diario | buscador. Two values, not four since migration …120: legacy_mongo moved to via_importacion (it was a ROUTE, not an origin) and desconocido was retired for having no possible emitter. ⛔ Outside the natural key, the guard and the contentHash | | fuente_origen | text | Which signal produced fuente, so an inference is not read as a fact: ingesta (the emitter asserted it) | evidencia (a column only the bulletin writes) | ventana (the import pass or the sync_run window, …120) | codigo (the only one that can be wrong). Priority: evidencia > ventana > codigo | | via_importacion | text | BY WHICH ROUTE the row arrived, a different question from who wrote it (…120): legacy_mongo = it came in the legacy Mongo dump; null = direct ingest. ⭐ It exists because the legacy dump was a route for two sources, and conflating them cost 369,171 bulletin rows in a WHERE | | evento_id | bigint | WHICH ROWS DESCRIBE THE SAME FACT (…122). It is the min(id) of the group, NOT the id of the canonical row: that separates "which rows are the same fact" (a backfill revertible per lote) from "which one is shown" (a read rule), and the second already changed once. ⭐ Being the minimum makes it stable under growth: ids are monotone, so a new row never lowers the minimum of the group it joins. ⛔ NULL means "this row is its own event" and it is for 89.8 % of the corpus: every read groups by coalesce(evento_id, id), never by evento_id alone. ⛔ Outside the natural key, the guard and the contentHash; neither copy of upsertAnotacion writes it | | evento_metodo | text | WHICH SIGNAL grouped the rows: equivalencia_seccion (same mark, same day, and the two sections form a confirmada pair in seccion_equivalencia — 297,805 events / 595,853 rows) | a_favor_de (same (mark, fecha, seccion), exactly two rows, one carries the "A Favor de:" prefix and the two proses match once it is removed — 279,008 events / 558,016 rows) | manual. Same doctrine as fuente_origen: an inference must not read as a fact. ⭐ It is what allows reverting one family without the other inside a single lote | | created_at | timestamptz | ⚠ The row's CREATION time, not its last write: rows created by the legacy import in June were enriched later by our parser |

!!! danger "⛔ This page said the opposite until 2026-09-10, and it was the ALREADY-FIXED defect" This block used to carry the key without seccion and a warning saying «seccion is NOT in the key, and that drops rows»: that for a non-M code with no observation the key collapsed to (solicitud, fecha) and the day's second row vanished without trace. It quoted a real measurement — 3,016 rows dropped, 5.37 %, 012 + OPO08 the worst pair.

**It was true, and migration `…072` fixed it.** The page kept describing the problem instead of
the fix, so for weeks it told every reader that the corpus silently loses rows. **Today `seccion`
is INSIDE the key** and that case does not exist.

📌 The lesson, which is why this is left written instead of deleted: a page that documents a
defect must be re-read **the day the defect is fixed**, not only when the schema changes.

Dedup key uq_anotacion_event_seccion (instancia_nro_solicitud, tipo, fecha, seccion, md5(coalesce(observacion,''))), with NULLS NOT DISTINCT — a repeated event is a clean DO NOTHING (events never mutate).

Catalogs

Table	PK	Other
`estado`	`codigo`	`nombre` — controlled status set
`tipo_signo`	`codigo`	`nombre`
`pais`	`codigo`	`nombre` — full ISO set + deprecated codes
`clase`	`numero`	`nombre` — Nice classes, `CHECK (numero BETWEEN 1 AND 45)`

An unknown catalog value is accepted as null + logged (D-09) — a new INAPI value never halts ingestion.

Ingest / operations

Table	Key	Purpose
`sync_state`	`source` (PK)	per-source resume cursor (`cursor jsonb`) plus movement statistics (migration `…124`): `last_movement_at` (MONOTONIC — written with `greatest`, because backfilling an old bulletin stamps `occurred_at` in the past and must not push freshness backwards) and `movement_count` (CUMULATIVE: how many changes the source has produced ever, not `count(movement_log)` — retention deletes rows and never touches this counter). Maintained by `recordMovement` in the SAME transaction that writes the movement
`movement_log`	`id`	append-only change feed (`entity_type`, `entity_id`, `change_type`, `source`, `occurred_at`, `content_hash`) — drives the Buscador work-list
`raw_source`	`id`	retained raw bytes (`storage_path` under `RAW_STORAGE_DIR`, `content_type`, `size`) — re-derivability (D-10)
`dead_letter`	`id`	quarantine (`source`, `reason`, `raw_source_id` → `raw_source`, `payload jsonb`) — non-aborting (D-07)
`sync_run`	`id`	run history (`source`, `started_at`, `finished_at`, `status ok\\|failed\\|running`, `processed`, `changed`, `quarantined`, `error`) — OBS-03

Access control

Table	Key	Purpose
`consumer`	`id`	org/tenant (`name` UNIQUE)
`api_key`	`id`	`consumer_id` → `consumer`; `key_hash` (sha256, UNIQUE — never plaintext), `prefix`, `scopes text[]`, `monthly_quota int` (`>= 0`), `expires_at`, `rotated_at`, `revoked_at`

Scopes: brands:read (/v1/brands, MCP search_brands/get_brand_detail), insights:read (/v1/freshness, /v1/sync-runs). Empty scopes = unrestricted. Quota counters live in Redis (shared REST ↔ MCP), not Postgres, so the read path stays SELECT-only.

Key indexes

FTS: GIN on instancia.search_vector and persona.search_vector (Spanish, accent-insensitive via unaccent).
Fuzzy: pg_trgm GIN/GiST on denomination for typo-tolerant search.
Vector: HNSW on instancia.embedding (halfvec, cosine) for semantic similarity.
Reconcile: partial idx_persona_noident_reconcile (pais, lower(nombre)) WHERE identificador IS NULL.
Natural keys / dedup: uq_anotacion_event_seccion, uq_persona_pais_identificador, plus the table PKs above.

Migrations are applied via dbmate up; the full DDL (indexes, generated columns, FK ordering, Spanish FTS config) lives in ../db/migrations/.