Database schema

The IRIS Core 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"
    tipo_anotacion ||--o{ anotacion : "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

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`	→ `tipo_signo(codigo)`
`estado`	`text`	→ `estado(codigo)`
`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)
`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)

Column	Type	Notes
`id`	`bigint`	PK (identity)
`instancia_nro_solicitud`	`text`	→ `instancia` (a missing parent is stubbed/quarantined)
`tipo`	`text`	→ `tipo_anotacion(codigo)` — M1..M14
`fecha`, `fecha_vencimiento`	`date`
`observacion`, `seccion`, `seccion_nombre`	`text`
`created_at`	`timestamptz`

Dedup key uq_anotacion_event (instancia_nro_solicitud, tipo, fecha, md5(coalesce(observacion,''))) — 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`
`tipo_anotacion`	`codigo`	`seccion_nombre` — M1..M14
`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`)
`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, 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/.