--- slug: "superpapotas-synthforge" source_type: "readme" source_url: "https://cdn.jsdelivr.net/gh/Superpapotas/SynthForge@main/README.md" repo: "https://github.com/Superpapotas/SynthForge" source_file: "README.md" branch: "main" --- # 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`. - `.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 ` 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).