Metadata-Version: 2.5
Name: prismschema
Version: 0.1.0
Summary: Open-source DDL parser and PrismSQL schema contract generator
Project-URL: Homepage, https://www.insightits.com/products/prismsql.html
Project-URL: Documentation, https://www.insightits.com/docs/prismsql-api.md
Project-URL: Repository, https://github.com/insightitsGit/PrismSchema
Author-email: Amin Parva <insightits.info@gmail.com>, Insight IT Solutions LLC <info@insightits.com>
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: contract,database,ddl,prismsql,schema,sql
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Code Generators
Requires-Python: >=3.10
Requires-Dist: click>=8.1
Requires-Dist: pyyaml>=6.0
Provides-Extra: dev
Requires-Dist: build>=1.2; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: twine>=5.0; extra == 'dev'
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == 'mysql'
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3.1; extra == 'postgres'
Description-Content-Type: text/markdown

# PrismSchema

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

**Author:** [Amin Parva](mailto:insightits.info@gmail.com)

[![PyPI](https://img.shields.io/badge/PyPI-prismschema-blue?logo=pypi&logoColor=white)](https://pypi.org/project/prismschema/)
[![License](https://img.shields.io/badge/License-Apache%202.0-green.svg)](LICENSE)
[![Python](https://img.shields.io/badge/Python-3.10%2B-blue?logo=python&logoColor=white)](https://www.python.org/)
[![Build](https://img.shields.io/github/actions/workflow/status/insightitsGit/PrismSchema/ci.yml?branch=main&label=CI)](https://github.com/insightitsGit/PrismSchema/actions/workflows/ci.yml)
[![PyPI version](https://img.shields.io/pypi/v/prismschema.svg)](https://pypi.org/project/prismschema/)
[![PrismSQL](https://img.shields.io/badge/Companion-PrismSQL-2f9e7a)](https://www.insightits.com/products/prismsql.html)
[![Discussions](https://img.shields.io/badge/GitHub-Discussions-181717?logo=github)](https://github.com/insightitsGit/PrismSchema/discussions)
[![GitHub](https://img.shields.io/badge/GitHub-insightitsGit%2FPrismSchema-181717?logo=github)](https://github.com/insightitsGit/PrismSchema)

> 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](https://www.insightits.com/products/prismsql.html)** · **[Schema contract spec](https://www.insightits.com/docs/prismsql-api.md)** · **[Hosted dashboard](https://www.insightits.com/dashboard.html#prismsql)**

---

**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](https://www.insightits.com/products/prismsql.html) — 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](https://www.insightits.com/dashboard.html#prismsql), 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`](prismschema/validator.py) and the [PrismSQL API spec](https://www.insightits.com/docs/prismsql-api.md).

```
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

```bash
# 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

```bash
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

```bash
# 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

```bash
prismschema validate --contract ./contract.json
```

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

### Example 4 — Python API

```python
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

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

Fixtures under [`tests/fixtures/`](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](mailto:insightits.info@gmail.com)
- Product: [PrismSQL](https://www.insightits.com/products/prismsql.html)
- GitHub Discussions: [insightitsGit/PrismSchema](https://github.com/insightitsGit/PrismSchema/discussions)

---

## License

Apache License 2.0 — see [`LICENSE`](LICENSE).

---

## Citation

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

```bibtex
@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](mailto:insightits.info@gmail.com))
- Company: [https://www.insightits.com](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
