MGC Knowledge Graph: Integration Guide

Engineer-facing reference for wiring the research-kg knowledge graph into the live Mapping Global China site: warehouse Postgres, the /kg API, and the frontend map pages. This is the exact reference (file paths, endpoint shapes, SQL). For the visual overview, see the separate one-pager.

All file paths below were read directly from the working trees at: kg-handoff/, mgc-api/, mgc-frontend/, mgc/, design/system/.

A. Overview and the one decision

research-kg extracts a knowledge graph from the MFA press-conference corpus and academic papers: typed, NLI-verified relations; reified claims with stance; dated events; every node/edge/claim carries provenance (source document, character span, evidence text, extractor, model, confidence). It runs locally on SQLite. This package lands it in the warehouse Postgres.

Your first decision: Option A or Option B.

• Option A, standalone kg.* (fastest). Create kg.* from postgres_kg_schema.sql, load mgc_kg_data.sql.gz and map_link.sql as they are. Self-contained, keyed by research-kg's own text node ids (org:china-communications-construction, country:AFG, claim:<hash>, ...). Queryable immediately. Its entities are not linked to global.entity_registry.
• Option B, integrated, keyed to global.entity_registry. Add a kg.node_map bridge table (node_id to entity_registry.entity_id uuid) and reconcile the KG's entities onto the registry, so the graph's relations, claims, and events attach to the same canonical entities the rest of the warehouse uses. More work.

