superpapotas-synthforge

内容来源：README.md（说明文档） · 原始地址 · 查看安装指南

原始内容

# SynthForge

Create and augment BI/POS datasets with LLMs from one local CLI.

SynthForge is a simple data augmentation tool: point it at a CSV, XLSX workbook, Parquet file, or DuckDB table; declare the new columns you want; write one prompt; get an enriched output file or table. The engine handles prompts, JSON schemas, batching, caching, retries, metadata, validation, and Azure OpenAI setup.

No notebooks. No custom parser required for normal use. No Python fixture factory.

## Start Here

New to SynthForge? Start with the full user guide:

- [Complete User Guide](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/USER_GUIDE.md): end-to-end guide for installation, Azure GPT-5 Nano, file/table augmentation, POS workflows, web search, validation, exports, MCP, SQL advanced mode, and troubleshooting.
- [CLI quick reference](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/CLI.md): short command-focused reference.
- [Provider setup](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/PROVIDERS.md): Azure/OpenAI/LiteLLM, web search, Tavily, workers, rate limits, and retries.
- [MCP guide](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/MCP.md): how Codex and Claude Code should operate SynthForge through tools.

## Why SynthForge

- Versus Faker: keeps simple local generation, but adds LLM semantic fields, file/table augmentation, caching, validation, and MCP.
- Versus SDV-style modeling: focuses on fast BI/demo/test marts, not statistical reproduction of sensitive production data.
- Versus ad-hoc LLM scripts: keeps prompts in auditable runs and lets deterministic logic own IDs, FKs, dates, samples, ranges, and financial math.

## Project Status

SynthForge is an early `0.1.x` project. The default provider is `fake`, so tests and offline demos run without API keys. The best-tested real provider path is Azure OpenAI `gpt-5-nano`, with optional Responses web search for current public facts.

Current limitations:

- SQL generation is advanced mode. For normal work, use `augment-file` or `augment`.
- `generate()`, `generate_struct()`, and `generate_row_summary()` are supported in `CREATE TABLE ... AS SELECT` projections. Materialize generated columns first, then filter/join/deduplicate/sort in a second SQL statement.
- MCP safe mode is a local guardrail, not a security sandbox. Only enable unsafe SQL for trusted local workflows.
- SynthForge generates synthetic/demo data; it does not provide differential privacy, anonymization guarantees, or compliance certification.

SQL compatibility quick table:

| Need | Use | Rule |
| --- | --- | --- |
| File/table enrichment | `augment-file` / `augment` | No custom SQL required. |
| Normal relational work | DuckDB SQL | Use standard DuckDB syntax. |
| Generated BI columns | `generate_struct(...)` | Put it in a CTAS projection; SynthForge flattens declared columns. |
| Text summary column | `generate(...)` or `generate_row_summary(...)` | Materialize first, then filter/group/sort later. |
| Agent-safe file movement | MCP file tools | Avoid raw `read_*`, `COPY`, and `PRAGMA` unless unsafe mode is explicitly enabled. |

## Requirements

- Python 3.11 or newer.
- DuckDB is installed from the Python package dependencies.
- Optional: Azure OpenAI, Gemini API, Codex CLI, or another LiteLLM-backed provider for real LLM generation. Azure `gpt-5-nano` remains the focused v0.1 API path.

## Install

```bash
git clone https://github.com/Superpapotas/synthforge
cd synthforge
python3 -m pip install -e .
```

Optional real LLM support:

```bash
python3 -m pip install -e '.[llm]'
```

The default provider is `fake`, so examples run offline.

Development install:

```bash
python3 -m pip install -e '.[dev]'
```

## Quickstart

Print the built-in operating guide:

```bash
python3 -m synthforge guide
```

Start offline first. The default `fake` provider proves file handling, schemas, caching, and audit columns without an API key.

No-SQL file augmentation:

```bash
cat > products.csv <<'CSV'
sku,product_name,brand,category
SKU1,Cold Brew Coffee,Blue Bottle,Grocery
CSV

python3 -m synthforge augment-file products.csv \
  --out products_enriched.csv \
  --columns "description VARCHAR, category_clean VARCHAR" \
  --prompt "Generate POS-ready product catalog fields from this row" \
  --context "sku,product_name,brand,category" \
  --db demo.duckdb
```

Current-price lookup with web search:

```bash
python3 -m synthforge augment-file products.csv \
  --out products_prices.csv \
  --columns "current_price_usd DOUBLE, retailer_or_source VARCHAR, source_domain VARCHAR, source_url VARCHAR, price_note VARCHAR" \
  --prompt "Use web search to find a current US retail price for this product. Match package size when possible." \
  --context "sku,product_name,brand,category" \
  --web-search \
  --db demo.duckdb
```

This also works with `.xlsx`, `.tsv`, and `.parquet`. See [docs/CLI.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/CLI.md), [docs/FILES.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/FILES.md), and [docs/AUGMENT.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/AUGMENT.md).

Configure Azure OpenAI GPT-5 Nano when you want real output quality or web search:

```bash
export SYNTHFORGE_API_KEY=YOUR_AZURE_OPENAI_KEY

python3 -m synthforge configure-azure \
  --endpoint https://YOUR-RESOURCE.openai.azure.com \
  --model gpt-5-nano \
  --web-search

python3 -m synthforge doctor
```

This writes a local `.env` file and configures Azure chat completions plus Azure Responses web search at `https://YOUR-RESOURCE.openai.azure.com/openai/v1`. Azure web search availability, quota, cost, and compliance behavior can vary by subscription and region. See [docs/PROVIDERS.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/PROVIDERS.md) before running large current-fact jobs.

For web-search price lookup, test a small slice first with `--limit 5`, then scale up once sources look right.

Gemini native SDK with Google Search grounding:

```bash
export SYNTHFORGE_PROVIDER=gemini
export SYNTHFORGE_MODEL=gemini-3.1-flash-lite
export GEMINI_API_KEY=YOUR_GEMINI_API_KEY
export SYNTHFORGE_WEB_SEARCH=1
```

Codex CLI provider for local/personal workflows:

```bash
export SYNTHFORGE_PROVIDER=codex_cli
export SYNTHFORGE_MODEL=gpt-5.4-mini
export SYNTHFORGE_CODEX_REASONING_EFFORT=low
export SYNTHFORGE_CODEX_SEARCH=1
```

No-SQL table augmentation:

```bash
python3 -m synthforge query "CREATE OR REPLACE TABLE products AS SELECT * FROM (VALUES ('SKU1', 'Apple iPhone case', 'Apple')) AS t(sku, product_name, brand)" --db demo.duckdb
python3 -m synthforge augment products \
  --into products_enriched \
  --columns "description VARCHAR, category VARCHAR" \
  --prompt "Generate catalog fields from the product row" \
  --context "sku,product_name,brand" \
  --db demo.duckdb
python3 -m synthforge query "SELECT sku, description, category, _gen_status FROM products_enriched" --db demo.duckdb
```

For periodic jobs, use `--mode upsert --key sku --where "..."`. See [docs/AUGMENT.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/AUGMENT.md).

SQL workflow:

```bash
python3 -m synthforge init --db demo.duckdb
python3 -m synthforge run examples/demo.sql --db demo.duckdb
python3 -m synthforge query "SELECT * FROM enriched_tickets" --db demo.duckdb
```

`query` prints compact CSV by default; use `--format json` for structured rows. `run --show-final` prints the final query result when a script ends in `SELECT`/`WITH`/`SHOW`.

Packaged demos work from source checkouts and installed wheels:

```bash
python3 -m synthforge demo --list
python3 -m synthforge demo pos_product_enrichment --db pos.duckdb
python3 -m synthforge demo bi_retail_mart --db retail.duckdb --no-show-final
```

## 60-Second Demo

```bash
python3 -m synthforge run examples/linkedin_profiles.sql --db demo.duckdb
python3 -m synthforge query "SELECT user_id, full_name, job_title, LEFT(linkedin_description, 80) AS preview FROM mock_users_with_linkedin LIMIT 3" --db demo.duckdb
```

Example output with the offline `fake` provider:

```csv
user_id,full_name,job_title,preview
1,Allison Hill,Health physicist,Summary: user_id=1; full_name=Allison Hill; email=donaldgarcia@example.net; addr
2,Michael Thomas,TEFL teacher,Summary: user_id=2; full_name=Michael Thomas; email=swood@example.org; address=0
3,Joshua Atkins,Clinical cytogeneticist,Summary: user_id=3; full_name=Joshua Atkins; email=noahroberts@example.org; addr
```

