TDM23 Python and Data-Layer Potential Refactoring 20260924.pdf

TDM23 Python and Data-Layer Potential Refactoring for TDM28

Draft 20260924

This document reflects a code review done by CTPS.

Python and Data-Layer Refactoring Synthesis

Status: proposal under review. This document describes a recommended direction, not the current implementation or an approved delivery commitment. It was checked against the repository on 2026-09-

24. Production components still use the Qt/threaded runners and direct SQLite access; the execution-neutral runner is a limited demonstration, not a production migration.

Objective

Refactor TDM23 so that model execution originates in TransCAD, Python code is safe in both embedded and subprocess runtimes, and model users can access data through familiar published files without requiring direct database access.

The recommended direction is:

1. Make Python components synchronous, execution-neutral, and reliably repeatable.

2. Let TransCAD invoke those components through one stable Python entry point.

3. Use TransCAD 10 embedded Python as the preferred mode after compatibility and parity gates pass, while retaining PythonScript as a supported fallback.

4. Publish a governed folder of CSV files as the standard scenario data product for direct user access.

5. Keep SQLite as the model execution store unless a separate, evidence-based architecture decision approves a replacement.

DuckDB and Parquet may provide technical benefits, but they do not by themselves provide the browse-and-open workflow sought for nontechnical model users. They remain options for developer and analyst workflows rather than default user requirements.

Refactoring Priorities

1. Refactor Python before changing how TransCAD calls it

The current pattern assumes that every model step runs in a disposable Python process. Components create Qt applications and threads, enter event loops, call sys.exit() , and rely on process termination to release log handlers, SQLite connections, and other state. That pattern is not suitable for repeated calls in TransCAD 10's persistent, in-process Python runtime.

The first priority is therefore to make code/model/tdmpy independent of its execution host:

Replace QThread -based model classes with ordinary synchronous classes.

Remove component-owned QApplication , GUI event loops, and successful SystemExit calls.

Inject a small status-reporting interface for GISDK, logging, tests, or an optional standalone Qt UI.

Centralize exception handling at the Python entry point; components should clean up and re-raise failures.

Make logger setup idempotent and close database connections and files deterministically.

Remove hard-coded paths, wildcard imports, and duplicated runner wrappers.

This is a prerequisite for broad embedded-Python adoption. It also improves subprocess execution and automated testing even if embedded Python is delayed.

2. Establish one TransCAD-to-Python execution contract

TransCAD should own component selection and lifecycle initiation. Both embedded and subprocess modes should call the same public Python function, for example:

run_component(component, json_file, options=None)

A component registry should map stable external names to implementations and argument adapters. The entry point should validate inputs, construct the component and status reporter, run synchronously, return only GISDK-compatible values, and allow failures to reach GISDK.

The GISDK layer should provide three explicit modes:

process : use the existing PythonScript path.

embedded : use TransCAD 10 CreateObject("Python", ...) .

auto : select embedded only when its prerequisites pass.

Keep process as the production default during migration. After each component has passed output, failure, cleanup, and repeated-call tests, make embedded the normal TransCAD 10 mode. Do not retry a component automatically in process mode after embedded execution has begun, because that could repeat partial writes.

PythonScript should remain available for TransCAD 9, troubleshooting, native-library incompatibilities, memory-heavy work, and cases where process isolation is safer.

3. Stabilize the embedded runtime as a separate deployment concern

A limited MVP demonstrates that TransCAD-originated embedded calls can reach an execution-neutral Python component. It does not establish production readiness or parity for existing model components. The tested UV dual-layout is a compatibility workaround rather than a durable production environment. Package compatibility observations, including pywin32 and caliperpy installation, must be rechecked against the versions selected for deployment.

For production, prefer two reproducible environments:

A dedicated, distribution-style interpreter registered for embedded TransCAD 10 execution, with official caliperpy and its dependency lock.

The existing UV virtual environment for PythonScript process execution.

