Generated Relational Data Source

For a step-by-step explanation of how the rows are constructed, why the distributions and planted cases exist, and how hashes and database verification work, read the synthetic data generation walkthrough. It includes Mermaid diagrams, a worked smoke-profile example, and reproducibility exercises. This page focuses on operation, configuration, and recorded performance evidence.

The recommendations.synthetic package provides a deterministic source for correctness and scale experiments without importing production records. One GenerationConfig identifies a Commerce Scope, profile, root seed, cutoff, and optional source capabilities. Calling generate_relational_data_source returns a regenerable logical source; each table can be iterated again without storing the generated Interaction Dataset in the recommendation service.

The fixed profile quotas are:

Profile	Catalog	Views	Purchases	History	Categories
smoke	1,000	25,000	10,000	90 days	20
development	25,000	100,000,000	20,000,000	365 days	43
qualification	200,000	100,000,000	5,000,000	730 days	43

The generated logical source always exposes catalog, views, online_purchases, offline_purchases, and compatibility_rules. Catalog, interaction, and rule rows follow the canonical source ordering and use generated source_sequence values where the logical table requires a final tie-breaker. Online and offline Order IDs are separate namespaces even when their source IDs intentionally collide.

Generation produces a GenerationManifest with the resolved profile, named RNG substreams, cohort parameters, planted signals, per-table counts, canonical logical SHA3-512 digests, and a content-derived dataset_id. The independent OracleManifest contains bounded observations; neither document contains raw interaction rows. write_artifacts writes these documents as generation-manifest.json and oracle-manifest.json.

The default materializer is SQLite:

recommendations-generate-source \
  --profile smoke \
  --output-dir artifacts/generated-smoke

The CLI accepts the smoke and development profiles. To materialize directly into PostgreSQL, install the postgresql extra, store the SQLAlchemy URL in an environment variable, and pass only that variable's name:

recommendations-generate-source \
  --profile development \
  --database-url-env GENERATED_SOURCE_DATABASE_URL \
  --workers 4 \
  --output-dir artifacts/generated-development

--database-url-env selects PostgreSQL; without it, the CLI writes SQLite. The URL must use the postgresql+psycopg driver. Both paths accept --batch-size (default 1,000, positive), --seed, --cutoff, the three Commerce Scope identifiers, --no-offline-purchases, and --no-compatibility. Both also accept positive --max-cart-items and --max-view-pages-per-session (alias --max-view-pages) limits on physical purchase and view rows per Order or Browsing Session. These custom limits are recorded in the Generation Manifest; they cannot be used with --append. PostgreSQL additionally uses --workers (default 4, range 1–16). Neither path replaces existing source tables unless --replace is supplied. PostgreSQL also supports --append to extend existing history as described below. --output-dir is required; PostgreSQL writes three JSON evidence files there, while SQLite also writes generated-source.sqlite. CLI generation does not expose the qualification profile. When omitted, --cutoff resolves to midnight UTC on the current date for a new source. The manifest records the resolved value; supply that exact timestamp to reproduce the dataset later. Append continues to require an explicit cutoff.

Docker PostgreSQL for generated data

compose.generated-source.yaml provides a dedicated PostgreSQL database and a one-shot generator. It is separate from the mutable simulation-db used by the Commerce Simulation Harness. Create a gitignored .env file in the repository root containing GENERATED_SOURCE_POSTGRES_PASSWORD=<your local password>, then run:

mkdir -p artifacts/generated-development
docker compose -f compose.generated-source.yaml up -d generated-source-db
docker compose -f compose.generated-source.yaml run --build --rm generator

The generator uses the development profile and four workers by default. Set GENERATED_SOURCE_WORKERS in .env to change the worker count (1–16). The command replaces only the five generated-source tables in this dedicated database when rerun. The manifest and receipt appear under artifacts/generated-development; PostgreSQL data persists in the recommendations-generated-source_generated-source-db-data Docker volume. The database is exposed only on 127.0.0.1:5434 by default; set GENERATED_SOURCE_POSTGRES_PORT in .env to change the host port.