The /kg endpoints in mgc-api/app/routes/kg.py are already written for Option B. Every handler joins global.entity_registry for names (_fetch_entity, _fetch_relations, kg_search, kg_claims, kg_events, kg_path), and every entity_id path/query parameter is typed Annotated[UUID, ...] (see kg.py:261,292,384,451,517-518). If you pick Option A, that SQL needs to be repointed at kg.nodes/text ids before it will run. You do not have to write that port from scratch: kg-demo/local_kg_api.py is a complete, working Option-A rewrite of the same /kg/* contract over SQLite (same routes, same response shapes, entity_id is a plain text node_id throughout). Diff it against mgc-api/app/routes/kg.py to see exactly which lines change.

Option B reconciliation snags (from kg-handoff/README.md, confirmed against the design docs):

1. research-kg's Project and Framework entities (Gwadar, CPEC, BRI, ...) are not in entity_registry and are excluded from its org seed. Expect a large propose-new batch.
2. Two resolvers disagree: research-kg's BGE-M3 cross-lingual clustering (mgc/mgc_kg/resolve.py) versus the registry's trigram resolver (entity_resolver/) can over-merge or under-merge the same entity. Reconcile at the canonical-component grain and route ambiguous cases to global.review_queue rather than auto-merging.
3. research-kg's press-QA and papers corpora are not yet bridged into global.documents, so the provenance foreign keys (source_doc_id) need those corpora ingested or bridged first, or the joins to global.documents in kg.py (d.source_url, d.title, d.published_date) return null for every row.

design/system/SYSTEM_DESIGN.md section 4.1 documents a specific, lower-cost version of Option B worth knowing about even if you start with Option A: load the data physically unchanged (Option A shape) but serve it through a kg.node_map bridge table keyed by node_id, with entity_id populated incrementally (mapped / proposed / unmapped) as the registry resolver catches up, rather than blocking the whole graph on a single reconciliation pass. An unmapped node still renders under its own node_id, name_en, and country context; it just does not cross-link into warehouse dossiers yet. If you go this route, the API change is mechanical: repoint the 4 global.entity_registry joins in kg.py to kg.node_map instead (same column names).

B. Loading the graph

Files in kg-handoff/:

• postgres_kg_schema.sql: creates CREATE SCHEMA kg and 9 tables (documents, nodes, node_mentions, edges, map_link, claims, aliases, embeddings, place_mentions, pipeline_runs), pgvector extension, and the indexes listed in the file.
• mgc_kg_data.sql.gz: gzipped SQL dump (21 MB) of 7 of those tables: documents, nodes, node_mentions, edges, claims, aliases, place_mentions. No embeddings or pipeline_runs rows are included.
• map_link.sql: the map<->KG bridge table, map_link, as a separate dump (1 MB, 8,441 rows). See section C.

Step 1: create the schema.

psql "$DATABASE_URL" -f kg-handoff/postgres_kg_schema.sql

Step 2: load the data dump. Do not pipe it straight into psql. The dump is a raw SQLite .dump export: its own CREATE TABLE statements name the tables kg_documents, kg_nodes, kg_node_mentions, kg_edges, kg_claims, kg_aliases, kg_place_mentions (underscore, no schema prefix), not kg.documents / kg.nodes / etc. Column order and types line up 1:1 with postgres_kg_schema.sql (verified by inspection: e.g. kg_documents's 14 columns match kg.documents's 14 columns in the same order), but the table names do not match the schema you just created. Run the dump's own CREATE TABLE ... statements and you get a second, useless set of flat tables outside the kg schema (in whatever schema is first on search_path, typically public) while the real kg.* tables you made in step 1 stay empty. The dump also wraps everything in one BEGIN TRANSACTION; ... COMMIT;, so a single bad row can abort the entire load if you run it unmodified.

The practical fix: extract only the INSERT lines and rewrite the kg_ prefix to kg. (every table name in the dump follows exactly this pattern, so a literal string substitution is correct, not a regex needed):

gunzip -c kg-handoff/mgc_kg_data.sql.gz \
  | grep '^INSERT INTO kg_' \
  | sed 's/^INSERT INTO kg_/INSERT INTO kg./' \
  | psql "$DATABASE_URL"

Each INSERT is single-row (one statement per row), so without an explicit transaction wrapper a bad row is skipped and the rest of the load continues; wrap in BEGIN; ... COMMIT; yourself once you have done a first pass and confirmed it is clean.

JSON columns to watch: documents.external_ids, documents.attrs, nodes.attrs, edges.attrs, and claims.subject_node_ids are TEXT in the SQLite source and JSONB in postgres_kg_schema.sql. Postgres will implicitly cast a valid JSON text literal ('{}', '[]', a JSON object string) into a jsonb column on insert, and a SQL NULL loads fine regardless of declared source type. What will fail a row is an empty string ('') landing in a jsonb column, since '' is not valid JSON. Spot-check a sample after loading (SELECT node_id FROM kg.nodes WHERE attrs::text = '' type queries) rather than assuming the SQLite export never produced one.

map_link.sql has the identical table-name issue (CREATE TABLE kg_map_link, INSERT INTO kg_map_link) and one more wrinkle: its entity_id column is declared TEXT in the dump versus UUID in postgres_kg_schema.sql's kg.map_link. Every value in the current dump is literal NULL (Option A has not populated entity_id yet), so this loads without issue; it would only matter once you start writing real UUIDs through this same INSERT path. Load it the same way, after step 1 and step 2 (kg.map_link has a foreign-key-shaped dependency on kg.nodes existing, though it is not FK-enforced in the DDL):

gunzip -c kg-handoff/map_link.sql \
  | grep '^INSERT INTO kg_' \
  | sed 's/^INSERT INTO kg_/INSERT INTO kg./' \
  | psql "$DATABASE_URL"

(map_link.sql is not gzipped; drop the gunzip -c and just grep/sed the file directly, or pipe it through cat for symmetry with the command above.)

What Option B needs that is not in postgres_kg_schema.sql today (verified against the file; see design/system/SYSTEM_DESIGN.md section 4.1, "Two serving tables the loader must also project"):

• kg.node_map (the bridge table itself: node_id PK, entity_id uuid nullable, entity_type, name_en, name_zh, country_iso3, admin_unit_id, degree, resolution_status).
• kg.events (a real events table: research-kg represents events today as Event nodes plus HAS_EVENT / HAS_PARTICIPANT / OCCURRED_IN edges, not a dedicated table; kg.py's /kg/events handler expects kg.events(event_id, event_type, event_date, participant_entity_ids uuid[], source_doc_id, ...) directly).
• kg.claim_subjects(claim_id, node_id), exploding claims.subject_node_ids JSONB into rows, so "claims about X" is an index scan instead of a JSONB containment scan.

None of these three exist in postgres_kg_schema.sql as shipped. A loader targeting Option B (or the node_map hybrid) has to add them; they are required output of the load, not something the DDL already provides.

Real counts in the current dump (verified by counting INSERT statements directly): 44,781 nodes, 118,205 edges, 22,341 claims, 5,622 documents, 45,430 node_mentions, 2,081 aliases, 16,916 place_mentions, 8,441 map_link rows.

C. The map<->KG link (kg.map_link)

This is the table that answers "the user clicked this point on a /viz map, what does the knowledge graph know about it."

Schema (postgres_kg_schema.sql:95-103):

CREATE TABLE kg.map_link (
    source_dataset TEXT NOT NULL,
    source_row_id  TEXT NOT NULL,
    node_id        TEXT NOT NULL,
    entity_id      UUID,             -- populated only under Option B; NULL under Option A
    is_primary     BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (source_dataset, source_row_id, node_id)
);
CREATE INDEX ix_map_link_node ON kg.map_link(node_id);

One row per (map point, KG node it touches). A single map point can have several rows: an investment deal maps to every co-investor Organization node it involves (verified in the data, e.g. investment:4624 links to both org:mcc and org:jiangxi-copper, both is_primary = true).

How it is built (mgc/mgc_kg/build_map_link.py, run via python -m mgc_kg.build_map_link): a pure backfill from kg_edges, no new extraction. The rule:

SELECT DISTINCT source_dataset, source_row_id,
       CASE WHEN source_dataset='viz:conflict-events' THEN dst_id ELSE src_id END AS node_id
FROM kg_edges WHERE source_dataset IS NOT NULL

The primary node for a map point is the structural edge's src_id (the Project / Person / Organization / Country node) for every dataset except viz:conflict-events, where it is dst_id (the Impact node, since the conflict-events structural edge points at the impact, not from it).

The hard requirement: source_row_id must be the /viz API's own row id for that family. Every family currently in map_link uses these id schemes (verified against real rows in map_link.sql):

Gotcha: a map layer only resolves if the row id it hands useKgResolve matches one of these exactly. Layers served by the separate Platform API under a different id namespace will not resolve until repointed. Concretely, the energy map used to carry a cgp_id like BY.001 from the China's Global Power database, which does not match map_link; it has since been repointed at /viz/energy-projects, whose plant:<hash> ids do. deriveMapPoint (section E) needs the /viz-family id, not a platform API's native id, or /kg/resolve returns [] even though the entity exists in the graph.

Country selection resolves differently, via the KG's own country:<ISO3> node id (verified: bri:AFG -> country:AFG), not through map_link at all. The country: prefix matters: /kg/entity/country:AFG (Option A / local reference) is a real node; a bare AFG is not.

The resolver query (what /kg/resolve runs under the hood):

SELECT node_id, entity_id, is_primary
  FROM kg.map_link
 WHERE source_dataset = $1 AND source_row_id = $2

(The real handler in mgc-api/app/routes/kg.py:kg_resolve also LEFT JOINs kg.nodes for label/node_type so the response carries a display name; see section D.)

D. The /kg API contract

Source: mgc-api/app/routes/kg.py + mgc-api/app/schemas/kg.py (the production route file, Option-B-shaped) and kg-demo/local_kg_api.py (a complete working Option-A reference implementation of the identical contract over SQLite; run it with uvicorn local_kg_api:app --reload --port 18010).

Every relation/claim/event row carries a provenance block (Provenance mixin in app/schemas/kg.py:29-41): source_doc_id, url, title, published_date, evidence_text (verbatim source snippet), char_start, char_end. This is what CitationBlock in RelationshipsPanel.tsx renders as "From the source."

/kg/ask is not yet in mgc-api/app/routes/kg.py. It exists only in kg-demo/local_kg_api.py (local_kg_api.py:1737) as the reference implementation, and the frontend already has a hook built against its exact response shape (mgc-frontend/src/hooks/kg/useKgAsk.ts, wired into the energy map's "Ask MGC" overlay). Porting it to mgc-api is outstanding work, not a documentation gap; design/system/SYSTEM_DESIGN.md section 4.3 sketches the production version (trigram + tsvector/ts_rank_cd RRF-fused, intent keywords in Python, same never-empty fallback chain).

The relevance filter (hub-noise suppression) exists only in the reference implementation, not in mgc-api/app/routes/kg.py yet. Verified: kg.py has no HUB_NODE_TYPES, CONTEXT_EDGE_TYPES, or include_context anywhere. In kg-demo/local_kg_api.py (local_kg_api.py:47-53):

HUB_NODE_TYPES = {"Country", "Sector", "Framework", "Theme", "Tag"}
CONTEXT_EDGE_TYPES = {"LOCATED_IN", "IN_SECTOR", "PART_OF", "PARTY_TO"}

Nearly every node links to a hub node type (a plant's country, a project's sector), so those edges carry little information and just duplicate what the map popup already shows. By default, fetch_relations drops a CONTEXT_EDGE_TYPES edge if either endpoint is a HUB_NODE_TYPES node (local_kg_api.py:300-317). Checking both endpoints, not just the "neighbor," matters: it means a country's own incoming X LOCATED_IN Pakistan edges are filtered when you are looking at Pakistan itself, not only a project's outgoing edge to its country. Pass include_context=true to see them. Informative relation types (INVESTS_IN, STATE_VISIT, HAS_IMPACT, ...) are always kept, even when they touch a hub node, since the filter only ever drops CONTEXT_EDGE_TYPES. If you deploy kg.py as-is today, /kg/entity/{id} and /kg/entity/{id}/relations will return this hub noise unfiltered; porting HUB_NODE_TYPES/CONTEXT_EDGE_TYPES/ include_context from local_kg_api.py is necessary to match the UX the frontend was designed against.

Option A vs Option B id convention: every entity_id/path param above is a UUID in mgc-api/app/routes/kg.py as written. Under Option A, those same params are plain text node_id strings (org:mcc, country:AFG, claim:<hash>), as implemented in local_kg_api.py. Mixing the two, e.g. calling the real kg.py with a text node id where it expects a UUID, fails FastAPI's path validation with a 422 before it ever reaches SQL. This is the same gap SYSTEM_DESIGN.md section 7 calls out as risk F15 ("map passes ISO3 where UUID is required").

E. Frontend wiring

The pattern already built on branch feat/kg-relationships-panel:

• The same-origin API proxy, mgc-frontend/app/api/mgc/[...path]/route.ts, allowlists kg/ in ALLOWED_ENDPOINT_PREFIXES (route.ts:21), so any /kg/* path reaches mgc-api through /api/mgc/kg/*. (The proxy also has a demo-only MGC_KG_API_BASE_URL escape hatch that redirects kg/* to a local service instead, route.ts:79-90; leave that env var unset in production, it must not ship set.)
• deriveMapPoint(feature, family) in mgc-frontend/src/components/maps/shared/kgMapPoint.ts turns a clicked map feature into {dataset: 'viz:<family>', rowId}. Pass family explicitly whenever the caller already knows the layer (recommended): the fallback prefix-inference (FAMILY_BY_ID_PREFIX) only covers port, plant, asset, investment, bri-contract, state-visit. It does not cover diplomatic-visits, bri-membership, or conflict-events, whose row ids do not follow a <prefix>:<n> scheme deriveMapPoint can infer (see the table in section C); those three families must always pass family explicitly.
• useKgResolve({dataset, rowId}) (mgc-frontend/src/hooks/kg/useKgResolve.ts) calls GET /api/mgc/kg/resolve?dataset=&row_id=&primary_only=. Its resolvedId convenience field is entity_id ?? node_id (useKgResolve.ts:51), which works unmodified under both Option A and Option B, since the field simply falls back to whichever id came back populated.
• RelationshipsPanel (mgc-frontend/src/components/maps/shared/RelationshipsPanel.tsx) takes either:
• mapPoint={{dataset, rowId}} for per-point layers: resolves via useKgResolve first, then feeds the resolved id into useKgEntity/useKgRelations/useKgClaims/useKgEvents. See the energy map (mgc-frontend/app/maps/energy/page.tsx), where clicking a power-plant point calls deriveMapPoint({id: project.id}, 'energy-projects') (page.tsx:132) and opens the panel with that mapPoint.
• entityId={...} directly for country choropleths, bypassing useKgResolve. The investment map (mgc-frontend/app/maps/investment/page.tsx:154-155) builds this as `country:${store.selectedCountryIso3}`, matching the KG's own country:<ISO3> node id scheme (section C).

Flag: the energy map's own country-panel call (mgc-frontend/app/maps/energy/page.tsx:550) passes entityId={store.selectedCountryIso3}, the bare ISO3, not prefixed with country:. That is inconsistent with the investment map's pattern and with the KG's node id scheme documented in section C. Against the Option A reference backend (local_kg_api.py), a bare AFG is not a valid node_id (the real node is country:AFG), so this call as written would return an empty neighborhood. Worth fixing to `country:${store.selectedCountryIso3}` to match the investment page before relying on the energy map's relationships panel for country selections. - Renders four tabs (Network, Relations, Claims, Events) plus a CitationBlock (RelationshipsPanel.tsx:63-128) for the provenance block on every row.

The remaining wiring task per map: attach each layer's click handler to build either a {dataset, row_id} map point (per-point layers, follow the energy map's handleProjectClick pattern) or a country:<iso3> entity id (choropleth layers, follow the investment map's pattern, and fix the energy map's country-panel call to match) and pass it into RelationshipsPanel.

F. Keeping it fresh

Locally, the pipeline does a full reset-and-rebuild every run (mgc_kg/cli.py calls store.reset() by default), not an incremental append. This is intentional and cheap: a separate LLM checkpoint (data/checkpoint.sqlite, survives the reset) replays every already-extracted document's relations, claims, and events at zero cost, so only genuinely new or previously-failed documents cost an LLM call. Do not append pipeline output into an existing running store; plain INSERTs with no natural unique key on kg_edges will duplicate rows on every re-run (this is also risk F3 in SYSTEM_DESIGN.md section 7, see section G below).

It runs via mgc/scripts/kg_daily.sh, a thin wrapper around python -m mgc_kg.cli run, scheduled two ways locally: a launchd plist (mgc/deploy/launchd/com.mgc.kg-daily.plist, installed at ~/Library/LaunchAgents/com.mgc.kg-daily.plist) and an interim tmux scheduler (mgc/scripts/kg_tmux_scheduler.sh); the script takes a mkdir based lock so the two don't race the same SQLite file if both fire at once. Budget is a cumulative, all-time cap, not a per-day cap: BUDGET_USD (mgc/mgc_kg/config.py:120, default $3.00, overridden to $30.00 in kg_daily.sh) is persisted in the checkpoint DB's budget table and reloaded on every run; once total spend across all runs reaches the cap, the pipeline stops paying for new LLM calls (it still finishes the graph from whatever is already extracted) and stays capped on every subsequent run until you raise it.

kg_daily.sh also regenerates kg_map_link after every successful rebuild (the reset wipes it) and refreshes a served snapshot copy:

python -m mgc_kg.build_map_link

Run this after any manual rebuild too, or map clicks stop resolving.

In production, this should not run as a local script. SYSTEM_DESIGN.md section 2 specifies a dedicated mgc-knowledge-graph.service (oneshot) + .timer unit (OnCalendar=*-*-* 05:00:00 UTC) on the same droplet as the rest of mgc-hub's enrichment modules, standalone rather than chained off the 02:00 scrape cascade (memory isolation: the KG loads GLiNER-multi + BGE-M3 + mDeBERTa-NLI, roughly 5 GB, which must not stack on the existing NER/embedding peak). The daily pass becomes a true incremental left-anti-join over new/changed documents (section 2.2), gated by an acceptance check before promotion (provenance completeness, NLI-canary pass, mint-rate ceiling, FK/tier violations, anomaly bounds) that blocks the materialized-view refresh on failure, so a bad run never goes live and yesterday's graph keeps serving (section 2.5). See SYSTEM_DESIGN.md sections 2 and 3 for the full incremental design (work queue SQL, per-doc vs. global-count edge cadence, weekly full reconciliation).

G. Production checklist and risks

Ordered steps to go live:

1. Load the schema and dump (section B).
2. Decide Option A vs Option B (section A). If Option A, port the /kg/* SQL to text node_ids (or deploy local_kg_api.py's logic directly).
3. Deploy the /kg endpoints (mgc-api/app/routes/kg.py, branch feat/kg-endpoints per kg-handoff/README.md; tracked as PR #43).
4. Ship the frontend wiring (feat/kg-relationships-panel; tracked as PR #175).
5. Wire the remaining per-map click handlers (section E).
6. Deploy.

The 5 pre-ship risks (from design/system/SYSTEM_DESIGN.md section 7, its "P0 / must-fix-before-build" list):

1. kg.edges has no natural unique key. Only edge_id BIGSERIAL PRIMARY KEY exists today; every insert is a plain INSERT, masked by the pipeline's full reset. Without a real unique key (e.g. (edge_type, src_id, dst_id, source_doc_id, char_start, char_end) for span edges), any move to upsert-based incremental loading accumulates duplicate edges forever instead of updating in place.
2. The LLM checkpoint's cache key has no text hash. The key is sha256(kind | doc_id | model | prompt_version) (mgc/mgc_kg/checkpoint.py:63); it does not include a hash of the document text itself. A re-scraped or corrected document gets requeued under the same doc_id but hits a cache hit on the old extraction, so the corrected source text ships with stale machine-read facts. Add a text_hash component to the cache key.
3. Settle Option A vs Option B before the API points at real tables. mgc-api's /kg routes already assume Option B's uuid-keyed entity_registry joins while the data as shipped is Option A's text ids; deployed together unreconciled, /kg/* returns nothing or 422s.
4. Per-day budget must not reuse the cumulative counter. As shipped, BUDGET_USD is lifetime-cumulative (section F). A naive "$1/day" policy built on the same counter either disables the LLM immediately (since the seeded historical spend already exceeds a $1 cap) or, if force-reset daily, throws away the resume-safety the cumulative design provides. Add a genuinely per-run spend counter separate from the lifetime guardrail.
5. Write documents last, in the same transaction as the document's edges. A crash between creating the Document node and writing its edges leaves a permanently half-extracted document that a "already processed" work-queue check will skip forever. Commit the document marker last within the doc's own edge transaction (or additionally gate the work queue on "checkpoint has no done row for this doc").

See design/system/SYSTEM_DESIGN.md section 7 for the full risk list (10 more items) and section 8 for security/DR gaps (notably: /kg/* has no auth or rate limiting today, and the LLM checkpoint is not in the weekly pg_dump backup).