Full BI mart demo:

```bash
python3 -m synthforge run examples/bi_retail_mart.sql --db retail.duckdb
python3 -m synthforge query "SELECT sales_channel, order_status, COUNT(*) FROM orders GROUP BY 1,2" --db retail.duckdb
```

SaaS subscription/churn mart:

```bash
python3 -m synthforge run examples/saas_subscription_mart.sql --db saas.duckdb --show-final
```

This builds `accounts`, `subscriptions`, `usage_events`, `support_tickets`, and `account_health` with valid FKs, MRR/adoption metrics, churn-risk bands, expansion signals, and row-level account health summaries.

POS product enrichment demos:

| If you need... | Run | Main output |
| --- | --- | --- |
| Simple product copy/category fields | `pos_product_enrichment` | `enriched_new_products` |
| New SKU intake | `pos_new_product_intake` | `product_catalog_ai` |
| Changed-product queue/idempotency | `pos_product_enrichment_queue` | `product_enrichment_queue`, `product_catalog_ai` |
| Watermark microbatch lifecycle | `pos_product_lifecycle_microbatch` | `product_catalog`, `product_catalog_ai` |
| Operator control tower | `pos_catalog_control_tower` | `pos_control_tower` |
| SKU/UPC identity resolution | `pos_identity_resolution` | `product_identity_recommendations` |
| Supplier taxonomy mapping | `pos_taxonomy_mapping` | `taxonomy_mapping_review` |
| Price/promo/margin QA | `pos_price_promo_margin_qa` | `pricing_policy_review` |
| Current web price lookup | `pos_web_price_lookup_40` | `pos_web_products_price_enriched` |

```bash
python3 -m synthforge run examples/pos_product_enrichment.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_new_product_intake.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_catalog_control_tower.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_product_lifecycle_microbatch.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_product_enrichment_queue.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_identity_resolution.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_taxonomy_mapping.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_price_promo_margin_qa.sql --db pos.duckdb --show-final
```

This models incremental retail/POS feeds: process only events after a watermark, detect new SKUs or changed product hashes, dedupe noisy product events, resolve SKU/UPC identity, govern supplier taxonomy, QA price/promo/margin changes, update deterministic price/discontinue changes in SQL, generate listing/receipt/search/compliance fields with `generate_struct(...)`, version content, route risky products to a steward queue, insert good rows into `product_catalog`/`product_catalog_ai`, and audit `_gen_status` failures.

## Run Tests

```bash
python3 -m pip install -e '.[dev]'
python3 -m compileall synthforge
python3 -m pytest -q
```

The test suite uses the offline `fake` provider by default. Do not commit API keys, generated DuckDB databases, or local benchmark outputs.

## SQL Examples

Scalar enrichment:

```sql
CREATE TABLE enriched_tickets AS
SELECT
  id,
  ticket_text,
  generate('Clasifica la intención en: compra, queja, devolución', ticket_text) AS intent
FROM support_tickets;
```

`generate()` can accept multiple context columns: `generate('classify this ticket', title, body, original_category)`.

Multi-column extraction:

```sql
CREATE TABLE enriched_products AS
SELECT
  product_name,
  generate_struct(
    'Extrae marca, categoría y precio aproximado en USD',
    product_name,
    'brand VARCHAR, category VARCHAR, est_price DOUBLE'
  ) AS ai
FROM products;
```

Row-level summary using every selected column:

```sql
CREATE TABLE calendar_with_summary AS
SELECT
  event_id,
  event_name,
  event_date,
  owner_team,
  region,
  priority,
  generate_row_summary(
    'Write a concise BI summary using every selected calendar column.'
  ) AS summary
FROM calendar_events;
```

`generate_row_summary()` is SynthForge-native SQL. DuckDB first materializes the selected row context, then SynthForge sends the full row as JSON to the LLM and writes the generated summary back into the final table.

Synthetic rows:

```sql
INSERT INTO customers
SELECT * FROM generate_rows(
  prompt := 'Clientes B2B mexicanos del sector de manufactura',
  rows := 1000,
  schema := 'id INTEGER SEQUENCE, name VARCHAR FAKER name, email VARCHAR FAKER email, country VARCHAR FAKER country'
);
```

`generate_rows(...)` works in `INSERT INTO ... SELECT * FROM generate_rows(...)`, `CREATE TABLE ... AS SELECT * FROM generate_rows(...)`, projected CTAS/INSERT forms such as `CREATE TABLE ids AS SELECT id FROM generate_rows(...)`, and preview queries such as `SELECT * FROM generate_rows(...) LIMIT n` or `WITH preview AS (...) SELECT ... FROM preview WHERE ...`. Simple projection previews like `SELECT id FROM generate_rows(...) LIMIT 5` push the limit into the generation budget and preserve batch insertion order for stable previews. Simple quoted table targets like `"customer rows"` are supported.

CTAS enrichment also supports CTE-shaped BI SQL:

```sql
CREATE TABLE enriched AS
WITH source_rows AS (
  SELECT * FROM imported_products WHERE active = true
)
SELECT
  sku,
  generate('Write catalog copy for this SKU', product_name, brand, category) AS product_description
FROM source_rows;
```

Structured generators:

```sql
INSERT INTO customers
SELECT * FROM generate_rows(
  prompt := 'B2B customer accounts; only summary needs LLM reasoning',
  rows := 100,
  schema := '
    customer_id INTEGER SEQUENCE,
    company_name VARCHAR FAKER company,
    email VARCHAR FAKER email,
    industry VARCHAR SAMPLE manufacturing|software|retail,
    annual_revenue DOUBLE RANGE 1000000..100000000,
    summary VARCHAR LLM
  '
);
```

Supported field directives:

- `NULL` / `NOT NULL`: optional SQL nullability after the type, for example `id INTEGER NOT NULL SEQUENCE`.
- `SEQUENCE`: deterministic 1..N IDs across batches; `INSERT INTO` existing tables offsets from the current max value to avoid duplicate keys on repeated appends.
- `FAKER provider`: uses Python Faker providers like `company`, `name`, `email`, `country`, `city`, `address`.
- `SAMPLE a|b|c`: samples from fixed literals.
- `SAMPLE 'a, b, c'`: quoted comma syntax for fixed literals.
- `RANGE min..max`: numeric range.
- `WEIGHTED_SAMPLE value:weight|...`: weighted categorical sampling.
- `DATE_RANGE yyyy-mm-dd..yyyy-mm-dd`: date generation inside inclusive bounds.
- `FK table.column`: sample valid foreign keys from an existing table column.
- `LLM`: generate this field with the configured model.

If no directive is provided, obvious deterministic fields such as `id`, `*_id`, `email`, `name`, `country`, and dates are generated locally; other fields default to `LLM`.

See `examples/calendar_summary.sql` for the ventasPipeline-style calendar pattern: structured calendar rows plus a summary per row from all selected columns.

See `examples/bi_retail_mart.sql` for a full five-table synthetic BI mart with `customers`, `products`, `orders`, `order_items`, and `support_tickets`.

See `examples/saas_subscription_mart.sql` for a B2B SaaS subscription mart with MRR, adoption, support tickets, churn risk, expansion signals, and generated account health summaries.

See `examples/pos_new_product_intake.sql` for a production-style POS intake flow: dedupe incoming product events, enrich only unknown SKUs, append to a catalog, and audit the run with SQL.

See `examples/pos_catalog_control_tower.sql` for a more operational catalog-control flow: latest event per SKU, price-only updates without LLM calls, semantic refreshes, compliance/data-quality review tasks, and `generate_row_summary(...)` for BI/operator summaries.

See `examples/pos_product_lifecycle_microbatch.sql` for a stateful scheduled POS lifecycle microbatch: persisted watermark filtering, latest-event dedupe, price/discontinue actions without LLM calls, listing content generation, persistent content versioning, steward tasks, and a BI summary table.

See `examples/pos_product_enrichment_queue.sql` for the backend queue pattern behind continuous product onboarding: latest product snapshots, `input_hash` idempotency, queued microbatches, generated catalog content, queue status updates, and reruns that do not spend tokens for unchanged SKUs.

See `examples/pos_identity_resolution.sql` for SKU/UPC dedupe: SQL creates normalized keys, exact UPC auto-merges, ambiguous brand/family/unit candidates use `generate_struct(...)` for merge recommendation and rationale, and the output includes merge/review queues plus canonical identity clusters.