If the shared dual-layout environment remains temporarily, automate its setup, repair, package installation, prefix checks, and smoke tests. Registration, package readiness, and embedded execution should be separate deployment gates.

4. Define a governed CSV data contract

A CSV folder can improve direct accessibility because users can browse files, open small tables in Excel, and use familiar GIS or reporting tools without learning SQL or a database browser. It should be treated as a managed data package, not as an undocumented collection of files.

Every scenario package should include:

A README.xlsx or similarly accessible index describing the available datasets and how to use them.

A versioned data dictionary defining columns, types, units, null handling, keys, relationships, and whether a dataset is editable.

A machine-readable manifest containing scenario and run IDs, contract version, file list, row counts, checksums, and completion status.

Stable filenames, column names, encoding, quoting, null representation, and deterministic row ordering.

Separate inputs , summaries , and detailed directories.

Numbered file parts for tables too large for one practical CSV or Excel.

CSV does not enforce types, keys, relationships, or transactions. A publication service therefore needs explicit metadata and validation for the guarantees it claims. It should export from a consistent SQLite snapshot to a staging directory, validate the package, and promote it only after the model run succeeds. Promotion by directory rename protects the final publication path on filesystems that support atomic rename; it is not equivalent to a database transaction. Completed packages should be treated as immutable publications.

5. Introduce a narrow data-access boundary

Direct SQL is widespread in the current components. A storage boundary may improve testability and consistency, but the proposed DataStore API has not been validated against representative model workloads. Prototype it with a small read-heavy component and a large write-heavy component before requiring all components to adopt it.

Candidate shared responsibilities include:

typed reads and writes;

logical key and relationship validation;

table replacement and append rules;

error propagation and cleanup.

Package staging, manifests, checksums, and publication belong in a separate publication service. SQLite remains the execution store in this proposal. Any future backend replacement requires its own decision record, prototype, and performance and recovery evidence.

Storage and Access Options

Option Recommended role Assessment

Governed

CSV folder

User-facing publication Directly accessible and portable when accompanied by metadata and validation; not proposed as the model's transactional execution store.

XLSX Index, data dictionary, curated summaries, and small governed inputs

Familiar and useful, but unsuitable for complete multi-million-row tables.

SQLite Model execution store Already integrated throughout the model and provides transaction semantics and indexed queries. Users need not access it directly.

Parquet Optional technical format More compact and typed than CSV, but less accessible. Do not make it the standard user-facing format.

DuckDB Optional developer/analyst tool Useful for technical analysis but still requires

SQL or specialized tooling. It does not solve the model-user usability requirement.

The immediate design question is how to publish understandable, verifiable tabular outputs without weakening the integrity of model execution. Publishing CSV from a consistent database snapshot separates that concern from any future storage-engine decision.

Recommended Target Architecture

TransCAD model step +-- TransCAD 10 embedded Python (preferred after validation) +-- PythonScript subprocess (supported fallback / TransCAD 9) v shared Python entry point and component registry v synchronous execution-neutral component +-- status reporter: GISDK / log / test / optional Qt +-- data-access boundary +-- SQLite model execution store +-- publication service +-- governed scenario CSV package +-- README and data dictionary +-- manifest, row counts, and checksums +-- inputs +-- curated summaries +-- partitioned detailed tables

Normal model users should not need SQL, Python, or direct database access. A Model Data Manager can help users locate datasets, open the package index, create editable templates for approved small inputs, review validation reports, and publish a new scenario. Spreadsheet changes should produce versioned input overrides and a newly initialized scenario; they should never modify a completed result package.

Delivery Sequence

Phase 1: Execution-neutral Python foundation

Refactor the base class, status reporting, error boundary, logging, resource cleanup, and representative components. Prove repeated execution in one Python process without Qt, duplicate logging, stale state, or retained file/database locks.

Phase 2: Unified TransCAD runner

