DuckDB SQL Plugin

Plugin ID: com.sureclinical.sql-duckdb Type: translator · Format key: sql-duckdb · Version: 1.1.0 Source: sqlDuckDbPlugin.ts · sqlDdlUtils.ts

The DuckDB SQL Plugin is a built-in Schema Builder translator that converts Mapper View rows into CREATE TABLE DDL for DuckDB. It mirrors the PostgreSQL plugin's grouping and FK inference algorithm but uses DuckDB-native type names, making it the preferred output format for the SureCentric analytical lakehouse.

---

Usage

1. Open a JSON-LD schema in Schema Builder
2. Open the Mapper View (map icon in the toolbar)
3. Review and configure each row's mapTo (target table/column name) and targetType
4. Set rows with targetType = TABLE (or nodeType = owl:Class) as table headers
5. In the Target Format dropdown, select SQL DDL (DuckDB)
6. Click Export → save as schema.sql

Load into DuckDB:

duckdb /data/clinical.duckdb < schema.sql

---

DDL Generation Algorithm

The DuckDB plugin uses the identical two-phase algorithm as the PostgreSQL plugin:

Phase 1 — Grouping

• Rows with targetType === 'TABLE' or nodeType === 'owl:Class' start a new table
• Subsequent non-TABLE rows become columns of that table
• Every table gets an iri VARCHAR NOT NULL PRIMARY KEY column from @id

Phase 2 — FK Inference

• Columns typed URI, IRI, or owl:ObjectProperty are checked against known table names
• Trailing suffixes (_iri, _id, _ref, _fk) are stripped
• Matching table names emit REFERENCES <table>(iri) automatically

---

DuckDB Type Mapping

JSON-LD / Mapper Type	DuckDB Type
STRING	VARCHAR
INTEGER	INTEGER
FLOAT / NUMBER	DOUBLE
BOOLEAN	BOOLEAN
DATETIME / TIMESTAMP	TIMESTAMP
DATE	DATE
UUID	VARCHAR(36)
JSON / OBJECT	JSON
URI / IRI	VARCHAR (upgraded to FK REFERENCES if inferred)
TABLE	(table boundary marker — not a column)

---

Example Output

-- Auto-generated by Schema Builder · com.sureclinical.sql-duckdb v1.1.0
-- Source: eclinical-ehr-subset.jsonld

CREATE TABLE IF NOT EXISTS clinical_site (
  iri VARCHAR NOT NULL PRIMARY KEY,
  site_name VARCHAR,
  country VARCHAR,
  site_code VARCHAR
);

CREATE TABLE IF NOT EXISTS patient (
  iri VARCHAR NOT NULL PRIMARY KEY,
  patient_id VARCHAR(36),
  date_of_birth DATE,
  sex VARCHAR,
  clinical_site_iri VARCHAR REFERENCES clinical_site(iri)
);

CREATE TABLE IF NOT EXISTS visit (
  iri VARCHAR NOT NULL PRIMARY KEY,
  visit_date TIMESTAMP,
  visit_type VARCHAR,
  patient_iri VARCHAR REFERENCES patient(iri)
);

---

Differences from PostgreSQL Plugin

Behaviour	PostgreSQL	DuckDB
String type	TEXT	VARCHAR
Float type	DOUBLE PRECISION	DOUBLE
Timestamp type	TIMESTAMP WITH TIME ZONE	TIMESTAMP
UUID type	UUID	VARCHAR(36)
JSON type	JSONB	JSON
PK column	iri TEXT NOT NULL PRIMARY KEY	iri VARCHAR NOT NULL PRIMARY KEY
FK inference	identical	identical

---

Role in the CARD Pipeline

The DuckDB plugin is the primary DDL output for the SureCentric analytical lakehouse. The typical CARD pipeline sequence:

1. Mapper View → Export (DuckDB DDL) → schema.sql
2. Mapper View → Export (Seed Dataset SQL) → inserts.sql
3. duckdb /data/clinical.duckdb < schema.sql
4. duckdb /data/clinical.duckdb < inserts.sql
5. CARD /provision → registers tables as Superset datasets

See the CARD System and Seed Dataset Generator pages for the full end-to-end workflow.

---

Shared Utilities

Both SQL plugins share sqlDdlUtils.ts:

Utility	Purpose
sanitizeName(raw)	Extracts URI fragment or last path segment → snake_case SQL identifier
buildTableNameSet(tables)	Builds Set<string> of all known table names for FK inference
inferForeignKey(col, tableNames)	Strips suffixes and checks against known tables → returns ref table or null
ddlHeader(dialect, sourceUri)	Emits the -- Auto-generated ... comment block