See `examples/pos_taxonomy_mapping.sql` for category governance: SQL applies known supplier alias rules, unmatched terms use `generate_struct(...)` for taxonomy suggestions and reasons, and regulated/low-confidence mappings route to a review queue.

See `examples/pos_price_promo_margin_qa.sql` for price and promotion QA: SQL calculates margin floors, below-cost blocks, promo overlap, unknown SKUs, and age-restricted review gates, while `generate_struct(...)` writes merchant notes, customer-safe messages, risk summaries, recommendations, and steward queues.

Practical referential integrity:

```sql
INSERT INTO orders
SELECT * FROM generate_rows(
  prompt := 'Órdenes de compra realistas',
  rows := 5000,
  schema := 'order_id INTEGER SEQUENCE, customer_id INTEGER FK customers.id, total DOUBLE RANGE 25..2500'
);
```

## Providers

Offline fake provider:

```bash
export SYNTHFORGE_PROVIDER=fake
```

LiteLLM/OpenAI-compatible provider:

```bash
export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=openrouter/meta-llama/llama-3.1-8b-instruct
export SYNTHFORGE_API_KEY=...
```

`SYNTHFORGE_PROVIDER=litellm` is the portable default for real LLMs. Provider aliases such as `openrouter`, `openai`, `azure`, `anthropic`, `gemini`, `vertex_ai`, `xai`, `nvidia`, `huggingface`, `novita`, `vercel_ai_gateway`, `ollama`, or `local` also route through the same LiteLLM runtime; `python3 -m synthforge doctor --json` reports this as `"provider_runtime": "litellm"`. If you use an alias and provide an unprefixed model, SynthForge normalizes common LiteLLM model names for you, e.g. `SYNTHFORGE_PROVIDER=azure` + `SYNTHFORGE_MODEL=gpt-5-nano` becomes `azure/gpt-5-nano`, `SYNTHFORGE_PROVIDER=ollama` + `SYNTHFORGE_MODEL=llama3.1` becomes `ollama/llama3.1`, and `SYNTHFORGE_PROVIDER=vertex_ai` + `SYNTHFORGE_MODEL=gemini-1.5-pro` becomes `vertex_ai/gemini-1.5-pro`.

Structured JSON outputs are enabled by default through LiteLLM `response_format=json_schema` when the provider supports it. Disable with `SYNTHFORGE_STRUCTURED_OUTPUTS=0` if a provider rejects native structured outputs.

See [docs/PROVIDERS.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/PROVIDERS.md) for OpenRouter, OpenAI, Azure OpenAI, Anthropic, Gemini, Ollama, and reliability controls.

Ollama through LiteLLM:

```bash
export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=ollama/llama3.1
export SYNTHFORGE_BASE_URL=http://localhost:11434
```

## Web Search

Web search is opt-in because it adds external network calls, cost, and reproducibility risk. When web search is enabled, SynthForge bypasses the normal generation cache so current-fact outputs are not reused from stale search results; a dedicated search cache/TTL is future scope.

Native Responses API web search, using the current GA `web_search` tool only:

```bash
export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=responses
export SYNTHFORGE_RESPONSES_API_KEY=...   # or OPENAI_API_KEY
export SYNTHFORGE_RESPONSES_MODEL=gpt-4.1-mini
```

For Azure OpenAI Responses, point the Responses adapter at the Azure `/openai/v1` endpoint and pass the Azure key explicitly:

```bash
export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=responses
export SYNTHFORGE_RESPONSES_BASE_URL=https://YOUR-RESOURCE.openai.azure.com/openai/v1
export SYNTHFORGE_RESPONSES_API_KEY=...
export SYNTHFORGE_RESPONSES_MODEL=gpt-5-nano
```

Tavily search with any SynthForge LLM provider:

```bash
export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=tavily
export SYNTHFORGE_TAVILY_API_KEY=...
export SYNTHFORGE_WEB_SEARCH_GRANULARITY=row  # row|batch; row avoids cross-product context bleed
export SYNTHFORGE_TAVILY_RATE_RPM=60          # optional; defaults to SYNTHFORGE_RATE_RPM when set
export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=openrouter/meta-llama/llama-3.1-8b-instruct
export SYNTHFORGE_API_KEY=...
```

`responses` delegates search to the Responses API tool path and intentionally uses `SYNTHFORGE_RESPONSES_API_KEY`/`OPENAI_API_KEY` plus `SYNTHFORGE_RESPONSES_BASE_URL`, not the main LiteLLM credentials. OpenAI URLs use `Authorization: Bearer`; Azure OpenAI URLs use `api-key`. The provider forces `tool_choice=required` and `max_tool_calls=1` so SQL runs that enable search actually perform one hosted web-search call per LLM request. Legacy provider aliases, legacy env aliases, and the older preview search path are intentionally not supported; set `SYNTHFORGE_WEB_SEARCH_PROVIDER=responses` for native Responses search. `tavily` searches externally, appends search snippets and URLs to the row context, and then calls the normal SynthForge LLM provider, so it works with OpenRouter, Azure, Anthropic, Gemini, Ollama, or any LiteLLM-compatible model. Tavily defaults to `SYNTHFORGE_WEB_SEARCH_GRANULARITY=row` so a batched POS/product run searches each row independently; use `batch` only when the batch shares one topic and Tavily quota is more important than per-row precision. Tavily search calls have a separate RPM cap through `SYNTHFORGE_TAVILY_RATE_RPM`; if unset, SynthForge reuses `SYNTHFORGE_RATE_RPM` for search. Search result caching is future scope.

## Rate Limit Probing

SynthForge can probe provider rate-limit headers and recommend a safe worker count. Azure OpenAI uses a direct deployment probe when endpoint, key, and API version are configured; other LiteLLM-backed providers use a minimal structured generation call and whatever headers the provider exposes.

```bash
export SYNTHFORGE_PROVIDER=azure
export SYNTHFORGE_MODEL=gpt-5-nano
export SYNTHFORGE_BASE_URL=https://your-resource.openai.azure.com
export SYNTHFORGE_API_KEY=...
export SYNTHFORGE_API_VERSION=2025-04-01-preview

python3 -m synthforge probe-limits \
  --context-columns 4 \
  --latency-samples 3 \
  --sample-prompt "Write a 1-2 sentence LinkedIn style profile from a JSON row."
```

`--context-columns` estimates a narrower row-level prompt instead of assuming every call costs 1000 tokens. Override with `--estimated-tokens-per-call` when you already know the real cost. The worker recommendation is capped at 512 by default; lower it with `--max-workers-cap` if your provider or machine cannot handle that many in-flight requests.

Azure `gpt-5-*` deployments may reject explicit temperature values. The Azure probe intentionally omits `temperature`; runtime generation also retries without optional parameters if the deployment rejects them.

To let SynthForge probe automatically at runtime:

```bash
export SYNTHFORGE_MAX_WORKERS=auto
export SYNTHFORGE_RATE_ESTIMATED_TOKENS=530
export SYNTHFORGE_RATE_MAX_WORKERS_CAP=512
python3 -m synthforge run examples/linkedin_profiles.sql --db demo.duckdb
```

The recommendation uses provider headers such as `x-ratelimit-limit-requests`, `x-ratelimit-limit-tokens`, renewal/reset periods, measured p95 latency, estimated tokens per call, and a safety factor. Use a representative `--sample-prompt`; a tiny `"ok"` probe will under-recommend workers for slow real prompts. Runtime AIMD also reads remaining request/token headers from LiteLLM responses, including nested `_hidden_params`, honors `retry-after`/`retry-after-ms`, cools down on reset headers, caps concurrency to remaining request budget when exposed, and reduces concurrency when 429/503 errors appear. If a provider does not expose useful rate headers, the probe returns a conservative fallback and you should set explicit `SYNTHFORGE_RATE_RPM` / `SYNTHFORGE_RATE_TPM`.

If your provider does not expose useful headers, set hard RPM/TPM caps and SynthForge will pace request starts before the call is made:

```bash
export SYNTHFORGE_RATE_RPM=600
export SYNTHFORGE_RATE_TPM=200000
export SYNTHFORGE_RATE_ESTIMATED_TOKENS=530
```

`SYNTHFORGE_RATE_ESTIMATED_TOKENS` is a conservative per-call floor used by the TPM window. The runtime also estimates prompt/input/output size and charges the larger value, so wide batched rows are throttled more carefully than narrow one-column summaries.

## Batching

Row summaries and LLM-generated fields are batched by prompt/schema to reduce request count. Control batch size with:

```bash
export SYNTHFORGE_SUMMARY_BATCH_SIZE=10
```

Use `1` to force one request per row. In Azure `gpt-5-nano` testing, `5-10` was faster than large batches like `25`; tune per provider/model.

`generate_rows(...)` streams completed batches into an internal DuckDB staging table and only moves rows into the target table after the run passes error policy. Completed async batches are inserted by source offset, not completion time, so previews and final tables keep deterministic generation order. This avoids holding the full generated dataset in Python memory and prevents `CREATE OR REPLACE` from destroying an existing target when generation fails.

CTAS enrichment with `generate(...)`, `generate_struct(...)`, or `generate_row_summary(...)` also resolves and writes final rows in chunks controlled by `SYNTHFORGE_BATCH_SIZE`. DuckDB still materializes a local staging table for the source query, but SynthForge no longer fetches and materializes the full final dataset in Python before writing the target. When DuckDB can count the CTAS source rows safely, SynthForge preflights `SYNTHFORGE_MAX_GENERATE_JOBS` before creating staging. Generated values must be projected as plain aliases; transform, deduplicate, group, sort, or qualify them in a second SQL statement after materialization. SynthForge rejects `DISTINCT`, `GROUP BY`, `ORDER BY`, `HAVING`, and `QUALIFY` in the same CTAS as deferred generation to avoid DuckDB evaluating those clauses against placeholders.

For `generate_struct(...)`, the declared schema is read from SQL as well as runtime placeholders, so zero-row microbatches still create the expected output columns. Because struct output is flattened into table columns, generated field names must not duplicate selected source columns.

`INSERT INTO existing_table SELECT * FROM generate_rows(...)` now preflights the target schema instead of silently altering it. If the target lacks `_gen_status`/`_gen_error`, either add those columns, use `CREATE OR REPLACE TABLE`, or set `SYNTHFORGE_INCLUDE_META=0` for clean-table inserts.

## Internal Tables

- `__synth_cache__`: prompt/input/model/schema cache for non-web-search generations, including `augment`, `augment-file`, SQL `generate(...)`, and `generate_rows(...)` LLM fields.
- `__synth_errors__`: per-row or per-batch failures.
- `__synth_jobs__`: queued/cache/ok/error job telemetry.
- `__synth_runs__`: one row per CLI/API/MCP engine run, with `run_id`, status, provider, model, statement count, and error message.
- `__synth_table_generations__`: sidecar lineage for generated user tables, including row count and failed-row count even when final tables omit `_gen_status`.
- `<db>.synth.jsonl`: append-only run telemetry for statements, jobs, batches, exact cache hits, dedupe hits, retries/fallbacks, token/cost usage, provider `cached_tokens` when exposed, and run finish events.

Generated output tables include `_gen_status` and `_gen_error` by default for BI auditability. Set `SYNTHFORGE_INCLUDE_META=0` to keep final user tables clean while still writing failures to `__synth_errors__`, run/job telemetry, and `__synth_table_generations__`. `synthforge validate` and `export-bundle` automatically mark generated tables as not BI-ready when `_gen_status` is missing/NULL/not `ok`, or when clean generated tables have failed rows in the sidecar metadata.

By default SynthForge allows partial completion but fails a statement when more than 10% of generated rows/jobs fail. Use `SYNTHFORGE_ALLOW_PARTIAL=0` for fail-fast production runs, or explicitly raise `SYNTHFORGE_MAX_ERROR_RATE` when you want dead-letter style outputs for review.

For intentional cache invalidation after changing prompt templates or governance rules, set:

```bash
export SYNTHFORGE_PROMPT_TEMPLATE_VERSION=pos-catalog-v2
```

That version is part of the cache key, so old generated values remain auditable but new runs generate fresh outputs.

OpenAI/Azure provider prompt caching is separate from SynthForge's exact output cache. SynthForge keeps static instructions/schema before row inputs so provider prefix caching can work. Optional controls:

```bash
export SYNTHFORGE_PROMPT_CACHE_KEY=pos-catalog-v1
export SYNTHFORGE_PROMPT_CACHE_RETENTION=24h        # provider/deployment dependent
export SYNTHFORGE_PROMPT_CACHE_PREFIX_MODE=extended # experimental; increases first-call input tokens
```

Use `extended` only after measuring `cached_tokens` in telemetry; cached tokens reduce provider cost/latency where supported but still count toward TPM/rate limits.

Useful operations:

```bash
python3 -m synthforge estimate examples/pos_product_enrichment.sql --db pos.duckdb
python3 -m synthforge runs --db pos.duckdb
python3 -m synthforge jobs --db pos.duckdb
python3 -m synthforge errors --db pos.duckdb
python3 -m synthforge cache stats --db pos.duckdb
python3 -m synthforge validate orders --db pos.duckdb --not-null customer_id --unique order_id --fk customer_id=customers.customer_id
python3 -m synthforge telemetry summary --db pos.duckdb
```

`estimate` statically counts `generate_rows(...)` volume and, when the referenced source tables already exist in the target DuckDB file, uses DuckDB itself to count `CREATE TABLE ... AS SELECT ..., generate(...)` / `generate_row_summary(...)` projection jobs. That gives agents and humans a practical request/token budget before running large enrichment scripts.

Use `--run-id <id>` on `jobs` and `errors` to inspect one execution.

JSONL telemetry is enabled by default for file-backed DuckDB databases. Disable it with `SYNTHFORGE_TELEMETRY=0`, or choose a custom path with `SYNTHFORGE_TELEMETRY_JSONL=/path/to/synthforge.jsonl`. Provider responses that include `usage`, `response_cost`, or `cached_tokens` metadata are recorded in job/batch events as `usage`.

## Validation

Use deterministic validation checks after generation or enrichment:

```bash
python3 -m synthforge validate orders --db retail.duckdb \
  --not-null customer_id \
  --unique order_id \
  --fk customer_id=customers.customer_id \
  --date-range order_date=2025-01-01..2026-12-31 \
  --allowed 'status=paid|open|cancelled'
```

The output is a table of `ok`/`fail` checks with failed row counts. Supported checks are `--not-null`, `--unique`, `--fk`, `--date-range`, and `--allowed`.

For product/POS feeds, add the domain preset:

```bash
python3 -m synthforge validate clean_products --db pos.duckdb --preset pos_products --format json
```

`pos_products` auto-detects columns such as `sku`, `product_name`, `category`, `price`, and `product_description`, then checks duplicate/missing SKUs, blank names/categories/descriptions, and non-positive prices.

## BI Export Bundle

Export a handoff directory for BI/QA instead of a naked table:

```bash
python3 -m synthforge export-bundle clean_products --db pos.duckdb --to out/clean_products \
  --data-format parquet \
  --preset pos_products \
  --not-null sku \
  --unique sku \
  --format json
```

The bundle contains the data file, `schema.json`, `validation.json`, `semantic_model.json`, `queries.sql`, `README.md`, and `manifest.json`. If you pass `--run-id`, it also includes run jobs/errors for audit.

## MCP Server

SynthForge has an optional stdio MCP server for Codex and Claude Code:

```bash
python3 -m pip install -e '.[mcp]'
python3 -m synthforge.mcp_server --db workspace.duckdb
```

Call `synthforge_init()` first when an agent needs the operating guide; it returns AGENTS.md-style instructions, safe config, SQL rules, tool purposes, and recommended examples. For simple file enrichment, use `synthforge_augment_file(...)` so the agent does not need to import, write SQL, and export manually. For simple table enrichment, use `synthforge_augment_table(...)`. Use `synthforge_sql(sql, max_rows=100, result_format="csv", mode="run")` when a full SQL workflow is needed. It runs full SynthForge SQL scripts and returns compact CSV output when the final statement is a query. Use `generate_struct(...)` for clean BI columns; SynthForge flattens those outputs into the declared columns, so follow-up SQL should select generated columns directly instead of `ai.field`. Use the same SQL tool with `mode="estimate"` before large generations to get minimum row/request/token budgets without executing the script. Narrow import/export tools move CSV/TSV/Parquet files and profile imported data without opening arbitrary file SQL; `synthforge_validate_table` and `synthforge_admin` cover CLI-style validation, doctor, runs/jobs/errors, cache, telemetry, init, and rate-limit probing. MCP resources/prompts expose built-in POS/BI recipes and SQL examples for agent discovery. For safety, the MCP server defaults to one configured DB, caps generated rows/jobs, blocks external file/extension SQL such as `COPY`, direct file scans, `sniff_csv(...)`, `*_scan(...)`, `ATTACH`, `INSTALL`, `LOAD`, `PRAGMA`, profiling output, and `read_*`, prevents direct SQL reads/writes of internal `__synth_*` tables, and prevents file tools from touching internal tables unless explicitly enabled; enable `SYNTHFORGE_MCP_UNSAFE_SQL=1` only for trusted local workflows. See `docs/MCP.md` for Codex and Claude Code configuration snippets.

