Metadata-Version: 2.4
Name: meridian-schema-forge
Version: 0.1.3
Summary: Build a processing pipeline from any schema, in any format: normalize a schema (XML/XSD, JSON/JSON-Schema, YAML, SQL DDL, delimited), classify field roles, then profile (exact tiktoken counts) or de-identify — from files or SQL.
Author: Meridian Intelligence
License: Proprietary — Meridian Intelligence, engagement-scoped (see LICENSE)
Project-URL: Documentation, https://github.com/Meridian-Int/meridian-schema-forge#readme
Keywords: schema,de-identification,pseudonymization,profiling,tiktoken,tokens,data-pipeline,xml,json-schema,sql
Classifier: Programming Language :: Python :: 3
Classifier: License :: Other/Proprietary License
Classifier: Operating System :: OS Independent
Classifier: Intended Audience :: Developers
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
License-File: LICENSE.pdf
Requires-Dist: PyYAML>=5.4
Requires-Dist: jsonschema>=4.0
Requires-Dist: tiktoken>=0.7
Requires-Dist: SQLAlchemy>=1.4
Provides-Extra: sql
Requires-Dist: pyodbc>=4.0; extra == "sql"
Provides-Extra: stream
Requires-Dist: ijson>=3.0; extra == "stream"
Provides-Extra: pandas
Requires-Dist: pandas>=1.3; extra == "pandas"
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Dynamic: license-file

# schemaforge

**schemaforge makes a safe, shareable copy of your data with the identifying details taken out.** Names, emails, phone numbers, IDs, birth dates and the like are swapped for realistic fakes, so the copy is still useful for testing, analysis, or sharing — but it no longer points back to real people. Turning real data into a safe copy like this is called **de-identifying** it, and it is schemaforge's main job. (It can also **profile** data — measure its size and shape — but start with de-identification.)

To do that, schemaforge needs two things from you: your **data**, and a **schema**. If those words are new, the next section explains them plainly, in order. This manual is meant to be read top to bottom with nobody to ask; every command can be copied and pasted as-is.

---

## The two things schemaforge needs

### 1. Your data

The actual records — the rows themselves. They can be in a file (CSV, JSON, XML, YAML) or in a live database. For example, a `customer` table full of rows looks like this:

```text
customer_id | email            | full_name | birth_date
1           | jane@example.com | Jane Doe  | 1990-05-01
2           | john@example.com | John Roe  | 1985-11-20
```

### 2. A schema

A **schema** is a short description of the *shape* of your data — **not** the rows, only their structure: which tables exist (schemaforge calls a table an *entity*), which columns each table has (it calls a column a *field*), what type each field is, and which fields are the *keys* that link tables together. The schema for the `customer` table above is just this:

```sql
CREATE TABLE customer (
  customer_id INTEGER PRIMARY KEY,   -- the key that identifies each row
  email       VARCHAR(255),          -- text, up to 255 characters
  full_name   VARCHAR(120),
  birth_date  DATE
);
```

That `CREATE TABLE …` block **is** a schema — a plain-text file you hand to schemaforge. From it, schemaforge learns there is a `customer` entity with four fields, that `customer_id` is its key, and that `birth_date` is a date. It uses that to decide what to do with each field: replace `email` and `full_name` (they identify a person), keep `customer_id` as a consistent fake key, generalize `birth_date` down to just the year.

### Where does the schema come from?

You are in one of three situations. Find yours:

| Your situation | What you do |
| --- | --- |
| **Your data is in a database** (Postgres, MySQL, SQL Server, Oracle, and others) | The database already stores its own schema. You export it to a file with one command. **[Appendix A](#appendix-a--generating-a-schema-from-your-database)** gives the exact command for your database. |
| **Someone handed you a schema file** (a `.sql`, `.json`, `.xml`, `.yaml`, or a `.csv` with a header row) | You already have what you need. Go to **Install**. |
| **You have only data and no schema** | You don't need one. schemaforge reads a sample of your data file and works the schema out for you automatically. |

**The short version:** most schemas come straight out of the database that holds the data, and Appendix A shows you exactly how to get yours. If you have no schema at all, schemaforge builds one from the data itself.

---

## Install

You need **Python 3.9 or newer**. Check what you have:

```bash
python3 --version
```

If that prints `Python 3.9` or higher, install schemaforge with one command:

```bash
python3 -m pip install meridian-schema-forge
```

That single line is everything you need to work with CSV, JSON, XML, YAML, and SQLite data. If your data lives in another kind of database (Postgres, MySQL, SQL Server, Oracle, and so on), you install that one database's driver as well — a single extra line, given for your exact database in **[Appendix A](#appendix-a--generating-a-schema-from-your-database)**. You do not need to install anything else up front.

Now check it worked:

```bash
schemaforge --version
```

You should see a version number. If instead you see `command not found`, jump to **[Troubleshooting](#troubleshooting)**.

> Working from a copy of the source code instead of installing from PyPI? Run `python3 -m pip install .` inside the project folder — that gives you the same `schemaforge` command plus the `examples/quickstart` files used in the next section.

---

## Your first run in 5 minutes

A source checkout ships a ready-made example at `examples/quickstart`: a SQL schema (`schema.sql`) and two CSV files (`data/customer.csv`, `data/order.csv`). Run the whole pipeline against it. Each command is explained below.

```bash
cd examples/quickstart
```

**1. Look at the schema and the roles schemaforge assigns.** `inspect-schema` parses the schema; `--classify` also resolves each field's role.

```bash
schemaforge inspect-schema --schema schema.sql --classify
```

```text
origin: sql-ddl   entities: 2   relationships: 1

customer  (pk: customer_id)
  customer_id              integer    PK NOT NULL             role=structural
  email                    string     NOT NULL                role=identifier
  full_name                string                             role=quasi_identifier
  birth_date               date                               role=quasi_identifier
  sex                      string                             role=categorical
  notes                    string                             role=free_text

order  (pk: order_id)
  order_id                 integer    PK NOT NULL             role=structural
  customer_id              integer    FK->customer.customer_id NOT NULL  role=structural
  order_date               date                               role=date
  amount                   decimal                            role=numeric
  status                   string                             role=categorical
  is_paid                  boolean                            role=boolean
```

**2. Build the plan.** This writes `plan.yaml` — the file you will review and re-run from.

```bash
schemaforge plan --schema schema.sql --source ./data --out plan.yaml
```

```text
wrote plan.yaml  (content hash 0c6116b367ea)
```

**3. Profile the data.** Exact token counts plus per-field metrics, written to `profile.json`.

```bash
schemaforge profile --plan plan.yaml --out-json profile.json
```

```text
records: 7   total tokens: 259   content: 63 (24.3%)
wrote profile.json
```

**4. Create a secret, then de-identify.** `init-secret` writes a private HMAC key (a keyed hash secret, `chmod 600`) used to make pseudonyms deterministic. `deidentify` then writes the cleaned data.

```bash
schemaforge init-secret --out secret.key
schemaforge deidentify --plan plan.yaml --out ./clean --secret-file secret.key
```

```text
wrote secret.key (chmod 600). Keep it private.
de-identified 7 records across 2 entities; mapping size 13
wrote ./clean   private key -> ./clean-PRIVATE
```

**5. (Optional) Validate the profile output against its bundled contract.**

```bash
schemaforge validate --json profile.json --kind profile
```

```text
valid.
```

### What you now have

```text
examples/quickstart/
  plan.yaml              # the reproducible plan — review this
  profile.json           # token counts + per-field metrics
  secret.key             # HMAC secret (chmod 600) — keep PRIVATE, never ship
  clean/
    customer.jsonl       # de-identified rows, one JSONL file per entity
    order.jsonl
  clean-PRIVATE/         # (chmod 700) re-identification map + audit — NEVER ship
    mapping.json
    audit.json
    README.txt
```

Peek at a de-identified row to see roles in action:

```json
{"customer_id": "33534645", "email": "user3baa4f655e@example.com", "full_name": "PERSON_F5F777", "birth_date": "1990", "sex": "F", "notes": "REDACTED called about order ID"}
```

Notice: the primary key became a stable surrogate, `email` (an identifier) became a shape-preserving surrogate, `full_name` (quasi-identifier) became a consistent synthetic token (`PERSON_…`), `birth_date` was generalized to the year, `sex` (categorical) passed through, and the free-text `notes` was scrubbed — the customer's own name became `REDACTED` and the embedded order number became `ID`. Every replacement is deterministic (the same input always maps to the same output), so the surrogate `customer_id` here matches the one in `order.jsonl` and the join still works.

That is the entire tool. The rest of this manual explains how to point it at *your* schema and data.

---

## The core idea: schema + data → plan → run

schemaforge always works in the same shape, no matter the domain:

1. **Schema** defines the *structure* (entities and fields).
2. **Classify** assigns each field exactly one **role** from a fixed vocabulary of 8.
3. **Plan** (`plan.yaml`) captures the normalized schema, the source binding, and one role per field. This is your **human review checkpoint** — you can edit any role by hand.
4. **Run** a processor from the plan: `profile` (measure) or `deidentify` (transform).

The **plan is the single reproducible artifact**. Once you have a reviewed `plan.yaml`, every later run reads it with `--plan plan.yaml` and needs nothing else.

### The seven commands at a glance

| Command | What it does |
| --- | --- |
| `inspect-schema` | Parse a schema and print the normalized model; add `--classify` to show roles. |
| `plan` | Build the editable `plan.yaml`. |
| `profile` | Count tokens and compute per-field metrics. |
| `deidentify` | Write role-driven de-identified data (the main job). |
| `validate` | Check a `profile` or `plan` JSON against its bundled contract. |
| `run` | Run a registered custom processor. |
| `init-secret` | Write a private HMAC secret (`chmod 600`). |

**Exit codes** (the same everywhere): **0** = success · **2** = a QA gate failed, nothing was written · **1** = usage or other error.

`profile`, `deidentify`, and `run` accept **either** `--plan plan.yaml` **or** a fresh `--schema`/`--source` pair. `plan` and `inspect-schema` do not take `--plan`.

---

## Step 1 — Give it your schema

The schema tells schemaforge what the entities and fields are. Start here by figuring out which situation you are in.

### Decision guide

| Your situation | What to do |
| --- | --- |
| **I have a live database** (Postgres, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Mongo, …) | You need a **schema file** first. schemaforge does **not** reflect a schema from a live URL. Go to **Appendix A** to export a schema-only DDL dump, then come back with that file as `--schema`. |
| **I already have a schema file** (`.sql`, `.xsd`/`.xml`, `.json`, `.yaml`, `.csv` header, …) | Pass it directly with `--schema yourfile`. Continue to Step 2. |
| **I only have data files** and no schema | Omit `--schema`. Point `--source` at a data file and schemaforge **infers** the schema from a sample of it. Continue to Step 2. |

> **Rule to remember:** a database **URL** given to `--source` must always be paired with a `--schema` **file**. Passing a URL to `--schema` fails with *"Could not auto-detect the schema format."* See **Appendix A**.

### Generating a schema

Don't have a schema file yet? There are three ways to get one.

**1. Export it from your database.** Your database can write its own schema out as a `.sql` file in a single command. The exact command for Postgres, MySQL, SQL Server, Oracle, Snowflake, BigQuery, and more is in **[Appendix A](#appendix-a--generating-a-schema-from-your-database)**. This is the most reliable option, because the database writes out its real types and keys.

**2. Let schemaforge infer one from your data.** Point it at a single data file and *omit* `--schema`; schemaforge reads a sample, works out the fields and types, and writes the result into `plan.yaml` for you to review and edit:

```bash
schemaforge plan --source customers.csv --out plan.yaml
```

Open `plan.yaml` — the inferred schema sits under `schema:` (every field, its type, and its role). Correct anything it guessed wrong, then run everything else from that plan. To just *look* at what it inferred, without writing a plan:

```bash
schemaforge inspect-schema --schema customers.csv --classify
```

Point `--source` at **one file** here (a `.csv`, `.jsonl`, or `.json`) — inference reads a sample of that single file, so include enough rows to cover every field. For several tables at once, use option 1 or 3.

**3. Write one by hand.** A schema is just a small text file, and the clearest format is SQL DDL — one `CREATE TABLE` per table. Copy this, rename the tables and columns to match your data, and save it as `schema.sql`:

```sql
CREATE TABLE customer (
  customer_id INTEGER PRIMARY KEY,                        -- the key for this table
  email       VARCHAR(255),
  full_name   VARCHAR(120),
  birth_date  DATE,
  notes       TEXT
);

CREATE TABLE "order" (                                    -- quote a name that is a SQL keyword
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customer(customer_id),   -- links each order to a customer
  order_date  DATE,
  amount      DECIMAL(10,2)
);
```

Then confirm it parses before you rely on it:

```bash
schemaforge inspect-schema --schema schema.sql --classify
```

You should see both tables, their keys, and a role for every field.

### Accepted schema formats

| Format | Extensions | Notes |
| --- | --- | --- |
| SQL DDL | `.sql`, `.ddl` | Parses `CREATE TABLE`, primary keys, foreign keys, column types. |
| XML / XSD | `.xsd`, `.xml` | XSD gives declared structure; plain XML can be inferred. |
| JSON / JSON-Schema | `.json` | JSON-Schema is read directly; plain JSON can be inferred. |
| YAML | `.yaml`, `.yml` | Reads schema-shaped YAML or infers from data. |
| Delimited header | `.csv`, `.tsv` | Uses the header row as the field list. |

Format is auto-detected by extension first, then by content.

### Forcing the format

If auto-detection guesses wrong (for example a `.ddl` that isn't recognized, or a `.xml` you want read as XSD), force it with `--format`. Valid values: `sql`, `xml`, `json`, `yaml`, `delimited`.

```bash
schemaforge inspect-schema --schema customers.xsd --format xml
schemaforge inspect-schema --schema schema.ddl  --format sql
schemaforge inspect-schema --schema customers.csv --format delimited
```

> **To get a schema out of your specific database** (Postgres, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Mongo, …), see **Appendix A — Generating a Schema from Your Database**. It gives the exact export command per engine.

Once you can run `schemaforge inspect-schema --schema <yourfile> --classify` and see your entities and roles, Step 1 is done.

---

## Step 2 — Point it at your data

`--source` says where the actual rows live. It accepts files **or** a database URL.

### File sources

Point `--source` at a **single file**, a **directory**, or a **glob**:

```bash
# a directory (one file per entity)
schemaforge plan --schema schema.sql --source ./data --out plan.yaml

# a single data file
schemaforge profile --schema customer.json --source ./customer.jsonl --out-json profile.json

# a glob
schemaforge profile --schema schema.sql --source "./exports/*.csv" --out-json profile.json
```

**Filename-stem matching.** When `--source` is a directory or glob, each file is matched to a schema entity by its filename **stem** (the name without extension). For a schema with entities `customer` and `order`:

```text
data/
  customer.csv     → entity "customer"
  order.csv        → entity "order"
```

If an entity ends up with zero records, the stem probably doesn't match the entity name — see **Troubleshooting**.

**Readable data formats:** CSV, TSV, TXT, JSONL, JSON, YAML, XML. CSV, JSONL, and XML are **streamed** (memory-friendly). JSON and YAML are **loaded fully**, so for very large data prefer **JSONL** or a **SQL** source.

### SQL sources

`--source` accepts any SQLAlchemy database URL. Remember the rule: a URL source **must** be paired with a `--schema` file (from Appendix A).

```bash
# SQLite (bundled with Python — no extra driver needed)
schemaforge plan --schema schema.sql --source "sqlite:///app.db" --out plan.yaml
schemaforge profile --plan plan.yaml --out-json profile.json
```

For SQL Server, Azure SQL, or Microsoft Fabric, install the `pyodbc` driver (`python3 -m pip install pyodbc`) and supply an ODBC URL:

```bash
schemaforge plan \
  --schema schema.sql \
  --source "mssql+pyodbc://user:password@host/database?driver=ODBC+Driver+18+for+SQL+Server" \
  --out plan.yaml
```

The exact URL and driver for **your** database — plus how to export the schema file that must accompany it — are in **Appendix A**.

---

## Step 3 — Review the plan

`plan.yaml` is the human review checkpoint. Open it and confirm every field's `role` is what you want. This is where you catch a misclassification **before** any data is processed.

```yaml
schemaforge_version: 0.1.3
source:
  kind: file
  location: ./data
  group_key: customer_id
content_roles: [categorical, free_text, numeric, quasi_identifier]
processors:
  profile: {}
  deidentify:
    date_strategy: shift
    date_max_days: 365
schema:
  entities:
    - name: customer
      primary_key: [customer_id]
      fields:
        - name: customer_id
          type: integer
          is_primary_key: true
          role: structural
        - name: email
          type: string
          role: identifier         # ← edit this if it's wrong
        - name: full_name
          type: string
          role: quasi_identifier
        - name: notes
          type: string
          role: free_text
```

The plan records the normalized schema, the source binding, **one role per field**, processor options, the list of roles counted as profile "content" (`content_roles`), a config digest, and the schemaforge version.

### Fixing a wrong role

You have two ways:

- **Edit `plan.yaml` directly.** Change a field's `role:` to any of the 8 valid roles, save, and re-run from the plan. No rebuild needed.
- **Use a config override and rebuild.** Add a `roles:` entry to a `config.yaml` (see **Configuration**) and rebuild the plan. Prefer this when the same rule applies across runs.

```bash
# re-run straight from the reviewed plan — no schema/source needed
schemaforge profile   --plan plan.yaml --out-json profile.json
schemaforge deidentify --plan plan.yaml --out ./clean --secret-file secret.key
```

You only need to regenerate the plan if the **schema**, **source**, or **config** changed.

---

## Step 4 — Run a processor

### profile — measure the data

```bash
schemaforge profile --plan plan.yaml --out-json profile.json
```

`profile` reports, per the example run, `records: 7   total tokens: 259   content: 63 (24.3%)`. In full it gives you:

| Metric | Meaning |
| --- | --- |
| Record counts by entity | How many rows each entity has. |
| Total token count | Exact token count over full records, using `o200k_base` (with `cl100k_base` as a cross-check). |
| Content-only token count | Tokens from fields whose role is in `content_roles` — the part that is "real content" rather than structure. |
| Structural token count | Tokens attributable to keys and structure. |
| Per-field metrics | Coverage %, distinct counts, date ranges, numeric min/max/mean, and categorical top values. |

Profiling is **streaming**, and its output validates against a bundled contract (`schemaforge validate --json profile.json --kind profile`).

### deidentify — the main job

```bash
schemaforge init-secret --out secret.key           # once
schemaforge deidentify --plan plan.yaml --out ./clean --secret-file secret.key
```

Each field is transformed according to its **role**:

| Role | What de-identify does |
| --- | --- |
| `identifier` | Replaced with a **deterministic keyed surrogate** (a stable pseudonym; never equal to the source value). |
| **Primary & foreign keys** | Replaced with deterministic surrogates that **share a namespace**, so a PK and the FKs that reference it map identically and **joins survive**. |
| `quasi_identifier` | **Generalized** — names → synthetic tokens, dates → year, ages → bands (with a 90+ cap), ZIP → prefix. |
| `date` | **Interval-preserving shift** (or generalized to year with `--date-strategy generalize`). A date that **cannot be parsed is redacted**, never passed through. |
| `free_text` | **Scrubbed** of: the record's own identifiers, any person **name carried from another entity**, and shape-detected identifiers (email, phone, SSN, IP, URL, long number runs). |
| `numeric`, `categorical`, `boolean` | Passed through unchanged. |
| `structural` | Passed through unless it is a primary or foreign key. |

**Determinism.** Given the same secret, the same input always produces the same surrogate — so re-runs are stable and cross-entity joins line up. The secret comes from `init-secret`, is never logged, and must be kept private.

**Date strategy and grouping.** `--date-strategy shift` (default) moves dates by a consistent offset **within a group**, preserving intervals; `--date-strategy generalize` reduces each date to its year. Use `--group-key KEY` to set the field that records are grouped by (e.g. all of one customer's dates shift by the same offset).

**Fail-closed leak scan.** Before anything is delivered, a **shape-based net** checks every output value against known identifier shapes — so even a field the classifier *mislabeled* is caught, and the run stops with exit code **2** having written nothing to `--out`. The output is built in a **staging directory**; it appears in `--out` **only on a clean pass** (fail-closed).

> **When the leak scan fires:** it names the field and value shape that tripped it. The fix is to set that field's `role` to `identifier` (or `quasi_identifier`) in `plan.yaml`, then re-run. Nothing was written, so you have lost nothing.

**Honest limitation.** Free text can still hold PII that has no name-like or recognizable shape — a bare street address, for example. That can survive the scrub. If a free-text field is high-risk, the safe move is to set its role to `identifier` in `plan.yaml` (so the whole field is replaced) or drop the field before you deliver.

### run — a custom processor

```bash
schemaforge run --plan plan.yaml --processor rowcount --out-json rows.json
```

Runs a processor you have registered via the Python API (see **Python API**).

---

## Understanding the output files

After a profile + de-identify run you will have these artifacts. Know which are safe to hand over:

| Artifact | What it is | Safe to deliver? |
| --- | --- | --- |
| `plan.yaml` | Normalized schema, source binding, and roles. Contains **no row values**. | Internal — it exposes your schema and field names, but no data. Keep it as your reproducible record. |
| `profile.json` | Token counts and per-field metrics. | **Review first.** Metrics can include real sample values — categorical top values, numeric min/max, date ranges. Redact before sharing if those are sensitive. |
| `clean/<entity>.jsonl` | The de-identified rows, one JSONL file per entity. | **Yes — this is the deliverable.** |
| `clean-PRIVATE/` (`mapping.json`, `audit.json`, `README.txt`) | The re-identification mapping and audit log (`chmod 700`). | **Never.** This reverses the de-identification. Keep it with the secret, apart from the delivered data. |
| `secret.key` | The HMAC secret used for deterministic pseudonymization (`chmod 600`). | **Never.** Keep private; it is never logged. |

Rule of thumb: ship **only** `clean/`. The `-PRIVATE` directory and `secret.key` stay behind.

---

## Field roles reference

Every field gets **exactly one** role from this closed vocabulary of eight:

| Role | Meaning | Common examples |
| --- | --- | --- |
| `identifier` | Directly identifies a person, account, or row. | email, phone, SSN, UUID, account number |
| `quasi_identifier` | May identify someone in combination with other fields. | name, birth date, ZIP, demographic fields |
| `free_text` | Narrative or unstructured text. | notes, comments, descriptions |
| `date` | Date or timestamp to shift or generalize. | order_date, created_at |
| `numeric` | Quantity or measurement. | amount, score, quantity |
| `categorical` | Label, enum, or code with limited values. | status, country, type |
| `boolean` | True/false value. | active, is_paid |
| `structural` | Keys or structure needed for joins and shape. | primary key, foreign key, row index |

### How a role is decided

Classification is **deterministic**. The classifier considers these signals **in order**, and the first that resolves wins:

1. **Config overrides** — an explicit `roles:` entry in your config.
2. **Primary/foreign keys and structural facts** from the schema.
3. **Declared type or format** of the field.
4. **Built-in name patterns.**
5. **Sampled values** from the data.
6. **Type fallback.**

If a role is wrong, edit `plan.yaml` or add a `roles:` override in `config.yaml` and rebuild.

---

## Configuration (config.yaml)

A `config.yaml` lets you steer classification and processor behavior. **Every section is optional.** Pass it when building the plan:

```bash
schemaforge plan --schema schema.sql --source ./data --config config.yaml --out plan.yaml
```

```yaml
# config.yaml — every section is optional.

# Force a field's role when the classifier gets it wrong.
# Key is "entity.field" (preferred) or a bare "field" (applies everywhere).
roles:
  customer.membership_code: categorical
  order.tracking_ref: identifier

# Extra name patterns (regex, case-insensitive), merged over the built-ins.
patterns:
  identifier: ['(^|_)policy_no($|_)', 'external_ref']
  free_text:  ['clinical_summary']

# Tune the sampled-value classifier thresholds.
thresholds:
  sample_size: 200
  id_distinct_ratio: 0.95
  categorical_max_distinct: 50
  freetext_min_len: 40

# Which roles the profiler counts as "content" (vs structure).
content_roles: [free_text, numeric, categorical, quasi_identifier]

# Per-processor options.
processors:
  deidentify:
    date_strategy: shift        # or: generalize (reduce dates to the year)
    date_max_days: 365          # max shift window
    # also available: leak_min_digits, reference_year
```

| Section | Controls |
| --- | --- |
| `roles` | Override the role of a specific field (`entity.field`) or every field of a name (`field`). This is signal #1 — it wins over everything. |
| `patterns` | Extra case-insensitive regexes per role, merged over the built-in name patterns. |
| `thresholds` | The sampled-value classifier's cutoffs (sample size, ID distinctness ratio, categorical cap, free-text minimum length). |
| `content_roles` | Which roles `profile` counts as "content" tokens vs structure. |
| `processors.deidentify` | `date_strategy` (`shift`/`generalize`), `date_max_days`, plus `leak_min_digits` and `reference_year`. |

---

## Safety & quality gates

schemaforge enforces **hard QA gates**. If a gate fails it exits with code **2** and writes nothing (usage/other errors exit **1**; success exits **0**). The gates:

- The schema has **at least one entity** with **uniquely named fields**.
- **Every field has exactly one valid role.**
- Plans **round-trip and validate** against the bundled plan contract.
- Profile metrics are **internally consistent** (token, count, coverage, percentile, and distribution figures agree).
- De-identify has **matching input/output record counts**, an **injective** mapping, and **no known identifier or identifier-shaped value surviving** verbatim in the output.

For **de-identify**, QA runs **before** the output is promoted from staging. A failed gate therefore writes **nothing** to your `--out` directory — the delivery is fail-closed.

### Untrusted input hardening

schemaforge treats every input file as hostile:

- **XML** (schema or data) is parsed with **DTD/`DOCTYPE` declarations rejected**, closing entity-expansion ("billion laughs") denial-of-service and external-entity (XXE) disclosure. A sub-1 KB file cannot expand to gigabytes.
- **YAML** is always loaded with `safe_load`, so no arbitrary Python objects are constructed from input.
- In-memory **XML** input is size-capped, and the source is only ever **read**, never written.

---

## Troubleshooting

Symptoms within this manual's scope — command usage, plans, roles, the leak gate, and the secret:

| Symptom | Likely cause | Fix |
| --- | --- | --- |
| `schemaforge: command not found` | Not installed on your `PATH`. | `python3 -m pip install .` from the repo root, or run from a checkout with `PYTHONPATH=src python3 -m schemaforge ...`. |
| `No module named schemaforge` | Installed into a different interpreter/venv. | Activate the environment where you installed it, or reinstall with the `python3` you are using. |
| `Could not auto-detect the schema format` | You passed a **database URL** to `--schema`, or an unusual file extension. | Give `--schema` a **file** (export one via **Appendix A**), or force it with `--format sql\|xml\|json\|yaml\|delimited`. |
| An entity has **zero records** | Filename stem doesn't match the entity name, or a container file isn't keyed by entity. | Rename so the stem matches (e.g. `customer.csv` for entity `customer`); for one JSON/YAML/XML container, key the arrays by entity name. |
| A field has the **wrong role** | Classifier guessed from name/type/samples. | Edit `role:` in `plan.yaml` and re-run, **or** add a `roles:` override in `config.yaml` and rebuild the plan. |
| **De-identify fails with a leak gate** (exit 2) | A value matched an identifier shape in a field not marked as one. | Set that field's `role` to `identifier` or `quasi_identifier` in `plan.yaml` and re-run. Nothing was written to `--out`. |
| Command exits **2** with no output written | A QA gate failed (fail-closed). | Read the reported gate, fix the plan/field/config, re-run. |
| Free-text address or odd token survived | No name/shape signal to catch it. | Set that free-text field's role to `identifier` in `plan.yaml` (replaces the whole field), or drop the field before delivery. |

For **database connection and driver** problems (wrong URL, missing ODBC driver, auth failures), see **Appendix A — Common problems**.

---

## Python API

Everything the CLI does is available in Python:

```python
import schemaforge as sf

schema = sf.ingest("schema.sql")
plan = sf.build_plan(schema, "./data")

profile = sf.profile(plan)
assert not sf.errors(sf.run_qa(profile))
sf.write_json(profile.output, "profile.json")

secret = sf.generate_secret()
sf.deidentify(plan, out="clean", secret=secret)
```

Useful entry points:

```python
sf.ingest(source, fmt="auto")
sf.build_plan(schema, source, config=None)
sf.profile(plan)
sf.deidentify(plan, out="clean", secret_file="secret.key")
sf.run(plan, "processor_name")
sf.run_qa(result)
sf.errors(issues)
sf.validate(obj, "profile")
sf.write_json(obj, path)
sf.write_yaml(obj, path)
sf.generate_secret()
```

**Custom processors** register without touching core files, then run from the CLI with `schemaforge run --processor <name>`:

```python
import schemaforge as sf

@sf.register
class RowCount(sf.Processor):
    name = "rowcount"

    def run(self, reader, plan):
        rows = sum(1 for entity in reader.entities()
                     for _ in reader.iter_records(entity))
        return sf.ProcessorResult(self.name, {"rows": rows})

sf.run(plan, "rowcount")
```

---

## Requirements

- Python 3.9+
- PyYAML
- jsonschema
- tiktoken
- SQLAlchemy
- Optional database drivers for non-SQLite SQL sources (see Appendix A)

## License

Proprietary — Meridian Intelligence, engagement-scoped. This software is provided solely for the specific engagement for which it is supplied and must be deleted and cleared after that exercise. See [LICENSE](LICENSE) and [LICENSE.pdf](LICENSE.pdf).

---

> **Next:** see [Appendix A — Generating a Schema from Your Database](#appendix-a--generating-a-schema-from-your-database) for step-by-step schema export and connection instructions for every major database.

---

## Appendix A — Generating a Schema from Your Database

`schemaforge` needs a **schema** (the shape of your data: tables/entities, columns/fields, types, primary keys, and foreign keys). This appendix shows, for every common database, how to hand schemaforge that schema. You do not need to be a DBA — every step is copy-pasteable, and where a step changes between Windows, macOS, and Linux, it says so.

There are two ways to give schemaforge a schema, plus a fallback for when you have no schema at all:

- **Path 1 — Export a schema file** (`.sql` DDL, *schema only, no data*). You produce a plain-text `CREATE TABLE …` script and point schemaforge at it with `--schema`.
- **Path 2 — Connect schemaforge directly** to the live database with a SQLAlchemy URL (`--source "<url>"`), so it reads your real tables with their exact types.
- **Fallback — Infer from a data file** when you have neither a schema nor a live connection.

---

### Which path should I use?

| Your situation | What to do |
|---|---|
| **You can reach the live database** | *Best.* Use the live connection two ways: pull a **schema-only DDL dump** (Path 1) to feed `--schema`, and pass the **SQLAlchemy URL** (Path 2) as your live `--source`. schemaforge then reads the real tables — exact types, primary keys, foreign keys. |
| **You cannot connect** — a DBA emails you a `.sql`/`.ddl` script, or you have an offline dump | Use **Path 1** only. Feed the `.sql` file to `--schema` and profile from exported data files. |
| **You have only data files** (CSV / JSONL / JSON / XML / YAML), no schema and no live DB | **Infer it.** Point `--source` at the data file and omit `--schema`. |

> **Why the live connection is the most reliable.** A schema-only DDL dump is the database's own catalog written out exactly — same types, same keys — and it doubles as your `--schema`. That is why connecting to the live database (to dump the schema *and* to read the rows) beats a stale file someone sent you weeks ago.

---

### How schemaforge uses what you give it

Two flags do the work. Understanding them prevents most mistakes:

- **`--schema <file>`** defines the *structure*. It accepts SQL DDL (`.sql`/`.ddl`), XSD/XML (`.xsd`/`.xml`), JSON / JSON-Schema (`.json`), YAML (`.yaml`), or a delimited header (`.csv`/`.tsv`).
- **`--source <where>`** is where the *rows* live: a file, a directory, a glob like `"./exports/*.csv"`, **or** a SQLAlchemy database URL.

```bash
# Typical live run: schema from a dump file, data read live from the same DB
schemaforge plan --schema schema.sql --source "postgresql+psycopg://user:pass@host:5432/shop" --out plan.yaml
```

Two things to know about the current version:

- A **bare database URL is a data source, not a schema source.** Always pair a live `--source "<url>"` with a `--schema <file>`. schemaforge does **not** auto-generate a schema from a URL alone — passing a URL to `--schema` fails with *"Could not auto-detect the schema format."* The right schema file is the schema-only DDL dump from that same database (Path 1).
- If you **omit `--schema`**, then `--source` must be a **data file**, and schemaforge infers the schema from it:

```bash
# Fallback: infer the schema straight from a data file
schemaforge plan --source ./customers.csv --out plan.yaml
```

---

### Before you start — placeholders and passwords

Throughout, replace these placeholders:

| Placeholder | Meaning | Common default |
|---|---|---|
| `HOST` | server hostname or IP | `localhost` |
| `PORT` | server port | varies (see each DB) |
| `DBNAME` | database / catalog name | — |
| `USER` / `PASSWORD` | login credentials | — |

**If your password (or username) contains special characters** (`@ : / # ? % &` etc.), URL-encode it or the connection URL will break. Encode any value like this:

```bash
python3 -c "import urllib.parse,sys; print(urllib.parse.quote_plus(sys.argv[1]))" 'p@ss:w/rd#1'
# -> p%40ss%3Aw%2Frd%231
```

Drivers are **not** bundled with schemaforge — you `pip install` the one for your database (each Path 2 below tells you which, as a plain `pip install <package>` line).

---

### 1. SQLite

A single file — no server, no login.

**Path 1 — Export a schema file**

```bash
# macOS/Linux (sqlite3 is preinstalled); Windows: download "sqlite-tools" first
sqlite3 app.db .schema > schema.sql
```

GUI alternative: **DB Browser for SQLite** → *File → Export → Database to SQL file* → tick *Keep original schema, no data*.

**Path 2 — Connect schemaforge directly**

Nothing to install — SQLAlchemy and Python's `sqlite3` are already present.

```text
# absolute path uses FOUR slashes; relative path uses three
sqlite:////ABSOLUTE/PATH/app.db
sqlite:///relative/app.db
# Windows: sqlite:///C:\path\app.db
```

```bash
schemaforge plan --schema schema.sql --source "sqlite:////Users/you/app.db" --out plan.yaml
```

---

### 2. PostgreSQL

Also covers **Amazon RDS / Aurora PostgreSQL, Google Cloud SQL, Supabase, and Neon** — identical driver and URL; only the host and TLS requirement differ.

**Path 1 — Export a schema file**

```bash
# -s / --schema-only excludes all data; add -W to be prompted for the password
pg_dump --schema-only -h HOST -p 5432 -U USER -d DBNAME -f schema.sql
```

GUI (**pgAdmin**): right-click the database → *Backup…* → Format **Plain** → *Dump Options* tab → under *Sections* choose **Only schema** → *Backup*.

**Path 2 — Connect schemaforge directly**

```bash
pip install "psycopg[binary]"     # psycopg 3 (recommended)
# or:  pip install psycopg2-binary
```

```text
postgresql+psycopg://USER:PASSWORD@HOST:5432/DBNAME
postgresql+psycopg2://USER:PASSWORD@HOST:5432/DBNAME   # if you installed psycopg2
```

> Be explicit about the driver. In SQLAlchemy 2.1 a bare `postgresql://` URL defaults to psycopg 3; older versions default to psycopg2. Writing `+psycopg` or `+psycopg2` avoids surprises.

```bash
schemaforge plan --schema schema.sql \
  --source "postgresql+psycopg://appuser:s3cret@db.example.com:5432/shop" --out plan.yaml
```

**Managed services:** Supabase, Neon, RDS, and Cloud SQL usually **require TLS** — append `?sslmode=require`. Supabase host is `db.<project-ref>.supabase.co`; Neon requires SSL by default.

---

### 3. MySQL

**Path 1 — Export a schema file**

```bash
# --no-data (short: -d) omits all rows; add --routines --events to include procedures/triggers
mysqldump --no-data -h HOST -P 3306 -u USER -p DBNAME > schema.sql
```

GUI (**MySQL Workbench**): *Server → Data Export* → select the schema → set **Dump Structure Only** → *Export to Self-Contained File* → *Start Export*.

**Path 2 — Connect schemaforge directly**

```bash
pip install PyMySQL          # pure-Python, easiest
# or:  pip install mysqlclient  (C driver; needs build tools)
```

```text
mysql+pymysql://USER:PASSWORD@HOST:3306/DBNAME?charset=utf8mb4
mysql+mysqldb://USER:PASSWORD@HOST:3306/DBNAME     # if you installed mysqlclient
```

```bash
schemaforge plan --schema schema.sql \
  --source "mysql+pymysql://appuser:s3cret@db.example.com:3306/shop?charset=utf8mb4" --out plan.yaml
```

---

### 4. MariaDB

MariaDB speaks the MySQL protocol, so MySQL tools work too.

**Path 1 — Export a schema file**

```bash
# modern MariaDB ships mariadb-dump; older installs use mysqldump (same flags)
mariadb-dump --no-data -h HOST -P 3306 -u USER -p DBNAME > schema.sql
```

GUI (**DBeaver**): right-click the schema → *Generate SQL → DDL*, or *Tools → Dump* with data unchecked.

**Path 2 — Connect schemaforge directly**

```bash
pip install mariadb          # needs MariaDB Connector/C: macOS `brew install mariadb-connector-c`
# simplest cross-platform alternative that also works against MariaDB:
# pip install PyMySQL   ->   mysql+pymysql://...
```

```text
mariadb+mariadbconnector://USER:PASSWORD@HOST:3306/DBNAME
```

> Note: a bare `mariadb://` URL defaults to the `mysqldb` driver. Use `mariadb+mariadbconnector://` to select the MariaDB connector explicitly.

```bash
schemaforge plan --schema schema.sql \
  --source "mariadb+mariadbconnector://appuser:s3cret@db.example.com:3306/shop" --out plan.yaml
```

---

### 5. Microsoft SQL Server

**Path 1 — Export a schema file**

Command line (**mssql-scripter**, cross-platform — schema only is the default):

```bash
pip install mssql-scripter
mssql-scripter -S HOST,1433 -d DBNAME -U USER -P PASSWORD -f schema.sql
# (add --schema-and-data or --data-only ONLY if you want data; default is schema only)
```

GUI (**SSMS**): right-click the database → *Tasks → Generate Scripts…* → choose objects → *Advanced* → set **Types of data to script = Schema only** → save to a `.sql` file. **Azure Data Studio** offers the same *Generate Scripts* flow.

**Path 2 — Connect schemaforge directly**

```bash
pip install pyodbc
```

Also install Microsoft's ODBC driver:
- **Windows:** *ODBC Driver 18 for SQL Server* MSI from Microsoft.
- **macOS:** `brew install msodbcsql18`
- **Linux:** add Microsoft's apt/yum repo, then install `msodbcsql18`.

```text
mssql+pyodbc://USER:PASSWORD@HOST:1433/DBNAME?driver=ODBC+Driver+18+for+SQL+Server
```

> Driver 18 encrypts by default. Against a **dev/self-signed** server add `&TrustServerCertificate=yes`. Do **not** add that against production.

```bash
schemaforge plan --schema schema.sql \
  --source "mssql+pyodbc://sa:Str0ng%21Pass@sqlsvr.example.com:1433/shop?driver=ODBC+Driver+18+for+SQL+Server" \
  --out plan.yaml
```

---

### 6. Azure SQL Database

Same driver, tools, and URL as SQL Server — only the host and TLS posture differ.

**Path 1 — Export a schema file:** identical to SQL Server — `mssql-scripter`, or SSMS/Azure Data Studio *Generate Scripts → Schema only*.

**Path 2 — Connect schemaforge directly**

```bash
pip install pyodbc     # plus the ODBC Driver 18 install shown in §5
```

- Host is `SERVERNAME.database.windows.net`, port `1433`.
- Encryption is required — driver 18 does this by default, so **do not** add `TrustServerCertificate=yes` (Azure presents a valid certificate).
- Some setups need the username as `USER@SERVERNAME`.

```text
mssql+pyodbc://USER:PASSWORD@SERVERNAME.database.windows.net:1433/DBNAME?driver=ODBC+Driver+18+for+SQL+Server
```

```bash
schemaforge plan --schema schema.sql \
  --source "mssql+pyodbc://appuser:s3cret@myserver.database.windows.net:1433/shop?driver=ODBC+Driver+18+for+SQL+Server" \
  --out plan.yaml
```

---

### 7. Microsoft Fabric (Warehouse / Lakehouse SQL analytics endpoint)

Fabric's SQL endpoint accepts read queries over the standard SQL Server protocol, with two rules: **only ODBC Driver 18+** and **only Microsoft Entra ID auth** (SQL username/password is not supported). Get the host from *Warehouse → Settings → SQL connection string* — it looks like `<name>.datawarehouse.fabric.microsoft.com`.

**Path 1 — Export a schema file**

Connect **SSMS** or **Azure Data Studio** to the SQL analytics endpoint using *Microsoft Entra* authentication, then *Generate Scripts → Schema only* → save `.sql` (same flow as §5). `mssql-scripter` with Entra auth works too.

**Path 2 — Connect schemaforge directly**

```bash
pip install pyodbc azure-identity     # plus the ODBC Driver 18 install shown in §5
```

Because Entra tokens are involved, build the URL from a full ODBC string. For automation, a **service principal** is simplest:

```bash
# prints a ready-to-paste --source URL
python3 - <<'PY'
import urllib.parse
odbc = ("Driver={ODBC Driver 18 for SQL Server};"
        "Server=<ENDPOINT>.datawarehouse.fabric.microsoft.com,1433;"
        "Database=<WAREHOUSE>;Encrypt=Yes;TrustServerCertificate=No;"
        "Authentication=ActiveDirectoryServicePrincipal;"
        "UID=<CLIENT_ID>;PWD=<CLIENT_SECRET>")
print("mssql+pyodbc:///?odbc_connect=" + urllib.parse.quote_plus(odbc))
PY
```

```bash
schemaforge plan --schema schema.sql --source "mssql+pyodbc:///?odbc_connect=Driver%3D..." --out plan.yaml
```

> For a person at a laptop, swap `Authentication=ActiveDirectoryServicePrincipal;UID=...;PWD=...` for `Authentication=ActiveDirectoryInteractive;UID=<your-email>` — a browser window handles sign-in. For headless jobs, the most robust method fetches an access token with `azure-identity` and passes it via pyodbc's `attrs_before` (create the engine in a short Python wrapper).

---

### 8. Oracle Database

**Path 1 — Export a schema file**

- **SQL Developer (GUI):** *Tools → Database Export…* → untick *Export Data* (DDL only) → pick your schema/objects → save `.sql`. Or right-click a table → *Quick DDL → Save to File*.
- **SQL\*Plus (`DBMS_METADATA`):** spool per-table DDL to a file:

```sql
SET LONG 200000 PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON FEEDBACK OFF
SPOOL schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'YOUR_SCHEMA') FROM all_tables WHERE owner = 'YOUR_SCHEMA';
SPOOL OFF
```

- **Data Pump (metadata only):** export metadata, then let `impdp` write the DDL without importing anything:

```bash
expdp USER/PASSWORD SCHEMAS=YOUR_SCHEMA CONTENT=METADATA_ONLY DIRECTORY=DATA_PUMP_DIR DUMPFILE=meta.dmp
impdp USER/PASSWORD DIRECTORY=DATA_PUMP_DIR DUMPFILE=meta.dmp SQLFILE=schema.sql
```

**Path 2 — Connect schemaforge directly**

```bash
pip install oracledb        # modern, thin-mode driver (preferred over legacy cx_Oracle)
```

```text
oracle+oracledb://USER:PASSWORD@HOST:1521/?service_name=SERVICE
```

```bash
schemaforge plan --schema schema.sql \
  --source "oracle+oracledb://appuser:s3cret@db.example.com:1521/?service_name=XEPDB1" --out plan.yaml
```

---

### 9. IBM Db2

**Path 1 — Export a schema file**

From a Db2 client shell, `db2look` extracts DDL (`-e`), optionally limited to one schema (`-z`):

```bash
db2look -d DBNAME -e -z YOUR_SCHEMA -o schema.sql
```

**Path 2 — Connect schemaforge directly**

```bash
pip install ibm_db_sa       # pulls in the ibm_db driver
```

```text
db2+ibm_db://USER:PASSWORD@HOST:50000/DBNAME
```

For TLS, append `;SECURITY=SSL` and use the SSL port your server exposes (often `50001`/`25000`).

```bash
schemaforge plan --schema schema.sql \
  --source "db2+ibm_db://db2inst1:s3cret@db.example.com:50000/SHOP" --out plan.yaml
```

---

### 10. Amazon Redshift

Redshift has no `SHOW CREATE TABLE` and no `pg_dump` support, so use AWS's official admin view for Path 1.

**Path 1 — Export a schema file**

```bash
# 1) Load the AWS-provided view (one-time). Grab v_generate_tbl_ddl.sql from:
#    https://github.com/awslabs/amazon-redshift-utils (src/AdminViews/)
psql "host=HOST port=5439 dbname=DBNAME user=USER" -f v_generate_tbl_ddl.sql

# 2) Emit DDL for a schema, no headers/footers, into schema.sql
psql "host=HOST port=5439 dbname=DBNAME user=USER" -At \
  -c "SELECT ddl FROM admin.v_generate_tbl_ddl WHERE schemaname='public' ORDER BY tablename, seq;" > schema.sql
```

GUI alternative: **DBeaver** → right-click table/schema → *Generate DDL*.

**Path 2 — Connect schemaforge directly**

```bash
pip install sqlalchemy-redshift redshift_connector
```

```text
redshift+redshift_connector://USER:PASSWORD@HOST:5439/DBNAME
```

Host is the cluster endpoint; the default database is often `dev`.

```bash
schemaforge plan --schema schema.sql \
  --source "redshift+redshift_connector://awsuser:s3cret@my-cluster.abc123.us-east-1.redshift.amazonaws.com:5439/dev" \
  --out plan.yaml
```

---

### 11. Snowflake

**Path 1 — Export a schema file**

`GET_DDL` returns CREATE statements for a whole schema in one shot. Run it in a Snowflake worksheet and save the result, or via SnowSQL:

```sql
SELECT GET_DDL('schema', 'MYDB.PUBLIC', true);
```

```bash
# SnowSQL straight to a file, no framing
snowsql -q "SELECT GET_DDL('schema','MYDB.PUBLIC', true);" \
  -o output_file=schema.sql -o friendly=false -o header=false -o timing=false -o output_format=plain
```

**Path 2 — Connect schemaforge directly**

```bash
pip install snowflake-sqlalchemy       # brings in the Snowflake connector
```

```text
snowflake://USER:PASSWORD@ACCOUNT/DBNAME/SCHEMA?warehouse=WAREHOUSE&role=ROLE
```

`ACCOUNT` is your account identifier, typically `orgname-accountname`.

```bash
schemaforge plan --schema schema.sql \
  --source "snowflake://appuser:s3cret@myorg-myacct/SHOP/PUBLIC?warehouse=COMPUTE_WH&role=ANALYST" \
  --out plan.yaml
```

---

### 12. Google BigQuery

BigQuery uses Google credentials, not a username/password — there is no secret in the URL.

**Path 1 — Export a schema file**

```bash
# JSON schema per table (schemaforge reads JSON directly)
bq show --schema --format=prettyjson PROJECT:DATASET.TABLE > table.json
```

Or get DDL for a whole dataset via `INFORMATION_SCHEMA` and save it as `schema.sql`:

```sql
SELECT ddl FROM `PROJECT.DATASET`.INFORMATION_SCHEMA.TABLES;
```

**Path 2 — Connect schemaforge directly**

```bash
pip install sqlalchemy-bigquery
```

Authenticate first (pick one):

```bash
gcloud auth application-default login
# or point at a service-account key:
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json
```

```text
bigquery://PROJECT/DATASET
```

```bash
schemaforge plan --schema table.json \
  --source "bigquery://my-gcp-project/analytics" --out plan.yaml
```

---

### 13. CockroachDB

**Path 1 — Export a schema file**

```bash
cockroach sql --url "URL" --execute "SHOW CREATE ALL TABLES;" > schema.sql
# CockroachDB Cloud: copy the full connection string ("URL") from the console
```

**Path 2 — Connect schemaforge directly**

```bash
pip install sqlalchemy-cockroachdb psycopg2-binary
```

```text
cockroachdb://USER:PASSWORD@HOST:26257/DBNAME?sslmode=verify-full
# local insecure cluster: cockroachdb://root@localhost:26257/defaultdb?sslmode=disable
```

CockroachDB Cloud requires TLS — keep `sslmode=verify-full` and add `&sslrootcert=/path/to/ca.crt`.

```bash
schemaforge plan --schema schema.sql \
  --source "cockroachdb://appuser:s3cret@my-cluster.cockroachlabs.cloud:26257/shop?sslmode=verify-full" \
  --out plan.yaml
```

---

### 14. NoSQL / document stores (MongoDB, DynamoDB)

Document stores have **no fixed DDL** — every document can differ. So there is nothing to "export as a schema." The path is: **export a representative sample to JSON Lines (JSONL), then let schemaforge infer the schema** from that data file.

> JSONL = one JSON object per line. It is exactly what `mongoexport` produces by default, and what schemaforge infers from best. Sample **broadly** — inference can only see the fields present in your sample, so include enough documents to cover every field.

**MongoDB** — export with `mongoexport` (part of MongoDB Database Tools):

```bash
mongoexport \
  --uri="mongodb://USER:PASSWORD@HOST:27017/DBNAME" \
  --collection=customers \
  --limit=5000 \
  --out=customers.jsonl
# Add a representative slice if the collection is huge:
#   --query='{"status":"active"}'  --sort='{"updatedAt":-1}'
```

**DynamoDB** — scan a sample, then flatten items to one-per-line with `jq`:

```bash
aws dynamodb scan --table-name Customers --max-items 5000 --output json > scan.json
jq -c '.Items[]' scan.json > customers.jsonl
```

> DynamoDB items are type-tagged (e.g. `{"email":{"S":"a@b.com"}}`). schemaforge will infer against that shape; if you want clean field names, unwrap the tags with a `jq` transform or the AWS SDK before writing the JSONL.

**Then infer the schema** — point `--source` at the JSONL and omit `--schema`:

```bash
schemaforge plan --source customers.jsonl --out plan.yaml
```

---

### How do I check my schema file worked?

Before you build a plan, eyeball the parsed schema:

```bash
schemaforge inspect-schema --schema schema.sql --classify
```

You should see each entity, its primary key, `PK` / `FK` / `NOT NULL` flags, and a suggested **role** per field:

```text
origin: sql-ddl   entities: 2   relationships: 1

customer  (pk: customer_id)
  customer_id              integer    PK NOT NULL             role=structural
  email                    string     NOT NULL                role=identifier
  country                  string                             role=categorical

order  (pk: order_id)
  order_id                 integer    PK NOT NULL             role=structural
  customer_id              integer    FK->customer.customer_id  role=structural
  total                    float                              role=numeric
```

Check the entity count, that primary/foreign keys are detected, and that roles look sane. If schemaforge cannot guess the format from the file extension, name it explicitly with `--format` (`sql`, `xsd`, `json`, `yaml`, or `delimited`):

```bash
schemaforge inspect-schema --schema schema.ddl --format sql --classify
```

---

### Common problems

| Symptom / message | Likely cause | Fix |
|---|---|---|
| `SchemaError: Could not auto-detect the schema format` | You passed a database **URL** (or an unusual file) to `--schema` | `--schema` takes a *file*, not a URL. Point it at a real schema file and add `--format sql` (or `xsd`/`json`/`yaml`/`delimited`). |
| `ModuleNotFoundError: No module named 'psycopg' / 'pymysql' / 'oracledb' / 'pyodbc' …` | Database driver not installed | `pip install` the driver from that database's Path 2 (e.g. `pip install "psycopg[binary]"`). |
| `Can't load plugin: sqlalchemy.dialects:<name>` | The dialect package is missing | Install it: `snowflake-sqlalchemy`, `sqlalchemy-redshift`, `sqlalchemy-cockroachdb`, `sqlalchemy-bigquery`, or `ibm_db_sa`. |
| `Data source name not found … ODBC Driver 18 for SQL Server` | ODBC driver absent or the name in `driver=` doesn't match | Install *ODBC Driver 18 for SQL Server* (macOS `brew install msodbcsql18`) and match the `driver=ODBC+Driver+18+for+SQL+Server` string exactly. |
| Authentication failed / password rejected | Wrong credentials, **or** special characters in the password broke the URL | Verify the login; URL-encode special characters in `PASSWORD` (see *Before you start*). |
| `SSL connection is required` / `sslmode` error | Managed database mandates TLS | Add `?sslmode=require` (Postgres/Cockroach) or `Encrypt=Yes` (SQL Server — default on driver 18). |
| `SSL: CERTIFICATE_VERIFY_FAILED` against a dev SQL Server | Self-signed certificate | Add `&TrustServerCertificate=yes` — **dev only**, never in production. |
| Connection times out | Wrong host/port, or a firewall/security group blocks you | Confirm `PORT` (see the quick reference), and open network access to the DB. |
| `entities: 0`, or the dump has no `CREATE TABLE` | You exported **data only**, or a proprietary/binary dump | Re-export **schema only, as plain SQL**: `pg_dump -s`, `mysqldump --no-data`, or *Generate Scripts → Schema only*. |
| Inferred schema is missing fields | Data sample too small or too sparse | Export a larger, more representative JSONL/CSV sample so every field appears at least once. |

---

### Driver quick reference

| Database | `pip install` | URL scheme | Default port |
|---|---|---|---|
| SQLite | *(built in)* | `sqlite:///` | — |
| PostgreSQL (+ RDS/Aurora/Cloud SQL/Supabase/Neon) | `"psycopg[binary]"` *or* `psycopg2-binary` | `postgresql+psycopg://` / `postgresql+psycopg2://` | 5432 |
| MySQL | `PyMySQL` *or* `mysqlclient` | `mysql+pymysql://` / `mysql+mysqldb://` | 3306 |
| MariaDB | `mariadb` *or* `PyMySQL` | `mariadb+mariadbconnector://` | 3306 |
| SQL Server / Azure SQL | `pyodbc` + ODBC Driver 18 | `mssql+pyodbc://` | 1433 |
| Microsoft Fabric | `pyodbc` + `azure-identity` + ODBC Driver 18 | `mssql+pyodbc:///?odbc_connect=…` | 1433 |
| Oracle | `oracledb` | `oracle+oracledb://` | 1521 |
| IBM Db2 | `ibm_db_sa` | `db2+ibm_db://` | 50000 |
| Amazon Redshift | `sqlalchemy-redshift redshift_connector` | `redshift+redshift_connector://` | 5439 |
| Snowflake | `snowflake-sqlalchemy` | `snowflake://` | 443 |
| Google BigQuery | `sqlalchemy-bigquery` | `bigquery://` | — |
| CockroachDB | `sqlalchemy-cockroachdb psycopg2-binary` | `cockroachdb://` | 26257 |
| MongoDB / DynamoDB | *export to JSONL, then infer* | *(no URL — `--source file.jsonl`)* | — |