To inspect the generated rows or stop the database:

docker compose -f compose.generated-source.yaml exec generated-source-db \
  psql -U generated_source -d generated_source -c 'SELECT COUNT(*) FROM views;'
docker compose -f compose.generated-source.yaml down

down preserves the database volume. Add --volumes only when deliberately discarding the generated database. The container reads its password through PGPASSWORD; the connection URL passed to the generator contains no credential.

The PostgreSQL path creates catalog, views, online_purchases, offline_purchases, and compatibility_rules in the connection's current schema. By default, four concurrent psycopg COPY workers consume generated batches through bounded queues. Canonical-order index construction and receipt digest verification also run per table in parallel. --workers accepts 1 through 16; the shared queue holds at most one pending --batch-size batch per active worker, in addition to the batches being written. URL-created engines support all 16 workers. For caller-supplied SQLAlchemy QueuePools, COPY concurrency is capped at the configured nonzero pool size without depending on overflow connections. Any connected worker can consume the next batch. Shutdown continues delivering stop signals while consumers remain alive, even when another writer fails. The tables load in order, starting with Catalog because the interaction and compatibility tables reference it. Each table has one Python row producer feeding its COPY workers; increasing the worker count does not parallelize generation of a single table.

PostgreSQL CLI provisioning computes the Generation Manifest's table counts and canonical digests as rows are generated for COPY, so it makes one generation pass rather than a preliminary manifest pass followed by another generation pass. After all COPY workers finish, it builds the same content-derived dataset ID, creates canonical-order indexes, and independently reads the staging tables to verify their counts and digests. The manifest, oracle, and receipt files are written only after the verified database publication succeeds. Direct generate_relational_data_source calls still build a manifest immediately, and the SQLite path retains its existing generation behavior.

For Python callers, provision_postgresql(config, destination, workers=4) in recommendations.synthetic.postgresql returns the completed generated source and its verified MaterializedSource. materialize_postgresql(source, destination, ...) remains available when the caller already has a fully manifested source. Both accept a psycopg SQLAlchemy URL or Engine. The shared MaterializedSource exposes engine, canonical queries, and receipt, and disposes an engine it created when used as a context manager.

Workers write only collision-resistant staging tables. After counts, logical SHA3-512 digests, and relational checks pass, one short transaction swaps the staging tables into the canonical names. Existing source tables fail closed; use --replace only against an explicitly disposable or replaceable destination. A failed worker, index build, or receipt check leaves the last published tables intact. Staging cleanup is best effort; if cleanup itself fails, uniquely named staging tables remain inert and should be inspected before a later manual removal.

Append activity through a newer cutoff

Use --append to keep the existing Catalog, compatibility rules, and interactions while adding new activity between the last committed cutoff (inclusive) and a newer cutoff (exclusive). The following walkthrough uses an existing PostgreSQL development dataset and the default Docker output directory. It requires Docker Compose, the sibling ../telemetry checkout, and the original generation manifest. Appending a full year adds another 100 million views and 20 million purchases; a shorter interval adds proportionally fewer rows.

1. Open a terminal in the repository and confirm the existing manifest is present.

cd /path/to/recommendations
ls -l artifacts/generated-development/generation-manifest.json

Replace /path/to/recommendations with your checkout path. Keep the existing gitignored .env file containing GENERATED_SOURCE_POSTGRES_PASSWORD; Compose reads it automatically. Use the same password and output directory used for original generation. If you configured GENERATED_SOURCE_OUTPUT_DIR, inspect that host directory instead; the container path below remains /app/artifacts/generated-development.

1. Ensure the generated-source database is running.

docker compose -f compose.generated-source.yaml up -d generated-source-db
docker compose -f compose.generated-source.yaml ps

Wait for the database to show as running and healthy. This starts the existing database volume without regenerating its contents.

1. Build the updated generator image.

docker compose -f compose.generated-source.yaml build generator