Implement the component registry and one GISDK-compatible entry point. Correct the GISDK wrappers so they return real component status rather than unconditionally reporting success. Keep process mode as the default.

Phase 3: Incremental embedded rollout

Automate and validate the embedded environment, then migrate components from small deterministic reporting tasks through large pandas/NumPy and geospatial tasks. For each component, compare legacy process, refactored process, and embedded results. Confirm that failures reach TransCAD and that subsequent calls in the same session remain healthy.

Phase 4: Governed CSV package

Create the versioned dataset catalog, manifest, accessible package index, curated summaries, and partitioned detailed CSVs. Initially generate the package from a consistent SQLite snapshot. Confirm that users can locate and use supported data without SQL, Python, SQLite, DuckDB, or Parquet tools.

Phase 5: Governed scenario editing

Pilot validation-only templates for small approved inputs such as parking , walkbike , and enrollment . Feed approved inputs through the existing scenario initialization path rather than editing completed outputs. Enable publication only after stable keys, lifecycle metadata, locking, staging, and failure propagation are complete. A valid edit creates a new scenario.

Phase 6: Review the storage architecture

Evaluate the storage boundary after the execution refactor and publication service have production evidence. Keep

SQLite unless a measured limitation justifies replacement. A replacement proposal should compare transaction safety, query patterns, memory use, restart behavior, performance, and downstream TransCAD compatibility; user access alone is not a reason to replace an internal execution store.

Decision Gates and Success Measures

Embedded mode should become the default only when:

the registered environment is reproducible;

official caliperpy and required native dependencies are compatible;

repeated calls do not leak resources or destabilize TransCAD;

component outputs match the process baseline;

exceptions and cancellation are reported correctly.

These gates should be reproduced against the deployment environment before embedded execution is enabled for production components.

The governed CSV publication can be released when:

every dataset has an approved contract, logical key, and validation rules;

large tables are partitioned and can be processed with bounded memory;

a failed write or model step publishes no partial package;

row counts, relationships, reconciliation totals, and checksums are verified;

published model results match a consistent SQLite baseline;

inputs and generated results are clearly separated;

completed scenario packages are immutable and recoverable.

These publication gates should become executable acceptance tests as the catalog is implemented.

The refactor is successful when TransCAD reliably initiates model components, both execution modes share the same calculation code, failures cannot be reported as success, repeated embedded calls are safe, completed scenario data remains reproducible, and users can browse and use the governed CSV package without database knowledge or specialized data tools.

Final Recommendation

Proceed with a staged TransCAD-native Python refactor, but treat synchronous, execution-neutral components and reliable failure propagation as the first deliverables. Use embedded Python as the intended TransCAD 10 destination, not as a direct replacement for the current subprocess wrapper. Preserve process mode as a permanent isolation and compatibility option.

Make a governed folder of CSV files the target scenario data product for direct user accessibility. Include an accessible index and data dictionary, a machine-readable manifest, stable schemas, partitioned large tables, checksums, validation, and atomic publication. Use XLSX only where it improves navigation, documentation, summaries, or small-input editing.

Keep SQLite as the execution store while introducing a narrow, tested data-access boundary and a separate publication service. Revisit the storage engine only through a future architecture decision supported by measured needs. DuckDB or Parquet may remain optional technical tools outside the standard model-user workflow.

References

1. SQLite transactions (https://www.sqlite.org/transactional.html) https://www.sqlite.org/transactional.html

2. SQLite Online Backup API (https://www.sqlite.org/backup.html)

3. W3C Model for Tabular Data and Metadata on the Web (https://www.w3.org/TR/tabular-data-model/)

4. Frictionless Data Package specification (https://specs.frictionlessdata.io/data-package/)

Built from 1 selected source documents on 2026-09-24.

https://www.sqlite.org/backup.html https://www.w3.org/TR/tabular-data-model/ https://specs.frictionlessdata.io/data-package/