docs/architecture.md

DemoPrep Architecture

Current Migration Direction

DemoPrep is moving from a DDL-first pipeline to a dataset-first pipeline for demo-critical quality.

Legacy flow:

research -> generated DDL -> populate columns -> repair rows -> ThoughtSpot

Target flow:

research -> scenario contract -> canonical dataset -> derived DDL -> ThoughtSpot

The reason for the change is data quality. A business demo needs coherent facts across a full dataset: seasonal trends, stable product/store/channel behavior, metric formulas, and believable outliers. Those are easier to generate before physical DDL exists.

Package Layout

New code should live under demoprep_app/:

demoprep_app/
  controllers/   UI/API entry points and compatibility wrappers
  pipeline/      orchestration for end-to-end demo builds
  research/      research prompts and company context extraction
  scenario/      structured scenario contract
  dataset/       dataset-first contracts and generators
  ddl/           DDL derivation from generated datasets
  integrations/  external systems such as Snowflake, Supabase, ThoughtSpot, LLMs

Large top-level modules such as chat_interface.py remain in place during the migration. New behavior should be added in package modules and called from the legacy entry points only as compatibility wiring.

Dataset-First Runtime

The first runtime slice is wired into the main app. When dataset-first mode is enabled, the flow is:

scenario contract -> generated dataset bundle -> derived DDL -> Snowflake load

Runtime activation currently checks app settings plus:

DEMOPREP_DATASET_FIRST=1

This should be configured in the environment or persisted settings. The normal app entry point remains:

python app.py

In the UI, the stage may still display as DDL creation. In dataset-first mode that stage means the app has generated a dataset bundle and is deriving DDL from the dataset metadata, not asking the LLM to design the physical schema first.

Scenario Contract

The scenario contract is the central artifact. It captures:

• company and use case
• scenario type
• natural fact grain
• dimensions
• measures
• formulas
• value ranges
• seasonality
• business events
• dashboard questions

Dataset generators consume this contract and produce complete, coherent tables. DDL is then compiled from the generated dataset metadata.

For public/custom demos, the contract must be extracted from the user request and research before generation. The extractor lives in:

demoprep_app/scenario/extractor.py

The extractor is responsible for preserving customer/domain nouns such as teams, venues, properties, product lines, fan segments, ticket tiers, service lines, or marketing channels. Scenario families are execution scaffolds; they should not be used as a substitute for understanding the specific demo context.

Scenario Families

The matrix should map many vertical / line / function combinations into a smaller number of scenario families. Current families live in:

demoprep_app/scenario/families.py

Routing lives in:

demoprep_app/scenario/selector.py

Specialized generators should be added only where the generic family generator does not reach demo-quality results. Current specialized generators are SaaS sales and retail sales.

The selector is only a fallback and routing hint. Public-quality custom demos should use the extracted scenario contract when available, then validate that the generated dataset includes concrete domain values from the prompt/research.

Test Harness Identity

The quality test harness should not infer schemas from company prefixes or run timestamps. It now reads the final model/liveboard links from the page, exports the exact ThoughtSpot model TML, and resolves the Snowflake schema from that model. If the model URL is missing, the test should fail clearly rather than guessing a schema.

Open Design Question: Date Modeling

Many generated models currently include a physical DATES or MONTHS table with derived fields such as month number, year, quarter, and year-quarter. ThoughtSpot can derive many of those attributes from a proper date column on the fact table, so the physical date dimension may be unnecessary for standard demo models.

Keep this as an explicit design decision before changing generation:

• Prefer a direct date column on the fact table when the model only needs normal calendar drill paths such as day, month, quarter, and year.
• Keep a physical date/time dimension when the scenario needs custom calendar semantics such as fiscal periods, academic terms, holidays, business days, seasons, campaign periods, or event calendars.
• If a date dimension remains, avoid exposing redundant derived fields that make the ThoughtSpot model noisy.

No runtime change has been made for this yet. The next pass should inspect a few recent models and decide whether the generated date dimension is adding demo value or just cluttering the worksheet.