Rebuild after updating the generator code so the container includes append support. This Docker workflow does not require installing the Python environment on the host.

1. Capture the new cutoff once and save the printed timestamp.

APPEND_CUTOFF="$(date -u +%Y-%m-%dT%H:%M:%SZ)"
echo "$APPEND_CUTOFF"

The cutoff must be at least 24 hours after the existing manifest's source.cutoff. Keep this terminal open and retain the exact timestamp for retries. To use a fixed cutoff, assign an explicit UTC timestamp instead, such as APPEND_CUTOFF='2026-09-23T00:00:00Z'.

1. Run the append in the same terminal.

docker compose -f compose.generated-source.yaml run --rm generator \
  recommendations-generate-source \
  --append \
  --database-url-env GENERATED_SOURCE_DATABASE_URL \
  --output-dir /app/artifacts/generated-development \
  --cutoff "$APPEND_CUTOFF"

Use the complete command above. Running only docker compose -f compose.generated-source.yaml run --rm generator invokes the service's default replacement command. Do not add --replace; omit --workers or set it to 1.

Profile, seed, Commerce Scope, and optional capabilities are inherited from the manifest. Leave the command running until it exits. It verifies the complete source before and after insertion, so a large dataset can take substantial time. The CLI prints a JSON result at completion rather than displaying a progress bar. Allow disk space for added rows, indexes, and PostgreSQL transaction/WAL growth.

1. Verify the committed cutoff and counts after the command succeeds.

docker compose -f compose.generated-source.yaml exec generated-source-db \
  psql -U generated_source -d generated_source -c "
    SELECT
      manifest::jsonb #>> '{source,cutoff}' AS cutoff,
      entry->>'name' AS table_name,
      (entry->>'row_count')::bigint AS row_count
    FROM recommendations_generated_source_state
    CROSS JOIN LATERAL
      jsonb_array_elements(manifest::jsonb->'tables') AS entry;
  "

These are the counts recorded by complete-source verification. For the development profile, Catalog size remains 25,000 Items while interaction counts increase. The default host output directory now contains updated evidence:

artifacts/generated-development/generation-manifest.json
artifacts/generated-development/oracle-manifest.json
artifacts/generated-development/materialization-receipt.json

1. If interrupted, retry with the same cutoff.

In a new terminal, return to the repository and restore the exact timestamp printed in step 4:

APPEND_CUTOFF='PASTE-THE-SAVED-TIMESTAMP-HERE'

Rerun step 5 without evaluating date again. Failure before commit rolls back new rows. If the database committed but evidence export failed, retrying the exact latest cutoff repairs the local files without adding duplicate activity. Wait for an earlier generator process to exit before retrying; concurrent appends are rejected.

1. Submit a separate Training Run against the extended source.

The repository provides config/generated-data-source.json, which registers the generated tables as generated-source for synthetic-property / synthetic-catalog. Start the API and worker against it before submitting the run:

export RECOMMENDATIONS_DATA_SOURCES_FILE_HOST="$PWD/config/generated-data-source.json"
export MERCHANT_PLATFORM_DATABASE_URL="postgresql+psycopg://generated_source:${GENERATED_SOURCE_POSTGRES_PASSWORD}@host.docker.internal:5434/generated_source"
docker compose up --build -d api worker
curl --fail-with-body -sS http://127.0.0.1:8000/openapi.json >/dev/null

host.docker.internal lets the API and worker containers reach the generated-source database published by Docker Desktop on port 5434. Source .env first when it contains GENERATED_SOURCE_POSTGRES_PASSWORD; URL-encode that password if it includes URL-reserved characters. The recommendation API has no /ready route; /openapi.json is its non-mutating availability probe. /health and /ready belong to the Simulation Storefront. The default simulation configuration points at a different database and scope. Use your actual registered identifiers if they differ from the generator defaults below.

