PrismSchema

Open-source DDL parser & live database introspection engine that turns raw SQL schemas into production-ready PrismSQL contracts.

Author: Amin Parva

Point PrismSchema at a DDL dump, .sql migration file, or live read-only database connection — get back a validated PrismSQL schema contract (JSON/YAML) with inferred join paths, column types, tenant isolation hints, and soft-delete detection. Zero hosted API dependency.

PrismSQL product · Schema contract spec · Hosted dashboard

---

Keywords: DDL to JSON schema contract, PostgreSQL schema introspection CLI, MySQL foreign key join path inference, PrismSQL schema generator, text-to-SQL schema binding, database schema contract validator, natural language to SQL schema, PrismSchema, SQL DDL parser Python

What PrismSchema Does (30-Second Summary)

Input	Output
PostgreSQL / MySQL / SQLite DDL file	Validated PrismSQL contract (.json / .yaml)
Live read-only DB connection	Same contract, introspected from information_schema
Existing contract file	prismschema validate checklist (required + recommended fields)

PrismSchema is the top-of-funnel, open-source companion to PrismSQL — Insight IT Solutions' schema-bound natural language → SQL compiler. It removes the manual work of hand-authoring schema contracts before you can compile NL prompts safely.

---

The Core Problem (Why PrismSchema?)

Teams adopting schema-bound text-to-SQL hit the same wall:

1. Hand-written schema contracts don't scale — Every new table, FK, or tenant column means editing JSON by hand. Drift between the live database and the contract becomes a security and correctness risk.
2. Raw DDL isn't PrismSQL-ready — Warehouse types, join cardinality, isolation modes, and soft-delete semantics must be normalized before a compiler can enforce them.
3. Validation is an afterthought — Missing via_join path IDs, wrong column types, or oversized contracts fail late in production instead of at extract time.

PrismSchema automates the bridge:

Constraint	PrismSchema answer
Input flexibility	DDL files or live introspection (PostgreSQL, MySQL, SQLite)
Join topology	Infers join_paths from FOREIGN KEY / REFERENCES with N:1, 1:N, 1:1 cardinality
Type safety	Maps warehouse types → PrismSQL types (INTEGER, NUMERIC, TIMESTAMPTZ, …)
Multi-tenant RAG / SaaS	Suggests jwt_email isolation when columns like tenant_email exist
Soft deletes	Detects deleted_at, is_deleted, and similar patterns
Fail fast	Built-in validator enforces the full PrismSQL contract spec locally

Generate locally. Upload to hosted PrismSQL when you're ready for enterprise JWT isolation and NL → SQL compilation.

---

High-Value Use Cases

1. Bootstrap Schema-Bound Text-to-SQL

Keywords: text to SQL schema contract, NL to SQL schema binding, RAG SQL schema guardrails

Export your app schema once with prismschema extract, upload the contract to the PrismSQL dashboard, and compile natural language questions against only declared tables, columns, and join paths — no hallucinated tables.

2. CI/CD Schema Contract Drift Detection

Keywords: database schema CI, DDL contract validation, schema drift detection pipeline

Run extraction in CI on every migration PR. Diff the generated contract against the checked-in golden file. Fail the build when join paths, column allow-lists, or isolation modes change unexpectedly.

3. Multi-Tenant SaaS & Healthcare / Finance Isolation

Keywords: JWT email row isolation, multi-tenant SQL schema, HIPAA text-to-SQL guardrails

PrismSchema inspects column names and FK graphs to propose jwt_email tenant isolation or via_join child-table scoping — the same modes PrismSQL enforces at compile time.

4. Legacy Database Onboarding

Keywords: legacy database introspection, PostgreSQL information_schema export, MySQL schema to JSON

Connect read-only to a brownfield PostgreSQL or MySQL instance. PrismSchema walks live metadata and emits a contract you can review, trim, and version — without writing DDL parsers by hand.

---

PrismSQL Contract Shape (What Gets Generated)

Ground truth: prismschema/validator.py and the PrismSQL API spec.

PrismSQL Schema Contract
┌──────────────────────────────────────────────────────────────────────────┐
│ schema_id, dialect, timezone, description                              │
│ policy (limits, rate limits, wall-clock budget)                          │
├──────────────────────────────────────────────────────────────────────────┤
│ tables["schema.table"]                                                   │
│   ├─ allow_columns[]          ← queryable column allow-list              │
│   ├─ column_types{}           ← PrismSQL-normalized types                │
│   ├─ isolation{}              ← jwt_email | via_join | none            │
│   ├─ soft_delete{}            ← optional deleted_at / is_deleted rules   │
│   └─ indexes[]                ← declared indexes for planner hints       │
├──────────────────────────────────────────────────────────────────────────┤
│ join_paths[]                                                             │
│   ├─ path_id, left/right endpoints (schema.table.column)                 │
│   ├─ cardinality (N:1, 1:N, 1:1)                                       │
│   └─ join type (INNER / LEFT)                                            │
└──────────────────────────────────────────────────────────────────────────┘

Field	Source	Role
allow_columns	DDL columns or introspection	Compiler allow-list — queries cannot reference undeclared columns
column_types	Inferred from warehouse types	Type checking & literal coercion in PrismSQL
join_paths	FK / REFERENCES constraints	Safe JOIN graph — no invented relationships
isolation.mode	Tenant columns + FK topology	Row-level scoping (jwt_email, via_join)
soft_delete	Pattern match on column names	Automatic deleted_at IS NULL style filters
policy	Sensible defaults	Rate limits and row caps for hosted compilation

---

Quickstart & Code Examples

Installation

# From PyPI
pip install prismschema