## Contributing

Contributions are welcome through issues and pull requests. See [CONTRIBUTING.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/CONTRIBUTING.md) for local setup, testing expectations, and review flow.

See [ROADMAP.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/ROADMAP.md) for v0.2 priorities, good first issues, and explicit non-goals.

See [docs/USE_CASES.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/docs/USE_CASES.md) for POS catalog enrichment and other practical patterns.

## Security

Please report suspected vulnerabilities privately when possible. See [SECURITY.md](https://github.com/Superpapotas/SynthForge/blob/HEAD/SECURITY.md) for supported versions and reporting guidance.

## License

SynthForge is licensed under the Apache License 2.0. See [LICENSE](https://github.com/Superpapotas/SynthForge/tree/HEAD/LICENSE).

SynthForge

Create and augment BI/POS datasets with LLMs from one local CLI.

SynthForge is a simple data augmentation tool: point it at a CSV, XLSX workbook, Parquet file, or DuckDB table; declare the new columns you want; write one prompt; get an enriched output file or table. The engine handles prompts, JSON schemas, batching, caching, retries, metadata, validation, and Azure OpenAI setup.

No notebooks. No custom parser required for normal use. No Python fixture factory.

Start Here

New to SynthForge? Start with the full user guide:

Complete User Guide: end-to-end guide for installation, Azure GPT-5 Nano, file/table augmentation, POS workflows, web search, validation, exports, MCP, SQL advanced mode, and troubleshooting.
CLI quick reference: short command-focused reference.
Provider setup: Azure/OpenAI/LiteLLM, web search, Tavily, workers, rate limits, and retries.
MCP guide: how Codex and Claude Code should operate SynthForge through tools.

Why SynthForge

Versus Faker: keeps simple local generation, but adds LLM semantic fields, file/table augmentation, caching, validation, and MCP.
Versus SDV-style modeling: focuses on fast BI/demo/test marts, not statistical reproduction of sensitive production data.
Versus ad-hoc LLM scripts: keeps prompts in auditable runs and lets deterministic logic own IDs, FKs, dates, samples, ranges, and financial math.

Project Status

SynthForge is an early 0.1.x project. The default provider is fake, so tests and offline demos run without API keys. The best-tested real provider path is Azure OpenAI gpt-5-nano, with optional Responses web search for current public facts.

Current limitations:

SQL generation is advanced mode. For normal work, use augment-file or augment.
generate(), generate_struct(), and generate_row_summary() are supported in CREATE TABLE ... AS SELECT projections. Materialize generated columns first, then filter/join/deduplicate/sort in a second SQL statement.
MCP safe mode is a local guardrail, not a security sandbox. Only enable unsafe SQL for trusted local workflows.
SynthForge generates synthetic/demo data; it does not provide differential privacy, anonymization guarantees, or compliance certification.

SQL compatibility quick table:

Need	Use	Rule
File/table enrichment	`augment-file` / `augment`	No custom SQL required.
Normal relational work	DuckDB SQL	Use standard DuckDB syntax.
Generated BI columns	`generate_struct(...)`	Put it in a CTAS projection; SynthForge flattens declared columns.
Text summary column	`generate(...)` or `generate_row_summary(...)`	Materialize first, then filter/group/sort later.
Agent-safe file movement	MCP file tools	Avoid raw `read_*`, `COPY`, and `PRAGMA` unless unsafe mode is explicitly enabled.

Requirements

Python 3.11 or newer.
DuckDB is installed from the Python package dependencies.
Optional: Azure OpenAI, Gemini API, Codex CLI, or another LiteLLM-backed provider for real LLM generation. Azure gpt-5-nano remains the focused v0.1 API path.

Install

git clone https://github.com/Superpapotas/synthforge
cd synthforge
python3 -m pip install -e .

Optional real LLM support:

python3 -m pip install -e '.[llm]'

The default provider is fake, so examples run offline.

Development install:

python3 -m pip install -e '.[dev]'

Quickstart

Print the built-in operating guide:

python3 -m synthforge guide

Start offline first. The default fake provider proves file handling, schemas, caching, and audit columns without an API key.

No-SQL file augmentation:

cat > products.csv <<'CSV'
sku,product_name,brand,category
SKU1,Cold Brew Coffee,Blue Bottle,Grocery
CSV

python3 -m synthforge augment-file products.csv \
  --out products_enriched.csv \
  --columns "description VARCHAR, category_clean VARCHAR" \
  --prompt "Generate POS-ready product catalog fields from this row" \
  --context "sku,product_name,brand,category" \
  --db demo.duckdb

Current-price lookup with web search:

python3 -m synthforge augment-file products.csv \
  --out products_prices.csv \
  --columns "current_price_usd DOUBLE, retailer_or_source VARCHAR, source_domain VARCHAR, source_url VARCHAR, price_note VARCHAR" \
  --prompt "Use web search to find a current US retail price for this product. Match package size when possible." \
  --context "sku,product_name,brand,category" \
  --web-search \
  --db demo.duckdb

This also works with .xlsx, .tsv, and .parquet. See docs/CLI.md, docs/FILES.md, and docs/AUGMENT.md.

Configure Azure OpenAI GPT-5 Nano when you want real output quality or web search:

export SYNTHFORGE_API_KEY=YOUR_AZURE_OPENAI_KEY

python3 -m synthforge configure-azure \
  --endpoint https://YOUR-RESOURCE.openai.azure.com \
  --model gpt-5-nano \
  --web-search

python3 -m synthforge doctor

This writes a local .env file and configures Azure chat completions plus Azure Responses web search at https://YOUR-RESOURCE.openai.azure.com/openai/v1. Azure web search availability, quota, cost, and compliance behavior can vary by subscription and region. See docs/PROVIDERS.md before running large current-fact jobs.

For web-search price lookup, test a small slice first with --limit 5, then scale up once sources look right.

Gemini native SDK with Google Search grounding:

export SYNTHFORGE_PROVIDER=gemini
export SYNTHFORGE_MODEL=gemini-3.1-flash-lite
export GEMINI_API_KEY=YOUR_GEMINI_API_KEY
export SYNTHFORGE_WEB_SEARCH=1

Codex CLI provider for local/personal workflows:

export SYNTHFORGE_PROVIDER=codex_cli
export SYNTHFORGE_MODEL=gpt-5.4-mini
export SYNTHFORGE_CODEX_REASONING_EFFORT=low
export SYNTHFORGE_CODEX_SEARCH=1

No-SQL table augmentation:

python3 -m synthforge query "CREATE OR REPLACE TABLE products AS SELECT * FROM (VALUES ('SKU1', 'Apple iPhone case', 'Apple')) AS t(sku, product_name, brand)" --db demo.duckdb
python3 -m synthforge augment products \
  --into products_enriched \
  --columns "description VARCHAR, category VARCHAR" \
  --prompt "Generate catalog fields from the product row" \
  --context "sku,product_name,brand" \
  --db demo.duckdb
python3 -m synthforge query "SELECT sku, description, category, _gen_status FROM products_enriched" --db demo.duckdb

For periodic jobs, use --mode upsert --key sku --where "...". See docs/AUGMENT.md.

SQL workflow:

python3 -m synthforge init --db demo.duckdb
python3 -m synthforge run examples/demo.sql --db demo.duckdb
python3 -m synthforge query "SELECT * FROM enriched_tickets" --db demo.duckdb

query prints compact CSV by default; use --format json for structured rows. run --show-final prints the final query result when a script ends in SELECT/WITH/SHOW.

Packaged demos work from source checkouts and installed wheels:

python3 -m synthforge demo --list
python3 -m synthforge demo pos_product_enrichment --db pos.duckdb
python3 -m synthforge demo bi_retail_mart --db retail.duckdb --no-show-final

60-Second Demo

python3 -m synthforge run examples/linkedin_profiles.sql --db demo.duckdb
python3 -m synthforge query "SELECT user_id, full_name, job_title, LEFT(linkedin_description, 80) AS preview FROM mock_users_with_linkedin LIMIT 3" --db demo.duckdb

Example output with the offline fake provider:

user_id,full_name,job_title,preview
1,Allison Hill,Health physicist,Summary: user_id=1; full_name=Allison Hill; email=donaldgarcia@example.net; addr
2,Michael Thomas,TEFL teacher,Summary: user_id=2; full_name=Michael Thomas; email=swood@example.org; address=0
3,Joshua Atkins,Clinical cytogeneticist,Summary: user_id=3; full_name=Joshua Atkins; email=noahroberts@example.org; addr

Full BI mart demo:

python3 -m synthforge run examples/bi_retail_mart.sql --db retail.duckdb
python3 -m synthforge query "SELECT sales_channel, order_status, COUNT(*) FROM orders GROUP BY 1,2" --db retail.duckdb

SaaS subscription/churn mart:

python3 -m synthforge run examples/saas_subscription_mart.sql --db saas.duckdb --show-final

This builds accounts, subscriptions, usage_events, support_tickets, and account_health with valid FKs, MRR/adoption metrics, churn-risk bands, expansion signals, and row-level account health summaries.

POS product enrichment demos:

If you need...	Run	Main output
Simple product copy/category fields	`pos_product_enrichment`	`enriched_new_products`
New SKU intake	`pos_new_product_intake`	`product_catalog_ai`
Changed-product queue/idempotency	`pos_product_enrichment_queue`	`product_enrichment_queue`, `product_catalog_ai`
Watermark microbatch lifecycle	`pos_product_lifecycle_microbatch`	`product_catalog`, `product_catalog_ai`
Operator control tower	`pos_catalog_control_tower`	`pos_control_tower`
SKU/UPC identity resolution	`pos_identity_resolution`	`product_identity_recommendations`
Supplier taxonomy mapping	`pos_taxonomy_mapping`	`taxonomy_mapping_review`
Price/promo/margin QA	`pos_price_promo_margin_qa`	`pricing_policy_review`
Current web price lookup	`pos_web_price_lookup_40`	`pos_web_products_price_enriched`

python3 -m synthforge run examples/pos_product_enrichment.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_new_product_intake.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_catalog_control_tower.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_product_lifecycle_microbatch.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_product_enrichment_queue.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_identity_resolution.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_taxonomy_mapping.sql --db pos.duckdb --show-final
python3 -m synthforge run examples/pos_price_promo_margin_qa.sql --db pos.duckdb --show-final

This models incremental retail/POS feeds: process only events after a watermark, detect new SKUs or changed product hashes, dedupe noisy product events, resolve SKU/UPC identity, govern supplier taxonomy, QA price/promo/margin changes, update deterministic price/discontinue changes in SQL, generate listing/receipt/search/compliance fields with generate_struct(...), version content, route risky products to a steward queue, insert good rows into product_catalog/product_catalog_ai, and audit _gen_status failures.

Run Tests

python3 -m pip install -e '.[dev]'
python3 -m compileall synthforge
python3 -m pytest -q

The test suite uses the offline fake provider by default. Do not commit API keys, generated DuckDB databases, or local benchmark outputs.

SQL Examples

Scalar enrichment:

CREATE TABLE enriched_tickets AS
SELECT
  id,
  ticket_text,
  generate('Clasifica la intención en: compra, queja, devolución', ticket_text) AS intent
FROM support_tickets;

generate() can accept multiple context columns: generate('classify this ticket', title, body, original_category).

Multi-column extraction:

CREATE TABLE enriched_products AS
SELECT
  product_name,
  generate_struct(
    'Extrae marca, categoría y precio aproximado en USD',
    product_name,
    'brand VARCHAR, category VARCHAR, est_price DOUBLE'
  ) AS ai
FROM products;

Row-level summary using every selected column:

CREATE TABLE calendar_with_summary AS
SELECT
  event_id,
  event_name,
  event_date,
  owner_team,
  region,
  priority,
  generate_row_summary(
    'Write a concise BI summary using every selected calendar column.'
  ) AS summary
FROM calendar_events;

generate_row_summary() is SynthForge-native SQL. DuckDB first materializes the selected row context, then SynthForge sends the full row as JSON to the LLM and writes the generated summary back into the final table.

Synthetic rows:

INSERT INTO customers
SELECT * FROM generate_rows(
  prompt := 'Clientes B2B mexicanos del sector de manufactura',
  rows := 1000,
  schema := 'id INTEGER SEQUENCE, name VARCHAR FAKER name, email VARCHAR FAKER email, country VARCHAR FAKER country'
);

generate_rows(...) works in INSERT INTO ... SELECT * FROM generate_rows(...), CREATE TABLE ... AS SELECT * FROM generate_rows(...), projected CTAS/INSERT forms such as CREATE TABLE ids AS SELECT id FROM generate_rows(...), and preview queries such as SELECT * FROM generate_rows(...) LIMIT n or WITH preview AS (...) SELECT ... FROM preview WHERE .... Simple projection previews like SELECT id FROM generate_rows(...) LIMIT 5 push the limit into the generation budget and preserve batch insertion order for stable previews. Simple quoted table targets like "customer rows" are supported.

CTAS enrichment also supports CTE-shaped BI SQL:

CREATE TABLE enriched AS
WITH source_rows AS (
  SELECT * FROM imported_products WHERE active = true
)
SELECT
  sku,
  generate('Write catalog copy for this SKU', product_name, brand, category) AS product_description
FROM source_rows;

Structured generators:

INSERT INTO customers
SELECT * FROM generate_rows(
  prompt := 'B2B customer accounts; only summary needs LLM reasoning',
  rows := 100,
  schema := '
    customer_id INTEGER SEQUENCE,
    company_name VARCHAR FAKER company,
    email VARCHAR FAKER email,
    industry VARCHAR SAMPLE manufacturing|software|retail,
    annual_revenue DOUBLE RANGE 1000000..100000000,
    summary VARCHAR LLM
  '
);

Supported field directives:

NULL / NOT NULL: optional SQL nullability after the type, for example id INTEGER NOT NULL SEQUENCE.
SEQUENCE: deterministic 1..N IDs across batches; INSERT INTO existing tables offsets from the current max value to avoid duplicate keys on repeated appends.
FAKER provider: uses Python Faker providers like company, name, email, country, city, address.
SAMPLE a|b|c: samples from fixed literals.
SAMPLE 'a, b, c': quoted comma syntax for fixed literals.
RANGE min..max: numeric range.
WEIGHTED_SAMPLE value:weight|...: weighted categorical sampling.
DATE_RANGE yyyy-mm-dd..yyyy-mm-dd: date generation inside inclusive bounds.
FK table.column: sample valid foreign keys from an existing table column.
LLM: generate this field with the configured model.

If no directive is provided, obvious deterministic fields such as id, *_id, email, name, country, and dates are generated locally; other fields default to LLM.

See examples/calendar_summary.sql for the ventasPipeline-style calendar pattern: structured calendar rows plus a summary per row from all selected columns.

See examples/bi_retail_mart.sql for a full five-table synthetic BI mart with customers, products, orders, order_items, and support_tickets.

See examples/saas_subscription_mart.sql for a B2B SaaS subscription mart with MRR, adoption, support tickets, churn risk, expansion signals, and generated account health summaries.

See examples/pos_new_product_intake.sql for a production-style POS intake flow: dedupe incoming product events, enrich only unknown SKUs, append to a catalog, and audit the run with SQL.

See examples/pos_catalog_control_tower.sql for a more operational catalog-control flow: latest event per SKU, price-only updates without LLM calls, semantic refreshes, compliance/data-quality review tasks, and generate_row_summary(...) for BI/operator summaries.

See examples/pos_product_lifecycle_microbatch.sql for a stateful scheduled POS lifecycle microbatch: persisted watermark filtering, latest-event dedupe, price/discontinue actions without LLM calls, listing content generation, persistent content versioning, steward tasks, and a BI summary table.

See examples/pos_product_enrichment_queue.sql for the backend queue pattern behind continuous product onboarding: latest product snapshots, input_hash idempotency, queued microbatches, generated catalog content, queue status updates, and reruns that do not spend tokens for unchanged SKUs.

See examples/pos_identity_resolution.sql for SKU/UPC dedupe: SQL creates normalized keys, exact UPC auto-merges, ambiguous brand/family/unit candidates use generate_struct(...) for merge recommendation and rationale, and the output includes merge/review queues plus canonical identity clusters.

See examples/pos_taxonomy_mapping.sql for category governance: SQL applies known supplier alias rules, unmatched terms use generate_struct(...) for taxonomy suggestions and reasons, and regulated/low-confidence mappings route to a review queue.

See examples/pos_price_promo_margin_qa.sql for price and promotion QA: SQL calculates margin floors, below-cost blocks, promo overlap, unknown SKUs, and age-restricted review gates, while generate_struct(...) writes merchant notes, customer-safe messages, risk summaries, recommendations, and steward queues.

Practical referential integrity:

INSERT INTO orders
SELECT * FROM generate_rows(
  prompt := 'Órdenes de compra realistas',
  rows := 5000,
  schema := 'order_id INTEGER SEQUENCE, customer_id INTEGER FK customers.id, total DOUBLE RANGE 25..2500'
);

Providers

Offline fake provider:

export SYNTHFORGE_PROVIDER=fake

LiteLLM/OpenAI-compatible provider:

export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=openrouter/meta-llama/llama-3.1-8b-instruct
export SYNTHFORGE_API_KEY=...

SYNTHFORGE_PROVIDER=litellm is the portable default for real LLMs. Provider aliases such as openrouter, openai, azure, anthropic, gemini, vertex_ai, xai, nvidia, huggingface, novita, vercel_ai_gateway, ollama, or local also route through the same LiteLLM runtime; python3 -m synthforge doctor --json reports this as "provider_runtime": "litellm". If you use an alias and provide an unprefixed model, SynthForge normalizes common LiteLLM model names for you, e.g. SYNTHFORGE_PROVIDER=azure + SYNTHFORGE_MODEL=gpt-5-nano becomes azure/gpt-5-nano, SYNTHFORGE_PROVIDER=ollama + SYNTHFORGE_MODEL=llama3.1 becomes ollama/llama3.1, and SYNTHFORGE_PROVIDER=vertex_ai + SYNTHFORGE_MODEL=gemini-1.5-pro becomes vertex_ai/gemini-1.5-pro.

Structured JSON outputs are enabled by default through LiteLLM response_format=json_schema when the provider supports it. Disable with SYNTHFORGE_STRUCTURED_OUTPUTS=0 if a provider rejects native structured outputs.

See docs/PROVIDERS.md for OpenRouter, OpenAI, Azure OpenAI, Anthropic, Gemini, Ollama, and reliability controls.

Ollama through LiteLLM:

export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=ollama/llama3.1
export SYNTHFORGE_BASE_URL=http://localhost:11434

Web Search

Web search is opt-in because it adds external network calls, cost, and reproducibility risk. When web search is enabled, SynthForge bypasses the normal generation cache so current-fact outputs are not reused from stale search results; a dedicated search cache/TTL is future scope.

Native Responses API web search, using the current GA web_search tool only:

export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=responses
export SYNTHFORGE_RESPONSES_API_KEY=...   # or OPENAI_API_KEY
export SYNTHFORGE_RESPONSES_MODEL=gpt-4.1-mini

For Azure OpenAI Responses, point the Responses adapter at the Azure /openai/v1 endpoint and pass the Azure key explicitly:

export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=responses
export SYNTHFORGE_RESPONSES_BASE_URL=https://YOUR-RESOURCE.openai.azure.com/openai/v1
export SYNTHFORGE_RESPONSES_API_KEY=...
export SYNTHFORGE_RESPONSES_MODEL=gpt-5-nano

Tavily search with any SynthForge LLM provider:

export SYNTHFORGE_WEB_SEARCH=1
export SYNTHFORGE_WEB_SEARCH_PROVIDER=tavily
export SYNTHFORGE_TAVILY_API_KEY=...
export SYNTHFORGE_WEB_SEARCH_GRANULARITY=row  # row|batch; row avoids cross-product context bleed
export SYNTHFORGE_TAVILY_RATE_RPM=60          # optional; defaults to SYNTHFORGE_RATE_RPM when set
export SYNTHFORGE_PROVIDER=litellm
export SYNTHFORGE_MODEL=openrouter/meta-llama/llama-3.1-8b-instruct
export SYNTHFORGE_API_KEY=...

responses delegates search to the Responses API tool path and intentionally uses SYNTHFORGE_RESPONSES_API_KEY/OPENAI_API_KEY plus SYNTHFORGE_RESPONSES_BASE_URL, not the main LiteLLM credentials. OpenAI URLs use Authorization: Bearer; Azure OpenAI URLs use api-key. The provider forces tool_choice=required and max_tool_calls=1 so SQL runs that enable search actually perform one hosted web-search call per LLM request. Legacy provider aliases, legacy env aliases, and the older preview search path are intentionally not supported; set SYNTHFORGE_WEB_SEARCH_PROVIDER=responses for native Responses search. tavily searches externally, appends search snippets and URLs to the row context, and then calls the normal SynthForge LLM provider, so it works with OpenRouter, Azure, Anthropic, Gemini, Ollama, or any LiteLLM-compatible model. Tavily defaults to SYNTHFORGE_WEB_SEARCH_GRANULARITY=row so a batched POS/product run searches each row independently; use batch only when the batch shares one topic and Tavily quota is more important than per-row precision. Tavily search calls have a separate RPM cap through SYNTHFORGE_TAVILY_RATE_RPM; if unset, SynthForge reuses SYNTHFORGE_RATE_RPM for search. Search result caching is future scope.

Rate Limit Probing

SynthForge can probe provider rate-limit headers and recommend a safe worker count. Azure OpenAI uses a direct deployment probe when endpoint, key, and API version are configured; other LiteLLM-backed providers use a minimal structured generation call and whatever headers the provider exposes.

export SYNTHFORGE_PROVIDER=azure
export SYNTHFORGE_MODEL=gpt-5-nano
export SYNTHFORGE_BASE_URL=https://your-resource.openai.azure.com
export SYNTHFORGE_API_KEY=...
export SYNTHFORGE_API_VERSION=2025-04-01-preview

python3 -m synthforge probe-limits \
  --context-columns 4 \
  --latency-samples 3 \
  --sample-prompt "Write a 1-2 sentence LinkedIn style profile from a JSON row."

--context-columns estimates a narrower row-level prompt instead of assuming every call costs 1000 tokens. Override with --estimated-tokens-per-call when you already know the real cost. The worker recommendation is capped at 512 by default; lower it with --max-workers-cap if your provider or machine cannot handle that many in-flight requests.

Azure gpt-5-* deployments may reject explicit temperature values. The Azure probe intentionally omits temperature; runtime generation also retries without optional parameters if the deployment rejects them.

To let SynthForge probe automatically at runtime:

export SYNTHFORGE_MAX_WORKERS=auto
export SYNTHFORGE_RATE_ESTIMATED_TOKENS=530
export SYNTHFORGE_RATE_MAX_WORKERS_CAP=512
python3 -m synthforge run examples/linkedin_profiles.sql --db demo.duckdb

The recommendation uses provider headers such as x-ratelimit-limit-requests, x-ratelimit-limit-tokens, renewal/reset periods, measured p95 latency, estimated tokens per call, and a safety factor. Use a representative --sample-prompt; a tiny "ok" probe will under-recommend workers for slow real prompts. Runtime AIMD also reads remaining request/token headers from LiteLLM responses, including nested _hidden_params, honors retry-after/retry-after-ms, cools down on reset headers, caps concurrency to remaining request budget when exposed, and reduces concurrency when 429/503 errors appear. If a provider does not expose useful rate headers, the probe returns a conservative fallback and you should set explicit SYNTHFORGE_RATE_RPM / SYNTHFORGE_RATE_TPM.

If your provider does not expose useful headers, set hard RPM/TPM caps and SynthForge will pace request starts before the call is made:

export SYNTHFORGE_RATE_RPM=600
export SYNTHFORGE_RATE_TPM=200000
export SYNTHFORGE_RATE_ESTIMATED_TOKENS=530

SYNTHFORGE_RATE_ESTIMATED_TOKENS is a conservative per-call floor used by the TPM window. The runtime also estimates prompt/input/output size and charges the larger value, so wide batched rows are throttled more carefully than narrow one-column summaries.

Batching

Row summaries and LLM-generated fields are batched by prompt/schema to reduce request count. Control batch size with:

export SYNTHFORGE_SUMMARY_BATCH_SIZE=10

Use 1 to force one request per row. In Azure gpt-5-nano testing, 5-10 was faster than large batches like 25; tune per provider/model.

generate_rows(...) streams completed batches into an internal DuckDB staging table and only moves rows into the target table after the run passes error policy. Completed async batches are inserted by source offset, not completion time, so previews and final tables keep deterministic generation order. This avoids holding the full generated dataset in Python memory and prevents CREATE OR REPLACE from destroying an existing target when generation fails.

CTAS enrichment with generate(...), generate_struct(...), or generate_row_summary(...) also resolves and writes final rows in chunks controlled by SYNTHFORGE_BATCH_SIZE. DuckDB still materializes a local staging table for the source query, but SynthForge no longer fetches and materializes the full final dataset in Python before writing the target. When DuckDB can count the CTAS source rows safely, SynthForge preflights SYNTHFORGE_MAX_GENERATE_JOBS before creating staging. Generated values must be projected as plain aliases; transform, deduplicate, group, sort, or qualify them in a second SQL statement after materialization. SynthForge rejects DISTINCT, GROUP BY, ORDER BY, HAVING, and QUALIFY in the same CTAS as deferred generation to avoid DuckDB evaluating those clauses against placeholders.

For generate_struct(...), the declared schema is read from SQL as well as runtime placeholders, so zero-row microbatches still create the expected output columns. Because struct output is flattened into table columns, generated field names must not duplicate selected source columns.

INSERT INTO existing_table SELECT * FROM generate_rows(...) now preflights the target schema instead of silently altering it. If the target lacks _gen_status/_gen_error, either add those columns, use CREATE OR REPLACE TABLE, or set SYNTHFORGE_INCLUDE_META=0 for clean-table inserts.

Internal Tables

__synth_cache__: prompt/input/model/schema cache for non-web-search generations, including augment, augment-file, SQL generate(...), and generate_rows(...) LLM fields.
__synth_errors__: per-row or per-batch failures.
__synth_jobs__: queued/cache/ok/error job telemetry.
__synth_runs__: one row per CLI/API/MCP engine run, with run_id, status, provider, model, statement count, and error message.
__synth_table_generations__: sidecar lineage for generated user tables, including row count and failed-row count even when final tables omit _gen_status.
<db>.synth.jsonl: append-only run telemetry for statements, jobs, batches, exact cache hits, dedupe hits, retries/fallbacks, token/cost usage, provider cached_tokens when exposed, and run finish events.

Generated output tables include _gen_status and _gen_error by default for BI auditability. Set SYNTHFORGE_INCLUDE_META=0 to keep final user tables clean while still writing failures to __synth_errors__, run/job telemetry, and __synth_table_generations__. synthforge validate and export-bundle automatically mark generated tables as not BI-ready when _gen_status is missing/NULL/not ok, or when clean generated tables have failed rows in the sidecar metadata.

By default SynthForge allows partial completion but fails a statement when more than 10% of generated rows/jobs fail. Use SYNTHFORGE_ALLOW_PARTIAL=0 for fail-fast production runs, or explicitly raise SYNTHFORGE_MAX_ERROR_RATE when you want dead-letter style outputs for review.

For intentional cache invalidation after changing prompt templates or governance rules, set:

export SYNTHFORGE_PROMPT_TEMPLATE_VERSION=pos-catalog-v2

That version is part of the cache key, so old generated values remain auditable but new runs generate fresh outputs.

OpenAI/Azure provider prompt caching is separate from SynthForge's exact output cache. SynthForge keeps static instructions/schema before row inputs so provider prefix caching can work. Optional controls:

export SYNTHFORGE_PROMPT_CACHE_KEY=pos-catalog-v1
export SYNTHFORGE_PROMPT_CACHE_RETENTION=24h        # provider/deployment dependent
export SYNTHFORGE_PROMPT_CACHE_PREFIX_MODE=extended # experimental; increases first-call input tokens

Use extended only after measuring cached_tokens in telemetry; cached tokens reduce provider cost/latency where supported but still count toward TPM/rate limits.

Useful operations:

python3 -m synthforge estimate examples/pos_product_enrichment.sql --db pos.duckdb
python3 -m synthforge runs --db pos.duckdb
python3 -m synthforge jobs --db pos.duckdb
python3 -m synthforge errors --db pos.duckdb
python3 -m synthforge cache stats --db pos.duckdb
python3 -m synthforge validate orders --db pos.duckdb --not-null customer_id --unique order_id --fk customer_id=customers.customer_id
python3 -m synthforge telemetry summary --db pos.duckdb

estimate statically counts generate_rows(...) volume and, when the referenced source tables already exist in the target DuckDB file, uses DuckDB itself to count CREATE TABLE ... AS SELECT ..., generate(...) / generate_row_summary(...) projection jobs. That gives agents and humans a practical request/token budget before running large enrichment scripts.

Use --run-id <id> on jobs and errors to inspect one execution.

JSONL telemetry is enabled by default for file-backed DuckDB databases. Disable it with SYNTHFORGE_TELEMETRY=0, or choose a custom path with SYNTHFORGE_TELEMETRY_JSONL=/path/to/synthforge.jsonl. Provider responses that include usage, response_cost, or cached_tokens metadata are recorded in job/batch events as usage.

Validation

Use deterministic validation checks after generation or enrichment:

python3 -m synthforge validate orders --db retail.duckdb \
  --not-null customer_id \
  --unique order_id \
  --fk customer_id=customers.customer_id \
  --date-range order_date=2025-01-01..2026-12-31 \
  --allowed 'status=paid|open|cancelled'

The output is a table of ok/fail checks with failed row counts. Supported checks are --not-null, --unique, --fk, --date-range, and --allowed.

For product/POS feeds, add the domain preset:

python3 -m synthforge validate clean_products --db pos.duckdb --preset pos_products --format json

pos_products auto-detects columns such as sku, product_name, category, price, and product_description, then checks duplicate/missing SKUs, blank names/categories/descriptions, and non-positive prices.

BI Export Bundle

Export a handoff directory for BI/QA instead of a naked table:

python3 -m synthforge export-bundle clean_products --db pos.duckdb --to out/clean_products \
  --data-format parquet \
  --preset pos_products \
  --not-null sku \
  --unique sku \
  --format json

The bundle contains the data file, schema.json, validation.json, semantic_model.json, queries.sql, README.md, and manifest.json. If you pass --run-id, it also includes run jobs/errors for audit.

MCP Server

SynthForge has an optional stdio MCP server for Codex and Claude Code:

python3 -m pip install -e '.[mcp]'
python3 -m synthforge.mcp_server --db workspace.duckdb

Call synthforge_init() first when an agent needs the operating guide; it returns AGENTS.md-style instructions, safe config, SQL rules, tool purposes, and recommended examples. For simple file enrichment, use synthforge_augment_file(...) so the agent does not need to import, write SQL, and export manually. For simple table enrichment, use synthforge_augment_table(...). Use synthforge_sql(sql, max_rows=100, result_format="csv", mode="run") when a full SQL workflow is needed. It runs full SynthForge SQL scripts and returns compact CSV output when the final statement is a query. Use generate_struct(...) for clean BI columns; SynthForge flattens those outputs into the declared columns, so follow-up SQL should select generated columns directly instead of ai.field. Use the same SQL tool with mode="estimate" before large generations to get minimum row/request/token budgets without executing the script. Narrow import/export tools move CSV/TSV/Parquet files and profile imported data without opening arbitrary file SQL; synthforge_validate_table and synthforge_admin cover CLI-style validation, doctor, runs/jobs/errors, cache, telemetry, init, and rate-limit probing. MCP resources/prompts expose built-in POS/BI recipes and SQL examples for agent discovery. For safety, the MCP server defaults to one configured DB, caps generated rows/jobs, blocks external file/extension SQL such as COPY, direct file scans, sniff_csv(...), *_scan(...), ATTACH, INSTALL, LOAD, PRAGMA, profiling output, and read_*, prevents direct SQL reads/writes of internal __synth_* tables, and prevents file tools from touching internal tables unless explicitly enabled; enable SYNTHFORGE_MCP_UNSAFE_SQL=1 only for trusted local workflows. See docs/MCP.md for Codex and Claude Code configuration snippets.

Contributing

Contributions are welcome through issues and pull requests. See CONTRIBUTING.md for local setup, testing expectations, and review flow.

See ROADMAP.md for v0.2 priorities, good first issues, and explicit non-goals.

See docs/USE_CASES.md for POS catalog enrichment and other practical patterns.

Security

Please report suspected vulnerabilities privately when possible. See SECURITY.md for supported versions and reporting guidance.

License

SynthForge is licensed under the Apache License 2.0. See LICENSE.