curl --fail-with-body -sS \
  -X POST http://127.0.0.1:8000/v1/training-runs \
  -H 'Content-Type: application/json' \
  -H "Idempotency-Key: synthetic-after-append-${APPEND_CUTOFF}" \
  -d '{
    "data_source_id": "generated-source",
    "tracking_id": "synthetic-property",
    "catalog_id": "synthetic-catalog"
  }'

Replace RUN_ID with the returned run_id to inspect its state:

curl --fail-with-body -sS http://127.0.0.1:8000/v1/training-runs/RUN_ID

Reuse the idempotency key when retrying the same submission. Choose a new key for an intentionally new Training Run. Appending changes source data; only successful training publishes a new Recommendation Snapshot.

Append guarantees and limits

The explicit command overrides Compose's default replacement command. The first append needs the existing generation-manifest.json in the mounted output directory; it verifies the database against that manifest before inserting anything. Profile, seed, Commerce Scope, and optional capabilities are inherited. Conflicting explicit options and --append --replace are rejected. SQLite append is not supported.

New row quotas follow the base profile's daily rate. For example, development adds approximately 136,986 views and 5,479 purchase rows per day. Cumulative integer rounding preserves the long-term totals. An extension must span at least 24 hours; the exact latest committed cutoff can be retried without inserting rows. Earlier cutoffs are rejected. Use a fixed timestamp to retry precisely; rerunning a command containing date requests a new cutoff. Generation boundaries are part of the identity: one ten-day append need not produce the same rows as ten one-day appends.

The generator reuses the base Catalog and interaction distributions, namespaces each batch's Sessions and Orders, and advances table-local row sequences. It deterministically maps generated timestamps into the new interval. Append intervals omit the original profile's deliberately oversized test groups. Their aggregate source is not an unmodified named profile, and the Oracle Manifest explicitly marks the original ranking and distribution oracles unavailable.

Append uses one COPY writer in one transaction, preserving existing rows without copying the old dataset into staging tables. --workers must be omitted or set to 1 for this mode. Bounded reads verify the complete source before and after insertion, so verification still scans the existing 120 million rows and can take substantial time. PostgreSQL also needs space for new rows, indexes, and transaction/WAL growth. Existing indexes and constraints remain in place.

Writers are serialized with a schema-scoped advisory lock and source-table locks; ordinary reads remain allowed. Another append fails promptly, and conflicting table locks time out after five seconds. COPY or digest failure rolls back all new rows and lineage. A successful commit includes the aggregate manifest in recommendations_generated_source_state. This is generator-owned metadata in the generated database, not a service control-database migration.

The database record lets a retry repair local evidence after a commit followed by an export failure. generation-manifest.json, oracle-manifest.json, and the standard v3 materialization receipt are exported through the shared evidence writer. Keep the original/ancestor manifest when recovering; unrelated source manifests are rejected. Lineage retains one base manifest and up to 256 cutoff boundaries. --replace deliberately starts a new lineage and clears this state.

Python callers use the existing provision_postgresql(config, destination, append_to=manifest) entry point, with config.cutoff naming the new cutoff and all other source options matching the parent. It returns GeneratedSourceEvidence and MaterializedSource; ordinary creation and replacement continue returning a regenerable source and MaterializedSource. Interval generation lives in GeneratedRelationalDataSource.extend; PostgreSQL transaction/verification behavior stays in the materializer, and manifest/receipt export uses the common evidence path.

Performance and verification limits

Training uses bulk insertion for derived aggregates. With pipeline.threads above one (the default is four), one background writer overlaps those inserts with source reading and reduction. One derived batch may be in flight; raw interactions remain in the ordered source stream. Increasing threads does not create additional Python source readers. Changes to the worker image apply to subsequent runs after a rebuild; let an active run finish before recreating its worker.

On a local Apple Silicon machine with Python 3.14.7 and PostgreSQL 18, a reduced development profile with 25,000 Catalog Items, 500,000 views, and 20,000 purchases took 17.44 seconds through the earlier two-pass path and 15.07 seconds through one-pass provisioning at four workers. The manifests matched byte for byte. This is a local comparison, not a runtime target or evidence for the unmodified 52-million-interaction development profile. Generation, per-row canonical hashing, and readback of the largest table still limit throughput; worker count alone will not remove those costs. The qualification profile requires its separate approved environment and evidence gates.