# Optional live introspection drivers
pip install "prismschema[postgres]"   # PostgreSQL (psycopg)
pip install "prismschema[mysql]"      # MySQL (PyMySQL)

# From git (latest main)
git clone https://github.com/insightitsGit/PrismSchema.git
cd PrismSchema
python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -U pip
pip install -e ".[dev]"

prismschema --version
pytest -q

Core deps: click, PyYAML. Optional extras: postgres, mysql, dev.

Example 1 — Extract from a DDL File

prismschema extract \
  --dialect postgresql \
  --input ./schema.sql \
  --output ./contract.json \
  --schema-id my_app_v1

Sample output summary:

Tables processed: 12
Join paths found: 8
  - public.orders (6 columns)
  - public.order_items (4 columns)
  -> orders_to_items: public.order_items.order_id -> public.orders.id (N:1)

Example 2 — Live Read-Only Introspection

# SQLite (file path)
prismschema extract \
  --dialect sqlite \
  --connection ./app.db \
  --output ./contract.json

# PostgreSQL (DSN)
prismschema extract \
  --dialect postgresql \
  --connection "postgresql://readonly:pass@localhost:5432/mydb" \
  --output ./contract.yaml \
  --format yaml

Example 3 — Validate Before Upload

prismschema validate --contract ./contract.json

Prints required vs recommended field checklist — exit code 1 if required fields are missing.

Example 4 — Python API

from prismschema.parser.ddl_parser import parse_ddl_file
from prismschema.parser.introspection import introspect_database
from prismschema.builder import build_contract
from prismschema.validator import validate_contract

# From DDL
catalog = parse_ddl_file("schema.sql", dialect="postgresql")
contract = build_contract(catalog, schema_id="my_app_v1")

# From live DB
catalog = introspect_database("postgresql", "postgresql://readonly@localhost/mydb")
contract = build_contract(catalog, schema_id="my_app_v1", timezone="America/New_York")

validate_contract(contract)  # raises ValidationError if invalid

---

CLI Reference

Command	Description
prismschema extract	Parse DDL or introspect a live DB → PrismSQL JSON/YAML
prismschema validate	Validate a contract; prints required/recommended checklist

extract options

Flag	Description
--dialect	Required. postgresql, mysql, or sqlite
--input	Path to a .sql / DDL file
--connection	Live read-only connection (SQLite path, PostgreSQL DSN, MySQL DSN)
--output	Output path (.json or .yaml)
--schema-id	Contract ID (default: derived from input filename)
--timezone	IANA timezone (default: UTC)
--format	json or yaml

---

What PrismSchema Infers Automatically

Feature	Behavior
Column types	Maps warehouse types → PrismSQL types (INTEGER, NUMERIC, TIMESTAMPTZ, …)
Join paths	From FOREIGN KEY and REFERENCES constraints
Cardinality	N:1 for typical child→parent FKs; 1:1 when FK columns are unique
Soft delete	Detects deleted_at, is_deleted, and similar columns
Tenant isolation	Suggests jwt_email when columns like tenant_email exist
Child tables	Sets via_join isolation when a table is on the left of a join path
Primary keys	Inferred from DDL constraints or introspection metadata
Indexes	Captured from CREATE INDEX statements or live catalog

---

Supported Databases

Dialect	DDL file parsing	Live introspection	Extra install
PostgreSQL	✅	✅	pip install "prismschema[postgres]"
MySQL	✅	✅	pip install "prismschema[mysql]"
SQLite	✅	✅	built-in

---

Workflow: PrismSchema → PrismSQL

DDL / live DB ──► prismschema extract ──► contract.json
                                              │
                                              ▼
                                    prismschema validate
                                              │
                                              ▼
                         Upload to PrismSQL dashboard (hosted NL → SQL)
                                              │
                                              ▼
                         Compile prompts with JWT isolation & join enforcement

Hosted PrismSQL dashboard: https://www.insightits.com/dashboard.html#prismsql

PrismSchema runs entirely locally. Your DDL, connection strings, and generated contracts never leave your machine unless you choose to upload.

---

Development

git clone https://github.com/insightitsGit/PrismSchema.git
cd PrismSchema
pip install -e ".[dev]"
pytest -q
python -m build

Fixtures under tests/fixtures/ cover PostgreSQL clinic billing, MySQL orders, and edge-case DDL.

---

Deploying Schema-Bound NL → SQL at Scale?

Building a regulated analytics copilot, a multi-tenant SaaS reporting layer, or a private text-to-SQL stack with row-level isolation?

Insight ITS works with enterprise teams on:

• Custom PrismSQL schema contracts for complex ontologies and audit corpora
• Private compliance connectors (JWT isolation, soft-delete enforcement, HITL review)
• Managed PrismSQL deployments with versioned schema contracts

Talk to us

• Email: insightits.info@gmail.com
• Product: PrismSQL
• GitHub Discussions: insightitsGit/PrismSchema

---

License

Apache License 2.0 — see LICENSE.

---

Citation

If PrismSchema helps your schema-bound text-to-SQL or RAG pipeline:

@software{prismschema2026,
  title  = {PrismSchema: DDL Parser and PrismSQL Schema Contract Generator},
  author = {Amin Parva},
  year   = {2026},
  url    = {https://github.com/insightitsGit/PrismSchema}
}

---

PrismSchema — from raw DDL to validated schema contracts in one command.

---

Links

• Author: Amin Parva (insightits.info@gmail.com)
• Company: https://www.insightits.com
• GitHub: https://github.com/insightitsGit/PrismSchema
• PyPI: https://pypi.org/project/prismschema/
• Companion product: https://www.insightits.com/products/prismsql.html
• Schema spec: https://www.insightits.com/docs/prismsql-api.md