PostgreSQL SQL Plugin

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

The PostgreSQL SQL Plugin is a built-in Schema Builder translator that converts Mapper View rows into production-ready CREATE TABLE DDL for PostgreSQL. It is the primary DDL output plugin for deploying schemas to the SureCentric analytics warehouse.

---

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 (PostgreSQL)
6. Click Export → save as schema.sql

---

DDL Generation Algorithm

The plugin uses a two-phase algorithm:

Phase 1 — Grouping

Mapper View rows are scanned in order:

• A row with targetType === 'TABLE' or nodeType === 'owl:Class' starts a new table
• All subsequent non-TABLE rows become columns of that table
• If no TABLE rows exist, a single anonymous table is emitted

Every generated table automatically receives an iri TEXT NOT NULL PRIMARY KEY column derived from the JSON-LD @id field.

Phase 2 — FK Inference

After grouping, each column's type is inspected:

• Columns with targetType === 'URI', 'IRI', or nodeType === 'owl:ObjectProperty' are candidates for foreign key resolution
• Trailing suffixes (_iri, _id, _ref, _fk) are stripped from the column name
• If the result matches a known table name, a REFERENCES <table>(iri) constraint is emitted automatically

---

PostgreSQL Type Mapping

JSON-LD / Mapper Type	PostgreSQL Type
STRING	TEXT
INTEGER	INTEGER
FLOAT / NUMBER	DOUBLE PRECISION
BOOLEAN	BOOLEAN
DATETIME / TIMESTAMP	TIMESTAMP WITH TIME ZONE
DATE	DATE
UUID	UUID
JSON / OBJECT	JSONB
URI / IRI	TEXT (upgraded to FK REFERENCES if inferred)
TABLE	(table boundary marker — not a column)

---

Example Output

Given an eClinical mapping with ClinicalSite, Patient, and Visit nodes:

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

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

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

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

---

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

---

Integration with CARD Pipeline

The PostgreSQL plugin is the primary DDL source for the CARD System PostgreSQL target. The CARD /provision endpoint:

1. Runs the generated schema.sql against the configured PostgreSQL instance
2. Registers each created table as a Superset dataset
3. Optionally loads seed data from the Seed Dataset Generator