A subsequent local profile on an Apple M2 Pro (12 cores, 32 GiB), Python 3.14.7, and disposable PostgreSQL 18.6 measured the same reduced profile at seed 47 with four COPY workers. Rechecking the prior algorithms after warm-up gave 15.10–15.13 seconds; the optimized path took 10.74–11.03 seconds. Initial baseline runs varied from 14.76 to 26.02 seconds, so these are local observations rather than a general performance guarantee. All eight production-path runs produced byte-identical Generation Manifests and verified database receipts.

The optimizations sum repeating group-size cycles arithmetically, avoid rebuilding the same Catalog cohort union for each Item, and reduce canonical timestamp and JSON encoding overhead. They preserve row ordering, RNG consumption, canonical bytes, and dataset identity. The schedule search performs bounded work independent of row count; regression tests compare the previous schedule on small inputs and enforce a work bound at 50 million rows.

Measured stage	Prior algorithms, median seconds	Optimized, median seconds
Full provisioning	15.11	10.91
Generation and COPY	8.65	7.32
Canonical index construction	0.75	0.82
Independent database digest verification	3.46	2.55
Producer enqueue calls, included in generation	0.015	0.022

The small enqueue duration indicates little producer backpressure in this workload. Stage totals exclude source initialization and other orchestration; they need not sum to full provisioning.

A separate throwaway experiment split views into fixed ranges of 4,096 complete sessions, used per-session seeds, and bounded pending process tasks to twice the process count. Generation, canonical encoding, process transfer, and ordered hashing of 500,000 views took 3.25–3.32 seconds serially, 2.15–2.18 seconds with two processes, and 1.30–1.37 seconds with four. Full provisioning of that experimental dataset took 11.10–11.23 seconds serially and 8.79–8.90 seconds with four generation processes. Experimental digests matched across process counts and database receipts verified. Each process initialized its Catalog once; the largest observed partition contained 29,096 rows and 3.42 MB of canonical bytes. At most eight process tasks were pending.

The experiment is not the production generator: its RNG semantics change the dataset, and full behavioral-oracle, failure, and full-scale memory validation remain necessary before adoption. Its roughly 2.5-fold generation improvement yielded only about a 21% end-to-end improvement. Local aggregate results and runnable experiment scripts are under .build/synthetic-performance/; these ignored diagnostic artifacts contain no exported interaction rows.

Generator v3 corrects a separate purchase-schedule defect in v2: the search assumed a 3.7-row average although the actual cycle averages 3.55. At development and qualification sizes, that mismatch produced a group requiring more distinct products than the Catalog contained, preventing generation from completing. The estimate now comes directly from the cycle. Regular purchase groups remain within 200 distinct products; the deliberate 201- and 400-product groups, exact row quotas, and 5% repeated-row quota remain intact. Named profiles are checked with and without offline purchases.

The version change is recorded in GenerationManifest.generator_version; profile and manifest schema versions are unchanged. Previously bounded schedules remain identical, while affected purchase streams and their content-derived dataset IDs change. The timings above describe the earlier byte-compatible optimization, not a benchmark of the corrected full profiles. This fix does not add process-based generation or change RNG seeding.

A materialization receipt proves that one database realization matches its Generation Manifest. It does not by itself establish a PostgreSQL Qualification Claim; qualification still requires the approved profile, environment, service build, and evidence gates.

The command writes a verified materialization-receipt.json; the SQLite path additionally writes generated-source.sqlite. Use the returned MaterializedSource.queries with SqlAlchemySourceAdapter to run the normal Training Run pipeline.

The generator owns synthetic-source provisioning. The recommendation worker continues to read only the registered Relational Data Source and retains no raw interaction staging path.